Re: [sqlite] suggestion
Don't know about ALTER TABLE, but having native corelated subqueries support would save on code and possibly speed up getting at the end result greatly. Vijay - Forwarded by Vijay S Tenneti/Europe/Group 1 Software Europe Ltd on 21/04/2004 18:46 - Puneet Kishor <[EMAIL PROTECTED] org> To [EMAIL PROTECTED] 21/04/2004 16:23 cc [EMAIL PROTECTED], [EMAIL PROTECTED] Subject Re: [sqlite] suggestion On Apr 21, 2004, at 8:37 AM, [EMAIL PROTECTED] wrote: > > Puneet Kishor wrote: > >> my guess is because it can be done other ways (see the docs on this >> specifically), and the idea is to keep SQLite as simple as possible. >> The >> more "conveniences" that are added to it, the more complicated it will >> become. > >> Usually, once the database is set, there is little reason to ALTER >> it... >> it all boils down to ROI... > > I beg to differ concerning the last point. Adding MORE sql commands is > not a > convenience Basil, I am simply speculating. Why really ALTER TABLE doesn't exist only Richard can definitively answer. That said, in the spirit of discussion, this thread could possibly influence Richard (or not). So, here is my take -- > but rather can save hundreds to thousands of lines of code!!! I have > been > programming in I can't visualize a scenario where an ALTER TABLE command would save 100s-1000s of lines of code, but that could be my failing. According to the docs (and from my basic SQL knowledge) it seems I would save about 5 lines of code if I had an ALTER table command. If I am using a scripting language, it would be a suitable function that I could call at will. That said, would I welcome an ALTER TABLE command? Absolutely. I use databases primarily as data stores for web apps, and I am constantly modifying the tables because I am a firm adherent to the "design by the seat of my pants" school. I find MS-Access to be a particularly wonderful database for web app development. It offers easy and convenient modification to tables, a diagramming view where I can make or break relationships, quick querying, etc. Once everything is done, I can always migrate the db to SQL Server or Oracle or MySQL. I sincerely wish there were something like that on my Mac. The new version of Filemaker offers a really nice relationship builder interface, its other shortcomings notwithstanding. SQL Server's Enterprise Manager is nice, but not quite. And Oracle's Java tools, at least the ones I have used... well, they defy the definition of good taste and sensibility. Suffice it to say that I would not have survived without TOAD. The thing here is... all these products take a long time to develop. They are expensive -- even Access and FM Pro are $300+. Richard's product is free. I am not quite sure how he makes money doing this, but I am very glad for his gift to us all. SQLite is wonderful, but given what I assume are few resources, my sense is that he is applying it toward developing the basic capabilities of the database, especially if certain "convenient" tasks can be done via not so cumbersome alternatives. Btw, I have a question that I can't find an answer to -- Is Richard the only developer of SQLite? There are many very knowledgeable sounding C and db type folks on this list, but I would be curious to know more about the "core developers" of SQLite, if there are any besides him. I would like to know this for my own gratification, for knowing who to thank in my prayers, and also as background knowledge on SQLite when trying to justify it for a project. Regards. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Backing up data by date
Forgive my ignorance, I have yet to use a transaction and therefore can you give me an example. Lloyd - Original Message - From: "Christian Smith" <[EMAIL PROTECTED]> To: "Lloyd thomas" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, April 21, 2004 3:27 PM Subject: Re: [sqlite] Backing up data by date > On Tue, 20 Apr 2004, Lloyd thomas wrote: > > >Which is the best way to backup rows which meet a certain date criteria? > >ie WHERE data is <= '2003-11-20'. > >Would I need to select and save the data to a temporary table and then DUMP the temp table. > > > > Just begin a transaction to get a snapshot of the database, then dump the > data within the transaction. No temporary table needed. Will lock the > database, but it is the only way to ensure a consistent view unless you > are saving data from a single table or view, in which case you won't need > the transaction. > > Christian > > -- > /"\ > \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL > X - AGAINST MS ATTACHMENTS > / \ > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] suggestion
My guess is, since it would appear that Richard is a member of a facility of a higher education institute, and somehow involved in computer education, I would speculate there are "students" involved in SQLite coding. Why else would a "Good to Excellent" C programmer have such an elaborate pre-release test suite? :-) Fred > -Original Message- > From: Puneet Kishor [mailto:[EMAIL PROTECTED] > Sent: Wednesday, April 21, 2004 10:24 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [sqlite] suggestion > ... > > Btw, I have a question that I can't find an answer to -- > > Is Richard the only developer of SQLite? There are many very > knowledgeable sounding C and db type folks on this list, but I would be > curious to know more about the "core developers" of SQLite, if there > are any besides him. I would like to know this for my own > gratification, for knowing who to thank in my prayers, and also as > background knowledge on SQLite when trying to justify it for a project. ... - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] suggestion
On Apr 21, 2004, at 8:37 AM, [EMAIL PROTECTED] wrote: Puneet Kishor wrote: my guess is because it can be done other ways (see the docs on this specifically), and the idea is to keep SQLite as simple as possible. The more "conveniences" that are added to it, the more complicated it will become. Usually, once the database is set, there is little reason to ALTER it... it all boils down to ROI... I beg to differ concerning the last point. Adding MORE sql commands is not a convenience Basil, I am simply speculating. Why really ALTER TABLE doesn't exist only Richard can definitively answer. That said, in the spirit of discussion, this thread could possibly influence Richard (or not). So, here is my take -- but rather can save hundreds to thousands of lines of code!!! I have been programming in I can't visualize a scenario where an ALTER TABLE command would save 100s-1000s of lines of code, but that could be my failing. According to the docs (and from my basic SQL knowledge) it seems I would save about 5 lines of code if I had an ALTER table command. If I am using a scripting language, it would be a suitable function that I could call at will. That said, would I welcome an ALTER TABLE command? Absolutely. I use databases primarily as data stores for web apps, and I am constantly modifying the tables because I am a firm adherent to the "design by the seat of my pants" school. I find MS-Access to be a particularly wonderful database for web app development. It offers easy and convenient modification to tables, a diagramming view where I can make or break relationships, quick querying, etc. Once everything is done, I can always migrate the db to SQL Server or Oracle or MySQL. I sincerely wish there were something like that on my Mac. The new version of Filemaker offers a really nice relationship builder interface, its other shortcomings notwithstanding. SQL Server's Enterprise Manager is nice, but not quite. And Oracle's Java tools, at least the ones I have used... well, they defy the definition of good taste and sensibility. Suffice it to say that I would not have survived without TOAD. The thing here is... all these products take a long time to develop. They are expensive -- even Access and FM Pro are $300+. Richard's product is free. I am not quite sure how he makes money doing this, but I am very glad for his gift to us all. SQLite is wonderful, but given what I assume are few resources, my sense is that he is applying it toward developing the basic capabilities of the database, especially if certain "convenient" tasks can be done via not so cumbersome alternatives. Btw, I have a question that I can't find an answer to -- Is Richard the only developer of SQLite? There are many very knowledgeable sounding C and db type folks on this list, but I would be curious to know more about the "core developers" of SQLite, if there are any besides him. I would like to know this for my own gratification, for knowing who to thank in my prayers, and also as background knowledge on SQLite when trying to justify it for a project. Regards. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Backing up data by date
On Tue, 20 Apr 2004, Lloyd thomas wrote: >Which is the best way to backup rows which meet a certain date criteria? >ie WHERE data is <= '2003-11-20'. >Would I need to select and save the data to a temporary table and then DUMP the temp >table. > Just begin a transaction to get a snapshot of the database, then dump the data within the transaction. No temporary table needed. Will lock the database, but it is the only way to ensure a consistent view unless you are saving data from a single table or view, in which case you won't need the transaction. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] suggestion
On Wed, 21 Apr 2004 [EMAIL PROTECTED] wrote: > >Puneet Kishor wrote: > >> my guess is because it can be done other ways (see the docs on this >> specifically), and the idea is to keep SQLite as simple as possible. The >> more "conveniences" that are added to it, the more complicated it will >> become. > >> Usually, once the database is set, there is little reason to ALTER it... >> it all boils down to ROI... > >I beg to differ concerning the last point. Adding MORE sql commands is >not a convenience but rather can save hundreds to thousands of lines of >code!!! If a new revision of some app requires updated tables, there are invariably bigger problems than changing the schema. Changing the schema is the easy bit, just write a script to dump the old data, recreate the table, and restore the data from the dump. The only troublesome schema updates in my experience (which to be fair is not massive) is where the data model has fundamentally changed, and ALTER TABLE ain't gonna help you with that. >I have been programming in SQL for over a decade now and it is by >far the best programming language in use, even with all its warts mainly >because with one SQL statement can do the work of many lines of code. >SQLIte is "Lite" mainly because it is designed around a single >user/process with file locking on a single database file. I am sure if it >was designed for a multi-user/ server with table/page/row locking over >multiple files, it would be at least 10 time bigger even if it >implemented the same SQL commands. Agreed. >Increasing SQLite DDL capabilities is a must as well as adding more >control-flow statements. Yes I like the fantastic low level control I get >by using the "c" api calls but 99% of my client solutions can be written >completely using SQL only. I think even in its current implementation, >SQLite is the best embedded SQL based engine available and I am sure ver >3.0 and beyond will add more enhancements to the SQL commands it >supports... I'm not sure about anyone else, but I've used SQLite mainly as a data store. I'm working on a project which will have trigger based callbacks to C code for cache invalidation, but that's about it. Whatever, you have the source, so scratch that itch... Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] suggestion
> > I'm new to sqlite but as far as I can tell it does not support ALTER TABLE > > command. I could find that useful in future versions. > > That's right. > ALTER TABLE would be very helpful. > Are there any reasons it is not implemented yet ? What do you mean by "ALTER TABLE"? The ability to add new columns or constraints? That can be implemented by renaming the original table, creating a new table with the new structure, then importing the contents of the original table into the new one. In fact, with the exception of adding a new column to the *end*, this is precisely how MS SQL 2000 implements adding a new column to a table. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
