Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)
Ah, this tool seems very handy. For those curious, I'll paste the results below. The index approximately doubles the storage size, but I am intentionally making that tradeoff to avoid the slow down when enforcing a unique/primary key on the Reference table while inserting. -AJ /** Disk-Space Utilization Report For F:/mytestdb2.db Page size in bytes 4096 Pages in the whole file (measured) 104071490 Pages in the whole file (calculated).. 104071489 Pages that store data. 104071489 100.000% Pages on the freelist (per header) 00.0% Pages on the freelist (calculated) 10.0% Pages of auto-vacuum overhead. 00.0% Number of tables in the database.. 3 Number of indices. 1 Number of defined indices. 1 Number of implied indices. 0 Size of the file in bytes. 426276823040 Bytes of user payload stored.. 174138410641 40.9% *** Page counts for all tables with their indices * REFERENCE. 9300818889.4% MAINDATA.. 1106330010.6% SQLITE_MASTER. 10.0% *** Page counts for all tables and indices separately * REFERENCE. 5030453448.3% HASHINDEX. 4270365441.0% MAINDATA.. 1106330010.6% SQLITE_MASTER. 10.0% *** All tables and indices Percentage of total database.. 100.000% Number of entries. 17948049998 Bytes of storage consumed. 426276818944 Bytes of payload.. 321412979244 75.4% Bytes of metadata. 100378462716 23.5% Average payload per entry. 17.91 Average unused bytes per entry 0.25 Average metadata per entry 5.59 Average fanout 241.00 Maximum payload per entry. 37003 Entries that use overflow. 535470.0% Index pages used.. 430147 Primary pages used 103581347 Overflow pages used... 59995 Total pages used.. 104071489 Unused bytes on index pages... 96815925 5.5% Unused bytes on primary pages. 4383344069 1.0% Unused bytes on overflow pages 5242782 2.1% Unused bytes on all pages. 4485402776 1.1% *** All tables Percentage of total database.. 59.0% Number of entries. 9031683455 Bytes of storage consumed. 251362652160 Bytes of payload.. 174138410911 69.3% Bytes of metadata. 73116919243 29.1% Average payload per entry. 19.28 Average unused bytes per entry 0.45 Average metadata per entry 8.10 Average fanout 332.00 Maximum payload per entry. 37003 Entries that use overflow. 535470.0% Index pages used.. 184608 Primary pages used 61123232 Overflow pages used... 59995 Total pages used.. 61367835 Unused bytes on index pages... 9414297112.5% Unused bytes on primary pages. 4007962045 1.6% Unused bytes on overflow pages 5242782 2.1% Unused bytes on all pages. 4107347798 1.6% *** All indices *** Percentage of total database.. 41.0% Number of entries. 8916366543 Bytes of storage consumed. 174914166784 Bytes of payload.. 147274568333 84.2% Bytes of metadata. 27261543473 15.6% B-tree depth.. 5 Average payload per
Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)
Simon, Dominique, and Arun - Thank you for the feedback. I'll leave the threading off for inserts since I've seen DB contention issues with other multithreaded/multiprocessed attempts. The indexing improvement is nice though. I misspoke when I said it was 200-300gb for just the integers -- my brain was a little fuzzy. Right now, the integer table has 3 columns: rowid, integer, and foreign row id to a second table (so 8byte int, 8 byte int, variable byte int I believe, unless the rowid is also a variably-sized int). The rowid is left in to prevent using it as a primary key, or enforcing a unique constraint upfront which would result in an extremely slow insertion if I understand the basics correctly (and based on my own tests). This works out to about 180 gb of the table. There is a second table which stores some text information at 150 million rows, and then references my integer table. This makes up some of the difference. These rows are probably on the order of 200-300 bytes each so if I do the math, something in the 210-250 gb range seems reasonable. -AJ -- 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] Boosting insert and indexing performance for 10 billion rows (?)
Thank you for the good suggestions. I've been applying them to a smaller subset of my database to see how it might perform. I had tried fiddling with the cache size but it seemed to make performance slightly degrade in my case. In principle it should work, so perhaps my smaller database isn't entirely representative of the larger one. I will test it out fully later. However, the threading pragma is showing a pretty dramatic (3-4x increase in speed) improvement, so I think this will be a huge benefit. For some reason I had overlooked that setting. The attach limit looks like it would be good to try at some point. I am using Windows, but unfortunately I am preparing this database for use in a tool that will be shared with other installations/operating systems. In order to keep it extremely simple for the end user, I'm sticking to whatever I can rig through the pre-compiled Python version, but for my own personal projects I will try to fiddle with attach. The ability to split the database into multiple files will come in handy if I end up tackling anything larger. -AJ -- 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
[sqlite] Boosting insert and indexing performance for 10 billion rows (?)
Hi everyone - I've been using SQLite through Python (3.7) for a scientific project. The data comes out to 10 billion rows of an 8 byte signed integer (~200-300 gb pre-index), and while insertion takes ~6 hours, indexing takes 8 hours by itself. Indexing also seems to slow as it is built. Does this sound reasonable for this amount of data? I'm running this on an SSD to try and help the IO as best I can. While I've been getting a lot of mileage out of this, I was wondering if you had any tips on getting it to run faster. I've tried various PRAGMA modifications to try and help the insert, but I'm wondering if there's anything I can do to appreciably speed any of this up. For my purposes, I don't need any sort of safeguards for power loss etc. - I've already turned the journal and synchronous to off. This is a database that will be built one time and accessed on occasion, and query speed is fine as-is. The only things I can think about are perhaps partitioning the table and running the indexing in parallel on the partitions, but this seems clunky, especially with Python's 10-database ATTACH limit. The parameter for modifying this is non-obvious in the Python package, and since I haven't done the experiment, I don't know to what extent that would help. Thank you for any insight into this. The database is working fine as-is, but I am trying to see for the sake of convenience and education if I can get it to insert and/or index faster. Cheers, -AJ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Typo in documentation for fts trigger
http://www.sqlite.org/fts3.html CREATE TRIGGER t2_bu AFTER UPDATE ON t2 BEGIN INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c); END; CREATE TRIGGER t2_bd AFTER INSERT ON t2 BEGIN INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c); END; should have the _bu and _bd postfixes changed to _au and _ad. CREATE TRIGGER t2_au AFTER UPDATE ON t2 BEGIN INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c); END; CREATE TRIGGER t2_ad AFTER INSERT ON t2 BEGIN INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c); END; AJ ONeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in example rank function
Correction: my code functions as described in the documentation, but not as it is described in the comments of the example code. The example code comments state that the weight is determined by every column whereas the example given above the code states that the weight as determined by the row. example mentioned in documentation: rank(matchinfo(documents), documents_data.weight); code comment use case: rank(matchinfo(documents), , , ...); The comment in the code makes more sense then the example for common use cases (title vs body rather than one row vs another), however, in the case that a column isn't supplied it makes sense to use a default weight of 1.0 if the weights aren't supplied. AJ ONeal On Sat, Jul 21, 2012 at 5:27 PM, AJ ONeal wrote: > I also found an error in the signedness of ints (using -Wall -Werror). > > The corrected code here functions as described in the documentation's > example: > https://github.com/coolaj86/sqlite3-fts4-rank/blob/master/fts4-rank.c#L59 > > AJ ONeal > > > On Sat, Jul 21, 2012 at 4:39 PM, AJ ONeal wrote: > >> Back to looking at http://www.sqlite.org/fts3.html#appendix_a >> >> Notice the line: >> >> if( nVal!=(1+nCol) ) goto wrong_number_args; >> >> nVal will always be 2 with the given use case: >> >> rank(matchinfo(documents), documents_data.weight) >> >> or in the previous use case it will be 1 >> >> rank(matchinfo(documents)) >> >> Seems that it would be best to assign a default weight of 1 if nVal == 1 >> or the double value of apVal[1] otherwise. >> >> Also the line >> >> double weight = sqlite3_value_double(apVal[iCol+1]); >> >> should be simplified to: >> >> double weight = sqlite3_value_double(apVal[1]); >> >> AJ ONeal >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] matchinfo example does not work as documented
Not everyone who uses the sqlite docs is a 1337 c guru. Most of them are probably rubyists, pythonistas, some php script kiddies, and, as of late, android and ios devs (some of which are a bit more leet). Monkey see, monkey do. If I see output in comments, I expect the same output when I run the code. It wasn't clear to me, it's obviously very clear to you. I think the docs could be better, more eye-catching on the important points: -- NOTE: blobs print as empty strings in the sqlite3 cli, if you want to see debug output use hex(). -- NOTE: this isn't what the output you see looks like, it's been hand-formatted for easy-reading But y'all do whatcha gon' do. AJ ONeal On Sat, Jul 21, 2012 at 6:22 PM, Pavel Ivanov wrote: > On Sat, Jul 21, 2012 at 7:35 PM, AJ ONeal wrote: > >> > >> Read the documentation carefully: > >> http://www.sqlite.org/fts3.html#matchinfo. Right the first paragraph: > >> > >> The matchinfo function returns a blob value. If it is used within a > >> query that does not use the full-text index (a "query by rowid" or > >> "linear scan"), then the blob is zero bytes in size. Otherwise, the > >> blob consists of zero or more 32-bit unsigned integers in machine > >> byte-order. > >> > >> What part of this paragraph makes you believe that if you print the > >> result of matchinfo as text you will see something meaningful? > >> > > > > The part where it shows output in the comments of the example that, > > according to common conventions used in documentation, would indicate it > is > > the output of the function (which it is, just not the user-viewable > output). > > Where did you see that? Could you cite it? All I see is > > -- ... If each block of 4 bytes in the blob is interpreted > -- as an unsigned integer in machine byte-order, the values will be: > -- > -- 3 2 1 3 2 0 1 1 1 2 2 0 1 1 0 0 0 1 1 1 > > So it's clearly says: you have to interpret it, it's not like you just > print it as string. > > > Plenty of languages (javascript, ruby, python, etc, etc, etc) > pretty-print > > native objects when they are to be represented as text. > > > > What about that paragraph indicates that the sqlite3 cli doesn't know how > > to pretty-print understand its own native types? > > sqlite3 cli understand its native type which is BLOB. But how should > it pretty-print it? BLOB can contain absolutely any information and > it's not its job to parse SQL to try to understand what this blob can > actually contain. > > > Pavel > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] matchinfo example does not work as documented
> > Read the documentation carefully: > http://www.sqlite.org/fts3.html#matchinfo. Right the first paragraph: > > The matchinfo function returns a blob value. If it is used within a > query that does not use the full-text index (a "query by rowid" or > "linear scan"), then the blob is zero bytes in size. Otherwise, the > blob consists of zero or more 32-bit unsigned integers in machine > byte-order. > > What part of this paragraph makes you believe that if you print the > result of matchinfo as text you will see something meaningful? > The part where it shows output in the comments of the example that, according to common conventions used in documentation, would indicate it is the output of the function (which it is, just not the user-viewable output). Plenty of languages (javascript, ruby, python, etc, etc, etc) pretty-print native objects when they are to be represented as text. What about that paragraph indicates that the sqlite3 cli doesn't know how to pretty-print understand its own native types? AJ ONeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in example rank function
I also found an error in the signedness of ints (using -Wall -Werror). The corrected code here functions as described in the documentation's example: https://github.com/coolaj86/sqlite3-fts4-rank/blob/master/fts4-rank.c#L59 AJ ONeal On Sat, Jul 21, 2012 at 4:39 PM, AJ ONeal wrote: > Back to looking at http://www.sqlite.org/fts3.html#appendix_a > > Notice the line: > > if( nVal!=(1+nCol) ) goto wrong_number_args; > > nVal will always be 2 with the given use case: > > rank(matchinfo(documents), documents_data.weight) > > or in the previous use case it will be 1 > > rank(matchinfo(documents)) > > Seems that it would be best to assign a default weight of 1 if nVal == 1 > or the double value of apVal[1] otherwise. > > Also the line > > double weight = sqlite3_value_double(apVal[iCol+1]); > > should be simplified to: > > double weight = sqlite3_value_double(apVal[1]); > > AJ ONeal > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] error in example rank function
Back to looking at http://www.sqlite.org/fts3.html#appendix_a Notice the line: if( nVal!=(1+nCol) ) goto wrong_number_args; nVal will always be 2 with the given use case: rank(matchinfo(documents), documents_data.weight) or in the previous use case it will be 1 rank(matchinfo(documents)) Seems that it would be best to assign a default weight of 1 if nVal == 1 or the double value of apVal[1] otherwise. Also the line double weight = sqlite3_value_double(apVal[iCol+1]); should be simplified to: double weight = sqlite3_value_double(apVal[1]); AJ ONeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No documentation for xFunc?
Through experimentation it appears that the signature should be documented
as
void (*xFunc)(sqlite3_context* pCtx, int nArgs, sqlite3_value** apArgs)
Where
sqlite3_context* could be expressed as pCtx (the database connection)
int could be expressed as nArgs (analogous to argc)
sqlite3_value** could be expressed as apArgs (analogous to argv)
I based my experiment on the rankfunc example on the fts docs:
static void rankfunc(sqlite3_context *pCtx, int nVal, sqlite3_value
**apVal){
sqlite3_result_double(pCtx, nVal);
}
// the 3 means that 3 arguments are required.
sqlite3_create_function(db, "testfn", 3, SQLITE_ANY, 0, testfn, 0, 0);
testfn("1", "2", "3"); -- nVal is 3
testfn("1", "2"); -- returns an error
Whereas
// the -1 means that any number of arguments are accepted.
sqlite3_create_function(db, "testfn", -1, SQLITE_ANY, 0, testfn, 0, 0);
testfn("1", "2", "3"); -- nVal is 3
testfn("1", "2"); -- nVal is 2
testfn(matchinfo(documents)); -- nVal is 1
testfn(matchinfo(documents), 2, 3, 4, 5, 6, 7); -- nVal is 7
sqlite3_create_function(db, "rank", -1, SQLITE_ANY, 0, rankfunc, 0, 0);
AJ ONeal
On Sat, Jul 21, 2012 at 2:36 PM, AJ ONeal wrote:
> According to
> http://www.sqlite.org/c3ref/create_function.html
>
> `sqlite3_create_function` accepts a callback parameter `void
> (*xFunc)(sqlite_func*,int,const char**)`
>
> However, I can't find the documentation which explains what the parameters
> to `xFunc` mean.
> http://www.sqlite.org/search?q=xfunc
> http://www.sqlite.org/capi3.html
> http://www.sqlite.org/c_interface.html#cfunc
> http://www.sqlite.org/c3ref/value_blob.html
> (I've also googled about outside of sqlite.org)
>
> If the documentation is available, please link me to it.
>
> If not, please explain what these 3 parameters are and how they should be
> used.
>
> AJ ONeal
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] No documentation for xFunc?
According to http://www.sqlite.org/c3ref/create_function.html `sqlite3_create_function` accepts a callback parameter `void (*xFunc)(sqlite_func*,int,const char**)` However, I can't find the documentation which explains what the parameters to `xFunc` mean. http://www.sqlite.org/search?q=xfunc http://www.sqlite.org/capi3.html http://www.sqlite.org/c_interface.html#cfunc http://www.sqlite.org/c3ref/value_blob.html (I've also googled about outside of sqlite.org) If the documentation is available, please link me to it. If not, please explain what these 3 parameters are and how they should be used. AJ ONeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] matchinfo example does not work as documented
Specifically: -- The next set of three integers (0 1 1) pertain to the hits for "default" -- in column 1 of the table (0 in this row, 1 in all rows, spread across -- 1 rows). -- SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'default transaction "these semantics"'; -- the blob appears as an empty string. To view an ascii representation, use hex() SELECT hex(matchinfo(t1)) FROM t1 WHERE t1 MATCH 'default transaction "these semantics"'; 030002000100030002000100010010002000200010001000000100010001000000 AJ ONeal On Sat, Jul 21, 2012 at 2:06 PM, AJ ONeal wrote: > > Now I see a number which matches my expectations: >> > SELECT hex(matchinfo(t1)) FROM t1 WHERE t1 MATCH 'default transaction >> > "these semantics"'; >> > >> > >> 030002000100030002000100010001000200020001000100010001000100 >> > >> > Will you update the documentation with this information? >> > >> >> What information do you propose to add to the documentation? > > > That in order to see the data the hex() function must be used. > > I imagine that most users who are following this documentation for the > first time are using the sqlite3 binary and following along by copying and > pasting the examples. > > AJ ONeal > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] matchinfo example does not work as documented
> > > Now I see a number which matches my expectations: > > SELECT hex(matchinfo(t1)) FROM t1 WHERE t1 MATCH 'default transaction > > "these semantics"'; > > > > > 030002000100030002000100010001000200020001000100010001000100 > > > > Will you update the documentation with this information? > > > > What information do you propose to add to the documentation? That in order to see the data the hex() function must be used. I imagine that most users who are following this documentation for the first time are using the sqlite3 binary and following along by copying and pasting the examples. AJ ONeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] matchinfo example does not work as documented
> > matchinfo returns a blob. Try running "hex(matchinfo(t1))" so that you can > see the blob content. Now I see a number which matches my expectations: SELECT hex(matchinfo(t1)) FROM t1 WHERE t1 MATCH 'default transaction "these semantics"'; 030002000100030002000100010001000200020001000100010001000100 Will you update the documentation with this information? I would submit a pull request myself, but I'm not familiar with fossil. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] matchinfo example does not work as documented
According do
http://www.sqlite.org/fts3.html#matchinfo
sqlite3 test.sqlite3
CREATE VIRTUAL TABLE t1 USING fts4(a, b);
INSERT INTO t1 VALUES('transaction default models default', 'Non
transaction reads');
INSERT INTO t1 VALUES('the default transaction', 'these semantics present');
INSERT INTO t1 VALUES('single request', 'default data');
SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'default transaction "these
semantics"';
Should result in:
3 2 1 3 2 0 1 1 1 2 2 0 1 1 0 0 0 1 1 1
However, I get back an empty string.
SELECT matchinfo(t1, 'ns') FROM t1 WHERE t1 MATCH 'default transaction';
returns two empty strings
sqlite3 --version
3.7.13 2012-06-11 02:05:22 f5b5a13f7394dc143aa136f1d4faba6839eaa6dc
AJ ONeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to compile and load the example fts4 rank function?
I have now built the example rank function and it loads without error. However, due to the matchinfo problem I discovered (and started a new thread about) I have yet to get the example to work correctly. Instead it always prints out Error: near line 19: wrong number of arguments to function rank() The unadulterated example code with the required headers / footers and an over simplified Makefile and documentation is available here: https://github.com/coolaj86/sqlite3-fts4-rank If you would like to try it for yourselves and point out any failing in my attempt, it would be much appreciated. AJ ONeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to compile and load the example fts4 rank function?
Thanks Keith! I put up a project which is exactly the same as the example, except that it includes a full buildable example for Mac, Linux, and Windows: https://github.com/coolaj86/sqlite3-fts4-rank I also found a document on the wiki: http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions The search terms I used were 'sqlite3 loadable extensions'. The search terms that I was using before were things like 'sqlite3 custom function' and 'sqlite3 user extension'. Luckily one of my searches lead me to a forum which linked to the wiki. AJ ONeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to compile and load the example fts4 rank function?
That example isn't from a 3rd party. It's the rank function listed here: http://www.sqlite.org/fts3.html#appendix_a Can you give me a link to documentation for what options to pass to gcc and what functions to call to activate such an extension? I've never done this before. AJ ONeal On Sat, Jul 21, 2012 at 10:34 AM, Richard Hipp wrote: > On Sat, Jul 21, 2012 at 3:36 AM, AJ ONeal wrote: > > > I naively tried > > > > wget > > > > > https://raw.github.com/gist/3154964/d570955d45580c095c99de6eb0c378395d4b076d/sqlite3-fts4-rank.c > > gcc -c sqlite3-fts4-rank.c -o sqlite3-fts4-rank.o > > > > sqlite3 > > .load sqlite3-fts4-rank.o > > > > But that didn't work. > > > > Can I get a link to the docs on this? I don't think I was using the right > > search terms to find it. > > > > Anything you find on GitHub is put there by a private third-party and is > not endorsed or supported by the SQLite core team. This doesn't mean it is > bad or deficient - it might be great software. It also doesn't mean you > cannot get help for it on this mailing list, since lots of people hang out > here who might know what you are talking about. Just understand that what > you are working with is not part of the SQLite core and is thus likely to > be unknown to a large percentage of the readers of this list, so don't be > disappointed if you don't get a quick response. Also, please don't blame > us if it lacks appropriate documentation or doesn't work so well. Thanks. > > > > > > > AJ ONeal > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to compile and load the example fts4 rank function?
I naively tried wget https://raw.github.com/gist/3154964/d570955d45580c095c99de6eb0c378395d4b076d/sqlite3-fts4-rank.c gcc -c sqlite3-fts4-rank.c -o sqlite3-fts4-rank.o sqlite3 .load sqlite3-fts4-rank.o But that didn't work. Can I get a link to the docs on this? I don't think I was using the right search terms to find it. AJ ONeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unrecognized parameter: content=""
Very interesting indeed! ls ~/Library/Caches/Homebrew/sqlite-* sqlite-3.7.10.tar.gz sqlite-3.7.13.tar.gz # # in original (failing) terminal # sqlite3 --version 3.7.7 2011-06-25 16:35:41 8f8b373eed7052e6e93c1805fc1effcf1db09366 which sqlite3 /usr/bin/sqlite3 # # in new (succeeding) terminal # which sqlite3 /usr/local/bin/sqlite3 It appears that the original version of sqlite3 that shipped with OS X is still in the PATH of the old terminal, but the new one installed with brew in loaded in the new on. Thanks for helping me figure this out! AJ ONeal On Sat, Jul 21, 2012 at 1:11 AM, Dan Kennedy wrote: > On 07/21/2012 02:03 PM, AJ ONeal wrote: > >> Weird: now that I've reproduced the error (using the script), I can no >> longer reproduce the successful execution: >> >> sqlite3 ':memory:' 'CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, >> c);' >> >> >> Hmm... yet when I open another terminal window it begins to work again. >> And when I go back to the previous terminal window it fails again. >> >> What about my terminal session / environment might be contributing to this >> behavior? >> > > Using two different SQLite binaries. > > content= is supported by version 3.7.11 or newer (or, unofficially, > 3.7.9 or newer). > > __**_ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unrecognized parameter: content=""
Weird: now that I've reproduced the error (using the script), I can no longer reproduce the successful execution: sqlite3 ':memory:' 'CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, c);' Hmm... yet when I open another terminal window it begins to work again. And when I go back to the previous terminal window it fails again. What about my terminal session / environment might be contributing to this behavior? AJ ONeal On Sat, Jul 21, 2012 at 12:48 AM, AJ ONeal wrote: > I'm on OS X > > brew install sqlite3 > sqlite3 --version > 3.7.13 2012-06-11 02:05:22 f5b5a13f7394dc143aa136f1d4faba6839eaa6dc > > Interestingly when I do it like this it works: > sqlite3 ':memory:' 'CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, > c);' > > But when I do it like this > sqlite3 -init meta-fts.table.sql meta-fts-new.sqlite3 > > where `meta-fts.table.sql` is https://gist.github.com/3154874 > wget > https://raw.github.com/gist/3154874/8f582883d62c82aeafed5eabf639e603ec1ac379/meta-fts.table.sql > > Then it throws the error. > > AJ ONeal > > > On Fri, Jul 20, 2012 at 11:34 PM, Kees Nuyt wrote: > >> On Fri, 20 Jul 2012 23:12:43 -0600, AJ ONeal wrote: >> >> >I've tested with sqlite3 and the nodejs sqlite3 module and I get this >> error >> >when using the example from the documentation (as well as variants more >> >suitable to my application) for full-text search. >> > >> >Docs: >> >http://www.sqlite.org/fts3.html#section_6_2_1 >> > >> >Test: >> >CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, c); >> >CREATE VIRTUAL TABLE fts USING fts4(content="data", name TEXT, path >> TEXT); >> > >> >Result: >> >unrecognized parameter: content="" >> > >> >Was this never implemented? Is it deprecated? Does it require special >> build >> >options? Any thoughts? >> >> Cannot reproduce that error message in the test above with: >> SQLite version 3.7.12.1 2012-05-22 02:45:53 or >> SQLite version 3.7.14 2012-06-21 17:21:52. >> >> Is your test sample complete? >> >> >> -- >> Regards, >> >> Kees Nuyt >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Built-in ranking functions for fts?
In the documentation it gives examples for `countintegers` and `rank`, but those functions aren't built in. http://www.sqlite.org/fts3.html#appendix_a So how can I do anything "useful" with fts as-is? Are there built-in functions that provide some sort of generic result ranking? While I respect that many users will want to implement a custom solution, it seems that the solutions mentioned solve the 90/10 problem. If not, to whom should I propose that the 'good enough' functions mentioned in the documentation should be part of the standard FTS build? AJ ONeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unrecognized parameter: content=""
I'm on OS X brew install sqlite3 sqlite3 --version 3.7.13 2012-06-11 02:05:22 f5b5a13f7394dc143aa136f1d4faba6839eaa6dc Interestingly when I do it like this it works: sqlite3 ':memory:' 'CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, c);' But when I do it like this sqlite3 -init meta-fts.table.sql meta-fts-new.sqlite3 where `meta-fts.table.sql` is https://gist.github.com/3154874 wget https://raw.github.com/gist/3154874/8f582883d62c82aeafed5eabf639e603ec1ac379/meta-fts.table.sql Then it throws the error. AJ ONeal On Fri, Jul 20, 2012 at 11:34 PM, Kees Nuyt wrote: > On Fri, 20 Jul 2012 23:12:43 -0600, AJ ONeal wrote: > > >I've tested with sqlite3 and the nodejs sqlite3 module and I get this > error > >when using the example from the documentation (as well as variants more > >suitable to my application) for full-text search. > > > >Docs: > >http://www.sqlite.org/fts3.html#section_6_2_1 > > > >Test: > >CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, c); > >CREATE VIRTUAL TABLE fts USING fts4(content="data", name TEXT, path TEXT); > > > >Result: > >unrecognized parameter: content="" > > > >Was this never implemented? Is it deprecated? Does it require special > build > >options? Any thoughts? > > Cannot reproduce that error message in the test above with: > SQLite version 3.7.12.1 2012-05-22 02:45:53 or > SQLite version 3.7.14 2012-06-21 17:21:52. > > Is your test sample complete? > > > -- > Regards, > > Kees Nuyt > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] unrecognized parameter: content=""
I've tested with sqlite3 and the nodejs sqlite3 module and I get this error when using the example from the documentation (as well as variants more suitable to my application) for full-text search. Docs: http://www.sqlite.org/fts3.html#section_6_2_1 Test: CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, c); CREATE VIRTUAL TABLE fts USING fts4(content="data", name TEXT, path TEXT); Result: unrecognized parameter: content="" Was this never implemented? Is it deprecated? Does it require special build options? Any thoughts? AJ ONeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite_exec() encoding?
what encoding does sqlite_exec() callback function receive data in ? some tests i've done seem to suggest UTF-8 other tests show chars U+80 to U+FF are returned as single chars with values 128-255. (suggesting its not UTF-8) where is *any* documentation about this? -- Not sent from an iPhone ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Math Update with two tables
I would like to use math with more then one table, for example I know how to do this (Factors & Potential = columns) UPDATE Records SET Factors= (Factors * Potential) but i don't know how with an additional table, i came up with UPDATE Records,Table2 SET Factors= (Factors * Potential Table2.Field23) Any help is greatly appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to maintain EXCLUSIVE access to database continuously ?
use your chosen OS's reader/writer locks per user. Some additional info User (Client App) connects to middle-tier application-server and then user may access any one company in normal or exclusive mode. Only application-server communicates with database. If user wants exclusive access, Application-Server needs to maintain that exclusive access to database for that user. Rohit - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Wish to store a C structure in sqlite column
Narendran wrote: thanks a lot, I believe i can store a structure now, but there is still a cache . I am in the process on creating a Independant API,I can store a structure in SQLite thro the above specified ways. what if some one else wants to use my API's which i used to create the DBI's ,and the destination Database engine doesn't support the blob datatypes. My question may be silly, but this is a serious issues for me. I am unable to resolve the problem for 3 days,hat's off to everyone. Thanking you , B.Narendran my suggestion of using base64 encoding for your struct will mean you can store it in any text field in any database, and its also possible to send it thru just about any protocol and it will most likely still be OK. you do know that you should not store pointers in your structure? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Wish to store a C structure in sqlite column
I hackish method might be something like this:
struct s
{
...
};
struct s myS;
char buf[sizeof(s)*2]; // *2 as base64 encoding will be approx 33% bigger.
base64_encode( &myS, buf, sizeof(s) );
INSERT INTO table ( myTextField ) VALUES ( 'buf' );
then retrieval is the opposite.
Noel Frankinet wrote:
Narendran a écrit :
Noel Frankinet wrote:
Narendran a écrit :
Dear Friends,
I am in the process of forming a Generic API,(sql oriented and
BerkelyDB
and sister databases). In the process of integration ,i like to
store a
Structure in Sqlite.
as far as my knowledge SQLITE allows me to declare the column types
suppoted by the programming languare or say i am using blob . My
requirement
is i wish to store a structure in the SQLite column.
I am unable to form a sql statement to store the structure ,i am
also
not
clear with whether i can have a strucure as column type.
suggestions will be really helpful.
Thanking you,
B.Narendran
You will need to turn your c struct into a blob and store that blob.
When retrieving the blob, you need a way to turn it back into your
struct.
Its releatively easy if your struct does not contains pointers.
Best wishes
--
Noël Frankinet
Gistek Software SA
http://www.gistek.net
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Dear Frankinet,
Thanks for ur reply,
I am unable to understand what u have said. I am having a structure
and I
am converting in to a blob. This means i am supposed to remove the
'\0' in
between the strucure and put a final '\0' (NULL) character . Blob
need only
on e null character to terminate it.
I tried to memcopy the structure elements and store them ,I can
store but
i am unable to find a way to retrieve it back.
typedef struct ethernetcard1
{
char port[10];
char ipaddress[20];
char mask[20];
int bandwidth;
}
what i tried is
char *buffer;
int bufferlen;buffersize;
bufferlen = strlen(port)+strlen(ipaddress)+strlen(mask)+sizeof(int)+1;
memcpy(buffer,user.port,strlen(user.port);
buffersize = strlen(user.port);
memcpy(buffer,user.ipaddress,strlen(user.ipaddress));
buffersize += strlen(user.ipaddress);
and finally i included a NULL character to the buffer to make it as
string
and i can insert in to a text field in sqlite column . I am unable to
figure
out a way to retrieve it back if i am storing in this way or a blob
type I
think blob will be similar to this .
expecting ur valuable suggestion.
Thanking you,
Narendran
hello Narendran,
Unfortunately, I'm still using 2.xx, so I encode the blob in character
using sqlite_encode and I decode it back when I get it from sqlite.
You are on the right track, but for string you should have a way to
store the length.
I have written some encoding function (like write_string and
read_string) to help encoding and decoding from the buffer (and avoi
all those mesy memcpy).
I hope this help
Best wishes
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] cwd error?
Oh, it created one in the cwd, not the place where i thought the DB should be... is there a way to turn that 'feature' off, so that i can prevent that from happening, i'd prefer just an error. Will Leshner wrote: On 9/21/06, AJ <[EMAIL PROTECTED]> wrote: any ideas? You haven't really opened the database you think you have? What happens when you do: SELECT * FROM sqlite_master; - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] cwd error?
problem: SQL error: no such table: input_queue but when i check it using the commandline app, its clearly there. i just created another table called 'test' with 1 col of integers and inserted '34'and it .dumps OK (using the command line app) but says :SQL error: no such table: test has it something to do with calling the application that uses this db from the non-cwd ? If so, why is it not saying something like "failed to load db"it must be loading the database.. as it gets as far as a SQL query. but then fails. When i had everything i the same folder, everything worked OK. any ideas? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] What is that message?
It is an auto-response email generated because Stiaan is(was) apparently subscribed to this mailing list at one point. On Sat, 01 Jan 2005 16:15:56 +0200, Ahmet Aksoy <[EMAIL PROTECTED]> wrote: > Hi, > When I send a message to the group, I get the following message. > What is it? Who is Stiaan? Why should I contact him? > Ahmet Aksoy > > Hello [EMAIL PROTECTED] > > I regret to inform you that Stiaan is no longer employed by IndusProject > Developers as he decided to pursue new opportunities with a different company. > > Please contact him for his new email address. > > The Indus mail server > >
Re: [sqlite] A late Christmas gift / early New Year's gift...
I have 6, too. Haven't been able to get rid of them :) If you want them, you're more than welcome to have them. AJ On Wed, 29 Dec 2004 17:03:28 +0100, Nemanja Corlija <[EMAIL PROTECTED]> wrote: > If somebody didn't get an Gmail invite from Joseph, I have 4 more to > offer to SQLite community > > Cheers, > Nemanja
Re: [sqlite] VACUUM function problem
Defragging the database just removes empty pages making the database file smaller. It doesn't actually touch any data. Have a look here: http://www.sqlite.org/lang.html#vacuum AJ On Tue, 28 Dec 2004 17:30:10 +0100, D.W. <[EMAIL PROTECTED]> wrote: > I have deleted some objects in my database. Now I have objects at id > (INTEGER PRIMARY KEY) =1,3,4,5,7,8,9,16,17,20 . > id=2,6,10,.. are empty. I want to defrag the database so that I have objects > continuously at id=1,2,3,4,5,6,7,... > Is the vacuum function not the right function? > Daniel
