I've spent the weekend refreshing on databases. I'm using LiveCode and also Trevor's fabulous sqlYoga, and beginning to realise how little I know about databases! The question I've got is about the database schema design and optimising it for the speed of adding records.
* Exporting Handlers* I think I may be missing some basic things, as it seems to me that the only easy way to force an update to be unique is to use primary keys. You can use "triggers" but this seems like this would be slower. However if I uses a surrogate primary key (ie the usual auto-incremented numeric id field), for each updated record I need to check first whether it is already in the database before adding it - this is a lot of overhead in terms of finding the ids based on other fields. However I ran in to problems using composite primary keys, as the queries got a too complicated for me to track down the bugs in them easily. To elaborate on the example - in LiveCode each and every handler in the LiveCode environment can be uniquely identified by the following fields: 1. handler_name 2. handler_type (ie one of "c,f,g,s" standing for "command", "function", "getprop", "setprop") 3. location (the "rugged id" of the LiveCode control - ie control id 1387 of card id 1002 of stack "Test") 4. hander_num (not usually useful - but there can be more than one identical handler in a script, only "1" is ever called) 5. handler_scope ("p" for "private" for example) >From these elements you can define a unique reference to a given handler in script with the following type of "key" - test_Command,c,stack "Test",1,p Which would refer to (the firsts instance of) a private command "test_Command" in script of the the stack "Test". I've been using this way to refer to handlers for years and have a large library of code that manipulates references to handlers, ie: - hkey_EditScript hKey - put hkey_GetHandler (hKey ) into somehandler - put hkey_ExtractHandlerCalls (hKey ) into hKey s Therefore in database terminology hKey is a composite primary key, and you can make this a composite primary key. This has the advantage of enforcing that addition of keys to the database is normalized - that is adding an hKey that already exists fails to add a duplicate entry. The problem I am having is that while using a composite primary key is great for making the updates fast and simple - it is giving me headaches with the more complex joins and queries, I'm building in LiveCode / sqlYoga. However I do need these updates to be as fast as possible - a few ticks for adding several hundred records, as I want to automatically add them to the database every time a script is compiled. These are the options I can think of: 1. Stick to composite primary keys for the handler table, and battle through all the bugs / issues with using complex joins based on composite keys. 2. Use "triggers" - and unknown to me, not tested with regard to speed, and looks like it may be difficult to keep sqlite and online db in sync, as they may not share the same way of implementing "triggers". 3. Use a "staging" table of some sort - use composite primary keys on this temporary table, and then at a later date (perhaps when the stack is saved) process this temporary table, moving the data into the db proper. 4. Asynchronously update the db - perhaps using a slave process, to maintain GUI responsiveness. 5. Something else? At the moment I am going for 3, as it seems to offer the best in terms of keeping the db design as conventional as possible, and speed in terms of GUI response. Any advice from the db experts out there? _______________________________________________ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution