This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use Stack Overflow.

new plugin sfPropelSqlDiffPlugin - build diff sql

Ask help about existing plugins, or find advice about the way to build up your own.

new plugin sfPropelSqlDiffPlugin - build diff sql

by gray » Thu Jan 10, 2008 7:58 pm

Hi

sfPropelSqlDiff plugin generate diff.sql file, which contains difference beetween schema.yml and current database structure.

It provides new symfony task - propel-build-sql-diff which will create file diff.sql with ALTER TABLE statements, whitch will make current database structure exactly as it described schema.yml without deleting data in tables as it does propel-build-sql task.

http://trac.symfony-project.com/wiki/sfPropelSqlDiffPlugin

Comments, bugreports and paches are wellcome :)~ :-)~ :smile:
gray
Member
 
Posts: 30
Joined: Thu Jan 10, 2008 7:50 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Mob[st]er » Fri Jan 11, 2008 10:21 pm

#1 THe idea is GREAT ! Congratulations ! Thank you for doing this !

#2 I found many bugs :(~ :-(~ :sad:

Amongst those bugs:

Notice: Undefined index: fkeys in C:\mywebprojects\project\plugins\sfPropelSqlDiffP
lugin\lib\dbInfo.php on line 219

Notice: Undefined index: fkeys in C:\mywebprojects\project\plugins\sfPropelSqlDiffP
lugin\lib\dbInfo.php on line 233


Using symfony version 1.0.11-PRE on windows XP with wamp

It creates the diff file but I'm just afraid to use it as I don't want to screw up my DB ..
Mob[st]er
Junior Member
 
Posts: 11
Joined: Thu Aug 23, 2007 10:59 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by gray » Sat Jan 12, 2008 10:30 pm

Mob[st wrote: er wrote on Fri, 11 January 2008 21:21]
Amongst those bugs:

Notice: Undefined index: fkeys in C:\mywebprojects\project\plugins\sfPropelSqlDiffP
lugin\lib\dbInfo.php on line 219

Well, in my php configuration, notices are disabled... Ok, i'll fix it.
gray
Member
 
Posts: 30
Joined: Thu Jan 10, 2008 7:50 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Mob[st]er » Fri Jan 18, 2008 7:59 pm

Hello again,

I wanted to thank you again for the plugin, i've been looking for something exactly like that for a couple of months now ;)~ ;-)~ :wink: THe only thing I found before that was http://syncsql.com/ .

Also, I wanted to know if you had some time to fix these errors ?

THanks again !

P.S. Think should be included in 1.1 directly !
Mob[st]er
Junior Member
 
Posts: 11
Joined: Thu Aug 23, 2007 10:59 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by gray » Fri Jan 25, 2008 12:29 am

Mob[st[/b]</td></tr><tr><td class="quote"> er wrote: er wrote on Fri, 18 January 2008 18:59]
P.S. Think should be included in 1.1 directly !

This plugin can't be included in 1.1. It isn't yet stable. It is tested only for mysql.
gray
Member
 
Posts: 30
Joined: Thu Jan 10, 2008 7:50 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by halfer » Fri Jan 25, 2008 4:24 pm

This is an excellent idea, something that symfony IMO has needed for a long time. I've not tried it yet, but nevertheless, thank you!

PS will it deal with schema.xml files, and schemas within plugins?
halfer
Faithful Member
 
Posts: 10148
Joined: Mon Jan 30, 2006 1:16 pm
Location: West Midlands, UK

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Mob[st]er » Fri Jan 25, 2008 4:38 pm

Hello again gray !

THanks for the update ! Also, I don't know if it's related to the current but but it appears that when you add a field to the yml file and then you run de sqldiff, the diff.sql generated contains the new fields BUT it doesn't contain the "location" on where to add the field.

Example:
ALTER TABLE test ADD 'some_field'
instead of
ALTER TABLE test ADD 'some_field' AFTER 'some_other_field'

Is it just me ?

Regards
Mob[st]er
Junior Member
 
Posts: 11
Joined: Thu Aug 23, 2007 10:59 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by michael.piecko » Fri Jan 25, 2008 4:57 pm

Hmm. "ALTER ... AFTER ...", it is not common SQL, isn't it?

Michael
michael.piecko
Faithful Member
 
Posts: 624
Joined: Fri Jun 30, 2006 5:56 pm
Location: Germany

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by halfer » Fri Jan 25, 2008 5:04 pm

Avoid AFTER if you can. The x-platform way to re-order table elements is to rename the table, rename constraints, recreate the table, recreate the constraints for the new table, and copy the data across. However this is a bit involved, so I'd recommend not implementing this until the current version is stable :)~ :-)~ :smile:
halfer
Faithful Member
 
Posts: 10148
Joined: Mon Jan 30, 2006 1:16 pm
Location: West Midlands, UK

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by gray » Wed Jan 30, 2008 7:42 pm

New version has been uploaded.

Changelog:
- added task propel-insert-sql-diff
- fixed php notices
- fixed problem with default values like ''

About fields order in table and AFTER... Symfony user dont work directly with database, but with schema.yml, so i think it isn't important how fields is placed in "real" table.
gray
Member
 
Posts: 30
Joined: Thu Jan 10, 2008 7:50 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by nautilebleu » Fri Feb 01, 2008 2:28 pm

I'm currently trying to update my schema using the plugin.

I've encounter one problem and found another small thing that should be changed:

<ol type="1">
<li>First of all, the file dbInfo.php uses a short open tag, which should be avoid because the default option in the php.ini file is short_open_tag = Off ;
<li>The second point is less important but in order to follow the guideline and the naming convention of symfony, the file dbInfo.php should be named dbInfos.class.php
</ol>
all about me
t-shirts
nautilebleu
Faithful Member
 
Posts: 281
Joined: Fri Jun 16, 2006 2:54 pm
Location: Caen, France

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Dincho » Mon Feb 11, 2008 5:15 pm

Hi, excellent idea for plugin! Thanks!

But something going wrong here:
Code: Select all
>> propel-sql-diff building database patch

                                         
  [SQLException]                         
  Invalid resultset column: Create Table                                       


Symfony [url=mailto:1.0.11@LAMP]1.0.11@LAMP[/url]

All common propel builders works fine.
Also i have try this after and before some schema changes, but same result.
Dincho
Junior Member
 
Posts: 15
Joined: Fri Jul 27, 2007 7:35 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by gray » Mon Feb 11, 2008 6:13 pm

Dincho wrote:
But something going wrong here:
Code: Select all
>> propel-sql-diff building database patch
                                         
  [SQLException]                         
  Invalid resultset column: Create Table                                       



I think you have something specific in schema.yml. If you share it (if it isn't secret) i will try to fix it.
gray
Member
 
Posts: 30
Joined: Thu Jan 10, 2008 7:50 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by gray » Mon Feb 11, 2008 6:21 pm

The problem is because it can't find column "Create Table"
in query result
show create table <tablename>
gray
Member
 
Posts: 30
Joined: Thu Jan 10, 2008 7:50 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Dincho » Mon Feb 11, 2008 6:56 pm

Why this forum have no PM ? :(~ :-(~ :sad:
Please drop me an empty mail to:

$str = 'dincho xbuv com';
str_replace(' ', '.', $str); :D~ :-D~ :grin:

I do not want to share my schema to public, sorry
Dincho
Junior Member
 
Posts: 15
Joined: Fri Jul 27, 2007 7:35 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by skyblaze » Thu Feb 28, 2008 1:38 am

anyone using this plugin right now? is it good/stable enough?
skyblaze
Faithful Member
 
Posts: 205
Joined: Thu Jan 24, 2008 4:24 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by klemens_u » Mon Mar 03, 2008 11:49 am

Hello!

It seems like sfPropelSqlDiffPlugin is missing?!?

I can't install it via symfony plugin-install nor via subversion.

Any reason for this?

;)~ ;-)~ :wink: Klemens
User avatar
klemens_u
Junior Member
 
Posts: 19
Joined: Mon Sep 10, 2007 7:03 pm
Location: Vienna, Austria

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by gray » Mon Mar 03, 2008 2:11 pm

You can try

symfony plugin-install http://plugins.symfony-project.com/sfPropelSqlDiffPlugin-0.1 .1

There is some issues with version i suppose.
gray
Member
 
Posts: 30
Joined: Thu Jan 10, 2008 7:50 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by klemens_u » Mon Mar 03, 2008 4:05 pm

Thanks!

(a note on the wiki page regarding the version thing wouldn't hurt!)

btw: while testing sfPropelSqlDiffPlugin I noticed that the plugin is using the files in data/sql. It happened in my place, that there where some old files form previous 'propel-build-sql' (I suppose). The result was a messed up diff file. I had to delete these files to get a proper result. Could this be done automatically by calling propel-build-sql-diff?

Thanks for your effort!

;)~ ;-)~ :wink: Klemens
User avatar
klemens_u
Junior Member
 
Posts: 19
Joined: Mon Sep 10, 2007 7:03 pm
Location: Vienna, Austria

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by gray » Mon Mar 03, 2008 4:31 pm

klemens_u wrote: Could this be done automatically by calling propel-build-sql-diff?


Well, yes. It could be included in future version, or you can try to do it yourself ;)~ ;-)~ :wink:
gray
Member
 
Posts: 30
Joined: Thu Jan 10, 2008 7:50 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Emiliano.Gabrielli » Mon Mar 03, 2008 6:12 pm

nautilebleu wrote: I'm currently trying to update my schema using the plugin.

I've encounter one problem and found another small thing that should be changed:

<ol type="1">
<li>First of all, the file dbInfo.php uses a short open tag, which should be avoid because the default option in the php.ini file is short_open_tag = Off ;
<li>The second point is less important but in order to follow the guideline and the naming convention of symfony, the file dbInfo.php should be named dbInfos.class.php
</ol>


I can confirm the importance of these points, the second one raising an exception for me (dbInfo class not found)
Emiliano.Gabrielli
Junior Member
 
Posts: 8
Joined: Mon Mar 03, 2008 5:30 pm
Location: Rome

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Emiliano.Gabrielli » Mon Mar 03, 2008 6:19 pm

I was testing this very useful plugin, but I encountered a big big problem:

I'm used to divide my schema into a number of files, one for each macro feature I implement .. so in may case I have :

config/
schema.yml
profiling-schema.yml
toponomastic-schema.yml

and so on ..

Well, at the moment I run the symfony task schmaS and DB were quite in sync , *but* the sesulting diff.sql was a number of terrific

DROP TABLE !!

I think the plugin do not takes into account the files I mentioned, but symfony does, so it is to be considered as a plugin's bug
Emiliano.Gabrielli
Junior Member
 
Posts: 8
Joined: Mon Mar 03, 2008 5:30 pm
Location: Rome

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by klemens_u » Mon Mar 03, 2008 6:36 pm

Hi Emiliano,

try deleting everything in data/sql (backup first!).
Then run again propel-build-sql-diff

Does it help?

;)~ ;-)~ :wink: Klemens
User avatar
klemens_u
Junior Member
 
Posts: 19
Joined: Mon Sep 10, 2007 7:03 pm
Location: Vienna, Austria

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Emiliano.Gabrielli » Mon Mar 03, 2008 7:45 pm

I had hust tryed of course .. nope!
Emiliano.Gabrielli
Junior Member
 
Posts: 8
Joined: Mon Mar 03, 2008 5:30 pm
Location: Rome

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Emiliano.Gabrielli » Tue Mar 04, 2008 1:04 pm

Found the bug at least !!

The plugin has troubles with schema using a "description:" field

Code: Select all
 89     privacy:
 90       type: CHAR
 91       size: 4
 92       required: true
 93       default: "0000"
 94 #      description: "Bitmask rappresentante i consensi dati (generale,f,g,h)"
 95     auth_code:
 96       type: CHAR
 97       size: 32
 98       default: NULL
 99 #      description: "Codice di autorizzazione per l'attivazione o la modifica della password"
100     _uniques:
101       UNIQUE_email:


commenting out the "description" lines makes everything working nice !!
So this is a bug, plz try to fix it :)~ :-)~ :smile:
Emiliano.Gabrielli
Junior Member
 
Posts: 8
Joined: Mon Mar 03, 2008 5:30 pm
Location: Rome

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Emiliano.Gabrielli » Wed Mar 05, 2008 6:20 pm

I have found a number of very very seriuos bugs related to InnoDB tables:

constraints, indexes and relations often fail on insert due to a number of reasons....

one problem, for example, is the impossibility of DROP and INDEX *if* it is also a FK !!!
Emiliano.Gabrielli
Junior Member
 
Posts: 8
Joined: Mon Mar 03, 2008 5:30 pm
Location: Rome

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by gray » Thu Mar 06, 2008 5:27 pm

Emiliano.Gabrielli wrote: I have found a number of very very seriuos bugs related to InnoDB tables:

constraints, indexes and relations often fail on insert due to a number of reasons....

one problem, for example, is the impossibility of DROP and INDEX *if* it is also a FK !!!


Yes it is impossible to drop index, if it is used in foreign key.
If you drop both index and foreign key, plugin will generate DROP FOREIGN KEY statement, then DROP INDEX, and all will be ok.
But if you just drop index and create another one with fk column, plugin will not handle it.
I'll try to change it to generate first ADD INDEX and then DROP INDEX, hope it will not break something :)~ :-)~ :smile:
gray
Member
 
Posts: 30
Joined: Thu Jan 10, 2008 7:50 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Emiliano.Gabrielli » Thu Mar 06, 2008 6:11 pm

gray wrote:
Emiliano.Gabrielli wrote on Wed, 05 March 2008 17:20 wrote: I have found a number of very very seriuos bugs related to InnoDB tables:

constraints, indexes and relations often fail on insert due to a number of reasons....

one problem, for example, is the impossibility of DROP and INDEX *if* it is also a FK !!!


Yes it is impossible to drop index, if it is used in foreign key.
If you drop both index and foreign key, plugin will generate DROP FOREIGN KEY statement, then DROP INDEX, and all will be ok.
But if you just drop index and create another one with fk column, plugin will not handle it.
I'll try to change it to generate first ADD INDEX and then DROP INDEX, hope it will not break something :)~ :-)~ :smile:



I found that replacing

ALTER TABLE `t2` DROP KEY `t1_id` ;
ALTER TABLE `t2` ADD INDEX `FK_t1_id` (`t1_id`);

with a single

ALTER TABLE `t2` DROP KEY `t1_id` ,
`t2` ADD INDEX `FK_t1_id` (`t1_id`);

should work!
Emiliano.Gabrielli
Junior Member
 
Posts: 8
Joined: Mon Mar 03, 2008 5:30 pm
Location: Rome

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Emiliano.Gabrielli » Fri Mar 07, 2008 1:02 pm

Emiliano.Gabrielli wrote: Found the bug at least !!

The plugin has troubles with schema using a "description:" field

Code: Select all
 89     privacy:
 90       type: CHAR
 91       size: 4
 92       required: true
 93       default: "0000"
 94 #      description: "Bitmask rappresentante i consensi dati (generale,f,g,h)"
 95     auth_code:
 96       type: CHAR
 97       size: 32
 98       default: NULL
 99 #      description: "Codice di autorizzazione per l'attivazione o la modifica della password"
100     _uniques:
101       UNIQUE_email:


commenting out the "description" lines makes everything working nice !!
So this is a bug, plz try to fix it :)~ :-)~ :smile:





I found another BUG in comments handling:

if on the DB there is a comment containing parentheses, such as " ;)", it breaks the preg_match around line 45 of the class ...

plz try to fix it too :)~ :-)~ :smile:
Emiliano.Gabrielli
Junior Member
 
Posts: 8
Joined: Mon Mar 03, 2008 5:30 pm
Location: Rome

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by sumedh » Sat Mar 08, 2008 9:20 am

Hi,

I am not being able to get this to work. Everything else in my symfony and propel is working fine.

When I execute "symfony propel-build-sql-diff frontend", it first executes schema generation, and writes to lib.model.schma.sql, but then throws following error...

>> file- /home/sumedh/workspace/zam_fe/config/generated-schema.xml
>> propel-sql-diff building database patch

Warning: Cannot modify header information - headers already sent by (output started at /home/sumedh/workspace/zam_fe/plugins/sfPropelSqlDiffPlugin/ data/tasks/sfPropelSqlDiffTask.php:16) in /usr/share/php/symfony/exception/sfException.class.php on line 101

[exception] sfFileException
[message] Unable to open the log file "/home/sumedh/workspace/zam_fe/log/frontend_dev.log" for writing
[stack trace]

at () in SF_SYMFONY_LIB_DIR/log/sfLogger/sfFileLogger.class.php line 44
at sfFileLogger->initialize(array('file' =&gt; '/home/sumedh/workspace/zam_fe/log/frontend_dev.log')) in SF_ROOT_DIR/cache/frontend/dev/config/config_logging.yml.php line 24
at include('/home/sumedh/workspace/zam_fe/cache/frontend/dev/co nfig/config_logging.yml.php') in SF_SYMFONY_LIB_DIR/symfony.php line 90
at require('/usr/share/php/symfony/symfony.php') in SF_SYMFONY_LIB_DIR/util/sfCore.class.php line 55
at sfCore::callBootstrap() in SF_SYMFONY_LIB_DIR/util/sfCore.class.php line 33
at sfCore::bootstrap('/usr/share/php/symfony', '/usr/share/php/data/symfony') in SF_ROOT_DIR/apps/frontend/config/config.php line 8
at require_once('/home/sumedh/workspace/zam_fe/apps/frontend/co nfig/config.php') in SF_ROOT_DIR/plugins/sfPropelSqlDiffPlugin/data/tasks/sfPrope lSqlDiffTask.php line 29

My log file has rwx permissions for everyone...not sure why it mentions log file problem...

Any advice?
sumedh
Junior Member
 
Posts: 6
Joined: Sat Mar 08, 2008 9:15 am

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by Emiliano.Gabrielli » Mon Mar 10, 2008 10:16 am

sumedh wrote: Hi,

[exception] sfFileException
[message] Unable to open the log file "/home/sumedh/workspace/zam_fe/log/frontend_dev.log" for writing


Your log file is not writeable by the user you are using to run the task (you are in the cli interface, so you are not "apache" ... double check your permissions and try run "symfony fix-perms"
Emiliano.Gabrielli
Junior Member
 
Posts: 8
Joined: Mon Mar 03, 2008 5:30 pm
Location: Rome

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by sumedh » Mon Mar 10, 2008 10:57 am

Hi,

The log file is accessible for writing to all...

I also ran symfony fix-perms as you said...

But still getting the same error... :(~ :-(~ :sad:

My other symfony tasks are writing to the same log file properly...so may be permissions is not the problem?

Thanks in advance...
sumedh
Junior Member
 
Posts: 6
Joined: Sat Mar 08, 2008 9:15 am

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by cokker » Mon Mar 10, 2008 1:44 pm

Have you tried to delete log file so it get newly created?

greets
Sven
cokker
Faithful Member
 
Posts: 591
Joined: Mon Jan 15, 2007 10:43 am
Location: Germany

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by sumedh » Mon Mar 10, 2008 1:49 pm

Yup...it worked...

But I wonder why... :(~ :-(~ :sad:

I mean the log file is working fine for everything else...and it has rwx permission for everyone...

thanks any way...
sumedh
Junior Member
 
Posts: 6
Joined: Sat Mar 08, 2008 9:15 am

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by cokker » Mon Mar 10, 2008 1:54 pm

I think this has to do with caching of file-stats inside PHP.

http://www.php.net/manual/en/function.clearstatcache.php

greets
Sven
cokker
Faithful Member
 
Posts: 591
Joined: Mon Jan 15, 2007 10:43 am
Location: Germany

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by sumedh » Mon Mar 10, 2008 2:01 pm

Yup...permission caching would be the issue I suppose...thanx...

BTW, the next question is, how can I use this plugin to sync my Stage DB with my Dev DB?

I mean, right now it compares the schema.yml with current DB (which would be Dev), right? When developers work on some fetaure, they would change the Dev DB to test the feature...but later on, when we want to go for full QA, we want to make sure all Dev changes are in sync on Stage DB...

Can this plugin help in it?

Thanks in advance...
sumedh
Junior Member
 
Posts: 6
Joined: Sat Mar 08, 2008 9:15 am

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by gray » Tue Mar 18, 2008 5:14 pm

sumedh wrote: I mean, right now it compares the schema.yml with current DB (which would be Dev), right? When developers work on some fetaure, they would change the Dev DB to test the feature...but later on, when we want to go for full QA, we want to make sure all Dev changes are in sync on Stage DB...

Can this plugin help in it?


It can help, if all changes where in schema.yml, and it can't help if dev database was changed directly.
But there is some other tools for synchronizing two mysql databases.
gray
Member
 
Posts: 30
Joined: Thu Jan 10, 2008 7:50 pm

Re: new plugin sfPropelSqlDiffPlugin - build diff sql

by naholyr » Mon Mar 24, 2008 2:10 am

Very useful plugin, it solves a quite simple but every-day annoying issue, especially during development (losing all the test-data everytime is very boring >< ;).

I easily accept it cannot support renaming (how could it ?), and it's really good for me.

But your lib/dbInfo.php file uses short_tags ! this is evil, causing the output of the class in CLI, and because of that i always start my projects whining against this :P
Please fix this ^^

By the way, you could provide this new task I now always use insteda of propel-build-all && cc ;)~ ;-)~ :wink:
Code: Select all
pake_desc('rebuild model and upgrade database');
pake_task('propel-update-all',
        'app_exists', // 0. Pre-requisites
        'propel-build-model',     // 1. Rebuild model
        'propel-insert-sql-diff', // 3. generate and insert SQL
        'clear-cache'             // 4. Clear cache
);
naholyr
Faithful Member
 
Posts: 223
Joined: Mon Jun 25, 2007 11:55 am

Small fix for code

by yspro » Mon Mar 24, 2008 12:37 pm

In dbInfo.php class find method "getDiffWith" and replace the rows between "//drop, alter index" and "//drop, alter field":


<span name="php"><code><span style="color: #000000">
<span style="color: #0000BB"><?php </span><span style="color: #FF8000">//drop, alter index       </span><span style="color: #007700">if(</span><span style="color: #0000BB">$tabledata</span><span style="color: #007700">[</span><span style="color: #DD0000">'keys'</span><span style="color: #007700">])            foreach(</span><span style="color: #0000BB">$tabledata</span><span style="color: #007700">[</span><span style="color: #DD0000">'keys'</span><span style="color: #007700">] as </span><span style="color: #0000BB">$field</span><span style="color: #007700">=></span><span style="color: #0000BB">$fielddata</span><span style="color: #007700">) {             </span><span style="color: #0000BB">$otherdata </span><span style="color: #007700">= @</span><span style="color: #0000BB">$db_info2</span><span style="color: #007700">-></span><span style="color: #0000BB">tables</span><span style="color: #007700">[</span><span style="color: #0000BB">$tablename</span><span style="color: #007700">][</span><span style="color: #DD0000">'keys'</span><span style="color: #007700">][</span><span style="color: #0000BB">$field</span><span style="color: #007700">];             </span><span style="color: #0000BB">$ind_name </span><span style="color: #007700">= @</span><span style="color: #0000BB">$otherdata</span><span style="color: #007700">[</span><span style="color: #DD0000">'type'</span><span style="color: #007700">]==</span><span style="color: #DD0000">'PRIMARY'</span><span style="color: #007700">?</span><span style="color: #DD0000">'PRIMARY KEY'</span><span style="color: #007700">:</span><span style="color: #DD0000">"{$otherdata['type']} INDEX"</span><span style="color: #007700">;             </span><span style="color: #0000BB">$diff_ar </span><span style="color: #007700">= </span><span style="color: #0000BB">array_diff</span><span style="color: #007700">(</span><span style="color: #0000BB">$fielddata</span><span style="color: #007700">[</span><span style="color: #DD0000">'fields'</span><span style="color: #007700">],</span><span style="color: #0000BB">$otherdata</span><span style="color: #007700">[</span><span style="color: #DD0000">'fields'</span><span style="color: #007700">]);             </span><span style="color: #0000BB">$difference </span><span style="color: #007700">= (empty(</span><span style="color: #0000BB">$diff_ar</span><span style="color: #007700">)) ? </span><span style="color: #0000BB">FALSE </span><span style="color: #007700">: </span><span style="color: #0000BB">TRUE</span><span style="color: #007700">;                          if(</span><span style="color: #0000BB">$fielddata</span><span style="color: #007700">[</span><span style="color: #DD0000">'code'</span><span style="color: #007700">] and !</span><span style="color: #0000BB">$otherdata</span><span style="color: #007700">[</span><span style="color: #DD0000">'code'</span><span style="color: #007700">]) {                   if(</span><span style="color: #0000BB">$fielddata</span><span style="color: #007700">[</span><span style="color: #DD0000">'type'</span><span style="color: #007700">]==</span><span style="color: #DD0000">'PRIMARY'</span><span style="color: #007700">) {                     </span><span style="color: #0000BB">$diff_sql </span><span style="color: #007700">.= </span><span style="color: #DD0000">"ALTER TABLE `$tablename` DROP PRIMARY KEY; "</span><span style="color: #007700">;                   } else {                     </span><span style="color: #0000BB">$diff_sql </span><span style="color: #007700">.= </span><span style="color: #DD0000">"ALTER TABLE `$tablename` DROP INDEX $field; "</span><span style="color: #007700">;                   }             } elseif(</span><span style="color: #0000BB">$difference </span><span style="color: #007700">|| </span><span style="color: #0000BB">$fielddata</span><span style="color: #007700">[</span><span style="color: #DD0000">'type'</span><span style="color: #007700">]!=</span><span style="color: #0000BB">$otherdata</span><span style="color: #007700">[</span><span style="color: #DD0000">'type'</span><span style="color: #007700">]) { </span><span style="color: #FF8000">//fixed by [YS.PRO]                   </span><span style="color: #007700">if(</span><span style="color: #0000BB">$this</span><span style="color: #007700">-></span><span style="color: #0000BB">debug</span><span style="color: #007700">) {                     </span><span style="color: #0000BB">$diff_sql </span><span style="color: #007700">.= </span><span style="color: #DD0000">"/* old definition: {$fielddata['code']}    new definition:  {$otherdata['code']} */ "</span><span style="color: #007700">;                   }                   if(</span><span style="color: #0000BB">$fielddata</span><span style="color: #007700">[</span><span style="color: #DD0000">'type'</span><span style="color: #007700">]==</span><span style="color: #DD0000">'PRIMARY'</span><span style="color: #007700">) {                     </span><span style="color: #0000BB">$diff_sql </span><span style="color: #007700">.= </span><span style="color: #DD0000">"ALTER TABLE `$tablename` DROP PRIMARY KEY; "</span><span style="color: #007700">; </span><span style="color: #FF8000">//ok                   </span><span style="color: #007700">} else {                     </span><span style="color: #0000BB">$diff_sql </span><span style="color: #007700">.= </span><span style="color: #DD0000">"ALTER TABLE `$tablename` DROP INDEX $field; "</span><span style="color: #007700">;                   }                   </span><span style="color: #0000BB">$diff_sql </span><span style="color: #007700">.= </span><span style="color: #DD0000">"ALTER TABLE `$tablename` ADD $ind_name "</span><span style="color: #007700">.(</span><span style="color: #0000BB">$field</span><span style="color: #007700">?</span><span style="color: #DD0000">"`$field`"</span><span style="color: #007700">:</span><span style="color: #DD0000">""</span><span style="color: #007700">).</span><span style="color: #DD0000">" {$otherdata['code']}; "</span><span style="color: #007700">;             }           } </span><span style="color: #0000BB">?></span>
</span>
</code></span>
yspro
Junior Member
 
Posts: 19
Joined: Mon Mar 24, 2008 12:31 pm
Location: Belarus::Minsk

Re: Small fix for code

by gray » Mon Mar 24, 2008 1:31 pm

yspro wrote: In dbInfo.php class find method "getDiffWith" and replace the rows between "//drop, alter index" and "//drop, alter field":


This patch will ignore fields order in keys? Fields order in keys is important, if you want to speedup execution of some queries. For example multiple index key1(field1, field2) can act as single key index key2(field1), but can't as key3(field2)
gray
Member
 
Posts: 30
Joined: Thu Jan 10, 2008 7:50 pm