Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
...oopss.. I supposed it was a fault of mine. Thanks for the support and the help. Just two points: 1. the "PRAGMA writable_schema=ON" is not described in the "official" documentation for the PRAGMA syntax 2. does this mean that if I create an invalid view in the database, the entire database file becomes unreadable because of the "schema error"? I know I can't create an invalid view, because SQLite refuses to create it, but this seems a problem to me... could SQLite just open the database, and complain only on the invalid views (i.e. for instance when I open the view to query the data in it)? Thanks again Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] delete on view
Hi folks! After having UPDATE and INSERT on a view working, I'd also like to DELETE FROM a view. I have the following tables and a view: --- create table dbapp_tablenames ( tablenameID integer primary key, tablename text not null unique on conflict ignore ); create table dbapp_tablefields ( tablenameID integer not null, tablefield text not null ); create view dbapp as select tablename,group_concat(tablefield) as fields from dbapp_tablefields as fs join dbapp_tablenames as ts on (ts.tablenameID=fs.tablenameID) group by ts.tablenameID; create trigger dbapp_delete instead of delete on dbapp begin delete from dbapp_tablefields where tablenameID=( select tablenameID from dbapp_tablenames where tablename=old.tablename and tablefield=old.tablefield); select case when (( select tablefield from dbapp_tablefields f join dbapp_tablenames t on (f.tablenameID=t.tablenameID) where tablefield=old.fields and tablname=old.tablename) is null) then (delete from dbapp_tablenames where tablename=old.tablename) end; end; --- But the trigger produces a syntax error at "delete" in the first line. What's wrong? Greetings, Fabiano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] nested tree table porting from mysql -> sqlite
I have trouble to move up node on sqlite
on mysql i write:
UPDATE SET rgt = rgt + IF(rgt<%1,%2,-%3)
on sqlite i must rewrite query to
UPDATE SET rgt = (SELECT CASE WHEN (rgt < %1) THEN rgt + %2 ELSE rgt + -%3
END)
is here a way to create a trigger or similar on sqlite to support
IF(expression,int or string,int or string)
and use the same query?
detail code...
/* nested tree table */
/*
create table catememo (
id INTEGER PRIMARY KEY AUTOINCREMENT,
root_id INTEGER,
name varchar(110),
lft INTEGER,
rgt INTEGER,
oldid INTEGER,
attribute BLOB)");
online demo http://www.klempert.de/nested_sets/demo/
show tree
SELECT n.*, round((n.rgt-n.lft-1)/2,0) AS childs,
count(*)+(n.lft>1) AS level,
((min(p.rgt)-n.rgt-(n.lft>1))/2) > 0 AS lower,
(( (n.lft-max(p.lft)>1) )) AS upper FROM catememo n,
catememo p WHERE n.lft BETWEEN p.lft
AND p.rgt AND
(p.root_id = n.root_id) AND (p.id != n.id OR n.lft = 1)
GROUP BY n.root_id,n.id ORDER BY n.root_id,n.lft"
*/
/* move node up */
/* mysql UPDATE SET rgt = rgt + IF(rgt<%1,%2,-%3)
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF()
returns expr2; otherwise it returns expr3. IF()
returns a numeric or string value, depending on the
context in which it is used.
mysql> SELECT IF(1>2,2,3);
-> 3
mysql> SELECT IF(1<2,'yes','no');
-> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
-> 'no'
*/
/* sqlite translate */
QString handleleft = QString("SET lft= (SELECT CASE WHEN (lft < %1) "
"THEN lft + %2 ELSE lft + -%3 END), ")
.arg(nn1left)
.arg(inc)
.arg(desc);
QString handleright = QString("rgt= (SELECT CASE WHEN (rgt < %1) "
"THEN rgt + %2 ELSE rgt + -%3 END)")
.arg(nn1left)
.arg(inc)
.arg(desc);
QString sqlup = QString("UPDATE %1 ").arg(table);
sqlup.append(handleleft + handleright);
sqlup.append(QString(" WHEREroot_id= %1 AND lft>=%2
").arg(r2.value("root_id").toInt()).arg(nn2left));
sqlup.append(QString("AND rgt<=%1 ").arg(nn1right));
qDebug() << " sqlup -> " << sqlup;
if (query.exec(sqlup)) {
/* reload table nested tree */
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] nested tree table porting from mysql -> sqlite
<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have trouble to move up node on sqlite > on mysql i write: > UPDATE SET rgt = rgt + IF(rgt<%1,%2,-%3) > on sqlite i must rewrite query to > UPDATE SET rgt = (SELECT CASE WHEN (rgt < %1) THEN rgt + %2 ELSE rgt > + -%3 END) You don't need SELECT keyword here. You could also get closer to the original statement with UPDATE tableName SET rgt = rgt + (CASE WHEN (rgt < %1) THEN %2 ELSE -%3 END); > is here a way to create a trigger or similar on sqlite to support > IF(expression,int or string,int or string) > and use the same query? sqlite3_create_function[16] Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete on view
"Fabiano Sidler" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > create trigger dbapp_delete instead of delete on dbapp begin > delete from dbapp_tablefields where tablenameID=( > select tablenameID from dbapp_tablenames > where tablename=old.tablename > and tablefield=old.tablefield); > > select case when (( > select tablefield from dbapp_tablefields f > join dbapp_tablenames t on (f.tablenameID=t.tablenameID) > where tablefield=old.fields and tablname=old.tablename) is null) > then (delete from dbapp_tablenames where tablename=old.tablename) > end; > > end; > --- > > But the trigger produces a syntax error at "delete" in the first line. The problem is with the DELETE statement you are trying to nest into a select statement (the second statement in the trigger). You can't do that - DELETE can only appear at the top level. You need something like this: delete from dbapp_tablenames where tablename=old.tablename and not exists ( select * from dbapp_tablefields f where f.tablenameID = dbapp_tablenames.tablenameID and tablefield=old.fields ); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] nested tree table porting from mysql -> sqlite
Igor Tandetnik wrote: > > You don't need SELECT keyword here. You could also get closer to the > original statement with > > UPDATE tableName SET rgt = rgt + (CASE WHEN (rgt < %1) THEN %2 ELSE -%3 > END); > This also has the advantage that it is standard SQL and therefore portable to many other database systems, not just mySQL. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] List Issues
Starting yesterday afternoon we seemed to experience some serious delays in mailing list distribution. It has been difficult to determine what the actual cause is, as there has been no configuration changes in either the mailing list of mail server. It *appears* as if this may have been a transient network issue, perhaps problems with DNS, but that is still just a guess. It may be Mailman related. If you post to the list and do not see it appear within 10 minutes (or at all), please contact me so I can track it down. It helps me to have something specific to look for. I am very sorry for the inconvenience, and will be working on it continually until it is resolved. -- Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Regular Expressions and sqlite3_create_function
I am trying to enable the REGEXP operator. I have read on the forums that you must use the sqlite3_create_function. I would like to use the boost regular expressions library under the hood. Question is, does anyone have an example using this function (sqlite3_create_function) to override regexp()? TIA T ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regular Expressions and sqlite3_create_function
Jason Tudor wrote: > I am trying to enable the REGEXP operator. I have read on the forums that > you must use the sqlite3_create_function. I would like to use the boost > regular expressions library under the hood. Question is, does anyone have > an example using this function (sqlite3_create_function) to override > regexp()? > I haven't seem one, but your can look at the source for the LIKE and GLOB operators in the source file func.c at http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/func.c=1.191 The REGEXP operator will need to use the same sqlite API functions to implement its operation. It will need to get its arguments, perform the comparison,and return a result the same way. Once implemented you will need to register your function so sqlite wil call it when it parses a REGEXP operator in an SQL statement. You can see how SQLite registers it builtin functions in the source as well. It uses the same API's you will use. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Major memory leak
Greetings! I must be doing something wrong. I've got a simple table with three columns, a key column, a value column and a timestamp column. There are 357,000 rows. The timestamps are stored as floating-point numbers (Julian dates), and the other two fields contain integers. I open the table, read one record, and close it. If I do not sort the data, there is no memory loss. Here's the query: select datetime(value_timestamp) AS latest_time from trend_data If I sort the data and ask for only the first record, I leak over 2 megabytes of data. Here's the query: select datetime(value_timestamp) AS latest_time from trend_data order by value_timestamp desc limit 1 I got the same result when the query used the max() function instead of ordering the recordset. My program use sqlite3_prepare16_v2, followed by sqlite3_step, followed by sqlite3_finalize. Is there something I've left out? The application in which these calls are made is designed to be started and left alone. At one point, I checked TaskManager on the customer's computer and found that one instance of this program was using over 950 megabytes of memory! At that point, I advised the customer to restart the program once a day, but I'd really like to be able to tell them they don't have to do that any more. Thanks very much! RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] New crashes with SQLite 3.5.7
Hey all, Mozilla has recently upgraded to sqlite 3.5.7, and we've suddenly gotten a lot of crashes. The mozilla bug report is here: https://bugzilla.mozilla.org/show_bug.cgi?id=424163 We haven't looked into it to much, but I figured I'd point it out to so you were aware of it. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Major memory leak
Greetings! I must be doing something wrong. I've got a simple table with three columns, a key column, a value column and a timestamp column. There are 357,000 rows. The timestamps are stored as floating-point numbers (Julian dates), and the other two fields contain integers. I open the table, read one record, and close it. If I do not sort the data, there is no memory loss. Here's the query: select datetime(value_timestamp) AS latest_time from trend_data If I sort the data and ask for only the first record, I leak over 2 megabytes of data. Here's the query: select datetime(value_timestamp) AS latest_time from trend_data order by value_timestamp desc limit 1 I got the same result when the query used the max() function instead of ordering the recordset. My program use sqlite3_prepare16_v2, followed by sqlite3_step, followed by sqlite3_finalize. Is there something I've left out? The application in which these calls are made is designed to be started and left alone. At one point, I checked TaskManager on the customer's computer and found that one instance of this program was using over 950 megabytes of memory! At that point, I advised the customer to restart the program once a day, but I'd really like to be able to tell them they don't have to do that any more. Thanks very much! RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New crashes with SQLite 3.5.7
"Shawn Wilsher" <[EMAIL PROTECTED]> wrote: > Hey all, > > Mozilla has recently upgraded to sqlite 3.5.7, and we've suddenly > gotten a lot of crashes. The mozilla bug report is here: > https://bugzilla.mozilla.org/show_bug.cgi?id=424163 > > We haven't looked into it to much, but I figured I'd point it out to > so you were aware of it. > The whole Bitvec thing is new to 3.5.7, but I thought it was thoroughly tested. I'll have a look. Any additional information you can send, such as the size of the database file at the point of failure, or the exact line on which the problem occurs, will be appreciated. (I know the bug report gives a line-number, but line numbers shift from one amalgamation to another, and I don't know which amalgamation you are using - I want the text of the line on which the problem occurs.) A reproducible test case would, of course, be ideal. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New crashes with SQLite 3.5.7
> Any additional information you can send, such as the size of > the database file at the point of failure, or the exact line > on which the problem occurs, will be appreciated. (I know the > bug report gives a line-number, but line numbers shift from > one amalgamation to another, and I don't know which amalgamation > you are using - I want the text of the line on which the problem > occurs.) Each individual crash report listed on this page will point you to the proper place: http://crash-stats.mozilla.com/report/list?range_unit=weeks_search=signature_type=contains=sqlite3BitvecSet=sqlite_value=1 Example from one crash report (beware, really large html file): http://bonsai.mozilla.org/cvsblame.cgi?file=mozilla/db/sqlite3/src/sqlite3.c=1.14=22783#22783 > A reproducible test case would, of course, be ideal. That might be a bit difficult to reproduce. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regular Expressions and sqlite3_create_function
On Mar 20, 2008, at 10:38 PM, Jason Tudor wrote: > I am trying to enable the REGEXP operator. I have read on the > forums that > you must use the sqlite3_create_function. I would like to use the > boost > regular expressions library under the hood. Question is, does > anyone have > an example using this function (sqlite3_create_function) to override > regexp()? There is one in the ext/icu/icu.c file of the source distribution. http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/icu.c=1.7 Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Major memory leak
It might be helpful to include the version of sqlite. Have you run your code through a memory analysis routine such as valgrind, to validate that the leak is not occuring in your application code? HTH, Ken Rob Richardson <[EMAIL PROTECTED]> wrote: Greetings! I must be doing something wrong. I've got a simple table with three columns, a key column, a value column and a timestamp column. There are 357,000 rows. The timestamps are stored as floating-point numbers (Julian dates), and the other two fields contain integers. I open the table, read one record, and close it. If I do not sort the data, there is no memory loss. Here's the query: select datetime(value_timestamp) AS latest_time from trend_data If I sort the data and ask for only the first record, I leak over 2 megabytes of data. Here's the query: select datetime(value_timestamp) AS latest_time from trend_data order by value_timestamp desc limit 1 I got the same result when the query used the max() function instead of ordering the recordset. My program use sqlite3_prepare16_v2, followed by sqlite3_step, followed by sqlite3_finalize. Is there something I've left out? The application in which these calls are made is designed to be started and left alone. At one point, I checked TaskManager on the customer's computer and found that one instance of this program was using over 950 megabytes of memory! At that point, I advised the customer to restart the program once a day, but I'd really like to be able to tell them they don't have to do that any more. Thanks very much! RobR ___ 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] Malformed database schema with SQLite version > 3.5.x
On Thu, 20 Mar 2008 10:23:23 +0100, you wrote: >...oopss.. I supposed it was a fault of mine. >Thanks for the support and the help. > >Just two points: > >1. the "PRAGMA writable_schema=ON" is not described in >the "official" documentation for the PRAGMA syntax It's not a supported PRAGMA. In fact, it is very dangerous to use. It's there for debugging, recovery(?) and testing only, and shouldn't be used as a replacement for SQL. >2. does this mean that if I create an invalid view in the database, the >entire database file becomes unreadable because of the "schema error"? Yes, if you insert invalid SQL (or any other value) in sqlite_master by means of "writable_schema" you can easily damage your database beyond repair. >I know I can't create an invalid view, >because SQLite refuses to create it, >but this seems a problem to me... could SQLite just open the database, and >complain only on the invalid views (i.e. for instance when I open the view >to query the data in it)? If you define views the way you are supposed to define them, with SQL: CREATE VIEW viewname AS SELECT .. ; they are validated against the schema immediately, and rejected if they are not valid. Views that refer to tables or columns that no longer exist won't give a schema error, but an error about what's missing when the view is executed, just like an invalid SELECT statement would do. So, it is not a problem until you create your own problem by using undocumented, unsupported backdoors. Consider it the same as patching table pages by hex-editing the database file. Feel free to do it, but don't expect a safety net. >Thanks again >Marco -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
Kees Nuyt <[EMAIL PROTECTED]> wrote: > > >I know I can't create an invalid view, > >because SQLite refuses to create it, > >but this seems a problem to me... could SQLite just open the database, and > >complain only on the invalid views (i.e. for instance when I open the view > >to query the data in it)? > > If you define views the way you are supposed to define > them, with SQL: > > CREATE VIEW viewname AS > SELECT .. > ; > > they are validated against the schema immediately, and > rejected if they are not valid. Views that refer to > tables or columns that no longer exist won't give a > schema error, but an error about what's missing when > the view is executed, just like an invalid SELECT > statement would do. > > So, it is not a problem until you create your own > problem by using undocumented, unsupported backdoors. > Consider it the same as patching table pages by > hex-editing the database file. > > Feel free to do it, but don't expect a safety net. > I think there must have been a bug in older versions of SQLite that allowed some invalid VIEWs to be inserted into the sqlite_master table. I don't think Marco was messing around with the writable_schema pragma in order to insert the invalid VIEWs. He just happened to have the misfortune of using a version of SQLite that failed to completely validate his input. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why did 3.5.7 pass the following test in alter2
do_test alter2-2.1 {
execsql {
CREATE TABLE abc2(a, b, c);
INSERT INTO abc2 VALUES(1, 2, 10);
INSERT INTO abc2 VALUES(3, 4, NULL);
INSERT INTO abc2 VALUES(5, 6, NULL);
CREATE VIEW abc2_v AS SELECT * FROM abc2;
SELECT * FROM abc2_v;
}
} {1 2 10 3 4 {} 5 6 {}}
do_test alter2-2.2 {
# ALTER TABLE abc ADD COLUMN d;
alter_table abc2 {CREATE TABLE abc2(a, b, c, d);}
execsql {
SELECT * FROM abc2_v;
}
} {1 2 10 {} 3 4 {} {} 5 6 {} {}}
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> CREATE TABLE abc2(a, b, c);
sqlite> INSERT INTO abc2 VALUES(1, 2, 10);
sqlite> INSERT INTO abc2 VALUES(3, 4, NULL);
sqlite> INSERT INTO abc2 VALUES(5, 6, NULL);
sqlite> CREATE VIEW abc2_v AS SELECT * FROM abc2;
sqlite> SELECT * FROM abc2_v;
1|2|10
3|4|
5|6|
sqlite> ALTER TABLE abc2 ADD COLUMN d;
sqlite> SELECT * FROM abc2_v;
1|2|10
3|4|
5|6|
I would expect the view to give 4 columns after the alter, not 3.
as the following direct select shows.
sqlite> SELECT * FROM abc2;
1|2|10|
3|4||
5|6||
Regards,
Noah
CONFIDENTIALITY NOTICE:
This message may contain confidential and/or privileged information. If you are
not the addressee or authorized to receive this for the addressee, you must not
use, copy, disclose, or take any action based on this message or any
information herein. If you have received this message in error, please advise
the sender immediately by reply e-mail and delete this message. Thank you for
your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why did 3.5.7 pass the following test in alter2
> sqlite> CREATE TABLE abc2(a, b, c); > > sqlite> CREATE VIEW abc2_v AS SELECT * FROM abc2; > sqlite> SELECT * FROM abc2_v; > > sqlite> ALTER TABLE abc2 ADD COLUMN d; > > > I would expect the view to give 4 columns after the alter, not 3. > as the following direct select shows. I started out by explaining why SQLite was not doing what you expected, and then I found out that there does in fact seem to be a bug afoot. There are two reasonable behaviors here: 1. Preserving the "*"ness, so that adding a column to the relevant source table will add the column to the view 2. Expand the '*' at view-creation-time, so that adding columns to the source table(s) does not affect the view (I'm pretty sure PostgreSQL works like this) It looks like SQLite mostly does the first one. However, if a table is altered, the schema cache is not flushed. Watch! D:\>sqlite3 tmp.db SQLite version 3.5.6 Enter ".help" for instructions sqlite> .headers on sqlite> create table abc2 (a,b,c); sqlite> insert into abc2 values(1,2,3); sqlite> create view abc2_v as select * from abc2; sqlite> select * from abc2_v; a|b|c 1|2|3 sqlite> alter table abc2 add column d; sqlite> select * from abc2_v; a|b|c 1|2|3 As you can see, SQLite hasn't realized that abc2_v needs to be updated. However, if I open another command prompt and run: D:\>sqlite3 tmp.db SQLite version 3.5.6 Enter ".help" for instructions sqlite> create table xyz(a); This increments the schema version, which will invalidate the schema cache (and any prepared statements, but that doesn't really apply to sqlite3.) So, back in the original terminal: sqlite> select * from abc2_v; a|b|c|d 1|2|3| Looking at the 'alter2.test' source at http://www.sqlite.org/cvstrac/fileview?f=sqlite/test/alter2.test=1.13, I can see that the "alter_table" function appears to open a dedicated connection to the database, which means when the "execsql" function is then called, it is not on the same connection as the "alter_table" one and cannot take advantage of the database cache. I think we might need an alter2b.test, and maybe even an alternot2b.test (ba-dum-pshh!) -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL vs. MicroBlaze
Hello, I am trying to use SQLite on a MicroBlaze processor running uClinux using the petalogix toolchain. I can open an in-memory database, but when I try to open a file database the library can't open the file. Internally, the open call returns 22 - invalid argument. The application is compiled using the amalgamation. When compiled for Linux running on a PC, the application runs fine. Does anyone have any experience with this platform that might help? I'm just starting to look into the compiler options. Thanks, Rob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Use of two columns for a key and query on first clm.
If I use two columns for a key (primary or separate index) and query just on the first column component will I always get back the first match in a set. For example. - create table mytable ( clm1 text collate nocase, clm2 text collate nocase, constraint mycs1 primary key( clm1, clm2 ) ); insert following: Clm1 Clm2 abc 123 abc 456 abc 789 def 123 def 456 def 789 select * from table where clm1='def'; - Will the returned row always be def - 123. ie. the first row for def? I've looked at the query plan for this select and it does use the index if clm1 alone is in the query and it appears to match on the first row. Also my tests indicate I do get back the first matching row. But I'd like confirmation if possible. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use of two columns for a key and query on first clm.
No, use ORDER BY Neville Franks wrote: > If I use two columns for a key (primary or separate index) and query > just on the first column component will I always get back the first > match in a set. For example. > > - > create table mytable ( clm1 text collate nocase, clm2 text collate nocase, > constraint mycs1 primary key( clm1, clm2 ) ); > > insert following: > Clm1 Clm2 > abc 123 > abc 456 > abc 789 > def 123 > def 456 > def 789 > > select * from table where clm1='def'; > - > > Will the returned row always be def - 123. ie. the first row for def? > > I've looked at the query plan for this select and it does use the > index if clm1 alone is in the query and it appears to match on the > first row. > > Also my tests indicate I do get back the first matching row. But I'd > like confirmation if possible. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use of two columns for a key and query on first clm.
Let me rephrase the question slightly. If I do select * from table where clm1='def'; then step through the results will I see all rows that include 'def'. The answer must be yes. The only issue is what order they will be in. If I want them ordered by clm2 then yes I'd probably need use ORDER BY. However in this specific example I would have thought the index order would be used, which is clm1+clm2 which is the same as using ORDER BY clm2. But I appreciate this isn't guaranteed. Friday, March 21, 2008, 11:41:52 AM, you wrote: JS> No, use ORDER BY JS> Neville Franks wrote: >> If I use two columns for a key (primary or separate index) and query >> just on the first column component will I always get back the first >> match in a set. For example. >> >> - >> create table mytable ( clm1 text collate nocase, clm2 text >> collate nocase, constraint mycs1 primary key( clm1, clm2 ) ); >> >> insert following: >> Clm1 Clm2 >> abc 123 >> abc 456 >> abc 789 >> def 123 >> def 456 >> def 789 >> >> select * from table where clm1='def'; >> - >> >> Will the returned row always be def - 123. ie. the first row for def? >> >> I've looked at the query plan for this select and it does use the >> index if clm1 alone is in the query and it appears to match on the >> first row. >> >> Also my tests indicate I do get back the first matching row. But I'd >> like confirmation if possible. >> -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] endless loop example
In trying to break my code with the seinfeld database examples, I found this. SELECT f.name as food, e1.name, e1.season, e2.name, e2.season FROM episodes e1, foods_episodes fe1, foods f, episodes e2, foods_episodes fe2 Why does this put SQLite into an endless loop? (I am not using the shell tool, just preparing the statement as is and stepping throug the rows) - Looking for last minute shopping deals? Find them fast with Yahoo! Search. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] endless loop example
On 3/21/08, Derek Developer <[EMAIL PROTECTED]> wrote: > In trying to break my code with the seinfeld database examples, I found this. Derek, You are going to get much better help from the list, not to mention that you will probably get further with your "code breaking," if you provide some more background to your question. For example, what on earth is this "seinfeld database example" that you are talking about? > SELECT f.name as food, e1.name, e1.season, e2.name, e2.season FROM episodes > e1, foods_episodes fe1, foods f, episodes e2, foods_episodes fe2 > Why does this put SQLite into an endless loop? Probably because there is no JOIN clause causing a cartesian join across 5 tables. > (I am not using the shell tool, just preparing the statement as is and > stepping throug the rows) > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
