[sqlite] Re: Re[sqlite] garding software of SQlite2.1
Hi, Thank you very much for your response.I tried to convert SQLite2.1 version to SQlite3 version but unable to convert them .Could you please explain in which command prompt we need to convert them.From given url we found only .exe file not software.We need software .If we convert them into SQLite3 version we nee to change source code of .Net.So we don't want to convert them.Thats why we need SQLite2.1 version software. thank u vijaya Kees Nuyt wrote: > > [Default] On Thu, 25 Oct 2007 21:04:44 -0700 (PDT), Vijaya > Lakshmi <[EMAIL PROTECTED]> wrote: > >>Hi all, >> I need small help regarding software .Acutually my application was >>developed in SQLite2.1 by some body now i need SQLite2.1 version.Actually I >>found SQLite3 version but by using this software i am unable to run my >>application.please let me know from which site i can get SQLite2.1 version >>software. >>thank for ur help in advance. >> >>vijaya > > http://www.sqlite.org/download.html > Scroll to: Historical Binaries And Source Code > > You will find 2.8.17 there. > 2.8.17 is outdated but stable. > You should try 2.8.17, 2.1 is really obsolete. > See http://www.sqlite.org/cvstrac/timeline for changes, at the > bottom you can specify what you want to see. > > If 2.8.17 really is not an option for you, you'll have to try to > get an earlier version from CVS, or try to download an earlier > version by specifying the version you need by hand, using the > same naming scheme as the versions listed on the download page. > Often the older files are still there, just not listed anymore > on the download page. > > Upgrading to 3.5.1 might be less difficult than you first think, > and v2 databases can usually be converted easily to v3 with: > sqlite2 yourv2.db .dump | sqlite3 yourv3.db > > HTH > -- > ( Kees Nuyt > ) > c[_] > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/Regarding-software-of-SQlite2.1-tf4695062.html#a13461328 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] Mechanism for pre-allocating contiguous files for Sqlite?
Hi Teg, Isn't an open issued for the database and journal separately? I'm very familiar with the behavior for read-only, not so much for read/write... Regards, -- Gary On 10/28/07, Teg <[EMAIL PROTECTED]> wrote: > > Hello Gary, > > Sunday, October 28, 2007, 4:51:11 PM, you wrote: > > GM> Hi Teg, > > GM> Have you considered the SQLite VFS? > > GM> Regards, > GM> -- Gary > > > GM> On 10/28/07, Teg <[EMAIL PROTECTED]> wrote: > >> > >> > >> I'd like to pre-allocate the DB for Sqlite so, I can ensure it's a > >> contiguous block of space on the disk. I'm aware of the "Insert a > >> bunch of data then delete" method but, it doesn't ensure a contiguous > >> block on disk. Is there some way I can allocate a file with OS calls > and > >> then > >> use it as an SQLite DB? > >> > >> C > >> > >> > >> > >> > - > >> To unsubscribe, send email to [EMAIL PROTECTED] > >> > >> > - > >> > >> > > Tried. One problem is at the VFS level, the code doesn't know if it's > writing to a journal or main DB file. You can pre-allocate in VFS > but, when the journal and main DB are combined, the main DB grows by > journal file size (or so I seem to observe). > > This would probably have to be done in the pager. > > -- > Best regards, > Tegmailto:[EMAIL PROTECTED] > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re[2]: [sqlite] Mechanism for pre-allocating contiguous files for Sqlite?
Hello Gary, Sunday, October 28, 2007, 4:51:11 PM, you wrote: GM> Hi Teg, GM> Have you considered the SQLite VFS? GM> Regards, GM> -- Gary GM> On 10/28/07, Teg <[EMAIL PROTECTED]> wrote: >> >> >> I'd like to pre-allocate the DB for Sqlite so, I can ensure it's a >> contiguous block of space on the disk. I'm aware of the "Insert a >> bunch of data then delete" method but, it doesn't ensure a contiguous >> block on disk. Is there some way I can allocate a file with OS calls and >> then >> use it as an SQLite DB? >> >> C >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> >> Tried. One problem is at the VFS level, the code doesn't know if it's writing to a journal or main DB file. You can pre-allocate in VFS but, when the journal and main DB are combined, the main DB grows by journal file size (or so I seem to observe). This would probably have to be done in the pager. -- Best regards, Tegmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
On 10/28/07, Michael Ruck <[EMAIL PROTECTED]> wrote: > I'd suggest putting this into the documentation of > sqlite3_last_insert_rowid(), that > the call is not reliable in scenarios such as this one. It might be appropriate to just stress it only works for successful INSERTs. I'd just assumed that was true anyway. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] improving performance on SELECT
Hi everyone,
I'm trying to improve SELECT queries on a db I created.
Here's the part I think is relevant:
create table featureSet (fsetid integer primary key, man_fsetid text,
chrom text);
create table pmfeature (fid integer primary key, fsetid not null
references "featureSet" ("fsetid"), x integer, y integer);
create index man_fsetid_idx on featureSet ("man_fsetid");
create index fset_idx_chrom on featureSet ("chrom");
create index fset_idx_fsetid on featureSet ("fsetid");
create index pmf_idx_fsetid on pmfeature ("fsetid");
And then I need to run many queries like:
SELECT fid, man_fsetid, pmfeature.allele, pmfeature.strand FROM
featureSet, pmfeature WHERE man_fsetid IN () AND
pmfeature.fsetid = featureSet.fsetid ORDER BY fid
That list usually contains 10K or more "man_fsetid" elements.
The featureSet table has about 945K records.
The pmfeature table has about 7M records.
I'd very much appreciate if anybody more experienced in this field
could give me some hints on how to improve this.
Thank you very much,
Benilton
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Mechanism for pre-allocating contiguous files for Sqlite?
Hi Teg, Have you considered the SQLite VFS? Regards, -- Gary On 10/28/07, Teg <[EMAIL PROTECTED]> wrote: > > > I'd like to pre-allocate the DB for Sqlite so, I can ensure it's a > contiguous block of space on the disk. I'm aware of the "Insert a > bunch of data then delete" method but, it doesn't ensure a contiguous > block on disk. Is there some way I can allocate a file with OS calls and > then > use it as an SQLite DB? > > C > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
[sqlite] Mechanism for pre-allocating contiguous files for Sqlite?
I'd like to pre-allocate the DB for Sqlite so, I can ensure it's a contiguous block of space on the disk. I'm aware of the "Insert a bunch of data then delete" method but, it doesn't ensure a contiguous block on disk. Is there some way I can allocate a file with OS calls and then use it as an SQLite DB? C - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Virtual table xFilter argv object longevity
Thanks Dan > -Original Message- > From: Dan Kennedy [mailto:[EMAIL PROTECTED] > Sent: Sunday, October 28, 2007 11:11 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Virtual table xFilter argv object longevity > > On Sun, 2007-10-28 at 14:03 +, Evans, Mark (Tandem) wrote: > > The question is what is the lifetime of sqlite3_value > objects passed > > as argv array to the xFilter virtual table module call? Can I save > > the pointers and reference the values for constraint testing in my > > implementation of xNext? > > No. They are only good until the xFilter() call returns. The > popStack() near the bottom of the OP_VFilter opcode in vdbe.c > will invalidate them. > > Dan. > - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
I'd suggest putting this into the documentation of sqlite3_last_insert_rowid(), that the call is not reliable in scenarios such as this one. -Ursprüngliche Nachricht- Von: D. Richard Hipp [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 28. Oktober 2007 17:48 An: sqlite-users@sqlite.org Betreff: Re: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote: > Yes, I am well aware of this possibility as I've written in my > initial mail. > It just doesn't fit with the > description of sqlite3_last_insert_rowid() in my understanding. I > think this > is a bug - either in the documentation > or in the implementation. sqlite3_last_insert_rowid() should return > the > correct id, no matter what and it doesn't. > Consider this scenario: CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE); INSERT INTO ex1 VALUES(1,1,1); INSERT INTO ex1 VALUES(2,2,2); INSERT INTO ex1 VALUES(3,3,3); Now you do your INSERT OR IGNORE: INSERT OR IGNORE INTO ex1 VALUES(1,2,3); Three different constraints fail, one for each of three different rows. So if sqlite3_last_insert_rowid() were to operate as you suggest and return the rowid of the failed insert, when rowid would it return? 1, 2, or 3? D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Good point. Thanks. -Ursprüngliche Nachricht- Von: D. Richard Hipp [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 28. Oktober 2007 17:48 An: sqlite-users@sqlite.org Betreff: Re: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote: > Yes, I am well aware of this possibility as I've written in my > initial mail. > It just doesn't fit with the > description of sqlite3_last_insert_rowid() in my understanding. I > think this > is a bug - either in the documentation > or in the implementation. sqlite3_last_insert_rowid() should return > the > correct id, no matter what and it doesn't. > Consider this scenario: CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE); INSERT INTO ex1 VALUES(1,1,1); INSERT INTO ex1 VALUES(2,2,2); INSERT INTO ex1 VALUES(3,3,3); Now you do your INSERT OR IGNORE: INSERT OR IGNORE INTO ex1 VALUES(1,2,3); Three different constraints fail, one for each of three different rows. So if sqlite3_last_insert_rowid() were to operate as you suggest and return the rowid of the failed insert, when rowid would it return? 1, 2, or 3? D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote: Yes, I am well aware of this possibility as I've written in my initial mail. It just doesn't fit with the description of sqlite3_last_insert_rowid() in my understanding. I think this is a bug - either in the documentation or in the implementation. sqlite3_last_insert_rowid() should return the correct id, no matter what and it doesn't. Consider this scenario: CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE); INSERT INTO ex1 VALUES(1,1,1); INSERT INTO ex1 VALUES(2,2,2); INSERT INTO ex1 VALUES(3,3,3); Now you do your INSERT OR IGNORE: INSERT OR IGNORE INTO ex1 VALUES(1,2,3); Three different constraints fail, one for each of three different rows. So if sqlite3_last_insert_rowid() were to operate as you suggest and return the rowid of the failed insert, when rowid would it return? 1, 2, or 3? D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Virtual table xFilter argv object longevity
On Sun, 2007-10-28 at 14:03 +, Evans, Mark (Tandem) wrote: > The question is what is the lifetime of sqlite3_value objects passed as > argv array to the xFilter virtual table module call? Can I save the > pointers and reference the values for constraint testing in my > implementation of xNext? No. They are only good until the xFilter() call returns. The popStack() near the bottom of the OP_VFilter opcode in vdbe.c will invalidate them. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Yes, I am well aware of this possibility as I've written in my initial mail. It just doesn't fit with the description of sqlite3_last_insert_rowid() in my understanding. I think this is a bug - either in the documentation or in the implementation. sqlite3_last_insert_rowid() should return the correct id, no matter what and it doesn't. Since I have a bunch of tables of this structure I don't want to waste memory/processor time just to retrieve the rowid I should have gotten in the first place from the insert. Mike -Ursprüngliche Nachricht- Von: Kees Nuyt [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 28. Oktober 2007 15:36 An: sqlite-users@sqlite.org Betreff: Re: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() [Default] On Sun, 28 Oct 2007 10:00:52 +0100, "Michael Ruck" <[EMAIL PROTECTED]> wrote: >Hi, > >I did specify UNIQUE for category. The id is also kept, so everything >is working >*except* that I don't get the id of the record ignored from >sqlite3_last_insert_rowid(). > >Mike You could simply do a SELECT id FROM categories WHERE category = ''; to retrieve the id. After the (ignored) INSERT the database pages with the relevant parts of the BTree for the UNIQUE index on category will still be in memory, so the SELECT will be fast. For even more speed you can prepare the SELECT statement during the init of your program, and bind to the appropriate values every time you need it, so it doesn't have to be parsed every time. Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
[Default] On Sun, 28 Oct 2007 10:00:52 +0100, "Michael Ruck" <[EMAIL PROTECTED]> wrote: >Hi, > >I did specify UNIQUE for category. The id is also kept, so everything is >working >*except* that I don't get the id of the record ignored from >sqlite3_last_insert_rowid(). > >Mike You could simply do a SELECT id FROM categories WHERE category = ''; to retrieve the id. After the (ignored) INSERT the database pages with the relevant parts of the BTree for the UNIQUE index on category will still be in memory, so the SELECT will be fast. For even more speed you can prepare the SELECT statement during the init of your program, and bind to the appropriate values every time you need it, so it doesn't have to be parsed every time. Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Virtual table xFilter argv object longevity
The question is what is the lifetime of sqlite3_value objects passed as argv array to the xFilter virtual table module call? Can I save the pointers and reference the values for constraint testing in my implementation of xNext? Fingers crossed. Many thanks, Mark
AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Hi, I did specify UNIQUE for category. The id is also kept, so everything is working *except* that I don't get the id of the record ignored from sqlite3_last_insert_rowid(). Mike -Ursprüngliche Nachricht- Von: Kees Nuyt [mailto:[EMAIL PROTECTED] Gesendet: Samstag, 27. Oktober 2007 23:45 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() [Default] On Sat, 27 Oct 2007 16:26:36 +0200, "Michael Ruck" <[EMAIL PROTECTED]> wrote: >Hi, > >I have a table of unique values in the following format: > >CREATE TABLE categories (id INTEGER PRIMARY KEY, category UNIQUE TEXT) > >I want inserts into this table to succeed, even though the corresponding >entry already exists. So I use inserts in the following format: > >INSERT OR IGNORE INTO categories VALUES (NULL, ?) > >However, if I follow this successful execution with a call to >sqlite3_last_insert_rowid() I don't get the rowid of row, which caused the >insert to be ignored, but one I preformed previously (which doesn't >necessarily have anything to do with this one.) This causes some relations >in my database model to break. > >I know I could use INSERT OR FAIL and a subsequent SELECT, but that seems >awkward and like unnecessary code bloat to me. Additionally I kind of think, > >this breaks the description and sense of sqlite3_last_insert_rowid(). > >SQlite version used is 3.3.16. > >Is this intentional? Any suggestions or should I file a ticket for this? > >Thanks! >Mike You supply NULL for the primary key, which in this case means SQLite will make up a new id for you. http://www.sqlite.org/lang_createtable.html : Specifying a PRIMARY KEY normally just creates a UNIQUE index on the corresponding columns. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, SQLite ignores the datatype specification of columns and allows any kind of data to be put in a column regardless of its declared datatype.) If a table does not have an INTEGER PRIMARY KEY column, then the B-Tree key will be a automatically generated integer. The B-Tree key for a row can always be accessed using one of the special names "ROWID", "OID", or "_ROWID_". This is true regardless of whether or not there is an INTEGER PRIMARY KEY. An INTEGER PRIMARY KEY column can also include the keyword AUTOINCREMENT. The AUTOINCREMENT keyword modified the way that B-Tree keys are automatically generated. Additional detail on automatic B-Tree key generation is available separately. http://www.sqlite.org/autoinc.html : When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. To specify a ROWID manually, just include it in the list of values to be inserted. For example: CREATE TABLE test1(a INT, b TEXT); INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello'); If no ROWID is specified on the insert, an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. And: If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. By supplying NULL as the key (ROWID) you actually don't specify a value, so SQLite creates a new row with a new id. If you want category to be unique, you will have to specify a UNIQUE constraint for it. HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
