Re: [sqlite] Choosing one value from the group with priority

[Ivan Krylov]

On Tue, 12 Feb 2019 13:31:59 +0100
Clemens Ladisch  wrote:

> SQLite allows to SELECT columns that are not mentioned in the GROUP
> BY clause, but they get their values from a random row in the group.

Thank you for pointing this out! I was aware of the row being
random when selected this way, but I didn't realize that it wasn't
standard SQL. One more reason to avoid SELECTs like this, then.

> There is another SQLite extension which allows to select a row in the
> group by using MAX() or MIN():
> 
>   select *, min(abs(source_id - 3)) from test where id in (1,2) group
> by id;

I'm afraid to use this because the queries I'm considering now may be
reused later in a different SQL engine, and a shortcut like this might
be easy to look over. Maybe I should just select min(abs(source_id-?))
because it is so much simpler, leave a comment and worry about it
and the more general cases later (if ever).

-- 
Best regards,
Ivan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Choosing one value from the group with priority

[Ivan Krylov]

On Tue, 12 Feb 2019 14:08:20 +0200
R Smith  wrote:

> The semantics are made more legible using a CTE here, but can be
> done without it. The essential difference is that it uses a sub-query
> to obtain the minimum priority as opposed to introducing a sub-table
> sort.

Thank you for your reply!

The CTE example has been a learning experience. I have played around
some more and produced a solution for the general "every source_id has
its own priority" case, which I'm including in case someone else finds
this thread:

WITH
RANKED(id, value, rk) AS (
SELECT id, value, CASE source_id
-- source_id =then=> rank
when 3 then 1
when 1 then 2
when 2 then 3
END
FROM test
)
SELECT id, value
FROM RANKED AS test1
WHERE
test1.id IN (1,2)
AND test1.rk = (
SELECT MIN(rk)
FROM RANKED AS test2
WHERE test2.id = test1.id
)
;

This produces the same query plan as your query. Omitting a WHEN allows
me to completely ignore a source_id, should I want that. With

RANKED(id, value, rk) AS (
SELECT id, test.value, RANKING.rowid
FROM test
JOIN carray($ptr,$n,$type) AS RANKING
ON test.source_id = RANKING.value
)

I can supply a list of source_ids in order of decreasing priority from
an array in my application, though I lose cross-database portability
(is there a portable way to pass an array to a parameterized query,
though?) and may lose some of the performance to the JOIN (didn't
build a sample application to run EXPLAIN QUERY PLAN, sorry).

-- 
Best regards,
Ivan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Choosing one value from the group with priority

[Ivan Krylov]

Hi!

I have a table of some values obtained from different sources:

create table test (
id, source_id, value,
primary key(id, source_id)
);
insert into test values
(1, 1, 11), (1, 2, 12), (1, 3, 13),
(2, 1, 21),
(3, 2, 32);

When SELECTing the values, I would like to filter by id and get only
one value per id. This part is misleadingly easy:

select * from test where id in (1,2) group by id;

but then I don't get to control which source I'm obtaining the values
from (when there is more than one). Let's assume for now that I prefer
to choose values with a particular source_id, but if those are not
present, I would take what's available. I managed to invent a query
which would assign a priority to each value using window functions:

select
*, row_number() over win
from test
where
id in (1,2)
window win as (
partition by id
order by abs(source_id-3)
);

By subtracting a different value in the ORDER BY ABS(...) clause, I can
force a value with a different source_id to come up first.

And since SQLite doesn't currently allow putting window functions in the
WHERE clause, I'm using a nested query to actually get the values with
the highest priority:

select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;

which results in the following query plan:

QUERY PLAN
|--CO-ROUTINE 1
|  |--CO-ROUTINE 3
|  |  |--SEARCH TABLE test USING INDEX sqlite_autoindex_test_1 (id=?)
|  |  `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
|  `--SCAN SUBQUERY 3
`--SCAN SUBQUERY 1

Is this the most effective way to express my query? Can the more general
problem of assigning a priority to all sources (e.g. "I want records
from source_id 3, otherwise 1, otherwise 2") be solved in a similar way?

-- 
Best regards,
Ivan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug

[Ivan De La Cruz]

SQLiteDataAdapater is dropping characters after a space in the field when 
filling a datatable (c# winforms).

I.e.
Field   
   : 100 ml
Select returns  
  : 100 ml
DataTable column after Fill 
  : 100
If I add ' marks around the text
  : '100 ml'

My workaround for now is to add ' marks and then remove them after the 
datatable has been populated.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Cannot compile SQLite amalgamation with MDK-ARM (Keil)

[Ivan Kyb]

Cannot compile SQLite amalgamation with MDK-ARM (Keil). I have met errors:

|..\..\Libraries\sqlite-amalgamation-3081101\sqlite3.c(25838):error:#5:cannot 
opensource input file"sys/types.h":No such fileordirectory 
..\..\Libraries\sqlite-amalgamation-3081101\shell.c(59):error:#5:cannot 
opensource input file"pwd.h":No such fileordirectory|

I can see many includes of linux headers, they will not be found too. 
What can I do to provide them? May be use another STL? My question is on 
stackoverflow.com 
.

[sqlite] /tmp

[Ivan Shmakov]

> Richard Hipp writes:
> On Fri, Nov 2, 2012 at 6:44 PM, Tod Olson wrote:

[…]

 >> This is fine on small data, but when I load 1.8GB of data (8.8
 >> million rows) the second CREATE fails, reporting a disk I/O error.

 > You might be running out of /tmp space.  Do you have plenty of /tmp
 > space available for use by the sorter.

Do I understand it correctly that SQLite actually uses $TMPDIR,
and defaults to /tmp if it's not set or empty?

[…]

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR IGNORE safety?

[Ivan Shmakov]

>>>>> Igor Tandetnik writes:
>>>>> Ivan Shmakov <oneing...@gmail.com> wrote:
>>>>> Igor Tandetnik writes:

 >>> Note that ending up in the IGNORE branch of INSERT OR IGNORE
 >>> statement doesn't constitute failure, but normal execution.  The
 >>> same is true for INSERT ... SELECT statement where SELECT returns
 >>> zero rows so nothing is actually inserted.

 >> Thus was my question: how do I ensure that a SELECT … INSERT
 >> statement results in exactly one row being inserted?

 > In your hypothetical example of two INSERT statements in a trigger,
 > that is what would indeed happen, assuming both statements succeed.
 > If either of the two statements fails, the trigger as a whole would
 > fail and any changes it made rolled back.

Yes.

The point is that, thanks to OR IGNORE, the first one succeeds
even if the record being INSERT'ed already exists in the table.

(The ON CONFLICT clause is non-standard, and I was initially
reluctant to use it in my code.  However, the example being
discussed has made its benefits obvious to me.)

 >> (Additional constraint: it should be possible to use the code in a
 >> trigger.)  The best thing I was able to imagine is to replace:

 >> INSERT INTO "bar" ("foo")
 >> SELECT  f."rowid" FROM "foo" f WHERE f."foo" = ?1;

 >> with:

 >> INSERT INTO "bar" ("foo")
 >> VALUES ((SELECT f."rowid" FROM "foo" f WHERE f."foo" = ?1));

 > If the nested SELECT returns an empty set, then this statement
 > attempts to insert NULL into bar.foo, which would fail due to
 > NOT NULL constraint.

… Exactly as intended.

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR IGNORE safety?

[Ivan Shmakov]

> Igor Tandetnik writes:

[…]

 > Note that ending up in the IGNORE branch of INSERT OR IGNORE
 > statement doesn't constitute failure, but normal execution.  The same
 > is true for INSERT ... SELECT statement where SELECT returns zero
 > rows so nothing is actually inserted.

Thus was my question: how do I ensure that a SELECT … INSERT
statement results in exactly one row being inserted?
(Additional constraint: it should be possible to use the code in
a trigger.)  The best thing I was able to imagine is to replace:

INSERT INTO "bar" ("foo")
SELECT  f."rowid" FROM "foo" f WHERE f."foo" = ?1;

with:

INSERT INTO "bar" ("foo")
VALUES ((SELECT f."rowid" FROM "foo" f WHERE f."foo" = ?1));

… The task I'm solving seems unlike an unusual one.  Suppose
that, e. g., I'm recording a history of clients.  Each client
has a name, and is served an arbitrary (but positive) number of
times, at particular dates.  Or, for the 0'th approximation:

CREATE TABLE "history" (
"client-name"   TEXTNOT NULL,
-- as in: CAST (strftime ('%s', 'now') AS INTEGER), or now ()
"timestamp" INTEGER NOT NULL
);

The redundancy of the schema above could, however, be reduced as
follows:

CREATE TABLE "client" (
"name"  TEXTNOT NULL
);

CREATE UNIQUE INDEX "client-unique"
ON "client" ("name");

CREATE TABLE "history-1" (
"client"INTEGER NOT NULL  REFERENCES "client",
"timestamp" INTEGER NOT NULL
);

Now, I wish to preserve the simplicity of access of the first
variant.  Thus, I create a VIEW:

CREATE VIEW "history"
AS  SELECT  c."name" AS "client-name",
h."timestamp"
FROM "history-1" h
INNER JOIN "client" c
ON (c."rowid" = h."client");

… and a TRIGGER:

CREATE TRIGGER "history-insert"
INSTEAD OF INSERT ON "history" FOR EACH ROW BEGIN
INSERT OR IGNORE INTO "client" ("name")
VALUES (new."client-name");
INSERT INTO "history-1" ("client", "timestamp")
VALUES ((SELECT c."rowid"
 FROM "client".c
 WHERE c."name" = new."client-name"),
new."timestamp");
END;

This way, an application can SELECT from and INSERT into this
new "history" VIEW with just the same simplicity as was possible
with the former "history" TABLE.  Naturally, this trigger
requires that a single INSERT to the "history" VIEW results in
exactly a single INSERT to the underlying "history-1" TABLE.
(Which is what brought my question above.)

To note is that, with little CASE trickery, it's possible to
support “hybrid” (id, value) views (c."rowid" AS "client-id" in
the VIEW above), and NULL non-constraints, just as well.

-- 
FSF associate member #7257  np. ml_hygm.xm

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR IGNORE safety?

[Ivan Shmakov]

>>>>> Ivan Shmakov writes:
>>>>> Simon Slavin writes:
>>>>> On 2 Nov 2012, at 8:58am, Ivan Shmakov wrote:

 >>> INSERT OR IGNORE INTO "foo" ("foo")
 >>> VALUES (?1);
 >>> INSERT INTO "bar" ("foo")
 >>> VALUES ((SELECT f."rowid" FROM "foo" f WHERE f."foo" = ?1));

[…]

 > The end result for the command sequence I'm looking for is simple:

 > • "foo" has a (foo → ?1) record — it doesn't matter if it was
 > INSERT'ed just now, or was added at some point before;

 > • "bar" has a (foo → id) record, where ‘id’ is the ROWID of the
 > aforementioned "foo" record.

Correction: “… has a /new/ (foo → id) record…”

 > AIUI, the command sequence above does just that.

[…]

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR IGNORE safety?

[Ivan Shmakov]

>>>>> Simon Slavin writes:
>>>>> On 2 Nov 2012, at 8:58am, Ivan Shmakov wrote:

 >> INSERT OR IGNORE INTO "foo" ("foo")
 >> VALUES (?1);
 >> INSERT INTO "bar" ("foo")
 >> VALUES ((SELECT f."rowid" FROM "foo" f WHERE f."foo" = ?1));

 >> Or is there a better way to ensure that the inner SELECT either
 >> returns a single row, or fails?

 > What do you mean by 'fails'?  Returning zero rows from a SELECT is
 > not failure: it's successfully reporting that there are no such rows
 > in the table.

Which, as long as the task below is considered, indicates a
“contradiction” (of a kind.)

 > And there may be rows in the table even if the first INSERT failed:
 > the rows may have already been inserted.

Yes.

The end result for the command sequence I'm looking for is
simple:

• "foo" has a (foo → ?1) record — it doesn't matter if it was
  INSERT'ed just now, or was added at some point before;

• "bar" has a (foo → id) record, where ‘id’ is the ROWID of the
  aforementioned "foo" record.

AIUI, the command sequence above does just that.

 > If what you mean is that some syntax error or conflict prevented the
 > first INSERT from working, the correct way to do it is to look at the
 > result returned from that INSERT and see whether it is SQLITE_OK.

ACK, thanks.  However, I'm not really interested in the result
of the first INSERT, and, in this case, these commands may
actually be a part of a trigger (where, I believe, I cannot look
at the result returned all that easily.)

[…]

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR IGNORE safety?

[Ivan Shmakov]

>>>>> Clemens Ladisch writes:
>>>>> Ivan Shmakov wrote:

 >> I wonder if I'm on safe side when I use, say:

 >> INSERT OR IGNORE INTO "foo" ("foo")
 >> VALUES (?1);

 >> I mean, if the first INSERT fails for the reason other than the
 >> UNIQUE constraint set

 > <http://sqlite.org/lang_conflict.html> says:

 > | The ON CONFLICT clause applies to UNIQUE and NOT NULL constraints

[…]

 > If it fails for any other reason, you get a normal error.

The point is that I have a NOT NULL constraint as well:

 >> CREATE TABLE "foo" ("foo" TEXT NOT NULL);

(There was a missing UNIQUE in CREATE INDEX, BTW.)

However, as it's clear to me now, the second INSERT as specified
won't raise an error should there be no ?1 in "foo".

Thus, I need something like the following instead:

INSERT OR IGNORE INTO "foo" ("foo")
VALUES (?1);
INSERT INTO "bar" ("foo")
VALUES ((SELECT f."rowid" FROM "foo" f WHERE f."foo" = ?1));

Or is there a better way to ensure that the inner SELECT either
returns a single row, or fails?

TIA.

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT OR IGNORE safety?

[Ivan Shmakov]

I wonder if I'm on safe side when I use, say:

INSERT OR IGNORE INTO "foo" ("foo")
VALUES (?1);
INSERT INTO "bar" ("foo")
SELECT f."rowid"
FROM "foo" f
WHERE f."foo" = ?1;

(within a transaction) against the following schema:

CREATE TABLE "foo" ("foo" TEXT NOT NULL);
CREATE INDEX "foo-unique" ON "foo" ("foo");
CREATE TABLE "bar" ("foo" INTEGER NOT NULL REFERENCES "foo");

I mean, if the first INSERT fails for the reason other than the
UNIQUE constraint set, the second one will fail too, and it's
the success of the second one that I was interested in the first
place.

JFTR, the other possible ways to implement the first INSERT that
I'm aware of, are:

INSERT INTO "foo" ("foo")
SELECT ?1
WHERE NOT EXISTS (SELECT 1 FROM "foo" f WHERE f."foo" = ?1);

INSERT INTO "foo" ("foo")
SELECT ?1
EXCEPT SELECT f."foo" FROM "foo" f;

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: Out-Of-Memory error when doing DELETE from a table with 150 million records

[Ivan P]

Hi Richard,

Shouldn't the delete statement be able to flush it's stored rowids to disk
when it understands the memory is not enough for handling. Otherwise it
doesn't seem scalable enough.
To avoid this we decided to change a database structure.

I would consider this thread as not solving my current problem, but solving
SQLite inability to work with large data sets.


On Thu, Oct 18, 2012 at 12:12 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Oct 17, 2012 at 11:58 AM, Ivan P <iva...@cloudberrylab.com> wrote:
>
> > Hello!
> >
> > I've got Out-Of-Memory error when delete records from a table that has
> > about 150,000,000 records.
> >
> > The table is created as:
> > CREATE TABLE differential_parts_temp (plan_id TEXT, [version_id] INT NOT
> > NULL, [page_index] INT NOT NULL, [md5low] INT64 NOT NULL, [md5high] INT64
> > NOT NULL);
> > CREATE INDEX differentialpartstemp_versionid ON differential_parts_temp
> > (version_id, plan_id);
> >
> > It has approx 150,000,000 records.
> > The VERY MOST (if not all) of those records have
> > plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43'
> >
> > The following request leads to "Out of Memory" error (the application
> eats
> > all the free memory up to 3.5 Gb , and then fails)
> > DELETE FROM differential_parts_temp WHERE
> > plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43'
> >
>
> In order to do a DELETE, SQLite first identifies every row that needs
> deleting.  It remembers the rowid of each such row in memory.  Normally
> this works fine, but it can give problems when you are trying to delete
> 150M rows, apparently.
>
> One possible work-around:
>
> CREATE TABLE new_dpt AS SELECT * FROM differential_parts_temp WHERE
> plan_id<>bf43c9ae-d681-4f2a-be19-0e0426db2b43';
> DROP TABLE differential_parts_temp;
> ALTER TABLE new_dpt RENAME AS differential_parts_temp;
>
> Another possible work-around:
>
> DELETE FROM differential_parts_temp WHERE rowid IN (SELECT rowid FROM
> differential_parts_temp WHERE
> plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43' LIMIT 100);
> -- repeat the previous statement 150 times, or until sqlite3_changes()
> returns zero.
>
>
>
> >
> > I tried different SQLite clients including the following precompiled
> > binary:
> > http://www.sqlite.org/sqlite-shell-win32-x86-3071401.zip
> >
> > Why the DELETE statement can eat so much memory?
> >
> > The total database size is 20 GBytes.
> > SQLite Expert shows the following DB properties:
> > auto_vacuum=none
> > automatic_index=on
> > cache_size=2000
> > case_sensitive_like=off
> > collation_list=[NOCASE], [RTRIM], [BINARY]
> > count_changes=off
> > default_cache_size=2000
> > empty_result_callbacks=off
> > encoding=UTF-8
> > foreign_keys=on
> > freelist_count=0
> > full_column_names=off
> > fullfsync=off
> > journal_mode=delete
> > journal_size_limit=-1
> > legacy_file_format=off
> > locking_mode=normal
> > max_page_count=1073741823
> > page_count=20719252
> > page_size=1024
> > read_uncommitted=off
> > recursive_triggers=off
> > reverse_unordered_selects=off
> > schema_version=27
> > secure_delete=off
> > short_column_names=on
> > synchronous=full
> > temp_store=default
> > temp_store_directory=""
> > user_version=0
> > wal_autocheckpoint=1000
> >
> > Thanks,
> > IP
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: Out-Of-Memory error when doing DELETE from a table with 150 million records

[Ivan P]

The operating system is Windows7 x64 Ultimate, 4 Gb RAM
I have not specified any PRAGMAs when run sqlite3.exe. The program was
launched normally (in non-elevated mode).
The database file is located on the local disk (C:) with a 95 GB of free
space

Here's how I launch this:
C:\temp\131418>sqlite3.exe problem.db
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> delete from differential_parts_temp where
plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43';
Error: out of memory
sqlite>

It works for about 10 minutes on my not loaded i5-2400 3.1Ghz, reaching by
this moment about 1.7Gb of memory in task manager, and then fails.



On Wed, Oct 17, 2012 at 8:44 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 17 Oct 2012, at 4:58pm, Ivan P <iva...@cloudberrylab.com> wrote:
>
> > Why the DELETE statement can eat so much memory?
>
> Because it doesn't delete each one row singly, doing all the file updates
> that are needed to delete that row, then move on to the next row.  If it
> did it would take an extremely long time to operate.  Nevertheless it is
> definitely not meant to ever crash in the way you're seeing.  It is meant
> to realise when its available memory is full, flush changes to disk, then
> carry on.
>
> > SQLite Expert shows the following DB properties:
>
> Some of these (e.g foreign_keys) are not properties of the database, but
> of the connection that SQLite Expert has opened to it, so they are not
> relevant to anything you've been doing in the shell tool.  So instead we
> ask the following questions:
>
> Which version of Windows are you running ?  Did you specify any PRAGMAs in
> the shell tool ?  Are you opening the file on a local disk or across a file
> sharing connection ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug report: Out-Of-Memory error when doing DELETE from a table with 150 million records

[Ivan P]

Hello!

I've got Out-Of-Memory error when delete records from a table that has
about 150,000,000 records.

The table is created as:
CREATE TABLE differential_parts_temp (plan_id TEXT, [version_id] INT NOT
NULL, [page_index] INT NOT NULL, [md5low] INT64 NOT NULL, [md5high] INT64
NOT NULL);
CREATE INDEX differentialpartstemp_versionid ON differential_parts_temp
(version_id, plan_id);

It has approx 150,000,000 records.
The VERY MOST (if not all) of those records have
plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43'

The following request leads to "Out of Memory" error (the application eats
all the free memory up to 3.5 Gb , and then fails)
DELETE FROM differential_parts_temp WHERE
plan_id='bf43c9ae-d681-4f2a-be19-0e0426db2b43'

I tried different SQLite clients including the following precompiled binary:
http://www.sqlite.org/sqlite-shell-win32-x86-3071401.zip

Why the DELETE statement can eat so much memory?

The total database size is 20 GBytes.
SQLite Expert shows the following DB properties:
auto_vacuum=none
automatic_index=on
cache_size=2000
case_sensitive_like=off
collation_list=[NOCASE], [RTRIM], [BINARY]
count_changes=off
default_cache_size=2000
empty_result_callbacks=off
encoding=UTF-8
foreign_keys=on
freelist_count=0
full_column_names=off
fullfsync=off
journal_mode=delete
journal_size_limit=-1
legacy_file_format=off
locking_mode=normal
max_page_count=1073741823
page_count=20719252
page_size=1024
read_uncommitted=off
recursive_triggers=off
reverse_unordered_selects=off
schema_version=27
secure_delete=off
short_column_names=on
synchronous=full
temp_store=default
temp_store_directory=""
user_version=0
wal_autocheckpoint=1000

Thanks,
IP
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB concatenation?

[Ivan Shmakov]

> Pavel Ivanov writes:

[...]

 >> Unfortunately, the string concatenation operator, when applied to
 >> two BLOB's, results in a text string instead of a BLOB, like:

 >> SELECT quote (X'5445' || X'5354'); => 'TEST'

 > Maybe the following?

 > SELECT quote(cast(X'5445' || X'5354' as blob));

Indeed, it seems to work.  Thanks!

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BLOB concatenation?

[Ivan Shmakov]

With substr (), it's possible to split a BLOB, like:

SELECT quote (substr (X'1337cafe', 3, 2));
  => X'CAFE'

However, how do I concatenate two blobs?

Unfortunately, the string concatenation operator, when applied
to two BLOB's, results in a text string instead of a BLOB, like:

SELECT quote (X'5445' || X'5354');
  => 'TEST'

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient usage of sqlite

[Ivan Shmakov]

> Baruch Burstein writes:

[…]

 > My resources are a bunch of sound and image files, level data files,
 > script files and other game data stuff.  Instead of distributing my
 > game with about 20-30 small (some very small) files, I thought I
 > would roll all the files into some kind of archive.  I started to
 > roll my own format, when it occurred to me that sqlite may be well
 > suited for this.

The ar(1) archive format [1] may also be considered.  In
particular, the Debian project distributes pre-built binaries
for its software in ar archives (known as Debian package files,
or .deb's, there), which consist of a metadata part
(control.tar.gz) and the packaged files themselves
(data.tar.gz.)

However, if the intent is to associate a variety of metadata
with each of the data files, using SQLite seems to me like a
more natural solution.

[1] http://en.wikipedia.org/wiki/Ar_(Unix)

 > Which brought me to wonder if storing 5-10 tables with some of them
 > having <500 bytes of data may be very inefficient.

Which kind of game data takes so little space, I wonder?

 > I don't want to substitute a 20K file for my <10K of files.  I know
 > this is not a lot, but it still bothers me, like what when I have a
 > game with 500M of files?  (you never know, it may happen!).  No
 > searching is needed (except once for the key to load a resource)

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transparent compression implementations for SQLite?

[Ivan Shmakov]

>>>>> Simon Slavin <slav...@bigfraud.org> writes:
>>>>> On 31 Dec 2011, at 4:56pm, Ivan Shmakov wrote:

 >> The integers could take up to 32 bits long, but I deem them likely
 >> to “cluster”, like, e. g.: 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 101, 101,
 >> 102, 102, 102, 103, 103, 768, 768, etc.  My guess is that such
 >> sequences should be quite compressible, but the point is that
 >> there'll only be a few such numbers per row, and millions of such
 >> rows in the table.

 > Thing is, an integer like 103 takes two bytes to store,

Those were given as examples only.  The real values are going to
use up to 28 bits (roughly, 4 octets) or so.

 > and one of those bytes is the byte that indicates what kind of value
 > is being stored.  So it's really only taking one byte per value.

Is it?  I mean, if I know for sure that I'd only ever need, say,
eight 32-bit integers per row, and pack them into a single BLOB
instead of having them stored as proper INTEGER's, won't I be
able to save a few bytes per row?  (For a performance penalty
when accessing these fields.  Also, I'd have to forget about
indexes on these, other than a UNIQUE one on the whole
BLOB-coded tuple, I guess.)

(With a few functions to unpack these values back out of BLOB's,
I'd even be able to CREATE a suitable VIEW.  Or may be a VIRTUAL
TABLE.)

 > And you can't improve on this by compressing individual values, only
 > by compressing at the table level.  And if you're compressing at
 > table level, making any changes is rather slow.

For now, I'm interested mostly in INSERT's.  (And SELECT's, of
course.)

 >> [snip]is there a way to determine the filesystem space occupied by a
 >> particular table, or index, in SQLite?  It now seems to me that the
 >> problem is different to what I've initially presumed.

 > Not inside SQLite's API functions.  You can do it by reading the file
 > format and multiplying the page size by how many pages are used.

ACK, thanks.  Do I understand it correctly that the
sqlite-analyzer (suggested elsewhere in this thread) does this
very thing?

TIA.

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transparent compression implementations for SQLite?

[Ivan Shmakov]

>>>>> Roger Binns writes:
>>>>> On 30/12/11 20:10, Ivan Shmakov wrote:

 >> The problem is that I have a table, each row holding something like
 >> 64 bytes of data (of BLOB and INTEGER types), which don't seem too
 >> compressible, but these rows themselves tend to be similar to each
 >> other, so that the table could still be compressed as a whole, or in
 >> chunks.

 > SQLite stores integers in a variable length encoding using (almost)
 > the minimum bytes necessary.  Consequently keeping your numbers as
 > small as possible will help.

The integers could take up to 32 bits long, but I deem them
likely to “cluster”, like, e. g.: 1, 1, 1, 1, 2, 2, 2, 3, 3, 3,
101, 101, 102, 102, 102, 103, 103, 768, 768, etc.  My guess is
that such sequences should be quite compressible, but the point
is that there'll only be a few such numbers per row, and
millions of such rows in the table.

 > You don't describe what your blobs are, but again encoding them in
 > the shortest length possible will result in less data and redundancy
 > making compression less significant.

On a second thought, the BLOB's are of little concern: there are
not as many of them as of the integers, and they're already in a
separate table, like:

CREATE TABLE "foo" (
"id"INTEGER PRIMARY KEY,
"blob"  BLOBNOT NULL);

CREATE UNIQUE INDEX "foo-unique"
ON "foo" ("blob");

However, this makes me wonder, is there a way to determine the
filesystem space occupied by a particular table, or index, in
SQLite?  It now seems to me that the problem is different to
what I've initially presumed.

 > The page you linked to is a pretty good writeup on the subject.  You
 > can get good compression using CEROD but that doesn't allow making
 > changes.  Other than that you'll need to put something together
 > yourself.

ACK.  I'd rarely really need either UPDATE or DELETE on the
table in question, so I could probably move the accumulated data
into a separate read-only and compressed database file once in a
while, though such a design seems rather complex (and fragile)
to implement.

 > You can compress your fields individually but then you'll only be
 > able to equality comparisons (not ordering or partial matches).
 > Using a precomputed dictionary can give good results even on short
 > fields.  Here is an example for English(ish) text:

 > https://github.com/antirez/smaz

 > Other than that you'll either need to work at the filesystem level or
 > the SQLite page level as the article describes.  Here is another
 > compressor to consider:

 > http://blosc.pytables.org/trac

 > But I suggest you start at the beginning with an understanding of the
 > SQLite file format:

 > http://www.sqlite.org/fileformat2.html

ACK.  Thanks!

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transparent compression implementations for SQLite?

[Ivan Shmakov]

>>>>> Simon Slavin writes:
>>>>> On 31 Dec 2011, at 7:11am, Ivan Shmakov wrote:

 >> Download the code and prebuilt DLL.

 > It includes both.  In other words, the vfs_compress.c file you asked
 > for is in there.

Indeed.  Thanks!

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transparent compression implementations for SQLite?

[Ivan Shmakov]

>>>>> Simon Slavin writes:
>>>>> On 31 Dec 2011, at 4:10am, Ivan Shmakov wrote:

 >> I wonder, if anyone has any experience with [1] (and where could one
 >> get the vfs_compress.c file, BTW?), or any other transparent
 >> compression implementations for SQLite?

 > Links to the code and a pre-build .dll are in the article, about
 > 2/3rds down the page.

That's strange.  For me, it reads like the following, with the
links to the DLL (which is of little use to me), the SQLite's
own download page and the amalgamation itself (both under
http://www.sqlite.org/) and http://zlib.net/.  Nowhere can I see
a link to vfs_compress.c.  (I've tried it both with Lynx and
Iceweasel, and I've also checked the (X)HTML code itself, but to
no avail.)

TIA.

--cut: 
http://blog.ashodnakashian.com/2011/09/sqlite-with-built-in-online-compression/ 
--

Download

The code holds the same copyright claims as Sqlite, namely none. The
code is experimental. Use it at your own risk.

[78]Download the code and prebuilt DLL. This sqlite3.dll is version
3.7.7.1 amalgamation created with the default settings/flags from
the amalgamation created from original sources by the original
configure and make files. The compression code is added and it’s
built using VS2010 Sp1 and statically liked to the runtime
libraries, as such it has no dependencies.

Building

To build the code, first [79]download a recent Sqlite version. The
[80]3.7.7.1 amalgamation is perfect. The [81]latest Zlib must also
be downloaded and built.

Add the Zlib headers to the include path, copy the vfs_compress.c
file next to sqlite sources and build. Next, build sqlite3.c
amalgamation (or the original sources) and link the binaries of
sqlite3, vfs_compress and Zlib to create the executable.

…

References

…
  78. 
http://blog.ashodnakashian.com/wp-content/uploads/2011/09/sqlite3_compress.zip
  79. http://www.sqlite.org/download.html
  80. http://www.sqlite.org/sqlite-amalgamation-3070701.zip
  81. http://zlib.net/
…

--cut: 
http://blog.ashodnakashian.com/2011/09/sqlite-with-built-in-online-compression/ 
--

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] transparent compression implementations for SQLite?

[Ivan Shmakov]

I wonder, if anyone has any experience with [1] (and where could
one get the vfs_compress.c file, BTW?), or any other transparent
compression implementations for SQLite?

The problem is that I have a table, each row holding something
like 64 bytes of data (of BLOB and INTEGER types), which don't
seem too compressible, but these rows themselves tend to be
similar to each other, so that the table could still be
compressed as a whole, or in chunks.

TIA.  And HNY.

[1] 
http://blog.ashodnakashian.com/2011/09/sqlite-with-built-in-online-compression/

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create sqlite3_value * from scratch?

[Ivan Shmakov]

>>>>> Pavel Ivanov writes:
>>>>> On Wed, Oct 12, 2011 at 11:12 AM, Ivan Shmakov wrote:

[…]

 >> Consider, e. g.:

 >> sqlite3_value *a
 >>   = sqlite3_int64_value (1);
 >> assert (a != 0);
 >> sqlite3_value *b
 >>   = sqlite3_text_value (-1, "qux");
 >> sqlite3_value *c
 >>   = sqlite3_blob_value (blob_size, blob);
 >> assert (b != 0);
 >> int r;
 >> r = db_triv_exec_bound (db, sql_1, a, b, 0);
 >> assert (r == SQLITE_OK);

[…]

 > You can do absolutely the same thing but use your_own_value* instead
 > of sqlite3_value*.  Why stick with SQLite's internal data structures?

No special reason, but it would have saved me some work.  Not to
mention that it seems to me a bit like a duplicate effort.

Note also that such a code would've to be updated should a new
type be introduced to SQLite (however unlikely, it still may
happen.)

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create sqlite3_value * from scratch?

[Ivan Shmakov]

>>>>> Igor Tandetnik writes:
>>>>> Ivan Shmakov <i...@gray.siamics.net> wrote:

 >> I wonder, is it possible to create sqlite3_value * from scratch
 >> from within SQLite library's user code?

 > There's a circuitous route that leads there.  You can prepare a
 > statement of the form "select ?;", bind the parameter with one of
 > sqlite3_bind_* functions, then step it once, and retrieve the value
 > right back with sqlite3_column_value.  Note that the resulting
 > sqlite3_value is "unprotected" (see
 > http://sqlite.org/c3ref/value.html) and can only be used in limited
 > ways.  And of course it's only valid until the "select ?;" statement
 > is reset or finalized.

Unfortunately, the latter clearly prevents the kind of interface
I've had in mind (below.)

 >> Given some way to construct a sqlite3_value wrapping object, I
 >> could instead rely on sqlite3_bind_value () alone, thus
 >> eliminating the necessity of type specifiers in the interface.

 > Well, a hypothetical API that constructs sqlite3_value from raw data
 > would have to take the type specifier anyway, wouldn't it?  You would
 > just be moving the same logic to another place.

Yes.  But this still may make code clearer, and, occasionally,
also more concise.  Consider, e. g.:

   sqlite3_value *a
 = sqlite3_int64_value (1);
   assert (a != 0);
   sqlite3_value *b
 = sqlite3_text_value (-1, "qux");
   sqlite3_value *c
 = sqlite3_blob_value (blob_size, blob);
   assert (b != 0);
   int r;
   r = db_triv_exec_bound (db, sql_1, a, b, 0);
   assert (r == SQLITE_OK);
   r = db_triv_exec_bound (db, sql_2, b, c, 0);
   assert (r == SQLITE_OK);
   r = db_triv_exec_bound (db, sql_3, c, a, 0);
   assert (r == SQLITE_OK);
   sqlite3_value_free (a);
   sqlite3_value_free (b);
   sqlite3_value_free (c);

And I see no way to implement my_sqlite3_int64_value () using
the identity SELECT (above), as it'd require
my_sqlite3_value_free () to find the prepared statement
associated with the value (to call sqlite3_finalize () on it, so
to avoid a memory leak), and I know of no documented way of
doing that.

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] create sqlite3_value * from scratch?

[Ivan Shmakov]

I wonder, is it possible to create sqlite3_value * from scratch
from within SQLite library's user code?

The idea is as follows.  The current dbtriv.c [1] code has a
db_triv_exec_bound () function, used roughly as follows:

   /* sqlite3 *db; */
   const char *sql
 = ("INSERT INTO foo (bar, baz)"
" VALUES ($1, $2);");
   int r
 = db_triv_exec_bound (db, sql,
   SQLITE_INTEGER, (sqlite3_int64)1,
   SQLITE3_TEXT,   -1, "qux",
   -1);
   assert (r == SQLITE_OK);

This function will look up a cached prepared statement for the
SQL code passed, creating and caching one if none's found, and
then bind the parameters with sqlite3_bind_int64 (),
sqlite3_bind_text (), etc., matching the type specifiers passed.

Given some way to construct a sqlite3_value wrapping object, I
could instead rely on sqlite3_bind_value () alone, thus
eliminating the necessity of type specifiers in the interface.

TIA.

[1] https://gitorious.org/e2dis/e2dis-devel/blobs/master/lib/dbtriv.c

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DISTINCT vs. UNIQUE INDEX, and NOT EXISTS vs. EXCEPT

[Ivan Shmakov]

>>>>> Jim Morris writes:
>>>>> On 10/6/2011 10:43 PM, Ivan Shmakov wrote:
>>>>> Jim Morris writes:

[…]

 >>> INSERT INTO fts3_table (a,b,c)
 >>> SELECT 'an A','a B','a C'
 >>> WHERE NOT EXISTS
 >>> (SELECT DISTINCT a,b,c
 >>> FROM fts3_table
 >>> WHERE a='an A' AND b='a B' AND c='a C');

 >> However, I wonder, would the following (slightly more concise) query
 >> imply any performance loss in comparison to the one above?

 >> INSERT INTO fts3_table (a, b, c)
 >> SELECT 'an A', 'a B', 'a C'
 >> EXCEPT SELECT DISTINCT a, b, c
 >>FROM fts3_table;

 >> Also, I'm curious if DISTINCT may cause any performance loss in the
 >> case that the columns in question are constrained by an UNIQUE
 >> index?  Like:

 >> CREATE UNIQUE INDEX "foo-unique"
 >> ON "foo" (a, b, c);

 > I don't know for sure.  You would need to do some testing to
 > determine performance issues.  I wouldn't use distinct if the values
 > are already guaranteed to be unique

As per EXPLAIN, SELECT DISTINCT indeed doesn't take the
uniqueness constraint into consideration.

 > or in a not exits clause since it may have additional overhead.

Surprisingly enough, when used in the EXCEPT part of the query,
DISTINCT has no effect on the VM code (as shown by EXPLAIN)!

At a first glance, the code for the NOT EXISTS variant (sans
INSERT; hopefully it doesn't matter) seems simpler (one
operation less in total, one less Next, has no OpenEphemeral's,
and IdxGE instead of IdxInsert and IdxDelete):

addr  opcode p1p2p3p4 p5  comment  
  -        -  --  -
0 Trace  0 0 000   
1 Goto   0 21000   
2 OpenRead   0 2 1 1  00   
3 Rewind 0 19000   
4 Integer0 2 000   
5 Integer1 3 000   
6 OpenRead   2 3 0 keyinfo(1,BINARY)  00   
7 Column 0 0 400   
8 IsNull 4 14000   
9 SeekGe 2 144 1  00   
10IdxGE  2 144 1  01   
11Integer1 2 000   
12IfZero 3 14-1   00   
13Next   2 10000   
14Close  2 0 000   
15If 2 18100   
16Column 0 0 700   
17ResultRow  7 1 000   
18Next   0 4 001   
19Close  0 0 000   
20Halt   0 0 000   
21Transaction0 0 000   
22VerifyCookie   0 2 000   
23Transaction1 0 000   
24VerifyCookie   1 1 000   
25TableLock  1 2 0 temp.staged00   
26TableLock  0 2 0 foo00   
27Goto   0 2 000   

The code for the EXCEPT variant is:

addr  opcode p1p2p3p4 p5  comment  
  -        -  --  -
0 Trace  0 0 000   
1 OpenEphemeral  2 1 0 keyinfo(1,BINARY)  00   
2 Goto   0 22000   
3 OpenRead   1 2 1 1  00   
4 Rewind 1 9 000   
5 Column 1 0 100   
6 MakeRecord 1 1 200   
7 IdxInsert  2 2 000   
8 Next   1 5 001   
9 Close  1 0 000   
10OpenRead   0 2 0 2  00   
11Rewind 0 15000   
12 

Re: [sqlite] How to design this table?

[Ivan Shmakov]

> Simon Slavin writes:
> On 9 Oct 2011, at 3:57am, 张一帆 wrote:

 >> i have some data like "a and b or c ...",there will be a word 'and'
 >> or 'or' which means the Logical relations between each item.

 > If you have "a and b or c" does that mean

 > (a and b) or cOR
 > a and (b or c)?

 > How does your software know ?

The boolean AND and OR operations are often compared to the
usual arithmetics' × and +.  Therefore, a AND b OR c is akin to
a × b + c, and the precedence rules (borrowed from arithmetics)
will make that (a × b) + c.

And SQLite, among many others, behaves just like that:

$ sqlite3 :memory: 'SELECT 0 AND  1 OR 1' 
1
$ sqlite3 :memory: 'SELECT 0 AND (1 OR 1)' 
0
$ 

I guess that it just follows the standard.

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ensuring uniqueness of tuples spanning across multipletables?

[Ivan Shmakov]

> Jim Morris writes:

 > The recent thread may relate: "[sqlite] Is there an efficient way to
 > insert unique rows (UNIQUE(a, b, c)) into an fts3 virtual table?"

 > INSERT INTO fts3_table (a,b,c)
 > SELECT 'an A','a B','a C'
 > WHERE NOT EXISTS
 > (SELECT DISTINCT a,b,c
 > FROM fts3_table
 > WHERE a='an A' AND b='a B' AND c='a C');

 > The above SQL could be adapted to your schema.  As mentioned, the
 > performance will be slower than a straight insert.

Thanks.  It's a solution not quite for the problem I'm having,
but I'll probably stick to it (and to the denormalized schema it
imples.)

However, I wonder, would the following (slightly more concise)
query imply any performance loss in comparison to the one above?

INSERT INTO fts3_table (a, b, c)
SELECT 'an A', 'a B', 'a C'
EXCEPT SELECT DISTINCT a, b, c
   FROM fts3_table;

Also, I'm curious if DISTINCT may cause any performance loss in
the case that the columns in question are constrained by an
UNIQUE index?  Like:

CREATE UNIQUE INDEX "foo-unique"
ON "foo" (a, b, c);

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ensuring uniqueness of tuples spanning across multipletables?

[Ivan Shmakov]

>>>>> Igor Tandetnik <itandet...@mvps.org> writes:
>>>>> Ivan Shmakov <i...@gray.siamics.net> wrote:

 >> This structure is, obviously, could just as well be represented
 >> with, e. g.:

 >> CREATE TABLE "foo" (
 >> "key"   INTEGER PRIMARY KEY,
 >> "value" INTEGER NOT NULL,
 >> "value-1"   INTEGER,
 >> …
 >> "value-N"   INTEGER);

 > Or else with this:

 > CREATE TABLE "foo" (
 > key INTEGER PRIMARY KEY,
 > value   INTEGER NOT NULL);

 > create table fooDetails(
 > key INTEGER PRIMARY KEY
 > REFERENCES "foo" (key),
 > L integer not null,
 > value integer not null);

 > That's what a fully normalized schema would look like for your data.

Indeed.  But I don't quite understand how does it make my task
any easier?  I fail to see how can I ensure that the
(value _1, …, value _N) tuple is unique.  (I. e., that there's
an /invertible/ mapping of keys to value tuples.)

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ensuring uniqueness of tuples spanning across multiple tables?

[Ivan Shmakov]

Well, this case is somewhat weird.  I have a number of tables
like:

PRAGMA "foreign_keys" = 1;

CREATE TABLE "foo-L" (
key INTEGER PRIMARY KEY
REFERENCES "foo" (key),
value   INTEGER NOT NULL);

Which are tied to a single table, like:

CREATE TABLE "foo" (
key INTEGER PRIMARY KEY,
value   INTEGER NOT NULL);

This structure is, obviously, could just as well be represented
with, e. g.:

CREATE TABLE "foo" (
"key"   INTEGER PRIMARY KEY,
"value" INTEGER NOT NULL,
"value-1"   INTEGER,
…
"value-N"   INTEGER);

(I. e., by abandoning the NOT NULL constraint from the
respective columns.)  For which I could then specify an
uniqueness constraint:

CREATE UNIQUE INDEX "foo-unique"
ON "foo" ("value", "value-1", …, "value-N");

Now, I'm somewhat concerned that the table above may get overly
sparse at times, which makes me wonder if I could establish an
equivalent constraint over the original multi-relation
structure, as described above?

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max() with LIMIT

[Ivan Shmakov]

>>>>> Igor Tandetnik writes:
>>>>> Ivan Shmakov <i...@gray.siamics.net> wrote:
>>>>> Tobias Vesterlund writes:

 >>> Is it possible to get the highest value in a "limited column" when
 >>> using LIMIT?

 >> Sure.

 >> SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10);

 > This only works by accident.  There's no requirement that the
 > subselect return rows in any particular order.  It can, in principle,
 > choose any ten rows.

There wasn't the requirement that the query should be
unambiguous in its interpretation in the OP, either.

However, yes, almost anytime LIMIT is used, it has to be
complemented with ORDER BY for a definite result.

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max() with LIMIT

[Ivan Shmakov]

> Tobias Vesterlund writes:

[…]

 > If I do SELECT max(id) FROM t; it will return 99.

 > If I do SELECT id FROM t WHERE id > 0 LIMIT 10; it will return
 > 1,2,3,4,5,6,7,8,9,10

 > But If I do SELECT max(id) FROM t WHERE id > 0 LIMIT 10; it will
 > return 99.

 > My logic, which may be flawed in this case, tells me the third SELECT
 > should return 10 and not 99.

LIMIT applies /after/ the aggregation.  Consider, e. g., using
GROUP BY, and then selecting no more than 10 groups' maximums.

 > Is it possible to get the highest value in a "limited column" when
 > using LIMIT?

Sure.

SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10);

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Ivan Shmakov]

> Pete Helgren writes:

 > I may end up going this direction, at the moment I am not having much
 > luck with the conditional copy in Busybox.  Your suggestion:

 > cp -n newdatabase.db /data/newdatabase.db

 > Isn't supported in the version of Busybox that I am running.  Also
 > the script example I tried:

 > if  [ -f /data/newdatabase.db];
 > then
 > echo "Nothing to do, database exists"
 > else
 > cp newdatabase.db /data/newdatabase.db
 > fi

 > delivers the error  [:missing]

The primary token delimiter in POSIX Shell is space.  Hence, the
following line:

   if  [ -f /data/newdatabase.db];

Is understood as: “check if the file ‘/data/newdatabase.db]’
exists” (note the closing bracket), and it certainly lacks a
closing bracket for the ‘test’ (AKA ‘[’) command.

The solution would be as follows:

 - if  [ -f /data/newdatabase.db];
 + if  [ -f /data/newdatabase.db ];

OTOH, the when the ‘test’ form of the command is used, closing
bracket is not necessary, thus:

   if  test -f /data/newdatabase.db ;

Please also consider joining the news:comp.unix.shell newsgroup
(e. g., on Aioe, nntp://aioe.org/comp.unix.shell/), as there're
quite a few folks familiar with the arcane art of Unix Shell
programming.  (AFAIK, Thunderbird has the support for the
Internet News service.)

[…]

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite_temp_master vs. .dump (now a library, too!)

[Ivan Shmakov]

>>>>> Roger Binns writes:
>>>>> On 08/16/2011 04:59 PM, Ivan Shmakov wrote:

[…]

 >> Also, are the .dump and .read commands implemented as part of
 >> the sqlite3 binary, or are they part of the library?

 > They are part of the standalone shell (ie not the library).  The
 > shell source code is under the same license as the rest of SQLite (ie
 > as public domain as possible) so you are free to make a copy and do
 > whatever you want with it.

I've just extracted all the respective routines out of shell.c
(sqlite-autoconf-3070701.tar.gz), added a couple of wrappers,
and posted the resulting code in news:alt.sources [1].

However, .dump doesn't seem examine sqlite_temp_master, which is
somewhat unfortunate, given that it was my intent to use it as a
debugging aid for my Image reassembly tool (imrt; to be part of
the e2dis [2] suite.)

I guess that I could simply substitute sqlite_master for
sqlite_temp_master (perhaps conditionally), and get what I want?
(Possibly doing s/CREATE TABLE/CREATE TEMP TABLE/ as well.)

Anything else to consider?

TIA.

[1] news:86vctigdga@gray.siamics.net
nntp://aioe.org/alt.sources/732

http://groups.google.com/group/alt.sources/browse_thread/thread/19146b6db22067a4
[2] https://gitorious.org/e2dis

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL ROWNUM option is failed - Want to Read records in chunks from table

[Ivan Shmakov]

> Tarun  writes:

[…]

 > I planned to execute query that works on SQL ROWNUM option

 > "select * from employee2 where rownum > 1 and rownum < 2"

Perhaps:

SELECT * FROM employee2 ORDER BY oid LIMIT 1 OFFSET 1 

 > but i m getting error from sqlite3 that "no such column: rownum"

 > Please help me to achieve "read records in chunks". I am stuck.
 > Thanks in advance.

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] type of a value bound by sqlite3_bind_blob ()?

[Ivan Shmakov]

>>>>> Ivan Shmakov writes:
>>>>> Roger Binns writes:

[…]

 >> Consequently if you had a trigger pulling a stunt like this, your
 >> code could try to insert a blob and silently (wrongly) end up with a
 >> string.  SQLite won't even complain if the blob isn't a valid text
 >> encoding producing an invalid string.

 > I don't have any triggers (at least, it wasn't my intent to add
 > them.)  My code is roughly as shown below.

 > And I don't seem to understand where's the problem.

Silly mistake on my part, as I've just found.

Namely, I've had /two/ functions to alter the table in question.
One of them INSERT's the tuple, and uses sqlite3_bind_blob ().
The other, that UPDATE's the tuple, however, uses
sqlite3_bind_text ().

Before I saw that there's sound support for blobs in the current
SQLite, I've planned to use Base64 for these fields; after, I've
changed one of the functions, but not the other.

And yes, code duplication is clearly a bad thing.

Now that the discrepancy is fixed, the problem is gone.

Thanks.

[…]

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] type of a value bound by sqlite3_bind_blob ()?

[Ivan Shmakov]

>>>>> Roger Binns writes:
>>>>> On 08/17/2011 09:25 PM, Ivan Shmakov wrote:

 >> Somehow, I've assumed that sqlite3_bind_blob () will bind a
 >> parameter to a blob.

 > It does.  There are no affinity rules that will cause otherwise.

 > There are some operations that cause blobs to be silently promoted to
 > strings.  IMHO these are egregious errors in SQLite since a bucket of
 > bytes cannot be turned into characters unless you know the encoding
 > which SQLite doesn't.  Here is an example:

 > sqlite> select X'616263' || 'd';
 > abcd
 > sqlite> select typeof(X'616263' || 'd');
 > text

As a matter of personal preference, I'd be calling the code
above “an error.”  It's not meaningful to my eye, and I'd likely
avoid it in my code on the basis that even if it has some
interpretation in the language, it'd be just too much a mental
strain to remember such.

 > Consequently if you had a trigger pulling a stunt like this, your code
 > could try to insert a blob and silently (wrongly) end up with a string.
 > SQLite won't even complain if the blob isn't a valid text encoding
 > producing an invalid string.

I don't have any triggers (at least, it wasn't my intent to add
them.)  My code is roughly as shown below.

And I don't seem to understand where's the problem.

Also, I've built a simpler example following the same scheme
(except the use of sqlite3_bind_parameter_index ()), and it
doesn't seem to cast blobs to strings.

Any suggestions?

TIA.

   const char *const sql
 = ("INSERT"
" INTO \"chunk\" (\"id\", \"length\", \"sha1\", \"sha256\")"
" VALUES ($id, $length, $sha1, $sha256)");

   {
 ix_sha1
   = sqlite3_bind_parameter_index (st, "$sha1");
 assert (ix_sha1 > 0);
   }

   {
 int r
   = sqlite3_reset (st);
 assert (r == SQLITE_OK);
   }

   {
 int r
   = sqlite3_bind_blob (st, ix_sha1,
sha1, CHUNK_DB_SHA1_LEN,
SQLITE_TRANSIENT);
 assert (r == SQLITE_OK);
   }

   {
 int r
   = sqlite3_step (st);
 assert (r == SQLITE_DONE);
   }

[…]

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] type of a value bound by sqlite3_bind_blob ()?

[Ivan Shmakov]

>>>>> Roger Binns writes:
>>>>> On 08/16/2011 04:59 PM, Ivan Shmakov wrote:

 >> In the sqlite3's .dump command's output, the binary blobs may either
 >> be represented as hexadecimal X''-literals, or as text strings.

[…]

 > I suggest using typeof on the data you think is blobs to verify what
 > type they really are.

The values in question are indeed of mixed type.  Thanks!

Yet, it makes me wonder, how the following bit of code could
ever have resulted in a value of the TEXT type being bound?

  {
int r
  = sqlite3_bind_blob (st, ix_sha1,
   sha1, CHUNK_DB_SHA1_LEN,
   SQLITE_TRANSIENT);
assert (r == SQLITE_OK);
  }

Somehow, I've assumed that sqlite3_bind_blob () will bind a
parameter to a blob.

(It's SQLite 3.7.3 debian 1.)

 >> Also, are the .dump and .read commands implemented as part of
 >> the sqlite3 binary, or are they part of the library?

 > They are part of the standalone shell (ie not the library).  The
 > shell source code is under the same license as the rest of SQLite (ie
 > as public domain as possible) so you are free to make a copy and do
 > whatever you want with it.

ACK.  Thanks.

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] forcing X'' literals in sqlite3's .dump?

[Ivan Shmakov]

In the sqlite3's .dump command's output, the binary blobs may
either be represented as hexadecimal X''-literals, or as text
strings.

I wonder, how do I force sqlite3(1) to exclusively use the X''
representation?

Also, are the .dump and .read commands implemented as part of
the sqlite3 binary, or are they part of the library?

TIA.

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serialising memory DB

[Ivan Voras]

--- "Brandon, Nicholas (UK)"
<[EMAIL PROTECTED]> wrote:

> 
> Is there a way to serialise a memory DB so that it
> can be sent over a
> network socket and re-opened on another computer as
> a memory DB?
> 
> I was thinking of using a SQLite table as a
> configuration system (like a
> .ini file) that can be sent over the network.
> Obviously you could use a
> file based DB instead and pass that around but I
> would like to be able
> to use memory DB for computers that either don't
> have hard drives or an

I don't know if it will help you, but I've been
working on something almost, but not completely unlike
that here: http://sqlcached.sourceforge.net

It's work in progress, it may explode if looked at
harshly, etc. :)

> OS that doesn't handle POSIX file operations (fopen
> et al).

If you have an OS that doesn't offer fopen() & similar
functions at compile time, you'll probably have a hard
time compiling anything using sqlite...

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] How to get the lastest version of SQLITE with PHP ???

[Ivan Voras]

--- Night Media LTD <[EMAIL PROTECTED]> wrote:

> Hello John, and thanks for the idea. 
> 
>  
> 
> But PHP DB is only an abstraction layer, it need a
> sqlite extention to work
> with sqlite database. And this extension only exist
> at present in pecl of
> The sqlite 2. 

How did you come to this conclustion? Your own post
says:

"""
I got 

 

Changelog for PDO_SQLITE 

Release What has changed? 

1.0.1 - Updated libsqlite in ext/pdo_sqlite to 3.2.8.
(Ilia) 

"""

So, PDO_SQLITE supports sqlite 3.2.8.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] How to get the lastest version of SQLITE with PHP ???

[Ivan Voras]

--- Night Media LTD <[EMAIL PROTECTED]> wrote:

> Its incredible that a big language as PHP cannot
> have the lastest version of
> sqlite running with... 

Maybe the time has come to ask you why do you need the
latest version of sqlite? Version 2.8 is widely used
and as far as I can see works ok and bugs get solved.
Version 3.2 in PHP's tree is not that old and (also,
as far as I know) database file format of any 3.x
version is the same?

People tend not to replace things that work - that's
why many projects (including my own) are still using sqlite2.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] How to get the lastest version of SQLITE with PHP ???

[Ivan Voras]

--- Night Media LTD <[EMAIL PROTECTED]> wrote:
> 
> Release What has changed? 
> 
> 1.0.1 - Updated libsqlite in ext/pdo_sqlite to
> 3.2.8. (Ilia) 

>From the source of config.m4 in ext/pdo_sqlite:

--with-pdo-sqlite[=DIR]   PDO: sqlite support. DIR is
the sqlite base directory, the bundled sqlite is used
by default])

On my platform (FreeBSD) the bundled version is not
used by default so that's how I came to the wrong
conclusion. If you can rebuild it from scratch, maybe
you also can exclude the bundled library.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] R�f. : Re: [sqlite] How to get the lastest version of SQLITE with PHP ???

[Ivan Voras]

--- Night Media LTD <[EMAIL PROTECTED]> wrote:

>  
>   
> Already tested but this not worked.
>   
> Because in fact pdo_sqlite look have is own sqlite
> library into
> Php_source_dir/ext/pdo_sqlite/sqlite

I was talking about the PECL PHP extension. Don't know
about the bundled one.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] SQLite :memory: performance difference between v2 and v3?

[Ivan Voras]

> In any case we seem to be settling to a long term
> sustained rate of 
> about 40 flushes per second for WinXP. Since SQLite
> is getting only 10 
> inserts per second, it seems to me that SQLite must
> be doing about 4 
> flushes per insert. Does that seem right to you
> Richard?

For what it's worth, it's maybe slightly worse on
FreeBSD. Here's a sample of iostat for the (original)
C benchmark with sqlite2:

  tty ad0 cpu
 tin tout  KB/t tps  MB/s  us ni sy in id
   0   43 11.71 661  7.56  12  0  5  2 81
   0  129 13.65 457  6.08   2  0  1  1 96
   0   43 13.16 662  8.52   2  0  6  2 91
   0   43 13.21 767  9.89   2  0  8  2 89
   0   43 13.61 591  7.86   5  0  2  1 93
   0   43 13.19 523  6.74   0  0  5  2 93
   0   43 13.21 520  6.71   1  0  3  1 95
   0   43 13.45 453  5.95   1  0  6  0 93
   0   43 13.26 663  8.59   2  0  4  1 93
   0   43 13.17 410  5.27   2  0  2  2 95
   0   43 13.52 495  6.53   2  0  2  0 97
   8   43 13.41 565  7.40   2  0  6  1 91

I/O transactions/s rate probably averages to something
like 550, and the benchmark reported ~90 queries/s, so
the rate is around 6 I/O ops per INSERT.

sqlite3 is similar, but with a twist:

  tty ad0 cpu
 tin tout  KB/t tps  MB/s  us ni sy in id
   0   43 11.48 560  6.28   2  0  4  2 92
   0  129 11.79 695  8.01   2  0  5  1 92
   0   43 12.12 405  4.79   5  0  5  0 91
   0   43 12.57 483  5.92   1  0  7  0 93
   8   43 12.66 384  4.74   2  0  3  2 94
   0   43 12.00 416  4.87   1  0  4  0 95
   0   43 11.93 756  8.81   2  0  3  2 93
   0   43 12.04 696  8.19   3  0  3  1 93
   8   43 12.36 677  8.17   1  0  3  1 95
   8   43 12.38 680  8.22   2  0  5  0 94
   0   43 11.74 554  6.36   0  0  1  1 98
   0   43 12.16 657  7.80   1  0  2  1 96
   0   43 12.16 789  9.37   2  0  3  1 95
   0   43 12.36 573  6.92   2  0  5  2 92

The I/O transactions/s rate is similar (or at least
not significantly lower) but the transactions are a
bit smaller. Measured ~65 INSERTs/sec. so it's ~8 I/O
transactions per INSERT. Exact statistics calulations
are left as an excercise for the reader :)

(interpretation of cpu stats: time spent in file
system & disk is "sy" (=system) + "in" (=interrupt);
id=idle, us=user)


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] SQLite :memory: performance difference between v2 and v3?

[Ivan Voras]

--- Dennis Cote <[EMAIL PROTECTED]> wrote:

> All,
> 
> More mysteries. To investigate this low insert
> performance under WinXP I 
> wrote a simple test program that loops writing one
> character to a file 
> and then flushing the file. The strange thing is
> that it seems to 
> alternate between two different run times as shown
> below. This is for 6 
> consecutive runs.

Don't know for sure, but I can offer some educated
guesses for the scattering of results:

- NTFS is a fairly complex file system (more complex
than traditional FS-es), with several tables into
which file data must be written and cross linked, as
well as a data journal. The scattering of locations
for these journals means that seek times are
different, depending where the data is laid out on
disk. In particular because there's a journal,
consecutive program runs never actually write to the
same place on the disk even if it seems so to the
application. The results you have seen (alternating
between two values) can also be provoked on complex
database systems (in particular PostgreSQL) with
simple benchmarks (e.g. pgbench) on relativly simple
non-journaled file systems (such as UFS) when one
benchmark run nearly fills a write-ahead log and the
log gets processed/commited in the next run.

- Windows has many background disk users / writers -
Explorer, registry and other components are known to
"wake up" periodically and write their data (whatever
it is).


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] SQLite :memory: performance difference between v2 and v3?

[Ivan Voras]

> > profile results with dirty pages fix:
> > 
> >   %   cumulative   self  self
> total
> >  time   seconds   secondscalls  ms/call 
> ms/call  name
> >   9.20  0.31 0.31   100011 0.00
> 0.03  sqlite3VdbeExec
> >   7.42  0.56 0.25  4849544 0.00
> 0.00  sqlite3VdbeRecordCompare
> >   6.68  0.79 0.23 10487713 0.00
> 0.00  parseCellPtr

Just curious: what did you use to generate the
profile? (i.e. which profiler?)


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] Re: SQLite :memory: performance difference between v2 and v3?

[Ivan Voras]

--- Dennis Cote <[EMAIL PROTECTED]> wrote:

> I made a modified version of your test program. It
> uses the preferred 
> prepare/bind/step/finalize call family to execute
> the SQL. This way it 
> only parses the SQL once, and executes it many
> times, each time using 
> different parameter values. I created a similar
> program using the 
> equivalent sqlite version 2 calls. Both programs are
> included below.

There is a reason my program is like that: in
production, queries will have no correlation and
therefore cannot use statement preperation or
transactions (I need the "autocommit" mode).


> * transactions speed up memory inserts on version 3
> by a factor of 10, 
> but slow down memory insert on version 2 by about
> 6%.

I think this behaviour is conter-intuitive for a
memory database - there's no need for locking and IMHO
(not an expert :) ) these should run almost the same
as without transactions.

For now, I'll use sqlite2 for my application.

Thanks to everyone for interest and feedback!

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] SQLite :memory: performance difference between v2 and v3?

[Ivan Voras]

> Does it only happen with a :memory: database or even
> if you use a
> file?

When using a file the difference is much smaller, but
still there: ~70 qps with sqlite3, ~90 qps with
sqlite2.




__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] SQLite :memory: performance difference between v2 and v3?

[Ivan Voras]

--- Erik Jensen <[EMAIL PROTECTED]> wrote:

> Does it only happen with a :memory: database or even
> if you use a
> file?

I'll test it this evening (but I need a :memory:
database for my application)

> With version of the v3 lib did you use?

It's 3.3.5.

> I'm asking because i noticed a similar performance
> drop (factor 10-15)
> when i wanted to upgrade my application from 3.2.1
> to 3.3.5
> 
> Regards,
> Eric
> 
> 


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

[sqlite] Re: SQLite :memory: performance difference between v2 and v3?

[Ivan Voras]

> Testing is difficult to do correctly. As several
people 
> noted on this list just today the first time they
ran 
> a query it had much different
> performance than subsequent
> runs of the query. Did you run these tests more 
> than one time? What's your environment?

This is not a "first time" problem - it's a "first
100,000 times" problem :)

I tried the same thing many times, even under
different languages (C, PHP) and two different
machines and always get the same behaviour (sqlite2
much faster than sqlite3). This is on FreeBSD 6.

Since the attachments got stripped, here's pasted code
for the test program:

#include 
#include 
#include 

#include 

int main(int argc, char** argv) {
int i, count = 10, t, time1, time2;
sqlite3 *db;
char *msg;
char sql[200];

if (sqlite3_open(":memory:", ) != 0) {
fprintf(stderr, "Error: %s\n",
sqlite3_errmsg(db));
exit(1);
}

sqlite3_exec(db, "CREATE TABLE cache (key varchar
not null, value varchar not null, time integer not
null, primary key(key))", NULL, NULL, NULL);
sqlite3_exec(db, "CREATE INDEX cache_time ON
cache(time)", NULL, NULL, NULL);

t = time1 = time(NULL);
for (i = 0; i < count; i++) {
sprintf(sql, "INSERT INTO cache(key, value,
time) VALUES ('key%d', 'value %d', %d)", i, i, t);
if (sqlite3_exec(db, sql, NULL, NULL, ) !=
SQLITE_OK) {
fprintf(stderr, "sqlite error %s\n", msg);
exit(1);
}
}
time2 = time(NULL);

printf("%0.1f qps\n", (float)count /
(time2-time1));
return 0;
}

The test program for sqlite2 is the same, only
sqlite3_* is replaced with sqlite_* (and different
libraries linked, of course). Granularity of time() is
coarse, but it doesn't matter here - performance
difference is in order of magnitude.




__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

[sqlite] why subselect is better then join ?

[ivan]

hi

I noticed that function sqlite3_step() is very slow when i want to fetch
query with join(s).

For example :

create same table t (a int, b int, c int ... );

and insert into t large numbers of records, (about  1)

time of fetching select * form t limit 0,100 is this same what select
* from t limit 9000,100;

but when i want to use "join", like : select * from t join t2 on
(t2.id=t.a) join t3 on (t3.id=t.b) ... , there is big difference
between fetching this query with limit 0,100 and limit 9000,100;

anyway using subselects insted of joins take this same time with both
limits.

Is it a bug ?

Thanks
 Iv

[sqlite] sth more then step()

[ivan]

Hi,

Is there any way to skip rows in prepared statement and jump to next, sth
like moveto (in sqlite3) ??

Why I have to use LIMIT in SELECT when I need use only OFFSET ?

Thanks
 Iv

Re: [sqlite] Newbie help

[Ivan Dimitrov]

yes, it is works with LccWin32 ! you have  to Add all files to Project & 
build!

Regards
I. Dimitrov

From: Ulrik Petersen <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Newbie help
Date: Tue, 18 Jan 2005 13:02:31 +0100
MIME-Version: 1.0
Received: from sqlite.org ([67.18.92.124]) by mc12-f35.hotmail.com with 
Microsoft SMTPSVC(5.0.2195.6824); Tue, 18 Jan 2005 04:04:03 -0800
Received: (qmail 9798 invoked by uid 1000); 18 Jan 2005 12:02:48 -
Received: (qmail 9791 invoked from network); 18 Jan 2005 12:02:47 -
X-Message-Info: JGTYoYF78jGjurZCJPTEIJ2PapVSph6S
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
Precedence: bulk
List-Id: SQLite Users Mailing List 
List-Post: 
List-Help: 
List-Unsubscribe: 
List-Subscribe: 
Delivered-To: mailing list sqlite-users@sqlite.org
User-Agent: Mozilla Thunderbird 0.9 (X11/20041124)
X-Accept-Language: en-us, en
References: <[EMAIL PROTECTED]>
X-Enigmail-Version: 0.89.0.0
X-Enigmail-Supports: pgp-inline, pgp-mime
X-SA-Exim-Mail-From: [EMAIL PROTECTED]
X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on 
eryx.humfak.auc.dk
X-Spam-Status: No, hits=0.1 required=5.0 tests=AWL autolearn=no 
version=2.63
X-Spam-Level: X-SA-Exim-Version: 3.0 (built Mon May 19 15:33:23 MEST 2003)
X-SA-Exim-Scanned: Yes
X-MailScanner: Found to be clean
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 18 Jan 2005 12:04:03.0995 (UTC) 
FILETIME=[CDCBCEB0:01C4FD55]

Greetings Ram,
Ram Kumar wrote:
I am using lcc compiler for my basic learning.
Is that sufficient to compile SQLite? Is there any free/openSource
alternative?

You will need both a compiler and a linker; I don't know lcc so I am not 
sure if it supplies a linker.

Otherwise, here are a couple of free alternatives:
http://www.mingw.org/
http://www.cygwin.com/   (really a Unix-like environment on Win32, but 
includes gcc)

If you want a good, free IDE, I can recommend both Dev-C++ and Mingw 
Developer Studio:

http://sourceforge.net/projects/dev-cpp
http://petra.hos.u-szeged.hu/~aking/www.parinya.ca/
HTH
Ulrik P.
--
Ulrik Petersen, MA, B.Sc.
University of Aalborg, Denmark
Homepage: http://ulrikp.org

_
Express yourself instantly with MSN Messenger! Download today it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

[sqlite] about sqlite_freemem()

[Ivan Dimitrov, PhD]

hi,
in DLL case of sqlite, do i need to call sqlite_freemem() after
sqlite_exec()/sqlite_get_table(), or only after  sqlite_open() , after
error?
Ivan

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] cursor

[ivan]

hi
is there sth like cursors ? (DECLARE CURSOR FOR SELECT  ;) and
FETCH,MOVE ?


thanks
 bye

ivan


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] vdbe question

[ivan]

hi

how can i create vdbe for same special sql query, then check how many row
a will get, and for example move to row 100 and fetch 20 rows ,
its could be just sql cursor but i didn't find it in sqlite ..

what can i do about it ?

thanks :)


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]