[sqlite] Lemon Parser - Modular & Extensible ?
Hello: Is lemon parser modular and extensible? Thanks Uma Asif Lodhi <[EMAIL PROTECTED]> wrote: Hi Everybody, I have just joined this mailing list as Sqlite looks like a good software solution to my needs. What I need right now is RE-assurance of "crash-recovery" that is mentioned on your front page. So, I would be thankful if you experts would give me an "accurate" and fair picture of the crash-recovery aspects of SQLite - without any hype. -- Best regards, Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimization of equality comparison when NULL involved
> select tableA.path, tableA.value from tableA,tableB where > tableA.path=tableB.path and (tableA.value=tableB.value or > (tableA.value IS NULL AND tableB.value IS NULL)); > > It's possible that won't use an index, either, due to the OR, in which > case you could try a union between a select with is-null only, and > another with equality, something like: > > select tableA.path, tableA.value from tableA,tableB where > tableA.path=tableB.path and tableA.value=tableB.value union > select tableA.path, tableA.value from tableA,tableB where > tableA.path=tableB.path and tableA.value IS NULL AND tableB.value IS > NULL; Even though this query has no OR operation: select tableA.path, tableA.value from tableA,tableB where tableA.path=tableB.path and tableA.value=tableB.value it has to do a complete pass over one of the tables anyway since it does not have a constrained value (or at least a full pass over an index). So I think in this case the first query with the OR listed above is more efficient since it would do just a single pass over one of the tables instead of 2 passes (one for each SELECT in the UNION). I've been thinking of heuristics to rewrite SELECTs with ORs using UNIONs, which is pretty straightforward, but knowing when not to perform this optimization is the tricky part. Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Trigger update of multiple columns
Hi John, Thanks for your reply. You would have a better database if you normalize it and not do what you propose. Meaning what, exactly? Obviously, I've only given rough indications as to my data source, such as: the huge Products table (which is actually a UNION ALL of various supplier catalogs) without detailing where all the data in there comes from, since that would distract too much from my question. But suffice to say that "normalizing" beyond the current structure is not straight forward. In any case, at least in my ignorance of what you propose, it's beside the point. In essence, I'm asking: is it possible to update multiple columns in a row, where all those values come from a single related row, without SQLite having to find (eg via SELECT) that related row multiple times? Or, put another way, I want to get SQLite to: 1. Locate the related row. 2. Grab the desired columns from that row, putting each in the related row. I hope that clarifies. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Trigger update of multiple columns
You would have a better database if you normalize it and not do what you propose. T&B wrote: Hi All, I have a pretty standard sales tracking database consisting of tables: Products - Each row is a product available for sale. Includes fields: Code, Buy, Sell, Description Sales - Each row is a sale made to a customer. Includes fields: Ref, Customer Sale_Products - Each row is an product (many) included in a sale (one). Includes fields: Sale_Ref, Code, Buy, Sell, Description Now, when I add a new Sale_Products row and assign a product Code to it, I want to trigger it to auto enter the Buy and Sell prices, and the description, by looking up the related Product (ie where Sale_Products.Code = Products.Code) How can I do this? I have something like this: create trigger Update_Sale_Products_Code after update of Code on Sale_Products begin update Sale_Products set Buy = (select Buy from Products where Products.Code = new.Code) , Sell = (select Sell from Products where Products.Code = new.Code) , Description = (select Description from Products where Products.Code = new.Code) where rowid=new.rowid ; end It works, but it's unnecessarily slow, since it takes a while to look up the huge Products table (which is actually a UNION ALL of various supplier catalogs), and it's looking it up for each updating field (and I have more fields to lookup than shown in this example). It would be more efficient to look it up once to find the corresponding product (according to Products.Code = new.Code), but I'm stumped as to how to do that. I tried: create trigger Update_Sale_Products_Code after update of Code on Sale_Products begin update Sale_Products set Buy = (select Buy from Products) , Sell = (select Sell from Products) , Description = (select Description from Products) where rowid=new.rowid and Products.Code = new.Code ; end But that fails, and seems a bit ambiguous anyway. It seems to need some kind of JOIN, but I can't see provision for it in the UPDATE syntax. There must be a much simpler way that I'm overlooking. Please enlighten me. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How SQLite may work on AVR or similar mcu?
Dne pátek 15 červen 2007 13:57 Eduardo Morras napsal(a): > At 14:18 13/06/2007, you wrote: > >I do not need it actually, but i am so inquisitive. > > > >I have heard that sqlite has been ported to AVR too. > >How may be solved the main storage solved on it? > >So for example, i have system with ATmega64 with 128KB of external SRAM. > >The whole ram is organized as static 60KB and 18 pages of 4KB (the last > > 4KB page lays in the internal RAM shadow), but it can be simlply > > reconfigured because the memory address logic is provided by programmable > > logic device (CPLD) > > > >As the firm data storage memory is there 1MB spi dataflash, but there is a > >possibility to replace it with 16MB chip with the same interface for an > >example. > > > >I am at 230th page of Mike Owen's book The definitive Guide to > >SQLite and with > >every next page, my image of SQL on a chip w/o OS disappears in haze. > > > >Is i wrote before, i do not need it actually, i use sqlite in linux > >environment. > > > >Thank you for let you make me a picture. > > > >Jakub Ladman > > 128KB RAM is very low. I have run it on powerpc 440 device with Yes and that is the reason, why i am asking. Somebody wrote here at mailing list, that he has ported sqlite to some architectures and the given list contains AVR eight bit risc microcontroller. At the moment, when i understood how sqlite works, i have started to have doubts that's it even possible. Jakub Ladman > uclinux but with more ram (1GB). There is minimalist os that can be > fit on a few KB, check contiki and contiki mail-list > http://www.sics.se/contiki/ > https://lists.sourceforge.net/lists/listinfo/contiki-developers it's > used on commodore 64 and other relics. I used it before in a > development card (Virtex II Pro - xupv2p). You can cut down contiki > and sqlite features. The problem will be the stack/heap that sqlite > needs for run. I don't know about ATmega64 but can't you add a fpga > with dram controller? Perhaps using a free i/o port or changing the > cpld with the fpga? > > HTH > > -- > "General error, hit any user to continue." > > > --- >-- To unsubscribe, send email to [EMAIL PROTECTED] > --- >-- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Trigger update of multiple columns
Hi, I tried to update a list of columns: UPDATE t SET (c1, c2, c3) = (SELECT c1, c2, c3) FROM t2 WHERE .. but this syntax is not accepted as you probably already know. I may promote [INSERT OR] REPLACE then. It is syntactically described in the SQLite documentation but for the semantics you may see the original MySQL doc. http://dev.mysql.com/doc/refman/5.0/en/replace.html It is the only way that I see to do the update with only a single scan of the product table. But may be REPLACE causes troubles in combination with triggers. Because indirectly it performs a DELETE and a new INSERT. Other suggestions should be welcome. Regards, Ed Op 17-jun-2007, om 10:00 heeft T&B het volgende geschreven: Hi All, I have a pretty standard sales tracking database consisting of tables: Products - Each row is a product available for sale. Includes fields: Code, Buy, Sell, Description Sales - Each row is a sale made to a customer. Includes fields: Ref, Customer Sale_Products - Each row is an product (many) included in a sale (one). Includes fields: Sale_Ref, Code, Buy, Sell, Description Now, when I add a new Sale_Products row and assign a product Code to it, I want to trigger it to auto enter the Buy and Sell prices, and the description, by looking up the related Product (ie where Sale_Products.Code = Products.Code) How can I do this? I have something like this: create trigger Update_Sale_Products_Code after update of Code on Sale_Products begin update Sale_Products set Buy = (select Buy from Products where Products.Code = new.Code) , Sell = (select Sell from Products where Products.Code = new.Code) , Description = (select Description from Products where Products.Code = new.Code) where rowid=new.rowid ; end It works, but it's unnecessarily slow, since it takes a while to look up the huge Products table (which is actually a UNION ALL of various supplier catalogs), and it's looking it up for each updating field (and I have more fields to lookup than shown in this example). It would be more efficient to look it up once to find the corresponding product (according to Products.Code = new.Code), but I'm stumped as to how to do that. I tried: create trigger Update_Sale_Products_Code after update of Code on Sale_Products begin update Sale_Products set Buy = (select Buy from Products) , Sell = (select Sell from Products) , Description = (select Description from Products) where rowid=new.rowid and Products.Code = new.Code ; end But that fails, and seems a bit ambiguous anyway. It seems to need some kind of JOIN, but I can't see provision for it in the UPDATE syntax. There must be a much simpler way that I'm overlooking. Please enlighten me. Thanks, Tom -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recovery After Crash
Hi Kees, Thanks for replying. On 6/17/07, Kees Nuyt <[EMAIL PROTECTED]> wrote: >... thankful if you experts would give me an "accurate" and fair >picture of the crash-recovery aspects of SQLite - without any hype. I'm not sure if you would qualify this as hype, but sqlite is used in many end-user products, ranging from operating systems .. Basically, I intend to use sqlite's data capacity as well - I mean 2^41 bytes - for reasonably sized databases. Well, not as much as 2^41 but somewhere around 2^32 to 2^36 bytes. I would like to know if the "crash-recovery" feature will still work and the high-performance mentioned will be valid even if I have this kind of a data volume. And yes, I am talking about highly normalized database schemas with number of tables exceeding 80. Please reply assuming I tend to come up optimized db & query designs - keeping in view general rules for database/query optimizations. -- Thanks again and best regards, Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recovery After Crash
Hi Asif, On Sun, 17 Jun 2007 12:59:21 +0500, you wrote: >Hi Everybody, > >I have just joined this mailing list as Sqlite looks like a good >software solution to my needs. What I need right now is RE-assurance >of "crash-recovery" that is mentioned on your front page. So, I would >be thankful if you experts would give me an "accurate" and fair >picture of the crash-recovery aspects of SQLite - without any hype. I'm not sure if you would qualify this as hype, but sqlite is used in many end-user products, ranging from operating systems (OS X), webbrowsers (Firefox), embedded systems like PDA's, mp3 players etcetera. These are all enviroments where the users 1) aren't especially careful 2) aren't willing or able to handrecover a database. They even don't know it's there. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Trigger update of multiple columns
Hi All, I have a pretty standard sales tracking database consisting of tables: Products - Each row is a product available for sale. Includes fields: Code, Buy, Sell, Description Sales - Each row is a sale made to a customer. Includes fields: Ref, Customer Sale_Products - Each row is an product (many) included in a sale (one). Includes fields: Sale_Ref, Code, Buy, Sell, Description Now, when I add a new Sale_Products row and assign a product Code to it, I want to trigger it to auto enter the Buy and Sell prices, and the description, by looking up the related Product (ie where Sale_Products.Code = Products.Code) How can I do this? I have something like this: create trigger Update_Sale_Products_Code after update of Code on Sale_Products begin update Sale_Products set Buy = (select Buy from Products where Products.Code = new.Code) , Sell = (select Sell from Products where Products.Code = new.Code) , Description = (select Description from Products where Products.Code = new.Code) where rowid=new.rowid ; end It works, but it's unnecessarily slow, since it takes a while to look up the huge Products table (which is actually a UNION ALL of various supplier catalogs), and it's looking it up for each updating field (and I have more fields to lookup than shown in this example). It would be more efficient to look it up once to find the corresponding product (according to Products.Code = new.Code), but I'm stumped as to how to do that. I tried: create trigger Update_Sale_Products_Code after update of Code on Sale_Products begin update Sale_Products set Buy = (select Buy from Products) , Sell = (select Sell from Products) , Description = (select Description from Products) where rowid=new.rowid and Products.Code = new.Code ; end But that fails, and seems a bit ambiguous anyway. It seems to need some kind of JOIN, but I can't see provision for it in the UPDATE syntax. There must be a much simpler way that I'm overlooking. Please enlighten me. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Recovery After Crash
Hi Everybody, I have just joined this mailing list as Sqlite looks like a good software solution to my needs. What I need right now is RE-assurance of "crash-recovery" that is mentioned on your front page. So, I would be thankful if you experts would give me an "accurate" and fair picture of the crash-recovery aspects of SQLite - without any hype. -- Best regards, Asif - To unsubscribe, send email to [EMAIL PROTECTED] -