Re: [sqlite] selecting unique list of latest timestamps

[Igor Tandetnik]

Mr. Puneet Kishor  wrote:
> CREATE TABLE uris (
> uri_id INTEGER PRIMARY KEY,
> uri TEXT
> );
> 
> 
> CREATE TABLE history (
> history_id INTEGER PRIMARY KEY,
> uri_id INTEGER,
> downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP
> );
>
> I am looking for an efficient way to select the uris, and the latest 
> 'downloaded_on' time stamp for each uri, but am drawing a
> blank.

select uris.uri_id, uris.uri, max(downloaded_on)
from uris join history using (uri_id)
group by uris.uri_id;

-- 
Igor Tandetnik

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

[sqlite] selecting unique list of latest timestamps

[Mr. Puneet Kishor]

I have a bunch of uris stored in a table

CREATE TABLE uris (
uri_id INTEGER PRIMARY KEY, 
uri TEXT
);

uri_id  uri 
--  --
1   http://foo.com
2   http://bar.com
3   http://baz.com
4   http://qux.com

A program periodically downloads the content of the above web sites. Another 
table stores the download history, that is, when the content was last 
downloaded.

CREATE TABLE history (
history_id INTEGER PRIMARY KEY, 
uri_id INTEGER, 
downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP
);

history_id  uri_id  downloaded_on  
--  --  ---
1   4   2011-05-04 02:25:09
2   3   2011-05-03 02:34:08
3   1   2011-05-01 02:50:43
4   2   2011-05-02 02:50:45
6   4   2011-05-14 02:50:48

The content itself is stored in an FTS4 table linked by history_id.

CREATE VIRTUAL TABLE fts_uri (
history_id, content
);

I am looking for an efficient way to select the uris, and the latest 
'downloaded_on' time stamp for each uri, but am drawing a blank. The list 
should look like so

uri_id  uri downloaded_on
--  --  ---
1   http://foo.com  2011-05-01 02:50:43
2   http://bar.com  2011-05-02 02:50:45
3   http://baz.com  2011-05-03 02:34:08
4   http://qux.com  2011-05-14 02:50:48

Suggestions?

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

[sqlite] C++ Sample Code

[Don Ireland]

Can anyone suggest a good source for sample C++ code?  I really would 
rather not come here and ask for every little thing--I'd learn it better 
this way.

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

Re: [sqlite] Can't send messages to list from pc?

[Don Ireland]

Thanks. I saw the message too--at least now I know to send as plain text.

Don Ireland

-Original Message-
From: Simon Slavin 
To: General Discussion of SQLite Database 
Sent: Sat, 14 May 2011 2:35 PM
Subject: Re: [sqlite] Can't send messages to list from pc?


On 14 May 2011, at 5:17pm, Don Ireland wrote:

> We'll see if this works.  I'm sending it from my pc and I sent it as 
> plain text.

I can see that message.  So there's a chance that your rich text messages are 
being sent as attachments and/or without a plaintext equivalent, so the system 
rejects them.

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

Re: [sqlite] Can't send messages to list from pc?

[Don Ireland]

Thanks. I saw the message too--at least

Don Ireland

-Original Message-
From: Simon Slavin 
To: General Discussion of SQLite Database 
Sent: Sat, 14 May 2011 2:35 PM
Subject: Re: [sqlite] Can't send messages to list from pc?


On 14 May 2011, at 5:17pm, Don Ireland wrote:

> We'll see if this works.  I'm sending it from my pc and I sent it as 
> plain text.

I can see that message.  So there's a chance that your rich text messages are 
being sent as attachments and/or without a plaintext equivalent, so the system 
rejects them.

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

Re: [sqlite] Can't send messages to list from pc?

[Simon Slavin]

On 14 May 2011, at 5:17pm, Don Ireland wrote:

> We'll see if this works.  I'm sending it from my pc and I sent it as 
> plain text.

I can see that message.  So there's a chance that your rich text messages are 
being sent as attachments and/or without a plaintext equivalent, so the system 
rejects them.

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

Re: [sqlite] correct extension to use for sqlite3

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/13/2011 09:13 PM, Igor Tandetnik wrote:
> prad  wrote:
>> i've seen .db and .sqlite and both work.
>> however, is there a rational for one or the other (eg firefox sqlite
>> plugin looks for .sqlite)?
> 
> SQLite itself doesn't care. Each application is free to choose its own 
> convention.

Your operating system may however outsmart you.  For example if you pick
'sdb' for SQLite database you'll find your code running very slowly.  The
gory reasons why are the bottom section of:

  http://www.sqlite.org/cvstrac/wiki/wiki?p=PerformanceTuningWindows

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk3O0W0ACgkQmOOfHg372QTN+wCgqL/UaRDAF+wKEfAiNlm7+6a5
FPcAoIpSe8eLgLy8fBs/uTmRas9Rl3ho
=Q8N3
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] valgrind & WAL w/3.7.6.2

[Steven Parkes]

> Upgrading zlib to the latest release (1.2.5) fixed all of my valgrind
> warnings

Thanks. I'll try to replicate ...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS & sqlite3_last_insert_rowid

[Steven Parkes]

> Please try the latest code checkin (
> http://www.sqlite.org/src/info/e569f18b98) and let me know if it works any
> better for you.

Thanks. I've already adjusted the code to manually assign keys, but I'll try to 
get back to checking it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common Multi-treaded Problem

[Pavel Ivanov]

> If on thread #1 using connection #1 does a step on a prepared select 
> statement and then is blocked before the sqlite3_column() statements and 
> tailing reset statement and thread #2 using connection #2 tries to do a step 
> of a different select prepared statement on the same DB, will the second 
> select step return a database lock error?

No, two select statements never block each other.


Pavel


On Fri, May 13, 2011 at 8:14 PM, John Deal  wrote:
> Hello Pavel,
>
> Many thanks on the extensive information.  I think I understand what you are 
> saying.  I do have a couple of questions.
>
> If on thread #1 using connection #1 does a step on a prepared select 
> statement and then is blocked before the sqlite3_column() statements and 
> tailing reset statement and thread #2 using connection #2 tries to do a step 
> of a different select prepared statement on the same DB, will the second 
> select step return a database lock error?  It just seems with what I 
> understand this is a race condition.  Of course with multi-core, thread #1 
> does not even have to block for this to happen.
>
> If the above is true, then it seems the solution is to use a single DB 
> connection and let the selects serialize.  I choose the separate 
> connection/separate thread model since that was implemented before the 
> separate thread/common connection model assuming the implementation of the 
> former was more established than the later.
>
> Again thanks for the information and I apologize for taking up so much list 
> bandwidth.  I hope others can benefit.
>
> John
>
> --- On Thu, 5/12/11, Pavel Ivanov  wrote:
>
>> From: Pavel Ivanov 
>> Subject: Re: [sqlite] Common Multi-treaded Problem
>> To: "General Discussion of SQLite Database" 
>> Date: Thursday, May 12, 2011, 9:53 PM
>> > Humm.  Resetting each prepared
>> statement right after use seemed to work.  So in review, a
>> select prepared statement will lock the DB from other
>> threads (or is it DB connections?) but not the current
>> thread (or is it DB connection).
>>
>> Yes, you are right. Transactions and database locks are
>> per-connection. So if you work with only one connection
>> (even sharing
>> it between different threads) and not resetting your
>> statements it
>> means that all your application works in a one huge
>> transaction,
>> committing everything at the end (I guess if you hard kill
>> your
>> application in the middle you'll see that nothing was
>> committed to the
>> database). And if you work with several different
>> connections (no
>> matter in different threads or in a single thread) they
>> will block
>> each other, i.e. if you execute writing transaction in one
>> connection
>> you won't be able to write in a second connection and
>> sometimes you
>> won't even able to read in a second connection.
>>
>> And answering your question from another email: you can
>> step through
>> any number of prepared statements simultaneously as long as
>> they are
>> all created from the same connection. They won't block each
>> other from
>> executing. You just can't call sqlite3_step() on one
>> connection
>> simultaneously in several threads - they will be
>> serialized. Other
>> than that you are free to step through any number of select
>> statements
>> and execute updates in parallel. But there's one rule of
>> thumb to
>> remember: never change data that should be returned in some
>> active
>> select statement. You can get very surprising behavior in
>> this case.
>>
>>
>> Pavel
>>
>>
>> On Thu, May 12, 2011 at 8:33 PM, John Deal 
>> wrote:
>> > Hello Pavel,
>> >
>> > Humm.  Resetting each prepared statement right after
>> use seemed to work.  So in review, a select prepared
>> statement will lock the DB from other threads (or is it DB
>> connections?) but not the current thread (or is it DB
>> connection).
>> >
>> > Thanks for the help!
>> >
>> > John
>> >
>> > --- On Thu, 5/12/11, Pavel Ivanov 
>> wrote:
>> >
>> >> From: Pavel Ivanov 
>> >> Subject: Re: [sqlite] Common Multi-treaded
>> Problem
>> >> To: "General Discussion of SQLite Database" 
>> >> Date: Thursday, May 12, 2011, 5:58 PM
>> >> > Interesting is the impression I
>> >> had with prepared statements was the reset was
>> only
>> >> necessary if you wanted to reuse that statement.
>>  Since
>> >> each each DB connection is in its own instance of
>> a class
>> >> (with it own set of prepared statements) I would
>> not think
>> >> there would be any dependency on different
>> physical prepared
>> >> statements on different threads.  I would expect
>> this with
>> >> incomplete transactions.
>> >>
>> >> There's no dependency between different prepared
>> >> statements, but there
>> >> is dependency between transactions as they use the
>> same
>> >> database. And
>> >> transaction cannot be finished (implicitly or
>> 

Re: [sqlite] Can't send messages to list from pc?

[Don Ireland]

We'll see if this works.  I'm sending it from my pc and I sent it as 
plain text.


On 5/13/2011 5:40 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 05/13/2011 02:35 PM, Don Ireland wrote:
>> Anybody have any ideas?  This is really odd.
> Two possibilities:
>
> - - You have different from addresses configured on the phone and desktop.
> There is no way for the mailing list to know they are the same person.
>
> - - Your desktop email gets rejected because your home system is directly or
> part of a block of IP addresses that are on a RBL
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk3Ns28ACgkQmOOfHg372QTRTwCcDYZrbzoGYVzHtPQ35m9LBR2u
> t8YAnR4pkTOmdOT02eVh7PLYgnvbRbVl
> =Vmb4
> -END PGP SIGNATURE-
> ___
> 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] Can't send messages to list from pc?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/13/2011 09:33 PM, Don Ireland wrote:
> 1) if I were sending from an address other than the subscribed one, I'd get 
> those bounce messages telling me that. But I get NO message at all. 
> I'll check the message headers over the weekend though. 

Not all mail implementations are diligent at getting you bounce messages and
some servers just drop stuff if over thresholds.  For example my mail server
will do SMTP rejects for RBL members but silent drops for messages with
spamassassin scores over 7.

> 2) both the DROID and pc send out via the same mail server. Isn't it the
server that would be blacklisted and not the device?

The device IP address will still be in the received headers and differ from
your desktop.  Your headers yesterday had random vzw headers and today
client.mchsi.com as origins while claiming to be com.flipdogsolutions.

In general you are lucky to be able to send email at all with this solution.
 In practise you should be ensuring that reverse DNS on your server IP
address results in a name containing "static" otherwise various RBL will
believe you are part of a dynamic range and should be using ISP servers for
email.

In any event you have a funky setup going on and will only work out what is
happening with the email by trial and error.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk3Ok1gACgkQmOOfHg372QRTLgCaA3TT8byKRm+NRn4md4+kBMV0
RpgAoJxRgf1QV4whSzEegIvgKoKM/Osv
=FRUP
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IN clause in search query to search a single field containing comma delimited values

[Trevor Borgmeier]

Thanks for the responses.  The three table approach you describe is what 
I normally use, but the export is coming from a filemaker database where 
they are stored this way so I thought I'd play with it as is. I was 
surprised when I tried a similar query with the same data in MySQL and 
saw that it worked -- MySQL parsed the fields contents as if they were 
separate items. Regardless, I think I'll write a quick script to 
normalize that data into its own table.

Thanks!



on 5/13/11 7:16 PM BareFeetWare wrote:
> On 14/05/2011, at 5:15 AM, Trevor Borgmeier wrote:
>
>> I have a database where a field's value is a comma separated list
>> indicating the basic categories the item belongs to.
>>
>> so if the "categories" field has a value of "1,8,15"
>>
>> I want to do a query like this:
>>
>> SELECT categories FROM myTable WHERE "8" IN (categories);
>>
>> but it only finds records where "8" is the only category...
> The "in" operator deals with sets (eg explicit list of items or the results 
> of a select), not text strings. You would use "in" like this:
>
> select * from MyTable where Category in (1, 8, 15)
> or:
> select * from MyTable where Category in (select Category from OtherTable 
> where OtherTable.Name = MyTableName)
>
> See:
> http://www.sqlite.org/lang_expr.html
> under the heading "The IN and NOT IN operators"
>
>> Is there anyway for it to evaluate the contents fo the categories field 
>> first rather than compare it as a whole?
> There is no function built into SQLite to convert a text string into a set 
> (eg convert "1,8,15" into (1, 8, 15)), but such a function is not needed in 
> this case. You need a better design of your database. SQLite is relational 
> and you need to make your schema relational.
>
>> The describe query works in MySQL, but the port doesn't... So far the hack 
>> is to do something like this...
>>
>> SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%";
>>
>> but I'm guessing LIKE isn't as efficient, and the query is more awkward.
> Yes, that will work but yes, it is inefficient. Again, it doesn't use the 
> relational engine that you have at your disposal.
>
>> Any advise would be appreciated.  Thanks!
> You need to "normalize" your data structure. One of the demands of a 
> normalized structure is that each column contains only one value. So instead 
> of having multiple Category values stored in the Categories column, you need 
> a separate table that lists each of the Categories linked to its MyTable row. 
> This might look something like this:
>
> create table MyTable
> ( ID integer primary key not null
> , Name text
> )
> ;
>
> create table Category
> ( ID integer primary key not null
> , Name text
> )
> ;
>
> create table "MyTable Category"
> ( ID integer primary key not null
> , MyTable integer not null references MyTable (ID)
> , Category integer not null references Category (ID)
> )
> ;
>
> Once it has some data, you could query like this:
>
> select Name
> from MyTable join "MyTable Category" on MyTable.ID = "MyTable 
> Category".MyTable
> where "MyTable Category".Category = 8
> ;
>
> If you're confused, please post your schema, including at least some data, 
> and I'll show you how it works in your case.
>
> Tom
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
>   --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> ___
> 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] SQLite search using IN clause

[Trevor Borgmeier]

I have a database where a field's value is a comma separated list 
indicating the basic categories the item belongs to.

so if the "categories" field has a value of "1,8,15"

I want to do a query like this:

SELECT categories FROM myTable WHERE "8" IN (categories);

but it only finds records where "8" is the only category...

Is there anyway for it to evaluate the contents fo the categories field 
first rather than compare it as a whole?

The describe query works in MySQL, but the port doesn't... So far the 
hack is to do something like this...

SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%";

but I'm guessing LIKE isn't as efficient, and the query is more 
awkward.  Any advise would be appreciated.  Thanks!

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