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. an
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 an
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
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 accou
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
> c
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
_
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 that
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 the newer 3.7 verio
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.
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 f
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 pro
> 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
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
http://sqlite.org:8080/
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 2011-02-10 23:43
> b7fa56688
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 i
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 2011-02-
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, Count)
@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 +
> hundreds.di
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 pro
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', julianday('2011-
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 AUTO
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 advantag
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
> 2
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!
--
- stephan beal
http
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:
>
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 in
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 units;
>
> http://ww
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
(and a few others)
Ve
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 the part that tells SQLite
>> whether you are
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
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.
>
http://www.php.net/manual/en/pdostatement.bind
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" SET "Data" = CAST ("Data" AS BLOB);
>>
>> Y’know the urban legend
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 linked. sqlite3 can be
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', 1);
>> INSERT INTO
--- 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: Thursday, February 10, 2011, 12:28 PM
> > [zm]: Based on the
> documentation, appl
> [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.
If
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 restaurant who design a
> complicated pl
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
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 "t
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: "Zaryab Munir (zmunir)"
> Date: Thursday, February 10, 2011, 5:11 AM
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:
http://stackover
> 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 Pe
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
b
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 o
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 C
--- 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 result
> set is not de
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 should
> 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
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 (sel
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 so
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: "directors",
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.
> Qu
> 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 fai
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
> already when I get to
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:", &db);
When I try to use sqlite3_exec( ) I get segment
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:", &db);
When I try to use sqlite3_exec( ) I get segmentation faults.
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 that differ only in orde
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
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 valu
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 bac
> -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 way
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)
> > presumably this is only af
62 matches
Mail list logo
