Re: [sqlite] Searching with like for a specific start letter

[Jean-Christophe Deschamps]

Tim,

>Queries using GLOB do use the index on the column in question (i.e.
>optimization is attempted)
>Queries using LIKE do not use that index if the LIKE operator has been
>overridden.

Sorry but GLOB doesn't use an index either if LIKE/GLOB has been 
overloaded.  This is consistent with the docs and the output of Explain 
query plan for both variants when an extension is active and overloads 
LIKE/GLOB.

Things can be different with a custom built of SQLite, where native 
LIKE/GLOB itself has been modified.  With custom code, all bets are off.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Install multiple versions of Sqlite?

[Simon Slavin]

On 26 Apr 2010, at 9:30pm, Forrie wrote:

> We are using Redhat, which tends to lag behind in some versioning --
> we want to use the latest version of Subversion 1.6.x, which in turn
> requires a newer version of Sqlite -- RHEL has an older version
> present, which in turn is a dependency for a slew of other subsystems
> (so uninstalling it would be disastrous).
> 
> My question is essentially whether multiple versions of Sqlite can be
> safely installed on the system without creating a conflict.  I'm going
> to "guess" that I can, especially if the newer lib is under /usr/local
> and I point the ./configure process to it.

You don't really 'install' SQLite on a computer.  Because it's available as 
source, you can compile it into any number of applications you like, install as 
many (shared) libraries that include it as you like, and leae as many copies of 
it on your hard disk as you want.  So the answer to your question is that it's 
fine: put one copy in /usr/local, another in /libs, and another in /bin.  As 
long as each of your programs only uses a version of SQLite it's happy with, 
you're fine.

The trick -- if it is a trick -- is to know which one of these copies/libraries 
each of your apps is using.  And that can be complicated with RedHat because of 
its (in your excellent word) slew of dependencies.  If you know Redhat well 
enough to put the right thing in each of your configuration files, go to it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Install multiple versions of Sqlite?

[Paul Rigor (uci)]

That sounds about right, but you should ensure that LD_LIBRARY_PATH is set
to reflect the appropriate sqlite lib folder each time you run something
that requires this, eg, your latest subversion install.

Goodluck,
Paul

On Mon, Apr 26, 2010 at 1:30 PM, Forrie  wrote:

> We are using Redhat, which tends to lag behind in some versioning --
> we want to use the latest version of Subversion 1.6.x, which in turn
> requires a newer version of Sqlite -- RHEL has an older version
> present, which in turn is a dependency for a slew of other subsystems
> (so uninstalling it would be disastrous).
>
> My question is essentially whether multiple versions of Sqlite can be
> safely installed on the system without creating a conflict.  I'm going
> to "guess" that I can, especially if the newer lib is under /usr/local
> and I point the ./configure process to it.
>
> Anyone have experience with this?
>
>
> Thanks.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Paul Rigor
Pre-doctoral BIT Fellow and Graduate Student
Institute for Genomics and Bioinformatics
Donald Bren School of Information and Computer Sciences
University of California, Irvine
http://www.ics.uci.edu/~prigor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Install multiple versions of Sqlite?

[Forrie]

We are using Redhat, which tends to lag behind in some versioning --
we want to use the latest version of Subversion 1.6.x, which in turn
requires a newer version of Sqlite -- RHEL has an older version
present, which in turn is a dependency for a slew of other subsystems
(so uninstalling it would be disastrous).

My question is essentially whether multiple versions of Sqlite can be
safely installed on the system without creating a conflict.  I'm going
to "guess" that I can, especially if the newer lib is under /usr/local
and I point the ./configure process to it.

Anyone have experience with this?


Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REQUEST: Restriction of updating unique column

[Makoto SAITO]

thank you for valuable comments.

> At least, there should be a pragma or clause similar to the "deferred"
> clause available for foreign keys, causing uniqueness/existence/custom
> constraints to be checked only after FK (=trigger) action.  With such
> feature available, we wouldn't have to sacrifice integrity constraints
> on critical columns.

I hope the feature too.

Could sqlite developers plan to implement it?

2010/4/19 Jean-Christophe Deschamps :
>
>>I found the the restriction of updating unique column in ver3.6.21.
>>and same problem is reported in follwoing mail
>>
>>Marc-Andre Gosselin wrote:
>>date: Thu, 16 Jun 2005
>>title: "[sqlite] Update unique column"
>> >
>> > I discovered a behavior in SQLite 2.8.16 that doesn't conform to
>> the SQL
>> > standard, here's an example :
>> >
>> > CREATE TABLE tbUpdateUnique (a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c
>> > VARCHAR(100));
>> > INSERT INTO tbUpdateUnique VALUES('', 1, "Test 1");
>> > INSERT INTO tbUpdateUnique VALUES('', 2, "Test 2");
>> > INSERT INTO tbUpdateUnique VALUES('', 3, "Test 3");
>> >
>> > Now when I try the following update, I get a constraint error :
>> >
>> > UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;
>> >
>> > In the SQL Standard and NIST SQL test suite they say than an update
>> > should be considered atomic, and verify unique constraints only
>> after the operation has
>> > updated all rows. From what I experienced with SQLite, constraints
>> are verified
>> > after each row has been updated, resulting in a constraint error. I
>> also tried
>> > these with no success :
>> >
>> > BEGIN TRANSACTION;
>> > UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;
>> > COMMIT TRANSACTION;
>> >
>> > and
>> >
>> > UPDATE tbUpdateUnique SET b = b + 1 WHERE a IN (SELECT a FROM
>> tbUpdateUnique
>> > WHERE b >= 2 ORDER BY b DESC);
>> >
>>
>># full mail can be seen in archieve log at
>># http://www.mail-archive.com/sqlite-users@sqlite.org/msg08597.html
>>
>>Will this restriction be fixed in near feature?
>>
>>I also know the workaround to use temporary value and update twice.
>>(example is shown is
>>http://www.mail-archive.com/sqlite-users@sqlite.org/msg50894.html)
>>but I don't want to use this workaround if possible
>>because it is not gut feeling and less performance.
>
> I also have been in the situation where a unique constraint gets raised
> early: while inserting items in a nested tree (using integral LO-HI
> intervals) you have to completely abandon unique constraints on the LO
> and HI keys.  Contrary to what occurs in other engines, SQLite seems to
> assert constraints at insert/delete time, ignoring the fact that
> (insert or delete) trigger will increment or decrement the upper part
> of the tree interval on HI and LO keys (in the case of a nested tree).
>
>
> ___
> 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] Searching with like for a specific start letter

[Alberto Simões]

Just to complete the thread, I decided for the following:

SELECT substr(normalized,1,1) AS letter, COUNT(*) from entry group by
letter order by letter;

Thank you ALL!

2010/4/26 Alberto Simões :
> Hello
>
> Thank you all for the answers.
>
> On Mon, Apr 26, 2010 at 12:59 PM, Black, Michael (IS)
>  wrote:
>> When you say "running on the fly" do you mean running from an sqlite3 
>> command prompt?
>
> I mean somebody will query it and will be waiting for the answer.
>
>> Or are you doing this in some other programming language?
>
> Perl
>
>> Why in the world would you use a database to do this?
>
> Probably I am not using a database for this, but for something else,
> and I want to add a feature to let users read some statistics.
>
>>
>>
>> Hello
>>
>> I am running on the fly a query to count the number of words starting
>> with one of the 26 letters.
>>
>> I am doing the usual SELECT COUNT(term) from dictionary WHERE normword
>> LIKE "a%"  (for the 26 letters)
>>
>> normword is the term normalized without accents and the like
>>
>>
>> Is there any way to make this query faster? It is taking about 10
>> second for 140K entries.
>>
>> One idea is to add a column named 'letter' and SELECT COUNT(letter)
>> from dictionary WHERE letter = 'a'.
>> But are there other solutions?
>>
>> Thanks
>> --
>> Alberto Simões
>> ___
>> 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
>>
>>
>
>
>
> --
> Alberto Simões
>



-- 
Alberto Simões
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching with like for a specific start letter

[Alberto Simões]

Hello

Thank you all for the answers.

On Mon, Apr 26, 2010 at 12:59 PM, Black, Michael (IS)
 wrote:
> When you say "running on the fly" do you mean running from an sqlite3 command 
> prompt?

I mean somebody will query it and will be waiting for the answer.

> Or are you doing this in some other programming language?

Perl

> Why in the world would you use a database to do this?

Probably I am not using a database for this, but for something else,
and I want to add a feature to let users read some statistics.

>
>
> Hello
>
> I am running on the fly a query to count the number of words starting
> with one of the 26 letters.
>
> I am doing the usual SELECT COUNT(term) from dictionary WHERE normword
> LIKE "a%"  (for the 26 letters)
>
> normword is the term normalized without accents and the like
>
>
> Is there any way to make this query faster? It is taking about 10
> second for 140K entries.
>
> One idea is to add a column named 'letter' and SELECT COUNT(letter)
> from dictionary WHERE letter = 'a'.
> But are there other solutions?
>
> Thanks
> --
> Alberto Simões
> ___
> 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
>
>



-- 
Alberto Simões
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 28, Issue 26

[Bernie Reiter]

@jason

I'd like to follow-up Igor's and Michael's suggestion (see their referred-to 
messages below).

How about that:
a) You do a dump of your database table containing the values bob, 
jean-batiste, ...
a1) select a file for your dump:
sqlite> .output FILENAME
a2) Dump the database in an SQL text format
 If TABLE specified, only dump tables matching
 LIKE pattern TABLE.
sqlite> .dump ?TABLE? ...  
sqlite> .quit

I presume that you are working on a *NIX/Linux box.

Testwise grep the line containing 'bob' or any of the other working values in 
your generated dump file
$ grep --version
GNU grep 2.6.3

$ grep -i bob mydump-file.txt

Now grep using 'jean' or any other of your values exluding the dash in 
question. e.g. 'jean' only for  'jean-batiste'
$ grep -i jean mydump-file.txt

DO NOT use an editor to extract the line containing the value of e.g. 
'jean-batiste' as some editors are changing values when saving files. Better 
use grep instead.

If this is working then check your system for either the "hexdump" or the "od" 
(for octal dump) program, e.g.
$ echo a- | hexdump
000 2d61 000a  
003

$ echo a- | od -x
000 2d61 000a
003
In both cases the hex value of the letter 'a' and my dash character '-' was 
shown as 2d61.

$ echo aa | od -x
000 6161 000a
will show you '61' as the hex value of 'a'

$ N.B.: 2 dashes used here in this example, i.e., like "echo dashdash":
echo -- | od -x   
000 2d2d 000a
will show you '2d' as the hex value of '-'

Now look at the man page to work out the offset so that you start "dumping" 
with the last letter before your dash, e.g. n-batiste ... This way you can 
extract the value of your dash from your generated dump file.


If you are not working on a *NIX/Linux box then you might 
a) download grep as part of the GNU text-utils
or
b) download Fedora12 Live and boot it from USB-stick

Otherwise use your generated dump file and upload it using the Online HexDump 
Utility web site at
http://www.fileformat.info/tool/hexdump.htm


Finally use, e.g. 
the Decimal/Hex/Binary/Octal Calculator at 
http://www.indianabiomedical.com/Calculator/hex.html 
or
Online Unit Converters. Common Converters. Numbers Converter
at http://www.translatorscafe.com/cafe/units-converter/numbers/c/


and take it from there.

I did something like that while parsing for German Umlaute in html with awk to 
generate ASCII records for importing into SQLite - rather a RPitA (German 
Umlaute, not awk or SQLite ... :-) 

Cheerio

bernie


--

Message: 16
Date: Sun, 25 Apr 2010 23:28:06 -0400
From: "Igor Tandetnik" 
Subject: Re: [sqlite] values containing dash - not evaluated
To: sqlite-users@sqlite.org
Message-ID: 
Content-Type: text/plain;charset="iso-8859-1"

jason d wrote:
> I believe you misunderstood my problem. Its not that records dont exist. and
> select statement for Bob does work. a select * does display all the data.
> its the names with dashes that dont shows up. and i have 40,000 records.
> any with dashes do not give any result on a pure select statement. but if I
> select on any other column and then work on the resultset it is ok. for
> example I may choose column projectname since it does not have a dash (-) in
> it. The information is clearly there, just its as if it does not equate to
> anything at all.
> 
> SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.

What does this statement return:

select name, hex(name) from Groups
where name like '%jean%';

My guess is, you either have leading and/or trailing whitespace around the 
value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some other 
Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex dump 
would tell.
-- 
Igor Tandetnik

--

Message: 31
Date: Mon, 26 Apr 2010 06:59:24 -0500
From: "Black, Michael (IS)" 
Subject: Re: [sqlite] values containing dash - not evaluated
To: "General Discussion of SQLite Database" 
Message-ID:
<9e0073162fd25b489a01ad86d92d983e032ed...@xmbil132.northgrum.com>
Content-Type: text/plain; charset="iso-8859-1"

First off confirm it's not a bug with sqlite2:

sqlite> create table Groups (name varchar(10));
sqlite> insert into Groups values('bob');
sqlite> insert into Groups values('jean-baptiste');
sqlite> select * from Groups where name='jean-baptiste';
jean-baptiste

If you don't get a results this way tje sqlite2 is the problem (which I doubt).

Then do an sql .dump of your table.
sqlite> .dump Groups
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Groups (name varchar(10));
INSERT INTO "Groups" VALUES('bob');
INSERT INTO "Groups" VALUES('jean-baptiste');
COMMIT;

Then you should be able to see the SQL representation of the string and perhaps 
see what your problem is.

I don't know if sqlite2 has 

Re: [sqlite] Searching with like for a specific start letter

[Tim Romano]

Edit: I meant to type "Firefox" not Firebird.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching with like for a specific start letter

[Tim Romano]

I am not quite sure what it is, and why it is, that you are doubting,
Jean-Christophe.

Queries using GLOB do use the index on the column in question (i.e.
optimization is attempted)
Queries using LIKE do not use that index if the LIKE operator has been
overridden.

You could confirm this claim from the docs and/or by using EXPLAIN QUERY
PLAN, assuming you had access to a version of SQLite where the LIKE operator
has been overridden (as it has been in Adobe AIR, Firebird, and in
the version that ships with the  System.Data.SQLite .NET provider as well,
IIRC.


Regards
Tim Romano


On Mon, Apr 26, 2010 at 9:24 AM, Jean-Christophe Deschamps 
wrote:

> At 14:31 26/04/2010, you wrote:
>
> >If the implementation of SQLite you are using overrides the LIKE operator
> >(as more than a few do), then SQLite will not make use of an index on the
> >column in question. Use the GLOB operator instead.
>
> I doubt it.  GLOB is absolutely nothing more or less than an invokation
> of the same code for LIKE but with slightly different
> parameters.  Except if people have made a completely differing version,
> departing from the architecture of the standard SQLite code (and there
> is little reason to, if any) AND have made LIKE and GLOB two completely
> distinct functions, there shouldn't be any significant difference in
> running time (for equivalent queries, of course).
>
> Also if ever LIKE is overloaded, then GLOB gets excluded from standard
> optimization, except large changes in SQLite code.
>
> ___
> 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] Inserting from other table

[sabapathy]

Yes currently it depends on the order. Should be checking if it can be
avoided.

The issue has been resolved by the method you've suggested (INSERT INTO
new_table (col_1, col_3)
SELECT col_1, col_2 FROM old_table;)

Thanks to everyone!!

I am looking for how to get total no.of columns even if the table is empty..

Thanks,
Sabapathy


Pavel Ivanov-2 wrote:
> 
>> But I came to know that new columns can be added only at the end.
>> So I have to create an empty table in new format and insert the records
>> from
>> old table & delete the old table(?).
> 
> Are you sure that your software tool depends on the order of columns
> in the database? Even if new version creates necessary table with new
> columns in the middle it could be written in the way that it doesn't
> matter what's the actual order of columns. And if it really depends on
> the order of columns then I'd say it's poorly written application and
> it can be filed as bug report. Especially if it doesn't support
> automatic upgrade from old database format.
> 
>> How can I do this efficiently even if there are tens of thousands of
>> records..?
> 
> I believe the only quick and efficient way to do that is INSERT INTO
> ... SELECT ...
> 
>> Can I provide mapping of columns to insert from one table to another?
>> (For eg, if the old table has 2 columns & new one 3 columns, I want to
>> insert 1st column to 1st column, 2nd to 3rd and leave 2nd column empty in
>> new table)
> 
> INSERT INTO new_table (col_1, col_3)
> SELECT col_1, col_2 FROM old_table;
> 
> 
> Pavel
> 
> On Mon, Apr 19, 2010 at 1:47 PM, sabapathy 
> wrote:
>>
>> The DB had some 15 columns before.
>> And there are lot of records saved using the s/w tool.
>> But in the latest version of tool there are some columns added in DB
>> inbetween of existing columns.
>> So to use the DB saved using previous version of tool, I need to add some
>> columns(blank) in between in the old DB.
>> But I came to know that new columns can be added only at the end.
>> So I have to create an empty table in new format and insert the records
>> from
>> old table & delete the old table(?).
>> How can I do this efficiently even if there are tens of thousands of
>> records..?
>>
>> Can I provide mapping of columns to insert from one table to another?
>> (For eg, if the old table has 2 columns & new one 3 columns, I want to
>> insert 1st column to 1st column, 2nd to 3rd and leave 2nd column empty in
>> new table)
>>
>> Thanks..
>> --
>> View this message in context:
>> http://old.nabble.com/Inserting-from-other-table-tp28287723p28287723.html
>> Sent from the SQLite mailing list archive at Nabble.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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Inserting-from-other-table-tp28287723p28367790.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Licensing of SQLIte

[Lynn Fredricks]

> I just have some doubts on the licensing issues of SQLite.
> 
>1. Being open-source, is my company liable to post back
>   changes/modifications to
>   SQLite? What are the licensing terms & conditions?
>2. As our present development is on a Linux variant, are there any
>   present
>   feature/functionality that need to be posted back?

You cannot get any freer than the public domain license of SQLite. Many
companies have taken advantage of this to produce their own products based
on the source code.

Best regards,

Lynn Fredricks
President
Paradigma Software
http://www.paradigmasoft.com

Valentina SQL Server: The Ultra-fast, Royalty Free Database Server 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive triggers

[Andy Gibbs]

From: "Simon Slavin"
Sent: Monday, April 26, 2010 2:31 PM
> I don't know the answer to this question, but I have considered it in one 
> of my
> programs.  It simply issues a "PRAGMA recursive_triggers = 'on'", then 
> does a
> "PRAGMA recursive_triggers" and looks to see what it gets back.  Anything 
> but
> a '1' indicates too early a version of SQLite, so it triggers an error 
> message and
> a quit.

That is a good general solution.

Thanks.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching with like for a specific start letter

[Jean-Christophe Deschamps]

At 14:31 26/04/2010, you wrote:

>If the implementation of SQLite you are using overrides the LIKE operator
>(as more than a few do), then SQLite will not make use of an index on the
>column in question. Use the GLOB operator instead.

I doubt it.  GLOB is absolutely nothing more or less than an invokation 
of the same code for LIKE but with slightly different 
parameters.  Except if people have made a completely differing version, 
departing from the architecture of the standard SQLite code (and there 
is little reason to, if any) AND have made LIKE and GLOB two completely 
distinct functions, there shouldn't be any significant difference in 
running time (for equivalent queries, of course).

Also if ever LIKE is overloaded, then GLOB gets excluded from standard 
optimization, except large changes in SQLite code.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching with like for a specific start letter

[Tim Romano]

Yes. If the OP's [normword] column contains proper nouns, he must normalize
to lower case in order to get accurate results from GLOB.
Or, if his lexicon contains proper nouns in upper case and normal nouns in
lower case, then he could always leave the case intact and use GLOB to get a
count of proper nouns versus normal nouns ;-)

... GLOB 'A*'
... GLOB 'a*'

Regards
Tim Romano




On Mon, Apr 26, 2010 at 8:47 AM, Igor Tandetnik  wrote:

> Tim Romano wrote:
> > If the implementation of SQLite you are using overrides the LIKE operator
> > (as more than a few do), then SQLite will not make use of an index on the
> > column in question. Use the GLOB operator instead.
> >
> > For example, I have a lexicon containing 263,000 words:
> >
> > select count(*) from lexicon where spelling like 'a%'   // 552 ms on
> first
> > run and then 355ms on second and subsequent runs
> > select count(*) from lexicon where spelling glob 'a*'  // 110 ms on first
> > run and then ~10ms on second and subsequent runs
>
> Note that, by default, LIKE is case-insensitive while GLOB is
> case-sensitive. Thus, even if not overridden with a custom function, LIKE
> cannot be optimized unless the column is declared with NOCASE collation.
> Again, this article provides all the details:
> http://sqlite.org/optoverview.html#like_opt
> --
> Igor Tandetnik
>
> ___
> 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] Searching with like for a specific start letter

[Igor Tandetnik]

Tim Romano wrote:
> If the implementation of SQLite you are using overrides the LIKE operator
> (as more than a few do), then SQLite will not make use of an index on the
> column in question. Use the GLOB operator instead.
> 
> For example, I have a lexicon containing 263,000 words:
> 
> select count(*) from lexicon where spelling like 'a%'   // 552 ms on first
> run and then 355ms on second and subsequent runs
> select count(*) from lexicon where spelling glob 'a*'  // 110 ms on first
> run and then ~10ms on second and subsequent runs

Note that, by default, LIKE is case-insensitive while GLOB is case-sensitive. 
Thus, even if not overridden with a custom function, LIKE cannot be optimized 
unless the column is declared with NOCASE collation. Again, this article 
provides all the details: http://sqlite.org/optoverview.html#like_opt
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive triggers

[Simon Slavin]

On 26 Apr 2010, at 11:09am, Andy Gibbs wrote:

> I notice in the fossil repository that Sqlite is now moving towards version 
> 3.7.0.  According to the pagehttp://www.sqlite.org/news.html#2009_sep_11, it 
> is anticipated that recursive triggers will be enabled by default from 
> version 3.7.0 (cf also http://www.sqlite.org/limits.html#max_trigger_depth).  
> Please can I simply enquire whether this is still to be expected?

I don't know the answer to this question, but I have considered it in one of my 
programs.  It simply issues a "PRAGMA recursive_triggers = 'on'", then does a 
"PRAGMA recursive_triggers" and looks to see what it gets back.  Anything but a 
'1' indicates too early a version of SQLite, so it triggers an error message 
and a quit.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching with like for a specific start letter

[Tim Romano]

If the implementation of SQLite you are using overrides the LIKE operator
(as more than a few do), then SQLite will not make use of an index on the
column in question. Use the GLOB operator instead.

For example, I have a lexicon containing 263,000 words:

select count(*) from lexicon where spelling like 'a%'   // 552 ms on first
run and then 355ms on second and subsequent runs
select count(*) from lexicon where spelling glob 'a*'  // 110 ms on first
run and then ~10ms on second and subsequent runs


Alternatively:

select substr(spelling,1,1), count (*) from  lexicon
group by substr(spelling,1,1)
order by  substr(spelling,1,1)

// ~3500 ms on first run and then ~2400 ms on second and subsequent runs


Of course, if your lexicon is static, you could create an ancillary table of
first letters and their corresponding counts.

Regards
Tim Romano

2010/4/25 Alberto Simões 

> Hello
>
> I am running on the fly a query to count the number of words starting
> with one of the 26 letters.
>
> I am doing the usual SELECT COUNT(term) from dictionary WHERE normword
> LIKE "a%"  (for the 26 letters)
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values containing dash - not evaluated

[Simon Slavin]

On 26 Apr 2010, at 1:04pm, Michal Seliga wrote:

> i had similar problems and it was caused by microsoft office
> it didn't used ordinary dash but some strange character with different
> ascii code - so search based on it always failed
> i had to convert these strange dashes to ordinary ones to make it work
> try, maybe this is also your case

Good catch.  MS Office, under some circumstances, automatically replaces the 
'-' (minus sign) character with a hyphen ('‐').  Technically the hyphen is the 
right character to use to join two words, but since it doesn't have an easy 
key-combination many people don't type it and like the automatic conversion 
that Office does.  Annoyingly neither of these are actually dashes: there are 
n-dash ('–') and m-dash ('—') characters too.  So there are four characters 
that all look similar but do not have the same hash value in normal text 
processing.

Simon.

PS: Don't get me started on figure-dashes and graphical horizontal lines.  
Unicode should not include graphical icons.  Bah humbug.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Recursive triggers

[Andy Gibbs]

Hi,

I notice in the fossil repository that Sqlite is now moving towards version 
3.7.0.  According to the page http://www.sqlite.org/news.html#2009_sep_11, it 
is anticipated that recursive triggers will be enabled by default from version 
3.7.0 (cf also http://www.sqlite.org/limits.html#max_trigger_depth).  Please 
can I simply enquire whether this is still to be expected?

Many thanks
Andy

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values containing dash - not evaluated

[Michal Seliga]

i had similar problems and it was caused by microsoft office
it didn't used ordinary dash but some strange character with different
ascii code - so search based on it always failed
i had to convert these strange dashes to ordinary ones to make it work
try, maybe this is also your case


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching with like for a specific start letter

[Black, Michael (IS)]

When you say "running on the fly" do you mean running from an sqlite3 command 
prompt?
 
Or are you doing this in some other programming language?
 
Why in the world would you use a database to do this?
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Alberto Simões
Sent: Sun 4/25/2010 3:39 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Searching with like for a specific start letter



Hello

I am running on the fly a query to count the number of words starting
with one of the 26 letters.

I am doing the usual SELECT COUNT(term) from dictionary WHERE normword
LIKE "a%"  (for the 26 letters)

normword is the term normalized without accents and the like


Is there any way to make this query faster? It is taking about 10
second for 140K entries.

One idea is to add a column named 'letter' and SELECT COUNT(letter)
from dictionary WHERE letter = 'a'.
But are there other solutions?

Thanks
--
Alberto Simões
___
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] values containing dash - not evaluated

[Black, Michael (IS)]

First off confirm it's not a bug with sqlite2:
 
sqlite> create table Groups (name varchar(10));
sqlite> insert into Groups values('bob');
sqlite> insert into Groups values('jean-baptiste');
sqlite> select * from Groups where name='jean-baptiste';
jean-baptiste
 
If you don't get a results this way tje sqlite2 is the problem (which I doubt).
 
Then do an sql .dump of your table.
sqlite> .dump Groups
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Groups (name varchar(10));
INSERT INTO "Groups" VALUES('bob');
INSERT INTO "Groups" VALUES('jean-baptiste');
COMMIT;
 
Then you should be able to see the SQL representation of the string and perhaps 
see what your problem is.
 
I don't know if sqlite2 has the .mode command, but if it does it's simpler yet.
 
sqlite> .mode insert
sqlite> select * from Groups where name like('%jean%');
INSERT INTO table VALUES('jean-baptiste');
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Igor Tandetnik
Sent: Sun 4/25/2010 10:28 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] values containing dash - not evaluated



jason d wrote:
> I believe you misunderstood my problem. Its not that records dont exist. and
> select statement for Bob does work. a select * does display all the data.
> its the names with dashes that dont shows up. and i have 40,000 records.
> any with dashes do not give any result on a pure select statement. but if I
> select on any other column and then work on the resultset it is ok. for
> example I may choose column projectname since it does not have a dash (-) in
> it. The information is clearly there, just its as if it does not equate to
> anything at all.
>
> SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.

What does this statement return:

select name, hex(name) from Groups
where name like '%jean%';

My guess is, you either have leading and/or trailing whitespace around the 
value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some other 
Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex dump 
would tell.
--
Igor Tandetnik

___
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