Re: [sqlite] SQLite version 3.6.14
Hello! On Thursday 07 May 2009 06:36:50 D. Richard Hipp wrote: > See http://www.sqlite.org/asyncvfs.html for > additional information. Can you add some diagramms of async I/O database usage? And usage scenarios may be very useful. Now I'm don't undertand when can applications to get help from 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
[sqlite] SQLite counters by "key" and "tempkey" extensions
Hello!
There is simple example for "tempkey" extension (Public Domain license):
create table colors(name text);
insert into colors values ('Red');
insert into colors values ('Green');
insert into colors values ('Blue');
select tempkey_install(); -- temp table "tempkeys" wiil be created
select tempkey_increment('','',1) as counter, name from colors;
1|Red
2|Green
3|Blue
select tempkey_get('',''); -- current values for key name '' in key group name
''
3
--select tempkey_delete('',''); -- delete key name '' in key group name ''
select tempkey_uninstall(); -- delete all keys
The "key" extension is similar but is operate with non-temp table "keys".
Test script for module "key":
select 'ERROR' where key_install() is not null; --Create "keys" table.
select 'ERROR' where key_exists('common','nds%')!=0; --Check is param exists
select 'ERROR' where key_get('common','nds%') is not null; --Get _non-existing_
value _without_ default_value
select 'ERROR' where key_get('common','nds%',18)!='18'; --Get _non-existing_
value _with_ default_value
select 'ERROR' where key_set('common','nds%',10)!='10'; --Set value
select 'ERROR' where key_get('common','nds%')!='10'; --Get _existing_ value
select 'ERROR' where key_add('common','nds%',18) is not null; --Add _existing_
key
select 'ERROR' where key_replace('common','nds%',20)!='20'; --Replace
_existing_ key
select 'ERROR' where key_delete('common','nds%')!=1; --Delete existing key
select 'ERROR' where key_delete('common','nds%') is not null; --Delete
non-existing key
select 'ERROR' where key_delete('common') is not null; --Delete all keys in unit
select 'ERROR' where key_replace('common','nds%',20) is not null; --Replace
_non-existing_ key
select 'ERROR' where key_add('common','nds%',18)!='18'; --Add _non-existing_ key
select 'ERROR' where key_increment('common','num',10)!='10'; --Increment
_non-existing_ value
select 'ERROR' where key_increment('common','num',10)!='20'; --Increment
_existing_ value
select 'ERROR' where key_prepend('common','string','prepend-')!='prepend-';
--Prepend value
select 'ERROR' where
key_append('common','string','-append')!='prepend--append'; --Append value
select 'ERROR' where key_flush('common','string')!=1; --Flush existing key
select 'ERROR' where key_delete('common','string') is not null; --Flush
non-existing key
select 'ERROR' where key_uninstall() is not null; -- Drop all keys from "keys"
table.
SQLite build with some extra modules you can get from my debian repository:
deb http://mobigroup.ru/debian/ lenny main contrib non-free
deb-src http://mobigroup.ru/debian/ lenny main contrib non-free
Install repository keyring as
sudo aptitude install debian-mobigroup-keyring
Also you can get sources directly from
http://mobigroup.ru/debian/pool/main/s/sqlite3/
The patch file for current SQLite release is
http://mobigroup.ru/debian/pool/main/s/sqlite3/sqlite3_3.6.16-2.diff.gz
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] Announce of the new "Versioning" extension
Hello!
This may be used for table versioning and replication.
Source code is available here
http://mobigroup.ru/files/sqlite-ext/
You can get from the debian repository the SQLite build with some extra
extensions:
deb http://mobigroup.ru/debian/ lenny main contrib non-free
deb-src http://mobigroup.ru/debian/ lenny main contrib non-free
=Test script==
CREATE TABLE sessions (
key text not null,
value text not null
);
insert into sessions (key,value) values ('test key 0','test value 0');
select versioning_table('sessions');
.schema
select * from _versioning_sessions;
insert into sessions (key,value) values ('test key 1','test value 1');
insert into sessions (key,value) values ('test key 2','test value 2');
select * from _versioning_sessions;
delete from sessions;
select * from _versioning_sessions;
select unversioning_table('sessions');
.schema
==Test script results=
sqlite> CREATE TABLE sessions (
...> key text not null,
...> value text not null
...> );
sqlite> insert into sessions (key,value) values ('test key 0','test value 0');
sqlite>
sqlite> select versioning_table('sessions');
sqlite> .schema
CREATE TABLE _undo(sql TEXT, status TEXT);
CREATE TABLE _versioning_sessions(key text,value text, _date REAL, _action
TEXT, _rowid INTEGER);
CREATE TABLE sessions (
key text not null,
value text not null
);
CREATE TRIGGER _versioning_sessions_d AFTER DELETE ON sessions BEGIN INSERT
INTO _versioning_sessions (_date, _action, _rowid) values (julianday('now'),
'D', old.rowid);END;
CREATE TRIGGER _versioning_sessions_i AFTER INSERT ON sessions BEGIN INSERT
INTO _versioning_sessions SELECT *, julianday('now') as _date, 'I' as _action,
new.rowid as _rowid FROM sessions WHERE rowid=new.rowid;END;
CREATE TRIGGER _versioning_sessions_u AFTER UPDATE ON sessions BEGIN INSERT
INTO _versioning_sessions SELECT *, julianday('now') as _date, 'U' as _action,
new.rowid as _rowid FROM sessions WHERE rowid=new.rowid;END;
sqlite> select * from _versioning_sessions;
test key 0|test value 0|2455027.00753221|I|1
sqlite>
sqlite> insert into sessions (key,value) values ('test key 1','test value 1');
sqlite> insert into sessions (key,value) values ('test key 2','test value 2');
sqlite> select * from _versioning_sessions;
test key 0|test value 0|2455027.00753221|I|1
test key 1|test value 1|2455027.00753347|I|2
test key 2|test value 2|2455027.00753368|I|3
sqlite>
sqlite> delete from sessions;
sqlite> select * from _versioning_sessions;
test key 0|test value 0|2455027.00753221|I|1
test key 1|test value 1|2455027.00753347|I|2
test key 2|test value 2|2455027.00753368|I|3
||2455027.00753382|D|1
||2455027.00753382|D|2
||2455027.00753382|D|3
sqlite>
sqlite> select unversioning_table('sessions');
sqlite> .schema
CREATE TABLE _undo(sql TEXT, status TEXT);
CREATE TABLE _versioning_sessions(key text,value text, _date REAL, _action
TEXT, _rowid INTEGER);
CREATE TABLE sessions (
key text not null,
value text not null
);
sqlite> select * from _versioning_sessions;
test key 0|test value 0|2455027.00753221|I|1
test key 1|test value 1|2455027.00753347|I|2
test key 2|test value 2|2455027.00753368|I|3
||2455027.00753382|D|1
||2455027.00753382|D|2
||2455027.00753382|D|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] New Book Available
Hello! On Thursday 09 July 2009 17:50:14 cmar...@unc.edu wrote: > What is the title? I am not finding a new book by van der > Laans book on Google or Amazon. As Rick wrote to me: "The book is finished and available through the Publisher Lulu.com. It will be available through Amazon and so on with 6 to 8 weeks. Amazon is a little slow." Now the book is available here: http://www.lulu.com/preview/paperback-book/the-sql-guide-to-sqlite/7251432 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] Announce of the new "Versioning" extension
Hello!
On Wednesday 15 July 2009 09:56:28 Neville Franks wrote:
> Hi Alexey,
> Thank you for this extension which could be quite interesting to many
> SQLite users. Is there any documentation on this available, possibly
> in your new book? I couldn't find any with the source at
> http://mobigroup.ru/files/sqlite-ext/
I did add README file.
> Also you have used the GNU License which means we cannot use this in
> any commercial applications. It also goes against the Public Domain
> license used by SQLite itself - see
> http://www.sqlite.org/copyright.html It would be great if this could
> be changed.
I did change license to Public Domain same as SQLite core and my other
extensions.
P.S. Added indexes in new "Versioning" version.
README =
"Versioning" SQLite extension
Copyright 2009 Alexey Pechnikov
The code is public domain.
The extension may be used for table versioning and replication.
Functions:
versioning_table(SOURCE) - add versioning support for SOURCE table by
1. drop if exists previously created _versioning_SOURCE table
2. add _versioning_SOURCE table to store versions
4. copy current state of SOURCE table
3. create triggers on SOURCE table
unversioning_table(SOURCE) - remove versioning support for SOURCE table.
Doesn't drop _versioning_SOURCE table but only remove SOURCE triggers!
The _versioning_SOURCE table consists all fields of SOURCE table without any
checks or constraints and some additional fields
_date REAL, _action TEXT, _rowid INTEGER
to store date of perform action on SOURCE row, action name ('I' - insert, 'U' -
update, 'D' - delete) and original record rowid.
Add versioning example:
CREATE TABLE key (name text not null);
select versioning_table('key');
.schema
CREATE TABLE _undo(sql TEXT, status TEXT);
CREATE TABLE _versioning_key(name text, _date REAL, _action TEXT, _rowid
INTEGER);
CREATE TABLE key (name text not null);
CREATE INDEX versioning_key_date_idx on _versioning_key(_date);
CREATE INDEX versioning_key_rowid_date_idx on _versioning_key(_rowid,_date);
CREATE TRIGGER _versioning_key_d AFTER DELETE ON key BEGIN INSERT INTO
_versioning_key (_date, _action, _rowid) values (julianday('now'), 'D',
old.rowid);END;
CREATE TRIGGER _versioning_key_i AFTER INSERT ON key BEGIN INSERT INTO
_versioning_key SELECT *, julianday('now') as _date, 'I' as _action, new.rowid
as _rowid FROM key WHERE rowid=new.rowid;END;
CREATE TRIGGER _versioning_key_u AFTER UPDATE ON key BEGIN INSERT INTO
_versioning_key SELECT *, julianday('now') as _date, 'U' as _action, new.rowid
as _rowid FROM key WHERE rowid=new.rowid;END;
Versioning example:
insert into key (name) values ('test key 1');
insert into key (name) values ('test key 1');
delete from key;
.header on
select * from _versioning_key;
name|_date|_action|_rowid
test key 1|2455027.87582762|I|1
test key 1|2455027.87582772|I|2
|2455027.87709961|D|1
|2455027.87709961|D|2
Now you can select versions of SOURCE row by rowid:
.header on
select * from _versioning_key where _rowid=1;
name|_date|_action|_rowid
test key 1|2455027.87582762|I|1
|2455027.87709961|D|1
For replication can be selected versions of all records by current time which
were changed after previous syncronization (1 hour ago, as example):
.header on
select * from _versioning_key where _date>julianday('now','-1 hour');
name|_date|_action|_rowid
test key 1|2455027.87582762|I|1
test key 1|2455027.87582772|I|2
|2455027.87709961|D|1
|2455027.87709961|D|2
These records may be synced by sql dump or by other ways.
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: GiST index
Hello! There are a lot of tasks where GiST index is needed. Does exists any plans to implement 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
[sqlite] Multi-master replication with updated Versioning extension
Hello! Please see http://mobigroup.ru/files/sqlite-ext/versioning/ Master-slave may use ROWIDs and multy-master may use md5 hash of full record. This is test version and I'm glad to get any comments and ideas. P.S. md5 extension sources is here http://mobigroup.ru/files/sqlite-ext/md5/ 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] Multi-master replication with updated Versioning extension
Hello! On Thursday 30 July 2009 17:25:15 P Kishor wrote: > > I haven't looked at your work in depth, but I am interested in this. I > > have implemented a very simple versioning system with TRIGGERs whereby > > every change (INSERT, UPDATE, DELETE) in a column in a table is stored > > in a versions table along with its primary key, allowing me to go back > > and examine any version and roll back to it, if desired. Yes, the primary key field is good enough for master-slave replication but not for multi-master because each master has self sequence counter. And full record hash may be used for master-slave replication on tables without primary keys. As table-independant way extension use ROWID field and md5 hash of all fields. So multi-master is possible with some restrictions such as non-unique records is denied. But may be ROWID + hash of record can help for this situation 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] Multi-master replication with updated Ver sioning extension
Hello! On Thursday 30 July 2009 18:17:45 Jim Showalter wrote: > MD5 hashes can still collide. How does this implementation deal with > hash collisions? You may use any other hash (sha256 as example). But I think md5 collisions is not the problem for common 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] Multi-master replication with updated Ver sioning extension
Hello! On Thursday 30 July 2009 19:47:39 Jay A. Kreibich wrote: > Then again, given that ROWID values are signed 64 bit values, you > could just start each master at some offset (like +0x00FF) > and not worry about it. It would still be a good idea to force all > the tables into an AUTOINCREMENT mode somehow. I'm not agree. I did use this but it is "manual replication". Before start replication databases may be copied from prototype database as files and so they are binary equal. And for replication between different versions of the database you way doesn't work. UUID is really unique key for multi-master replication. SQLite extension is available by link http://mobigroup.ru/files/sqlite-ext/uuid/ Hash of record may be used as pseudo-UUID for some tables. > PRAGMA request: http://www.sqlite.org/cvstrac/tktview?tn=4002 This is bad because ROWID without explicit field is not persistant and may change after dump/restore. Please see illustration of the problem: sqlite> create table test(a); sqlite> insert into test values(1); sqlite> insert into test values(2); sqlite> insert into test values(3); sqlite> select rowid,* from test; 1|1 2|2 3|3 sqlite> delete from test where rowid=2; sqlite> .dump test BEGIN TRANSACTION; CREATE TABLE test(a); INSERT INTO "test" VALUES(1); INSERT INTO "test" VALUES(3); COMMIT; sqlite> drop table test; sqlite> BEGIN TRANSACTION; sqlite> CREATE TABLE test(a); sqlite> INSERT INTO "test" VALUES(1); sqlite> INSERT INTO "test" VALUES(3); sqlite> COMMIT; sqlite> select rowid,* from test; 1|1 2|3 As you can see now ROWID=2 is correspond to field value 3 and it's wrong. 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] Multi-master replication with updated Versioning extension
Hello! I made some changes: hash field in actions table has always name "checksum" (so versioning and replication logic doesn't influence of hash algorithm) versioning() function without second argument now start "local" mode history and actions tables are renamed Updated files is here http://mobigroup.ru/files/sqlite-ext/versioning/ Now there are two problems in the realization: the "replace" conflict resolution algorithm for SOURCE table may produce errors - tickets 3964, 3982 versioning_drop() function doesn't work - ticket 4001 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] Multi-master replication with updated Versioning extension
Hello! On Friday 31 July 2009 18:34:17 Ken wrote: > I've looked at your code and discussions on this list about the versioning. I > have a few questions. > > 1. How are you moving the data around from one master to another? > 2. How are you applying the changes once moved to the master? By the tcl scripts. Databases only store data and versions information. I'm planning to publish some of the tcl scripts on the next week. 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] Multi-master replication with updated Ver sioning extension
Hello!
On Thursday 30 July 2009 18:17:45 Jim Showalter wrote:
> MD5 hashes can still collide. How does this implementation deal with
> hash collisions?
Now it is possible to use any hash function:
-- first column is unique key
select versioning('arg1','sessions');
-- use md5 hash of all fields
select versioning('md5','sessions');
Note: function arg1() is coded in versioning extension.
select arg1('a');
a
select arg1('a','b','c');
a
When you will define sha256 function (as example) you can do
select versioning('sha256','sessions');
Thanks for you comment.
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] New group_split() function as inverse of group_concat()
Hello! Can somebody to show example of the group_split() function? PRAGMA can return rowset but how to do this in user functions? 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] Indexes problem in unicode extension support
Hello!
I'm using extension for base unicode support
(http://mobigroup.ru/files/sqlite-ext/unicode/), but in last two releases find
the problem with indexes by columns with redefined NOCASE collation
(groups.name autoindex and composite index with
const_telephony_direction.name):
=
$ sqlite3 :memory:
SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE groups
...> (
...> name TEXT collate NOCASE UNIQUE NOT NULL DEFAULT ''
...> );
sqlite> INSERT INTO "groups" VALUES('Администраторы');
sqlite> CREATE TABLE const_telephony_direction (
...> name text not null,
...> destcode text not null
...> );
sqlite> INSERT INTO "const_telephony_direction"
VALUES('Совинтел_МГМН_старые_кмст','7485');
sqlite> CREATE INDEX const_telephony_direction_complex_idx on
const_telephony_direction(name,destcode);
sqlite> COMMIT;
sqlite>
sqlite> pragma integrity_check;
rowid 1 missing from index sqlite_autoindex_groups_1
=
Without indexes on redefined NOCASE collated fields this database is correct:
=
$ sqlite3 :memory:
SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE groups
...> (
...> name TEXT collate NOCASE NOT NULL DEFAULT ''
...> );
sqlite> INSERT INTO "groups" VALUES('Администраторы');
sqlite> CREATE TABLE const_telephony_direction (
...> name text not null,
...> destcode text not null
...> );
sqlite> INSERT INTO "const_telephony_direction"
VALUES('Совинтел_МГМН_старые_кмст','7485');
sqlite> CREATE INDEX const_telephony_direction_complex_idx on
const_telephony_direction(destcode);
sqlite> COMMIT;
sqlite>
sqlite> pragma integrity_check;
ok
=
Can anybody help me to to fix 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] Indexes problem in unicode extension support
Hello! On Friday 18 September 2009 20:05:15 Jean-Christophe Deschamps wrote: > Alexey, > > >I'm using extension for base unicode support > >(http://mobigroup.ru/files/sqlite-ext/unicode/), but in last two > >releases find the problem with indexes by columns with redefined > >NOCASE collation > > This code has many problems and the version on your site (the same > version is available elsewhere) doesn't work as expected. NOCASE is > not the only function with problems, there are more with UPPER, > LOWER. Even some tries have wrong data. I had to recompile three of them. This module is based on code from http://ioannis.mpsounds.net/blog/2007/12/19/sqlite-native-unicode-like-support/ > I now have some time to finish it and it should be ready for beta > anytime soon. > > I compile for Windows 32 with MinGW gcc but it shouldn't be very hard > for someone to make it work on another OS as well. I can test it on debian lenny. 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] Most wanted features of SQLite ?
Hello! On Saturday 19 September 2009 00:43:18 Noah Hart wrote: > Stored Procedures There are Tiny C compiler extension and realization of stored procedures for SQLite 2 and Lua extension and other. So you can use one or all of these. 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 create operator "IS Empty" similar to "IS NULL"
Hello!
Please see ticket
http://www.sqlite.org/src/info/15e11a2c87
Function IFEmpty may be simple:
static void ifemptyFunc(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
int i;
for(i=0; ihttp://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Saturday 19 September 2009 18:21:22 John Stanton wrote: > There is a PL/SQL implementation available and we use Javascript as a > stored procedure capability in Sqlite. It integrates nicely with WWW > applications. I don't know this. Can you show link to docs and examples? Thx. 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] Most wanted features of SQLite ?
Hello! On Saturday 19 September 2009 02:17:39 Subsk79 wrote: > StepSqlite brings powerful Stored Procedure support with full power of > PL/SQL syntax to SQLite. It is a 'compiler' as opposed to a mere > 'wrapper' so it generates much more efficient code than any wrapper > could ever achieve - for instance, it pre-compiles all SQL in your > code right when the lib is loaded - no compile-overload at runtime - > this is exactly what one expects from a true 'Stored' Procedure. Do you have support for compiled extension stored into database table? It's not good way to distribute external libs. 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] Most wanted features of SQLite ?
Hello!
On Saturday 19 September 2009 02:32:03 Roger Binns wrote:
> If you are using the C api then use
> sqlite3_auto_extension - http://www.sqlite.org/c3ref/auto_extension.html -
> to register a callback that is called whenever a new db is opened.
For "autoload" extension we must call this function? It's very strange.
I think this code in openDatabase function is more useful for statically
linked extensions:
#ifdef SQLITE_ENABLE_RTREE
if( !db->mallocFailed && rc==SQLITE_OK){
rc = sqlite3RtreeInit(db);
}
#endif
More interesting may be loading extensions from database table but it's not
realised now, really?
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] Understanding ICU collations: Where needed?
Hello!
You can add code for load collation for all database connections.
As example, in function sqlite3IcuInit() before "return rc;" add these lines:
UErrorCode status = U_ZERO_ERROR;
UCollator *pUCollator = ucol_open("ru_RU", &status);
if( !U_SUCCESS(status) ){
return SQLITE_ERROR;
}
rc = sqlite3_create_collation_v2(db, "russian", SQLITE_UTF16, (void
*)pUCollator,.
icuCollationColl, icuCollationDel
);
if( rc!=SQLITE_OK ){
ucol_close(pUCollator);
}
And you collation will be loaded automatically:
$ sqlite3
SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma collation_list;
0|russian
1|NOCASE
2|RTRIM
3|BINARY
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] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 00:14:56 Darren Duncan wrote: > 3b. I would like to have the option for SQLite to always operate using > 2-valued-logic rather than 3-valued-logic, meaning that NULL is simply > treated > as another value of its own singleton type that is disjoint from all other > types > same as Integer, Numeric, Text, Blob are disjoint. And so, one could then > just > use ordinary equality or not-equality tests to check for NULL, and NULL would > equal NULL but not equal anything else, and boolean tests would always return > true or false, not null. Once again, this would mean that behavior is more > like > what users actually expect and bugs can be avoided, and the query optimizer > can > be more efficient again, allowing more reorganization knowing at answers > wouldn't change due to this. NULL is the old RDBMS problem. And SQLite Tcl interface has no equal availability for NULL values because we can't operate with non-defined variables. So we can translate NULL values into empty Tcl strings but not vice versa. 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] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 02:45:32 sub sk79 wrote: > 1. Generate a loadable SQLite extension and distribute it with DB. We can write C extension without any wrapper. Why we need to learn your extension for getting the same result and possible new bugs? > 2. If distributing loadable extensions is a concern (security or > otherwise), StepSqlite also has an option to generate a regular C++ > library instead. This library can be linked into the user's > application code And how about Tcl, Python, etc? We need to recompile it? :-) Application code (Tcl, Python, etc.) can be stored into database and executed by application. C code can be compiled once and executed with Tiny C compiler. I'm sorry but I don't see any helpful features of your wrapper. 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] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 02:42:30 Darren Duncan wrote: > As for Tcl, well one solution there is to create a new singleton TCL type and > use its one value to correspond to NULL, and so then empty string will > continue > to just mean empty string, as it should. Not being able to distinguish > known-to-be-an-empty-string from unknown-or-N/A value is a bad thing. In human interface can be used 'not defined' or 'overload' or any other value and all of these together in dependence of the situation. SQLite can store 'undef' and 'overload' and other values into integer or fload fields and this behaviour is fine. Application can operate with correct data by checking a typeof(). For single undefined value we can do more simple. Empty string or 'undefined' values are equal because have the same typeof()=text. I don't see any reasons to use a new singleton TCL type inside of empty string. 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] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 08:05:04 Darren Duncan wrote: > The more general solution here to the duplicate column name problem is to be > stricter than the SQL standard and treat attempts to return duplicate column > names as a syntax or constraint error. For example, if you had 2 tables > 'foo' > and 'bar' with columns named (a, b) and (b, c), then a plain "select * from > foo > inner join bar on ..." should throw an exception because there would be two > 'b' > in the result. And so, proper NATURAL or USING behavior is one way to say > "select * from foo inner join bar ..." with success, and spelling out the > result > column list rather than using "*" is another way. But you have to deal with > it > explicitly or the SQL will refuse to run, is what the DBMS should do, or the > DBMS should be customizable so it can be thusly strict. It's interesting. The new pragma "unique_column_names" may be helpful for a lot of situations same as the "indexed by" condition for selects. 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] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 18:16:19 sub sk79 wrote: > PL/SQL has a very wide user-base and a huge repository of existing > code-base in the world. Using StepSqlite PL/SQL compiler this huge > base can use SQLite by reusing their code as well as reusing their > skills - no learning curve for this set of users. But I write stored procedures and triggers for PostgreSQL on Tcl. You can write it on perl, java, etc. PL/pgSQL or PL/SQL is not the best solution to all. IMHO is more interesting any open source lang than proprietary PL/SQL. Oracle has a lot of a non-standart extensions which are not exists in SQLite. And Oracle ideology is very different. You may not replace Oracle to SQLite with the same application architecture. 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] Most wanted features of SQLite ?
Hello! On Monday 21 September 2009 01:45:07 Fred Williams wrote: > With the background of the never ending drumbeat of "feature, feature, > feature..." on this list as a reason, I wonder if the structure of > SQLite could be "enhanced" to better support the "plug-in" concept, aka: > as with Firefox, IGoogle, and the like for instance. SQLite does have the best extensibility of known to me DBMS. You can bind function or collation from any programming lang. And you can easy write extension with a new functions and collations. You can use SQLite functions in any programming lang and create wrappers. Is it not enought for 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] SQL Lite date / time functions
Hello! On Monday 21 September 2009 05:44:31 D. Richard Hipp wrote: > Is the content of the "date" field either a julian day number or an > ISO-8601 format date string? Please speak is now correct to store 'start of month' dates in julianday format? Does SQLite now uses internal reprezentation of microseconds for the julianday and store it as long integer or float? 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] Most wanted features of SQLite ?
Hello! On Monday 21 September 2009 11:36:15 Roger Binns wrote: > Also not mentioned is that it is available under a public domain license and > hence anyone has the right to use it in any way they deem fit, make changes, > distribute changes, charge anything they want, keep everything public, > private or anything else. And last few years every month are released new important features. About five years ago I did start to use SQLite as RDBMS for PDA and as helper storage for server but now I have some projects translated to SQLite from PostgreSQL and Oracle. That's great! 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] List of active savepoints names
Hello! On Monday 21 September 2009 11:48:37 Lukas Gebauer wrote: > > > I am searching some API function for get list of active savepoint > > > names. Is this possible? Thank you! > > > > No, this is not possible. Why do you need it in the first place? > > It will be great for debugging, for example. > You can create a custom wrapper for savepoints which may have the stack of calls. 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] Most wanted features of SQLite ?
Hello! On Monday 21 September 2009 14:33:30 Grzegorz Wierzchowski wrote: > BTW while we are at subject of SQLite extensions. > I'm very new on this e-mail list but already saw here and there in mails > several links to places around the web with some extensions. There are a lot of extensions in defferent places. As example the compress extension functions are publicated by DRH in mail list, see comments in the sources. Yes, it is. Now you can see my archive http://mobigroup.ru/files/sqlite-ext/ Of cource I will glad to know about other extensions repositories. > I think it could be quite helpfull "first check place" if anybody is looking > for something like virtual table which stores data in csv files, or so, or > opposite - have wrote something general, and want to share. Please see VirtualText extension from Spatialite project http://mobigroup.ru/files/sqlite-ext/virtualtext/ I did pack this as single extension. NB: in multitheaded env is more better to make copy of date in SQLite with the extension and after operate with the copy. 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] SQL Lite date / time functions
Hello!
On Monday 21 September 2009 15:59:47 Igor Tandetnik wrote:
> > Please speak is now correct to store 'start of month' dates in
> > julianday format?
>
> Was it at some point "incorrect" to do so? Yes, you can store any date
> in julian day format, start of month or otherwise.
Are correct selects like as
SELECT * from tbl_name where date = julianday('now','start of month');
Equal condition for dates is work now but is it safety?
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] Faster inserts in SQlite ...
Hello! On Monday 21 September 2009 15:05:04 Atul_Vaidya wrote: > 5. PRAGMA cache_size = 12000; I use value 128 000 on servers. You must set pragma page_size=4096; If database is not empty is needed to vacuum it. > > i also avoid the update calls, and i do all the insertion in between Begin > Transaction and End Transaction.I was planning to call the insertion calls > to SQLite in a separate thread.I am using the SQLite Version 3.3.13.When i > dig in through documentation, the latest version was 3.3.18.FAQ says that > the SQlite is Thread safe. Please suggest me if I should work in this > direction. Last version is 3.6.18 and is more better to use this. But your version is thread-safe 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] Faster inserts in SQlite ...
Hello! On Monday 21 September 2009 19:50:37 Pavel Ivanov wrote: > > You must set pragma page_size=4096; > > Can I ask why "must" and this number? Why not the maximum 32768? $ sudo tune2fs -l /dev/sdb1|grep 4096 Block size: 4096 Most modern FS have the same block size. 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] Faster inserts in SQlite ...
Hello! On Monday 21 September 2009 23:11:57 Pavel Ivanov wrote: > > Most modern FS have the same block size. > > Though I don't think that in case of synchronous = OFF there's any > benefit of using pages of the exactly same size as block in file > system. Correct me if I'm wrong. Have you any IO operations? As result you have dependence of page size. 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] SQL Lite date / time functions
Hello!
On Monday 21 September 2009 19:56:07 Igor Tandetnik wrote:
> > Are correct selects like as
> > SELECT * from tbl_name where date = julianday('now','start of month');
>
> I see no reason why not. Note that the condition will only hold when
> "date" column represents midnight on the corresponding day.
>
> > Equal condition for dates is work now but is it safety?
>
> In general, comparing double values for exact equality is risky.
> However, fractions that are powers of two are represented exactly in a
> double, so the comparison would work for values that represent, for
> example, 3, 6, 9 and 12 o'clock (both AM and PM).
Can you add this note to documentation? This is very speculate question.
Especially because internal SQLite representation of julianday was chanded
some times ago.
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] SQL Lite date / time functions
Hello! On Thursday 24 September 2009 05:54:12 Dan Bishop wrote: > > Can you add this note to documentation? This is very speculate question. > > Especially because internal SQLite representation of julianday was chanded > > some times ago. > It's not a date-specific issue; the same thing occurs with ordinary numbers. > > sqlite> select (1.0/49)*49 = 1.0; > 0 Yes it is, but for ordinary numbers is not useful to check like this select (1.0/50)*50 = 1.0; 1 The equality check of midnights in julianday format is more common task. I see this question in the mail list every week. 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] BUG: wrong type casting for constants in tcl interface
Hello!
I find some incorrect types casting for constants in all SQLite versions.
= test script =
#!/usr/bin/tclsh8.5
package require sqlite3
sqlite3 db :memory:
db eval {
create table test(a int);
insert into test values (1);
}
proc test {label sql} {
global i
puts -nonewline $label\t
puts [db eval $sql]
}
set i 1
test 1.1 {select * from test where a=$i}
test 1.2 {select * from test where 1=$i} ;# it doesn't work
test 1.3 {select a from test where a IN (cast($i AS INT), 160)}
test 1.4 {select a from test where 1 IN (cast($i AS INT), 160)}
set i [db onecolumn {select quote($i)}]
test 2.1 "select * from test where a=$i"
test 2.2 "select * from test where 1=$i" ;# it doesn't work
test 2.3 "select a from test where a IN ($i, 160)"
test 2.4 "select a from test where 1 IN ($i, 160)" ;# it doesn't work
test 3.1 "create view view_test1 as select * from test where a=$i;select * from
view_test1"
test 3.2 "create view view_test2 as select * from test where 1=$i;select * from
view_test2" ;# it doesn't work
test 3.3 "create view view_test3 as select * from test where a IN ($i);select *
from view_test3"
test 3.4 "create view view_test4 as select * from test where 1 IN ($i);select *
from view_test4" ;# it doesn't work
==
= result =
1.1 1
1.2
1.3 1
1.4 1
2.1 1
2.2
2.3 1
2.4
3.1 1
3.2
3.3 1
3.4
==
Of cource results of tests 1.1 and 1.2, 2.1 and 2.2, etc. must be equal.
I sqlite3 shell all work correct.
== sqlite3 shell
sqlite> select * from test where 1=1;
1
sqlite> select * from test where 1=quote(1);
1
sqlite> select a from test where a IN (1, 160);
1
sqlite> select a from test where 1 IN (1, 160);
1
sqlite> select a from test where 1 IN (quote(1), 160);
1
sqlite> select a from test where a IN (quote(1), 160);
1
======
P.S. I did report about this problem some times ago...
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: extended last_insert_rowid(table_name) function
Hello! With this function the triggers like this CREATE TRIGGER view_user_insert instead of insert on view_user begin insert into user ... insert into user_attribute (user_id,...) values (last_insert_rowid(),...); -- user table _must_ have PK column insert into user_balance (user_id,...) values ((select max(rowid) from user),...); ... end; will be rewrited as CREATE TRIGGER view_user_insert instead of insert on view_user begin insert into user ... insert into user_attribute (user_id,...) values (last_insert_rowid(user),...); insert into user_balance (user_id,...) values (last_insert_rowid(user),...); ... 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
Re: [sqlite] dump in-memory db to file in tcl
Hello!
1. Use tcl backup API
The "backup" method
The "backup" method makes a backup copy of a live database. The command syntax
is like this:
dbcmd backup ?source-database? backup-filename
The optional source-database argument tells which database in the current
connection should be backed up. The default value is main (or, in other words,
the primary database file). To back up TEMP tables use temp. To backup an
auxilary database added to the connection using the ATTACH command, use the
name of that database as it was assigned in the ATTACH command.
The backup-filename is the name of a file into which the backup is written.
Backup-filename does not have to exist ahead of time, but if it does, it must
be a well-formed SQLite database.
The "restore" method
The "restore" method copies the content a separate database file into the
current database connection, overwriting any preexisting content. The command
syntax is like this:
dbcmd restore ?target-database? source-filename
The optional target-database argument tells which database in the current
connection should be overwritten with new content. The default value is main
(or, in other words, the primary database file). To repopulate the TEMP tables
use temp. To overwrite an auxilary database added to the connection using the
ATTACH command, use the name of that database as it was assigned in the ATTACH
command.
The source-filename is the name of a existing well-formed SQLite database file
from which the content is extracted.
2. Use sql commands to copy database structure from sqlite_master table and
copy data
2006-02-20: A simple TCL-Implementation for loading a DB into memory:
proc loadDB {dbhandle filename} {
if {$filename != ""} {
#attach persistent DB to target DB
$dbhandle eval "ATTACH DATABASE '$filename' AS loadfrom"
#copy each table to the target DB
foreach {tablename} [$dbhandle eval "SELECT name FROM
loadfrom.sqlite_master WHERE type = 'table'"] {
$dbhandle eval "CREATE TABLE '$tablename' AS SELECT * FROM
loadfrom.'$tablename'"
}
#create indizes in loaded table
foreach {sql_exp} [$dbhandle eval "SELECT sql FROM
loadfrom.sqlite_master WHERE type = 'index'"] {
$dbhandle eval $sql_exp
}
#detach the source DB
$dbhandle eval {DETACH loadfrom}
}
}
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 speed greatly decreasing over time
Hello! Try this: pragma cache_size=20; 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 speed greatly decreasing over time
Hello! Because update == delete + insert you must have fragmentation. The degree of the fragmentation is proporsional to count of replaces. So you may have problems after inserting a lot of dublicates. Are you really need to insert or update? 1. I'm using few hundread MB databases like CREATE TABLE telephony_log ( ... unique (nas_name,port,duration,origin,date_start) on conflict ignore ); Performance of "ignore" conflict resolution is more better. 2. May be unique index can be better than text PK 3. May be "insert into t select * from temp_t" can be helpful 4. You can mark records as deleted without deleting it immediate. And delete old records periodically whith vacuum after this operation. == PRAGMA auto_vacuum=0; pragma default_cache_size=20; CREATE TABLE t (k varchar(50) not null, d text not null, e datetime,is_new int default 1); create index t_k_idx on t(k); begin; CREATE TEMP TABLE temp_t (k varchar(50) primary key not null, d text not null, e datetime,is_new int default 1); insert into temp_t ... update t set is_new=0 where k in (select k from temp_t); insert into t select * from temp_t; commit; By cron: delete from t where is_new=0; vacuum; 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: wrong type casting for constants in tcl interface
Hello! On Thursday 08 October 2009 11:31:14 Dan Kennedy wrote: > The behaviour is actually correct, assuming that SQLite is > using sqlite3_bind_text() to bind the value of $i to the SQL > statement executed by [db eval]. > Why integer or real is binded as text? It's very strange and produced results doesn't correspond to sqlite3 shell results. 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: wrong type casting for constants in tcl interface
Hello!
These work fine:
set i 1
string is wideint -strict $i ;# for correct typing
test 1.1 {select * from test where a=$i}
test 1.2 {select * from test where 1=$i}
But it's badly to manually call [string is wideint -strict]
before all queries.
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 speed greatly decreasing over time
Hello! But why do you not compress big text strings? And index size can be reduced by using md5 hash of text key field. See the extensions http://mobigroup.ru/files/sqlite-ext/ http://mobigroup.ru/files/sqlite-ext/md5/ 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: BUG: wrong type casting for constants in tcl interface
if { $_result eq $result} {
puts OK
} else {
puts ERROR\t$result!=$_result
}
}
proc sql {value} {return $value}
db function sql sql
set i 1
set j [db onecolumn {select quote($i)}]
test 1.0 {select typeof($i)} integer
test 1.1 {select * from test where a=$i} 1
test 1.2 {select * from test where 1=$i} 1 ;# it doesn't work in orig sqlite
test 1.3 {select a from test where a IN (cast($i AS INT), 160)} 1
test 1.4 {select a from test where 1 IN (cast($i AS INT), 160)} 1
test 2.0 {select typeof($j)} integer
test 2.1 "select * from test where a=$j" 1
test 2.2 "select * from test where 1=$j" 1 ;# it doesn't work in orig sqlite
test 2.3 "select a from test where a IN ($j, 160)" 1
test 2.4 "select a from test where 1 IN ($j, 160)" 1 ;# it doesn't work in orig
sqlite
test 3.1 "create view view_test1 as select * from test where a=$j;select * from
view_test1" 1
test 3.2 "create view view_test2 as select * from test where 1=$j;select * from
view_test2" 1 ;# it doesn't work in orig sqlite
test 3.3 "create view view_test3 as select * from test where a IN ($j);select *
from view_test3" 1
test 3.4 "create view view_test4 as select * from test where 1 IN ($j);select *
from view_test4" 1 ;# it doesn't work in orig sqlite
test 4.1 "select typeof(sql($j))" integer
===
$ ./tclsqlite_bind.tcl
===
1.0 OK
1.1 OK
1.2 OK
1.3 OK
1.4 OK
2.0 OK
2.1 OK
2.2 OK
2.3 OK
2.4 OK
3.1 OK
3.2 OK
3.3 OK
3.4 OK
4.1 OK
===
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] BUG: The difference between working tclsqlite in tclsh shell and from tcl script
- Tcl_GetWideIntFromObj(0, pVar, &v);
+}else if( TCL_OK == Tcl_GetWideIntFromObj(0, pVar, &v)){
sqlite3_result_int64(context, v);
+}else if( TCL_OK == Tcl_GetDoubleFromObj(0, pVar, &r)){
+ sqlite3_result_double(context, r);
}else{
data = (unsigned char *)Tcl_GetStringFromObj(pVar, &n);
sqlite3_result_text(context, (char *)data, n, SQLITE_TRANSIENT);
@@ -1629,6 +1621,9 @@
SqlPreparedStmt *pPreStmt; /* Pointer to a prepared statement */
int rc2;
+Tcl_ObjType *tclWideIntType = Tcl_GetObjType("wideint");
+Tcl_ObjType *tclDoubleType = Tcl_GetObjType("double");
+
if( choice==DB_EVAL ){
if( objc<3 || objc>5 ){
Tcl_WrongNumArgs(interp, 2, objv, "SQL ?ARRAY-NAME? ?SCRIPT?");
@@ -1728,7 +1723,7 @@
assert( pPreStmt==0 );
}
- /* Bind values to parameters that begin with $ or :
+ /* Bind values to parameters that begin with $ or : or @
*/
nVar = sqlite3_bind_parameter_count(pStmt);
nParm = 0;
@@ -1744,10 +1739,10 @@
if( pVar ){
int n;
u8 *data;
-char *zType = pVar->typePtr ? pVar->typePtr->name : "";
-char c = zType[0];
+double r;
+Tcl_WideInt v;
if( zVar[0]=='@' ||
- (c=='b' && strcmp(zType,"bytearray")==0 && pVar->bytes==0) ){
+ ( pVar->typePtr && pVar->typePtr->name[0]=='b' &&
strcmp(pVar->typePtr->name,"bytearray")==0 && pVar->bytes==0) ){
/* Load a BLOB type if the Tcl variable is a bytearray and
** it has no string representation or the host
** parameter name begins with "@". */
@@ -1755,18 +1750,10 @@
sqlite3_bind_blob(pStmt, i, data, n, SQLITE_STATIC);
Tcl_IncrRefCount(pVar);
apParm[nParm++] = pVar;
-}else if( c=='b' && strcmp(zType,"boolean")==0 ){
- Tcl_GetIntFromObj(interp, pVar, &n);
- sqlite3_bind_int(pStmt, i, n);
-}else if( c=='d' && strcmp(zType,"double")==0 ){
- double r;
- Tcl_GetDoubleFromObj(interp, pVar, &r);
- sqlite3_bind_double(pStmt, i, r);
-}else if( (c=='w' && strcmp(zType,"wideInt")==0) ||
- (c=='i' && strcmp(zType,"int")==0) ){
- Tcl_WideInt v;
- Tcl_GetWideIntFromObj(interp, pVar, &v);
+}else if( TCL_OK == Tcl_GetWideIntFromObj(interp, pVar, &v)) {
sqlite3_bind_int64(pStmt, i, v);
+}else if( TCL_OK == Tcl_GetDoubleFromObj(interp, pVar, &r)) {
+ sqlite3_bind_double(pStmt, i, r);
}else{
data = (unsigned char *)Tcl_GetStringFromObj(pVar, &n);
sqlite3_bind_text(pStmt, i, (char *)data, n, SQLITE_STATIC);
===
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] building permanently loaded extensions
Hello!
On Thursday 17 September 2009 07:25:33 P Kishor wrote:
> pursuant to a recent email, I am wondering if I can build
> extension-functions.c
> <http://www.sqlite.org/contrib/download/extension-functions.c?get=25>
> so that it is permanently available within sqlite library, and not
> just when loaded manually via a command. If I can, could someone
> please guide me to a tutorial for doing so?
In main.c add these lines
#ifdef SQLITE_ENABLE_FUNCTIONS
if( !db->mallocFailed && rc==SQLITE_OK ){
rc = sqlite3FunctionsInit(db);
}
#endif
The modified extension is available here
http://mobigroup.ru/files/sqlite-ext/functions/
And compile with -DSQLITE_ENABLE_FUNCTIONS
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 performance oddity
Hello! How about solution like to select rowid from my_fts_table where my_fts_table match @query2 and rowid in (select rowid from my_fts_table where my_fts_table match @query1) limit 21 I don't know about performance of this but rowid index may be used success. 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 in-memory database far too slow in my use case
Hello!
On Sunday 11 October 2009 00:54:04 Simon Slavin wrote:
> > Using transactions speeds up a long series of SELECTs because it
> > eliminates the need to re-acquire a read-only file-lock for each
> > individual SELECT.
> >
> > Since in-memory databases have no file locks, I'm not sure that is
> > relevant to this specific case.
>
> I wasn't sure about that. It could still be slower
You can check it very easy.
In transactions:
ve...@veter-laptop:/tmp$ ./test.tcl
19968119 microseconds per iteration
25649514 microseconds per iteration
Without transactions:
ve...@veter-laptop:/tmp$ ./test.tcl
35586024 microseconds per iteration
28630785 microseconds per iteration
$ cat ./test.tcl
#!/usr/bin/tclsh8.5
package require sqlite3
sqlite3 db :memory:
set limit 50
db eval {create table test(id int primary key, value text)}
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set value "value $i"
db eval {insert into test (value) values ($value)}
}
}
}]
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select value from test where rowid=$rowid}
}
}
}]
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 in-memory database far too slow in my use case (new benchmark inside)
Hello!
On Sunday 11 October 2009 12:04:37 Ron Arts wrote:
>CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>
> Then I insert 50 records like this:
>
>INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>
> (with consecutive values for the id value.)
>
> do you expect the id column now to be mapped to the Row ID, so
> this results in the fastest possible way of selecting by id?
Yes, the id is alias for rowid in your table. Check it by
sqlite3 > explain query plan select name from company where id=1;
> I now get this:
>
> sqlite3 prepared insert in trx 50 records time: 5.08 secs
> sqlite3 select 50 records time: 19.28 secs
> sqlite3 prepared select 50 records time: 3.47 secs
> glib2 hash tables insert 50 records time: 0.37 secs
> glib2 hash tables lookup 50 records time: 0.25 secs
>
> But I'm still looking to speed up selects.
Hm... I think you may not open more than 8 000 tcp/ip sockets per second
in common case and so SQLite speed is good enough. Why you write about
"to handle tens of thousands requests per second"?
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] flexible data import
Hello! On Sunday 11 October 2009 12:56:59 Alexander Poddey wrote: > my question again: does something like this already exist? There are a few discussion topics about the ETL solutions. [sqlite] Someone knows about an ETL tool in foreign languages? [sqlite] Software operation and ETL tools [sqlite] Low-Cost data migration and ETL tools etc. 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 in-memory database far too slow in my use case
Hello!
On Sunday 11 October 2009 22:52:29 Jay A. Kreibich wrote:
> A bit to my surprise, the difference is even more significant using
> prepared statements in a C program. For a half-million selects over a
> similar table in a :memory: database, there is a 20% speed-up by
> wrapping all the selects in a transaction (vs the 10% you're seeing).
> It's averaging about 4.3 seconds in auto-commit mode, and 3.4 seconds
> in an explicit transaction.
The tcl programm doing copy of the selected rows. May be your C programm
doesn't copy the selected data?
The modified tcl test script can show how transaction increase speed of data
extracting. In this case the test programm perform copy of data too but the
data doesn't extracting from database table when we use the "select NULL"
construction.
The performance increased of ~10% when we extract table data and of
~3% again.
$ ./test.tcl
insert transaction 50 rows
21233766 microseconds per iteration
select 50 rows
28164019 microseconds per iteration
select without extract 50 rows
26379441 microseconds per iteration
select transaction 50 rows
25749923 microseconds per iteration
select transaction without extract 50 rows
25644248 microseconds per iteration
$ cat ./test.tcl
#!/usr/bin/tclsh8.5
package require sqlite3
sqlite3 db :memory:
set limit 50
db eval {create table test(id int primary key, value text)}
puts "insert transaction $limit rows"
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set value "value $i"
db eval {insert into test (value) values ($value)}
}
}
}]
puts "\nselect $limit rows"
puts [time {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select value from test where id=$rowid}
}
}]
puts "\nselect without extract $limit rows"
puts [time {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select NULL from test where id=$rowid}
}
}]
puts "\nselect transaction $limit rows"
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select value from test where id=$rowid}
}
}
}]
puts "\nselect transaction without extract $limit rows"
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select NULL from test where id=$rowid}
}
}
}]
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] BUG: The difference between working tclsqlite in tclsh shell and from tcl script - in 3.6.19 is reproduced too
- Tcl_GetWideIntFromObj(0, pVar, &v);
+}else if( TCL_OK == Tcl_GetWideIntFromObj(0, pVar, &v)){
sqlite3_result_int64(context, v);
+}else if( TCL_OK == Tcl_GetDoubleFromObj(0, pVar, &r)){
+ sqlite3_result_double(context, r);
}else{
data = (unsigned char *)Tcl_GetStringFromObj(pVar, &n);
sqlite3_result_text(context, (char *)data, n, SQLITE_TRANSIENT);
@@ -1629,6 +1621,9 @@
SqlPreparedStmt *pPreStmt; /* Pointer to a prepared statement */
int rc2;
+Tcl_ObjType *tclWideIntType = Tcl_GetObjType("wideint");
+Tcl_ObjType *tclDoubleType = Tcl_GetObjType("double");
+
if( choice==DB_EVAL ){
if( objc<3 || objc>5 ){
Tcl_WrongNumArgs(interp, 2, objv, "SQL ?ARRAY-NAME? ?SCRIPT?");
@@ -1728,7 +1723,7 @@
assert( pPreStmt==0 );
}
- /* Bind values to parameters that begin with $ or :
+ /* Bind values to parameters that begin with $ or : or @
*/
nVar = sqlite3_bind_parameter_count(pStmt);
nParm = 0;
@@ -1744,10 +1739,10 @@
if( pVar ){
int n;
u8 *data;
-char *zType = pVar->typePtr ? pVar->typePtr->name : "";
-char c = zType[0];
+double r;
+Tcl_WideInt v;
if( zVar[0]=='@' ||
- (c=='b' && strcmp(zType,"bytearray")==0 && pVar->bytes==0) ){
+ ( pVar->typePtr && pVar->typePtr->name[0]=='b' &&
strcmp(pVar->typePtr->name,"bytearray")==0 && pVar->bytes==0) ){
/* Load a BLOB type if the Tcl variable is a bytearray and
** it has no string representation or the host
** parameter name begins with "@". */
@@ -1755,18 +1750,10 @@
sqlite3_bind_blob(pStmt, i, data, n, SQLITE_STATIC);
Tcl_IncrRefCount(pVar);
apParm[nParm++] = pVar;
-}else if( c=='b' && strcmp(zType,"boolean")==0 ){
- Tcl_GetIntFromObj(interp, pVar, &n);
- sqlite3_bind_int(pStmt, i, n);
-}else if( c=='d' && strcmp(zType,"double")==0 ){
- double r;
- Tcl_GetDoubleFromObj(interp, pVar, &r);
- sqlite3_bind_double(pStmt, i, r);
-}else if( (c=='w' && strcmp(zType,"wideInt")==0) ||
- (c=='i' && strcmp(zType,"int")==0) ){
- Tcl_WideInt v;
- Tcl_GetWideIntFromObj(interp, pVar, &v);
+}else if( TCL_OK == Tcl_GetWideIntFromObj(interp, pVar, &v)) {
sqlite3_bind_int64(pStmt, i, v);
+}else if( TCL_OK == Tcl_GetDoubleFromObj(interp, pVar, &r)) {
+ sqlite3_bind_double(pStmt, i, r);
}else{
data = (unsigned char *)Tcl_GetStringFromObj(pVar, &n);
sqlite3_bind_text(pStmt, i, (char *)data, n, SQLITE_STATIC);
===
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] BUG: datatypes conversion logic error
Hello! The documentation speak "Datatypes In SQLite Version 3 The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statement that work on statically typed databases should would the same way in SQLite." But is't wrong and SQLite type system is incompatible with other databases and produce logic errors. SQLite is typeless database but $ sqlite3 :memory: SQLite version 3.6.19 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select 1='1'; 0 PostgreSQL is strict typing database but $ psql -h localhost --cluster 8.1/testing -U postgres template1 template1=> select 1='1'; ?column? -- t (1 row) In SQLite selects, views and triggers on views there are a lot of problems as result of the bug. As example the trigger is work incorrect for _numeric_ group_name because the group_name field is defined as text: CREATE TRIGGER view_user_service_hw_insert instead of insert on view_user_service_hw begin select RAISE (ABORT,'Service does not found') WHERE (select count(id) from user_service where id=NEW.user_service_id and delete_date IS NULL)=0; select RAISE (ABORT,'The hardware is used yet') WHERE (select count(id) from view_user_service_hw where user_service_id=NEW.user_service_id and group_name=NEW.group_name and delete_date IS NULL)>0; insert into user_service_hw (user_service_id,hw_id) select NEW.user_service_id, id from hw_telephony_number where group_name=NEW.group_name and 'telephony_number'=(select hw_name from view_user_service where id=NEW.user_service_id); end; And we must patch this like to CREATE TRIGGER view_user_service_hw_insert instead of insert on view_user_service_hw begin select RAISE (ABORT,'Service does not found') WHERE (select count(id) from user_service where id=NEW.user_service_id and delete_date IS NULL)=0; select RAISE (ABORT,'The hardware is used yet') WHERE (select count(id) from view_user_service_hw where user_service_id=NEW.user_service_id and group_name=cast(NEW.group_name as text) and delete_date IS NULL)>0; insert into user_service_hw (user_service_id,hw_id) select NEW.user_service_id, id from hw_telephony_number where group_name=cast(NEW.group_name as text) and 'telephony_number'=(select hw_name from view_user_service where id=NEW.user_service_id); end; There are no same problems in PostgreSQL and so SQLite has incompatible 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] BUG: datatypes conversion logic error
Hello! $ sqlite3 SQLite version 3.6.19 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select 1='1'; 0 sqlite> create table test(a text); sqlite> insert into test values (1); sqlite> select * from test where a='1'; 1 sqlite> select * from test where a=1; 1 So 1 can be equal to '1' and can be not. It's terrible behaviour. 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] Does has FTS3 external tokenizers ability now?
Hello! The feature was planning some times ago. Is this released now? 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: datatypes conversion logic error
Hello! On Friday 30 October 2009 18:03:29 Pavel Ivanov wrote: > Don't use word 'terrible' for things that you don't understand > completely. To understand it read once more about SQLite datatypes, > affinity and about cases when datatype is changed automatically in > expressions: http://www.sqlite.org/datatype3.html. It will explain to > you why 1 ='1' doesn't work but a = 1 and a = '1' work. Please see paragraph "Datatypes In SQLite Version 3" in the document. This speaking about "The dynamic type system of SQLite is backwards compatible with the more common static type systems". But some tests show the serious compatible problem. > Although I can admit that your case with trigger and my own tests show > that for some reason affinity rules do not work in the "instead of" > trigger but work in simple selects and all other types of triggers > (I've tested "after insert" and "before insert"). Yes, there are some differents. But documentation of SQLite datatypes is not right correspond to the realization. So documentation is wrong or realization is wrong. 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: datatypes conversion logic error
Hello! On Friday 30 October 2009 18:30:26 Pavel Ivanov wrote: > > You think words "SQLite is backwards compatible" describe > implementation in some way? If this sentence is removed from the > documentation you will be happier and finally read the whole document > to understand how SQLite's type system work in details? The datatype conversion logic is not correct and is not compatible with other RDBMS. So the declared tasks of the SQLite datatypes system is not performed and document is not consistent. I think the text '1' must be equal to numeric 1 always like to standart de-facto for RDBMS. > I agree that this statement can be a bit too much general and is not > applicable in some cases. But all cases are explained in the rest of > the document, so I think it's not quite right to extract just this one > statement and based on it accuse the whole SQLite that it doesn't work > correctly. I did find some datatypes bugs in tclsqlite interface and fix it as http://geomapx.blogspot.com/2009/10/tclsqlite.html Note: the original code produce different results from tclsh shell and from tcl script file. As you can see the datatyping is not ideal now. 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 has FTS3 external tokenizers ability now?
Hello! On Friday 30 October 2009 18:39:19 Dan Kennedy wrote: > > The feature was planning some times ago. Is this released now? > > Yes. See the README.tokenizers file in the full source (tar.gz) > distribution for docs. I don't find any about creating tokenizer on Tcl or other lang. Is it possible? 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: datatypes conversion logic error
Hello!
On Friday 30 October 2009 22:16:27 Simon Slavin wrote:
> > I think the text '1' must
> > be equal to numeric 1 always like to standart de-facto for RDBMS.
>
>
> Personally I think that 1 and 1.0 are the same, and that '1' is never
> the same as either. Some people and some languages feel that 1 is
> never the same as 1.0. It's all a point of view.
Now SQLite think that 1 is equal to '1' in some causes and think
different in other.
$ sqlite3
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(a text);
sqlite> insert into test values(1);
sqlite> insert into test values('1');
sqlite> select * from test where a=1;
1
1
sqlite> select * from test where a='1';
1
1
$ sqlite3
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(a int);
sqlite> insert into test values(1);
sqlite> insert into test values('1');
sqlite> select * from test where a=1;
1
1
sqlite> select * from test where a='1';
1
1
As you can see above 1 is always equal to '1'.
And integer is automatically converted to float:
select 1.0=1
1
But numeric is not converted to text before check:
sqlite> select 1='1';
0
sqlite> select 1.1='1.1';
0
Do you know some language where 1='1' or 1!='1' randomly?
The correct way is to convert numeric values to text before check
of equality.
SQLite datatyping is similar to Tcl by ideology. As example in Tcl:
$ tclsh8.5
tclsh8.5 [/tmp]expr {1==1.0?1:0}
1
tclsh8.5 [/tmp]expr {1=="1.0"?1:0}
1
tclsh8.5 [/tmp]expr {1=="1"?1:0}
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] BUG: datatypes conversion logic error
Hello! On Saturday 31 October 2009 02:31:44 Simon Slavin wrote: > > On 30 Oct 2009, at 9:47pm, Alexey Pechnikov wrote: > > > Now SQLite think that 1 is equal to '1' in some causes and think > > different in other. > > Just like every other language, once you get into it you have to learn > how the language works to understand what's going on. Your problem is > not really with the comparison, it's with what happens to a value when > it is stored in a table. Strongly typed languages usually do one of > two things: > > A) not allow the comparison at all (you get a syntax error from the > compiler) > > B) say that two values of different types never equal one-another You did forget the third way: C) convert values to same type before comparision In C the comparision some of different types is valid: int x =1; double y=2; if (y>x) ... The internal representation of the integer and of the double is different and internal conversation is needed. Most modern languages make this for string values too. In Tcl value can has string or numeric internal representation and is converted before comparison. When values can have the same type and is equal than the result of equality check is positive. When values doesn't have the same type or have the same type but isn't equal than equality check is negative. 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: datatypes conversion logic error
Hello!
$ sqlite3
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(a blob);
sqlite> insert into test values(1);
sqlite> insert into test values('1');
sqlite> create view view_test as select a||'' as a from test;
sqlite> select typeof(a) from view_test;
text
text
sqlite> select * from view_test where a=1;
sqlite> create view view_test2 as select cast(a as text) as a from test;
sqlite> select typeof(a) from view_test2;
text
text
sqlite> select * from view_test2 where a=1;
1
1
And so text '1' in view_test is not equal to text '1' in view_test2. Are you
sure
that current datatypes realisation is right?
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] has used anyone sqlite for production web sites ??
Hello! Add SQLite database to your current site and write to it too. You will see how this work on you environment. About your site load. 60/80 Concurrent users with common query time about 50 ms produce 1200/1600 requests per second. "10 concurrent writers" with "4/7 Insert or Update per request" can produce about 1 kB data by transaction and so 200kB data per second (20 tps as above). By 86400 second per day and 365 day per year your site produce ~6 TB database! This is not a small project :-) May be you speak about user sessions? User session can continue a lot of times but total requests count can be small. I'm using SQLite for few GB databases but with ~1000 concurrent sessions. See some my tests and results here http://geomapx.blogspot.com/2009/09/sqlite-3617-mobigroup2.html Article is russian but you can read sql listings. And I did add a few english comments. I can add that time of the creating new SQLite db connection is about 0.5 ms and so you can open database 2000 times per second on single-core processor. So the total amount of readers can be huge. P.S. GeoIP by SQLite http://geomapx.blogspot.com/2009/10/geoip-sqlite.html Try to download the dataset and test it on your site. 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: datatypes conversion logic error
Hello! On Saturday 31 October 2009 18:55:58 Pavel Ivanov wrote: > > And so text '1' in view_test is not equal to text '1' in view_test2. Are > > you sure > > that current datatypes realisation is right? > > Are you sure that when you compare column in view to something it is > not optimized to get value from table and compare it? In this case > your results are explainable. I'm sure there are the bugs. I'm not sure why. The SQLite shell is part of SQLite project and so the bugs must be reported here. 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] The zlib-compression patch for FTS3
Hello! The code is only prototype but it's simple and work for me. http://geomapx.blogspot.com/2009/11/fts3-compression.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] The zlib-compression patch for FTS3
Hello! See some tests here: http://geomapx.blogspot.com/2009/11/tests-of-zlib-patched-fts3.html The quick results: 1. The metadata compression is not useful. I think the metadata realization is nice. 2. The documents compression decreasing speed of count(*) selects. I think it's error in FTS3 virtual table realisation or in my compression code. 3. The database size by compressing a documents is decreased about 3x factor on my test docset. 4. The speed of selecting documents snippets is de-facto independent of the documents compression. The patch for the only documents compression is added to http://geomapx.blogspot.com/2009/11/fts3-compression.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] The zlib-compression patch for FTS3
Hello! On Sunday 01 November 2009 21:14:05 Scott Hess wrote: > Your results for the contents table pretty much match what I recall. > For that table, compression is helpful without much downside. I seem > to recall that zlib's costs are mostly on the compression side, so for > databases with relatively low update rates, it might be reasonable to > amp up the compression ratio. Gains can really depend on the amount > of data you are working with relative to the size of your page cache The zlib compression for hight update rates is good because may really decrease the disk activity. The processors now have more better performance than disks. 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] Some enhancements for FTS3
Hello!
The FTS3 work fine but is really unfriendly to developers. As example it is
easy to write
tcl interface code for snowball stemmer utility "stemwords" and for stopwords
dictionary
but there are no ways to use it in FTS3. The user functions can be easy writed
on C or
any other language but FTS3 does not work with these.
1. There are no interfaces for stemmer, stopwords dictionary, etc. in the FTS3
extension.
It's very difficult to understand the code of FTS3 extension and change it. Is
it possible to
add calls of user-defined functions for this tasks?
The virtual table creating command may be extended as
sql-command ::= CREATE VIRTUAL TABLE [ database-name .] table-name USING fts3
[( [ argument [, argument, [argument, ...] ]*] )]
argument ::= name | TOKENIZE tokenizer | FUNCTION user_function
tokenizer ::= SIMPLE | PORTER | user-defined
When FUNCTION return null than the word must be ignored else the tokenized word
is
replaced by returned from function.
As example application can bind these functions like as
#!/usr/bin/tclsh8.5
package require sqlite3
sqlite3 db :memory:
proc stopword {word} {
...
}
proc stemmer {word} {
...
}
db function stopword stopword
db function stemmer stemmer
db eval {CREATE VIRTUAL TABLE t USING fts3(content, TOKENIZE icu ru_RU,
FUNCTION stopword, FUNCTION stemmer);}
Of cource we can extend the example above with a synonyms dictionary function
or
internal soundex() function or other.
I think the feature is "must have".
2. The snippet function have now the ability for change snippet text size and
return
very small text fragment. As example the standalone unix diff -u command return
3
lines before and after context and this can easy be changed by command-line
arguments. Yes, application can use self snippet realization on base of
offsets()
information but it's produce additional difficults.
3. The user defined tokenizer function will be very helpful. The tokenizer is
stream
interface and must have the stream position so the user defined tokenizer can
have
the interface like to
tokenizer (document_text, document_position)
This function can be called from xNext() interface function.
I don't sure about the realization and may be the interface will be 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] Compile Virtualtext extension
Hello! On Thursday 05 November 2009 14:47:03 Gary_Gabriel wrote: > I want to use the Virtualtext extension in an application and I > downloaded it from VirtualText extension > <http://mobigroup.ru/files/sqlite-ext/virtualtext/>. Oh, I'm sorry, is needed the http://mobigroup.ru/files/sqlite-ext/iconv/ extension too. Compile as gcc -fPIC -lm -shared ../iconv/iconv.c virtualtext.c -o libsqlitevirtualtext.so I did fix the comment in code. Thanks for your report! 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] Compile Virtualtext extension
Hello! On Friday 06 November 2009 09:31:11 Gary_Gabriel wrote: > Hi Alexey thanks for the prompt answer. I saw your extension and tried > it in the Spatialite GUI. The trial worked well in the GUI and it is > useful for messaging environments. However I am still not sure what the > recommended way of compiling it is. > > - Should it be compiled when compiling SQLite or is it (normally) > compiled alone afterwards? I'm compile it as part of my own SQLite build. The debian lenny repository is here: deb http://mobigroup.ru/debian/ lenny main contrib non-free deb-src http://mobigroup.ru/debian/ lenny main contrib non-free > - What is the order of compiling? > - First http://mobigroup.ru/files/sqlite-ext/iconv/ extension. Compile > as gcc -fPIC -lm -shared ../iconv/iconv.c virtualtext.c -o > libsqlitevirtualtext.so This command compile both extensions and you can already load libsqlitevirtualtext.so library to SQLite. > - Virtualtext extension. Compile as gcc -fPIC -lm -shared virtualtext.c > -o libsqlitevirtualtext.so. See above. > - I assume that both *.c files are put into the SQLite directory for > compiling. Does "../iconv/iconv.c" from the compile commands mean that > iconv stores in a sub-directory? Yes, the file ../iconv/iconv.c is stored in other directory. Of cource you can put iconv.c and iconv.h files from http://mobigroup.ru/files/sqlite-ext/iconv/ to the virtualtext directory and compile as gcc -fPIC -lm -shared iconv.c virtualtext.c -o libsqlitevirtualtext.so The iconv may be useful without virtualtext extension and so I did place it in the different directory. 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] Compile Virtualtext extension
Hello!
1. Compiling as library
ve...@veter-laptop:/mnt/work/chroot/lenny/tmp/sqlite3-3.6.20/ext/virtualtext$ ls
virtualtext.c
ve...@veter-laptop:/mnt/work/chroot/lenny/tmp/sqlite3-3.6.20/ext/virtualtext$
gcc -fPIC -lm -shared virtualtext.c ../iconv/iconv.c -o libsqlitevirtualtext.so
virtualtext.c: In function ‘text_clean_text’:
virtualtext.c:328: warning: assignment makes pointer from integer without a cast
virtualtext.c: In function ‘text_parse’:
virtualtext.c:587: warning: assignment makes pointer from integer without a cast
ve...@veter-laptop:/mnt/work/chroot/lenny/tmp/sqlite3-3.6.20/ext/virtualtext$ ls
libsqlitevirtualtext.so virtualtext.c
If MinGW does not recognize it try these commands:
gcc -fPIC -lm -L../iconv/ -shared virtualtext.c -o libsqlitevirtualtext.so
or
gcc -fPIC -lm -L../iconv/ -shared virtualtext.c ../iconv/iconv.c -o
libsqlitevirtualtext.so
2. Compiling with SQLite
2.1 Patch main.c like to
#ifdef SQLITE_ENABLE_ICU
# include "sqliteicu.h"
#endif
+#ifdef SQLITE_ENABLE_VIRTUALTEXT
+# include "iconv.h"
+#endif
...
+#ifdef SQLITE_ENABLE_VIRTUALTEXT
+ if( !db->mallocFailed && rc==SQLITE_OK ){
+rc = sqlite3VirtualTextInit(db);
+ }
+#endif
+
sqlite3Error(db, rc, 0);
.
/* -DSQLITE_DEFAULT_LOCKING_MODE=1 makes EXCLUSIVE the default locking
2.2. Patch mksqlite3c.tcl
sqliteLimit.h
vdbe.h
vdbeInt.h
+
+ iconv.h
} {
set available_hdr($hdr) 1
}
...
tokenize.c
complete.c
+ iconv.c
+ virtualtext.c
+
main.c
notify.c
.
2.3 Patch Makefile.in
SRC += \
$(TOP)/ext/rtree/rtree.h \
$(TOP)/ext/rtree/rtree.c
+SRC += \
+ $(TOP)/ext/iconv/iconv.h \
+ $(TOP)/ext/iconv/iconv.c \
+ $(TOP)/ext/virtualtext/virtualtext.c
2.4 Build SQLite with option -DSQLITE_ENABLE_VIRTUALTEXT
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] Converting .dbf to SQLite
Hello! On Thursday 12 November 2009 07:30:28 Rich Shepard wrote: >Perhaps. I learned today that only the Winduhs version of OO.o can import > .mdb files; the linux version cannot. Try to read by Linux ODBC and save 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
[sqlite] Degradation of indexing speed (some tests)
Hello! There is the problem with indices on medium-size databases. See tests here: http://geomapx.blogspot.com/2009/11/degradation-of-indexing-speed.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
[sqlite] Select problem with equal compare
1. I try to select: sqlite> select save_date from photo_tags where save_date<2454612.21079943 limit 1 offset 3073; save_date = 2454612.21079943 But 2454612.21079943<2454612.21079943 is wrong result. 2. And I try sqlite> select save_date from photo_tags where save_date=2454612.21079943; sqlite> So, sqlite calculate 2454612.21079943!=2454612.21079943 3. And this query working correctly: sqlite> select save_date from photo_tags where save_date=(select save_date from photo_tags where save_date<2454612.21079943 limit 1 offset 3073); save_date = 2454612.21079943 Table is like CREATE TABLE photo_tags ( save_date REAL, ... ); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select problem with equal compare
> Please read the recent thread "What is quicker" which has a long > discussion on the limitations of floating point. > > A short summary might be, "Unless you assume that any value stored in > floating point format is only an approximation to the input value, you > will run into trouble sooner or later." If you need exact results (and > want to avoid a _lot_ of analysis), use string values or integers > (possibly scaled). I can create table as CREATE TABLE photo_tags ( save_date REAL collate binary, ... ); and compare is right, but is this correct way for date values? > Also, when starting a new thread, please do not just reply to an ongoing > one and change the subject. I'm sorry. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select problem with equal compare
В сообщении от Monday 09 June 2008 17:52:24 Dennis Cote написал(а): > If you > consider dates to be equal when the two dates are the same to within one > second, then you could use that value as your maximum difference. Since > a julian day number has units of days, you can use a value of 1/86400 > days (i.e. 1 second) as your epsilon value. The same value can be used > to test for less than as you are doing in your query. The save_date is > less than the specified value if the difference is greater than this > epsilon value. > > select save_date > from photo_tags > where 2454612.21079943 - save_date > 1/86400; Well, but what is precision of sqlite timer? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
В сообщении от Friday 06 June 2008 03:51:38 P Kishor написал(а):
> http://sqlite.org/contrib/download/extension-functions.c?get=22
$ wget http://sqlite.org/contrib/download/extension-functions.c?get=22
$ mv extension-functions.c?get=22 extension-functions.c
$ gcc -fPIC -shared extension-functions.c -o libsqlitefunctions.so
$ sqlite3 :memory:
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> SELECT load_extension('./libsqlitefunctions.so');
SQL error: ./libsqlitefunctions.so: undefined symbol: log
What can I do?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
> Note: You cannot use these functions from the sqlite3 program, you
> must write your own program using the sqlite3 API, and call
> sqlite3_enable_load_extension. See "Security Considerations" in
> http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions.
I can load other extensions and use them.
> When I try your load from the command line, I get a similar
> (but not the same) error.
> sqlite> SELECT load_extension('./libsqlitefunctions.so');
> SQL error: ./libsqlitefunctions.so: undefined symbol: sqrt
> and I certainly have a sqrt function and use these extensions
> without problems. So I guess it's a symptom of the security feature
> in sqlite that prohibits loading extensions.
sqlite> SELECT load_extension('./libsqlitefunctions.so');
SQL error: ./libsqlitefunctions.so: undefined symbol: log
sqlite> select sqrt(1);
SQL error: no such function: sqrt
> On the chance that log is not defined for your environment.
> I recommend you change the line
> #define HAVE_LOG10 1
> to
> #define HAVE_LOG10 0
> and try again.
I try with no effect.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
> The extension-functions file doesn't actually implement any of the
> math functinos, it simply acts as a glue layer between SQLite and the
> system math library. In this case, it looks like the run-time linker
> that loads the extension can't resolve the call for log() from the
> extension into the math library, resulting in an unresolved link.
>
> On many systems the math library is part of the standard set of libs
> that are imported by the linker for all applications. There are a
> few systems, however, where the math library is not part of the
> standard lib set. On those systems, you need to explicitly tell the
> linker you want the math library made avaliable. You can do that
> by compiling the lib with -lm to import the math library. The math
> lib won't be pulled into the .so, but it will be noted that if the
> run-time linker pulls in the extension, it will also need to pull in
> the math library before it attepts to resolve all the symbols.
>
> At least, in theory.
Thanks for coherent explanation. I am compiling library with switch -lm and
now it's work.
$ gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.so
$ sqlite3 :memory:
sqlite> SELECT load_extension('./libsqlitefunctions.so');
sqlite> select sqrt(16);
4.0
sqlite>
>
> What OS are you trying this on?
Linux Debian Etch (sqlite pakage backported from Debian Lenny).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] table sqlite_extensions
Hello!
How can I create table sqlite_extensions? I read
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions
and try
sqlite> create table sqlite_extensions (
...> load INT, -- the order in which the extension
...>-- is to be loaded/initialized.
...>-- lowest number loads first.
...>-- if tied, order is alphabetical by name.
...>
...> name TEXT, -- name of the extension shared library.
...>-- Note: both the path and suffix is NOT to be included
in name.
...>
...> init TEXT, -- name of the shared library entry point init
function.
...>-- optional, may be null.
...>-- if null, the entry point function will be
constructed
...>-- from name.
...>
...> fini TEXT -- name of the shared library entry point unload
function.
...>-- shared library unloading will be in the reverse
order
...>-- of loading.
...>-- optional, may be null.
...> );
SQL error: object name reserved for internal use: sqlite_extensions
sqlite> insert into sqlite_extensions (name) values ('libSqliteRtree');
SQL error: no such table: sqlite_extensions
And I found this page
http://article.gmane.org/gmane.comp.db.sqlite.general/29803
about table sqlite_shared_libs, but
sqlite> CREATE TABLE sqlite_shared_libs(
...> architecture TEXT,
...> library BLOB
...> );
SQL error: object name reserved for internal use: sqlite_shared_libs
sqlite> select * from sqlite_shared_libs;
SQL error: no such table: sqlite_shared_libs
Best regards, Alexey.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] tuple comparisons, SELECT (1, 2) = (1, 2)
В сообщении от Friday 13 June 2008 16:26:01 Igor Tandetnik написал(а): > "Taylor Basilio" <[EMAIL PROTECTED]> > wrote in message > news:[EMAIL PROTECTED] > > > On Jun 13, 2008, at 7:41 PM, Igor Tandetnik wrote: > >> "Taylor Basilio" <[EMAIL PROTECTED]> > >> wrote in message > >> news:[EMAIL PROTECTED] > >> > >>> Is there a way to do tuple comparisons in SQLite? Are there plans to > >>> support it as "SELECT (1, 2) = (1, 2)" in the future? > >> > >> Why? Is there any DBMS that supports this syntax? > > > > MySQL, PostgreSQL > > Are you sure? I can't find any such thing in MySQL nor ProstreSQL > documentation. The closest I see is > > select ROW(1,2) = ROW(1, 2); > http://www.postgresql.org/docs/8.3/interactive/functions-comparisons.html#R >OW-WISE-COMPARISON It's more shortly: where (a,b,c,d,e)=(1,2,3,4,5) then where a=1 and b=2 and c=3 and d=4 and e=5 and postgres lets it: $ psql template1 -U postgres -h localhost Welcome to psql 8.3.1, the PostgreSQL interactive terminal. template1=# create table test (a int,b int,c int,d int,e int); CREATE TABLE template1=# select * from test where (a,b,c,d,e)=(1,2,3,4,5); a | b | c | d | e ---+---+---+---+--- (0 rows) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How compile libSqliteIcu.so and libSqlitefts3.so
Hello!
I try:
$ gcc -shared -lm icu.c `icu-config --ldflags` -o libSqliteIcu.so
[EMAIL PROTECTED]:~/sqlite/ext/icu$ sqlite3 :memory: "SELECT
load_extension('/usr/lib/sqlite3/libSqliteIcu.so');"
SQL error: error during initialization:
$ gcc -shared fts3.c -o libSqlitefts3.so
[EMAIL PROTECTED]:~/sqlite/ext/fts3$ sqlite3 :memory: "SELECT
load_extension('/usr/lib/sqlite3/libSqlitefts3.so');"
SQL error: /usr/lib/sqlite3/libSqlitefts3.so: undefined symbol:
sqlite3Fts3HashFind
What can I do? My system Linux Debian Etch on x86 arch.
Best regards, Alexey.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree extension - Windows Binary?
В сообщении от Friday 13 June 2008 20:38:42 Andrew Brampton написал(а): > Hi Donald, > > I have a index on both lat and long, but please correct me if I'm wrong, > but I think SQLite will only use a single index per SELECT. So only one of > the index is in use. I think the EXPLAIN command confirms this for me. You can use multiple indices as http://www.mail-archive.com/sqlite-users%40sqlite.org/msg27224.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Client/Srever SQLite
В сообщении от Monday 02 June 2008 19:40:58 Alex Katebi написал(а): > Hi All, > > I am using remote procedure calls (RPC) for SQLite in my application. I > have implemented a few SQLite RPC functions that I needed successfully. > I am wondering if there are other people like me who need this. > If there are enough people who could benefit from this I can make it > available as an open source public domain software. > Then people can add more functions as needed. It's interesting. I think, it can helpful for replication system (as berkeleydb replication). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Client/Srever SQLite
В сообщении от Wednesday 18 June 2008 18:42:25 John Stanton написал(а): > The magic potion is the ability to embed Sqlite in the application > server and avoid IPCs and multiple processes. Why not multiple processes? And what about threads? If Sqlite library is used in multi-threaded application server (for example, AOL Web Server) we can use multiple read threads at same time, collisions may be with write threads (I think, PRAGMA read_uncommitted=1 is usefull methode for creating lock-free read queris, if it correspond to apptication logic). And parallel reads performance is better then read and write in only one thread. If "db timeout" command will work correctly (with Sqlite =<3.5.7 this command not work right) then writes can wait for previos transaction commit/rollback. And your opinion about this? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
В сообщении от Tuesday 17 June 2008 20:59:19 Dennis Cote написал(а):
> I would recommend that you change your database schema an combine your
> timestamp string and subsecond integer field into a single floating
> point julian day number that provides both pieces of information in
> units of days (since the julian day epoch).
There is problem for select one row by time - we must use query
select * from events where time || =julianday('2008-06-16 23:59:59.999');
and index is not used for this query.
Maybe patch http://www.sqlite.org/cvstrac/chngview?cn=5215
resolved problem but I'm not sure.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
В сообщении от Wednesday 18 June 2008 21:32:46 Dennis Cote написал(а):
> Alexey Pechnikov wrote:
> > There is problem for select one row by time - we must use query
> > select * from events where time || =julianday('2008-06-16 23:59:59.999');
> > and index is not used for this query.
> > Maybe patch http://www.sqlite.org/cvstrac/chngview?cn=5215
> > resolved problem but I'm not sure.
>
> There is no problem with this except that it is usually unreliable to do
> equality comparisons with floating point data such as julian day numbers.
>
> When you say "select one row by time" do you mean that there can only be
> one row in each millisecond period, or do you really mean that you want
> all the rows within that one millisecond period, or perhaps you only
> want the first row in that millisecond period.
>
> In any case, the secret is to know what your required time resolution
> is, and use a normal range query to select rows using the start and end
> times of that period. For a one millisecond long period you can use:
>
>select * from events
>where time between julianday('2008-06-16 23:59:59.999')
> and julianday('2008-06-16 23:59:59.999') + 1.0/(8640)
>
> or
>
>select * from events
>where time between julianday('2008-06-16 23:59:59.999')
> and julianday('2008-06-17 00:00:00.000')
>
I find get only _one_ row. I found correspond timestamp by other questions. I
don't want get more than one row.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
В сообщении от Wednesday 18 June 2008 22:44:16 Dennis Cote написал(а): > Alexey Pechnikov wrote: > > I find get only _one_ row. I found correspond timestamp by other > > questions. I don't want get more than one row. > > In that case you would be better off to get the rowid of that row using > the other questions. Then you can get the row of interest directly using > the rowid. > I'm replicating my database using sqlite dump and load or sql queries. I'm not sure that rowid is not different after that. > You should not be linking rows using timestamps, but if you must, it is > important to avoid calculations with and conversions to and from > floating point. If you retrieve a floating point value from one query > and pass it back as a parameter to another query, you should ensure that > it remains as double all along the way. > >double time = sqlite3_column_double(s1, 1); > >sqlite3_bind_double(s2, 1, time); > > If you do this, it should be possible to compare these values for > floating point equality. Can I do it from tcl? And how use index? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Client/Srever SQLite
В сообщении от Wednesday 18 June 2008 23:40:05 John Stanton написал(а): > Alexey Pechnikov wrote: > > В сообщении от Wednesday 18 June 2008 18:42:25 John Stanton написал(а): > >> The magic potion is the ability to embed Sqlite in the application > >> server and avoid IPCs and multiple processes. > > > > Why not multiple processes? And what about threads? If Sqlite library is > > used in multi-threaded application server (for example, AOL Web Server) > > we can use multiple read threads at same time, collisions may be with > > write threads (I think, PRAGMA read_uncommitted=1 is usefull methode for > > creating lock-free read queris, if it correspond to apptication logic). > > And parallel reads performance is better then read and write in only one > > thread. If "db timeout" command will work correctly (with Sqlite =<3.5.7 > > this command not work right) then writes can wait for previos transaction > > commit/rollback. And your opinion about this? > > Alex, > This was the thinking. > The overhead of spawning processes is avoided. Shared caching for > Sqlite is assisted and POSIX locking overhead can be omitted. Open DB > handles can be pooled. Synchronization can use fundamental primitives > such as mutex. IPCs are avoided. Global storage can be used to advantage. Are you speaking about mutexes inside Sqlite or client programm mutex? What is "Global storage"? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
В сообщении от Thursday 19 June 2008 00:22:37 Dennis Cote написал(а):
> Alexey Pechnikov wrote:
> > I'm replicating my database using sqlite dump and load or sql queries.
> > I'm not sure that rowid is not different after that.
>
> If you assign each row an id (and even better make it an integer primary
> key) and use those id columns to link related records then all your
> links will survive across a dump and reload sequence.
>
> However, that shouldn't matter for the a single session with a loaded
> database. Your "other question" is returning timestamp based on some
> criteria, it could simply return a rowid instead of a timestamp. The
> rowid is valid for the entire session with the database.
>
> > Can I do it from tcl? And how use index?
>
> I'm not sure how you handle floating point values in TCL since it is
> typeless. It may not be possible to avoid the conversions. This is even
> more reason to use ranges to locate records by time, and rowids to
> locate specific records.
I have same database on host A and host B. On _both_ hosts I can
insert/update/delete/select rows and periodically synchronize databases.
CREATE TABLE photos
(
name TEXT NOT NULL,
filename TEXT NOT NULL,
hostname TEXT NOT NULL DEFAULT 'A',
save_date REAL COLLATE BINARY,
update_date REAL COLLATE BINARY,
delete_date REAL COLLATE BINARY
);
CREATE TRIGGER photos_delete before delete on photos begin
update photos set delete_date = julianday('now') where rowid=old.rowid and
delete_date IS NULL;
select RAISE (IGNORE);
end;
CREATE TRIGGER photos_insert after insert on photos begin
update photos set save_date = coalesce(save_date, julianday('now')) where
rowid=new.rowid;
end;
CREATE TRIGGER photos_update after update on photos begin
update photos set update_date = julianday('now') where rowid=old.rowid;
end;
For replication I can select all rows updated by selected period and send it
to other hosts.
ROWID can duplicate on host A and host B for different rows. But the chances
of timestamp unique are good. So, I select item by save_date form host A or
host B equally.
P.S. Yes, multy-master replication is very complex but very helpfull... I try
use simple way. Are exists any better choices?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a table size without counting
В сообщении от Thursday 19 June 2008 01:09:42 Teg написал(а): > I doubt the COUNT in Sqlite could be any faster. You really notice the > slowdown though, the first time you access the DB and nothing's in > cache. I think, you can use index directly as create index id_idx on mytable(id); select count(id) from mytable where id>0; explain query plan select count(id) from mytable where id>0; 0|0|TABLE mytable WITH INDEX id_idx It's better than use count(*) with "where" condition. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long delay for delete/select row in database
В сообщении от Thursday 19 June 2008 02:37:57 Dennis Cote написал(а): > I'm not sure if you even need unique id numbers for these records, or > why you are concerned that there might be multiple records with the same > save_date for that matter? Is this table linked to any others? I'm using URL /../[save_date] or /../[ROWID] for my site. Well, I can use second key for host identity as /../[host_id]/[ROWID] and bind user session to the host. P.S. Is any method for multy-master replication of SQLite databases? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multy-master replication of SQLite databases
Hello! Is any method for multy-master replication of SQLite databases? Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multy-master replication of SQLite databases
В сообщении от Thursday 19 June 2008 20:23:22 Stephen Woodbridge написал(а): > Alexey Pechnikov wrote: > > Hello! > > > > Is any method for multy-master replication of SQLite databases? > > http://www.google.com/search?num=100&hl=en&newwindow=1&q=sqlite++replication Are you really saw solution for replication in this links? Especially as sync as async. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multy-master replication of SQLite databases
В сообщении от Thursday 19 June 2008 21:47:12 Stephen Woodbridge написал(а): > Alexey Pechnikov wrote: > > В сообщении от Thursday 19 June 2008 20:23:22 Stephen Woodbridge написал(а): > >> Alexey Pechnikov wrote: > >>> Hello! > >>> > >>> Is any method for multy-master replication of SQLite databases? > >> > >> http://www.google.com/search?num=100&hl=en&newwindow=1&q=sqlite++replica > >>tion > > > > Are you really saw solution for replication in this links? > > Especially as sync as async. > > I did do a search "multi master sqlite replication" and got a different > set of results, but I did not read all the results so there might not be > a solution, but if there is it is likely in these results. I did read a lot of google search results and did not found multy-master raplication solution for Sqlite. These are universal messaging services for distributed applications building but I did not found sqlite-specific. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Client/Srever SQLite
> I use mutexes set up as read or write locks around Sqlite to synchronize > access. In Sqlite 3.5.9 function "db timeout" work fine, I was test it. So internal mutex is enough now, I think. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a table size without counting
В сообщении от Thursday 19 June 2008 17:40:14 Dennis Cote написал(а): > Stephen Woodbridge wrote: > > Well if the index requires fewer page reads then it should be > > proportionally faster. For example if you can only get 5 rows on a page > > but 25 index entries, you have 1/5 the number of pages to read. > > Yes, that is true, but this effect is offset by the fact that these > index pages compete with the table pages for the finite space in the > page cache. The speed of counting is usually only an issue with large > databases where the table doesn't fit in the cache. When loading index > pages to speed the count() you are also ejecting table pages that might > be used for the next data query, and hence slowing that data query down > because it now has to reload the data pages. > > Adding such an index also slows down all insert, update, and delete > operations because they must modify the index as well as the table. I'm testing database size 100 Gb+ on server with 2GB RAM. I need use big databases. For small databases this optimization is not necessary. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite with client/server support
В сообщении от Friday 20 June 2008 03:00:21 Stephen Woodbridge написал(а): > This looks interesting: > http://sqlitedbms.sourceforge.net/index.htm > > Alexey would this work for your multi-master replication? Maybe you > modify this to work for the specific task you have in mind. May be I can solve my problem by this schema: Host A: attach read/write A.db (master A) and read-only B.db (slave B) Host B: attach read/write B.db (master B) and read-only A.db (slave A) Master-slave replication is more simple than master-master and was discussed often here. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
