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

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-manager/ Regards Tim Romano Swarthmore

Re: [sqlite] partial index?

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?

ex used under those circumstances? -- Tim Romano On Thu, Aug 19, 2010 at 9:16 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > Tim Romano <tim.romano...@gmail.com> wrote: > > How would you find a row whose column X contained value Y if the > "partial" > &g

Re: [sqlite] partial index?

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?

ust 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 be highly portable. Partial indexes, not. Regards

Re: [sqlite] partial index?

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

Re: [sqlite] trigger or application code

he 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 <itandet...@mvps.org> wrote: > David Bicking <dbic...@yaho

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

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 <sh...@google.com> wrote: > On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts <vieuxt...@gmail.com> wrote: > > On Fri, Aug 6, 2010 at

Re: [sqlite] How to write the trigger?

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

Re: [sqlite] How to write the trigger?

-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.sqlit

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

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 <pechni...@mobigroup.ru>wrote:

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

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() function in

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

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 Binns <r

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

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 <rog...@rogerbinns.com> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1

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

ing 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 <rog...@rogerbinns.com> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/26/2010 06:03 AM, Tim

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

s? 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 25, 201

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

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 <rog...@rogerbi

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

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 <rog...@rogerbinns.com> wrote: > -BEGIN PGP SIGNED MESSAGE- > > Remember

Re: [sqlite] error in sum function

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 On Thu, Jul 15

Re: [sqlite] error in sum function

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

o 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 NULL UNIQUE , "product" TEXT,

Re: [sqlite] Query critique

in the FRIENDS table. Regards Tim Romano Swarthmore PA On Fri, Jul 9, 2010 at 5:52 AM, Benoit Mortgat <mort...@gmail.com> wrote: > On Fri, Jul 9, 2010 at 11:08, Ian Hardingham <i...@omroth.com> wrote: > > Hey guys. > > > > I have a query which is very slow, a

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

gards 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

h 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 Tim Roman

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

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 <dbic...@yahoo.com> wrote: > > > All things considered, I would say this is clearly and 100% a bug in >

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

r 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, Jun 29, 2010 at 12:30 PM, Jay A. Krei

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

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 <punk.k

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

Tim Romano Swarthmore PA On Tue, Jun 29, 2010 at 11:18 AM, Pavel Ivanov <paiva...@gmail.com> 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 opi

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

t 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 <punk.k...@gmail.com> wrote: > On Tue, Jun 29, 2010 at 9:58 AM, Tim Ro

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

urned 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 <j...@kreibi.ch> wrote: > On Tue, Jun 29, 2010 at 06:59:18AM -0400, Tim

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

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?

these 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 <paiva...@gmail.com> wrote: > >

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

quot;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 <paiva...@gmail.com> wrote: > > the primary key column [id] is defined as INTEGER PRMARY KEY; so defined, > > SQLite will trea

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

tity (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 <tim.romano...@gmail.com> wrote: > And myspecialvalue can be INTEGER|TEXT. > > > On Mo

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

And myspecialvalue can be INTEGER|TEXT. On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano <tim.romano...@gmail.com> wrote: > In this example: > > CREATE TABLE tableA { > > id INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT NOT NULL UNIQUE, > myspecia

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

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 <keekyc...@gmail.com> wrote: > Dear all

Re: [sqlite] alternative to UNIQUE CONSTRAINT

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 <oliver@web.de> wrote: > Igor Tandetnik <itandet...@...> writes: > > [...] > > > Isn't that exactly what you

Re: [sqlite] Question About SQLITE and AIR efficiency

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 <d...@sqlite.org> wrote: > On Sun, Jun 20, 2010 at 6:11 PM, Felipe Aramburu <fel...@kwhours.com> > wrote: > > > I have

Re: [sqlite] database development - correct way?

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

) in 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 <oliver@web.de> wrote: > Hello, > > despite it's just a question about

Re: [sqlite] Oracle connection

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

Re: [sqlite] Oracle connection

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 <wdl...@web

Re: [sqlite] Aggregate and join query very slow

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

eady-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 <j...@q-e-d.org>wrote: > > > > >I would first create an INTEGER primary key and then place an index on

Re: [sqlite] select intersecting intervals

* 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 <jan.assel...@iba-benelux.co

Re: [sqlite] join performance query

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 11, 2

Re: [sqlite] join performance query

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

where id_playlist=2 {AND|OR } number > 258 ) as MYPLAYLISTSONGS Regards Tim Romano On Tue, May 11, 2010 at 12:39 PM, Tim Romano <tim.romano...@gmail.com>wrote: > You could remove the title condition from the inner SONGS select, limiting > your conditions to artist and genre; an

Re: [sqlite] join performance query

ion)'// --> 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 at 11:13 AM, Andrea

Re: [sqlite] join performance query

ion)' ) 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 <galea...@korg.it> wrote: > Hi guys, > I'm in a bind for a huge time consuming query! > I

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

; 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 <paiva...@gmail.com> wr

Re: [sqlite] Should this work?

. 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 <youngsan...@gmail.

Re: [sqlite] Should this work?

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?

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 <youngsan...@gmail.com> wrote: > Got it, thinks Jay. > > > On 5/6/10,

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

, 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 <youngsan...@gmail.

Re: [sqlite] Optimising usage of LIKE

By "version" I meant "implementation". On Mon, May 3, 2010 at 7:25 AM, Tim Romano <tim.romano...@gmail.com> 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 &g

Re: [sqlite] Optimising usage of LIKE

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 Tim Romano

Re: [sqlite] scripting language interpreter

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

@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

Very cool, Jay. Regards Tim Romano On Sat, May 1, 2010 at 11:23 AM, Jay A. Kreibich <j...@kreibi.ch> 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 >

Re: [sqlite] scripting language interpreter

s 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 power that loadable extensi

[sqlite] scripting language interpreter

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

. 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 <j...@q-e-d.org>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

. Regards Tim Romano On Mon, Apr 26, 2010 at 8:27 PM, Jean-Christophe Deschamps <j...@q-e-d.org>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

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

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

of proper nouns versus normal nouns ;-) ... GLOB 'A*' ... GLOB 'a*' Regards Tim Romano On Mon, Apr 26, 2010 at 8:47 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > Tim Romano wrote: > > If the implementation of SQLite you are using overrides the LIKE operator > > (

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

(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 <has

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

indexes? Is it? Regards Tim Romano On Fri, Apr 16, 2010 at 8:17 AM, Mike Goins <mike.go...@adtecservices.net>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, exte

Re: [sqlite] Index and GLOB

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 <itandet...@mvps.org> wrote: > Tim Romano wrote: > > I belie

Re: [sqlite] Index and GLOB

'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 <mike.go...@adtecservices.net>wrote: > First thanks to all that have helped on

Re: [sqlite] Feature request: hash index

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 <pechni...@mobigroup.ru>wrote: > Hello! > > On Monday 05 April 2010 22:22:40 Roger Binns wro

Re: [sqlite] Feature request: hash index

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 <pechni...@mobigroup.ru>wrote: > Hello! > > The b-tree index is not good choice for high-selective data, but there is &g

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

/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

--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 <fel...@kwhours.com> wrote: > I have some code that is using flex sdk 3.2

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

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

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

PRIMARY KEY structid 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

Re: [sqlite] Preserving column size

; many 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-users@

Re: [sqlite] Preserving column size

neral interface for this as > there 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

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

an 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

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

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

On 3/21/2010 5:22 PM, Max Vlasov wrote: > On Sun, Mar 21, 2010 at 3:50 PM, Tim Romano<tim.rom...@yahoo.com> wrote: > > >> For someone who doesn't read C, could someone who knows please describe >> the SQLite INTERSECT algorithm? What optimizations are available to

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

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

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 ___ sqlite-users mailing

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

as no 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

t; 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 data of

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

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

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-users@sqlite

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

On 3/9/2010 10:56 AM, Scott Hess wrote: > On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano<tim.rom...@yahoo.com> wrote: > >> Of these three: >> >> select c from T where 1=2 // returns 0 rows >> select min(c) from T where 1=2 // returns 1 r

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

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

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?

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

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

, 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 my I

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

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 own access to SQL commands. If this system is

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

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/27/2010 12

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

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

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

es require periodic maintenance using VACUUM? Regards Tim Romano On 1/25/2010 11:47 AM, Simon Slavin wrote: > On 25 Jan 2010, at 1:40pm, Tim Romano wrote: > > >> What is the maximum number of literal values that can be put inside the IN ( >> ) list ? >> >>

  1   2   >