[sqlite] Getting random data from grouped data in one table

[eric wong]

Here is the situation. I have one database table "TB_Patient" with the
columns below:

PATIENT_ID
PATIENT_GROUP
FIRSTNAME
LASTNAME
ADDRESS

I want to get PATIENT_ID randomly from certain PATIENT_GROUP.

There are 5 PATIENT_GROUP only with total row in the table is about
1 (10K).

The number of data per PATIENT_GROUP is not the same. Certain
PATIENT_GROUP has less data than the others.

Do you know the FASTEST possible SQL query for this task?

When there is NO PATIENT_GROUP, I could come up with this query:

SELECT PATIENT_ID FROM TB_Patient WHERE rowid >= (ABS(RANDOM()) %
(SELECT MAX(rowid) FROM TB_Patient)) LIMIT 1

But now, I must get the data from certain PATIENT_GROUP.

What's your best approach to solve this? The objective is the fastest
possible query.

Thanks.

-- 
Regards,

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

Re: [sqlite] Storing data with SQLite

[liran ritkop]

Igor Tandetnik wrote:
> 
> It's possible with an UPDATE statement, but it's expensive. You can't just
> append to an existing blob, you have to provide all of the blob's data,
> old and new.
> 
> You might be better off storing the data in separate files, and just using
> SQLite to maintain metadata, like timestamps.
> 

Ok, I'll do it that way, it's a smart idea.
The question is, if i use files and not blob data, when i want to update the
data (this time the file, and not the blob field) it can lead to some kind
of fragmentation. Does anyone know, if sqlite know how to deal with it, if i
save it in a blob field? and if i save it in a file, so does ext4 filesystem
for example, deals with it better than sqlite? I guess its a little bit
complicated question, but it's interesting.



>> I have different problem related to this subject:
>> i wrote some code that put blob data (i declared a string and pass it
>> with
>> (char*) to the buffer i send to the blob).
>> The problem is that it takes only 32 bytes of that string.. what with the
>> rest of the string? why does it cut it?
> 
> How exactly do you bind that string? Show some code. My guess is, you tell
> SQLite to treat it as NUL-terminated string (rather than specifying its
> length explicitly), and there's a zero byte in your data at offset 32.
> -- 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

I upload a file which contain the important lines in my code.
The result in the blob data, as i said, is 32 bytes, which means 32 chars:
0123456789:;<=>?@ABCDEFGHIJKLMNO
http://old.nabble.com/file/p31104442/toUpload.c toUpload.c 
As you can see i dont have a null at the end but the char 'P' is missing..


-- 
View this message in context: 
http://old.nabble.com/Storing-data-with-SQLite-tp31096174p31104442.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Storing data with SQLite

[Simon Davies]

On 9 March 2011 08:54, liran ritkop  wrote:
>
.
.
.
>
> I upload a file which contain the important lines in my code.
> The result in the blob data, as i said, is 32 bytes, which means 32 chars:
> 0123456789:;<=>?@ABCDEFGHIJKLMNO
> http://old.nabble.com/file/p31104442/toUpload.c toUpload.c
> As you can see i dont have a null at the end but the char 'P' is missing..
>

>From your code:
<<
buf = (char*) str;
len = sizeof(buf) * 8;
.
.
retval2 = sqlite3_bind_blob(stmt, 1, buf, len, NULL);
>>

len == 32

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

Re: [sqlite] Storing data with SQLite

[liran ritkop]

Thanks..
dumb error, but i did it..
:-)


Simon Davies wrote:
> 
> On 9 March 2011 08:54, liran ritkop  wrote:
>>
> .
> .
> .
>>
>> I upload a file which contain the important lines in my code.
>> The result in the blob data, as i said, is 32 bytes, which means 32
>> chars:
>> 0123456789:;<=>?@ABCDEFGHIJKLMNO
>> http://old.nabble.com/file/p31104442/toUpload.c toUpload.c
>> As you can see i dont have a null at the end but the char 'P' is
>> missing..
>>
> 
> From your code:
> <<
> buf = (char*) str;
> len = sizeof(buf) * 8;
> .
> .
>   retval2 = sqlite3_bind_blob(stmt, 1, buf, len, NULL);
>>>
> 
> len == 32
> 
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Storing-data-with-SQLite-tp31096174p31104845.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Failure during Rollback statement

[pkcpkc]

What would happen if the following situation occurs: 
I begin a transaction and then do multiple sqlite execute statements. One of
the execute statements fails and I try doing a rollback. Now when I try the
rollback the rollback also fails. Does it mean database is corrupted?
Or will it be rolled back automatically?
-- 
View this message in context: 
http://old.nabble.com/Failure-during-Rollback-statement-tp31104890p31104890.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Failure during Rollback statement

[Richard Hipp]

On Wed, Mar 9, 2011 at 5:01 AM, pkcpkc  wrote:

>
> What would happen if the following situation occurs:
> I begin a transaction and then do multiple sqlite execute statements. One
> of
> the execute statements fails and I try doing a rollback. Now when I try the
> rollback the rollback also fails. Does it mean database is corrupted?
> Or will it be rolled back automatically?
>

Why is the ROLLBACK failing?

If I/O errors or out-of-memory errors prevent a rollback from happening,
SQLite abandons its changes, leaving the rollback journal (or WAL file) on
the disk, with the idea that the next process to come along will fix the
problem.  But this is a very unusual occurrence, since rollback does not
often fail.



> --
> View this message in context:
> http://old.nabble.com/Failure-during-Rollback-statement-tp31104890p31104890.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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

Re: [sqlite] Storing data with SQLite

[Igor Tandetnik]

liran ritkop  wrote:
> Ok, I'll do it that way, it's a smart idea.
> The question is, if i use files and not blob data, when i want to update the
> data (this time the file, and not the blob field) it can lead to some kind
> of fragmentation.

You are using Flash storage - why do you care about fragmentation? There are no 
disk head seeks to be concerned about. In fact, file systems designed for Flash 
often intentionally introduce fragmentation, for wear leveling.

> Does anyone know, if sqlite know how to deal with it, if i
> save it in a blob field?

SQLite just uses the underlying file system operations. It has no control over 
fragmentation.

> and if i save it in a file, so does ext4 filesystem
> for example, deals with it better than sqlite?

This question doesn't make much sense. SQLite database is a file. SQLite uses 
the file system to read and write to that file. It's up to the file system how 
this file is physically written to the disk.
-- 
Igor Tandetnik

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

Re: [sqlite] Failure during Rollback statement

[Jay A. Kreibich]

On Wed, Mar 09, 2011 at 02:01:14AM -0800, pkcpkc scratched on the wall:
> 
> What would happen if the following situation occurs: 
> I begin a transaction and then do multiple sqlite execute statements. One of
> the execute statements fails and I try doing a rollback. Now when I try the
> rollback the rollback also fails. Does it mean database is corrupted?
> Or will it be rolled back automatically?

  There are situations when a statement error can cause a rollback.  It
  is possible SQLite automatically rolled back the transaction on the
  first error.  This would cause the ROLLBACK to return an error.

  What is the error code returned by the ROLLBACK?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Failure during Rollback statement

[Simon Slavin]

On 9 Mar 2011, at 1:34pm, Jay A. Kreibich wrote:

> On Wed, Mar 09, 2011 at 02:01:14AM -0800, pkcpkc scratched on the wall:
>> 
> 
>> What would happen if the following situation occurs: 
>> I begin a transaction and then do multiple sqlite execute statements. One of
>> the execute statements fails and I try doing a rollback. Now when I try the
>> rollback the rollback also fails. Does it mean database is corrupted?
>> Or will it be rolled back automatically?
> 
>  There are situations when a statement error can cause a rollback.  It
>  is possible SQLite automatically rolled back the transaction on the
>  first error.  This would cause the ROLLBACK to return an error.
> 
>  What is the error code returned by the ROLLBACK?

Right.  Although the larger picture is that you continue to get error codes 
from perfectly normal legal statements.  I can imagine this problem happening 
by failure of underlying hardware.  For example, that the disk is full.  So for 
the original poster 'pkcpkc' the answer is this: we would say that the database 
it not corrupt, but that it is in a state where SQLite knows that things must 
be done before the database can be used further.

So anything that tries to sqlite3_open() that database will trigger another 
attempt to restore to the ROLLBACK point, and if it fails to do that it will 
just keep issuing error messages rather than accepting new SQL statements.

What is important is that you don't mess with the files in the meantime.  A 
common mistake appears to be deleting one more more journal files, then trying 
to use a database manager or the command-line tool to open the database.  The 
second bit is okay, but messing with the journal files will lose you data.

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

Re: [sqlite] Storing data with SQLite

[Philip Graham Willoughby]

On 9 Mar 2011, at 13:05, Igor Tandetnik wrote:

> liran ritkop  wrote:
>> Ok, I'll do it that way, it's a smart idea.
>> The question is, if i use files and not blob data, when i want to update the
>> data (this time the file, and not the blob field) it can lead to some kind
>> of fragmentation.
> 
> You are using Flash storage - why do you care about fragmentation? There are 
> no disk head seeks to be concerned about. In fact, file systems designed for 
> Flash often intentionally introduce fragmentation, for wear leveling.
> 
>> Does anyone know, if sqlite know how to deal with it, if i
>> save it in a blob field?
> 
> SQLite just uses the underlying file system operations. It has no control 
> over fragmentation.
> 
>> and if i save it in a file, so does ext4 filesystem
>> for example, deals with it better than sqlite?
> 
> This question doesn't make much sense. SQLite database is a file. SQLite uses 
> the file system to read and write to that file. It's up to the file system 
> how this file is physically written to the disk.

There are a couple of things you could call 'fragmentation', one of which you 
can't control very easily (and also doesn't matter on flash storage), and one 
that you can control (and potentially should worry about).

Fragmentation in the Windows defrag.exe sense is indeed irrelevant.

However if you put stuff in a blob field it gets stored in the database file, 
which will 'fragment' your database by dispersing your other data across more 
of a byte range in that file. This will affect how your database interacts with 
the operating system's cache: most operating systems will prefetch/cache data 
fairly linearly on a per-file basis, so if there's a lot of large blobs you 
don't really care for in most queries dispersed through your database file 
you'll be squandering a lot of that benefit.

The most significant downsides to storing the data which would have been in 
blobs outside your database is that you can end up with the database 
identifying something which no longer exists or with a file existing which has 
been logically deleted from the database; you will need to do extra work to 
deal with those issues.

Another alternative which is qualitatively somewhere between the two discussed 
so far is to keep your blobs in a separate database attached to the first: this 
means that your main database file won't be 'fragmented' and provided you 
aren't using WAL mode and you are using transactions correctly you should never 
have missing or orphaned blobs.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] Getting random data from grouped data in one table

[Max Vlasov]

On Wed, Mar 9, 2011 at 11:53 AM, eric wong  wrote:

> But now, I must get the data from certain PATIENT_GROUP.
>
> What's your best approach to solve this? The objective is the fastest
> possible query.
>
> Thanks.
>
>

There's an approach with "order by random" mentioned by Richard, there's
also another one that is self-explained from this query

SELECT * FROM Table LIMIT 1 OFFSET abs(random() % (SELECT Count(*) FROM
Table))

in your case this should lead to something like

SELECT patient_id FROM TB_Patient
WHERE patient_group=$group
LIMIT 1
OFFSET abs(random() % (SELECT Count(*) FROM TB_Patient WHERE GroupId=$group)

Both approaches should scan the table so slow for large tables by design,
but I found the latter being a slightly faster, but I'm not sure whether
this is always the case.

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

[sqlite] memory usage after VACUUM

[Nick Hodapp]

I'm using sqlite in an iOS app, via the popular FMDB wrapper.

My profiling tool is showing me that the app is using 2.5 MB of memory
before a VACUUM, and nearly 6MB after.  The tool shows that the extra memory
was allocated by sqlite3MemMalloc().  If I close and re-open the database
then the extra memory usage goes away, returning to 2.5 MB.

Is there any sqlite function I can call, or some other technique, to reduce
the memory allocated and hung-onto by sqlite, particularly during a VACUUM?

It's possible but unlikely that the FMDB wrapper is affecting things.  I
haven't removed it from the equation to test, however.

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

Re: [sqlite] memory usage after VACUUM

[Enrico Thierbach]

On 09.03.2011, at 16:23, Nick Hodapp wrote:

> I'm using sqlite in an iOS app, via the popular FMDB wrapper.
> 
> My profiling tool is showing me that the app is using 2.5 MB of memory
> before a VACUUM, and nearly 6MB after.  The tool shows that the extra memory
> was allocated by sqlite3MemMalloc().  If I close and re-open the database
> then the extra memory usage goes away, returning to 2.5 MB.
> 
> Is there any sqlite function I can call, or some other technique, to reduce
> the memory allocated and hung-onto by sqlite, particularly during a VACUUM?
> 
> It's possible but unlikely that the FMDB wrapper is affecting things.  I
> haven't removed it from the equation to test, however.
> 
> Nick Hodap

Hi nick,

just a shot in the dark: is there any chance that probably FMDB releases the 
memory properly, but the corresponding auto release pool
is not yet released? In that case the memory would still be held there. 

For anyone not into iOS development: that is Cocoa's memory management solution.

/eno


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

Re: [sqlite] memory usage after VACUUM

[Pavel Ivanov]

> Is there any sqlite function I can call, or some other technique, to reduce
> the memory allocated and hung-onto by sqlite, particularly during a VACUUM?

Yes, execute "pragma cache_size = 100" for example, or put other
number of your liking into there.

If closing and re-opening of the database makes extra memory usage to
go away then it was used by database cache. Default cache size is 2000
pages. Command shown above sets it to 100 pages. I don't know what
size of cache is best for you, just beware that usually with smaller
cache SQLite needs more frequently to go to disk and thus queries are
slower. But cache works only if you repeatedly access the same
tables/indexes over the same connection. And I don't know how much
slower queries are without cache on iOS.


Pavel

On Wed, Mar 9, 2011 at 10:23 AM, Nick Hodapp  wrote:
> I'm using sqlite in an iOS app, via the popular FMDB wrapper.
>
> My profiling tool is showing me that the app is using 2.5 MB of memory
> before a VACUUM, and nearly 6MB after.  The tool shows that the extra memory
> was allocated by sqlite3MemMalloc().  If I close and re-open the database
> then the extra memory usage goes away, returning to 2.5 MB.
>
> Is there any sqlite function I can call, or some other technique, to reduce
> the memory allocated and hung-onto by sqlite, particularly during a VACUUM?
>
> It's possible but unlikely that the FMDB wrapper is affecting things.  I
> haven't removed it from the equation to test, however.
>
> Nick Hodap
> ___
> 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] duplicate a prepared statement?

[Nick Hodapp]

Is it possible to duplicate a prepared statement?

I have a need to run the same query multiple times with different
parameters.  I can't re-use the prepared statement because multiple copies
of the same query, but with different parameters, are active at the same
time.  That is, I need to step through the results of these queries in step
with each other, not serially.

I was hoping to avoid preparing the same sql multiple times, on the
assumption that it would be more efficient to somehow duplicate a prepared
statement.

Is this possible?

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

[sqlite] Creation and modification timestamps with "insert or replace" possible?

[Armin Kunaschik]

Hi there,

I'm trying this for quite some time... and I'm totally stuck.

I have the following table:

CREATE TABLE example(
 date integer primary key not null,
 text text,
 ctime TIMESTAMP,
 mtime TIMESTAMP);

ctime=creation time (should be set only once)
mtime=modification time (should be set every time the row is updated)

Important: I also want to be able to "insert or replace" rows and keep ctime.

Currently I'm trying with 2 triggers:

CREATE TRIGGER insert_example_timestamp AFTER INSERT ON example
BEGIN
 UPDATE example SET ctime = DATETIME('now','localtime')
 WHERE ( rowid = new.rowid AND ctime IS NULL);
END;

CREATE TRIGGER update_example_timestamp AFTER UPDATE ON example
BEGIN
 UPDATE example SET mtime=DATETIME('now','localtime') WHERE rowid = new.rowid;
END;

The update trigger works fine, but the insert trigger ALWAYS updates ctime.
I tried various combinations to query a unset/empty ctime and only
update if ctime is NULL or ''.
It doesn't work inside the trigger.
Either it updates always or never.
Where is my mistake?

Is there another way to achieve this? Maybe without triggers?

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

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Mr. Puneet Kishor]

On Mar 9, 2011, at 5:19 PM, Armin Kunaschik wrote:

> Hi there,
> 
> I'm trying this for quite some time... and I'm totally stuck.
> 
> I have the following table:
> 
> CREATE TABLE example(
>  date integer primary key not null,
>  text text,
>  ctime TIMESTAMP,
>  mtime TIMESTAMP);
> 
> ctime=creation time (should be set only once)
> mtime=modification time (should be set every time the row is updated)
> 

change ctime definition to

ctime DEFAULT CURRENT_TIMESTAMP

and use trigger for only the mtime


> Important: I also want to be able to "insert or replace" rows and keep ctime.
> 
> Currently I'm trying with 2 triggers:
> 
> CREATE TRIGGER insert_example_timestamp AFTER INSERT ON example
> BEGIN
>  UPDATE example SET ctime = DATETIME('now','localtime')
>  WHERE ( rowid = new.rowid AND ctime IS NULL);
> END;
> 
> CREATE TRIGGER update_example_timestamp AFTER UPDATE ON example
> BEGIN
>  UPDATE example SET mtime=DATETIME('now','localtime') WHERE rowid = new.rowid;
> END;
> 
> The update trigger works fine, but the insert trigger ALWAYS updates ctime.
> I tried various combinations to query a unset/empty ctime and only
> update if ctime is NULL or ''.
> It doesn't work inside the trigger.
> Either it updates always or never.
> Where is my mistake?
> 
> Is there another way to achieve this? Maybe without triggers?
> 
> Many thanks!
> ___
> 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] Creation and modification timestamps with "insert or replace" possible?

[Armin Kunaschik]

On Wed, Mar 9, 2011 at 11:21 PM, Mr. Puneet Kishor  wrote:
>
> On Mar 9, 2011, at 5:19 PM, Armin Kunaschik wrote:
>
>> Hi there,
>>
>> I'm trying this for quite some time... and I'm totally stuck.
>>
>> I have the following table:
>>
>> CREATE TABLE example(
>>  date integer primary key not null,
>>  text text,
>>  ctime TIMESTAMP,
>>  mtime TIMESTAMP);
>>
>> ctime=creation time (should be set only once)
>> mtime=modification time (should be set every time the row is updated)
>>
>
> change ctime definition to
>
> ctime DEFAULT CURRENT_TIMESTAMP
>
> and use trigger for only the mtime
>
Unfortunately this will overwrite ctime with the new current time :-(
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Igor Tandetnik]

On 3/9/2011 5:19 PM, Armin Kunaschik wrote:
> I'm trying this for quite some time... and I'm totally stuck.
>
> I have the following table:
>
> CREATE TABLE example(
>   date integer primary key not null,
>   text text,
>   ctime TIMESTAMP,
>   mtime TIMESTAMP);
>
> ctime=creation time (should be set only once)
> mtime=modification time (should be set every time the row is updated)
>
> Important: I also want to be able to "insert or replace" rows and keep ctime.

INSERT OR REPLACE is indistinguishable from a DELETE followed by INSERT. 
I don't see how you can preserve ctime through that.
-- 
Igor Tandetnik

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

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Armin Kunaschik]

On Wed, Mar 9, 2011 at 11:28 PM, Igor Tandetnik  wrote:
> On 3/9/2011 5:19 PM, Armin Kunaschik wrote:
>> I'm trying this for quite some time... and I'm totally stuck.
>>
>> I have the following table:
>>
>> CREATE TABLE example(
>>   date integer primary key not null,
>>   text text,
>>   ctime TIMESTAMP,
>>   mtime TIMESTAMP);
>>
>> ctime=creation time (should be set only once)
>> mtime=modification time (should be set every time the row is updated)
>>
>> Important: I also want to be able to "insert or replace" rows and keep ctime.
>
> INSERT OR REPLACE is indistinguishable from a DELETE followed by INSERT.
> I don't see how you can preserve ctime through that.
Is it really DELETE and INSERT internally?
This would explain, why ctime is "invisible" inside the trigger...
it's simply not there
when replace is used.

Maybe I should think about using a temporary table for that purpose...
any ideas?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug in FTS3 "NOT" operator

[Alexey Pechnikov]

With ICU extension does not work "NOT ..." construction. Below the
example from FTS3 documentation:

sqlite> CREATE VIRTUAL TABLE docs USING fts3();
sqlite> INSERT INTO docs(docid, content) VALUES(1, 'a database is a
software system');
sqlite> INSERT INTO docs(docid, content) VALUES(2, 'sqlite is a
software system');
sqlite> INSERT INTO docs(docid, content) VALUES(3, 'sqlite is a database');
sqlite> SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite';
a database is a software system
sqlite> SELECT * FROM docs WHERE docs MATCH 'NOT sqlite';
Error: malformed MATCH expression: [NOT sqlite]

Without ICU this work fine. Please check this on upstream version
becouse I use some patches for FTS3 extension.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Jay A. Kreibich]

On Wed, Mar 09, 2011 at 11:19:51PM +0100, Armin Kunaschik scratched on the wall:

> Important: I also want to be able to "insert or replace" rows and keep ctime.

> The update trigger works fine, but the insert trigger ALWAYS updates ctime.

  As Igor pointed out, "INSERT OR REPLACE" is called "INSERT OR **REPLACE**"
  not "INSERT OR UPDATE."  The old row is completely DELETEed before the
  new row is INSERTed.  There is no UPDATE in a INSERT OR REPLACE.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Simon Slavin]

On 9 Mar 2011, at 10:37pm, Armin Kunaschik wrote:

> Is it really DELETE and INSERT internally?

As far as you can tell when how SQLite behaves, yes it is.

> This would explain, why ctime is "invisible" inside the trigger...
> it's simply not there
> when replace is used.
> 
> Maybe I should think about using a temporary table for that purpose...
> any ideas?

Why use a temporary table for it.  You appear to have two sets of data which 
should not be in the same table.  One associates your primary key (the date) 
with ctime, and another associates your primary key with text and mtime.  So 
make two tables.  In one you use INSERT OR FAIL, in the other you use INSERT OR 
REPLACE.

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

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Armin Kunaschik]

On Wed, Mar 9, 2011 at 11:44 PM, Simon Slavin  wrote:
>
> On 9 Mar 2011, at 10:37pm, Armin Kunaschik wrote:
>
>> Is it really DELETE and INSERT internally?
>
> As far as you can tell when how SQLite behaves, yes it is.
>
>> This would explain, why ctime is "invisible" inside the trigger...
>> it's simply not there
>> when replace is used.
>>
>> Maybe I should think about using a temporary table for that purpose...
>> any ideas?
>
> Why use a temporary table for it.  You appear to have two sets of data which 
> should not be in the same table.  One associates your primary key (the date) 
> with ctime, and another associates your primary key with text and mtime.  So 
> make two tables.  In one you use INSERT OR FAIL, in the other you use INSERT 
> OR REPLACE.

Great idea! In conjunction with the DEFAULT this would make things far easier.
And having the date in 2 tables instead of one is no disadvantage here.
I'll give it a try...

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

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Jay A. Kreibich]

On Wed, Mar 09, 2011 at 11:37:46PM +0100, Armin Kunaschik scratched on the wall:
> On Wed, Mar 9, 2011 at 11:28 PM, Igor Tandetnik  wrote:

> > INSERT OR REPLACE is indistinguishable from a DELETE followed by INSERT.

> Is it really DELETE and INSERT internally?

  Yes and no.  "REPLACE" alters the conflict resolution on UNIQUE
  constraints.  In all cases, the new row is INSERTed.  In the case of
  a REPLACE, if the INSERT would cause a UNIQUE constraint violation
  than any and all rows that are part of the conflict are first
  deleted before the new row is INSERTed.  In some situations a single
  INSERT OR REPLACE can actually cause multiple existing rows to be
  deleted before the new row is inserted.

  So it is always an INSERT, but sometimes the INSERT triggers one or
  more internal DELETEs first.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query within user defined funtion / UDF in sqlite

[RAKESH HEMRAJANI]

Hello Experts,

How can i execute a query from user defined function.

the user defined function is defined inside sqlite itself
(in shell.c opendb function, there is user defined function shellstatic but 
it doenst query the DB)

for example:

an user defined function is intended to get the rowcount of the input table.

user can type the below command

select rowcount('employee') from sqlite_master;

the user defined function should replace rowcount('employee') with the actual 
rowcount of the employee table.

pls note that the function should be defined within sqlite only and should be 
usable from sqlite command line interface as described above.

actual code:
it fails since m unable to get call back reference from context variable and 
bcoz of that application terminates while printing the value to the o/p 
interface (user screen)

static void rowcount(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
struct callback_data *p;
char *rowcounttest;
rowcounttest = "SELECT 10 from sqlite_master";
char *zErrMsg = 0;
sqlite3 *db = sqlite3_context_db_handle(context);
shell_exec(db, rowcounttest, shell_callback, context, );
}






















 
 

















 






 



 
 
 









 

















 




 




















 



 

 

Re: [sqlite] Query within user defined funtion / UDF in sqlite

[Igor Tandetnik]

RAKESH HEMRAJANI  wrote:
> for example:
> 
> an user defined function is intended to get the rowcount of the input table.
> 
> user can type the below command
> 
> select rowcount('employee') from sqlite_master;

What's the role of sqlite_master in this query? Accepting, for the sake of 
argument, that it's useful to encapsulate row count in a user-defined function, 
why isn't the query simply

select rowcount('employee');

> actual code:
> it fails since m unable to get call back reference from context variable

What is this "callback reference" you speak of? Why can't you provide your own 
callback (if for some reason you insist on using sqlite3_exec), or use 
sqlite3_prepare / sqlite3_step interface?

> and bcoz of that application terminates while printing
> the value to the o/p interface (user screen) 

Why does a custom function need to print anything? It just needs to calculate 
its result, and report it using one of sqlite3_result_* functions.
-- 
Igor Tandetnik

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

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Mohd Radzi Ibrahim]

On 10-Mar-2011, at 6:52 AM, Jay A. Kreibich wrote:

> On Wed, Mar 09, 2011 at 11:37:46PM +0100, Armin Kunaschik scratched on the 
> wall:
>> On Wed, Mar 9, 2011 at 11:28 PM, Igor Tandetnik  wrote:
> 
>>> INSERT OR REPLACE is indistinguishable from a DELETE followed by INSERT.
> 
>> Is it really DELETE and INSERT internally?
> 
>  Yes and no.  "REPLACE" alters the conflict resolution on UNIQUE
>  constraints.  In all cases, the new row is INSERTed.  In the case of
>  a REPLACE, if the INSERT would cause a UNIQUE constraint violation
>  than any and all rows that are part of the conflict are first
>  deleted before the new row is INSERTed.  In some situations a single
>  INSERT OR REPLACE can actually cause multiple existing rows to be
>  deleted before the new row is inserted.
> 
>  So it is always an INSERT, but sometimes the INSERT triggers one or
>  more internal DELETEs first.


So, that's explain why my column ID INTEGER PRIMARY KEY, changes when I use 
REPLACE. I could not rely on the ID with this command. 

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