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/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 rowid + 1. -- 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] .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 solution. Going back to the latest set of results why is the assignments in the second pass taking so much longer when there's still 5+ GB of memory free? -- 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] 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=="" || !Len) return "";
String S;
int StrLen = Str.Length();
if (Start < 0) Start = StrLen + Start + 1;
if (Len < 0) {Start += Len; Len = -Len;}
for (int i = std::max(1, Start); i <= StrLen && i < Start+Len; i++) S +=
Str[i];
return S;
}
// String is a windows wide string type
// I wrote it a while ago so it could probably be done gooder :-)
--
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] 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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. Richard suggested this earlier. Can this be done in C rather than the shell? Is that a minus sign before the DSQLITE_ENABLE_MEMSYSS? If I try compiling with a minus sign before that directive I get a compile error "macro names must be identifiers ". Compiles OK if I leave out the minus sign. -- 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] 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 don't know if it would use the temp page cache size or the database page cache size. I think it has something to do with persistent versus temp tables. You can't qualify a temp table e.g. "create temp table mem.Tbl" isn't allowed so it's either "create temp table Tbl" (standard temp table) or "create table mem.Tbl" (persistent table within a temp db). -- 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] 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 flushed to disc if sqlite came under memory pressure (https://sqlite.org/inmemorydb.html). I tried the large insert in such a db but the performance was only on a par with a temp table with temp_store set as FILE. -- 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] 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 results where the query consists of a BaseTbl linked to lookup tables. As I said on that thread I consider the time taken to run a query to be equal to the time taken to compile that list of RowIDs. Thereafter a page of results can be obtained pretty much instantaneously by linking the appropriate RowIDs with a subsection of the original query. In that thread I was storing the RowIDs in a vector but this thread is about testing storing them in a temp table. It isn't as fast as the vector but there isn't a great deal of difference until you get into the millions of records and (I'm hoping) to remove any dependency on RAM. I have a well known sqlite browser on my laptop. If I view my largest table (2.4 million recs) in a grid in this browser the top page appears with the counter showing 'record 1 of at least x' alongside a vertical scrollbar that doesn't work properly (it can't because it doesn't have the record count). If I click 'Last record' in the navigation bar it takes almost 3 secs for the last page to appear. Even then the vertical scrollbar doesn't work properly. It's all pretty ugly. In contrast I can get a list of all the RowIDs for that table in approx. 0.6 secs, show an accurate record counter / vertical scrollbar and navigate to any point in the table in microseconds. That's in win64 with an SSD based DB but it's still well worth it in win32 with a hard drive. -- 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] 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 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" Put it this way, if I create n temp tables will there be n_+ 1 page caches or just the main cache containing a further n temporary caches? >Be aware that you cannot magically create more RAM in your computer by defining enough temporary tables. I don't know how I managed to give you the impression I ever thought that Simon. All I was trying to determine was how I could best manage available memory while hoping sqlite would do it for me. From the tests I've done increasing the cache_size or SQLITE_DEFAULT_TEMP_CACHE_SIZE has made no difference yet the default sizes are hopelessly inadequate to hold a temp table of that size. I would've liked to have known if sqlite took care of it all for me or if it was down to the OS. All I was able to determine was that setting temp_store = 2 (MEMORY) did speed up the queries but I've no idea if using that setting is risky on a lower spec pc. -- 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] 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 parameter and the temp_store pragma are set to store temporary tables and index on disk, the information is still initially stored in memory in the page cache. The temporary file is not opened and the information is not truly written to disk until the page cache is full. This means that for many common cases where the temporary tables and indices are small (small enough to fit into the page cache) no temporary files are created and no disk I/O occurs. Only when the temporary data becomes too large to fit in RAM does the information spill to disk. Each temporary table and index is given its own page cache which can store a maximum number of database pages determined by the SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time parameter. (The default value is 500 pages.) The maximum number of database pages in the page cache is the same for every temporary table and index. The value cannot be changed at run-time or on a per-table or per-index basis. Each temporary file gets its own private page cache with its own SQLITE_DEFAULT_TEMP_CACHE_SIZE page limit." * Can someone tell me, if I create a temporary table does is its 'separate cache' created within the cache_size cache or is it completely separate from that? -- 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] 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. Thanks Keith. So sqlite does look to increase the temp table's cache size if it's not big enough? Looking at the results I posted earlier, why did that not happen until cache_size was set to -500? Why didn't sqlite just increased the cache_size from -400 to the required memory? Mind you, I'm assuming the test results for cache_size <= -400 were slower because sqlite resorted to HD but maybe that's not the case. -- 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] 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 which tries to use a lot of memory: the virtual >memory mechanism kicks in and memory is swapped to and from disk. Thanks Simon but is that to say sqlite attempts to increase the temp table's page cache size beyond the size it was originally allotted? If the answer to that is yes then that also answers question 2). -- 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] 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 cross joined to itself to create a (pointless and not distinct) large insert of 111,724,900 recs] create temp table Key (ID int); INSERT = secs to run insert into Key select ID from Tbl cross join Tbl using (ID); DELETE = secs to run delete from Key; The results for different values of temp_store and cache_size were as follows STORE, CACHE, INSERT, DELETE 0, 0, 23.00, 1.87 2, 0, 21.10, 1.27 2, -8, 20.36, 1.30 2, -80, 20.61, 1.30 2, -200, 20.70, 1.30 2, -300, 20.72, 1.30 2, -400, 20.70, 1.30 2, -500, 13.58, 1.29 2, -800, 13.48, 1.29 2, -8000, 13.52,1.28 2, -800, 13.64, 1.29 At cache_size = -500 the timings come more into line with the memory results from my old tests. Some things I need cleared up 1) I read in those links that each temp table is given (by default) its own page cache of 500 pages. Is this a separately created page cache or is it 500 pages from THEE page chache? If it's the latter that will explain the slowdown for cache_size < -500. Or does the cache_size pragma dictate the size of the separately created page cache? 2) My DB page size is 1024. If the temp table is allocated a separate page cache of 500 * 1024 bytes this means that sqlite managed to store 111,724,900 records in 500 KB? That's an average of 0.0046 bytes per record? 3) Similar to the original question, if you set temp_store = 2 (memory) and there isn't enough memory for the table what happens? -- 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] 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 TABLE" statement is originally evaluated. These TEMP tables, together with any associated indices, triggers, and views, are collectively stored in a separate temporary database file that is created as soon as the first "CREATE TEMP TABLE" statement is seen.* Running the same tests again using an actual table (TEMP keyword omitted) shows the in memory INSERT (and DELETE) to be twice as fast on the database loaded into memory compared to when it's accessed from the SSD.The timings in the original tests were similar to the in memory database suggesting the TEMP table for those tests were created in memory regardless of whether the actual database was loaded in memory or not. I can't see any documentation suggesting that though. Could someone clarify? -- 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] 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 TABLE" statement is originally evaluated. These TEMP tables, together with any associated indices, triggers, and views, are collectively stored in a separate temporary database file that is created as soon as the first "CREATE TEMP TABLE" statement is seen. *Running the same tests again using an actual table (TEMP keyword omitted) shows the in memory INSERT (and DELETE) to be twice as fast on the database loaded into memory compared to when it's accessed from the SSD.The timings in the original tests were similar to the in memory database suggesting the TEMP table for those tests were created in memory regardless of whether the actual database was loaded in memory or not. I can't see any documentation suggesting that though. Could someone clarify? -- 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] 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 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 there was no real difference. For lesser inserts (2 million) the speedup was around 33% but would hardly be noticeable to the end user. I daresay it would've been more noticeable if my laptop had a hard drive but the moral of the story is get yourself an SSD and leave sqlite to take care of the hard stuff. -- 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] 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 1 to many relationship in the query and we can’t proceed. (At this stage I’m by no means sure of this). * The above isn't guaranteed to work. Consider the following CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER); CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER); CREATE INDEX ib ON t1(b); EXPLAIN QUERY PLAN SELECT t1.RowID FROM t1 LEFT JOIN t2 ON x = b; 0|0|0|SCAN TABLE t1 Everything above is as expected, t2.x is primary key lookup for t1.b so trailing left join is dropped. Suppose though we made t2.y the lookup for t1.b CREATE UNIQUE INDEX iy ON t2(y); EXPLAIN QUERY PLAN SELECT t1.RowID FROM t1 LEFT JOIN t2 ON y = b; 0|0|0|SCAN TABLE t1 0|1|1|SEARCH TABLE t2 USING COVERING INDEX iy (y=?) As you can see what at first sight looks like a redundant trailing left join ISN’T dropped. If however we had defined t2 with CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER NOT NULL); Then we would get EXPLAIN QUERY PLAN SELECT t1.RowID FROM t1 LEFT JOIN t2 ON y = b; 0|0|0|SCAN TABLE t1 Therefore, the suggested formula would fail on any non premium key lookup index where the index field(s) were not declared as NOT NULL. Strangely (or maybe not) EXPLAIN QUERY PLAN SELECT t1.RowID FROM t1 LEFT JOIN t2 ON y = b *WHERE y IS NOT NULL*; doesn't resolve the problem. -- 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] 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 not a 1-to-n join. I replied > If Tbl_2 isn’t involved in the columns, where or order by then > changing it to left join will mean it will definitely be left out so > I don’t get what you mean E.Pasma. I’m talking bollocks again. The left join will only be omitted if it has a 1 to 1 relationship with BaseTbl so E.Pasma is correct. 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 1 to many relationship in the query and we can’t proceed. (At this stage I’m by no means sure of this). Otherwise the RowSQL becomes SELECT BaseTbl.RowID FROM BaseTbl jointype_1 Tbl1 on comparison_1 jointype_2 Tbl2 on comparison_2 . . jointype_n Tbln on comparison_n WHERE ... ORDER BY ... And we leave the SQLite optimiser to filter out any left joins that aren’t required. -- 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] 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; by setting ?1 = *ID + TopRecNo* and ?2 = n. -- 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] 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 the query select * from (select Value from carray(ID+?1, ?2, 'int64')) inner join AwfyBigTbl on AwfyBigTbl.RowID = _Value; by setting ?1 = TopRecNo and ?2 = n. I realise I could get the same result with select * from AwfyBigTbl where ACol=? limit n offset TopRecNo; but the first way will be faster and, as the record count - ID.size() - is known , it would allow a record counter to be shown, vertical scrollbar to be sized, last known record to be relocated etc. Anyway, what I'm trying to do is find a way of doing the same thing for more complicated queries. Given a select where a 'base table' is attached to lookup tables how can I determine which of the lookup tables can be removed from the table such that select BaseTbl.RowID from ... where ... order by ... will find the set of records that represents the original query. Hope that's clearer. -- 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] 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. ___ 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 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: http://sqlite.1065341.n5.nabble.com/Compiling-spellfix-for-sqlite3-tp70656p97105.html Sent from the SQLite mailing list archive at 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
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);
nErr += sqlite3_auto_extension((void(*)())sqlite3_eval_init);
#ifndef SQLITE_OMIT_VIRTUALTABLE
nErr += sqlite3_auto_extension((void(*)())sqlite3_csv_init);
nErr += sqlite3_auto_extension((void(*)())sqlite3_stmt_init);
nErr += sqlite3_auto_extension((void(*)())sqlite3_carray_init);
#endif
return nErr ? SQLITE_ERROR : SQLITE_OK;
}
My compiler gave the following errors/warnings.
[bcc32 Error] csv.c(42): E2209 Unable to open include file 'sqlite3ext.h'
I fixed this error by changing to "sqlite3ext.h"
[bcc32 Error] sqlite3.c(204450): E2451 Undefined symbol 'sqlite3_stmt_init'
Not sure if this is to do with the fact that stmt.c is already present in
sqlite3.c
[bcc32 Error] compress.c(18): E2209 Unable to open include file 'zlib.h'
Above error appears even if I change to "zlib.h"
[bcc32 Warning] compress.c(54): W8065 Call to function 'compress' with no
prototype
[bcc32 Error] compress.c(55): E2451 Undefined symbol 'Z_OK'
[bcc32 Warning] compress.c(87): W8065 Call to function 'uncompress' with no
prototype
[bcc32 Error] compress.c(88): E2451 Undefined symbol 'Z_OK'
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/Compiling-spellfix-for-sqlite3-tp70656p97100.html
Sent from the SQLite mailing list archive at 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 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. ___ 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
Keith, I finally managed to use carray this morning. The C++ builder IDE had a facility for entering conditional defines in Project|Options. I had noticed this before asking the question on the c++ builder forum but hadn't realised the -D part of -DSQLITE_EXTRA_INIT=cor_init wasn't required. Thanks for all your help but would you mind if I haunted you just a little bit longer to clear something up? You sent me an email with SampleExts.zip and wrote When I build my sqlite3.dll (on windows with gcc) I get the following functions for every connection: collation_list row = (0, u'ROT13') collation_list row = (1, u'NUMERICS') ... pragma_function_list() row= (u'aavg', 0) pragma_function_list() row= (u'abs', 1) pragma_function_list() row= (u'acos', 0) I don't recognise the above 'list' notation and could find nothing on it when googled. Could you point me to any documentation as I 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 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. > > Yeah, it must be in there somewhere. Can't help though as I have a deadly > allergy to IDE's. > > In a non-GUI you would specify it as a command line option to the compiler > (actually, to the pre-processor), as in: > > gcc -DSQLITE_EXTRA_INIT=core_init sqlite3.c > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > > ___ > sqlite-users mailing list > sqlite-users@.sqlite > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://sqlite.1065341.n5.nabble.com/Compiling-spellfix-for-sqlite3-tp70656p97036.html Sent from the SQLite mailing list archive at 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
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: http://sqlite.1065341.n5.nabble.com/Compiling-spellfix-for-sqlite3-tp70656p97028.html Sent from the SQLite mailing list archive at 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 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] carray.c(430): E2040 Declaration terminated incorrectly
// on the '-DSQLITE_EXTRA_INIT=core_init' line
I assume carray.c wasn't being compiled at all the other way and that's why
there were no errors reported. I tried wrapping the carray.c code in extern
"C" {...carray.c code...} in case it was something to do with that but then
I got a 'Declaration terminated incorrectly' error on the first line which
contains extern "C" {
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/Compiling-spellfix-for-sqlite3-tp70656p97006.html
Sent from the SQLite mailing list archive at 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
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) || defined(SQLITE_ENABLE_STMTVTAB) */
/** End of stmt.c
/
I then added the following code to the end of carray.c
int core_init(const char* dummy)
{
int nErr = 0;
nErr += sqlite3_auto_extension((void*)sqlite3_carray_init);
return nErr ? SQLITE_ERROR : SQLITE_OK;
}
-DSQLITE_CORE
-DSQLITE_EXTRA_INIT=core_init
Everything compiled OK but when I tried to access carray in a query I got a
"no such table" error. To be honest I had no idea where to put what and,
through debugging, I know the core_init function was never entered. Can you
tell me where I'm going wrong?
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/Compiling-spellfix-for-sqlite3-tp70656p97003.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
