Re: [sqlite] Index Usage
William Hachfeld wrote: Am I also correct in understanding that if I did: CREATE INDEX MultiColumnIndex ON Example (begin, end, grp); SELECT id FROM Example WHERE x < end AND y >= begin AND grp=g; That I would only make use of 1 of the 3 terms in the index? Correct. Specifically the y>=begin term would be used. The VDBE opcodes for my sub-select query looked almost identical to the non- sub-select version. So I'm assuming that internally SQLite folds these together and treats them, in effect, like a single query rather than a two-part query. Yes. Subqueries are folded into the main query where possible. This optimization is necessary to implement views efficiently. A view is really just an alias for a subquery. At the risk of trying everyone's patience, I have one more question... Can any generalizations be made about the relative performance of the following queries (again using the same example table): CREATE INDEX IndexA ON Example (grp, begin); SELECT id FROM Example WHERE grp=g AND x < end AND y >= begin; versus: CREATE INDEX IndexA ON Example (grp); CREATE INDEX IndexB ON Example (begin); SELECT * FROM Example WHERE grp=g INTERSECT SELECT * FROM Example WHERE x < end AND y >= begin; or maybe even: CREATE INDEX IndexA ON Example (grp, begin); CREATE INDEX IndexB ON Example (end); SELECT * FROM Example WHERE grp=g AND y >= begin INTERSECT SELECT * FROM Example WHERE x < end; given a large (~1,000,000 rows) table? Is the cost of creating the temporary table for the compound SELECT usually going to outweigh the benefit of using a second index? INTERSECT creates two temporary tables, not one. I'm guessing the first query would be faster. But that is only a guess. Try it and see what you get. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Index Usage
Thanks for the information Richard. Your explanation, along with the "Virtual Database Engine" document that I was reading when you wrote this, makes things a lot more clear. After reading Ulrik's suggestions, I decided to poke around a little bit using "EXPLAIN" to see if I could discover what SQLite would do for my two purposed queries... I was able to see in the VDBE opcodes for my first query exactly what you are telling me - that SQLite will use the "grp" and "begin" terms only. So am I also correct in understanding that if I did: CREATE INDEX MultiColumnIndex ON Example (begin, end, grp); SELECT id FROM Example WHERE x < end AND y >= begin AND grp=g; That I would only make use of 1 of the 3 terms in the index? The VDBE opcodes for my sub-select query looked almost identical to the non- sub-select version. So I'm assuming that internally SQLite folds these together and treats them, in effect, like a single query rather than a two-part query. At the risk of trying everyone's patience, I have one more question... Can any generalizations be made about the relative performance of the following queries (again using the same example table): CREATE INDEX IndexA ON Example (grp, begin); SELECT id FROM Example WHERE grp=g AND x < end AND y >= begin; versus: CREATE INDEX IndexA ON Example (grp); CREATE INDEX IndexB ON Example (begin); SELECT * FROM Example WHERE grp=g INTERSECT SELECT * FROM Example WHERE x < end AND y >= begin; or maybe even: CREATE INDEX IndexA ON Example (grp, begin); CREATE INDEX IndexB ON Example (end); SELECT * FROM Example WHERE grp=g AND y >= begin INTERSECT SELECT * FROM Example WHERE x < end; given a large (~1,000,000 rows) table? Is the cost of creating the temporary table for the compound SELECT usually going to outweigh the benefit of using a second index? Is there any way to force the temporary table to be placed in main memory rather than on disk? -- William Hachfeld ([EMAIL PROTECTED], 651-683-3103) SGI Debugger, Object, and Performance Tools
Re: [sqlite] Index Usage
William Hachfeld wrote: Hi, Have a question for everyone regarding index usage in SQLite... Say that I have the following database schema: CREATE TABLE Example ( id INTEGER PRIMARY KEY, grp INTEGER, begin INTEGER, end INTEGER ); and I want to perform the following query: SELECT id FROM Example WHERE grp=g AND x < end AND y >= begin; on a large number of rows (say around one million) for some group 'g' and an interval '[x, y)'. And, of course, with the assumption that (end > begin) for all rows. Will my query performance be substantially improved by creating an index such as: CREATE INDEX MultiColumnIndex ON Example (grp, begin, end) or will the operators "<" and ">=" prohibit SQLite from using the index? SQLite uses inequalities in WHERE clause terms, but only for the right-most used term of an index. So in the case above, SQLite will use the grp and begin columns of the index and ignore the end column. So the index CREATE INDEX multi ON Example(grp,begin) would work just as well as the one that includes the third "end" column. Also, I'm aware that SQLite supports multi-column indicies, but not the use of multiple indicies per query. Is it possible to get around the later restriction by expressing my above query using a sub-select: SELECT id FROM (SELECT * FROM Example WHERE grp=g) WHERE x < end AND y >= begin; and then creating the following indicies instead: CREATE INDEX GroupIndex ON Example (group) CREATE INDEX IntervalIndex ON Example (begin, end) And if so, can any generalizations be made regarding the performance of using the two indicies versus the first, single, index? How about disk usage? This won't help any. Interval conditions are recognized as long as the same column is used in both terms. For example, this would help: ... WHERE y>=begin AND y= and the < terms, SQLite has to choose one or the other, it cannot use both. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] ALTER TABLE statement?
Richard, When you wrote, "...as my boss feels that he can't easily alter the database with a GUI tool" Is there any chance you meant to type "withOUT a GUI tool" instead? If so, and if you're on a PC, then the nice utility: SqliteExplorer http://www.sqlite.org/contrib has the ability to alter sqlite tables. Just right-click on the table and choose DESIGN TABLE. It actually generates the SQL needed to effect your design changes, but then you're only an EXECUTE button away from implementing them. Don't forget to refresh the schema using F5 to see the results. Most folks, though, would be more than happy to keep their boss from schema-ing behind their backs. Donald Griggs Desk: 803-735-7834 Opinions are not necessarily those of Misys Healthcare Systems nor its board of directors. -Original Message- From: Richard Boehme [mailto:[EMAIL PROTECTED] Sent: Thursday, October 28, 2004 1:52 PM To: [EMAIL PROTECTED] Subject: [sqlite] ALTER TABLE statement? Does anyone know if there are any plans for an ALTER TABLE statement? Not having it is a major issue in possibly adopting SQLite, as my boss feels that he can't easily alter the database with a GUI tool (the ones I've seen for SQLite don't handle it Thanks. Richard Boehme
Re: [sqlite] Problems with SQLite and XP SP2
In a message dated 10/28/2004 2:04:14 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: After installation SP2 for XP Home on Toshiba notebook my application (VC++ 6.0) working width SQLite (2.8.15) data base (30 000 records, 30 MB) goes crazy. Is nearly frozen, all virtual memory is allocated (~800MB), but Task Manager reports that my application process gets 8MB - so as ususal. Who took the rest? ;) So I uninstall SP2 and everything is fine now, again. But what could be the problem? Probably not SQLite itself, but... On another machine (standalone XP SP2) the same application and data base file works ok. The application itself is quite simple VC++ 6.0 MFC application. Works fine for a 2 years till now... Adware/spyware could be the culprit here, run spybot and see what it reports _http://www.safer-networking.org/en/download/_ (http://www.safer-networking.org/en/download/) Wei
Re: [sqlite] ALTER TABLE statement?
User Richard Boehme wrote:: Does anyone know if there are any plans for an ALTER TABLE statement? Not having it is a major issue in possibly adopting SQLite, as my boss feels that he can't easily alter the database with a GUI tool (the ones I've seen for SQLite don't handle it Read sqlite docs and its WIKI pages: http://www.sqlite.org/cvstrac/tktview?tn=236,8 Q 13 http://www.sqlite.org/faq.html Greets -- Mateusz Łoskot, mateusz (at) loskot (dot) net Registered Linux User #220771, Debian (Sarge)
Re: [sqlite] ALTER TABLE statement?
This sample is wrong. You forgot indexes and triggers Altering a table (if there are indexes or triggers associated to it) cannot be done using only SQL... Perhaps you search these examples ??? -Inserire una nuova colonna nel database (esempio completo). ATTENZIONE FARE PRIMA UNA COPIA DEL FILE-DATABASE sqlite prova #Crea una tabella sqlite> create table t1(a text, b text); #Inizia la transazione sqlite> BEGIN TRANSACTION; #Crea una tabella temporanea uguale all'altra tabella sqlite> CREATE TEMPORARY TABLE t1_backup(a,b); #Fa un copia ed incolla dalla prima tabella alla tabella temporanea sqlite> INSERT INTO t1_backup SELECT a,b FROM t1; #Cancella la prima tabella sqlite> DROP TABLE t1; #Crea la nuova tabella con la nuova colonna sqlite> CREATE TABLE t1(a text,b text,c text); #Fa una copia dalla tabella temporanea alla nuova tabella sqlite> INSERT INTO t1 (a,b) select * FROM t1_backup; #Cancella la tabella temporanea sqlite> DROP TABLE t1_backup; #Finisce la transazione. sqlite> COMMIT; #Controllo nomi colonne sqlite> .schema -Rinominare una colonna (esempio completo). ATTENZIONE FARE PRIMA UNA COPIA DEL FILE-DATABASE sqlite prova #Crea una tabella sqlite> create table t1(a text, b text); #Inizia la transazione sqlite> BEGIN TRANSACTION; #Crea una tabella temporanea uguale all'altra tabella sqlite> CREATE TEMPORARY TABLE t1_backup(a,b); #Fa un copia ed incolla dalla prima tabella alla tabella temporanea sqlite> INSERT INTO t1_backup SELECT a,b FROM t1; #Cancella la prima tabella sqlite> DROP TABLE t1; #Crea la nuova tabella con il nuovo nome della colonna sqlite> CREATE TABLE t1(a text,botte text); #Fa una copia dalla tabella temporanea alla nuova tabella sqlite> INSERT INTO t1 (a,botte) select * FROM t1_backup; #Cancella la tabella temporanea sqlite> DROP TABLE t1_backup; #Finisce la transazione. sqlite> COMMIT; #Controllo nomi colonne sqlite> .schema
Re: [sqlite] ALTER TABLE statement?
Perhaps you search these examples ??? -Inserire una nuova colonna nel database (esempio completo). ATTENZIONE FARE PRIMA UNA COPIA DEL FILE-DATABASE sqlite prova #Crea una tabella sqlite> create table t1(a text, b text); #Inizia la transazione sqlite> BEGIN TRANSACTION; #Crea una tabella temporanea uguale all'altra tabella sqlite> CREATE TEMPORARY TABLE t1_backup(a,b); #Fa un copia ed incolla dalla prima tabella alla tabella temporanea sqlite> INSERT INTO t1_backup SELECT a,b FROM t1; #Cancella la prima tabella sqlite> DROP TABLE t1; #Crea la nuova tabella con la nuova colonna sqlite> CREATE TABLE t1(a text,b text,c text); #Fa una copia dalla tabella temporanea alla nuova tabella sqlite> INSERT INTO t1 (a,b) select * FROM t1_backup; #Cancella la tabella temporanea sqlite> DROP TABLE t1_backup; #Finisce la transazione. sqlite> COMMIT; #Controllo nomi colonne sqlite> .schema -Rinominare una colonna (esempio completo). ATTENZIONE FARE PRIMA UNA COPIA DEL FILE-DATABASE sqlite prova #Crea una tabella sqlite> create table t1(a text, b text); #Inizia la transazione sqlite> BEGIN TRANSACTION; #Crea una tabella temporanea uguale all'altra tabella sqlite> CREATE TEMPORARY TABLE t1_backup(a,b); #Fa un copia ed incolla dalla prima tabella alla tabella temporanea sqlite> INSERT INTO t1_backup SELECT a,b FROM t1; #Cancella la prima tabella sqlite> DROP TABLE t1; #Crea la nuova tabella con il nuovo nome della colonna sqlite> CREATE TABLE t1(a text,botte text); #Fa una copia dalla tabella temporanea alla nuova tabella sqlite> INSERT INTO t1 (a,botte) select * FROM t1_backup; #Cancella la tabella temporanea sqlite> DROP TABLE t1_backup; #Finisce la transazione. sqlite> COMMIT; #Controllo nomi colonne sqlite> .schema
[sqlite] Problems with SQLite and XP SP2
Hello All! After installation SP2 for XP Home on Toshiba notebook my application (VC++ 6.0) working width SQLite (2.8.15) data base (30 000 records, 30 MB) goes crazy. Is nearly frozen, all virtual memory is allocated (~800MB), but Task Manager reports that my application process gets 8MB - so as ususal. Who took the rest? ;) So I uninstall SP2 and everything is fine now, again. But what could be the problem? Probably not SQLite itself, but... On another machine (standalone XP SP2) the same application and data base file works ok. The application itself is quite simple VC++ 6.0 MFC application. Works fine for a 2 years till now... Regards hilaner
[sqlite] ALTER TABLE statement?
Does anyone know if there are any plans for an ALTER TABLE statement? Not having it is a major issue in possibly adopting SQLite, as my boss feels that he can't easily alter the database with a GUI tool (the ones I've seen for SQLite don't handle it Thanks. Richard Boehme
Re: [sqlite] Index Usage
Thanks for the advice Ulrik! I don't believe, however, that the alternate query you purposed using BETWEEN is quite equivalent to what I was going to do. I am storing intervals [begin, end) in the database and then looking for those intervals from the database that intersect [x, y) - not those intervals contained by [x, y). A subtle, but important, distinction in my application. In any case, your information about SQLite's use (or lack there-of) of a (begin, end) index is certainly helpful to me. Clearly I need to use the EXPLAIN command and see if I can decipher what SQLite does under various permutations of the query. -- William Hachfeld ([EMAIL PROTECTED], 651-683-3103) SGI Debugger, Object, and Performance Tools
[sqlite] Shadow Pager?
Does anyone know if there is any effort at putting the shadow pager that I've heard mentioned around into SQLite? Thanks. Richard Boehme
Re: [sqlite] Index Usage
Christian Jensen wrote:
I noticed that you use { instead of (
What do those do?
Sorry. They were meant as pseudo-syntax so that he could insert
whatever his own value was. I did that because I didn't want him to write
BETWEEN 10 AND 15-1
but rather calculate the 15-1 inside his program, and then do
BETWEEN 10 AND 14
I guess I should have made that clear.
Cheers,
Ulrik
-Original Message-
From: Ulrik Petersen [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 28, 2004 10:28 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Index Usage
William,
William Hachfeld wrote:
Hi,
Have a question for everyone regarding index usage in SQLite... Say
that I have the following database schema:
CREATE TABLE Example (
id INTEGER PRIMARY KEY,
grp INTEGER,
begin INTEGER,
end INTEGER
);
and I want to perform the following query:
SELECT id FROM Example WHERE grp=g AND x < end AND y >= begin;
on a large number of rows (say around one million) for some group 'g'
and an interval '[x, y)'. And, of course, with the assumption that (end
begin) for all rows. Will my query performance be substantially
improved by creating an index such as:
CREATE INDEX MultiColumnIndex ON Example (grp, begin, end)
or will the operators "<" and ">=" prohibit SQLite from using the
index?
I have almost the same table in my linguistic database, Emdros
(http://emdros.org). What I have found that works best for me is to put
an index on what you call "begin" (not a double index), then do
SELECT id
FROM Example
WHERE grp = g
AND begin BETWEEN {x} AND {y-1}
AND end BETWEEN {x} AND {y-1}.
For some strange reason, this is about 5% faster than what you were
proposing. It could be because SQLite does not know that begin <= end,
and so can't make optimizations about when to stop looking.
Also, I'm aware that SQLite supports multi-column indicies, but not the
use of multiple indicies per query. Is it possible to get around the
later restriction by expressing my above query using a sub-select:
SELECT id FROM (SELECT * FROM Example WHERE grp=g)
WHERE x < end AND y >= begin;
and then creating the following indicies instead:
CREATE INDEX GroupIndex ON Example (group)
CREATE INDEX IntervalIndex ON Example (begin, end)
And if so, can any generalizations be made regarding the performance of
using the two indicies versus the first, single, index? How about disk
usage?
I cannot comment on this, except that I've run EXPLAIN on my versions of
the above queries, and found that SQLite wouldn't consult the "end" part
of the (begin,end) index. Instead, it would consult the "end" part of
the table column, and then only use the "begin" part of the index. At
least that's how I understood the EXPLAIN output, but I may be wrong.
The upshot of the above is that you can save diskspace by not doing the
double index, and only indexing "begin", since for these queries, the
"end" part is redundant (i.e., not used) in the index.
Cheers,
Ulrik
--
Ulrik Petersen, MA, B.Sc.
Emdros -- the text database engine for analyzed or annotated text
http://emdros.org/
--
Ulrik Petersen, MA, B.Sc.
RE: [sqlite] Index Usage
I noticed that you use { instead of (
What do those do?
-Original Message-
From: Ulrik Petersen [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 28, 2004 10:28 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Index Usage
William,
William Hachfeld wrote:
>Hi,
>
>Have a question for everyone regarding index usage in SQLite... Say
>that I have the following database schema:
>
>CREATE TABLE Example (
> id INTEGER PRIMARY KEY,
> grp INTEGER,
> begin INTEGER,
> end INTEGER
>);
>
>and I want to perform the following query:
>
>SELECT id FROM Example WHERE grp=g AND x < end AND y >= begin;
>
>on a large number of rows (say around one million) for some group 'g'
>and an interval '[x, y)'. And, of course, with the assumption that (end
>> begin) for all rows. Will my query performance be substantially
>improved by creating an index such as:
>
>CREATE INDEX MultiColumnIndex ON Example (grp, begin, end)
>
>or will the operators "<" and ">=" prohibit SQLite from using the
index?
>
>
I have almost the same table in my linguistic database, Emdros
(http://emdros.org). What I have found that works best for me is to put
an index on what you call "begin" (not a double index), then do
SELECT id
FROM Example
WHERE grp = g
AND begin BETWEEN {x} AND {y-1}
AND end BETWEEN {x} AND {y-1}.
For some strange reason, this is about 5% faster than what you were
proposing. It could be because SQLite does not know that begin <= end,
and so can't make optimizations about when to stop looking.
>Also, I'm aware that SQLite supports multi-column indicies, but not the
>use of multiple indicies per query. Is it possible to get around the
>later restriction by expressing my above query using a sub-select:
>
>SELECT id FROM (SELECT * FROM Example WHERE grp=g)
> WHERE x < end AND y >= begin;
>
>and then creating the following indicies instead:
>
>CREATE INDEX GroupIndex ON Example (group)
>CREATE INDEX IntervalIndex ON Example (begin, end)
>
>And if so, can any generalizations be made regarding the performance of
>using the two indicies versus the first, single, index? How about disk
usage?
>
>
I cannot comment on this, except that I've run EXPLAIN on my versions of
the above queries, and found that SQLite wouldn't consult the "end" part
of the (begin,end) index. Instead, it would consult the "end" part of
the table column, and then only use the "begin" part of the index. At
least that's how I understood the EXPLAIN output, but I may be wrong.
The upshot of the above is that you can save diskspace by not doing the
double index, and only indexing "begin", since for these queries, the
"end" part is redundant (i.e., not used) in the index.
Cheers,
Ulrik
--
Ulrik Petersen, MA, B.Sc.
Emdros -- the text database engine for analyzed or annotated text
http://emdros.org/
Re: [sqlite] Index Usage
William,
William Hachfeld wrote:
Hi,
Have a question for everyone regarding index usage in SQLite... Say that I have
the following database schema:
CREATE TABLE Example (
id INTEGER PRIMARY KEY,
grp INTEGER,
begin INTEGER,
end INTEGER
);
and I want to perform the following query:
SELECT id FROM Example WHERE grp=g AND x < end AND y >= begin;
on a large number of rows (say around one million) for some group 'g' and an
interval '[x, y)'. And, of course, with the assumption that (end > begin) for
all rows. Will my query performance be substantially improved by creating an
index such as:
CREATE INDEX MultiColumnIndex ON Example (grp, begin, end)
or will the operators "<" and ">=" prohibit SQLite from using the index?
I have almost the same table in my linguistic database, Emdros
(http://emdros.org). What I have found that works best for me is to put
an index on what you call "begin" (not a double index), then do
SELECT id
FROM Example
WHERE grp = g
AND begin BETWEEN {x} AND {y-1}
AND end BETWEEN {x} AND {y-1}.
For some strange reason, this is about 5% faster than what you were
proposing. It could be because SQLite does not know that begin <= end,
and so can't make optimizations about when to stop looking.
Also, I'm aware that SQLite supports multi-column indicies, but not the use of
multiple indicies per query. Is it possible to get around the later restriction
by expressing my above query using a sub-select:
SELECT id FROM (SELECT * FROM Example WHERE grp=g)
WHERE x < end AND y >= begin;
and then creating the following indicies instead:
CREATE INDEX GroupIndex ON Example (group)
CREATE INDEX IntervalIndex ON Example (begin, end)
And if so, can any generalizations be made regarding the performance of using
the two indicies versus the first, single, index? How about disk usage?
I cannot comment on this, except that I've run EXPLAIN on my versions of
the above queries, and found that SQLite wouldn't consult the "end" part
of the (begin,end) index. Instead, it would consult the "end" part of
the table column, and then only use the "begin" part of the index. At
least that's how I understood the EXPLAIN output, but I may be wrong.
The upshot of the above is that you can save diskspace by not doing the
double index, and only indexing "begin", since for these queries, the
"end" part is redundant (i.e., not used) in the index.
Cheers,
Ulrik
--
Ulrik Petersen, MA, B.Sc.
Emdros -- the text database engine for analyzed or annotated text
http://emdros.org/
Re: [sqlite] sqlite3_busy_handler is like sqlite2_busy_handler?
two parameters. int my_handler(void *arg, int calls); On Thu, 28 Oct 2004 16:38:58 +0200, Marco Bambini <[EMAIL PROTECTED]> wrote: > From sqlite3.h (3.0.7): > > /* > ** This routine identifies a callback function that is invoked > ** whenever an attempt is made to open a database table that is > ** currently locked by another process or thread... The > ** second argument is the name of the locked table and the third > ** argument is the number of times the table has been busy. If the > ** busy callback returns 0, then sqlite3_exec() immediately returns > ** SQLITE_BUSY. If the callback returns non-zero, then sqlite3_exec() > ** tries to open the table again and the cycle repeats. > ** > > int sqlite3_busy_handler(sqlite3*, int(*)(void*,int), void*); > > Documentation and function prototype are different, what is the right > prototype for the sqlite3_busy_handler callback routine? > It is with 2 or 3 parameters? It seems to me that the name of the > locked table is missed or the documentation is wrong... > > Thanks a lot. > Marco Bambini > > -- Cory Nelson http://www.int64.org
[sqlite] sqlite3_busy_handler is like sqlite2_busy_handler?
From sqlite3.h (3.0.7): /* ** This routine identifies a callback function that is invoked ** whenever an attempt is made to open a database table that is ** currently locked by another process or thread... The ** second argument is the name of the locked table and the third ** argument is the number of times the table has been busy. If the ** busy callback returns 0, then sqlite3_exec() immediately returns ** SQLITE_BUSY. If the callback returns non-zero, then sqlite3_exec() ** tries to open the table again and the cycle repeats. ** int sqlite3_busy_handler(sqlite3*, int(*)(void*,int), void*); Documentation and function prototype are different, what is the right prototype for the sqlite3_busy_handler callback routine? It is with 2 or 3 parameters? It seems to me that the name of the locked table is missed or the documentation is wrong... Thanks a lot. Marco Bambini
[sqlite] Problem with free Build.c
Hi,
We have sqlite 3.0.7 running on ARM platform with the RTOS from JMI. We
have the problem with the following "free" sequence done in build.c in the
function sqliteDeleteIndex.
Index *pOld;
assert( db!=0 && p->zName!=0 );
pOld = sqlite3HashInsert(>aDb[p->iDb].idxHash, p->zName,
strlen((char*)p->zName)+1, 0);
if( pOld!=0 && pOld!=p ){
sqlite3HashInsert(>aDb[p->iDb].idxHash, pOld->zName,
strlen((char*)pOld->zName)+1, pOld);
}
if( p->zColAff ){
sqliteFree(p->zColAff);
}
freeIndex(p)
and the function freeIndex frees the p->zColAff. With our application we
are having problems of freeing the same pointer again and again.
Though it could be attributed to our own limitation of freeing Memory
still I feel that freeing the same pointer again could be removed. Please
look into the problem and if required solve the same. I have freeing of
p->zCollAff removed in my application and I am not having any more
problems (during sqlite3_close().
Thank you,
Regards,
Sankara Narayanan B
[sqlite] creating tables with multi-column uniqueness
Does the following schema: create table t(col1 text NOT NULL, col2 text NOT NULL, col3 text NOT NULL, UNIQUE(col1,col2), PRIMARY KEY(col1,col2)); State that the COMBINATION of col1+col2 must be unique? Or that BOTH col1 and col2 must be unique? I assumed the first but I am getting for rejections for "uniqueness constraint failed" using sqlite 2.8.0. Thank you! Shawn M. Downey MPR Associates 632 Plank Road, Suite 110 Clifton Park, NY 12065 518-371-3983 x3 (work) 860-508-5015 (cell)
Re: [sqlite] How are NULL values deleted?
Downey, Shawn wrote: Does the following schema: create table t(col1 text NOT NULL, col2 text NOT NULL, col3 text NOT NULL, UNIQUE(col1,col2), PRIMARY KEY(col1,col2)); State that the COMBINATION of col1+col2 must be unique? Or that BOTH col1 and col2 must be unique? I assumed the first but I am getting for rejections for "uniqueness constraint failed" using sqlite 2.8.0. PRIMARY KEY implies UNIQUE and NOT NULL. So your specification is redundant. Furthermore, a bug in SQLite causes multiple identical indices to be created if you use both UNIQUE and PRIMARY KEY on the same columns. You'll still get the right answers, but your database file will be larger and updates will be slower. The combination of col1+col2 must be unique. This works. Example: [EMAIL PROTECTED] drh]$ sqlite :memory: SQLite version 2.8.15 Enter ".help" for instructions sqlite> create table t(a,b,c,primary key(a,b)); sqlite> insert into t values(1,2,3); sqlite> insert into t values(1,3,4); sqlite> insert into t values(3,2,4); sqlite> insert into t values(1,2,4); SQL error: columns a, b are not unique sqlite> -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] How are NULL values deleted?
Does the following schema: create table t(col1 text NOT NULL, col2 text NOT NULL, col3 text NOT NULL, UNIQUE(col1,col2), PRIMARY KEY(col1,col2)); State that the COMBINATION of col1+col2 must be unique? Or that BOTH col1 and col2 must be unique? I assumed the first but I am getting for rejections for "uniqueness constraint failed" using sqlite 2.8.0. Thank you! Shawn M. Downey MPR Associates 632 Plank Road, Suite 110 Clifton Park, NY 12065 518-371-3983 x3 (work) 860-508-5015 (cell)
Re: [sqlite] datatype mismatch
Cory Nelson wrote: I'm trying to insert some UTF-16 into my database but I'm getting a "datatype mismatch" error when I call sqlite3_step() after i bind the string with sqlite3_bind_text16(). You are trying to insert something that is not an integer into a column of type INTEGER PRIMARY KEY. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
