Re: [sqlite] Stitching together Text Files into a New Database

2010-08-25 Thread Tim Romano
speed up queries. E.g. you might want an index on source if you frequently need to ask a question about the rows from the a particular campaign. There are a variety of GUI tools available for SQLite. The one I use most often is a plug-in for Firefox and is found here: http://code.google.com/p/sqlite-

Re: [sqlite] partial index?

2010-08-21 Thread Tim Romano
inclusion" test described in the paper that the partial index will not be used if the query itself does not contain the same set of conditions that were used to define the index. That makes the partial index safe, not the trouble I was envisioning. Regards Tim Romano On Fri, Aug 20, 2

Re: [sqlite] partial index?

2010-08-20 Thread Tim Romano
artial-index used under those circumstances? -- Tim Romano On Thu, Aug 19, 2010 at 9:16 PM, Igor Tandetnik wrote: > Tim Romano wrote: > > How would you find a row whose column X contained value Y if the > "partial" > > index on column X specified that rows cont

Re: [sqlite] partial index?

2010-08-19 Thread Tim Romano
Typo: "... more performant than partial query" should read "more performant than a partial index". Tim Romano > >> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] partial index?

2010-08-19 Thread Tim Romano
ial query: not only will the index contain just as few nodes, but the table itself will contain fewer rows than the table when using a partial index. And programming would not be more difficult: you'd simply substitute a trigger for the partial index declaration. Moreover, this technique would b

Re: [sqlite] partial index?

2010-08-19 Thread Tim Romano
The partial index is one very messy thing, fraught with ambiguities, something to avoid. I can imagine other business rules being really bollixed up by the sudden reappearance of zombie rows. Regards Tim Romano Swarthmore PA on the Gender column. On Thu, Aug 19, 2010 at 4:30 PM, Eric

Re: [sqlite] trigger or application code

2010-08-10 Thread Tim Romano
imit 1 The problem with this approach is that any [code] value under the sun is acceptable; there's no ITEMS table to prevent invalid codes via a foreign key declaration. Regards Tim Romano On Tue, Aug 10, 2010 at 1:20 PM, Igor Tandetnik wrote: > David Bicking wrote: > > I am b

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

2010-08-09 Thread Tim Romano
at contains a common letter is not helpful when the list of matches is a very long one. Regards Tim Romano Swarthmore PA On Fri, Aug 6, 2010 at 9:54 PM, Scott Hess wrote: > On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts wrote: > > On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess wrote: &

Re: [sqlite] How to write the trigger?

2010-08-03 Thread Tim Romano
as it's hot and humid here. Regards Tim Romano Swarthmore PA On Tue, Aug 3, 2010 at 9:10 AM, Black, Michael (IS) wrote: > You could've tested this in the time it took for you to get answer: > > > > From: sqlite-users-boun...@sqlite.org on behalf of Tim Romano > Sent: Tue 8

Re: [sqlite] How to write the trigger?

2010-08-03 Thread Tim Romano
Is a to-be-inserted row supposed to be included among the counted rows in a BEFORE INSERT trigger? Could you end up with 5 rows? What happens if you make this an AFTER trigger? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/

Re: [sqlite] How to write the trigger?

2010-08-03 Thread Tim Romano
o top-level UPDATE<http://www.sqlite.org/lang_update.html> andDELETE <http://www.sqlite.org/lang_delete.html> statements, not UPDATE<http://www.sqlite.org/lang_update.html> and DELETE <http://www.sqlite.org/lang_delete.html> statements within triggers." http://www.

Re: [sqlite] crypt() as SQL core function

2010-08-02 Thread Tim Romano
Whoa, Nellie. :-) I use the abs() function on full-text proximity queries for situations when the order of the words is not relevant. Don't be taking that function out. Regards Tim Romano Swarthmore PA On Mon, Aug 2, 2010 at 2:34 AM, Alexey Pechnikov wrote: > 2010/8/2 Simo

Re: [sqlite] PRAGMA database_list: insert into table?

2010-07-31 Thread Tim Romano
icode-savvy approach but not a request for the raw-reverse function, because one would have ready access to the naive solution via the UDF mechanism. Regards Tim Romano Swarthmore PA P.S. A raw-reverse function should probably be called FLIP() so as not to get bollixed up with any reverse() fu

Re: [sqlite] PRAGMA database_list: insert into table?

2010-07-30 Thread Tim Romano
e. I may decide to forget about it as a target platform and rewrite the app as a web-service, where I would indeed have access to the UDF mechanism. But there was merit in having the application work in offline mode too. Regards Tim Romano Swarthmore PA On Tue, Jul 27, 2010 at 12:10 PM, Roger Binn

Re: [sqlite] PRAGMA database_list: insert into table?

2010-07-27 Thread Tim Romano
the two stock replies: "you should do that in your application" and "you could do that in an extension". Regards Tim Romano Swarthmore PA On Mon, Jul 26, 2010 at 6:37 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/26/2010 12:

Re: [sqlite] PRAGMA database_list: insert into table?

2010-07-26 Thread Tim Romano
not going to let the developer load an extension either. That's what I've been trying to make clear. Regards Tim Romano Swarthmore PA On Mon, Jul 26, 2010 at 12:07 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/26/2010 06:03 AM, Tim Romano wr

Re: [sqlite] PRAGMA database_list: insert into table?

2010-07-26 Thread Tim Romano
statements? The other program (the "layer sitting in front of SQLite") does not first need to invoke sqlite3_enable_load_extension? Is the entry point to the extension being written to the database by sqlite3_auto_extension? Regards Tim Romano Swarthmore PA On Sun, Jul

Re: [sqlite] PRAGMA database_list: insert into table?

2010-07-25 Thread Tim Romano
e, but the essence of something does not always come out of its origins; destiny and destination can shape things too. Who could have known, way back when, that SQLite would influence the direction of the web? Regards Tim Romano Swarthmore PA On Sat, Jul 24, 2010 at 10:57 AM, Roger Binns

Re: [sqlite] PRAGMA database_list: insert into table?

2010-07-24 Thread Tim Romano
codepoints could give middleware the hiccups, and insisted that it this reversal be done "in the application". Regards Tim Romano Swarthmore PA On Fri, Jul 23, 2010 at 11:19 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > > Remember that SQLite is a library - it

Re: [sqlite] error in sum function

2010-07-15 Thread Tim Romano
done with CAST in a CHECK constraint. Using a CAST in the CHECK constraint can prevent the insertion of REALS into a column one has defined as INTEGER. It's possible to turn loose-loafer-wearing SQLite into a veritable buttoned-down wing-tipped data martinet. Regards Tim Romano Swarthmore PA

Re: [sqlite] error in sum function

2010-07-14 Thread Tim Romano
Ignore the typo: should be 2 | 2.2 As someone who tends to make typogarphical errors, I do like forums with post-editing capabilities much better than mailing lists. Regards Tim Romano Swarthmore PA ___ sqlite-users mailing list sqlite-users

Re: [sqlite] error in sum function

2010-07-14 Thread Tim Romano
lue with something other than zero(s) to the right of the decimal point. And you can convert that test into a check constraint to prevent non-integer values from being inserted into the table: CREATE TABLE "main"."PROD" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NU

Re: [sqlite] Query critique

2010-07-09 Thread Tim Romano
the FRIENDS table. Regards Tim Romano Swarthmore PA On Fri, Jul 9, 2010 at 5:52 AM, Benoit Mortgat wrote: > On Fri, Jul 9, 2010 at 11:08, Ian Hardingham wrote: > > Hey guys. > > > > I have a query which is very slow, and was wondering if there was any > >

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

2010-07-03 Thread Tim Romano
} Regards Tim Romano Swarthmore PA > > On 2 Jul 2010, at 5:15pm, Peng Yu wrote: > > I want to select an entry that appears > > <=n times and only show n times if it appears more than n times. I > > think that "group by" might help. > _

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

2010-07-01 Thread Tim Romano
Inasmuch as ALTER TABLE cannot (today) add a PK constraint to that tabular object, it will remain a table without a primary key. However, I do not know how that table-without-key would fare if the SQLite database file were queried by Adobe AIR. My guess is that one would need a bandage. Regards

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

2010-07-01 Thread Tim Romano
as SELECT... syntax ought not to produce a table with an INT primary key if the prototype had INTEGER. Regards Tim Romano Swarthmore PA On Wed, Jun 30, 2010 at 9:24 AM, David Bicking wrote: > > > All things considered, I would say this is clearly and 100% a bug in > Adobe's

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

2010-06-30 Thread Tim Romano
ver it may be) has been done. CONCLUSION: wherever the SQLite architects find opportunities to tighten up behaviors in this nexus, the tightening up effort would be well-spent, IMO. Which brings me back full circle to the subject line of this posting. Regards Tim Romano Swarthmore PA On Tue,

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

2010-06-30 Thread Tim Romano
Puneet, I am simply pointing out a potential pitfall. Putting up a highway sign that says "Soft Shoulder" is one way to go about things. Widening the shoulder and perhaps paving it is another. Regards Tim Romano Swarthmore PA On Tue, Jun 29, 2010 at 11:56 AM, P Kishor wrote: > O

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

2010-06-29 Thread Tim Romano
Tim Romano Swarthmore PA On Tue, Jun 29, 2010 at 11:18 AM, Pavel Ivanov wrote: > > I think > > SQLite implementations should probably adhere to a core spec but I > recognize > > this as my bias, not dogma. > > Probably this is my personal opinion but why should SQLite

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

2010-06-29 Thread Tim Romano
hat -- I didn't write that program or install the compatible terminal. But since then, whenever I see the opportunity for things going FUBAR, I will say something. Regards Tim Romano Swarthmore PA On Tue, Jun 29, 2010 at 11:10 AM, P Kishor wrote: > On Tue, Jun 29, 2010 at 9:58 AM,

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

2010-06-29 Thread Tim Romano
's PK value is 999) and Harpo will be returned by a join when the foreign key = 3 not when the foreign key = 999. Regards Tim Romano -- Most people assume one is wearing underwear. Not so for intelligence. On Tue, Jun 29, 2010 at 9:46 AM, Jay A. Kreibich wrote: > On Tue, Jun 29, 2010

[sqlite] create table {table-name} as select.... table definition is imperfectly cloned

2010-06-29 Thread Tim Romano
CREATE TABLE "main"."proto" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "name" TEXT) CREATE TABLE "main"."clone" as select * from PROTO The primary key of table CLONE is defin

Re: [sqlite] (python) how to define unchangeable global ID in a table?

2010-06-28 Thread Tim Romano
possibilities, it is not best practice to exchange autoincremented keys between systems but to use instead an alternate unique key that is guaranteed to remain constant. Regards Tim Romano Swarthmore PA On Mon, Jun 28, 2010 at 2:31 PM, Pavel Ivanov wrote: > > Such freedom is not suitab

Re: [sqlite] (python) how to define unchangeable global ID in a table?

2010-06-28 Thread Tim Romano
ing "position in a string in exchange protocol between 2 systems". Regards Tim Romano Swarthmore PA On Mon, Jun 28, 2010 at 10:23 AM, Pavel Ivanov wrote: > > the primary key column [id] is defined as INTEGER PRMARY KEY; so defined, > > SQLite will treat this column as an

Re: [sqlite] (python) how to define unchangeable global ID in a table?

2010-06-28 Thread Tim Romano
(i.e. an "alternate unique key"); a second column which *explicitly* acts in this manner will be clearer. Regards Tim Romano Swarthmore PA On Mon, Jun 28, 2010 at 8:43 AM, Tim Romano wrote: > And myspecialvalue can be INTEGER|TEXT. > > > On Mon, Jun 28, 2010 at 8:39 AM, Tim Ro

Re: [sqlite] (python) how to define unchangeable global ID in a table?

2010-06-28 Thread Tim Romano
And myspecialvalue can be INTEGER|TEXT. On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano wrote: > In this example: > > CREATE TABLE tableA { > > id INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT NOT NULL UNIQUE, > myspecialvalue

Re: [sqlite] (python) how to define unchangeable global ID in a table?

2010-06-28 Thread Tim Romano
guarantee that the associated value is both unique and remains unchanging. Of course you have to prevent edits to the associated value to enforce its immutability. Regards Tim Romano Swarthmore PA On Sat, Jun 26, 2010 at 11:34 AM, kee wrote: > Dear all > > I have 2 string lists,

Re: [sqlite] alternative to UNIQUE CONSTRAINT

2010-06-28 Thread Tim Romano
Could there be an issue with the character-encoding of the text column? Regards Tim Romano Swarthmore PA On Fri, Jun 25, 2010 at 12:35 PM, Oliver Peters wrote: > Igor Tandetnik writes: > > [...] > > > Isn't that exactly what you were asking for - a different syntax to &

Re: [sqlite] Question About SQLITE and AIR efficiency

2010-06-21 Thread Tim Romano
and their decision was to document it rather than to change their implementation. Regards Tim Romano Swarthmore PA On Sun, Jun 20, 2010 at 7:44 PM, Richard Hipp wrote: > On Sun, Jun 20, 2010 at 6:11 PM, Felipe Aramburu > wrote: > > > I have a query that I can execute in about 15

Re: [sqlite] database development - correct way?

2010-06-09 Thread Tim Romano
: the OrderHeader table would contain a single-column reference to CUSTOMER rather than two columns. Either approach is legitimate as far as RDBMS design is concerned; however some client-side application frameworks and middleware libraries do not support multi-column primary keys. Regards Tim

Re: [sqlite] database development - correct way?

2010-06-09 Thread Tim Romano
OrderDetail if you wanted to prevent the same article from appearing on more than one line-item of the order. Regards Tim Romano Swarthmore PA Regards Tim Romano . On Wed, Jun 9, 2010 at 9:09 AM, Oliver Peters wrote: > Hello, > > despite it's just a question about construction I ho

Re: [sqlite] Oracle connection

2010-06-03 Thread Tim Romano
http://tinyurl.com/29sk9pr On Thu, Jun 3, 2010 at 12:09 PM, Simon Hax wrote: > I don't know what JET is. > I testet ADO. That works. > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Oracle connection

2010-06-03 Thread Tim Romano
the first back-end out to the client, which then pushes the data out to the other back-end. http://my.safaribooksonline.com/0596004397/adonetckbk-CHP-3-SECT-6#X2ludGVybmFsX0ZsYXNoUmVhZGVyP3htbGlkPTAtNTk2LTAwNDM5LTcvMTM2 Regards Tim Romano On Thu, Jun 3, 2010 at 11:25 AM, Simon Hax wrote: >

Re: [sqlite] Aggregate and join query very slow

2010-05-28 Thread Tim Romano
How many distinct media-types are there? How many distinct facilities do you have? How many rows are typically returned by your FacilityScore subquery? SELECT facilitynumber,SUM(score_rev) AS score FROM release_cl WHERE media<3 AND year=2006 GROUP BY facilitynumber Regards Tim Rom

Re: [sqlite] select intersecting intervals

2010-05-13 Thread Tim Romano
en it intersects vectors of ids. You have a ready-made acronym to advertise the speed if it turns out to be faster: MPH. ;-) Regards Tim Romano Swarthmore PA On Wed, May 12, 2010 at 8:52 PM, Jean-Christophe Deschamps wrote: > > > > >I would first create an INTEGER primary key and

Re: [sqlite] select intersecting intervals

2010-05-12 Thread Tim Romano
. select * from T JOIN ( select pk_col from T where i_from > ? intersect select pk_col from T where i_to < ? ) as DESIREDINTERVAL ON T.pk_col = DESIREDINTERVAL.pk_col and T.name = ? Regards Tim Romano Swarthmore PA On Wed, May 12, 2010 at 12:00 PM, Jan Asselman wrote: > Hi, >

Re: [sqlite] join performance query

2010-05-11 Thread Tim Romano
Let's try that again : expose the [number] column to the outer selection (** are for emphasis**): ( select id_song, **number** from ( select id_song, **number** from PLAYLIST_SONG where id_playlist=2 {and|or } number > 258 ) as MYPLAYLISTSONGS Regards Tim Romano On Tue, May

Re: [sqlite] join performance query

2010-05-11 Thread Tim Romano
And you would put move your title-condition to the outer query: . . . ) as SONGIDLIST on SONG.id_song = SONGIDLIST.id_song where your title-condition and|or your title-number condition Regards Tim Romano ___ sqlite-users mailing list sqlite-users

Re: [sqlite] join performance query

2010-05-11 Thread Tim Romano
where id_playlist=2 {AND|OR } number > 258 ) as MYPLAYLISTSONGS Regards Tim Romano On Tue, May 11, 2010 at 12:39 PM, Tim Romano wrote: > You could remove the title condition from the inner SONGS select, limiting > your conditions to artist and genre; an index on column [artist] w

Re: [sqlite] join performance query

2010-05-11 Thread Tim Romano
je(karaoke version)'// --> moved to outer select > ) as MYSONGS The goal is to produce small inner subsets using indexes, and then to join these with each other, and to let the inner subsets expose the necessary columns to the outer query. Regards Tim Romano On Tue, May 11, 2010

Re: [sqlite] join performance query

2010-05-11 Thread Tim Romano
je(karaoke version)' ) as MYSONGS on MYSONGS.id_song = MYPLAYLISTSONGS.id_song ) as SONGIDLIST on SONG.id_song = SONGIDLIST.id_song Regards Tim Romano On Tue, May 11, 2010 at 6:07 AM, Andrea Galeazzi wrote: > Hi guys, > I'm in a bind for a huge time consuming query! >

Re: [sqlite] Select via Wi-fi very slow

2010-05-11 Thread Tim Romano
quals" substring searches. GLOB is case-sensitive. select * from products where description GLOB 'shirt*' Note the asterisk wildcard instead of the percent-symbol. Regards Tim Romano Swarthmore PA Regards Tim Romano On Tue, May 11, 2010 at 5:50 AM, Pavel Ivanov wrote:

Re: [sqlite] Should this work?

2010-05-10 Thread Tim Romano
. id | min(data_index) | max(data_index) However, it is not clear to me where you want to put that aggregated set. Do you have another *table* called SERIESID with those three columns in it? Regards Tim Romano Swarthmore PA On Mon, May 10, 2010 at 2:43 AM, Matt Young wrote: > # series d

Re: [sqlite] Should this work?

2010-05-06 Thread Tim Romano
elaboration: " ... you could this (to find the set to be inserted): " TR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should this work?

2010-05-06 Thread Tim Romano
s: select distinct col1, col2 from T1 where not exists ( select col1, col2 from T2 where T1.col1 = T2.col1 and T1.col2 = T2.col2 ) Regards Tim Romano Swarthmore PA On Thu, May 6, 2010 at 9:14 AM, Matt Young wrote: > Got it, thinks Jay. > > > On 5/6/10, Jay A. Kreibich wrote: &g

Re: [sqlite] Can I throw a query out to the group?

2010-05-03 Thread Tim Romano
o a_format, you can supply an alias for that column in the inner select, like this: select a_format from ( select table_id as a_format from table_id_list where prefix_code = 'MyPrefix_code' ); Regards Tim Romano Swarthmore PA On Sun, May 2, 2010 at 10:22 AM, Matt Young wrote

Re: [sqlite] Optimising usage of LIKE

2010-05-03 Thread Tim Romano
By "version" I meant "implementation". On Mon, May 3, 2010 at 7:25 AM, Tim Romano wrote: > Which version of SQLite are you using? If LIKE has been overridden in the > implementation you're using, it won't have the advantage of an index > whatever the coll

Re: [sqlite] Optimising usage of LIKE

2010-05-03 Thread Tim Romano
Which version of SQLite are you using? If LIKE has been overridden in the implementation you're using, it won't have the advantage of an index whatever the collation, in which case you might consider GLOB though it is case-sensitive. Regards

Re: [sqlite] scripting language interpreter

2010-05-02 Thread Tim Romano
Since Javascript has been mentioned: http://code.google.com/apis/v8/intro.html Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] scripting language interpreter

2010-05-02 Thread Tim Romano
@DRH : Thank you for the info on SQLite with TCL. Regards Tim Romano > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] scripting language interpreter

2010-05-02 Thread Tim Romano
Very cool, Jay. Regards Tim Romano On Sat, May 1, 2010 at 11:23 AM, Jay A. Kreibich wrote: > > > > I'd love to see a Lua extension. I know that's been proposed. Lua is > MIT licensed, small, fast, and reasonably mature. It is also designed > to be easily embe

Re: [sqlite] scripting language interpreter

2010-05-01 Thread Tim Romano
nt difference is that the scripting language can be "sandboxed" and prevented from referencing the file system. If support for an interprested scripting language were available, Google's and Adobe's and other similar implementations of SQLite could have access to the sort of po

[sqlite] scripting language interpreter

2010-05-01 Thread Tim Romano
(ala MS-Access->VBA) would be an amazingly powerful desktop tool. Do you know of any project pursuing such an integration? Regards Tim Romano ___ 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

2010-04-28 Thread Tim Romano
standard syntax. Hence, Adobe and Google et al don't have a LIKEU(). Tim Romano On Wed, Apr 28, 2010 at 10:09 AM, Jean-Christophe Deschamps wrote: > Tim, > > > I agree it is possible to overload LIKE and GLOB independantly but I > don't see a practical situation where

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

2010-04-28 Thread Tim Romano
are dissimilar. Regards Tim Romano On Mon, Apr 26, 2010 at 8:27 PM, Jean-Christophe Deschamps wrote: > 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 op

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

2010-04-26 Thread 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

2010-04-26 Thread Tim Romano
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

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

2010-04-26 Thread Tim Romano
nt 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

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

2010-04-26 Thread Tim Romano
icon 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 Si

Re: [sqlite] Data optimization with GLOB, virtual deletes

2010-04-17 Thread Tim Romano
was faster than separate indexes? Is it? Regards Tim Romano On Fri, Apr 16, 2010 at 8:17 AM, Mike Goins wrote: > Sorry, this may look a bit familiar. > > Table structure: > CREATE TABLE tb_file (tb_file_key INTEGER NOT NULL PRIMARY KEY > AUTOINCREMENT , basename TEXT, extension TEXT

Re: [sqlite] Index and GLOB

2010-04-11 Thread Tim Romano
Right, Igor. We can eliminate the middle-column issue : ... where basename GLOB 'a'// no index ... where basename GLOB 'a*' // index used Regards Tim Romano On Sun, Apr 11, 2010 at 8:43 AM, Igor Tandetnik wrote: > Tim Romano wrote: > > I believe the beh

Re: [sqlite] Index and GLOB

2010-04-11 Thread Tim Romano
;b' (column 'extension' is the middle column of the three-column composite index) you are running into a scenario analogous to .... where someColumn LIKE '%x%'. Regards Tim Romano On Fri, Apr 9, 2010 at 5:03 PM, Mike Goins wrote: > First thanks to all that have helped o

Re: [sqlite] Feature request: hash index

2010-04-05 Thread Tim Romano
IN( {inlist} ) syntax is not optimized, Alexey. http://www.mail-archive.com/sqlite-users@sqlite.org/msg49985.html Regards Tim Romano On Mon, Apr 5, 2010 at 3:22 PM, Alexey Pechnikov wrote: > Hello! > > On Monday 05 April 2010 22:22:40 Roger Binns wrote: > > Virtual tables

Re: [sqlite] Feature request: hash index

2010-04-05 Thread Tim Romano
parse hash would be more suitable than a btree for some lexicographical database applications. Regards Tim Romano On Mon, Apr 5, 2010 at 8:45 AM, Alexey Pechnikov wrote: > Hello! > > The b-tree index is not good choice for high-selective data, but there is > no > hash index in SQLit

Re: [sqlite] Problem with sqlite providing different programs different data

2010-04-01 Thread Tim Romano
ssage/2365982#2365982 Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with sqlite providing different programs different data

2010-04-01 Thread Tim Romano
of SQLite --Adobe is treating INT and INTEGER primary keys as if they were the same, taking both forms as an alias for the RowId, which in SQLite they are not. Regards Tim Romano On Thu, Apr 1, 2010 at 1:10 PM, Felipe Aramburu wrote: > I have some code that is using flex sdk 3.2 and I am

Re: [sqlite] Select * from tablename results in an exception

2010-03-30 Thread Tim Romano
http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/27aec612-5ca4-41ba-80d6-0204893fdcd1 Maybe related. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Preserving column size

2010-03-30 Thread Tim Romano
Sorry I misunderstood your requirement, Kevin. I was focusing on the singular "a table" and "the table" in your original post, versus the plural "many types of structs". I thought you were looking for serialization approach. Regards Tim Romano On Tue, Mar 30, 20

Re: [sqlite] Preserving column size

2010-03-30 Thread Tim Romano
tructid INTEGER (FK references STRUCT) membername TEXT memberdatatype TEXT membervalue TEXT casting 'membervalue' as appropriate during the reconstitution phase. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Preserving column size

2010-03-29 Thread Tim Romano
any types of structs used. > Couldn't you convert the structs to JSON format and store them in a TEXT field? There are a number of C++ JSON libraries listed here: http://www.json.org/ Regards Tim Romano ___ sqlite-users mailing list sqlite-u

Re: [sqlite] Preserving column size

2010-03-29 Thread Tim Romano
ere are many types of structs used. You could convert the structs to JSON format and store them in a TEXT field. There are a number of C++ JSON libraries listed here: http://www.json.org/ Regards Tim Romano P.S. Apologies if this reply comes through twice -- I resubscribed under a new email

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-22 Thread Tim Romano
a query that uses a single index followed by a looping read of the base table: select rowid from T where lat >= ? and lon <= ? and lat <= ? and lon >= ? Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http:/

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-22 Thread Tim Romano
On 3/22/2010 7:32 AM, Tim Romano wrote: > On 3/22/2010 2:15 AM, Max Vlasov wrote: > >>> Assuming a table where Latitude column and Longitude column each have >>> their own index: >>> >>> perform select #1 which returns the rowids of rows whose

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-22 Thread Tim Romano
he x and y values were not random values What is your performance with a query that uses only a single index without the INTERSECT function? select rowid from TT where x >= ? and y <= ? and and x <= ? and y >= ? Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-21 Thread Tim Romano
On 3/21/2010 5:22 PM, Max Vlasov wrote: > On Sun, Mar 21, 2010 at 3:50 PM, Tim Romano wrote: > > >> For someone who doesn't read C, could someone who knows please describe >> the SQLite INTERSECT algorithm? What optimizations are available to it? >> Does INTER

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-21 Thread Tim Romano
On 3/21/2010 10:26 AM, Igor Tandetnik wrote: > Tim Romano wrote: > >> For latitude/longitude queries >> > Without diving into the details of your situation, I wonder if you are aware > of R-Tree extension: > > http://www.sqlite.org/rtree.html > T

[sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-21 Thread Tim Romano
s of rowids is more expensive than using one index and reading the base table to get the other geo-value, either that, or the point at which INTERSECT becomes faster than INNER JOIN is well beyond the size of my test database. Regards Tim Romano ___ sqlit

Re: [sqlite] [sqlite-dev] Poor SQLite indexing performance

2010-03-16 Thread Tim Romano
unique index is being created/reorganized during batch population of the table. Regards Tim Romano On 3/15/2010 10:31 AM, Pavel Ivanov wrote: > > >> Is there any way to have a UNIQUE >> field but disable indexing till the end? >> > How do you expect your unique

Re: [sqlite] On conflicting Primary key how to insert record and increment the Key

2010-03-15 Thread Tim Romano
rogram" will let you create a view on TableB? create view MyView as select address, weight from TableB and then you could insert into TableA(address, weight) select address, weight from MyView Regards Tim Romano On 3/15/2010 9:32 AM, dravid11 wrote: > Well the situation is that i am merging

Re: [sqlite] On conflicting Primary key how to insert record and increment the Key

2010-03-15 Thread Tim Romano
If all you want to do is to insert a new row, do not mention the primary key column name in the insert statement: INSERT INTO temp (address, weight) values( "blah blah", 100) The autoincrementing primary key will be autoincremented. Regards Tim Romano On 3/15/2010 9:15 AM, drav

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Tim Romano
Foo was simply my shorthand for "another column, not column 'c' ", Sorry. The slanty lines are just drawing attention to the group-by clause, which was the subject of my post. Tim Romano ___ sqlite-users mailing list sqlite-us

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Tim Romano
On 3/9/2010 10:56 AM, Scott Hess wrote: > On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano wrote: > >> Of these three: >> >> select c from T where 1=2 // returns 0 rows >> select min(c) from T where 1=2 // returns 1 row >> select min(8

[sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Tim Romano
Wrapping a column in the min() function causes a query that returns no rows to return a row? select c from T where 1=2 // returns 0 rows select min(c) from T where 1=2 // returns 1 row select min(88,99) from T where 1=2 // returns 0 rows Tim Romano On 3/9/2010 4:15 AM

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Tim Romano
select min(askingprice) from cars4sale group by rowid //<= a group by is required here? Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Two columns in one index, or one column for each index?

2010-02-17 Thread Tim Romano
d from MYTABLE where (lon >=-80 and lon <= -55) ) as IDLIST on IDLIST.id = MYTABLE.id Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Hash keys

2010-02-16 Thread Tim Romano
ss those issues. Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what are the limitations for IN() lists?

2010-01-29 Thread Tim Romano
, but each of the million items would be looked for in the b-tree? Regards Tim Romano On 1/28/2010 12:26 PM, Dan Kennedy wrote: > On Jan 28, 2010, at 10:26 PM, Tim Romano wrote: > > >> Thanks for this clarification. >> >> Wouldn't SQLite simply rewrite m

Re: [sqlite] what are the limitations for IN() lists?

2010-01-28 Thread Tim Romano
don't want to recompile the same insert statement again and again and again and would use a parameter to avoid that problem. Regards Tim Romano On 1/27/2010 11:30 AM, Simon Slavin wrote: > > mm. A couple of things worth considering: first that JavaScript under HTML5 > has its ow

Re: [sqlite] what are the limitations for IN() lists?

2010-01-28 Thread Tim Romano
Thanks for this clarification. Wouldn't SQLite simply rewrite my IN-list query, transparently, as an equijoin against a transient table that has been populated with the values in the IN-list? I don't understand why the IN-list should have to be avoided. Thanks Tim Romano On 1/

Re: [sqlite] what are the limitations for IN() lists?

2010-01-27 Thread Tim Romano
loses the database connection 7) sends the results to the browser-agent At what point does step #3) in the top IN-list approach become more expensive than steps 2a-2e and 5a-5b in the bottom in-memory approach? Regards Tim Romano ___ sqlite-

  1   2   >