Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
On Dec 5, 2007, at 1:24 AM, Joe Wilson wrote: --- Dan <[EMAIL PROTECTED]> wrote: The "b" in the ORDER BY does not match "x1.b" because it is not a simple identifier (according to matchOrderbyToColumn()). It does not match either "" or " as ". After failing to find a match for "b" in the leftmost SELECT, SQLite searches the next leftmost and matches "b" to "b" (column 2). That's how it is at the moment, anyhow. http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html Cheers. I'm starting to realise why this little corner of sqlite is the way it is... I believe that there are 2 different issues with the current implementation: 1. The result set column names of a compound SELECT should drop all table qualifiers, as they've lost all meaning once in a UNION. i.e., instead of: sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2; x1.b|a value|value you should see: b|a value|value as other databases do: mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY b; +--+--+ | b| a| +--+--+ |2 |1 | |9 |0 | +--+--+ mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY x1.b; ERROR 1054 (42S22): Unknown column 'x1.b' in 'order clause' 2. The compound SELECT's ORDER BY statement elements should only be matched against the leftmost SELECT. If there is no match in the leftmost SELECT, then an error should result - even if a match could potentially be found in non-leftmost SELECTs. Or do you disagree? Not in principle. But I think changes that break backwards compatibility would be more trouble than they're worth for something like this. In the absence of clearer guidance from sql-92, it's probably more important to be compatible with earlier sqlite versions than with mysql and friends. Maybe it would be better to document the current behaviour and move on. Dan. __ __ Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/ sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] search time in FTS3 tables sometimes very long
Hi Scott! You're great! I checked the attached modification and found no search taking longer than 20s now! It's a great improvement. I didn't find any other problems, so I will leave the modification in my FTS3 compilation. Many thanks! Ingo Scott Hess schrieb: 2007/12/4 Scott Hess <[EMAIL PROTECTED]>: This seems a little excessive, though. I do see that there's an O(N^2) path in the prefix-searching (loadSegmentLeavesInt()'s call to docListUnion()). I can reasonably make that O(logN), which might help a great deal, if you're hitting it. Not really sure how to tell if you're hitting it, but I'll experiment at my end and see whether I can improve things there. With the attached patch, the time to match against 't*' with the rfc dataset goes from 1m16s to 5s. It passes the tests, but I'll not guarantee that this is what I'll check in. I want to think on it. But let me know if this doesn't help. -scott - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Damaged database file
Hi, Do you have any experiments on damaged file recovery? How can be damaged the opened database file? We are using windows XP. We would like to integrate error handling into our software to handle all cases. Regards, Laszlo -- www.pannonvision.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] File Is Encrypted Or Is Not a Database
"Mark Easton" <[EMAIL PROTECTED]> wrote: > Sorry ... how do I get sqlite v3.4.2? > Any version of SQLite from 3.0.0 through 3.5.3 will open, read, and write a version 3.4.2 database file. If it says "file is encrypted or is not a database" then you do not have a 3.4.2 database file. Something else is wrong. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SOLVED: Allocating Memory For A Pool Of Database Connections
It works when passing the db_cache_list[i]->db to sqlite3_open_v2 without trying to allocate it. Thanks a lot Richard and Ken. - Never miss a thing. Make Yahoo your homepage.
Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
> Not in principle. But I think changes that break backwards > compatibility would be more trouble than they're worth for > something like this. In the absence of clearer guidance > from sql-92, it's probably more important to be compatible > with earlier sqlite versions than with mysql and friends. > > Maybe it would be better to document the current behaviour > and move on. > > Dan. It would be tricky to document the current behavior accurately. Compound queries with ORDER BY without aliases have never really worked in a uniform way in sqlite. I don't think fixing the issue will trouble people, as most must use column aliases and subqueries as a workaround for these problems anyway. I think at the very least, the inconsistency of the column names in the result set should be resolved: SQLite version 3.5.3 Enter ".help" for instructions sqlite> create table foo(a); sqlite> insert into foo values(1); sqlite> .header on sqlite> select foo.a from foo; a 1 sqlite> select foo.a from foo union all select foo.a from foo; a 1 1 sqlite> select foo.a from foo union all select foo.a from foo order by 1; foo.a 1 1 sqlite> select foo.a from foo union all select foo.a from foo group by 1; a 1 1 Notice the column headings. Why does the column name change in the result set because of the addition of an ORDER BY but not with a GROUP BY clause or with a regular non-compound query? And should regular queries support expressions in ORDER BY, while compound statements not? sqlite> select a from foo order by a*a-3*a; 1 sqlite> select a from foo union all select a+5 as a from foo order by a*a-3*a; SQL error: ORDER BY term number 1 does not match any result column The only way to get this query to work is to use this workaround: sqlite> select * from (select a from foo union all select a+5 from foo) order by a*a-3*a; 1 6 Other databases allow expressions in compound SELECT/ORDER BY without the subquery: mysql> select a from foo union all select a+5 as a from foo order by a*a-3*a; +--+ | a| +--+ |1 | |6 | +--+ I only have MySQL to test with here. I'm fairly certain it works on most other open source and popular commercial databases. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Damaged database file
On Wed, 05 Dec 2007 13:17:55 +0100, Pannonvision <[EMAIL PROTECTED]> wrote: >Hi, > >Do you have any experiments on damaged file recovery? How can be >damaged the opened database file? We are using windows XP. It's really hard to damage: http://www.sqlite.org/atomiccommit.html and SQLite repairs the database itself automatically. All common cases for corruption are taken care of, you don't have to implement anything except a good backup regime. In exotic cases, damage can still occur, read chapter 9.0 Things That Can Go Wrong Of you want to damage your database intentionally, you can: http://www.sqlite.org/lockingv3.html has a chapter 6.0 How To Corrupt Your Database Files >We would like to integrate error handling into our software to handle >all cases. > >Regards, > >Laszlo -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to check if the column is existed in the table
Hi All, Is it possible to check if the certain column is existed in the table. For example : I had the table as define below: create table tablea (appid int, bytesIn int, bytesOut int) and I want to add one more column with default value of 1 and I don't want to drop the table and recreate it again because I want to keep the data in the table. Thanks, jp Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Re: [sqlite] How to check if the column is existed in the table
On Wed, 5 Dec 2007 09:56:30 -0800 (PST), Joanne Pham <[EMAIL PROTECTED]> wrote: >Hi All, >Is it possible to check if the certain column is existed in the table. >For example : I had the table as define below: > create table tablea (appid int, bytesIn int, bytesOut int) You can get information about columns in a table with PRAGMA table_info(tablea); >and I want to add one more column with default >value of 1 and I don't want to drop the table >and recreate it again because I want to keep >the data in the table. -- rename the original table ALTER TABLE tablea RENAME TO tablea_old; -- create the new table CREATE TABLE tablea ( appid int, bytesIn int, bytesOut int, newcol int DEFAULT 1 ); -- Load data into the new table INSERT INTO tablea (appid, bytesIn, bytesOut) SELECT appid, bytesIn, bytesOut FROM tablea_old; -- Drop the original table DROP TABLE tablea_old; -- optimize the database VACUUM; ANALYZE; >Thanks, >jp HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Is this a valid use of attach?
Thanks, that solved the issue, of course if I had checked the error message I would have solved it myself :( Mike -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 05 December 2007 16:27 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is this a valid use of attach? "Mike Marshall" <[EMAIL PROTECTED]> wrote: > Possibly a stupid question but I'm trying to do an attach and then query via > a single sqlite3_exec() statement. > > > > Is the following a valid use? It is if the name of your database file is literally "dbpath". But from context, I suspect you are thinking that dbpath is some kind of variable that holds the database file name. That is incorrect. Substitute the name of your file (appropriately quoted) in place of where you have written "dbpath". > > > > sqlite3_exec(pDB,"ATTACH dbpath as universe;select * from othertable where > identifier IN (select identifier from > universe.identifierlist)",cbfn,NULL,NULL); > > > > Thanks in advance > > > > Mike - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] how to cast utf16 text to int?
I have table that holds values of different types in utf16. I also know value type for the current row. How should I cast value to compare it with integer? This test shows 1 instead of 17 that I expected. sqlite> create table t (value text, field_type int); sqlite> insert into t values (X'31003700', 1); sqlite> select value from t; 1 sqlite> I know that sqlite3.exe does not support Unicode but problem persist in api call also. If I use this query: select count(1) as num from t where type = 1 and value = ? and bind MBC buffer with sqlite3_bind_blob it works fine for value = 3100 (i.e. 1) but return no data when value consists of more than one digit. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Is this a valid use of attach?
Possibly a stupid question but I'm trying to do an attach and then query via a single sqlite3_exec() statement. Is the following a valid use? sqlite3_exec(pDB,"ATTACH dbpath as universe;select * from othertable where identifier IN (select identifier from universe.identifierlist)",cbfn,NULL,NULL); Thanks in advance Mike
[sqlite] unable to open a temporary database file for storing temporary tables
This is my first post. I thought I had osted yesterday but do not see it so forgive me if this is a duplicate. I have seen the error "malformed database schema - unable to open a temporary database file for storing temporary tables" discussed in another thread relative to PHP and BSD Unix. However, I am having this problem on Windows 2003 using the Tcl API. If I run the .databases command, I see something like: C:\DOCUME~1\charlie\LOCALS~1\Temp\sqlite_PfaaLBX02TU6g9w Is the DOS like path statement a problem? The application runs fine for a few days but then gets the above error. Shutting everything down and restarting seems to clear it for a whle. Would periodically clearing the cache help? I would appreciate any ideas from this group.
[sqlite] Re: How to check if the column is existed in the table
Joanne Pham wrote: Is it possible to check if the certain column is existed in the table. For example : I had the table as define below: create table tablea (appid int, bytesIn int, bytesOut int) and I want to add one more column with default value of 1 and I don't want to drop the table and recreate it again because I want to keep the data in the table. http://sqlite.org/lang_altertable.html Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to check if the column is existed in the table
Hi Kees, Is there anyway that we can find out how many columns are defined in the table? Thanks for the info. JP - Original Message From: Kees Nuyt <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, December 5, 2007 10:18:48 AM Subject: Re: [sqlite] How to check if the column is existed in the table On Wed, 5 Dec 2007 09:56:30 -0800 (PST), Joanne Pham <[EMAIL PROTECTED]> wrote: >Hi All, >Is it possible to check if the certain column is existed in the table. >For example : I had the table as define below: > create table tablea (appid int, bytesIn int, bytesOut int) You can get information about columns in a table with PRAGMA table_info(tablea); >and I want to add one more column with default >value of 1 and I don't want to drop the table >and recreate it again because I want to keep >the data in the table. -- rename the original table ALTER TABLE tablea RENAME TO tablea_old; -- create the new table CREATE TABLE tablea ( appid int, bytesIn int, bytesOut int, newcol int DEFAULT 1 ); -- Load data into the new table INSERT INTO tablea (appid, bytesIn, bytesOut) SELECT appid, bytesIn, bytesOut FROM tablea_old; -- Drop the original table DROP TABLE tablea_old; -- optimize the database VACUUM; ANALYZE; >Thanks, >jp HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] - Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Re: [sqlite] unable to open a temporary database file for storing temporary tables
Regarding the DOS-like name, it may be the canonical name on Windows for FAT/VFAT file systems. Is that what you're using? I wonder if there's a hash collision in trying to get the file name down to DOS 8.3 file name format. Do you have many sqlite_* files in that directory? Since the file prefix is sqlite_ instead of etilqs_, you must be using an older sqlite release. A newer version may have solved the problem. Some guesses: Do you have sufficient space and/or privileges in that directory? Is some anti-virus program running doing some crazy stuff like locking directories or temporarily filling up Temp? Is there a limit on the number of files in a directory on Windows? --- Charlie Bursell <[EMAIL PROTECTED]> wrote: > I have seen the error "malformed database schema - unable to open a > temporary database file for storing temporary tables" discussed in > another thread relative to PHP and BSD Unix. However, I am having this > problem on Windows 2003 using the Tcl API. > > If I run the .databases command, I see something like: > > C:\DOCUME~1\charlie\LOCALS~1\Temp\sqlite_PfaaLBX02TU6g9w > > Is the DOS like path statement a problem? > > The application runs fine for a few days but then gets the above error. > Shutting everything down and restarting seems to clear it for a whle. > > Would periodically clearing the cache help? Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] File Is Encrypted Or Is Not a Database
Mark Easton wrote: It seems more than a little strange that I cannot determine the version of a sqlite db. I see you have solved your problem, but for future reference (and for others' information) you could have simply executed the following query using Perl to determine the version of sqliite that it was using: select sqlite_version(); It will return the version as a string. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] determining storage type
Hi, How do you tell what the storage type of a field is? If a field returns 1234, how can you tell if it is stored as an integer or text? Thanks Hua-Ying
Re: [sqlite] SQLite is in Android
> http://www.sqlite.org/famous.html Btw, you write there: > There are unconfirmed reports on the internet that Apple also uses SQLite in > the iPhone and in the iPod touch. I'm pretty sure that SQLite is used there, I browsed my phone and saw several instances of SQLite there, I'd say that they use it almost for any data storage in iPhone... ;-) Jiri - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] determining storage type
Hua-Ying Ling wrote: How do you tell what the storage type of a field is? If a field returns 1234, how can you tell if it is stored as an integer or text? If you are using the command line shell the typeof() function will return the type. select field, typeof(field) from a_tabe; HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid use of attach?
"Mike Marshall" <[EMAIL PROTECTED]> wrote: > Possibly a stupid question but I'm trying to do an attach and then query via > a single sqlite3_exec() statement. > > > > Is the following a valid use? It is if the name of your database file is literally "dbpath". But from context, I suspect you are thinking that dbpath is some kind of variable that holds the database file name. That is incorrect. Substitute the name of your file (appropriately quoted) in place of where you have written "dbpath". > > > > sqlite3_exec(pDB,"ATTACH dbpath as universe;select * from othertable where > identifier IN (select identifier from > universe.identifierlist)",cbfn,NULL,NULL); > > > > Thanks in advance > > > > Mike - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] unable to open a temporary database file for storing temporary tables
Thanks for the response. I have finally been able to look at the system where this is running. You are correct. They are on an older system (2.8.17). I'll try to get them to update. They are using 3 different databases. When I issue a .databases command I see that all three are using the same temporary database. Is it possible they could be bumping heads? When issuing PRAGMAS, do they have to be issued from sqlite executable or can they also be issued via the Tcl API? Thanks -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 05, 2007 2:35 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] unable to open a temporary database file for storing temporary tables Regarding the DOS-like name, it may be the canonical name on Windows for FAT/VFAT file systems. Is that what you're using? I wonder if there's a hash collision in trying to get the file name down to DOS 8.3 file name format. Do you have many sqlite_* files in that directory? Since the file prefix is sqlite_ instead of etilqs_, you must be using an older sqlite release. A newer version may have solved the problem. Some guesses: Do you have sufficient space and/or privileges in that directory? Is some anti-virus program running doing some crazy stuff like locking directories or temporarily filling up Temp? Is there a limit on the number of files in a directory on Windows? --- Charlie Bursell <[EMAIL PROTECTED]> wrote: > I have seen the error "malformed database schema - unable to open a > temporary database file for storing temporary tables" discussed in > another thread relative to PHP and BSD Unix. However, I am having this > problem on Windows 2003 using the Tcl API. > > If I run the .databases command, I see something like: > > C:\DOCUME~1\charlie\LOCALS~1\Temp\sqlite_PfaaLBX02TU6g9w > > Is the DOS like path statement a problem? > > The application runs fine for a few days but then gets the above error. > Shutting everything down and restarting seems to clear it for a whle. > > Would periodically clearing the cache help? Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: determining storage type
Hua-Ying Ling <[EMAIL PROTECTED]> wrote: How do you tell what the storage type of a field is? If a field returns 1234, how can you tell if it is stored as an integer or text? sqlite3_column_type Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] benchmarking UTF8 vs UTF16 encoded databases
Kees Nuyt <[EMAIL PROTECTED]> writes: > > Just a suggestion: Perhaps even on the home page. > > "This the homepage for SQLite - a library that implements a > self-contained, serverless, zero-configuration, _portable_, > transactional SQL database engine." > > With a link to a 'Portable' paragraph on the 'Distinctive > Features' page http://www.sqlite.org/different.html A very good suggestion. It is interesting, that normally when software is portable. Then the software is designed and developed in a way so that the software will compile and build on various machines (hereamong HW architectures). In this situation the emphasis should be that the fileformat, even though it is binary, is portable (even across HW architectures). Intuitively one would expect that text-based fileformats are portable, and binary file formats are not portable. Jarl - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite is in Android
One of my co-workers was playing around with SQLite on his iPhone and was able to access data including contacts and call log and pretty much everything. It's a SQLite database and not encrypted. Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Jiri Hajek [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 05, 2007 6:03 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite is in Android > http://www.sqlite.org/famous.html Btw, you write there: > There are unconfirmed reports on the internet that Apple also uses SQLite in the iPhone and in the iPod touch. I'm pretty sure that SQLite is used there, I browsed my phone and saw several instances of SQLite there, I'd say that they use it almost for any data storage in iPhone... ;-) Jiri - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to check if the column is existed in the table
Hi Joanne, On Wed, 5 Dec 2007 11:53:03 -0800 (PST), Joanne Pham <[EMAIL PROTECTED]> wrote: > Hi Kees, > Is there anyway that we can find out how > many columns are defined in the table? Interpret the results of PRAGMA table_info(tablea); or Do a SELECT * LIMIT 1 on the table and get the information from the result set. There was a discussion over that last method just one or two days ago, so you could try the archives. sqlite3_column_count() perhaps? >Thanks for the info. >JP HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite is in Android
Actually the SQLite Database doesn't need to be encrypted it shouldn't be jailbroken or hacked into, or broken in any way. Also I am sure that an encryption algorithm inside the iPhone would cause long boot times, etc. and space issues since it already has limited space for the actual OS anyway. AT&T users have no access to the data on other phone user's phones so security is more or less not a problem. On Dec 5, 2007, at 7:42 PM, Samuel R. Neff wrote: One of my co-workers was playing around with SQLite on his iPhone and was able to access data including contacts and call log and pretty much everything. It's a SQLite database and not encrypted. Sam - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite:Deletion in Joins method
Hi Dennis,
Thanks a lot for ur help.
Ya.Its deleting properly in all the tables by the following trigger.
"CREATE TRIGGER fkdc_MUSIC
AFTER DELETE ON MUSIC
FOR EACH ROW BEGIN
DELETE from ARTIST where ArtistId = old.Artist_Id and not exists (select id
from MUSIC where Artist_Id = old.Artist_Id); DELETE from ALBUM where AlbumId
= old.Album_Id and not exists (select Id from MUSIC where Album_Id =
old.Album_Id); DELETE from BGM where BgmId = old.Bgm_Id and not exists
(select Id from MUSIC where Bgm_Id = old.Bgm_Id); DELETE from PLAYLIST where
TrackId = old.Id and not exists (select Id from MUSIC where Id = old.Id);
END;"
Right now I am trying to INSERT and UPDATE by means of triggers.
My doubt is:
"CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, Album
Text,unique(Album));"
"CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,Artist
Text,unique(Artist));"
"CREATE TABLE BGM (BgmId INTEGER PRIMARY KEY NOT NULL,Bgm
Text,unique(Bgm));"
"CREATE TABLE PLAYLIST(PlayListName Text,TrackId INTEGER);"
"CREATE TABLE MUSIC (Id INTEGER PRIMARY KEY NOT NULL,Album_Id
INTEGER,Artist_Id INTEGER,Bgm_Id INTEGER,Track text);"
Now For insertion,
1.Do I want to insert in to ALBUM table first or I can directly insert into
MUSIC table.If I am entering into MUSIC table I wont get the AlbumId from
ALBUM table since it is not inserted.Similar for ARTIST and BGM table.
By using Triggers is it possible to insert in to all tables .In
ALBUM,ARTIST,BGM tables only unique values of Album,Artist,Bgm should be
present.i.e,if I am entering a record in MUSIC table and the Album_Id is
already present in ALBUM table then I wont insert that Album in ALBUM table.
I tried with the following and its working.
"insert or ignore into ALBUM (Album) values('Album3');"
"insert or ignore into ARTIST (Artist) values('Artist1');"
"insert or ignore into BGM (Bgm) values('Rock');"
"insert into MUSIC (Album_Id,Artist_Id,Bgm_Id,Track) values((select AlbumId
from ALBUM where Album='Album3'),(select ArtistId from ARTIST where
Artist='Artist1'),(select BgmId from BGM where Bgm='Rock'),'Track1.mp3');"
I want to know weather the above is better than TRIGGERS.
If we can do this by INSERT TRIGGERS how the statement looks like [For both
insert and update].
Even if update any records in one table It has to be changed in all the
others Please help to solve this.
Thanks & Regards,
Sreedhar.A
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 04, 2007 9:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite:Deletion in Joins method
Sreedhar.a wrote:
> CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text);
> CREATE TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist
> Text); CREATE TABLE BGM (BgmId INTEGER NOT NULL PRIMARY KEY,Bgm Text);
>
> CREATE TABLE MUSIC (Id INTEGER NOT NULL PRIMARY KEY,Album_Id INTEGER
> CONSTRAINT fk_Album_id REFERENCES ALBUM(AlbumId) ON DELETE
> CASCADE,Artist_Id INTEGER NOT NULL CONSTRAINT fk_Artist_id REFERENCES
> ARTIST(ArtistId) ON DELETE CASCADE,Bgm_Id INTEGER NOT NULL CONSTRAINT
> fk_Bgm_id REFERENCES BGM(BgmId)ON DELETE CASCADE );
>
> I created a trigger as follows .
>
> Here I am checking after deleting that record in Music Table I will
> check wheather that Artist_id is present now in that table MUSIC i.e,
> (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id =
> OLD.Artist_Id).And if that Artist_Id is Null then I will delete it in
> the
ARTIST table.
>
> But this is not happening with the below trigger.
> Do I need to add more constraints in the below trigger.
> Please help to solve this.
>
> "CREATE TRIGGER fkdc_MUSIC
> AFTER DELETE ON MUSIC
> FOR EACH ROW
> BEGIN
> SELECT CASE
> WHEN
> (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id)
> IS NOT NULL THEN 'DELETE FROM ARTIST WHERE ArtistId=OLD.Artist_Id'
> END;
> END;"
>
>
>
You need to use a conditional delete in your trigger. You can't do that
using a select with a case statement. You could try something like this
(untested):
CREATE TRIGGER fkdc_MUSIC
AFTER DELETE ON MUSIC
FOR EACH ROW
BEGIN
delete from Artist
where ArtistId = old.ArtistId
and not exist (select id from music where ArtistId = old.ArtistId);
delete from Album
where AlbumId = old.AlbumId
and not exist (select id from music where AlbumId = old.AlbumId);
delete from BGM
where BgmId = old.BgmId
and not exist (select id from music where BgmId = old.BgmId); END;"
A couple of other things to note:
You should probably change your table definitions to key the phrase "INTEGER
PRIMARY KEY" together so that sqlite can use its btree key optimization.
Instead of this:
CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text);
use this:
CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, Album Text);
Also, if you want to speed up the searches in the deletes trigger above, at
the expense of slowing down all the insert and delete operat
