Re: [sqlite] insert gdkpixbuf to sqlite database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Divya K M wrote:
> I am using a GdkPixbuf and want to store the pixbuf created to the
> sqlite3 database.Please can you tell what's wrong in code found below.

It isn't using the SQLite API :-)  SQLite does fundamentally work with blobs
so the issue is the GDA layer you are using.

> GValue* bValue  =  gda_value_new_blob(str,len);
> dataValue = gda_value_new_from_string ( gda_blob_to_string
> (bValue,len), G_TYPE_STRING);

This is a horrendous way of using blobs with SQLite.  SQLite is a library in
the same process so there is no need to copy blobs around and encode them as
strings - it can work directly with them as bindings.

You are probably going to be better off asking on gnome lists and mention
you are trying to do blobs with SQLite.  In the doc I do see references to
GdaBlobOp which would provide the same interface as SQLite's incremental
blob i/o assuming gda has implemented that.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkpv4QQACgkQmOOfHg372QQs9ACePuJsBboYxlqlecldb0jGuDYC
2agAn1USBGQ6g3IURvr5N784UaqmT+D5
=bwwn
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] insert gdkpixbuf to sqlite database

[Divya K M]

Hi,

I am using a GdkPixbuf and want to store the pixbuf created to the
sqlite3 database.Please can you tell what's wrong in code found below.

execute_sql_non_select (cnc, "CREATE TABLE IF NOT EXISTS Thumbails
(AlbumId text, ThumbData blob, ThumPath text)"); 

GdkPixbuf *thumbPixbuf;
thumbPixbuf = gdk_pixbuf_new_from_file_at_size(inPath,250,250,);

result = gdk_pixbuf_save_to_buffer (thumbPixbuf,str, , "jpeg", NULL,
NULL);

GValue* bValue  =  gda_value_new_blob(str,len);
dataValue = gda_value_new_from_string ( gda_blob_to_string
(bValue,len), G_TYPE_STRING);

res = gda_insert_row_into_table (dbConnection, "Thumbails", ,
"AlbumId",idValue, "ThumbData", dataValue,"ThumPath", pathValue, NULL);

Thanks in advance,
Divya

---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a system for arbitrarily tagging rows -- revisited

[Pavel Ivanov]

> I can't understand what that != 1 constraint is doing in the FROM
> clause. Shouldn't a constraint be in the WHERE clause while the FROM
> clause should only describe the source tables?

AFAIK, when you're writing your FROM clause in the form "table1 JOIN
table2 ON condition" you can write pretty much any condition on table2
right here in the FROM clause (I don't remember exactly but maybe you
can put condition on table1 here too). But when you're writing query
in the form "FROM table1, table2 WHERE conditions" then yes, of
course, all conditions should go into WHERE clause. And this is
exactly why I don't like "table1 JOIN table2" form (one can easily
confuse himself by putting related conditions to different places) and
write my queries always in the form "FROM table1, table2". In this
particular case I've decided to continue to write in your syntax
preference and put related conditions (pt_p.page_id = pt_np.page_id
AND pt_np.tag_id != 1) in one place because I think when they stand
nearby intention of the query is more understandable.

Pavel

On Tue, Jul 28, 2009 at 9:59 PM, P Kishor wrote:
> On Tue, Jul 28, 2009 at 7:45 PM, Pavel Ivanov wrote:
>> First of all you've mentioned schema which is not in agreement
>> slightly with sql you've given. But of course we can guess...
>> Second your sql seems to do slightly different thing from what you're
>> saying. In particular I'm concerned about this sentence:
>>
>>> If the tag is the same as the 'current_tag', add a 'classtype' of
>>> 'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by
>>> clicking on any of the tags and seeing the color of the relevant tags
>>> change);
>>
>> What sql does is: if tag is among those assigned to pages having also
>> 'current_tag' then add a classtype 'tag_a' else 'tag_b'.
>>
>> But keeping all that in mind if I were you I'd write select in the
>> following way:
>>
>> SELECT
>>    CASE WHEN t_cur.tag_id is null THEN 'tag_b' ELSE 'tag_a'
>>    END AS classtype,
>>    t.tag_id,
>>    t.tag_name,
>>    count(*) tag_num
>> FROM pages_tags pt_p
>>    JOIN pages_tags pt_np on pt_p.page_id = pt_np.page_id
>>                  AND pt_np.tag_id != 1
>>    JOIN tags t on pt_np.tag_id = t.tag_id
>>    LEFT OUTER JOIN (
>>                  SELECT DISTINCT pt_pcur.tag_id
>>                  FROM pages_tags pt_cur
>>                      JOIN pages_tags pt_pcur on pt_pcur.page_id =
>> pt_cur.page_id
>>                  WHERE pt_cur.tag_id = ?) t_cur
>>              on t_cur.tag_id = t.tag_id
>> WHERE pt_p.tag_id = 1
>> GROUP BY t.tag_id, t.tag_name
>> ORDER BY t.tag_name
>>
>> It eliminates executing of select for each row and also shows clearly
>> how it's intended to be executed...
>
>
> Yes, indeed. Your query works well. One question... what is happening with
>
> FROM
>  pages_tags pt_p JOIN pages_tags pt_np ON pt_p.page_id = pt_np.page_id
>  AND pt_np.tag_id != 1
>
>
> I can't understand what that != 1 constraint is doing in the FROM
> clause. Shouldn't a constraint be in the WHERE clause while the FROM
> clause should only describe the source tables?
>
>
>>
>> Pavel
>>
>> On Tue, Jul 28, 2009 at 7:21 PM, P Kishor wrote:
>>> Following up on my recent question about arbitrarily tagging rows in a
>>> table, my website has certain pages that are tagged as 'Photos' and
>>> other tags (http://www.punkish.org/Photos). The table schema is as
>>> follows
>>>
>>> TABLE pages (p_id INTEGER PRIMARY KEY, p_name TEXT);
>>> TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT);
>>> TABLE pages_tags (p_id INTEGER, t_id INTEGER);
>>>
>>> Given a parameter ? = 'current_tag'
>>>
>>> Find all the non-'Photos' tags and their Counts for the pages that are
>>> tagged as 'Photos' (tag_id = 1);
>>> If the tag is the same as the 'current_tag', add a 'classtype' of
>>> 'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by
>>> clicking on any of the tags and seeing the color of the relevant tags
>>> change);
>>>
>>>
>>> SELECT
>>>    -- set 'classtype' to 'tag_a' or 'tag_b'
>>>    CASE
>>>        WHEN t.tag_id IN (
>>>            SELECT DISTINCT tag_id
>>>            FROM pages_tags
>>>            WHERE page_id IN (
>>>              SELECT p.page_id
>>>              FROM pages p JOIN pages_tags pt ON p.page_id = pt.page_id
>>>              WHERE pt.tag_id = ?
>>>            )
>>>        )
>>>        THEN 'tag_a'
>>>        ELSE 'tag_b'
>>>    END AS classtype,
>>>    t.tag_id,
>>>    t.tag_name,
>>>    Count(tag_name) AS tag_num
>>>
>>> FROM tags t JOIN pages_tags pt ON t.tag_id = pt.tag_id
>>>
>>> WHERE
>>>    -- all tags that are not 'Photos'
>>>    t.tag_id != 1 AND
>>>
>>>    -- all pages that are tagged as 'Photos'
>>>    pt.page_id IN (
>>>        SELECT page_id FROM pages_tags WHERE tag_id = 1
>>>    )
>>>
>>> GROUP BY classtype, t.tag_id, tag_name
>>> ORDER BY tag_name
>>>
>>> Question: The above works just fine, but seems awfully convoluted,
>>> which could be a 

Re: [sqlite] search time is non-determinate in multi-thread enviroment

[pierr]

Jay A. Kreibich-2 wrote:
> 
> 
>   THREADSAFE=1 is "Serialize mode" and THREADSAFE=2 is "Multithread"
>   mode.  THREADSAFE=2 (basic Multithread) actually offers less protection,
>   requiring the application to provide its own locks to prevent multiple
>   threads from accessing the DB at the same time, while THREADSAFE=1
>   (Serialize) handles all that for you.  Generally, unless you're doing
>   a lot of thread and lock management yourself, you want to use =1.
> 

http://www.sqlite.org/threadsafe.html
"Multi-thread. In this mode, SQLite can be safely used by multiple threads 
provided that no single database connection is used simulataneously in two
or more threads." 

If I have one(different) connection for each thread , it seems no need to
synchronize these threads' accessing to the database because they are
through different connection. Did I read it wrongly?
-- 
View this message in context: 
http://www.nabble.com/search-time-is-non-determinate-in-multi-thread-enviroment-tp24693604p24711210.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] a system for arbitrarily tagging rows -- revisited

[P Kishor]

On Tue, Jul 28, 2009 at 7:45 PM, Pavel Ivanov wrote:
> First of all you've mentioned schema which is not in agreement
> slightly with sql you've given. But of course we can guess...
> Second your sql seems to do slightly different thing from what you're
> saying. In particular I'm concerned about this sentence:
>
>> If the tag is the same as the 'current_tag', add a 'classtype' of
>> 'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by
>> clicking on any of the tags and seeing the color of the relevant tags
>> change);
>
> What sql does is: if tag is among those assigned to pages having also
> 'current_tag' then add a classtype 'tag_a' else 'tag_b'.
>
> But keeping all that in mind if I were you I'd write select in the
> following way:
>
> SELECT
>    CASE WHEN t_cur.tag_id is null THEN 'tag_b' ELSE 'tag_a'
>    END AS classtype,
>    t.tag_id,
>    t.tag_name,
>    count(*) tag_num
> FROM pages_tags pt_p
>    JOIN pages_tags pt_np on pt_p.page_id = pt_np.page_id
>                  AND pt_np.tag_id != 1
>    JOIN tags t on pt_np.tag_id = t.tag_id
>    LEFT OUTER JOIN (
>                  SELECT DISTINCT pt_pcur.tag_id
>                  FROM pages_tags pt_cur
>                      JOIN pages_tags pt_pcur on pt_pcur.page_id =
> pt_cur.page_id
>                  WHERE pt_cur.tag_id = ?) t_cur
>              on t_cur.tag_id = t.tag_id
> WHERE pt_p.tag_id = 1
> GROUP BY t.tag_id, t.tag_name
> ORDER BY t.tag_name
>
> It eliminates executing of select for each row and also shows clearly
> how it's intended to be executed...


Yes, indeed. Your query works well. One question... what is happening with

FROM
  pages_tags pt_p JOIN pages_tags pt_np ON pt_p.page_id = pt_np.page_id
  AND pt_np.tag_id != 1


I can't understand what that != 1 constraint is doing in the FROM
clause. Shouldn't a constraint be in the WHERE clause while the FROM
clause should only describe the source tables?


>
> Pavel
>
> On Tue, Jul 28, 2009 at 7:21 PM, P Kishor wrote:
>> Following up on my recent question about arbitrarily tagging rows in a
>> table, my website has certain pages that are tagged as 'Photos' and
>> other tags (http://www.punkish.org/Photos). The table schema is as
>> follows
>>
>> TABLE pages (p_id INTEGER PRIMARY KEY, p_name TEXT);
>> TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT);
>> TABLE pages_tags (p_id INTEGER, t_id INTEGER);
>>
>> Given a parameter ? = 'current_tag'
>>
>> Find all the non-'Photos' tags and their Counts for the pages that are
>> tagged as 'Photos' (tag_id = 1);
>> If the tag is the same as the 'current_tag', add a 'classtype' of
>> 'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by
>> clicking on any of the tags and seeing the color of the relevant tags
>> change);
>>
>>
>> SELECT
>>    -- set 'classtype' to 'tag_a' or 'tag_b'
>>    CASE
>>        WHEN t.tag_id IN (
>>            SELECT DISTINCT tag_id
>>            FROM pages_tags
>>            WHERE page_id IN (
>>              SELECT p.page_id
>>              FROM pages p JOIN pages_tags pt ON p.page_id = pt.page_id
>>              WHERE pt.tag_id = ?
>>            )
>>        )
>>        THEN 'tag_a'
>>        ELSE 'tag_b'
>>    END AS classtype,
>>    t.tag_id,
>>    t.tag_name,
>>    Count(tag_name) AS tag_num
>>
>> FROM tags t JOIN pages_tags pt ON t.tag_id = pt.tag_id
>>
>> WHERE
>>    -- all tags that are not 'Photos'
>>    t.tag_id != 1 AND
>>
>>    -- all pages that are tagged as 'Photos'
>>    pt.page_id IN (
>>        SELECT page_id FROM pages_tags WHERE tag_id = 1
>>    )
>>
>> GROUP BY classtype, t.tag_id, tag_name
>> ORDER BY tag_name
>>
>> Question: The above works just fine, but seems awfully convoluted,
>> which could be a result of my thinking too much about it.
>> Particularly, it seems the two nested SELECTs in the CASE clause would
>> be executed for each row in the result set. Could I do this more
>> elegantly?
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> Sent from Madison, WI, United States
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu

Re: [sqlite] DATETIME and storage type

[John Stanton]

Sqlite does not have a DATETIME type.  It stores the decalred type but 
ignored it.  You can use it in your application.

Rael Bauer wrote:
> Hi,
>  
> If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table 
> "notes" add column "last_modified" DATETIME default "2001-01-01";) will the 
> declared default value be stored as a string or real value?
>  
> Also, more generally, how can I find out what storage type field values have 
> been stored in?
>  
> Thanks
> Rael Bauer
>
>
>   
> ___
> 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] search time is non-determinate in multi-thread enviroment

[pierr]

Igor Tandetnik wrote:
> 
> pierr wrote:
>> Hi,
>>   My sqlite is configure as serialized (THREADSAFE=1). My application
>> has only one connection but it is shared by two thread. One thread
>> will do insert/update/delete in the background , another thread will
>> do the select upon request of the user from gui.
>>  Typically, we will call following function 12 times (with different
>> parameters ,of course)before showing the pages to user. In the
>> standalone test (single  thread), a call to following function will
>> take less than 40ms, so 12 times will take less than 500 ms and it is
>> acceptable. However, in the real application, sometimes this function
>> took 1000ms to return the result which make the gui slow.
> 
> When the second thread makes SQLite calls, it takes a lock on the 
> connection. If the UI thread runs a SELECT at this moment, it also tries 
> to take that lock, and so has to wait until the second thread releases 
> it.
> 
1. I expected the second thread would impact the SELECT thread but not that
much. The serach time increase as much as 6 times on average. But in my
experiment, when i add a usleep(1) (10ms) in the INSERT thread, the
search time will back to normal.I am not sure if this is the POINT. I would
try this in my real application to see what happened.

2. I also suspect the increase of search time is caused by the SELECT thread
can not be scheduled timely to run because of INTERRUPT that happend very
frequently in my system , or because of other threads cost to much CPU time
like the other_thead in my expriment ,which has nothing but while(1).
(Actually ,I don't quite understand why this would impact the SELECT thread
(that much). Although other_thread has a while(1), I thought kernel (linux
2.6) should use time_sharing scheduler policy as both other_thread and
select_thread are nomal thread , i.e, not real time thread and no priority
set explicitly. So priority base preemptive should not be applied here. Any
idea? )

3.I think the way I use the sqlite (one thread for write , another for read)
is not uncommon. Do we have any best practice for the question I am asking
here?


Igor Tandetnik wrote:
> 
> Further, you cannot open two connections to the same in-memory database.
> 

This would be the thing that prevent me from going THREADSAFE=2.

-- 
View this message in context: 
http://www.nabble.com/search-time-is-non-determinate-in-multi-thread-enviroment-tp24693604p24711093.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] a system for arbitrarily tagging rows -- revisited

[P Kishor]

Pavel,


On Tue, Jul 28, 2009 at 7:45 PM, Pavel Ivanov wrote:
> First of all you've mentioned schema which is not in agreement
> slightly with sql you've given. But of course we can guess...
> Second your sql seems to do slightly different thing from what you're
> saying. In particular I'm concerned about this sentence:
>
>> If the tag is the same as the 'current_tag', add a 'classtype' of
>> 'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by
>> clicking on any of the tags and seeing the color of the relevant tags
>> change);
>
> What sql does is: if tag is among those assigned to pages having also
> 'current_tag' then add a classtype 'tag_a' else 'tag_b'.

You are absolutely correct about both of the above.

The first error (wrong schema) was because of me copying the concept
schema from my earlier email and juxtaposing it with my real SQL
query... but yes, p_id is the same as page_id and t_id is the same as
tag_id.

The second error is because of my mind being fixated on an earlier
iteration of my solution that applied 'tag_a' only when the
'current_tag' matched the tag in the row. Of course, that is not what
I wanted... I wanted to display *all* the tags, but highlight only all
those tags that matched current rows being displayed.

In any case, your sharp eye caught both errors. I will study and try
out your suggested statement. Many thanks for that.


>
> But keeping all that in mind if I were you I'd write select in the
> following way:
>
> SELECT
>    CASE WHEN t_cur.tag_id is null THEN 'tag_b' ELSE 'tag_a'
>    END AS classtype,
>    t.tag_id,
>    t.tag_name,
>    count(*) tag_num
> FROM pages_tags pt_p
>    JOIN pages_tags pt_np on pt_p.page_id = pt_np.page_id
>                  AND pt_np.tag_id != 1
>    JOIN tags t on pt_np.tag_id = t.tag_id
>    LEFT OUTER JOIN (
>                  SELECT DISTINCT pt_pcur.tag_id
>                  FROM pages_tags pt_cur
>                      JOIN pages_tags pt_pcur on pt_pcur.page_id =
> pt_cur.page_id
>                  WHERE pt_cur.tag_id = ?) t_cur
>              on t_cur.tag_id = t.tag_id
> WHERE pt_p.tag_id = 1
> GROUP BY t.tag_id, t.tag_name
> ORDER BY t.tag_name
>
> It eliminates executing of select for each row and also shows clearly
> how it's intended to be executed...
>
> Pavel
>
> On Tue, Jul 28, 2009 at 7:21 PM, P Kishor wrote:
>> Following up on my recent question about arbitrarily tagging rows in a
>> table, my website has certain pages that are tagged as 'Photos' and
>> other tags (http://www.punkish.org/Photos). The table schema is as
>> follows
>>
>> TABLE pages (p_id INTEGER PRIMARY KEY, p_name TEXT);
>> TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT);
>> TABLE pages_tags (p_id INTEGER, t_id INTEGER);
>>
>> Given a parameter ? = 'current_tag'
>>
>> Find all the non-'Photos' tags and their Counts for the pages that are
>> tagged as 'Photos' (tag_id = 1);
>> If the tag is the same as the 'current_tag', add a 'classtype' of
>> 'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by
>> clicking on any of the tags and seeing the color of the relevant tags
>> change);
>>
>>
>> SELECT
>>    -- set 'classtype' to 'tag_a' or 'tag_b'
>>    CASE
>>        WHEN t.tag_id IN (
>>            SELECT DISTINCT tag_id
>>            FROM pages_tags
>>            WHERE page_id IN (
>>              SELECT p.page_id
>>              FROM pages p JOIN pages_tags pt ON p.page_id = pt.page_id
>>              WHERE pt.tag_id = ?
>>            )
>>        )
>>        THEN 'tag_a'
>>        ELSE 'tag_b'
>>    END AS classtype,
>>    t.tag_id,
>>    t.tag_name,
>>    Count(tag_name) AS tag_num
>>
>> FROM tags t JOIN pages_tags pt ON t.tag_id = pt.tag_id
>>
>> WHERE
>>    -- all tags that are not 'Photos'
>>    t.tag_id != 1 AND
>>
>>    -- all pages that are tagged as 'Photos'
>>    pt.page_id IN (
>>        SELECT page_id FROM pages_tags WHERE tag_id = 1
>>    )
>>
>> GROUP BY classtype, t.tag_id, tag_name
>> ORDER BY tag_name
>>
>> Question: The above works just fine, but seems awfully convoluted,
>> which could be a result of my thinking too much about it.
>> Particularly, it seems the two nested SELECTs in the CASE clause would
>> be executed for each row in the result set. Could I do this more
>> elegantly?
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> Sent from Madison, WI, United States
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> 

Re: [sqlite] a system for arbitrarily tagging rows -- revisited

[Pavel Ivanov]

First of all you've mentioned schema which is not in agreement
slightly with sql you've given. But of course we can guess...
Second your sql seems to do slightly different thing from what you're
saying. In particular I'm concerned about this sentence:

> If the tag is the same as the 'current_tag', add a 'classtype' of
> 'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by
> clicking on any of the tags and seeing the color of the relevant tags
> change);

What sql does is: if tag is among those assigned to pages having also
'current_tag' then add a classtype 'tag_a' else 'tag_b'.

But keeping all that in mind if I were you I'd write select in the
following way:

SELECT
CASE WHEN t_cur.tag_id is null THEN 'tag_b' ELSE 'tag_a'
END AS classtype,
t.tag_id,
t.tag_name,
count(*) tag_num
FROM pages_tags pt_p
JOIN pages_tags pt_np on pt_p.page_id = pt_np.page_id
  AND pt_np.tag_id != 1
JOIN tags t on pt_np.tag_id = t.tag_id
LEFT OUTER JOIN (
  SELECT DISTINCT pt_pcur.tag_id
  FROM pages_tags pt_cur
  JOIN pages_tags pt_pcur on pt_pcur.page_id =
pt_cur.page_id
  WHERE pt_cur.tag_id = ?) t_cur
  on t_cur.tag_id = t.tag_id
WHERE pt_p.tag_id = 1
GROUP BY t.tag_id, t.tag_name
ORDER BY t.tag_name

It eliminates executing of select for each row and also shows clearly
how it's intended to be executed...

Pavel

On Tue, Jul 28, 2009 at 7:21 PM, P Kishor wrote:
> Following up on my recent question about arbitrarily tagging rows in a
> table, my website has certain pages that are tagged as 'Photos' and
> other tags (http://www.punkish.org/Photos). The table schema is as
> follows
>
> TABLE pages (p_id INTEGER PRIMARY KEY, p_name TEXT);
> TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT);
> TABLE pages_tags (p_id INTEGER, t_id INTEGER);
>
> Given a parameter ? = 'current_tag'
>
> Find all the non-'Photos' tags and their Counts for the pages that are
> tagged as 'Photos' (tag_id = 1);
> If the tag is the same as the 'current_tag', add a 'classtype' of
> 'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by
> clicking on any of the tags and seeing the color of the relevant tags
> change);
>
>
> SELECT
>    -- set 'classtype' to 'tag_a' or 'tag_b'
>    CASE
>        WHEN t.tag_id IN (
>            SELECT DISTINCT tag_id
>            FROM pages_tags
>            WHERE page_id IN (
>              SELECT p.page_id
>              FROM pages p JOIN pages_tags pt ON p.page_id = pt.page_id
>              WHERE pt.tag_id = ?
>            )
>        )
>        THEN 'tag_a'
>        ELSE 'tag_b'
>    END AS classtype,
>    t.tag_id,
>    t.tag_name,
>    Count(tag_name) AS tag_num
>
> FROM tags t JOIN pages_tags pt ON t.tag_id = pt.tag_id
>
> WHERE
>    -- all tags that are not 'Photos'
>    t.tag_id != 1 AND
>
>    -- all pages that are tagged as 'Photos'
>    pt.page_id IN (
>        SELECT page_id FROM pages_tags WHERE tag_id = 1
>    )
>
> GROUP BY classtype, t.tag_id, tag_name
> ORDER BY tag_name
>
> Question: The above works just fine, but seems awfully convoluted,
> which could be a result of my thinking too much about it.
> Particularly, it seems the two nested SELECTs in the CASE clause would
> be executed for each row in the result set. Could I do this more
> elegantly?
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> Sent from Madison, WI, United States
> ___
> 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] a system for arbitrarily tagging rows -- revisited

[P Kishor]

Following up on my recent question about arbitrarily tagging rows in a
table, my website has certain pages that are tagged as 'Photos' and
other tags (http://www.punkish.org/Photos). The table schema is as
follows

TABLE pages (p_id INTEGER PRIMARY KEY, p_name TEXT);
TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT);
TABLE pages_tags (p_id INTEGER, t_id INTEGER);

Given a parameter ? = 'current_tag'

Find all the non-'Photos' tags and their Counts for the pages that are
tagged as 'Photos' (tag_id = 1);
If the tag is the same as the 'current_tag', add a 'classtype' of
'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by
clicking on any of the tags and seeing the color of the relevant tags
change);


SELECT
-- set 'classtype' to 'tag_a' or 'tag_b'
CASE
WHEN t.tag_id IN (
SELECT DISTINCT tag_id
FROM pages_tags
WHERE page_id IN (
  SELECT p.page_id
  FROM pages p JOIN pages_tags pt ON p.page_id = pt.page_id
  WHERE pt.tag_id = ?
)
)
THEN 'tag_a'
ELSE 'tag_b'
END AS classtype,
t.tag_id,
t.tag_name,
Count(tag_name) AS tag_num

FROM tags t JOIN pages_tags pt ON t.tag_id = pt.tag_id

WHERE
-- all tags that are not 'Photos'
t.tag_id != 1 AND

-- all pages that are tagged as 'Photos'
pt.page_id IN (
SELECT page_id FROM pages_tags WHERE tag_id = 1
)

GROUP BY classtype, t.tag_id, tag_name
ORDER BY tag_name

Question: The above works just fine, but seems awfully convoluted,
which could be a result of my thinking too much about it.
Particularly, it seems the two nested SELECTs in the CASE clause would
be executed for each row in the result set. Could I do this more
elegantly?



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner joins on prepared statements

[Igor Tandetnik]

Shaun Seckman (Firaxis)
 wrote:
>Say I have the following prepared statement "select
> B.foo from A inner join B on A.bar = B.bar where A.foo = ?" used to
> populate values from a reference table.  If I intend on executing this
> statement several times with different bound values,  is it better for
> me to generate a temporary table to remove the inner join or will
> SQLite internally keep a temporary table  for this statement?

I'm not sure what temporary table you are talking about. There's no 
reason for SQLite to generate a temporary table to execute this 
statement - let alone keep one around.

Igor Tandetnik 



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

Re: [sqlite] How secure is encrypted sqlite database

[Kees Nuyt]

On Tue, 28 Jul 2009 16:32:51 -0400, Ram G
 wrote:

>Hi
>I have modified (credit goes to System.Data.SQLite) SQlite library to
>encrypt the database. File is encrypted and data insert/update/retrieval
>works fine.
>
>The question I have is, how secure is the encrypted database. Please correct
>me if I am wrong, SQLite reads the file and stores some of the data pages in
>memory. In the case of an encrypted database, the data cached in memory
>pages is encrypted or clear text?

Well, you modified the library, so you now better than we do
whether pages are decrypted when they are read into memory
or not. Which layer/subsystem did you change? 

If it is implemented in VFS, the pages in memory are not
encrypted and security depends on the access to the sqlite
memory from some other process, which depends on hardware,
operating system, and system/security administration.

>Thanks in advance.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How secure is encrypted sqlite database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Robert Simpson wrote:
> The .NET provider uses RC4 encryption.  The entire file is encrypted,
> including the metadata. 

You also neglected to mention that the encryption key has to be provided
locally to decrypt/encrypt the database :-)  Since the key is already on the
computer, the bad guy can just go ahead and find that as an alternative to
the other attacks mentioned.  And if they do you wouldn't even know they had
since it won't alter the data or leave any other finger print.

I like to measure security strength by how much a reasonable bad guy would
charge to break it.  If they had local access to the machine then it would
be a few hundred dollars.  If not then a few hundred plus whatever it takes
to gain local access (dependent on operating system, configuration, who can
be bribed if necessary, exposure to networks etc).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkpva8oACgkQmOOfHg372QQiBQCfaxiha/VzKzRnDLXzaUAckhMP
Uj4AnREN+Fu2K8MwA/j4Exx/dU60MrlX
=hIh6
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How secure is encrypted sqlite database

[Simon Slavin]

On 28 Jul 2009, at 9:32pm, Ram G wrote:

> I have modified (credit goes to System.Data.SQLite) SQlite library to
> encrypt the database. File is encrypted and data insert/update/ 
> retrieval
> works fine.
>
> The question I have is, how secure is the encrypted database.

You seem to have implemented encryption yourself.  Or are you using  
the hwachi extension ?  Or sqlite-crypt ?  How secure is your own  
method of encryption ?  Do you wipe cleartext memory after doing your  
own encryption and decryption ?

> Please correct
> me if I am wrong, SQLite reads the file and stores some of the data  
> pages in
> memory. In the case of an encrypted database, the data cached in  
> memory
> pages is encrypted or clear text?

There has to be, at some stage, plaintext data in memory somewhere.   
Details of how it's handled should probably be either completely  
public, or as secret as practical.

If you want to have the best encryption of your SQLite data you should  
contact DRH, who maintains custom package which does exactly that (the  
hwachi one I mentioned earlier).  See



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

Re: [sqlite] How secure is encrypted sqlite database

[Robert Simpson]

The .NET provider uses RC4 encryption.  The entire file is encrypted,
including the metadata.  SQLite (the engine) may store unencrypted pages in
its internal cache, but I don't really know for sure.

The bottom line is that nothing is safe from a hacker who has your binaries
and database on his/her computer and is determined to read your data.

It is impossible to write an unhackable program that is designed to run on a
local computer.  Anytime a hacker has access to a debugger and
locally-running code, you are vulnerable in some fashion or another.

One could write a very simple MIIM (Man in the middle) attack by renaming
the sqlite DLL to something else and writing their own DLL that provides all
the same exports.

Heck, SQLite being open-source, one can just as easily recompile it and put
their own debugging code in it and trace all the calls in and out.

The best you can hope for is to eliminate the casual prying eyes.  You can't
shut out a determined hacker.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ram G
Sent: Tuesday, July 28, 2009 1:33 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How secure is encrypted sqlite database

Hi
I have modified (credit goes to System.Data.SQLite) SQlite library to
encrypt the database. File is encrypted and data insert/update/retrieval
works fine.

The question I have is, how secure is the encrypted database. Please correct
me if I am wrong, SQLite reads the file and stores some of the data pages in
memory. In the case of an encrypted database, the data cached in memory
pages is encrypted or clear text?

Thanks in advance.
___
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] inner joins on prepared statements

[Shaun Seckman (Firaxis)]

Hello All,

Say I have the following prepared statement "select
B.foo from A inner join B on A.bar = B.bar where A.foo = ?" used to
populate values from a reference table.  If I intend on executing this
statement several times with different bound values,  is it better for
me to generate a temporary table to remove the inner join or will SQLite
internally keep a temporary table  for this statement?

 

-Shaun

 

 

 

 

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

[sqlite] How secure is encrypted sqlite database

[Ram G]

Hi
I have modified (credit goes to System.Data.SQLite) SQlite library to
encrypt the database. File is encrypted and data insert/update/retrieval
works fine.

The question I have is, how secure is the encrypted database. Please correct
me if I am wrong, SQLite reads the file and stores some of the data pages in
memory. In the case of an encrypted database, the data cached in memory
pages is encrypted or clear text?

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

Re: [sqlite] good books for beginners (was Re: What is a Relation?)

[Darren Duncan]

Darren Duncan wrote:

> So I farmed out this question yesterday to another list I'm on, and got 
> another 

> And another response said:
> 
>After having read a few of the responses, the original question strikes me 
> as 
> "very hard to answer".
> 
>I mean, how can one be a "seasoned programmer" and at the same time be 
> "new 
> to databases" 


Before anyone decides to call that responder out on this comment, someone 
already did, and followed with a generic recommendation:

   What about programmers who work in image processing, 3D graphics, games
development, device driver development, real-time image rendering,
systems programming, embedded systems, and so on?  Such fields tend to
be heavily focused on implementing and optimising algorithms or
interfacing with hardware, and often don't even encounter persistence,
let alone data management as we typically understand it.  For
programmers who have been working in these fields but are moving into
developing (for example) business applications, I think almost any
introductory database text would be a suitable starting point.

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

Re: [sqlite] Insert multiple entries in a single INSERT statement

[Simon Slavin]

On 28 Jul 2009, at 7:22pm, Shaun Seckman (Firaxis) wrote:

>Looking at the SQL syntax chart it doesn't seem like
> this is possible.  In other SQL servers I'm able to use the statement
> "insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
> '3');".  Is this possible in SQLite or must I instead insert one at a
> time.


One INSERT command = one new record.

Depending on what part of the API you're using, you may be able to  
string multiple INSERT commands together, separated by a semi-colon.

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

[sqlite] good books for beginners (was Re: What is a Relation?)

[Darren Duncan]

Paul Claessen wrote:
> So .. would anyone know a good book for seasoned programmers, who are new to 
> databases, that addresses all these issues?

So I farmed out this question yesterday to another list I'm on, and got another 
recommendation for you.

Quoth:

   For beginners, whether programmers or not, I recommend
   Fred Rolland's
   "Essence of Databases".
http://www.amazon.co.uk/Essence-Databases-Computing-Fred-Rolland/dp/0137278276/

And also the same first recommendation I gave yesterday:

   "SQL and Relational Theory
   How to Write Accurate SQL Code"
   by C. J. Date
   http://oreilly.com/catalog/9780596523060/

And another response said:

   After having read a few of the responses, the original question strikes me 
as 
"very hard to answer".

   I mean, how can one be a "seasoned programmer" and at the same time be "new 
to databases" 

   At the very least, "being a seasoned programmer" implies "being familiar 
with 
data management of some sort", because in the end, that is the only thing that 
programming is all about.

   So, "being a seasoned programmer" and at the same time "being new to 
databases", means imo the same thing as "understanding data (management), but 
not understanding how that is done with a ((T)R)DBMS.

   I feel like a book targeted to this kind of audience should go VERY DEEP on 
the subject of "the structure of data" as it is commonly perceived/imposed by a 
relational system (and after having covered that, it should go more or less 
equally deep on (some) relational algebra).  I honestly doubt whether such a 
book really exists.  Even Date's "Introduction to ..." doesn't strike me as 
suitable because it covers so much more subjects.

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

Re: [sqlite] Insert multiple entries in a single INSERT statement

[Hoover, Jeffrey]

(apologies, I had a typo in my code sample, here is a correction)
if your are using Perl you can "bulk insert" the data...
.
.
.
use DBI;
our $errorMessage;
.
.
.
my @mydata = ();
{
my @myrow = (1,1);
push @mydata, \...@myrow;
}
{
my @myrow = (2,2);
push @mydata, \...@myrow;
}
{
my @myrow = (3,3);
push @mydata, \...@myrow;
}
my $numinserts = ($dbh, "insert into
foo(col1,col2)
values (?,?)", \...@mydata );
if ( !defined $numinserts ) { die $errorMessage }
.
.
.
sub bulkInsertData {
my ($dbh,$insertSQL,$rowdata) = @_;
$errorMessage = undef;

# prepare insert statement
my $stmt = $dbh->prepare($insertSQL);
if ( !defined $stmt) {
$errorMessage = "bulkInsertData could not prepare SQL
$insertSQL: $DBI::errstr";
return undef;
}

# if no data return without doing anything  
my @data = @$rowdata;
my $nrows = scalar @data;
if ( $nrows == 0 ) { return 0; }

# insert data into table
my @rowStatus = ();
my $numInserted = $stmt->execute_array( { ArrayTupleStatus =>
\...@rowstatus, ArrayTupleFetch => sub{ shift @data } } );
if ( ! defined $numInserted ) {
$errorMessage = "bulkInsertData insert failed: " .
(\...@rowstatus);
return undef;
} elsif ( ! $numInserted == $nrows ) {
my $numFailed = $nrows - $numInserted;
$errorMessage = "bulkInsertData $numFailed rows failed
to insert: " . (\...@rowstatus);
return undef;
} else {
$errorMessage = undef;
}

return $numInserted;
}

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman
(Firaxis)
Sent: Tuesday, July 28, 2009 2:22 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Insert multiple entries in a single INSERT statement

Hello,

Looking at the SQL syntax chart it doesn't seem like
this is possible.  In other SQL servers I'm able to use the statement
"insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
'3');".  Is this possible in SQLite or must I instead insert one at a
time.

 

-Shaun

 

 

___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert multiple entries in a single INSERT statement

[Rich Shepard]

On Tue, 28 Jul 2009, Shaun Seckman (Firaxis) wrote:

>   Looking at the SQL syntax chart it doesn't seem like this is possible. 
> In other SQL servers I'm able to use the statement "insert into
> foo('col1', col2') values ('1', '1'), ('2', '2'), ('3', '3');".  Is this
> possible in SQLite or must I instead insert one at a time.

Shaun,

   I've only seen single values per row in the references and that's what
I've done both programmatically and in the shell. Programmatically, use a
loop for new values. In the shell, I write a .sql file that has the INSERT
INTO statements in a transaction.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert multiple entries in a single INSERT statement

[Hoover, Jeffrey]

if your are using Perl you can "bulk insert" the data...
.
.
.
use DBI;
our $errorMessage;
.
.
.
my @mydata = ();
{
my @myrow = (1,1);
push @mydata, \...@myrow;
}
{
my @myrow = (2,2);
push @mydata, \...@myrow;
}
{
my @myrow = (3,3);
push @mydata, \...@myrow;
}
my $numinserts = ($dbh, "foo('col1', col2')
values (?,?)", \...@mydata );
if ( !defined $numinserts ) { die $errorMessage }
.
.
.
sub bulkInsertData {
my ($dbh,$insertSQL,$rowdata) = @_;
$errorMessage = undef;

# prepare insert statement
my $stmt = $dbh->prepare($insertSQL);
if ( !defined $stmt) {
$errorMessage = "bulkInsertData could not prepare SQL
$insertSQL: $DBI::errstr";
return undef;
}

# if no data return without doing anything  
my @data = @$rowdata;
my $nrows = scalar @data;
if ( $nrows == 0 ) { return 0; }

# insert data into table
my @rowStatus = ();
my $numInserted = $stmt->execute_array( { ArrayTupleStatus =>
\...@rowstatus, ArrayTupleFetch => sub{ shift @data } } );
if ( ! defined $numInserted ) {
$errorMessage = "bulkInsertData insert failed: " .
(\...@rowstatus);
return undef;
} elsif ( ! $numInserted == $nrows ) {
my $numFailed = $nrows - $numInserted;
$errorMessage = "bulkInsertData $numFailed rows failed
to insert: " . (\...@rowstatus);
return undef;
} else {
$errorMessage = undef;
}

return $numInserted;
}

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman
(Firaxis)
Sent: Tuesday, July 28, 2009 2:22 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Insert multiple entries in a single INSERT statement

Hello,

Looking at the SQL syntax chart it doesn't seem like
this is possible.  In other SQL servers I'm able to use the statement
"insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
'3');".  Is this possible in SQLite or must I instead insert one at a
time.

 

-Shaun

 

 

___
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] Insert multiple entries in a single INSERT statement

[Kees Nuyt]

On Tue, 28 Jul 2009 14:22:28 -0400, "Shaun Seckman
(Firaxis)"  wrote:

>Hello,
>
>Looking at the SQL syntax chart it doesn't seem like
>this is possible.  In other SQL servers I'm able to use the statement
>"insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
>'3');".  Is this possible in SQLite or must I instead insert one at a
>time.

Insert one row at a time.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert multiple entries in a single INSERT statement

[Shaun Seckman (Firaxis)]

Hello,

Looking at the SQL syntax chart it doesn't seem like
this is possible.  In other SQL servers I'm able to use the statement
"insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
'3');".  Is this possible in SQLite or must I instead insert one at a
time.

 

-Shaun

 

 

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

Re: [sqlite] search time is non-determinate in multi-thread enviroment

[Jay A. Kreibich]

On Tue, Jul 28, 2009 at 07:59:23AM -0400, Igor Tandetnik scratched on the wall:
> pierr wrote:
> >   Any insight? Should I go with THREADSAFE=2 and two connections: one
> > for read ,another for write? Thanks.
> 
> THREADSAFE is not a number of allowed concurrent threads. It's a boolean 
> switch - 0 means non-threadsafe, non-zero means thread-safe. 
> THREADSAFE=2 won't make any difference.

  THREADSAFE=1 is "Serialize mode" and THREADSAFE=2 is "Multithread"
  mode.  THREADSAFE=2 (basic Multithread) actually offers less protection,
  requiring the application to provide its own locks to prevent multiple
  threads from accessing the DB at the same time, while THREADSAFE=1
  (Serialize) handles all that for you.  Generally, unless you're doing
  a lot of thread and lock management yourself, you want to use =1.

  http://sqlite.org/compile.html#threadsafe
  http://sqlite.org/threadsafe.html

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search time is non-determinate in multi-thread enviroment

[Igor Tandetnik]

pierr wrote:
> Hi,
>   My sqlite is configure as serialized (THREADSAFE=1). My application
> has only one connection but it is shared by two thread. One thread
> will do insert/update/delete in the background , another thread will
> do the select upon request of the user from gui.
>  Typically, we will call following function 12 times (with different
> parameters ,of course)before showing the pages to user. In the
> standalone test (single  thread), a call to following function will
> take less than 40ms, so 12 times will take less than 500 ms and it is
> acceptable. However, in the real application, sometimes this function
> took 1000ms to return the result which make the gui slow.

When the second thread makes SQLite calls, it takes a lock on the 
connection. If the UI thread runs a SELECT at this moment, it also tries 
to take that lock, and so has to wait until the second thread releases 
it.

>   Any insight? Should I go with THREADSAFE=2 and two connections: one
> for read ,another for write? Thanks.

THREADSAFE is not a number of allowed concurrent threads. It's a boolean 
switch - 0 means non-threadsafe, non-zero means thread-safe. 
THREADSAFE=2 won't make any difference.

Further, you cannot open two connections to the same in-memory database.

Igor Tandetnik 



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

[sqlite] DATETIME and storage type

[Rael Bauer]

Hi,

If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table
"notes" add column "last_modified" DATETIME default "2001-01-01";) will the
declared default value be stored as a string or real value?

Also, more generally, how can I find out what storage type field values have
been stored in?

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

Re: [sqlite] How to dump cookies.sqlite

[Kees Nuyt]

On Mon, 27 Jul 2009 20:39:34 -0700 (PDT), Mutiullah Qureshi
 wrote:

>Hello friends.
>I have downloaded sqlite3-3.6.16.bin.gz and extracted sqlite3-3.6.16.bin in 
>fedora 9. Now I want to dump cookies.sqlite from mozila firefox cache to 
>cookies.tmp. I have tried:
>
>sqlite> .dump cookies.sqlite cookies.tmp

You are already in sqlite there. It is unclear which
database you specified on the command line.


It's a one liner (from the shell prompt):

sqlite3 /path/to/cookies.sqlite .dump >cookies.tmp

Note: the optional parameter of .dump is not a database, but
one or more table names. Without parameter .dump dumps all
tables.

For more help use
(from the shell prompt)
sqlite3 -help 
for command line options

(from the sqlite prompt)
sqlite> .h
for the command line tool commands.

>BEGIN TRANSACTION;
>COMMIT;
>sqlite> 
>
>But this does not create any cookies.tmp. 
>I am a newbie on this environment, 
>just switched from Windows systems. PLease guide me.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] hi - sqlite3 advantages

[Kees Nuyt]

On Mon, 27 Jul 2009 22:54:18 -0430, An 
wrote:

>Hi !
>
>SQLite is a really cool database...
>
>I currently still use version 2... I would like the insight of whoever feels
>like it of some advantages of version 3 over version 2 maybe i get
>convinced on the importance to make the version evolution, or possible mayor
>advantages ; )

V2 is frozen, v3 being developed. 
v3 has more features. 
More optimizations. 
More people know v3 so you get more support.

http://www.sqlite.org/news.html
http://www.sqlite.org/oldnews.html
http://www.sqlite.org/docs.html
http://www.sqlite.org/changes.html
http://www.sqlite.org/lockingv3.html

That being said, if you have a stable application that works
fine with v2 and you're not planning major functional
updates, v2 will do fine. 
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search time is non-determinate in multi-thread enviroment

[pierr]

pierr wrote:
> 
> Hi,
>My sqlite is configure as serialized (THREADSAFE=1). My application has
> only one connection but it is shared by two thread. One thread will do
> insert/update/delete in the background , another thread will do the select
> upon request of the user from gui. 
>   Typically, we will call following function 12 times (with different
> parameters ,of course)before showing the pages to user. In the standalone
> test (single  thread), a call to following function will take less than
> 40ms, so 12 times will take less than 500 ms and it is acceptable.
> However, in the real application, sometimes this function took 1000ms to
> return the result which make the gui slow.
> 
>  int search_eit_event_in_time_inteval(...)
>  86 {
>  93 
>  94 sql = (char *)" SELECT *,rowid FROM tblEvent_basic "
>  95   " WHERE "
>  96   " service_id = ? AND "
>  97   " start_time < ? AND end_time > ? "
>  98   " ORDER by start_time ASC";
>  }
> 
>  I did some experiment try to understand why this happend? Here is the
> code I used :
>  http://pastebin.ca/1509723
> 
>  I found :
>  1. When  i create a write_thread
> a. with usleep(1) in while(1) 
> search time always < 50ms
> b. without usleep(1) in while(1) 
>  17.7 % search time > 50ms , usually 200ms
>   
>  2. When  i create another_thread (no database access at all)
> a. with usleep(1) in while(1) 
> search time always < 50ms
> b. without usleep(1) in while(1) 
>  21.7 % search time > 50ms , usually 200ms.
> 
>Any insight? Should I go with THREADSAFE=2 and two connections: one for
> read ,another for write? Thanks. 
> 
I was using memory database. This should be impormant background information
:)
-- 
View this message in context: 
http://www.nabble.com/search-time-is-non-determinate-in-multi-thread-enviroment-tp24693604p24693647.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] search time is non-determinate in multi-thread enviroment

[pierr]

Hi,
   My sqlite is configure as serialized (THREADSAFE=1). My application has
only one connection but it is shared by two thread. One thread will do
insert/update/delete in the background , another thread will do the select
upon request of the user from gui. 
  Typically, we will call following function 12 times (with different
parameters ,of course)before showing the pages to user. In the standalone
test (single  thread), a call to following function will take less than
40ms, so 12 times will take less than 500 ms and it is acceptable. However,
in the real application, sometimes this function took 1000ms to return the
result which make the gui slow.

 int search_eit_event_in_time_inteval(...)
 86 {
 93 
 94 sql = (char *)" SELECT *,rowid FROM tblEvent_basic "
 95   " WHERE "
 96   " service_id = ? AND "
 97   " start_time < ? AND end_time > ? "
 98   " ORDER by start_time ASC";
 }

 I did some experiment try to understand why this happend? Here is the code
I used :
 http://pastebin.ca/1509723

 I found :
 1. When  i create a write_thread
a. with usleep(1) in while(1) 
search time always < 50ms
b. without usleep(1) in while(1) 
 17.7 % search time > 50ms , usually 200ms
  
 2. When  i create another_thread (no database access at all)
a. with usleep(1) in while(1) 
search time always < 50ms
b. without usleep(1) in while(1) 
 21.7 % search time > 50ms , usually 200ms.

   Any insight? Should I go with THREADSAFE=2 and two connections: one for
read ,another for write? Thanks. 
-- 
View this message in context: 
http://www.nabble.com/search-time-is-non-determinate-in-multi-thread-enviroment-tp24693604p24693604.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