Re: [sqlite] Mixed ASC and DESC in single column

[Jim Morris]

On 11/21/2019 8:08 AM, Hamish Allan wrote:
> Thank you very much Clemens, but I've realised I've asked the wrong
> question.
>
> Is it possible to achieve the same if the identifiers are not unique?
>
> So for instance:
>
> CREATE TABLE IF NOT EXISTS Data (identifier TEXT, info TEXT);
>
> INSERT INTO Data VALUES ("id1", "foo/2");
> INSERT INTO Data VALUES ("id1", "bar/3");
> INSERT INTO Data VALUES ("id1", "baz/1");
>
> INSERT INTO Data VALUES ("id2", "foo/1");
> INSERT INTO Data VALUES ("id2", "bar/2");
> INSERT INTO Data VALUES ("id2", "baz/2");
>
> INSERT INTO Data VALUES ("id3", "foo/1");
> INSERT INTO Data VALUES ("id3", "bar/2");
> INSERT INTO Data VALUES ("id3", "baz/1");
>
> If I want the query to be like "bar ASC, foo DESC, baz ASC", it should
> return:
>
> id3 -- (bar/2, foo/1, baz/1)
> id2 -- (bar/2, foo/1, baz/2)
> id1 -- (bar/3)
>
> Or if I want a query like "baz DESC, foo DESC, bar ASC":
>
> id2 -- (baz/2)
> id1 -- (baz/1, foo/2)
> id3 -- (baz/1, foo/1)
>
> I tried:
>
> SELECT * FROM (SELECT identifier FROM Data WHERE info LIKE 'bar/%' ORDER BY
> info ASC)
> UNION
> SELECT * FROM (SELECT identifier FROM Data WHERE info LIKE 'foo/%' ORDER BY
> info DESC)
> UNION
> SELECT * FROM (SELECT identifier FROM Data WHERE info LIKE 'baz/%' ORDER BY
> info ASC);
>
> but of course this approach no longer works...
>
> Thanks again,
> Hamish
>
>
>
>
> On Thu, 21 Nov 2019 at 14:02, Clemens Ladisch  wrote:
>
>> Hamish Allan wrote:
>>> I want to get the uuids in order as if `foo`, `bar` and `bar` were
>>> different columns, e.g. if the desired order were "bar ASC, foo DESC, baz
>>> ASC"
>> SELECT * FROM (SELECT uuid FROM Data WHERE info LIKE 'bar/%' ORDER BY info
>> ASC)
>> UNION ALL
>> SELECT * FROM (SELECT uuid FROM Data WHERE info LIKE 'foo/%' ORDER BY info
>> DESC)
>> UNION ALL
>> SELECT * FROM (SELECT uuid FROM Data WHERE info LIKE 'baz/%' ORDER BY info
>> ASC);
>>
>>
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

A possible solution would be to create a computed field for sorting,
flexible but wordy. Something like:

Select *

from (Select Data.*

case when substr(info,1,4) = 'bar/' then '-' else '' end ||

case when substr(info,1,4) = 'baz/' then '-' else '' end ||

case when substr(info,1,4) = 'foo/' then '' else '' end as
sortfield

from Data

)

order by sortfield,...

)

The sort might also be another joined table


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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jim Morris]

Not sure this helps, a way to a conditionally insert based on if record
already exists, is a select with literals left outer joined to the maybe
record and use a where test value is null.

Something like this pseudo SQL

insert into T  (valueA, valueB') (select 'ValueA', 'ValueB' left outer
join T where T.valueA ='valueA' and T.valueA is null);


On 11/18/2019 2:14 PM, Simon Slavin wrote:
> On 18 Nov 2019, at 10:00pm, Jose Isaias Cabrera  wrote:
>
>> Thanks Keith.  So, you are saying that this is a bad INSERT, and I don't 
>> know much to argue, but is working. If I take out the first IfNull, and 
>> there is not, at least one instance of 'p006' in the table, the INSERT never 
>> works. I was thinking of using COALESCE, but that would also mean that one 
>> or the other would have to be not null. Any suggestion would be appreciated.
> Being completely serious, whenever I see "undocumented" or "implementation 
> dependent" or "optimization side-effect", or a SQL statement I can't parse in 
> my head, I usually decide to do it in my programming language instead.  This 
> simplifies testing and debugging, and makes things easier for the poor 
> engineer who has to understand my code.
>
> You can do clever things in a language like SQL which allows recursive 
> construction clauses.  But what strikes me as ingenious when I'm writing it 
> can look bizarre and impenetrable to me, or someone else, a year later.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use CASE statement to SUM() some numbers

[Jim Morris]

Maybe something like:

CREATE VIEW "Sum of Expenses Between two Dates" AS 
SELECT Date,
sum( CASE
  WHEN Date BETWEEN date('now', '-1 months') AND date('2019-11-04', 
'-1 days') THEN Expense
  ELSE 0
  END) as 'Sum of Expenses:'
  FROM Expenses;

On 11/15/2019 12:22 PM, David Raymond wrote:
> So why do you need a case? What will not work with the simple:
>
> select sum(Expense)
> from Expenses
> where Date between date('now', '-1 months') and date('2019-11-04', '-1 days');
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count error?

[Jim Morris]

Using a sub-select should work

select

(select count(n) from t0) as "t0_count",

(select count(n) from t1) as "t1_count",

(select count(n) from t2) as "t2_count"

;

On 11/1/2019 9:07 AM, Jose Isaias Cabrera wrote:
> Jose Isaias Cabrera, on Friday, November 1, 2019 11:51 AM, wrote...
>>
>> Richard Hipp, on Friday, November 1, 2019 11:41 AM, wrote...
>>> On 11/1/19, Jose Isaias Cabrera, on
 sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 
 AS
 b LEFT JOIN t2 AS c;
 3375|3375|3375

 Huh?  I expected the result:

 15|15|15
>>> You did a three-way join on tables with 15 rows each.  15x15x15 is 3375.
>>>
>>> A LEFT JOIN without an ON clause is just a JOIN.  Or, to view it
>>> another way, it is the same as having "ON true" on each LEFT JOIN.
>>> LEFT JOIN only differs from JOIN when the ON clause evaluates to false
>>> or NULL, in which case the right table of the LEFT JOIN pretends to be
>>> a table of a single row of all NULL values.
>> Thanks, Dr. Hipp.  Now I understand why the real query hung, and didn't 
>> produce a
>> response.  Sorry for the lack of knowledge. :-)  Anyone, out there, how do I 
>> get
>> the record count of three tables in one call?  Thanks.
> So, I got it to work in 3 rows,
>
> select count(n) from t0 UNION ALL Select count(n) FROM t1 UNION ALL SELECT 
> count(n) FROM t2;
> 15
> 15
> 15
>
> But, is there a way to get it to work on one row?  Sorry for the newbie post. 
>  Thanks.
>
> josé
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Jim Morris]

In your application you can create a wrapping iterator that pre-reads
the next value instead or directly accessing the low level step
function.  Then you can ask if it is the last.

On 10/15/2019 9:44 AM, Simon Slavin wrote:
> On 15 Oct 2019, at 5:38pm, Philippe RIO <51...@protonmail.ch> wrote:
>
>> The only way is to make a query for getting the number of records and in the 
>> second query I have to count the number of records retrieved to know if it 
>> is the last one.
> Assume your intended query specifies an order which has unique keys (in other 
> words, you know exactly what order rows will be answered in.  Suppose it is
>
> SELECT ... ORDER BY a, b DESC, c
>
> You can find the last row which will be returned by reversing the order and 
> adding LIMIT 1
>
> SELECT ... ORDER BY a DESC, b ASC, c DESC LIMIT 1
>
> You can then remember the value(s) of some column(s) of the row returned, and 
> watch for the same one(s) when you do your desired SELECT.
>
> Depending on the number of rows in your result set, this may or may not be 
> faster than counting the number of rows.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Jim Morris]

What is the journal mode?

On 3/12/2019 10:30 AM, Ted Goldblatt wrote:

On Tue, Mar 12, 2019 at 11:45 AM James K. Lowden 
wrote:


On Tue, 12 Mar 2019 10:36:37 -0400
ted.goldbl...@gmail.com wrote:


The problem is basically that as part of a test, the customer wants
to power fail the device, and then to be able to recover (after the
device restarts) data that was generated/stored as close to the power
failure as possible.

...

Unfortunately, when doing this, it appears that significant amounts
of data that should be in the database are missing and/or corrupted,
and this doesn?t appear particularly dependent on when the failure
occurs.

Only 3 possibilities exist:

1.  SQLite finished its transaction before the power cut, and the data
were committed and do appear in the database later.


This is not the case


2.  SQLite did not finish its transaction, and the transaction was
rolled back as part of database initialization after power was
restored.

This is also not the case


3.  The hardware or driver reported the data were written when they
were not.


This *may* be the case


We can dismiss as statistically insignificant possibility #4, a bug in
SQLite, because of SQLite's excellent testing regimen and gigantic user
base.

I have been writing software for too many decades to casually dismiss the
possibilities of software bugs.  If there couldn't be bugs in SQLite, there
would have been no bug fixes since the version being used here, and having
briefly perused the revision history, it is obvious that isn't the case.
Further, different users run in different environments and do different
things, both of which can shake loose bugs or unexpected behaviors.  That
said, I don't think what is going on here is a "oops" type bug, though it
could be an interaction (or configuration) issue - possibly something that
could have been addressed in one way or other over the last 8 years, hence
my question.



If you can confirm that SQLite finished the transaction whose data the
database does not reflect on restart, you really must suspect the
driver or device.  I don't know much about USB drives, but consumer
grade hard drives *normally* lie about committed data for performance
reasons.  "It's easy to make it fast if it doesn't have to be right."
USB devices face at least as much temptation to misrepresent their
performance.


I can confirm the state of the database after restart.  As far as the
backing device - this is an industrial device.  The storage is not USB,
consumer grade, or a hard drive.  It is NAND flash parts soldered to the PC
board and directly addressed by the (locally written) low-level device
driver.  I can assure you that there was no one to be impressed by any
performance numbers - that isn't an issue.  What may be an issue, however,
is the (real-time OS supplied) file system driver, which *could* do
buffering and *might *not honor sync() properly.  I am currently looking
into that.




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

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

Re: [sqlite] Can this be done with SQLite

[Jim Morris]

Wouldn't the mod operator do this?

Do an update and set key = 1 + (5 + key)%5


On 1/22/2018 12:38 PM, David Raymond wrote:

Unless I'm reading you wrong then just do the normal

begin transaction;
update playYouTubeVideo set speed = (
 select speed from playYouTubeVideo where key = '2')
 where key = '1';
update playYouTubeVideo set speed = (
 select speed from playYouTubeVideo where key = '3')
 where key = '2';
...
update playYouTubeVideo set speed = (
 select speed from playYouTubeVideo where key = '5')
 where key = '1';
commit;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Cecil Westerhof
Sent: Monday, January 22, 2018 3:30 PM
To: SQLite mailing list
Subject: [sqlite] Can this be done with SQLite

I have the following table:
CREATE TABLE playYouTubeVideo (
 key TEXTNOT NULL,
 speed   FLOAT   NOT NULL,

 CONSTRAINT key   CHECK(length(key)  == 1),
 CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),

 PRIMARY KEY(key)
);


Say I want to rotate a part:
- The value by key '1' becomes the value by key '2'.
​- The value by key '2' becomes the value by key '3'.
​- The value by key '3' becomes the value by key '4'.
- The value by key '4' becomes the value by key '5'.
- The value by key '5' becomes the value by key '6'.

​I suppose that I need to do this programmatically, or can this be done
with SQL?

And optionally also:
​- The value by key '1' becomes the value by key '5'.
​


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

Re: [sqlite] Binding an order by

[Jim Morris]

What you may be able to do is to use a case statement(s) which uses a 
bound variable to either a column or dummy


E.g

order by case orderControlValue = 1 then column1 else "" end, ...


On 10/5/2017 11:51 AM, Igor Tandetnik wrote:

On 10/5/2017 2:45 PM, Stephen Chrzanowski wrote:

Given the query:

select EventID, Airline, ContactInfo,TicketID,CreateDate from tEvents 
where

Resolved=:Resolved order by :OrderBy

I wanted to bind :OrderBy with field names


You can't.  A bound parameter can only appear where a literal is 
allowed by syntax. Field names you'll have to embed directly into the 
query.


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

[sqlite] Incremental backup/sync facility?

[Jim Morris]

Doesn't this eliminate the use of prepared statements?


On 5/6/2016 11:10 AM, Jeffrey Mattox wrote:
> As an aside, this is how Apple syncs Core Data to iCloud (and then to 
> multiple iOS devices) if the backing store uses SQLite (the default).  When a 
> small amount of data changes (which is common), the changes get send out, not 
> the entire (mostly unchanged and potential huge) database.
>
> Jeff
>
>
>> On May 6, 2016, at 7:43 AM, Simon Slavin  wrote:
>>
>> Believe it or not, the fastest way to synchronise the databases is not to 
>> synchronise the databases.  Instead you keep a log of the instructions used 
>> to modify the database.  You might, for example, modify the library that you 
>> use for INSERT, DELETE and UPDATE commands to execute those commands and 
>> also save the command to another 'commandLog' table
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Parsing the contents of a field

[Jim Morris]

Might be doable with a recursive CTE

On 1/13/2016 1:22 AM, Bart Smissaert wrote:
> It probably can be done with just SQLite's built-in text functions such as
> instr and substr,
> although with 20 to 30 items it may get a bit messy and complex.
>
> RBS
>
> On Wed, Jan 13, 2016 at 5:42 AM, audio muze  wrote:
>
>> I have a table of roughly 500k records with a number of fields
>> containing delimited text that needs to be parsed and written to
>> separate tables as a master lists.  In order to do this I need to
>> parse the field contents, however, I don't see any functions within
>> SQLite to enable that.  The number of delimited entries embedded in a
>> field can vary from none to as man as 20/30.  Is there an addin I can
>> compile with SQLite that provides the ability to parse a string?
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MultiThread Error 10 on Windows Mobile 6.x

[Jim Morris]

Problem is trying to get a shared lock in sqlite.c, "static int 
winLock(sqlite3_file *id, int locktype)"
at line 37428:
while( cnt-->0 && (res = winLockFile(>h, SQLITE_LOCKFILE_FLAGS,
  PENDING_BYTE, 0, 1, 0))==0 ){
cnt = 2
locktype = 1//SHARED_LOCK
lastErrno = 6//ERROR_INVALID_HANDLE

Thoughts?

On 11/3/2015 6:12 PM, Jim Morris wrote:
> I have a connection used by the main (UI) thread and recently added a 
> background thread, with own connection(no shared cache) to upload 
> changes to server on Windows Mobile 6.0/6.5 devices and upgraded to 
> SQLite 3.9.2.  The background thread is getting an error 10, extended 
> error 3850 as a result of a step when reading a result set in the 
> background thread.  I'm unclear on the cause of this error.
>
> Is this likely to be a logic error in my code?   E.g. locking, 
> statements not reset, transactions open or an OS related issue on the 
> device.  Possibly unrelated, as I recall we needed to go to journal 
> mode "PERSIST" years ago to resolve an OS NVFS issue. Most of the 
> reads are not in transactions and most of the writes are in 
> transactions that are "BEGIN IMMEDIATE TRANSACTION".  Busy timeout is 
> 30 seconds.
>
> Anyone else still using Windows Mobile 6.x devices?
>
> Is there a graceful work around?
>
>
>  (3850) SQLITE_IOERR_LOCK
>
> The SQLITE_IOERR_LOCK error code is an extended error code 
> <https://www.sqlite.org/rescode.html#pve> for SQLITE_IOERR 
> <https://www.sqlite.org/rescode.html#ioerr> indicating an I/O error in 
> the advisory file locking logic. Usually an SQLITE_IOERR_LOCK error 
> indicates a problem obtaining a PENDING lock 
> <https://www.sqlite.org/lockingv3.html#pending_lock>. However it can 
> also indicate miscellaneous locking errors on some of the specialized 
> VFSes <https://www.sqlite.org/vfs.html> used on Macs.
>
> Thanks
>
>
>
> On 11/3/2015 10:42 AM, R.Smith wrote:
>>
>>
>> On 2015-11-03 06:31 PM, ALBERT Aur?lien wrote:
>>> Hi,
>>>
>>> I'm often getting the "Database is locked" error message and I don't 
>>> really understand what can lead to this.
>>>
>>> I'm using these settings :
>>>- sqlite3_config(SQLITE_CONFIG_MULTITHREAD)
>>>- multiple threads
>>>- only one sqlite connection per thread
>>>- each thread have a different connection instance
>>>
>>> Could anyone clarify some things for me ?
>>>
>>> 1/ Can different threads perform read at the same time (I think yes)
>> Yes - at least, SQLite will handle the requests in a way that seems 
>> simultaneous.
>>
>>> 2/ Can different threads perform write at the same time (I think no)
>> Nothing can write simultaneously
>>
>>> 3/ Can a thread perform read while another single one is performing 
>>> write ? (I think yes)
>> Depends on the transaction and Journaling mode. See:
>> https://www.sqlite.org/lockingv3.html#writing
>> https://www.sqlite.org/lockingv3.html#rollback
>> In fact, that entire page should be of interest to you.
>>
>> Also, WAL journal mode is inherently good at this. See:
>> https://www.sqlite.org/wal.html#concurrency
>>
>>> 4/ Can a thread perform read while another thread has started a 
>>> transaction ? (I think yes)
>> Only if it isn't an exclusive transaction and has not started a write 
>> operation, or WAL mode is used, or it is the same connection with 
>> read_uncommitted turned on... actually, there are a lot of "if"s here.
>>
>> In general, it is best to view a "Transaction" as a locked state and 
>> plan accordingly, and if you absolutely need to read simultaneously 
>> from elsewhere, be prepared to take special steps in your code and 
>> read up on the modes.
>>
>>> My "Database is locked" problem seems to only appears in situation 
>>> number 4, may I am wrong on this one ? Maybe on others ?
>>
>> To put this shortly - you cannot do two things at the same time in a 
>> database.
>> Most contentions can be (and are) resolved internally with simple 
>> rules, but as soon as a more prominent contention appears (and there 
>> are many ways this could happen) then one reader/writer thread will 
>> have to wait a little bit until a previous is finished. SQLite's 
>> "timeout" setting (as mentioned and linked by Simon already) should 
>> fix 99% of these without making you add any extra code.
>>
>> There may be one or two extreme cases or race conditions that 
>> requires special consideration, but mostly setting the timeout should 
>> suffice. If you still get locked problems - please post again.
>>
>> Cheers,
>> Ryan
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MultiThread Error 10 on Windows Mobile 6.x

[Jim Morris]

No.  The app works, however when I scroll the list there are may 
requests to get records from the DB and it seems to interact with the 
background thread to produce this error.


On 11/3/2015 7:02 PM, Joe Mistachkin wrote:
> Jim Morris wrote:
>> Is there a graceful work around?
>>
>>
>>(3850) SQLITE_IOERR_LOCK
>>
> Are any of the underlying database files read-only?
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MultiThread Error 10 on Windows Mobile 6.x

[Jim Morris]

I have a connection used by the main (UI) thread and recently added a 
background thread, with own connection(no shared cache) to upload 
changes to server on Windows Mobile 6.0/6.5 devices and upgraded to 
SQLite 3.9.2.  The background thread is getting an error 10, extended 
error 3850 as a result of a step when reading a result set in the 
background thread.  I'm unclear on the cause of this error.

Is this likely to be a logic error in my code?   E.g. locking, 
statements not reset, transactions open or an OS related issue on the 
device.  Possibly unrelated, as I recall we needed to go to journal mode 
"PERSIST" years ago to resolve an OS NVFS issue.  Most of the reads are 
not in transactions and most of the writes are in transactions that are 
"BEGIN IMMEDIATE TRANSACTION".  Busy timeout is 30 seconds.

Anyone else still using Windows Mobile 6.x devices?

Is there a graceful work around?


  (3850) SQLITE_IOERR_LOCK

The SQLITE_IOERR_LOCK error code is an extended error code 
 for SQLITE_IOERR 
 indicating an I/O error in 
the advisory file locking logic. Usually an SQLITE_IOERR_LOCK error 
indicates a problem obtaining a PENDING lock 
. However it can 
also indicate miscellaneous locking errors on some of the specialized 
VFSes  used on Macs.

Thanks



On 11/3/2015 10:42 AM, R.Smith wrote:
>
>
> On 2015-11-03 06:31 PM, ALBERT Aur?lien wrote:
>> Hi,
>>
>> I'm often getting the "Database is locked" error message and I don't 
>> really understand what can lead to this.
>>
>> I'm using these settings :
>>- sqlite3_config(SQLITE_CONFIG_MULTITHREAD)
>>- multiple threads
>>- only one sqlite connection per thread
>>- each thread have a different connection instance
>>
>> Could anyone clarify some things for me ?
>>
>> 1/ Can different threads perform read at the same time (I think yes)
> Yes - at least, SQLite will handle the requests in a way that seems 
> simultaneous.
>
>> 2/ Can different threads perform write at the same time (I think no)
> Nothing can write simultaneously
>
>> 3/ Can a thread perform read while another single one is performing 
>> write ? (I think yes)
> Depends on the transaction and Journaling mode. See:
> https://www.sqlite.org/lockingv3.html#writing
> https://www.sqlite.org/lockingv3.html#rollback
> In fact, that entire page should be of interest to you.
>
> Also, WAL journal mode is inherently good at this. See:
> https://www.sqlite.org/wal.html#concurrency
>
>> 4/ Can a thread perform read while another thread has started a 
>> transaction ? (I think yes)
> Only if it isn't an exclusive transaction and has not started a write 
> operation, or WAL mode is used, or it is the same connection with 
> read_uncommitted turned on... actually, there are a lot of "if"s here.
>
> In general, it is best to view a "Transaction" as a locked state and 
> plan accordingly, and if you absolutely need to read simultaneously 
> from elsewhere, be prepared to take special steps in your code and 
> read up on the modes.
>
>> My "Database is locked" problem seems to only appears in situation 
>> number 4, may I am wrong on this one ? Maybe on others ?
>
> To put this shortly - you cannot do two things at the same time in a 
> database.
> Most contentions can be (and are) resolved internally with simple 
> rules, but as soon as a more prominent contention appears (and there 
> are many ways this could happen) then one reader/writer thread will 
> have to wait a little bit until a previous is finished. SQLite's 
> "timeout" setting (as mentioned and linked by Simon already) should 
> fix 99% of these without making you add any extra code.
>
> There may be one or two extreme cases or race conditions that requires 
> special consideration, but mostly setting the timeout should suffice. 
> If you still get locked problems - please post again.
>
> Cheers,
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Moving from Sqlite 3.5.9 to 3.9.1

[Jim Morris]

First, I was able to drop in the 3.9.2 sqlite.c and sqlite.h files, 
build and run the app without compiler, linker or other apparent 
errors.  Great job by dev team!

Our Windows Mobile 6.x app has been using SQLite 3.5.9, threading 
mode=1.  I added a background thread, with its own connection to sync 
new records, in Sqlite DB to server and got error SQLITE_BUSY (5).   Set 
the busy timeout and still was seeing issues.  Moving to 3.9.2 the busy 
errors went away.  Always nice.

Is there any concerns I should have other than a full regression?

[sqlite] When was PRAGMA busy_timeout added to SQLite3

[Jim Morris]

I'm using an archaic version, I know, but busy_timeout doesn't seem to 
work.  It doesn't return the value I set.  When was it added?

sqlite3 temp.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> PRAGMA busy_timeout=3;
sqlite> PRAGMA busy_timeout;
sqlite>

[sqlite] When was PRAGMA busy_timeout added to SQLite3

[Jim Morris]

I'm using an archaic version, I know, but busy_timeout doesn't seem to 
work.  It doesn't return the value I set.  When was it added?

sqlite3 temp.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> PRAGMA busy_timeout=3;
sqlite> PRAGMA busy_timeout;
sqlite>

[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?

[Jim Morris]

If you are not using WAL mode that might help.

On 7/2/2015 7:59 AM, Kathleen Alexander wrote:
> Hi Adam,
>
> Thank you very much for your response. I had not considered using copies of
> the database.  In my method there are cases where writes to the database by
> one process may be relevant to another process, so I'm not sure that that
> would be a good option.
>
> I don't explicitly set sqlite3_busy_timeout(sqlite3*, int ms) so I will try
> adding that after the database connection is opened to see if it limits
> those errors.
>
> Thanks Again.
>
> On Thu, Jul 2, 2015 at 10:28 AM, Adam Devita  wrote:
>
>> Good day,
>>
>> I'm sure others on the list will add better insight, but is your task
>> parallel enough that your nodes can work with a copy of the database
>> and submit changes the one the others copy from when 'done' their
>> calculation?
>>
>> Are you using https://www.sqlite.org/c3ref/busy_timeout.html ?
>>
>> regards,
>> Adam
>>
>> This may be beside the point in terms of optimization, but  your query
>> looks rather character based on int like information.
>>
>> On Thu, Jul 2, 2015 at 10:09 AM, Kathleen Alexander 
>> wrote:
>>> Hi,
>>>
>>> I apologize if this is an incorrect forum for this question, but I am
>>> pretty new to SQLite and have been unable to resolve this issue through
>>> other searches. Feel free to direct me to a more appropriate forum.
>>>
>>> Essentially, I have written an application in C++ that interfaces (reads
>>> and writes) with a SQLite database, and I am getting lots of 'database is
>>> locked' errors. Right now, I am trying to establish whether those errors
>>> are due to my improper use of SQLite itself, or if the real problem is
>> that
>>> SQLite is not a good fit for my application.
>>>
>>> My application runs on Linux (ubuntu 13.10), and is driven by a bash
>> script
>>> that spawns many (~60 on a 64 core workstation) instances of a serial,
>> C++
>>> program, each of which opens its own connection to the database and
>>> performs reads and writes.
>>>
>>> *An example SELECT query from my program looks like:*
>>> //open db connection
>>> sqlite3 *db;
>>> char *zErrMsg = 0;
>>> SQLITE3 sql(dbase.c_str());
>>>
>>> statement = "SELECT * from configs_table WHERE id='31'";
>>> sql.exe(statement.c_str());
>>> if( sql.vcol_head.size() > 0 ){
>>> //do things with sql.vdata[]
>>> }//end query returned results
>>>
>>> *An example of a write statement looks like:*
>>> statement = "UPDATE configs_table SET searched='2' WHERE id='31'";
>>> sql.exe(statement.c_str());
>>>
>>> About 97% of the time, the select statement works fine, but in the other
>> 3%
>>> of cases, I see a 'database is locked' error in the log file of my
>> program.
>>> About 50% of the time, the write statement returns 'database is locked'.
>>>
>>> Additionally, if this application is running and I try to query the
>>> database from the terminal, I almost always get a 'database is locked'
>>> error.
>>>
>>> Thus, I am wondering if I am doing something wrong in my implementation
>> of
>>> the C++ --> SQLite interaction, or if the real problem is that this
>>> application is not well suited to use with SQLite (I went through the
>>> checklist before implementing it and thought my application passed the
>>> suitability requirements).
>>>
>>> Lastly:
>>> A. if it seems like this is an implementation issue, rather than a
>>> capability issue, if I were to scale up my method to spawn say 500-1000
>>> processes at a time (on a supercomputing cluster), would there be any
>>> concern about SQLite scaling to that level?
>>> B. If SQLite is not a good fit for my program, do you have any
>> suggestions
>>> of an alternative database engine that is free or might be free or
>>> inexpensive for academic use?
>>>
>>> Thanks in advance,
>>> Kathleen
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> --
>> --
>> VerifEye Technologies Inc.
>> 151 Whitehall Dr. Unit 2
>> Markham, ON
>> L3R 9T1
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Transpose selected rows into columns

[Jim Morris]

You might try

select SerialNumber, V0, V5, V5-V0
from
(select SerialNumber,
max(case Stim when 'V0' then Resp else null end) V0,
max(case Stim when 'V5' then Resp else null end) V5
from MyTable group by SerialNumber)



On 4/7/2015 6:58 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
> Igor,
>
> Your solution works well. What I can't figure out is how to efficiently 
> create a column representing V5-V0.
>
> SerialNumber |  V0   |  V5  | Vdiff
> -|---|--|---
> 123  |  0.2  |  0.6 |  0.4
>
>
> This is what I'm using, but it takes twice as long:
>
> select SerialNumber,
>
> max(case Stim when 'V0' then Resp else null end) V0,
> max(case Stim when 'V5' then Resp else null end) V5,
>
> (max(case Stim when 'V0' then Resp else null end) -
> max(case Stim when 'V5' then Resp else null end)) Vdiff
>
> from MyTable group by SerialNumber;
>
>
> There must be a more efficient way. (I tried V5-V0 and assigning intermediate 
> values to variables but got nothing but errors.)
>
> Thanks,
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com
>
>
>
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
>> users-bounces at mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
>> Sent: Friday, March 27, 2015 3:20 PM
>> To: sqlite-users at mailinglists.sqlite.org
>> Subject: Re: [sqlite] Transpose selected rows into columns
>>
>> On 3/27/2015 11:48 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
>>> I want the rows containing V0 and V5 to become columns like this:
>>>
>>> SerialNumber |  V0   |  V5
>>> -|---|---
>>> 123  | 0.136 | 0.599
>>> 124  | 0.126 | 0.587
>>> 125  | 0.119 | 0.602
>> select SerialNumber,
>> max(case Stim when 'V0' then Resp else null end) V0,
>> max(case Stim when 'V5' then Resp else null end) V5 from MyTable
>> group by SerialNumber;
>>
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
> attachments are solely for the use of the addressee and may contain 
> information that is privileged or confidential. Any disclosure, use or 
> distribution of the information contained herein is prohibited. In the event 
> this e-mail contains technical data within the definition of the 
> International Traffic in Arms Regulations or Export Administration 
> Regulations, it is subject to the export control laws of the U.S.Government. 
> The recipient should check this e-mail and any attachments for the presence 
> of viruses as L-3 does not accept any liability associated with the 
> transmission of this e-mail. If you have received this communication in 
> error, please notify the sender by reply e-mail and immediately delete this 
> message and any attachments.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID option

[Jim Morris]

To improve efficiency you could add "where 1=2" to avoid returning any 
rows.  Should just check validity.


On 5/7/2014 8:19 AM, Stephan Beal wrote:

On Wed, May 7, 2014 at 4:57 PM, Simon Slavin  wrote:


somehow ?  Perhaps the ROWID field of a table might have its own
particular indication, and if you don't see any rows marked like that you
could deduce that the table had no ROWID column.  I'm sure there are better
ways the


This isn't efficient, but it should work without corner cases: (pseudocode):


function hasRowId(tablename) {
   prepare SELECT 1 FROM tablename; // if this fails, tablename likely does
not exist. else...
   prepare SELECT rowid FROM tablename; // if this fails, rowid missing
   return true only if the second PREPARE succeeds.
}




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

Re: [sqlite] inner vs. outer join inconsistency

[Jim Morris]

I believe a check constraint with an appropriate typeof comparison has 
been suggested for this usage.


On 3/6/2013 6:29 AM, Ryan Johnson wrote:
I would agree that no warning is needed for for columns that don't 
state any affinity, or for a non-affinity FK that refers to some PK 
with affinity.


I tend to agree with OP that an explicitly text foreign key referring 
to an explicitly int primary key is probably worth a warning (perhaps 
from the hypothetical "lint mode" that surfaces now and then), since 
it's pretty likely that a user who took the trouble to specify 
affinities for both PK and FK probably made a mistake if the types are 
different. Sure, some record might override affinity and store 'abc' 
as its "int" primary key, but even if your app relies on that 
behavior, an "int" foreign key would be harmless for the same reason.


Off topic, I'd love a way to request strong typing for a column (so 
that attempts to store 'abc' into an int column would fail). You can 
emulate it with a pair of before/update triggers (select raise(...) 
where typeof(intcol)!='integer'), but that's clunky. Oh well... in 
retrospect, most of the times I've been bitten by type mismatches were 
probably either due to this bug or (more likely) due to my not 
specifying any affinity at all and then being surprised when 1 != '1'.


Ryan

On 06/03/2013 7:23 AM, Stephen Chrzanowski wrote:

SQLite doesn't care what kind of data type you are using, so, no, it
shouldn't throw an error.  The logic of this database engine is that you
will always be comparing apples to apples, regardless if one happens 
to be

orange.

On Wed, Mar 6, 2013 at 4:50 AM, Tom Matrix  wrote:


Richard Hipp  writes:


On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix  wrote:


I’ve encountered a problem, which is hardly reproducable on arbitrary
databases, therefore I attached one.

A simple, reproducible test case for (what we think is) your 
problem can

be

seen in this ticket:

  www.sqlite.org/src/tktview/fc7bd6358f59b

This bug has been latent in SQLite for almost four years and you 
are the
first to hit it.  Probably this is because not many applications 
contain
A=B in the WHERE clause where A is a text expression and B is an 
integer

expression.  You can probably work around the problem by changing your
schema so that entries.measurementid is an integer rather than text.

  This
does not excuse SQLite:  It is still getting the wrong answer and 
needs

to

be fixed.  We are working on a fix now.  But a simple change to your

schema

will work around the problem and get you going even before that fix is
available.
Very good example, thank you! It really solved my problem. 
Nevertheless, I

think
I don't have to mention that entries.measurementid must have been an
integer,
and this bug must be fixed in our schema, too.

My only remaining concern is, however: Should not SQLite give an 
error (or

at
least a warning) in cases where a foreign key constraint refers to a
different
data type?

Thanks again,
Tamás

___
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


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

Re: [sqlite] JDBC Drivers for SQLite?

[Jim Morris]

http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

Check out Google for more.

On 12/12/2012 8:54 AM, Tilsley, Jerry M. wrote:

All,

Might be a silly question, but does anyone know if any JDBC drivers exist for 
SQLite?

Thanks,

Jerry



Disclaimer
This email is confidential and intended solely for the use of the individual to 
whom it is addressed. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of St. Claire Regional Medical 
Center. If you are not the intended recipient, be advised that you have 
received this email in error and that any use, dissemination, forwarding, 
printing or copying of the email is strictly prohibited. If you received this 
email in error please notify the St. Claire Regional Helpdesk by telephone at 
606-783-6565.
___
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] Ordering of fields in a join

[Jim Morris]

Adding the warning to the explain plan output should work well.

...


And yet the coding mistake in the SQL query was very subtle.  It makes me
wonder if we shouldn't somehow come up with a "warning" mechanism in SQLite
to give developers a heads-up on error-prone constructs, such as using ==
between two columns with different default collating sequences...




Pavel
___
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] classic update join question

[Jim Morris]

This analysis is a good candidate for inclusion in a FAQ or similar 
document.


On 9/5/2012 7:28 PM, Keith Medcalf wrote:

sqlite> create table alpha (frequency, term);
sqlite> create table beta (term, frequency);
sqlite> create index betaterm on beta(term);
sqlite> .explain
sqlite> explain query plan update alpha set frequency = (select frequency from 
beta where beta.term >= alpha.term);
sele  order  from  deta
  -    
0 0  0 SCAN TABLE alpha (~100 rows)
0 0  0 EXECUTE CORRELATED SCALAR SUBQUERY 0
0 0  0 SEARCH TABLE beta USING INDEX betaterm (term>?) 
(~25 rows)
sqlite> explain update alpha set frequency = (select frequency from beta where 
beta.term >= alpha.term);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 38000
2 Null   0 1 200
3 OpenRead   0 2 0 2  00
4 Rewind 0 8 000
5 Rowid  0 2 000
6 RowSetAdd  1 2 000
7 Next   0 5 001
8 Close  0 0 000
9 OpenWrite  0 2 0 2  00
10RowSetRead 1 36200
11NotExists  0 10200
12Null   0 3 400
13Null   0 5 000
14Integer1 6 000
15OpenRead   1 3 0 2  00
16OpenRead   2 4 0 keyinfo(1,BINARY)  00
17Column 0 1 700
18IsNull 7 28000
19SeekGe 2 287 1  00
20Column 2 0 800
21IsNull 8 27000
22IdxRowid   2 8 000
23Seek   1 8 000
24Column 1 1 900
25Move   9 5 100
26IfZero 6 28-1   00
27Next   2 20000
28Close  1 0 000
29Close  2 0 000
30SCopy  5 3 000
31Column 0 1 400
32NotExists  0 33200
33MakeRecord 3 2 8 bb 00
34Insert 0 8 2 alpha  05
35Goto   0 10000
36Close  0 0 000
37Halt   0 0 000
38Transaction0 1 000
39VerifyCookie   0 3 000
40TableLock  0 2 1 alpha  00
41TableLock  0 3 0 beta   00
42Goto   0 2 000
sqlite>

sqlite> explain query plan select alpha.term, beta.frequency from alpha, beta 
where beta.term >= alpha.term;
SELECT item[0] = {0:1}
item[1] = {1:1}
FROM {0,*} = alpha
  {1,*} = beta
WHERE GE({1:0},{0:1})
END
sele  order  from  deta
  -    
0 0  0 SCAN TABLE alpha (~100 rows)
0 1  1 SEARCH TABLE beta USING INDEX betaterm (term>?) 
(~25 rows)
sqlite> explain select alpha.term, beta.frequency from alpha, beta where beta.term 
>= alpha.term;
SELECT item[0] = {0:1}
item[1] = {1:1}
FROM {0,*} = alpha
  {1,*} = beta
WHERE GE({1:0},{0:1})
END
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 22000
2 OpenRead   0 2 0 2  00
3 OpenRead   1 3 0 2  00
4 OpenRead   2 4 0 keyinfo(1,BINARY)  00
5 Rewind 0 18000
6 Column 0 1 100
7 IsNull 1 17000
8 SeekGe 2 171 1  00
9 Column 2 0 200
10IsNull 2 16000
11IdxRowid   2 2 000
12Seek   1 2 000
13Column 0 1 300
14Column 1 1

Re: [sqlite] SQLite4 (don't scream)

[Jim Morris]

Are there significant improvements in speed for existing SQL?

How does the compiled size compare with SQLite3?


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

Re: [sqlite] Trouble importing hex encoded blob

[Jim Morris]

Joshua,

It doesn't exist in the standard version.  We added it in-house to aid 
development and testing.  The code I posted was the changes we made to 
the 3.5.9 shell.c in addition to adding an existing hex to byte function 
from our libraries.


If you can compile a new shell the existing shell, haven't checked the 
shell code recently, is probably still similar enough to use a similar 
style, if desired.


The provided code is just a hint as to what might work for you.  We 
never had time to attempt to push the code back to the standard shell.  
The sqlite environment suggested was for Linux and since I'm using 
Windows didn't have time to set up an environment.


Jim

On 5/14/2012 10:06 AM, Joshua Shanks wrote:

Hey Jim,

I downloaded the source or 3.7.12 from sqlite.org and can't find that code.

$ ls
shell.c  sqlite3.c  sqlite3ext.h  sqlite3.h
$ head -n3 sqlite3.c
/**
** This file is an amalgamation of many separate C source files from SQLite
** version 3.7.12.  By combining all the individual C code files into this
$ grep blobBuffer *
$


On Mon, May 14, 2012 at 8:24 AM, Jim Morris<jmor...@bearriver.com>  wrote:

We added blob import on an old version of the shell, 3.5.9. Using a simple
HexToByte function.

To function: static int do_meta_command(char *zLine, struct callback_data
*p){

Added:unsigned char * blobBuffer = NULL;

In the loop // * Bind cached values to prepared statement.  *
we added
else if( nColType[i] == SQLITE_BLOB )
{
textLen = strlen(azCol[i]);
// Convert from Hex to Binary.
blobLen = HexToByte(,azCol[i], textLen );

// Have sqlite make an internal copy since we may have
multiple blobs...
rc = sqlite3_bind_blob(pStmt, i+1, blobBuffer, blobLen,
SQLITE_TRANSIENT);
}

with a free(blobBuffer); done for each row after the reset.



On 5/11/2012 10:22 PM, Joshua Shanks wrote:

I peeked at the source code real quick and it looks like it just
converts the contents of the file into a bunch of SQL that is
essentially opening a transaction and doing an insert for each row
followed by a commit. This suggest I just need to format it
differently so I'll play around with that tomorrow and report back on
if I make an progress. It looks to be using prepared statements
instead of straight inserts which is what i tried to convert out of.

On Fri, May 11, 2012 at 7:22 PM, Richard Hipp<d...@sqlite.org>wrote:

On Fri, May 11, 2012 at 10:13 PM, Joshua Shanks<jjsha...@gmail.com>
  wrote:


I set the separator to tab and then in the file it is

X'somevalue'\tX'someothervalue'\n
X'morestuff'\tX'evenmore'\n

but with real hex values

According to the documentation


That document you quote is describing the SQL langauge, not CSV.  There
is
no way to enter BLOBs using CSV, that I know of.

The ".import" command operatos on CSV, not SQL.



BLOB literals are string literals containing hexadecimal data and
preceded by a single "x" or "X" character. For example:

X'53514C697465'

On Fri, May 11, 2012 at 6:16 PM, Simon Slavin<slav...@bigfraud.org>
wrote:

On 12 May 2012, at 2:01am, Joshua Shanks<jjsha...@gmail.com>wrote:


But when I try to use the .import method the values get imported as
the string "X'" instead of the hex blob value and don't get pulled
out correctly.

.import is for .csv files.  What are you putting in the .csv file to

express a value in hex ?  I don't think there's a way to do it.

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



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

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


___
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] Trouble importing hex encoded blob

[Jim Morris]

We added blob import on an old version of the shell, 3.5.9. Using a 
simple HexToByte function.


To function: static int do_meta_command(char *zLine, struct 
callback_data *p){


Added:unsigned char * blobBuffer = NULL;

In the loop // * Bind cached values to prepared statement.  *
we added
else if( nColType[i] == SQLITE_BLOB )
{
textLen = strlen(azCol[i]);
// Convert from Hex to Binary.
blobLen = HexToByte( ,azCol[i], 
textLen );


// Have sqlite make an internal copy since we may 
have multiple blobs...
rc = sqlite3_bind_blob(pStmt, i+1, blobBuffer, 
blobLen, SQLITE_TRANSIENT);

}

with a free(blobBuffer); done for each row after the reset.


On 5/11/2012 10:22 PM, Joshua Shanks wrote:

I peeked at the source code real quick and it looks like it just
converts the contents of the file into a bunch of SQL that is
essentially opening a transaction and doing an insert for each row
followed by a commit. This suggest I just need to format it
differently so I'll play around with that tomorrow and report back on
if I make an progress. It looks to be using prepared statements
instead of straight inserts which is what i tried to convert out of.

On Fri, May 11, 2012 at 7:22 PM, Richard Hipp  wrote:

On Fri, May 11, 2012 at 10:13 PM, Joshua Shanks  wrote:


I set the separator to tab and then in the file it is

X'somevalue'\tX'someothervalue'\n
X'morestuff'\tX'evenmore'\n

but with real hex values

According to the documentation


That document you quote is describing the SQL langauge, not CSV.  There is
no way to enter BLOBs using CSV, that I know of.

The ".import" command operatos on CSV, not SQL.



BLOB literals are string literals containing hexadecimal data and
preceded by a single "x" or "X" character. For example:

X'53514C697465'

On Fri, May 11, 2012 at 6:16 PM, Simon Slavin
wrote:

On 12 May 2012, at 2:01am, Joshua Shanks  wrote:


But when I try to use the .import method the values get imported as
the string "X'" instead of the hex blob value and don't get pulled
out correctly.

.import is for .csv files.  What are you putting in the .csv file to

express a value in hex ?  I don't think there's a way to do it.

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




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

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


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

Re: [sqlite] Data Import Techniques

[Jim Morris]

If you are not wrapping the inserts in an explicit transaction, try that.

On 5/2/2012 9:04 AM, Nigel Verity wrote:

Hi
I am writing an application which requires approximately 50,000 items to be 
imported from a text file into a table. Each item is a single string of 8 
characters, and the target table has an auto-incrementing PK and one other 
field, to hold the 8 character string.
Using the "Import Table Data" function in SQLiteman, the data loads very quickly. However 
in my application, using either an SQL "insert" command or a resultset, the import is 
very much slower. Is there another technique I can use to speed things up?
Thanks
Nige
___
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] Re Query planner creating a slow plan

[Jim Morris]

It is possible using an alias would force better behavior:

selsect theDate
 from (select transfer_date as theDate from transfer_history
where regn_no='039540' and transfer_date<= '2012-05-01') order by
theDate

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

Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

[Jim Morris]

Sorry, no.  SQLite is embedded in our application and requires 
authentication and sync to server before you can get off the login page.



On 2/9/2012 9:33 AM, Tim Leland wrote:

Is the sqlite part separate or emended in the application? Is it possible to
just send me the .exe and test it on the scan gun?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jim Morris
Sent: Thursday, February 09, 2012 12:22 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

It will definitely run.  Pelles is new to me.  Our application uses
SQLite 3.5.9 on WinCE 4.2 through 6.5 using C++.  I don't recall that we
had to change any source, but we did modify the shell for better input
handling, but there were probably some compiler flags we needed to set.
Been years since I had to touch it.

___
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] Compiling SQLite3 to run on Windows Mobile 6.5

[Jim Morris]

Don't know about those devices but we use the MC55A and related device 
without issue with C++ and I'm pretty sure they support C#.


http://www.motorola.com/Business/US-EN/Business+Product+and+Services/Mobile+Computers/Handheld+Computers/MC55A0

On 2/9/2012 9:24 AM, Richard Hipp wrote:

On Thu, Feb 9, 2012 at 12:21 PM, Noah Hart  wrote:


The C#-SQLite port at http://code.google.com/p/csharp-sqlite/ supports
both
Silverlight and Windows Phone


Tim tells me that he needs it to run on a handheld barcode scanner (
http://www.motorola.com/Business/US-EN/Business+Product+and+Services/Bar+Code+Scanning).
Do they run C#?  I dunno - I'm asking?





Hope that help,

Noah Hart


Tim Leland wrote:

Does anyone have any tips/suggestions for getting sqlite3 to run on
windows
mobile 6.5?



Thanks



Tim Leland

W. Lee Flowers&  Co.

127 E. W Lee Flowers Rd.

Scranton, S.C. 29591

(843)389-2731



___
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/Compiling-SQLite3-to-run-on-Windows-Mobile-6.5-tp33294689p33294909.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

[Jim Morris]

It will definitely run.  Pelles is new to me.  Our application uses 
SQLite 3.5.9 on WinCE 4.2 through 6.5 using C++.  I don't recall that we 
had to change any source, but we did modify the shell for better input 
handling, but there were probably some compiler flags we needed to set.  
Been years since I had to touch it.


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

Re: [sqlite] General question on sqlite3_prepare, the bind and resets of prepared statements

[Jim Morris]

Yes,  works great!

On 11/11/2011 4:24 PM, Matt Young wrote:

Embedded Sqlite3 questions:
I want to load and prepare multiple statements, keep them prepared and when
I want to use one of them, I will reset,  bind and step.

Can pre-prepare multiple independent statements, then run them one at a
time at random?

Thanks, this may be a newbie question for embeded sqlite3
___
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] ensuring uniqueness of tuples spanning across multipletables?

[Jim Morris]

On 10/6/2011 10:43 PM, Ivan Shmakov wrote:

Jim Morris writes:

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

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

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

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

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

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

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

CREATE UNIQUE INDEX "foo-unique"
 ON "foo" (a, b, c);
I don't know for sure.  You would need to do some testing to determine 
performance issues.
I wouldn't use distinct if the values are already guaranteed to be 
unique or in a not exits clause since it may have additional overhead.

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

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

[Jim Morris]

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


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

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

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

Re: [sqlite] How to get the grand total of count(*) in a select statement

[Jim Morris]

That should be select 'Total', count(*) from hosts
On 10/3/2011 11:52 AM, Jim Morris wrote:

This should do it.
select region, count(*) from hosts group by region
union all
select 'Total, count(*) from hosts
;



On 10/3/2011 11:49 AM, James Kang wrote:

select region, count(*) from hosts group by region

___
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] How to get the grand total of count(*) in a select statement

[Jim Morris]

This should do it.
select region, count(*) from hosts group by region
union all
select 'Total, count(*) from hosts
;



On 10/3/2011 11:49 AM, James Kang wrote:

select region, count(*) from hosts group by region

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

Re: [sqlite] LEFT JOIN optimization

[Jim Morris]

Your where clause "WHERE ItemsME.IDR ..." is only satisfied if there is 
an associated ItemsME record so the left outer join is pointless.  Just 
use the inner join.  Normally the left outer join would include all of 
ItemsME_Properties, that probably explains the table scan.


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

Re: [sqlite] PHP, SQLite3 object API, SQLite3::escapeString

[Jim Morris]

I'd guess it was for escaping strings used to build SQL statements by 
concatenation rather than using prepared statements and binding.

On 8/18/2011 7:12 AM, Simon Slavin wrote:
> ...
> The SQLite3 object API for PHP includes a function SQLite3::escapeString .  
> The documentation for it doesn't explain what it's of or when to use it.
...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate by break in sequence

[Jim Morris]

You can't replace multiple rows in a single insert/update/delete statement.
You might consider copying the duplicates to a temp table, delete them 
from the old then use a select on the temp table to generate the new 
rows for the old table.  The select portion would be something like
select longitude, distance, SUM(IVSUM)/SUM(IVCount), SUM(IVSUM), 
SUM(IVCount) FROM  GROUP By longitude, Distance.

On 8/16/2011 9:05 AM, Anantha Prasad wrote:
> Wanted to know if Sqlite can do simple math when there is a break in
> sequence in the data. For example the foll. table is sorted by Longitude and
> then Distance:
>
> Id  Longitude Distance IVmean  IVsum IVcount
> 42 71.0 10  10.5000   221
> 43 71.0 10   29.4286  28  824
> 44 71.0 20   9.467762  587
> 45 71.0 20   11.6667  1441680
> 46 71.0 30   3.553247  167
> 47 71.0 40   4.573249   67
>
> I want to compute replace the IVmean for each repeated Distance by
> IVsum/IVcount - for repeated Distance - for example,
>
> Record 42 and 44 should be replaced by
> 71.0  10  (2+28)/(21+824)
> Record 44 and 45 should be replaced by
> 71.0   20 (62+144)/(587+1680)
>
> Is this possible to do this in Sqlite.
>
> Thanks much.
> Pras
> ___
> 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] "override" table?

[Jim Morris]

A union variation that is more amenable to generic selects:

select a,b,c,1 as tablename from real where id=123 and not exists (Select 1 
from over where over.id = real.id)
union
select a,b,c,2 as tablename from over where id=123



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

Re: [sqlite] System.Data.SQLite: commands not persisting on db.

[Jim Morris]

On 7/15/2011 1:26 AM, Mattia wrote:
> - deleting the old database (data.db) and renaming new.db with the
> correct name (new.db becomes data.db).
After this step, as a test, reopen the connection to new.db and ensure 
that the data is there.

Are you sure a commit is done before closing connection?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR UPDATE?

[Jim Morris]

Or do an update and if no records are modified then do an insert.

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

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Jim Morris]

The between operator is order dependent.  This variation might work:

SELECT d from T_d
inner join (select min(pos) as xMin, max(pos) as yMax FROM T_x WHERE txt 
= '1990' OR txt='1991') as xcriteria on xPos between xMin and xMax
inner join (select min(pos) as yMin, max(pos) as yMax FROM T_y WHERE txt 
= 'cogs' OR txt='sg expenses') as ycriteria on yPos between yMin and yMax

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

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

[Jim Morris]

string literals are enclose in single quotes not double quotes"

select (select v from t1 where n='a') wrong,*  from a1;



On 6/28/2011 11:42 AM, thilo wrote:
> select (select v from t1 where n="a") wrong,*  from a1;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query with UNION on large table

[Jim Morris]

Did you try to time a simpler select:

SELECT min(n1, n2) as new1, max(n1,n2) as new2 FROM table1 group by new1, new2 
having count(*)>  1


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

Re: [sqlite] SQLITE run slow

[Jim Morris]

You might also consider a trigger to calculate the distance once on 
insert/update.  They you could use an index.

On 6/14/2011 4:53 AM, Simon Slavin wrote:
> On 14 Jun 2011, at 12:11pm, Martin.Engelschalk wrote:
>
>> make sure you have an index on  category and distance, like
>>
>> create index MyIndex on Location  (category, distance)
> Peter is calculating distance inside his SELECT, but I agree that an index on 
> (category) is an excellent idea.
>
> The other thing is that this is a bit like RTREEs.  So Peter, you might like 
> to read
>
> 
>
> I don't know if it's worth using rtrees in your particular example but you 
> might find them useful.
>
> 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] HELP: SQLException getErrorCode vs gerErrorMessage()

[Jim Morris]

I've only used the xerial driver recently and haven't checked for the 
error code on exception.  It does come with source, so you may be able 
to walk into the code to see how the value is set/retrieved.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create DB file and then Create Table - Table FAILS.

[Jim Morris]

This line seems erroneous: SQLiteCommand sqlCmd(%conn); Isn't the 
percent the modulus operator?

Shouldn't it be: SQLiteCommand sqlCmd();

On 6/1/2011 5:06 PM, Don Ireland wrote:
> I'm hoping someone can help me with this.
>
> Using Visual Studio C++, the following code DOES create the DB file.
> But the table doesn't get created and I'm stumped as to why it won't
> create the table.
>
>   SQLiteConnection conn;
>   conn.ConnectionString = "Data
> Source=D:\Users\Don\Downloads\CashBoxPrefs.dat";
>   conn.Open();
>   SQLiteCommand sqlCmd(%conn);
>   sqlCmd.CommandText = "CREATE TABLE IF NOT EXISTS Prefs
> (SyncEnabled bool,SyncWatchPort int,SyncLoginKey TEXT,SyncAccessKey
> TEXT, SyncLogFile TEXT, SyncLogClearInterval int,GenLatestBook TEXT,
> GenBookMRU_0 TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 TEXT, GenBookMRU_3
> TEXT, GenBookMRU_4 TEXT);";
>
>
> But if I run following at the SQLite3.exe from the command line, it DOES
> create the file and the table.
> sqlite3 Cashboxprefs.dat
> sqlite>  CREATE TABLE IF NOT EXISTS Prefs (SyncEnabled bool,SyncWatchPort
> int,SyncLoginKey TEXT,SyncAccessKey TEXT, SyncLogFile TEXT,
> SyncLogClearInterval int,GenLatestBook TEXT, GenBookMRU_0
> TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 TEXT, GenBookMRU_3 TEXT,
> GenBookMRU_4 TEXT);
> ___
> 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] Better way to get records by IDs

[Jim Morris]

If you just need them in descending order, i.e. not an arbitrary order, 
then "order by rec desc" will work.

On 5/20/2011 7:23 AM, jose isaias cabrera wrote:
> "Martin Engelschalk" on Friday, May 20, 2011 10:21 AM wrote...
>
>
>> Hi,
>>
>> to order, you have to use "order by". In that case, however, it gets
>> complicated.
>>
>> SELECT * FROM Jobs WHERE rec IN (87, 33, 27,2, 1)
>> order by case rec when 87 then 1
>>when 33 then 2
>>when 37 then 3
>>when 2 then 4
>>when 1 then 5
>>end;
>>
>> Martin
> Thanks, this will work.
>
>> Am 20.05.2011 15:55, schrieb jose isaias cabrera:
>>> "Oliver Peters" on Friday, May 20, 2011 9:47 AM wrote...
>>>
>>>
 jose isaias cabrera   writes:

> Greetings.
>
> I would like to get a bunch of records of IDs that I already know. For
> example, this table called Jobs,
> rec,...,data,...
> 1,...,aaa,...
> 2,...,zzz,...
> ...
> ...
> 99,...,azz,...
>
 [...]


 What about

 SELECT *
 FROM table
 WHERE id BETWEEN 1 AND 99;

 greetings
 Oliver
>>> I presented a bad example, Oliver.  My apologies.  I want specific IDs,
>>> so
>>> the WHERE rec IN (1,2,27,33,87) works perfectly.  However, I have one
>>> last
>>> question: if I do this call,
>>>
>>> SELECT * FROM Jobs WHERE rec IN (87, 33, 27,2, 1)
>>>
>>> the result is 1, 2, 27, 33, 87.  How can I get that specific order?
>>>
>>> thanks,
>>>
>>> josé
>>>
>>> ___
>>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query efficiency

[Jim Morris]

You must use the alias if specified:

select ar.* from aa ar, ab ab1, ab ab2;
rather than
select aa.* from aa ar, ab ab1, ab ab2;




On 5/19/2011 10:33 AM, Matthew Jones wrote:
> select aa.* from aa ar, ab ab1, ab ab2;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with sqlite3_prepare_v2?

[Jim Morris]

Yes, transaction are designed to work with multiple statements.

Begin
Statement 1
Statement 2
...
Statement N
Commit

On 5/17/2011 7:58 AM, StyveA wrote:
> Hello again,
>
> I've got an other question about prepare :
>
> Is it possible to make a BEGIN transaction, then prepare many statements
> (insert, update, delete) and then COMMIT all at once in the right order?
> Or should I each time make a BEGIN, prepare, COMMIT?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best JDBC driver for SQLite?

[Jim Morris]

I've just started using Xerial also for a small project and have not had 
any problems so far.  I haven't used any other JDBC wrappers so have no 
comparison info.

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

Re: [sqlite] SQLite3.DLL 3.7.6 memory leaks

[Jim Morris]

Are you sure these leaks aren't yours? Although I don't know the Sqlite 
internals some of the data values don't seem to be related to Sqlite, like:

c:/DEV/Platform/
PolicyDataPack.i

You can try to use the allocation number to narrow down the code 
location.  Don't remember the specifics though.

On 4/18/2011 6:03 AM, Khanh Nguyen wrote:
> Hi,
>
>
>
> My name is Khanh Nguyen and currently using SQLite3.DLL 3.7.6 in my
> application (DLL built with VS2008 C++ with this flag: Multi-threaded
> Debug DLL (/MDd).
>
>
>
> The DLL has some memory leaks that I have captured here:
>
>
>
> The thread 'Win32 Thread' (0x368) has exited with code 0 (0x0).
>
> Detected memory leaks!
>
> Dumping objects ->
>
> {8390} normal block at 0x01364C70, 64 bytes long.
>
>   Data:  63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
> 2F
>
> {8382} normal block at 0x0138C9C0, 32 bytes long.
>
>   Data:<7Zl7ji/F9x+bOgbG>  37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62
> 47
>
> {8380} normal block at 0x0138B938, 32 bytes long.
>
>   Data:  50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E
> 69
>
> {8377} normal block at 0x01391F48, 448 bytes long.
>
>   Data:< >  10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
> CD
>
> {7174} normal block at 0x01391DC8, 64 bytes long.
>
>   Data:  63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
> 2F
>
> {7166} normal block at 0x013621D8, 32 bytes long.
>
>   Data:<7Zl7ji/F9x+bOgbG>  37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62
> 47
>
> {7164} normal block at 0x01386690, 32 bytes long.
>
>   Data:  50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E
> 69
>
> {7161} normal block at 0x01390430, 448 bytes long.
>
>   Data:< >  10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
> CD
>
> {7108} normal block at 0x0138CDC8, 64 bytes long.
>
>   Data:  63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
> 2F
>
> {7100} normal block at 0x01386320, 32 bytes long.
>
>   Data:  45 4E 74 35 4B 6B 59 48 52 71 65 55 2F 69 4C
> 52
>
> {7098} normal block at 0x0138BCD8, 32 bytes long.
>
>   Data:  50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E
> 62
>
> {7095} normal block at 0x0138D530, 448 bytes long.
>
>   Data:< >  10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
> CD
>
> {7033} normal block at 0x013796D8, 64 bytes long.
>
>   Data:  63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
> 2F
>
> {7025} normal block at 0x0136DED0, 32 bytes long.
>
>   Data:<5U/jyx2txHeUQUe/>  35 55 2F 6A 79 78 32 74 78 48 65 55 51 55 65
> 2F
>
> {7023} normal block at 0x013658D0, 32 bytes long.
>
>   Data:  4E 65 74 77 6F 72 6B 53 65 74 74 69 6E 67 33
> 2E
>
> {7020} normal block at 0x01389BF0, 448 bytes long.
>
>   Data:< >  10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
> CD
>
> Object dump complete.
>
> The program '[5432] DebugConsole.exe: Native' has exited with code 0
> (0x0).
>
>
>
> Please help me overcome this memory leak issue.
>
>
>
> Thanks and Kind Regards,
>
>
>
> Khanh
>
>
>
> ___
> 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] How to Use an Apostrophe in a Text Field?

[Jim Morris]

Did you try doubling the apostrophes?

*Goin'' Down the Road Feelin'' Bad*


On 4/17/2011 6:16 PM, Simon Slavin wrote:
> On 17 Apr 2011, at 11:54pm, Alan Holbrook wrote:
>
>> I'm using SQLite with VBE2008.  I've defined a table with a number of text
>> fields in it.  If the information I want to write to the database contains
>> an embedded apostrophe, the program throws an error.  That is, if I set
>> textfield1 to *Going Down the Road Feeling Bad*, the data gets written
>> correctly and the program continues.  But if I set textfield1 to *Goin' Down
>> the Road Feelin' Bad*, I get an error.
>>
>> Is there a way I can use an apostrophe in the data to be written?
> Your library might do it for you.  If you're writing directly to the SQLite 
> library then I believe you can double the apostrophe:
>
> Goin'' Down the Road Feelin'' Bad
>
> so it might be worth trying that.
>
> 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] GROUP BY Problem

[Jim Morris]

Did you want to use correlated sub queries?
Something like:

SELECT c1,c2,
(select sum(t2.c3) FROM t2 WHERE t2.key2=t1.key1) as mySum,
(select count(t3.c4) FROM t3 where t3.key3=t1.key1) as myCount
FROM t1
;



On 4/7/2011 5:31 PM, Pete wrote:
> I am trying to use GROUP BY to summarise information from a main table and
> two sub tables, e.g.:
>
>
> SELECT c1,c2,sum(t2.c3),count(t3.c4) FROM t1 LEFT JOIN t2 on t2.key2=t1.key1
> LEFT JOIN t3.key3=t1.key1 GROUP BY t1.key1
>
>
> The result is that the count column returns the count of (the number of t2
> entries * the number of t3 entries), and the sum column returns (the t2 sum
> value * the count of entries in t3).
>
>
> For example if the sum of t2.c3 is actually 1000 (from 3 rows) and the count
> of t3.c4 is 5, the sum column returns 5000 and the count column returns 15.
>   If either of t2 or t3 has no qualifying entries, the calculation for the
> other table is correct.
>
>
> I guess GROUP BY isn't designed to deal with this type of situation.  Can
> anyone suggest a way to do this?
>
>
> Thanks,
>
> Pete
> ___
> 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] how to do this query?

[Jim Morris]

A simple restatement should work:

delete
from xxx
where entry_id in (select
t1.entry_id
from
xxx t1
where not
t1.entry_id in(select
t2.entry_id
from
xxx t2
where
t1.patient_id = t2.patient_id
order by
t2.start_date desc limit 1))

On 3/24/2011 12:00 PM, Bart Smissaert wrote:
> delete
> from
> xxx t1
> where not
> t1.entry_id in(select
> t2.entry_id
> from
> xxx t2
> where
> t1.patient_id = t2.patient_id
> order by
> t2.start_date desc limit 1)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .import FILE TABLE

[Jim Morris]

.separator ","
.import myPath/myCSVfile myTable

We use a "import" file with these commands.


On 3/10/2011 2:32 PM, jcilibe...@comcast.net wrote:
> Thanks, but doesn't seem to work:
>
>
> I used command>
> - Original Message -
> From: "Gerry Snyder"
> To: "General Discussion of SQLite Database"
> Sent: Thursday, March 10, 2011 12:39:33 PM
> Subject: Re: [sqlite] .import FILE TABLE
>
> On 3/10/2011 1:28 PM, jcilibe...@comcast.net wrote:
>> Hello, thanks but adding separator "," doesn't seem to work.
>
> I tried the following commands:
>> .import separator "," myPath/myCSVfile myTable
>> . import separator , myPath/myCSVfile myTable
>> . import myPath/myCSVfile myTable separator ,
>> . import myPath/myCSVfile myTable separator ","
>
> None worked: any ideas? Examination of the myCSVfile.txt shows [1, "Jack", 
> "Sammamish"]
>>
>> Unbelievably active user group!
>>
>>
>> I have been unable to import a CSV text file from MS Access to sqlite:
>> 1. Created a small table (3 fields and 1 record) in Access and exported it 
>> to a CSV text file named "myCSVfile.txt"
>>
>>
>> 2. Transferred from PC to Mac. Opened file "myCSVfile.txt" ...looks OK eg: 
>> [1, "Jack", "Sammamish"]
>>
>>
>> 3. Created a new DB ("myDB") and table ("myTable") in SQLite Database 
>> Browser eg: [ID:primaryKey Name:text City:text]
>>
>>
>> 4. Opened the DB in terminal with>  sqlite myPath/myDB
> Does adding the line:
>
> .separator ","
>
> help?
>
>> 5. Entered command>  .import myPath/myCSVfile myTable
>>
>>
>> Always get back message: "line 1: expected 3 columns of data but found 1"
>>
>>
>> Help! I've read many archived posts...so I know this should work.
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .import FILE TABLE

[Jim Morris]

Make sure your separator is the ","
http://www.sqlite.org/sqlite.html

On 3/10/2011 12:28 PM, jcilibe...@comcast.net wrote:
> Hello,
>
>
> Unbelievably active user group!
>
>
> I have been unable to import a CSV text file from MS Access to sqlite:
> 1. Created a small table (3 fields and 1 record) in Access and exported it to 
> a CSV text file named "myCSVfile.txt"
>
>
> 2. Transferred from PC to Mac. Opened file "myCSVfile.txt" ...looks OK eg: 
> [1, "Jack", "Sammamish"]
>
>
> 3. Created a new DB ("myDB") and table ("myTable") in SQLite Database Browser 
> eg: [ID:primaryKey Name:text City:text]
>
>
> 4. Opened the DB in terminal with>  sqlite myPath/myDB
>
>
> 5. Entered command>  .import myPath/myCSVfile myTable
>
>
> Always get back message: "line 1: expected 3 columns of data but found 1"
>
>
> Help! I've read many archived posts...so I know this should work.
> ___
> 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] Help with join

[Jim Morris]

A correlated sub-query might work for you.

SELECT
[Analyzers].[AnalyzerID]
, [Analyzers].[Name] AS [Analyzer]
, [Analysis].[AnalysisID]
, [Analysis].[ScanID]
, [Analysis].[Timestamp]
, [Analysis].[EndTime]
, (SELECT COUNT(*) AS NumDefects FROM Defects d where d.AnalysisID = 
Analysis.AnalysisID) as NumDefects,
, [Analysis].[Result]
FROM   [Analysis]
JOIN [Analyzers] ON [Analyzers].[AnalyzerID] = [Analysis].[AnalyzerID]
ORDER BY [Analysis].[Timestamp];



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

Re: [sqlite] Performance Problem

[Jim Morris]

On the MC55 and MC70 we use with Sqlite 3.5.9:
PRAGMA temp_store = MEMORY
PRAGMA journal_mode = PERSIST
PRAGMA journal_size_limit = 50

On 2/16/2011 5:24 AM, Black, Michael (IS) wrote:
> Try this benchmark program and see what numbers you get.  You need to compare 
> to other machines with the same benchmark to see if it's the machine or your 
> programming/architecture.
> The MC55 is a 520Mhz PXA270 so I would expect to see more than a 6X 
> difference from my 3Ghz box (memory speed is no doubt slower too).
>
> batch 1 10 0
> Sqlite Version: 3.7.5
> Inserting 1 rows using a bulk of 10
> commits per second: 14217.7
> batch 1 10 4
> using wal mode
> Sqlite Version: 3.7.5
> Inserting 1 rows using a bulk of 10
> commits per second: 44952.5
> batch 1 10 6
> using index on t(i)
> using wal mode
> Sqlite Version: 3.7.5
> Inserting 1 rows using a bulk of 10
> commits per second: 42383.5
>   batch 1 10 7
> using mode: :memory:
> using index on t(i)
> using wal mode
> Sqlite Version: 3.7.5
> Inserting 1 rows using a bulk of 10
> commits per second: 219279.0
>
> #include
> #include
> #include
> #ifdef _WIN32
> #include
> #include
> #include
> #else
> #include
> #endif
> #include
> #include "sqlite3.h"
> double elapsed()
> {
> #ifdef _WIN32X
>   struct _timeb timebuffer;
>   _ftime( );
>   return( (double)timebuffer.time + timebuffer.millitm / 1000.0);
> #else
>   double t1;
>   struct timeval tv;
>   static long base_seconds;
>   gettimeofday(,NULL);
>   if (base_seconds==0) base_seconds=tv.tv_sec;
>   return (tv.tv_sec-base_seconds)+tv.tv_usec/(double)100;
> #endif
> }
> int AddTrigger(sqlite3 *db)
> {
>  char SqlTxt[256];
>  int rc;
>  strcpy(SqlTxt,"CREATE TRIGGER XX AFTER INSERT ON t FOR EACH ROW BEGIN ");
>  strcat(SqlTxt,"UPDATE t2 SET n = NEW.i+1;");
>  strcat(SqlTxt,"END;");
>  rc = sqlite3_exec(db,SqlTxt,NULL,NULL,NULL);
>  if (rc != SQLITE_OK) {
>   puts(sqlite3_errmsg(db));
>  }
>  return(0);
> }
> int main(int argc, char *argv[])
> {
>sqlite3 *db;
>sqlite3_stmt *stmt=NULL;
>int rc;
>int n=0;
>int nrec=0;
>int interval=0;
>int flags=0;
>double t1;
>char   SqlTxt[256];
>if (argc != 4) {
> fprintf(stderr,"Usage: %s nrecords commit_interval flags\n",argv[0]);
> fprintf(stderr,"Flag 0 = simple table\n");
> fprintf(stderr,"Flag 1 = in-memory database\n");
> fprintf(stderr,"Flag 2 = add index\n");
> fprintf(stderr,"Flag 4 = WAL mode\n");
> fprintf(stderr,"Flag 8 = Add an update trigger per insert\n");
> fprintf(stderr,"Flag 16= Add a manual update per insert\n");
> fprintf(stderr,"Flag 32 = Synchronous=Off\n");
> fprintf(stderr,"Add flags to combine features\n");
> exit(-1);
>}
>nrec=atoi(argv[1]);
>interval=atoi(argv[2]);
>flags=atoi(argv[3]);
>if (flags&  1)
>{
> puts("using mode: :memory:");
> rc=sqlite3_open(":memory:",);
>}
>else {
> remove("batch.db");
> rc=sqlite3_open("batch.db",);
>}
>if (rc != SQLITE_OK) {
> puts(sqlite3_errmsg(db));
>}
>rc=sqlite3_exec(db,"create table t (i integer)",NULL,NULL,NULL);
>if (rc != SQLITE_OK) {
>   puts(sqlite3_errmsg(db));
>}
>rc=sqlite3_exec(db,"create table t2 (n integer)",NULL,NULL,NULL);
>if (rc != SQLITE_OK) {
>   puts(sqlite3_errmsg(db));
>}
>rc=sqlite3_exec(db,"insert into t2 values(0)",NULL,NULL,NULL);
>if (rc != SQLITE_OK) {
>  puts(sqlite3_errmsg(db));
>}
>if (flags&  32) {
> rc=sqlite3_exec(db,"pragma synchronous=OFF",NULL,NULL,NULL);
> if (rc != SQLITE_OK) {
> puts(sqlite3_errmsg(db));
> }
> puts("using pragma synchronous=OFF");
>}
>if (flags&  2) {
> rc=sqlite3_exec(db,"create index tx on t (i)",NULL,NULL,NULL);
> if (rc != SQLITE_OK) {
>puts(sqlite3_errmsg(db));
> }
>  puts("using index on t(i)");
>}
>if (flags&  4) {
> rc=sqlite3_exec(db,"pragma journal_mode=WAL",NULL,NULL,NULL);
> if (rc != SQLITE_OK) {
>   puts(sqlite3_errmsg(db));
> }
> puts("using wal mode");
>}
>if (flags&  8) {
> AddTrigger(db);
> puts("using update trigger");
> /**  if (interval != 1) {
>  fprintf(stderr,"Can't do trigger and begin/commit together\n");
>  exit(-1);
> }
> **/
>}
>if( flags&  16 )
>   puts("using manual update after insert");
>printf("Sqlite Version: %s\n", sqlite3_version);
>printf("Inserting %d rows using a bulk of %d\n", nrec, interval);
>sqlite3_prepare_v2(db,"insert into t values(?)",-1,,NULL);
>t1=elapsed();
>if (interval != 1) rc=sqlite3_exec(db,"begin",NULL,NULL,NULL);
>while(n<  nrec)
>{
> ++n;
> if (interval != 1&&  (n% interval)==0) {
>  sqlite3_exec(db,"commit",NULL,NULL,NULL);
>  sqlite3_exec(db,"begin",NULL,NULL,NULL);
> }
> sqlite3_bind_int(stmt,1,n);
> 

Re: [sqlite] completion of sql words

[Jim Morris]

I tihnk the firefox plug-in Sqlite Manager does.

On 2/11/2011 2:30 PM, Simon Slavin wrote:
> On 11 Feb 2011, at 7:19pm, prad wrote:
>
>> does the sqlite3 interface have completion of sql key words?
>> in postgresql you can type SEL and press tab to complete.
>> is there such a thing for sqlite3?
> sqlite3 doesn't have an interface.  It is only a programming API.
>
> Although you can download a command-line tool for sqlite3 from the sqlite3 
> web site, it's provided just for convenience and many people don't use it (or 
> even know about it).
>
> 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] Bi-directional unique

[Jim Morris]

If you don't care about the order then use and instead of trigger to 
force the ordering then you will get the collisions you expect.

On 2/9/2011 10:12 AM, Black, Michael (IS) wrote:
> I have a need to create a unique bi-directional relationship.
>
> You can think of it as pairings of people who eat dinner together.
>
>
>
> create table t(i int, j int);
>
> insert into t(1,2);
>
> insert into t(2,1);<<  should give an error because the pairing of 1-2 
> already exists.
>
> insert into t(3,2);<<  OK
>
> insert into t(3,1);<<  OK
>
> insert into t(1,3);<<  should be error
>
>
>
> You can't guarantee that one column is less than the other so there's no win 
> there.
>
>
>
> Speed is of the utmost concern here so fast is really important (how many 
> ways can I say that???).
>
>
>
> Is there anything clever here that can be done with indexes or such?
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
> ___
> 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] Disk I/O Error

[Jim Morris]

Could a backup or virus scanning software be locking the database?

On 2/8/2011 10:12 AM, Nathan Biggs wrote:
> I haven't tried that, but if we stop the application then restart it,
> everything works again.  That is until we get another Disk I/O error
> which happens the next day.
> I'm wondering if the virus scanner is blocking the database write.
>
>
>
> On 2/8/2011 1:03 PM, Simon Slavin wrote:
>> On 8 Feb 2011, at 5:59pm, Nathan Biggs wrote:
>>
>>> Once a day, not at the same time, we are getting a disk I/O error from
>>> our application using SQLite.
>> If you try to duplicate the database file do you get any kind of error
>> then ?
>>
>> If not, use the command-line tool to run an integrity check:
>>
>> http://www.sqlite.org/pragma.html#pragma_integrity_check
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I query for a specific count of items?

[Jim Morris]

Only apples
SELECT distinct customerid
FROM Customers c1
WHERE Type = 'Apple' AND not exists (select 1 from customers c2 where 
c2.customerid=c1.customerid and  not Type = 'Apple')
;


Apples and Bananas
SELECT distinct  customerid
FROM Customers c1
WHERE Type = 'Apple' AND exists (select 1 from customers c2 where 
c2.customerid=c1.customerid and  Type = 'Banana')
;



On 2/3/2011 9:40 AM, Igor Tandetnik wrote:
> On 2/3/2011 12:26 PM, Puneet Kishor wrote:
>> On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote:
>>> INSERT INTO Customers VALUES (NULL, 1239, 'Banana');
>> Your EntryID is INTEGER PRIMARY KEY, yet you are inserting NULLs.
> That's how you tell SQLite to generate IDs automatically.
>
>> Your CustomerID seems like it should be unique, yet you have identical rows 
>> inserted.
> It's not declared unique, why do you think it should be?
>
>> For example, what is the difference between the first and the second row?
> EntryID.
>
>>> #1) Query for customers who *ONLY* bought apples
>> SELECT *
>> FROM Customers
>> WHERE Type = 'Apple';
> That would also report customers that bought something else besides apples.
>
>>> #2) Query for customers who bought apples *AND* bananas
>> SELECT *
>> FROM Customers
>> WHERE Type = 'Apple' OR Type = 'Banana';
> That would report customers that only bought apples, as well as those
> that only bought bananas.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[Jim Morris]

I'd probably move the analyze out of the loop.

Since your joining on props.id a better index pind might be
create index pind on props (id, pnam)

The name of column id in table props would be clearer as obj_id since it 
is not the id of the property but the id of the record in the obj table.

On 2/2/2011 3:23 PM, Jeff Rogers wrote:
> Andreas Kupries wrote:
>
>> It seems to me that you are looking for
>>  http://en.wikipedia.org/wiki/Database_normalization
>>
> SQLite seems to do quite poorly performance-wise with fully-normalized
> attribute tables like this, when you want to query against multiple
> attributes.  My timing comparisons with postgres show sqlite to be as
> much as 10x-15x slower than pg.
>
> My timing code is at http://paste.tclers.tk/2346
>
> This is a synthetic test, but I ran across the issue in a real
> application.  I'm not sure what else I can do do optimize the queries;
> using a denormalized table is the only thing that seems to help.
>
> -J
> ___
> 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] SQL query on sort order

[Jim Morris]

If not already done creating a page of additional collations on the wiki 
would make sense and minimize work all around.

> I just mailed you an extension for SQLite offering the collation you need.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to optimize this simple select query ?

[Jim Morris]

Did you try a compound index?

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

Re: [sqlite] How Execute Joint Queries In Sqlite?

[Jim Morris]

A union of a left and right joins should do it.

On 12/7/2010 4:50 AM, Simon Slavin wrote:
> On 7 Dec 2010, at 12:37pm, Pavel Ivanov wrote:
>
>>> I Writed A FULL OUTER JOIN Query In Sqlite But I Got A Message Like Not
>>> Supported. What Should I Do To Do This
>> You should re-think once more: do you really need a full outer join?
>> Maybe you can change your schema so that it was more clear and didn't
>> require full outer join for querying.
> Just a note that it is probably very easy to do this.  You may even be able 
> to phrase your SELECT with an INNER JOIN without changing the schema.  Pavel 
> is being helpful, not insulting you.
>
> 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] Select fails even though data is in the table.

[Jim Morris]

I still think you should use the same columns for searching for the 
duplicate that cause the collision.   Using col4 seem problematic.   Can 
you change the code to use col2 and col3?

On 12/1/2010 8:24 AM, Hemant Shah wrote:
> This is a single thread/process. No other thread or process is accessing the 
> data.
>
> This is a single process that reads data from message queue and dumps into 
> database to look for duplicate rows.
>
> The problem occurs for some rows only (about 3 to 5 an hour).
>
>
> Hemant Shah
> E-mail: hj...@yahoo.com
>
>
> --- On Wed, 12/1/10, Jim Morris<jmor...@bearriver.com>  wrote:
>
>> From: Jim Morris<jmor...@bearriver.com>
>> Subject: Re: [sqlite] Select fails even though data is in the table.
>> To: sqlite-users@sqlite.org
>> Date: Wednesday, December 1, 2010, 10:15 AM
>> If you have another thread running
>> that deletes or modifies the table,
>> then move the commit to after the select for duplicate to
>> ensure
>> transactional integrity.
>>
>> On 12/1/2010 8:10 AM, Black, Michael (IS) wrote:
>>> The problem is probably in the bind calls that you are
>> not showing.
>>> If you care to share them we may be able to help.
>>>
>>> Michael D. Black
>>> Senior Scientist
>>> Advanced Analytics Directorate
>>> Northrop Grumman Information Systems
>>>
>>>
>>> 
>>>
>>> From: sqlite-users-boun...@sqlite.org
>> on behalf of Hemant Shah
>>> Sent: Wed 12/1/2010 10:08 AM
>>> To: General Discussion of SQLite Database
>>> Subject: EXTERNAL:Re: [sqlite] Select fails even
>> though data is in the table.
>>>
>>>
>>> The unique key is col3 and col4 (SeqNum and MD5Sum).
>>>
>>> If the insert fails for this unique key then col4
>> should be the same.
>>> It should find the row for the even if I select for
>> col4 only.
>>> Hemant Shah
>>> E-mail: hj...@yahoo.com
>>>
>>>
>>>
>>>
>>>
>>>
>>> ___
>>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select fails even though data is in the table.

[Jim Morris]

If you have another thread running that deletes or modifies the table, 
then move the commit to after the select for duplicate to ensure 
transactional integrity.

On 12/1/2010 8:10 AM, Black, Michael (IS) wrote:
> The problem is probably in the bind calls that you are not showing.
> If you care to share them we may be able to help.
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Hemant Shah
> Sent: Wed 12/1/2010 10:08 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] Select fails even though data is in the table.
>
>
>
> The unique key is col3 and col4 (SeqNum and MD5Sum).
>
> If the insert fails for this unique key then col4 should be the same.
> It should find the row for the even if I select for col4 only.
>
> Hemant Shah
> E-mail: hj...@yahoo.com
>
>
>
>
>
>
> ___
> 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] Select fails even though data is in the table.

[Jim Morris]

Why are you looking for a duplicate with col4 instead of the unique key, 
col2, col3 that caused the collision?

On 12/1/2010 7:29 AM, Hemant Shah wrote:
> Folks,
>
> My C program creates a in-memory database. It creates a table and a unique 
> index on two columns. If the insert fails due to unique index, it prints old 
> row and new row. Sometimes it cannot find the old row even though the insert 
> failed.
>
> Here is the pseudo code:
>
> CreateStmt = “create table mytable (TimeStamp char[50], col2 [char 10], col3 
> int, col4 char[33]”;
> sqlite3_exec(CreateStmt)
>
> IndexStmt = “create unique index myidx (col3 asc, col4 asc)”;
> sqlite3_exec(IndexStmt);
>
> InsertStmt = “insert into mytable (TimeStamp, col2, col3, col4) values 
> (?,?,?,?)”;
> sqlite3_prepare_v2(InsertStmt)
> sqlite3_bind calls for each column
> ReturnCode = sqlite3_step(InsertStmtHandle);
> if (ReturnCode != SQLITE_DONE)
> {
> sqlite3_reset(InsertStmtHandle);
> if (sqlite3_errcode(DbHandle) == SQLITE_CONSTRAINT)
> {
>/* duplicate row */
>sqlite3_finalize(InsertStmtHandle);
>sqlite3_exec(DbHandle, "COMMIT", NULL, NULL, NULL);
>
>
>SelectStmt = “select TimeStamp, col2, col3, col4 from mytable where 
> col4 = ?”;
>sqlite3_prepare_v2(SelectStmt)
>sqlite3_bind calls
>ReturnCode = sqlite3_step(SelectStmtHandle);
>if (ReturnCode != SQLITE_ROW)
>{
>   sqlite3_reset(SelectStmtHandle);
>   printf("Row not found. ReturnCode: %d, Error Message:%s, Error 
> Code: %d\n", ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
>}
>else
>{
>   /* print row */
>}
> }
> else
> {
>/* other error
> }
> }
>
>
> The program prints ReturnCode as 101 which is SQLITE_DONE and error messages 
> is “No error message”, errcode is 0.
>
> If the insert statement failed because of duplicate row, then why did it not 
> find the old row?
>
> This only happens for some of the rows.
>
> I am inserting rows at very high rate (about every 50 microseconds) and only 
> keep one minute worth of data in the table.
>
> How do I debug/fix this problem?
>
> Thanks.
>
>
>
> Hemant Shah
> E-mail: hj...@yahoo.com
>
>
>
> ___
> 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] joining two sequences?

[Jim Morris]

Slightly better version:

select distinct f1 .key as foo_key, b1.key as bar_key
from bar b1
inner join foo f1 on f1 .value = b1.value
where
not exists
(
-- Values for a particular key in foo
select f3.value from foo f3 WHERE f3.key= f1.key
union
-- Values for a particular key in bar
select b3.value from bar b3 WHERE b3.key = b1.key
except
-- Values common to both foo key and bar key
select f2.value from foo f2 inner join bar b2 on b2.value = f2.value 
WHERE b2.key = b1.key AND f2.key= f1.key
);

On 11/19/2010 6:40 PM, Jim Morris wrote:
> This should return a the equivalent keys in the two maps.  The basic
> idea is to compare the values in each key in foo(left outer join foo)
> with the values for each key in bar where there are any matching
> values(left outer join bar) and only select those with a complete match(
> inner join).  Not sure this is the most efficient way.
>
> select distinct f1 .key as foo_key, b1.key as bar_key
> from bar b1
> inner join foo f1 on f1 .value = b1.value
> where
> not exists
> (
> -- Values for a particular key in foo
> select f3.value from foo f3 left outer join bar b3 on b3.value= f3.value
> WHERE f3.key= f1.key
> union
> -- Values for a particular key in bar
> select f3.value from bar b3 left outer join foo f3 on b3.value= f3.value
> WHERE b3.key = b1.key
> except
> -- Values common to both foo key and bar key
> select f2.value from foo f2 inner join bar b2 on b2.value = f2.value
> WHERE b2.key = b1.key AND f2.key= f1.key
> );
>
>
>
> On 11/19/2010 1:03 PM, Petite Abeille wrote:
>> Hello,
>>
>> Given two tables describing sequences of key value pairs, what would be a 
>> reasonable way to join them?
>>
>> For example, assuming two table foo and bar with identical structure:
>>
>> create temporary table foo
>> (
>>   key integer not null,
>>   value   text not null,
>>   constraint  foo_pk primary key( key, value )
>> );
>>
>> create temporary table bar
>> (
>>   key integer not null,
>>   value   text not null,
>>   constraint  bar_pk primary key( key, value )
>> );
>>
>> And a set of sequences in each of the table:
>>
>> insert into foo values( 1, 'a' );
>>
>> insert into foo values( 2, 'a' );
>> insert into foo values( 2, 'b' );
>>
>> insert into foo values( 3, 'a' );
>> insert into foo values( 3, 'b' );
>> insert into foo values( 3, 'c' );
>>
>> insert into bar values( 4, 'a' );
>> insert into bar values( 4, 'b' );
>>
>> What would be a good way to join foo( 2, 'a', )( 2, 'b' ) to bar( 4, 'a', )( 
>> 4, 'b' )? In other words, join the sequences with the same values?
>>
>> Right now, I'm using group_concat to flatten the sequences:
>>
>> select  *
>> from(
>>   select  key,
>>   group_concat( value ) as value
>>   fromfoo
>>
>>   group bykey
>>   )
>> as  foo
>>
>> join(
>>   select  key,
>>   group_concat( value ) as value
>>   frombar
>>
>>   group bykey
>>   )
>> as  bar
>> on  bar.value = foo.value
>>
>> Which results in:
>>
>> key|value|key|value
>> 2|a,b|4|a,b
>>
>> All good, if perhaps clunky.
>>
>> But the documentation for group_concat mention that the order of the 
>> concatenated elements is arbitrary [1]. Which perhaps would preclude 
>> group_concat from being reliably use as a join predicate, no?
>>
>> Could someone think of a nice alternative to group_concat to join such data 
>> structure?
>>
>> Thanks in advance.
>>
>> Cheers,
>>
>> PA.
>>
>> [1] http://www.sqlite.org/lang_aggfunc.html
>>
>>
>>
>>
>>
>>
>>
>>
>> ___
>> 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] joining two sequences?

[Jim Morris]

This should return a the equivalent keys in the two maps.  The basic 
idea is to compare the values in each key in foo(left outer join foo) 
with the values for each key in bar where there are any matching 
values(left outer join bar) and only select those with a complete match( 
inner join).  Not sure this is the most efficient way.

select distinct f1 .key as foo_key, b1.key as bar_key
from bar b1
inner join foo f1 on f1 .value = b1.value
where
not exists
(
-- Values for a particular key in foo
select f3.value from foo f3 left outer join bar b3 on b3.value= f3.value 
WHERE f3.key= f1.key
union
-- Values for a particular key in bar
select f3.value from bar b3 left outer join foo f3 on b3.value= f3.value 
WHERE b3.key = b1.key
except
-- Values common to both foo key and bar key
select f2.value from foo f2 inner join bar b2 on b2.value = f2.value 
WHERE b2.key = b1.key AND f2.key= f1.key
);



On 11/19/2010 1:03 PM, Petite Abeille wrote:
> Hello,
>
> Given two tables describing sequences of key value pairs, what would be a 
> reasonable way to join them?
>
> For example, assuming two table foo and bar with identical structure:
>
> create temporary table foo
> (
>  key integer not null,
>  value   text not null,
>  constraint  foo_pk primary key( key, value )
> );
>
> create temporary table bar
> (
>  key integer not null,
>  value   text not null,
>  constraint  bar_pk primary key( key, value )
> );
>
> And a set of sequences in each of the table:
>
> insert into foo values( 1, 'a' );
>
> insert into foo values( 2, 'a' );
> insert into foo values( 2, 'b' );
>
> insert into foo values( 3, 'a' );
> insert into foo values( 3, 'b' );
> insert into foo values( 3, 'c' );
>
> insert into bar values( 4, 'a' );
> insert into bar values( 4, 'b' );
>
> What would be a good way to join foo( 2, 'a', )( 2, 'b' ) to bar( 4, 'a', )( 
> 4, 'b' )? In other words, join the sequences with the same values?
>
> Right now, I'm using group_concat to flatten the sequences:
>
> select  *
> from(
>  select  key,
>  group_concat( value ) as value
>  fromfoo
>
>  group bykey
>  )
> as  foo
>
> join(
>  select  key,
>  group_concat( value ) as value
>  frombar
>
>  group bykey
>  )
> as  bar
> on  bar.value = foo.value
>
> Which results in:
>
> key|value|key|value
> 2|a,b|4|a,b
>
> All good, if perhaps clunky.
>
> But the documentation for group_concat mention that the order of the 
> concatenated elements is arbitrary [1]. Which perhaps would preclude 
> group_concat from being reliably use as a join predicate, no?
>
> Could someone think of a nice alternative to group_concat to join such data 
> structure?
>
> Thanks in advance.
>
> Cheers,
>
> PA.
>
> [1] http://www.sqlite.org/lang_aggfunc.html
>
>
>
>
>
>
>
>
> ___
> 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] GROUP BY driving me crazy

[Jim Morris]

If you would explain why/how the position value is significant that 
might help.

I fixed your pseudo SQL to run in SQLite Manager and I don't understand 
from the
sample data what your trying to do. There is only one image per item.
Do you have multiple images per item and only want to return the first?

A query like the following seem to yield reasonable results:
SELECT products.name, items.id,images.filename, images.position
FROM products
INNER JOIN items ON items.product_id = products.id
LEFT JOIN images ON images.item_id = items.id
WHERE items.name='White'
ORDER BY products.name ASC, images.position ASC
;

The cleaned up code is:

CREATE TABLE products (id, category_id, name, description);
CREATE TABLE items (id, product_id, part_number, name, price, buyable);
CREATE TABLE images (id, item_id, filename, position);

INSERT INTO products (id, category_id, name ) VALUES (1, 1, 'SQLite 
T-Shirt');
INSERT INTO products (id, category_id, name ) VALUES (2, 1, 'SQLite Long 
Sleeved Shirt');
INSERT INTO items (id, product_id, name) VALUES ('SQLT-WHT', 1, 'White');
INSERT INTO items (id, product_id, name) VALUES ('SQLT-BLK', 1, 'Black');
INSERT INTO items (id, product_id, name) VALUES ('SQLL-WHT', 2, 'White');
INSERT INTO items (id, product_id, name) VALUES ('SQLL-BLK', 2, 'Black');
INSERT INTO items (id, product_id, name) VALUES ('SQLL-BLU', 2, 'Blue');
INSERT INTO images (item_id, filename, position) VALUES 
('SQLT-WHT','sqlt-white.jpg', 2);
INSERT INTO images (item_id, filename, position) VALUES 
('SQLT-BLK','sqlt-black.jpg', 1);
INSERT INTO images (item_id, filename, position) VALUES 
('SQLL-WHT','sqll-white.jpg', 2);
INSERT INTO images (item_id, filename, position) VALUES 
('SQLL-BLK','sqll-black.jpg', 1);


On 11/10/2010 2:47 PM, James wrote:
> This will only display products which have items with images.  I think
> I'm going to sit back and see if there's a simpler way to achieve what
> I'm trying to do.  Maybe I'm going about this the wrong way, or I need
> to make some compromises.
>
> Thanks
>
> On Wed, Nov 10, 2010 at 3:01 PM, Igor Tandetnik  wrote:
>> select name, filename from products p, images im
>> where im.item_id = (
>>   select im2.item_id from items left join images im2 on (items.id = 
>> im2.item_id)
>>   where items.product_id = p.id and items.buyable
>>   order by position desc limit 1)
>> order by name;
>>
>> --
>> Igor Tandetnik
>>
>>
>> ___
>> 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] GROUP BY driving me crazy

[Jim Morris]

There is no logic way to show you intended result.  You need some sort 
of data that can be used as a filter.

If you want to filter by color why not add color to the item and use a 
WHERE clause?  Or maybe style?

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

Re: [sqlite] GROUP BY driving me crazy

[Jim Morris]

Can you better explain your intent?
Why are you grouping?  This is normally for creating sums, averages, 
counts etc.

Do you have a small sample of input vs output desired?

On 11/10/2010 11:11 AM, James wrote:
> I've been fighting with this for a couple days now.  I've been
> searching like mad, and thought I found solutions, but nothing seems
> to work.  I think I may have reached the limit of my understanding  :)
>
> This is just a simplified example of what I'm going after:
>
> SELECT products.id, products.name, images.src
> FROM products
> INNER JOIN items ON items.product_id = products.id
> LEFT JOIN images ON images.item_id = items.id
> WHERE items.buyable = 1
> GROUP BY products.id
> ORDER BY products.name, images.position DESC
>
> I've also tried things like:
>
> SELECT products.id, products.name, images.src
> FROM products
> INNER JOIN items ON items.product_id = products.id
> LEFT JOIN (SELECT * FROM images ORDER BY position DESC) images ON
> images.item_id = items.id
> WHERE items.buyable = 1
> GROUP BY products.id
> ORDER BY products.name
>
> and...
>
> SELECT products.id, products.name, (SELECT images.src FROM images
> WHERE images.item_id = items.id ORDER BY images.position DESC LIMIT
> 0,1)
> FROM products
> INNER JOIN items ON items.product_id = products.id
> WHERE items.buyable = 1
> GROUP BY products.id
> ORDER BY products.name
>
> Without the GROUP BY, the ordering is correct.  I found this example
> to order for GROUP_CONCAT, but I don't understand how I could
> translate it for my problem:
>
> SELECT ID, GROUP_CONCAT(Val)
> FROM (
> SELECT ID, Val
> FROM YourTable
> ORDER BY ID, Val
> );
>
> Is this a fairly simple problem and solution?  What would you search
> for to find solutions to this?  I'm having a heck of a time.
> ___
> 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] Comma-delimited field to rows (Once again)

[Jim Morris]

  Not much help but this removes the multiplication:
SELECT B1.B + B2.B + B3.B + B4.B FROM
(SELECT 0 AS B UNION SELECT 1 AS B) AS B1,
(SELECT 0 AS B UNION SELECT 2 AS B) AS B2,
(SELECT 0 AS B UNION SELECT 4 AS B) AS B3,
(SELECT 0 AS B UNION SELECT 8 AS B) AS B4

On 10/15/2010 3:00 PM, Max Vlasov wrote:
> Hi,
> from time to time I try to solve well-known task of making rows from a
> comma-delimited list and the best I could do was this:
> - create a user-function returning zero-based Nth item from the list (let's
> call it GetItemFromSet)
> - make a complex query like this (this one allows up to 16 elements in the
> list, can be expanded with similar selects)
>
> SELECT Trim(GetItemFromSet(Value, '23, 14, 1, 7, 9')) Item FROM
> (SELECT B1.B*1 + B2.B*2 + B3.B*4 + B4.B*8 AS VALUE FROM
> (SELECT 0 AS B UNION SELECT 1 AS B) AS B1,
> (SELECT 0 AS B UNION SELECT 1 AS B) AS B2,
> (SELECT 0 AS B UNION SELECT 1 AS B) AS B3,
> (SELECT 0 AS B UNION SELECT 1 AS B) AS B4,
> ) WHERE NOT (Item Is Null)
>
> Is there a way to implement something more elegant or at least to improve
> this approach. For example, the query for 16 bit limit will have 16 selects
> and bigger expression and also will iterate through all cross join output
> when we actually only have 5 items in the list.
>
> Max
> ___
> 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] What is the most efficient way to get the close by numbers?

[Jim Morris]

  If there is an index on (name, position) the a where like below might 
use it.

A1.name=A2.name and A2.position between( A1.position - 10, A1.position + 10 )


On 8/20/2010 3:54 PM, Peng Yu wrote:
> Hi,
>
> I have the following code to search for neighboring positions
> (distance<=10). But it is slow for large data set. I'm wondering what
> is the most efficient query for such a search. Note that I don't
> create an index, as I'm not sure what index to create on table A.
>
> $ cat main.sql
> #!/usr/bin/env bash
>
> rm -f main.db
> sqlite3 main.db<
> create table A (name text, position integer);
> insert into A values('a', 1);
> insert into A values('a', 5);
> insert into A values('a', 21);
> insert into A values('b', 3);
> insert into A values('b', 15);
> insert into A values('b', 19);
>
> .mode column
> .headers on
> .echo on
> select * from A as A1, A as A2 where A1.name=A2.name and
> abs(A1.position - A2.position)<= 10 and A1.position != A2.position;
>
> EOF
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to inner join on named intervals?

[Jim Morris]

Did you try something like(pseudo code):

select * from A inner join B on A.name = B.name AND ( B.left 
between(A.left,A.right) OR B.right between(A.left,A.right) )

On 8/13/2010 8:07 AM, Peng Yu wrote:
> Hi,
>
> Suppose that I have a table "A", each row represents a interval. For
> example, the first row represents an interval [1,10) with a name "a".
> The first and second rows are considered overlapping because the
> interval [1,10) and interval [5,15) intersect and both rows have the
> same name "a".
>
> name left right   tag
> -
> a  1 10   tag1
> a  5 15   tag2
> a21 30   tag3
> b  3 12   tag4
> b15 25   tag5
> b19 30   tag6
>
> I want to "inner join" the above table and the following table "B"
> based on the named interval overlapping.
>
> name left right   attr
> -
> a  3   7   attr1
> a  8 12   attr2
> a16 18   attr3
> a25 35   attr4
> b31 32   attr5
>
> The result is the following. In each row, the named interval from A
> overlaps the named interval from B. I don't see there is an easy way
> to do this in sqlite3. I could use an external program (such as python
> sqlite package) to enumerate all the named interval from table A and
> search for overlapping named intervals in table B, but this operation
> has a complexity of M log (N), where M is the length of table A and N
> is the length of table B. If some sort of "inner join" could be used,
> the complexity should be reduced to log(M+N). I'm wondering if there
> something that can help do this kind of named interval inner join
> easily.
>
> A.name A.left A.right A.tag B.name B.left B.right B.attr
> 
> a  1 10 tag1a  3   7attr1
> a  1 10 tag1a  8 12attr2
> a  5 15 tag2a  3   7attr1
> a  5 15 tag2a  8 12attr2
> a21 30 tag3a16 18attr3
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with complex UPDATE question

[Jim Morris]

You must add additional data to the rows so you can refer to them 
unambiguously.

table1 (KEY, COL1, ord)

0, 1,1
0, 2,2
1, 3,1
1, 4,2
2, 5,1
2, 6,2
3, 7,1
3, 8,2


On 7/23/2010 12:16 PM, peterwinson1 wrote:
> Jim you maybe correct that I don't have enough data to unambiguously identify
> the rows.  But just in case I was not very clear the first time.
>
> What I want to do is take the COL1 values of the first 2 rows [1, 2] and
> subtract them from the COL1 values, two rows at a time.  so [1, 2] - [1, 2],
> then [3, 4] - [1, 2], then [5, 6] - [1, 2], and finally [7, 8] - [1, 2].
> The question is can I do this just using SQL?
>
>
>
>
> Jim Morris-4 wrote:
>
>> What you are trying to do is unclear to me.  It seems that table1
>> doesn't have enough data to unambiguously identify the rows.
>>
>> On 7/23/2010 8:03 AM, peterwinson1 wrote:
>>  
>>> Thanks Eric and Alan for your help.  I tried to apply your code to my
>>> problem
>>> and it works to a limited extent because the problem is more complicated
>>> than the example I gave in the post.  I tries to simplify my exact
>>> problem
>>> but that didn't work out.  So here is the problem that I trying to solve.
>>>
>>> table1 (KEY, COL1)
>>>
>>> 0, 1
>>> 0, 2
>>> 1, 3
>>> 1, 4
>>> 2, 5
>>> 2, 6
>>> 3, 7
>>> 3, 8
>>>
>>> table2 (KEY, X, Y)
>>>
>>> 0, 0, 0
>>> 1, 0, 1
>>> 2, 1, 0
>>> 3, 1, 1
>>>
>>> What I would like to do is, like before, subtract COL1 from COL1 where
>>> table1.KEY = 0 and WHERE table1.KEY is IN (SELECT table2 WHERE X=0).  But
>>> I
>>> want to do a vector subtraction instead of a scalar subtraction.
>>>
>>> So far I have
>>>
>>> UPDATE table1 set COL1 = COL1 - (SELECT COL1 WHERE table1.KEY = 0) WHERE
>>> table1.KEY IN (SELECT table2 WHERE X=0)
>>>
>>> The result I would like to get is
>>>
>>> table1
>>>
>>> 0, 0 //(1 - 1)
>>> 0, 0 //(2 - 2)
>>> 1, 2 //(3 - 1)
>>> 1, 2 //(4 - 2)
>>> 2, 5
>>> 2, 6
>>> 3, 7
>>> 3, 8
>>>
>>> Instead I get
>>>
>>> 0, 0 //(1 - 1)
>>> 0, 1 //(2 - 1)
>>> 1, 2 //(3 - 1)
>>> 1, 3 //(4 - 1)
>>> 2, 5
>>> 2, 6
>>> 3, 7
>>> 3, 8
>>>
>>> Is this possible in SQL?
>>>
>>>
>>> peterwinson1 wrote:
>>>
>>>
>>>> Hello,
>>>>
>>>> I have a some what complex question about UPDATE.  I have the following
>>>> table
>>>>
>>>> table1 (KEY, COL1)
>>>>
>>>> 0, 1
>>>> 1, 2
>>>> 2, 3
>>>> 3, 4
>>>>
>>>> What I would like to do is to UPDATE COL1 by subtracting the COL1 value
>>>> where KEY = 0 from the COL1 value of the current row so that the result
>>>> would be.
>>>>
>>>> 0, 0
>>>> 1, 1
>>>> 2, 2
>>>> 3, 3
>>>>
>>>> Can this be done in SQL?  It does not have to be one UPDATE/SELECT
>>>> statement.
>>>>
>>>> Thank you
>>>> pw
>>>>
>>>>
>>>>
>>>>
>>>>  
>>>
>>>
>> ___
>> 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] Help with complex UPDATE question

[Jim Morris]

What you are trying to do is unclear to me.  It seems that table1 
doesn't have enough data to unambiguously identify the rows.

On 7/23/2010 8:03 AM, peterwinson1 wrote:
> Thanks Eric and Alan for your help.  I tried to apply your code to my problem
> and it works to a limited extent because the problem is more complicated
> than the example I gave in the post.  I tries to simplify my exact problem
> but that didn't work out.  So here is the problem that I trying to solve.
>
> table1 (KEY, COL1)
>
> 0, 1
> 0, 2
> 1, 3
> 1, 4
> 2, 5
> 2, 6
> 3, 7
> 3, 8
>
> table2 (KEY, X, Y)
>
> 0, 0, 0
> 1, 0, 1
> 2, 1, 0
> 3, 1, 1
>
> What I would like to do is, like before, subtract COL1 from COL1 where
> table1.KEY = 0 and WHERE table1.KEY is IN (SELECT table2 WHERE X=0).  But I
> want to do a vector subtraction instead of a scalar subtraction.
>
> So far I have
>
> UPDATE table1 set COL1 = COL1 - (SELECT COL1 WHERE table1.KEY = 0) WHERE
> table1.KEY IN (SELECT table2 WHERE X=0)
>
> The result I would like to get is
>
> table1
>
> 0, 0 //(1 - 1)
> 0, 0 //(2 - 2)
> 1, 2 //(3 - 1)
> 1, 2 //(4 - 2)
> 2, 5
> 2, 6
> 3, 7
> 3, 8
>
> Instead I get
>
> 0, 0 //(1 - 1)
> 0, 1 //(2 - 1)
> 1, 2 //(3 - 1)
> 1, 3 //(4 - 1)
> 2, 5
> 2, 6
> 3, 7
> 3, 8
>
> Is this possible in SQL?
>
>
> peterwinson1 wrote:
>
>> Hello,
>>
>> I have a some what complex question about UPDATE.  I have the following
>> table
>>
>> table1 (KEY, COL1)
>>
>> 0, 1
>> 1, 2
>> 2, 3
>> 3, 4
>>
>> What I would like to do is to UPDATE COL1 by subtracting the COL1 value
>> where KEY = 0 from the COL1 value of the current row so that the result
>> would be.
>>
>> 0, 0
>> 1, 1
>> 2, 2
>> 3, 3
>>
>> Can this be done in SQL?  It does not have to be one UPDATE/SELECT
>> statement.
>>
>> Thank you
>> pw
>>
>>
>>
>>  
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite database handle caching and write permissions

[Jim Morris]

You also need to watch for multiple command separated via ';'

On 7/15/2010 11:36 AM, JT Olds wrote:
> I considered that also, but I wasn't sure about whether or not that
> guaranteed no disk writes (maybe some sort of function call might be
> made there). That also restricts things like the usage of in-memory
> temp tables that might be useful. It appears that sqlite knows whether
> or not a statement will definitively, actually hit disk, whereas
> filtering by SELECT seemed unclear to me as to whether it would quite
> cover or catch everything.
>
> If that is truly the best way, then that's fine I guess.
>
> -JT
>
> On Thu, Jul 15, 2010 at 12:25 PM, Simon Slavin  wrote:
>
>> On 15 Jul 2010, at 7:07pm, JT Olds wrote:
>>
>>  
>>> is there a way to check a prepared statement
>>> before allowing its use as to if it will attempt to write to disk?
>>>
>> You could perhaps accept only statements that start with 'SELECT'.  It 
>> depends on how you're passing them to SQLite.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>  
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling as part of MFC C++ project

[Jim Morris]

You need to use the project properties to set the file as a C file and 
to not use precompiled headers

On 7/13/2010 5:47 PM, GHCS Software wrote:
> What do I need to do to get sqlite3.c to compile in a MFC C++ project
> (Visual C++)? If I just add it to the project, I end up getting a
> compile error something like: "Unexpected end of file while searching
> for pre-compiled header directive".
>
> Do I need to rename it "sqlite3.cpp" and put the include of
> at the top? Or what??
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] loading data from file with the file name as the extra field

[Jim Morris]

Try creating a script file something like(Psuedo code):
.separator ","
CREATE TEMP TABLE dummy (value);
.import file1.csv dummy
INSERT INTO TEST (filename,number) (SELECT 'file1.csv', value FROM dummy;
delete from dummy;
.import file2.csv dummy
INSERT INTO TEST (filename,number) (SELECT 'file2.csv', value FROM dummy;
DROP TABLE dummy;

On 7/2/2010 1:42 PM, Peng Yu wrote:
> Hi,
>
> Suppose that I have a number of files, each file has some numbers in
> it (by line). I want to load the content of each file and the
> associated filename into the following table.
>
> create table test (id integer primary key, filename text,  number integer);
>
> For example, if file 'a' has number 1,2,3. Basically, I want to do
>
> insert into test (filename, number) values('a',1);
> insert into test (filename, number) values('a',2);
> insert into test (filename, number) values('a',3);
>
> I could convert each file with an additional field that has the
> filename, and then .import it. But I wondering if there is a syntax in
> sql that can allow me to add a field directly. I don't find such a way
> and I just want to double check.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

[Jim Morris]

Are you thinking of limit?

On 7/2/2010 9:58 AM, Simon Slavin wrote:
> On 2 Jul 2010, at 5:15pm, Peng Yu wrote:
>
>
>> I want to select an entry that appears
>> <=n times and only show n times if it appears more than n times. I
>> think that "group by" might help.
>>  
> There's no simple format which will do what you want.  Do it in software.
>
> 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] EXTERNAL:Re: How to select an entry that appears <=n times and only show n times if it appears more than n times?

[Jim Morris]

Maybe this?

SELECT, min(Count(type_id),n)
FROM foods
GROUP BY


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

Re: [sqlite] Slow query

[Jim Morris]

You also haven't specified a primary key, i.e. on "id"

On 6/28/2010 11:24 PM, J. Rios wrote:
> I have the next table
>
> table1( id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER
> );
>
> I have created the next indexes : index1( name ), index2( id2 ), index3(
> name2 );
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] marking transaction boundaries

[Jim Morris]

I don't understand the driver for this but have you considered creating 
a function that would be called as part of the insert or trigger that 
would have greater access to application/sqlite internal info that might 
be used to create a transaction id.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Minor WAL document typo

[Jim Morris]

http://www.sqlite.org/draft/wal.html
'a' should be 'as' in the text "located in the same directory or folder 
a the original database file"
Should be "located in the same directory or folder as the original 
database file"

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

Re: [sqlite] WHERE = does not work

[Jim Morris]

Is is possible the character encoding is different?

On 4/30/2010 6:59 AM, Adam DeVita wrote:
> Is it possible there is a null, tab, newline or other invisible character?
> Try
>
> select timeStamp, '' || resourceType || 'xx'  From MyTable where
> resourceType like 'PSM' LIMIT 10;
>
> On Fri, Apr 30, 2010 at 9:53 AM, ecforu  wrote:
>
>
>> I don't think it is a case issue.  See below from sqlite3 command line.
>> Also one thing to note - I build the database from c API.  I don't know if
>> that makes a difference.
>>
>> sqlite>
>> sqlite>  select timeStamp, resourceType From MyTable where resourceType like
>> 'PSM' LIMIT 10;
>> timeStamp|resourceType
>> 2010-04-28 17:46:45.316|PSM
>> 2010-04-28 17:46:49.854|PSM
>> 2010-04-28 17:46:52.830|PSM
>> 2010-04-28 17:47:04.939|PSM
>> 2010-04-28 17:47:06.776|PSM
>> 2010-04-28 17:47:08.846|PSM
>> 2010-04-28 17:47:12.001|PSM
>> 2010-04-28 17:47:13.845|PSM
>> 2010-04-28 17:47:16.837|PSM
>> 2010-04-28 17:47:18.846|PSM
>> sqlite>
>> sqlite>
>> sqlite>  select timeStamp, resourceType From MyTable where resourceType =
>> 'PSM' LIMIT 10;
>> sqlite>
>> sqlite>
>> sqlite>
>>
>> THANKS
>>
>>
>> On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS)<
>> michael.bla...@ngc.com
>>  
>>> wrote:
>>>
>>  
>>> You are likely getting the case insensitive result with "like".
>>>
>>> sqlite>  create table t(resourceType varchar);
>>> sqlite>  insert into t values('PSM');
>>> sqlite>  insert into t values('psm');
>>> sqlite>  select * from t where resourceType = 'PSM';
>>> PSM
>>> sqlite>  select * from t where resourceType like 'PSM';
>>> PSM
>>> psm
>>> sqlite>  select * from t where upper(resourceType) = 'PSM';
>>> PSM
>>> psm
>>>
>>> Michael D. Black
>>> Senior Scientist
>>> Northrop Grumman Mission Systems
>>>
>>>
>>> 
>>>
>>> From: sqlite-users-boun...@sqlite.org on behalf of ecforu
>>> Sent: Fri 4/30/2010 8:31 AM
>>> To: General Discussion of SQLite Database
>>> Subject: Re: [sqlite] WHERE = does not work
>>>
>>>
>>>
>>> But the like WHERE clause works the way it is.  Its the = that isn't
>>> working.  I would rather use = than like.  I'm just using like for now
>>> because it works.
>>>
>>> Thanks
>>>
>>> On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer<
>>> tsaw...@mybowlingdiary.com>  wrote:
>>>
>>>
 With the like clause you have to use the % sign as a wildcard. So
 resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite
  
>>> website
>>>
 has excellent docs on standard SQL.

 -Original Message-
 From: ecforu
 Sent: Friday, April 30, 2010 09:22
 To: sqlite-users@sqlite.org
 Subject: [sqlite] WHERE = does not work

 I have an sqlite3 database which I can't query with WHERE =.  I have to
  
>>> use
>>>
 WHERE like.

 Any ideas why this is?

 For example I have a resourceType column that has as some of its
  
>> entries
>>  
 (over 50) 'PSM'.

 SELECT * FROM MyTable WHERE resourceType = 'PSM'  -->  returns nothing.

 SELECT * FROM MyTable WHERE resourceType like 'PSM' -->  returns all PSM
 entries.

 What's the diff?

 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

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

Re: [sqlite] Query plan of MAX (id) versus MAX (id) + 1 in a range

[Jim Morris]

Did you try something like:

SELECT  id + 1 FROM foo WHERE id>= 100 AND id<  200 and id = MAX (id);



On 02/22/2010 7:02 AM, ArtemGr wrote:
> Simon Slavin  writes:
>
>> Just out of interest, and I know that theoretically this is not an optimal
>> statement, but have you compared
>> this with the results of putting the '+1' in the brackets ?
>>  
> Thanks for the pointer, Simon.
>
> Looking back to my analizys, I see that it was wrong.
>   SELECT MAX (id) FROM foo WHERE id>= 100 AND id<  200;
> actually jumps to Close directly after AggStep,
> it would only read a single row to produce the result.
>
> No suck luck for
>   SELECT MAX (id) + 1 FROM foo WHERE id>= 100 AND id<  200;
> which goes thru the whole range.
>
>
> As for MAX (id) + 1 versus MAX (id + 1),
>
> SELECT MAX (id) + 1 FROM foo WHERE id>= 100 AND id<  200;
> calculates MAX (id) in a cycle, then it adds 1 outside of the cycle,
> before submitting ResultRow.
>
> SELECT MAX (id + 1) FROM foo WHERE id>= 100 AND id<  200;
> increments id in every iteration of the cycle and passes the result to max.
>
> MAX (id + 1) is clearly less optimizable than MAX (id) + 1.
> Obvioulsy, SQLite already have a special case optimization for MAX (id),
> but optimization breaks with MAX (id) + 1, making it impractical for use in
> INSERT SELECT.
>
> ___
> 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