[sqlite] upgrading DB from 3.6.23 to 3.7.5

I am in the process of upgrading my app from using SQLite.net w/ 3.6.23 to SQLite.net w/ 3.7.5. When the .Net program starts in a fresh install state, aka no system db exists and it builds one up via SQL script all works fine. When it opens an existing 3.6.23 system db, it gets a database lock.

Re: [sqlite] Speed up count(distinct col)

On 11 Feb 2011, at 2:37am, Yuzem wrote: > Simon Slavin-3 wrote: >> > >> By looking at the file on disk ? Are you taking into account the journal >> file ? >> > > Yes, I do all the counts So if I deleted one record and created another you wouldn't spot it ? > and save the data to a file

Re: [sqlite] Speed up count(distinct col)

On 11/02/2011, at 1:37 PM, Yuzem wrote: > For example lets say I have the following data: > 1|director|1 > 2|director|2 > 3|director|1 > > In this example the total count for directors is 2, I have two distinct > directors. > In the table "Capacity Statistics" I will have: > director|2 > > The

Re: [sqlite] Speed up count(distinct col)

Simon Slavin-3 wrote: > > By looking at the file on disk ? Are you taking into account the journal > file ? > Yes, I do all the counts and save the data to a file and then if the file is newer then the database I use the file else I count again. No, I am not taking the journal file into

Re: [sqlite] bind or column index out of range

On Thu, Feb 10, 2011 at 11:24:34PM -0200, Fabr?cio Cruz Casarini scratched on the wall: > Guys, > > I am developing an application in php-gtk and I'm using sqlite3. > > I'm accessing the database using adodb class. > > Whenever I try to add a record to the table empty get the error "bind or >

[sqlite] bind or column index out of range

Guys, I am developing an application in php-gtk and I'm using sqlite3. I'm accessing the database using adodb class. Whenever I try to add a record to the table empty get the error "bind or column index out of range". What is the solution to this problem? []'s Fabrício Cruz Casarini

Re: [sqlite] Speed up count(distinct col)

On 11/02/2011, at 11:30 AM, Simon Slavin wrote: > Come to think of it, what's really happening here is a FOREIGN KEY situation. Yes, that's why I have the foreign keys (ie "references") in the schema. So, for instance, if you delete a movie, all of the actors, directors etc associated with

Re: [sqlite] generating mini-calendar: DATE and quoting...

On Thu, Feb 10, 2011 at 06:02:01PM -0500, Samuel Adam scratched on the wall: > On Thu, 10 Feb 2011 17:55:57 -0500, Jay A. Kreibich wrote: >> SELECT date( '2011-01-01', digit || 'days' ) AS d FROM digits; > > s/'days'/' days'/ Indeed. 3.6.x does not require the space, but

Re: [sqlite] Speed up count(distinct col)

On 11 Feb 2011, at 12:26am, BareFeetWare wrote: > On 11/02/2011, at 11:11 AM, Simon Slavin wrote: > >> Erm ... there are also movies which have more than one person directing. >> You need to copy across the rowid from the MoviePeople table, and delete >> just based on that particular record.

Re: [sqlite] Speed up count(distinct col)

On 11/02/2011, at 11:11 AM, Simon Slavin wrote: > Erm ... there are also movies which have more than one person directing. You > need to copy across the rowid from the MoviePeople table, and delete just > based on that particular record. Adding to what Simon said: The schema I posted allows

Re: [sqlite] Speed up count(distinct col)

On 10 Feb 2011, at 11:55pm, Yuzem wrote: > That's the beauty of using a additional table. I make the column unique in > the the extra table and then on any insert in "movies people" I insert in > for example the directors table and any duplicate will be automatically > rejected. > > The same

Re: [sqlite] Speed up count(distinct col)

> BareFeetWare-2 wrote: >> >> In that case, you should cache the counts in a separate table or two. That >> has a negligible overhead when you add a movie (which is infrequent), and >> basically no overhead when viewing (which is frequent). > I am doing that but in the application level, the down

Re: [sqlite] Speed up count(distinct col)

On 10 Feb 2011, at 11:55pm, Yuzem wrote: > I check the modified time of the database. By looking at the file on disk ? Are you taking into account the journal file ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] wal file size

On Thu, Feb 10, 2011 at 6:45 PM, Michael Barton wrote: > Is there something I need to do to keep my sqlite WAL files from > getting huge with 3.7.5? > > -rw--- 1 swift swift 152M 2011-02-10 23:43 > b7fa56688b61c70ef29ed2ad94b7beeb.db > -rw--- 1 swift swift 19M

Re: [sqlite] Speed up count(distinct col)

BareFeetWare-2 wrote: > > In that case, you should cache the counts in a separate table or two. That > has a negligible overhead when you add a movie (which is infrequent), and > basically no overhead when viewing (which is frequent). I am doing that but in the application level, the down side

[sqlite] wal file size

Is there something I need to do to keep my sqlite WAL files from getting huge with 3.7.5? -rw--- 1 swift swift 152M 2011-02-10 23:43 b7fa56688b61c70ef29ed2ad94b7beeb.db -rw--- 1 swift swift 19M 2011-02-10 23:43 b7fa56688b61c70ef29ed2ad94b7beeb.db-shm -rw--- 1 swift swift 2.4G

Re: [sqlite] Speed up count(distinct col)

Oops, I should have said old instead of new in a couple of places: > begin immediate > ; > create table "Capacity Statistics" > ( ID integer primary key unique references "Capacity" (ID) on delete > cascade > , Count integer not null > ) > ; > insert into "Capacity Statistics" (ID,

Re: [sqlite] generating mini-calendar: DATE and quoting...

@igor, @jay (and a good assist from @samuel): Cool beans -- that works and is cleaner than my hack. Thank you. FYA, the final form of this query (to be used as a sub-query throughout much of our system) is: > SELECT DATE('#{start_time.to_s(:db)}', (thousands.digit * 1000 + >

Re: [sqlite] Speed up count(distinct col)

On 11/02/2011, at 9:40 AM, Yuzem wrote: > Yes, in my application I have in the sidebar all those sections (movies, > years, tags, keywords, actors, directors, writers, etc...) and I count each > one, how many movies, how many years, etc... > It isn't very slow if I update only one item but the

Re: [sqlite] generating mini-calendar: DATE and quoting...

On Thu, 10 Feb 2011 17:55:57 -0500, Jay A. Kreibich wrote: > On Thu, Feb 10, 2011 at 02:47:29PM -0800, fearless_fool scratched on the > wall: >> >> Meh. I have a solution, but I don't like it very much because it feels >> convoluted: >> >> > sqlite> select strftime('%Y-%m-%d',

Re: [sqlite] generating mini-calendar: DATE and quoting...

On 2/10/2011 5:17 PM, fearless_fool wrote: > I'd like to write a query that generates ten consecutive days starting at > "2011-02-05" (for example), but I believe I'm having trouble with quoting. > Assume I have a table of ten digits such as: > > CREATE TABLE "digits" ("id" INTEGER PRIMARY KEY

Re: [sqlite] generating mini-calendar: DATE and quoting...

On Thu, Feb 10, 2011 at 02:47:29PM -0800, fearless_fool scratched on the wall: > > Meh. I have a solution, but I don't like it very much because it feels > convoluted: > > > sqlite> select strftime('%Y-%m-%d', julianday('2011-01-01') + digit) > > as d from digits; > This takes

Re: [sqlite] generating mini-calendar: DATE and quoting...

Meh. I have a solution, but I don't like it very much because it feels convoluted: > sqlite> select strftime('%Y-%m-%d', julianday('2011-01-01') + digit) as d > from digits; > 2011-01-01 > 2011-01-02 > 2011-01-03 > 2011-01-04 > 2011-01-05 > 2011-01-06 > 2011-01-07 > 2011-01-08 > 2011-01-09 >

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

On Thu, Feb 10, 2011 at 11:22 PM, Samuel Adam wrote: > Thanks for actually looking this up. i didn't look THAT closely, as you found out: > Worse, PDO::PARAM_LOB is for binding a stream and not a regular variable: > http://www.php.net/manual/en/pdo.lobs.php Doh! --

Re: [sqlite] Speed up count(distinct col)

Simon Slavin-3 wrote: > > How much slower. Did you make an index SQLite could use for that query ? > Using distinct isn't slow, it is what I would expect but count(*) is incredibly fast, it is instantaneous no matter how large is the table. Yes, I tried with an index. BareFeetWare-2 wrote:

Re: [sqlite] generating mini-calendar: DATE and quoting...

Samuel Adam-2 wrote: > > > http://www.sqlite.org/lang_datefunc.html > http://www.sqlite.org/lang_select.html > (and a few others) > Very truly, > Hi SA: So I've been reading those very pages carefully. And since the docs say > Note that "±NNN months" works by rendering the original date

Re: [sqlite] generating mini-calendar: DATE and quoting...

On Thu, Feb 10, 2011 at 05:27:02PM -0500, Samuel Adam scratched on the wall: > On Thu, 10 Feb 2011 17:17:29 -0500, fearless_fool wrote: > > [snip] > > A query that does NOT work is: > > > > sqlite> SELECT DATE("2011-02-05 21:42:20", "units.digit DAY") AS d FROM > > digits AS

Re: [sqlite] generating mini-calendar: DATE and quoting...

On Thu, 10 Feb 2011 17:17:29 -0500, fearless_fool wrote: [snip] > A query that does NOT work is: > > sqlite> SELECT DATE("2011-02-05 21:42:20", "units.digit DAY") AS d FROM > digits AS units; http://www.sqlite.org/lang_datefunc.html http://www.sqlite.org/lang_select.html

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

On Thu, 10 Feb 2011 16:38:40 -0500, Stephan Beal wrote: > On Sun, Feb 6, 2011 at 2:36 PM, Samuel Adam wrote: > >> >>* Make sure the binding is done as BLOB and not TEXT. PDO >> probably >> has >> its own flags defined for this. This is

[sqlite] generating mini-calendar: DATE and quoting...

I'd like to write a query that generates ten consecutive days starting at "2011-02-05" (for example), but I believe I'm having trouble with quoting. Assume I have a table of ten digits such as: CREATE TABLE "digits" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "digit" integer) INSERT INTO

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

On Sun, Feb 6, 2011 at 2:36 PM, Samuel Adam wrote: > >* Make sure the binding is done as BLOB and not TEXT. PDO probably > has > its own flags defined for this. This is the part that tells SQLite > whether you are inserting TEXT or BLOB. >

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

On Thu, 10 Feb 2011 15:21:57 -0500, Yves Goergen wrote: > On 07.02.2011 23:47 CE(S)T, Samuel Adam wrote: >> On Mon, 07 Feb 2011 03:16:54 -0500, Philip Graham Willoughby >> wrote: >>> What about: >>> >>> UPDATE "message_revision"

Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )

On Thu, Feb 10, 2011 at 9:59 PM, Zaryab M. Munir wrote: > [zm]: No there is no other function read() and I did compile glibc. I want > to check if it is static or dynamic. Is static linking a requirement ? > Thanks. > If you're linking to libXXX.so then it's dynamically

Re: [sqlite] Outer query returning results not found in subquery

On Thu, Feb 10, 2011 at 2:29 PM, Igor Tandetnik wrote: > On 2/10/2011 2:17 PM, Dan Kubb wrote: >> Database setup: >> >>      CREATE TABLE "test" ("letter" VARCHAR(1) PRIMARY KEY, "number" INTEGER >> NOT NULL); >> >>      INSERT INTO "test" ("letter", "number") VALUES('b',

Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )

--- On Thu, 2/10/11, Pavel Ivanov wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] SIGSEGV Error when using sqlite3_exec( ) > To: "General Discussion of SQLite Database" > Cc: "Zaryab Munir (zmunir)" > Date:

Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )

> [zm]:  Based on the documentation, applications can have multiple connections > to a file database by calling sqlite3_open() repeatedly.  Is there a way to > have multiple connections to the ":memory:" database. No. In-memory databases are special, see http://www.sqlite.org/inmemorydb.html.

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

On 07.02.2011 23:47 CE(S)T, Samuel Adam wrote: > On Mon, 07 Feb 2011 03:16:54 -0500, Philip Graham Willoughby > wrote: >> What about: >> >> UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB); > > Y’know the urban legend about the folks at the

Re: [sqlite] Speed up count(distinct col)

On Feb 10, 2011, at 5:00 PM, Yuzem wrote: > The only thing I can think of is to have additional tables for the ids of > all directors, all writers, etc... > Tables "movies", "people", "capacity" and then tables "directors", > "writers", etc.. with only the IDs so I can count using count(*) which

Re: [sqlite] Outer query returning results not found in subquery

On 2/10/2011 2:17 PM, Dan Kubb wrote: > Database setup: > > CREATE TABLE "test" ("letter" VARCHAR(1) PRIMARY KEY, "number" INTEGER > NOT NULL); > > INSERT INTO "test" ("letter", "number") VALUES('b', 1); > INSERT INTO "test" ("letter", "number") VALUES('a', 2); > INSERT INTO

Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )

Thanks, my reply inline: Sincerely, Zaryab --- On Thu, 2/10/11, Pavel Ivanov wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] SIGSEGV Error when using sqlite3_exec( ) > To: "General Discussion of SQLite Database" > Cc:

[sqlite] Outer query returning results not found in subquery

Hi, I have a query that when executed stand-alone returns different results than when it's a subquery. At first I wasn't sure if it was just me, so I submitted a question to Stackoverflow with some detail and someone else replied that they could reproduce what I was seeing:

Re: [sqlite] Speed up count(distinct col)

> Thanks you but what I want to do is to count without using count(distinct > col) because it is much slower than count(*). I think you'll find the schema I posted very fast, since it's running everything, including distinct, on primary key columns. Or you can try this: select count(*) from

Re: [sqlite] Speed up count(distinct col)

On 10 Feb 2011, at 4:00pm, Yuzem wrote: > Thanks you but what I want to do is to count without using count(distinct > col) because it is much slower than count(*). How much slower. Did you make an index SQLite could use for that query ? A good index for that might be People_ID,Capacity_ID

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

On Thu, Feb 10, 2011 at 09:35:04AM -0600, Puneet Kishor scratched on the wall: > that code would be very complex to cover all the possible cases. The > simplest solution is to depend upon AS aliasing To be clear, that's not an excuse the development team is using to avoid writing a hard bit

Re: [sqlite] Speed up count(distinct col)

BareFeetWare-2 wrote: > > Then you can count the directors like this: > > select count(distinct People_ID) from "Movie People" join Capacity on > "Movie People".Capacity_ID = Capacity.ID where Capacity.Name = 'director'; > > or: > > select count(distinct People_ID) from "Movie People" where

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

--- On Thu, 2/10/11, Puneet Kishor wrote: > Date: Thursday, February 10, 2011, 10:35 AM > > On Thursday, February 10, 2011 at 9:22 AM, Nißl Reinhard > wrote: > > Hi, > > > > I'm sorry Pavel, I think you've got me wrong. > > > > > It's not "buggy". Name of the column in

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

On Thursday, February 10, 2011 at 9:22 AM, Nißl Reinhard wrote: > Hi, > > I'm sorry Pavel, I think you've got me wrong. > > > It's not "buggy". Name of the column in result set is not defined > > unless you use "as". > Pavel is not wrong. SQLite is not buggy. Your expectation of what it

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

> But why do I get different column names but the same result for these > statements? >        select  a  from (select a from x); >        select [a] from (select a from x); >        select "a" from (select a from x); Please show us documentation that says you should get the same column name for

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

Hi, I'm sorry Pavel, I think you've got me wrong. > It's not "buggy". Name of the column in result set is not defined > unless you use "as". But why do I get different column names but the same result for these statements? select a from (select a from x); select [a] from

Re: [sqlite] Speed up count(distinct col)

On 08/02/2011, at 10:19 AM, Yuzem wrote: > I have the tables "movies" and "people" > Those two tables are related by tables "directors", "writers", etc... > movies: id, title > people: id, name > > directors: movieId, peopleId > writers: movieId, peopleId > etc... > > How can I normalize that

Re: [sqlite] EXT : Speed up count(distinct col)

Hi, thanks a lot for the helpful replies. Sorry to bother again, but there is still something that hasn't been answered. Simon Slavin-3 has addressed my question but not exactly what I was asking. Suppose I have two tables "movies" and "people" and other tables to relate both tables:

Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )

Zaryab, There's no need to repeat your email several times. >    Question1:  Can I have multiple connections opened for each thread to >    the same in-memory dbase. No. Each connection to ":memory:" creates unique in-memory database which will be deleted when that connection is closed. >    

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

>        select [a] from (select * from x); > You'll get the following "buggy" output: >        [a] >        1 It's not "buggy". Name of the column in result set is not defined unless you use "as". >        CREATE TABLE y("[a]" INT); > I came across this issue as statements like the following

Re: [sqlite] Feature suggestion for the Tcl interface : ability to use a list variable in combination with IN

See http://sqlite.mobigroup.ru/wiki?name=ext_intarray_tcl 09.02.2011 17:49 пользователь "Fredrik Karlsson" написал: > Dear list, > > I find the IN operator quite useful for selecting a set number of things. > However, I often have a Tcl list with the things I want to match >

[sqlite] SIGSEGV Error when using sqlite3_exec( )

Hi, I am using an in-memory dbase in a multi-threaded application and have the following two questions: I create dbase connections by each thread using the API: { Sqlite3 *db =3D NULL; Sqlite3_open(":memory:", ); When I try to use sqlite3_exec( ) I get

[sqlite] help: sigsegv error using sqlite3_exec( )

Hi, I am using an in-memory dbase in a multi-threaded application and have the following two questions: I create dbase connections by each thread using the API: { Sqlite3 *db = NULL; Sqlite3_open(":memory:", ); When I try to use sqlite3_exec( ) I get segmentation faults. }

Re: [sqlite] Bi-directional unique

On Wed, 09 Feb 2011 20:14:19 -0500, Igor Tandetnik wrote: > On 2/9/2011 7:29 PM, Samuel Adam wrote: […snip garbage…] > > It seems (1, 2) and (2, 1) would result in distinct xk values, thus > defeating the point of the exercise. It is again possible to insert two > pairs

Re: [sqlite] Regarding "Manual Control Of Query Plans"

I remember why I added the sqlite_stat2 flag together with the ANALYZE command. It makes certain illformed queries efficient. CREATE TABLE IF NOT EXISTS TC -- 10 rows ( C INTEGER PRIMARY KEY, Y CHAR(255) NOT NULL UNIQUE, ); CREATE TABLE IF NOT EXISTS TB -- 100 rows ( B INTEGER

[sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

Hi, identifier quotation characters ("[]) get part of column names for certain statements, i. e. it depends on the complexity of the statement to trigger this bug. To reproduce the bug, type the following in sqlite3: .headers ON create table x(a int); insert into x

Re: [sqlite] database disk image is malformed 3.7.x

I'm having the same problem with my application. Basically, it's a combination of jukebox/music management app I've been developing myself over the last few years. I had always used the dump commands to back up & restore the database, given that I develop on both linux and windows. When the

Re: [sqlite] database disk image is malformed 3.7.x

> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: woensdag 9 februari 2011 19:26 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] database disk image is malformed 3.7.x > > > > I didn't find a

Re: [sqlite] determine SQLITE_MAX_LENGTH or sql_limit/sqlite3_limit

That should do the trick, thanks for pointing it out. On 10 February 2011 06:18, Dan Kennedy wrote: > On 02/10/2011 01:56 AM, Vannus wrote: > > Zeoslib is reading sqlite field lengths incorrectly, as it checks for > > brackets after the field typename ie. CHAR(123) > >