[sqlite] FTS3/FTS4+ICU segfault on NULLs
This code produce segfault on current trunk SQLite and some previous releases (3.7.0.1 and 3.7.3): CREATE VIRTUAL TABLE fts USING fts3(name,TOKENIZE icu ru_RU); insert into fts (name) values (NULL); insert into fts (name) values (NULL); delete from fts; Or: CREATE VIRTUAL TABLE fts USING fts3(name,TOKENIZE icu en_US); insert into fts (name) values (NULL); insert into fts (name) values (NULL); delete from fts; ... -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "Error: disk I/O error" on big databases vacuuming
I try to vacuum database about 11Gb size on debian squeeze host with 1,5 Gb RAM: sqlite3 test.db 'vacuum;' Error: disk I/O error Note: any new files does not created on vacuuming process (may be created journal, does not it?). But this work correct: sqlite3 test.db '.dump'|sqlite3 test2.db -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Error: disk I/O error" on big databases vacuuming
There is >30 Gb of free space. 2011/3/8 Jay A. Kreibich <j...@kreibi.ch>: > On Tue, Mar 08, 2011 at 07:51:22PM +0300, Alexey Pechnikov scratched on the > wall: >> I try to vacuum database about 11Gb size on debian squeeze host with 1,5 Gb >> RAM: >> >> sqlite3 test.db 'vacuum;' >> Error: disk I/O error >> >> Note: any new files does not created on vacuuming process (may be >> created journal, does not it?). > > Yes. A copy of the database and a journal file. Both may reach the > size of the original database. So, if you database is 11GB in size, > you may need as much as 22GB of free disk space to complete the > vacuum process. > > -j > > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible bug in FTS3 "NOT" operator
With ICU extension does not work "NOT ..." construction. Below the example from FTS3 documentation: sqlite> CREATE VIRTUAL TABLE docs USING fts3(); sqlite> INSERT INTO docs(docid, content) VALUES(1, 'a database is a software system'); sqlite> INSERT INTO docs(docid, content) VALUES(2, 'sqlite is a software system'); sqlite> INSERT INTO docs(docid, content) VALUES(3, 'sqlite is a database'); sqlite> SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite'; a database is a software system sqlite> SELECT * FROM docs WHERE docs MATCH 'NOT sqlite'; Error: malformed MATCH expression: [NOT sqlite] Without ICU this work fine. Please check this on upstream version becouse I use some patches for FTS3 extension. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS4 bug in last_insert_rowid()
$ sqlite3
SQLite version 3.7.6
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE VIRTUAL TABLE fts USING fts4(a,TOKENIZE icu russian);
sqlite> select last_insert_rowid();
0
sqlite> insert into fts(a) values ('test');
sqlite> insert into fts(a) values ('test');
sqlite> select last_insert_rowid();
2
sqlite> delete from fts where rowid=2;
sqlite> select last_insert_rowid();
3
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
2011/4/4 Nico Williams <n...@cryptonector.com>:
> You're missing something: FTS4 is a virtual table
In triggers code we can't know about virtual tables! So in triggers we
may get wrong results?..
As example (this _does not_ work now):
CREATE TRIGGER view_job_update instead of update on view_job
begin
insert into job_record
(record_version,user_id,id,users,routes,forms,ts_from,ts_to,name,state)
values (OLD.record_version+1, NEW.user_id, OLD.id, NEW.users,
NEW.routes, NEW.forms, NEW.ts_from, NEW.ts_to, NEW.name, NEW.state);
insert into job (id,current_id,ts,ts_from,ts_to,name)
select id,rowid,ts,ts_from,ts_to,name from job_record where
rowid=last_insert_rowid();
delete from job_fts where rowid=last_insert_rowid();
insert into job_fts (rowid,задание,дата,автор,состояние)
select id,name,date('now','localtime'),user,state from view_job
where id=last_insert_rowid();
end;
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS4 compress/uncompress functions
Which functions will be correct? As example, affinity for uncompress functions can be as "blob" as "text". Now this is not important but some tests will be nice for future compability. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 compress/uncompress functions
What type of value return _uncompress_? The original content may be blob or text, so uncompress may return blob I think... Or we need to store in compressed field the type of this. The compression does not work for indices?! But FTS index compression can reduce indices size by factor 3x-4x... 2011/4/13 Dan Kennedy <danielk1...@gmail.com>: > On 04/13/2011 06:22 AM, Alexey Pechnikov wrote: >> Which functions will be correct? As example, affinity for uncompress >> functions can be as "blob" as "text". Now this is not important but >> some tests will be nice for future compability. > > You mean what type of value should compress() return? > > Any type. So long as uncompress() can transform the value back > to the original text. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 compress/uncompress functions
Hm, FTS internally may operate with text data only and so blob/text types are equal. In my compression patches for FTS the blob result of uncompress() is correct... but there is no documentation for FTS4 compression and will be type ignoring of uncompressed values correct in future? Saving types of uncompressed values isn't trivial but is it needed?.. 2011/4/13 Dan Kennedy <danielk1...@gmail.com>: > On 04/13/2011 02:49 PM, Alexey Pechnikov wrote: >> What type of value return _uncompress_? The original content may be >> blob or text, so uncompress may return blob I think... Or we need to >> store in compressed field the type of this. > > I guess that is correct. uncompress() should return the same type > and value as was passed to compress(). > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fix FTS3/FTS4+ICU segfault on NULLs
Bug report is here http://www.mail-archive.com/sqlite-users@sqlite.org/msg59442.html The fix is here http://sqlite.mobigroup.ru/fdiff?v1=e7d3fea30c80e2f3=f7d463b30fd7d54f I'm frustrated with no upstream fix of segfault in core extension :( -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up index insertion
RAM drive or SSD can help you... -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Set minimum word/character length in FTS4?
No. But you can create custom tokenizer for ignoring short words. 2011/4/28 skibulk <skib...@gmail.com>: > > Hi, I know that you can set the minimum word size in MySQL. I.E. if you > querry "dog" and the Minimum character length is set to 4 you won't get any > results. Is this same customization option avaliable in SQLite's Full Text > Search engine? > > Thanks! > -- > View this message in context: > http://old.nabble.com/Set-minimum-word-character-length-in-FTS4--tp31499462p31499462.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syncing databases on different servers
See http://sqlite.mobigroup.ru/wiki?name=sqlite3-rdiff 2011/6/1 Ian Hardingham <i...@omroth.com>: > Hey guys, > > First of all, I'm aware that SQLite is not a good choice for concurrency > over several machines. > > I do not need instant syncing of SQLite databases, however I do have a > table stored on server X which I would like to gradually send changes of > to various other servers connected by TCP. > > In the first instance, I'd be quite happy to send rows with ids which do > not exist in the other servers' tables, and not worry about changes to rows. > > Does anyone have any advice on this matter? > > Thanks, > Ian > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syncing databases on different servers
See undo extension: http://sqlite.mobigroup.ru/artifact/265e408b4352d66cfc79a9990cb2c22fb390d3b6 http://sqlite.mobigroup.ru/artifact/2250bbbc83f80eff73ce003ab7a30293c688ae9b And history extension: http://sqlite.mobigroup.ru/artifact/0b8eaab9564575fcf37f459bb85c501f65089b31 http://sqlite.mobigroup.ru/artifact/315fe02f9d84a4b40270d736e6e996599e05e010 http://sqlite.mobigroup.ru/artifact/4bc946d184aac14660d13531f67e516131ab7175 And versioning extension: http://sqlite.mobigroup.ru/artifact/7c6ef278210c6a06e9efd4b197649ff6084cea3c http://sqlite.mobigroup.ru/artifact/db7f9954fbd9bb825f338f4717ec9a89621da08e 2011/6/1 Gary_Gabriel <gabrielrobert.qu...@googlemail.com>: > Hi Ian, > >> I do not need instant syncing of SQLite databases, however I do have >> a table stored on server X which I would like to gradually send >> changes of to various other servers connected by TCP. >> >> In the first instance, I'd be quite happy to send rows with ids which >> do not exist in the other servers' tables, and not worry about >> changes to rows > > Here are two methods that you may consider, they are suitable to send > new rows or sync entry value changes. > > 1) The first is a standard logging method. To make an example of a schema: > - There are three tables: SearchIndex, ThreadSession and > ThreadIndex. Each table has three indexing columns or fields. Indexing > in this case means if the entry value changes, then the new information > in this row should be synced with the server. There are other columns > but their content is further desciption relative to the indexing columns > in the table. > - INSERT, UPDATE and DELETE triggers monitor the 3 indexing columns > in each table using the LIKE operator. A change in the entry value of > the indexing fields fires the trigger which inserts a record in a > logging table with the table name and Integer Primary Key. Joining the > data table with the log table generates the row containing the current > values. > - Use the generated row to sync the dbs. > - This method has been heavily exercised and tested over consider time > and proves to be reliable. Using the LIKE operator in this scenario has > not been detrimental to performance and accurate. > > 2) Method 2 uses temporary tables to hold the queried values in a user > session. The same tables as in 1) are used in this example. One > temporary table holds the query result rows for each table. So for the > tables SearchIndex, ThreadSession and ThreadIndex there are also > SearchIndex_temp, ThreadSession_temp and ThreadIndex_temp. In addition > one column flags changes based on the same criteria as 1). If the > contents of one of the three indexing columns or fields in the temp > tables changes, then the temp row is flagged. > - In the three tables DELETE the rows that have not changed and are not > flagged and what remains can be used to sync the changes. > > > - Gary Gabriel > > > > _______ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
I start tclsqlite in 16 threads on 2-core Intel Xeon servers and these work fine. 2011/6/3 Ian Hardingham <i...@omroth.com>: > Guys, the server for this game - > > http://www.frozensynapse.com > > uses SQLite. We've had an unexpectedly successful launch which has > resulted in the server being swamped with players, and I'm trying to > optimise everywhere I can. I've always been under the impression that > SQLite is pefectly fast and it's the scripting language I wrote the > server in which is too blame. (Yes, I know writing a back-end in a > single-threaded scripting language is an absolutely terrible idea). > However, everyone in the industry I talk to says that SQLite must be one > of the problems. > > I may be looking at a complete re-write. I may also need to have a > solution which scales beyond one machine. Can anyone give me advice on > this matter specifically? > > (The video on that website at 2.04 gives a good idea of what kind of > functions are being powered by the database). > > Thanks, > Ian > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
I think it's very slow. Update of non-indexed fiels may be faster. Do you create a new db connection for each update?.. Or may be you have a lot of unused indicies? 2011/6/3 Ian Hardingham <i...@omroth.com>: > Thank you Igor, I'll do some more thorough profiling. > > When I run the query: > > UPDATE multiturnTable SET complete=1 WHERE id=-5 > > This takes ~45ms (as reported by SQLite's profile) - is this in the > right ballpark? I'm running a fairly fast modern intel chip here. > > Thanks, > Ian > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can we configure high availibility with SQLITE?
No, not like to other. Sqlite engine does not has internal high availability functions. Use application-level sync or async replication/logging if needed. 2011/6/11 irfan khan <irfan.8...@gmail.com>: > Hi, > > Can we configure high availibility with SQLITE databases like we can do > with other rdbms. > > -- > > > > Thanks & Regards > > Irfan Khan > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Patch for fts3_tokenizer.c to compile FTS3 extension as a loadable module
In fts3_tokenizer.c these lines are invalid: #include "sqlite3ext.h" #ifndef SQLITE_CORE SQLITE_EXTENSION_INIT1 #endif Use single line instead: #include "sqlite3.h" -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to parse VIEW fields definitions?
As example, we have view: create view vtest as select name1 || ' ' || name2 as name from test; How to get the definition of "name" field (will be "name1 || ' ' || name2")? Of cource, the view can be more complex. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to parse VIEW fields definitions?
The new PRAGMA or Virtual Table will be more useful. But I don't sure about possibility of this. 2011/6/14 Nico Williams <n...@cryptonector.com>: > On Tue, Jun 14, 2011 at 1:58 PM, Alexey Pechnikov > <pechni...@mobigroup.ru> wrote: >> As example, we have view: >> >> create view vtest as select name1 || ' ' || name2 as name from test; >> >> How to get the definition of "name" field (will be "name1 || ' ' || >> name2")? Of cource, the view can be more complex. > > Take Lemon and the parse.y file from SQLite3, and build a parser that > does what you want? > > Nico > -- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation typos and inconsistent logic
1. May be added note "All triggers associated with the view are also deleted." to http://www.sqlite.org/lang_dropview.html 2. This behaviour is incompatible with other DBMS and undocumented: sqlite> create table a(a); sqlite> create view b as select * from a; sqlite> drop table a; sqlite> select * from b; Error: no such table: main.a sqlite> .s CREATE VIEW b as select * from a; sqlite> create view a as select 1; sqlite> create view b as select * from a; sqlite> drop view a; sqlite> select * from b; Error: no such table: main.a sqlite> .s CREATE VIEW b as select * from a; This strange, really. View cannot reference objects in attached databases but view can reference deleted object? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best solution to import big DBF file?
1. sqlite3-dbf - converter of XBase / FoxPro tables to SQLite http://sqlite.mobigroup.ru/wiki?name=sqlite3-dbf 2. Or see the VirtualText extension from Spatialite project. This extension can be builded with SQLite http://sqlite.mobigroup.ru/dir?name=ext/virtualtext http://sqlite.mobigroup.ru/dir?name=ext/iconv I did support SQLite build with this some times ago (deb packages), but the first solution is more useful for me now. 2011/6/27 Gilles Ganault <gilles.gana...@free.fr>: > Hello > > I have a 87MB file in DBF format that I'd like to import into SQLite. > > What is the best tool for this, free or affordable? > > Thank you. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error: malformed database schema
$ sqlite3 test.db -- Loading resources from /home/mbg/.sqliterc SQLite version 3.7.7.1 2011-06-28 16:48:37 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select count(*) from sqlite_master; 283 .dump ... sqlite> select count(*) from sqlite_master; Error: malformed database schema (job_user_id_idx) - no such table: main.job_record .dump ... sqlite> pragma integrity_check; *** in database main *** Page 5 is never used Page 35 is never used Page 67 is never used Page 97 is never used .... -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Storing/editing hierarchical data sets
See FTS3 extension where the full-text index is stored in multi btree in regular tables. Note: FTS2 is more simple. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database handle access from tokenizer module (stopwords table support)
I want to add stopwords table support for ICU tokenizer but there is no database handle access (icuOpen). Any ideas? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3: synonyms dictionary and tokens length
With synonyms dictionary the result token length can be more then original token length. Is it problem for current realization of FTS? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3: synonyms dictionary and tokens length
With 0-length token in icuNext there is the error: Error: SQL logic error or missing database May xNext returns 0 length when the token is stopword? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3: synonyms dictionary and tokens length
> No. Don't return anything for a stop word. Just advance to the next > non stop-word token and return it. Thanks, I did and it's work. And another question... Is there any way to use multi-word synonyms? Like to: sqlite -> Open Source SQLite DBMS I think the single token "Open Source SQLite DBMS" will not useful. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3: synonyms dictionary and tokens length
I want to add the table
CREATE TABLE ext_fts_synonyms(word text not null unique, synonym text not null);
insert into ext_fts_synonyms('sqlite','sqlite dbms');
And replace in tokenizer the term 'sqlite' to 2 terms 'sqlite' and 'dbms'
for search by queries like to
> select text from fts where fts match 'dbms educate';
'SQLite may be useful for education'
But how to return from tokenizer 2 terms or more instead of single term?..
Note: Snowball stemmer and stopwords table I did add and can do:
> select text from fts where fts match 'sqlite educate'
SQLite may be useful for education
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] my new site is using as backend sqlite
2011/7/14 Sebastian Bermudez <sebastian.bermu...@yahoo.com>: > please, don't smile !! this is very very micro site. for my personal url > shorter function May be you want to see the test site of the business product on Tcl + SQLite? :) https://merch.mobigroup.ru/ The site language is russian. You can try stemmed SQLite FTS4 search and enjoy by fast work. Note: you can guess the admin user password :D -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3: synonyms dictionary and tokens length
2011/7/15 Dan Kennedy <danielk1...@gmail.com>: > I think you could just have the tokenizer return "dbms" whenever > it sees "sqlite" in the input. But queries to original text will not work: select text from fts where fts match 'sqlite educate'; You can see synonyms dictionary in PostgreSQL full-text search as example. It's very common task for many applications. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3: synonyms dictionary and tokens length
2011/7/15 Dan Kennedy <danielk1...@gmail.com>: >> But queries to original text will not work: >> select text from fts where fts match 'sqlite educate'; > > I think it will. Query strings - like 'sqlite educate' - are > also parsed using the tokenizer. So the query will be transformed > to 'dbms educate' before it is run. I wanted to say - we can't rank results differently. The query 'sqlite educate' may return result 'dbms education' with low rank and 'sqlite education' with high rank. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [FTS3] The Compress and Uncompress functions and extension
But why you don't use compress/uncompress functions from DRH? See http://www.mail-archive.com/sqlite-users%40sqlite.org/msg17018.html I did wrap these into extension and add SQLITE_COMPRESS_MIN_LENGTH http://sqlite.mobigroup.ru/artifact/a5da96353bb851b34114052ba85041fdffb725cd http://sqlite.mobigroup.ru/artifact/56df1be3c402d7d49c3a13be704a2ff22c3003d2 http://sqlite.mobigroup.ru/dir?name=ext/compress -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [FTS3] The Compress and Uncompress functions and extension
2011/7/22 Abhinav Upadhyay <er.abhinav.upadh...@gmail.com>: > Thanks for pointing out that mail archive discussion. I wasn't using > compress/uncompress because uncompress requires you to store the size > of the compressed buffer which is returned by the compress function > while compressing. But that email discussion suggests a nifty trick to > overcome this. And you can get the content size without decompression. It can be useful. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New madIS v1.3 release
Why JPack? May be Tcl lists will be more useful? The tcl dictionary
(also known as associative array) can be stored as list too.
SELECT TCLCMD('dict', 'get', 'key 1 mykey 2', 'mykey');
2
SELECT TCLCMD('lindex', 'key 1 mykey 2', 0);
key
SELECT TCLCMD('join', 'key 1 mykey 2', '-');
key-1-mykey-2
SELECT TCLCMD('lreplace', 'key 1 mykey 2', -1, -1, 'test');
test key 1 mykey 2
SELECT TCLCMD('lreplace', 'key 1 mykey 2', 'end', 'end', 'test');
key 1 mykey test
SELECT TCLCMD('linsert', 'a b', 0, 'c');
c a b
SELECT TCLCMD('linsert', 'a b', 'end', 'c');
a b c
SELECT TCLCMD('lsort', 'a c b');
a b c
SELECT TCLCMD('lsort', '-decreasing', 'a c b');
c b a
SELECT TCLCMD('lreverse', 'a c b');
b c a
SELECT TCLCMD('lsearch', 'a c b', 'b');
2
SELECT TCLCMD('lsearch', 'a c b', 'd');
-1
See details here: http://sqlite.mobigroup.ru/wiki?name=ext_tcl
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LevelDB benchmark
LevelDB use append log but SQLite is tested without WAL :) I check and some tests 2.5x faster with WAL. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LevelDB benchmark
Hm, I test I find index on PK field: CREATE TABLE test (key blob, value blob, PRIMARY KEY(key)) CREATE INDEX keyindex ON test (key) Epic fail, I think :D Default test on Intel(R) Atom(TM) CPU N450 @ 1.66GHz fillseq : 442.937 micros/op;0.2 MB/s fillseqsync :1678.168 micros/op;0.1 MB/s (1 ops) fillseqbatch : 73.016 micros/op;1.5 MB/s ... And with enabled WAL and synchronous=NORMAL and wal_autocheckpoint=4096 (LevelDB log size is 4Mb by default) and without index on PK field (!): fillseq : 139.190 micros/op;0.8 MB/s fillseqsync : 228.869 micros/op;0.5 MB/s (1 ops) fillseqbatch : 56.131 micros/op;2.0 MB/s ... -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LevelDB benchmark
There are the LevelDB sources and tests svn checkout http://leveldb.googlecode.com/svn/trunk/ leveldb-read-only Build SQLite test as make db_bench_sqlite3 And LevelDB test as make db_bench My patch for leveldb-read-only/doc/bench/db_bench_sqlite3.cc to disable redudant index and enable WAL is here: http://pastebin.com/dM2iqdvj And patch as above plus integer keys instead of blobs http://pastebin.com/CnBeChWg P.S. For blob-to-blob mapping we may use table with index on hashed key. Virtual table can simplify this. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LevelDB benchmark
With integer->blob mapping patch I get these results: $ ./db_bench_sqlite3 SQLite: version 3.7.7.1 Date: Fri Jul 29 05:32:05 2011 CPU:2 * Intel(R) Atom(TM) CPU N450 @ 1.66GHz CPUCache: 512 KB Keys: 16 bytes each Values: 100 bytes each Entries:100 RawSize:110.6 MB (estimated) fillseq : 77.394 micros/op;1.3 MB/s fillseqsync : 133.326 micros/op;0.7 MB/s (1 ops) fillseqbatch : 31.511 micros/op;3.1 MB/s fillrandom : 518.605 micros/op;0.2 MB/s fillrandsync : 227.374 micros/op;0.4 MB/s (1 ops) fillrandbatch : 411.859 micros/op;0.2 MB/s overwrite: 793.869 micros/op;0.1 MB/s overwritebatch : 743.661 micros/op;0.1 MB/s readrandom : 31.236 micros/op; readseq : 20.331 micros/op; fillrand100K :4872.027 micros/op; 19.6 MB/s (1000 ops) fillseq100K :7249.182 micros/op; 13.2 MB/s (1000 ops) readseq100K : 634.887 micros/op; readrand100K : 606.026 micros/op; $ ./db_bench LevelDB:version 1.2 Date: Fri Jul 29 11:20:59 2011 CPU:2 * Intel(R) Atom(TM) CPU N450 @ 1.66GHz CPUCache: 512 KB Keys: 16 bytes each Values: 100 bytes each (50 bytes after compression) Entries:100 RawSize:110.6 MB (estimated) FileSize: 62.9 MB (estimated) WARNING: Snappy compression is not enabled fillseq : 10.107 micros/op; 10.9 MB/s fillsync : 276.920 micros/op;0.4 MB/s (1000 ops) fillrandom : 21.275 micros/op;5.2 MB/s overwrite: 30.717 micros/op;3.6 MB/s readrandom : 48.781 micros/op; readrandom : 39.841 micros/op; readseq : 2.227 micros/op; 49.7 MB/s readreverse : 3.549 micros/op; 31.2 MB/s compact : 5274551.868 micros/op; readrandom : 35.392 micros/op; readseq : 1.743 micros/op; 63.5 MB/s readreverse : 2.927 micros/op; 37.8 MB/s fill100K :6631.138 micros/op; 14.4 MB/s (1000 ops) crc32c : 11.447 micros/op; 341.2 MB/s (4K per op) snappycomp : 8.106 micros/op; (snappy failure) snappyuncomp : 26.941 micros/op; (snappy failure) acquireload : 1.407 micros/op; (each op is 1000 loads) -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LevelDB benchmark
2011/7/29 Black, Michael (IS) <michael.bla...@ngc.com>: > What they don't say explicitly is that if all you need is key/value > capability then an SQL database is overkill and only slows you down (bit of a > duh factor there though not obvious to neophytes). The overhead by SQL layer is small. And are used prepared statements in test. As I see LevelDB use data integrity equal to SQLite WAL mode with disabled fsync. And LevelDB is limited only single-thread access. With the patched test SQLite is about 2x - 20x slower and it's absolutly normal I think. With dirrefent page_size we can make some tests faster. P.S. There is constant database created by DJB. And exists patch to drop "constant" limitation. IMHO it's functionally equal and better solution than LevelDB... -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LevelDB benchmark
2011/7/29 Black, Michael (IS) <michael.bla...@ngc.com>: > 2X-20X is hardly "small...overhead" in my world. > > Even 2X is the difference between 30 days and 15 days. One 16-computer blade > rack vs two racks ($200,000 vs $400,000). > > That's why google did this. Works for what they need and is lots cheaper. And single-thread without correct synchronous. Plus full in-memory copy of LevelDB log. And tests is adopted for specific scenarious (IMHO test of fixed value size is incorrect). I'm sure key-value database may be faster. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LevelDB benchmark
2011/7/30 Gabor Cselle <ga...@google.com>: > Just a heads-up that we just posted an update to the benchmarks: > - No more superfluous index on the primary key > - WAL turned on with auto-checkpointing every 4096 pages You may use "PRAGMA synchronous = NORMAL" instead of "PRAGMA synchronous = FULL" in WAL mode. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Will SQLite supports UnQL?
Very interesting annonce: http://www.couchbase.com/press-releases/unql-query-language -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
Multi-master replication can be easy with correct data structures. As example: CREATE TABLE test ( id INTEGER PRIMARY KEY ON CONFLICT REPLACE, cid INTEGER UNIQUE -- equal to test_record.id ); CREATE TABLE test_record ( id INTEGER NOT NULL, -- record version id rid INTEGER PRIMARY KEY, -- equal to test.id - permanent record id rev INTEGER, -- record revision number ... FOREIGN KEY(id) REFERENCES test ); CREATE VIEW view_test AS SELECT r.* FROM test as t, test_record as r WHERE t.cid=r.rid; For multimaster replication is needed copy all un-exists "test_record" rows from host A to host B and from B to A and then regenerate some rows in "test" tables. And Fossil is well-known example of SQLite database replication :D -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
2011/7/31 Roger Binns <rog...@rogerbinns.com>: > It takes more than a few lines of SQL. You've also got to be careful how > you write data (result is a DAG), you need replication code and you need > conflict resolution code. There are no conflicts! Two versions are two _different_ versions of the row. Simple copy _all_ un-exists rows by SQL query with attached database or simple script http://sqlite.mobigroup.ru/wiki?name=sqlite3-rdiff It can be included into custom sqlite3 shell :) On application level we may show the last version of each record (and full history of record). And user can view and revert any changes. I think easy replication is not feature of NoSQL solutions. With big disks we can create and use append-only datasets in SQL DBMS. And virtual table can incapsulate all details. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
2011/7/31 Roger Binns <rog...@rogerbinns.com>: > My original point was that CouchDB has multi-master replication builtin - > you do not have to write extra code or do anything special. and > You can > programmatically find conflicts and then it is up to you how to resolve them > since only your code knows the semantics of the data. You write extra code to find and resolve CouchDB replication conflicts but you are speak about "do not have to write extra code". One of these approvals is false! -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
2011/7/31 Simon Slavin <slav...@bigfraud.org>: > Right. So if a customer orders one of my products they'll be presented with > two different costs and pick the one they like. Great system. You customers have edit privilegies to you prices?! It's not the replication problem of cource. As example the name in your passport can be differ to others documents. But you passport is valid. The price in the shop now and tomorrow can be different. And prices in dirrefent shops are not equal. Do you going to shops or you frustrated by these conflicts?.. Your applications may be ready to work with real data. There are a lot of causes of _potential_ conflicts and replication is only one of these. Replication is aggregation process but is not "silver bullet" for extracting correct data. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
Roger, thanks for good explanation and examples of the multi-master replication problems. But I think a conflict-resolutions strategies may be independent. And checking logical data integrity is needed for systems without replication too. This checking is not a part of the replication but it's part of data processing. Of cource you can start replication and checking in single SQLite transaction. A normalized database can't be easy replicated and checked. The problem is not in SQLite. CouchDB has simple replication as result of simple database schema. You can create simple schema of SQLIte database and will have simple replication. Some complex data structures can be serialized (I use Tcl structures and you can use JSON) and stored in simple SQLite database scheme. But there is the problem with indexing serialized data... I use FTS3 extension as index of Tcl structures. And I want to know how UnQL can resolve this problem. > SQLite has no library level language other than very limited SQL. It's trivial to add Tcl language to SQLite as example: http://sqlite.mobigroup.ru/wiki?name=ext_tcl And we can add javascript support for JSON data processing. Are you really want it? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
2011/8/1 Black, Michael (IS) <michael.bla...@ngc.com>: > This is a side-question to this thread...but has anybody every done row-level > locking for edit? What problem are you solving? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
2011/8/1 Simon Slavin <slav...@bigfraud.org>: > > On 1 Aug 2011, at 6:56pm, Alexey Pechnikov wrote: > >> 2011/8/1 Black, Michael (IS) <michael.bla...@ngc.com>: >>> This is a side-question to this thread...but has anybody every done >>> row-level locking for edit? >> >> What problem are you solving? > > Please stop asking key questions. I don't understand the problem. IMHO Redis+SQLite is quick way to do it. Use Redis key for locking and incremented key as unique id generator. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
2011/8/1 Simon Slavin <slav...@bigfraud.org>: > I'm sorry Alexey, I was trying to be funny and failed. Your question is very > important for this situation. Oh, I'm sorry! My english is bad by night :) -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
2011/8/2 Eric Scouten <e...@scouten.com>: > It falls apart badly in a highly distributed environment where ... > > ... May be a RDF storage is more reasonable for this. Operations with atomic facts can be highly distributed. And SPARQL is similar to SQL. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need Help! -- SQlite database on server
2011/8/4 Vinoth raj <vinoth@gmail.com>: > So, the requirement is to save sqlite database on a server from a C++ > application. There are a lot of ways to copy file to server. SQLite database is single file. And you can send SQL dump. And you can send diff of SQL dump. And you can export your data and send RDF or other data format. I don't see your problem. May be you want any different?.. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Example of implementing undo/redo using sqlite3?
2011/8/4 Stephan Beal <sgb...@googlemail.com>: > i'm wondering if anyone can point me to an example of implementing such a > beast? http://sqlite.mobigroup.ru/artifact/265e408b4352d66cfc79a9990cb2c22fb390d3b6 http://sqlite.mobigroup.ru/artifact/2250bbbc83f80eff73ce003ab7a30293c688ae9b P.S. Many of the design ideas were cribbed from: http://www.sqlite.org/cvstrac/wiki?p=UndoRedo -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature request: extend the IN operator
Is it possible to support construction like to where x IN "1 2 3" There are a lot of situations when is very useful to store list of identifiers in table field. I know about the intarray virtual table but it's need constructor/destructor calls (and produce segfaults with the autorizer function). Does somebody interesting in this functionality too? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: extend the IN operator
2011/8/7 Simon Slavin <slav...@bigfraud.org>:
> For example,
>
> SELECT * FROM cars WHERE cars.name IN ('Corolla', 'Fiesta')
create table t (names TEXT);
insert into t(name) values ('Corolla Fiesta');
SELECT * FROM cars WHERE cars.name IN (select names from t where rowid=1);
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: extend the IN operator
2011/8/7 Stephan Beal <sgb...@googlemail.com>:
> Also keep in mind that sqlite3 tries, to a large degree, to be compatible
> with ANSI SQL, and (IN "A B C"), in the form you describe, is not
> ANSI-specified.
Yes, but in PostgreSQL (as example) we can create user-defined
function returns table
from the list. SQLite can't do it and is impossible to use user extension too.
P.S. Example for PostgreSQL:
select list2items('a b c');
CREATE OR REPLACE FUNCTION public.list2items(in_list text)
RETURNS SETOF text AS
$BODY$
SELECT lindex($1, s) FROM generate_series(0,llength($1)-1) AS s;
$BODY$
LANGUAGE 'sql' IMMUTABLE;
CREATE OR REPLACE FUNCTION public.lindex(list text, index int4)
RETURNS text AS
$BODY$
return [lindex $1 $2]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
CREATE OR REPLACE FUNCTION public.llength(list text)
RETURNS int4 AS
$BODY$
return [llength $1]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: extend the IN operator
2011/8/7 Simon Slavin <slav...@bigfraud.org>:
> You don't need to. The SQLite expressions I listed tell you how to achieve
> the result without doing that.
Really? And how can you perform the query like to:
sqlite> create table t1(ids text);
sqlite> insert into t1 (ids) values ('1 2 3');
sqlite> insert into t1 (ids) values ('2 3 4');
sqlite> insert into t1 (ids) values ('3 4 5');
sqlite> create table t2(name text);
sqlite> insert into t2 (name) values ('name1');
sqlite> insert into t2 (name) values ('name2');
sqlite> insert into t2 (name) values ('name3');
sqlite> insert into t2 (name) values ('name4');
sqlite> insert into t2 (name) values ('name5');
sqlite> select * from t2 where rowid in (select ids from t1 where rowid=2);
A simple calculation: if each list of identifiers have about 1000
items and there are
1 000 000 lists than the table of relations (t1.rowid, t2.rowid) will
have 1 000 000 000
rows! It's too slow and is not useful in real world. Of cource all
systems store lists of
identifiers in similar situations.
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: extend the IN operator
Igor, how about simple test? Normalized database is big and slow.
$ time ./test_norm_idx.tcl
real32m54.978s
user32m14.885s
sys 0m39.842s
$ time ./test_idx.tcl
real7m19.005s
user6m55.226s
sys 0m11.717s
$ ls -lh *db
2,1G test_idx.db
7,1G test_norm_idx.db
test_idx.tcl
=
#!/usr/bin/tclsh8.5
package require sqlite3
sqlite3 db [string map {.tcl .db} $argv0]
db eval {PRAGMA page_size=8192}
db eval {
create table parent(dt DATETIME, user_id INTEGER);
create index parent_dt_idx on parent(dt);
create index parent_user_id_idx on parent(user_id);
create virtual table parent_fts using fts4(childs TEXT);}
db transaction {
for {set i 1} {$i<=10} {incr i} {
set time [clock microseconds]
set childs ""
for {set j [expr {$i*1000}]} {$j<=[expr {$i*1000+1000}]} {incr j} {
lappend childs $j
}
db eval {insert into parent(dt, user_id) values ($time, 1)}
db eval {insert into parent_fts(childs) values ($childs)}
}
}
test_norm_idx.tcl
=
#!/usr/bin/tclsh8.5
package require sqlite3
# test normalized
sqlite3 db [string map {.tcl .db} $argv0]
db eval {PRAGMA page_size=8192}
db eval {create table link(dt DATETIME, user_id INTEGER, parent_id
INTEGER, child_id INTEGER);
create index link_child_id_idx on link(child_id);
create index link_dt_idx on link(dt);
create index link_user_id_idx on link(user_id);}
db transaction {
for {set i 1} {$i<=1} {incr i} {
set time [clock microseconds]
db eval {insert into link(dt, user_id, parent_id, child_id)
values ($time, 1, $i%1000, $i)}
}
}
P.S. With versioning of all records we need some additional fields and
normalized database
is very big and very slow.
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: extend the IN operator
2011/8/8 Simon Slavin <slav...@bigfraud.org>: > You're quite right. If someone was going to write that functionality into a > SQLite app in real life they'd probably use 'LIKE' or 'GLOB' and store all > the possibilities in one row. FTS3/FTS4 is better as index for lists. See my test script for Igor. We can fast search any id by using "match" operator on FTS table. P.S. FTS table has nice scalability. Check insertion a lot of records and insertion speed is constant. I did try 400 millions of records (and did get database size > 100 Gb). -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read only scaling optimization
2011/8/10 Wiktor Adamski <bardzotajneko...@interia.pl>: > You > may try increasing page size - bigger block means less near-random > reads from the disc. It's good way. With page size 8k instead of default 1k selects performance may increasing ~3x. Note: PostgreSQL use 8k disk pages. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite with 10M record
2011/8/11 Sumit Gupta <gamersu...@gmail.com>: > 10-10-2011 18:48:42, 10,20, 30, 40, 50, 60, 80 .. <48 such entries> Use unixtime (time in seconds) and index on this field and your queries will be fast. You can use single blob as integers array for more compact database. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read only scaling optimization
2011/8/12 Pavel Ivanov <paiva...@gmail.com>: > It's a little surprising to me that with all the same conditions 2 > files residing on the same drive have better performance than the same > files residing on different drives. Theoretically that shouldn't > happen. Yes, it's not right behaviour. Is needed the sources of the test programm. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine tuning of Sqlite
2011/9/2 Simon Slavin <slav...@bigfraud.org>: > > On 2 Sep 2011, at 7:39am, jerome moliere wrote: > >> I must give some real clues to my customers to fine tune Sqlite > > 1) What problem(s) are you trying to solve ? > > 2) Must your system be set up for multi-process (or multi-user) access, or > can we ignore all problems concerning those ? > > 3) Are you using transactions to batch together data changes which relate to > one-another ? > > 4) If speed is a problem, do you have indexes defined appropriate to your > commands ? Really, these are nice questions! Jerome, different environments may have different pragmas... we don't know about your environment enough. You may show some problematic tests as example. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine tuning of Sqlite
Jerome, can you show your database structure and queries? Which FS is used? Did you set corresponding pragmas page_size, cache_size, journal_mode? Indicies can be very slow... may be the FTS4 table will be much faster for your queries. Do you use a prepared queries?.. Which type of storage are you use? Can you group a set of inserts into single transaction? Did you try to use in-memory database as temp storage and copy set of records into main database in single transaction? There are a lot of questions without answers. 2011/9/6 jerome moliere <jerome.moli...@gmail.com>: > Hi Alexey, > I thought I answered to those questions but once again : > * my application runs on an embedded device (Honeywell 9900) , it's a > very complex application (too much I think) > and we are facing performance problems while inserting datas (about > 500 objects to be inserted inducing about 2000 queries and the same > amount of transactions because the application runs with NON EXCLUSIVE > flag, using serializable default isolation level). As any application > running on an embedded device it 's not well suited for multi users!!! > The application stores data for ONE employee of the company (our > customers)... > We have indexes (too much I think) but as far as I know in our case > indexing is one performance problem rather than a solution (indexing > in an INSERT phase adds overheads..) > > But now my initial problem is solved , it was a problem with Xerial > JDBC classes , sqlite manages well group of PRAGMA (where Xerial > SQLiteConfig objects don't) > > I'd like to use this post to thank you because I did not see such a > good group for a while , with people reacting quickly and with a lot > of value added comments... > So thank you guys ,keep this very special Open Source mind alive for a while > > > I just post an entry on my blogger.com page.. > For those interested : > http://romjethoughts.blogspot.com/2011/09/sqlite-performance-tuning.html > > > kind regards > Jerome > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine tuning of Sqlite
2011/9/6 jerome moliere <jerome.moli...@gmail.com>: > Did you set corresponding pragmas >> page_size, cache_size, journal_mode? > page_size yes > cache_size no because we are using a higher level cache (in Java) > journal_mode is in the list of the pragmas to be tested through the > benchmark setting this parameter to OFF speeds up transactions for > around 20% I think your cache size in smaller than the modified indicies size. Use more big cache or reduce indicies size. > Indicies can be very >> slow... may be the FTS4 table will be much faster for your queries. > > can you give more details about this concept ? what is the FTS4 table > ? I am sorry don't know what is is !!! See http://www.sqlite.org/fts3.html The full-text index is very fast and scalable. You can use it instead of a lot of btree-indicies when you can rewrite your search queries as FTS. >> Did you try to use in-memory database as temp storage and >> copy set of records into main database in single transaction? > > it's one of the goal of the benchmark but this solution has av ery > high level of database corruption isn't it ..so I prefer to keep it as > a joker... Database corruption can't be the result of the coping from temp in-memory database to main disk-database! -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine tuning of Sqlite
2011/9/6 jerome moliere <jerome.moli...@gmail.com>: > Could you give me more details about corruption cases ? > Is there a list of contexts where we can get corrupted tables ? http://www.sqlite.org/howtocorrupt.html -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does coalesce terminate early?
Richard, is the future documented anywhere? 2011/9/15 Richard Hipp <d...@sqlite.org>: > On Thu, Sep 15, 2011 at 8:08 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > >> Richard Hipp <d...@sqlite.org> wrote: >> > On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton < >> scarle...@miltonstreet.com>wrote: >> > >> >> Forgive me, fore I have forgotten the term used to describe the behavior >> if >> >> a C if statement where it stops executing on the first false statement, >> >> but... Does coalesce do that? >> >> >> > >> > "Short-circuit evaluation" is the usual term applied to this kind of >> thing, >> > and yes, COALESCE() does short-circuit evaluation. If you say >> > "coalesce(A,B)" and A is not NULL than B is never evaluated, which can be >> a >> > significant performance win if, for example, B is a complex subquery. >> >> When did this start, with what SQLite version? > > > 3.6.21 - December 2009 > > >> I must admit I'm somewhat behind (using 3.6.X for some X I don't recall at >> the moment), but in the version I use, in expression coalesce(someField, >> customFunction()) I definitely see customFunction() called even when >> someField is not null. >> -- >> Igor Tandetnik >> >> ___ >> 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 > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Internal v. External BLOBs
There is a problem with access to file in directory with big amount of files. FS directory indicies are not really good. I did test 100 millions of 1k files in SQLite and results were better than reading from set of directories in FS. But for files about 1 Mb and more the SQLIte performance is not good. Is any reason why SQLite big blobs reading may be slowly? The performance of the BLOBS may limit performance of FTS and other custom storage/index realizations (Spatialite, etc). And it's more important I think. Especially when we need FTS index as fast hash index. 2011/9/21 Richard Hipp <d...@sqlite.org>: > If you are storing large BLOBs in SQLite, can you read them faster if they > are stored directly in the database file, or can you get to them quicker if > you store just a filename in the database and read the BLOB content from a > separate file? > > We did some experiments to try to answer this question, and the results > seemed interesting enough to share with the community at large. Bottom > line: On Linux workstations, it is faster to store BLOBs in the database if > they are less than about 100KB in size, and faster to store them in a > separate file if they are larger than about 100KB. This is on Ubuntu with > EXT4 and a fast SATA disk - your mileage may vary with different operating > systems, filesystems, and hardware. > > The complete report is here: > http://www.sqlite.org/intern-v-extern-blob.html > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems building/running SQLite test.exe with ICU enabled
Yes, on linux host I have a lot of problems in SQLite tests with ICU enabled. 2011/9/30 Marc Henrickson <marc.henrick...@wtsparadigm.com>: > I have downloaded the SQLite source code (sqlite-netFx-source-1.0.74.0) as > well as the latest ICU libraries (icu4c-4_8-Win32-msvc10). I then build the > SQLite projects and run the test.2008 application and it runs fine when > SQLITE_ENABLE_ICU is undefined. However, when I do define SQLITE_ENABLE_ICU, > I get the following exception when I click "Run" on the test.exe application. > > System.Reflection.TargetInvocationException: Exception has been thrown by the > target of an invocation. ---> System.TypeInitializationException: The type > initializer for 'System.Data.SQLite.SQLiteFactory' threw an exception. ---> > System.DllNotFoundException: Unable to load DLL 'SQLite.Interop.DLL': The > specified module could not be found. (Exception from HRESULT: 0x8007007E) > at System.Data.SQLite.UnsafeNativeMethods.sqlite3_config(Int32 op, > SQLiteLogCallback func, IntPtr pvUser) > at System.Data.SQLite.SQLite3.SetLogCallback(SQLiteLogCallback func) in > c:\SQLiteTest\System.Data.SQLite\SQLite3.cs:line 935 > at System.Data.SQLite.SQLiteFactory..ctor() in > c:\SQLiteTest\System.Data.SQLite\SQLiteFactory.cs:line 121 > at System.Data.SQLite.SQLiteFactory..cctor() in > c:\SQLiteTest\System.Data.SQLite\SQLiteFactory.cs:line 131 > > I am building and running the application with Visual Studio 2008 on a > Windows 7 64-bit machine. > SQLite.Interop.2008 is set to the Win32 configuration. > Both test.2008 and System.Data.SQLite.2008 projects are set to x86 > configuration. > > I have updated the SQLite.Interop.2008 project to build the > SQLite.Interop.DLL and both the Interop.DLL and the System.Data.SQLite.DLL is > included in the test.exe folder. I have also updated the SQLite.Interop.2008 > project to include the proper ICU library files. The project builds without > error and the test.exe will open up the main window. I get the exception > when clicking "Run". When I remove the SQLITE_ENABLE_ICU definition from the > SQLite.Interop.2008 project, it will build, and run as expected without error. > > Has anyone seen this before, and have a solution? I am happy to provide more > details if needed. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS vs INDEX
FTS use index multi-tree and de-facto has _no_ insert speed degradation. I did do test for 400+ millions of records. With b-tree index there is insert speed degradation: http://geomapx.blogspot.com/2010/04/sqlite-index-degradation-tests.html http://geomapx.blogspot.com/search?q=index+speed So FTS as hash-index is nice. 2011/10/19 Fabian <fabianpi...@gmail.com>: > Did anyone do some benchmarks how the insert-speed of FTS compares to a TEXT > INDEX column? I don't need many of the extra features of FTS, because I > always need to look up rows by prefix or exact match, and both can be > implemented efficiently via TEXT INDEX too. But if the overhead is > comparable, I'd rather use FTS. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS vs INDEX
2011/10/19 Fabian <fabianpi...@gmail.com>: > Thanks, that's good to hear! It makes me wonder why SQLite doesn't use that > same multi-tree mechanism for regular indexes, but that's a whole different > question. It's impossible with SQLite3 database format. May be SQLite4 will be support it :) -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS vs INDEX
2011/10/19 Fabian <fabianpi...@gmail.com>:
> I always do inserts in batches of 100.000 rows, and after each batch I
> manually merge the b-trees using:
>
> INSERT INTO table(table) VALUES('optimize');
>
> Is there a possibility that it will do automatic maintenance half-way during
> a batch? Or will it always wait untill the transaction is finished?
I think you are victim of the premature optimization :)
See documentation:
"several different b-trees that are incrementally merged as rows are inserted,
updated and deleted. This technique improves performance when writing to an
FTS table, but causes some overhead for full-text queries that use the index."
So you can work with a big FTS tables without using the "optimize" method.
I use some FTS tables with tens of millions records and effect of the
"optimize"
isn't measurable.
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual Tables and tcl / apsw
2011/10/25 Dan Kennedy <danielk1...@gmail.com>: > Not possible. The Tcl interface has no bindings for either the > virtual table or VFS interfaces. But why? Is there any technical/ideological problems? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 200 lines of fun sqlite3 code
iple(m.op->stmt,*(TRIPLE *)p);
> }
> if(m.status == SQLITE_OK) {
> do {
>
> if(m.op->stmt)
> m.status = sqlite3_step(m.op->stmt );
> m.status = ghandler(m.top);
> if(m.status == G_RESTART) return;
> } while( m.status == SQLITE_ROW || (m.status == SQLITE_OK) );
> if(m.op->stmt)
> m.status = sqlite3_reset(m.op->stmt);
> }
> else gerror("bind \n");
> }
> void init_gbase() {
> m.status = sqlite3_open(GBASE,);
> debug("Gbase");
> m.status = sqlite3_create_function_v2(m.db,GFUN,2,SQLITE_UTF8
> ,0,gfunction,0,0,
> NULL);
> m.status = sqlite3_prepare_v2(m.db,POP_SQL, strlen(POP_SQL),
> [G_POP].stmt,0);
> m.status = sqlite3_prepare_v2(m.db,TEST_SQL, strlen(TEST_SQL),
> [G_TEST].stmt,0);
>
> }
>
> int _tmain(int argc, _TCHAR* argv[])
> {
> init_dll();
> init_gbase();
> m.top=POP_TRIPLE;
> for(;;) {
> m.status = SQLITE_OK;
> m.called = POP_TRIPLE;
> if(m.status == G_RESTART)
> m.self_rowid=0;
> read_triples();}
> }
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fts4 table + triggers
Use view + triggers on view. Triggers on view can modify FTS4 table and any other. 2011/11/22 Ephraim Stevens <ephraim.stev...@gmail.com>: > Greetings all, > > It is well known that you cannot create triggers against virtual tables and > fts4 tables are a form of virtual table. > > Has anyone developed a work around method for this or simulating the end > effect? > > I have a full text search table which is comprised of joining and selecting > from some underlying tables. I need to synch this full text search table > with underlying tables upon insert or update (of the full text search > table). > > A trigger would be ideal here but you can't use them against virtual > tables. I'm sure someone has run into this same dillema. Thanks for any > suggestions. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite support for geometric column types?
2011/11/26 Peter Aronson <pbaron...@att.net>: > Not directly. But Alessandro Furieri has created a version of SQLite with > OGC conforming geometry columns, called SpatiaLite. It can be found here: > > http://www.gaia-gis.it/spatialite/ > > It's based on an older version of SQLite (3.6.16, but a beta version > supports 3.7.3), and it uses a more restrictive license (not hard, since any > license is more restrictive than SQLite's non-license!) It can be used as extensions set for any new SQLite version. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value
The problem can be fixed by variables bindings patch: http://sqlite.mobigroup.ru/wiki?name=tclsqlite I think, you can do same for you lang. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value
I think there are some problems with dynamic datatypes in sqlite. This may be redesigned in SQLite4 but not in SQLite3. P.S. Datatypes recognized differently after Shift-Insert SQLite3 commands into Tcl shell. This is similar to problem with datatypes in triggers. I did report bug but for backward compability problem wil not be resolved by upstream. So I did write patch for myself. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value
Of cource you may report your bug! But I'm not sure about possibility of the bugfix in upstream and so I speak about patch to SQLite binding for your language. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite lets selecting a column that is not inthe Group by clause
2010/6/11 Igor Tandetnik <itandet...@mvps.org>: > Well, many SQL engines, as well as SQL-92 standard, do prohibit such a > syntax. According to the standard, in a statement using GROUP BY, any column > reference that appears in SELECT clause must also appear in GROUP BY clause > or be part of an argument of an aggregate function. SQLite allows "naked" > non-grouped columns as an extension (which is occasionally useful). This is needed as replacement of the "distinct on" clause. I don't know other way to translate many queries from PostgreSQL to SQLite. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite lets selecting a column that is not inthe Group by clause
2010/6/11 Igor Tandetnik <itandet...@mvps.org>: > SQLite allows "naked" non-grouped columns as an extension (which is > occasionally useful). And this is needed as replacement of the "distinct on" clause. I don't know other way to translate many queries from PostgreSQL to SQLite. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite lets selecting a column that is not inthe Group by clause
And this is needed as replacement of the "distinct on" clause. I don't know other way to translate many queries from PostgreSQL to SQLite. 2010/6/11 Igor Tandetnik <itandet...@mvps.org>: > SQLite allows "naked" non-grouped columns as an extension (which is > occasionally useful). -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] where to find an examples of writing a extension?
See SQLite source tree for upstream extensions. Or, as example, the unofficial repository http://sqlite.mobigroup.ru/dir?ci=a3386b467d6ffd67=ext 2010/6/20 Sam Carleton <scarle...@miltonstreet.com>: > Where might I find some examples of creating user defined functions in SQLite? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert Using Tcl Array?
You can use simple wrapper like as
proc ::dataset::update {table fields} {
array set info $fields
foreach {key value} $fields {
if {$key eq {id}} continue
if {$key eq {*}} continue
lappend sql_pairs $key=:info($key)
}
set sql_pairs [join $sql_pairs ,]
set query "update $table set $sql_pairs where id=$info(id)"
db eval $query
}
2010/5/30 Ross Hayden <r...@nsnull.com>:
> If SQLite can return to me a Tcl array from SELECT, why does the feature not
> exist to INSERT, UPDATE, or DELETE using a properly formed Tcl array as an
> argument?
>
> Seems nice to have, but perhaps I'm not aware of possible dangers in such a
> feature.
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create virtual table if not exists table_id???
Thanks a lot for this link! 2010/5/12 Roger Binns <rog...@rogerbinns.com> > > > http://www.sqlite.org/cvstrac/tktview?tn=2604 > > To fix it requires code changes to SQLite and the SQLite team haven't > deemed > this necessary (yet). > > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Slow FTS3 query for time strings (like to "19:10:59")
sqlite> .schema role CREATE VIRTUAL TABLE role USING fts3(uuid TEXT, uid INTEGER, dt TEXT, ts INTEGER, fields, tasks, TOKENIZE icu ru_RU); Fast queries: sqlite> select count(*) from role where role match '1278429059'; count(*) 471 CPU Time: user 0.00 sys 0.00 sqlite> select count(*) from role where role match '19 10 59'; count(*) 476 CPU Time: user 0.584037 sys 0.00 Slow queries: sqlite> select count(*) from role where role match '"19:10:59"'; count(*) 471 CPU Time: user 3.500219 sys 0.080005 sqlite> select count(*) from role where role match '19:10:59'; count(*) 476 CPU Time: user 1.888118 sys 0.036002 Why these are too slow and how last queries are interpretated by FTS3? Why results are different? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL journal size grow unlimited
$ ls -lh -rw-r--r-- 1 veter veter 700M Июл 7 00:55 test.db -rw-r--r-- 1 veter veter 192K Июл 7 00:55 test.db-shm -rw-r--r-- 1 veter veter 186M Июл 7 00:55 test.db-wal -rwxr-xr-x 1 veter veter 3,0K Июл 7 00:47 test.tcl $ sqlite3 test.db 'pragma page_size' 8192 $ sqlite3 test.db 'PRAGMA wal_autocheckpoint' 1000 -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL journal size grow unlimited
There is only single process: $ fuser test.db test.db: 3326 $ ps aux|grep 3326|grep -v grep veter 3326 55.9 3.3 309892 278832 pts/1 Dl+ 01:19 17:11 /usr/bin/tclsh8.5 ./test.tcl And I see now: $ ls -lh итого 3,9G -rw-r--r-- 1 veter veter 3,1G Июл 7 01:50 test.db -rw-r--r-- 1 veter veter 896K Июл 7 01:51 test.db-shm -rw-r--r-- 1 veter veter 880M Июл 7 01:51 test.db-wal -rwxr-xr-x 1 veter veter 3,0K Июл 7 01:19 test.tcl My test script http://mobigroup.ru/files/test.tcl -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL journal size grow unlimited
Oh, yes, check null size for new database is incorrect in WAL mode. I
replace this script by more simple:
==
http://mobigroup.ru/files/test.tcl
#!/usr/bin/tclsh8.5
package require sqlite3
sqlite3 db grow.db
db eval {pragma cache_size=32000}
db eval {pragma journal_mode=WAL}
db eval {PRAGMA synchronous=normal}
db eval {CREATE VIRTUAL TABLE role USING fts3(id INTEGER PRIMARY KEY, uuid
TEXT);}
for {set j 1} {$j<=200} {incr j} {
puts $j
db transaction {
for {set i 0} {$i<10} {incr i} {
db eval {insert into role (uuid) values (hex(randomblob(16)))}
}
}
}
db close
==
After step between 40 and 45 WAL journal starts grow:
$ ls -lh|grep grow
-rw-r--r-- 1 veter veter 358M Июл 7 07:09 grow.db
-rw-r--r-- 1 veter veter 32K Июл 7 07:08 grow.db-shm
-rw-r--r-- 1 veter veter 23M Июл 7 07:09 grow.db-wal
$ ls -lh|grep grow
-rw-r--r-- 1 veter veter 358M Июл 7 07:09 grow.db
-rw-r--r-- 1 veter veter 32K Июл 7 07:08 grow.db-shm
-rw-r--r-- 1 veter veter 105M Июл 7 07:09 grow.db-wal
$ ls -lh|grep grow
-rw-r--r-- 1 veter veter 374M Июл 7 07:09 grow.db
-rw-r--r-- 1 veter veter 256K Июл 7 07:09 grow.db-shm
-rw-r--r-- 1 veter veter 228M Июл 7 07:09 grow.db-wal
There is only single process:
$ ls -lh|grep grow
-rw-r--r-- 1 veter veter 862M Июл 7 07:11 grow.db
-rw-r--r-- 1 veter veter 256K Июл 7 07:10 grow.db-shm
-rw-r--r-- 1 veter veter 254M Июл 7 07:11 grow.db-wal
$ fuser grow.db
grow.db: 16190
$ ps aux|grep 16190|grep -v grep
veter16190 84.8 3.2 304372 273136 pts/1 Dl+ 07:07 3:48
/usr/bin/tclsh8.5 ./test.tcl
$ ls -lh|grep grow
-rw-r--r-- 1 veter veter 862M Июл 7 07:11 grow.db
-rw-r--r-- 1 veter veter 256K Июл 7 07:10 grow.db-shm
-rw-r--r-- 1 veter veter 254M Июл 7 07:11 grow.db-wal
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 snippet problem: function returns full document content
For some documents snippet function returns full document content: sqlite> select length(snippet(file_text)) from file_text where file_text match 'переоформление договора' and rowid=1015; 42312 sqlite> select substr(snippet(file_text),1,500) from file_text where file_text match 'переоформление договора' and rowid=1015; ..., ЗК, АР). Информацию о ━━━ Главная\ Обучаемся!\ Бланки заявлений ━━━ Заполнение регистрационной формы клиента (РФА) [blanki_clip_image002] • Цифры в скобках, например, (1) – это сноски и указывать на РФА их не нужно. • Все данные вносятся разборчиво, печатными буквами. • РФА – это документ, дописывать и исправлять на -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL journal size grow unlimited
Thanks, the problem is fixed in trunk for my tests. 7 июля 2010 г. 19:03 пользователь Richard Hipp <d...@sqlite.org> написал: > > This problem should be fixed in the latest snapshot available at > http://www.sqlite.org/draft/download.html (or from Alexey's fossil > mirror.) > > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL questions
See http://sqlite.org/draft/wal.html : <http://sqlite.org/draft/wal.html>"An SQLite database _connection_ defaults to journal_mode=DELETE. To convert to WAL mode, use the following pragma: PRAGMA journal_mode=WAL;" -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem to switch between WAL and delete journal modes
Some times database can return journal_mode=delete when exists WAL journal: $ ls|grep grow grow.db grow.db-shm grow.db-wal $ sqlite3 SQLite version 3.7.0 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> pragma journal_mode; delete sqlite> .q $ ls|grep grow grow.db grow.db-shm grow.db-wal $ sqlite3 grow.db 'pragma journal_mode' delete $ ls|grep grow grow.db -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL questions
I see this too: $ sqlite3 grow.db 'pragma journal_mode' delete $ hexdump -s 17 -n 2 grow.db | head -n1 011 0200 $ sqlite3 grow.db 'pragma journal_mode=delete' delete $ hexdump -s 17 -n 2 grow.db | head -n1 011 0100 -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL questions
> > Remove the WAL mode from "PRAGMA journal_mode". The journal_mode pragma > only specifies the various rollback journal modes. Enable the WAL using a > separate pragma such as "PRAGMA wal=ON; PRAGMA wal=OFF;" > It's more clean I think. With wal=on and journal_mode=delete SQLite may delete WAL journal before the last connection is closed and so provide backward compability (of cource, only new SQLite versions can restore the crashed databases). With wal=on and journal_mode=persist SQLite may use persistent WAL journal without backward compability reasons. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL questions
2010/7/8 Richard Hipp <d...@sqlite.org> > In the current implementation, if you call "PRAGMA wal_checkpoint" just > prior to closing the database, the WAL file will be deleted automatically. > But it keeps the database in WAL mode, so the WAL is recreated the next > time > you open and write to the database. It's not helpful for backward compability. How about version downgrade of the Android or some other mobile OS and as result impossibility to open any SQLite database?.. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL questions
2010/7/8 Jay A. Kreibich <j...@kreibi.ch> > > It's not helpful for backward compability. How about version downgrade of > > the Android or some other mobile OS and as result impossibility to open > any > > SQLite database?.. > > That's not backwards compatibility (newer versions working with items > from older environments), that's forwards compatibility (older versions > working new items from a newer environment). > I did speak about "Backwards Compatibility" chapter from http://www.sqlite.org/draft/wal.html It is no different than a new(er) application that uses newer APIs > not working on an older version of the OS. And how many such changes in POSIX file API?.. I think SQLite API as applications file format is similar to POSIX file API. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL: Wrong error "database disk image is malformed"
I have long running test in WAL mode (inserting millions of records in autocommit mode). In other connection by sqlite3 shell I see these errors: sqlite> select (select count(*) from role_exist)/1000/1000; 12 sqlite> select (select count(*) from role_exist)/1000/1000; Error: file is encrypted or is not a database sqlite> select (select count(*) from role_exist)/1000/1000; 12 The database is correct: sqlite> pragma integrity_check; ok And test continue works: sqlite> select (select count(*) from role_exist)/1000/1000; Error: database disk image is malformed sqlite> select (select count(*) from role_exist)/1000/1000; Error: database disk image is malformed sqlite> select (select count(*) from role_exist)/1000/1000; Error: database disk image is malformed sqlite> select (select count(*) from role_exist)/1000/1000; 13 -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for sqlite shell
Maxim, please show example here and the link to your implementation. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
