Re: [sqlite] WITHOUT ROWID tables

Does sqlite3_table_column_metadata(db,dbName,tblName,"rowid",0,0,0,0,0)==SQLITE_OK return false if table tblName is a without rowid table? https://sqlite.org/c3ref/table_column_metadata.html -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___

Re: [sqlite] Table has different rootpage in EXPLAIN and sqlite_master

You were correct David. The explain rootpage referred to an index and I was checking against the table rootpage. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] .timer

Keith, did you ever get any feedback from ms about this bug? There's certainly been no windows update that solved the problem. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Shouldn't have to specify primary key explicitly

>INTEGER PRIMARY KEY doesn’t default to autoincrement. It’s used in place of the automatically created >autoincrement rowid but you have to supply the values (I.e. they’re not created automatically). I stand corrected. If you supply null for the integer primary key it will assign the highest

Re: [sqlite] .timer

PS I can't find 'cache' in task manager. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

It seems the array was being optimised away. I had to initialise every value to get the OS to claim the RAM. Once I did that the timings for the array were on a par with the vector with the second pass being slower than the first. While that clears up that part of the mystery I'm no closer to a

Re: [sqlite] Strange concatenation result

There's nothing special about Y=0. The Y can be anywhere outwith the string. e.g. substr('abc', 6, -4) = 'bc' substr('abc', -5, 3) = 'a' All substr functions should work this way. I wrote a c++ function to emulate it. String substr(const String , int Start, int Len) { if (Str=="" ||

Re: [sqlite] sqlite3_btreeinfo causes sql logic error

Seems to be if you include any non-text column (apart from rootpage) from sqlite_btreeinfo in the select it causes an sql logic error. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list

Re: [sqlite] unexpected row value error

Probably won't help but the final one works with SELECT in double brackets SELECT * FROM x JOIN y ON y.a = x.a WHERE (x.a, x.b) IN ( ( SELECT a, b FROM z ) ); . -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list

Re: [sqlite] SQLite 3.22.0 coming soon

8 d. Omit unused LEFT JOINs even if they are not the right-most joins of a query. Thanks for fixing this. Working fine for me so far. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list

Re: [sqlite] What happens if an in memory database runs out of memory

>You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5 and then starting the "sqlite3.exe" command-line shell with the "--heap" argument to tell it how much memory to use. Give it a few megabytes. Then start up your in-memory database and fill it up to see what happens.

Re: [sqlite] What happens if an in memory database runs out of memory

>I would think that a temp file database (created with an empty string) is no different from a regular disk file resident database EXCEPT that the file is generated with an random tmpfile name and automatically unlinked when closed, and that "memory pressure" equates to "page cache is full". I

Re: [sqlite] What happens if an in memory database runs out of memory

I thought I had posted this earlier but I don't see it. Earlier I said the ideal solution would be something that uses memory and defaults to disc if it runs out of memory. In response Richard's suggested using a temp database with a blank name as that would use memory but parts of it would be

Re: [sqlite] What happens if an in memory database runs out of memory

Keith / Simon, thanks to both of you for those detailed replies but I'll need a bit of time to digest them. It may seem I'm a bit OCD trying to save a few microseconds here and there but this is to do with a thread I started a while back regarding getting a list of RowIDs pointing to the query

Re: [sqlite] What happens if an in memory database runs out of memory

>According to the documentation you quoted, >"Each temporary table and index is given its own page cache" >every temporary table gets its own cache. Each cache can grow to a maximum size of SQLITE_DEFAULT_TEMP_CACHE_SIZE . Yeah, but it also says "SQLite uses a page cache of recently read

Re: [sqlite] What happens if an in memory database runs out of memory

*"SQLite uses a page cache of recently read and written database pages. This page cache is used not just for the main database file but also for transient indices and tables stored in temporary files. If SQLite needs to use a temporary index or table and the SQLITE_TEMP_STORE compile-time

Re: [sqlite] What happens if an in memory database runs out of memory

>Until the underlying system memory allocator fails and then it go boom. How much goes boom is OS dependent. Some OSes will only allow the errant process go boom. Others (such as those from Microsoft) the entire OS go boom if the out of memory condition encompases the entire V=V address space.

Re: [sqlite] What happens if an in memory database runs out of memory

>> 3) Similar to the original question, if you set temp_store = 2 (memory) and >> there isn't enough memory for the table what happens? >By 'memory' that web page is referring to whatever your operating system thinks is memory. So the >same thing happens as would happen to any application

Re: [sqlite] What happens if an in memory database runs out of memory

Thanks Simon/Gunter. I thought those sections cleared things up until I tried a few tests. I opened the DB, set temp_store to STORE, cache_size to CACHE and then calculated the average secs taken (over 2 runs) to run the following [Tbl has integer primary key ID, contains 10,570 records & is

Re: [sqlite] What happens if an in memory database runs out of memory

My apologies. The inserts in the above tests were made on a TEMP table which I assumed was being created in the existing database. I've since discovered that isn't the case. *Tables created using the "CREATE TEMP TABLE" syntax are only visible to the database connection in which the "CREATE TEMP

Re: [sqlite] What happens if an in memory database runs out of memory

My apologies. The inserts in the above tests were made on a TEMP table which I assumed was being created in the existing database. I've since discovered that isn't the case.*Tables created using the "CREATE TEMP TABLE" syntax are only visible to the database connection in which the "CREATE TEMP

Re: [sqlite] What happens if an in memory database runs out of memory

Sorry cache_size should be -8,000,000. It didn't make any difference to the results or conclusion though. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] What happens if an in memory database runs out of memory

You're definitely right about me wasting my time Simon. I loaded my entire database (1 GB) into memory and set cache_size = -8192 for an 8 GB cache size (using win64). I then ran my test (inserting the results of a query, returning 111 million bigints, into a non-indexed single column table) and

Re: [sqlite] Getting an advance list of RowIDs for a query result set

*I’m now wondering if you omit the WHERE & ORDER BY and run the following EXPLAIN QUERY PLAN SELECT BaseTbl.RowID FROM BaseTbl left join Tbl1 on comparison_1 left join Tbl2 on comparison_2 . . left join Tbln on comparison_n then if it returns more than 1 row then this implies there’s a

Re: [sqlite] Getting an advance list of RowIDs for a query result set

E.Pasma wrote >> What about changing the remaining inner join to left join > >> Select BaseTbl.RowID >> from BaseTbl >> left join Tbl_2 on Tbl2.Y = BaseTbl.Y >> where BaseTbl.Col=? > >> and see if the SQLiter optimizer now leaves Tbl_2 out from the query >> plan. It will only do that if it is

Re: [sqlite] Getting an advance list of RowIDs for a query result set

Sorry, in last post select * from (select Value from carray(ID+?1, ?2, 'int64')) inner join AwfyBigTbl on AwfyBigTbl.RowID = _Value; by setting ?1 = TopRecNo and ?2 = n. should read select * from (select Value from carray(*?1*, ?2, 'int64')) inner join AwfyBigTbl on AwfyBigTbl.RowID = _Value;

Re: [sqlite] Getting an advance list of RowIDs for a query result set

A trivial example of what I'm trying to do. Given select * from AwfyBigTbl where ACol=?; I'd run the query select RowID from AwfyBigTbl where ACol=?; step through the records and store the values in a std::vector called ID. I could then retrieve n records starting at TopRecNo (0 based) with

Re: [sqlite] Get result of 'pragma foreign_keys' in c programme

Thanks Jens, working now. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling spellfix for sqlite3

Thanks for those explanations Keith and Richard. I always thought the <> were the ones supplied by the language. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Compiling-spellfix-for-sqlite3-tp70656p97122.html Sent from the SQLite mailing list archive at Nabble.com.

Re: [sqlite] Compiling spellfix for sqlite3

Thanks Keith, the config info is most welcome. I wasn't sure if I was supposed to download zlib.h the fact it was inside <> instead of "" but understand now. Is the include in csv.c not a bug though, or is it again down to my compiler? -- View this message in context:

Re: [sqlite] Compiling spellfix for sqlite3

While testing I appended the following code to the end of sqlite3.c. #include "csv.c" #include "stmt.c" #include "compress.c" #include "eval.c" #include "carray.c" int core_init(const char* dummy) { int nErr = 0; nErr += sqlite3_auto_extension((void(*)())sqlite3_compress_init);

Re: [sqlite] Compiling spellfix for sqlite3

Thanks for the explanation Keith and the help. I've learned a lot the last few days. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Compiling-spellfix-for-sqlite3-tp70656p97040.html Sent from the SQLite mailing list archive at Nabble.com.

Re: [sqlite] Compiling spellfix for sqlite3

would like to add some functions/collations that would be automatically attached to every database connection. Keith Medcalf wrote > On Tuesday, 22 August, 2017 09:30, curmudgeon > tam118118@ > wrote: > >>Your cast did the trick Keith and it compiled f

Re: [sqlite] Compiling spellfix for sqlite3

Your cast did the trick Keith and it compiled fine once I removed the '-DSQLITE_EXTRA_INIT=core_init' line but I have no idea how to get that directive into the c++ builder application. I've put up a question on the c++ builder forum but unanswered as yet. -- View this message in context:

Re: [sqlite] Compiling spellfix for sqlite3

Thanks Keith. I followed your instructions but I'm now getting the following compiler errors [bcc32 Error] carray.c(412): E2342 Type mismatch in parameter 'xInit' (wanted 'void (*)()', got 'void *') // on the 'nErr += sqlite3_auto_extension((void*)sqlite3_carray_init); line' [bcc32 Error]

Re: [sqlite] Compiling spellfix for sqlite3

Keith, I know this is an old post but it refers to something we discussed recently. I tried the following I added the #include carray.c line to just above the bottom of the amalgamation such that the last few lines are #include carray.c #endif /* SQLITE_CORE */ #endif /* !defined(SQLITE_CORE)