Re: [sqlite] Trouble with constraints and triggers
On 23/09/2010 11:52 p.m., Richard Hipp wrote: >> Josh Gibbswrote: >>> CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT, >>> Subject TEXT); >>> CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES >>> Message(message_id) ON DELETE CASCADE, recipient_ID INTEGER REFERENCES >>> Recipient(recipient_id)); >>> CREATE TABLE Recipient (recipient_ID INTEGER PRIMARY KEY AUTOINCREMENT, >>> name); >>> >>> I've tried creating a trigger after delete on MessageRecipient to remove >> the >>> referenced Recipient, and this works if it's the only related item, >>> however any >>> other MessageRecipient relationship causes the delete to fail. As there >>> is no >>> 'or ignore' for the delete statement, I can't get this to keep my data >>> clean. > > DELETE FROM recipient > WHERE recipient_id = old.recipient_id > AND NOT EXISTS(SELECT 1 FROM message_recipient > WHERE recipient.recipient_id= > message_recipient.recipient_id); > This works perfectly, thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build
They are still using 16-bits offsets... arghh! On Thu, Sep 23, 2010 at 14:10, Shane Harrelsonwrote: > This limitation has been around for a while in the MS Visual > debuggers... I can't find the MSDN article that discusses it, but > once you exceed 64k lines, all bets are off. > > Work arounds include using the canonical source to build and debug, or > stripping comment lines, white space etc. from the amalgamation to get > below 64k. > > > HTH. > -Shane > > > On Thu, Sep 23, 2010 at 10:54 AM, Ben Harper wrote: > > I can't confirm this behaviour on anything other than 2010. But I seem to > recall the same business a few months ago, when I must have been on 2008. > > > > -Original Message- > > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Virgilio Fornazin > > Sent: 23 September 2010 03:24 PM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build > > > > Which MSVC compiler has this bug? There's a link to information on that ? > > > > On Thu, Sep 23, 2010 at 10:17, Ben Harper wrote: > > > >> I just discovered the MSVC compiler generates bad debug info for source > >> files larger than 64k lines, which is the case with the Sqlite > amalgamation. > >> Does anyone know of a workaround? > >> > >> Thanks, > >> Ben > >> ___ > >> 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 > > > > > > ___ > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build
This limitation has been around for a while in the MS Visual debuggers... I can't find the MSDN article that discusses it, but once you exceed 64k lines, all bets are off. Work arounds include using the canonical source to build and debug, or stripping comment lines, white space etc. from the amalgamation to get below 64k. HTH. -Shane On Thu, Sep 23, 2010 at 10:54 AM, Ben Harperwrote: > I can't confirm this behaviour on anything other than 2010. But I seem to > recall the same business a few months ago, when I must have been on 2008. > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Virgilio Fornazin > Sent: 23 September 2010 03:24 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build > > Which MSVC compiler has this bug? There's a link to information on that ? > > On Thu, Sep 23, 2010 at 10:17, Ben Harper wrote: > >> I just discovered the MSVC compiler generates bad debug info for source >> files larger than 64k lines, which is the case with the Sqlite amalgamation. >> Does anyone know of a workaround? >> >> Thanks, >> Ben >> ___ >> 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 > > > ___ > 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
[sqlite] Potential Bug, Select appears to hang
There are more fields in each table, but for the sake of brevity, I ommited them from the snippets below. With smaller data sets, the second query works just fine as well. SELECT COUNT(*) FROM link; 960219 SELECT COUNT(*) FROM node; 812193 Pvid's are INTEGERS. On linux, the following query executes just fine from the interactive shell: "SELECT COUNT(*) FROM link ld LEFT OUTER JOIN node n ON n.pvid = ld.light_node_pvid WHERE ld.light_node_pvid != '-1' AND ld.light_node_pvid != '-2' AND n.pvid IS NULL;" But, this one appears to hang(note the white space and eols in this one): "SELECT COUNT(*) FROM link ld LEFT OUTER JOIN node n ON n.pvid = ld.light_node_pvid WHERE ld.light_node_pvid != '-1' AND ld.light_node_pvid != '-2' AND n.pvid IS NULL;" I get the same results with perl::dbi. Any idea's? The information contained in this communication may be CONFIDENTIAL and is intended only for the use of the recipient(s) named above. If you are not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please notify the sender and delete/destroy the original message and any copy of it from your computer or paper files. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build
I can't confirm this behaviour on anything other than 2010. But I seem to recall the same business a few months ago, when I must have been on 2008. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Virgilio Fornazin Sent: 23 September 2010 03:24 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build Which MSVC compiler has this bug? There's a link to information on that ? On Thu, Sep 23, 2010 at 10:17, Ben Harperwrote: > I just discovered the MSVC compiler generates bad debug info for source > files larger than 64k lines, which is the case with the Sqlite amalgamation. > Does anyone know of a workaround? > > Thanks, > Ben > ___ > 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How does xBestIndex know that a LIKE query is case insensitive?
Thanks - I've stepped through some of where.c to try and understand exactly what's going on, but in vain. I'm looking around line 1272 of where.c, but I can't find where the system decides not to call xBestIndex because of a collation mismatch. By collation mismatch, I mean the column's collation sequence is not 'NOCASE', whereas the LT/GT expressions are NOCASE. I assume that's the condition that decides whether or not xBestIndex is invoked? To be clear, when you say "the collating sequence is NOCASE", do you mean that the column named 'field' has a collating sequence of 'NOCASE'? If so, I don't understand how this comes to be. The docs clearly state that the default is BINARY, and I've tried explicitly creating my vtable with (field COLLATE BINARY)... so that seems unlikely to me. But I'll have to dig further. Ben -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: 23 September 2010 01:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] How does xBestIndex know that a LIKE query is case insensitive? On Thu, Sep 23, 2010 at 7:16 AM, Ben Harperwrote: > I have a virtual table implementation that implements the > xBestIndex/xFilter functions. > Problem: > A LIKE query (for eg. field LIKE "prefix%") gets sets to xBestIndex as a > GT/LT pair. > However, I can't tell from the sqlite3_index_info whether that GT/LT should > be NOCASE collation or BINARY collation. I want the default LIKE behaviour, > which is NOCASE, but I can't figure out where to glean this information from > inside xBestIndex. > > Am I missing something? > LIKE will only get converted to a GT/LT pair if the collating sequence is NOCASE, or if you have specified PRAGMA case_sensitive_like=ON and the collating sequence is BINARY. So if you have a GT/LT pair in xBestIndex and you have not missed with case_sensitive_like, then you can be sure that the collating sequence is NOCASE. > > Thanks, > Ben > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ 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
[sqlite] MyJSQLView Version 3.23 Released
MyJSQLView Version 3.23 Released The MyJSQLView project is pleased to provide the release of Version 3.23 to the public. The release marks the addition of support for the SQLite database. New to this release of MyJSQLView is also the support for in memory databases that is featured for both the SQLite and HSQL databases. What this means is data sets can now be imported into a table and MyJSQLView should be able to perform quicker searches and sorts of the in memory data. The group has also abandoned the practice of not including some of the open source plugins for the application that it produces. The last release of the application did not include the TableFieldProfiler plugin with MyJSQLView and as a result the downloads of that plugin amounted to less then 5% of the total MyJSQLView downloads. The group thought users were really missing out on a valuable tool which had been expanded and included internationalization. So the TableFieldProfiler is now included with the standard MyJSQLView download. This release of MyJSQLView has also enhanced the plugin architecture by letting users manually load plugins from alternative directories than the default lib/plugins directory. You will find access to the Plugin Management Tool in the top main tab menu for the application. Speaking of plugins, developers will now find the Plugin Basics Tutorial has been updated and an advanced one has also been created under the documentation of the web site. Since this is a release that supports a new database make sure and check out the additional entries that have been given for the connection parameters in the example reference myjsqlview.xml file. We would also like to let you know that a new database is available at the web site for the US Congress. The data set is of the legislative branches' representatives, courtesy of Sunlight Labs. Dana M. Proctor MyJSQLView Project Manager http://dandymadeproductions.com/projects/MyJSQLView/ MyJSQLView provides an easy to use Java based user interface frontend for viewing, adding, editing, or deleting entries in the the SQLite databases. A query frame allows the building of complex SELECT SQL statements. The application allows easy sorting, searching, and import/export of table data. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE OR IGNORE statement?
On 23 Sep 2010, at 2:59pm, Andy Gibbs wrote: > It seems to be a trade-off -- either the complexity is in the DELETE > statement to keep the primary key table tidy or in the SELECT statement > querying it. If it has to be a choice, then the complexity has to be in the > DELETE statement since this happens very infrequently. Hmm. Yes, I think so. Some part of your code somewhere has to list all the secondary tables, and you already have it in the least annoying place. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE OR IGNORE statement?
On Thursday, September 23, 2010 3:36 PM, Simon Slavin wrote: > On 23 Sep 2010, at 2:13pm, Andy Gibbs wrote: > >> I've got a table with a primary key and then any number of additional >> tables >> with foreign keys that reference this primary key table with "ON DELETE >> RESTRICT" > > I always worry when I see descriptions like this. Those additional > tables: do they all have the same columns ? If so, can you amagamate > them all into one big table ? Just insert one extra column saying what > kind of row this row is. > > Not only does this fix the problem you raised, but it means you don't > need to change your schema each time you encounter a new type of > information. Thanks for the suggestion, Simon. If only it were that simple. Unfortunately, each of the foreign key tables are actually quite distinct in their purpose, so putting them all into one huge table would not be the right solution. The primary key is a timestamp (as an integer, i.e. number of seconds since some arbitrary epoch or other). The primary key table holds then the "common" information on the "action" that has happened, i.e. timestamp, user name, and some other data. The foreign key tables are all those that hold data for the particular actions that can be done, but really they are very very different from each other. Of course it would have been possible instead to merge the columns from the primary key table into each of the foreign key tables and not have the primary key table, but the really nice thing about keeping the common data it central, is that only one table needs to be queried e.g. to find out the which users have been making alterations to the system and when (this is one of the main design requirements). It seems to be a trade-off -- either the complexity is in the DELETE statement to keep the primary key table tidy or in the SELECT statement querying it. If it has to be a choice, then the complexity has to be in the DELETE statement since this happens very infrequently. Cheers Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE OR IGNORE statement?
On 23 Sep 2010, at 2:13pm, Andy Gibbs wrote: > I've got a table with a primary key and then any number of additional tables > with foreign keys that reference this primary key table with "ON DELETE > RESTRICT" I always worry when I see descriptions like this. Those additional tables: do they all have the same columns ? If so, can you amagamate them all into one big table ? Just insert one extra column saying what kind of row this row is. Not only does this fix the problem you raised, but it means you don't need to change your schema each time you encounter a new type of information. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build
Which MSVC compiler has this bug? There's a link to information on that ? On Thu, Sep 23, 2010 at 10:17, Ben Harperwrote: > I just discovered the MSVC compiler generates bad debug info for source > files larger than 64k lines, which is the case with the Sqlite amalgamation. > Does anyone know of a workaround? > > Thanks, > Ben > ___ > 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] Breakpoints uncorrelated on MSVC amalgamation build
OK. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Teg Sent: 23 September 2010 03:20 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build Hello Ben, Thursday, September 23, 2010, 9:17:39 AM, you wrote: BH> I just discovered the MSVC compiler generates bad debug info for BH> source files larger than 64k lines, which is the case with the Sqlite amalgamation. BH> Does anyone know of a workaround? BH> Thanks, BH> Ben BH> ___ BH> sqlite-users mailing list BH> sqlite-users@sqlite.org BH> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Don't use the amalgamation? That's one reason I don't use it. -- Best regards, Tegmailto:t...@djii.com ___ 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] Breakpoints uncorrelated on MSVC amalgamation build
Hello Ben, Thursday, September 23, 2010, 9:17:39 AM, you wrote: BH> I just discovered the MSVC compiler generates bad debug info for BH> source files larger than 64k lines, which is the case with the Sqlite amalgamation. BH> Does anyone know of a workaround? BH> Thanks, BH> Ben BH> ___ BH> sqlite-users mailing list BH> sqlite-users@sqlite.org BH> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Don't use the amalgamation? That's one reason I don't use it. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Breakpoints uncorrelated on MSVC amalgamation build
I just discovered the MSVC compiler generates bad debug info for source files larger than 64k lines, which is the case with the Sqlite amalgamation. Does anyone know of a workaround? Thanks, Ben ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DELETE OR IGNORE statement?
Hi, I've got a table with a primary key and then any number of additional tables with foreign keys that reference this primary key table with "ON DELETE RESTRICT" hence stopping the deletion of any row from the primary key table if there exists any rows in any of the foreign key tables that reference that row. All well and fine, but what I'd really like is to be able to tidy up the primary key table when rows are no longer referenced by any foreign key table (e.g. following deletes in a foreign key table). At this point, a simple "DELETE OR IGNORE FROM pkey_table" should have sufficed. However, I was surprised to discover that "DELETE OR IGNORE" is not an option. (I guess it may not even be standard SQL - I don't know, but it seems an odd omission if so!) Anyone got a better way of doing this? I've thought about a mammoth "DELETE FROM pkey_table WHERE pkey NOT IN (SELECT fkey FROM fkey_tab1 UNION SELECT fkey FROM fkey_tab2 UNION SELECT ... UNION SELECT ... ... ...)", but the problem is that there are quite a number of foreign key tables (and more get added from time to time) and I'd really like something I can stick in a AFTER DELETE trigger on each of the foreign key tables without the problem of having to update all the triggers each time a table is added/removed. Thanks for any suggestions! Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OS/2] Update OS/2 support in sqlite 3.7.x
On Wed, Sep 22, 2010 at 6:56 PM, Walter Meinlwrote: > This defect report is cloned from mozilla bug595599 > RW: "This patch disables WAL journalling on OS/2 since that feature > requires memory-mapped file i/o which OS/2 doesn't support, and makes > other small changes where needed." > The patch was originally created by Rich Walsh against the amalgamation > file in the mozilla-tree (SQlite v 3.7.1) > I've installed fossil and broke up the patch to apply against current > trunk files (src/os.h, src/os_os2.c and src/mutex_os2.c) > The patch can be downloaded from this link. > https://bugzilla.mozilla.org/attachment.cgi?id=477692 > The original patch: > https://bugzilla.mozilla.org/attachment.cgi?id=474575 > If the underlying VFS does not support shared-memory (which the OS/2 VFS does not) then SQLite simply will not go into WAL mode. No patching is needed for this. Everything should work as delivered. What exactly is malfunctioning? What is the problem that this patch attempts to fix? > Additional question for future reports: Is it possible to add > attachments to the mailing list? > Thanks, Walter > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite GUID in WHERE Clause
> I know the literal GUID value shown is correct, as I copied it directly from > the results pane when I do a SELECT * FROM UserRole, however, as soon as I > add the WHERE clause, I get no results. Execute 'SELECT UserId, typeof(UserId) FROM UserRole' preferably from sqlite command line utility and see what SQLite datatype and value is stored in your GUID column. It will suggest you how you should use it to make a literal value. I bet your GUID value is stored as BLOB and whatever utility you use to query the database recognizes GUID type in table definition and automatically transforms it to standard string representation before displaying. Pavel On Wed, Sep 22, 2010 at 4:15 PM,wrote: > Simple question, likely to have a simple answer. > > I have tables in SQLite using GUID primary key columns. I am attempting to > select a row based on its literal guid value in C#.Net. > > Here's a sample query: > > SELECT Role.Id, Role.Name, Role.Description, Role.Active, Role.BitMask > FROM UserRole INNER JOIN > Role ON UserRole.RoleId = Role.Id > WHERE (UserRole.UserId = '{29831334-a434-4c06-a297-b58889f4d3c6}') > > I know the literal GUID value shown is correct, as I copied it directly from > the results pane when I do a SELECT * FROM UserRole, however, as soon as I > add the WHERE clause, I get no results. > > I know there is an option to set BinaryGuid=True/False, at the connection > level, and I have done this both ways with the same results. > > Is there some other way to declare the literal guid value so that it will > match? (I have tried without the { } pair as well, BTW). > > Thanks! > ___ > 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] How does xBestIndex know that a LIKE query is case insensitive?
On Thu, Sep 23, 2010 at 7:16 AM, Ben Harperwrote: > I have a virtual table implementation that implements the > xBestIndex/xFilter functions. > Problem: > A LIKE query (for eg. field LIKE "prefix%") gets sets to xBestIndex as a > GT/LT pair. > However, I can't tell from the sqlite3_index_info whether that GT/LT should > be NOCASE collation or BINARY collation. I want the default LIKE behaviour, > which is NOCASE, but I can't figure out where to glean this information from > inside xBestIndex. > > Am I missing something? > LIKE will only get converted to a GT/LT pair if the collating sequence is NOCASE, or if you have specified PRAGMA case_sensitive_like=ON and the collating sequence is BINARY. So if you have a GT/LT pair in xBestIndex and you have not missed with case_sensitive_like, then you can be sure that the collating sequence is NOCASE. > > Thanks, > Ben > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble with constraints and triggers
Josh Gibbswrote: > On 23/09/2010 3:15 p.m., Igor Tandetnik wrote: >> You could do something like >> >> delete from Recipient where recipient_ID = old.recipient_ID and >>recipient_ID not in (select recipient_ID from MessageRecipient); >> > > That was the last idea we had as well. Trouble is MessageRecipient > contains hundreds > of thousands of records. Would that cipple the speed of the delete, or > would the query > apply the 'not in' to the sub-select on its index? If there is an index on MessageRecipient(recipient_ID), the query should use it. Alternatively, you could maintain a reference count in Recipient (with still more triggers), and delete the record once the count reaches zero. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble with constraints and triggers
On Wed, Sep 22, 2010 at 11:15 PM, Igor Tandetnikwrote: > Josh Gibbs wrote: > > CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT, > > Subject TEXT); > > CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES > > Message(message_id) ON DELETE CASCADE, recipient_ID INTEGER REFERENCES > > Recipient(recipient_id)); > > CREATE TABLE Recipient (recipient_ID INTEGER PRIMARY KEY AUTOINCREMENT, > > name); > > > > I've tried creating a trigger after delete on MessageRecipient to remove > the > > referenced Recipient, and this works if it's the only related item, > > however any > > other MessageRecipient relationship causes the delete to fail. As there > > is no > > 'or ignore' for the delete statement, I can't get this to keep my data > > clean. > > You could do something like > > delete from Recipient where recipient_ID = old.recipient_ID and > recipient_ID not in (select recipient_ID from MessageRecipient); > DELETE FROM recipient WHERE recipient_id = old.recipient_id AND NOT EXISTS(SELECT 1 FROM message_recipient WHERE recipient.recipient_id= message_recipient.recipient_id); > > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [OS/2] Update OS/2 support in sqlite 3.7.x
This defect report is cloned from mozilla bug595599 RW: "This patch disables WAL journalling on OS/2 since that feature requires memory-mapped file i/o which OS/2 doesn't support, and makes other small changes where needed." The patch was originally created by Rich Walsh against the amalgamation file in the mozilla-tree (SQlite v 3.7.1) I've installed fossil and broke up the patch to apply against current trunk files (src/os.h, src/os_os2.c and src/mutex_os2.c) The patch can be downloaded from this link. https://bugzilla.mozilla.org/attachment.cgi?id=477692 The original patch: https://bugzilla.mozilla.org/attachment.cgi?id=474575 Additional question for future reports: Is it possible to add attachments to the mailing list? Thanks, Walter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite GUID in WHERE Clause
Simple question, likely to have a simple answer. I have tables in SQLite using GUID primary key columns. I am attempting to select a row based on its literal guid value in C#.Net. Here's a sample query: SELECTRole.Id, Role.Name, Role.Description, Role.Active, Role.BitMask FROMUserRole INNER JOIN Role ON UserRole.RoleId = Role.Id WHERE(UserRole.UserId = '{29831334-a434-4c06-a297-b58889f4d3c6}') I know the literal GUID value shown is correct, as I copied it directly from the results pane when I do a SELECT * FROM UserRole, however, as soon as I add the WHERE clause, I get no results. I know there is an option to set BinaryGuid=True/False, at the connection level, and I have done this both ways with the same results. Is there some other way to declare the literal guid value so that it will match? (I have tried without the { } pair as well, BTW). Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How does xBestIndex know that a LIKE query is case insensitive?
I have a virtual table implementation that implements the xBestIndex/xFilter functions. Problem: A LIKE query (for eg. field LIKE "prefix%") gets sets to xBestIndex as a GT/LT pair. However, I can't tell from the sqlite3_index_info whether that GT/LT should be NOCASE collation or BINARY collation. I want the default LIKE behaviour, which is NOCASE, but I can't figure out where to glean this information from inside xBestIndex. Am I missing something? Thanks, Ben ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users