[sqlite] Temporary index
In the documentation below under the Pragmas section there seems to be a hint that I can create a temporary index. However the CREATE INDEX syntax does not allow the word TEMPORARY to be used. Anyone know what is going on? Clive PRAGMA temp_store; PRAGMA temp_store = DEFAULT; (0) PRAGMA temp_store = FILE; (1) PRAGMA temp_store = MEMORY; (2) Query or change the setting of the temp_store parameter. When temp_store is DEFAULT (0), the compile-time C preprocessor macro TEMP_STORE is used to determine where temporary tables and indices are stored. When temp_store is MEMORY (2) temporary tables and indices are kept in memory. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] In-memory database: manually modifying ROWID value increases memory.
Dear All, Say I have 3 columns in one Table, with one INTEGER, two TEXT columns, If ROWID is manually inserted and made descending for 1 records from 1 to 1, (or even if random number for ROWID - both these cases), the memory occupied is more. Why is this so? Is that indexing happens, If so, is entire table elements gets sorted? Thanks
[sqlite] Re: Temporary index
Clive.Bluston-cPKiotmf5pXN/[EMAIL PROTECTED] wrote: In the documentation below under the Pragmas section there seems to be a hint that I can create a temporary index. However the CREATE INDEX syntax does not allow the word TEMPORARY to be used. Anyone know what is going on? TEMP_STORE is used to determine where temporary tables and indices are stored. The documentation is talking about temporary tables, and indexes on those tables. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question
Thank you for your reply. I tried adding NOT NULL to my primary key column, but the results are the same. My compiler is old and I don't think it supports a 64 bit data type. Maybe this is the root cause of my problem. If I create a table like this: CREATE TABLE rollover (id INTEGER PRIMARY KEY, name TEXT) or: CREATE TABLE rollover (id INTEGER PRIMARY KEY NOT NULL, name TEXT) and I insert a row like this: INSERT INTO rollover VALUES (2147483647, 'One'); the row has the values: 2147483647, 'One' If I then insert a row like this: INSERT INTO rollover VALUES (NULL, 'Two'); the newly inserted row has the following values: -2147483648, 'Two' If I then try to insert another row like this: INSERT INTO rollover VALUES (NULL, 'Three'); I get: SQL error: PRIMARY KEY must be unique I would like the primary key to rollover to 1, instead of to -2147483648. Does anyone have any ideas where I should look? I will compile this for Windows and see what my results are using a compiler that supports 64 bit integers. Thanks, Shawn -Original Message- From: Brickl Roland [mailto:[EMAIL PROTECTED] Sent: Saturday, October 13, 2007 5:29 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question Hallo Odekirk Shawn, SQLite use up to an 64Bit signed Integer for Primary Keys, even on non 64Bit-Systems! Integer PrimaryKeys are always autoincrementing. When you don't specify it it uses after (2^63)-1 a random free positiv value. When you write autoincrement for your create table it never reuse deleted positiv values and you get an SQLITE_FULL error, thats all. And please don't forget the not null for your primary key. Without this you get a little bit different behavior. Greats, Brickl --- Odekirk, Shawn [EMAIL PROTECTED] schrieb: I am evaluating SQLite for a project I am working on. I have a question about the behavior of the INTEGER PRIMARY KEY auto increment feature. My platform uses 32 bit integers, so the valid values for an unsigned integer are 0 - 4294967296 and the valid values for a signed integer are -2147483648 - 2147483647. Since the INTEGER PRIMARY KEY data type is a signed integer, the maximum positive value is 2147483648. If my table already has a row with the maximum positive value in the primary key field, and I insert a row using NULL as the value of the primary key field, the row is inserted and the primary key is assigned the value of -2147483648. That makes sense to me and I have no problem with that. The problem is that the next row I insert generates the error SQL error: PRIMARY KEY must be unique. I suspect that this is because SQLite tries to use the next largest positive value when it increments the primary key field. Is there an easy way to cause the INTEGER PRIMARY KEY column to use an unsigned integer instead, or to roll over to 0 instead of the most negative value for the data type? I suspect that in practice I will not run into this issue. However, I would feel better knowing that there is no chance that I will encounter this problem. Thanks, Shawn __ Yahoo! Clever: Sie haben Fragen? Yahoo! Nutzer antworten Ihnen. www.yahoo.de/clever - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question
Odekirk, Shawn [EMAIL PROTECTED] wrote: My compiler is old and I don't think it supports a 64 bit data type. Maybe this is the root cause of my problem. Very likely. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] fts sqlite-3_5_1 ?
Im sorry if I am missing something obvious but I see precompiled fts2.dll is no longer available for download alongside 3.5.1, if this indeed means I am not blind, what does this mean? Thank you.
RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question
Brickl Roland [mailto:[EMAIL PROTECTED] wrote: Integer PrimaryKeys are always autoincrementing. When you don't specify it it uses after (2^63)-1 a random free positiv value. Odekirk, Shawn [EMAIL PROTECTED] wrote: I will compile this for Windows and see what my results are using a compiler that supports 64 bit integers. Indeed, compiled using Microsoft Visual Studio 2005 it works as described. So, now to dive into the source and figure out how to make it work using my old SCO tools. Thanks, Shawn - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In-memory database: manually modifying ROWID value increases memory.
It could be malloc fragmentation. Which sqlite version, operating system, and malloc implementation are you using? --- Babu, Lokesh [EMAIL PROTECTED] wrote: Say I have 3 columns in one Table, with one INTEGER, two TEXT columns, If ROWID is manually inserted and made descending for 1 records from 1 to 1, (or even if random number for ROWID - both these cases), the memory occupied is more. Why is this so? Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=listsid=396545469 - To unsubscribe, send email to [EMAIL PROTECTED] -
