Re: [sqlite] Searching with like for a specific start letter
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?
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?
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?
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
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
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
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
@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 the .mode command, but if it does it's simpler yet. sqlite> .mode insert sqlite> select * from Grou
Re: [sqlite] Searching with like for a specific start letter
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
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
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
> 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
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
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
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
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
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
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
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
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
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
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
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