[sqlite] Re: Error in SQLite's CSV output
Hi Richard and all, Back in January I reported that earlier bugs (October) seemed to have been fixed (thank you). I think I've discovered yet another bug in the CSV output using the sqlite3 command line tool. Here's a sample of the new bug: .mode csv select 'a=1,234', 'b=5'; gives: a=1,234,b=5 but should give: "a=1,234",b=5 I'm using sqlite 3.4.0 on Mac OS X 10.5.1. CSV (comma separated values) output should show value1,value2,value3 etc. If a value contains a comma, then csv should encapsulate that value in quotes: "value,1",value2,value3. And if a value contains a quote, csv should also encapsulate that value in quotes and show the quote as a double quote, eg "value,1","value ""2""",value3 Sqlite3 seems to ignore the need for quotes in a value that contains an equals "=". I don't know if it also fails with other characters. Needless to say, this messes up the output considerably, placing output in the wrong column. Am I missing something? Is this a known problem? Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?
> This scenario has played out multiple times. > > Say what you want about DeviceSQL the product, but one thing > is undeniable: their sales presentations are top-notch. The > first remark of yours that I quoted above shows this to be the case. Yes - but a company that sells technical products has to also do that, too. > But impressive sales talks do not necessarily translate into > impressive products. In fact, a management-oriented sales > presentation, such as provided by Encirq, can be a put-off > for technical people. The engineers and programmers I > normally deal with are much more attracted to the droll, > just-the-facts type of product that they see and get with > SQLite. Flashly sales talks that are low in technical > detail, such as those offered in the past by Encirq (I > haven't seen the "webinar") tend to frighten many technical people. That's true. A lot of those kinds of sales presentations are correctly targeted at decision makers that make financial decisions. I don't consider it a bad thing - it's really a necessity to be competitive. The bear in the woods isnt evil, he's just hungry like the other bears :-) > When engineers contacted me with help in defending SQLite, it > was not because they didn't understand SQLite. It was > because they recognized that their management did not > understand SQLite, and that they had no hope of communicating > as effectively as the Encirq sales team, and that they were > desparate for any kind of help they could get. Sadly, they > got little help from me since I, like they, am hopelessly > outclassed by the Encirq sales people when it comes to giving > impressive talks. On no occasion have I told the engineers > anything they didn't already know, though I might have helped > them to organize their thoughts a little. I think what you are seeing is evolution of the software industry. It really isnt necessary for there to be such an extreme split between engineering and management - and by evolution I mean that engineering has to adapt to a tighter relationship with management, or they are destined to have their roles outsourced. Noone should know the product than its own engineers, and its those who can bridge that divide that will be running the engineering and IT departments. Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
On Fri, Dec 14, 2007 at 04:28:07PM -0600, John Stanton wrote: > Samuel R. Neff wrote: > >I'm not sure I see added value in incorporating procedural SQL in an > >embedded database like SQLite or DeviceSQL. Isn't the easily extensible > >mechanism that SQLite has for creating custom functions in the host > >language [...] > > The difference is if you are pursuing the DeviceSQL approach and > compiling a library of data manipulation functions. The partitioning is > cleaner if procedural logic can be included in those functions. > > In our Sqlite applications we have implemented Javascript as a DB > procedural language by adding an Sqlite class. You can store it in the > DB and execute it. You can also run Javascript scripts which manipulate > an Sqlite DB. When using Sqlite we see the value of a DB procedural > language in the dynamic storage of rules rather than having rules > chiselled in stone in the application code. Multiple rules in > Javascript can be integrated and compiled into one executable procedure, > giving great flexibility in building systems in the "expert" style. > > We chose Javascript for practical reasons. It is universally used in > WWW pages so the developers do not have any new language to learn. SQL is very expressive, and procedural SQL is too. Much more so that C. So if you're writing an embedded app in C then procedural SQL might come in handy (also, the VDBE code will probably be smaller than the corresponding object code). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
Samuel R. Neff wrote: -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 3:55 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DeviceSQL ... is it not directly comparable to DeviceSQL unless the external compiler handles not only SQL but also PL/SQL. The addition of the command language allows for creating a library of data manipulation functions rather than just embedded SQL. ... John, I'm not sure I see added value in incorporating procedural SQL in an embedded database like SQLite or DeviceSQL. Isn't the easily extensible mechanism that SQLite has for creating custom functions in the host language (C, C#, ActionScript, whatever) a fully valid replacement for whatever procedure language could be provided through VDBE? Also doesn't the fact that SQLite (and any embedded db) runs in-process negate the importance of SQL procedural code? In my experience, stored procedures are usually used to offload more work to the DB server and thus perform DB intensive code closer to where the DB data is, which is not necessary when the DB is already in-process? After moving from MSSQL to SQLite for our application, which previously used stored procedures, we've never missed the functionality of TSQL and found creating UDFs in C# for SQLite much easier and more powerful that using TSQL UDFs, especially 'cause they run in-process and thus have full access to the host application's objects and code. Sam The difference is if you are pursuing the DeviceSQL approach and compiling a library of data manipulation functions. The partitioning is cleaner if procedural logic can be included in those functions. In our Sqlite applications we have implemented Javascript as a DB procedural language by adding an Sqlite class. You can store it in the DB and execute it. You can also run Javascript scripts which manipulate an Sqlite DB. When using Sqlite we see the value of a DB procedural language in the dynamic storage of rules rather than having rules chiselled in stone in the application code. Multiple rules in Javascript can be integrated and compiled into one executable procedure, giving great flexibility in building systems in the "expert" style. We chose Javascript for practical reasons. It is universally used in WWW pages so the developers do not have any new language to learn. --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: [Linux + PHP] Recommended way to access SQLite?
Kees Nuyt wrote: >> So the options are: >> 1. use the old SQLite2 sqlite_() functions (or some class that turns this >> into OO) >> 2. PDO to use the SQLite3 linked-in library >> 3. PDO to access the SQLite3 DLL >> >> ... with 2 being the recommended choice. > > yes, > - 2 for speed and reliability, > - 3 if one needs the latest SQLite I highly recommend PDO for any and all PHP database access that needs doing. It's very full featured, fast, and easy to work with. It's not worth learning the proprietary commands for PHP has for each DBMS. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: [Linux + PHP] Recommended way to access SQLite?
On Fri, 14 Dec 2007 03:15:17 +0100, Gilles Ganault <[EMAIL PROTECTED]> wrote: >On Mon, 10 Dec 2007 16:35:48 +0100, Kees Nuyt <[EMAIL PROTECTED]> wrote: > >So I activated php_pdo_sqlite_external instead and copied > >sqlite3.dll v3.5.3 to %serverroot%/bin . > >Thanks for the infos. > >So the options are: >1. use the old SQLite2 sqlite_() functions (or some class that turns this >into OO) >2. PDO to use the SQLite3 linked-in library >3. PDO to access the SQLite3 DLL > >... with 2 being the recommended choice. yes, - 2 for speed and reliability, - 3 if one needs the latest SQLite -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] DeviceSQL
-Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 3:55 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DeviceSQL ... is it not directly comparable to DeviceSQL unless the external compiler handles not only SQL but also PL/SQL. The addition of the command language allows for creating a library of data manipulation functions rather than just embedded SQL. ... John, I'm not sure I see added value in incorporating procedural SQL in an embedded database like SQLite or DeviceSQL. Isn't the easily extensible mechanism that SQLite has for creating custom functions in the host language (C, C#, ActionScript, whatever) a fully valid replacement for whatever procedure language could be provided through VDBE? Also doesn't the fact that SQLite (and any embedded db) runs in-process negate the importance of SQL procedural code? In my experience, stored procedures are usually used to offload more work to the DB server and thus perform DB intensive code closer to where the DB data is, which is not necessary when the DB is already in-process? After moving from MSSQL to SQLite for our application, which previously used stored procedures, we've never missed the functionality of TSQL and found creating UDFs in C# for SQLite much easier and more powerful that using TSQL UDFs, especially 'cause they run in-process and thus have full access to the host application's objects and code. Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] create table with datatype = DATE - STORE as DATETIME or INTEGER.
If you make your date and time columns numeric (floating point) you use 8 bytes to store a date and time. You then use the embedded Sqlite date and time functions to present date and time as your application chooses. I believe that if you declare the column as DATE Sqlite will give it an underlying type of numeric. Look at Sqlite's "manifest typing" to see how it ignores the SQL data types and stores information in the way it chooses as most appropriate. Look at date.c to get an idea of the inbuilt Sqlite date and time support. Joanne Pham wrote: Hi All, Should I create the column in DATETIME or the INTEGER to store the time. DATETIME has the value of GMT time. So I store this value as INTEGER then I need to convert datetime format but it will be use less space if I use the INTEGER. Please give me an advice. Thanks Joanne - Original Message From: John Stanton <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, December 13, 2007 3:05:52 PM Subject: Re: [sqlite] create table with datatype = DATE The type DATE is a declared type, not an actual type and has no effect u nless your code specifically picks it out as a declared type. To do what you want use a trigger on insert and update the date field with datetime('now'); Joanne Pham wrote: Hi All, I create the table as : create table test (name varchar(30), createDate DATE default DATETIME('NOW')); but I got the error message. I want to have the default as now if it is not specify. Thanks in advance, Joanne Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs - To unsubscribe, send email to [EMAIL PROTECTED] - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
We did that with our products which used byte code. Byte code compiled on earlier versions would run on later ones, but new code with the extra opcodes would not run on old interpreters. It protected customers who had lost their source code or were afraid to recompile after an upgrade because of version control blunders. Noah Hart wrote: Why can't new OP codes be simply added to the end, so that the serializations will always work? Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 8:20 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DeviceSQL Importance: High Case in point: A new opcode has been added for the pending 3.5.4 release. And this caused many of the other opcodes to be automatically renumbered. A serialization created for 3.5.3 has little hope of running on 3.5.4. -- D. Richard Hipp <[EMAIL PROTECTED]> CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
Dennis, your application may not be a microwave oven but it does appear to be "deeply embedded" as opposed to a general purpose DB server which handles generic queries. I was aware of the runtime only version of Sqlite, but is it not directly comparable to DeviceSQL unless the external compiler handles not only SQL but also PL/SQL. The addition of the command language allows for creating a library of data manipulation functions rather than just embedded SQL. My point was that the DeviceSQL approach could be used with Sqlite to produce a smaller footprint embedded product and that such a product has the potential of being less memory hungry than DeviceSQL simply by virtue of the higher information density of the VDBE target code compared to native machine instructions. Dennis Cote wrote: John Stanton wrote: DeviceSQL is not suitable for general purpose SQL processing, unlike Sqlite, and should only be compared as an alternative in deeply embedded applications so the only useful comparison is one which looks like a cell phone, microwave oven or a TV set top box. The application doesn't necessarily have to look like a microwave. :-) It could be any application that uses a fixed set of predetermined SQL statements to perform its operations. My primary application does exactly that using SQLite with all statements prepared and cached as needed. It runs on a standard PC under Windows. We have no need for executing arbitrary, runtime generated, or user supplied SQL as many other applications do. Every SQL statement that can be executed is known before the application starts. I can imagine that a version of Sqlite which does not include its SQL compiler and which uses precompiled VDBE code would provide similar functionality to DeviceSQL, particularly if the Sqlite compiler were extended to generate VDBE from PL/SQl. I can imagine that the higher information density of the VDBE code could deliver the advantage =of a smaller memory footprint. I thought that this already existed as a commercial product that Richard offered, but I can't find any reference to it on the paid support page at http://www.hwaci.com/sw/sqlite/prosupport.html. My recollection was an offline compiler that ran on a PC and generated VDBE code to execute SQL statements, along with source code for a runtime execution engine that would execute those pre-compiled statements. The execution engine could be built for any target, and was much smaller than SQLite because it eliminated the parser and code generator functionality. Does this exist, or was I just imagining it? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?
"Lynn Fredricks" <[EMAIL PROTECTED]> wrote: > > I don't know anything about DeviceSQL but their > presentation is enough to get my respect :-) > [...] > > It seems to me that if the engineers are coming to you to defend their > selection of SQLite, then they didnt know SQLite as well as they should This scenario has played out multiple times. Say what you want about DeviceSQL the product, but one thing is undeniable: their sales presentations are top-notch. The first remark of yours that I quoted above shows this to be the case. But impressive sales talks do not necessarily translate into impressive products. In fact, a management-oriented sales presentation, such as provided by Encirq, can be a put-off for technical people. The engineers and programmers I normally deal with are much more attracted to the droll, just-the-facts type of product that they see and get with SQLite. Flashly sales talks that are low in technical detail, such as those offered in the past by Encirq (I haven't seen the "webinar") tend to frighten many technical people. When engineers contacted me with help in defending SQLite, it was not because they didn't understand SQLite. It was because they recognized that their management did not understand SQLite, and that they had no hope of communicating as effectively as the Encirq sales team, and that they were desparate for any kind of help they could get. Sadly, they got little help from me since I, like they, am hopelessly outclassed by the Encirq sales people when it comes to giving impressive talks. On no occasion have I told the engineers anything they didn't already know, though I might have helped them to organize their thoughts a little. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
On 14-Dec-2007, at 5:41 AM, Clay Dowling wrote: I have to say, this discussion has been very informative, although probably not in a way that would make mr Weick happy. I've certainly learned a lot about encirq that tells me what I need to know about doing business with them. Same thought here. Kudos to D. Richard Hipp for keeping his cool under provocation. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?
> > August? We start to discuss about DeviceSQL some days ago, or I am > > wrong? > > > > I have several support customer in Europe who have been > visited by the Encirq sales rep there, trying to get them to > abandon SQLite in favor of DeviceSQL. The way this normally > happens is that a sales talk is given to the management. > Then the management goes to their engineers asking for a > comparison of DeviceSQL and SQLite. The engineers then come > to me for help in defending SQLite. > I respond with a letter outlining the strengths and > weaknesses of each product as known to me. I am always very > careful to outline the limitations of my knowledge in these > cases and to attempt to give as fair and as balanced of a > comparison as I can. > > In one recent episode (prehaps the one that Steve is > referring to) my reply was forwarded to the Encirq sales rep. > This provoked a vigorous response from Encirq in which they > attempted a point-by-point rebuttal of my letter. While Im not in the habit of defending the competition, Id like to toss my 2-cents in on this. I don't know anything about DeviceSQL but their presentation is enough to get my respect :-) The database market is very mature and if you do not have a set of special features (in the actual engineering of the product, deployment or in its licensing) that is compeling to a certain customer segment, you are dead meat. Understanding those compeling reasons is one part engineering and one part management. Engineering should understand technical limitations/advantages and needs to be able to convey them convincingly to management to the best of their understanding of product strategy. Likewise management also makes decisions not always based on engineers understanding or lack of understanding of the direction of the business (let along execs jockeying against each other ;-)). And no matter how you couch or caveat a statement, one isnt always present to know that those caveats are also passed along - you may get little difference out the other end between "God told me..." and "I witnessed it myself." It seems to me that if the engineers are coming to you to defend their selection of SQLite, then they didnt know SQLite as well as they should because - it seems they havent made a very informed choice for using SQLite (or any db) to begin with. The informed one might not be with the company any more. But if a sales guy from DeviceSQL can pinpoint the needs of an organization better than its own engineers, then its even worse (or better if you are the DeviceSQL sales rep!). Are you sure your customer is in Europe and not the US federal government? :-) Best regards, Lynn Fredricks President Paradigma Software http://www.paradigmasoft.com Valentina SQL Server: The Ultra-fast, Royalty Free Database Server - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Default Encoding In Sqlite
I wrote: > The default storage encoding on disk is UTF-8, but it can be changed > to UTF-16 with a PRAGMA. As Igor reminds me, if you create the database file using sqlite3_open16() the default will be UTF-16 instead. You can still set it explicitly via a PRAGMA if you wish. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] create table with datatype = DATE - STORE as DATETIME or INTEGER.
Joanne Pham wrote: Hi All, Should I create the column in DATETIME or the INTEGER to store the time. DATETIME has the value of GMT time. So I store this value as INTEGER then I need to convert datetime format but it will be use less space if I use the INTEGER. Please give me an advice. See http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions where it says "The julian day number is the preferred internal representation of dates." A Julian day number is a floating point number that can be easily compared and converted to data and time strings for display. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Default Encoding In Sqlite
On 12/14/07, Kalyani Phadke <[EMAIL PROTECTED]> wrote: > Whats default encoding in SQLite3 ? How does SQLite3 handles storing > Japanese/Chinese text in database? I know in SQL express/sql server I > have to use nVarchar/nchar/ntext datatypes to store Japanese/Chinese > text in database. It seems that in SQLite3 column having text datatype > can also store chinese characters.. SQLite assumes TEXT data is Unicode. You can work with it in either UTF-8 by using the *_text() APIs, or UTF-16 using the *_text16() calls. It will convert between the two encodings as necessary. The default storage encoding on disk is UTF-8, but it can be changed to UTF-16 with a PRAGMA. Note that I said it "assumes" the data is in that form. SQLite does not validate the encoding, so it is possible to store text data in some other encoding, like SJIS. You will just get strange results when asking SQLite to convert the data, such as when storing it with *_text(), but retrieving it with *_text16(). The sqlite3 shell is intended to work with UTF-8, but because of the way different platforms handle the terminal/console encodings, it can be difficult to use properly. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Default Encoding In Sqlite
Kalyani Phadke wrote: Whats default encoding in SQLite3 ? Either UTF-8 or UTF-16, depending on whether the database file was originally created with sqlite3_open or sqlite3_open16. How does SQLite3 handles storing Japanese/Chinese text in database? With aplomb and flair. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
[EMAIL PROTECTED] wrote: Dennis Cote <[EMAIL PROTECTED]> wrote: It could be any application that uses a fixed set of predetermined SQL statements to perform its operations. My primary application does exactly that using SQLite with all statements prepared and cached as needed. It runs on a standard PC under Windows. We have no need for executing arbitrary, runtime generated, or user supplied SQL as many other applications do. Every SQL statement that can be executed is known before the application starts. Your application uses a fixed set of SQL statements now. But that set of statements might change in the next release. Or you might change or modify a table, or add an index. A key feature SQLite is that these changes preserve the file format. With other systems, when you change the schema the file format changes with it. Yes, of course. We can also generally use SQL to "upgrade" existing database files to the new schema quite easily using SQLite. One of the ideas I try to push is SQLite as an Application File Format. The idea is that you put your information in an SQLite database file and it is then readable by diverse, general-purpose tools, and across multiple releases. Sure, the schema might change from one release to the next, but the data is still easily accessible. The biggest problem I see with this concept right now is that if you use any custom functions or collations you really can't work with your database using diverse, general purpose tools. If you have triggers or views that call these custom functions then you have difficulties accessing the data from any other application, especially third party database browsers etc. That is the principal reason I think it would be very useful to extend the base set of functions and collations that are universally available to all applications using SQLite to access a database file. Things like graphical browser applications, where code size really isn't an issue, would always include the full set of functions. Users on space restricted embedded systems could easily eliminate the functions they aren't using to minimize the code space for their targets. The size of the SQLite shared library or DLL is normally not a concern on workstations with ram measured in GB and disks that are on the order of TB. Custom functions are a great idea, but it would be better to have a wider selection of standard functions so that users only needed to resort to custom functions in rare cases (or at least less often). In order to promote this idea, we are very careful to make sure that the SQLite file format does not change in ways that are not backwards compatible. Our goal is to make sure that the information you put into an SQLite database today is still easily accessible using general-purpose tools after 20 or 30 years. Further to this goal, watch for detailed specficiations of the SQLite file format to appear in 2008. I'm glad to hear that, and look forward to seeing it. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Default Encoding In Sqlite
Whats default encoding in SQLite3 ? How does SQLite3 handles storing Japanese/Chinese text in database? I know in SQL express/sql server I have to use nVarchar/nchar/ntext datatypes to store Japanese/Chinese text in database. It seems that in SQLite3 column having text datatype can also store chinese characters.. Thanks, -Kalyani - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] create table with datatype = DATE - STORE as DATETIME or INTEGER.
Hi All, Should I create the column in DATETIME or the INTEGER to store the time. DATETIME has the value of GMT time. So I store this value as INTEGER then I need to convert datetime format but it will be use less space if I use the INTEGER. Please give me an advice. Thanks Joanne - Original Message From: John Stanton <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, December 13, 2007 3:05:52 PM Subject: Re: [sqlite] create table with datatype = DATE The type DATE is a declared type, not an actual type and has no effect u nless your code specifically picks it out as a declared type. To do what you want use a trigger on insert and update the date field with datetime('now'); Joanne Pham wrote: > Hi All, > I create the table as : >create table test (name varchar(30), createDate DATE default > DATETIME('NOW')); > but I got the error message. I want to have the default as now if it is not > specify. > Thanks in advance, > Joanne > > > > > Never miss a thing. Make Yahoo your home page. > http://www.yahoo.com/r/hs - To unsubscribe, send email to [EMAIL PROTECTED] - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Re: [sqlite] DeviceSQL
"Noah Hart" <[EMAIL PROTECTED]> wrote: > Why can't new OP codes be simply added to the end, so that the > serializations will always work? > We do not often add new opcodes. The usual change is to alter the meaning and operation of existing opcodes. We also delete existing opcodes about as often as we add new ones. Furthermore, the opcode numbers are not chosen haphazardly. The opcode numbers are in many cases carefully aligned with token numbers in the parser in order to make the code generate smaller. And opcodes that are often used together within switch statements are grouped together numerically so that the jump tables used to implement the switch() statements are smaller - resulting in a smaller executable. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?
3.5.4 -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
On Fri, Dec 14, 2007 at 04:20:15PM +, [EMAIL PROTECTED] wrote: > The EXPLAIN option generates human-readable assembly. We > use EXPLAIN extensively when debugging and enhancing. Yes, but there's no assembler for EXPLAIN output, is there? Also, the only way to deal with EXPLAIN is to step through its results -- there's no way to directly insert its output rows into a temporary table and then use a select using group_concat() to generate a single string consisting of the VDBE assembly. It is, however, possible to use .mode insert and explain to generate INSERT statements that can then be used to insert the VDBE program into a table, and then one can do more interesting things (I suppose one could even write an assembler in SQL). > I should point out that while we work very hard to keep the > file format for SQLite stable, we make no such effort with > the VDBE. The opcodes can and do change significantly between > point releases. Hence the serializations generated by one > version of SQLite can only be interpreted by that exact same > version of SQLite. Right. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] DeviceSQL
Why can't new OP codes be simply added to the end, so that the serializations will always work? Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 8:20 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DeviceSQL Importance: High Case in point: A new opcode has been added for the pending 3.5.4 release. And this caused many of the other opcodes to be automatically renumbered. A serialization created for 3.5.3 has little hope of running on 3.5.4. -- D. Richard Hipp <[EMAIL PROTECTED]> CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
Nicolas Williams <[EMAIL PROTECTED]> wrote: > On Fri, Dec 14, 2007 at 03:38:17PM +, [EMAIL PROTECTED] wrote: > > That would be the Serialized Statement Extension, SSE. > > The SSE provides the programmer with two new APIs: > > Would it be useful to generate human-readable VDBE "assemply"? > > Or, how do you develop parser changes that involve new VDBE code > patterns? > The EXPLAIN option generates human-readable assembly. We use EXPLAIN extensively when debugging and enhancing. I should point out that while we work very hard to keep the file format for SQLite stable, we make no such effort with the VDBE. The opcodes can and do change significantly between point releases. Hence the serializations generated by one version of SQLite can only be interpreted by that exact same version of SQLite. Case in point: A new opcode has been added for the pending 3.5.4 release. And this caused many of the other opcodes to be automatically renumbered. A serialization created for 3.5.3 has little hope of running on 3.5.4. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
On Fri, Dec 14, 2007 at 10:10:04AM -0600, Nicolas Williams wrote: > On Fri, Dec 14, 2007 at 03:38:17PM +, [EMAIL PROTECTED] wrote: > > That would be the Serialized Statement Extension, SSE. > > The SSE provides the programmer with two new APIs: > > Would it be useful to generate human-readable VDBE "assemply"? I meant "assembly". > Or, how do you develop parser changes that involve new VDBE code > patterns? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
On Fri, Dec 14, 2007 at 03:38:17PM +, [EMAIL PROTECTED] wrote: > That would be the Serialized Statement Extension, SSE. > The SSE provides the programmer with two new APIs: Would it be useful to generate human-readable VDBE "assemply"? Or, how do you develop parser changes that involve new VDBE code patterns? Just curious, Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
Dennis Cote <[EMAIL PROTECTED]> wrote: > > It could be any application that uses a fixed set of predetermined SQL > statements to perform its operations. My primary application does > exactly that using SQLite with all statements prepared and cached as > needed. It runs on a standard PC under Windows. We have no need for > executing arbitrary, runtime generated, or user supplied SQL as many > other applications do. Every SQL statement that can be executed is known > before the application starts. > Your application uses a fixed set of SQL statements now. But that set of statements might change in the next release. Or you might change or modify a table, or add an index. A key feature SQLite is that these changes preserve the file format. With other systems, when you change the schema the file format changes with it. One of the ideas I try to push is SQLite as an Application File Format. The idea is that you put your information in an SQLite database file and it is then readable by diverse, general-purpose tools, and across multiple releases. Sure, the schema might change from one release to the next, but the data is still easily accessible. In order to promote this idea, we are very careful to make sure that the SQLite file format does not change in ways that are not backwards compatible. Our goal is to make sure that the information you put into an SQLite database today is still easily accessible using general-purpose tools after 20 or 30 years. Further to this goal, watch for detailed specficiations of the SQLite file format to appear in 2008. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
On Dec 14, 2007 10:38 AM, <[EMAIL PROTECTED]> wrote: > That would be the Serialized Statement Extension, SSE. > The SSE provides the programmer with two new APIs: > >int sqlite3_serialize(sqlite3_stmt*, void**, int*); >int sqlite3_deserialize(sqlite3*, void*, int, sqlite3_stmt**); > > The first routine takes an SQL statement that was generated by > sqlite3_prepare() and converts it into a form that can be stored > on disk or compiled into a program. The second routine does the > reverse; it takes the serialization of a statement and converts it > back into a working SQL statement that can be used just like any > other statement created by sqlite3_prepare(). > > You compile SQLite normally on your development workstation, but > for you embedded target you add -DSQLITE_OMIT_PARSER to leave off > the parser. By omitting other optional features (date/time functions, > views, triggers) you can get the size of the library down to the 70KiB > range or less. > > On a workstation, you can sqlite3_prepare() statements, then hand > them to sqlite3_serialize(). The results can be hard coded into > C programs to be manually deserialized later, if you like, though > that is a lot of work. A simpler approach is to use the special > sqlite_statement table: > >CREATE TABLE sqlite_statement( > id INTEGER PRIMARY KEY, > sql TEXT, > serial BLOB >); > > A new API is available that will automatically extract and deserialize > an SQL statement from the sqlite_statement table given its id number: > >int sqlite3_fetch_statement(sqlite3*, int id, sqlite3_stmt**); > > The idea here is that the SQL statements needed by an application can > be inserted as plain text into the sqlite_statement table. For > example: > >INSERT INTO sqlite_statement(sql) VALUES('SELECT * FROM table1'); > > After many such statements are inserted, they can all be serialized > as follows: > >UPDATE sqlite_statement SET serial = sqlite_serialize(sql,id); > > Then the complete database can be moved from the development platform > over to the embedded device and the embedded device can use the > sqlite3_fetch_statement() API to extract the statements it needs to > execute. > > To be useful, your precompiled statements will normally contain > parameters (ex: "INSERT INTO tx VALUES(?,?,?)") and the embedded > application will using sqlite3_bind_xxx() interfaces to attach > values to these parameter prior to invoking sqlite3_step(). > > The SSE has not been kept current with the base SQLite. But if there > is interest, we could resurrect it easily enough. I think that Mozilla may find that useful. I think it came up about a month ago with some places code (new bookmark back-end). Seth, I think it was you and Mano that were talking about this. Would this be useful for us? Cheers, Shawn > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?
Dr Gerard Hammond wrote: (2) If an ORDER BY term is a simple identifer (like "x", not "x.y" and not "x.y.z") and if there if the k-th column uses that same identifer as an AS alias, the sort by the k-th column. CREATE TABLE a(x,y); INSERT INTO a VALUES(1,8); INSERT INTO a VALUES(9,2); SELECT x AS y FROM a ORDER BY y; I don't understand. If I say "ORDER BY y" aren't I saying sort the result set based on the column " as y" of the result set, not the table "a.y"? ie they should come out y 9 1 Your description is correct, but your output is in the wrong order. The default sort order is ascending so the 9 will sort after the 1. Y 1 9 Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
Dennis Cote <[EMAIL PROTECTED]> wrote: > > > I can imagine that a version of Sqlite which does not include its SQL > > compiler and which uses precompiled VDBE code would provide similar > > functionality to DeviceSQL, particularly if the Sqlite compiler were > > extended to generate VDBE from PL/SQl. I can imagine that the higher > > information density of the VDBE code could deliver the advantage =of a > > smaller memory footprint. > > I thought that this already existed as a commercial product that Richard > offered, but I can't find any reference to it on the paid support page > at http://www.hwaci.com/sw/sqlite/prosupport.html. My recollection was > an offline compiler that ran on a PC and generated VDBE code to execute > SQL statements, along with source code for a runtime execution engine > that would execute those pre-compiled statements. The execution engine > could be built for any target, and was much smaller than SQLite because > it eliminated the parser and code generator functionality. Does this > exist, or was I just imagining it? > That would be the Serialized Statement Extension, SSE. The SSE provides the programmer with two new APIs: int sqlite3_serialize(sqlite3_stmt*, void**, int*); int sqlite3_deserialize(sqlite3*, void*, int, sqlite3_stmt**); The first routine takes an SQL statement that was generated by sqlite3_prepare() and converts it into a form that can be stored on disk or compiled into a program. The second routine does the reverse; it takes the serialization of a statement and converts it back into a working SQL statement that can be used just like any other statement created by sqlite3_prepare(). You compile SQLite normally on your development workstation, but for you embedded target you add -DSQLITE_OMIT_PARSER to leave off the parser. By omitting other optional features (date/time functions, views, triggers) you can get the size of the library down to the 70KiB range or less. On a workstation, you can sqlite3_prepare() statements, then hand them to sqlite3_serialize(). The results can be hard coded into C programs to be manually deserialized later, if you like, though that is a lot of work. A simpler approach is to use the special sqlite_statement table: CREATE TABLE sqlite_statement( id INTEGER PRIMARY KEY, sql TEXT, serial BLOB ); A new API is available that will automatically extract and deserialize an SQL statement from the sqlite_statement table given its id number: int sqlite3_fetch_statement(sqlite3*, int id, sqlite3_stmt**); The idea here is that the SQL statements needed by an application can be inserted as plain text into the sqlite_statement table. For example: INSERT INTO sqlite_statement(sql) VALUES('SELECT * FROM table1'); After many such statements are inserted, they can all be serialized as follows: UPDATE sqlite_statement SET serial = sqlite_serialize(sql,id); Then the complete database can be moved from the development platform over to the embedded device and the embedded device can use the sqlite3_fetch_statement() API to extract the statements it needs to execute. To be useful, your precompiled statements will normally contain parameters (ex: "INSERT INTO tx VALUES(?,?,?)") and the embedded application will using sqlite3_bind_xxx() interfaces to attach values to these parameter prior to invoking sqlite3_step(). The SSE has not been kept current with the base SQLite. But if there is interest, we could resurrect it easily enough. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
John Stanton wrote: DeviceSQL is not suitable for general purpose SQL processing, unlike Sqlite, and should only be compared as an alternative in deeply embedded applications so the only useful comparison is one which looks like a cell phone, microwave oven or a TV set top box. The application doesn't necessarily have to look like a microwave. :-) It could be any application that uses a fixed set of predetermined SQL statements to perform its operations. My primary application does exactly that using SQLite with all statements prepared and cached as needed. It runs on a standard PC under Windows. We have no need for executing arbitrary, runtime generated, or user supplied SQL as many other applications do. Every SQL statement that can be executed is known before the application starts. I can imagine that a version of Sqlite which does not include its SQL compiler and which uses precompiled VDBE code would provide similar functionality to DeviceSQL, particularly if the Sqlite compiler were extended to generate VDBE from PL/SQl. I can imagine that the higher information density of the VDBE code could deliver the advantage =of a smaller memory footprint. I thought that this already existed as a commercial product that Richard offered, but I can't find any reference to it on the paid support page at http://www.hwaci.com/sw/sqlite/prosupport.html. My recollection was an offline compiler that ran on a PC and generated VDBE code to execute SQL statements, along with source code for a runtime execution engine that would execute those pre-compiled statements. The execution engine could be built for any target, and was much smaller than SQLite because it eliminated the parser and code generator functionality. Does this exist, or was I just imagining it? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and Columnar Databases
This is probably do to the cache locality problem. We know how to fix this, Joe. Would you like to have a go at it? What is the fix to the "cache locality problem"? Michael -- http://MichaelScharf.blogspot.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite and Columnar Databases
> This model is completely removed from how the data is physically > stored, eg whether in rows first or in columns first, and the > physical store is determined just by the DBMS behind the scenes, and > hence is an implementation detail. The DBMS can arrange how it likes > in order to satisfy the logical model in a way that performs well. > The users do not contort their schemas beyond what is logically clean > in order to gain performance; having a clean schema will let the DBMS > infer this automatically. This may be true in theory, but if it were really true in practice, why would anyone bother building databases with different storage models? How a database does what it does impacts everything from where you should use it to how you design your schema to how you write your queries. And if you think that no one contorts their schemas to make their queries run faster then, umm... Well, I don't think I need to say anything else about that. > According to some comments, Vertica (a column-store maker) is making > the same case that I am, which is just to have a logical clean > schema, and performance benefits will automatically follow from that. They say that because their system performs better when your schema is simpler. It's not just a philosophical belief that simpler is better. > I will also note that a column-based store essentially works like a > heavily indexed row-based store, in which there is an index on every > key or every column, and so all searches, which includes those on > which joins are performed, can/do look in what is otherwise indexes. Conceptually I agree, though I do not think you could build an equally-effective system by indexing a row store. > This is potentially slower for updates (or maybe not), but can be > faster for queries, depending on circumstances. But... But... How the data is stored is an implementation detail that users don't need to worry about, right? So who cares if updates are slower? The logical model is the same, right? Thank you for making my point. :) -T - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
I have to say, this discussion has been very informative, although probably not in a way that would make mr Weick happy. I've certainly learned a lot about encirq that tells me what I need to know about doing business with them. Clay steveweick wrote: > > Good idea... I'll pass it along to the right folks. Meanwhile, if anyone > has > further questions or comments, please feel free to write me here (if they > think the group would be interested) or at [EMAIL PROTECTED] > > Steve > > I would like to recommend that Encriq create a forum or mailing list of > their own for those who are interesting in learning more. For me, what > might be an interesting product is quickly being overshadowed by this > thread. > > > -- > View this message in context: > http://www.nabble.com/DeviceSQL-tp14297970p14329799.html > Sent from the SQLite mailing list archive at Nabble.com. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Lazarus Registration http://www.lazarusid.com/registration.shtml - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] Problems Insert with Date and Time values
Thank you again for all you work.. Giuliano - Original Message - From: "Ion Silvestru" <[EMAIL PROTECTED]> To: "Giuliano" Sent: Friday, December 14, 2007 10:08 AM Subject: Re[2]: [sqlite] Problems Insert with Date and Time values It seems that "BETWEEN" in SQLite is treated as "BETWEEN..AND selects fields that are between and including the test values", so it can be used instead of ">=" and "<=". - To unsubscribe, send email to [EMAIL PROTECTED] -
Re[3]: [sqlite] Problems Insert with Date and Time values
>It seems that "BETWEEN" in SQLite is treated as "BETWEEN..AND selects >fields that are between and including the test values", so it can be >used instead of ">=" and "<=". I am sorry, but it seems I am partially correct. I tested again and concluded: 1. "BETWEEN" in SQLite is treated as "BETWEEN..AND selects fields that are between and including the test values", so it can be used instead of ">=" and "<=", ONLY if you specify COMPLETE test values, example "20071001" AND "20071131". 2. If you specify PARTIAL test values, example "200710" AND "200711", then "BETWEEN" in SQLite is treated as "BETWEEN..AND selects fields between the test values, including the first test value and excluding the last test value". 3. It is not reliable to use "BETWEEN" for now (and not only in SQLite, but also in other DBMS), better use >= and <=. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re[2]: [sqlite] Problems Insert with Date and Time values
It seems that "BETWEEN" in SQLite is treated as "BETWEEN..AND selects fields that are between and including the test values", so it can be used instead of ">=" and "<=". - To unsubscribe, send email to [EMAIL PROTECTED] -
Re[2]: [sqlite] Problems Insert with Date and Time values
I avoided "BETWEEN" because this operator is treated differently in different databases, and I don't tested how it is treated in SQLite. http://www.w3schools.com/sql/sql_between.asp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problems Insert with Date and Time values
Thanks a lot! any suggestion for a query then that read and compare dates, for ex.: SELECT somefiled FROM sometable WHERE datefield1 BETWEEN ' '? AND ' '? How can I query then the string values as date? Thanks again Giuliano - Original Message - From: "Ion Silvestru" <[EMAIL PROTECTED]> To: "Giuliano" Sent: Friday, December 14, 2007 8:55 AM Subject: Re: [sqlite] Problems Insert with Date and Time values >is it possible (how?) to insert into 2 different fields (date) the >following values: 09:30:00(only a time value...) 14/07/07 (only year value, 14th of december 2007) Just store date and/or time as string: "20070714" or "070714", "093000". It is compact, you can sort them easily etc. You can store them also as Integer: 20070714, 93000, this will save space in database, as integers are stored in variable-length format (VarInt). - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -