Re: [sqlite] Proposal: sqlite3_column_rowid
Hi Derrel > --- Ursprüngliche Nachricht --- > Von: [EMAIL PROTECTED] > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] Proposal: sqlite3_column_rowid > Datum: Tue, 16 May 2006 09:12:37 -0400 > What does it do with queries that are pulling data from multiple tables? > How about computed column data? > > Consider these queries: > SELECT a.c2, b.c3 FROM a, b WHERE a.c1 = b.c1; > SELECT c1 + c2 FROM a; Ado and OLEDB supports Information in the Fieldcollection of a Recordset like the following snips: if (DBFieldInfo->Attributes & adFldMayDefer) dothis... if (DBFieldInfo->Attributes & adFldUpdatable) etc. if (DBFieldInfo->Attributes & adFldUnknownUpdatable) if (DBFieldInfo->Attributes & adFldIsNullable) if (DBFieldInfo->Attributes & adFldRowID) if (DBFieldInfo->Attributes & adFldKeyColumn That would be right to solve the Problems... think so... In ADO/OLEDB I request this infos and respect them. Regards, Anne -- Echte DSL-Flatrate dauerhaft für 0,- Euro*! "Feel free" mit GMX DSL! http://www.gmx.net/de/go/dsl
[sqlite] Re: spatial sqlite anyone ?
Hello dear Noel, hello all sqlite users, a spatial extension for sqlite would be nice, although I think that replacing the indexing scheme (e.g. replace the current b-tree with a quad-tree or another spatial index) is alot of work. Just some questions / thoughts: 1. How would you handle indexing? Would you replace completely the btree with a spatial index (the hardest thing to do)? And if so, btree indexing would still be available or not? 2. Go for GEOS's STRTree. It's the fastest spatial index I could find (I did some research awhile ago on open-source solutions regarding spatial indexes). 3. You might want to take a look at SpatialIndex (http://u-foria.org/marioh/spatialindex/). The library is pretty clever designed and it supports RSTAR-tree, MVR-tree and TPR-tree. The problem I found with this library was that it was slower than GEOS at the time I last checked. One of the features I like is that it allows persisting the index. This might be an intermediate solution: you could store the index as a blob in the database. It would not be very efficient (as efficient as replacing indexing) but it would be a place to start. 4. Another nice library who might do the job is CGAL (http://www.cgal.org/). I didn't benchmarked it mostly because GEOS suited my needs just fine, but how knows, it might be better than the ones I mentioned. George.
RE: [sqlite] Control the scheme layout
Selon "Griggs, Donald" <[EMAIL PROTECTED]>: > If I understand your question, the FAQ may help: > > http://sqlite.org/faq.html#q9 Oops, I did not see this point. But the FAQ does not explain how to retreive the field names from a table. It deals with the indices only and I think indice != field. Thanks. -- Delf
[sqlite] Time difference for insertion and retrieval
Hi all, I am using prepared statement for insertion and retrieval of records to and from the database. The time it takes to insert and retrieve 10,00,000 records varies in different trials. The time for different trials are as follows. For inserting 16 byte string the time taken are 14.850797, 17.057271, 17.637058, 19.796403, 17.503216. For retrieving 10,00,000 records randomly, time taken are, 50.308758, 48.103936, 49.521511, 49.227987, 45.499152. I would like to know y this time difference showing. I am using the following code for calculating the time. start_time = getTime(); sqlite3_step(insert); end_time = getTime(); where getTime is the function which returns the time in microseconds. With Regards, Anish Enos Mathew The information contained in, or attached to, this e-mail, contains confidential information and is intended solely for the use of the individual or entity to whom they are addressed and is subject to legal privilege. If you have received this e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your system and notify your system manager. Please do not copy it for any purpose, or disclose its contents to any other person. The views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of the company. The recipient should check this e-mail and any attachments for the presence of viruses. The company accepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email. www.aztecsoft.com
[sqlite] add primary key after inserts?
As I understand SQL, "alter table blah add primary key (blah1, blah2)" should be how you do it. The sqlite documentation seems to say otherwise. Actually, I cannot figure out from the documentation how to add a primary key after the table is created and data is entered. How is it done? Or is an Index sufficient? Now that I've asked the question, here's some FYI from my experimentation. I have a table that looks like this: CREATE TABLE IF NOT EXISTS results (qi INTEGER NOT NULL, ri INTEGER NOT NULL, run INTEGER NOT NULL, drl INTEGER NOT NULL, score INTEGER NOT NULL, qis INTEGER NOT NULL, ris INTEGER NOT NULL ); I originally had this line as well: , PRIMARY KEY (qi, ri, run) I need to do billions (no joke) of consecutive inserts into this table. Writing binary out with std::ofstream was using 26% of my code execution time according to the profiler. I changed to sqlite3, and now the sqlite3_step function uses 50% of my execution time. After reading the news groups, I removed the primary key. That dropped it down to about 41%. That was significant. However, I was still significantly slower than binary writes with ofstream. Then, I tried the PRAGMA temp_store = 2. That made absolutely no difference. I'll assume that's what it was to begin with, though it reports 0. Also, from the profiler, it seems that the step function does a lot of memory allocations and deallocations, yet I cannot find them in the code. If it helps, I was testing 600k inserts in transactions of 0xFFF inserts and my current settings: PRAGMA auto_vacuum = 0; \ PRAGMA case_sensitive_like = 1; \ PRAGMA cache_size = 32768; \ PRAGMA default_cache_size = 32768; \ PRAGMA count_changes = 0; \ PRAGMA synchronous = 0; \ PRAGMA page_size = 4096; \ PRAGMA temp_store = 2; What else can I do to speed up my inserts? Thanks, Brannon
[sqlite] SQLite db file size and NFS file locking issue
Hello- Is there any limit (besides the OS files system limit) on how big the db file size can be with SQLite? Also, is there any NFS locking issue with SQLite database? Sometimes if a client opens the files and die, do we have problems reading the file again because NFS lock for the file still exists!? Is this true? Rajan
Re: [sqlite] Fwd: sqlite 2 versus sqlite3 and callbacks
Sqlite3_exec is three seperate sqlite function, prepare, bind and step. Just use them. JS Patty Fernandez wrote: Hello, Sqlite version 2 used to split the sqlite_exec routine into three separate functions utilizing a structure called sqlite_vm. This would allow the access of data without using a callback function. Did these separate functions remerge in Sqlite 3 to the sqlite3_prepare call? Thanks, patty
RE: [sqlite] Control the scheme layout
Delf, If I understand your question, the FAQ may help: http://sqlite.org/faq.html#q9 Donald Griggs Opinions are not necessarily those of Misys Healthcare Systems nor its board of directors. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 17, 2006 3:57 PM To: sqlite-users@sqlite.org Subject: [sqlite] Control the scheme layout Hi, I'm coding an C++ application based on a set of plugins. Each plugins adds a functionnary to the application and needs its own table (with fields) or a pre-existant table (with its own field). For instance, my DB contains the table TX with the field FX for the plugins X. I load the plugins Y that needs TY with FY but they do not exist. -> How to test that? I load the plugins Z that needs TX and FZ but FZ does not exist... -> How to process to know that? Thanks a lot for your next answers. -- Delf
[sqlite] Re: sqlite 2 versus sqlite3 and callbacks
Patty Fernandez <[EMAIL PROTECTED]> wrote: Sqlite version 2 used to split the sqlite_exec routine into three separate functions utilizing a structure called sqlite_vm. This would allow the access of data without using a callback function. Did these separate functions remerge in Sqlite 3 to the sqlite3_prepare call? See sqlite3_prepare, sqlite3_step, sqlite3_reset, sqlite3_finalize, sqlite3_bind_*, sqlite3_column_* Igor Tandetnik
[sqlite] Fwd: sqlite 2 versus sqlite3 and callbacks
Hello, Sqlite version 2 used to split the sqlite_exec routine into three separate functions utilizing a structure called sqlite_vm. This would allow the access of data without using a callback function. Did these separate functions remerge in Sqlite 3 to the sqlite3_prepare call? Thanks, patty
RE: [sqlite] Make for windows
Thanks. That command created the files; however, grep sqlite3_sleep ./tsrc/* reports: sqlite3.h:int sqlite3_sleep(int); and that's all. In other words, it's declared but never defined. > So I downloaded the latest CVS files on my Linux box. What do I run to > generate the c files I need for compilation on my Windows box? In > other words, what make command is used to generate the files for the > zip = source download? Where do the files end up? Do I need any > special parameters to configure or make for this to work right? > > I searched the newsgroup for this information, but was unsuccessful. > Is there a link that explains it already available? > Edit Makefile.linux-gcc to suit your setup. Then type make target_source Windows sources will appear in the "tsrc" subdirectory. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Control the scheme layout
Hi, I'm coding an C++ application based on a set of plugins. Each plugins adds a functionnary to the application and needs its own table (with fields) or a pre-existant table (with its own field). For instance, my DB contains the table TX with the field FX for the plugins X. I load the plugins Y that needs TY with FY but they do not exist. -> How to test that? I load the plugins Z that needs TX and FZ but FZ does not exist... -> How to process to know that? Thanks a lot for your next answers. -- Delf
Re: [sqlite] Make for windows
"Brannon King" <[EMAIL PROTECTED]> wrote: > So I downloaded the latest CVS files on my Linux box. What do I run to > generate the c files I need for compilation on my Windows box? In other > words, what make command is used to generate the files for the zip = > source > download? Where do the files end up? Do I need any special parameters to > configure or make for this to work right? > > I searched the newsgroup for this information, but was unsuccessful. Is > there a link that explains it already available? > Edit Makefile.linux-gcc to suit your setup. Then type make target_source Windows sources will appear in the "tsrc" subdirectory. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Make for windows
So I downloaded the latest CVS files on my Linux box. What do I run to generate the c files I need for compilation on my Windows box? In other words, what make command is used to generate the files for the zip source download? Where do the files end up? Do I need any special parameters to configure or make for this to work right? I searched the newsgroup for this information, but was unsuccessful. Is there a link that explains it already available? Thanks for your time. __ Brannon King ¯¯
Re: [sqlite] Réf. : Re: [sqlite] Réf. : Re: [sqlite] DB in memory - table create won't work
your solution is most probably in os_unix.c, have a look at the file system APIs... Mario Hebert Legerity "Night Media LTD" <[EMAIL PROTECTED]> 05/17/2006 01:28 PM Please respond to sqlite-users@sqlite.org To cc Subject [sqlite] Réf. : Re: [sqlite] Réf. : Re: [sqlite] DB in memory - table create won't work Oh ... !! Because its a special filesystem ! I got this problem on a VPS server running with XEN 3.0 And the harddisk is /dev/sda1 (but don't know the filesystem). Best Regards Derel Bruno ---Message original--- De : [EMAIL PROTECTED] Date : 05/17/06 20:19:55 A : sqlite-users@sqlite.org Sujet : Re: [sqlite] Réf. : Re: [sqlite] DB in memory - table create won't work Well, I could understand more your situation since it was probably because The sync with the hardware was not ported properly to your platform and Data was not being saved to the media. Although, I just can't make sense Why the opposite happens to me *sigh* Mario Hebert Legerity "Night Media LTD" <[EMAIL PROTECTED]> 05/17/2006 01:06 PM Please respond to sqlite-users@sqlite.org To CC Subject [sqlite] Réf. : Re: [sqlite] DB in memory - table create won't work Got exactly the SAME issue With PHP 4.3 and the sqlite 3 extension. But was reversed. When creating :memory: database, its worked, But when creating :file database I got the "No such table" problem. I ve let down sqlite . And I ve retake mysql. Good luck Best Regards Derel Bruno ---Message original--- De : Jay Sprenkle Date : 05/17/06 20:03:17 A : sqlite-users@sqlite.org Sujet : Re: [sqlite] DB in memory - table create won't work On 5/17/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I got flash database to work for me. What I am trying now is to create a > database in memory that I could sync later on in flash. The problem is > that when I do a table create it does not seem to work: > > 1. I open database : memory: with success. > 2. I create a random table: create table test ( a int ); > 3. I try to do an insert: insert into test values (5); > > I get the following error, > > No such table: test Works ok for me: C:\Temp\>sqlite3 :memory: SQLite version 3.0.8 Enter ".help" for instructions Sqlite> .schema Sqlite> create table test(int a ); Sqlite> insert into test values(5); Sqlite> .schema CREATE TABLE test(int a ); Sqlite> select * from test; 5 Sqlite> __ Information NOD32 1.1543 (20060517) __ Ce message a ete verifie par NOD32 Antivirus System. http://www.nod32.com
[sqlite] Réf. : Re: [sqlite] Réf. : Re: [sqlite] DB in memory - t able create won't work
Oh ... !! Because its a special filesystem ! I got this problem on a VPS server running with XEN 3.0 And the harddisk is /dev/sda1 (but don't know the filesystem). Best Regards Derel Bruno ---Message original--- De : [EMAIL PROTECTED] Date : 05/17/06 20:19:55 A : sqlite-users@sqlite.org Sujet : Re: [sqlite] Réf. : Re: [sqlite] DB in memory - table create won't work Well, I could understand more your situation since it was probably because The sync with the hardware was not ported properly to your platform and Data was not being saved to the media. Although, I just can't make sense Why the opposite happens to me *sigh* Mario Hebert Legerity "Night Media LTD" <[EMAIL PROTECTED]> 05/17/2006 01:06 PM Please respond to sqlite-users@sqlite.org To CC Subject [sqlite] Réf. : Re: [sqlite] DB in memory - table create won't work Got exactly the SAME issue With PHP 4.3 and the sqlite 3 extension. But was reversed. When creating :memory: database, its worked, But when creating :file database I got the "No such table" problem. I ve let down sqlite . And I ve retake mysql. Good luck Best Regards Derel Bruno ---Message original--- De : Jay Sprenkle Date : 05/17/06 20:03:17 A : sqlite-users@sqlite.org Sujet : Re: [sqlite] DB in memory - table create won't work On 5/17/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I got flash database to work for me. What I am trying now is to create a > database in memory that I could sync later on in flash. The problem is > that when I do a table create it does not seem to work: > > 1. I open database : memory: with success. > 2. I create a random table: create table test ( a int ); > 3. I try to do an insert: insert into test values (5); > > I get the following error, > > No such table: test Works ok for me: C:\Temp\>sqlite3 :memory: SQLite version 3.0.8 Enter ".help" for instructions Sqlite> .schema Sqlite> create table test(int a ); Sqlite> insert into test values(5); Sqlite> .schema CREATE TABLE test(int a ); Sqlite> select * from test; 5 Sqlite> __ Information NOD32 1.1543 (20060517) __ Ce message a ete verifie par NOD32 Antivirus System. http://www.nod32.com
[sqlite] sqlite3_sleep
I downloaded the "sqlite-source-3_3_5.zip" file, dropped it into a VC7.1 static lib project and compiled it. That worked fine (aside from numerous warnings about sizeof(size_t) possibly != sizeof(int)) , however it is missing the sqlite3_sleep function, which appears to reside in "experimental.c", not included in that zip package. Can I get the current "experimental.c" file out of CVS and add that to my project? Also, when can we expect an official release that includes the speedup discussed on the newsgroup previously ( http://www.mail-archive.com/sqlite-users%40sqlite.org/msg14954.html ) ? Thanks for your time. __ Brannon King ¯¯
Re: [sqlite] Réf. : Re: [sqlite] DB in memory - table create won't work
Well, I could understand more your situation since it was probably because the sync with the hardware was not ported properly to your platform and data was not being saved to the media. although, I just can't make sense why the opposite happens to me *sigh* Mario Hebert Legerity "Night Media LTD" <[EMAIL PROTECTED]> 05/17/2006 01:06 PM Please respond to sqlite-users@sqlite.org To cc Subject [sqlite] Réf. : Re: [sqlite] DB in memory - table create won't work Got exactly the SAME issue With PHP 4.3 and the sqlite 3 extension. But was reversed. When creating :memory: database, its worked, But when creating :file database I got the "No such table" problem. I ve let down sqlite . And I ve retake mysql. Good luck Best Regards Derel Bruno ---Message original--- De : Jay Sprenkle Date : 05/17/06 20:03:17 A : sqlite-users@sqlite.org Sujet : Re: [sqlite] DB in memory - table create won't work On 5/17/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I got flash database to work for me. What I am trying now is to create a > database in memory that I could sync later on in flash. The problem is > that when I do a table create it does not seem to work: > > 1. I open database : memory: with success. > 2. I create a random table: create table test ( a int ); > 3. I try to do an insert: insert into test values (5); > > I get the following error, > > No such table: test Works ok for me: C:\Temp\>sqlite3 :memory: SQLite version 3.0.8 Enter ".help" for instructions Sqlite> .schema Sqlite> create table test(int a ); Sqlite> insert into test values(5); Sqlite> .schema CREATE TABLE test(int a ); Sqlite> select * from test; 5 Sqlite>
[sqlite] Réf. : Re: [sqlite] DB in memory - table create won't wor k
Got exactly the SAME issue With PHP 4.3 and the sqlite 3 extension. But was reversed. When creating :memory: database, its worked, But when creating :file database I got the "No such table" problem. I ve let down sqlite . And I ve retake mysql. Good luck Best Regards Derel Bruno ---Message original--- De : Jay Sprenkle Date : 05/17/06 20:03:17 A : sqlite-users@sqlite.org Sujet : Re: [sqlite] DB in memory - table create won't work On 5/17/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I got flash database to work for me. What I am trying now is to create a > database in memory that I could sync later on in flash. The problem is > that when I do a table create it does not seem to work: > > 1. I open database : memory: with success. > 2. I create a random table: create table test ( a int ); > 3. I try to do an insert: insert into test values (5); > > I get the following error, > > No such table: test Works ok for me: C:\Temp\>sqlite3 :memory: SQLite version 3.0.8 Enter ".help" for instructions Sqlite> .schema Sqlite> create table test(int a ); Sqlite> insert into test values(5); Sqlite> .schema CREATE TABLE test(int a ); Sqlite> select * from test; 5 Sqlite>
Re: [sqlite] DB in memory - table create won't work
Actually I forgot to double check with the cygwin version I have. Makes me wonder if I made something goofy in my port, but it should reside in memory so I am puzzled about what could be wrong. The same piece of software works when I write to flash Puzzled Mario Hebert Legerity "Jay Sprenkle" <[EMAIL PROTECTED]> 05/17/2006 12:56 PM Please respond to sqlite-users@sqlite.org To sqlite-users@sqlite.org cc Subject Re: [sqlite] DB in memory - table create won't work On 5/17/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I got flash database to work for me. What I am trying now is to create a > database in memory that I could sync later on in flash. The problem is > that when I do a table create it does not seem to work: > > 1. I open database :memory: with success. > 2. I create a random table: create table test ( a int ); > 3. I try to do an insert: insert into test values (5); > > I get the following error, > > no such table: test works ok for me: C:\Temp\>sqlite3 :memory: SQLite version 3.0.8 Enter ".help" for instructions sqlite> .schema sqlite> create table test(int a ); sqlite> insert into test values(5); sqlite> .schema CREATE TABLE test(int a ); sqlite> select * from test; 5 sqlite>
RE: [sqlite] spatial sqlite anyone ?
Hello Noel- I am interested in this as well - I have wanted spatial feature in SQLite for a while. I think is a great idea and would be useful for many powerful applications. Please keep me (us) posted on this. Would this go natively inside SQLite engine? Vivek >-Original Message- >From: Noel Frankinet [mailto:[EMAIL PROTECTED] >Sent: Wednesday, May 17, 2006 2:42 AM >To: sqlite-users@sqlite.org >Subject: [sqlite] spatial sqlite anyone ? > >Hello all, > >I would like to set up a project to "spatialise" sqlite. > 1 - to be able to create geometry colum that would store >points,multipoints,lines,multilines,polygones and multi-polygones (Ogis >"simple features") > 2 - to be able to load and exchange data from WKT (well know text >format) and binary (shape file for instance) > 3 - to create a spatial index on geometry data (either an quadtree or a >more advanced Rtree). > 4 - to do spatial operation (join, disjoin, near, ...) on those data. > >I'm ready to contribute code for 1 and 2, there are open-source c or c++ >library for 3 and 4. > >Ideally source should be on sqlite.org. > >What do you think ? Any interest ? > >-- >Noël Frankinet >Gistek Software SA >http://www.gistek.net
Re: [sqlite] DB in memory - table create won't work
On 5/17/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I got flash database to work for me. What I am trying now is to create a database in memory that I could sync later on in flash. The problem is that when I do a table create it does not seem to work: 1. I open database :memory: with success. 2. I create a random table: create table test ( a int ); 3. I try to do an insert: insert into test values (5); I get the following error, no such table: test works ok for me: C:\Temp\>sqlite3 :memory: SQLite version 3.0.8 Enter ".help" for instructions sqlite> .schema sqlite> create table test(int a ); sqlite> insert into test values(5); sqlite> .schema CREATE TABLE test(int a ); sqlite> select * from test; 5 sqlite>
[sqlite] DB in memory - table create won't work
I got flash database to work for me. What I am trying now is to create a database in memory that I could sync later on in flash. The problem is that when I do a table create it does not seem to work: 1. I open database :memory: with success. 2. I create a random table: create table test ( a int ); 3. I try to do an insert: insert into test values (5); I get the following error, no such table: test what could I do wrong ? Regards, Mario Hebert Legerity
[sqlite] Adding BeOS support to sqlite
Because Mozilla Firefox now incorporates sqlite, the Bezilla team has created added code to support THREADSAFE operation under BeOS, Zeta and Haiku OSs. If possible, we'd like to submit this code to the official sqlite repository. What is the process for submitting changes to sqlite code for review/incorporation?
Re: [sqlite] Proposal: sqlite3_column_rowid
Ralf Junker wrote: Even if sqlite3_column_rowid can sometimes not exactly identify a data field, it can still do so reliably with the majority of queries IMHO. It could at least do so much better than any other work-around I can think of. This would finally enable applications to allow users to edit most of their custom queries and views which are not already build into the application. I agree. This is why your proposal would be very useful, even if there are a few corner cases to watch out for. Select statement 1: SELECT c1, c2, c3 FROM a NATURAL JOIN b; Results: sqlite_column_int(0) returns 1 sqlite_column_table_name(0) returns 'a' sqlite_column_origin_name(0) returns 'c1' -- Select statement 2: SELECT c1, c2, c3 FROM a JOIN b USING(c1); Results: sqlite_column_int(0) returns 1 sqlite_column_table_name(0) returns 'a' sqlite_column_origin_name(0) returns 'c1' Technically SQLite gets both of these results wrong. The value for c1 returned by these select statements are not supposed to be taken from table a. The sqlite_column_table_name should return a null value. The origin name could still be returned, because the column name is still c1. The SQL standard says that a USING clause provides a list of common column names that must compare equal to implement the join. Only one copy of the common column data is returned in the result, and the common column names (the names that appears in the USING clause) cannot be qualified in the SELECT statement that contains that USING clause. The same thing applies to a NATURAL join, since that is really just a shortcut for a USING clause with all the names that are common between the two tables. SQLite should generate an error for the qualified c1 names in all the queries below. The c1 value is not from either table, and so qualifying the common name with a table name is illegal. SQLite version 3.3.5 Enter ".help" for instructions sqlite> CREATE TABLE a (c1, c2); sqlite> INSERT INTO a VALUES (1, 2); sqlite> sqlite> CREATE TABLE b (c1, c3); sqlite> INSERT INTO b VALUES (1, 3); sqlite> sqlite> SELECT a.c1, c2, c3 FROM a NATURAL JOIN b; 1|2|3 sqlite> SELECT b.c1, c2, c3 FROM a NATURAL JOIN b; 1|2|3 sqlite> SELECT a.c1, c2, c3 FROM a JOIN b USING(c1); 1|2|3 sqlite> SELECT b.c1, c2, c3 FROM a JOIN b USING(c1); 1|2|3 sqlite> This should probably be fixed when your proposal is implemented. Dennis Cote
Re: [sqlite] calling convention ...
cdecl throughout, and remember your callback & SQL function definitions as well ;-) On 17/05/06, Marten Feldtmann <[EMAIL PROTECTED]> wrote: I wanted to use the prebuild dll of sqlite - and I was looking for the information, what calling convention was used within the sqlite.dll. It seems to me, that "cdecl" is used and NOT "stdcall" - right ? Marten
[sqlite] calling convention ...
I wanted to use the prebuild dll of sqlite - and I was looking for the information, what calling convention was used within the sqlite.dll. It seems to me, that "cdecl" is used and NOT "stdcall" - right ? Marten
Re: AW: [sqlite] spatial sqlite anyone ?
Martin Pfeifle wrote: I am very interested. We are working on spatial sqlite for almost one year. We plan to include sqlite into an embedded spatial application. You mention that there are open-source code for library 3 and 4. Can you give me a hint where to find it? I will contact you at the end of the week providing more information about our "spatial sqlite" and the requirements of our application. Best Martin Hello Martin I also have some working code. I use it on a pda application. for point 3 and 4 I was thinking to the rtree library (i don't have a link right now, but google knows it), and the geos lib (from posgis). I wait for your mail, I hope that we will find contributor, a spatial sqlite would be a "killer" app I think. Best wishes -- Noël Frankinet Gistek Software SA http://www.gistek.net
Re: [sqlite] Sub select problem
On Wed, 17 May 2006, Malk0 wrote: [...] BUT if i add a GROUP BY clause in the subselect the results returned in ct is just false ex: - doing SELECT count(*) FROM (SELECT * FROM mytable GROUP BY col1) _TMPTABLE_; return one row with 20 in ct and that's FALSE, this is the total amount of row in mytable instead of the amount of grouped row Am i doing something wrong? tested on a mysql database give me correct results, is this a bug, is there any way to work around? [...] Hello, I tested this with SQLite 3.3.5 and it works just fine. But to circumvent the problem you could apply some aggregate function to your data in the sub select, e.g. SELECT count(*) FROM (SELECT count(*) FROM table GROUP BY column); Looks strange, but works on SQLite 2.8.13, the only old SQLite2 installation I still had lying around on an old box by chance. cu, Thomas
AW: [sqlite] spatial sqlite anyone ?
I am very interested. We are working on spatial sqlite for almost one year. We plan to include sqlite into an embedded spatial application. You mention that there are open-source code for library 3 and 4. Can you give me a hint where to find it? I will contact you at the end of the week providing more information about our "spatial sqlite" and the requirements of our application. Best Martin - Ursprüngliche Mail Von: Noel Frankinet <[EMAIL PROTECTED]> An: sqlite-users@sqlite.org Gesendet: Mittwoch, den 17. Mai 2006, 11:42:07 Uhr Betreff: [sqlite] spatial sqlite anyone ? Hello all, I would like to set up a project to "spatialise" sqlite. 1 - to be able to create geometry colum that would store points,multipoints,lines,multilines,polygones and multi-polygones (Ogis "simple features") 2 - to be able to load and exchange data from WKT (well know text format) and binary (shape file for instance) 3 - to create a spatial index on geometry data (either an quadtree or a more advanced Rtree). 4 - to do spatial operation (join, disjoin, near, ...) on those data. I'm ready to contribute code for 1 and 2, there are open-source c or c++ library for 3 and 4. Ideally source should be on sqlite.org. What do you think ? Any interest ? -- Noël Frankinet Gistek Software SA http://www.gistek.net
[sqlite] Sub select problem
First of all i'm using Sqlite for 2 years now and just want to tell it's great! that's done and now here's why i'm sending this message to the list: I'm working on pagination and want to retrieve the number of row that i would normally retrieve without the Limit clause. So i hoped to use subselect to do such thing but discover a weird behaviour and perhaps a bug? background info : - doing SELECT * FROM mytable i get 20 rows and that's fine - doing SELECT * FROM mytable GROUP BY col1 return 10 rows and that's fine too - doing SELECT count(*) as ct FROM (SELECT * FROM mytable) _TMPTABLE_; just work fine too i retrieve one row with ct = 20 BUT if i add a GROUP BY clause in the subselect the results returned in ct is just false ex: - doing SELECT count(*) FROM (SELECT * FROM mytable GROUP BY col1) _TMPTABLE_; return one row with 20 in ct and that's FALSE, this is the total amount of row in mytable instead of the amount of grouped row Am i doing something wrong? tested on a mysql database give me correct results, is this a bug, is there any way to work around? At least is there a better way to get the total amount of row a query would have return without a LIMIT clause as using SQL_CALC_FOUND_ROWS and FOUND_ROWS() in mysql (i would prefer that the subselect work in the good way) Thanks for your help nathan
[sqlite] spatial sqlite anyone ?
Hello all, I would like to set up a project to "spatialise" sqlite. 1 - to be able to create geometry colum that would store points,multipoints,lines,multilines,polygones and multi-polygones (Ogis "simple features") 2 - to be able to load and exchange data from WKT (well know text format) and binary (shape file for instance) 3 - to create a spatial index on geometry data (either an quadtree or a more advanced Rtree). 4 - to do spatial operation (join, disjoin, near, ...) on those data. I'm ready to contribute code for 1 and 2, there are open-source c or c++ library for 3 and 4. Ideally source should be on sqlite.org. What do you think ? Any interest ? -- Noël Frankinet Gistek Software SA http://www.gistek.net
Re: [sqlite] Proposal: sqlite3_column_rowid
Hello Dennis, please ignore my previous response - it is all wrong because I used the wrong table definitions and data (see below for corrections). My appologies for this, and thanks for holding on to sqlite3_column_rowid. >I like your proposal, and I understand what you want it for (at least I think >I do :-) ). Judging from your writing I am sure you understand perfectly well! >I just think that the cases where SQLite can't determine a unique source for >the column data, even though it may be taking the data directly from a >particular table column, should be treated the same as a calculated value and >return a sentinel rowid value of -1. I agree: whenever SQLite cannot determine a unique combination of database, table, origin and RowID - for whatever reason - sqlite3_column_rowid should return -1 or an optional error code (with the appropriate API) and set an error message, if possible. >I'm just saying that you can't update the columns used to join tables by >editing the results of a query that joins two tables using either of these >mechanisms. You can edit the other columns that result from the join, but to >edit the joined columns you would need to use a different query. I would be very happy with this, especially since joined columns are likely to be primary index columns which hardly ever require manual changes anyway. Even if sqlite3_column_rowid can sometimes not exactly identify a data field, it can still do so reliably with the majority of queries IMHO. It could at least do so much better than any other work-around I can think of. This would finally enable applications to allow users to edit most of their custom queries and views which are not already build into the application. If at all possible, I would really, really love to see sqlite3_column_rowid or some similar functionality to become part of SQLite. Are there any views from the main developers? I'd be very much interested in their feedback! Thanks & Regards, Ralf - For completeness, here are the correct test results: CREATE TABLE a (c1, c2); INSERT INTO a VALUES (1, 2); CREATE TABLE b (c1, c3); INSERT INTO b VALUES (1, 3); -- Select statement 1: SELECT c1, c2, c3 FROM a NATURAL JOIN b; Results: sqlite_column_int(0) returns 1 sqlite_column_table_name(0) returns 'a' sqlite_column_origin_name(0) returns 'c1' sqlite_column_int(1) returns 2 sqlite_column_table_name(1) returns 'a' sqlite_column_origin_name(1) returns 'c2' sqlite_column_int(2) returns 3 sqlite_column_table_name(2) returns 'b' sqlite_column_origin_name(2) returns 'c3' -- Select statement 2: SELECT c1, c2, c3 FROM a JOIN b USING(c1); Results: sqlite_column_int(0) returns 1 sqlite_column_table_name(0) returns 'a' sqlite_column_origin_name(0) returns 'c1' sqlite_column_int(1) returns 2 sqlite_column_table_name(1) returns 'a' sqlite_column_origin_name(1) returns 'c2' sqlite_column_int(2) returns 3 sqlite_column_table_name(2) returns 'b' sqlite_column_origin_name(2) returns 'c3' -- Select statement 3: SELECT * FROM a JOIN b on a.c1 = b.c1; Results: sqlite_column_int(0) returns 1 sqlite_column_table_name(0) returns 'a' sqlite_column_origin_name(0) returns 'c1' sqlite_column_int(1) returns 2 sqlite_column_table_name(1) returns 'a' sqlite_column_origin_name(1) returns 'c2' sqlite_column_int(2) returns 1 sqlite_column_table_name(2) returns 'b' sqlite_column_origin_name(2) returns 'c1' sqlite_column_int(3) returns 3 sqlite_column_table_name(3) returns 'b' sqlite_column_origin_name(3) returns 'c3' - Select statement 4: SELECT c1, c2, c3 FROM a JOIN b on a.c1 = b.c1; Results: Error SQLITE_ERROR: ambiguous column name: c1 -- Select statement 5: SELECT b.c1, c2, c3 FROM a JOIN b on a.c1 = b.c1; Results: sqlite_column_int(0) returns 1 sqlite_column_table_name(0) returns 'b' sqlite_column_origin_name(0) returns 'c1' sqlite_column_int(1) returns 2 sqlite_column_table_name(1) returns 'a' sqlite_column_origin_name(1) returns 'c2' sqlite_column_int(2) returns 3 sqlite_column_table_name(2) returns 'b' sqlite_column_origin_name(2) returns 'c3'
