Re: [sqlite] X most recent entries

Assuming that higher rowids really are later rowids, wouldn't adding "ORDER BY rowid DESC" and "LIMIT 5000" do the job? Will On 3/14/11 10:58 AM, "Ian Hardingham" wrote: Ah, sorry about this - my query is this one: SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid

[sqlite] Weird CHECK behavior

Howdy! I have a database with tables defined like this: CREATE TABLE table1 ( idINTEGER PRIMARY KEY, value REAL DEFAULT 1.0 CHECK (0.0 <= value) ); CREATE TABLE table2 ( idINTEGER PRIMARY KEY, value REAL DEFAULT 1.0 CHECK (0.0 <= value AND value <= 1.0) ); The following

Re: [sqlite] Weird CHECK behavior

On 4/7/11 2:52 PM, "Jay A. Kreibich" <j...@kreibi.ch> wrote: >On Thu, Apr 07, 2011 at 02:44:49PM -0700, Duquette, William H (318K) >scratched on the wall: >> Howdy! >> >> I have a database with tables defined like this: >> >> CREATE TABLE tab

Re: [sqlite] Weird CHECK behavior

On 4/7/11 2:52 PM, "Jay A. Kreibich" <j...@kreibi.ch> wrote: >On Thu, Apr 07, 2011 at 02:44:49PM -0700, Duquette, William H (318K) >scratched on the wall: >> Howdy! >> >> I have a database with tables defined like this: >> >> CREATE TABLE tab

Re: [sqlite] Weird CHECK behavior

On 4/7/11 4:37 PM, "Jay A. Kreibich" <j...@kreibi.ch> wrote: >On Thu, Apr 07, 2011 at 03:45:18PM -0700, Duquette, William H (318K) >scratched on the wall: > >> Hmmm. I tried this; but this constraint fails for ANY value I give it. > >> I tried this: >&

Re: [sqlite] "override" table?

On 7/20/11 8:27 AM, "Simon Slavin" wrote: >On 20 Jul 2011, at 4:21pm, KeithB wrote: > >> I'd like to create a temporary table to "shadow" one of my persistent >> tables. It will have the same columns and hold "override" values that, >> when present, take precedence over the

Re: [sqlite] Strange foreign key constraint failed with DROP TABLE

What if you defined the foreign key with "ON DELETE CASCADE"? Dropping the employer table will delete the employees. Will -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory "It's amazing what you can do with the right tools." On 8/19/11

Re: [sqlite] Strange foreign key constraint failed with DROP TABLE

On 8/19/11 10:18 AM, "Boris Kolpackov" <bo...@codesynthesis.com> wrote: >Hi William, > >"Duquette, William H (318K)" <william.h.duque...@jpl.nasa.gov> writes: > >> What if you defined the foreign key with "ON DELETE CASCADE"

Re: [sqlite] Strange foreign key constraint failed with DROP TABLE

On 8/19/11 10:44 AM, "Boris Kolpackov" <bo...@codesynthesis.com> wrote: >Hi William, > >"Duquette, William H (318K)" <william.h.duque...@jpl.nasa.gov> writes: > >> On 8/19/11 10:18 AM, "Boris Kolpackov" <bo...@codesynthesis

Re: [sqlite] SQLite Wiki doesn't work

I believe that SQLite3 is being hosted using Fossil now, rather than cvstrac; and I don't see a "Wiki" link on the main SQLite page. At a guess, the old Wiki pages have been migrated to Fossil, but can now only be edited by the SQLite developers. If there are links to the old wiki anywhere, they

[sqlite] Progress callback and nested queries

I'm pretty sure I know the answer to this. Sqlite3 allows you to define a "progress" callback, which will be called every so many byte-code instructions during a long-running query, so that you can update a progress bar or like that. I'm assuming that querying the same database using the same

Re: [sqlite] Progress callback and nested queries

On 11/2/11 10:01 PM, "Dan Kennedy" <danielk1...@gmail.com> wrote: >On 11/03/2011 01:11 AM, Duquette, William H (318K) wrote: >> I'm pretty sure I know the answer to this. >> >> Sqlite3 allows you to define a "progress" callback, which will be >&

[sqlite] Can a custom SQL function return NULL?

If I define a custom SQL function in Tcl using the SQLite "$db function" command, is there any way to make the function return NULL? I'm guessing not. Thanks! -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory "It's amazing what you can do

Re: [sqlite] Can a custom SQL function return NULL?

On 12/22/10 10:35 AM, "Richard Hipp" <d...@sqlite.org> wrote: On Wed, Dec 22, 2010 at 1:16 PM, Duquette, William H (318K) < william.h.duque...@jpl.nasa.gov> wrote: > If I define a custom SQL function in Tcl using the SQLite "$db function" > command, is the

Re: [sqlite] Can a custom SQL function return NULL?

On 12/22/10 10:52 AM, "Simon Slavin" wrote: >> There is no way to get a Tcl function to return NULL, since TCL has no >> concept of NULL. So, no, sadly, you cannot get an SQLite function >> implemented in Tcl to return NULL. > > ... but you might find reading this useful:

[sqlite] Unexpected cascading delete

I've just discovered that a REPLACE can trigger a cascading delete. Is this expected behavior? I have an undo scheme where I grab entire rows from the database before they are changed; then, on undo I simply put the rows back using "INSERT OR REPLACE". My assumption was that doing a REPLACE was

Re: [sqlite] Unexpected cascading delete

Richard, I was afraid you were going to tell me that; it makes all too much sense, once I thought about. Thanks for the definitive word. Will On 1/12/11 2:08 PM, "Richard Hipp" <d...@sqlite.org> wrote: On Wed, Jan 12, 2011 at 4:54 PM, Duquette, William H (318K) &

Re: [sqlite] REGEXP parameter order - embarassed newbie

Being one who speaks good English, has a logical mind, and has previously programmed in C, AND who had used SQLite for around five years on the strength of that, I still found the book useful when I read it a couple of months ago. I already knew the basics, but it shed light on a few obscure

Re: [sqlite] uuid generation in sqlite

A question on using randomblob(16) to generate UUIDs, as the SQLite docs suggest: what assurance do you have that the UUID really is universally unique? It's a pseudo-random number, and you can replicate a stream of pseudo-random numbers by setting the seed appropriately. Is randomblob()

Re: [sqlite] uuid generation in sqlite

On 1/24/11 8:29 AM, "Richard Hipp" <d...@sqlite.org> wrote: > On Mon, Jan 24, 2011 at 11:21 AM, Duquette, William H (318K) < > william.h.duque...@jpl.nasa.gov> wrote: > >> A question on using randomblob(16) to generate UUIDs, as the SQLite docs &

Re: [sqlite] uuid generation in sqlite

On 1/24/11 8:36 AM, "Simon Slavin" <slav...@bigfraud.org> wrote: > > On 24 Jan 2011, at 4:21pm, Duquette, William H (318K) wrote: > >> A question on using randomblob(16) to generate UUIDs, as the SQLite docs >> suggest: what assurance do you have that the

[sqlite] WAL for production use

Howdy! In SQLite 3.7.4/3.7.5, does WAL seem to be stable enough for production use? And then, an architecture question. I have an app that occasionally needs to do significant background processing. I'd like to keep the GUI awake and looking at the current data set while the app is computing

Re: [sqlite] WAL for production use

Thanks, Richard! Will On 2/2/11 8:22 AM, "Richard Hipp" <d...@sqlite.org> wrote: On Wed, Feb 2, 2011 at 11:14 AM, Duquette, William H (318K) < william.h.duque...@jpl.nasa.gov> wrote: > Howdy! > > In SQLite 3.7.4/3.7.5, does WAL seem to be stable enough for p

Re: [sqlite] Question about database design

On 2/2/11 11:48 AM, "Bert Nelsen" wrote: > Because I felt so stupid about these mostly empty columns taking so much > space, I tended to replace all the "phone" columns by a single column named > "customerPhone". > I stored the values into customerPhone like that: >

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

On 10/22/12 1:44 PM, "Guillaume Saumure" wrote: >Le 2012-10-22 15:35, Paul van Helden a écrit : >>> It would be possible to implement TRUNCATE TABLE on top of that, but >>> this would be only syntactic sugar. >>> >> ..or better portability. TRUNCATE TABLE works (since

[sqlite] SQLite driver for Java

Howdy! What driver are people using to access SQLite databases from Java applications? Will -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory "It's amazing what you can do with the right tools."

Re: [sqlite] SQLite driver for Java

Thanks, Julian; we'll see. I'm not married to JDBC; an SQLite API wrapper might suit me down to the ground. I'm not doing general SQL database stuff, but we've done quite a lot with SQLite in the past, and I'd like to retain it in my toolkit. In any event I'm using Java 1.7; it's a little

Re: [sqlite] Database design preferences

On 11/30/12 8:34 AM, "Simon Slavin" wrote: > >On 30 Nov 2012, at 3:50pm, Staffan Tylen wrote: > >> I'm looking for both administrative and technical advice on the pros and >> cons of either creating one single database table with many columns or >>

Re: [sqlite] Simple SQLite-based spreadsheet?

I don't think the OP really cares about linking SQLite to a spreadsheet. If I'm reading him correctly, he's just looking for an easy way to populate SQLite database tables using a simple GUI he doesn't have to develop himself, and doesn't have to pay a lot of money for. In other words, he wants

Re: [sqlite] Found it

On 1/26/12 9:36 AM, "Stephan Beal" wrote: >On Thu, Jan 26, 2012 at 2:53 PM, Mohit Sindhwani wrote: > >> Absolutely! I come home from work and tune in to this thread, gripped >>:) >> > >+1 to Mohit and the others who's written similar responses. i rarely

[sqlite] Views and Performance

Howdy! Suppose I have two related tables, t1 and t2, and I write a view like this: CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column); If I am querying data just from t1, is there a performance penalty for using myview in the query? Or will the query planner generate

Re: [sqlite] Views and Performance

On 3/2/12 8:29 AM, "Igor Tandetnik" wrote: >On 3/2/2012 11:29 AM, Pavel Ivanov wrote: >>> If I am querying data just from t1, is there a performance penalty >>> for using myview in the query? Or will the query planner generate >>> approximately the same bytecode as it

Re: [sqlite] Views and Performance

On 3/2/12 8:31 AM, "Simon Davies" <simon.james.dav...@gmail.com> wrote: >On 2 March 2012 16:23, Duquette, William H (318K) ><william.h.duque...@jpl.nasa.gov> wrote: >> Howdy! >> >> Suppose I have two related tables, t1 and t2, and I write a view like

Re: [sqlite] SQLite via TCL Question

On 5/8/12 1:51 PM, "Tilsley, Jerry M." wrote: >This is probably a newbie question so please bear with me. I'm accessing >a SQLite database through TCL and periodically I get a "Database Locked" >error. This is a multi-thread process that writes to the DB, do I need

[sqlite] count() problem.

I have a database with two tables, one of which depends on the other: CREATE TABLE a( a_id INTEGER PRIMARY KEY b_id INTEGER); CREATE TABLE c( b_id INTEGER, num INTEGER, flag INTEGER, PRIMARY KEY (b_id,num)); In words, each

Re: [sqlite] count() problem.

"num", which is what I want. Thanks, this was extremely helpful! Will > > >On Thu, Jun 14, 2012 at 2:00 PM, Duquette, William H (318K) ><william.h.duque...@jpl.nasa.gov> wrote: >> I have a database with two tables, one of which depends on the other: >> >

Re: [sqlite] count() problem.

On 6/14/12 1:00 PM, "Igor Tandetnik" <itandet...@mvps.org> wrote: >On 6/14/2012 2:00 PM, Duquette, William H (318K) wrote: >> What I want to do is find a_id's for which c contains no rows with the >>matching b_id in which the flag column is 1. > >Why don't y

[sqlite] Problem with Foreign Key constraints

Howdy! I have some code that does the following: 1. Takes a snapshot of some number of database tables, e.g., saves the data from those tables as a text string. 2. Later, clears the tables and restores their content from the snapshot. The snapshot is restored by creating a new INSERT statement

Re: [sqlite] Problem with Foreign Key constraints

anced GEOINT Solutions Operating Unit >Northrop Grumman Information Systems > >____________ >From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] >on behalf of Duquette, William H (318K) [william.h.duque...@jpl.nasa.gov] >Sent: Monda

Re: [sqlite] Problem with Foreign Key constraints

On 10/1/12 1:32 PM, "Duquette, William H (318K)" <william.h.duque...@jpl.nasa.gov> wrote: >Howdy! > >I have some code that does the following: > >1. Takes a snapshot of some number of database tables, e.g., saves the >data from those tables as a text st

[sqlite] Custom SQL functions and "nullvalue"

Howdy! The SQLite3 Tcl interface has a "nullvalue" command, which determines how NULLs are represented as Tcl values. If you do a query on a NULL value, you get the "nullvalue" value. ("nullvalue" defaults to the empty string.) However, if a NULL value is passed to a custom SQL function,

Re: [sqlite] Custom SQL functions and "nullvalue"

On 10/3/12 4:20 AM, "Richard Hipp" <d...@sqlite.org> wrote: >On Tue, Oct 2, 2012 at 5:12 PM, Duquette, William H (318K) < >william.h.duque...@jpl.nasa.gov> wrote: > >> Howdy! >> >> The SQLite3 Tcl interface has a "nullvalue" command, wh

Re: [sqlite] Fw: sqlite + EF4 + 'on cascade delete' = not working

On 10/4/12 7:29 AM, "David Richardson" wrote: >I¹m having some sort of >bug with system.data.sqlite. I¹ve been trying for weeks now! I¹ve >installed (System.Data.SQLite 1.0.81.0) and >i¹m using sqlite in combination with Entity Framework 4. Mostly it does >what I >want.

Re: [sqlite] Fw: sqlite + EF4 + 'on cascade delete' = not working

as soon as I open the connection, and the cascading deletes in the schema all take place as expected. However, I'm not using Entity Framework 4 or anything like it; there's evidently something else going on. Will ____________ From: "Duquette, William H (318K)" <william.h