Synchronisation

In order to make a CRM in drupal that has a local and a remote version I had to try to synchronise the two.
I have tried to do that in three steps

Good to know is, that I made the synchronisation program in vb.net, my knowledge of PHP was to small to make it in PHP.
It will only work with mysql 5 because that has the table with meta-data (information_schema)
I don't know if mysql 4 has this but I have been told that it hasn't.

The program is dual layered. It has a data connection layer that handles the connection between the program and mysql.
I also have the layer that handles the normal data input. I could have made that the functions would be on a different layer.
In that way my program would be three layered. But it wasn't always as easy, so I created functions in my first layer.

While I was creating it I have learned allot. I have learned that I did it the wrong way, but that is also a valuable lesson!

I tried to synchronise the dumps that I took with my self-made program.

This was step 1 in my program.

• Making dumps of a remote and locale and backup database
  My program is able to take a dump of any mysql database without BLOB data types. For one very strange reason I can't read it out of the database.
  It seems that even phpmyadmin has problems with it, because it only works fine if you export to sql.
  So my program can't read it also, but it can take dumps and return it in list boxes.

Step 2

• comparing each database with the backup.
  If you are able to get dumps out of mysql you see a big problem here. It comes out as strings. Now I know that it might have worked if I used list views. If I had done that I would have been able to compare each field from every table. This is my first real program, so now I know that what I did wasn't the smartest thing to do.
  So more about the actual program
  In order to know if it might be an upgrade that I have to execute, I have to know the place of the primary key.(PK)
  To find the PK I had to search all of the strings if they have a CREATE TABLE statement. If they have then I have to search for more information about that table in the database "information_schema"
  This way I created an additional list box for the table and an additional list box for the PK
  Now that I have the different tables with PK's I could start to compare.
  In that way I could find that if the primary key is the same but something else is different it has to be an update statement.
  If the primary key isn't found in the newest table, it has to be a delete statement.
  If the primary key isn't found in the oldest table, it had to be an insert statement.
  You can see this wasn't an easy job if you have to search everything in a string with all different functions.
  Personally I think this is the reason why the program really can only handle one table at the time and not whole databases.

Step 3

• executing the different query's
  Working with three different dumps means that you have to update three also.
  So with every difference I encountered I had to find to which database the query should be executed.
  I encountered some problems with executing the queries also. Every time I had more then 2500 queries to be executed the program crashed because of an error with mysql.
  I created a loop for 3000 insert queries with random input and it was able to execute +/-2000 of them and then mysql stopped and crashed.

If any one can use a part of the program or wants to convert the program to PHP or something like that, feel free to do so.
I know that there is still allot of work at the program but now I don't have enough time anymore with school and after that job hunting.

Greetings
Cryztov

In attachment is the whole sln file.
(rightclick and choose save as)
I rarred it and called it synchjob.rar
but because of different rules I had to change the extension to .txt instead of .rar (so you will have to change it back in order for it to work)