Re: [sqlite] UPSERT available in pre-release

[Quan Yong Zhai]

Perfect, That’s a big step of SQLite.

I think one of our projects will benefit of the new upsert.

Thanks a lot.



wordcount --all :memory: sqlite3.c

  2.422 wordcount --insert

  2.341 wordcount --insert --without-rowid

  3.610 wordcount --replace

  1.766 wordcount --replace --without-rowid

  1.594 wordcount --upsert

  1.625 wordcount --upsert --without-rowid

  2.171 wordcount --select

  2.281 wordcount --select --without-rowid

  2.423 wordcount --update

  2.391 wordcount --update --without-rowid

  0.375 wordcount --delete

  0.328 wordcount --delete --without-rowid

  0.372 wordcount --query

  0.328 wordcount --query --without-rowid

24.027 wordcount --all



Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Richard Hipp <d...@sqlite.org>
Sent: Saturday, April 21, 2018 3:49:08 AM
To: SQLite mailing list
Subject: Re: [sqlite] UPSERT available in pre-release

Please try again with the latest pre-release snapshot.

On 4/19/18, Quan Yong Zhai <q...@msn.com> wrote:
> Dear Richard,
>
> I modified the wordcount.c in SQLite/test directory, to use the new upsert
> command:
>
>INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE
> SET cnt=cnt+1
>
> Before:
>
>wordcount --all :memory: sqlite3.c
>
>   2.406 wordcount --insert
>
>   2.296 wordcount --insert --without-rowid
>
> After:
>
> wordcount --all :memory: sqlite3.c
>
>   1.701 wordcount --insert
>
>   3.547 wordcount --insert --without-rowid
>
>
>
> As you can see, it’s very strangely ,in the table with rowid, the upsert
> improved a lot, but in the table without rowidd, it’s slower than the origin
> sql.
>
>
>
>
>
> Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows
> 10
>
>
>
> 
> From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf
> of Richard Hipp <d...@sqlite.org>
> Sent: Thursday, April 19, 2018 6:29:55 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] UPSERT available in pre-release
>
> The latest pre-release snapshot [1] contains support for UPSERT
> following the PostgreSQL syntax.  The documentation is still pending.
> Nevertheless, early feedback is welcomed.  You can respond either to
> this mailing list, or directly to me.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Quan Yong Zhai]

Dear Richard,

I modified the wordcount.c in SQLite/test directory, to use the new upsert 
command:

   INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE SET 
cnt=cnt+1

Before:

   wordcount --all :memory: sqlite3.c

  2.406 wordcount --insert

  2.296 wordcount --insert --without-rowid

After:

wordcount --all :memory: sqlite3.c

  1.701 wordcount --insert

  3.547 wordcount --insert --without-rowid



As you can see, it’s very strangely ,in the table with rowid, the upsert 
improved a lot, but in the table without rowidd, it’s slower than the origin 
sql.





Sent from Mail for Windows 10




From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Thursday, April 19, 2018 6:29:55 PM
To: General Discussion of SQLite Database
Subject: [sqlite] UPSERT available in pre-release

The latest pre-release snapshot [1] contains support for UPSERT
following the PostgreSQL syntax.  The documentation is still pending.
Nevertheless, early feedback is welcomed.  You can respond either to
this mailing list, or directly to me.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to optimise a somewhat-recursive query?

[Quan Yong Zhai]

Drop the view, add a new column “last_retrieval_datetime” to the table urls, 
and whenever fetch a page, update this column

发自我的 iPhone

> 在 2018年3月19日，06:15，Clemens Ladisch  写道：
> 
> I have not looked at the schema and queries in detail.
> But at a first glance:
> 
>> CREATE VIEW v_most_recent_lookup_per_url AS
>> ...
>> ORDER BY url_id;
> 
> Drop the ORDER BY; it is useless in a view used in another query,
> and just might slow things down.
> 
>> 011SEARCH TABLE lookups AS l USING AUTOMATIC COVERING INDEX 
>> (is_generic_flag=? AND url_id=?)
> 
> If SQLite thinks that an AUTOMATIC index is useful, you should consider
> creating it explicitly:
>  CREATE INDEX lookup_generid_id_idx ON lookups(is_generic_flag, url_id);
> 
> 
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] If I got many to many relationship data first, how do I insert them to my table?

[Quan Yong Zhai]

对于这样的问题SQLite也没有特别的解决方案。你说的方法几乎就是“标准”的做法，先把customer_id 设置为null, 然后插入该订单，（ 
这样就不会引起外部键检查失败）， 当获取该订单准确的customer_id后，再把它更新。
App的逻辑应考虑到这一点，所有customer_id 为null的订单都是不完整的，相当于草稿。

Zhai

Sent from Mail for Windows 10

From: 邱朗
Sent: 2017年3月31日 18:09
To: SQLite mailing list
Subject: [sqlite] If I got many to many relationship data first, how do I 
insert them to my table?

Say my mobile app has a customer table, a product table, and an order table to 
record who buys what, the order table basically has 2 foreign keys, customer_id 
& product_id.

Now I got the order information first, within in it I can't find the customer 
information in my local sqlite table. As it turns out this is a new customer, 
whose information will come later from another thread/queue. To make things 
even worse the customer id I got from the order information is not the same one 
I used locally. My local customer id is INTEGER PRIMARY KEY (I do record that 
"true customer id" as another column and set index on it)

So how I do record this order information? I can come up with some clumsy 
solution, e.g. if I can't find contact info, I insert a record for it first. 
Later after I get the real information for this customer, I update customer & 
order table. But I was wondering is there any "standard" way for the situation 
like this?
BTW, I did ask the same question at stackoverflow, but because I use sqlite 
(while all the data come from web storing in MySQL) I was wondering if sqlite 
has any specific solution for it.
Thanks,
Qiulang
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Disk I/O utilization

[Quan Yong Zhai]

1. The latest SQLite improved REPLACE operation, It's alot faster now.
2.use SAVEPOINT  to batch transactions. They'll save many IO.

发自我的 Mobile Phone

发件人: Vince Scafaria
发送时间: ‎2016/‎12/‎13 9:24
收件人: 
sqlite-users@mailinglists.sqlite.org
主题: [sqlite] Disk I/O utilization

Our application does a lot of SQLite read/write in a background process. On 
some environments this hammers the disk I/O (e.g. Task Manager -> Disk). Do you 
have suggestions on how we might minimize this?

Perhaps related, one operation that seems to use a lot of the Disk I/O is when 
we use INSERT OR REPLACE INTO. We use this as a workaround for performing an 
UPDATE together with JOIN, which is not allowed by SQLite. I feel as though one 
way we could minimize disk usage would be if we could use the MSSQL syntax of:

UPDATE x
SET ValueA = y.ValueA
FROM TableOne x
JOIN TableTwo y ON y.Id = x.TableTwoId
WHERE COALESCE(x.ValueA,'') != COALESCE(y.ValueA,'');

Instead we do:

INSERT OR REPLACE INTO TableOne ( Id, TableTwoId, ValueA )
SELECT x.Id, x.TableTwoId, y.ValueA
FROM TableOne x
JOIN TableTwo y ON y.Id = x.TableTwoId
WHERE COALESCE(x.ValueA,'') != COALESCE(y.ValueA,'');

Our tables all have a primary key on Id. By including Id in the statement, it 
does a REPLACE. As I understand it, replace includes a behind-the-scenes DELETE 
which triggers a re-indexing. Maybe that is part of the problem, but I don't 
see a workaround. Ideas?

Thank you!

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

Re: [sqlite] CREATE TABLE fails

[Quan Yong Zhai]

CREATE TABLE IF NOT EXISTS abc("abc_tnam" char(129) NOT NULL,

"abc_tid" integer, "abc_ownr" char(129) NOT NULL, "abc_cnam" char(129) NOT NULL

, "abc_cid" smallint, "abc_labl" char(254), "abc_lpos" smallint, "abc_hdr" char(

254), "abc_hpos" smallint, "abc_itfy" smallint, "abc_mask" char(31), "abc_case"

smallint, "abc_hght" smallint, "abc_wdth" smallint, "abc_ptrn" char(31), "abc_bm

ap" char(1), "abc_init" char(254), "abc_cmnt" char(254), "abc_edit" char(31), "a

bc_tag" char(254), PRIMARY KEY ("abc_tnam", "abc_ownr", "abc_cnam"));



Sent from Mail for Windows 10



From: Igor Korot
Sent: 2016年11月22日 21:46
To: Discussion of SQLite Database; 
General Discussion of SQLite Database
Subject: [sqlite] CREATE TABLE fails



Hi, ALL,
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE IF NOT EXISTS abc("abc_tnam" char(129) NOT NULL,
 "abc_tid" integer, "abc_ownr" char(129) NOT NULL, "abc_cnam" char(129) NOT NULL
, "abc_cid" smallint, "abc_labl" char(254), "abc_lpos" smallint, "abc_hdr" char(
254), "abc_hpos" smallint, "abc_itfy" smallint, "abc_mask" char(31), "abc_case"
smallint, "abc_hght" smallint, "abc_wdth" smallint, "abc_ptrn" char(31), "abc_bm
ap" char(1), "abc_init" char(254), "abc_cmnt" char(254), "abc_edit" char(31), "a
bc_tag" char(254) PRIMARY KEY "abc_tnam", "abc_ownr", "abc_cnam");
Error: near ""abc_tnam"": syntax error
sqlite>

Where am I wrong?

Also, just out of curiosity - what symbols are allowed in the table/field name?
Is there any restrictions even for "" syntax?

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

Re: [sqlite] Changing ID's to UUID

[Quan Yong Zhai]

Create a custom function MD5 ,

Custum_uuid()  -> MD5(table_name  || Numeric_ID || "salt string" )

Update table tab1 set id= md5('tab1' || '$' || ID || '$' || 'My custom string')


Update table tab1 set ref_id= md5('tab2' || '$' || ref_id || '$' || 'My custom 
string')

发自我的 Windows Phone

发件人: Cecil Westerhof
发送时间: ‎2016/‎11/‎15 16:03
收件人: SQLite mailing list
主题: [sqlite] Changing ID's to UUID

I have several tables where a numeric ID is used. I want to change
those to UUID's. Is there a smart way to do this, or need I to do this
one by one?

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

Re: [sqlite] Are identical calls to deterministic functions coalesced?

[Quan Yong Zhai]

>Does the query optimizer coalesce identical calls to deterministic functions?

It’s not coalesce identical calls when inserting new record.

CREATE TABLE t(x json);
CREATE INDEX i2 on t(json_extract(x,'$.type')) where json_extract(x,'$.type') 
not null;
explain insert into t values(?);

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 21000  Start at 21
1 OpenWrite  0 2 0 1  00  root=2 iDb=0; t
2 OpenWrite  1 4 0 k(2,,) 00  root=4 iDb=0; i2
3 NewRowid   0 1 000  r[1]=rowid
4 Variable   1 2 000  r[2]=parameter(1,)
5 Affinity   2 1 0 C  00  affinity(r[2])
6 Null   0 3 000  r[3]=NULL
7 Copy   2 5 000  r[5]=r[2]
8 Function0  2 5 4 json_extract(-1)  02  r[4]=func(r[5..6])
9 IsNull 4 14000  if r[4]==NULL goto 14
10Copy   2 9 000  r[9]=r[2]
11Function0  2 9 7 json_extract(-1)  02  
r[7]=func(r[9..10]); i2 column 0
12IntCopy1 8 000  r[8]=r[1]; rowid
13MakeRecord 7 2 300  r[3]=mkrec(r[7..8]); 
for i2
14IsNull 3 16000  if r[3]==NULL goto 16
15IdxInsert  1 3 010  key=r[3]
16MakeRecord 2 1 400  r[4]=mkrec(r[2])
17Insert 0 4 1 t  1b  intkey=r[1] data=r[4]
18Close  0 0 000
19Close  1 0 000
20Halt   0 0 000
21Transaction0 1 5 0  01  usesStmtJournal=0
22TableLock  0 2 1 t  00  iDb=0 root=2 write=1
23String80 6 0 $.type 00  r[6]='$.type'
24String80 100 $.type 00  r[10]='$.type'
25Goto   0 1 000

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

[sqlite] Possible optimization oppurtunity

[Quan Yong Zhai]

Dear SQLite developers,

SQLite does not use covering index on following situation
CREATE TABLE t(x json);
CREATE INDEX i1 on t( json_extract(x, "$.name"));

explain query plan select json_extract(x,"$.name") from t where 
json_extract(x,"$.name")=?;
0|0|0|SEARCH TABLE t USING INDEX i1 (=?)
explain query plan select json_extract(x,"$.name") from t;
0|0|0|SCAN TABLE t

Since “$.name” already extracted and stored in index i1,  Should SQLite using 
covering index on i1 rather than recalculate json_extract(x,”$.name”) every 
time?

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

Re: [sqlite] A possible double bug?

[Quan Yong Zhai]

"The database file format is cross-platform - you can freely copy a database 
between 32-bit and 64-bit systems or between big-endian and little-endian 
architectures.

Quote:
"In your machine, implementation, and SQLite installation the
two value representations may be identical, while in others it may not be."

Does it mean the promise about cross-platform is broken?

发件人: Bernardo Sulzbach<mailto:mafagafogiga...@gmail.com>
发送时间: ‎2016/‎10/‎17 17:19
收件人: 
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
主题: Re: [sqlite] A possible double bug?

On 10/17/2016 07:12 AM, Quan Yong Zhai wrote:
> I can’t reproduce the problem,

As it has already been pointed out, this is normal for floating point
arithmetic. In your machine, implementation, and SQLite installation the
two value representations may be identical, while in others it may not be.

fabs(a - b) is the simplest way to have an idea of how close two values
are, and dividing by the magnitude of one of them (after checking that
it is not zero, etc.) afterwards is also a good idea in some cases.

--
Bernardo Sulzbach
http://www.mafagafogigante.org/
mafagafogiga...@mafagafogigante.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A possible double bug?

[Quan Yong Zhai]

I can’t reproduce the problem,

http://sqlite.org/datatype3.html#type_affinity

“When text data is inserted into a NUMERIC column, the storage class of the 
text is converted to INTEGER or REAL (in order of preference) if such 
conversion is lossless and reversible”
So after  “create table test (foo REAL)”. Foo field have “REAL” type affinity.

INSERT INTO test VALUES(62.027393);   or
INSERT INTO test VALUES(“62.027393”);

Or after prepare “ INSERT INTO test VALUES(?) “
   Bind_text  “62.027393”
   Bind_double  62.027393

In all the four situation,  the  value insert into foo field  is binary 
identical, it’s a 8-bytes REAL value.


#include 
#include 

int main() {
  sqlite3* db;
  sqlite3_stmt* stmt;

  sqlite3_open("double.sqlite", );
  sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS test(foo REAL);", 0, 0, 0);
  sqlite3_prepare(db, "INSERT INTO test VALUES(?)", -1, , 0);
  sqlite3_bind_text(stmt, 1, "62.027393", -1, SQLITE_STATIC);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  sqlite3_prepare(db, "INSERT INTO test VALUES(62.027393)", -1, , 0);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  sqlite3_close(db);
}


e:\Nana>sqlite3 double.sqlite

sqlite> select typeof(foo) from test;
real
real
sqlite> select * from test a cross join test b where a.foo=b.foo;
62.027393|62.027393
62.027393|62.027393
62.027393|62.027393
62.027393|62.027393
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] a small defrag tool for SQLite database file

[Quan Yong Zhai]

Hi guys,
I have a modified version of the “scrub” tool, the origin one found in  SQLite 
source tree /ext/misc/scrub.c
It’s a defrag tool for SQLite database file,  implement a function drop out all 
freelist-pages and rearrange
the other useful databases pages. And it’s blazing faster than ‘Vacuum’.

https://github.com/nanazhai/misc

Hope you like it.

Sent from Mail for Windows 10
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to support 64-bit value in rtree module?

[Quan Yong Zhai]

How about splits the 64 bits integer to two 32 bits integer, and store it in 
two fields of rtree table.

发自我的 Windows Phone

发件人: Gelin Yan
发送时间: ‎2016/‎10/‎11 1:23
收件人: SQLite mailing list
主题: [sqlite] Is it possible to support 64-bit value in rtree module?

Hi All

 As we know, the current rtree module supports 32-bit signed value
only, Is it

possible to support 64-bit? Thanks.

Regards

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

Re: [sqlite] SQLite3 database slowdown at 1GB

[Quan Yong Zhai]

To avoid unnecessary rebalance and  split operation of b-tree, sort the data 
before insert it to table.



Create temp table bf_temp(id integer, offset); -- careful, no primary key

Begin;

Insert into bf_temp(id, offset) values(?,?);

…

create table if not exists bf_fav_nis(id integer primary key, offset integer);

insert into bf_fav_nis select * from bf_temp order by id;

commit;



slight modified version of your program, take 1min19sec to  import 22M rows,



#include 

#include 

#include 



#include "sqlite3.h"



using namespace std;



namespace os {



int getmillicount()

{

 // Something like GetTickCount but portable

 // It rolls over every ~ 12.1 days (0x10/24/60/60)

 // Use GetMilliSpan to correct for rollover

 timeb tb;

 ftime(  );

 int nCount = tb.millitm + (tb.time & 0xf) * 1000;

 return nCount;

}

};



struct SQLite3Statement

{

  sqlite3_stmt *stmt;

  SQLite3Statement(){stmt=NULL;}

  ~SQLite3Statement()

  {

if(stmt) sqlite3_finalize(stmt);

  }

  int bind_exec(std::string , sqlite3_int64 p2)

  {

if(stmt)

{

  sqlite3_bind_text(stmt, 1, p1.c_str(), p1.size(), NULL);

  sqlite3_bind_int64(stmt, 2, p2);

  int rc = sqlite3_step(stmt);

  sqlite3_reset(stmt);

  return rc;

}

return -1;

  }

};



struct SQLite3

{

  sqlite3 *db;

  SQLite3(const char *dbname)

  {

db = NULL;

sqlite3_open_v2(dbname, , SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, 
NULL);

  }

  ~SQLite3()

  {

sqlite3_close(db);

  }



  int exec_dml(const char *szSql)

  {

return sqlite3_exec(db, szSql, NULL, NULL, NULL);

  }



  int prepare(SQLite3Statement , const char *szSql)

  {

return sqlite3_prepare_v2(db, szSql, -1, , NULL);

  }

};



struct file

{

  FILE *fp;

  file(const char *fname, const char *mode)

  {

fp = fopen(fname, mode);

  }

  int tell()

  {

return ftell(fp);

  }

  std::string read_line()

  {

char line[32];

if(!fgets(line, sizeof(line),fp)) line[0] = '\0';;

return line;

  }

};



int main(int argc, char *argv[])

{

  int total_milli_param = 0;

  if(argc > 1)

  {

total_milli_param = atoi(argv[1]);

  }



  SQLite3 db("bf-favorecidos-nis.db");

  db.exec_dml("PRAGMA synchronous = 0;");

  db.exec_dml("drop table if exists bf_fav_nis;");

  db.exec_dml("create temp table if not exists bf_temp(id integer, offset 
integer);");

  db.exec_dml("create table if not exists bf_fav_nis(id integer primary key, 
offset integer);");

  SQLite3Statement stmt;

  db.prepare(stmt, "insert into bf_temp(id, offset) values(?,?)");

  file fd("favorecidos-nis.txt","r");



  db.exec_dml("begin;");

  std::string line;

  auto count = 0, offset = 0,

total_count = 22501426, //total number of line in fd

total_milli = (total_milli_param ? total_milli_param : 190020); //total 
miliseconds spent to process all lines



  auto step_count = 10,

start_milli = os::getmillicount(), last_milli = os::getmillicount();



  
printf("rec_count\tcount_rate\tmilli_rate\tstep_rate\tstep_count\tspent_milli\ttill_now_milli\n");

  while( (line = fd.read_line()).size() )

  {

stmt.bind_exec(line, offset);

++count;

offset = fd.tell();



if( (count % step_count) == 0)

{

  auto now_milli = os::getmillicount();

  auto till_now_milli = now_milli - start_milli;

  auto spent_milli = now_milli - last_milli;

  auto step_rate = step_count / spent_milli;

  auto total_count_rate = ((count + 0.0) / total_count) * 100;

  auto total_milli_rate = ((till_now_milli + 0.0) / total_milli) * 100;

  last_milli = now_milli;

  printf("%d\t%f\t%f\t%d\t%d\t%d\t%d\n", count, total_count_rate, 
total_milli_rate, step_rate * 1000, step_count, spent_milli, till_now_milli);

  db.exec_dml("commit;begin;");

}

  }

  db.exec_dml("insert into bf_fav_nis select * from bf_temp order by id");

  db.exec_dml("commit;");



  auto milli_spent = os::getmillicount() - start_milli;

  printf("%d\t%d\t%d\t%f\n", count, (count / milli_spent) * 1000, milli_spent, 
milli_spent / 60.0);

  return 0;

}



Sent from Mail for Windows 10

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

Re: [sqlite] SQLite 3.15.0 scheduled for 2016-10-14

[Quan Yong Zhai]

http://sqlite.org/draft/rowvalue.html

Quote<<

A "row value" is an ordered list of two or more scalar values. In other words, 
a "row value" is a vector.>>



some word in the above sentence not strictly correct. A “row value” is a tuple, 
not a vector. When your using a tuple, you know how many items in it, and the 
type of each item of it.



Like “SELECT * FROM CUSTOMER WHERE (first_name, last_name, birthday) = ( :1, 
:2, :3)”

Obviously, LHS and RHS is a tuple, sqlite3_bind_values() is not needed for 
above SQL statements.



A vector is an arbitrary length of elements of same type.

Like “SELECT * FROM CUSTOMER WHERE CUST_ID  IN (?, ?, ?, ? ….)”



What you need is sqlite3_bind_vector(), And that’s carray extension work for, 
simple modify a few lines, it will support blobs or your custom type. I don’t 
think a dedicated sqlite3_bind_values() or sqlite3_bind_vector() worth the 
effort.



Zhai



Sent from Mail for Windows 10



From: Dominique Devienne
Sent: 2016年9月23日 23:29
To: SQLite mailing list
Subject: Re: [sqlite] SQLite 3.15.0 scheduled for 2016-10-14



On Fri, Sep 23, 2016 at 5:04 PM, Keith Medcalf  wrote:

> [...] bind the RHS of a IN operator of arbitrary size [...]
>
> A carray won't work for you?


No. carray doesn't support blobs.

The semantic of carray is fundamentally flawed for variable-sized values.
And the lifetime management, or lack thereof, is also fundamentally flawed.

Binding is about providing explicit values to a compiled statement.

Binding a surrogate (a raw pointer...) to those values, to a table-valued
function,
which further assumes a given representation in memory, which for char*
implies
an indirection to some other memory slots which must be null-terminated,
that's
all very "icky" IMHO, and most unlike SQLite's usual clean designs.

My proposed sqlite3_bind_row_value() is I think much closer to SQLite's
norm. FWIW. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite - Delete large table in sqlite

[Quan Yong Zhai]

DELETE FROM tab WHERE rowid in (SELECT rowid from tab ORDER BY … LIMIT 1000);

If SQLite compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT  
http://www.sqlite.org/compile.html#enable_update_delete_limit

DELETE FROM tab WHERE … ORDER BY … LIMIT 1000;

From: Simon Slavin
Sent: 2016年9月9日 23:33
To: SQLite mailing list
Subject: Re: [sqlite] sqlite - Delete large table in sqlite


On 9 Sep 2016, at 6:14pm, Венцислав Русев  wrote:

> I don't know if this will work for you, but can't you delete 1000 rows then 
> another 1000 ... until the table is empty.

If you do do that, each 1000 rows should be deleted inside a transaction.  So

BEGIN
DELETE FROM myTable WHERE ...
END

But SQLite has an optimization in for deleting all rows of a table

DELETE FROM myTable// no "WHERE" clause

which should mean it's faster to do it that way.

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

Re: [sqlite] CREATE FUNCTION

[Quan Yong Zhai]

>From: Rousselot, Richard A
>Sent: 2016年8月23日 22:14
>To: SQLite mailing list
>Subject: Re: [sqlite] CREATE FUNCTION

>Trust, me if I could remove that dumb message I would. :)

There is no such thing as a dumb question.
Packing all the core business logic in SQL language has some advantage,  It’s 
easier to review and maintain, the database designer define the schema and 
implement the SQL “procedures” 
or “functions” to manipulate the data,. Other programmer just use whatever 
language they like to call those
procedures, instead of interweave the host language statements and raw SQL 
statements. It’s more concise, 
the complexity of business logic is hidden, the database consistence is easier 
to preserved. 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exec vs Prepare, step, finalize.

[Quan Yong Zhai]

> From: michael.j.falco...@gmail.com
> Date: Thu, 11 Aug 2016 15:53:39 +1000
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
> 
> I have a self styled routine (similar to the glibc manual example) for
> concatenating the strings values that make up the sql statement. It uses
> memcpy rather than the built in strcat etc.
sqlite3_mprintf  http://www.sqlite.org/c3ref/mprintf.html provide some 
formattingoptions to defending SQL injection. '%Q' to quote string parameters, 
'%w' to quote table name or column name..
>So what exactly is the issue
> with the string building if it does  not include sql derived from user
> input? I'm not quite seeing that bit, sorry or the vagueness
> 
> It does however sound like it would just be better to adopt the three step
> functions as the preferred method in all cases, which is probably what I'm
> trying to come to grips with. I do see the prepare/step/finalize process
> with bound parameters etc is very much preferred in most cases, but
> wondered if those cases where SQL is application provided were an
> exception. I'm leaning towards a no on that now. Thanks for your input and
> in advance or any additional insight.
> 

I am not a security expert, but I think the culprit of SQL injection 
vulnerability in SQLite is not sqlite3_exec(). It's the way  how the SQL 
command text constructed. if you look into the SQLite source code, there are 
many places used sqlite3_exec(), and  theparameters are carefully quoted by 
'%Q', '%q' or '%w'.
  
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

[Quan Yong Zhai]

SQLite version 3.14.0 2016-07-26 04:49:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(x,y,z);
sqlite> create index i1 on t(x,y);
sqlite> explain query plan select * from t where y=100 order by x desc;
0|0|0|SCAN TABLE t USING INDEX i1
sqlite> explain query plan select * from t where y=100 order by x desc limit 1;
0|0|0|SCAN TABLE t
0|0|0|USE TEMP B-TREE FOR ORDER BY
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

[Quan Yong Zhai]

Query plan changed,  It's a bug

发件人: Alessandro Fardin
发送时间: ‎2016/‎7/‎26 16:37
收件人: 
sqlite-users@mailinglists.sqlite.org
主题: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

After updating from sqlite  3.8.11.1 to sqlite 3.13.0.

The Query planner with the same SELECT statement on same table with the
same indexes does not use index at all, but parse the entire table.
Of course this causes a dramatically slow down of the application.

As temporary  work around we have have added to the query the INDEXED BY
energy_d_dateTimeIdx statement.

In sqlite 3.8.11.1 the select was issued by using the
energy_d_dateTimeIdx index

Follows the shema of the table and indexes.



CREATE TABLE IF NOT EXISTS
'hst_energy_d' (
'timestamp' INTEGER,
'pupdate' INTEGER,
'idinstrum' INTEGER NOT NULL,
'enflag' INTEGER NOT NULL DEFAULT 0,
'recdate' TEXT(10) NOT NULL,
'rectime' TEXT(8) NOT NULL,
'Vlnsys' REAL,
'Vl1n' REAL,
'Vl2n' REAL,
'Vl3n' REAL,
'Vllsys' REAL,
'Vl1l2' REAL,
'Vl2l3' REAL,
'Vl3l1' REAL,
'Al1' REAL,
'Al2' REAL,
'Al3' REAL,
'kWsys' REAL,
'kWl1' REAL,
'kWl2' REAL,
'kWl3' REAL,
'kWhac' REAL,
'kWhacn' REAL,
'kvarsys' REAL,
'kvarl1' REAL,
'kvarl2' REAL,
'kvarl3' REAL,
'kvarhn' REAL,
'kvarh' REAL,
'kvarhacC' REAL,
'kvarhacL' REAL,
'kVAsys' REAL,
'kVAl1' REAL,
'kVAl2' REAL,
'kVAl3' REAL,
'PSeq' REAL,
'THDAl1' REAL,
'THDAl2' REAL,
'THDAl3' REAL,
'THDVl1n' REAL,
'THDVl2n' REAL,
'THDVl3n' REAL,
'kWhl1' REAL,
'kWhl2' REAL,
'kWhl3' REAL,
'counter1' REAL,
'counter2' REAL,
'counter3' REAL,
'Hz' REAL,
'An' REAL,
'Hour' REAL,
'Hourn' REAL,
'Alsys' REAL,
'kvarhl1' REAL,
'kvarhl2' REAL,
'kvarhl3' REAL,
'kvarhnl1' REAL,
'kvarhnl2' REAL,
'kvarhnl3' REAL,
'kWhnl1' REAL,
'kWhnl2' REAL,
'kWhnl3' REAL,
'kVAh' REAL,
'kVAhl1' REAL,
'kVAhl2' REAL,
'kVAhl3' REAL,
'PFsys' REAL,
'PFl1' REAL,
'PFl2' REAL,
'PFl3' REAL,
'Wdmd' REAL,
'vardmd' REAL,
'VAdmd' REAL
);
CREATE INDEX IF NOT EXISTS energy_d_dateTimeIdx
ON hst_energy_d (recdate ASC,rectime ASC,idinstrum ASC,enflag ASC);
CREATE INDEX IF NOT EXISTS hst_energy_d_index_timestamp
ON hst_energy_d (timestamp ASC);
CREATE INDEX IF NOT EXISTS hst_energy_d_index_pupdate
ON hst_energy_d (pupdate ASC);

And now the SELECT STATEMENT


SELECT
pupdate,idinstrum,Vlnsys,Vl1n,Vl2n,Vl3n,Vllsys,Vl1l2,Vl2l3,Vl3l1,
Al1,Al2,Al3,kWsys,kWl1,kWl2,kWl3,kWhac,
kWhacn,kvarsys,kvarl1,kvarl2,kvarl3,kvarhn,kvarh,
kvarhacC,kvarhacL,kVAsys,kVAl1,kVAl2 ,kVAl3,PSeq,
THDAl1,THDAl2,THDAl3,THDVl1n,THDVl2n,THDVl3n,
kWhl1 ,kWhl2 ,kWhl3 ,counter1,counter2,counter3,Hz,An,Hour,Hourn,
Alsys,kvarhl1,kvarhl2,kvarhl3,kvarhnl1,kvarhnl2,kvarhnl3,kWhnl1,kWhnl2,
kWhnl3,kVAh,kVAhl1,kVAhl2,kVAhl3,
PFsys,PFl1,PFl2,PFl3,Wdmd,vardmd,VAdmd
FROM hst_energy_d
WHERE enflag = 0 ORDER BY recdate DESC, rectime DESC LIMIT 1;

---

 Alessandro Fardin
 Carlo Gavazzi Controls SpA - R
 Via Cima i Prà, 9/H
 32014 Ponte Nelle Alpi (BL) - ITALY
 Phone: (+39)0437.355811 / Fax: (+39)0437.355880
 Visit our site: www.gavazziautomation.com
 e-mail: alessandro.far...@gavazziacbu.it
---
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REFERENCES from TEMPORARY table to main database table?

[Quan Yong Zhai]

You can maintain a local conditional "index" table via temp trigger, as long as 
there's no other processes modify the permanent table.

Create table t(id integer primary key, x);
Create temp table t1(id integer primary key);
Create temp trigger trig_1 after insert on t begin
Insert into t1 select new.id where new.x > 1;
end;

发件人: Smith, Randall
发送时间: ‎2016/‎7/‎23 9:06
收件人: 
sqlite-users@mailinglists.sqlite.org
主题: [sqlite] REFERENCES from TEMPORARY table to main database table?

Hi.

I'm creating a specialized index table to a persistent table that I need to 
speed up a one-time operation.  This seems like a great application for a 
TEMPORARY table in SQLite, so the index will always be reliably cleaned up.

However, I can't find a way to have REFERENCES to the main table appear in the 
temp table.  I want, e.g.

CREATE TEMPORARY TABLE MySpecializedIndex
(
-- etc.
Symbol INTEGER REFERENCES Symbols(rowid)
)

For which I get an operational error "no such table: temp.Symbols".

Using "REFERENCES main.Symbols" appears to be a syntax error.

Is it impossible to have references from temp tables to main tables?  If so, 
aren't TEMPORARY tables largely useless?

Is there another idiom in SQLite for managing tables that are intended to have 
a short life or which should be reliably cleaned up when the DB closes?

Thanks for any information and suggestions!

Randall.

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

Re: [sqlite] In what case will the online backup api hang for acquiring the Btree lock of the destination on-disk file?

[Quan Yong Zhai]

If remove use of shared cache from file specifications, they will be two 
separated memory databases

发件人: Simon Slavin
发送时间: ‎2016/‎7/‎22 9:32
收件人: SQLite mailing list
主题: Re: [sqlite] In what case will the online backup api hang for acquiring the 
Btree lock of the destination on-disk file?


On 22 Jul 2016, at 2:12am, Yihong Zhan  wrote:

> Thanks Quan Yhong and Stephen. Today I did more experiment and finally 
> narrowed down the cause (but not the root cause) of the problem.

Please remove all use of shared cache from your file specifications and try 
again.

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

Re: [sqlite] In what case will the online backup api hang for acquiring the Btree lock of the destination on-disk file?

[Quan Yong Zhai]

https://www.sqlite.org/faq.html#q5

“But use caution: this locking mechanism might not work correctly if the 
database file is kept on an NFS filesystem. This is because fcntl() file 
locking is broken on many NFS implementations. You should avoid putting SQLite 
database files on NFS if multiple processes might try to access the file at the 
same time.”


Sent from Mail for Windows 10

From: Yihong Zhan
Sent: 2016年7月19日 19:44
To: 
sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] In what case will the online backup api hang for acquiring 
the Btree lock of the destination on-disk file?

Hi sqlite experts,

I am currently incorporating the sqlite online backup API (version 3.8.0.2) 
into our product. I meet a tricky problem, where the sqlite3_backup_step hangs 
at acquiring the BTree’s lock of the destination file.

Specifically, I am backing up in-memory database to NFS disk file. The 
in-memory database is manipulated in the main thread (T1) while the back-up 
runs in another thread (T2). There are only 2 threads in the process and there 
is no other process existing in our product environment.

The stack trace I got in the T2 when the hanging happens is as following:
#0  0x003de740d654 in __lll_lock_wait () from /lib64/libpthread.so.0
#1  0x003de7408f4a in _L_lock_1034 () from /lib64/libpthread.so.0
#2  0x003de7408e0c in pthread_mutex_lock () from /lib64/libpthread.so.0
#3  0x2acc20f6 in pthreadMutexEnter (p=0x23084300) at sqlite3.c:18299
#4  0x2acc1f31 in sqlite3_mutex_enter (p=0x23084300) at sqlite3.c:17812
#5  0x2acd9e4f in lockBtreeMutex (p=0x2aaad8001040) at sqlite3.c:49323
#6  0x2acd9f49 in sqlite3BtreeEnter (p=0x2aaad8001040) at 
sqlite3.c:49410
#7  0x2acdd970 in sqlite3BtreeBeginTrans (p=0x2aaad8001040, wrflag=2) 
at sqlite3.c:52240
#8  0x2ace71b7 in sqlite3_backup_step (p=0x2aaad8000fc0, nPage=50) at 
sqlite3.c:58451
#9  0x2aaace885b37 in ResDB::backup (this=0x2314c3f0, numBlocks=10, 
numPagesPerBlock=50, blockIntervalInMillisec=50) at dbEngine.cpp:659
#10 0x2aaace977373 in ResDBBackupManager::process (this=0x23030880) at 
dbBackupManager.cpp:53
#11 0x2aaace976e3a in DBBackupEventHandler::event (this=0x23040870, 
event=0x2aaad8000990) at dbBackupEventHandler.cpp:35

The sqlite3_backup_step hangs at the very first call and it cannot go any 
further so that nothing is actually back-uped and the file on-disk is still 
empty, which disappoints me very much.
-rw-rw-r-- 1 yhzhan cadence30 2016-07-19 06:59 Interactive.3.rdb

Take a look at the code of sqlite3_backup_step. It looks there are 2 locks for 
both source and destination database. One is for the database itself and the 
other is for the tables (Btree). The flow hangs at sqlite3BtreeBeginTrans

SQLITE_API int sqlite3_backup_step(sqlite3_backup *p, int nPage){
  sqlite3_mutex_enter(p->pSrcDb->mutex);
  sqlite3BtreeEnter(p->pSrc);
  if( p->pDestDb ){
sqlite3_mutex_enter(p->pDestDb->mutex);
  }
  ……
/* Lock the destination database, if it is not locked already. */
if( SQLITE_OK==rc && p->bDestLocked==0
 && SQLITE_OK==(rc = sqlite3BtreeBeginTrans(p->pDest, 2))
){
  p->bDestLocked = 1;
  sqlite3BtreeGetMeta(p->pDest, BTREE_SCHEMA_VERSION, >iDestSchema);
}

Also tried the latest version of sqlite 3.13.0.0, the problem remains.

My back-upcode is quite simple, almost the duplicate of online backup api 
example https://www.sqlite.org/backup.html

bool ResDB::backup(int numBlocks, int numPagesPerBlock, int 
blockIntervalInMillisec)
{
if (!m_sqlBackupDestObj) {
int rc = sqlite3_open(m_fileNameOnNFS.c_str(), _sqlBackupDestObj);
Q_ASSERT(rc == SQLITE_OK);
}

if (!m_backupHandle) {
m_backupHandle = sqlite3_backup_init(m_sqlBackupDestObj, "main", 
m_sqlObj, "main");
Q_ASSERT(m_backupHandle);
}

bool finished = false;
int i = 0;
while (!finished && i < numBlocks) {
while (true) {
int rc = sqlite3_backup_step(m_backupHandle, numPagesPerBlock);
if (rc == SQLITE_OK) {
sqlite3_sleep(blockIntervalInMillisec);
++i;
break;
} else if (rc == SQLITE_DONE) {
finished = true;
break;
} else if (rc == SQLITE_LOCKED) {
sqlite3_sleep(blockIntervalInMillisec);
continue;
} else if (rc == SQLITE_BUSY) {
sqlite3_sleep(blockIntervalInMillisec);
continue;
} else {
finished = true;
Q_ASSERT(false);
break;
}
}
}

if (finished) {
sqlite3_backup_finish(m_backupHandle);
m_backupHandle = NULL;
sqlite3_close(m_sqlBackupDestObj);
m_sqlBackupDestObj = NULL;
}

Re: [sqlite] Problem with savepoints

[Quan Yong Zhai]

It seemed that SQLite can’t create a temporary file,:

PRAGMA TEMP_STORE=MEMORY


From: Quan Yong Zhai<mailto:q...@msn.com>
Sent: 2016年7月6日 22:35
To: Jeff Archer<mailto:jeffarch...@gmail.com>; 
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: RE: [sqlite] Problem with savepoints

PRAGMA JOURNAL_MODE=MEMORY

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

From: Jeff Archer<mailto:jeffarch...@gmail.com>
Sent: 2016年7月6日 22:10
To: 
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: [sqlite] Problem with savepoints

Hi All,
I am a long time SQLite user but have generally used it from C++ in the
past.  In this project however, I am attempting to make a JDBC wrapper and
use from Android a newer version of SQLite (3.12.2) than that provided by
Android 4.2.2 (3.7.11).  Basically, what I have is SQLite compiled with the
NDK into a static library and then a Java wrapper to provide the JDBC
interfaces.  My application is then attempting to use the JDBC interfaces
to operate on the database.  This is generally working.  I can create/open
databases, add/retrieve data, etc. including making use of databases that
are originally created from the native android SQlite (3.7.11)

I have run into a strange issue when attempting to use savepoints.
I create first a transaction with "begin",
then create the outer savepoint "SAVEPOINT SQLITE_SAVEPOINT_1".  This
savepoint will be released after 8 groups have been inserted.
Next, for each of the 8 groups of data a group save point is created
"SAVEPOINT SQLITE_SAVEPOINT_2", data is inserted with sqlite3_step()
returning SQLITE_DONE, then savepoint is released "RELEASE SAVEPOINT
SQLITE_SAVEPOINT_2"
Repeat this successfully for groups 2 through 7.
Group 8 however can only do 26 successful inserts and on the 27th
sqlite3_step() is returning SQLITE_CANTOPEN (14).

This description is of having 128 inserts per group.  The exact number of
inserts seems to be dependent upon the length of the text being inserted
but as long as data does not change the failure point is predictable.

CREATE TABLE variables (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT
NULL, value TEXT NOT NULL)

INSERT INTO variables VALUES (null, '', '')

sqlite version 3.12.2

Thanks,

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

Re: [sqlite] Problem with savepoints

[Quan Yong Zhai]

PRAGMA JOURNAL_MODE=MEMORY

Sent from Mail for Windows 10

From: Jeff Archer
Sent: 2016年7月6日 22:10
To: 
sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Problem with savepoints

Hi All,
I am a long time SQLite user but have generally used it from C++ in the
past.  In this project however, I am attempting to make a JDBC wrapper and
use from Android a newer version of SQLite (3.12.2) than that provided by
Android 4.2.2 (3.7.11).  Basically, what I have is SQLite compiled with the
NDK into a static library and then a Java wrapper to provide the JDBC
interfaces.  My application is then attempting to use the JDBC interfaces
to operate on the database.  This is generally working.  I can create/open
databases, add/retrieve data, etc. including making use of databases that
are originally created from the native android SQlite (3.7.11)

I have run into a strange issue when attempting to use savepoints.
I create first a transaction with "begin",
then create the outer savepoint "SAVEPOINT SQLITE_SAVEPOINT_1".  This
savepoint will be released after 8 groups have been inserted.
Next, for each of the 8 groups of data a group save point is created
"SAVEPOINT SQLITE_SAVEPOINT_2", data is inserted with sqlite3_step()
returning SQLITE_DONE, then savepoint is released "RELEASE SAVEPOINT
SQLITE_SAVEPOINT_2"
Repeat this successfully for groups 2 through 7.
Group 8 however can only do 26 successful inserts and on the 27th
sqlite3_step() is returning SQLITE_CANTOPEN (14).

This description is of having 128 inserts per group.  The exact number of
inserts seems to be dependent upon the length of the text being inserted
but as long as data does not change the failure point is predictable.

CREATE TABLE variables (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT
NULL, value TEXT NOT NULL)

INSERT INTO variables VALUES (null, '', '')

sqlite version 3.12.2

Thanks,

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

[sqlite] optimization: slight faster linked list merge sorter

[Quan Yong Zhai]

Dear SQLIte developers,

Here is a small patch for the linked-list merge sorter in SQLite
to sort N items, 
It will save  about 2*N CPU instructions  by eliminate  unnecessary null 
pointer check,
Regards

make test passed.
fossil diff

Index: src/pcache.c
==
--- src/pcache.c
+++ src/pcache.c
@@ -690,21 +690,22 @@
** Do not both fixing the pDirtyPrev pointers.
*/
static PgHdr *pcacheMergeDirtyList(PgHdr *pA, PgHdr *pB){
   PgHdr result, *pTail;
   pTail = 
-  while( pA && pB ){
+  assert( pA && pB );
+  do{
 if( pA->pgnopgno ){
   pTail->pDirty = pA;
   pTail = pA;
   pA = pA->pDirty;
 }else{
   pTail->pDirty = pB;
   pTail = pB;
   pB = pB->pDirty;
 }
-  }
+  }while( pA && pB );
   if( pA ){
 pTail->pDirty = pA;
   }else if( pB ){
 pTail->pDirty = pB;
   }else{
@@ -748,11 +749,14 @@
   a[i] = pcacheMergeDirtyList(a[i], p);
 }
   }
   p = a[0];
   for(i=1; ipRight==0 || pA->v<=pA->pRight->v );
 assert( pB->pRight==0 || pB->v<=pB->pRight->v );
 if( pA->vv ){
   pTail->pRight = pA;
   pA = pA->pRight;
@@ -254,11 +255,11 @@
   pB = pB->pRight;
   pTail = pTail->pRight;
 }else{
   pA = pA->pRight;
 }
-  }
+  }while( pA && pB );
   if( pA ){
 assert( pA->pRight==0 || pA->v<=pA->pRight->v );
 pTail->pRight = pA;
   }else{
 assert( pB==0 || pB->pRight==0 || pB->v<=pB->pRight->v );
@@ -286,11 +287,14 @@
 aBucket[i] = pIn;
 pIn = pNext;
   }
   pIn = 0;
   for(i=0; ixCompare(
 pTask, , SRVAL(p1), p1->nVal, SRVAL(p2), p2->nVal
 );

@@ -1368,11 +1369,11 @@
   *pp = p2;
   pp = >u.pNext;
   p2 = p2->u.pNext;
   bCached = 0;
 }
-  }
+  }while( p1 && p2 );
   *pp = p1 ? p1 : p2;
   *ppOut = pFinal;
}

 /*
@@ -1432,11 +1433,14 @@
 p = pNext;
   }

   p = 0;
   for(i=0; i<64; i++){
-vdbeSorterMerge(pTask, p, aSlot[i], );
+if( !p )
+  p = aSlot[i];
+else if( aSlot[i] )
+  vdbeSorterMerge(pTask, p, aSlot[i], );
   }
   pList->pList = p;

   sqlite3_free(aSlot);
   assert( pTask->pUnpacked->errCode==SQLITE_OK

[sqlite] Changing the default page_size in 3.12.0

[Quan Yong Zhai]

>From: tonyp at acm.org
>To: SQLite mailing list
>Subject: Re: [sqlite] Changing the default page_size in 3.12.0

>I ran some tests and almost all of my databases (about 100 of them with 
>different content mix and with the biggest one being around 500MB) inflated 
>by a lot while only a couple or so shrunk in size by not much.

Really?

[sqlite] Why does the stored schema strings have their beginning upper-cased?

[Quan Yong Zhai]

VACUUM depends on upper cased "CREATE [UNIQUE] INDEX" sql text in sqlite_master 
when "  pragma case_sensitive_like = on " , or the VACUUM will not work properly

???: Simon Slavin
: ?2016/?3/?2 17:27
???: SQLite mailing list
??: Re: [sqlite] Why does the stored schema strings have their beginning 
upper-cased?


On 2 Mar 2016, at 9:16am, Clemens Ladisch  wrote:

> It does not say _why_ these normalizations are applied, but I'd guess it
> makes searching for specific entries easier.

In an informal, non-programming way, I'll just comment that the style here is 
that keywords are written in upper case and entity names (table names, column 
names, etc.) are written with at least one lower case character.  This doesn't 
help much, but when it does it saves a lot of time.  Like the time I saw 
someone trying to use a table called "cast".

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

[sqlite] Select top 1 from duplicate values

[Quan Yong Zhai]

sqlite> create table mytable(f1 integer,f2 integer,f3 integer);
sqlite> insert into mytable 
values(1,2,8),(1,3,9),(2,4,8),(2,5,2),(3,6,4),(3,7,8),(4,2,4);
sqlite> select f1, max(f2), f3 from mytable where (f3&8)!=0 group by f1;
1|3|9
2|4|8
3|7|8
sqlite>

[sqlite] Multiple Column index

[Quan Yong Zhai]

I supposed your SQL command like  this
   "select * from table where (DateTime>X AND
  DateTimeX AND
DateTimemailto:michele.pradella at selea.com>
: ?2016/?2/?19 16:06
???: sqlite-users at mailinglists.sqlite.org
??: [sqlite] Multiple Column index

Hi all, I have a question about using index with multiple column.
Take this test case
CREATE TABLE test (DateTime BIGINT,CarPlate VARCHAR(255));
CREATE INDEX indexA ON test(DateTime);
CREATE INDEX indexB ON test(CarPlate);
CREATE INDEX indexAB ON test(DateTime,CarPlate);

now if you do
[1] -> EXLPAIN QUERY PLAN SELECT * FROM test WHERE (DateTime=0) AND
(CarPlate='AA000BB')

you obtain:
0|0|0|SEARCH TABLE test USING COVERING INDEX indexAB (DateTime=? AND
CarPlate=?)
So it's good.

if you do
EXLPAIN QUERY PLAN SELECT * FROM test WHERE
(DateTime>1)AND(DateTime<100) AND (CarPlate = 'AA000BB');
0|0|0|SEARCH TABLE test USING INDEX indexB (CarPlate=?)
So is used only the indexB not the index for DateTime, and if you force
it indexAB
0|0|0|SEARCH TABLE test USING COVERING INDEX indexAB (DateTime>? AND
DateTimeX AND
DateTimehttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] determining is-leap-year in sqlite

[Quan Yong Zhai]

My SQLite cte exercise, the output looks like cal in Linux shell:
--
with recursive
  parameters(aday) as (
   select date()
  ),
  months(n, nm) as (
  
values(1,'January'),(2,'February'),(3,'March'),(4,'April'),(5,'May'),(6,'June'),
   
(7,'July'),(8,'August'),(9,'September'),(10,'October'),(11,'November'),(12,'December')
  ),
  daystr(s) as (
 select substr('  ', 1, strftime('%w',aday,'start of 
month')*3-1 ) ||
   substr('  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 
22 23 24 25 26 27 28 29 30 31',
1, strftime('%d',aday,'start of month','+1 month','-1 day')*3)
 from parameters
  ),
  padstr(i,s,r) as (
 select 0,'', s from daystr union all select i+1, substr(r, 1, 
7*3-1),substr(r,7*3+1)
 from padstr where r <>''
  ),
  monthTitle(mt) as (
select nm || ' ' || strftime('%Y', aday)
  from months,parameters
  where n = 0 + strftime('%m',aday)
  ),
  cal(s) as (
select substr('  ',1, (20-length(mt))/2) || mt
  ||char(13,10) ||  'Su Mo Tu We Th Fr Sa' || char(13,10) ||
  group_concat(s,char(13,10)) from padstr,monthTitle where s <>''
  )
select * from cal;


   February 2016
Su Mo Tu We Th Fr Sa
1  2  3  4  5  6
7  8  9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29
Sent from Mail for Windows 10

From: Stephan Beal
Sent: 2016?2?19? 9:00
To: SQLite mailing list
Subject: Re: [sqlite] determining is-leap-year in sqlite

On Fri, Feb 19, 2016 at 1:53 AM, Stephan Beal  wrote:

> It can now optionally mark the current date (but this feature slowed it
> down from 'instant' to 'just under a second or so', possibly due to SQL
> inefficiencies on my part).
>

Trimming the list of years from 100 years to now +/-5 years brought it back
to 'instant'. :) The latest code is online.

--
- 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-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can't understand why I need this cast

[Quan Yong Zhai]

The  '+ - *  / ' operator  trying  to convert text to numeric, so
typeof(strftime('%s', starttime)-(180*60))   -> integer
typeof('1')  -> text
typeof('1' + 0) -> integer
typeof( 1 || 0 ) -> text

???: Rob Willett<mailto:rob.sqlite at robertwillett.com>
: ?2016/?2/?18 20:16
???: SQLite mailing list<mailto:sqlite-users at mailinglists.sqlite.org>
??: Re: [sqlite] Can't understand why I need this cast

Quad,

Thanks, that almost makes sense. What still confuses us is that the
other maths expressions work OK without a cast.

e.g.

strftime('%s' , starttime) - (180 * 60)

and

(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)

and

strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,

all work but

strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))

doesn?t.



So why does

strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))

work and

strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))

not work.

Thanks,

Rob

On 18 Feb 2016, at 11:25, Quan Yong Zhai wrote:

> It looks like strftime('%s',...) should return an integer and
> strftime('%J', ...) should return a double value. But it always return
>  text value.
> 
> ???: Rob Willett<mailto:rob.sqlite at robertwillett.com>
> : ?2016/?2/?18 18:20
> ???: SQLite mailing
> list<mailto:sqlite-users at mailinglists.sqlite.org>
> ??: [sqlite] Can't understand why I need this cast
>
> Hi,
>
> I?m sure this is a really dumb question but I?m struggling to
> understand why the following SQL is needed for what should be a
> trivial
> SQL expression.
>
> I?ve minimised the example down to (hopefully) make it simpler.
>
> I have a table with an ISO date StartTime in it held as a string.
>
> 
> sqlite> .schema test1
> CREATE TABLE "Test1" (
>   "Id" INTEGER NOT NULL,
>   "StartTime" TEXT NOT NULL,
>  PRIMARY KEY("Id")
> );
>
> 
>
> I add in some test ISO date data that *looks* OK
>
> 
> sqlite> select * from test1;
> 1|2011-05-03T05:00:00Z
> 
>
> I run the following to check that the ISO date is between a range of
> seconds, i.e. its greater than three hours ago and less than three
> hours
> in the future. I know this is contrived but this is the minimum test
> case from a far larger query.
>
> select
>  strftime('%s' , starttime) ,
>  strftime('%s' , starttime) - (180 * 60) ,
>   strftime('%s' , starttime) + (180 * 60) ,
>  strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 *
> 60))
> ,
>  ,
>  strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180
> * 60))
> ,
>  strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180
> * 60))
> from
>  test1;
>
> What I expect to see is
>
> 1304398800|1304388000|1304409600|10800|10800|1|0
>
> 1. the ISO start time as secs - Correct
> 2. The ISO start time in secs minus 10800 - Correct
> 3, The ISO start time in secs plus 10800 - Correct
> 4. The value 10800 to check that the maths is correct - Correct
> 5. The value 10800 to check that the maths is correct - Correct
> 6. A check that the ISO value in secs is greater than the current time
> minus 10800 - Correct
> 7. A check that the ISO value in secs is less than the current time
> plus
> 10800 secs - INCORRECT
>
> if I CAST the value of the last column
>
> select
>  strftime('%s' , starttime) ,
>  strftime('%s' , starttime) - (180 * 60) ,
>   strftime('%s' , starttime) + (180 * 60) ,
>  strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 *
> 60))
> ,
>  (strftime('%s' , starttime) + (180 * 60)) - strftime('%s' ,
> starttime)
> ,
>  strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180
> * 60))
> ,
>  strftime('%s' , starttime) <= cast((strftime('%s' , starttime) +
> (180 *
> 60)) as integer)
> from
>  test1;
>
> I get the right answer
>
> 1304398800|1304388000|1304409600|10800|10800|1|1
>
> I?m puzzled as to why this should be. Why would I need to cast an
> integer to an integer. I??m sure there?s an obvious answer but I
> can?t find it.
>
> Any suggestions please?
>
> Thanks,
>
> Rob
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can't understand why I need this cast

[Quan Yong Zhai]

It looks like strftime('%s',...) should return an integer and strftime('%J', 
...) should return a double value. But it always return  text value.

???: Rob Willett
: ?2016/?2/?18 18:20
???: SQLite mailing list
??: [sqlite] Can't understand why I need this cast

Hi,

I?m sure this is a really dumb question but I?m struggling to
understand why the following SQL is needed for what should be a trivial
SQL expression.

I?ve minimised the example down to (hopefully) make it simpler.

I have a table with an ISO date StartTime in it held as a string.


sqlite> .schema test1
CREATE TABLE "Test1" (
 "Id" INTEGER NOT NULL,
 "StartTime" TEXT NOT NULL,
PRIMARY KEY("Id")
);



I add in some test ISO date data that *looks* OK


sqlite> select * from test1;
1|2011-05-03T05:00:00Z


I run the following to check that the ISO date is between a range of
seconds, i.e. its greater than three hours ago and less than three hours
in the future. I know this is contrived but this is the minimum test
case from a far larger query.

select
strftime('%s' , starttime) ,
strftime('%s' , starttime) - (180 * 60) ,
 strftime('%s' , starttime) + (180 * 60) ,
strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * 60))
,
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
,
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
from
test1;

What I expect to see is

1304398800|1304388000|1304409600|10800|10800|1|0

1. the ISO start time as secs - Correct
2. The ISO start time in secs minus 10800 - Correct
3, The ISO start time in secs plus 10800 - Correct
4. The value 10800 to check that the maths is correct - Correct
5. The value 10800 to check that the maths is correct - Correct
6. A check that the ISO value in secs is greater than the current time
minus 10800 - Correct
7. A check that the ISO value in secs is less than the current time plus
10800 secs - INCORRECT

if I CAST the value of the last column

select
strftime('%s' , starttime) ,
strftime('%s' , starttime) - (180 * 60) ,
 strftime('%s' , starttime) + (180 * 60) ,
strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * 60))
,
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
,
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,
strftime('%s' , starttime) <= cast((strftime('%s' , starttime) + (180 *
60)) as integer)
from
test1;

I get the right answer

1304398800|1304388000|1304409600|10800|10800|1|1

I?m puzzled as to why this should be. Why would I need to cast an
integer to an integer. I??m sure there?s an obvious answer but I
can?t find it.

Any suggestions please?

Thanks,

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

[sqlite] Can't understand why I need this cast

[Quan Yong Zhai]

sqlite>Select  typeof(strftime('%s','now'));
text
sqlite>select typeof('1' + 1);
integer

???: Rob Willett
: ?2016/?2/?18 18:20
???: SQLite mailing list
??: [sqlite] Can't understand why I need this cast

Hi,

I?m sure this is a really dumb question but I?m struggling to
understand why the following SQL is needed for what should be a trivial
SQL expression.

I?ve minimised the example down to (hopefully) make it simpler.

I have a table with an ISO date StartTime in it held as a string.


sqlite> .schema test1
CREATE TABLE "Test1" (
 "Id" INTEGER NOT NULL,
 "StartTime" TEXT NOT NULL,
PRIMARY KEY("Id")
);



I add in some test ISO date data that *looks* OK


sqlite> select * from test1;
1|2011-05-03T05:00:00Z


I run the following to check that the ISO date is between a range of
seconds, i.e. its greater than three hours ago and less than three hours
in the future. I know this is contrived but this is the minimum test
case from a far larger query.

select
strftime('%s' , starttime) ,
strftime('%s' , starttime) - (180 * 60) ,
 strftime('%s' , starttime) + (180 * 60) ,
strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * 60))
,
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
,
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
from
test1;

What I expect to see is

1304398800|1304388000|1304409600|10800|10800|1|0

1. the ISO start time as secs - Correct
2. The ISO start time in secs minus 10800 - Correct
3, The ISO start time in secs plus 10800 - Correct
4. The value 10800 to check that the maths is correct - Correct
5. The value 10800 to check that the maths is correct - Correct
6. A check that the ISO value in secs is greater than the current time
minus 10800 - Correct
7. A check that the ISO value in secs is less than the current time plus
10800 secs - INCORRECT

if I CAST the value of the last column

select
strftime('%s' , starttime) ,
strftime('%s' , starttime) - (180 * 60) ,
 strftime('%s' , starttime) + (180 * 60) ,
strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * 60))
,
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
,
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,
strftime('%s' , starttime) <= cast((strftime('%s' , starttime) + (180 *
60)) as integer)
from
test1;

I get the right answer

1304398800|1304388000|1304409600|10800|10800|1|1

I?m puzzled as to why this should be. Why would I need to cast an
integer to an integer. I??m sure there?s an obvious answer but I
can?t find it.

Any suggestions please?

Thanks,

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

[sqlite] the default synchronous setting in WAL mode is "FULL", not "NORMAL"

[Quan Yong Zhai]

http://sqlite.org/pragma.html#pragma_synchronous
Says:?NORMAL is the default when in WAL mode.?
But,

sqlite3 e:\files\t.db
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
sqlite> pragma journal_mode;
wal
sqlite> pragma synchronous; -- ???
2

sqlite> .open e:\\files\\t1.db
sqlite> pragma journal_mode;
delete
sqlite> pragma journal_mode=wal;
wal
sqlite> pragma synchronous;
2
sqlite>


Sent from Mail for Windows 10

[sqlite] A question

[Quan Yong Zhai]

Create an r-tree index on poitable(poiid), the query time of your SQL will drop 
to 1ms.
http://www.sqlite.org/rtree.html

???: ???
: ?2016/?2/?17 16:34
???: sqlite-users at mailinglists.sqlite.org
??: [sqlite] A question

I have a question:

there are two tables:
CREATE TABLE poiTable ( poiId INTEGER NOT NULL, catId INTEGER NOT NULL, 
mortonCode INTEGER NOT NULL, versionId INTEGER NOT NULL, iconSetId INTEGER , 
catIconSetId INTEGER , brandIconSetId INTEGER , regionId INTEGER , attrBitMask 
INTEGER , attrDisplayBitMask INTEGER , attributeBlob BLOB ,
 primary key (poiId, catId)
);

table index:
CREATE INDEX idx_poiTable_0 ON poiTable(catId);

CREATE TABLE poiVirtualTileTable (tileId INTEGER NOT NULL, minId INTEGER NOT 
NULL, maxId INTEGER NOT NULL, versionId INTEGER NOT NULL, isDirty INTEGER NOT 
NULL,
 primary key (tileId, minId)
);

The total number of records of poiTable is 383826;
The total number of records of poiVirtualTileTable is 9791;

sql1:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM poiTable a WHERE a.catId IN(8449,8450,8452)
Execute sql1 consumes 500-600ms, returned 20232 records

sql2:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM poiTable a,poiVirtualTileTable b WHERE a.poiId >= b.minId AND a.poiId <= 
b.maxId AND b.tileId = 557467343
Execute sql2 , returned 157 records

sql3:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM poiTable a,poiVirtualTileTable b WHERE a.poiId >= b.minId AND a.poiId <= 
b.maxId AND b.tileId = 67430686
Execute sql3 , returned 92231 records

sql4:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM nds111004.poiTable a,nds111004.poiVirtualTileTable b WHERE a.poiId >= 
b.minId AND a.poiId <= b.maxId AND b.tileId=557467343 AND a.catId 
IN(8449,8450,8452)
Execute sql4 consumes 500-600ms, returned 5847 records

sql5:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM nds111004.poiTable a,nds111004.poiVirtualTileTable b WHERE a.poiId >= 
b.minId AND a.poiId <= b.maxId AND b.tileId=67430683 AND a.catId 
IN(8449,8450,8452)
Execute sql5 consumes 500-600ms, returned 14 records

question:
the records returned of sql5 is less than sql4, but the time is almost the same 
?
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query Benchmark

[Quan Yong Zhai]

http://sqlite.org/rtree.html

To improve your query performance, you need a r-tree index on DateTime

???: Michele Pradella
: ?2016/?2/?12 17:28
???: sqlite-users at mailinglists.sqlite.org
??: Re: [sqlite] query Benchmark

I'm already using my software. The scope of test my query with sqlite
shell is to have a test environment without other interaction, just to
test witch is the fast version of my query to use in my software.
Another question is, if I have a query like this:
SELECT *field* FROM car_plates WHERE (*field* LIKE 'EX011%') AND
(DateTime>=14550588) AND (DateTime<=14552315) ORDER BY
DateTime;

If I try to force the use of an index on *field *but I think sqlite
can't use it, is it right?

Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.pradella at selea.com* 
*http://www.selea.com*
Il 12/02/2016 10.23, Simon Slavin ha scritto:
> On 12 Feb 2016, at 8:47am, Michele Pradella  
> wrote:
>
>> Hi all, is there a way to make a benchmark of queries to check which version 
>> is faster? I'm using sqlite shell, the question is about on how to make 
>> repetitive tests in the same conditions (for example I need to totally 
>> disable cache to avoid different results the second time query is executed).
> Using the ".timer ON" command in the command-line shell, as you write, is the 
> best way to do timing.  But there are many levels of cache in your computer 
> and there's no way to disable them all.  You will definitely get timings 
> influenced by interactions.
>
> It looks like you are trying to make commands run as fast as possible.  
> Unless you are writing an academic paper on theory, this is probably the 
> wrong thing to do.  Write your software and see if it runs fast enough.  Only 
> if it runs too slowly do you need to start worrying about speed.
>
> We can advise on good SQL commands and good indexes for you if you do need to 
> increase your speed.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] load a .db3 file into in-memory database

[Quan Yong Zhai]

>From: ???
>Sent: 2016?1?19? 14:04
>To: sqlite-users at mailinglists.sqlite.org
>Subject: Re: [sqlite] load a .db3 file into in-memory database

>Now i want to load the whole database into memory. Is there a direct method to 
>do it? Should i load record one by one by myself?

Using the SQLite Online Backup API
http://www.sqlite.org/backup.html

[sqlite] 答复: Best way to terminate a dead-transaction: commit or rollback?

[Quan Yong Zhai]

I prefer to COMMIT TRANSACTION, because
1. As a rule, all successfully executed transactions should be commit, 
otherwise should  be rollback.
2. To rollback a transaction with no  errors  occurred will  misguide  some 
developers.
3. if someone puts a writing statement or function in the " read only" 
transaction incidentally, there will be a difference, "ROLLBACK TRANSACTION"  
always return a  misguided SQLITE_OK, but "COMMIT" will inform you 
SQLITE_SNAPSHOT_BUSY or SQLITE_LOCK or something else additionally.

My 2 cents.

???: Olivier Mascia
: ?2016/?1/?14 23:54
???: SQLite mailing list
??: [sqlite] Best way to terminate a dead-transaction: commit or rollback?

Hello,

Let a transaction (started with BEGIN TRANSACTION) which did only reads.
Is it any better to end it by COMMIT TRANSACTION or ROLLBACK TRANSACTION, or is 
it completely insignificant?

--
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om

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

[sqlite] 答复: Do you mean utf8_fprintf on this commit ?

[Quan Yong Zhai]

Hi, Mistachkin
Thanks for you fix the shell
There is still some issue in the sqlite shell,  in windows sqlite3_open need 
file name  encoding with utf-8, and fopen need ansi , if the file name in  
command line include multi-byte characters, there will be a problem, eg

Shell.exe ???.db
// not work, sqlite3_open need utf8 string

In the sqlite shell:
sqlite>.open ??.db
//work fine, because the string ???.db" encoding with utf8,and sqlite3_open 
take it

sqlite>.read ??.sql
//not work , fopen need filename with ansi characters

???: Joe Mistachkin
: ?2016/?1/?1 4:08
???: 'SQLite mailing list'
??: Re: [sqlite] Do you mean utf8_fprintf on this commit ?


Domingo Alvarez Duarte wrote:
>
> On this commit https://www.sqlite.org/src/info/8cf01fff232d15d3 do you
> mean "utf8_fprintf" instead of "utf8_printf" ?
>

No, I meant to use the existing "utf8_printf" function (and the new
"raw_printf" macro).

--
Joe Mistachkin

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

[sqlite] A small patch for the SQLite shell in windows.

[Quan Yong Zhai]

Thank you Richard.
The patch work very well

Here is a simple test:

E:\sqlite-src-3090200>chcp
?: 936  ---active code page: 936

E:\sqlite-src-3090200>shell
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(x,y);
sqlite> insert into t values('??','??');
sqlite> insert into t values('ABCEDFG','');
sqlite> select length(x),length(y) from t;
2|2
7|4
sqlite> select substr(x,2,1), substr(y,3,1) from t;
?|
B|?
sqlite> select length('');
4
sqlite> select substr('',2,3);
???
sqlite> select instr('','?');
4

From: Richard Hipp
Sent: 2015?12?30? 21:38
To: Quan Yong Zhai
Cc: SQLite mailing list
Subject: Re: [sqlite] A small patch for the SQLite shell in windows.

Please test the alternative patch found at
https://www.sqlite.org/src/info/a0a08b8c0bbd4d71 and let me know
whether or not the alternative patch fixes your problem.

On 12/30/15, Quan Yong Zhai  wrote:
>>From: Richard Hipp
>>Sent: 2015?12?30? 20:21
>>To: SQLite mailing list
>>Subject: Re: [sqlite] A small patch for the SQLite shell in windows.
>
>>Please send a unified diff, generated using the -u option to the
>>"diff" command.? Even better would be the "-U 8" option, to include
>>more context.
>
> diff -U 8 src/shell.c /home/nana/shell.c
> diff.txt--cut here--


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] A small patch for the SQLite shell in windows.

[Quan Yong Zhai]

>From: Richard Hipp
>Sent: 2015?12?30? 20:21
>To: SQLite mailing list
>Subject: Re: [sqlite] A small patch for the SQLite shell in windows.

>Please send a unified diff, generated using the -u option to the
>"diff" command.? Even better would be the "-U 8" option, to include
>more context.

diff -U 8 src/shell.c /home/nana/shell.c
diff.txt--cut here--

--- src/shell.c 2015-11-03 01:44:00.0 +0800
+++ /home/nana/shell.c  2015-12-28 01:36:42.643546200 +0800
@@ -809,21 +809,42 @@

 /*
 ** This is the callback routine that the shell
 ** invokes for each row of a query result.
 */
 static int shell_callback(
   void *pArg,
   int nArg,/* Number of result columns */
-  char **azArg,/* Text of each result column */
+  char **azArgZ,/* Text of each result column */
   char **azCol,/* Column names */
   int *aiType  /* Column types */
 ){
   int i;
+#if defined(_WIN32) || defined(WIN32)
+  char** azArg = malloc(sizeof(char*)*nArg);
+  for (i = 0;i < nArg;i++) {
+ if (azArgZ[i] == 0)
+ azArg[i] = 0;
+ else {
+ int nlen = MultiByteToWideChar(CP_UTF8, 0, azArgZ[i], -1, 
NULL, 0);
+ assert(nlen > 0);
+ WCHAR* buff = (WCHAR*)malloc(nlen * sizeof(WCHAR));
+ (void)MultiByteToWideChar(CP_UTF8, 0, azArgZ[i], -1, buff, 
nlen);
+ int ilen = WideCharToMultiByte(CP_ACP, 0, buff, nlen, NULL, 
0, NULL, NULL);
+ assert(ilen > 0);
+ char* buff1 = (char*)malloc(ilen);
+ (void)WideCharToMultiByte(CP_ACP, 0, buff, nlen, buff1, ilen, 
NULL, NULL);
+ azArg[i] = buff1;
+ free(buff);
+ }
+  }
+#else
+  char** azArg = azArgZ;
+#endif
   ShellState *p = (ShellState*)pArg;

   switch( p->mode ){
 case MODE_Line: {
   int w = 5;
   if( azArg==0 ) break;
   for(i=0; i0 ) fprintf(p->out, "%s", p->colSeparator);
 fprintf(p->out,"%s",azArg[i] ? azArg[i] : p->nullValue);
   }
   fprintf(p->out, "%s", p->rowSeparator);
   break;
 }
   }
+#if defined(_WIN32) || defined(WIN32)
+  for (i = 0;i < nArg;i++) {
+ if (azArg[i])
+ free(azArg[i]);
+  }
+  free(azArg);
+#endif
   return 0;
 }

 /*
 ** This is the callback routine that the SQLite library
 ** invokes for each row of a query result.
 */
 static int callback(void *pArg, int nArg, char **azArg, char **azCol){
@@ -4247,19 +4275,35 @@
   memcpy(zSql+nSql, zLine, nLine+1);
   nSql += nLine;
 }
 if( nSql && line_contains_semicolon([nSqlPrior], nSql-nSqlPrior)
 && sqlite3_complete(zSql) ){
   p->cnt = 0;
   open_db(p, 0);
   if( p->backslashOn ) resolve_backslashes(zSql);
-  BEGIN_TIMER;
-  rc = shell_exec(p->db, zSql, shell_callback, p, );
-  END_TIMER;
+#if defined(_WIN32) || defined(WIN32)
+ int nlen = MultiByteToWideChar(CP_ACP, 0, zSql, -1, 0, 0);
+ assert(nlen > 0);
+ WCHAR* buff = (WCHAR*)malloc(nlen * sizeof(WCHAR));
+ (void)MultiByteToWideChar(CP_ACP, 0, zSql, -1, buff, nlen);
+ int ilen = WideCharToMultiByte(CP_UTF8, 0, buff, nlen, NULL, 0, NULL, 
NULL);
+ assert(ilen > 0);
+ char* buff1 = (char*)malloc(ilen);
+ (void)WideCharToMultiByte(CP_UTF8, 0, buff, nlen, buff1, ilen, NULL, 
NULL);
+ free(buff);
+ BEGIN_TIMER;
+ rc = shell_exec(p->db, buff1, shell_callback, p, );
+ END_TIMER;
+ free(buff1);
+#else
+ BEGIN_TIMER;
+ rc = shell_exec(p->db, zSql, shell_callback, p, );
+ END_TIMER;
+#endif
   if( rc || zErrMsg ){
 char zPrefix[100];
 if( in!=0 || !stdin_is_interactive ){
   sqlite3_snprintf(sizeof(zPrefix), zPrefix, 
"Error: near line %d:", startline);
 }else{
   sqlite3_snprintf(sizeof(zPrefix), zPrefix, "Error:");
 }



--cut here

[sqlite] A small patch for the SQLite shell in windows.

[Quan Yong Zhai]

Hi,
I have a small patch for the SQLite Shell 3.9.2, it convert sql command text to 
utf-8 before sending to SQLite engine,
And convert the result text back to default code page after sqlite3_exec.

Before patch( SQLite shell in windows 10 Chinese version): 

SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select length('');
6
sqlite> select substr('',1,1);

sqlite> select substr('',2,1);

sqlite> select substr('',3,1);

sqlite> select substr('',4,1);
?
sqlite> select instr('','?');
3

After patch:

sqlite> select length('');
4
sqlite> select substr('',1,1);
?
sqlite> select substr('',2,1);
?
sqlite> select substr('',3,1);
?
sqlite> select substr('',4,1);
?
sqlite> select instr('','?');
2
sqlite>

---diff.txtcut 
here---
817c817
<   char **azArg,/* Text of each result column */
---
>   char **azArgZ,/* Text of each result column */
821a822,842
> #if defined(_WIN32) || defined(WIN32)
>   char** azArg = malloc(sizeof(char*)*nArg);
>   for (i = 0;i < nArg;i++) {
> if (azArgZ[i] == 0)
> azArg[i] = 0;
> else {
> int nlen = MultiByteToWideChar(CP_UTF8, 0, azArgZ[i], -1, 
> NULL, 0);
> assert(nlen > 0);
> WCHAR* buff = (WCHAR*)malloc(nlen * sizeof(WCHAR));
> (void)MultiByteToWideChar(CP_UTF8, 0, azArgZ[i], -1, buff, 
> nlen);
> int ilen = WideCharToMultiByte(CP_ACP, 0, buff, nlen, NULL, 
> 0, NULL, NULL);
> assert(ilen > 0);
> char* buff1 = (char*)malloc(ilen);
> (void)WideCharToMultiByte(CP_ACP, 0, buff, nlen, buff1, ilen, 
> NULL, NULL);
> azArg[i] = buff1;
> free(buff);
> }
>   }
> #else
>   char** azArg = azArgZ;
> #endif
1042a1064,1070
> #if defined(_WIN32) || defined(WIN32)
>   for (i = 0;i < nArg;i++) {
> if (azArg[i])
> free(azArg[i]);
>   }
>   free(azArg);
> #endif
4255,4257c4283,4301
<   BEGIN_TIMER;
<   rc = shell_exec(p->db, zSql, shell_callback, p, );
<   END_TIMER;
---
> #if defined(_WIN32) || defined(WIN32)
> int nlen = MultiByteToWideChar(CP_ACP, 0, zSql, -1, 0, 0);
> assert(nlen > 0);
> WCHAR* buff = (WCHAR*)malloc(nlen * sizeof(WCHAR));
> (void)MultiByteToWideChar(CP_ACP, 0, zSql, -1, buff, nlen);
> int ilen = WideCharToMultiByte(CP_UTF8, 0, buff, nlen, NULL, 0, NULL, 
> NULL);
> assert(ilen > 0);
> char* buff1 = (char*)malloc(ilen);
> (void)WideCharToMultiByte(CP_UTF8, 0, buff, nlen, buff1, ilen, NULL, 
> NULL);
> free(buff);
> BEGIN_TIMER;
> rc = shell_exec(p->db, buff1, shell_callback, p, );
> END_TIMER;
> free(buff1);
> #else
> BEGIN_TIMER;
> rc = shell_exec(p->db, zSql, shell_callback, p, );
> END_TIMER;
> #endif
cut 
here--

[sqlite] 答复: about compile configure

[Quan Yong Zhai]

pragma mmap_size= 51200;
Pragma page_size = 8192;
Vacuum;
Pragma cache_size = N;

???: ???
: ?2015/?12/?21 18:08
???: SQLite mailing list
??: Re: [sqlite] about compile configure

The SQL statement is so easy.
the table create statement as following:
CREATE TABLE poiTable (poiId INTEGER NOT NULL, versionId INTEGER NOT NULL, 
regionId INTEGER , postalCode TEXT , phone TEXT , attrBitMask INTEGER , 
attributeBlob BLOB , primary key (poiId));
So the poiId is equal to the rowid.

such as : select * from poiTable where poiId = ... ;
And execute the sql by sqlite3_prepare_v2?sqlite3_step?
how could I improve the performance?









At 2015-12-21 17:15:56, "???" <2004wqg2008 at 163.com> wrote:
>What Simon said is very helpful for me. Thank you very much.
>I only want to improve the speed of reading data from data base. Do not do 
>insert?update and so on.
>
>I will try the following suggustion.
>PRAGMA synchronous = OFF
>
>Best regards
>
>
>
>
>
>
>
>
>
>
>At 2015-12-21 17:03:13, "Simon Slavin"  wrote:
>>
>>On 21 Dec 2015, at 6:19am, ??? <2004wqg2008 at 163.com> wrote:
>>
>>>  The meaning of "how to use sqlite_table" is that I guess the sqlite_table 
>>> may have contained some information which could help to improve speed.
>>>   I  am not meaning to  modify the data structure of  sqlite_master.
>>
>>There is nothing you can do with sqlite_table to improve speed.  Unless you 
>>have a very unusual setup there is nothing you can do with compilation 
>>options to improve speed.
>>
>>Since you say you are not using multi-threading or multi-process, you might 
>>like to read the documentation for
>>
>>PRAGMA synchronous = OFF
>>
>>This might increase speed for you.  However it also means that if your 
>>computer loses power or crashes while the database is open, you will lose 
>>more new data.
>>
>>
>>
>>However a big increase in speed comes from correct use of indexes.  If you 
>>have any SQL commands which include WHERE or ORDER BY, then these will 
>>execute faster if you have an ideal index on the table they use.  This can 
>>affect INSERT and UPDATE and DELETE FROM commands.  If you want help with 
>>this you must post your SQL commands here.
>>
>>Another big increase in speed can come from correctly using transactions.  
>>When you are making changes to your database it is finishing the transaction 
>>with END or COMMIT which takes most of the time.  So if you have many INSERT 
>>commands then
>>
>>INSERT ...
>>INSERT ...
>>INSERT ...
>>
>>is slow but
>>
>>BEGIN
>>INSERT ...
>>INSERT ...
>>INSERT ...
>>COMMIT
>>
>>can be much faster.  This can affect INSERT and UPDATE and DELETE commands.
>>
>>Simon.
>>___
>>sqlite-users mailing list
>>sqlite-users at mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>___
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 答复: Very Strange and Interesting Problem

[Quan Yong Zhai]

https://www.sqlite.org/lang_createtable.html#rowid

???: ???
: ?2015/?12/?14 18:21
???: sqlite-users at mailinglists.sqlite.org
??: [sqlite] Very Strange and Interesting Problem

hi, every one.
 Here is a very strange and interesting problem.
 I used the following SQL to create the table teacher.
 CREATE TABLE techer(poiId INTEGER NOT NULL PRIMARY KEY,
versionId INTEGER NOT NULL,
regionId INTEGER ,
postalCode TEXT ,
phone TEXT ,
attrBitMask INTEGER ,
attributeBlob BLOB)

 and  then I used the following SQL,
 select rowid,*  from teacher.So the strange and interesting problem 
happen.
 the name of the rowid column change to poiId, and the name of the poiId 
become poiId_1. But the two columns have the same values.

 Why the rowid change is column name?
 Is the problem reasonable ?  How do you think about this problem? why?
 I  am looking forward to hearing from you.

 Best regards.
 WQG








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

[sqlite] 答复: Making data unique

[Quan Yong Zhai]

Create Table T(ID integer, datetime Integer, data integer, primary key(ID, 
datetime))

???: Andrew Stewart
: ?2015/?12/?7 23:01
???: 'SQLite mailing list'
??: [sqlite] Making data unique

Hi,
I have a table that consists of 3 elements:
ID - integer
Date/time - integer
Data - integer
A single ID can exist multiple times.
A single Date/time can exist multiple times.
An ID & Date/time combination is unique.

What is the best way to ensure uniqueness in this table.

Thanks,
Andrew Stewart
Software Designer

Argus Controls
#101 - 18445 53 AVE
Surrey, BC  V3S 7A4

t: 1-888-667-2091  ext : 108
t: 1-604-536-9100  ext : 108
f: 604-538-4728
w: www.arguscontrols.com
e: astewart at arguscontrols.com

Notice: This electronic transmission contains confidential information, 
intended only for the person(s) named above. If you are not the intended 
recipient, you are hereby notified that any disclosure, copying, distribution, 
or any other use of this email is strictly prohibited. If you have received 
this transmission by error, please notify us immediately by return email and 
destroy the original transmission immediately and all copies thereof.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug?

[Quan Yong Zhai]

SQLite version 3.9.2 2015-11-02 18:31:45
sqlite> .header on
sqlite> select 0x1zzz;
zzz
1
sqlite>