Re: [sqlite] Quoting strings for SQLite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/17/2010 07:12 PM, Dan Bishop wrote: > Newlines, backslashes, and double quotes can be included literally. The > only other character you need to worry about is NUL. Funnily enough I'm busy writing my own quoting routine right now (the source are Python objects so I can't reuse the one in SQLite). The only sensible way I can see of producing a string literal with NUL (which SQLite quite happily supports) is to output something like: CAST(X'43440045' AS CHAR) Unfortunately the bytes have to be in the same encoding as the database but when generating this you don't know what the encoding is or will be for a dump. The SQLite shell dump command just ignores the NUL onwards silently losing data. I guess the only other alternative is to register a function that does the blob to string conversion taking into account blob encoding but that will then only work in SQLites where the function is registered. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkvKfSMACgkQmOOfHg372QTVhACguPE3otC/PLEK2ufATObPg5Os UrUAoIgy7Aa2Jt8euQFgAh84sxKiM8es =zQ6x -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pragmas for in-memory databases
On Sat, Apr 17, 2010 at 05:27:35PM -0700, andrew fabbro scratched on the wall: > If a DB is entirely in-memory (i.e., opened with :memory:), which pragmas > can be used to improve performance? > > (1) I assume synchronous = OFF is desirable N/A. There is no such thing as synchronizing memory writes. > (2) I'm guessing journal_mode = MEMORY is already set. No need to guess. Docs: "Note that the journal_mode for an in-memory database is either MEMORY or OFF and can not be changed to a different value." > Is journal_mode = OFF another possible speed gain? Of course, then > one loses the ability to do transactions. If it is, it is likely to be very minor. > (3) If journaling is set to MEMORY, what is the best setting then for > journal_size_limit? N/A. Only applies to on-disk journals. > I guess it depends on how much memory one is willing to > use overall, but in this case, it's not there for crash protection but > rather to support transactions. Is there a sizing guide? JOURNAL_SIZE_LIMIT doesn't limit the size of an active journal, only left-over journals. Docs: "This pragma may be used to limit the size of journal files left in the file-system **after** transactions are committed..." [emp. added] > (4) How about locking_mode? I imagine it would be OK and a small gain to > set to NORMAL in a single-threaded application, but obviously not a good > idea for multi-threaded. Docs: "The "temp" database (in which TEMP tables and indices are stored) and in-memory databases always uses exclusive locking mode." Multi-threaded has nothing to do with it. Even with multi-thread you still have to sync your use of the database connection. > (5) If referential integrity can be sacrificed (the Oracle DBA in my > whimpers a little), foreign_keys = false, but that's true whether on-disk or > in-memory. Yes. But if you don't have FKs, it is unlikely to make much difference. > (6) Is there any advantage to playing with: >- page_size? Doubt it. Unless you're storing a lot of TEXT/BLOB values that are just slightly larger than a page, and getting a lot of fragmentation, there isn't likely to be much difference. If you're memory bound a slightly larger page is likely to help, but that's a big balancing act that depends a lot on the data and layout of the database. >- default_cache_size? N/A. This only comes into play when a database is re-opened. You can't do that with an instance of an in-memory DB. >- read_uncommitted? I assume there's a different answer for single- vs. > multi-threaded (or rather, depending on how readers/writers interact) N/A. This only applies to shared-cache mode. Shared cache mode only applies when you have multiple connections to the same database. You can't have multiple connections to an in-memory instance. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quoting strings for SQLite
Simon Slavin wrote: > I am using a particular program which needs to be able to mess with an > already-established database. It has to issue UPDATE and INSERT commands > using one string for the entire command: no opportunity for binding. So it > has to assemble commands by concatenation. In order to do this properly I > need to know how to 'quote' a string to make it safe for inclusion in a > command. In the following I am not worrying too much about BLOB fields: it > would be nice if they worked but I can afford to ignore them if they're > difficult. > > SQLite uses single quotes for surrounding a string in a command. Single > quotes inside the string should be doubled. I can do that. Is there > anything else I need to worry about ? Return and newline characters ? > Escape characters ? Double quotes ? > Newlines, backslashes, and double quotes can be included literally. The only other character you need to worry about is NUL. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Pragmas for in-memory databases
If a DB is entirely in-memory (i.e., opened with :memory:), which pragmas can be used to improve performance? (1) I assume synchronous = OFF is desirable (2) I'm guessing journal_mode = MEMORY is already set. Is journal_mode = OFF another possible speed gain? Of course, then one loses the ability to do transactions. (3) If journaling is set to MEMORY, what is the best setting then for journal_size_limit? I guess it depends on how much memory one is willing to use overall, but in this case, it's not there for crash protection but rather to support transactions. Is there a sizing guide? (4) How about locking_mode? I imagine it would be OK and a small gain to set to NORMAL in a single-threaded application, but obviously not a good idea for multi-threaded. (5) If referential integrity can be sacrificed (the Oracle DBA in my whimpers a little), foreign_keys = false, but that's true whether on-disk or in-memory. (6) Is there any advantage to playing with: - page_size? - default_cache_size? - read_uncommitted? I assume there's a different answer for single- vs. multi-threaded (or rather, depending on how readers/writers interact) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT storage
On Apr 17, 2010, at 2:57 PM, slowpoison wrote: > > I want to know whether a TEXT field, when stored, will always take the > exact amount of space allocated for it in the schema definition. So, > when I say TEXT(1024), is the field guaranteed to take 1024 bytes on > disk per record or is there a non-trivial storage scheme at work > similar to VARCHAR types, which tries to optimize space usage. The latter. SQLite stores all strings as if they were in a VARCHAR(10). (That's VARCHAR(one-billion).) A 5-byte string requires 6 bytes of disk (one byte for the string size and 5 for the string itself). A one-billion byte string requires one-billion-and- five bytes of disk (5 bytes for the string size and one billion bytes to hold the string itself.) D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT storage
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/17/2010 11:57 AM, slowpoison wrote: > I was unable to find a reference to how TEXT data types are stored in > the db files. 2.3.1 and 2.3.2 answer that: http://www.sqlite.org/fileformat.html#varint_format > I want to know whether a TEXT field, when stored, will always take the > exact amount of space allocated for it in the schema definition. What makes you think the schema "allocates" space for strings? http://www.sqlite.org/datatype3.html > So, > when I say TEXT(1024), is the field guaranteed to take 1024 bytes on > disk per record or is there a non-trivial storage scheme at work > similar to VARCHAR types, which tries to optimize space usage. The numbers have no effect. "CHAR" and "TEXT" merely affect column affinity as described in the previous link. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkvKH6gACgkQmOOfHg372QTWswCggv3U/PY8t43HY48i2Kc+/y7V tW8Anjf0XTXCmpBhwZGNe2IBFksc71YT =V4tj -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to create temp tables using sql script file ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/17/2010 10:33 AM, Feng Tony wrote: > It's possible to create temporary tables by running sql scripts in my > application? What makes you think it wouldn't work, and what failed when you tried it? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkvKHqwACgkQmOOfHg372QT+PACgjfGweYOn/i0+7Sh4F2QvMvks 6XgAn3m/Xww92DKhbEGreZ7UQ/rbQctj =yZom -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quoting strings for SQLite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/17/2010 01:26 PM, Simon Slavin wrote: > Is there a simple, low-cost way I can use the QUOTE() function, http://www.sqlite.org/c3ref/mprintf.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkvKHnkACgkQmOOfHg372QT9ZQCdF4i59sdTDhT1v8ZWxSn6SwJG rOQAoOHHz/jSgcjd3wprHt4/hnBrzKUZ =tNvZ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TEXT storage
Hi, I was unable to find a reference to how TEXT data types are stored in the db files. The file format comment in btreeInt.h is too complex (or may be the wrong place) for me to deduce what I'm looking for. I want to know whether a TEXT field, when stored, will always take the exact amount of space allocated for it in the schema definition. So, when I say TEXT(1024), is the field guaranteed to take 1024 bytes on disk per record or is there a non-trivial storage scheme at work similar to VARCHAR types, which tries to optimize space usage. thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to create temp tables using sql script file ?
Hi All, It's possible to create temporary tables by running sql scripts in my application? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Quoting strings for SQLite
I am using a particular program which needs to be able to mess with an already-established database. It has to issue UPDATE and INSERT commands using one string for the entire command: no opportunity for binding. So it has to assemble commands by concatenation. In order to do this properly I need to know how to 'quote' a string to make it safe for inclusion in a command. In the following I am not worrying too much about BLOB fields: it would be nice if they worked but I can afford to ignore them if they're difficult. SQLite uses single quotes for surrounding a string in a command. Single quotes inside the string should be doubled. I can do that. Is there anything else I need to worry about ? Return and newline characters ? Escape characters ? Double quotes ? Is there a simple, low-cost way I can use the QUOTE() function, or any other function provided internally to do this ? I don't want to have to simulate a fully SELECT command but if I can just call it as a string function that would be great. I assume it will do everything that's needed. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on the VdbeCursor structure changes
I was using it to get the rowid of a given cursor in a SQLite statement.
Given a table schema like CREATE TABLE foo (A,B)
And an arbitrary select such as SELECT * FROM foo
I was able to return the rowid as a hidden column for the statement. This
included statements with multiple cursors (as a result of multiple joins).
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Saturday, April 17, 2010 11:08 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Question on the VdbeCursor structure changes
On Apr 17, 2010, at 1:02 PM, Paul Shaffer wrote:
> Due to changes in VdbeCursor structure, this code for 3.6.16 won't
> compile
> anymore:
>
> else if(pC->pseudoTable)
> {
> *prowid = pC->iKey;
> }
>
Your application should not be messing with internal data structures
of SQLite, all of which are subject to change without notice (as you
have discovered.)
Perhaps if you explain to us what you are trying to accomplish we will
be better able to help you.
> and for 3.6.23 would have to be replaced by something like this:
>
> else if(pC->pseudoTableReg>0)
> {
> //*prowid =
> }
>
> My problem is that after about an hour of reverse engineering I can't
> figure out a way to get the row id with the new code. Any help
> appreciated.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
D. Richard Hipp
d...@hwaci.com
___
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] Debugging SQLite Code in C - anything like Perl's DBI->trace?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/17/2010 11:08 AM, andrew fabbro wrote: > I realize that's at the DBI layer and part of the DBI spec, but...is anyone > aware of something similar in the C environment for SQLite? There does not > appear to be any sort of debug logging, etc. that one can turn on. sqlite3_trace: http://www.sqlite.org/c3ref/profile.html > If not, does anyone have any ideas for coding up some sort of logging > engine? I don't really want to get deep into SQLite internals, though I > suspect that's what would be needed... For my Python wrapper you can add a callback on each connection (sqlite3 *) which is called with each statement executed. (It tells you the python level objects used as bindings.) Same thing to get timings (uses sqlite3_profile). I also have what is called a connection hook which is a list of callbacks that are called when a connection is created. The intention is that they can be used to register functions, virtual tables etc. I used that as a base to build a tracing tool. The tool registers a connection hook and then runs the original code. The connection hook adds in callbacks for tracing and profiling which are printed if configured to do so. After the original code is finished the collected data is sorted and reports generated. - From a usability point of view, the best thing is that it requires no modification of the original program. You just use the tool to supervise running the program when you want tracing. You could achieve something similar for C based code by using a shared library with the same symbol names as SQLite and then use LD_PRELOAD to intercept the calls for tracing and reporting. (Under Windows you can use DLL injection which is a bit harder.) Here is my documentation and example output: http://apsw.googlecode.com/svn/publish/execution.html#apsw-trace Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkvKDVIACgkQmOOfHg372QSP8gCfTJemY94n5ZCnCjMmLU5yYlma OSwAn1/p0uu9Kqw8vY4PbstoCNH1Kkbl =nLmK -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Debugging SQLite Code in C - anything like Perl's DBI->trace?
On Sat, Apr 17, 2010 at 11:08:49AM -0700, andrew fabbro scratched on the wall: > I've been totally spoiled by Perl's DBI->trace functionality, which allows > one to toggle SQL tracing on and off, and at various depths, so you can see > what the engine is doing, what bind variables are being sent to it, etc. sqlite3_trace() http://www.sqlite.org/c3ref/profile.html Also see the build option SQLITE_ENABLE_IOTRACE http://www.sqlite.org/compile.html#enable_iotrace That only works out-of-the-box with the sqlite3 client, but looking at the code should make it clear how to integrate it into your own application for test purposes. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on the VdbeCursor structure changes
Yes, it's for System.Data.SQLite. I'm trying to build it with the latest
engine code. I've commented out that one else-if that won't compile and so
far no errors, but that's a really bad way to proceed. The SQLiteKeyReader
c# class is trying to get a row id for a cursor.
declspec(dllexport) int WINAPI sqlite3_cursor_rowid(sqlite3_stmt *pstmt,
int cursor, sqlite_int64 *prowid)
{
Vdbe *p = (Vdbe *)pstmt;
sqlite3 *db = (p == NULL) ? NULL : p->db;
int rc = 0;
VdbeCursor *pC;
int ret = 0;
sqlite3_mutex_enter(db->mutex);
while (1)
{
if (cursor < 0 || cursor >= p->nCursor)
{
ret = SQLITE_ERROR;
break;
}
if (p->apCsr[cursor] == NULL)
{
ret = SQLITE_ERROR;
break;
}
pC = p->apCsr[cursor];
ret = sqlite3VdbeCursorMoveto(pC);
if(ret)
break;
if(pC->rowidIsValid)
{
*prowid = pC->lastRowid;
}
else if(pC->pseudoTableReg>0)
{
}
//else if(pC->pseudoTable)
//{
// *prowid = pC->iKey;
//}
else if(pC->nullRow || pC->pCursor==0)
{
ret = SQLITE_ERROR;
break;
}
else
{
if (pC->pCursor == NULL)
{
ret = SQLITE_ERROR;
break;
}
sqlite3BtreeKeySize(pC->pCursor, prowid);
*prowid = *prowid;
}
break;
}
sqlite3_mutex_leave(db->mutex);
return ret;
}
> I think, without to be 100% sure, that it is for the wrapper .NET
> System.Data.SQLite.
> I was myself in front of this code (to have this wrapper using the latest
> sqlite version).
> I ended up by removing all this code, meaning that if you don't call
> dispose() in your code, it will not be garbage collected.
> It is a risk I took, and it works so far.
>
> Best regards,
> Sylvain
>
> On Sat, Apr 17, 2010 at 8:07 PM, D. Richard Hipp wrote:
>
>>
>> On Apr 17, 2010, at 1:02 PM, Paul Shaffer wrote:
>>
>> > Due to changes in VdbeCursor structure, this code for 3.6.16 won't
>> > compile
>> > anymore:
>> >
>> > else if(pC->pseudoTable)
>> > {
>> > *prowid = pC->iKey;
>> > }
>> >
>>
>> Your application should not be messing with internal data structures
>> of SQLite, all of which are subject to change without notice (as you
>> have discovered.)
>>
>> Perhaps if you explain to us what you are trying to accomplish we will
>> be better able to help you.
>>
>>
>> > and for 3.6.23 would have to be replaced by something like this:
>> >
>> > else if(pC->pseudoTableReg>0)
>> > {
>> > //*prowid =
>> > }
>> >
>> > My problem is that after about an hour of reverse engineering I can't
>> > figure out a way to get the row id with the new code. Any help
>> > appreciated.
>> >
>> >
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users at sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> D. Richard Hipp
>> drh at hwaci.com
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at 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 on the VdbeCursor structure changes
I think, without to be 100% sure, that it is for the wrapper .NET
System.Data.SQLite.
I was myself in front of this code (to have this wrapper using the latest
sqlite version).
I ended up by removing all this code, meaning that if you don't call
dispose() in your code, it will not be garbage collected.
It is a risk I took, and it works so far.
Best regards,
Sylvain
On Sat, Apr 17, 2010 at 8:07 PM, D. Richard Hipp wrote:
>
> On Apr 17, 2010, at 1:02 PM, Paul Shaffer wrote:
>
> > Due to changes in VdbeCursor structure, this code for 3.6.16 won't
> > compile
> > anymore:
> >
> > else if(pC->pseudoTable)
> > {
> > *prowid = pC->iKey;
> > }
> >
>
> Your application should not be messing with internal data structures
> of SQLite, all of which are subject to change without notice (as you
> have discovered.)
>
> Perhaps if you explain to us what you are trying to accomplish we will
> be better able to help you.
>
>
> > and for 3.6.23 would have to be replaced by something like this:
> >
> > else if(pC->pseudoTableReg>0)
> > {
> > //*prowid =
> > }
> >
> > My problem is that after about an hour of reverse engineering I can't
> > figure out a way to get the row id with the new code. Any help
> > appreciated.
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] Debugging SQLite Code in C - anything like Perl's DBI->trace?
I've been totally spoiled by Perl's DBI->trace functionality, which allows one to toggle SQL tracing on and off, and at various depths, so you can see what the engine is doing, what bind variables are being sent to it, etc. I realize that's at the DBI layer and part of the DBI spec, but...is anyone aware of something similar in the C environment for SQLite? There does not appear to be any sort of debug logging, etc. that one can turn on. Obviously, I can write things in my code, but sometimes it's helpful to see what the engine receives as well as what I think I send. I don't see any functions targeted at this, either. If not, does anyone have any ideas for coding up some sort of logging engine? I don't really want to get deep into SQLite internals, though I suspect that's what would be needed... Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on the VdbeCursor structure changes
On Apr 17, 2010, at 1:02 PM, Paul Shaffer wrote:
> Due to changes in VdbeCursor structure, this code for 3.6.16 won't
> compile
> anymore:
>
> else if(pC->pseudoTable)
> {
> *prowid = pC->iKey;
> }
>
Your application should not be messing with internal data structures
of SQLite, all of which are subject to change without notice (as you
have discovered.)
Perhaps if you explain to us what you are trying to accomplish we will
be better able to help you.
> and for 3.6.23 would have to be replaced by something like this:
>
> else if(pC->pseudoTableReg>0)
> {
> //*prowid =
> }
>
> My problem is that after about an hour of reverse engineering I can't
> figure out a way to get the row id with the new code. Any help
> appreciated.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question on the VdbeCursor structure changes
Due to changes in VdbeCursor structure, this code for 3.6.16 won't compile
anymore:
else if(pC->pseudoTable)
{
*prowid = pC->iKey;
}
and for 3.6.23 would have to be replaced by something like this:
else if(pC->pseudoTableReg>0)
{
//*prowid =
}
My problem is that after about an hour of reverse engineering I can't
figure out a way to get the row id with the new code. Any help appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Data optimization with GLOB, virtual deletes
Just guessing, but column 'path' probably has greater cardinality than
column 'extension'. What happens if you reverse the order of these columns
in the index?
i.e. (basename, path, extension, deleted)
Also, I don't recall your saying whether a single composite index was faster
than separate indexes? Is it?
Regards
Tim Romano
On Fri, Apr 16, 2010 at 8:17 AM, Mike Goins wrote:
> Sorry, this may look a bit familiar.
>
> Table structure:
> CREATE TABLE tb_file (tb_file_key INTEGER NOT NULL PRIMARY KEY
> AUTOINCREMENT , basename TEXT, extension TEXT, path TEXT, deleted
> INTEGER default 0 );
>
> Index:
> CREATE INDEX fullpath_idx on tb_file (basename, extension, path, deleted);
>
> Example insert:
> INSERT INTO tb_file (basename, extension, path) VALUES ('aa', 'bb', 'cc');
>
> Query:
> SELECT tb_file_key, basename, extension, path FROM tb_file WHERE
> basename GLOB 'a*' AND extension GLOB 'b*' AND path GLOB 'c*' AND
> deleted = 0 ORDER BY tb_file_key DESC;
>
>
> It's basically something to track existing files on a file system for
> an embedded device.
> As new files are added, new entries are made, and when files are
> removed the deleted column is set to 1 (Only one writer process, 5
> readers). There is not any type of VACUUM since there is not any
> shortage of space and the readers need access nearly all the time.
>
> My query gets slower as the table grows larger. The count of the
> deleted = 0 remains relatively constant while the virtually deleted
> (=1) grows. At 3000 deleted and 75 not, the query runs 4-5 times
> slower then when just the 75 not. The data lookup does not need to
> necessarily fast, while I prefer to minimize the growth in query .
>
> Some solutions I am looking at to minimize
> 1. Delete aged entries.
> 2. Delete aged entries and enable a vacuum mode that does not starve
> readers.
> 3. Create index on deleted, use that to create a TEMP table on which
> the query is run.
> 4. Optimize the query, part we have discussed already to remove the GLOB.
> 5. Fix? the index?
>
> Can I dismiss any of these right of the bat? I'm a little baffled
> with 4 and 5 and may need a couple suggestions.
>
> Thanks again.
> ___
> 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
