Re: [sqlite] Sync journal's directory per transaction? or until database closed? in PERSIST mode

[yongil jang]

1. the directory must sync per transaction?
Directory synchronization is necessary.
For example, If current file system uses EXT3 or EXT4 on linux, files can be 
lost if directory sync is not called.
AFAIK, new created journal file may not be linked at that time.
To get more detailed information about that, you may need to investigate about 
linux file system.

2. PERSIST mode must close the journal file descriptor?
Sorry, I don’t know exactly about this question. :)
In my guess, multiple connections to same sqlite database file can use 
different rollback journal mode.
(Ex: connection 1 uses delete mode and connection 2 uses persist mode)
To make it possible, SQLite may close journal file for each end of transaction.
(I think it is not a correct answer, sorry.)

Regards,
Yongil

> 2016. 6. 16., 오후 7:28, 박병언  작성:
> 
> First of all, my English skill is not good. So please forgive me if my
> sentences are rude.
> 
> My name is Byungeun Park.I am a Computer Science Master student in South
> Korea. I'm doing a
> 
> research to enhance the SQLite performance.
> 
> 
> 
> =
> static int unixSync(sqlite3_file *id, int flags){
>  int rc;
>  unixFile *pFile = (unixFile*)id;
> 
>  int isDataOnly = (flags_SYNC_DATAONLY);
>  int isFullsync = (flags&0x0F)==SQLITE_SYNC_FULL;
> 
>  /* Check that one of SQLITE_SYNC_NORMAL or FULL was passed */
>  assert((flags&0x0F)==SQLITE_SYNC_NORMAL
>  || (flags&0x0F)==SQLITE_SYNC_FULL
>  );
> 
>  /* Unix cannot, but some systems may return SQLITE_FULL from here. This
>  ** line is to test that doing so does not cause any problems.
>  */
>  SimulateDiskfullError( return SQLITE_FULL );
> 
>  assert( pFile );
>  OSTRACE(("SYNC%-3d\n", pFile->h));
>  rc = full_fsync(pFile->h, isFullsync, isDataOnly);
>  SimulateIOError( rc=1 );
>  if( rc ){
>storeLastErrno(pFile, errno);
>return unixLogError(SQLITE_IOERR_FSYNC, "full_fsync", pFile->zPath);
>  }
> 
>  /* Also fsync the directory containing the file if the DIRSYNC flag
>  ** is set.  This is a one-time occurrence.  Many systems (examples: AIX)
>  ** are unable to fsync a directory, so ignore errors on the fsync.
>  */
>  if( pFile->ctrlFlags & UNIXFILE_DIRSYNC ){
>int dirfd;
>OSTRACE(("DIRSYNC %s (have_fullfsync=%d fullsync=%d)\n", pFile->zPath,
>HAVE_FULLFSYNC, isFullsync));
>rc = osOpenDirectory(pFile->zPath, );
>if( rc==SQLITE_OK ){
>  full_fsync(dirfd, 0, 0);
>  robust_close(pFile, dirfd, __LINE__);
>}else{
>  assert( rc==SQLITE_CANTOPEN );
>  rc = SQLITE_OK;
>}
>pFile->ctrlFlags &= ~UNIXFILE_DIRSYNC;
>  }
>  return rc;
> }
> =
> 
> 
> In PERSIST mode, journal is still remain. But the red code is always
> generated in each transaction. That is, journal file descriptor is always
> closed at the end of transaction and reopen at the start of next
> transaction(I think this is a bug).
> 
> =
> rc = sqlite3_open("test.db", );
>  if( rc ){
>fprintf(stderr, "Can't open db: %s\n", sqlite3_errmsg(db));
>sqlite3_close(db);
>return(1);
>  }
> 
>  sqlite3_exec(db, "PRAGMA journal_mode=PERSIST", NULL, NULL, );
>  for(i=0;i<100;i++){
>sprintf(buf1, "insert into t1 values(%d, 't1-data-%d')", i, i);
>sqlite3_exec(db, buf1, NULL, 0, );
>  }
>  sqlite3_close(db);
> =
> This is the part of my test file. This code insert 0 to 100 values in the
> table t1 with PERSIST mode. Until the "test.db" closed, each insert
> statement open the journal file descriptor(each insert command pass
> through the red code).
> 
> From the red code documentation, the code is one time occurrence for fsync
> directory. I am not sure if this one time occurrence means one time per one
> transaction(one insert command) or one time until test.db close.
> 
> *I think one time journal directory sync and no close journal file
> descriptor until the test.db close is enough.*
> 
> Even though there is an accident like power disconnection, sqlite code can
> recognize the directory information loss and reopen the database file and
> the journal file. I get this result after I remove the red code and run
> with this test code in Linux. I supposed ctrl + c during program running is
> transaction interruption. After interruption and rerun the program, I
> checked the database can call the table t1 contents by select statement.
> 
> Do you think sync journal's directory per transaction is correct?
> If this opinion is wrong, would you mind if I know why
>1. the directory must sync per transaction?
>2. PERSIST mode must close the journal file descriptor?
> 
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL: no schema after close

[Yongil Jang]

In my guess, if you call following command after for each operation and
schema exits exactly... Your test may removes *-wal file.

conn.execute("PRAGMA wal_checkpoint;")

I hope it is correct answer.

Regards.

2016년 6월 3일 (금) 19:19, Максим Дементьев 님이 작성:

> Hello,
>
> I’ve got a set of python 3 unittests which use sqlite (versions 3.12.0 and
> 3.13.0 under Linux 4.4.6-gentoo x86_64).
>
> There is one test which closes the database and opens it again during the
> test.
> If I use the WAL (by using conn.execute("PRAGMA journal_mode=WAL;")) and
> run all unittests, this unittest produces a database file with the empty
> schema after first close, so it fails.
> When I run it alone (or without WAL), it passes.
>
> I’ve checked “hexdump -C” of the normal and the bad cases after first
> close, in both cases the size of database file is the same, they contain
> common data for inserted rows, but there is no “CREATE TABLE ...”
> statements in the bad database file.
>
> There are conn.commit() calls after each modification SQL statement. I
> even tried to put explicit "BEGIN; ... COMMIT;" around "CREATE TABLE IF NOT
> EXISTS..." statements, it doesn't help.
>
> Is it a well-known problem ?
>
> Regards,
> Max
>
>
> ___
> 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] Changing the default page_size in 3.12.0

[Yongil Jang]

AFAIK, 4, 8, 16 KB sized I/O based on flash memory shows better
performance. (1.x ~ 3.x)

As a result, Android uses 4KB page size.

It's good to embedded devices using flash memory.

2016? 3? 5? (?) 11:22, Donald Shepherd ?? ??:

> On Sat, 5 Mar 2016 at 09:19 Roger Binns  wrote:
>
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > On 04/03/16 07:48, Richard Hipp wrote:
> > > The tip of trunk (3.12.0 alpha) changes the default page size for
> > > new database file from 1024 to 4096 bytes. ... This seems like a
> > > potentially disruptive change, so I want to give you, the user
> > > community, plenty of time to consider the consequences and
> > > potentially talk me out of it.
> >
> > Can I talk you into it instead :-)  My standard boilerplate for new
> > databases is to set the page size to 4,096 bytes, and to turn on WAL.
> >
> > Roger
> >
>
> We've headed the same way.  4,096 significantly reduced the size of our
> databases when we switched to it some time ago.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] How to modify page_size

[Yongil Jang]

AFAIK, WAL mode is used and database is not empty, VACUUM operation doesn't
change page size.

But, I'm not sure about latest version of SQLite. :)

2016? 2? 25? (?) 16:50, R Smith ?? ??:

>
>
> On 2016/02/25 9:40 AM, Jim Wang wrote:
> > hi,all
> >   How could I modify the page_size of the Data base? I use the
> following method, but could not change the page_size.
> >nRet = sqlite3_exec(m_pDB, "PRAGMA page_size=8192;",
> 0,0,);
> >   Why?
>
> Because the Pragma specifies the ideal, it doesn't make the change.
>
> Follow that up with a "VACUUM;" to make the change and re-pack the DB file.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] Sqlite problem possible bug

[Yongil Jang]

How about to use dynamic binding?
For example, is your SQL(SELECT * from dbname where colname like '%אב%'),
use '?' instead of 'אב'.
In my guess, 'אב' can have same ASCII code of wildcard(%).

Full SQL can be as like as follows.

SELECT * from dbname where colname like '%?%'

To do this, you need to use sqlite3_bind*** functions in c API.

Regards
YONGIL.
2015. 1. 7. 오후 9:34에 "The Responsa Project" 님이 작성:

> To Whom it amy concern
>
>
> I am trying to use SQLITE and the like statement with wildcards and hebrew
>
> when I put in an english string it works correctly, such as
>
> Select  * from dbname where colname like '%123%'
>
> I will get all the entries from that column that contain 123 anywhere in
> the column.
>
> However if I substitute 123 with hebrew letters - it matches all the
> entries, not just the ones containing what I asked for.
>
> If I do not use the wilcards in the like it matches the exact word
> properly. In version 3.2.2 of sqlite this worked fine (with wildcards),
> later versions it does not. So for example "SELECT * from dbname where
> colname like '%אב%' will give me all the entries not only the ones matching
> only אב.
>
> I tried GLOB, which also did not work.?
>
>
> I would like to (and need to) upgrade to the latest version of Sqlite but
> I cannot because of this issue.
>
>
> Is this a bug? Am I doing something wrong?
>
>
> All help is appreciated, thanks in advance
>
>
> Sincerely,Sharon Gottlieb
>
>
> The Responsa Project
> Bar-Ilan University
> Ramat-Gan 52900, ISRAEL
> Tel: 972-3-5318-411 / Fax: 972-3-5341-850
> Email: respo...@mail.biu.ac.il
> Internet http://responsa.biu.ac.il
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] journal file is not removed when ATOMIC WRITE is enabled

[Yongil Jang]

For more information,

In pager_end_transaction() function,

int bDelete = (!pPager->tempFile &&
sqlite3JournalExists(pPager->jfd)); <-- sqlite3JournalExists() returns
0

I think both of pager_end_transaction() and sqlite3JournalExists()
functions work properly in this scenario.
However, could it(two files are exist at the same time) make a problem?


2014-12-29 9:40 GMT+09:00 Yongil Jang <yongilj...@gmail.com>:
> Dear developers,
>
> Please, look at following instructions.
>
> 1) Add SQLITE_IOCAP_ATOMIC or SQLITE_IOCAP_ATOMIC4K flags to
> unixDeviceCharacteristics() function (or any OS related functions)
> 2) Add SQLITE_ENABLE_ATOMIC_WRITE to compile option
> 3) Compile
> 4) run sqlite3 -  sqlite3 test.db
> 5) sqlite> pragma journal_mode=persist;
> 6) sqlite> create table a(id);
> 7) sqlite> pragma journal_mode=wal;
> 8) sqlite> insert into a values (1);
>
> With these instructions, 'test.db-journal' and 'test.db-wal' are
> exists at same time.
>
> Regards,
> Yongil Jang.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] journal file is not removed when ATOMIC WRITE is enabled

[Yongil Jang]

Dear developers,

Please, look at following instructions.

1) Add SQLITE_IOCAP_ATOMIC or SQLITE_IOCAP_ATOMIC4K flags to
unixDeviceCharacteristics() function (or any OS related functions)
2) Add SQLITE_ENABLE_ATOMIC_WRITE to compile option
3) Compile
4) run sqlite3 -  sqlite3 test.db
5) sqlite> pragma journal_mode=persist;
6) sqlite> create table a(id);
7) sqlite> pragma journal_mode=wal;
8) sqlite> insert into a values (1);

With these instructions, 'test.db-journal' and 'test.db-wal' are
exists at same time.

Regards,
Yongil Jang.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] automatic index on sqlite_sq_#######

[Yongil Jang]

Yes, it's not a bug.

It is a normal work but I just want to notify that some of automatic index
log message is not easy to recognize which query made this log output.

Thank you.
On Nov 20, 2014 5:39 PM, "Simon Slavin" <slav...@bigfraud.org> wrote:

>
> On 20 Nov 2014, at 7:45am, Yongil Jang <yongilj...@gmail.com> wrote:
>
> > I've found that following log string when using sub-query.
> >
> > "automatic index on sqlite_sq_9F222470(STAT_DATA_ID)"
>
> This is not output by SQLite.  It is output by a program which uses
> SQLite.  It may indicate normal working and not a bug.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] automatic index on sqlite_sq_#######

[Yongil Jang]

Dear developers,

I've found that following log string when using sub-query.

"automatic index on sqlite_sq_9F222470(STAT_DATA_ID)"

"sqlite_sq_#" looks like a temporary table name of sub-query result.
And "STAT_DATA_ID" is a alias name of some column in table.

I don't know whether this issue is normal or not.
But, in my opinion, "sqlite_sq_#" is not easy to recognize.

Regards,
Yongil Jang.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating records from a SELECT that exists in the table

[Yongil Jang]

How about this?

BEGIN;
 INSERT OR REPLACE INTO LSOpenProjects
   SELECT cl.* FROM client.LSOpenProjects cl JOIN LSOpenProjects ls ON
(cl.ProjID = ls.ProjID AND cl.ProjID <= 133560 AND cl.XtraB >= '2014-08-07
00:00:00';);
END;

I used join query to get existing records in local DB instead of subquery.


2014-08-10 7:30 GMT+09:00 jose isaias cabrera :

>
> Greetings!
>
> Long story... But, I have to update a DB from another DB if the record in
> the DB to be updated contains that record. So, with an attached DB,
>
> ATTACH 'somedb.sqlite' as client;
>
> I execute this command,
>
> BEGIN;
>  INSERT OR REPLACE INTO LSOpenProjects
>SELECT * FROM client.LSOpenProjects
>  WHERE id IN
>  (
>SELECT id from client.LSOpenProjects
>   WHERE
> client.LSOpenProjects.ProjID <= 133560 AND
>client.LSOpenProjects.XtraB >= '2014-08-07 00:00:00';
>  );
> END;
>
> This is great and it's working for one piece of the updates to a SharedDB,
> but I also want to run another command like the one above for the usersDB
> that will do the same, except that it should only update the id's that
> exists in the localDB.  In other words, only update from
> client.LSOpenProjects, if the LSOpenProject table has that id.  Imagine
> these two DBs:
> localUserDB: LSOpenProjects
> id,...,XtraB
> 20,...,'2014-04-09 10:20:34'
> 23,...,'2014-04-09 10:20:34'
> 27,...,'2014-04-09 10:20:34'
> 28,...,'2014-04-09 10:20:34'
>
> SharedDB: LSOpenProjects
> id,...,XtraB
> ...
> 20,...,'2014-08-09 14:23:14'
> 21,...,'2014-08-08 11:29:25'
> 22,...,'2014-07-09 16:12:07'
> 23,...,'2014-04-09 10:20:34'
> 24,...,'2014-08-08 23:44:11'
> 25,...,'2014-04-09 10:20:34'
> 26,...,'2014-08-09 10:00:55'
> 27,...,'2014-08-09 03:18:20'
> 28,...,'2014-04-09 10:20:34'
> 29,...,'2014-04-09 10:20:34'
> 30,...,'2014-04-09 10:20:34'
>
> The command above would return,
> 20
> 21
> 24
> 26
> 27
>
> but it should only update id 20 and 27 and should not insert any into the
> local DB.  I have no idea how to write it.  I am now using two separate SQL
> commands and using some programming to create the updates.  Any help would
> be greatly appreciate it.  Thanks.
>
> josé
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Android sqlite error

[Yongil Jang]

Errno 24 means that too many open files.

You may need to check FD counts.

Regards.
2014. 6. 13. 오전 10:56에 "J Decker" 님이 작성:

> /data/data/com.android.bluetooth/databases/btopp.db-journal  should be
> /data/data/com.android.bluetooth/files/databases/btopp.db-journal  and some
> systems don't allow making subdirectories in files so my library would make
> it more like
> /data/data/com.android.bluetooth/files/databases~btopp.db-journal
>
>
>
>
> On Thu, Jun 12, 2014 at 11:25 AM, mm.w <0xcafef...@gmail.com> wrote:
>
> > you can't write at dest, then the journal file fails, quite obvious
> reading
> > the logs
> >
> >
> > On Wed, Jun 11, 2014 at 11:41 PM, Sathish N 
> wrote:
> >
> > > I am new for sqlite.
> > >
> > > The following sqlite error comes in my android phone. when i run the
> > > application sum times.
> > > I like to know the meaning of this error, why this error come and how
> to
> > > solve this. I googled about this but nowhere the answer is mentioned i
> > > tried all possiblities from what i got when googled about this error.
> > >
> > >  E SQLiteLog: (14) cannot open file at line 30179 of [00cd9d8ce4]
> > >  E SQLiteLog: (14) os_unix.c:30179: (24)
> > > open(/data/data/com.android.bluetooth/databases/btopp.db-journal) -
> > >  E SQLiteLog: (14) cannot open file at line 30179 of [00cd9d8ce4]
> > >  E SQLiteLog: (14) os_unix.c:30199: (24)
> > > open(/data/data/com.android.bluetooth/databases/btopp.db-journal) -
> > >  E SQLiteLog: (14) statement aborts at 23: [SELECT * FROM btopp ORDER
> BY
> > > _id] unable to open database file
> > >  E SQLiteQuery: exception: unable to open database file (code 14);
> query:
> > > SELECT * FROM btopp ORDER BY _id
> > >
> > >
> > > Kindly please help regarding this issue why this happens
> > > --
> > > Regards
> > > Sathish N
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error of 'no such column name.rowid' when duplicated alias name is used

[Yongil Jang]

Hi,

I think I've found something interesting result of SQLite when I used
incorrect (duplicated) alias name with two tables.
As you can see below example, duplicated alias name of two tables that
don't have same column doesn't return error for normal case.
However, if I add "rowid" to example query, I get "no such column: [alias
name].rowid" error.

D:\>sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create temp table a (id integer);
sqlite> create temp table b (di integer);
sqlite> select * from a as aname, b as aname;
sqlite> select * from a as aname, b as aname where aname.id = aname.di;
sqlite> select **, aname.rowid* from a as aname, b as aname where aname.id
= aname.di;
*Error: no such column: aname.rowid*

I think duplicated alias name should be restricted from syntax parsing.

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

[sqlite] Different query plans between 3.7.13 and 3.8.0.2

[Yongil Jang]

Hi, all.

Please, check following query plans.
It makes different query plans between 3.7.13 and 3.8.0.2.
It doesn't matter in common cases.
But, when I insert almost 10,000 entries on this table, below query runs
slightly slower on 3.8.0.2.

Regards,
Yongil.

== database schema =

CREATE TABLE files (_id INTEGER PRIMARY KEY AUTOINCREMENT,_data TEXT,_size
INTEGER,format INTEGER,parent INTEGER,date_added INTEGER,date_modified
INTEGER,mime_type TEXT,title TEXT,description TEXT,_display_name
TEXT,picasa_id TEXT,orientation INTEGER,latitude DOUBLE,longitude
DOUBLE,datetaken INTEGER,mini_thumb_magic INTEGER,bucket_id
TEXT,bucket_display_name TEXT,isprivate INTEGER,title_key TEXT,artist_id
INTEGER,album_id INTEGER,composer TEXT,track INTEGER,year INTEGER
CHECK(year!=0),is_ringtone INTEGER,is_music INTEGER,is_alarm
INTEGER,is_notification INTEGER,is_podcast INTEGER,album_artist
TEXT,folder_id INTEGER NOT NULL DEFAULT 0,date_played INTEGER DEFAULT
0,count_played INTEGER DEFAULT 0,is_favorite INTEGER DEFAULT 0,index_key
TEXT,duration INTEGER,bookmark INTEGER,artist TEXT,album TEXT,resolution
TEXT,tags TEXT,category TEXT,language TEXT,mini_thumb_data TEXT,name
TEXT,media_type INTEGER,old_id INTEGER, storage_id INTEGER, is_drm INTEGER,
width INTEGER, height INTEGER, video_filetype TEXT, video_iswatched INTEGER
DEFAULT 0, protected_type INTEGER DEFAULT 0);
ANALYZE sqlite_master;
INSERT INTO "sqlite_stat1" VALUES('files','path_index_lower','9177 1');
INSERT INTO "sqlite_stat1" VALUES('files','format_index','9177 1148');
INSERT INTO "sqlite_stat1" VALUES('files','parent_index','9177 230');
INSERT INTO "sqlite_stat1" VALUES('files','bucket_name','9177 224 214 214');
INSERT INTO "sqlite_stat1" VALUES('files','bucket_index','9177 224 214 8
1');
INSERT INTO "sqlite_stat1" VALUES('files','folder_id_idx','9177 3059');
INSERT INTO "sqlite_stat1" VALUES('files','artist_id_idx','9177 3059');
INSERT INTO "sqlite_stat1" VALUES('files','titlekey_index','9177 4');
INSERT INTO "sqlite_stat1" VALUES('files','sort_index','9177 8 1');
INSERT INTO "sqlite_stat1" VALUES('files','album_id_idx','9177 3059');
INSERT INTO "sqlite_stat1" VALUES('files','title_idx','9177 2');
INSERT INTO "sqlite_stat1" VALUES('files','media_type_index','9177 1836');
INSERT INTO "sqlite_stat1" VALUES('files','path_index','9177 1');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('files',9261);
CREATE INDEX path_index ON files(_data);
CREATE INDEX media_type_index ON files(media_type);
CREATE INDEX title_idx ON files(title);
CREATE INDEX album_id_idx ON files(album_id);
CREATE INDEX sort_index ON files(datetaken ASC, _id ASC);
CREATE INDEX titlekey_index ON files(title_key);
CREATE INDEX artist_id_idx ON files(artist_id);
CREATE INDEX folder_id_idx on files(folder_id);
CREATE INDEX bucket_index on files(bucket_id, media_type, datetaken, _id);
CREATE INDEX bucket_name on files(bucket_id, media_type,
bucket_display_name);
CREATE INDEX parent_index ON files(parent);
CREATE INDEX format_index ON files(format);
CREATE INDEX path_index_lower ON files(_data COLLATE NOCASE);

 query =
SELECT bucket_id, media_type, bucket_display_name, _data FROM files WHERE
(storage_id!=131073) AND ((media_type=1 OR media_type=2)) GROUP BY 1,(2)
ORDER BY bucket_display_name COLLATE NOCASE ASC;

= query plans =
[3.7.13]
0|0|0|SCAN TABLE files USING INDEX bucket_name (~4588 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY

[3.8.0.2]
0|0|0|SEARCH TABLE files USING INDEX media_type_index (media_type=?)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in Apress SQLite book. Was: Different result set of query in examples of SQLite book

[Yongil Jang]

Thank you, Richard and James.

2013/6/14 James K. Lowden 
>
>
> Why not simply
>
> SELECT   f.name, count(e.food_id) as 'episodes'
> FROM foods as f
> OUTER
>  JOINfoods_episodes as e
> ON   f.id = e.food_id
> GROUP BY f.name
> ORDER BY episodes DESC LIMIT 10;
>
>
In my opinion,
That example is used in beginning of SQL chapter, therefore, JOIN and GROUP
BY is not explained yet.

Regards,
Yongil.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Different result set of query in examples of SQLite book

[Yongil Jang]

Thank you, Igor. :)


2013/6/13 Igor Tandetnik <i...@tandetnik.org>

> On 6/13/2013 12:24 AM, Yongil Jang wrote:
>
>> SELECT
>>name,
>>(SELECT COUNT(id) FROM foods_episodes WHERE food_id=f.id) count
>> FROM
>>foods f
>> ORDER BY count DESC LIMIT 10;
>>
>>
>> Result from v3.7.6 and v3.7.11.
>>
>> Hot Dog|5
>> Kasha|4
>> Ketchup|4
>> ..
>>
>>
>> Result from v3.7.13 and v3.7.17.
>>
>> Wax Beans (Generic brand)|412
>>
>>
>> Which result set is correct?
>> When I changed statement from COUNT(id) to COUNT(food_id) or COUNT(*), it
>> shows same result with older version.
>>
>
> Looks like a bug in the newer versions. id resolves to f.id, which within
> the context of the sub-select is a constant. So COUNT(id) should work no
> differently than, say, COUNT(1), which in turn should be equivalent to
> COUNT(*).
>
> Of course, there is really no good reason to write COUNT(id) here.
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Different result set of query in examples of SQLite book

[Yongil Jang]

Dear all,

Following select query returns different result data between v3.7.11 and
v3.7.13~.

CREATE TABLE foods(
  id integer primary key,
  type_id integer,
  name text );
CREATE TABLE foods_episodes(
  food_id integer,
  episode_id integer );

[Insert some data]

SELECT
  name,
  (SELECT COUNT(id) FROM foods_episodes WHERE food_id=f.id) count
FROM
  foods f
ORDER BY count DESC LIMIT 10;


Result from v3.7.6 and v3.7.11.

Hot Dog|5
Kasha|4
Ketchup|4
..


Result from v3.7.13 and v3.7.17.

Wax Beans (Generic brand)|412


Which result set is correct?
When I changed statement from COUNT(id) to COUNT(food_id) or COUNT(*), it
shows same result with older version.

I've got this examples from following links of "The Definitive Guide to
SQLite"

http://www.apress.com/9781430232254

Regards,
Yongil.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using "COLLATE nocase" with BETWEEN

[Yongil Jang]

Thank you, igor.
I've learned one more, today!
2013. 5. 22. 오후 10:20에 "Igor Tandetnik" <i...@tandetnik.org>님이 작성:

> On 5/22/2013 12:53 AM, Yongil Jang wrote:
>
>> But, in case of using BETWEEN operator with "COLLATE nocase", it returns
>> unexpected result as follows.
>>
>> sqlite> select * from mytable where data between 'abc/' and 'ABC0' COLLATE
>> nocase;
>>
>
> Make it
>
> where data COLLATE nocase between 'abc/' and 'ABC0'
>
> The expression "x between a and b" behaves the same as "x >= a AND x <=
> b". In your example, you end up with
>
> data >= 'abc/' and data <= 'ABC0' COLLATE nocase;
>
> The two comparisons use different collations.
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using "COLLATE nocase" with BETWEEN

[Yongil Jang]

It works very well if I use "COLLATE" as like as follows.

sqlite> select * from mytable where data between 'abc/' COLLATE nocase and
'abc0' COLLATE nocase;
abc/
ABC/
abc/a
ABC/A
abc/b
ABC/B
abc0
ABC0
sqlite> select * from mytable where data between 'abc0' COLLATE nocase and
'abc/' COLLATE nocase;
sqlite>



2013/5/22 Yongil Jang <yongilj...@gmail.com>

> I've found some unexpected result with "COLLATE" operator.
> I don't know what if it is a bug or my misunderstanding.
> Please, check below description.
>
> CREATE TABLE mytable (data);
> INSERT INTO "mytable" VALUES('abc');
> INSERT INTO "mytable" VALUES('ABC');
> INSERT INTO "mytable" VALUES('abc/');
> INSERT INTO "mytable" VALUES('ABC/');
> INSERT INTO "mytable" VALUES('abc/a');
> INSERT INTO "mytable" VALUES('ABC/A');
> INSERT INTO "mytable" VALUES('abc/b');
> INSERT INTO "mytable" VALUES('ABC/B');
> INSERT INTO "mytable" VALUES('abc0');
> INSERT INTO "mytable" VALUES('ABC0');
> CREATE INDEX data_idx on mytable(data);
> CREATE INDEX data_idx_nocase on mytable(data COLLATE nocase);
>
> As you can see here I defined two indices that can be used both case and
> nocase ordering.
> It works very well when I use "COLLATE nocase" with '=' unary operator.
>
> sqlite> select * from mytable where data = 'abc' COLLATE nocase;
> abc
> ABC
>
> But, in case of using BETWEEN operator with "COLLATE nocase", it returns
> unexpected result as follows.
>
> sqlite> select * from mytable where data between 'abc/' and 'ABC0' COLLATE
> nocase;
> *abc/*
> *abc/a*
> *abc/b*
> *abc0*
> sqlite> select * from mytable where data between 'ABC/' and 'abc0' COLLATE
> nocase;
> *abc*
> *abc/*
> *ABC/*
> *abc/a*
> *ABC/A*
> *abc/b*
> *ABC/B*
> *abc0*
> *ABC0*
>
> As you can see below ordered list,
> first result set of between query doesn't have upper case data.
> And, second result set contains unexpected data as like as 'abc', 'ABC'.
>
> sqlite> select * from mytable order by data COLLATE nocase;
> abc
> ABC
> abc/
> ABC/
> abc/a
> ABC/A
> abc/b
> ABC/B
> abc0
> ABC0
>
> Regards,
> Yongil.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using "COLLATE nocase" with BETWEEN

[Yongil Jang]

I've found some unexpected result with "COLLATE" operator.
I don't know what if it is a bug or my misunderstanding.
Please, check below description.

CREATE TABLE mytable (data);
INSERT INTO "mytable" VALUES('abc');
INSERT INTO "mytable" VALUES('ABC');
INSERT INTO "mytable" VALUES('abc/');
INSERT INTO "mytable" VALUES('ABC/');
INSERT INTO "mytable" VALUES('abc/a');
INSERT INTO "mytable" VALUES('ABC/A');
INSERT INTO "mytable" VALUES('abc/b');
INSERT INTO "mytable" VALUES('ABC/B');
INSERT INTO "mytable" VALUES('abc0');
INSERT INTO "mytable" VALUES('ABC0');
CREATE INDEX data_idx on mytable(data);
CREATE INDEX data_idx_nocase on mytable(data COLLATE nocase);

As you can see here I defined two indices that can be used both case and
nocase ordering.
It works very well when I use "COLLATE nocase" with '=' unary operator.

sqlite> select * from mytable where data = 'abc' COLLATE nocase;
abc
ABC

But, in case of using BETWEEN operator with "COLLATE nocase", it returns
unexpected result as follows.

sqlite> select * from mytable where data between 'abc/' and 'ABC0' COLLATE
nocase;
*abc/*
*abc/a*
*abc/b*
*abc0*
sqlite> select * from mytable where data between 'ABC/' and 'abc0' COLLATE
nocase;
*abc*
*abc/*
*ABC/*
*abc/a*
*ABC/A*
*abc/b*
*ABC/B*
*abc0*
*ABC0*

As you can see below ordered list,
first result set of between query doesn't have upper case data.
And, second result set contains unexpected data as like as 'abc', 'ABC'.

sqlite> select * from mytable order by data COLLATE nocase;
abc
ABC
abc/
ABC/
abc/a
ABC/A
abc/b
ABC/B
abc0
ABC0

Regards,
Yongil.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequential row numbers from query

[Yongil Jang]

Hi, all.

How about this approach?
I just implemented a simple code(User Defined Function) that returns a
number as like as row number.

It was worked pretty good with my simple SQL test cases.

sqlite> insert into test values ('first record');
sqlite> insert into test values ('second record');
sqlite> insert into test values ('third record');
sqlite> select rownum(0), * from test;
1 | first record
2 | second record
3 | third record

Note. parameter value of 0 is not necessary, but it should be exist to work
properly to use aux data in UDF.

Below shows my code.
It is registered by calling sqlite3_create_function() after database is
opened.

typedef struct ROWNUM_t ROWNUM_t;
struct ROWNUM_t{
  int nNumber;
};

static void rownum_free(void *p){
  sqlite3_free(p);
}

static void rownum(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  ROWNUM_t* pAux;

  pAux = sqlite3_get_auxdata(context, 0);

  if(!pAux) {
pAux = (ROWNUM_t*)sqlite3_malloc(sizeof(ROWNUM_t));
if(pAux) {
  pAux->nNumber = 0;
  sqlite3_set_auxdata(context, 0, (void*)pAux, rownum_free);
}
else {
  sqlite3_result_error(context, "sqlite3_malloc failed", -1);
  return;
}
  }
  pAux->nNumber++;

  sqlite3_result_int(context, pAux->nNumber);
}

Regards,
Yongil.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] SQLITE_FCNTL_CHUNK_SIZE

[Yongil Jang]

Hi.

Another question.
Below source code is a part of "pager_write_pagelist()".

In this code, dbSize of pPager or pgno of pList are compared with
dbHistSize of pPager.
However, szFile variable is only calculated from dbSize of pPager.

  /* Before the first write, give the VFS a hint of what the final

  ** file size will be.

  */

  assert( rc!=SQLITE_OK || isOpen(pPager->fd) );

  if( rc==SQLITE_OK

   && (pList->pDirty ? pPager->dbSize : pList->pgno+1)>pPager->dbHintSize

  ){

sqlite3_int64 szFile = pPager->pageSize *
(sqlite3_int64)pPager->dbSize;
sqlite3OsFileControlHint(pPager->fd, SQLITE_FCNTL_SIZE_HINT, );

pPager->dbHintSize = pPager->dbSize;

  }


Should this line be changed to check pList->pgno?

From: sqlite3_int64 szFile = pPager->pageSize *
(sqlite3_int64)pPager->dbSize;
To: sqlite3_int64 szFile = pPager->pageSize *
(sqlite3_int64)(pList->pDirty ? pPager->dbSize : pList->pgno+1);

When the original source is tested, fstat() in fcntlSizeHint() is called
frequently without any changes to file size.

Regards,
Yongil Jang.



2013/4/19 Yongil Jang <yongilj...@gmail.com>

> Dear all,
>
> I have a qustion about using chunk size on VFS.
>
> AFAIK, chunk size means that increasing and reducing file size has
> dependency on chunk size.
> For example, with chunk size of 32KB, DB file size can be 32KB, 64KB, 96KB
> and so on...
>
> However, when I tested with my own test scripts that inserts single record
> to a database file, file size increased with single page size. (ex: 4KB
> page size)
> In case of multiple insertion or update with transaction calls fallocate
> with chunk size.
>
> It would not be a big problem, but I will be happy if I can get some
> description about this.
>
> Best regards,
> Yongil Jang.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [Question] SQLITE_FCNTL_CHUNK_SIZE

[Yongil Jang]

Dear all,

I have a qustion about using chunk size on VFS.

AFAIK, chunk size means that increasing and reducing file size has
dependency on chunk size.
For example, with chunk size of 32KB, DB file size can be 32KB, 64KB, 96KB
and so on...

However, when I tested with my own test scripts that inserts single record
to a database file, file size increased with single page size. (ex: 4KB
page size)
In case of multiple insertion or update with transaction calls fallocate
with chunk size.

It would not be a big problem, but I will be happy if I can get some
description about this.

Best regards,
Yongil Jang.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[Yongil Jang]

Thank you, J. and Keith!


2013/4/4 Keith Medcalf <kmedc...@dessus.com>

>
> http://xkcd.com/327/
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of j.merr...@enlyton.com
> > Sent: Wednesday, 03 April, 2013 17:17
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] [Question] How can I recognize arguments are
> dynamic
> > binding values in user defined function?
> >
> >
> > The people who are using your software need a lesson about "SQL
> > injection".  No one should create SQL statements "on the fly" that
> include
> > literal character strings built from data.  Not only could there be
> issues
> > if there are special characters in the data to be included as a literal
> > string (including the possibility of a syntax error that prevents the
> > statement from being executed) but evil people could do severe damage --
> > but this can easily be avoided by using parameters.
> >
> > For example, if the value being searched for came from user input (say on
> > a web page) users of your software must not do things like this --
> >
> > sql = "select * from mytbl where mycol ='" + input + "'"
> >
> > because, if the input is something like
> >
> > x' ; drop table mytbl; --
> >
> > the table will be dropped!  This cannot happen if parameters are used to
> > pass the string.
> >
> > You might be better off providing only a method where the user passes
> > strings for the table name ("mytbl" in the example before), the columns
> to
> > be returned (separated by commas, or "*" for all as above), the name of
> > the column to compare ("mycol" in the example) and the value to search
> > for.  It would then be your code that builds and runs the SQL statement
> > using parameters.
> >
> > J. Merrill
> >
> > -Original Message-
> > Date: Wed, 3 Apr 2013 22:41:01 +0900
> >
> > From: Yongil Jang <yongilj...@gmail.com>
> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> > Subject: Re: [sqlite] [Question] How can I recognize arguments are
> >  dynamic binding values in user defined function?
> > Message-ID:
> >  <cao_0w+hg70q00zzcxn6ojigo_otrng25bbn9n_p+t0xavyq...@mail.gmail.com>
> > Content-Type: text/plain; charset=EUC-KR
> >
> > Thank you, Simon and Igor.
> >
> > I will investigate about your opinion, as you mentioned.
> >
> > In general, if parameter string contains alphabets only, it doesn't make
> > any problems.
> >
> > However, I couldn't check that my function is used correctly for every
> > applications.
> > Some developers don't know why does it fails when using special
> characters
> > and applications can be packaged with hidden issues.
> >
> > For this reason, I was looking for some solutions that I can send warning
> > messages to application developers if they use plain text without binding
> > arguments.
> >
> > Best regards,
> > Yongil jang.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[Yongil Jang]

Thank you, Simon and Igor.

I will investigate about your opinion, as you mentioned.

In general, if parameter string contains alphabets only, it doesn't make
any problems.

However, I couldn't check that my function is used correctly for every
applications.
Some developers don't know why does it fails when using special characters
and applications can be packaged with hidden issues.

For this reason, I was looking for some solutions that I can send warning
messages to application developers if they use plain text without binding
arguments.

Best regards,
Yongil jang.
2013. 4. 3. 오후 10:13에 "Igor Tandetnik" <i...@tandetnik.org>님이 작성:

> On 4/3/2013 8:58 AM, Yongil Jang wrote:
>
>> For more information,
>> I just made some functions that handling files path.
>> But, if file name includes "Special characters(ex: '"') " or "Unicode" and
>> it is used for myFunc()
>> then it makes "Syntax error" error code and execution is failed.
>> What I want to do is to make an "WARNING" message if parameter is not
>> dynamic binding value for developers who would use myFunc().
>>
>
> If the string literal is syntactically invalid, any syntax errors would be
> reported at the time the statement is prepared. Naturally, a statement with
> syntax errors cannot be executed, so your function wouldn't be called in
> the first place; of course, if it's not called, it can't issue any warnings.
>
> If your function is running, this means it's been given a valid string,
> whether as a string literal or a bound parameter.
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[Yongil Jang]

Thank you!

I may need to make plan B.
2013. 4. 3. 오후 10:04에 "Richard Hipp" <d...@sqlite.org>님이 작성:

> On Wed, Apr 3, 2013 at 8:58 AM, Yongil Jang <yongilj...@gmail.com> wrote:
>
> > Is there any way that I can recognize there arguments are generated from
> > dynamic binding (ex: "?") or static string?
> >
>
> No.  Applications-defined functions are call-by-value, as in C.  If you
> have a C function, you cannot tell if the parameters came from variables,
> expressions, or literals.  In the same way, SQLite application defined
> functions have no means of tracing the original of parameter values.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

[Yongil Jang]

Hello,
I have a question about user defined function.

When I make user defined function, that function has argument count and
values only.
Is there any way that I can recognize there arguments are generated from
dynamic binding (ex: "?") or static string?

For following examples, there are two different cases.

ex1)
SELECT * FROM myTable WHERE myFunc(col1, "test string");

ex2)
SELECT * FROM myTable WHERE myFunc(col1, ?);

As you can see here, second parameter is changed.
But, myFunc() gets same values for each case.

For more information,
I just made some functions that handling files path.
But, if file name includes "Special characters(ex: '"') " or "Unicode" and
it is used for myFunc()
then it makes "Syntax error" error code and execution is failed.
What I want to do is to make an "WARNING" message if parameter is not
dynamic binding value for developers who would use myFunc().

Best regards,
Yongil Jang.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Null pointer exception in shell.c(process_sqliterc)

[Yongil Jang]

Dear all,

I've found a small fix point in shell.c
In process_sqliterc() function, zBuf from sqlite3_mprintf() is used as
parameters of fopen() without any null value check.

  if (sqliterc == NULL) {
home_dir = find_home_dir();
if( home_dir==0 ){
#if !defined(__RTP__) && !defined(_WRS_KERNEL)
  fprintf(stderr,"%s: Error: cannot locate your home directory\n",
Argv0);
#endif
  return 1;
}
sqlite3_initialize();
*zBuf = sqlite3_mprintf("%s/.sqliterc",home_dir);*
sqliterc = zBuf;
  }
*  in = fopen(sqliterc,"rb"); <-- sqliterc can be null*

Regards,
Yongil Jang.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question: User defined function and auxiliary data for multiple instances on one query

[Yongil Jang]

Thank you, Dan!

It really works well as you commented!

Thank you again!


2013/2/8 Dan Kennedy <danielk1...@gmail.com>

> On 02/08/2013 12:08 PM, Yongil Jang wrote:
>
>> Dear,
>>
>> I'm trying to make a mystrstr function to use it as a sub-function of
>> "like".
>> mystrstr function is designed to use boyer-moore algorithm to improve
>> string search performance.
>> (Only for simple substing search, but not a complex search pattern)
>>
>> But, the problem is occurred when this mystrstr function is called two
>> more
>> times on one query.
>>
>> In boyer-moore algorithm, search pattern string should be preprocessed
>> before it is used.
>> I used sqlite3_set_auxdata() and sqlite3_get_auxdata() function to save
>> preprocessed search pattern to call preprocessing process only once.
>> It works very well if I use only one mystrstr function on one query.
>>
>> example)
>> select * from mytable where mystrstr(col1, 'test1') and mystrstr(col2,
>> 'test2');
>>
>> In this case, mystrstr function is called twice and there are two
>> different
>> search patterns.
>> I can use two aux data slots and can find preprocessed patterns by string
>> compare with original pattern string for each call of mystrstr().
>> But, I think it is not a good idea, because of string compare can make not
>> necessary processing cost.
>>
>
> I don't think you should have to do anything special for this to work.
>
> SQLite will allocate separate aux-data slots to each invocation. The
> array of aux-data slots accessed by "mystrstr(col1, 'test1')" is
> different to the array accessed by "mystrstr(col2, 'test2')". So if
> the implementation just stores the compiled version of the search
> pattern in aux-data slot 1 things should just work.
>
> Dan.
>
>
>
>
>
>> Could I get some more information of currently called function?
>> For example, If I can get PC(program count of VDBE) from context, this
>> value can be used to distinguish current position of my function on a
>> query.
>>
>> Thank you.
>> Yongil Jang.
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>>
>>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search string in the db question

[Yongil Jang]

Select * from mytable where c5 like '9806067880' OR c6 like '9806067880' OR
c7 like '9806067880';

AFAIK, like function should called for each column.

Regards,
Yongil Jang.


2013/2/8 YAN HONG YE <yanhong...@mpsa.com>

> my table has a table like this:
> c5  c6  c7
> 9806062380  9806062380  9806062380
> 9806062480  9806062680  9806062680
> 9806063280  9806068980  9806068980
> 9806062980  9806067980  9806067980
> 9806062280  9806067880  9806067880
> 9806734080  9806068280  9806068380
> 9806069180  9806068380  9806068680
> 9806063180  9806068680  9806635280
>
> I wanna to find in the column c5,c6,c7 include 9806067880 like this:
>
> select * from mytable where * like '9806067880';
>
> it's right?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question: User defined function and auxiliary data for multiple instances on one query

[Yongil Jang]

Dear,

I'm trying to make a mystrstr function to use it as a sub-function of
"like".
mystrstr function is designed to use boyer-moore algorithm to improve
string search performance.
(Only for simple substing search, but not a complex search pattern)

But, the problem is occurred when this mystrstr function is called two more
times on one query.

In boyer-moore algorithm, search pattern string should be preprocessed
before it is used.
I used sqlite3_set_auxdata() and sqlite3_get_auxdata() function to save
preprocessed search pattern to call preprocessing process only once.
It works very well if I use only one mystrstr function on one query.

example)
select * from mytable where mystrstr(col1, 'test1') and mystrstr(col2,
'test2');

In this case, mystrstr function is called twice and there are two different
search patterns.
I can use two aux data slots and can find preprocessed patterns by string
compare with original pattern string for each call of mystrstr().
But, I think it is not a good idea, because of string compare can make not
necessary processing cost.

Could I get some more information of currently called function?
For example, If I can get PC(program count of VDBE) from context, this
value can be used to distinguish current position of my function on a query.

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

Re: [sqlite] update to limits infomation

[Yongil Jang]

Suggestion for new development item of sqlite?
I just mentioned this idea because of I thought that it is helpful for the
others if it can be implemented.

Your answer is correct.
But, n x n times of comparation would be occurred to count bigger number to
set rank of each record in mytable can make this query to be slow.
And, in my experience, there was some requirement of sequence number in my
work area.

Regards,
Yongil Jang.


2013/2/5 Igor Tandetnik <i...@tandetnik.org>

> On 2/4/2013 9:37 PM, Yongil Jang wrote:
>
>> For example, if sqlite supports sequence() function that returns current
>> sequence number of result set then it can be used for this case, doesn't
>> it?
>>
>
> If SQLite supported such a function, then it could be used. But it
> doesn't, so it can't. I'm not sure what point you are trying to make here.
>
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update to limits infomation

[Yongil Jang]

I have a question.

For example, if sqlite supports sequence() function that returns current
sequence number of result set then it can be used for this case, doesn't it?

ex)
select sequence(), * from mytable order by score desc;
1, 10,rosa,95,0
2, 6,kero,90,0
3, 1,anna,80,0
4, 2,qera,65,0

This runtime values on result set may help to solve some query scenarios.


2013/2/5 Igor Tandetnik 

> On 2/4/2013 8:34 PM, Igor Tandetnik wrote:
>
>> On 2/4/2013 8:22 PM, YAN HONG YE wrote:
>>
>>> I hava a table like this:
>>> id,name,score,rank
>>> 1,anna,80,0
>>> 2,qera,65,0
>>> 6,kero,90,0
>>> 10,rosa,95,0
>>>
>>> what I would like to do is to update the rank position.
>>>
>>
>> update mytable set rank = (select count(*) from mytable t2 where
>> t2.score <= mytable.score);
>>
>
> Sorry, that should bet2.score >= mytable.score
>
>
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT and UPDATE in single query

[Yongil Jang]

Penman wrote:
How to find which keys have been updated from this query?

There is sqlite3_update_hook() function that returns rowid of changed
record.

Regards,
Yongil Jang.
On Jan 24, 2013 11:10 PM, "Igor Tandetnik" <i...@tandetnik.org> wrote:

> On 1/24/2013 12:47 AM, Richard Baron Penman wrote:
>
>> How to find which keys have been updated from this query?
>>
>
> You can't, really. If you need a list of keys (or in fact a list of
> anything from the database), you need to run a SELECT statement. At which
> point you are back where you started and might as well keep the original
> design.
>
> If you really don't want to do that for some reason, you could create an
> AFTER UPDATE trigger on the table, which would call a custom function,
> passing each key to it as the status is being updated.
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with query

[Yongil Jang]

like this?

sqlite> SELECT t1.key1, t1.key2, t2.description FROM MASTERLANGUAGES as t1,
MASTERLANGUAGES as t2 WHERE t1.ISOCode = 'DEU' and t2.ISOCode = 'ENG' and
t1.key1 = t2.key1 and t1.key2 = t2.key2;
FORM1|SAVE_BUTTON|Save
FORM1|HELP_BUTTON|Help

Sorry if my try is wrong.


2013/1/15 Kai Peters 

> Hi all,
>
> given
>
> CREATE TABLE masterlanguages (
>   ID  integer primary key autoincrement,
>   Key1varchar not null,
>   Key2varchar not null,
>   ISOCode varchar not null,
>   Description varchar not null,
>   MaxCharsinteger default 0
> );
>
>
> insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'ENG',
> 'Save', 0);
> insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'ENG',
> 'Help', 0);
> insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'DEU',
> 'Speichern', 0);
> insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'DEU',
> 'Hilfe', 0);
>
>
>
> In addition to the data from
>
> SELECT * FROM MASTERLANGUAGES WHERE ISOCode = 'DEU'
>
> I also need the Description field for the corresponding record (based on
> Key1 + Key2) in English so
> that I can display the original English description as well as its German
> translation.
>
>
> How can I achieve this?
>
> TIA,
> Kai
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite version of android 4.0.3

[Yongil Jang]

~Android gingerbread: 3.6.22
Honeycomb~ICS: 3.7.4
Jellybean~:3.7.11
On Jan 10, 2013 9:23 AM, "sqlitekyounoii" 
wrote:

> I want to know sqlite version of android 4.0.3
>
> It's bundle, so version is not know.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Copying a DB file that is being in use

[Yongil Jang]

How about to use sqlite backup apis?
These are designed to copy database file without locking on source file.
AFAIK, it restart backup progress when source file is changed.
 2012. 12. 7. 오전 4:30에 "romtek" 님이 작성:

> Hi,
>
> A recent discussion of a reader/writer problematic situation has
> prompted me to ask the following question.
>
> While the database file is being written to, the data isn't ready to
> be used by readers, so that DB is locked. What happens if I initiate
> copying of this file (e.g., to back it up)? Is the file being
> protected by the OS from being copied while it's being written to? Or
> do I need to make sure that nobody uses the DB before I attempt to
> copy the file?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create table returns "database disk image is malformed" when disk is full and "SQLITE_IOCAP_ATOMIC" and "SQLITE_DEFAULT_AUTOVACUUM" is enabled

[Yongil Jang]

Dear all,

I changed btree.c like as follows.
But, it may not be a best solution.
Whatever TCL test for "quick.test" is passed, following code can have some
mistakes...
I couldn't change it more with my poor knowledge.

*static int btreeCreateTable(Btree *p, int *piTable, int createTabFlags)*{
  BtShared *pBt = p->pBt;
  MemPage *pRoot;
  Pgno pgnoRoot;
  int rc;
  int ptfFlags;  /* Page-type flage for the root page of new table
*/
*  //yongiljang*
*  int nOrgPage;*
*  //yongiljang_end*
  ..
while( pgnoRoot==PTRMAP_PAGENO(pBt, pgnoRoot) ||
pgnoRoot==PENDING_BYTE_PAGE(pBt) ){
  pgnoRoot++;
}
assert( pgnoRoot>=3 );

*//yongiljang*
*nOrgPage = pBt->nPage;*
*//yongiljang_end*

/* Allocate a page. The page that currently resides at pgnoRoot will
** be moved to the allocated page (unless the allocated page happens
** to reside at pgnoRoot).
*/
rc = allocateBtreePage(pBt, , , pgnoRoot, 1);
..
/* Update the pointer-map and meta-data with the new root-page number.
*/
ptrmapPut(pBt, pgnoRoot, PTRMAP_ROOTPAGE, 0, );
if( rc ){
  *//yongiljang*
*  pBt->nPage = nOrgPage;*
*  put4byte(28 + (u8*)pBt->pPage1->aData, pBt->nPage);*
*  //yongiljang_end*
  releasePage(pRoot);
  return rc;
    }
..
}

B.R.
yongil jang.



2012/11/30 Yongil Jang <yongilj...@gmail.com>

> In btreeCreateTable, a new page is allocated by allocateBtreePage function
> without any failures.
> In normal case, this should be failed because of there is no space to
> write journal file.
> jrnlWrite function called from allocateBtree is passed by writing memory
> block(no real file) in atomic write mode.
>
> After finished of allocateBtreePage, ptrmapPut function called to update
> the pointer-map and meta-data with the new root-page number.
> Finally, this function is failed at sqlite3PagerWrite because of real file
> write is called at this time.
> But, the newly allocated page number in btree structure(or pager?) didn't
> changed to original value.
>
> In my opinion, issue point is updated page number is not returned to
> previous value.
> the allocateBtreePage function doesn't return disk full error and this
> wrong value is remained until next query execution.
> ptrmapPut function doesn't clear this page number.
>
> If I turn off "atomic write" option, it works very well.
> (allocateBtreePage function will return disk full error and page number
> will be replaced to original value)
>
> My question is how to replace page number in btree structure to original
> value when failure is occurred after call of allocateBtreePage funciton.
> Or... is there any better code for this?
>
> Thank you.
> Yongil Jang.
>
>
>
> 2012/11/26 Yongil Jang <yongilj...@gmail.com>
>
>> Hi, there.
>>
>> I just found some illegal processing of SQLite.
>> As I mentioned in title, "CREATE TABLE" query returns "database disk
>> image is malformed" when disk is full and "SQLITE_IOCAP_ATOMIC" and
>> "SQLITE_DEFAULT_AUTOVACUUM" is enabled.
>> Here is my test scripts.
>>
>> sudo mkdir /mnt/db
>> sudo chmod 777 /mnt/db
>> sudo mount -t tmpfs -o size=16K tmpfs /mnt/db
>> /home/yi.jang/git/sqlite_source/sqlite3 /mnt/db/test.db
>>
>> SQLite version 3.7.13 2012-06-11 02:05:22
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> pragma journal_mode=persist;
>> persist
>> sqlite> pragma page_size=4096;
>> sqlite> create table a (id);
>> sqlite> create table b (id);
>> *Error: database or disk is full*
>> sqlite> create table b (id);
>> *Error: database disk image is malformed*
>> sqlite>
>>
>> What I attached options to Makefile are as following.
>>
>> -DSQLITE_ENABLE_ATOMIC_WRITE
>> -DSQLITE_DEFAULT_AUTOVACUUM=1
>> -DSQLITE_DEFAULT_PAGE_SIZE=4096
>>
>> sqlite3.c is also changed.
>>
>> static int unixDeviceCharacteristics(sqlite3_file *id){
>>unixFile *p = (unixFile*)id;
>>   if( p->ctrlFlags & UNIXFILE_PSOW ){
>> return SQLITE_IOCAP_POWERSAFE_OVERWRITE | SQLITE_IOCAP_ATOMIC;
>>   }else{
>> return SQLITE_IOCAP_ATOMIC;
>>   }
>> }
>>
>> B.R.
>> Yongil Jang.
>>
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create table returns "database disk image is malformed" when disk is full and "SQLITE_IOCAP_ATOMIC" and "SQLITE_DEFAULT_AUTOVACUUM" is enabled

[Yongil Jang]

In btreeCreateTable, a new page is allocated by allocateBtreePage function
without any failures.
In normal case, this should be failed because of there is no space to write
journal file.
jrnlWrite function called from allocateBtree is passed by writing memory
block(no real file) in atomic write mode.

After finished of allocateBtreePage, ptrmapPut function called to update
the pointer-map and meta-data with the new root-page number.
Finally, this function is failed at sqlite3PagerWrite because of real file
write is called at this time.
But, the newly allocated page number in btree structure(or pager?) didn't
changed to original value.

In my opinion, issue point is updated page number is not returned to
previous value.
the allocateBtreePage function doesn't return disk full error and this
wrong value is remained until next query execution.
ptrmapPut function doesn't clear this page number.

If I turn off "atomic write" option, it works very well. (allocateBtreePage
function will return disk full error and page number will be replaced to
original value)

My question is how to replace page number in btree structure to original
value when failure is occurred after call of allocateBtreePage funciton.
Or... is there any better code for this?

Thank you.
Yongil Jang.



2012/11/26 Yongil Jang <yongilj...@gmail.com>

> Hi, there.
>
> I just found some illegal processing of SQLite.
> As I mentioned in title, "CREATE TABLE" query returns "database disk image
> is malformed" when disk is full and "SQLITE_IOCAP_ATOMIC" and
> "SQLITE_DEFAULT_AUTOVACUUM" is enabled.
> Here is my test scripts.
>
> sudo mkdir /mnt/db
> sudo chmod 777 /mnt/db
> sudo mount -t tmpfs -o size=16K tmpfs /mnt/db
> /home/yi.jang/git/sqlite_source/sqlite3 /mnt/db/test.db
>
> SQLite version 3.7.13 2012-06-11 02:05:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> pragma journal_mode=persist;
> persist
> sqlite> pragma page_size=4096;
> sqlite> create table a (id);
> sqlite> create table b (id);
> *Error: database or disk is full*
> sqlite> create table b (id);
> *Error: database disk image is malformed*
> sqlite>
>
> What I attached options to Makefile are as following.
>
> -DSQLITE_ENABLE_ATOMIC_WRITE
> -DSQLITE_DEFAULT_AUTOVACUUM=1
> -DSQLITE_DEFAULT_PAGE_SIZE=4096
>
> sqlite3.c is also changed.
>
> static int unixDeviceCharacteristics(sqlite3_file *id){
>   unixFile *p = (unixFile*)id;
>   if( p->ctrlFlags & UNIXFILE_PSOW ){
> return SQLITE_IOCAP_POWERSAFE_OVERWRITE | SQLITE_IOCAP_ATOMIC;
>   }else{
> return SQLITE_IOCAP_ATOMIC;
>   }
> }
>
> B.R.
> Yongil Jang.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create table returns "database disk image is malformed" when disk is full and "SQLITE_IOCAP_ATOMIC" and "SQLITE_DEFAULT_AUTOVACUUM" is enabled

[Yongil Jang]

I tried to solve this problem by myself...
But, It's very difficult work to me. :)

SQLITE_CORRUPT_BKPT is returned in lockBtree, because of, nPage(=4) is
bigger than nPageFile(=3).

First call of creating table "b" returns "database or disk is full" and it
is normal case.
But, second call of creating table "b" returns database corruption.
If I close database connection and reconnect to target database, it shows
same patterns with above.

In my opinion, a new page information of a table is not cleaned when meets
first "disk is full" in "ATOMIC WRITE" and "AUTOVACUUM" mode. It makes
database corruption status when nPage and nPageFile is compared.
For example, If auto_vacuum == 0, then nPage and nPageFile have same values
of #2. In btreeCreateTable function, it is changed with "if
(pBt->autoVacuum)" condition.

Could anyone give me some hints to solve it?

B. R.
Yongil Jang.



2012/11/26 Yongil Jang <yongilj...@gmail.com>

> Hi, there.
>
> I just found some illegal processing of SQLite.
> As I mentioned in title, "CREATE TABLE" query returns "database disk image
> is malformed" when disk is full and "SQLITE_IOCAP_ATOMIC" and
> "SQLITE_DEFAULT_AUTOVACUUM" is enabled.
> Here is my test scripts.
>
> sudo mkdir /mnt/db
> sudo chmod 777 /mnt/db
> sudo mount -t tmpfs -o size=16K tmpfs /mnt/db
> /home/yi.jang/git/sqlite_source/sqlite3 /mnt/db/test.db
>
> SQLite version 3.7.13 2012-06-11 02:05:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> pragma journal_mode=persist;
> persist
> sqlite> pragma page_size=4096;
> sqlite> create table a (id);
> sqlite> create table b (id);
> *Error: database or disk is full*
> sqlite> create table b (id);
> *Error: database disk image is malformed*
> sqlite>
>
> What I attached options to Makefile are as following.
>
> -DSQLITE_ENABLE_ATOMIC_WRITE
> -DSQLITE_DEFAULT_AUTOVACUUM=1
> -DSQLITE_DEFAULT_PAGE_SIZE=4096
>
> sqlite3.c is also changed.
>
> static int unixDeviceCharacteristics(sqlite3_file *id){
>   unixFile *p = (unixFile*)id;
>   if( p->ctrlFlags & UNIXFILE_PSOW ){
> return SQLITE_IOCAP_POWERSAFE_OVERWRITE | SQLITE_IOCAP_ATOMIC;
>   }else{
> return SQLITE_IOCAP_ATOMIC;
>   }
> }
>
> B.R.
> Yongil Jang.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DB change notification hooks across process barriers

[Yongil Jang]

I also have same problem.

What I'd done to solve this problem is reading change counter in db file
header.
I just implemented a simple code that reads file header.
When WAL mode is enabled, iChange variable in WAL header is used.

But... this code cannot be used for update notify...


2012/11/27 Tristan Van Berkom 

> Hi all,
> I just wanted to verify this detail.
>
> As I read here, 
> http://www.sqlite.org/c3ref/**update_hook.html
> ,
> the function sqlite3_update_hook() can be used to watch
> for row insert/update/delete changes to a DB.
>
> Now, the documentation does not mention anything about this
> function not working for modifications which were made by
> a separate process, however grepping the web shows a few
> warnings about this function.
>
> This one is over 2 years old but very detailed:
> 
> http://www.mail-archive.com/**sqlite-users@sqlite.org/**msg53058.html
>
> Is this still true ? Or has this been fixed in the past 2 years ?
>
> What is the preferred way of receiving change notifications from
> the SQLite connection ?
>
> If it's really the case that sqlite3_update_hook() does not
> work across separate connections/processes, could that please
> be noted in the documentation ? A simple note there could save
> people valuable time in experimentation.
>
> Best Regards,
>   -Tristan
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Create table returns "database disk image is malformed" when disk is full and "SQLITE_IOCAP_ATOMIC" and "SQLITE_DEFAULT_AUTOVACUUM" is enabled

[Yongil Jang]

Hi, there.

I just found some illegal processing of SQLite.
As I mentioned in title, "CREATE TABLE" query returns "database disk image
is malformed" when disk is full and "SQLITE_IOCAP_ATOMIC" and
"SQLITE_DEFAULT_AUTOVACUUM" is enabled.
Here is my test scripts.

sudo mkdir /mnt/db
sudo chmod 777 /mnt/db
sudo mount -t tmpfs -o size=16K tmpfs /mnt/db
/home/yi.jang/git/sqlite_source/sqlite3 /mnt/db/test.db

SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma journal_mode=persist;
persist
sqlite> pragma page_size=4096;
sqlite> create table a (id);
sqlite> create table b (id);
*Error: database or disk is full*
sqlite> create table b (id);
*Error: database disk image is malformed*
sqlite>

What I attached options to Makefile are as following.

-DSQLITE_ENABLE_ATOMIC_WRITE
-DSQLITE_DEFAULT_AUTOVACUUM=1
-DSQLITE_DEFAULT_PAGE_SIZE=4096

sqlite3.c is also changed.

static int unixDeviceCharacteristics(sqlite3_file *id){
  unixFile *p = (unixFile*)id;
  if( p->ctrlFlags & UNIXFILE_PSOW ){
return SQLITE_IOCAP_POWERSAFE_OVERWRITE | SQLITE_IOCAP_ATOMIC;
  }else{
return SQLITE_IOCAP_ATOMIC;
  }
}

B.R.
Yongil Jang.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS substring behavior

[Yongil Jang]

A little bit different.
That sentence of "full text search" is split as [ful, tex, sea, ull, ll, l,
ext, xt, xt, ear, arc, rch, ch, h] with 3 maximum length of key.
Of course, search pattern string length is limited to 3 and query is
changed as follows.

[Example: search 'earch']
SELECT ... FROM [FTS table] WHERE [search fields or table] MATCH 'ear' AND
([search field1] like '%earch%' OR [search field2] like '%earch%' OR ... OR
[search fieldn] like '%earch%')

The reason of ...
 1) 3 characters: To minimize FTS index size and to improve search
performance
 2) Adding like clauses: To refine result set
 2) many likes: To search all columns in FTS table when a table name is
used in MATCH clause.

BR,
Yongil Jang.



2012/11/9 Paul Vercellotti <pverce...@yahoo.com>

>
>
> That's a promising project; I hope it reaches maturity.
>
> I assume your modified tokenizer did a similar thing, like tokenizing
> "full text search" as [full, text, search, ull, ll, l, ext, xt, xt, earch,
> arch, rch, ch, h]?
>
> What worked and what did not work?
>
> Thanks,
> Paul
>
>
> 
>  From: Yongil Jang <yongilj...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Thursday, November 8, 2012 2:26 PM
> Subject: Re: [sqlite] FTS substring behavior
>
> How about look at following URL?
>
> https://github.com/jonasfj/trilite
>
> AFAIK, FTS doesn't support substring search.
> I also tried to edit FTS to find substring by changing simple tokenizer.
> It was worked partially, but not a good solution to use generally.
>
>
> 2012/11/9 Dan Kennedy <danielk1...@gmail.com>
>
> > On 11/09/2012 01:49 AM, Paul Vercellotti wrote:
> >
> >>
> >>
> >> Hi there,
> >>
> >> I wanted to clarify if FTS could provide any optimization for substring
> >> matches like '*ion*' or similar?
> >>
> >
> > No. I think it will actually search for tokens that start with the 4
> > ASCII characters "*ion" if you try that.
> >
> > Dan.
> >
> > __**_
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS substring behavior

[Yongil Jang]

How about look at following URL?

https://github.com/jonasfj/trilite

AFAIK, FTS doesn't support substring search.
I also tried to edit FTS to find substring by changing simple tokenizer.
It was worked partially, but not a good solution to use generally.


2012/11/9 Dan Kennedy 

> On 11/09/2012 01:49 AM, Paul Vercellotti wrote:
>
>>
>>
>> Hi there,
>>
>> I wanted to clarify if FTS could provide any optimization for substring
>> matches like '*ion*' or similar?
>>
>
> No. I think it will actually search for tokens that start with the 4
> ASCII characters "*ion" if you try that.
>
> Dan.
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] Does it necessary to sync directory on persist journal mode?

[Yongil Jang]

Please, don't forget my questions.

2012/9/10 Yongil Jang <yongilj...@gmail.com>

> Dear sqlite-users,
>
> I have a question about directory sync.
>
> Question: In case of journal file already exists on disk with persist
> journal mode,
>does it necessary to sync directory for first
> fsync(unixSync) call?
>
> AFAIK, directory sync should be called when a file is created or deleted.
> Does it mean that calling directory sync doesn't necessary for existing
> files?
>
> When I tested it with 1,000 insert queries, about 1,000 fsync calls were
> decreased on persist journal mode.
> To compare this, I just changed "unixOpen" function as like as follows.
>
>   int syncDir = 0;
>   if(isCreate && (eType==SQLITE_OPEN_MASTER_JOURNAL ||
> eType==SQLITE_OPEN_MAIN_JOURNAL || eType==SQLITE_OPEN_WAL))
>   {
> int exists = 0;
> unixAccess(pVfs, zPath, SQLITE_ACCESS_EXISTS, );
> if(!exists) {
>   syncDir = 1;
> }
>   }
>
> Thank you for reading this message.
>
> Best regards,
> YongIl Jang
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [Question] Does it necessary to sync directory on persist journal mode?

[Yongil Jang]

Dear sqlite-users,

I have a question about directory sync.

Question: In case of journal file already exists on disk with persist
journal mode,
   does it necessary to sync directory for first
fsync(unixSync) call?

AFAIK, directory sync should be called when a file is created or deleted.
Does it mean that calling directory sync doesn't necessary for existing
files?

When I tested it with 1,000 insert queries, about 1,000 fsync calls were
decreased on persist journal mode.
To compare this, I just changed "unixOpen" function as like as follows.

  int syncDir = 0;
  if(isCreate && (eType==SQLITE_OPEN_MASTER_JOURNAL ||
eType==SQLITE_OPEN_MAIN_JOURNAL || eType==SQLITE_OPEN_WAL))
  {
int exists = 0;
unixAccess(pVfs, zPath, SQLITE_ACCESS_EXISTS, );
if(!exists) {
  syncDir = 1;
}
  }

Thank you for reading this message.

Best regards,
YongIl Jang
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to check "atomic write" feature for 4KB page size on TCL test?

[Yongil Jang]

I found a reason of below failures.

This test is failed in case of "INSERT INTO abc VALUES(4, 5,
randstr(1000,1000))" query is executed in crash scripts.

When this query is executed, journal file is created because of record size
exceeds current page and crashed by test scripts.
However, if I change page size from 1KB to 4KB, journal file is not created
and not crashed. This means that new data is inserted and searched by
select query.

This failures are fixed by changing random string size from 1000 to 4072,
to make this test have same process with original.

Whatever, this test case is not for "atomic write", but it is only for
"crash-recovery" safety check when "atomic write" is enabled.

Best regards,
YongIl Jang.

2012/8/20 Yongil Jang <yongilj...@gmail.com>

> Dear all,
> I'm trying to check whether can I use "atomic write" feature with 4KB page
> size on my device.
> To do that, I'd used crash3.test on TCL test by doing like as follows.
>
> "./testfixture test/crash3.test"
>
> To check it correctly, testfixture is compiled with
> "-DSQLITE_ENABLE_ATOMIC_WRITE".
> All of this was passed without any errors as I expected.
>
> But, "crash3-1" test case only checks for 1KB page size of sqlite.
> To clarify this issue, I'd changed some values from "page_size = 1024" to
> "page_size = 4096" and added "-blocksize 4096".
>
> foreach {sql res2} [list \
>
>   {INSERT INTO abc VALUES(4, 5, 6)}{1 2 3 4 5 6} \
>
>   {DELETE FROM abc}{}\
>
>   {INSERT INTO abc SELECT * FROM abc}  {1 2 3 1 2 3} \
>
>   {UPDATE abc SET a = 2}   {2 2 3}   \
>
>   {INSERT INTO abc VALUES(4, 5, randstr(1000,1000))}   {n/a} \
>
>   {CREATE TABLE def(d, e, f)}  {n/a} \
>
> ] {
>
>   for {set ii 0} {$ii < 10} {incr ii} {
>
>
>
> db close
>
> forcedelete test.db test.db-journal
>
> sqlite3 db test.db
>
> do_test crash3-1.$tn.1 {
>
>   execsql {
>
> *PRAGMA page_size = 4096;*
>
> BEGIN;
>
> CREATE TABLE abc(a, b, c);
>
> INSERT INTO abc VALUES(1, 2, 3);
>
> COMMIT;
>
>   }
>
> } {}
>
> db close
>
>
>
> set crashfile test.db
>
> if {($ii%2)==0} { append crashfile -journal }
>
> set rand "SELECT randstr($tn,$tn);"
>
> do_test crash3-1.$tn.2 [subst {
>
>   crashsql -file $crashfile *-blocksize 4096* -char atomic {$rand
> $sql}
>   sqlite3 db test.db
>
>   execsql { PRAGMA integrity_check; }
>
> }] {ok}
>
>
>
> do_test2 crash3-1.$tn.3 {
>
>   execsql { SELECT * FROM abc }
>
> } {1 2 3} $res2
>
>
>
> incr tn
>
>   }
>
> }
>
> But, unfortunately, I got following failures.
>
> crash3-1.41.3...
> Expected: [{1 2 3} or {n/a}]
>  Got: [1 2 3 4 5 {/pB9kEkQw
> kEpYISEjN0q<:fT8comPoMZ9UdXVu_e86bCqrc8LPXIZ<8m!oln/4Z*4d9S3lIvAD6Ef>Q79Xs6n^LR YfC*feJ
> G9I^It?>tTdWVN5Ttoc<YbQyv4C2h6hK-Z47rqfUcmle*b7>962blIqn7uWx9d2xv/EiQ|61AZ-n|FOXjYrWc_^InVxJ:d/cEg7j32qdJf0GCSsnoUXN=GuER^_r4bc!R//GnRBmIgTj7aL18Bf/?4NPlF|-:YoveCRikCj2Aj:e1PgdwHytrZQc8G7c5_6=dsKkuui<KiB,C1bUWkE=aNS
> zb8TN+tE_,ec=M8/2pbtrdRwh53F>jk93guxHO:J6aHDKbGyom7W3ZO-.vjrIe_wq4lQPjjqTurZ5OamMCl+n|jf680X*IsmGL,d<9>3btX22cb2wjDRGL>shIs0WQkWJeKXLVneKZ!Pna56+iK-Y
> *Qsrrh0:.d+R4MxiezaDVue2LsF
> E3u-KwVqKScNOaJO>,TriK_S,t_Qb*bjco0Mg.feUlD8eLjqtpWDd?kgx0
> *n H.?_Y_a/I,K=5>P=d|.AakNc_/jq,e+9!06AGgrIYBzfhoemyj.r!MDZsg3T>y>R:1mc_|ysDpZwVs
> LHa9hNexhLc.9DLirYBHRR:rbMyJedJ_vN7/|vLQIJ8RBMqlj1<qqmkZKRiCpbolb9Zi,j2o!3q0qcVZO-|yZm8=o35Bjqu2f0n.YBR73|Eu5?-xzjG1K/Y2veXBfy^2VkI?l yum6Pe>G0c1X,_GG8Zx9j
> 9f|sQtfGhcbftpnP214GGWW1Fz8Z5k5AZ!-OC<Psg+nlYdp9Tsdq.WfVx?KUhjUg
> mki8<WN4K^s*!.Y_u,4s/CW3n,29d4zTN:=5Y3:8.25_10pwE
> Am I wrong?
> Or, This test case is not for safety check of "atomic write"?
>
> Best regards,
> YongIl Jang.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to check "atomic write" feature for 4KB page size on TCL test?

[Yongil Jang]

Dear all,
I'm trying to check whether can I use "atomic write" feature with 4KB page
size on my device.
To do that, I'd used crash3.test on TCL test by doing like as follows.

"./testfixture test/crash3.test"

To check it correctly, testfixture is compiled with
"-DSQLITE_ENABLE_ATOMIC_WRITE".
All of this was passed without any errors as I expected.

But, "crash3-1" test case only checks for 1KB page size of sqlite.
To clarify this issue, I'd changed some values from "page_size = 1024" to
"page_size = 4096" and added "-blocksize 4096".

foreach {sql res2} [list \

  {INSERT INTO abc VALUES(4, 5, 6)}{1 2 3 4 5 6} \

  {DELETE FROM abc}{}\

  {INSERT INTO abc SELECT * FROM abc}  {1 2 3 1 2 3} \

  {UPDATE abc SET a = 2}   {2 2 3}   \

  {INSERT INTO abc VALUES(4, 5, randstr(1000,1000))}   {n/a} \

  {CREATE TABLE def(d, e, f)}  {n/a} \

] {

  for {set ii 0} {$ii < 10} {incr ii} {



db close

forcedelete test.db test.db-journal

sqlite3 db test.db

do_test crash3-1.$tn.1 {

  execsql {

*PRAGMA page_size = 4096;*

BEGIN;

CREATE TABLE abc(a, b, c);

INSERT INTO abc VALUES(1, 2, 3);

COMMIT;

  }

} {}

db close



set crashfile test.db

if {($ii%2)==0} { append crashfile -journal }

set rand "SELECT randstr($tn,$tn);"

do_test crash3-1.$tn.2 [subst {

  crashsql -file $crashfile *-blocksize 4096* -char atomic {$rand $sql}

  sqlite3 db test.db

  execsql { PRAGMA integrity_check; }

}] {ok}



do_test2 crash3-1.$tn.3 {

  execsql { SELECT * FROM abc }

} {1 2 3} $res2



incr tn

  }

}

But, unfortunately, I got following failures.

crash3-1.41.3...
Expected: [{1 2 3} or {n/a}]
 Got: [1 2 3 4 5 {/pB9kEkQw
kEpYISEjN0q<:fT8comPoMZ9UdXVu_e86bCqrc8LPXIZ<8m!oln/4Z*4d9S3lIvAD6Ef>Q79Xs6n^LRtTdWVN5Ttoc962blIqn7uWx9d2xv/EiQ|61AZ-n|FOXjYrWc_^InVxJ:d/cEg7j32qdJf0GCSsnoUXN=GuER^_r4bc!R//GnRBmIgTj7aL18Bf/?4NPlF|-:YoveCRikCj2Aj:e1PgdwHytrZQc8G7c5_6=dsKkuuijk93guxHO:J6aHDKbGyom7W3ZO-.vjrIe_wq4lQPjjqTurZ5OamMCl+n|jf680X*IsmGL,d<9>3btX22cb2wjDRGL>shIs0WQkWJeKXLVneKZ!Pna56+iK-Y
*Qsrrh0:.d+R4MxiezaDVue2LsF
E3u-KwVqKScNOaJO>,TriK_S,t_Qb*bjco0Mg.feUlD8eLjqtpWDd?kgx0
*nP=d|.AakNc_/jq,e+9!06AGgrIYBzfhoemyj.r!MDZsg3T>y>R:1mc_|ysDpZwVs
LHa9hNexhLc.9DLirYBHRR:rbMyJedJ_vN7/|vLQIJ8RBMqlj1

[sqlite] How to get changed status of database?

[Yongil Jang]

Dear all,

I'm trying to check database file whether it had been changed by other
connections.
First hint was a change counter value of sqlite file header.
It was good solution to me, whatever I have to make implementation into
sqlite to read 24'th byte offset in sqlite file.
(It is also difficult job to me. :) )

However, this change counter value is not updated when I use WAL mode.
What I've found on the source of sqlite is walIndexTryHdr function that
checks changed status of current wal index by using memory compare.
Could anyone give me a hint how to check changed status of current database
using wal mode?

Regards,
Yongil jang
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] substr bug in 3.7.13?

[Yongil Jang]

Oh, sorry.
It was my fault.

It works very well with starting number 1. :)

2012/6/19 Bart Smissaert <bart.smissa...@gmail.com>

> Should that zero not be a 1?
> From the documentation:
> The left-most character of X is number 1
>
> RBS
>
>
> On 6/19/12, Yongil Jang <yongilj...@gmail.com> wrote:
> > Dear all,
> >
> > I've found following result when I try to use 'substr' function.
> >
> > sqlite> create table test (data text);
> > sqlite> insert into test values ('010101');
> > sqlite> select substr(data, 0, 2) from test;
> > 0
> > sqlite> select substr(data, 0, 3) from test;
> > 01
> >
> > As you can see, string length should be one plus value to get correct
> > length of string.
> > I'm using sqlite 3.7.13 legacy source code and compiled on my Ubuntu
> server
> > 10.04 64bit.
> >
> > Thank you for read this message.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] substr bug in 3.7.13?

[Yongil Jang]

Dear all,

I've found following result when I try to use 'substr' function.

sqlite> create table test (data text);
sqlite> insert into test values ('010101');
sqlite> select substr(data, 0, 2) from test;
0
sqlite> select substr(data, 0, 3) from test;
01

As you can see, string length should be one plus value to get correct
length of string.
I'm using sqlite 3.7.13 legacy source code and compiled on my Ubuntu server
10.04 64bit.

Thank you for read this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SIGBUS error with WAL mode on disk full

[Yongil Jang]

Thanks a lot!

It is very helpful for my job.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SIGBUS error in case of disk full with WAL mode

[Yongil Jang]

Dear all,

Thank you for your help.

I tried -DSQLITE_SHM_DIRECTORY="/dev/shm" option for make file.
It works very well.
(One line should be added to make "/dev/shm" directory before calling file
open)

But, I didn't tried to use locking mode because of multiple processes can
use same sqlite database at a same time.

I have one more question.

Following sentences are found on sqlite source.
And, there is a comment about database corruption.
Could I get more detailed explanation about this?


** can be enabled at compile-time using -DSQLITE_SHM_DIRECTORY="/dev/shm"
** or the equivalent.  The use of the SQLITE_SHM_DIRECTORY compile-time
** option results in an incompatible build of SQLite;  *builds of SQLite*
*** that with differing SQLITE_SHM_DIRECTORY settings attempt to use the*
*** same database file at the same time, database corruption will likely*
*** result*. The SQLITE_SHM_DIRECTORY compile-time option is considered
** "unsupported" and may go away in a future SQLite release.


Best wishes,
Jang.

2012/3/9 Scott Hess 

> On Thu, Mar 8, 2012 at 9:28 AM, Pavel Ivanov  wrote:
> >> Question:  Does anybody know of a better way to get memory shared among
> >> processes other than to create a fake file and mmap() it?  Are there
> some
> >> magic options to mmap() (perhaps Linux-only options) that prevent it
> from
> >> actually writing to disk?
> >
> > Why don't you use shm_open() instead of a real file? I'm not sure
> > though how it behaves with chroot jail.
>
> I do not recall the full semantics of shm_open(), but I _think_ that
> it shares the sysv shared-memory problem where the memory sticks
> around until explicitly deleted.  Using a fake file with mmap() will
> create a segment which only exists until the last process using it
> goes away.  This can become a resource issue on some systems.  Also,
> the name would have to be carefully constructed to prevent conflicts,
> as the namespace may not be the same as the file path namespace (even
> path-like names may have different length restrictions).  Like maybe
> base64(hash(canonical_path(dbpath))).
>
> BTW, the SQLite docs indicate that as of 3.7.4, you can arrange to use
> an exclusive mode which allows WAL to work without shm.  That may be a
> reasonable approach for some subset of users with this kind of
> problem.
>
> -scott
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite 3.7.8 version - insert query speed decreased

[YONGIL JANG]

Dear all,


I'm using sqlite 3.7.6.3 version on my system.

And, I'd tried to use 3.7.8 version, because of retrieval query speed of
distinct and group by has been increased.

Really, it shows improved performance for several scenes in my application.


However, insert queries of 1,000 entries to sqlite shows decreased
performance than 3.7.6.3 whatever I use transaction or not.

Some case shows 2 times slower than 3.7.6.3.


Is there any patches?

should I wait for newer version?


I can wait for newer version. But, I'd like to execute it as soon as
possible. ^^


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

[sqlite] WAL and changing modified file time of .db file

[YONGIL JANG]

Dear all,



I have a question of WAL journal mode.


I'm doing some project that uses sqlite with WAL journal mode.

Some days ago, I'd found DB crash and couldn't execute any SQL queries.

But, for a while, I couldn't find any scenarios can make this DB crash.


My question is changing modified file time of .db file can make DB crash
problems?

For some reason, I change last modified time of database file after it has
been opened.

My wondering point is this modified file time.


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