[sqlite] ANN: New version of Sqlite3Explorer available
Some small but usefull changes implemented, as per user request: get latest version here : http://www.singular.gr/sqlite/ Mike Cariotoglou Disclaimer Notice: This communication may be confidential. If you are not an intended recipient please note that any form of distribution, copying or use of this communication or the information in it, is prohibited. Please inform the sender appropriately and delete or destroy any copies of it from your system. SingularLogic SA & SingularLogic Integrator SA cannot accept any responsibility for the accuracy or completeness of this message as it has been transmitted over a public network. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug when uUsing Parameters with views
Just an idea : Parameters bound via sql_bind... *should* have affinity, since they are manifestly typed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug when uUsing Parameters with views
Quoting from the documentation on Type Affinity: " SQLite may attempt to convert values between the numeric storage classes (INTEGER and REAL) and TEXT before performing a comparison. Whether or not any conversions are attempted before the comparison takes place depends on the nominal affinity assigned to the expressions on either side of the binary operator. Affinities are assigned to expressions in the following cases: An expression that is a simple reference to a column value has the same affinity as the column it refers to. Note that if X and Y.Z are column names, then +X and +Y.Z are considered expressions. An expression of the form "CAST( TO )" is assigned an affinity as if it were a reference to a column declared with type Conversions are applied before the comparison as described below. In the following bullet points, the two operands are refered to as expression A and expression B. Expressions A and B may appear as either the left or right operands - the following statements are true when considering both "A B" and "B A". When two expressions are compared, if expression A has INTEGER or REAL or NUMERIC affinity and expression B does not, then NUMERIC affinity is applied to the value of expression B before the comparison takes place. When two expressions are compared, if expression A has been assigned an affinity and expression B has not, then the affinity of expression A is applied to the value of expression B before the comparison takes place. Otherwise, if neither of the above applies, no conversions occur. The results are compared as is. If a string is compared to a number, the number will always be less than the string. " since, in the examples posted, (select count(*) from ...) should be an expression, and since it is compared to a value, it should take on the affinity of the value, no ? AH, GOT IT. values do NOT have affinity. so I would either need to cast the expression OR the value. is this correct ? > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov > Sent: Tuesday, December 15, 2009 5:38 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Bug when uUsing Parameters with views > > Apparently result of count() and probably all other aggregate > functions (as well as result of any function at all) has no affinity. > So when you compare it to anything having no affinity too you > have no type transformation during comparison. And thus > values have to be exactly the same including their types... > > Everything is explainable and probably is impossible to fix > unless core aggregate functions become specially recognizable > part of the core or SQLite team figures out how to make > functions return their results with some affinity assigned... > > Pavel > > On Tue, Dec 15, 2009 at 10:25 AM, Simon Davies > wrote: > > 2009/12/15 D. Richard Hipp : > >> > >>> > >> > >> Because string '2' is not the same thing as integer 2. > >> > >> sqlite3> select 2='2'; > >> 0 > >> sqlite3> > >> > > > > Why > > sqlite> select cast( 2 as integer ) = '2'; > > 1 > > > >> > >> D. Richard Hipp > >> d...@hwaci.com > >> > > > > Simon > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug when uUsing Parameters with views
Simon, thanks for the answer. your code helped track down the issue, which I *still* believe to be a bug: The issue is not with parameters, after all, but with comparison of values, and something "magic" about count(), I suspect. It has to do with the fact that the layer I am using (PHP PDO) actually binds parameters as strings by defult. in general, this should not be an issue, since sqlite seems to be quite relaxed wrt to typing. However, in this case, it matters. The "bug" is now reproducible easily: give the bas SQL statmenent: select * from (select *," (select count(*) from ITEM_ARTIST where ARTIST_id=artists.artist_id) CNT from ARTISTS ) if you apply this WHERE : ' where artist_id=1' it works if you quote the value (making it a string), it still works : where artist_id='1' if you supply a value for CNT as integer, it works : where CNT=1 if you supply the value for CNT as string, it BREAKS: where CNT='1' Further investigation shows that the issue is somehow related to how sqlite compares values, and affinity. It seems that, while the rules defined in the documentation for comparison operators work as expected in most cases, something breaks in the case of pseudo-columns that are the result of a sub-select (like the case above, for CNT). If I use CAST to define an explicit affinity for the expression, it starts working : select * from (select *," cast((select count(*) from ITEM_ARTIST where ARTIST_id=artists.artist_id) as int) CNT from ARTISTS ) now, any WHERE clause works as expected. Ideas ? > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Davies > Sent: Tuesday, December 15, 2009 1:58 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Bug when uUsing Parameters with views > > 2009/12/15 Cariotoglou Mike : > > I checked in the bug database, and this does not seem to have been > > reported, and IMHO it is definitely a bug. > > workarounds exist,as pointed out by me and others. still, I > would like > > to hear from the core team whether this is recognized as a bug, and > > will be dealt with at some point in time. > > btw, I would have liked to post a script demonstrating the > bug, but I > > do not think this is possible, due to the fact that the > command-line > > sqlite does not handle parametric statements, or at least I > don't know > > how to write one :) > > > > I am unable to reproduce your problem (version 3.6.11): > > // > // create table item_artist( artist_id integer, data text ); > // create table artists( artist_id integer, data text ); // > insert into artists values( 1, 'a1_pic1' ); // insert into > artists values( 2, 'a1_pic2' ); // insert into item_artist > values( 1, 'a1_item1' ); // insert into item_artist values( > 2, 'a2_item1' ); // insert into item_artist values( 2, 'a2_item2' ); > > #include "stdafx.h" > #define SQLITE_PRIVATE > #include "sqlite3.c" > > int _tmain(int argc, _TCHAR* argv[]) > { > int cnt; > int dbStatus; > sqlite3* dbH; > sqlite3_stmt* stmt; > dbStatus = sqlite3_open( "tstBind.db", &dbH ); > > while( fscanf( stdin, "%d", &cnt ) ) > { > char* sql = "select * from ( select *," > "(select count(*) from ITEM_ARTIST where " > "ARTIST_id=artists.artist_id) CNT from > ARTISTS ) where " > "cnt = :a;"; > > const char* tail; > > if( 0 == cnt ) > { > exit(0); > } > > dbStatus = sqlite3_prepare_v2( dbH, sql, > strlen( sql ), &stmt, &tail ); > if( SQLITE_OK != dbStatus ) > { > printf( "%s\n", sqlite3_errmsg( dbH ) ); > } > > dbStatus = sqlite3_bind_int( stmt, 1, cnt ); > if( SQLITE_OK != dbStatus ) > { > printf( "%s\n", sqlite3_errmsg( dbH ) ); > } > > while( SQLITE_ROW == ( dbStatus = sqlite3_step( > stmt ) ) ) > { > printf( "%s ", sqlite3_column_text( stmt, 0 ) ); > printf( "%s ", sqlite3_column_text( stmt, 1 ) ); > printf( "%s\n", sqlite3_column_text( > stmt, 2 ) ); > } > sqlite3_finalize( stmt ); > } > sqlite3_close( dbH ); > > return 0; > } > > On executing if I enter 1 I get > 1 > 1 a1_pic1 1 > > and if 2, then > 2 > 2 a2_pic1 2 > > which all looks ok > > Regards, > Simon > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug when uUsing Parameters with views
I checked in the bug database, and this does not seem to have been reported, and IMHO it is definitely a bug. workarounds exist,as pointed out by me and others. still, I would like to hear from the core team whether this is recognized as a bug, and will be dealt with at some point in time. btw, I would have liked to post a script demonstrating the bug, but I do not think this is possible, due to the fact that the command-line sqlite does not handle parametric statements, or at least I don't know how to write one :) > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Romano > Sent: Tuesday, December 15, 2009 2:37 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Using Parameters with views > > I know the original question was about views and parameters, > but if the OP just needs the answer to this kind of query and > a view isn't mandatory, I've been able to use a parameter > with the HAVING clause. > The query could be rewritten: > > select artistname > from artist > join ITEM_ARTIST on artist.artistid = ITEM_ARTIST.artistid > group by artistname having count(ITEM_ARTIST.artistid) = > @desireditemcount > > Regards > Tim Romano > > Cariotoglou Mike wrote: > > I thought of that, and the answer is NO. > > actually, since my post, I did a little investigation : > > it is not the view that is the problem, it is the parameter > when used > > on a column that contains the COUNT function. Weird, it > seems like a genuine bug. > > > > if you replace > > > > WHERE CNT LIKE :PARAM > > > > in my original code, or in your example, it will work ! > > > > I tried this : (the subselect was originally the view) > > > > select * from > > ( > > select > > *,(select count(*) from ITEM_ARTIST where > ARTIST_id=artists.artist_id) > > CNT from ARTISTS > > ) where cnt like :a > > > > and it works. > > changing the operator from "LIKE" to "=" breaks it. > > > > > > -Original Message- > > From: sqlite-users-boun...@sqlite.org on behalf of Darren Duncan > > Sent: Mon 12/14/2009 2:44 AM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Using Parameters with views > > > > Do bind parameters work for you if you replace the view > with a subselect? > > > > Does this work for you? > > > > select * from ( > >select t1.*, > >(select count(*) from song_artist t2 where > t1.artist_id=t2.artist_id) as CNT > >from artists > > ) where CNT=:PARAM > > > > -- Darren Duncan > > > > Cariotoglou Mike wrote: > > > >> I don't know if this has come up before, is so please > point me to the > >> right direction :) > >> > >> I believe that using parameterized queries with views does > not work > >> as expected. > >> > >> consider this (more or less self-explanatory) schema: > >> > >> create table artists(artist_id) > >> create table songs(song_id) > >> create table song_artist(song_id,artist_id) > >> > >> create view VARTISTS as > >> select t1.*, > >> (select count(*) from song_artist t2 where > >> t1.artist_id=t2.artist_id) as CNT from artists > >> > >> The sql may be a little off as I am typing this from scratch, but > >> you get the idea. > >> > >> now, if I do this: > >> > >> select * from VARTISTS where CNT=10 > >> > >> it works. > >> > >> This, however, always returns an empty result set : > >> > >> select * from VARTISTS where CNT=:PARAM, > >> > >> for any value I bind to :PARAM. > >> > >> is this known/documented ? > >> > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > -- > > -- > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > > -- > > > > > > No virus found in this incoming message. > > Checked by AVG - www.avg.com > > Version: 8.5.427 / Virus Database: 270.14.106/2563 - Release Date: > > 12/13/09 19:47:00 > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using Parameters with views
I thought of that, and the answer is NO. actually, since my post, I did a little investigation : it is not the view that is the problem, it is the parameter when used on a column that contains the COUNT function. Weird, it seems like a genuine bug. if you replace WHERE CNT LIKE :PARAM in my original code, or in your example, it will work ! I tried this : (the subselect was originally the view) select * from ( select *,(select count(*) from ITEM_ARTIST where ARTIST_id=artists.artist_id) CNT from ARTISTS ) where cnt like :a and it works. changing the operator from "LIKE" to "=" breaks it. -Original Message- From: sqlite-users-boun...@sqlite.org on behalf of Darren Duncan Sent: Mon 12/14/2009 2:44 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Using Parameters with views Do bind parameters work for you if you replace the view with a subselect? Does this work for you? select * from ( select t1.*, (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id) as CNT from artists ) where CNT=:PARAM -- Darren Duncan Cariotoglou Mike wrote: > I don't know if this has come up before, is so please point me to the > right direction :) > > I believe that using parameterized queries with views does not work > as expected. > > consider this (more or less self-explanatory) schema: > > create table artists(artist_id) > create table songs(song_id) > create table song_artist(song_id,artist_id) > > create view VARTISTS as > select t1.*, > (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id) > as CNT > from artists > > The sql may be a little off as I am typing this from scratch, but you > get the > idea. > > now, if I do this: > > select * from VARTISTS where CNT=10 > > it works. > > This, however, always returns an empty result set : > > select * from VARTISTS where CNT=:PARAM, > > for any value I bind to :PARAM. > > is this known/documented ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using Parameters with views
I don't know if this has come up before, is so please point me to the right direction :) I believe that using parameterized queries with views does not work as expected. consider this (more or less self-explanatory) schema: create table artists(artist_id) create table songs(song_id) create table song_artist(song_id,artist_id) create view VARTISTS as select t1.*, (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id) as CNT from artists The sql may be a little off as I am typing this from scratch, but you get the idea. now, if I do this: select * from VARTISTS where CNT=10 it works. This, however, always returns an empty result set : select * from VARTISTS where CNT=:PARAM, for any value I bind to :PARAM. is this known/documented ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to represent a tree in SQL
just to throw in my two bits: I have done a lot of work with trees in SQL, and IMHO, the best method BY FAR is the one described in the link below (mysql article), mainly due to its capability to handle siblings and descendants. for example, the self-join, parent_node method described elsewhere in this list is failr ok for simple requirements, but is completely useles in the following cases: select all_descendans_on_any depth for a particular node find out if a node "belongs" to a parent which is not its immediate parent. in other words, SET operations are quite difficult in the node-parent relation, but very easy and efficient in the adjacent list model. I personally stopped looking for a better solution once I came across and comprehended the power of this method... > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of > Sebastian Bermudez > Sent: Wednesday, October 14, 2009 5:10 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] how to represent a tree in SQL > > > > look this: > > http://dev.mysql.com/tech-resources/articles/hierarchical-data.html > > is for mysql but aplies to every sql DB > > > - Mensaje original > De: Michael Chen > Para: sqlite-users@sqlite.org > Enviado: miƩ, octubre 14, 2009 10:40:45 AM > Asunto: [sqlite] how to represent a tree in SQL > > Dear there, > > I am developing a numerical application, where a single > rooted dynamic tree is the main data structure. I intended to > use SQLite for this purpose and also for other data as well. > However I have no reference how to represent a tree using > tables. I need these functionalities: > (1) basic tree: single root, multi-levels, arbitrary number > of branches, index each node, index each path (from root to a > leaf), lookup parent, lookup descendants > (2) dynamics: delete a path, add a path; maintain parent and > descendants table; maintain history of tree; lookup history > (3) each node has lots of matrix and vectors, which will be > updated with dynamics, and should be tracked > > As you see, it is nontrivial to write a tree structure to > support all these functions, while keep the code clean and > neat. That's why I want to use SQLite to keep things > straight. Is there a good reference on this? > > Michael Chen > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > Yahoo! Cocina > > Encontra las mejores recetas con Yahoo! Cocina. > > > http://ar.mujer.yahoo.com/cocina/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question regarding BCC32
Great. thanks for the info. however, this means that at least part of the runtime library actually comes from the VCL, and not the BCC32 libraries, is that not so ? and, BTW, would you share your code to embed the .obj file ? I know I have done it in the past, and all it takes is to implement the imports, but I dont have that code around anymore, so I would be obliged... From: sqlite-users-boun...@sqlite.org on behalf of John Elrick Sent: Mon 12/10/2009 9:29 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Question regarding BCC32 Cariotoglou Mike wrote: > Has anybody had experience using Borland's free compiler (bcc32) to > build the sqlite dll ? We use bcc32 to create an .obj which is linked into our Delphi application. No issues here and we've been using it for three years on a project deployed to several hundred thousand people. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question regarding BCC32
Has anybody had experience using Borland's free compiler (bcc32) to build the sqlite dll ? I gave it a try, and was surprised to see that I got a file that is 100k smaller than the usual MS visual studio dll I build. I assume this is due to a different size of the run-time library, so what I am asking is, has anybody used it before, and have you noticed any issues with the resulting code ? it *seems* to run fine here, but my compile options were : set cf=-jb -O2 -w- -K -DTHREADSAFE=1 -DNDEBUG -DNO_TCL -DWIN32 -DLIB -DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_RTREE bcc32 %cf% -c sqlite3.c and link options: ilink32 /Tpd /Gn /x C0D32.OBJ sqlite3.obj,sqlite3.dll,,CW32mt.LIB IMPORT32.LIB,sqlite3.bcc.def,sqlite3.res (I use separate link stage in order to link a version resource in, which I do not know how to do otherwise) (also, the .def file needs to be different than the usual MS file, due to the habit of this compiler to generate underscores in public names) Any response will be much appreciated ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3Explorer Sqlite Report Designer
No I am not in vaccation, I wish! I am rather busy this period, so no, I will not be able to do wthat you wanted, for the next couple of months or so... From: sqlite-users-boun...@sqlite.org on behalf of Rajesh Nair Sent: Thu 11/12/2008 8:55 AM To: General Discussion of SQLite Database Subject: [sqlite] Sqlite3Explorer Sqlite Report Designer Dear Cariotoglou Mike, Are you in vaccation? I am attaching the previous emails that I have send. If you are trying or planning to work on the same please inform, so that I can hope that some thing is going to happen -- Forwarded message -- From: Rajesh Nair Date: Thu, Dec 4, 2008 at 8:13 PM Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer To: General Discussion of SQLite Database Dear Cariotoglou Mike, I am using VC++ for last 6 years to develop utilities and some small projects. Formerly I was using MS-ACCESS and/or some database classes in my projects. Once I felt the power of sqlite I started using it from 2003 / 2004. After that I have not used any ADO / ODBC for my databases. I have felt the easyness of the moto "NO CONFIGURATION" of sqlite. I have developed some very use full VC++ classes to handle the sqlite database file. I was producing reports with my-own list-view control. Now some simple formated reports are developed but if any changes in the report will result a total EXE replace. That's why I am trying some external utility that could support my EXE to produce simple formated reports. I noticed that your DLL can open some FR3 or some FRF extention files. THE NEED IS THAT REPORT FORMAT WILL BE SAVED AS AN EXTERNAL FILE OF THE FORMAT THAT YOUR DLL CAN READ AND ALLIGN THE REPORT-VIEWER WINDOW FOR PRINTING. The dataset that you specified can be XML or sqlite database itself. If you are planning to find some time to support sqlite ( as you found some time to develop the Sqlite3Explorer ) then please keep some points in mind while rebuilding the DLL. 1) The dll can be used for both DESIGNING and PRE-VIEWING. 2) The dll can be used to either DESIGNING or PRE-VIEWING according to some parameters passed in to it. ( ie. If the HOST EXE needs only pre-viewing the report then the DESIGNER window must not appear. The designer mode will be shown on demand specified by a param passed to the DLL) If I get such a DLL then I will be using the same for my further projects and will also gradualy replace the method that I was using to show-report. If some others those who are using sqlite with VC++ / VB or some other WIN32 platform programs may try to use it. PLEASE INFORM Thanks Rajesh Nair On Mon, Nov 17, 2008 at 5:18 AM, Cariotoglou Mike <[EMAIL PROTECTED]> wrote: > here is an idea : I might be able to modify the dll to accept datasets in a > different, "standard" format, like XML. > this would imply that your product would need to retrieve data in whatever > format it needs, transform it to my format, > pass it to the dll which will do the design and reporting. it will not as > fast as it is now, but it is definately a solution. you would not have the > source of the dll, but you could use it freely, no IR issues. > > for this to work , the following assumptions must prove correct: > > 1. I originally designed the DLL with portability in mind, so it *coul* be > transformed as I described. since that time, otuehr developers have improved > it. I need to check that they have not introduced dependencies and design > decisions that would nullify its portability. > > 2. that you (or somebody else) actually would benefit from this approach. > f.e do you *like* the report designer (as seen and used from > sqlite3Explorer), or is it just something you came across and thought to > give a try? > > I only point this out, because it will take me at least 10-20 hours to do > this conversion, and it would be a big waste of time (mine) to do this just > as an intellectual exercise. I am not asking for financial compensation > here, just to know that I am actually doing something useful... > > > PS another format that would work, besides XML, and that I already have a > lot of code to support would be the ADO recordset. is this an option for you > ? what language are you planning to use in order to host the DLL, and what > database ? > > > > > From: [EMAIL PROTECTED] on behalf of Rajesh Nair > Sent: Sat 15/11/2008 5:53 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer > > > > Thanks > > So I can't use it... Ok ... But I got some source code of FastReport ( > some > ealier version ) from the internet and it is in Dephi/Pascal. I don't know > both of the tools. C
Re: [sqlite] Sqlite3Explorer Sqlite Report Designer
here is an idea : I might be able to modify the dll to accept datasets in a different, "standard" format, like XML. this would imply that your product would need to retrieve data in whatever format it needs, transform it to my format, pass it to the dll which will do the design and reporting. it will not as fast as it is now, but it is definately a solution. you would not have the source of the dll, but you could use it freely, no IR issues. for this to work , the following assumptions must prove correct: 1. I originally designed the DLL with portability in mind, so it *coul* be transformed as I described. since that time, otuehr developers have improved it. I need to check that they have not introduced dependencies and design decisions that would nullify its portability. 2. that you (or somebody else) actually would benefit from this approach. f.e do you *like* the report designer (as seen and used from sqlite3Explorer), or is it just something you came across and thought to give a try? I only point this out, because it will take me at least 10-20 hours to do this conversion, and it would be a big waste of time (mine) to do this just as an intellectual exercise. I am not asking for financial compensation here, just to know that I am actually doing something useful... PS another format that would work, besides XML, and that I already have a lot of code to support would be the ADO recordset. is this an option for you ? what language are you planning to use in order to host the DLL, and what database ? From: [EMAIL PROTECTED] on behalf of Rajesh Nair Sent: Sat 15/11/2008 5:53 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer Thanks So I can't use it... Ok ... But I got some source code of FastReport ( some ealier version ) from the internet and it is in Dephi/Pascal. I don't know both of the tools. Can you use the same code to build a dll which can be used with other windows programing languages? ( So that no one can raise their finger aganist me or YOU ). I don't want a very efficient-high-end report designer like FastReport. My clients require very simple reports, which does not contain much caculations etc If you can help me please. It won't be just for me. Those who are using SqLite with windows will be happy with a simple report deisgner and viewer. Also my company is not willing to puchase any product for just reporting, since our programs are supplied freely along with some of our products. So an extra cost is not feasible. A SIMPLE DISIGNER/VIEWER FOR WINDOWS USERS - Original Message - From: "Cariotoglou Mike" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Friday, November 14, 2008 7:14 PM Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer >I am the author of sqlite3Explorer. Sorry I did not answer you previous > post, reason is I did not motice it... :( > > I don't think what you want is feasible. the reason is this: the Dll > expects a "dataset" which sqlite3Explorer builds internally, and the > report engine takes it from there (Design, run, export etc). the dll > itself does not contain data retrieval code, and relies on the host for > this. > > this is ok, but the catch is here: the dataset format that the dll > understands is a proprietary format developed by and only used by the > company I work for. the dll engine is one the prime design/execute > engines for our commercial projects. > thus I cannot open-source it. and, since the dataset format in question > cannot be produced without having access to the proprietary libraries > we use here, what you want cannot be done. > > sorry... > >> -Original Message- >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of Rajesh Nair >> Sent: Friday, November 14, 2008 2:12 PM >> To: General Discussion of SQLite Database >> Subject: [sqlite] Sqlite3Explorer Sqlite Report Designer >> >> Hi all >> >> How can I use the mkFrxEngine.DLL supplied along with the >> Sqlite3Explorer. >> The dll is a report designer and viewer. Please help me to >> use that dll if any one knows. I asked the same before 10-14 >> days ago, but didn't get any reply. If even the author of the >> same is reading this please help me. I want to use it as a >> report viewer for my sqlite3 database. >> >> >> -- >> Regards >> Rajesh Nair >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-user
Re: [sqlite] Sqlite3Explorer Sqlite Report Designer
I am the author of sqlite3Explorer. Sorry I did not answer you previous post, reason is I did not motice it... :( I don't think what you want is feasible. the reason is this: the Dll expects a "dataset" which sqlite3Explorer builds internally, and the report engine takes it from there (Design, run, export etc). the dll itself does not contain data retrieval code, and relies on the host for this. this is ok, but the catch is here: the dataset format that the dll understands is a proprietary format developed by and only used by the company I work for. the dll engine is one the prime design/execute engines for our commercial projects. thus I cannot open-source it. and, since the dataset format in question cannot be produced without having access to the proprietary libraries we use here, what you want cannot be done. sorry... > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Rajesh Nair > Sent: Friday, November 14, 2008 2:12 PM > To: General Discussion of SQLite Database > Subject: [sqlite] Sqlite3Explorer Sqlite Report Designer > > Hi all > > How can I use the mkFrxEngine.DLL supplied along with the > Sqlite3Explorer. > The dll is a report designer and viewer. Please help me to > use that dll if any one knows. I asked the same before 10-14 > days ago, but didn't get any reply. If even the author of the > same is reading this please help me. I want to use it as a > report viewer for my sqlite3 database. > > > -- > Regards > Rajesh Nair > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.4 planned for 2008-10-15
well, since you asked, BNF is indeed more difficult to read, BUT it is machine-readable, which means validation tools and parsers can be built. so, if you did have the BNF grammar *available* (as opposed to part of the web documentation), I personally would be happier. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] request for information
Hi all, and DRH : I am the author of sqlite3Explorer, a windows Gui management tool. One of the users of this program has contacted me wrt to supporting encrypted databases. in the past, Sqlite3Explorer has supported encrypted databases (version 2 of sqlite), even though I do not own a copy of the encrypted source (see), because (in the past, at least), it was not necessary, as long as the sqlite3.dll was compiled with SEE enabled. I just made the proper calls to sqlite3_key, and it worked. I am now told that this does not work anymore, and I assume that something has changed between version 2 and version 3 of sqlite, which breaks my implementation. It fails with an error 21 (Improper use of API). I would like to fix this, but I am unable to do so, unless I have access to (some part) of the commercial version, and an understanding of the encryption initialization (key format, how to select which of the 4 encryption methods is used, etc). This request is aimed mainly towards DRH : is it possible to send me the documentation (as a minimum) of the SEE extension (and CEROD, if relevant), so that I can implement the corect calling sequence to open such databases ? The alternative would be for me to buy the source for these, which is a little over my budget for now :) I would like to assure you that I personally have no use for encrypted databases, I am only asking for this in order to support people that use my (freeware) utility... of course, if you are feeling generous, you could also consider giving me a free copy :), since I *do* contribute to sqlite, at least indirectly. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
RE: [sqlite] Casting bug
well, first of all you must have a typo, since the sql you show will return 14170, not 0.69 or anything like it. however, one thing springs out: Total * 100 + 100 is wrong IMHO, unless you are looking for CEILING functionallity. "round" would need : total * 100 +50 (which rounds to nearest integer at two decimal points, not to the nearest LARGER integer, which is what your sample does) > -Original Message- > From: T&B [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 13, 2007 4:27 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Casting bug > > When I try: > > select cast( 141.70 * 100 as integer) > > I get 0.69, but should get 0.70 > > What's the problem? Seems like a bug. > > I tried some other numbers in place of 141.70, and they > worked OK, though I imagine there are others that have the > bug that I just haven't tried. > > The above is the isolated buggy part of a formula I use to > round of real amounts to two fixed decimal places: > > select > substr( > ' $' || cast( Total as integer ) || '.' || substr( cast( Total * 100 + 100 as > integer ), -2, 2 ) > , -10, 10 > ) > from ( select 141.70 as Total ) > ; > > which gives $141.69 but should give $141.70 > > Is there a more reliable way, using only SQLite? > > Thanks, > Tom > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Sqlite3Explorer Version 3.0 is available
will do asap > -Original Message- > From: Miha Vrhovnik [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 29, 2007 5:15 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Sqlite3Explorer Version 3.0 is available > > Hi Mike, > > Thanks for updated version, but still no drag n drop support > for opening database files :( .... > > Regards, > Miha > > "Cariotoglou Mike" <[EMAIL PROTECTED]> wrote on 29.11.2007 9:09:59: > >New version with a lot of enhancements to the user interface, also > >updated to be compatible with the latest sqlite dll. > > > >information, change list and download here : > > > >http://www.singular.gr/sqlite > > > >(New features are described in the link near the top). > > > >Documentation, such as it is, is out of date :) > > > > > >- > -- > >-- To unsubscribe, send email to > >[EMAIL PROTECTED] > >- > -- > >-- > > > > > > > -- > It's time to get rid of your current e-mail client ... > ... and start using si.Mail. > > It's small & free. ( http://simail.sourceforge.net/ ) > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Sqlite3Explorer Version 3.0 is available
New version with a lot of enhancements to the user interface, also updated to be compatible with the latest sqlite dll. information, change list and download here : http://www.singular.gr/sqlite (New features are described in the link near the top). Documentation, such as it is, is out of date :) - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite icon
DRH : do you mind if I usurp the favicon.ico from the sqlite site, to use in my application ? thanks, mike - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite3Explorer
maybe... otoh, much of the thing's perceived value comes from things like the datagrid functionality. this is DevExpress, and somehow I doubt they have a version for lazarus. still, might have a look... From: John Elrick [mailto:[EMAIL PROTECTED] Sent: Mon 11/26/2007 1:56 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite3Explorer Joe Wilson wrote: > --- Cariotoglou Mike <[EMAIL PROTECTED]> wrote: > >> I wish I could "make it for Unix", but it uses a lot of windows-specific >> things, plus it is done >> in Delphi, and since >> Kylix is practically dead, wlll... >> > > I didn't realize it was written in Delphi. > Yes, that would be a difficult port to UNIX - more like a complete rewite. > Mike, It "might" be worth looking at Lazarus. Depending on the VCL components you are using, the port might be easier than you think...albeit non-trivial John - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] [OT] "encrypted" e-mail address (was Re: [sqlite] sqlite3Explorer)
no, a mistake. the address IS : mikecar at singular dot gr (mike will also work) thanks for pointing out the header problem. I was hoping the mailing list was "hiding" this info, obviously not An unencrypted address was plainly visible in the message headers ("From" and "X-Return-Path"). Following the decryption instructions produces a result which has no "@" character in it; where the header address has "@", the decrypted version has "car&.". Is this an elaborate joke, or what? - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite3Explorer
ooops mail obfuscation was wrong! it is actually : mike atsign singular dot gr (I used ampersand in the original post which was wrong. well, english is not my native lang...) - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite3Explorer
you can't ?! I overdid it, then :) I wish I could "make it for Unix", but it uses a lot of windows-specific things, plus it is done in Delphi, and since Kylix is practically dead, wlll... From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Sat 11/24/2007 7:00 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite3Explorer > the address follows, obfuscated for the usual reasons. to e-mail me, remove > any > numeric digits and punctuation from the address that follows, and do the > obvious substitutions. > hopefully spammers will not... > > m6_i_ke$car(ampersand)(dot)s#i#n#g#u#l#a#r(dot)gr Nevermind the spammers - I can't break that encryption. Nice software, that sqlite3Explorer, by the way. Do you plan to make a version for UNIX? Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3Explorer
Hi. I am the author of the Sqlite3Explorer GUI front end. I have been inactive on this tool for some time now, frankly because I initially created it to cover my own needs, and I havent had any new needs lately :) In any case, since I see that people still use it, and some even like it, I am willing to put some effort to bring it up to speed. So, I would like the community to make suggestions as to how I can improve the utility. I am not promising I will implement every suggestion, of course. In order to reduce clutter in the mailing list, I suggest that you send me your ideas to my personal e-mail. the address follows, obfuscated for the usual reasons. to e-mail me, remove any numeric digits and punctuation from the address that follows, and do the obvious substitutions. hopefully spammers will not... m6_i_ke$car(ampersand)(dot)s#i#n#g#u#l#a#r(dot)gr - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] PROBLEMS BUILDING 3.4.2 using Ms Visual Studio 2005
I am having problems building 3.4.2 from the amalgamated source,using Microsoft Visual Studio 2005 (this is the first time I am trying to use the amalgamated source). I get the following errors: Error 1 error C2133: 'sqlite3UpperToLower' : unknown size Error 37 error C2133: 'sqlite3OpcodeNames' : unknown size Error 184 error C2133: 'sqlite3IsIdChar' : unknown size strangely, I can build fine When using the separate source files. I do not know enough C/C++ to understand why the error occurs. I suspect it has to do with order of declarations in the source file. Note that all three errors have to do with internally declared arrays. in the separate source, they are declared as "extern", but in the amalgamated source they become "static". can somebody help please ? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Unique Index not working properly
> > Seems that there is a problem on unique key fields when null > > values are allowed > > > > CREATE TABLE z ( > > id VARCHAR(32) NOT NULL, > > f1 VARCHAR(32) NOT NULL, > > f2 VARCHAR(20), > > PRIMARY KEY (id) > > ); > > CREATE UNIQUE INDEX z_I1 ON z (f1, f2) > > > > insert into z values ('1', '1', null); > > insert into z values ('2', '1', null); > > this should give a unique constraint error, but does not. testing with sql server and oracle shows that they *will* give an error. in this case, NULL = NULL seems to be true can this be fixed ? it is quite a deviation from standard behavior. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Singular Sqlite3Explorer 2.0 question
suppose you have a text file that contains : Myname,yourname hisname Myname,yourname hisname Myname,yourname hisname Myname,yourname hisname Myname,yourname hisname Myname,yourname hisname Myname,yourname hisname notice that this is an irregular format, because the first column delimiter is a comma, and the second is a space. this kind of file cannot be parsed easily using "standard" formats like CSV. A regular expression allows you to break down the lines into columns in an ad hoc fashion. the expression : (\w+),(\w+) (\w+) will break lines into three columns, for example, based on a comma and space delimiter. the parentheses are essential, to provide subgroups that map to the columns. try the example above, it should become clearer. if you do not now how to use Perl regular expressions, you need to do some background reading first. regards,mike From: Noah Hart [mailto:[EMAIL PROTECTED] Sent: Tue 27/2/2007 6:59 PM To: sqlite-users@sqlite.org Subject: [sqlite] Singular Sqlite3Explorer 2.0 question Has anyone used this program? I am trying to use the "Import using Regular Expression" option. Itis looking for some type of regular expression to parse the columns. Any suggestions or sample expressions you can offer? Regards, Noah Hart 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] weird (and dangerous) bug in Microsoft Compiler
Robert, I re-verified using your option set, and I still get the same problem. there is clearly something wrong here, and it is not easy to track down. I wonder if you could help me with this. Shall we take this off-line, so that we dont clutter the list, and report back when done ? I would really appreciate your help. since your e-mail is not visible, I cannot contact you directly. would you drop me a line at mikecarSingular.gr btw, I noticed something in your configuration : the option THREADSAFE is set, but NOT set to 1 AFAIK, the source uses this : #if defined(THREADSAFE) && THREADSAFE which probably means you are compiling with no thread safety (does not make any difference for my issue, but you may want to know) From: Robert Simpson [mailto:[EMAIL PROTECTED] Sent: Wed 08-Nov-06 6:27 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] weird (and dangerous) bug in Microsoft Compiler I compiled everything from bare source myself. I maintain the ADO.NET 2.0 wrapper. > -Original Message- > From: mike cariotoglou [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 08, 2006 9:18 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] weird (and dangerous) bug in Microsoft Compiler > > did you also compile the ado .net wrapper ? > > - Original Message - > From: "Robert Simpson" <[EMAIL PROTECTED]> > To: > Sent: Wednesday, November 08, 2006 4:57 PM > Subject: RE: [sqlite] weird (and dangerous) bug in Microsoft Compiler > > > >I tried that same SELECT statement in 3 builds of SQLite on > VS2005. The > > code was called from C# using the ADO.NET 2.0 wrapper, and > I got 10 all 3 > > times. I tried /fp:fast, /fp:strict and /fp:precise > > > > The rest of the build options were: > > > >AdditionalOptions="/GS-" > >Optimization="2" > >FavorSizeOrSpeed="1" > > > > > PreprocessorDefinitions="WIN32;NDEBUG;_WINDOWS;_USRDLL;_CRT_SE > CURE_NO_DEPREC > > > ATE;NO_TCL;THREADSAFE;SQLITE_HAS_CODEC;SQLITE_ENABLE_COLUMN_ME > TADATA;SQLITE_ > > ENABLE_FTS1" > >StringPooling="true" > >ExceptionHandling="0" > >BufferSecurityCheck="false" > >EnableFunctionLevelLinking="true" > >RuntimeTypeInfo="false" > > > > > >> -Original Message- > >> From: mike cariotoglou [mailto:[EMAIL PROTECTED] > >> Sent: Wednesday, November 08, 2006 5:27 AM > >> To: sqlite-users@sqlite.org > >> Subject: [sqlite] weird (and dangerous) bug in Microsoft Compiler > >> > >> hello to all. I wish to report some quirks I discovered with > >> floating point > >> and > >> ROUND() function, while looking into a problem reported by my > >> development > >> team wrt > >> to sqlite handling of above function. > >> > >> first of all, let me state that I understand the issues > with inexact > >> floating point > >> representations, so let us not go into discussions about > which is the > >> "correct" > >> interpretation of 9.95. however, a given implementation > >> should at least be > >> consistent > >> wrt to this. > >> > >> my tests have shown that, for the following statement: > >> > >> select ROUND(9.95,1) > >> > >> the command-line sqlite3.exe (v 3.3.8) returns 10.0 > >> OTOH, the compiled DLL that can be downloaded from the sqlite > >> site returns > >> 9.9 ! > >> (as a reference, both MS SQL and ORACLE return 10.0) > >> > >> What gives ? is the result dependent on compilation options, > >> and if so, > >> which ? > >> > >> trying to investigate this issue, I compiled the dll locally > >> (3.3.8), using > >> Microsoft > >> Visual Studio 2005, and came across a beauty : > >> > >> the dll compiled with MSVC, using default options more or > >> less, gives 0.0 > >> (yes, zero) > >> > >> I pulled my hair out over this for some hours, and > discovered that : > >> > >> a. the floating point optimizer in MSVC has a bug, which is > >> triggered when > >> you use the > >> optimization setting /fp:precice (which is the default), and > >> gives the above > >> erroneous > >> result. > >> > >> b. you can get the correct behavior by speifying optimization > >> as /fp:strict > >> > >> Clearly, this is a problem with the microsoft compiler. > >> however, trying to > >> avoid future > >> issues, I suggest that somebody which is conversant in C > >> (which is not me), > >> try to find > >> the sqlite3 source construct that triggers this bug, and > >> re-writes the code > >> so that it > >> is not optimization-sensitive. I tracked the problem down > >> somewhere in the > >> vxprintf function > >> in the print.c source file. It is quite difficult to pin the > >> problem down, > >> because: > >> > >> the problem goes away when you build in debug mode, because > >> optimizations > >> are turned off. > >> Even if you force some optimizations by hand, in
RE: [sqlite] Stored procedures in triggers
> > Thoughts? Would making recursive triggers an error rather > than just silently ignoring them break anybody's code? even if it does, it should. otherwise, people may assume that the functionality exists,and rely on it. > I'm also looking at making DELETE triggers recursive. I can > do that because recursive DELETE triggers are guaranteed to > terminate (you will eventually run out of rows to delete.) > But INSERT or UPDATE triggers might go on forever. There are > also technical issues that make recursive INSERT and UPDATE > triggers more difficult so that I would prefer to delay > implementing them. > > Comments? Would it be useful to have recursive DELETE > triggers even without recursive INSERT or UPDATE triggers? not much IMHO
RE: [sqlite] Triggers and TEMP tables: ticket #1689
I am not sure how this would work. in order for a trigger to "See" another database, somebody (obviously not the trigger) must ATTACH the database first. is there an automatic ATTACH capability that I am not aware of ? if not, specifying triggers than span databases is looking for trouble IMHO. as for allowing it, well, guns are allowed also, so you can shoot yourself in the foot, if you really want to. > -Original Message- > From: Brad [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 28, 2006 3:28 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Triggers and TEMP tables: ticket #1689 > > > Should I remove the tests from SQLite that prevent triggers in one > > database from referring to tables in a different database? > > Well, I have a couple of triggers in my SQL Server database > that refer to tables in another database, though they are on > the same server. > Obviously, I can see some utility in that. I will admit, > however, that if I had to implement the system that uses that > scheme in SQLite, then I'd probably have the tables in the > same database file, rather than different ones. > > > >
RE: [sqlite] SQLite acces from vbScript
all you need is an OLEDB or ODBC wrapper for sqlite, and there are some around, check the wiki. once you install one, your sqlite is accesible from ADO, just like any other database (almost) From: John Latimer [mailto:[EMAIL PROTECTED] Sent: Mon 30-Jan-06 12:06 AM To: sqlite-users@sqlite.org Subject: [sqlite] SQLite acces from vbScript I am programming in vbScript within the framework of a larger program on a PC platform and need to have access to SQLite for my database needs. I am an inexperienced programmer, so I wouldn't be surprised if the answer is very obvious to you , but oblivious to me... I require use of an SQLite database with full database query functionallity using vbScript. John L> John A. Latimer The Hologenic Man Discovery: The more I learn, the more I learn how little I know... Goal: There's strength in simplicity. Note: Goal not always achieved.
RE: [sqlite] sqlite problem
I may be wrong on this, but try this: reverse the order of the tables in the FROM clause. you should not need the extra index, if the joins is FROM functions TO symbols event better, use JOIN syntax: select * from functions join symbols on functions.symbolID=symbols.id this should only need the primary keys which you already have > -Original Message- > From: Jim Crafton [mailto:[EMAIL PROTECTED] > Sent: Friday, January 27, 2006 5:30 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] sqlite problem > > I'm a newbie to using SQL in general, so my apologies if this > has been answered before. > > I have a series of tables I'd like to create to represents > the output that comes from parsing a bunch of source code > files. Basically ctags output in DB format. > > I've created the following tables using sqlite3 > > CREATE TABLE Types ( >TypeID INTEGER PRIMARY KEY > , TypeInfo CHAR(125) > ); > > CREATE TABLE Templates ( >TemplateID INTEGER PRIMARY KEY > , TemplateSignature CHAR(125) > ); > > CREATE TABLE Files ( >FileID INTEGER PRIMARY KEY > , Path CHAR(255) > ); > > CREATE TABLE Symbols ( >Id INTEGER PRIMARY KEY > , Kind INTEGER > , Name CHAR(125) > , FileID INTEGER > , TypeID INTEGER > , Offset INTEGER > , LineNumber INTEGER > , TemplateID INTEGER DEFAULT 0 NOT NULL > , Parent INTEGER DEFAULT 0 > ); > > CREATE TABLE Functions ( >FuncID INTEGER PRIMARY KEY > , Signature CHAR(125) > , SymbolID INTEGER > , ReturnTypeID INTEGER NOT NULL > ); > > > I can then populate the tables fine. In my test, the symbols > table had over 11,000 entries and the functions table over > 7,200 entries. > > When I run the following query: > "select symbols.name, functions.signature from symbols, > functions where functions.symbolid = symbols.id;" > > This takes a long time (over 20 secs) on a P4 3 Ghz with 1 Gb RAM. > Should it take this long? Is it slow because my table is > setup incorrectly? > > Thanks so much! > > Jim Crafton > > >
[sqlite] bug/misbehavior of the ALTER TABLE statement
I have observed a "bug" in sqlite (3.2.8). when a table is created, and then renamed via the ALTER TABLE statement, the create statement gets adjusted (which is correct), but the new name appears in single quotes. according to sql syntax, table and field names are quoted by double quotes, so this creates subsequent problems for parsing tools and such. can you verify and fix, pls ? to test: create table x(id) select * from sqlite_master (observe the create statememt) alter table x rename to x1 select * from sqlite_master (observe the create statememt again, the name is now 'x1' in quotes)
RE: [sqlite] implementing editable result sets
one idea. run an EXPLAIN fist, and then analyze the query plan. it will tell you if there are more than one tables, and maybe you can get info about aggregate functions and such. of course, there is a cost to this... > -Original Message- > From: Will Leshner [mailto:[EMAIL PROTECTED] > Sent: Monday, December 19, 2005 11:12 PM > To: Forum SQLite > Subject: [sqlite] implementing editable result sets > > Hi. I apologize in advance for the length of this question, > but it is a little involved. > > I am the author of a wrapper for SQLite and in that wrapper > there is an object called a RecordSet that represents the > results of a query. > One of the things you can do with a RecordSet is edit > records. The way I've implemented editing a RecordSet is to > construct an UPDATE statement based on the new values for the
RE: [sqlite] Problem with floating point fields, and a feature request
I see again that you all miss the point. I DO know how to handle floating point. My point is : a. a lot of people will make the error indicated. I am sure that they are poor programmers. I am also sure (judging from some of the questions posted in this list), that there is a lot of them... b. some databases (ORACLE is the only one that comes to mind) support this properly, by allowing for fixed point types. so, a type declared as NUMERIC(10,3), which is ANSI-92, will be handled properly in comparisons. Most other engines will use floating point only, and will fail.So, to the question : " should all databases implement your fix" the answer is YES if they need it. c. the REAL danger with wrappers is this: you are thinking of wrappers that hide the SQLITE api. Ok, I agree that those do not need to handle the problem, as the programmer can do it themselves. but consider: a lot of people out there, I suspect, use SQLITE through a higher-level API. take a look at the ODBC, OLEDB and .NET providers. They allow point-and-click programmers (yes, mum, they do exist, they are probably idiots, but they outnumber us "real" programmers by a factor of 10:1 at least) to use niceties as data binding, data-aware grids and so on. Now all these data providers do not just hide the API, they also add functionality. One very important one, is , to generate SQL to update the database when a data-aware control changes. The generated SQL is never under the control of the programmer, so he cannot do anything about floating point comparisons or anything else. In particular, generated UPDATE statements (and DELETE, for that matter), will add a WHERE clause, where ALL fields will be compared to their "original" value. why is this ? In order to achieve optimistic row locking, in other words to detect changes by other users in a multi-user environment. now THIS is the situation I am talking about, and there is simply no fix for this problem, unless the db engine itself can handle it. Of course, one can avoid the problem by not using floats, which also implies not using DATES since a lot of programming environments and DB apis use floats for dates (OLEDB does, Delphi does, Visual Basic does etc etc). But this will seriously reduce the usability of SQLITE with RAD environments, which try to hide the DB access from the programmer. Again, I am not advocating that point-and-click programming is good, or that RAD environments are good, or that data-aware controls are a cool idea. I am aware of the issues with all these, having used them for decades. What I am saying is that there a lot of poor sods out there that dont know any better, so they DO use these things, and they are in for a big surprise... since a solution to this issue is fairly simple, and the applicable audience is large, why not provide one? the fact that MSSQL will not be able to do the same is not an argument that has stopped drh before, has it. From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Wed 14-Dec-05 7:35 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Problem with floating point fields, and a feature request Dave Dyer wrote: >>>select * from test where f=13.06; -- returns no data > > > Pardon me for throwing a bomb, but no good programmer > would ever use = to compare floating point numbers. > > Choose a more appropriate representation for your data. > It is not a bomb, just something novice programmers have to learn. There is no = in floating point, it is only possible with integers.
RE: [sqlite] Problem with floating point fields, and a feature request
as you may remember, some time ago I raised an issue with floating point accuracy, and how this may affect sqlite. I now have a concrete example, which actually happened in an installation, and helps to demonstrate the severity of the issue: try this code: create table test(f double); insert into test values(13.04); update test set f=f+0.02; select * from test where f=13.06; -- returns no data can you imagine how many bugs waiting to happen are out there, because of code like this ? I know that there are a number of solutions to this problem, all involving changing the sql involved. however, these are not applicable to people using SQLITE via wrappers that generate their own UUPDATE code. what I have been trying to say is that, there is also a neat solution to the problem, and one that can be implemented easily : pragma floating_accuracy=0.001 setting the threshold for float comparisons to some predictable value. Am I the only one that sees the problem? if not, please speak up, and maybe we can get a neat solution!
RE: [sqlite] how can I import CSV file into SQLite quickly
sqlite3Explorer does that From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Wed 07-Dec-05 8:00 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] how can I import CSV file into SQLite quickly Someone somwhere must have a simple Perl script which does what you want. JS Robert L Cochran wrote: > I create an SQL file that has contents like this: > > [EMAIL PROTECTED] elections]$ cat insert_precinct.sql > BEGIN TRANSACTION; > INSERT INTO "precinct" VALUES(1, 'Community Center 15 Crescent Road', 3, > 'Greenbelt', 'Maryland', 0); > INSERT INTO "precinct" VALUES(2, 'Police Station 550 Crescent Road', 6, > 'Greenbelt', 'Maryland', 0); > INSERT INTO "precinct" VALUES(3, 'Springhill Lake Recreation Center 6111 > Cherrywood Lane', 8, 'Greenbelt', 'Maryland', 0); > COMMIT; > > Then I fire up sqlite3 on the command line, and issue > > .read insert_precinct.sql > > I realize this will probably make you unhappy because it means editing > your CSV file so that each line is transformed into an sql statement. > This can be done most easily with sed (if you are a Linux or Unix > person), but you need to know sed commands and you need to be willing to > patiently experiment until the sed script applies exactly the right edits. > Bob Cochran > > > ronggui wong wrote: > >> I have a very large CSV file with 1 rows and 100 columns.and the >> file looks like the following: >> "a","b","c","d", >> "1","2","1","3" , >> "3","2","2","1", >> .. >> >> If I use .import,It seems I have to set the variable names manually . >> Is there any way to import the whole data file into SQLite quickly? >> Thank you! >> >> ronggui >> >> >> >> >
RE: [sqlite] Feature request
I took a look at the code. My perception is that, while the new design *can* imnplement what I want, it cannot do so without writing code at the C level. since I am using sqlite via the dll "wrapper", I do not have this option, I would need an "api" level capability for this, which is not the same thing as the new virtual OsFile. > -Original Message- > From: Will Leshner [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 01, 2005 2:46 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Feature request > > > On Nov 30, 2005, at 4:18 PM, Cariotoglou Mike wrote: > > > The concept of in-memory database would be greatly enhanced > if there > > was a mechanism to serialize the internal cache to/from a > stream (not > > necessarily a disk stream). In-memory databases are > extremely useful > > for a number of applications, but ar a PITA to initalize/persist. > > With the very exciting virtual OsFile API that is being > developed, I'm wondering if we couldn't serialize a SQLite > database ourselves. I haven't looked at the entire API, so > maybe this isn't possible, but if you can create multiple > "subclasses" of OsFile in the same application, then you > could have an on-disk subclass for your file system, and an > in-memory one as well, and theoretically SQLite wouldn't know > the difference. Then you could attach one to another and copy > tables back and forth. > > >
RE: [sqlite] Feature request
I did not know about this one. where can I get info ? From: Will Leshner [mailto:[EMAIL PROTECTED] Sent: Thu 01-Dec-05 2:46 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Feature request With the very exciting virtual OsFile API that is being developed, I'm wondering if we couldn't serialize a SQLite database ourselves. I haven't looked at the entire API, so maybe this isn't possible, but if you can create multiple "subclasses" of OsFile in the same application, then you could have an on-disk subclass for your file system, and an in-memory one as well, and theoretically SQLite wouldn't know the difference. Then you could attach one to another and copy tables back and forth.
[sqlite] Feature request
Hi all. I would like to propose an enchancement in sqlite. I am not sure whether the issue has come up before, if it has, and has been shot down, pls let me know. The concept of in-memory database would be greatly enhanced if there was a mechanism to serialize the internal cache to/from a stream (not necessarily a disk stream). In-memory databases are extremely useful for a number of applications, but ar a PITA to initalize/persist. one might argue why such a mechanism would be useful. If I need a persistent database, you could say, then I would make it a disk-based one. Not so. Consider the case where you would like to store the contents of an entire "database" as part of another storage structure. consider the case where an entire sqlite database was a field in another RDBMS. consider OO databases. Consider case tool implementation. The applications would be endless. I took a look at the ":memory:" implementation, and it seems to me that this enchancement would be trivial, consisting of maybe 10-20 c code lines, for somebody who understands the internal structures involved. unfortunately, I do not do C, so I cannot do this myself. OTOH, given the extreme usefuleness of what I propose (IMHO), could you consider this as a permament addition in sqlite? the design I have in mind would be something like this: int sqlite3_loadMemDb(sqlite3 * db,reader) int sqlite3_saveMemDb(sqlite3 * db,writer) where "reader" and "writer" are function pointers with a signature like : int reader(void mem, int size) (excuse my attempt at C syntax, this is meant only as a tip) I suspect that the "load" function might need to know the number of pages beforehand, so some kind of overloaded definiton of "reader" would be required, that would return this information. if we agree that the idea has merit, the details can be worked out easily. what does the community, and especially DRH, think about this ?
RE: [sqlite] Request for comment: Proposed SQLite API changes
Clay, I like stick-shifts, I drive motorcycles *and* stick-shifts, and I was about to buy a Z4, but the wife stopped me (judging, correctly, that it is a girl-trap). I even write assembly now and then, when absolutely needed. I cant say it is fun, though. the thing is, I never have seen a single task implemented in C (not C++ mind you), that can not be implemented in Delphi, with the SAME or better performance, in a way that is 10 times more elegant, much better for maintainance and clarity, and less effort. afaik, the only down-side is that you don't get the delphi compiler in anything else than windows (ok, and linux). other than that, I see absolutely no advantage in the C language, and certainly no "fun" in it... for me, it is as fun as trying to start a fire by rubbing two sticks together. it can be done, and people have been doing it for thousands of years. but that was only because they did not have matches, not because they liked it. so, assuming that you had an efficient portable implementation of object pascal, do you really think *new* developers would opt to use C anyway ? > -Original Message- > From: Clay Dowling [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 03, 2005 7:53 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Request for comment: Proposed SQLite API changes > > > Cariotoglou Mike said: > > > a crappy .H file. these people (c programmers) live in wasteland, I > > really admire them for the constructive way they use header files, > > include files, defines, make files, configure files and > what not,and > > still manage to write code that is write-once, compile everywhere. > > this is something not easily achieved, given the tools they have to > > work with. otoh, I live in delphi land from day 1, and I > *know* what > > they are missing... > > As somebody who lives in both worlds, C land isn't such a bad > place. It's a little like the difference between driving a > nice comfortable Ford Taurus (Delphi) and a BMW Z3 with a > stick shift (if you haven't done it, > do: you probably never realized that driving could be so > fun). I like driving both. But there are certain situations > where one is a lot better than the other, as I'm sure you're aware. > > Clay Dowling > -- > Simple Content Management > http://www.ceamus.com > > > >
RE: [sqlite] Request for comment: Proposed SQLite API changes
since you work in D7, as I do, you already have namespaces (in the form of units), so this was never an issue, even if you wanted to have two versions of the same code built-in. as to why, well, consider a database managent tool that has to open both 2.x and 3.x databases, and the only tool to structure your code and isolate interface from implementation is a crappy .H file. these people (c programmers) live in wasteland, I really admire them for the constructive way they use header files, include files, defines, make files, configure files and what not,and still manage to write code that is write-once, compile everywhere. this is something not easily achieved, given the tools they have to work with. otoh, I live in delphi land from day 1, and I *know* what they are missing... > -Original Message- > From: Fred Williams [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 03, 2005 5:10 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Request for comment: Proposed SQLite API changes > > Thanks. I guess I never considered using two different > releases of any product within the same executable. Wonder > how many use this feature and why? > > > -Original Message- > > From: Joe Wilson [mailto:[EMAIL PROTECTED] > > Sent: Thursday, November 03, 2005 8:54 AM > > To: sqlite-users@sqlite.org > > Subject: RE: [sqlite] Request for comment: Proposed SQLite > API changes > > > > > > It's a primitive form of namespaces in C. > > Renaming the function calls allows Sqlite2 and Sqlite3 to coexist > > within the same executable/binary. > > > > Mind you, if the functionality of a documented function changes (as > > opposed to merely extended) I would think it would warrent a major > > revision number increase. Sqlite versions 3.1.0 and 3.2.0 did not > > change as radically as is planned for this upcoming release. > > > > --- Fred Williams <[EMAIL PROTECTED]> wrote: > > > > > Just curious, why is this being done anyway? I think this > > is the only > > > software product I have used that has this "feature." I > > fail to see the > > > usefulness from way up here above the source code, and I > ... > > > >
RE: [sqlite] Request for comment: Proposed SQLite API changes
ok, the sqlite_Schema thing can (and has) been wrapped. however, the error code issue is there, I believe that we have agreed in the past that it was bad design, but it could not be changed because of compatibility issues. I,for one, am willing to comb my code and re-code for this, so yes, please do it. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 03, 2005 4:05 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes > > Dan Kennedy <[EMAIL PROTECTED]> wrote: > > > Another proposal: Suppose that when creating an > sqlite3_stmt using > > > sqlite3_prepare, the original SQL text was stored in the > > > sqlite3_stmt. Then when a schema change occurred, the > statement was > > > automatically recompiled and rebound. > > > > The authorization callback > > would have to be reinvoked from within sqlite3_step() too. > > Yikes! I didn't think of that. This is pretty big negative > and will likely scuttle plans to do automatic re-prepare. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > >
RE: [sqlite] Page size problem
FYI, I tried the same script on windows xp2 sqlite 3.2.7, and it worked fine also > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 01, 2005 7:13 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Page size problem > > "Anton Kuznetsov" <[EMAIL PROTECTED]> wrote: > > Hello! > > > > Did anybody try to create an SQLite3 database with a custom > page_size (e.g. > > 8192) and fill it with data of more than 1Gb? As for me I didn't > > manage (using tclsqlite-3.2.7). It says "database disk > image is malformed". > > > > I just testing the script shown below. It generates a 2 GiB > database that seems to work fine. >
RE: [sqlite] SQL logic error when running vacuum;
as a matter of fact, I have also noticed that using the vaccum command from the command-line interface does bring this error up sometimes. however, since I use the graphical UI most of the time (being its author:) I don't use the command line interface that much. I suspect it has nothing to do with the engine itself, rather with the command-line tool. > -Original Message- > From: Preston Zaugg [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 04, 2005 6:35 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] SQL logic error when running vacuum; > > I have a database (encrypted w/ encryption extensions from > drh). After run a sql file on this database it gives the > error "SQL error: SQL logic error or missing database" after > running a vacuum. it also seems to give this error > sporadically on other statements. > > the sql file that i run does the following: > drops a table > creates a new table (same name different structure from the > the table that was just dropped) adds a unique constraint > runs 7000+ insert statements > > the file is executed using the .read of the command line > utility, and runs without error/warning. > > As a side note i am able to run this same script against a MS > SQL Server database without error. > > Between the fact that i can run this script on sql server and > don't get any errors on the .read, makes me think that my > script is valid/well formed. > > Oh.. other important stuff... i was on 3.2.2 when i started > running into this problem. Upgraded to 3.2.7 with the same > results. I'm on Windows 2003 and using a local database. > > Any suggestions? > --Preston > > > > >
RE: [sqlite] ANN: Sqlite3Explorer version 2.0 released
> > Interesting tool. Are sources available for porting to other > OSes? I would like to try on FC4. > -- > G. Roderick Singleton <[EMAIL PROTECTED]> PATH tech > unfortunately not, as it uses a lot of commercial components. plus, it is Delphi :)
RE: [sqlite] ANN: Sqlite3Explorer version 2.0 released
Yes, this is an issue with a number of menu selections, which I forgot to fix :) will do on the next release. For now, just open a db first.. > -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Monday, October 03, 2005 8:57 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ANN: Sqlite3Explorer version 2.0 released > > Cariotoglou Mike wrote: > > >this is a major release, with a lot of changes. please see > the readme > >at www.singular.gr/sqlite. > >Pls read the whole page carefully, as support for datatypes is now a > >lot more powerful, but slightly different than the previous versions. > >Report users : you will need to download an extra dll, it is > bookmarked > >in the above link. > > > >you can see the changes at a glance in > >www.singular.gr/sqlite/changes.htm > > > > > > > > > > > Hi Mike, > > Trying out your new version I ran into a crash when I clicked > the Query Builder button on the toolbar with no database > open. It works correctly with a database open. > > --- > sqlite3explorer2 > --- > Access violation at address 004B68E8 in module > 'sqlite3Explorer2.exe'. > Read of address 002C. > --- > OK > --- > > Dennis Cote > > >
[sqlite] ANN: Sqlite3Explorer version 2.0 released
this is a major release, with a lot of changes. please see the readme at www.singular.gr/sqlite. Pls read the whole page carefully, as support for datatypes is now a lot more powerful, but slightly different than the previous versions. Report users : you will need to download an extra dll, it is bookmarked in the above link. you can see the changes at a glance in www.singular.gr/sqlite/changes.htm
RE: [sqlite] about SQLite Explore with dll(3.2.7) problem
hi. I am the author of this program. I have not seen what you mentioned, but in any case, I am about to release a new major release 2.0 in a few days, which has been tested with 3.2.7 and does not seem to have any problems. From: Huanghongdong [mailto:[EMAIL PROTECTED] Sent: Wed 28-Sep-05 5:31 PM To: sqlite-users@sqlite.org Subject: [sqlite] about SQLite Explore with dll(3.2.7) problem I notic if I change the SQLiteExplore's dll to version 3.2.7 and use it to open the database wich created by old version dll then I get the a erro message,it saying something like "can't open the database file",anyone have idea?
RE: [sqlite] Problem with floating point fields, and a feature request
also, it kills the usage of indexes, whereas what I propose would not. > -Original Message- > From: Jay Sprenkle [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 20, 2005 5:04 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Problem with floating point fields, and > a feature request > > On 9/20/05, Cariotoglou Mike <[EMAIL PROTECTED]> wrote: > > > > There is an issue with floating point fields, which exists in every > > database I have dealt with, and of course exists in sqlite as well, > > all versions. > > Essentially, the issue is this: > > > > "When are two floating point values considered equal ?" > > > what's wrong with using round(n,2)? > > > --- > The Castles of Dereth Calendar: a tour of the art and > architecture of Asheron's Call > http://www.lulu.com/content/77264 > >
RE: [sqlite] Problem with floating point fields, and a feature request
nothing, when you hand-code. everything, when the code is auto-generated, which very frequently it is > -Original Message- > From: Jay Sprenkle [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 20, 2005 5:04 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Problem with floating point fields, and > a feature request > > On 9/20/05, Cariotoglou Mike <[EMAIL PROTECTED]> wrote: > > > > There is an issue with floating point fields, which exists in every > > database I have dealt with, and of course exists in sqlite as well, > > all versions. > > Essentially, the issue is this: > > > > "When are two floating point values considered equal ?" > > > what's wrong with using round(n,2)? > > > --- > The Castles of Dereth Calendar: a tour of the art and > architecture of Asheron's Call > http://www.lulu.com/content/77264 > >
RE: [sqlite] Problem with floating point fields, and a feature request
collating sequences do not apply to floating point comparisons, do they ? > -Original Message- > From: Dan Kennedy [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 20, 2005 5:12 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Problem with floating point fields, and > a feature request > > > > two floats A and B should be compared with this algorithm : > > > > diff=A-B > > if (diff>tolerance) then A>B > > else if (diff<-tolerance) then A > else A = B > > You could define a new collation sequence to do all that. > However, it's difficult to say what will happen when you have > three numbers A, B and C such that A==B and B==C but A!=C. > This will cause the occasional oddity. > > But if you know for some reason this will never happen, > everything will work fine. "Know" > > > > > > > __ > Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com > > >
[sqlite] Problem with floating point fields, and a feature request
There is an issue with floating point fields, which exists in every database I have dealt with, and of course exists in sqlite as well, all versions. Essentially, the issue is this: "When are two floating point values considered equal ?" Why is this an issue ? Floating point values are some times used as part of keys. Even worse, sql resolvers (engines that construct sql statements based on old/new values of client data) frequently construst statements like this one: update table set a= where b= such a statement will result from a user selecting , editing, and posting an update. Sqlite needs to find the relevant row, (either with an index or not), which at some point will involve comparing a float value stored in the database with a user-supplied float value. Now, depending on how floats are stored, and how they are inputted, this may or may not be an issue. in sqlite 2, where all is stored as text, one could choose to store floats with a known precision, and work around the inherently inaccurate comparison of floats. in sqlite 3, we have a FLOAT storage class , which means that we now store floats in binary form (which is good), and also means that equality is based on comparing floats in a binary way ( all 8 bytes being equal, in other words). This can/will be disastrous in many cases, one such was raised some time ago by another user (the '9.95' issue). The above update can fail, for no reason apparent to the user. It also happens with people working in Delphi, and using native date formats, which are actually floats. Dates are very often part of keys, sorting, grouping etc, so all kinds of rounding errors can and will manifest. Of course, one can work around this problem, by NOT storing floats in binary format, but in a well-defined text format. however, this would be a pity, as a lot of time would be taken by sqlite and user code in comverting back and forth, plus, in some cases, sqlite will try to promote values stored as text to binary, re-introducing the problem. not to mention that, floats stored as text will collate incorrectly, unless they are right-justified and padded with '0', which increases the storage requirements. This is not new, however, in sqlite we *do* have access to the people that write the code, so a fix *can* be found, if there is a will and understanding of the problem :) :) All possible solutions to this issue have to do with the way that float equality is established. It boils down to this: two floats A and B should be compared with this algorithm : diff=A-B if (diff>tolerance) then A>B else if (diff<-tolerance) then A or adding a user-defined callback (as you did with the REGEXP operator), such as sqlite_compareFloat(a,b), which defaults to whatever code is now used to compare two floats, but can be overriden by the user. the should be in terms of SIGNIFICANT digits, not number of decimals, due to the way floating point works. Having a capability like this would be of immence value to the stability of database applications, and , I suspect, would not require a major re-work. what do you think, DRH, is this doable ?
RE: [sqlite] problems compiling 3.2.6
drh already fixed it. see http://www.sqlite.org/cvstrac/chngview?cn=2720 > -Original Message- > From: Maurizio Ferraris [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 20, 2005 4:13 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] problems compiling 3.2.6 > > I also have the same problems with VC6, and unfortunately I > cannot switch to > VC7 for other reasons. > At the moment I reverted back to 3.2.5. > When I will have some spare time I will try to fix it myself > and I will inform the list. > My guess is: update too the latest SDK (that unfortunately > does not integrate well with VC6) for the first problem and > add another (__int64) cast before the conversion to double. > Of course this is just a hack and I hope someone will find a > better fix. > Regards. > Mau. > > Drew, Stephen wrote: > > Further update: > > > > I get both these errors in Visual Studio 6. > > > > As you say, I think the first is just that Visual Studio 6 has a > > missing definition in winbase.h. I agree with you that it is in the > > documentation (perhaps a check could be performed and it > defined if it > > doesn't exist...) > > > > The second seems to be a limitation of VS6 - it certainly isn't a > > problem in VS7. Can't really think of any neat solution around it > > either... > > > > This doesn't concern me, as - as I mention - I use VS7... > > > > Steve > > > > -Original Message- > > From: Drew, Stephen > > Sent: 19 September 2005 17:13 > > To: sqlite-users@sqlite.org > > Subject: RE: [sqlite] problems compiling 3.2.6 > > > > Mike, > > > > 3.2.6 compiles fine in Visual Studio 7 (.NET 2003). I can > give it a > > go in my copy of Visual Studio 6 if you like... > > > > Steve > > > > -Original Message- > > From: Cariotoglou Mike [mailto:[EMAIL PROTECTED] > > Sent: 19 September 2005 10:14 > > To: sqlite-users@sqlite.org > > Subject: [sqlite] problems compiling 3.2.6 > > > > I tried to compile 3.2.6 locally, using visual c 6, as I do > with all > > sqlite releases. this version introduces a couple of > changes that do > > not > > compile: > > > > os_win.c(482) : error C2065: 'INVALID_SET_FILE_POINTER' : > undeclared > > identifier > > vdbeapi.c(237) : error C2520: conversion from unsigned __int64 to > > double not implemented, use signed __int64 > > > > the first error has to do with an old version of winbase.h, > which for > > some reason omits the definition of > INVALID_SET_FILE_POINTER (although > > the documentation mentions it). this is probably a local > problem, and > > I will try to fix locally (although I would like to hear from other > > people about it. I am compiling WITHOUT mfc). > > > > the second I have no idea, as I don't know C. > > > > anybody help ? > > > > > > > > > > > > > > > > > > > > > > >
RE: [sqlite] query problem
You are correct. I just run a test on 3.2.6 release, and it does handle joins incorrectly. I cant get the files from cvs, so I am not sure whether the fix also Handles the reverse situation: Select * >From T1 left join t2 on (t1.ref=t2.id) and (t2.kind=1) Ie if there is a join term that restricts the RIGHT hand table only. Just a thought. > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 20, 2005 12:07 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] query problem > > I've changed my mind. I think instead that there is a bug in > SQLite that caused LEFT JOINs to be computed incorrectly if > one of the terms in the ON clause restricts only the left > table in the join. > > Check-in [2725] at http://www.sqlite.org/cvstrac/chngview?cn=2725 > contains > a fix for this problem for version 3.x. The problem has > existed in SQLite forever (because it originates from a > conceptual misunderstanding by the code author :-)) so > version 2.8.16 is still broken. Because the problem is > obscure, I am not inclined to fix it in the 2.8.x series... > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > >
RE: [sqlite] Using date fields in SQLiteExplorer
it is definitely possible. how have you defined the data type in the CREATE statement ? how did the data was inserted in the table initially ? have you enabled the "use datatypes" option ? dump the contents of the table with the "datatypes" disabled, so you see raw data. are they really floating point values ? remember, you can use the "show only" check box, to see the SQL that the resolver will produce, without actually running it. if you cannot figure it out, send me the db. > -Original Message- > From: Zibetti Paolo [mailto:[EMAIL PROTECTED] > Sent: Monday, September 19, 2005 1:07 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Using date fields in SQLiteExplorer > > Here is another question about dates and SQLiteExplorer (v 1.7). > I'm storing dates in the native Delphi format, i.e. as > floating point numbers ("dates as text" = FALSE). > Whenever I try to update a record that contains such a date, > SQLiteExplorer displays the message "unexpected count of > affected records:0" and modifications are not written to the database. > Is it possible that SQLiteExplorer is trying to update the > record on the database using the date fields in a "where" > clause but fails because of rounding errors in the floating > point rapresentation of dates ? > How can I work around this problem ? > > Thank you > Bye > > > >
[sqlite] problems compiling 3.2.6
I tried to compile 3.2.6 locally, using visual c 6, as I do with all sqlite releases. this version introduces a couple of changes that do not compile: os_win.c(482) : error C2065: 'INVALID_SET_FILE_POINTER' : undeclared identifier vdbeapi.c(237) : error C2520: conversion from unsigned __int64 to double not implemented, use signed __int64 the first error has to do with an old version of winbase.h, which for some reason omits the definition of INVALID_SET_FILE_POINTER (although the documentation mentions it). this is probably a local problem, and I will try to fix locally (although I would like to hear from other people about it. I am compiling WITHOUT mfc). the second I have no idea, as I don't know C. anybody help ?
[sqlite] sqlite3_errCode and error handling
when a sqlite3_Step call fails, say on a unique key violation of an INSERT statement, the returned error code is SQLITE_ERROR, which is documented. calling sqlite3_errcode at this point, however, also gives the same error, which is not. the correct error, which is SQLITE_CONSTRAINT, is only returned when calling sqlite3_finalize, which may happen at a much later stage in the code. I believe the error code should be returned right at the point where the error happens, because if it is not, the structure of the code suffers, since the finalization code for a statement needs to be modal.
RE: [sqlite] SUM and NULL values
There is one other thing, though. Allthough the sql standard is (insert your favorite 4-letter word), and although I personally hate nulls, I try to write applications where the sql is as portable as it can be. So, in the name of portability, you should follow the sql standard.
[sqlite] ANN: Sqlite3Explorer 1.8 released
Minor fix to work around a bug in pragma empty_result_callbacks, which makes Explorer incompatible with the ANALYZE command
RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
ok, I pinned it down. it is a genuine bug, and has nothing to do with dlls and wrappers. to reproduce, do this: 1. open the command-line utility on a database (or even with no database, it does not matter) 2. type this: SQLite version 3.2.5 Enter ".help" for instructions sqlite> pragma empty_Result_callbacks=1; sqlite> analyze; this will crash immediately. in general, if empty_Result_callbacks is set, AND the database is analyzed, sqlite crashes consistently. the reason it appeared in sqlite3Explorer is that it sets the pragma by default. DRH, pls check this. > -Original Message- > From: Miha Vrhovnik [mailto:[EMAIL PROTECTED] > Sent: Monday, September 05, 2005 7:14 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE > > "Cariotoglou Mike" <[EMAIL PROTECTED]> je ob 5.9.2005 > 14:49:54 napisal(a): > > >ok. > >I use sqlite3Explorer (which should not matter), I create a new > >database, then I run : > > > >create table test1(id integer); > >analyze > > > >this crashes immediately, with dll 3.2.5. it works fine from the > >command-line. > > > I can confirm that. But it seems that the problem is in > Sqlite explorer or your sqlite3.dll wrapper implementation. > When I try that with my own Delphi wrapper implementation it > works. But opening database with sqlite explorer results in crash. > > Regards, > Miha > > >
RE: [sqlite] Problems with threadsafe opt correction #2623
I hope that this restriction is not enforced on windows, ever! I have an application that will be completely broken by this: it is an application server, that serves multiple clients, pools db connections, and hands them out on demand, protecting the pool with a number of mechanisms that have never failed. so, the application is quite thread-safe. on windows, the option to use processes instead of threds,is not a very good one, as you know.so, any scheme that uses threads, AND resource pooling, will fail if you enforce above restriction, which is meaningless in windows, anyway. so, please don't! > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Monday, September 05, 2005 9:19 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Problems with threadsafe opt correction #2623 > > On Mon, 2005-09-05 at 19:58 +0200, RenƩ Tegel wrote: > > > As far as i can tell now the windows version of 3.2.5 seems > not affected. > > > > The restriction that a DB handle can only be used from a > single thread is currently only enforced on Unix, because > Unix boxes are the only place where using DB handles in > multiple threads is a problem. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > >
RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
ok. I use sqlite3Explorer (which should not matter), I create a new database, then I run : create table test1(id integer); analyze this crashes immediately, with dll 3.2.5. it works fine from the command-line. > -Original Message- > From: Ned Batchelder [mailto:[EMAIL PROTECTED] > Sent: Monday, September 05, 2005 3:10 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE > > Perhaps you could provide the exact SQL that crashed. The > code seems to be very well tested automatically, so it is > very unlikely that all ANALYZE executions fail. > > --Ned. > http://nedbatchelder.com > > -Original Message- > From: Cariotoglou Mike [mailto:[EMAIL PROTECTED] > Sent: Monday, 05 September, 2005 5:46 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE > > I tried the ANALYZE statement with sqlite 3.2.5 in dll form, > (both the pre-compiled version downloaded from the site, AND > a local compilation). > in both > cases, the statement fails with an ACCESS VIOLATION. however, > the same statement, when run from the pre-compiled > sqlite3.exe, works. > further, having succesfully ANALYZED a database with the > command-line program, I can no longer work with the database > using the DLL api. > I think there is something very wrong with the compilation > defines of the DLL. > > DRH: can you pls check and fix ? > ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS, > obviously) > > > > > > >
[sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
I tried the ANALYZE statement with sqlite 3.2.5 in dll form, (both the pre-compiled version downloaded from the site, AND a local compilation). in both cases, the statement fails with an ACCESS VIOLATION. however, the same statement, when run from the pre-compiled sqlite3.exe, works. further, having succesfully ANALYZED a database with the command-line program, I can no longer work with the database using the DLL api. I think there is something very wrong with the compilation defines of the DLL. DRH: can you pls check and fix ? ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS, obviously)
RE: [sqlite] ANN: sqlite3Explorer V 1.7
good to know... > -Original Message- > From: Dennis Jenkins [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 27, 2005 4:26 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ANN: sqlite3Explorer V 1.7 > > Dennis Jenkins wrote: > > > Cariotoglou Mike wrote: > > > >> *. new feature : support for encrypted databases (WITH the > licenced > >> version of sqlite) > >> > >> http://www.singular.gr/sqlite/ > >> > >> > > > > Awesome! Thanks! I was hoping that you would add > encryption support. > > :) (We purchased our license a few weeks ago). > > > > However, I am unable to get it to work. I am 100% sure that I am > > entered the encryption key when prompted for it. > > > > :) Never mind. I solved my problem. I forgot to define > 'SQLITE_HAS_CODEC=1' when compiling the DLL. It works great now. > (previously I was getting error '26', "not a database, or encrypted"). > > > >
[sqlite] ANN: sqlite3Explorer V 1.7
*. new feature : support for encrypted databases (WITH the licenced version of sqlite) http://www.singular.gr/sqlite/
RE: [sqlite] Multi-threading.
which gives me the opportunity to repear my oft-ignored reply :) what you say is true, provided the OS is geared towards multiple processes. which is not true for windows, but is true for *ix, as400 and other environments. if you need, say, a server that handles 500 sessions, and attempt to do this with spawning processes on windows, you are probably dead, memory and cpu-wise. on Linux, this is nothing, it can handle it easily. otoh, 500 threads for windows is business as usual, but threading on Linux, is , I hear, iffy at best. so, although this is good advice, it is not unconditionally applicable. > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Friday, July 15, 2005 3:27 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Multi-threading. > > On Fri, 2005-07-15 at 16:41 +0530, Roushan Ali wrote: > > Hello all, > > Can we use single sqlite_open handle(global) > across threads( > > if all database operations are serialized by using > semaphore) ? Please > > help. > > > > Opening a database connection in one thread and using it in > another will work on some operating systems but not on > others. You are advised not to do it. See > http://www.sqlite.org/cvstrac/tktview?tn=1272 > and http://www.sqlite.org/cvstrac/chngview?cn=2521. > > Actually, this seems like a good opportunity to repeat my > oft-ignored advice to not use more than one thread in a > single address space. If you need multiple threads, create > multiple processes. This has nothing to do with SQLite = it > is just good programming advice. I have worked on countless > multi- threaded programs over the years, and I have yet to > see a single one that didn't contain subtle, hard to > reproduce, and very hard to troubleshoot bugs related to > threading issues. > > I am constantly amazed at the prevailing idea (exemplified by > Java) that software should be strongly typed and should not > use goto statement or pointers - all in the name of reducing > bugs - but that it is OK to use multiple threads within the > same address space. Strong typing helps prevent only bugs > that are trivially easy to locate and fix. The use of goto > statements and pointers likewise results in deterministic > problems that are easy to test for and relatively easy to > track down and correct. But threading bugs tend to manifest > themselves as timing-dependent glitches and lock-ups that are > hardware and platform dependent, that never happen the same > way twice, and that only appear for customers after > deployment and never in a testing environment. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > >
RE: [sqlite] ANN: Sqlite3Explorer.exe version 1.6
which version of the dll are you using ? do you have REFERENCES declarations ? can you send me a sample of the data ? > -Original Message- > From: Hugh Gibson [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 22, 2005 4:22 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ANN: Sqlite3Explorer.exe version 1.6 > > Just got a crash when viewing table data using SQLite3Explorer version > 1.6: > > --- > sqlite3explorer > --- > Access violation at address 00402E29 in module 'sqlite3Explorer.exe'. > Write of address 01357AAE. > --- > OK > --- > > Pressing OK enabled me to scroll the data, but when reaching > near the bottom I got: > > --- > sqlite3explorer > --- > RecordIndex out of range. > --- > OK > --- > > Integrity check on the database gave: > > --- > sqlite3explorer > --- > Database seems ok > --- > OK > --- > > Scrolling up and down gave more errors, and eventually I had > to kill the app. > > The only thing that could affect this is that I had some > fields with lots of data, probably around 100k. > > Hugh > > >
[sqlite] ANN: Sqlite3Explorer.exe version 1.6
I have uploaded v 1.6 of sqlite3Explorer (www.singular.gr/sqlite). It has some minor bug fixes and enhancements, plus support for REGEXP operator. Sqlite3Explorer has had regular expression support for a long time now, using the (undocumented) MATCHES() function. I have now changed this to comply with the REGEXP operator. Functionality is PERL (PCRE) Since I have not been answering queries for some time now, because I was too busy, I will try to address some of the accumulated issues in this announcement, as well: >2. I have a table with signed 32-bit integers. Doing queries with negative >values doesn't work. I have to specify the 2's complement value to get it >working. Sqlite3Explorer does not support 64-bit UNSIGNED integers (32-bit is ok). This is a Delphi limitation, and not likely to be solved. >>1. The rowid (implicit column) is not displayed. of course. you need to add it to the SELECT statement explicitly. If any other product is showing you this, they are monkeying with your SQL. >I Have two databases with identical schema but different data. Open one >.database, view data in one table. Open the other database, the data shown >in the table is for the original database. Edit the data, and Update >database. The second database is updated with the changed information if >the same record exists there; otherwise you get: --- sqlite3explorer --- resolver: unexpected count of affected records: 0. --- OK --- This is a feature, not a bug. it allows you to load data, open another database and apply changes there. This works as designed. if you do not need this, simply do not use it. >Further to these, I've found that viewing of text data in the grid is >limited to 255 characters. Is it possible to make the default a lot bigger >or have a way of "zooming" a field to get the final value. Not strictly true. If you work in untyped mode, or if you work in typed mode, but your columns do NOT contain size information, then, yes, the columns default to a size of 255. Version 1.6 changes this to 8192. Also, a workaround for the previous versions would be to define your columns properly , such as : name varchar (wrong) name varchar(1200) (correct)
RE: [sqlite] Sqlite3explorer can't open my DB
ok, fine. what baffled me in the first place was the error message: > 4:malformed database schema - near "to": syntax error this is an sqlite error, not an sqlite3Explorer error, so I wonder whether at some point even sqlite would gag at this field type definition...
RE: [sqlite] Sqlite3explorer can't open my DB
Could you please let me know what the problem was ? I am the author of sqlite3Explorer, and perhaps I could fix it. > -Original Message- > From: Downey, Shawn [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 17, 2005 11:45 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Sqlite3explorer can't open my DB > > Thanks anyway. I solved this myself. > > Sqlite3Explorer is more particular about the table schema > syntax than Sqlite. > > Shawn M. Downey > MPR Associates > 632 Plank Road, Suite 110 > Clifton Park, NY 12065 > 518-371-3983 x3 (work) > 860-508-5015 (cell) > > > -Original Message- > From: Downey, Shawn > Sent: Tuesday, May 17, 2005 12:28 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Sqlite3explorer can't open my DB > > Version 1.4 of Sqlite3explorer could not open my database. > The error message is: > > > > 4:malformed database schema - near "to": syntax error > > > > An older version of Sqlite3explorer worked fine on this > database. I am using sqlite.dll version 3.2.1. Does anyone > else have problems with Sqlite3explorer? > > > > Shawn M. Downey > > MPR Associates > > 632 Plank Road, Suite 110 > > Clifton Park, NY 12065 > > 518-371-3983 x3 (work) > > 860-508-5015 (cell) > > > > > >
RE: [sqlite] SqliteExplorer and ISO8601 dates
new version 1.4, adds support for date formats, when storing dates as text. to support the format -MM-DD HH:MM:SS, all you need to do is change the date separator to "-", and change the time format (remove the .zzz) do NOT change the separator used in the actual date format, the "/" you see there is a meta-character. http://www.singular.gr/sqlite/
RE: [sqlite] SqliteExplorer and ISO8601 dates
Will fix and let you know > -Original Message- > From: Brad Schick [mailto:[EMAIL PROTECTED] > Sent: Saturday, April 30, 2005 9:31 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] SqliteExplorer and ISO8601 dates > > Is it possible to get SqliteExplorer to parse textual dates in the > ISO8601 format of: > > -MM-dd HH-mm-ss > > I believe this is the format that sqlite uses itself for > CURRENT_TIMESTAMP, et. al. > > I'm running SqliteExplorer 1.3 on WinXP with "Use Datatypes" > and "Dates as Text" enabled and it only seems to parse dates > slash seperators like 2005/04/27. Of course I can work around > this by disabling datatypes, but I like the extra error > checking provided by datatypes. > > Thanks, > -Brad > > >
RE: [sqlite] Trouble with column names
Imho, naming scheme should be like this: 1. column names as returned from sqlite3_column_name should always be a single word, NOT qualified by origin. So, all below should return "field1" as column name. If there is a column alias in the select, the *alias* should be returned as the single word. Case should be irrelevant for column names, as sql is supposed to be case-insensitive. 2. In case where an expression is given as column, say select field1+5 from..., it is Customary to return a pseudo name such as ExprN, where n=1..n. in any case, this is not Important, so use any scheme you find suitable. 3. there *should* be (nice to have) an sqlite3_column_origin function, which attempts to Return the column origin, in the form: Database.table.column In all cases, the *real* names should be returned, and not the aliases column_name column_origin -- > select field1 from test; field1 test.field1 > select "field1" from test;field1 '' > select FIELD1 from test; field1 (or FIELD1) '' > select "FIELD1" from test;field1 '' > select [field1] FROM test;field1 '' > select test.field1 from test; field1 '' > select a.field1 from test as a; field1 '' > select "a".field1 from test as a; field1 '' > select "a"."field1" from test as a; field1 '' > select main.test1.field1 from test; field1 main.test.field1 > select [main]."a".[field1] from test as a;field1 test.field1 select field1 as a from test a test.field1 etc > > This problem is related to the naming of columns in views. > Please tell me what the column names for the following views > should be: > > create view v1 as select field1 from test; > create view v2 as select FIELD1 from test; > create view v3 as select a.field1 from test as a; > create view v4 as select test1.field1 from test; > create view v5 as select "FIELD1" from test; > create view v6 as select "A"."FIELD1" from test as a; > create view v7 as select "a".field1 + 5 FROM test as a; > create view v8 as select "a".field1+5 from test as a; I believe in all cases, the names for sqlite3_column_name, and sqlite3_column_origin should be exactly the same as if the View was not present, ie as if the select was given directly. > Another place where naming is an issue is on CREATE TABLE AS > statements. What is the name of the column in each of the > following tables: > > create table t2 as select field1 from test; > create table t3 as select FIELD1 from test; > create table t4 as SELECT a.field1 from test as a; > -- and so forth Same comment as above, the column names should be the same for the equivalent SELECT (and data types as well) > For the above, what is the column name reported out when I do: > > select * from t2; > select * from t3; -- and so forth > > or > > select * from v1; > select * from v2; -- and so forth > > If someone will come up with a coherent set of column naming > rules - rules which other popular SQL database engines follow > - then I will be happy to implement them in SQLite. Doing so > will probably break a lot of existing code. But as the > column naming rules have never been specified or documented > before, I'd be willing to do it since any code that breaks > would have been depending on undefined behavior to begin with. > > Does the SQL standard have anything to say about this? > Does anybody know? I don't believe so. So, really, it is a matte of choice. However, let us examine the rationale of column naming requests you have been getting all this time. They come under two categories : A. people that do not like the current scheme / cannot make it compatible with some other database etc etc IMHO this is a lot of noise about a non-significant issue. Column names are retrieved from the sql statement, and people have the chance to map them to whatever they think is significant for the end-user. Programs can handle this easily, so I don't understand what it all the fuss about. My only comment here is that column names should be "short" (like pragma short_column_name), because this is what databases normally do. B. People who need to use column names to do generic processing, like machine-generated UPDATES, discovery of relations etc In this case, it is *ESSENTIAL* that : a. column names are consistent, and as informative as possible b. the origin of data can be retrieved as clearly as possible, since it is *this* which is needed by tools writers, and not any aliasing that the programmer has used in order to resolve ambiguities or for prettying up the sql statement. So, this is why I propose a second api, sqlite3_column_origin, which can return the real origin of the data, skipping alias
RE: [sqlite] NFS Query Performance
Just a thought. If transactions speed up the access, try this: Begin exclusive Select Select ... Select ... Commit Ie use a transaction around READS. This may acquire the lock once, and give you the same performance as inserts. > -Original Message- > From: William Hachfeld [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 19, 2005 10:18 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] NFS Query Performance > > On Tue, Apr 19, 2005 at 11:58:11AM -0700, Ted Unangst wrote: > > If you don't need locking, and it sounds like you don't, > just neuter > > the fcntl calls in os_unix.c. In fact, there's a #define > and comment > > for DJGPP that does exactly what you want. > > Hmmm. Thanks for the suggestion, Ted. I think I'll try this > out. If nothing else, this will help confirm whether the > majority of the slowdown is due to NFS locking performance. > > -- William Hachfeld > > >
[sqlite] ANN: new version of sqlite3Explorer (1.2)
Adds a simple report generator.
RE: [sqlite] what is the difference between TEXT and BLOB data type?
It is a limitation of sqliteExplorer. > -Original Message- > From: jack wu [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 07, 2005 7:05 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] what is the difference between TEXT and > BLOB data type? > > > other than encoded and not encoded. > > i did not find the length limitations in the documentation > but seems like TEXT can only hold less than 250 chars. is it > true or is it SqliteExplorer that only shows 250 chars. Thanks. > > > >
RE: [sqlite] ANN: Sqlite3Explorer
good idea. so, let me see if I understood correctly. you are suggesting that I rewrite the whole program in C++, plus any additional library code I might be using, so that I can use the widgets. does not sound as very practical advice to me.. > -Original Message- > From: Guru Kathiresan [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 05, 2005 8:21 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] ANN: Sqlite3Explorer > > Hi, > For designing wxWidgets forms like Delphi, you can use my > program IDE wx-devcpp from http://wxdsgn.sf.net . Although it > is still in beta stage, you can still able to create a decent > application with it. > > -Guru Kathiresan > > > -Original Message- > > From: Leif Jensen [mailto:[EMAIL PROTECTED] > > Sent: Monday, April 04, 2005 9:53 PM > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] ANN: Sqlite3Explorer > > > > Have you heard about wxWidgets (www.wxWidgets.org) ? A > great free > > GUI library (and much more) that works on Windows, Linux > and Mac (at > > least). Using that and your app. would do too ;-). I have > just made a > > database application with these that runs on both Linux and Windows > > using SQLite3. > > > > Leif > > > > > > Cariotoglou Mike wrote: > > > > >[EMAIL PROTECTED] it seems the new controls I used require this function, > > >which > > >only exists in win2k+. sorry for this, I think I will rebuild > > >*without* the devexpress controls, for more genral purpose use.. > > > > > > > > > > > >>-Original Message- > > >>From: Serge Liber [mailto:[EMAIL PROTECTED] > > >>Sent: Monday, April 04, 2005 8:30 PM > > >>To: sqlite-users@sqlite.org > > >>Subject: Re: [sqlite] ANN: Sqlite3Explorer > > >> > > >>Hi, > > >> > > >>When starting sqlite3Explorer an error message appears: > > >>"The procedure entry point GdiGradientFill could not be > located in > > >>the dll GDI32.dll" > > >> > > >>How can I proceed? > > >> > > >>I use NT4+sp6 > > >> > > >>Serge Liber > > >> > > >> > > >>New version is out. Since the problem with size limits was not > > >>lifted from contrib, I created a home site for the > program. You will > > >>now be able to get latest version from > > >> > > >>http://www.singular.gr/sqlite > > >> > > >>Latest is 1.1 , 24/3/2005 (ignore the typo in the date) > > >> > > >>Regards,mike > > >> > > >> > > >> > > >> > > >> > > > > > > > > > > > > > > > >
RE: [sqlite] ANN: Sqlite3Explorer
I found the offending call and removed it. it was *not* in the devExpress code, but the visual query builder code. as a result, table captions are no longer "nice"... pls download again. > -Original Message- > From: Serge Liber [mailto:[EMAIL PROTECTED] > Sent: Monday, April 04, 2005 8:30 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ANN: Sqlite3Explorer > > Hi, > > When starting sqlite3Explorer an error message appears: > "The procedure entry point GdiGradientFill could not be > located in the dll GDI32.dll" > > How can I proceed? >
RE: [sqlite] ANN: Sqlite3Explorer
[EMAIL PROTECTED] it seems the new controls I used require this function, which only exists in win2k+. sorry for this, I think I will rebuild *without* the devexpress controls, for more genral purpose use.. > -Original Message- > From: Serge Liber [mailto:[EMAIL PROTECTED] > Sent: Monday, April 04, 2005 8:30 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ANN: Sqlite3Explorer > > Hi, > > When starting sqlite3Explorer an error message appears: > "The procedure entry point GdiGradientFill could not be > located in the dll GDI32.dll" > > How can I proceed? > > I use NT4+sp6 > > Serge Liber > > > New version is out. Since the problem with size limits was > not lifted from contrib, I created a home site for the > program. You will now be able to get latest version from > > http://www.singular.gr/sqlite > > Latest is 1.1 , 24/3/2005 (ignore the typo in the date) > > Regards,mike > > >
RE: [sqlite] ANN: Sqlite3Explorer
I am afraid not. the components I am using do not port to Linux. > -Original Message- > From: Leif Jensen [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 30, 2005 7:56 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ANN: Sqlite3Explorer > >Any chance for a Linux version ? Or a web version ? > >Greetings, > > Leif > > > Cariotoglou Mike wrote: > > >New version is out. Since the problem with size limits was > not lifted > >from contrib, I created a home site for the program. You will now be > >able to get latest version from > > > >http://www.singular.gr/sqlite > > > >Latest is 1.1 , 24/3/2005 (ignore the typo in the date) > > > >Regards,mike > > > > > > > > > > >
[sqlite] ANN: Sqlite3Explorer
New version is out. Since the problem with size limits was not lifted from contrib, I created a home site for the program. You will now be able to get latest version from http://www.singular.gr/sqlite Latest is 1.1 , 24/3/2005 (ignore the typo in the date) Regards,mike
RE: [sqlite] Exporting MS Access MDB into SQLite
This may be an issue with the odbc driver. In the meanwhile, you could try importing with the gui tool, sqlite3explorer (www.sqlite.org/contrib). Afaik, it should import access ok. > -Original Message- > From: RAY BORROR [mailto:[EMAIL PROTECTED] > Sent: Monday, March 28, 2005 9:54 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Exporting MS Access MDB into SQLite > > SQLite Users, > > I started to export an Access MDB file into a DB file that > was created with SQLite. > > I was using the Visual Basic, Visual Data Manager tool to > export tables to the SQLite DB using the SQLite ODBC driver. > > The first table went OK. > All other tables after that came up with the following error: > VisData > The following Error occurred > OBDC-call failed > Number 3146 > > If I create a new DB in SQLite, I am able to export one table > without a problem. > It doesn't seem to matter which table it is. > > Please review and advise of any possible solutions. > > > Thanks, > > Ray Borror > >
RE: [sqlite] RegEx w/ sqlite, yet?
If you are working with delphi, I could have a solution for you. > -Original Message- > From: Jay [mailto:[EMAIL PROTECTED] > Sent: Saturday, March 26, 2005 3:41 AM > To: sqlite-users@sqlite.org; Win a 2 > Subject: Re: [sqlite] RegEx w/ sqlite, yet? > > > > Hello sqlite-users, > > > > any news on that? > > It's not part of the sql standard, so I would bet the answer > is going to be 'you have to add that yourself'. I did for my project. > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection > around http://mail.yahoo.com > > >
RE: [sqlite] Contrib uploads
it is already UPX'ed AND zipped. still... From: Eugene Wee [mailto:[EMAIL PROTECTED] Sent: Fri 3/25/2005 6:24 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Contrib uploads Hi, have you considered using UPX to reduce the executable filesize? http://upx.sourceforge.net
RE: [sqlite] getting table column names and types programaticly
Pragma table_info(tablename). For God's name, do read the documentation, somebody spent good time to write it! > -Original Message- > From: Gerry Snyder [mailto:[EMAIL PROTECTED] > Sent: Friday, March 25, 2005 1:19 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] getting table column names and types > programaticly > > Jim Dodgen wrote: > > Anyone know how I can query the field names and types for a > given table? > > Jim, > > select sql from sqlite_master where type="table" and name="gigo" > > will get something like: > > create table gigo(a,b,c) > > which includes the field names, and would include the types > if I had used any. > > Gerry > > >
RE: [sqlite] Contrib uploads
1.1 mb I used the [EMAIL PROTECTED] devExpress grid, which is great functionality-wise but bloats the Exe. > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 24, 2005 11:44 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Contrib uploads > > On Thu, 2005-03-24 at 11:24 +0200, Cariotoglou Mike wrote: > > I tried to upload a new version of sqlite3Explorer, and I > got back the > > error: > > "Too much POST data". > > How big of an upload are we talking about? > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > >
[sqlite] Contrib uploads
I tried to upload a new version of sqlite3Explorer, and I got back the error: "Too much POST data". I assume there is a limit to the size we can upload. If so, can it be extended a little? If not, anybody interested in sqlite3Explorer should contact me to see how I can send the file to you. However, since I see that there are 13,000 downloads, I would not be very excited about e-mailing 13,000 copies...
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
as a suggestion to the list, this issue should be answered only by those that disagree, else we will get 2k messages saying why not..
RE: [sqlite] [ANN] SQLite Analyzer 3
>From a quick look at sqlite analyzer, I would say the following : 1. it is not free 2. it has better looks than sqlite3Explorer 3. it cannot update tables using an editable grid approach 4. it does not have a visual query builder 5. I am not sure it has ADO import/export capabilities -Original Message- From: Eugene Wee [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 16, 2005 7:57 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] [ANN] SQLite Analyzer 3 Hi people, I'm relatively new to SQLite, and would like some comments as to the GUI tools listed in the wiki. In particular, does anyone actually use SQLite Analyzer 3 around here? If so, how does it compare to sqlite3Explorer? For reference sqlite3Explorer is the one on sqlite.org/contrib, and SQLite Analyzer at http://www.kraslabs.com/sqlite_analyzer.html Thanks, Eugene Wee
RE: [sqlite] Database Version 2 or 3, can you query for it?
The only way is to query one of the exported functions (sqlite3_version or something like this). I agree, however, that it would be nice to have a version resource in the DLL. Also, If somebody would take the trouble to do it, I would very much appreciate it if a proper MsVC project file , with appropriate config options (and version resource) could be part of the distribution. I would do it, only I am not a good user of MSVC so would probably botch it. > Is it possible to determine what the version number of the DLL > (Windows) is? The DLL doesn't seem to contain any version information > > >
RE: [sqlite] ticket 1147
Sure, an API for returning result set info would be fine, and more convenient than these pragmas, which for one thing are stateful, and thus hell for wrapper writers, which need to assume that only the *wrapper* may set these pragmas. Also, in order to avoid API explosion, I feel that a single api function, named , say, sqlite3_column_origin, could return all relevant information, such as database, table, column names, and as a bonus, primary key and "required" (not NULL) flags. Still, and in order not to lose focus on ticket 1147, I reported a *bug* in the current implementation. The bug *is* there, and very easy to reproduce. I feel that this should either be fixed, or the pragmas removed altogether. From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Mon 2/28/2005 9:24 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] ticket 1147 On Mon, 2005-02-28 at 11:54 -0700, Robert Simpson wrote: > Column Name - The name of the column as specified in the SELECT clause and > what SQLite already generates > Base Table - The base table the column came from or NULL if the column was > computed > Base Column - The base column of the table the column came from or NULL if > the column was computed > Catalog - The database the column came from or NULL if the column was > computed. > OK. This is progress. Now I understand that people need the database, table, and column that resultset values originate from in order to automatically construct an appropriate UPDATE statement. That makes sense. Wouldn't it be better to provide this information with a new API rather than depend on a column naming convention? That would avoid ambiguity in cases where users create dodgy column names that contain characters like space and '.' If such a new API appears soon, would people (please!) stop using those short_column_names and long_column_names pragmas? -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] ticket 1147
I have opened a ticket (#1147) for the full_column_names issue, which is back in 3.1.3. pls check it out. also, I noticed the following : when selecting from a view, and duplicate column names exist, there is an attempt to de-dupe them, by adding a sequence number, like this: ID ID:1 ID:2 etc this however does not happen with duplicate columns that are returned from a query. so, the statement: select * from master,detail where detail.masterID=master.ID and, create view v1 as select * from master,detail where detail.masterID=master.ID; select * from v1; do not have the same result wrt to column names. I feel this is inconsistent. you should either de-dupe all result sets (IMHO a bad idea), or leave all result sets alone, as far as names is concerned (my suggestion). I understand that this "column names" issue is becoming a pain for the sqlite authors, but OTOH, it is very important for wrapper authors...
RE: [sqlite] Version 3.1.3 is a headache
The fact is, the pragmas regarding column names now seem completely broken, as they do absolutely nothing. Was this by design, or a new bug ? -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 1:08 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Version 3.1.3 is a headache On Sat, 2005-02-26 at 22:43 +0100, Jakub Adamek wrote: > Hi, I really love SQLite, but upgrading to 3.1.3 was not a good idea. > I have already posted 3 tickets with rather serious problems with > column names. > > The last one is really annoying and I can't believe the auto-tests > could have missed it ... > >create table a (id, x); >create table b (id, y); >insert into a values (1,1); >insert into b values (1,2); >select * from a inner join b; > > column names returned: id,x,id,y How am I supposed to use such > column names? Ouwey. No wonder that my C++ wrapper does not want to > work with such a result set. > Your tickets are unhelpful and will likely be ignored. Rather than complain about the column names, perhaps you can present alternative suggestions. Posting what PostgreSQL, Oracle, and MySQL do with the same queries would be a good start. Explaining why you think the current names are undesirable (instead of just saying "Ouwey") would also be a positive step toward getting the problem addressed. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Feature request
> At 11:20 AM -0700 2/18/05, Robert Simpson wrote: > >PRAGMA real_column_names=0|1 YES, PLEASE. I also need this, as I am writing an OLEDB provider
RE: [sqlite] BLOB versus table storage
I agree. I have done similar work for GIS data. Unless you absolutely need access to inidvidual x,y data at the *sql* level, it is much better to use memory storage for these. And, if you plan to do operations like point-in-polygon, which I am sure you will, you need access to all the points at the same time, which means that streaming them into a BLOB will always be much better. One tip: if you can, use a binary storing technqiue instead of a text//based one. Converting to/from floating point numbers is a rather expensive operation. So, serialize the points to a data structure, and write it with one operation. It helps if you violate some design rules, when you do this. Consider this: In Delphi, which is my native language, the *good* design (theoretically speaking) would be this : Type Tpoint=class x, y:double End; Tpolygon=class points:tlist; ... Other properties and methods End; Or even : Tpolygon=class points:array of tpoint; End; However, this design does not serialize wellm as each TPOINT element is not just data, but has "hidden" pointer that cannot be serialized Now, consider this: Tpoint=RECORD x,y:double End; Tpolygon=class points:array of tpoint; End; Now, "points" is a sequencial array of records in memory, so you can stream to/from a blob with a single operation... > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 17, 2005 5:30 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] BLOB versus table storage > > On Thu, 2005-02-17 at 10:12 -0500, [EMAIL PROTECTED] wrote: > > Does anyone have a recommendation as to which solution > would be more > > optimal, both for space and processing time? In a worst case > > scenario, I could insert over 1 million polygons, resulting > in a max > > 20 million point list table. > > > > I think storing the points in a BLOB will likely be much > faster and more compact too. The downside is that you cannot > query for polygons by point values (unless, perhaps, you > create some custom SQL functions to scan the > BLOBs.) > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > >
RE: [sqlite] How can I verify that the index is really used?
Look at the "OpenRead" opcodes in the EXPLAIN list. Check the second paramter (I think), it is the root page in the sqlite_master table. It shows you if the engine opens index files or not. > -Original Message- > From: Serge Liber [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 15, 2005 2:51 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] How can I verify that the index is really used? > > Hi, > > How can I verify that my > SELECT * FROM my_table WHERE master_id = 5 statement uses the > index created in the field master_id in my_table? > EXPLAIN doesn't explain it to me. > > Thanx, > > Serge Liber >
RE: [sqlite] VACUUM question
It has been my experience that, when two processes have the same db open, and one of them does a VACUUM, and the other tries to update, The database gets corrupted. Not easy to simulate, but has happened at least twice, in fact it has been the only way I managed to corrupt a database. Comments, DRH ? > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 10, 2005 12:14 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] VACUUM question > > On Wed, 2005-02-09 at 14:56 -0500, Luc Vandal wrote: > > if my app has a sqlite3* pointer to the database and > another app does > > a VACUUM on the same database, will the pointer still be valid? > > Yes. > > > > > > Also, what if, while doing a VACUUM, a query executes on > the database? > > Will it just fail? > > You will get SQLITE_BUSY. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > >
RE: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
out of curiocity, can you give a try to my delphi wrappers? http://www.sqlite.org/contrib (not Tdataset replacement, though, but they should be quite fast for this reason. also try the "serverCursor" setting, which allows you to step thorugh the result set one at a time, meaning there is no double-buffering required: rs:=db.createStatement; rs.serverCursor:=true; rs.open while not rs.eof do.. let me know how they fare speed-wise, as I have not tested any other wrappers.. From: Chris Schirlinger [mailto:[EMAIL PROTECTED] Sent: Thu 3/2/2005 6:06 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow? No, the DLL wasn't the issue (heck the entire program is a DLL anyway and that's worked well for ages) We eventually discoverd through profiling the Delphi componants we had started using were the slow point, tossed them, and tried someone elses version (some stuff adapted by someone called Tim A.) and they work MUCH better It seems the chokepoints where in the wrapper code itself, perhaps in the mechanisms calling the various SQLite functions. I'd dig deeper but frankly the wrapper we have now lets me get to the bare bones easier. I am pleasently surprised about SQLites power now, for a large single user DB it works nicely. Sure, when we tossed a further 8 million rows into the DB it is slows down, but still ~1 secondish for a 3k select statement Still trying to wrench every piece of speed I can from the DB, and some functions don't seem to be working? (or I am misunderstanding them) For example: CREATE UNIQUE INDEX pk ON myTable (Field1, Field2) ON CONFLICT REPLACE; runs but doesn't create the ON CONFLICT part. When you examine the schema you see : CREATE UNIQUE INDEX pk ON myTable (Field1, Field2); Also any INSERT statement evaluate as if the default is still FAIL Still not really required, INSERT OR REPLACE works well. Was hoping setting the default CONFLICT handeler may speed things up a bit > I wouldn't think DLL calling overhead would be significant > when dealing with things as slow (relatively) as a database. > > Is it really necessary for it to be a DLL? > You might be able to statically link it and remove that overhead. > Are you using COM or ActiveX to call it? If I remember right > they had a lot more overhead than a vanilla DLL. > > Sounds like a job for the profiler!
RE: [sqlite] foreign keys? (sqlite3)
Sqlite parser DOES parse foreign key constraints. Try this: create table t1( id integer, id1 integer, id2 integer, id3 integer, foreign key (id1,id2) references anotherTable(id,id1), foreign key (id3) references somethingElse(id) ); pragma foreign_key_list(t1); However, this does not mean that it actually DOES anything with this info, it is just there for you to act, not for the engine (yet) > -Original Message- > From: Dick Davies [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 01, 2005 3:52 PM > To: SQLite > Subject: [sqlite] foreign keys? (sqlite3) > > > The docs seem to say sqlite3 does'nt support foreign keys, is > that correct? > > If so, I don't understand what > > ---8<- > --- > # > PRAGMA foreign_key_list(table-name); > > For each foreign key that references a column in the argument > table, invoke the callback function with information about > that foreign key. > The callback function will be invoked once for each column in > each foreign key. > ---8<- > --- > > actually does? > > -- > 'The pie is ready. You guys like swarms of things, right?' > -- Bender > Rasputin :: Jack of All Trades - Master of Nuns > > >