[ken]

'peterlaursen' wrote:

Please tell: is there anything new in this discussion that is not listed here: http://code.google.com/p/sqlyog/issues/detail?id=805

I would say “no”. I would welcome any non-zero amount of action on this.

On the bright side, I also own Database Comparer from EMS, and they have the same issue, but a prettier comparison GUI.

Still, I score it as a tie for last place.

Simply: If the “Compare tables without prefix” flag is set, shorten all the table names by taking the right substring of the tablename, rejecting the first “Prefix length” characters during the read of the database…

[ken]

Me too. This is very standard UI stuff. Filezilla handles it pretty well.

I am trying schema sync on dbs with different prefixes, and wanted to copy a connection, but had to create a new one- not a big deal, but doesn't make the Yog as fun as it could be.

[ken]

Really? I have another one of these, a Joomla 1.5 to 1.6 conversion, so I dumped the 1.5 db and search and replaced the prefix to match the 1.6 db. Now I know I can do Structure Sync.

But, really?, I can just ask for a structure sync of two databases with jos_ and abc_ as table prefixes? Let me try …

If there are any additional steps, please let me know…

I am looking, created the jos_ database on my new server…Is there no way to copy the existing connection?

OK, I can't make this work. It looks like all the aae_ tables are to be created in the _jos datababase, and vice versa, even where the same table name already exists, jos_zoo_tag and aae_zoo_tag.

What is this “There is no problem?”- I'd like to see it…

Thanks!

[ken]

As a first step, it would be nice to have the long-awaited feature to simply ignore the table prefix (first N characters) when doing syncs and compares. That would cover the case of migrating data from one Joomla instance to another, for example.

Thanks,

K

[ken]

I am doing another one of these. One database has jos_dos_ as prefix, the other drupal_

I am dumping an 18 MB SQL file, search replacing the prefix, creating a new database in order to try the sync. It's no fun.

K

[ken]

Thanks again.

Your ideas about transactions, rollback, etc. seem really good.

You could call this feature “Best Effort Append” 🙂

Since you can clone tables, etc., maybe there should be a Wizard, and the steps could be

• select target table for append
• to clone the target structure, optionally with our without data
• to run the insert
• report back some metrics
• open a new browser on the test table
• offer to run the BEA on the production table

I appreciate your considering this.

[ken]

Thanks. This seems like a matter of two text entry fields “Exclude_tablename_part”, one each for source and target, then an if statement wherever the tablenames are read, such that if Exclude_tablename_part is not null, then run a function to return the stripped tablename. Or maybe you would do them in batch, into two arrays. Anyway, it seems easy, not for me, but for a real coder.

Next, though, someone will come up with a case where both the prefix AND the suffix of the tablenames need to be stripped 🙂

Thanks for considering…

[ken]

This seems like another thread that falls into a category which might be paraphrased something like: “Make it more like Product X” (In this case, maybe MS Access). And Peter often says “Product X is not a standard”, which is kind of true, not an ISO or GATT standard, but anything used by millions of people becomes at least a source of expectations setting, if not a standard.

Anyway, I know that Peter et al. are now looking at my request to allow processing Append queries more like Access, which would be a big step forward, and toward my ability to get rid of Access (and Office altogether).

For Daniel, I sympathize, but I have an Ubuntu machine with a Vista VM, and a Netbook with XP on my desk, and I run some operations via PHPMA, some via Access, and some via Yog, whichever suits the purpose at hand.

I am saying that I can see everyone's points.

[ken]

I remember asking about this years ago, same answer…now at least I know why.

Thanks.

[ken]

'peterlaursen' wrote on '10:

” even though I had specified to omit the id field  ”   How did you do that and what function/tool did you use? Maybe a screenshot would  clarify?

I tried to add the data without the original PK by selecting all the fields EXCEPT the PK in the fields list of the Database Synchronization Wizard, by unselecting All (Fields) then manually checking every field in the list except the ID, which was the PK field.

'peterlaursen' wrote on '10:

The 'import external data tool' (ENTERPRISE/ULTIMATE) feature( has this option and no other tool in SQLyog  (not exports/backup, copy or datasync). With COMMUNITY you  may execute a SELECT statement, export to CSV from result tab and import using LOAD DATA syntax.

Oh, Thanks, Peter. I do have Enterprise, and will try this next time.

Best,

K

[ken]

Thanks, Peter!

[ken]

'peterlaursen' wrote on '23:

I am not sure if I understand.  Do you think there is an issue in SQLyog we should look into? If so please specify?

Thanks, Peter.

I think that I had two problems. The secondary one may have been my old ODBC driver for MySQL, but maybe that wasn't a factor at all.

The primary issue was that somehow, the Query objects from my Access db, which I believe is stock Northwind from Office XP, were getting picked up when I did “Import External Data”. I am new to this feature, so I was lazily selecting “Select All”, which included the Queries. When I got the message “Query Support Unavailable”, I didn't know whether that referred to MySQL (it was late). At that point, nothing was working.

The first thing I got working was a Where clause of 1=1, with the Select * from Products, which added records to the database, but in the table Results. So there was hope.

Next, I selected just the first 3 tables, which worked.

Then, I went back and selected the other 6 or so tables, which worked.

It turns out that the problem was the Access queries being checked/selected.

If there is a defect in SQLYog, perhaps it should not offer Query objects for import. Maybe this is a function of the Access version, I don't know.

I am attaching the database, if you would like to try to replicate–ooops, Error The server returned an error during upload. Anyway, you can easily find this file, probably.

Best,

Ken

[ken]

Good example, Peter, Thanks.

Just to keep focus on the objective, for those following along: Sometimes when doing a Schema Sync, there can be hundreds of lines generated, when all that is really being changed is the order of the fields. We are trying to make it easy to look for “real” (not just field order) schema changes.

My simple answer is that maybe when you find these cases, and the script generates the “after” or “first” lines:

1) Maybe you can highlight the word AFTER or FIRST, especially if that is the only thing that's being changed.

2) Maybe there could be a toggle to momentarily hide AFTER and FIRST lines from the script. In my case, that would reduce the generated script by over 90%, something like 775 lines to about 67.

I also imagine, in very simple terms, sorry, a report that parses the table structures to match on field names. Then an outer join from one to the other, which tells you which fields are to be created or deleted. Then, diff the remainder of the descriptor. Using your example,

We might end up with a table or array like: Table1Field, Table2Field, Table1Descriptor, Table2Descriptor

in which case for your example, there would be a row like: `the2nd`, INT(11) DEFAULT NULL, `the2nd`, TINYINT(11) DEFAULT NULL

which kind of takes the ordering out of the equation. Then you would run a query like : “Table2Descriptor <> Table1Descriptor” and the result might have the word TINYINT in RED or BOLD, or some other text variation. This would help isolate the REAL changes from the ordering only changes. Of course, you would do it in arrays or something, but I am thinking as if I could take the structures into a database, how could I use SQL to compare them?

Let me know if this clarifies, and your thoughts.

I just got an UltraEdit script, Delete Lines Containing Text, and was able to wipe all the after and first lines, and attached a little screen capture, where there were many such lines under accounts, there are only the two drops. So this process helps me assess any concerns in the proposed script.

Thanks,

Ken

[ken]

Thanks, Peter.

I put the generated script into my editor, just for practice, in this case it would probably have been quicker to scan visually, anyway, after doing a reverse search for the empty, ALTER and the FIRST and AFTER lines, the 775 line script is 67 lines of proposed DROPS that I can live with. It's much easier to see in that list, what is being proposed.

I do see that it would be very resource intensive to to the compare schemata excluding field ordering.

Thanks for the quick, thoughtful, and thorough reply.

Ken

[ken]

THANKS! Hopefully, it can go on your list for future implementation.

Have a great Holiday!

Best,

Ken

[Author]

Posts