On Tue, Jun 9, 2009 at 10:17 PM, Alexandre Courbot <gnu...@gmail.com> wrote: > > > I have now learned about the concept of virtual tables. Am I better > > of with my current approach because I can index the files in the temp > > table, or would I be better off using a virtual table to scan the hard > > drive for the images? > > Depends on how often you must reindex and how critical it is for you > to be up-to-date. A virtual table will ensure you do not have any > synchronization problem. On the other hand, if your temp table is > indexed, you may expect it to be faster. You do not tell much about > your join conditions.
Ok, here is what I am doing right now. The idea is to return all the images in the folder to the frontend and indicate which ones the current customer has in his/her favorite's. CREATE TEMP TABLE Files( ImageId CHAR(260) NOT NULL, DisplayName CHAR(260) NOT NULL, UNIQUE (ImageId)) INSERT INTO Files (ImageId, DisplayName) VALUES (?1, ?2) SELECT Files.ImageId, Favorite.Selected FROM Files LEFT OUTER JOIN Favorite ON Files.ImageId = Favorite.ImageId AND Favorite.FolderId = ?1 AND Favorite.CustomerId = ?2 ORDER BY Files.DisplayName, Files.ImageId The DisplayName is simply a matter of trimming off the front and/or end of the full filename. > > Also, since the only writing I am doing is to a temp table, can I > > connect to the SQLite database with read only? > > When I need to be such things, I just set my database file to be > read-only at OS level. :) Well, the database in general is read/write, such as when a customer logs in and out and when customers select images. Sam _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
