That sounds interesting. If you could execute a Java program on the client that will handle the update, and a server side servlet to process the request, it should be easy.
All I would do is add a version column (numeric or timestamp) to every table if you don’t already have one. Then, my client app would select the primary key and version for each table, possibly appending it to a csv file and then zipping it and sending it to the server. You could of course have a file per table if you don’t want to put it all in one file. On server side, I’ll extract the contents of the file and run a query against each table in the db, selecting all the content where the primary key is the same and the version number is not the same and writing the query into a text file. These would be your update statements. If your version column has the same name in all tables, you could use DatabaseMetaData to get each table’s columns and datatype. So, if you write this once, you could use it for all your projects and all databases, regardless of their structure. I’d run another query selecting all the data where the primary key is not is the list from the client. These would be your insert statements. Then run a query selecting all the primary keys from the client that are no longer in your db. These would be your delete statements. I’d add all these statements to the text file(s), zip it and send it back to the client. The client could then start a db transaction, apply the contents of the text file and commit if everything worked. You could have a graphical interface with a list of all the tables in the db (getting it via DatabaseMetaData). You can then select the ones to update and then run your updater application. Or call it from the command line with a list of tables to update, or, come to think of it, you could write the client and the server side as Java functions inside H2, calling the syncTables function on client side passing it the table names to update, or updating all tables if nothing was specified. The client function would then create the connection to the ‘server’ database and call the getSyncData function passing it the pk and version data per table. Of course, then you won’t have the zip/unzip functionality to minimize the network bandwidth used if you go the H2 function route. Anyway, just thinking out loud. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
