This is gonna be a long one, but I added (hopefully) some humor to make it a bit better of a read.
@OP Just so I'm clear, you're pushing SQL statements from clients to your server, but then pushing raw data from the server to the client? Myself, I'd never push a "Work In Progress" database, or a database that has ANY file handle open against it, to another machine. If you're cracking open the raw SQLite file and reading segments of raw data from it and expecting that data to exist on a client.. oh boy... You're in for a world of hurt. If you're set on sending the raw SQLite database, as suggested, I'd use the backup API for that database, then present the client with the backed up copy when then backup is complete. If, however, pushing 20gb over a traffic congested, 16Mbyte/sec *token ring*network for a one row update isn't your cup of tea, there are a few more options. Work the database so that when a client updates a particular database, a time stamp is placed on that row when the last update was done. A trigger would do the trick quite easily. The client would then make a request from the database to request all data starting from the date/time of last request. Client/Server time differences won't be an issue as you'd be reading the time from the rows and keeping tabs on the date in that new update field and not on the client system time. There are pros and cons to this; - Con: Deleted records would be perhaps a trick. You wouldn't want to delete any data as the clients would then never be aware of the delete. So this has your first serious impact on database size. You'd also have to add another field to each table to indicate the state of the row, if it should be deleted, or if it should be considered valid, but this will put another impact on database size. From there you can provide the client with proper inserts and proper deletes. Another option is to put the literal "delete" statements in another table, with appropriate server time stamp, and when the client makes a request for an update, return the results ordered on this time stamp. - Pro: UNDELETE becomes possible with the first CON added. - Con: If CASCADE deletes are part of the schema, and you rely on deleting one row to remove several other rows from other tables, it'll be serious SERIOUS fun to work through that. If the first CON is done, I suppose a trigger could be done to flag the state row and just not allow the delete to happen. Then, when you actually want to do the database cleanup, you temporarily turn off the triggers, do your thing, then turn them back on. - Con: No guarentee that the returned SQL code would be in order of execution to make things valid on the client side. Ordering by the time stamp, even if the millisecond is considered, if two fast inserts happen but need to happen in a certain order, things could get messed up for the client. The second option is to open a socket connection, not for raw data transmissions, but for sending SQL code from server to client. On "First contact" from a client, two things happen. First, a socket is opened up to retrieve the raw database your backup API took care of, but no data transfers happen until a second socket is opened up to listen for SQL code. With the two sockets open, you start downloading the raw data on the first socket, while caching SQL code on the second. Once the first socket is done, you attach a database connection to that data, then start pushing SQL code to the database. Again, pros and cons; - Con: You're still dealing with the *token ring* network on first load, which is going to cause some grief - Pro: The client only needs to deal with the second socket. It only ever has to listen for commands. - Con: You're still dealing with the *token ring* network........ .. hell, that fact doesn't even apply to SQLite.. WHY TOKEN RING MAN!?!?! - Con: A major revamp of code (Not database) will have to happen on both the server and client to get the backup and streaming sockets to work and have the clients aware of what is happening. You're already using sockets, so you have the knowledge to get somewhere with this. - Con: The server is not only going to have to handle the SQL code on itself, but, it'll also have to hand the code over to 'X' number of clients. - Con: Establishing which client gets what database might be a bit hard to do unless you start doing some kind of authentication, which would mean that the entire database gets put over to the client anyways - Pro: You'd be using MY IDEA and I wanna see it work when you get it done like that. ;) - Con: You'll have to pay for the plane ticket A third option would be to treat the data similar to how other major SQL servers handle the data. You could create an ODBC driver, or build your own internal protocols that have the client push information to the server, then the server does the work against that query, then pushes the results back in a format understood by the client. - Pro: No "database" on the client side. - Con: If the client is already expecting a database, a major overhaul is going to be needed to be done. - Pro: Depending on the volume of data, the* token ring* network bottle neck only happens when TWO people make a request, not just one. ;) - Con: The server code would probably have to be converted into a multi-threaded application to handle several requests at one, and SQLite isn't all that great with multi-threading (Not many applications are, and here we are with multi-core desktop work stations for several years now). Maybe I shouldn't say "isn't all that great" and gear it towards more like "You better know what you're doing". A fourth option would be to take the third suggestion, but remove SQLite from the picture and just use a major DBMS as your server. Use SQLite on the client for faster data access and periodically hit the DBMS for updates, etc. ALL major database engines, however, come with a real world price that'll hit the bank. MySQL may say "free" on the package, but you need a license for particular uses. I don't know the legal talk or the terms and conditions, but where I work, when we install it on computers we ship to customers sites, we have to pay for a license for that computer for some reason. I also don't know if we pay on a per-application purpose (Meaning that we can have many different 'databases' on the machine, but the one application package the servers run is what is being licensed to all those databases) or if its per CPU or if its per motherboard, or if its per site, or what. - Pro: You no longer have to worry about server side code base - Con: It'll cost you money, use case depending - Pro: Not re-inventing the wheel. Please note that I've NEVER done ANY of the above, but I've let those ideas rattle in my head for a very long time, and have considered impact, code, and other things, and I'm 1,000% positive I've missed important facets of each idea that'll hinder or help you along. I have often wanted to build a SQLite database server, but with MySQL and the purposes I use it for, why bother? Back in the 386 days, I wanted to build my own database engine before I even knew the "Structured Query Language" even existed, and to be honest, I don't know if SQL did exist in the 386 days. But when I found out how to use SQL, the idea of building my own database went out the door. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
