Re: [sqlite] Trouble with constraints and triggers

[Josh Gibbs]

On 23/09/2010 11:52 p.m., Richard Hipp wrote:
>> 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.
>
> 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

[Virgilio Fornazin]

They are still using 16-bits offsets... arghh!

On Thu, Sep 23, 2010 at 14:10, Shane Harrelson  wrote:

> 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

[Shane Harrelson]

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] Potential Bug, Select appears to hang

[Tilghman, Jack]

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

[Ben Harper]

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

Re: [sqlite] How does xBestIndex know that a LIKE query is case insensitive?

[Ben Harper]

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 Harper  wrote:

> 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

[dmp]

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?

[Simon Slavin]

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?

[Andy Gibbs]

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?

[Simon Slavin]

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

[Virgilio Fornazin]

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

Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build

[Ben Harper]

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

[Teg]

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

[Ben Harper]

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?

[Andy Gibbs]

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

[Richard Hipp]

On Wed, Sep 22, 2010 at 6:56 PM, Walter Meinl  wrote:

> 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

[Pavel Ivanov]

> 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?

[Richard Hipp]

On Thu, Sep 23, 2010 at 7:16 AM, Ben Harper  wrote:

> 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

[Igor Tandetnik]

Josh Gibbs  wrote:
>  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

[Richard Hipp]

On Wed, Sep 22, 2010 at 11:15 PM, Igor Tandetnik wrote:

> 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

[Walter Meinl]

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

[bill]

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?

[Ben Harper]

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