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

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

Re: [sqlite] WAL: no schema after close

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

[sqlite] Changing the default page_size in 3.12.0

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

[sqlite] How to modify page_size

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

Re: [sqlite] Sqlite problem possible bug

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

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

o. 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 > unixDe

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

> 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. ___

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

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,

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

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

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

Re: [sqlite] Android sqlite error

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 >

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

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

[sqlite] Different query plans between 3.7.13 and 3.8.0.2

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

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

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;

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

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

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

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

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

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 re

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

' 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

[sqlite] Using "COLLATE nocase" with BETWEEN

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

Re: [sqlite] sequential row numbers from query

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');

Re: [sqlite] [Question] SQLITE_FCNTL_CHUNK_SIZE

64 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> &g

[sqlite] [Question] SQLITE_FCNTL_CHUNK_SIZE

, 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?

r 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.

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

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에

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

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 >

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

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)

!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"); <-- sq

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

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 >> &quo

Re: [sqlite] search string in the db question

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

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

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

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() fun

Re: [sqlite] update to limits infomation

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

Re: [sqlite] SELECT and UPDATE in single query

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

Re: [sqlite] Need help with query

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

Re: [sqlite] sqlite version of android 4.0.3

~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. > ___ >

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

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

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

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 allocateB

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

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 &qu

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

ITE" 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. Co

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

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

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

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_OVERWRI

Re: [sqlite] FTS substring behavior

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 toke

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

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

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

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

f(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 re

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

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

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

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

[sqlite] How to get changed status of database?

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?

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 Jan

[sqlite] substr bug in 3.7.13?

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

[sqlite] SIGBUS error with WAL mode on disk full

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

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

[sqlite] SQLite 3.7.8 version - insert query speed decreased

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] WAL and changing modified file time of .db file

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