[sqlite] SQLite with wall enabled what's wrong

[Domingo Alvarez Duarte]

Can someone give any insight here ?

For me the main problem is the wall size that becomes stable at 
387MB (+- 8% of the database, but around 70% the size of the tables it's
manipulating).

And I'm stopping the active reader and calling "PRAGMA wal_checkpoint(FULL);"
manually every 1000 users.

It seems to me that something is not working as advertised.

I'm testing sqlite3 with wall enabled and while running a program that
exclusively open a database I get this on disk while doing it's work:

ls -l
-rw-r--r-- 1   6185697280 Mar 23 21:34 hacker-news-items.db
// 5.7GB
-rw-r--r-- 1   786432 Mar 23 21:34 hacker-news-items.db-shm
// 786KB
-rw-r--r-- 1  ? 397299872 Mar 23 21:34 hacker-news-items.db-wal 
//387MB stable at this size

To be able to compare I dumped all users and users_submitted on a separated
database to measure it's total size
-rw-r--r-- 1  ? 562626560 Mar 23 22:26 hacker-news-items-diff.db
//550MB db with all users and users_submitted  

?  
>  Wed Mar 23 2016 11:10:56 PM CET from "Domingo Alvarez Duarte"
>  Subject: [sqlite] SQLite with wall enabled
>what's wrong
>
>  Hello ! 
> 
> Can someone give any insight here ? 
> 
> For me the main problem is the wall size that becomes stable at 387MB (+-
>8%
> of the database, but around 15 times the size of the tables it's
> manipulating). 
> 
> It seems to me that something is not working as advertised. 
> 
> I'm testing sqlite3 with wall enabled and while running a program that
> exclusively open a database I get this on disk while doing it's work: 
> 
> ls -l
> -rw-r--r-- 1   6185697280 Mar 23 21:34 hacker-news-items.db //
>5.7GB
> -rw-r--r-- 1   786432 Mar 23 21:34 hacker-news-items.db-shm
> // 786KB
> -rw-r--r-- 1  ? 397299872 Mar 23 21:34 hacker-news-items.db-wal //
> 387MB stable at this size 
> 
> To be able to compare I dumped all users and users_submitted on a separated
> database to measure it's total size
> -rw-r--r-- 1  ?? 25014272 Mar 23 22:06 hacker-news-items-diff.db
> //24MB db with all users and users_submitted 
> 
> The schema: 
> 
> CREATE TABLE 'users' (
> ??? 'id' INTEGER PRIMARY KEY NOT NULL,
> ??? 'name' TEXT UNIQUE NOT NULL,
> ??? 'delay' INTEGER DEFAULT 0,
> ??? 'created' INTEGER DEFAULT 0,
> ??? 'karma' INTEGER DEFAULT 0,
> ??? 'about' TEXT DEFAULT ''
> ); 
> 
> CREATE TABLE users_submitted(user_id integer not null, item_id integer not
> null, unique(user_id, item_id)); 
> 
> Some stats about the data: 
> 
> select count(*) from users -> 389514 
> 
> select count(*) from users_submitted -> 9381845 
> 
> select max(length(about)) from users -> 83197 
> 
> select count(*) from users where length(about) > 256 -> 4704 
> 
> select count(*) from users where length(about) > 1024 -> 347 
> 
> select count(*) from users where length(about) > 2048 -> 92 
> 
> select count(*) from users where length(about) > 4096 -> 22 
> 
> select count(*) from (
> select count(*) as submissions
> from users_submitted 
> group by user_id
> )
> where submissions > 10 -> 64639 
> 
> ? 
> 
> The program (with not important parts stripped): 
> 
> 
> local db = SQLite3("hacker-news-items.db");
> db.exec_dml("PRAGMA synchronous =OFF;");
> db.exec_dml("PRAGMA journal_mode = WAL");
> db.exec_dml("CREATE TABLE IF NOT EXISTS sync_all_users(id integer primary
> key, name varchar, sync_date timestamp default current_timestamp);");
> db.exec_dml("insert or ignore into sync_all_users(id, name) values(1,
>'');");
> //only store one record all the time
> 
> local users_update_null = SQLite3Stmt(db, "update users set name=? where
> name=?");
> local users_update_stmt = SQLite3Stmt(db, "update users set delay=?,
>karma=?,
> about=? where name=?");
> local users_submitted_insert_stmt = SQLite3Stmt(db, "insert into
> users_submitted(user_id , item_id) values(?,?)");
> local users_exist_stmt = SQLite3Stmt(db, "select id from users where
> name=?");
> local sync_all_users_stmt = SQLite3Stmt(db, "update sync_all_users set
> name=?, sync_date=CURRENT_TIMESTAMP where id=1;");
> 
> 
> function getUserDataAndUpdate(user_id)
> {
> ??? local user = getUserFromSomeWhere(user_id);
> ??? if(user == "null")
> ??? {
> ??? ??? users_update_null.bind(1, "_null_" + user_id);
> ??? ??? users_update_null.bind(2, user_id);
> ??? ??? users_update_null.step();
> ??? ??? users_update_null.reset();
> ??? ??? return;
> ??? }
> 
> ??? local record = json2var(user);
> ??? local rec_id = type(record) == "table" ? table_rawget(record, "id",
> false) : false;
> ??? if(rec_id)
> ??? {
> ??? ??? users_update_stmt.bind(1, table_rawget(record, "delay", 0));
> ??? ??? users_update_stmt.bind(2, table_rawget(record, "karma", 0));
> ??? ??? users_update_stmt.bind(3, table_rawget(record, "about",
>null));
> ??? ??? users_update_stmt.bind(4, rec_id);
> ??? ??? users_update_stmtstep();
> ??? ??? users_update_stmt.reset();
> ??? ??? 
> ??? ??? local user_id = db.last_row_id();
> ??? ??? 
> ??? ??? local submitted = table_rawget(record, "sub

[sqlite] SQLite with wall enabled what's wrong

[Simon Slavin]

On 23 Mar 2016, at 11:18pm, Domingo Alvarez Duarte  wrote:

> I'm using prepared statements and "sqite3_step == SQLITE_ROW" undeer
> "stmt.next_row()".

Which programming language/environment are you using ?

When you are finished with your statements are you finalizing them ?

_prepare()
_step, _step, _step() ...
_finalize()

Simon.

[sqlite] SQLite with wall enabled what's wrong

[Simon Slavin]

How are you executing your SQLite calls ?  Are you using _step() or _exec() ?

Are you using SQLite calls directly or using a database library ?

Are you doing your INSERT calls in a transaction ?

Simon.

[sqlite] SQLite with wall enabled what's wrong

[Domingo Alvarez Duarte]

Hello !  

Can someone give any insight here ?  

For me the main problem is the wall size that becomes stable at 387MB (+- 8%
of the database, but around 15 times the size of the tables it's
manipulating).  

It seems to me that something is not working as advertised.   

I'm testing sqlite3 with wall enabled and while running a program that
exclusively open a database I get this on disk while doing it's work:  

ls -l
-rw-r--r-- 1   6185697280 Mar 23 21:34 hacker-news-items.db // 5.7GB
-rw-r--r-- 1   786432 Mar 23 21:34 hacker-news-items.db-shm
// 786KB
-rw-r--r-- 1  ? 397299872 Mar 23 21:34 hacker-news-items.db-wal //
387MB stable at this size  

To be able to compare I dumped all users and users_submitted on a separated
database to measure it's total size
-rw-r--r-- 1  ?? 25014272 Mar 23 22:06 hacker-news-items-diff.db
//24MB db with all users and users_submitted  

The schema:  

CREATE TABLE 'users' (
??? 'id' INTEGER PRIMARY KEY NOT NULL,
??? 'name' TEXT UNIQUE NOT NULL,
??? 'delay' INTEGER DEFAULT 0,
??? 'created' INTEGER DEFAULT 0,
??? 'karma' INTEGER DEFAULT 0,
??? 'about' TEXT DEFAULT ''
);  

CREATE TABLE users_submitted(user_id integer not null, item_id integer not
null, unique(user_id, item_id));  

Some stats about the data:  

select count(*) from users -> 389514  

select count(*) from users_submitted -> 9381845  

select max(length(about)) from users -> 83197  

select count(*) from users where length(about) > 256 -> 4704  

select count(*) from users where length(about) > 1024 -> 347  

select count(*) from users where length(about) > 2048 -> 92  

select count(*) from users where length(about) > 4096 -> 22  

select count(*) from (
select count(*) as submissions
from users_submitted 
group by user_id
)
where submissions > 10 -> 64639  

?  

The program (with not important parts stripped):  


local db = SQLite3("hacker-news-items.db");
db.exec_dml("PRAGMA synchronous =OFF;");
db.exec_dml("PRAGMA journal_mode = WAL");
db.exec_dml("CREATE TABLE IF NOT EXISTS sync_all_users(id integer primary
key, name varchar, sync_date timestamp default current_timestamp);");
db.exec_dml("insert or ignore into sync_all_users(id, name) values(1, '');");
//only store one record all the time

local users_update_null = SQLite3Stmt(db, "update users set name=? where
name=?");
local users_update_stmt = SQLite3Stmt(db, "update users set delay=?, karma=?,
about=? where name=?");
local users_submitted_insert_stmt = SQLite3Stmt(db, "insert into
users_submitted(user_id , item_id) values(?,?)");
local users_exist_stmt = SQLite3Stmt(db, "select id from users where
name=?");
local sync_all_users_stmt = SQLite3Stmt(db, "update sync_all_users set
name=?, sync_date=CURRENT_TIMESTAMP where id=1;");


function getUserDataAndUpdate(user_id)
{
??? local user = getUserFromSomeWhere(user_id);
??? if(user == "null")
??? {
??? ??? users_update_null.bind(1, "_null_" + user_id);
??? ??? users_update_null.bind(2, user_id);
??? ??? users_update_null.step();
??? ??? users_update_null.reset();
??? ??? return;
??? }

??? local record = json2var(user);
??? local rec_id = type(record) == "table" ? table_rawget(record, "id",
false) : false;
??? if(rec_id)
??? {
??? ??? users_update_stmt.bind(1, table_rawget(record, "delay", 0));
??? ??? users_update_stmt.bind(2, table_rawget(record, "karma", 0));
??? ??? users_update_stmt.bind(3, table_rawget(record, "about", null));
??? ??? users_update_stmt.bind(4, rec_id);
??? ??? users_update_stmtstep();
??? ??? users_update_stmt.reset();
??? ??? 
??? ??? local user_id = db.last_row_id();
??? ??? 
??? ??? local submitted = table_rawget(record, "submitted", false);
??? ??? if(submitted)
??? ??? {
??? ??? ??? for(local i = 0, len = submitted.len(); i < len; ++i)
??? ??? ??? {
??? ??? ??? ??? //print("submitted", submitted[i]);
??? ??? ??? ??? users_submitted_insert_stmt.bind(1, user_id);
??? ??? ??? ??? users_submitted_insert_stmt.bind(2,
submitted[i]);
??? ??? ??? ??? users_submitted_insert_stmt.step();
??? ??? ??? ??? users_submitted_insert_stmt.reset();
??? ??? ??? }
??? ??? }
??? }
}

function updateAllUsers()
{
??? local stmt_all = db.prepare("select? name from users where name >= ?
and name not like '|_null|_%' escape '|';");
??? local name = db.exec_get_one("select name from sync_all_users where
id=1");
??? stmt_all.bind(1, name);
??? local count = 0;
??? //db.exec_dml("begin;");
??? while(stmt_all.next_row())
??? {
??? ??? name = stmt_all.col(0);
??? ??? print("Now updating user", name, count);
??? ??? getUserDataAndUpdate(name);
??? ??? 
??? ??? if( (++count % 1000) == 0 )
??? ??? {
??? ??? ??? stmt_all.reset(); //stop the unique active reader to do
a checkpoint
??? ??? ??? print("Now processing", name, count);
??? ??? ??? db.exec_dml("PRAGMA wal_checkpoint(FULL);");
??? ??? ??? //db.exec_dml("commit;");
??? ??? ??? //db.exec_dml("begin;");
??? ??? ??? sync_all_users_stmt.bind(1, name);
??? ??? ??? sync_all_users_stmt.step();
??? ??? ??? sync_all_users_stmt.

[sqlite] SQLite RBU the missing piece !

[Domingo Alvarez Duarte]

Hello !  

Thanks for reply !  

It seems that I didn't explained myself properly because your answer doesn't
seem to address the problem !  

Cheers !  
>  Wed Mar 23 2016 07:48:47 PM CET from "Simon Slavin"
>  Subject: Re: [sqlite] SQLite RBU the missing piece !
>
>  On 23 Mar 2016, at 6:45pm, Domingo Alvarez Duarte
> wrote:
> 
>  
>>The sqlite rbu extension concept is interesting but it's missing the
>> generation of the diff files on the fly, we need something like
>> sqlite3_trace, sqlite3_update_hook or better yet a pragma
>> 

>  sqlite_trace() and sqlite_profile() already exist and do what you want.
> 
> 
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] SQLite RBU the missing piece !

[Domingo Alvarez Duarte]

Hello !  

The sqlite rbu extension concept is interesting but it's missing the
generation of the diff files on the fly, we need something like
sqlite3_trace, sqlite3_update_hook or better yet a pragma:  

PRAGMA generate_rbu=ON;  

And with that all insert/update/delete operation would generate the rbu diff
and store in a hidden table sqlite_rbu_log.  

Please add this to the wish list of sqlite !  

Cheers !

[sqlite] SQLite RBU the missing piece !

[Simon Slavin]

On 23 Mar 2016, at 6:45pm, Domingo Alvarez Duarte  wrote:

> The sqlite rbu extension concept is interesting but it's missing the
> generation of the diff files on the fly, we need something like
> sqlite3_trace, sqlite3_update_hook or better yet a pragma

sqlite_trace() and sqlite_profile() already exist and do what you want.



Simon.

[sqlite] sqldiff nowadays

[Alek Paunov]

Hi MM,

Sorry for the late replay - I usually manage to check the list only once 
a day :-(.

On 2016-03-22 16:05, MM wrote:
...

>> If, by chance, you are on something Fedora based, I could give you some
>> hints how to help our lead maintainer - Jan Stanek with the package
>> enhancement myself.

...

>
> Indeed, I am using fedora 23. I have the following rpms installed (though
> we are getting a bit out of scope for this list I suppose):

Great - Let's try to sort the issue out then!

Definitely it is not out of scope - At least with the goal of offloading 
the core sqlite team in mind, we should explain how the Linux 
distributions works, so future _packaging_ issues to be addressed to 
appropriate bug trackers instead of bothering upstream directly (i.e. 
this list).

>
> sqlite-libs-3.11.0-3.fc23.x86_64
> sqlite-3.11.0-3.fc23.x86_64
> sqlite-analyzer-3.11.0-3.fc23.x86_64
> sqlite-doc-3.11.0-3.fc23.noarch
> sqlite-devel-3.11.0-3.fc23.x86_64
>
> none of them has sqldiff.
>

I am aware of that - I have tried to make a remark above that we (the 
interested sqlite/fedora users) should try to assist our package 
maintainer (Jan Stanek) with the inclusion of the tool.

As first step, I prepared a test package set with added sqldiff - To 
test you could try:

dnf -y copr enable decalek/sqlite.tools
dnf -y install sqlite-tools

If it works for you, I will try to enumerate the tasks need to be done, 
so the tool to be included in the main Fedora package repositories.

Regards,
Alek

[sqlite] Article about pointer abuse in SQLite

[Doug Nebeker]

> For obvious security reasons all allocations from the Operating System are 
> pre-initialized to 0x00.  

Time to bash Windows, but according to the docs for HeapAlloc, memory is not 
automatically initialized to 0

https://msdn.microsoft.com/en-us/library/windows/desktop/aa366597(v=vs.85).aspx

This fits with my experience as well.

Doug



-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
Sent: Tuesday, March 22, 2016 8:41 PM
To: SQLite mailing list
Subject: Re: [sqlite] Article about pointer abuse in SQLite


> This discussion on the nature of undefined behaviour code is 
> interesting.  I don't know the reasoning, but it seems that VS6 often 
> initialized things to 0xcd in debug mode and (usually) had memory 
> uninitialized to 0x00 when complied in Release (perhaps 0x00 just 
> happens to be what was on the stack or heap).  I presume this wasn't 
> just to make people suffer  when things don't work the same in debug 
> vs release mode.

The initialization of memory to non-0x00 is a compiler function.

For obvious security reasons all allocations from the Operating System are 
pre-initialized to 0x00.  This is so that your program cannot request a big 
hunk of virtual memory which is full of a predecessor process data and then 
proceed to search it for nifty things like previously used private keys, 
userids, passwords, and so forth.  Such behaviour is required for any Operating 
Systems to obtain any security certification level whatsoever. 




___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] dump only data, change schema, reload

[Domingo Alvarez Duarte]

Hello !  

I had this problem before and asked to add this option to sqlite but somehow
it was not added so now I'm submitting here a patch that adds this
functionality to shell.c updated to today repository.  

Please consider add this to sqlite !  

Cheers !  

?  

--- /third-party/sqlite3/src/shell2.c
+++ /third-party/sqlite3/src/shell0.c
@@ -625,8 +625,6 @@
?? int *aiIndent; /* Array of indents used in MODE_Explain
*/
?? int nIndent;?? /* Size of array aiIndent[] */
?? int iIndent;?? /* Index of current op in aiIndent[] */
-? int dumpDataOnly; /*when dump a database exclude schema */
-? int doStartTransaction; /* when dumping schema only before first record
output "BEGIN;" */
?};
?
?/*
@@ -989,7 +987,6 @@
?? break;
 }
 case MODE_Semi:
-? if((p->cnt == 0) && p->doStartTransaction ) fprintf(p->out,"BEGIN
TRANSACTION;\n");
 case MODE_List: {
?? if( p->cnt++==0 && p->showHeader ){
 for(i=0; idumpDataOnly ){
?? if( strcmp(zTable, "sqlite_sequence")==0 ){
 zPrepStmt = "DELETE FROM sqlite_sequence;\n";
?? }else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
@@ -1844,7 +1840,6 @@
 return 0;
?? }else{
 utf8_printf(p->out, "%s;\n", zSql);
-? }
?? }
?
?? if( strcmp(zType, "table")==0 ){
@@ -1956,7 +1951,6 @@
?? ".dump ?TABLE? ...? Dump the database in an SQL text format\n"
?? " If TABLE specified,
only dump tables matching\n"
?? " LIKE pattern TABLE.\n"
-? ".dumpdata? ?TABLE? ... Like .dump without schema\n"
?? ".echo on|off?? Turn command echo on or off\n"
?? ".eqp on|off??? Enable or disable automatic EXPLAIN
QUERY PLAN\n"
?? ".exit? Exit this program\n"
@@ -2964,8 +2958,7 @@
 rc = shell_dbinfo_command(p, nArg, azArg);
?? }else
?
-? if( c=='d' && ((strncmp(azArg[0], "dump", n)==0) ||
-? (p->dumpDataOnly = (strncmp(azArg[0], "dumpdata", n)==0))) ){
+? if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){
 open_db(p, 0);
 /* When playing back a "dump", the content might appear in an order
 ** which causes immediate foreign key constraints to be violated.
@@ -2985,16 +2978,14 @@
 "SELECT name, type, sql FROM sqlite_master "
 "WHERE sql NOT NULL AND type=='table' AND
name!='sqlite_sequence'"
?? );
-? if(!p->dumpDataOnly){
-??? run_schema_dump_query(p, 
-? "SELECT name, type, sql FROM sqlite_master "
-? "WHERE name=='sqlite_sequence'"
-??? );
-??? run_table_dump_query(p,
-? "SELECT sql FROM sqlite_master "
-? "WHERE sql NOT NULL AND type IN
('index','trigger','view')", 0
-??? );
-? }
+? run_schema_dump_query(p, 
+??? "SELECT name, type, sql FROM sqlite_master "
+??? "WHERE name=='sqlite_sequence'"
+? );
+? run_table_dump_query(p,
+??? "SELECT sql FROM sqlite_master "
+??? "WHERE sql NOT NULL AND type IN ('index','trigger','view')",
0
+? );
 }else{
?? int i;
?? for(i=1; idumpDataOnly){
-? run_table_dump_query(p,
-??? "SELECT sql FROM sqlite_master "
-??? "WHERE sql NOT NULL"
-??? "? AND type IN ('index','trigger','view')"
-??? "? AND tbl_name LIKE shellstatic()", 0
-? );
-??? }
+??? run_table_dump_query(p,
+? "SELECT sql FROM sqlite_master "
+? "WHERE sql NOT NULL"
+? "? AND type IN ('index','trigger','view')"
+? "? AND tbl_name LIKE shellstatic()", 0
+??? );
 zShellStatic = 0;
?? }
 }
@@ -3021,7 +3010,6 @@
 sqlite3_exec(p->db, "PRAGMA writable_schema=OFF;", 0, 0, 0);
 sqlite3_exec(p->db, "RELEASE dump;", 0, 0, 0);
 raw_printf(p->out, p->nErr ? "ROLLBACK; -- due to errors\n" :
"COMMIT;\n");
-??? p->dumpDataOnly = 0; /* reset data only flag */
?? }else
?
?? if( c=='e' && strncmp(azArg[0], "echo", n)==0 ){
@@ -3082,7 +3070,6 @@
 memcpy(&data, p, sizeof(data));
 data.showHeader = 0;
 data.cMode = data.mode = MODE_Semi;
-??? data.doStartTransaction = 1;
 rc = sqlite3_exec(p->db,
??? "SELECT sql FROM"
??? "? (SELECT sql sql, type type, tbl_name tbl_name, name name,
rowid x"
@@ -3119,7 +3106,6 @@
? shell_callback, &data, &zErrMsg);
?? raw_printf(p->out, "ANALYZE sqlite_master;\n");
 }
-??? if(data.cnt) fprintf(p->out, "COMMIT;\n");
?? }else
?
?? if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
@@ -3717,7 +3703,6 @@
 memcpy(&data, p, sizeof(data));
 data.showHeader = 0;
 data.cMode = data.mode = MODE_Semi;
-??? data.doStartTransaction = 1;
 if( nArg==2 ){
?? int i;
?? for(i=0; azArg[1][i]; i++) azArg[1][i] = ToLower(azArg[1][i]);
@@ -3787,7 +3772,6 @@
 }else{
?? rc = 0;
 }
-??? if((rc == 0) && data.cnt) fprintf(data.out, "COMMIT;\n");
?? }else
?
?


>  Wed Mar 23 2016 10:50:09 AM CET from "Luca Ferrari"
>  Subject: [sqlite] dump only data, change schema,
>reload
>
>  Hi all,
> I've a few hundreds sqlite3 database files, all almost 

[sqlite] dump only data, change schema, reload

[Luca Ferrari]

On Wed, Mar 23, 2016 at 1:02 PM, Keith Medcalf  wrote:
>
> Do you mean something like this, perchance?  This sets .mode insert 
>  then does a select ..., which outputs the selected data in the 
> form of INSERT statements into a table called .  The .mode list 
> just allows other commands to be added to the file ...


I already considered the .mode insert, the problem is that while all
databases have the same table columns not all have the same column
order (some columns have been added to existing databases and some
databases where created with new schemas), so having INSERTs without
the column list is not suitable.

Another problem I'm facing is foreign keys from other tables, and
therefore I'm looking at a script that, via table_info, extracts the
column list and builds SQL to export and reimport data.
But it is a lot of work to just drop a constraint, that's why I'm
searching for smarter ways.

Luca

[sqlite] custom collation sequence for numeric columns?

[Eric Hill]

Hey,

SQLite has a brilliant facility for creating custom collations for columns with 
character affinity.  And it won't stop you from creating a custom collation for 
a column with numeric affinity, but your comparison function will never be 
called, it seems.

I wonder if you would ever consider allowing this to work for numeric columns?

The use case I have in mind is "value ordering" - specifying an order other 
than normal alphabetical order (or number order) for a categorical column 
(think days of the week).  While almost certainly less common, numeric columns 
can be semantically categorical, in which case a user-defined ordering is not 
out of the question.  And since it is (IMHO) in the nature of SQLite to make 
values strings when they need to be strings and numbers when they need to be 
numbers, it doesn't seem like something that would be entirely beyond the pale.

Anybody care about this besides me?

Thanks!

Eric

[sqlite] report a bug

[Dan Kennedy]

On 03/23/2016 10:48 AM, ? wrote:
> I want to report a bug.
>
>
> I write a tokenizer named thai?which is working according to the rule of thai 
> vowel,not by space.
>
>
> I build a table using fts5,like this,
> CREATE VIRTUAL TABLE tbl_tha using fts5( key1, key2,TOKENIZE="thai");
>
>
> and then insert a record:
> insert into tbl_tha  values('??','??');
>
>
> querying like this:
>
>
> SQL1:select * from tbl_tha   where tbl_tha  match '';
> SQL2:select * from tbl_tha   where tbl_tha  match '?? ??';
>
>
> SQL2 can query the result,but SQL1 return null;
>
>
> I have confirmed that,the tokenize can split  correctly to
> ??? ???  ??
>
>
> Is that a bug which can not query multi column?

I'm not 100% sure, but I don't think so. Fts5 parses query expressions 
according to the rules described here:

   https://www.sqlite.org/fts5.html#section_3

It can be complicated, but for queries like the above, it comes down to 
"split the input on whitespace". Once it is split, each component of the 
query is passed to the tokenizer. If the tokenizer returns more than one 
token, then these are handled in the same way as a phrase expression by 
fts5. So SQL1 is equivalent to:

  ... MATCH "??? + ??? +  + ??"


whereas SQL2 is:

  ... MATCH "(??? + ???) AND ( + ??)"

Maybe there should be an option for languages like Thai to tell FTS5 to 
handle this kind of thing as:

  ... MATCH "??? AND ??? AND  AND ??"

Dan.

[sqlite] report a bug

[Cezary H. Noweta]

Hello,

On 2016-03-23 07:32, Dan Kennedy wrote:
> On 03/23/2016 10:48 AM, ? wrote:
>> I build a table using fts5,like this,
>> CREATE VIRTUAL TABLE tbl_tha using fts5( key1, key2,TOKENIZE="thai");
>>
>> and then insert a record:
>> insert into tbl_tha  values('??','??');
>>  [...]
>> SQL1:select * from tbl_tha   where tbl_tha  match '';
>> [...]
>> SQL2 can query the result,but SQL1 return null;
>
> [...] So SQL1 is equivalent to:
>
>   ... MATCH "??? + ??? +  + ??"

You can change that behavior by changing 
``fts5_expr.c:fts5ExprGetToken()'' in such a way that it will split a 
text into separate tokens, for example:

change:
==
   for(z2=&z[1]; sqlite3Fts5IsBareword(*z2); z2++);
   pToken->n = (z2 - z);
==

to something like:
==
   for(z2=&z[1]; sqlite3Fts5IsBareword(*z2); z2++) {
 if ( isLastCharOfWord(z2) ) {
   z2 = &z2[numberOfBytesOfChar(z2)];
   break;
 }
   }
   pToken->n = (z2 - z);
==

However you must notice, that such correction breaks rules mentioned by 
Dan Kennedy and have an impact on a whole FTS5 mechanism.

-- best regards

Cezary H. Noweta

[sqlite] report a bug

[叶落天下秋]

I want to report a bug.


I write a tokenizer named thai?which is working according to the rule of thai 
vowel,not by space.


I build a table using fts5,like this,
CREATE VIRTUAL TABLE tbl_tha using fts5( key1, key2,TOKENIZE="thai"); 


and then insert a record:
insert into tbl_tha  values('??','??');


querying like this:


SQL1:select * from tbl_tha   where tbl_tha  match '';
SQL2:select * from tbl_tha   where tbl_tha  match '?? ??';


SQL2 can query the result,but SQL1 return null;


I have confirmed that,the tokenize can split  correctly to 
??? ???  ??


Is that a bug which can not query multi column?


wangjian
Thanks

[sqlite] dump only data, change schema, reload

[Stephan Beal]

On Wed, Mar 23, 2016 at 10:50 AM, Luca Ferrari 
wrote:

> ...The problem is that .dump provides data and schema, while I'd like to
> have data only.
> Other commands like .clone and .backup works pretty much the same, as
> far as I understand.
>
> This leads me to either use awk/sed to manipulate the dump or to
> hard-code single select statements into the script to extract data.
> Is there any smarter way to dump only data in a loadable form?
>


Probably the simplest approach is something like (untested):

alter table original_table rename to foo; -- move the original table
create table original_table (...); -- w/ new schema
insert into original_table (a,b,c) select a,b,c from foo; -- assuming no
transformation needs to take place
drop table foo; -- though you'll probably want to keep the old copy "just
in case"


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] dump only data, change schema, reload

[Luca Ferrari]

Hi all,
I've a few hundreds sqlite3 database files, all almost equals except
for some constraint that has changed during time.
As I know, there is no way to alter constraint (e.g., unique indexes),
and therefore I have to migrate data to a new schema version.
That is possible because data will fit into the same table schema, but
I'm looking for a way to do it massively (and remotely).
So far I was thinking to wrap a shell script to dump the content of a
single database, create the new schema and reload the dump in it.
The problem is that .dump provides data and schema, while I'd like to
have data only.
Other commands like .clone and .backup works pretty much the same, as
far as I understand.

This leads me to either use awk/sed to manipulate the dump or to
hard-code single select statements into the script to extract data.
Is there any smarter way to dump only data in a loadable form?

Thanks
Luca

[sqlite] dump only data, change schema, reload

[Keith Medcalf]

Do you mean something like this, perchance?  This sets .mode insert  
then does a select ..., which outputs the selected data in the form of INSERT 
statements into a table called .  The .mode list just allows other 
commands to be added to the file ...

.output aesodata.sql
.mode list
select 'BEGIN;';
.mode insert Interval
  select *
from Interval
order by Interval;
.mode list
select 'COMMIT;';
select 'BEGIN;';
.mode insert Actual
  select *
from Actual
order by Interval;
.mode list
select 'COMMIT;';
select 'BEGIN;';
.mode insert Forecast
  select *
from Forecast
order by Interval, Projected;
.mode list
select 'COMMIT;';
select 'VACUUM;';
select 'ANALYZE;';

which produces (elided output) like follows:

BEGIN;
INSERT INTO Interval VALUES(1996,9,30,'24',234486);
INSERT INTO Interval VALUES(1996,10,1,'01',234487);
INSERT INTO Interval VALUES(1996,10,1,'02',234488);
INSERT INTO Interval VALUES(1996,10,1,'03',234489);
INSERT INTO Interval VALUES(1996,10,1,'04',234490);
INSERT INTO Interval VALUES(1996,10,1,'05',234491);
INSERT INTO Interval VALUES(1996,10,1,'06',234492);
INSERT INTO Interval VALUES(1996,10,1,'07',234493);
INSERT INTO Interval VALUES(1996,10,1,'08',234494);
INSERT INTO Interval VALUES(1996,10,1,'09',234495);
...
COMMIT;
BEGIN;
INSERT INTO Actual VALUES(234487,1325513676,13.5,4998);
INSERT INTO Actual VALUES(234488,1325513676,13.5,4891);
INSERT INTO Actual VALUES(234489,1325513676,12.0,4831);
INSERT INTO Actual VALUES(234490,1325513676,10.5,4791);
INSERT INTO Actual VALUES(234491,1325513676,10.5,4778);
INSERT INTO Actual VALUES(234492,1325513676,13.5,4862);
INSERT INTO Actual VALUES(234493,1325513676,15.63,5173);
INSERT INTO Actual VALUES(234494,1325513676,19.65,5624);
INSERT INTO Actual VALUES(234495,1325513676,21.0,5773);
INSERT INTO Actual VALUES(234496,1325513676,21.0,5848);
...
COMMIT;
BEGIN;
INSERT INTO Forecast VALUES(234487,234486,1325513676,13.5,4911);
INSERT INTO Forecast VALUES(234488,234487,1325513676,13.5,4771);
INSERT INTO Forecast VALUES(234489,234488,1325513676,10.5,4732);
INSERT INTO Forecast VALUES(234490,234489,1325513676,10.5,4706);
INSERT INTO Forecast VALUES(234491,234490,1325513676,10.5,4730);
INSERT INTO Forecast VALUES(234492,234491,1325513676,13.5,4828);
INSERT INTO Forecast VALUES(234493,234492,1325513676,15.88,5165);
INSERT INTO Forecast VALUES(234494,234493,1325513676,16.3,5569);
INSERT INTO Forecast VALUES(234495,234494,1325513676,21.0,5761);
INSERT INTO Forecast VALUES(234496,234495,1325513676,21.0,5855);
...
COMMIT;
VACUUM;
ANALYZE;

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Luca Ferrari
> Sent: Wednesday, 23 March, 2016 03:50
> To: SQLite
> Subject: [sqlite] dump only data, change schema, reload
> 
> Hi all,
> I've a few hundreds sqlite3 database files, all almost equals except
> for some constraint that has changed during time.
> As I know, there is no way to alter constraint (e.g., unique indexes),
> and therefore I have to migrate data to a new schema version.
> That is possible because data will fit into the same table schema, but
> I'm looking for a way to do it massively (and remotely).
> So far I was thinking to wrap a shell script to dump the content of a
> single database, create the new schema and reload the dump in it.
> The problem is that .dump provides data and schema, while I'd like to
> have data only.
> Other commands like .clone and .backup works pretty much the same, as
> far as I understand.
> 
> This leads me to either use awk/sed to manipulate the dump or to
> hard-code single select statements into the script to extract data.
> Is there any smarter way to dump only data in a loadable form?
> 
> Thanks
> Luca
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS5 "constraint failed"

[Dan Kennedy]

On 03/23/2016 12:06 AM, Domingo Alvarez Duarte wrote:
> Hello !
>
> After seeing several times work/commits on fts5 I decided to try it on a
> table shown bellow, and when trying to populate it I get this error message:
>
>
> sqlite> INSERT INTO fts_idx_items(fts_idx_items) VALUES('rebuild');
> Error: constraint failed
>
> The table has 12,000,000 records and it show the error message after 10
> seconds working, any clue on what can be happening ?

Thanks for testing this. What does "SELECT sqlite_version();" return if 
you run it in the same shell?

Thanks,
Dan.