Re: [sqlite] Re: Adding columns of records and updating the result to a record
When you design a database do not mix record (row) structures in a single table (variant records). That used to be done with ISAM files but is no longer necessary with the advent of the RDBMS. Also you should design your database to have "Third Normal Form" which means that each data item is only stored in one place. Altering that item in its one location means that it is altered system-wide. In your case of an owner record linking to a chain of transactions or detail set you do not need to keep a summary in the owner record, just make it the entry point to the chain of transactions. You have one table for the owner rows and another for the transactions. If you avoid have summaries you simplify the logic of your application. Insertions and deletions become trivial. You do have to scan the full detail record set to get totals however. Another tidy design rule is to partition reference (static) and transactional (dynamic) data. It can make workflow logic simpler and more robust. jose isaias cabrera wrote: "Igor Tandetnik" wrote, jose isaias cabrera <[EMAIL PROTECTED]> wrote: I have a system that links two or more records to one head record using a column called ProjID, where ProjID has the unique recNO of the head record. Anyway, what I would like to do is something like this... Imagine, recNo,ProjID,Invoice,Fund 1,1,, 2,1,10.00,30.00 3,1,20.00,60.00 4,1,100.00,600.00 5,5,, 6,5,8.33,20.00 7,5,1.00,5.00 ... ... recNo is unique. What I would like to do is to keep the head record (1 and 5, on this instance) updated as the other children values get updated. Updated means, the Invoice sum of all the child records on the Invoice column of the head record, and the same for the Fund column. This is a very bad design. Relational model doesn't easily incorporate this notion of "special" records. All records in a table should be uniform. First of all, thank you for your help. I am new to DBs. So, I am learning by myself, just using old homegrown logic, but I want to learn and I thank you for your time. Now, how do you suggest a relational system with records? I could also have another table with parents. The problem is that I need to have these head records because they guide my tool. I suggest removing these "head" records from the table altogether. You I don't get it. What is so bad about it? can always calculate them whenever necessary, like this: select ProjId, sum(Invoice), sum(Fund) from tableName group by ProjId; You can make this query into a view if you are so inclined. Thanks. If, for some reason, you insist on storing these totals in the database persistently, create a separate table containing just those totals records. Use triggers to update these totals whenever something changes in the main table. Again, do not put totals records in the main table. Ok, I could do this. However, I am still intrigued why this is so bad. Or is it bad because the head and the children are together? - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
Hi Ken, you can get the exact insert speed of the flatfile.dat: - dump your data into the flat file - create a virtual table implementation for your flat file http://www.sqlite.org/cvstrac/wiki?p=VirtualTables&1150734307 - and use it from SQLite http://www.sqlite.org/lang_createvtab.html Michael I'm looking for suggestions on improving performance of my sqlite application. Here are system timings for a run where the sqlite db has been replaced with a flat file output. real 0m1.459s user0m0.276s sys 0m0.252s This is a run when using sqlite as the output format. real 0m3.095s user0m1.956s sys 0m0.160s As you can see sqlite takes twice as long and almost 8 times the user time. Output size for flat file: 13, 360, 504flatfile.dat Output size fo sqlit file: 11,042,816 sqlt.db f Slite db has the following pragmas set. PRAGMA default_synchronous=FULL PRAGMA temp_store=memory PRAGMA page_size=4096 PRAGMA cache_size=2000 Any ideas how to get the sqlite output timings to a more respectable level would be appreciated. Thanks Ken -- http://MichaelScharf.blogspot.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Adding columns of records and updating the result to a record
"P Kishor" wrote, On 3/15/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: "P Kishor" wrote, .. > Any basic tutorial in normalization and table design > will help you tremendously in figuring an efficient solution to your > problem. Any suggestions on the web? http://www.google.com/search?q=normalization http://www.google.com/search?q=relational+database good luck. Very nice. Muchas gracias. josé - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Adding columns of records and updating the result to a record
On 3/15/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: "P Kishor" wrote, .. > Any basic tutorial in normalization and table design > will help you tremendously in figuring an efficient solution to your > problem. Any suggestions on the web? http://www.google.com/search?q=normalization http://www.google.com/search?q=relational+database good luck. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Adding columns of records and updating the result to a record
"P Kishor" wrote, José, As Igor rightly points out, you are doing the "wrong" thing with this tool. A perfect database program, and SQLite approaches that in its simplicity, acts as simply an ultra-efficient device to store and retrieve your data. Everything else is up to you... like a perfect audio speaker that neither adds nor subtracts from the music, SQLite neither adds nor subtracts from the data. You are trying to implement a "spreadsheet" in a database. A database deals with "sets" where no member of the set knows anything about any other member of that set. In fact, the set doesn't even have the concept of SORT ORDER or the number of elements in the set. An element doesn't know what is behind it or ahead of it. Figuring that out is up to you. A spreadsheet, on the other hand, is very much aware of the "location" of its elements. You can refer to them via ROW::COL combination. Well, you can always query your database, put them in an array of arrays or array of hashes (or whatever data structure you fancy) and then you have a scheme where elements are "aware" of each other. Perhaps you didn't want this "advice" and you simply wanted to implement this in the db itself (but, heck, advice is free). Well, if Oh no, my friend. I welcome this advice, and any other good advice, very much. My father always says, "el que se lleva de consejo, muere de viejo," which loosely translates to, "he who listens to advice, dies of old age." So, I do welcome. Like I said to Igor, I am new to DB. So, what I did was thinking out of my very short wisdom. So... you want to do this _in_ the database, aggregate functions such as Count(), Sum() and SQL clauses such as GROUP BY and HAVING can give you what you want, but you certainly don't want those values in your table. A table should have only the unique attributes of a single element type. Any basic tutorial in normalization and table design will help you tremendously in figuring an efficient solution to your problem. Any suggestions on the web? ;-) Regards, On 3/15/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: Greetings! I have a system that links two or more records to one head record using a column called ProjID, where ProjID has the unique recNO of the head record. Anyway, what I would like to do is something like this... Imagine, recNo,ProjID,Invoice,Fund 1,1,, 2,1,10.00,30.00 3,1,20.00,60.00 4,1,100.00,600.00 5,5,, 6,5,8.33,20.00 7,5,1.00,5.00 ... ... recNo is unique. What I would like to do is to keep the head record (1 and 5, on this instance) updated as the other children values get updated. Updated means, the Invoice sum of all the child records on the Invoice column of the head record, and the same for the Fund column. This means that for head record 1, the database, after an update, should be, recNo,ProjID,Invoice,Fund 1,1,130.00,690.00 2,1,10.00,30.00 3,1,20.00,60.00 4,1,100.00,600.00 ... ... I know how to do this programmatically outside SQLite, but is there a an UPDATE command that I could use to update the Invoice or Fund column on the head record when on any of the children's mentioned column change? I have a lousy idea, which is to set the head record Invoice and Fund column to 0, and then run an add through all the records which have ProjID='1', for the first example, and after that assign the total to the ProjID record. But, is there an easier and more sophisticated way? It's probably something easy, but I am not THAT good yet, on SQL. Any help would be greatly appreciated. thanks, josé -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Adding columns of records and updating the result to a record
"Igor Tandetnik" wrote, jose isaias cabrera <[EMAIL PROTECTED]> wrote: I have a system that links two or more records to one head record using a column called ProjID, where ProjID has the unique recNO of the head record. Anyway, what I would like to do is something like this... Imagine, recNo,ProjID,Invoice,Fund 1,1,, 2,1,10.00,30.00 3,1,20.00,60.00 4,1,100.00,600.00 5,5,, 6,5,8.33,20.00 7,5,1.00,5.00 ... ... recNo is unique. What I would like to do is to keep the head record (1 and 5, on this instance) updated as the other children values get updated. Updated means, the Invoice sum of all the child records on the Invoice column of the head record, and the same for the Fund column. This is a very bad design. Relational model doesn't easily incorporate this notion of "special" records. All records in a table should be uniform. First of all, thank you for your help. I am new to DBs. So, I am learning by myself, just using old homegrown logic, but I want to learn and I thank you for your time. Now, how do you suggest a relational system with records? I could also have another table with parents. The problem is that I need to have these head records because they guide my tool. I suggest removing these "head" records from the table altogether. You I don't get it. What is so bad about it? can always calculate them whenever necessary, like this: select ProjId, sum(Invoice), sum(Fund) from tableName group by ProjId; You can make this query into a view if you are so inclined. Thanks. If, for some reason, you insist on storing these totals in the database persistently, create a separate table containing just those totals records. Use triggers to update these totals whenever something changes in the main table. Again, do not put totals records in the main table. Ok, I could do this. However, I am still intrigued why this is so bad. Or is it bad because the head and the children are together? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Adding columns of records and updating the result to a record
José, As Igor rightly points out, you are doing the "wrong" thing with this tool. A perfect database program, and SQLite approaches that in its simplicity, acts as simply an ultra-efficient device to store and retrieve your data. Everything else is up to you... like a perfect audio speaker that neither adds nor subtracts from the music, SQLite neither adds nor subtracts from the data. You are trying to implement a "spreadsheet" in a database. A database deals with "sets" where no member of the set knows anything about any other member of that set. In fact, the set doesn't even have the concept of SORT ORDER or the number of elements in the set. An element doesn't know what is behind it or ahead of it. Figuring that out is up to you. A spreadsheet, on the other hand, is very much aware of the "location" of its elements. You can refer to them via ROW::COL combination. Well, you can always query your database, put them in an array of arrays or array of hashes (or whatever data structure you fancy) and then you have a scheme where elements are "aware" of each other. Perhaps you didn't want this "advice" and you simply wanted to implement this in the db itself (but, heck, advice is free). Well, if you want to do this _in_ the database, aggregate functions such as Count(), Sum() and SQL clauses such as GROUP BY and HAVING can give you what you want, but you certainly don't want those values in your table. A table should have only the unique attributes of a single element type. Any basic tutorial in normalization and table design will help you tremendously in figuring an efficient solution to your problem. ;-) Regards, On 3/15/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: Greetings! I have a system that links two or more records to one head record using a column called ProjID, where ProjID has the unique recNO of the head record. Anyway, what I would like to do is something like this... Imagine, recNo,ProjID,Invoice,Fund 1,1,, 2,1,10.00,30.00 3,1,20.00,60.00 4,1,100.00,600.00 5,5,, 6,5,8.33,20.00 7,5,1.00,5.00 ... ... recNo is unique. What I would like to do is to keep the head record (1 and 5, on this instance) updated as the other children values get updated. Updated means, the Invoice sum of all the child records on the Invoice column of the head record, and the same for the Fund column. This means that for head record 1, the database, after an update, should be, recNo,ProjID,Invoice,Fund 1,1,130.00,690.00 2,1,10.00,30.00 3,1,20.00,60.00 4,1,100.00,600.00 ... ... I know how to do this programmatically outside SQLite, but is there a an UPDATE command that I could use to update the Invoice or Fund column on the head record when on any of the children's mentioned column change? I have a lousy idea, which is to set the head record Invoice and Fund column to 0, and then run an add through all the records which have ProjID='1', for the first example, and after that assign the total to the ProjID record. But, is there an easier and more sophisticated way? It's probably something easy, but I am not THAT good yet, on SQL. Any help would be greatly appreciated. thanks, josé -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite samples or templates, including PHP
On 3/15/07, T <[EMAIL PROTECTED]> wrote: Hi All, Following in a similar thread to Shilpa's request: > Are there database schema's (eg. for Phonebook ) available on the > net? Are there any templates out there for SQLite databases? In particular, I'm after SQLite and PHP integrated solutions, such as perhaps a shopping cart, or product catalog. Tom, Both those questions are likely to go unanswered on this list as these questions deviate way too much from its SQLite focus. Schemas and integrated solutions are going to vary hugely based on requirements. You just might get more mileage from a Google query. http://www.google.com/search?q=sqlite+php+shopping+cart http://www.google.com/search?q=sqlite+phonebook After all, Google is supposed to answer precisely whether or not "is available on the net." Regards, -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite samples or templates, including PHP
Hi All, Following in a similar thread to Shilpa's request: Are there database schema's (eg. for Phonebook ) available on the net? Are there any templates out there for SQLite databases? In particular, I'm after SQLite and PHP integrated solutions, such as perhaps a shopping cart, or product catalog. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] database is locked error with 3.3.13
I'm experiencing the same problem on QNX compiled for Renesas SH4... > This is not a bug in SQLite - it is a bug in Apple's implementation >(or more precisely their lack of implementation) of POSIX advisory > locks for AppleShare mounted volumes. The SQLite sources include > an (Apple-supplied) patch to work around the problem. Recompile > with > >-DSQLITE_ENABLE_LOCKING_STYLE=1 > > We are working toward turning on this patch by default, but we are not > quite there yet. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Adding columns of records and updating the result to a record
jose isaias cabrera <[EMAIL PROTECTED]> wrote: I have a system that links two or more records to one head record using a column called ProjID, where ProjID has the unique recNO of the head record. Anyway, what I would like to do is something like this... Imagine, recNo,ProjID,Invoice,Fund 1,1,, 2,1,10.00,30.00 3,1,20.00,60.00 4,1,100.00,600.00 5,5,, 6,5,8.33,20.00 7,5,1.00,5.00 ... ... recNo is unique. What I would like to do is to keep the head record (1 and 5, on this instance) updated as the other children values get updated. Updated means, the Invoice sum of all the child records on the Invoice column of the head record, and the same for the Fund column. This is a very bad design. Relational model doesn't easily incorporate this notion of "special" records. All records in a table should be uniform. I suggest removing these "head" records from the table altogether. You can always calculate them whenever necessary, like this: select ProjId, sum(Invoice), sum(Fund) from tableName group by ProjId; You can make this query into a view if you are so inclined. If, for some reason, you insist on storing these totals in the database persistently, create a separate table containing just those totals records. Use triggers to update these totals whenever something changes in the main table. Again, do not put totals records in the main table. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Adding columns of records and updating the result to a record
Greetings! I have a system that links two or more records to one head record using a column called ProjID, where ProjID has the unique recNO of the head record. Anyway, what I would like to do is something like this... Imagine, recNo,ProjID,Invoice,Fund 1,1,, 2,1,10.00,30.00 3,1,20.00,60.00 4,1,100.00,600.00 5,5,, 6,5,8.33,20.00 7,5,1.00,5.00 ... ... recNo is unique. What I would like to do is to keep the head record (1 and 5, on this instance) updated as the other children values get updated. Updated means, the Invoice sum of all the child records on the Invoice column of the head record, and the same for the Fund column. This means that for head record 1, the database, after an update, should be, recNo,ProjID,Invoice,Fund 1,1,130.00,690.00 2,1,10.00,30.00 3,1,20.00,60.00 4,1,100.00,600.00 ... ... I know how to do this programmatically outside SQLite, but is there a an UPDATE command that I could use to update the Invoice or Fund column on the head record when on any of the children's mentioned column change? I have a lousy idea, which is to set the head record Invoice and Fund column to 0, and then run an add through all the records which have ProjID='1', for the first example, and after that assign the total to the ProjID record. But, is there an easier and more sophisticated way? It's probably something easy, but I am not THAT good yet, on SQL. Any help would be greatly appreciated. thanks, josé - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
Ken <[EMAIL PROTECTED]> wrote: > > When the DB is closed when in synchrounous mode, > is it then persistent at the OS level even from power failures etc? > You don't have to close the DB. All you have to do is commit. Before the commit finishes, all of your data is guaranteed to be on oxide.** **Note: this assumes that the fsync() system call (or its equivalent on windows) really works as advertised. In practice, I am told, this assumption is false, because almost every disk controller lies and says that it has committed a sector to oxide before it really has. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
DRH, Thanks for your valuable insite. When the DB is closed when in synchrounous mode, is it then persistent at the OS level even from power failures etc? [EMAIL PROTECTED] wrote: Ken wrote: > > I should be able to run with synchronous=off. Since > the application maintains state in a seperate DB elsewhere. > Just to clarify the implications where, if you run with synchronous=off and you take a power failure or an OS crash in the middle of a transaction, then you run a serious risk of ending up with a corruption database file. However, (key point->) an application crash will not hurt the database. Since you have an alternate source of authoritative data and since power failures and OS crashes are reasonably infrequent, your decision to run with synchronous=off may well be the right choice. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to set pragma page_size
Thanks DRH... That worked. Ken [EMAIL PROTECTED] wrote: Ken wrote: > How does one set the page_size ? > > according to the documentation > "The page-size may only be set if the database has not yet been created. > " > > So how do you execute the pragma prior to creating the DB? without calling > sqlite3_open to get a DB handle that is needed to call prepare/step ? > The database file is not actually created until you issue a "CREATE TABLE" statement. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
Tito, Its even better now! Synchronous=normal and No primary keys (except 1 table) for auto increment. real0m1.975s user0m1.436s sys 0m0.140s Vs flat file test case: real0m0.862s user0m0.228s sys 0m0.188s This is now very respectable. Thanks, Ken Ken <[EMAIL PROTECTED]> wrote: Tito, There are no indices built besides the default ones. Hmm maybe I should try this by dropping the primary Keys.. I'll give that a try as well, GOOD idea! The entire batch of inserts (about 8 tables) is done in a single transaction. As an Oracle DBA, I'm pretty familar with tuning. This was definately an I/O issue and related to the code path vs say a select where the optimizer picked an incorrect plan. Regards, Ken Tito Ciuro wrote: Hello, IIRC (it was a while ago), one way to speed up insertion for large data sets is to drop the indexes, do the inserts (wrapped around a transaction) and then rebuild the indexes. For smaller data sets, the drop/rebuild indexes solution doesn't make sense because the time it takes to do that invalidates the performance gain. However, larger data sets seemed to benefit greatly. Again... that was a while ago... :-) I should dust-off my test app and see what the results are with the latest sources. I'll let you know what I find out. -- Tito On Mar 15, 2007, at 11:42 AM, John Stanton wrote: > There are no free lunches. When Sqlite stores your data item it > not only writes it into a linked list of pages in a file but also > inserts at least on key into a B-Tree index. It does it quite > efficiently so what you are seeing is the inevitable overhead of > storing the data in a structured form. The value of the structure > becomes obvious when you are retrieving a single item from a set of > millions and the index allows you to access it in a tiny fraction > of the time it would take to search an unstructured list like a > flat file. > > The ACID implementation in Sqlite provides data security but is > does involve a significant overhead. You pay a price for not > losing data in a system crash. > > Like all things in life "you pays your money and you takes your > choice". It is somewhat simpler with Sqlite in that you don't pay > your money, you just take your choice. > > If you want faster Sqlite performance use faster disks. The > latency is important so 15,000 rpm disks will be better than 5,400 > rpm ones. > > Ken wrote: >> To answer your question: Yes I can use a flat file at this stage, >> but eventually it needs to be imported into some type of >> structure. So to that end I decided early on to use sqlite to >> write the data out. I was hoping for better performance. The raw >> I/O to read the data and process is around .75 seconds (no write i/ >> o).. So using a flat file output costs about .7 seconds. >> Using sqlite to do the output costs about 2.25 seconds. My >> question is why? And what can be done to improve this >> performance? John Stanton wrote: Ken wrote: >>> I'm looking for suggestions on improving performance of my sqlite >>> application. >>> Here are system timings for a run where the sqlite db has been >>> replaced with a flat file output. >>> real 0m1.459s >>> user0m0.276s >>> sys 0m0.252s >>> This is a run when using sqlite as the output format. >>> real 0m3.095s >>> user0m1.956s >>> sys 0m0.160s >>> As you can see sqlite takes twice as long and almost 8 times the >>> user time. >>> Output size for flat file: 13, 360, 504flatfile.dat >>> Output size fo sqlit file: 11,042,816 sqlt.db f >>> Slite db has the following pragmas set. >>> PRAGMA default_synchronous=FULL >>> PRAGMA temp_store=memory >>> PRAGMA page_size=4096 >>> PRAGMA cache_size=2000 >>> Any ideas how to get the sqlite output timings to a more >>> respectable level would be appreciated. >>> Thanks >>> Ken >> If you want flat file performance, use a flat file. Sqlite is >> built on top of a flat file and cannot be faster or even as fast. >> If your application can use a flat file, why use anything more >> complex? >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to set pragma page_size
Ken <[EMAIL PROTECTED]> wrote: > How does one set the page_size ? > > according to the documentation > "The page-size may only be set if the database has not yet been created. > " > > So how do you execute the pragma prior to creating the DB? without calling > sqlite3_open to get a DB handle that is needed to call prepare/step ? > The database file is not actually created until you issue a "CREATE TABLE" statement. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite Performance
"Griggs, Donald" wrote on 03/15/2007 01:49:30 PM: > Regarding: > Creation of flat file takes 1.5 secs vs 3 seconds to create sqlite db. > Flat file is 13 MB, sqlite db is 11 MB. > > "Any ideas how to get the sqlite output timings to a more respectable > level would be appreciated. " > I think you may be looking at this as a one dimensional problem, or looking at it from the wrong angle. [I am using upper case here for mild emphasis - not yelling]. Really, there is a greater difference in gains at the other end. In other words, there IS a certain amount of overhead, BUT, what you get back from sqlite over a flat file is: The ability to use SQL, where a flat file is flat and brain dead. The ability to create indexes to help find specific data, where your other choices may require you to write code (which equals time which equals labor and money). Finally, businesses often throw hardware at performance, when tuning has already been tried. Good Luck. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
Ken <[EMAIL PROTECTED]> wrote: > > I should be able to run with synchronous=off. Since > the application maintains state in a seperate DB elsewhere. > Just to clarify the implications where, if you run with synchronous=off and you take a power failure or an OS crash in the middle of a transaction, then you run a serious risk of ending up with a corruption database file. However, (key point->) an application crash will not hurt the database. Since you have an alternate source of authoritative data and since power failures and OS crashes are reasonably infrequent, your decision to run with synchronous=off may well be the right choice. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
Tito, There are no indices built besides the default ones. Hmm maybe I should try this by dropping the primary Keys.. I'll give that a try as well, GOOD idea! The entire batch of inserts (about 8 tables) is done in a single transaction. As an Oracle DBA, I'm pretty familar with tuning. This was definately an I/O issue and related to the code path vs say a select where the optimizer picked an incorrect plan. Regards, Ken Tito Ciuro <[EMAIL PROTECTED]> wrote: Hello, IIRC (it was a while ago), one way to speed up insertion for large data sets is to drop the indexes, do the inserts (wrapped around a transaction) and then rebuild the indexes. For smaller data sets, the drop/rebuild indexes solution doesn't make sense because the time it takes to do that invalidates the performance gain. However, larger data sets seemed to benefit greatly. Again... that was a while ago... :-) I should dust-off my test app and see what the results are with the latest sources. I'll let you know what I find out. -- Tito On Mar 15, 2007, at 11:42 AM, John Stanton wrote: > There are no free lunches. When Sqlite stores your data item it > not only writes it into a linked list of pages in a file but also > inserts at least on key into a B-Tree index. It does it quite > efficiently so what you are seeing is the inevitable overhead of > storing the data in a structured form. The value of the structure > becomes obvious when you are retrieving a single item from a set of > millions and the index allows you to access it in a tiny fraction > of the time it would take to search an unstructured list like a > flat file. > > The ACID implementation in Sqlite provides data security but is > does involve a significant overhead. You pay a price for not > losing data in a system crash. > > Like all things in life "you pays your money and you takes your > choice". It is somewhat simpler with Sqlite in that you don't pay > your money, you just take your choice. > > If you want faster Sqlite performance use faster disks. The > latency is important so 15,000 rpm disks will be better than 5,400 > rpm ones. > > Ken wrote: >> To answer your question: Yes I can use a flat file at this stage, >> but eventually it needs to be imported into some type of >> structure. So to that end I decided early on to use sqlite to >> write the data out. I was hoping for better performance. The raw >> I/O to read the data and process is around .75 seconds (no write i/ >> o).. So using a flat file output costs about .7 seconds. >> Using sqlite to do the output costs about 2.25 seconds. My >> question is why? And what can be done to improve this >> performance? John Stanton wrote: Ken wrote: >>> I'm looking for suggestions on improving performance of my sqlite >>> application. >>> Here are system timings for a run where the sqlite db has been >>> replaced with a flat file output. >>> real 0m1.459s >>> user0m0.276s >>> sys 0m0.252s >>> This is a run when using sqlite as the output format. >>> real 0m3.095s >>> user0m1.956s >>> sys 0m0.160s >>> As you can see sqlite takes twice as long and almost 8 times the >>> user time. >>> Output size for flat file: 13, 360, 504flatfile.dat >>> Output size fo sqlit file: 11,042,816 sqlt.db f >>> Slite db has the following pragmas set. >>> PRAGMA default_synchronous=FULL >>> PRAGMA temp_store=memory >>> PRAGMA page_size=4096 >>> PRAGMA cache_size=2000 >>> Any ideas how to get the sqlite output timings to a more >>> respectable level would be appreciated. >>> Thanks >>> Ken >> If you want flat file performance, use a flat file. Sqlite is >> built on top of a flat file and cannot be faster or even as fast. >> If your application can use a flat file, why use anything more >> complex? >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to set pragma page_size
How does one set the page_size ? according to the documentation "The page-size may only be set if the database has not yet been created. " So how do you execute the pragma prior to creating the DB? without calling sqlite3_open to get a DB handle that is needed to call prepare/step ? Ive also tried setting this via -DSQLITE_DEFAULT_PAGE_SIZE=4096 at compile time and still get the default pagesize of 1024. Thanks
RE: [sqlite] sqlite Performance
Donald, I set the PRAGMA synchronous= OFF and here are the results: real0m2.258s user0m1.736s sys 0m0.168s -- Pragma synchronous= NORMAL real0m2.395s user0m1.520s sys 0m0.128s Pragma synchronous= FULL real0m3.228s user0m2.276s sys 0m0.136s Running with synchronous=off is 43% faster !!! Running with Synchrounous=normal is 33 % faster. I should be able to run with synchronous=off. Since the application maintains state in a seperate DB elsewhere. Thanks for you valuable Input. Ken "Griggs, Donald" <[EMAIL PROTECTED]> wrote: Regarding: Creation of flat file takes 1.5 secs vs 3 seconds to create sqlite db. Flat file is 13 MB, sqlite db is 11 MB. "Any ideas how to get the sqlite output timings to a more respectable level would be appreciated. " I may be way off base if I'm not understanding correctly, but how can one call these values less than respectable? To create an sqlite database (or any other) the system must do the same things it does for the flat file, plus maintain a paging structure, create indices (presumably), rollback journals, etc., etc. To take only twice as long seems great (but I'm no expert here). I'm guessing it might have taken sqlite even longer except that maybe compression of numeric values allowed it to actually need fewer disk writes for the final file (not counting journalling, though). That being said, if the data you're writing out is saved elsewhere (i.e. you can repeat the whole process if it should fail) then you can try turning synchronous OFF, or, if you have to be more conservative, to NORMAL. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
Hello, IIRC (it was a while ago), one way to speed up insertion for large data sets is to drop the indexes, do the inserts (wrapped around a transaction) and then rebuild the indexes. For smaller data sets, the drop/rebuild indexes solution doesn't make sense because the time it takes to do that invalidates the performance gain. However, larger data sets seemed to benefit greatly. Again... that was a while ago... :-) I should dust-off my test app and see what the results are with the latest sources. I'll let you know what I find out. -- Tito On Mar 15, 2007, at 11:42 AM, John Stanton wrote: There are no free lunches. When Sqlite stores your data item it not only writes it into a linked list of pages in a file but also inserts at least on key into a B-Tree index. It does it quite efficiently so what you are seeing is the inevitable overhead of storing the data in a structured form. The value of the structure becomes obvious when you are retrieving a single item from a set of millions and the index allows you to access it in a tiny fraction of the time it would take to search an unstructured list like a flat file. The ACID implementation in Sqlite provides data security but is does involve a significant overhead. You pay a price for not losing data in a system crash. Like all things in life "you pays your money and you takes your choice". It is somewhat simpler with Sqlite in that you don't pay your money, you just take your choice. If you want faster Sqlite performance use faster disks. The latency is important so 15,000 rpm disks will be better than 5,400 rpm ones. Ken wrote: To answer your question: Yes I can use a flat file at this stage, but eventually it needs to be imported into some type of structure. So to that end I decided early on to use sqlite to write the data out. I was hoping for better performance. The raw I/O to read the data and process is around .75 seconds (no write i/ o).. So using a flat file output costs about .7 seconds. Using sqlite to do the output costs about 2.25 seconds. My question is why? And what can be done to improve this performance? John Stanton <[EMAIL PROTECTED]> wrote: Ken wrote: I'm looking for suggestions on improving performance of my sqlite application. Here are system timings for a run where the sqlite db has been replaced with a flat file output. real 0m1.459s user0m0.276s sys 0m0.252s This is a run when using sqlite as the output format. real 0m3.095s user0m1.956s sys 0m0.160s As you can see sqlite takes twice as long and almost 8 times the user time. Output size for flat file: 13, 360, 504flatfile.dat Output size fo sqlit file: 11,042,816 sqlt.db f Slite db has the following pragmas set. PRAGMA default_synchronous=FULL PRAGMA temp_store=memory PRAGMA page_size=4096 PRAGMA cache_size=2000 Any ideas how to get the sqlite output timings to a more respectable level would be appreciated. Thanks Ken If you want flat file performance, use a flat file. Sqlite is built on top of a flat file and cannot be faster or even as fast. If your application can use a flat file, why use anything more complex? - To unsubscribe, send email to [EMAIL PROTECTED] - -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
There are no free lunches. When Sqlite stores your data item it not only writes it into a linked list of pages in a file but also inserts at least on key into a B-Tree index. It does it quite efficiently so what you are seeing is the inevitable overhead of storing the data in a structured form. The value of the structure becomes obvious when you are retrieving a single item from a set of millions and the index allows you to access it in a tiny fraction of the time it would take to search an unstructured list like a flat file. The ACID implementation in Sqlite provides data security but is does involve a significant overhead. You pay a price for not losing data in a system crash. Like all things in life "you pays your money and you takes your choice". It is somewhat simpler with Sqlite in that you don't pay your money, you just take your choice. If you want faster Sqlite performance use faster disks. The latency is important so 15,000 rpm disks will be better than 5,400 rpm ones. Ken wrote: To answer your question: Yes I can use a flat file at this stage, but eventually it needs to be imported into some type of structure. So to that end I decided early on to use sqlite to write the data out. I was hoping for better performance. The raw I/O to read the data and process is around .75 seconds (no write i/o).. So using a flat file output costs about .7 seconds. Using sqlite to do the output costs about 2.25 seconds. My question is why? And what can be done to improve this performance? John Stanton <[EMAIL PROTECTED]> wrote: Ken wrote: I'm looking for suggestions on improving performance of my sqlite application. Here are system timings for a run where the sqlite db has been replaced with a flat file output. real 0m1.459s user0m0.276s sys 0m0.252s This is a run when using sqlite as the output format. real 0m3.095s user0m1.956s sys 0m0.160s As you can see sqlite takes twice as long and almost 8 times the user time. Output size for flat file: 13, 360, 504flatfile.dat Output size fo sqlit file: 11,042,816 sqlt.db f Slite db has the following pragmas set. PRAGMA default_synchronous=FULL PRAGMA temp_store=memory PRAGMA page_size=4096 PRAGMA cache_size=2000 Any ideas how to get the sqlite output timings to a more respectable level would be appreciated. Thanks Ken If you want flat file performance, use a flat file. Sqlite is built on top of a flat file and cannot be faster or even as fast. If your application can use a flat file, why use anything more complex? - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
Scott, The whole job is wrapped in an explicit transaction. Variables are bound and statements prepared only once, using reset. This is a write only app. 100% insert. Ken Scott Hess <[EMAIL PROTECTED]> wrote: Are you using explicit transactions at all? If not, as a quick test, put the _entire_ job in a transaction and see what happens. -scott On 3/15/07, Ken wrote: > To answer your question: > Yes I can use a flat file at this stage, but eventually it needs to be > imported into some type of structure. So to that end I decided early on to > use sqlite to write the data out. > > I was hoping for better performance. The raw I/O to read the data and process > is around .75 seconds (no write i/o).. So using a flat file output costs > about .7 seconds. > > Using sqlite to do the output costs about 2.25 seconds. My question is why? > And what can be done to improve this performance? > > > > John Stanton wrote: Ken wrote: > > I'm looking for suggestions on improving performance of my sqlite > > application. > > > > Here are system timings for a run where the sqlite db has been replaced > > with a flat file output. > > real 0m1.459s > > user0m0.276s > > sys 0m0.252s > > > > This is a run when using sqlite as the output format. > > real 0m3.095s > > user0m1.956s > > sys 0m0.160s > > > > As you can see sqlite takes twice as long and almost 8 times the user time. > > > > Output size for flat file: 13, 360, 504flatfile.dat > > Output size fo sqlit file: 11,042,816 sqlt.db f > > > > Slite db has the following pragmas set. > > > > PRAGMA default_synchronous=FULL > > PRAGMA temp_store=memory > > PRAGMA page_size=4096 > > PRAGMA cache_size=2000 > > > > Any ideas how to get the sqlite output timings to a more respectable level > > would be appreciated. > > > > Thanks > > Ken > > > If you want flat file performance, use a flat file. Sqlite is built on > top of a flat file and cannot be faster or even as fast. If your > application can use a flat file, why use anything more complex? > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite Performance
ok my bad for poor wording... I'll try with Synchronous off. I may also try disabling the journal file since I can easily recreate the data if it is not successful. Thanks, Ken "Griggs, Donald" <[EMAIL PROTECTED]> wrote: Regarding: Creation of flat file takes 1.5 secs vs 3 seconds to create sqlite db. Flat file is 13 MB, sqlite db is 11 MB. "Any ideas how to get the sqlite output timings to a more respectable level would be appreciated. " I may be way off base if I'm not understanding correctly, but how can one call these values less than respectable? To create an sqlite database (or any other) the system must do the same things it does for the flat file, plus maintain a paging structure, create indices (presumably), rollback journals, etc., etc. To take only twice as long seems great (but I'm no expert here). I'm guessing it might have taken sqlite even longer except that maybe compression of numeric values allowed it to actually need fewer disk writes for the final file (not counting journalling, though). That being said, if the data you're writing out is saved elsewhere (i.e. you can repeat the whole process if it should fail) then you can try turning synchronous OFF, or, if you have to be more conservative, to NORMAL. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
To answer your question: Yes I can use a flat file at this stage, but eventually it needs to be imported into some type of structure. So to that end I decided early on to use sqlite to write the data out. I was hoping for better performance. The raw I/O to read the data and process is around .75 seconds (no write i/o).. So using a flat file output costs about .7 seconds. Using sqlite to do the output costs about 2.25 seconds. My question is why? And what can be done to improve this performance? John Stanton <[EMAIL PROTECTED]> wrote: Ken wrote: > I'm looking for suggestions on improving performance of my sqlite application. > > Here are system timings for a run where the sqlite db has been replaced with > a flat file output. > real 0m1.459s > user0m0.276s > sys 0m0.252s > > This is a run when using sqlite as the output format. > real 0m3.095s > user0m1.956s > sys 0m0.160s > > As you can see sqlite takes twice as long and almost 8 times the user time. > > Output size for flat file: 13, 360, 504flatfile.dat > Output size fo sqlit file: 11,042,816 sqlt.db f > > Slite db has the following pragmas set. > > PRAGMA default_synchronous=FULL > PRAGMA temp_store=memory > PRAGMA page_size=4096 > PRAGMA cache_size=2000 > > Any ideas how to get the sqlite output timings to a more respectable level > would be appreciated. > > Thanks > Ken > If you want flat file performance, use a flat file. Sqlite is built on top of a flat file and cannot be faster or even as fast. If your application can use a flat file, why use anything more complex? - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite Performance
Regarding: Creation of flat file takes 1.5 secs vs 3 seconds to create sqlite db. Flat file is 13 MB, sqlite db is 11 MB. "Any ideas how to get the sqlite output timings to a more respectable level would be appreciated. " I may be way off base if I'm not understanding correctly, but how can one call these values less than respectable? To create an sqlite database (or any other) the system must do the same things it does for the flat file, plus maintain a paging structure, create indices (presumably), rollback journals, etc., etc. To take only twice as long seems great (but I'm no expert here). I'm guessing it might have taken sqlite even longer except that maybe compression of numeric values allowed it to actually need fewer disk writes for the final file (not counting journalling, though). That being said, if the data you're writing out is saved elsewhere (i.e. you can repeat the whole process if it should fail) then you can try turning synchronous OFF, or, if you have to be more conservative, to NORMAL. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
Ken wrote: I'm looking for suggestions on improving performance of my sqlite application. Here are system timings for a run where the sqlite db has been replaced with a flat file output. real 0m1.459s user0m0.276s sys 0m0.252s This is a run when using sqlite as the output format. real 0m3.095s user0m1.956s sys 0m0.160s As you can see sqlite takes twice as long and almost 8 times the user time. Output size for flat file: 13, 360, 504flatfile.dat Output size fo sqlit file: 11,042,816 sqlt.db f Slite db has the following pragmas set. PRAGMA default_synchronous=FULL PRAGMA temp_store=memory PRAGMA page_size=4096 PRAGMA cache_size=2000 Any ideas how to get the sqlite output timings to a more respectable level would be appreciated. Thanks Ken If you want flat file performance, use a flat file. Sqlite is built on top of a flat file and cannot be faster or even as fast. If your application can use a flat file, why use anything more complex? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_update_hook
Hello, I'm trying to use the update hook functionality. I have lookup (aka join) tables that provide many-to-many relationships between rows in other tables. The problem is when I get the delete notification for the join tables the rowid is not useful in that context. I really need to know the values of the other columns in these tables but I can't select from within the update hook callback. I'm using triggers to enforce FK constraints and they work great. Is there any way I can use triggers to get the values of these rows before the delete happens (e.g. call a user-defined function in C)? Any other ideas? Chad - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SPAM-LOW: RE: [sqlite] Format of csv file RE: [sqlite] date/time implementation question
I use the tilde '~' character a lot.
On Thu, 15 Mar 2007, Griggs, Donald wrote:
> Regarding: "What is the default separator?" [for using with .import in
> the command line utility]
>
> It is the vertical bar ("|", virgule, pipe character).
>
> By the way, I don't *think* that .import allows the separator to be
> quoted, does it?
> E.g., don't try to import
> 5,238,"Cohen, Jr.",Rafi
> expecting the "Cohen, Jr." to be considered a single field.
>
> For that reason, depending on your data, sometimes the pipe or tab makes
> a better separator.
>
>
>
> -Original Message-
> From: Rafi Cohen [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 15, 2007 12:42 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Format of csv file RE: [sqlite] date/time
> implementation question
>
> Dennis, without knowing that we are talking here about non-standard
> format of the csv file, my client, for whom I'm preparing this project
> decided to change the file extension to .txt in order not to confuse
> with the standard format of .csv.
> Anyway, the time for this project is beginning to be tight so I'll still
> try today to implement the sql approach, but if I fail, at least for
> this version, I'll proceed with the first approach.
> Now, I understand that .import accepts any kind of file, just under
> condition that each line represents a valid row of the table and the
> separator is either the default one or the one given explicitly by the
> .separator command.
> What is the default separator? I could not discover this by looking into
> the code.
> My idea is to read the file sent by the client within the C application,
> check what needs to be checked for validation and store the relevant
> parts of it formatted appropriately in another file and then import from
> this file into a table and proceed as you suggested yesterday.
> Do you see any problem with this approach or have anhy other suggestion?
> Concerning separator, I can add the .separator "," command into the
> script before .import, but I may also use the default, if I know what it
> is.
> I tried this manually in sqlite3 and I know that coma is not the default
> separator as .import failed.
> But after .separator "," .import succeeded and I could indeed select the
> rows from the table.
> I hope this signs a success for me to implement this approach in my C
> code as you suggested.
> Thanks, Rafi.
>
> -Original Message-
> From: Dennis Cote [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, March 14, 2007 11:11 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Format lf csv file RE: [sqlite] date/time
> implementation question
>
>
> Rafi Cohen wrote:
> > Hi Dennis, the first approach is clear now and I may proceed with it.
> >
> Good to hear.
> > The second approach is interesting and chalenging, but leaves some
> > issues to clarify and in case I find solutions to those issues I well
> > may adopt it. 1. format of csv file: I have no idea how this csv file
> > is created and which database engine is used. I do know that I receive
>
> > such a file once or twice a day.
> > While reading the file onto the list of structures, I also validate
> the
> > consistency of the data in the file.
> > Before the data lines (starting with "d,") there is a header line
> > "hdr,". This line contains the sequential number of the file, number
> of
> > records in the file and a checksum on a specific field of the data
> (say
> > coumn 2).
> > As I knew nothing up to now about .import, I wonder if there is a way
> to
> > include those checings in the second approach?
> >
> Firstly, this does not sound like a standard CSV format file (see
> http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm ).
>
> Secondly, your validation checks can not be added to the normal csv
> .import command using the sqlite shell, but they could be added to a
> customized copy of the import routine that you add to your own code. It
> might also be possible to convert some of the validation tests to SQL
> check constraints on the table columns as well.
> > 2. The deletion of the future dates is incorrect. On the contrary, in
> > the first approach, I re-examine the remaining structures each half a
> > minute until any of them becomes past date, then I process it just
> > like any other past date structures and then free it. In case a new
> > .csv file arrives, I add the new list of structures to the remaining
> > ones and continue to examine them every half a minute. I could do the
> > same with the sql3_exec statement in the second approach, but I need
> > another approach for the case of the future records. I hope you have
> > satisfying answers for those 2 issues and then I'll be glad to proceed
>
> > with the second approach.
> >
> In that case you could split the imported data into two tables using the
>
> date test. And then process only the table that contains the old
> records. The future records would remain in a second table.
[sqlite] sqlite Performance
I'm looking for suggestions on improving performance of my sqlite application. Here are system timings for a run where the sqlite db has been replaced with a flat file output. real 0m1.459s user0m0.276s sys 0m0.252s This is a run when using sqlite as the output format. real 0m3.095s user0m1.956s sys 0m0.160s As you can see sqlite takes twice as long and almost 8 times the user time. Output size for flat file: 13, 360, 504flatfile.dat Output size fo sqlit file: 11,042,816 sqlt.db f Slite db has the following pragmas set. PRAGMA default_synchronous=FULL PRAGMA temp_store=memory PRAGMA page_size=4096 PRAGMA cache_size=2000 Any ideas how to get the sqlite output timings to a more respectable level would be appreciated. Thanks Ken
Re: [sqlite] Re: Re: Meta Information: How to retrieve the column names of a table ?
Samuel R. Neff wrote: Dennis, Do any database systems actually implement this part of the standard? MSSQL used the term information schema in their MSSQL 2000 version of metadata access but afaik it was not close to the ANSI standard at all. It's a been a while for me but I think the Oracle stuff is totally different from what ANSI spec. Samuel, The following is from the PostgreSQL online manual (see http://www.postgresql.org/docs/8.2/static/features.html ). It is a pretty good overview of the SQL standards and their implementation. Note they claim to support 9075-11 which is the information and definition schema. SQL-92 defined three feature sets for conformance: Entry, Intermediate, and Full. Most database management systems claiming SQL standard conformance were conforming at only the Entry level, since the entire set of features in the Intermediate and Full levels was either too voluminous or in conflict with legacy behaviors. Starting with SQL:1999, the SQL standard defines a large set of individual features rather than the ineffectively broad three levels found in SQL-92. A large subset of these features represents the "Core" features, which every conforming SQL implementation must supply. The rest of the features are purely optional. Some optional features are grouped together to form "packages", which SQL implementations can claim conformance to, thus claiming conformance to particular groups of features. The SQL:2003 standard is also split into a number of parts. Each is known by a shorthand name. Note that these parts are not consecutively numbered. * ISO/IEC 9075-1 Framework (SQL/Framework) * ISO/IEC 9075-2 Foundation (SQL/Foundation) * ISO/IEC 9075-3 Call Level Interface (SQL/CLI) * ISO/IEC 9075-4 Persistent Stored Modules (SQL/PSM) * ISO/IEC 9075-9 Management of External Data (SQL/MED) * ISO/IEC 9075-10 Object Language Bindings (SQL/OLB) * ISO/IEC 9075-11 Information and Definition Schemas (SQL/Schemata) * ISO/IEC 9075-13 Routines and Types using the Java Language (SQL/JRT) * ISO/IEC 9075-14 XML-related specifications (SQL/XML) PostgreSQL covers parts 1, 2, and 11. Part 3 is similar to the ODBC interface, and part 4 is similar to the PL/pgSQL programming language, but exact conformance is not specifically intended or verified in either case. PostgreSQL supports most of the major features of SQL:2003. Out of 164 mandatory features required for full Core conformance, PostgreSQL conforms to at least 150. In addition, there is a long list of supported optional features. It may be worth noting that at the time of writing, no current version of any database management system claims full conformance to Core SQL:2003. I suspect that IBM's DB2 also supports SQL schema information though I don't know for sure. This is another case of where the standard exists and incompatibilities between different database engines can be removed if they all migrate towards support of the standard. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: to quote or not ?
Thanks very much Igor, that explains it, cheers, Stef Mientki Igor Tandetnik wrote: Stef Mientki <[EMAIL PROTECTED]> wrote: So I would expect that I now can always double quote the selection fields, but unfortunately that doesn't seem to be true. From a graphical design, I get for instance: SELECT "Opnamen.PatNr", "Opnamen.Datum" Opnamen, PatNr and Datum are three separate identifiers, and have to be quoted separatedly (if at all), as in "Opnamen"."PatNr". "Opnamen.PatNr" is a single identifier, distinct from Opnamen.PatNr (which is two identifiers separated by period). To illustrate, consider these valid SQL statements: create table Opnamen (PatNr, "Opnamen.PatNr"); insert into Opnamen values (1, 2); select Opnamen.PatNr, "Opnamen"."PatNr", "Opnamen.PatNr", Opnamen."Opnamen.PatNr" from Opnamen; The last query should return a single row with values (1, 1, 2, 2) Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - KvK: 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Meta Information: How to retrieve the column names of a table ?
Dennis Cote schrieb: I think it should be possible to create a subset of the standard information schema in sqlite using virtual tables. That would be very nice and consistent ! Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
