The front-end could be any client that can issue a RESTful request and POST parameters to the server. Mine happens to be written in Adobe FlashBuilder ( née FlexBuilder). I wrote the webservice in ASP.NET using Robert Simpson's System.Data.SQLite ADO.NET provider against SQLite3.
The main challenge relating to the IN-list question I had (i.e. what is max # of items in an IN-list) is that the user on the front-end is presented with a grid analogous to this: Zip| Type (urban, suburban, rural) | City| State | Population |AverageIncome | AverageIQ | NumberOfFishingPonds My application has nothing to do with fishing, it is lexicographical in nature, but the structure of the problem is analogous: the user may see up to a thousand zip codes in a grid, which can be sorted in any number of ways and which offers the user the ability to check which zip codes for which they want to see greater detail. They might choose everything in OKLAHOMA. Or all rural ZIPS. There is also a SelectAll button which selects the whole kit and kaboodle. This list of selected zip codes gets POSTED to the webserver in one fell swoop. Rather than make a dozen or a hundred round-trips to the webserver, passing one or a few zipcodes at a time, because that would have significant latency through the cloud, I am passing the entire list of desired zip-codes, and getting a single freight train of data in response (about 200K of data which isn't too bad over broadband). The zip-list is simply plugged into an IN-list : select columns from foo where zip in ( 10024, 89445, etc .... ) I could inject those zip values into a TEMP table and rewrite my query as an equijoin, but I don't see why SQLite wouldn't do that transparently "behind the scenes", in any case. What would prevent such an internal optimization of the query? I understand Jay's point about avoiding the construction of SQL statement strings, but I don't consider that a hard-and-fast rule; it's simply a desideratum. It's main value, IMO, is for inserts where you don't want to recompile the same insert statement again and again and again and would use a parameter to avoid that problem. Regards Tim Romano On 1/27/2010 11:30 AM, Simon Slavin wrote: > > mm. A couple of things worth considering: first that JavaScript under HTML5 > has its own access to SQL commands. If this system is for use only inside an > organisation, and you can say everyone must use a modern browser, then you > can use the HTML5 tools which automatically ensure keep the databases local > (in fact, on the client's hard disk, not the server !). By the way, all the > browsers I've seen that support this actually use sqlite3 internally. > > However, you might be planning to do this on the server using PHP. And PHP > has more than one SQLite library and you should be sure you're using one that > uses sqlite3, not the original sqlite library. > > So part of your design decision is whether some of the presentation work can > be done in JavaScript on the client. > > >> 2c) issues query to disk-database to fetch random hex value to >> ensure temp table is named uniquely >> > You don't need this. If you're using a TEMP table, or keeping the table in > :memory:, then you can call it whatever you want: only the single connection > you're using right now can see it, and it will vanish as soon as Apache (or > whatever) has finished serving that particular web page. > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
