[sqlite] R*Tree performance
Hello there, I would like to get an idea, what (insert) performance I could expect when building a 3-dimensional (float) R*Tree. I am using SQLite as a static lib, compiled with the following defines: "SQLITE_ENABLE_RTREE" "SQLITE_OMIT_DEPRECATED" "SQLITE_THREADSAFE=2" "SQLITE_MAX_EXPR_DEPTH=0" The test I performed was on Win7 64-bit. I am using an in-memory DB. The PC has lots of RAM. I have 3 tables: points consists of an implicit rowid and 3 real columns elemenst consists of 4 integer columns, referencing a points rowid each. Populating the above tables from an array of data goes with a speed of approx. 1.2 M elements per second. I am happy with this. I have a transaction wrapped around a for loop where I bind the parameters and execute the parametrized query, inserting one row at a time. Now, I created a table for spatial lookups: create virtual table boundingboxes using rtree (elemID, minX, maxX, minY, maxY, minZ, maxZ); I have a rather poor performance of 50 k inserts per second. The data to be inserted is precalculated and passed to the loop. I am using the same logic as above, so the loop is wrapped in a transaction and I use a parametrized statement. Is this the performance I can expect, compared to more than a million inserts into a "simple" table? Could I do something better? thanks in advance ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] segmentation fault in sqlite api call
Your query string is ~ 61 characters (did not count precisely), not including the key length not the value length. Are you sure the real tests you run do not overflow the fixed buffer char query[200] which can hold no more than 199 characters? You would have huge problems as soon as strlen(key) + strlen(query) > ~139. Besides, there is still no point passing a char** to mydef_set(). You might as well have: > int mydef_set(sqlite3 *db,char *key, char *value) and call it as: > mydef_set(db,"sssi",val); And the code for mod_init() copied from your initial email can't be the code you compile along the remaining bits of the sample you provided. That function expect db to be a global, yet it returns it after changing it, it frees a query which was nowhere declared/allocated,... I sincerely think what you are looking at are weird bugs in your code, and you might be loosing precious time wondering what might go wrong in SQLite code : anything and everything if the caller plays fool. If you want some more help from the community, I suggest you should write a short self-contained sample as you tried to do, but this time check that it compiles fine (what you showed until now can't possibly even compile), and run and produces the same problem as your real program then show it. Please also tell what your platform is and how is SQLite linked to your code (static lib, dynamic lib, or compiled in along with your project code). -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia > sqlite3 *mod_init() { > >/* Open database */ >//rc = sqlite3_open("test.db", &dbObj->db); >lastError = sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | > SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE|SQLITE_OPEN_NOMUTEX , NULL); >if( lastError ) { >fprintf(stderr, "Can't open database: %s\n", > sqlite3_errmsg(dbObj->db)); >free(query); >return(0); >} else { >fprintf(stdout, "Opened database successfully\n"); >} >memset(query,0,200); >strcpy(query,"CREATE TABLE IF NOT EXISTS cosmos_db(" \ >"key TEXT PRIMARY KEY NOT NULL," \ >"valueVARCHAR(100));"); > >/* Execute SQL statement */ >lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); > > >if( lastError != SQLITE_OK ){ >fprintf(stderr, "SQL error: %s\n", zErrMsg); >sqlite3_free(zErrMsg); >} else { >fprintf(stdout, "Table created successfully\n"); >} > return db; > } > > Le 22 oct. 2018 à 07:15, Ratheendran R a écrit : > > int mydef_set(sqlite3 *db,char *key, char **value) > { >char *zErrMsg = 0; >int rc; >char query[200] >sprintf(query,"INSERT OR REPLACE INTO cosmos_db (key,value) values > ('%s', '%s');",key,*value); >/* Execute SQL statement */ >lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); >if( lastError != SQLITE_OK ) { > fprintf(stderr, "SQL error: %s\n", zErrMsg); > sqlite3_free(zErrMsg); > } else { > fprintf(stdout, "Update done successfully\n"); > } >return lastError; > } > > > int main() > { > > >sqlite3 *db; >db=mod_init(); >char *val=malloc(1000); >//strcpy(val, >char dest[]="axzchsdjzcjsdjdcfsjhgfcshgsdfgsfg h > dbhjbbssdfsdsgffjhdsgfjg"; >strcpy(val,dest); > >mydef_set(db,"sssi",&val); > > } ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regarding CoC
On Fri, 19 Oct 2018 at 19:52, Mantas Gridinas wrote: > I found code of conduct in documentation and I was wondering if it were > true. Checking the version history it appears to have been added on > 2018-02-22. > > 23. Do not nurse a grudge. ::sigh:: DROP TABLE grudges; I was amassing such a good collection :( -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] segmentation fault in sqlite api call
Thanks Olivier Mascia for the tips and suggestion I will definitely try out. sorry for the code with the double pointer which has raised lot of confusion I ensured this fault raised is not beacuse of any of my pointer usage . I am having a workaround with key/value memory allocation in heap and double pointer seen was its residue, I am wondering why this fault occurs for stack allocation. int mydef_set(sqlite3 *db,char *key, char **value) { char *zErrMsg = 0; int rc; char query[200] sprintf(query,"INSERT OR REPLACE INTO cosmos_db (key,value) values ('%s', '%s');",key,*value); /* Execute SQL statement */ lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); if( lastError != SQLITE_OK ) { fprintf(stderr, "SQL error: %s\n", zErrMsg); sqlite3_free(zErrMsg); } else { fprintf(stdout, "Update done successfully\n"); } return lastError; } int main() { sqlite3 *db; db=mod_init(); char *val=malloc(1000); //strcpy(val, char dest[]="axzchsdjzcjsdjdcfsjhgfcshgsdfgsfg h dbhjbbssdfsdsgffjhdsgfjg"; strcpy(val,dest); mydef_set(db,"sssi",&val); } Ratheendran On Sun, Oct 21, 2018 at 10:46 PM Olivier Mascia wrote: > Hi, > > mydef_set probably overflows your 'query' variable of which you don't show > declaration but I guess it is 200 bytes seeing your memset(query,0,200); > strcpy(query, ... > > This above and why this char** buffer in mydef_set prototype? > Think about what your intent was. Compare to what you did (right) for key > parameter. > > Try to stop writing software that sprintf things to fixed sized buffers. > Especially when the purpose is to dynamically build SQL statements. Learn > about using parameters in your SQL statement, prepare once and execute > many, supplying values for the parameters at each run (step). > > Hope it will help. > -- > Best Regards, Meilleures salutations, Met vriendelijke groeten, > Olivier Mascia > > > Le 21 oct. 2018 à 18:54, Ratheendran R a > écrit : > > > > Hi, > > > > I am a embedded engineer and new to sqlite,we want to use sqlite for our > > local storage instead of file i/o. > > > > I have created a table with key and value records of char type,now if I > try > > store a value with string length more than 50 char I get segmentation > > fault,please see the code below and let me know if I can improve it. > > > > sqlite3 *mod_init() { > > > >/* Open database */ > >//rc = sqlite3_open("test.db", &dbObj->db); > >lastError = sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | > > SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE|SQLITE_OPEN_NOMUTEX , NULL); > >if( lastError ) { > >fprintf(stderr, "Can't open database: %s\n", > > sqlite3_errmsg(dbObj->db)); > >free(query); > >return(0); > >} else { > >fprintf(stdout, "Opened database successfully\n"); > >} > >memset(query,0,200); > >strcpy(query,"CREATE TABLE IF NOT EXISTS cosmos_db(" \ > >"key TEXT PRIMARY KEY NOT NULL," \ > >"valueVARCHAR(100));"); > > > >/* Execute SQL statement */ > >lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); > > > > > >if( lastError != SQLITE_OK ){ > >fprintf(stderr, "SQL error: %s\n", zErrMsg); > >sqlite3_free(zErrMsg); > >} else { > >fprintf(stdout, "Table created successfully\n"); > >} > > return db; > > } > > > > > > > > int mydef_set(cf_db_t *dbObj,char *key, char **value) > > { > >char *zErrMsg = 0; > >int rc; > >sprintf(query,"INSERT OR REPLACE INTO cosmos_db (key,value) values > > ('%s', '%s');",key,*value); > >/* Execute SQL statement */ > >lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); > >if( lastError != SQLITE_OK ) { > > fprintf(stderr, "SQL error: %s\n", zErrMsg); > > sqlite3_free(zErrMsg); > > } else { > > fprintf(stdout, "Update done successfully\n"); > > } > >return lastError; > > } > > > > > > int main() > > { > >sqlite3 *db; > >db=mod_init(); > >mydef_set(db,"sssi","Hitjkahzdsdhdjksdhjsdhsjfhjsdhfjhsjd bcn > > bsdbgfhjsdgcsdfcbscbshdfgchdsfbbsdfcsfg"); > > } > > > > Thanks, > > Ratheendran > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] segmentation fault in sqlite api call
You have a vast number of undeclared variables that are pointing into super-crash-land: sqlite3 *mod_init() db is undeclared dbObj is undeclared lastError is undeclared query is undeclared zErrMsg is undeclared int mydef_set(cf_db_t *dbObj,char *key, char **value) type cf_db_t is undeclared query is undeclared db is undeclared lastError is undeclared char **value does not match passed type int main() in call to mydef_set parameter 1 (sqlite3*) does not match expected (cf_db_t*) parameter 2 (char*) does not match expected (char**) --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Ratheendran R >Sent: Sunday, 21 October, 2018 10:55 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] segmentation fault in sqlite api call > >Hi, > >I am a embedded engineer and new to sqlite,we want to use sqlite for >our >local storage instead of file i/o. > >I have created a table with key and value records of char type,now if >I try >store a value with string length more than 50 char I get segmentation >fault,please see the code below and let me know if I can improve it. > >sqlite3 *mod_init() { > >/* Open database */ >//rc = sqlite3_open("test.db", &dbObj->db); >lastError = sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE >| >SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE|SQLITE_OPEN_NOMUTEX , >NULL); >if( lastError ) { >fprintf(stderr, "Can't open database: %s\n", >sqlite3_errmsg(dbObj->db)); >free(query); >return(0); >} else { >fprintf(stdout, "Opened database successfully\n"); >} >memset(query,0,200); >strcpy(query,"CREATE TABLE IF NOT EXISTS cosmos_db(" \ >"key TEXT PRIMARY KEY NOT NULL," \ >"valueVARCHAR(100));"); > >/* Execute SQL statement */ >lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); > > >if( lastError != SQLITE_OK ){ >fprintf(stderr, "SQL error: %s\n", zErrMsg); >sqlite3_free(zErrMsg); >} else { >fprintf(stdout, "Table created successfully\n"); >} >return db; >} > > > >int mydef_set(cf_db_t *dbObj,char *key, char **value) >{ >char *zErrMsg = 0; >int rc; >sprintf(query,"INSERT OR REPLACE INTO cosmos_db (key,value) >values >('%s', '%s');",key,*value); >/* Execute SQL statement */ >lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); >if( lastError != SQLITE_OK ) { > fprintf(stderr, "SQL error: %s\n", zErrMsg); > sqlite3_free(zErrMsg); > } else { > fprintf(stdout, "Update done successfully\n"); > } >return lastError; >} > > >int main() >{ >sqlite3 *db; >db=mod_init(); >mydef_set(db,"sssi","Hitjkahzdsdhdjksdhjsdhsjfhjsdhfjhsjd bcn >bsdbgfhjsdgcsdfcbscbshdfgchdsfbbsdfcsfg"); >} > >Thanks, >Ratheendran >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] segmentation fault in sqlite api call
You're passing a char * to a routine that expects a char **, and then immediately trying to indirect through it, which means it's taking the text, treating it as a pointer, and passing the random data it's pointing to as a string to sqlite. On Sun., 21 Oct. 2018, 11:55 Ratheendran R, wrote: > Hi, > > I am a embedded engineer and new to sqlite,we want to use sqlite for our > local storage instead of file i/o. > > I have created a table with key and value records of char type,now if I try > store a value with string length more than 50 char I get segmentation > fault,please see the code below and let me know if I can improve it. > > sqlite3 *mod_init() { > > /* Open database */ > //rc = sqlite3_open("test.db", &dbObj->db); > lastError = sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | > SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE|SQLITE_OPEN_NOMUTEX , NULL); > if( lastError ) { > fprintf(stderr, "Can't open database: %s\n", > sqlite3_errmsg(dbObj->db)); > free(query); > return(0); > } else { > fprintf(stdout, "Opened database successfully\n"); > } > memset(query,0,200); > strcpy(query,"CREATE TABLE IF NOT EXISTS cosmos_db(" \ > "key TEXT PRIMARY KEY NOT NULL," \ > "valueVARCHAR(100));"); > > /* Execute SQL statement */ > lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); > > > if( lastError != SQLITE_OK ){ > fprintf(stderr, "SQL error: %s\n", zErrMsg); > sqlite3_free(zErrMsg); > } else { > fprintf(stdout, "Table created successfully\n"); > } > return db; > } > > > > int mydef_set(cf_db_t *dbObj,char *key, char **value) > { > char *zErrMsg = 0; > int rc; > sprintf(query,"INSERT OR REPLACE INTO cosmos_db (key,value) values > ('%s', '%s');",key,*value); > /* Execute SQL statement */ > lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); > if( lastError != SQLITE_OK ) { > fprintf(stderr, "SQL error: %s\n", zErrMsg); > sqlite3_free(zErrMsg); >} else { > fprintf(stdout, "Update done successfully\n"); >} > return lastError; > } > > > int main() > { > sqlite3 *db; > db=mod_init(); > mydef_set(db,"sssi","Hitjkahzdsdhdjksdhjsdhsjfhjsdhfjhsjd bcn > bsdbgfhjsdgcsdfcbscbshdfgchdsfbbsdfcsfg"); > } > > Thanks, > Ratheendran > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] segmentation fault in sqlite api call
The code you provided declares and defines a pointer, named ‘db’ in main(), which is used with the SQLite API but never made to point to a valid object in memory or even to allocated memory. Hence your segmentation fault. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] segmentation fault in sqlite api call
Hi, mydef_set probably overflows your 'query' variable of which you don't show declaration but I guess it is 200 bytes seeing your memset(query,0,200); strcpy(query, ... This above and why this char** buffer in mydef_set prototype? Think about what your intent was. Compare to what you did (right) for key parameter. Try to stop writing software that sprintf things to fixed sized buffers. Especially when the purpose is to dynamically build SQL statements. Learn about using parameters in your SQL statement, prepare once and execute many, supplying values for the parameters at each run (step). Hope it will help. -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia > Le 21 oct. 2018 à 18:54, Ratheendran R a écrit : > > Hi, > > I am a embedded engineer and new to sqlite,we want to use sqlite for our > local storage instead of file i/o. > > I have created a table with key and value records of char type,now if I try > store a value with string length more than 50 char I get segmentation > fault,please see the code below and let me know if I can improve it. > > sqlite3 *mod_init() { > >/* Open database */ >//rc = sqlite3_open("test.db", &dbObj->db); >lastError = sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | > SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE|SQLITE_OPEN_NOMUTEX , NULL); >if( lastError ) { >fprintf(stderr, "Can't open database: %s\n", > sqlite3_errmsg(dbObj->db)); >free(query); >return(0); >} else { >fprintf(stdout, "Opened database successfully\n"); >} >memset(query,0,200); >strcpy(query,"CREATE TABLE IF NOT EXISTS cosmos_db(" \ >"key TEXT PRIMARY KEY NOT NULL," \ >"valueVARCHAR(100));"); > >/* Execute SQL statement */ >lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); > > >if( lastError != SQLITE_OK ){ >fprintf(stderr, "SQL error: %s\n", zErrMsg); >sqlite3_free(zErrMsg); >} else { >fprintf(stdout, "Table created successfully\n"); >} > return db; > } > > > > int mydef_set(cf_db_t *dbObj,char *key, char **value) > { >char *zErrMsg = 0; >int rc; >sprintf(query,"INSERT OR REPLACE INTO cosmos_db (key,value) values > ('%s', '%s');",key,*value); >/* Execute SQL statement */ >lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); >if( lastError != SQLITE_OK ) { > fprintf(stderr, "SQL error: %s\n", zErrMsg); > sqlite3_free(zErrMsg); > } else { > fprintf(stdout, "Update done successfully\n"); > } >return lastError; > } > > > int main() > { >sqlite3 *db; >db=mod_init(); >mydef_set(db,"sssi","Hitjkahzdsdhdjksdhjsdhsjfhjsdhfjhsjd bcn > bsdbgfhjsdgcsdfcbscbshdfgchdsfbbsdfcsfg"); > } > > Thanks, > Ratheendran > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] segmentation fault in sqlite api call
Hi, I am a embedded engineer and new to sqlite,we want to use sqlite for our local storage instead of file i/o. I have created a table with key and value records of char type,now if I try store a value with string length more than 50 char I get segmentation fault,please see the code below and let me know if I can improve it. sqlite3 *mod_init() { /* Open database */ //rc = sqlite3_open("test.db", &dbObj->db); lastError = sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE|SQLITE_OPEN_NOMUTEX , NULL); if( lastError ) { fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(dbObj->db)); free(query); return(0); } else { fprintf(stdout, "Opened database successfully\n"); } memset(query,0,200); strcpy(query,"CREATE TABLE IF NOT EXISTS cosmos_db(" \ "key TEXT PRIMARY KEY NOT NULL," \ "valueVARCHAR(100));"); /* Execute SQL statement */ lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); if( lastError != SQLITE_OK ){ fprintf(stderr, "SQL error: %s\n", zErrMsg); sqlite3_free(zErrMsg); } else { fprintf(stdout, "Table created successfully\n"); } return db; } int mydef_set(cf_db_t *dbObj,char *key, char **value) { char *zErrMsg = 0; int rc; sprintf(query,"INSERT OR REPLACE INTO cosmos_db (key,value) values ('%s', '%s');",key,*value); /* Execute SQL statement */ lastError = sqlite3_exec(db, query, 0, 0, &zErrMsg); if( lastError != SQLITE_OK ) { fprintf(stderr, "SQL error: %s\n", zErrMsg); sqlite3_free(zErrMsg); } else { fprintf(stdout, "Update done successfully\n"); } return lastError; } int main() { sqlite3 *db; db=mod_init(); mydef_set(db,"sssi","Hitjkahzdsdhdjksdhjsdhsjfhjsdhfjhsjd bcn bsdbgfhjsdgcsdfcbscbshdfgchdsfbbsdfcsfg"); } Thanks, Ratheendran ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OPEN_READONLY in PHP
Thanks to Simon Slavin and Tim Streater All replies works fine. Thank you very much Ismael ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] geopoly data input options
Spatialite does to Sqlite what Postgis does to Postgres. Same concepts. Noël On Sat, 20 Oct 2018 at 22:49, Jonathan Moules wrote: > More specifically, in the "Simple Features for SQL" specification: > > http://www.opengeospatial.org/standards/sfs > > and if you have access (or gobs of money), there's the ISO spec (I'm > guessing it's the same) - > https://webstore.ansi.org/RecordDetail.aspx?sku=ISO+19125-1%3A2004 > > I'd also suggest PostGIS (a PostGreSQL extension for spatial) given > they're open-source you can take a gander at their code to get a feel > for it. > > PostGIS also has a superset called "EWKB", though the docs are fairly > poor on it (I'm not sure what the "E" is for - Enhanced"?) - "PostGIS > EWKB/EWKT add 3dm,3dz,4d coordinates support and embedded SRID > information" - probably beyond the scope of what you want in geopoly at > this point. > > Cheers, > > Jonathan > > > On 2018-10-19 21:56, Noel Frankinet wrote: > > There a WKB and WKT (text) representation). > > You can probably find everything : http://www.opengeospatial.org > > Spatialite is also a good source > > . > > > > > > On Fri, 19 Oct 2018 at 22:47, Richard Hipp wrote: > > > >> On 10/19/18, Thomas Kurz wrote: > Beginning with the next release, polygons will always be stored in the > binary format. > >>> Is the SQLite binary encoding identical to the Well-Known-Binary > geometry > >>> format? > >> That might have happened, except the WKB format was not known to me... > >> Where can I find information about WKB? > >> > >> -- > >> D. Richard Hipp > >> d...@sqlite.org > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Noël Frankinet Strategis sprl 0478/90.92.54 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64-column covering index limit clarification
On 10/19/2018 02:30 AM, Deon Brewis wrote: Hi, I seem to have run into a limit where SQLITE doesn't use an index correctly if an indexed column is over the 64th column in the table. It's a partial index like: CREATE INDEX idx ON table(A, B DESC, C, D) WHERE A > 0 Where A and B are columns 70 and 72 on 'table'. I know about the 64-column limitation for covering indexes: http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html However, this isn't a covering index, it's a partial index. But it seems to run into the same limit. Even if I forced in the index into a query it still does a "USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY A, B DESC" query. After I re-ordered the table, it magically started working. Can you post an SQL script that demonstrates the problem? Running the script below here, the partial index is used to optimize the ORDER BY in the query. Thanks, Dan. CREATE TABLE t1( c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50, c51, c52, c53, c54, c55, c56, c57, c58, c59, c60, c61, c62, c63, c64, c65, c66, c67, c68, c69, c70, c71, c72, c73, c74, c75, c76, c77, c78, c79, c80, c81, c82, c83, c84, c85, c86, c87, c88, c89 ); CREATE INDEX i1 ON t1(c80, c81 DESC, c82, c83) WHERE c80>0; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c80>0 ORDER BY c80, c81 DESC; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users