Re: [sqlite] sqlite-users Digest, Vol 59, Issue 28
YAN HONG YE wrote: > b c > 897313 897313 > 854809 868763 > 854808 854809 > 854980 854808 > 854974 854980 > 854973 854974 > 854780 854973 > 854778 854780 > 854769 854778 > 854768 854769 > 854738 854768 > 854691 854738 > 854690 854691 > 853709 854690 > 856707 853709 > 856697 856707 > 856695 856697 > 927681 856695 > 927696 927681 > 927695 927696 > 927694 927695 > 927693 927694 > 927692 927693 > 927691 927692 > 927690 927691 > 927690 > 910986 > 910985 > I have a table like this, how to get result not exists:b=c I don't understand. In your example, which rows do you want returned, and why? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 59, Issue 28
b c 897313 897313 854809 868763 854808 854809 854980 854808 854974 854980 854973 854974 854780 854973 854778 854780 854769 854778 854768 854769 854738 854768 854691 854738 854690 854691 853709 854690 856707 853709 856697 856707 856695 856697 927681 856695 927696 927681 927695 927696 927694 927695 927693 927694 927692 927693 927691 927692 927690 927691 927690 910986 910985 I have a table like this, how to get result not exists:b=c my command is : select c from mytable where not exists( b=c); but not correct. how to get correct result? ___ 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
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
> 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] light weight write barriers
Nico Williams, on 11/26/2012 03:05 PM wrote: Vlad, You keep saying that programmers don't understand "barriers". You've provided no evidence of this. Meanwhile memory barriers are generally well understood, and every programmer I know understands that a "barrier" is a synchronization primitive that says that all operations of a certain type will have completed prior to the barrier returning control to its caller. Well, your understanding of memory barriers is wrong, and you are illustrating that the memory barriers concept is not so well understood on practice. Simplifying, memory barrier instructions are not "cache flush" of this CPU as it is often thought. They set order how reads or writes from other CPUs are visible on this CPU. And nothing else. Locally on each CPU reads and writes are always seen in order. So, (1) on a single CPU system memory barrier instructions don't make any sense and (2) they should go at least in a pair for each participating in the interaction CPU, otherwise it's an apparent sign of a mistake. There's nothing similar in storage, because storage has strong consistency requirements even if it is distributed. All those clouds and hadoops with weak consistency requirements are outside of this discussion, although even they don't have anything similar to memory barriers. As I already wrote, concept of a flat Earth and Sun revolving around is also very simple to understand. Are you still using this concept? So just give us a barrier. Similarly to the flat Earth, I'd strongly suggest you to start using adequate concept of what you want to achieve starting from what I proposed few e-mails ago in this thread. If you look at it, it offers exactly what you want, only named correctly. Vlad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_changes and sqlite3_/extended_/errcode would benefit greatly of being available in stmt-specific variants!?
On Wed, Nov 21, 2012 at 4:10 PM, Mikael wrote: > Dear Dr. Hipp, > > There's with great reason there's the SQLITE_THREADSAFE=1 mode and also > the possibility to run several statements in parallell on one CPU core. > > Ensuring that no DB activity happens between that you do sqlite3_step() *on > a stmt* and sqlite3_changes() and sqlite3_/extended_/errcode() *on (as > in, regarding) the same stmt* is like the most cumbersome requirement ever, > requiring complexity and CPU resources spent by the user. > SQLite serializes access to each database connection. So if you have two or more threads trying to run different prepared statements from the same database connection at the same time, mutexes will ensure that only one thread runs while other the others wait. There is no parallelism at the prepared statement level. You can have two or more threads running prepared statements from different database connections at the same time and they will run in parallel, mostly (subject to operating system restrictions.) So, yes, we could add a few extra interfaces to SQLite to make things easier when running multiple prepared statements from the same database connection in different threads. But those extra interfaces are extra work to maintain and test and they do add overhead, however small it might be. And they are only useful to applications that are doing things that they maybe shouldn't be doing in the first place. If you really need to do this, you can make your own wrappers around sqlite3_step(). Use sqlite_db_mutex() to get the mutex for your database connection. Call sqlite3_mutex_enter() on that mutex. (It is a recursive mutex so doign that is safe.) Run your sqlite3_step() then sqlite3_changes() and/or sqlite3_extended_errcode(), and save the results of the last two in some fields of your wrapper object. Then run sqlite3_mutex_leave() on the DB mutex and return the result of the sqlite3_step(). If you write such a wrapper, then accessing the information you need becomes painless for you, and it does not add overhead to the millions of other SQLite applications that do not need it. > > The expense in SQLite of keeping track of changes and error code on a per > statement level can't be more than a 16 bytes per statement and a couple of > CPU instructions, can it? I mean, everywhere in SQLite's code where changes > or errcodes that regard a stmt are registered, the actual stmt regarded is > in some local variable already anyhow isn't it? > > SQLite is an overwhelmingly good library, though this particular thing is > really a matter of non-elegance from a user point of view, I mean there's > an enormous amount of cases where you want to check errcode or/and pick up > the number of changed rows right after sqlite3_step() so why design for > putting expensive-enough complexity on the user when there's no need for it? > > Thank you and with warm regards, > Mikael > > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_changes and sqlite3_/extended_/errcode would benefit greatly of being available in stmt-specific variants!?
Dear list, Do you have any feedback on this? I was pretty surprised when I ran into it myself actually ... looks like the kind of thing where lots of complexity for users could be saved by spending some very few CPU cycles in SQLite. Thanks 2012/11/21 Mikael > Dear Dr. Hipp, > > There's with great reason there's the SQLITE_THREADSAFE=1 mode and also > the possibility to run several statements in parallell on one CPU core. > > Ensuring that no DB activity happens between that you do sqlite3_step() *on > a stmt* and sqlite3_changes() and sqlite3_/extended_/errcode() *on (as > in, regarding) the same stmt* is like the most cumbersome requirement ever, > requiring complexity and CPU resources spent by the user. > > The expense in SQLite of keeping track of changes and error code on a per > statement level can't be more than a 16 bytes per statement and a couple of > CPU instructions, can it? I mean, everywhere in SQLite's code where changes > or errcodes that regard a stmt are registered, the actual stmt regarded is > in some local variable already anyhow isn't it? > > SQLite is an overwhelmingly good library, though this particular thing is > really a matter of non-elegance from a user point of view, I mean there's > an enormous amount of cases where you want to check errcode or/and pick up > the number of changed rows right after sqlite3_step() so why design for > putting expensive-enough complexity on the user when there's no need for it? > > Thank you and with warm regards, > Mikael > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing matchinfo() from metro
Actually just solved the problem...
C# code
in SQLite.cs, add
[DllImport("sqlite3", EntryPoint = "sqlite3_value_blob", CallingConvention
= CallingConvention.Cdecl)]
public static extern IntPtr valueBlob (IntPtr value);
[DllImport("sqlite3", EntryPoint = "sqlite3_value_bytes", CallingConvention
= CallingConvention.Cdecl)]
public static extern int valueBytes (IntPtr value);
in callback func
public static void rank (IntPtr DatabaseConnectionHandle, int
numberOfValues, IntPtr[] apVal)
{
..
int len = SQLite3.valueBytes(apVal[0]);
int numInts = len / sizeof(Int32);
int[] theValues = new int[numInts];
IntPtr ptr = SQLite3.valueBlob(apVal[0]); // Return value from
matchinfo()
for (int count = 0; count < numInts ; count++) {
if (count > 0)
ptr = IntPtr.Add(ptr, sizeof(Int32));
theValues[count] = Marshal.ReadInt32(ptr);
}
... do ranking calc
}
Sent from Windows Mail
*From:* Kenneth Grant
*Sent:* November 28, 2012 6:24 AM
*To:* sqlite-users@sqlite.org
*Subject:* Accessing matchinfo() from metro
Using the SQLite3 DLL ind Windows 8 metro with the C# managed code wrapper
work just great. And adding marshaling code for a ranking function also
works (as long as you keep a static var referencing the function and use
that var as the registration reference). But, the argument for the info
passed to the ranking function is not helpful as managed code can only
reference a pointer to a single object like a long, int, etc. general
pointers are not allows in managed code.
So the question is, has anybody solved this call-back issue.
Sent from my iPad
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help. Read only process being blocked by writer process.
Hi, CREATE TABLE rev(current); INSERT INTO rev VALUES(0); CREATE TABLE data(..., revision); Readers: SELECT * FROM data JOIN rev ON revision = current WHERE ... ; // or "SELECT current FROM rev" into var and passing it value in "SELECT * FROM data WHERE revision=?" Writer: // insert new records, old records remains SELECT current + 1 FROM rev; // into some variable BEGIN; INSERT INTO data(..., revision) VALUES (..., current ); INSERT INTO data(..., revision) VALUES (..., current ); ... ... repeat 1000 times ... COMMIT; BEGIN; INSERT INTO data(..., revision) VALUES (..., current ); INSERT INTO data(..., revision) VALUES (..., current ); ... ... repeat 1000 times ... COMMIT; // repeat inserting until all records are inserted; 1000 is a number of records taken by a wild guess ) // now switch readers to new records BEGIN; UPDATE rev SET current = current + 1; COMMIT; // no need for a begin/commit just to point that this runs inside its own transaction // now delete old records again incrementally // repeat this block until records stop deleting from table BEGIN; SELECT Max(rowid) - 1000 FROM data; // into variable MaxId DELETE FROM data WHERE revision = current - 1 AND rowid > MaxId; COMMIT; // done, there are only new records in a table, repeat above steps to insert new bunch of records Regads, GG Wed 28 Nov 2012 09:47:50 от Alejandro Martínez : > > > > >I have one process that each 30 minutes refills several tables in this > manner: > > sqlite3_open_v2(CACHEDB_PATH, &sqcache_conn, SQLITE_OPEN_CREATE | > SQLITE_OPEN_READWRITE, NULL) > > - For each table: > > begin deferred transaction; delete from [table]; > insert into table ... > insert into table ... > insert into table ... > insert into table ... > [up to 180.000 rows] > commit; > > and sometimes the commit fails, so it is retried. (why would it fail? its > the only writter) > > And then i have many other processes that open that sqlite database read > only. sqlite3_open_v2(_dbfile, &sqcache_conn, SQLITE_OPEN_READONLY, NULL) > and sqlite3_busy_timeout(sqcache_conn, 5000) > > These processes create very simple prepared statements to query that tables. > > And the big problem i'm having, is that when i step these prepared > statements, they lock for 5 seconds and then fail. > > And i put that busy timeout just for completeness, cause i wasn't expecting > any locking because for being a read only query. > > I really need these queries not to lock or fail. > > What am i doing wrong? > Any suggestions? > > Thank you, > Alejandro > ___ > 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] Please help. Read only process being blocked by writer process.
Richard Hipp wrote: On Wed, Nov 28, 2012 at 9:58 AM, Alejandro Martínez wrote: And wouldn't PRAGMA read_uncommitted achieve the effect i was expecting? Or does that cause other problems? read_uncommitted only works if both the read and writer are in the same process and are using shared cache. Reading "old" or inconsistent data would not be a problem for me. (as long as it is not corrupted data). That's really the crux of the problem. Suppose the writer has needing to split a btree node, for example. To do this, the writer would typically write two new child pages and then overwrite the old b-tree page with the parent page. If the writer were part way through this sequence when the reader comes along, the reader would see corrupt data, since the btree structure would be only partially updated and hence not well-formed. That's one of the fundamental problems with update-in-place DB designs. (Aside from their crash vulnerability and expensive crash recovery...) MVCC via copy-on-write has none of these issues. On Wed, Nov 28, 2012 at 11:20 AM, Richard Hipp wrote: On Wed, Nov 28, 2012 at 7:09 AM, Alejandro Martínez wrote: Ok, i will probably do that. Thank you. But i'd like to know. Why doesn't this work without wal? A read only operation shouldn't block, right? If you are not running WAL, then the database is updated directly. That means that there can be no readers active when a write is in progress because then the readers would see an incomplete and uncommitted transaction. And regarding the commit failing, does that need a busy timeout handler too? From documentation i though it would just wait until all readers are done reading and then write. And that further incoming readers would wait for those 5 seconds. And i was expecting not to really wait, as the commit should be pretty quick, right? I'm puzzled. On Wed, Nov 28, 2012 at 10:01 AM, Richard Hipp wrote: On Wed, Nov 28, 2012 at 6:56 AM, Alejandro Martínez < elpeq...@gmail.com wrote: Is that the only way? When i had done that in the past, the wal file grew constantly and i am afraid it could fill the hard disk. That could happen if say... one of the reading processes doesn't properly sqlite3_reset a prepared statement after stepping it. right? Correct. The WAL file will grow until a checkpoint resets it. And a checkpoint cannot reset the WAL file while there is a pending transaction. So if you have a statement holding a transaction open, the WAL file will grow without bound. The solution there is to not hold read transactions open indefinitely. Call sqlite3_reset() when you are done with a statement so that its implied read transaction will close. Thank you for your quick answer. On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp wrote: PRAGMA journal_mode=WAL -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help. Read only process being blocked by writer process.
On Wed, Nov 28, 2012 at 9:58 AM, Alejandro Martínez wrote: > And wouldn't PRAGMA read_uncommitted achieve the effect i was expecting? Or > does that cause other problems? > read_uncommitted only works if both the read and writer are in the same process and are using shared cache. > > Reading "old" or inconsistent data would not be a problem for me. (as long > as it is not corrupted data). > That's really the crux of the problem. Suppose the writer has needing to split a btree node, for example. To do this, the writer would typically write two new child pages and then overwrite the old b-tree page with the parent page. If the writer were part way through this sequence when the reader comes along, the reader would see corrupt data, since the btree structure would be only partially updated and hence not well-formed. > > > On Wed, Nov 28, 2012 at 11:20 AM, Richard Hipp wrote: > > > On Wed, Nov 28, 2012 at 7:09 AM, Alejandro Martínez > >wrote: > > > > > Ok, i will probably do that. Thank you. > > > > > > But i'd like to know. Why doesn't this work without wal? A read only > > > operation shouldn't block, right? > > > > > > > If you are not running WAL, then the database is updated directly. That > > means that there can be no readers active when a write is in progress > > because then the readers would see an incomplete and uncommitted > > transaction. > > > > > > > > > > > > And regarding the commit failing, does that need a busy timeout handler > > > too? From documentation i though it would just wait until all readers > are > > > done reading and then write. And that further incoming readers would > wait > > > for those 5 seconds. And i was expecting not to really wait, as the > > commit > > > should be pretty quick, right? > > > > > > I'm puzzled. > > > > > > > > > > > > On Wed, Nov 28, 2012 at 10:01 AM, Richard Hipp wrote: > > > > > > > On Wed, Nov 28, 2012 at 6:56 AM, Alejandro Martínez < > > elpeq...@gmail.com > > > > >wrote: > > > > > > > > > Is that the only way? > > > > > > > > > > When i had done that in the past, the wal file grew constantly and > i > > am > > > > > afraid it could fill the hard disk. > > > > > > > > > > That could happen if say... one of the reading processes doesn't > > > > > properly sqlite3_reset a prepared statement after stepping it. > right? > > > > > > > > > > > > > Correct. The WAL file will grow until a checkpoint resets it. And a > > > > checkpoint cannot reset the WAL file while there is a pending > > > transaction. > > > > So if you have a statement holding a transaction open, the WAL file > > will > > > > grow without bound. > > > > > > > > The solution there is to not hold read transactions open > indefinitely. > > > > Call sqlite3_reset() when you are done with a statement so that its > > > implied > > > > read transaction will close. > > > > > > > > > > > > > > > > > > Thank you for your quick answer. > > > > > > > > > > > > > > > > > > > > On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp > > wrote: > > > > > > > > > > > PRAGMA journal_mode=WAL > > > > > ___ > > > > > sqlite-users mailing list > > > > > sqlite-users@sqlite.org > > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > > > > > > > > > -- > > > > 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 > > > > > > > > > > > -- > > 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 > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help. Read only process being blocked by writer process.
And wouldn't PRAGMA read_uncommitted achieve the effect i was expecting? Or does that cause other problems? Reading "old" or inconsistent data would not be a problem for me. (as long as it is not corrupted data). On Wed, Nov 28, 2012 at 11:20 AM, Richard Hipp wrote: > On Wed, Nov 28, 2012 at 7:09 AM, Alejandro Martínez >wrote: > > > Ok, i will probably do that. Thank you. > > > > But i'd like to know. Why doesn't this work without wal? A read only > > operation shouldn't block, right? > > > > If you are not running WAL, then the database is updated directly. That > means that there can be no readers active when a write is in progress > because then the readers would see an incomplete and uncommitted > transaction. > > > > > > > And regarding the commit failing, does that need a busy timeout handler > > too? From documentation i though it would just wait until all readers are > > done reading and then write. And that further incoming readers would wait > > for those 5 seconds. And i was expecting not to really wait, as the > commit > > should be pretty quick, right? > > > > I'm puzzled. > > > > > > > > On Wed, Nov 28, 2012 at 10:01 AM, Richard Hipp wrote: > > > > > On Wed, Nov 28, 2012 at 6:56 AM, Alejandro Martínez < > elpeq...@gmail.com > > > >wrote: > > > > > > > Is that the only way? > > > > > > > > When i had done that in the past, the wal file grew constantly and i > am > > > > afraid it could fill the hard disk. > > > > > > > > That could happen if say... one of the reading processes doesn't > > > > properly sqlite3_reset a prepared statement after stepping it. right? > > > > > > > > > > Correct. The WAL file will grow until a checkpoint resets it. And a > > > checkpoint cannot reset the WAL file while there is a pending > > transaction. > > > So if you have a statement holding a transaction open, the WAL file > will > > > grow without bound. > > > > > > The solution there is to not hold read transactions open indefinitely. > > > Call sqlite3_reset() when you are done with a statement so that its > > implied > > > read transaction will close. > > > > > > > > > > > > > > Thank you for your quick answer. > > > > > > > > > > > > > > > > On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp > wrote: > > > > > > > > > PRAGMA journal_mode=WAL > > > > ___ > > > > sqlite-users mailing list > > > > sqlite-users@sqlite.org > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > > > > -- > > > 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 > > > > > > -- > 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
Re: [sqlite] Prefix joins
Eleytherios Stamatogiannakis wrote: > select * from a,b where a.c1 LIKE b.c1||'%'; > > but with the additional guarantee for the optimizer that all pattern > matching will happen on the postfix and not on the prefix, so the > optimizer will be able to use an index to do the join. The closest you can get is something along these lines: where a.c1 between b.c1 and b.c1 || x'FF'; This should use a full scan on b, and an index on a.c1 if available. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Prefix joins
Hello, Is there a version of "LIKE", in SQLite, which makes it clear that we only care to have a pattern on the postfix of a column? An example follows: select * from a,b where POSTFIX_LIKE(a.c1, b.c1 ,'%'); In above example, POSTFIX_LIKE works in the same way as if we had written: select * from a,b where a.c1 LIKE b.c1||'%'; but with the additional guarantee for the optimizer that all pattern matching will happen on the postfix and not on the prefix, so the optimizer will be able to use an index to do the join. Thanks in advance, Lefteris. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Does the provider work with EntityFramework.dll 4.4 and 5.0?
Hi, I checked out system.data.sqlite source and built the bundle setup package. Using the package I am able to make the design-time component work in VS2012. I have 2 questions: 1) Does the provider work with EntityFramework.dll 4.4 and 5.0? 2) How to build a NuGet package for VS2012? The page at http://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki does not say anything about it? I used the old System.Data.Sqlite.1.0.82.0.nupkg and it seemed not working. Thanks, Quanren ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help. Read only process being blocked by writerprocess.
Alejandro Martínez wrote: > But i'd like to know. Why doesn't this work without wal? A read only > operation shouldn't block, right? Which piece of documentation has led you to believe so? In a rollback journal mode, a database may have multiple readers OR a single writer at any point in time. In WAL mode, a database may have multiple readers AND a single writer at any point in time. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite and TCL on linux
On Wed, Nov 28, 2012 at 7:12 AM, brijesh_philips wrote: > Hi, > > I am using Ubuntu and i have TCL installed in my machine, >1. How do i link SQLite amalgamation with TCL ? > >From within a TCL script: package require sqlite3 >2. What steps should i do to get the regression tests working ? > In a shell, at the top of the SQLite source tree: ./configure; make test 3. In which site i can download the Regression tests ? > They are part of the SQLite source tree. > > > > Thanks > Brijesh > > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/Sqlite-and-TCL-on-linux-tp65742.html > Sent from the SQLite mailing list archive at Nabble.com. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- 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] Sqlite and TCL on linux
Hi, I am using Ubuntu and i have TCL installed in my machine, 1. How do i link SQLite amalgamation with TCL ? 2. What steps should i do to get the regression tests working ? 3. In which site i can download the Regression tests ? Thanks Brijesh -- View this message in context: http://sqlite.1065341.n5.nabble.com/Sqlite-and-TCL-on-linux-tp65742.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help. Read only process being blocked by writer process.
On Wed, Nov 28, 2012 at 7:09 AM, Alejandro Martínez wrote: > Ok, i will probably do that. Thank you. > > But i'd like to know. Why doesn't this work without wal? A read only > operation shouldn't block, right? > If you are not running WAL, then the database is updated directly. That means that there can be no readers active when a write is in progress because then the readers would see an incomplete and uncommitted transaction. > > And regarding the commit failing, does that need a busy timeout handler > too? From documentation i though it would just wait until all readers are > done reading and then write. And that further incoming readers would wait > for those 5 seconds. And i was expecting not to really wait, as the commit > should be pretty quick, right? > > I'm puzzled. > > > > On Wed, Nov 28, 2012 at 10:01 AM, Richard Hipp wrote: > > > On Wed, Nov 28, 2012 at 6:56 AM, Alejandro Martínez > >wrote: > > > > > Is that the only way? > > > > > > When i had done that in the past, the wal file grew constantly and i am > > > afraid it could fill the hard disk. > > > > > > That could happen if say... one of the reading processes doesn't > > > properly sqlite3_reset a prepared statement after stepping it. right? > > > > > > > Correct. The WAL file will grow until a checkpoint resets it. And a > > checkpoint cannot reset the WAL file while there is a pending > transaction. > > So if you have a statement holding a transaction open, the WAL file will > > grow without bound. > > > > The solution there is to not hold read transactions open indefinitely. > > Call sqlite3_reset() when you are done with a statement so that its > implied > > read transaction will close. > > > > > > > > > > Thank you for your quick answer. > > > > > > > > > > > > On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp wrote: > > > > > > > PRAGMA journal_mode=WAL > > > ___ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > -- > > 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 > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help. Read only process being blocked by writer process.
Ok, i will probably do that. Thank you. But i'd like to know. Why doesn't this work without wal? A read only operation shouldn't block, right? And regarding the commit failing, does that need a busy timeout handler too? From documentation i though it would just wait until all readers are done reading and then write. And that further incoming readers would wait for those 5 seconds. And i was expecting not to really wait, as the commit should be pretty quick, right? I'm puzzled. On Wed, Nov 28, 2012 at 10:01 AM, Richard Hipp wrote: > On Wed, Nov 28, 2012 at 6:56 AM, Alejandro Martínez >wrote: > > > Is that the only way? > > > > When i had done that in the past, the wal file grew constantly and i am > > afraid it could fill the hard disk. > > > > That could happen if say... one of the reading processes doesn't > > properly sqlite3_reset a prepared statement after stepping it. right? > > > > Correct. The WAL file will grow until a checkpoint resets it. And a > checkpoint cannot reset the WAL file while there is a pending transaction. > So if you have a statement holding a transaction open, the WAL file will > grow without bound. > > The solution there is to not hold read transactions open indefinitely. > Call sqlite3_reset() when you are done with a statement so that its implied > read transaction will close. > > > > > > Thank you for your quick answer. > > > > > > > > On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp wrote: > > > > > PRAGMA journal_mode=WAL > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > 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
Re: [sqlite] Please help. Read only process being blocked by writer process.
On Wed, Nov 28, 2012 at 6:56 AM, Alejandro Martínez wrote: > Is that the only way? > > When i had done that in the past, the wal file grew constantly and i am > afraid it could fill the hard disk. > > That could happen if say... one of the reading processes doesn't > properly sqlite3_reset a prepared statement after stepping it. right? > Correct. The WAL file will grow until a checkpoint resets it. And a checkpoint cannot reset the WAL file while there is a pending transaction. So if you have a statement holding a transaction open, the WAL file will grow without bound. The solution there is to not hold read transactions open indefinitely. Call sqlite3_reset() when you are done with a statement so that its implied read transaction will close. > > Thank you for your quick answer. > > > > On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp wrote: > > > PRAGMA journal_mode=WAL > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help. Read only process being blocked by writer process.
Is that the only way? When i had done that in the past, the wal file grew constantly and i am afraid it could fill the hard disk. That could happen if say... one of the reading processes doesn't properly sqlite3_reset a prepared statement after stepping it. right? Thank you for your quick answer. On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp wrote: > PRAGMA journal_mode=WAL ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help. Read only process being blocked by writer process.
On Wed, Nov 28, 2012 at 6:47 AM, Alejandro Martínez wrote: > I have one process that each 30 minutes refills several tables in this > manner: > > sqlite3_open_v2(CACHEDB_PATH, &sqcache_conn, SQLITE_OPEN_CREATE | > SQLITE_OPEN_READWRITE, NULL) > > - For each table: > > begin deferred transaction; delete from [table]; > insert into table ... > insert into table ... > insert into table ... > insert into table ... > [up to 180.000 rows] > commit; > > and sometimes the commit fails, so it is retried. (why would it fail? its > the only writter) > > And then i have many other processes that open that sqlite database read > only. sqlite3_open_v2(_dbfile, &sqcache_conn, SQLITE_OPEN_READONLY, NULL) > and sqlite3_busy_timeout(sqcache_conn, 5000) > > These processes create very simple prepared statements to query that > tables. > > And the big problem i'm having, is that when i step these prepared > statements, they lock for 5 seconds and then fail. > > And i put that busy timeout just for completeness, cause i wasn't expecting > any locking because for being a read only query. > > I really need these queries not to lock or fail. > Enable WAL mode. "PRAGMA journal_mode=WAL". http://www.sqlite.org/wal.html > > What am i doing wrong? > Any suggestions? > > Thank you, > Alejandro > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help. Read only process being blocked by writer process.
Extra info. Its running on this: SunOS 5.10 Generic_144488-11 sun4u sparc SUNW,Sun-Fire-V490 On Wed, Nov 28, 2012 at 9:47 AM, Alejandro Martínez wrote: > I have one process that each 30 minutes refills several tables in this > manner: > > sqlite3_open_v2(CACHEDB_PATH, &sqcache_conn, SQLITE_OPEN_CREATE | > SQLITE_OPEN_READWRITE, NULL) > > - For each table: > > begin deferred transaction; delete from [table]; > insert into table ... > insert into table ... > insert into table ... > insert into table ... > [up to 180.000 rows] > commit; > > and sometimes the commit fails, so it is retried. (why would it fail? its > the only writter) > > And then i have many other processes that open that sqlite database read > only. sqlite3_open_v2(_dbfile, &sqcache_conn, SQLITE_OPEN_READONLY, NULL) > and sqlite3_busy_timeout(sqcache_conn, 5000) > > These processes create very simple prepared statements to query that > tables. > > And the big problem i'm having, is that when i step these prepared > statements, they lock for 5 seconds and then fail. > > And i put that busy timeout just for completeness, cause i wasn't > expecting any locking because for being a read only query. > > I really need these queries not to lock or fail. > > What am i doing wrong? > Any suggestions? > > Thank you, > Alejandro > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Please help. Read only process being blocked by writer process.
I have one process that each 30 minutes refills several tables in this manner: sqlite3_open_v2(CACHEDB_PATH, &sqcache_conn, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL) - For each table: begin deferred transaction; delete from [table]; insert into table ... insert into table ... insert into table ... insert into table ... [up to 180.000 rows] commit; and sometimes the commit fails, so it is retried. (why would it fail? its the only writter) And then i have many other processes that open that sqlite database read only. sqlite3_open_v2(_dbfile, &sqcache_conn, SQLITE_OPEN_READONLY, NULL) and sqlite3_busy_timeout(sqcache_conn, 5000) These processes create very simple prepared statements to query that tables. And the big problem i'm having, is that when i step these prepared statements, they lock for 5 seconds and then fail. And i put that busy timeout just for completeness, cause i wasn't expecting any locking because for being a read only query. I really need these queries not to lock or fail. What am i doing wrong? Any suggestions? Thank you, Alejandro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
On Tue, Nov 27, 2012 at 11:16 PM, Keith Chew wrote: > I have found that after performing 5000 single transaction inserts, the WAL > size grows to approx 90MB. After a checkpoint, it becomes 0 and the main > DB's size goes up by less than 2MB. Is my observation correct? Ie am I > expecting such a huge size difference between the WAL and main DB file > formats? > In WAL mode, SQLite appends deltas of the database changes to the WAL file. Each transaction commits as its delta is appended. The checkpoint operation transfers these deltas into the main database file. Prior to a checkpoint, the main database file is unchanged in WAL mode. In WAL mode, a checkpoint is the only operation that ever changes the main database. If you do 5000 transactions without a checkpoint, and the delta for each transaction is 4 or 5 pages of 4KB each, that would come to about 90MB. You might want to consider doing checkpoints more frequently to prevent the WAL file from growing so large. -- 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] Accessing matchinfo() from metro
Using the SQLite3 DLL ind Windows 8 metro with the C# managed code wrapper work just great. And adding marshaling code for a ranking function also works (as long as you keep a static var referencing the function and use that var as the registration reference). But, the argument for the info passed to the ranking function is not helpful as managed code can only reference a pointer to a single object like a long, int, etc. general pointers are not allows in managed code. So the question is, has anybody solved this call-back issue. Sent from my iPad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
