[sqlite] New group_split() function as inverse of group_concat()

[Alexey Pechnikov]

Hello!

Can somebody to show example of the group_split() function? 
PRAGMA can return rowset but how to do this in user functions?

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

Re: [sqlite] Disk I/O error on AIX

[Ken]

Roger,

Thats too funny. I guess I forgot posting this last year.

Ken

--- On Mon, 8/3/09, Roger Binns  wrote:

> From: Roger Binns 
> Subject: Re: [sqlite] Disk I/O error on AIX
> To: "General Discussion of SQLite Database" 
> Date: Monday, August 3, 2009, 7:55 PM
> Ken wrote:
> > I'm getting a Disk I/O error when committing a
> transaction on an AIX system.
> > The file system is JFS.
> > The extended result code is 1290. Which i believe
> means that the extended code is a SQLITE_IOERR_DIR_FSYNC
> error.
> > 
> > Any ideas why this is happening or how to track it
> down?
> 
> This has come up before and you may recognise the poster:
> 
>    http://thread.gmane.org/gmane.comp.db.sqlite.general/39682/
> 
> Roger
> 
> ___
> 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 on AIX

[Ken]

Thanks Dan!

I'll check this in the morning.

Ken

--- On Tue, 8/4/09, Dan Kennedy  wrote:

> From: Dan Kennedy 
> Subject: Re: [sqlite] Disk I/O error on AIX
> To: "General Discussion of SQLite Database" 
> Date: Tuesday, August 4, 2009, 12:03 AM
> 
> On Aug 4, 2009, at 5:11 AM, Ken wrote:
> 
> >
> > Hi,
> >
> > I'm getting a Disk I/O error when committing a
> transaction on an AIX  
> > system.
> > The file system is JFS.
> >
> >
> > The extended result code is 1290. Which i believe
> means that the  
> > extended code is a SQLITE_IOERR_DIR_FSYNC error.
> >
> > Any ideas why this is happening or how to track it
> down?
> 
> AIX needs -DSQLITE_DISABLE_DIRSYNC.
> 
> Dan.
> 
> ___
> 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 on AIX

[Dan Kennedy]

On Aug 4, 2009, at 5:11 AM, Ken wrote:

>
> Hi,
>
> I'm getting a Disk I/O error when committing a transaction on an AIX  
> system.
> The file system is JFS.
>
>
> The extended result code is 1290. Which i believe means that the  
> extended code is a SQLITE_IOERR_DIR_FSYNC error.
>
> Any ideas why this is happening or how to track it down?

AIX needs -DSQLITE_DISABLE_DIRSYNC.

Dan.

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

Re: [sqlite] help with inconsistent results and segfault

[Dan Kennedy]

On Aug 4, 2009, at 4:58 AM, Pavlos Christoforou wrote:

> Hello all,
>
> We are currently evaluating sqlite for using it as the
> base engine for a financial reporting module. We have some
> fairly complex queries which yield strange results. We have
> tried to isolate the problem below. Please see select queries
> at the end and associated comments which detail the issue:

Which version are you using? The output of running this script
with 3.6.16 here is:

   x|cum_sum_x|total
   10.0|10.0|110.0
   10.0|20.0|110.0
   20.0|40.0|110.0
   30.0|70.0|110.0
   40.0|110.0|110.0
   x|cum_sum_x|total
   10.0|10.0|110.0
   10.0|20.0|110.0
   20.0|40.0|110.0
   30.0|70.0|110.0
   40.0|110.0|110.0
   sum(x)
   110.0
   sum(x)
   110.0
   SQL error near line 64: misuse of aggregate: max()

Dan.

> ===
>
> create table Test (x FLOAT);
>
> insert into Test values (10);
> insert into Test values (10);
> insert into Test values (20);
> insert into Test values (30);
> insert into Test values (40);
>
>
> create view TestViewWeird
> as
> select max(t1.x) as x,
>   sum(t2.x) as cum_sum_x,
>   -- bad sql below but is should yield consistent results IMO
>   (select sum(x) from Test) as total
> from Test as t1 inner join Test as t2
> on t2.oid <= t1.oid
> -- only difference is expression below
> and total * t1.x > 0
> --
> group by t1.oid
> ;
>
> create view TestView
> as
> select max(t1.x) as x,
>   sum(t2.x) as cum_sum_x,
>   -- bad sql below but is should yield consistent results IMO
>   (select sum(x) from Test) as total
> from Test as t1 inner join Test as t2
> on t2.oid <= t1.oid
> -- only difference is expression below
> and (select sum(x) from Test) * t1.x > 0
> --
> group by t1.oid
> ;
>
>
> create view TestViewSegFault as
> select max(t1.x) as x,
>   sum(t2.x) as cum_sum_x,
>   -- only difference is expression below
>   max((select sum(x) from Test)) as total
> from Test as t1 inner join Test as t2
> on t2.oid <= t1.oid
> and total * t1.x > 0
> --
> group by t1.oid
> ;
>
>
> .headers on
>
> -- below queries yield exactly the same results
> select * from TestView;
> select * from TestViewWeird;
>
> -- the first query below returns a value but the second returns a  
> null.
> select sum(x) from TestView;
> select sum(x) from TestViewWeird;
>
> -- below seg faults
> select * from TestViewSegFault;
>
> ==
>
> what are we doing wrong? Any help or pointers to appropriate docs  
> will be greatly appreciated.
>
> Thanks
>
> Pavlos
> ___
> 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] Date Cutoff Statement

[Rick Ratchford]

 
#>Rick Ratchford wrote:
#>> #>Try
#>> #>
#>> #>date(max(Date), 'weekday 5')
#>>
#>> It's likely I'm not using it correctly, because it returns 
#>nothing. :(
#>
#>I mean, replace "Date" in your statement with this expression. As in
#>
#>SELECT date(max(Date), 'weekday 5') FROM MyTable GROUP BY Year, Week;
#

It works. The last time I tried it I got nada. So I must have typed it in
wrong.

#>> #>> Is there an answer to my problem somewhere in the above output?
#>> #>
#>> #>No, not directly. Teach a man to fish, and all that.
#>>
#>> Completely understand. Just wish my fishing pole was a little more 
#>> forgiving. :)
#>
#>An interesting specimen of a double entendre here.
#>
#>Igor Tandetnik 

"double-entendre"?. I had to look that up.
 
Was a reference to "Teach a man to fish", where one needs good working tools
to get the job done. My 'tools' (mental-my mind) needs a bit of work. That's
the only part of the anatomy I was referring to. LOL! 

I had written a loop and had it check each date to make sure it was Friday
date. When it was not, I directly changed it in the recordset. This worked
also, but is not as clean and efficient as using this that function in the
SQL.

Thanks for your help Igor.





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

Re: [sqlite] Date Cutoff Statement

[Igor Tandetnik]

Rick Ratchford wrote:
> #>Try
> #>
> #>date(max(Date), 'weekday 5')
>
> It's likely I'm not using it correctly, because it returns nothing. :(

I mean, replace "Date" in your statement with this expression. As in

SELECT date(max(Date), 'weekday 5') FROM MyTable GROUP BY Year, Week;

> #>> Is there an answer to my problem somewhere in the above output?
> #>
> #>No, not directly. Teach a man to fish, and all that.
>
> Completely understand. Just wish my fishing pole was a little more
> forgiving. :)

An interesting specimen of a double entendre here.

Igor Tandetnik 



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

Re: [sqlite] Date Cutoff Statement

[Rick Ratchford]

#>> What I ended up with are the number of days per each week (row) and 
#>> the last date for that week that had data.
#>
#>Yes, of course. What did you expect?

Exactly what I got. :)

#>
#>> Here are the last few rows.
#>>
#>> count(*) max(Date)
#>> =
#>> 5 2009-06-26
#>> 4 2009-07-02
#>> 5 2009-07-10
#>> 5 2009-07-17
#>> 5 2009-07-24
#>> 5 2009-07-31
#>> 1 2009-08-03
#>>
#>> Note that each of these dates belongs to the FRIDAY of that week, 
#>> except for 2009-07-02 and 2009-08-03.
#>
#>Is this surprising?

Nope. :)

#>
#>> This is the problem. While week 2009-08-007 is not yet 
#>complete (it is 
#>> in progress), the prior dates need to be FRIDAY dates even if the 
#>> count < 5.
#>
#>Try
#>
#>date(max(Date), 'weekday 5')

It's likely I'm not using it correctly, because it returns nothing. :(

#>
#>For more details, see http://www.sqlite.org/lang_datefunc.html

Read it. Unfortunately it doesn't help dummies who aren't sure where in the
statement the darn thing should go. LOL!

#>> I was aware of this when the data was displayed earlier. 
#>I'm not sure 
#>> what is 'revealed' other than what is stated above.
#>
#>Well, you appeared surprised that you weren't getting all 
#>Friday dates.

Surprised? Mixed reviews. Grouping my daily data into weekly groups was a
big deal for me when Olaf showed that. I used to do this through lots of
code loops, converting daily data into weekly data. Then here comes this SQL
statement and viola! You might say I was a bit disappointed to find it
didn't account for weeks missing data for Friday.
 
#>You claimed you couldn't understand why an addition of a 
#>WHERE clause changed the output the way it did. I hoped the 
#>demonstration of a "truncated" group would help you "put your 
#>finger on your error".

I didn't understand why the WHERE didn't allow me to stop creating 'weekly'
rows up to a certain date (and not including). It turned out that my head
was still not wrapped around the GROUP thing.

#>In any case, you do seem to possess a clearer understanding 
#>of the issue at this time, whether due to, in spite of, or 
#>independently of my efforts.

You left off "with the addition of my efforts". It's been a GROUP help thing
(pun intended). And I really do appreciate your comments, help and time. :)

#>
#>> Is there an answer to my problem somewhere in the above output?
#>
#>No, not directly. Teach a man to fish, and all that.

Completely understand. Just wish my fishing pole was a little more
forgiving. :)

#>> Is there perhaps some SQL command that based on 'count' if less than
#>> 5 the
#>> difference can be added to the date before returning it in the 
#>> recordset?
#>
#>Well, you could do something like
#>
#>date(max(Date), (5 - count(*)) || ' days')
#>
#>but that won't work right if you are missing, say, Tuesday data.
#>
#>Igor Tandetnik 

And that wouldn't work for me since there are days prior to Friday that may
not have data due to Holidays.

It's starting to look like I'm going to have to loop through my recordset
after it has been created, check each date, and if not a Friday, change it
there.

Thanks!

Rick


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

Re: [sqlite] Date Cutoff Statement

[Igor Tandetnik]

Rick Ratchford wrote:
> What I ended up with are the number of days per each week (row) and
> the last
> date for that week that had data.

Yes, of course. What did you expect?

> Here are the last few rows.
>
> count(*) max(Date)
> =
> 5 2009-06-26
> 4 2009-07-02
> 5 2009-07-10
> 5 2009-07-17
> 5 2009-07-24
> 5 2009-07-31
> 1 2009-08-03
>
> Note that each of these dates belongs to the FRIDAY of that week,
> except for 2009-07-02 and 2009-08-03.

Is this surprising?

> This is the problem. While week 2009-08-007 is not yet complete (it
> is in
> progress), the prior dates need to be FRIDAY dates even if the count
> < 5.

Try

date(max(Date), 'weekday 5')

For more details, see http://www.sqlite.org/lang_datefunc.html

> I was aware of this when the data was displayed earlier. I'm not sure
> what
> is 'revealed' other than what is stated above.

Well, you appeared surprised that you weren't getting all Friday dates. 
You claimed you couldn't understand why an addition of a WHERE clause 
changed the output the way it did. I hoped the demonstration of a 
"truncated" group would help you "put your finger on your error".

In any case, you do seem to possess a clearer understanding of the issue 
at this time, whether due to, in spite of, or independently of my 
efforts.

> Is there an answer to my problem somewhere in the above output?

No, not directly. Teach a man to fish, and all that.

> Is there perhaps some SQL command that based on 'count' if less than
> 5 the
> difference can be added to the date before returning it in the
> recordset?

Well, you could do something like

date(max(Date), (5 - count(*)) || ' days')

but that won't work right if you are missing, say, Tuesday data.

Igor Tandetnik 



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

Re: [sqlite] Date Cutoff Statement

[Olaf Schmidt]

"Igor Tandetnik"  schrieb im
Newsbeitrag news:h584q5$jo...@ger.gmane.org...

> You've truncated the last group short, so a different row from that
> "incomplete" group accidentally happened to be chosen.
Yep - therefore the recommendation in the VB-newsgroup,
to better rely on the Having-clause (performance is not that
much an issue in Ricks case).

> Try
>
> SELECT count(*), max(Date) FROM MyTable GROUP BY Year, Week

Ah yes - good catch (the Max(Date) term).

Although the SQLite-engine behaves relative "stable",
regarding "which value is filled into a Column that is part
of an Group By-construct", when such a column is specified
without an aggregate-expression.
Currently! (yes, nothing to rely on) it is the last value, that
"reaches the group", probably because the (temporarily used)
sorter, which stores the incoming Rows "sorting whilst adding"
(according to the Group By-clause), shows a "stable" sort-
behaviour, not changing the "first-level, incoming order" which
is determined by the RowID of the underlying table itself, as
I see it.
And Ricks table is built with increasing (auto) RowIDs,
"in sync" to the (already sorted) incoming Trading-
days/dates - and their "derived" Year, Month, Week, etc.
Columns.

So I think Rick will not see a difference regarding the reported
Date between:
SELECT count(*), max(Date) As Date
FROM MyTable
GROUP BY Year, Week

and

SELECT count(*), Date
FROM MyTable
GROUP BY Year, Week

or for better comparison:
SELECT count(*), max(Date) As MaxDate, Date
FROM MyTable
GROUP BY Year, Week

But without doubt, he should change his current query to
the Max(Date) aggregate to be on the safe side.

That leads me to a different (somewhat OT-question in
the context of *this* thread)...

I recently noticed this entry in the SQLite-tktview:
http://www.sqlite.org/cvstrac/tktview?tn=3979

And want to implement a fast sorter for SQLite, to become
more familiar with the C-language.

My question to you experts (including David, who already
looked after that ticket) - is there already "work in progress"
or were there already attempts, to write at least a "temporary
only sorter", which handles all these "throw-away after delivery"
Distinct - and Group By, ... etc. cases?

Is it possible at all, to write such a thing without tight integration
into SQLites VDBE?
If yes, is there already an interface-spec available, to handle
such temporary sorts with different (pluggable) sort-modules,
in a similar way as the interface for e.g. the vfs' was designed
and works?

If no such interface-spec exists, would it be possible to
design such a thing (at least "roughly", by an sqlite-expert) and
publish it here or on the sqlite-site, before I start working on that,
or do you say: "just be creative!".

Any input on that is appreciated - links to already existing
attempts or code-snippets too (not meaning the sorting-
approach itself, more regarding the integration into SQLite).

Regards,

Olaf Schmidt



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

Re: [sqlite] Merging blobs on disk without taking up memory???

[Teg]

Hello Simon,

Monday, August 3, 2009, 8:28:03 PM, you wrote:

SD> 2009/8/3 sorka :
>>
>> Hi. I have a table that stores pieces of an image as a bunch of blobs. I get
>> the pieces out of order and store them in a table until I get all of the
>> pieces I need. I then want to assemble them in order and store the resulting
>> complete image in in another table entirely.
>>
>> Is there a smart way to go about this so that memory use is limited? A
>> select and order by the part number will return each blob in order. Is there
>> a way to use concat to build up the final blob in the other table without
>> having to store all the pieces in memory first?

SD> use blob incremental io:

SD> http://www.sqlite.org/c3ref/blob_open.html

>>
>> Thanks.
>> --

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

Why assemble them at all? You have the order the blobs appear. When
it's time to pull the completed blob out of the DB, just read them in
order.

-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Date Cutoff Statement

[Rick Ratchford]

Hello Igor.

What I ended up with are the number of days per each week (row) and the last
date for that week that had data.

Here are the last few rows.

count(*)max(Date)
=
5   2009-06-26
4   2009-07-02
5   2009-07-10
5   2009-07-17
5   2009-07-24
5   2009-07-31
1   2009-08-03

Note that each of these dates belongs to the FRIDAY of that week, except for
2009-07-02 and 2009-08-03.

This is because week ending 2009-07-03 had no data for Friday, so the last
day that week with data was 2009-07-02.

And since my data ends with today, being Monday 2009-08-03, we get that last
date/row.

This is the problem. While week 2009-08-007 is not yet complete (it is in
progress), the prior dates need to be FRIDAY dates even if the count < 5. 

I was aware of this when the data was displayed earlier. I'm not sure what
is 'revealed' other than what is stated above.

Is there an answer to my problem somewhere in the above output?

Is there perhaps some SQL command that based on 'count' if less than 5 the
difference can be added to the date before returning it in the recordset?

I'm a real greenie on this, so you might say I'm baffled. :-b

Thanks.
Rick



 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Monday, August 03, 2009 9:01 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Date Cutoff Statement
#>
#>Rick Ratchford wrote:
#>> SELECT Date FROM MyTable GROUP BY Year, Week
#>>
#>> This creates a recordset that groups all my prices into 'weekly'
#>> prices. In other words, each row represents the High, Low, Close 
#>> prices for each week, and the date is the FRIDAY DATE of that week.
#>
#>If this happens, then only by accident. The value of Date 
#>reported for each group comes from an arbitrary row belonging 
#>to the group. There is no guarantee which row will be so chosen.
#>
#>> However, by adding WHERE Date < '" dStopDate "' prior to GROUP BY...
#>> (and yes, assume Date and dStopDate are same format), my 
#>last record 
#>> returned is actually 07/27/2009 (the day before my 
#>dStopDate) rather 
#>> than my 'weekly' record of 07/24/2009.
#>
#>You've truncated the last group short, so a different row 
#>from that "incomplete" group accidentally happened to be chosen.
#>
#>Try
#>
#>SELECT count(*), max(Date) FROM MyTable GROUP BY Year, Week
#>
#>with and without WHERE clause. This might prove illuminating.
#>
#>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

Re: [sqlite] Date Cutoff Statement

[Igor Tandetnik]

Rick Ratchford wrote:
> SELECT Date FROM MyTable GROUP BY Year, Week
>
> This creates a recordset that groups all my prices into 'weekly'
> prices. In other words, each row represents the High, Low, Close
> prices for each week, and the date is the FRIDAY DATE of that week.

If this happens, then only by accident. The value of Date reported for 
each group comes from an arbitrary row belonging to the group. There is 
no guarantee which row will be so chosen.

> However, by adding WHERE Date < '" dStopDate "' prior to GROUP BY...
> (and yes, assume Date and dStopDate are same format), my last record
> returned is actually 07/27/2009 (the day before my dStopDate) rather
> than my 'weekly' record of 07/24/2009.

You've truncated the last group short, so a different row from that 
"incomplete" group accidentally happened to be chosen.

Try

SELECT count(*), max(Date) FROM MyTable GROUP BY Year, Week

with and without WHERE clause. This might prove illuminating.

Igor Tandetnik 



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

Re: [sqlite] Date Cutoff Statement

[Rick Ratchford]

#>>  
#>> What I want to do is modify this SELECT statement so that the rows 
#>> returned do not go past a certain date. Let's call it dStopDate.
#>>  
#>> If I have dStopDate = '2009-28-07'
#>
#>Did you mean '2009-07-28' ?
#>

Yes.

#>> for example, then the last row I want to return is 
#>07/24/2009, which 
#>> is the last week prior to my dStopDate.
#>>  
#>> However, by adding WHERE Date < '" dStopDate "' prior to 
#>GROUP BY... 
#>> (and yes, assume Date and dStopDate are same format), my 
#>last record 
#>> returned is actually 07/27/2009 (the day before my 
#>dStopDate) rather than my 'weekly'
#>> record of 07/24/2009.
#>
#>Did you mean "<=" instead of "<" ?

No. "<" is correct. I do not want to include the dStopDate. Just the dates
'up to' the dStopDate.

Anyway, Olaf answered this for me on a newsgroup. Appears that what I must
do is to do my test on the GROUPED data.

Therefore, the solution is to remove the WHERE and replace it with HAVING
after the GROUP BY in my statement.

This solved that problem (and now exposed a whole new one!).

Seems my GROUPED data has a slight flaw. While most of the rows contain a
FRIDAY date, there are some that have THURSDAYS date because no data was
available for that particular Friday. This is a problem, since I must have
nothing but FRIDAY dates for each row no matter if the data stopped on
Thursday.

LOL!!

Thanks.
Rick


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

Re: [sqlite] Disk I/O error on AIX

[Roger Binns]

Ken wrote:
> I'm getting a Disk I/O error when committing a transaction on an AIX system.
> The file system is JFS.
> The extended result code is 1290. Which i believe means that the extended 
> code is a SQLITE_IOERR_DIR_FSYNC error.
> 
> Any ideas why this is happening or how to track it down?

This has come up before and you may recognise the poster:

   http://thread.gmane.org/gmane.comp.db.sqlite.general/39682/

Roger

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

Re: [sqlite] Date Cutoff Statement

[John Machin]

On 4/08/2009 8:52 AM, Rick Ratchford wrote:
>  
> What I want to do is modify this SELECT statement so that the rows returned
> do not go past a certain date. Let's call it dStopDate.
>  
> If I have dStopDate = '2009-28-07'

Did you mean '2009-07-28' ?

> for example, then the last row I want to
> return is 07/24/2009, which is the last week prior to my dStopDate.
>  
> However, by adding WHERE Date < '" dStopDate "' prior to GROUP BY... (and
> yes, assume Date and dStopDate are same format), my last record returned is
> actually 07/27/2009 (the day before my dStopDate) rather than my 'weekly'
> record of 07/24/2009.

Did you mean "<=" instead of "<" ?



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

Re: [sqlite] Merging blobs on disk without taking up memory???

[Simon Davies]

2009/8/3 sorka :
>
> Hi. I have a table that stores pieces of an image as a bunch of blobs. I get
> the pieces out of order and store them in a table until I get all of the
> pieces I need. I then want to assemble them in order and store the resulting
> complete image in in another table entirely.
>
> Is there a smart way to go about this so that memory use is limited? A
> select and order by the part number will return each blob in order. Is there
> a way to use concat to build up the final blob in the other table without
> having to store all the pieces in memory first?

use blob incremental io:

http://www.sqlite.org/c3ref/blob_open.html

>
> Thanks.
> --

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

Re: [sqlite] Date Comparisons SQL

[Olaf Schmidt]

"Rich Shepard"  schrieb im
Newsbeitrag news:alpine.lnx.2.00.0908031516300.3...@salmo.appl-ecosys.com...

> > It was set as String actually.
>
> Rick,
>
>That's the storage class; well, TEXT is the storage class.
Yep.

> > I believe this is a WRAPPER thing though.
Exactly.
The COM-wrapper handles Date-Column-Definitions
which are "declared" in the Create Table Statement
as "..., MyDate Date, ..." or "..., MyShortDate ShortDate, ..."
with something like an "auto-mapping" to the appropriate
vbDate Variable-Type (which at binary-level, in the language
is a Floatingpoint-Type with Double-precision - counting the
days since 1899-12-30, with the "percentage of a day"
encoded in the fractional part of this floatingpoint-number).

So, it's the wrapper who maps vbDate-Types to the SQLite-engines
Text-storage-class (using the standard-SQL "textdate-format",
'-mm-dd' for ShortDates and '-mm-dd hh:mm:ss'
for "full dates". The SQLite-engine itself does not know about
these "VB-Double-DateTypes "the wrapper hands out to the
application.

Just to shade some light on the topic... ;-)

Olaf



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

[sqlite] Date Cutoff Statement

[Rick Ratchford]

I'm stuck on a problem that is halting my project. I hope someone can help
on this one as I think it's a SQL related question.
 
MyTable contains the Date, Year, Week (and other columns).
Week is a week number.
 
MyTable holds my stock prices for each trading day.
 
I've omited the price data column references to keep this question as clear
as possible.
 
SELECT Date FROM MyTable GROUP BY Year, Week
 
This creates a recordset that groups all my prices into 'weekly' prices. In
other words, each row represents the High, Low, Close prices for each week,
and the date is the FRIDAY DATE of that week.
 
So the above statement might return the following (last 5 only):
 
...
07/03/2009
07/10/2009
07/17/2009
07/24/2009
07/31/2009
 
Note that each row in the Date column is a date that represents the last
date of the week, Friday's date.
 
What I want to do is modify this SELECT statement so that the rows returned
do not go past a certain date. Let's call it dStopDate.
 
If I have dStopDate = '2009-28-07', for example, then the last row I want to
return is 07/24/2009, which is the last week prior to my dStopDate.
 
However, by adding WHERE Date < '" dStopDate "' prior to GROUP BY... (and
yes, assume Date and dStopDate are same format), my last record returned is
actually 07/27/2009 (the day before my dStopDate) rather than my 'weekly'
record of 07/24/2009.
 
I can't put my finger on my error.
 
Anyone have a suggestion?
 
Thanks.
 
Rick
 
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Disk I/O error on AIX

[Simon Slavin]

On 3 Aug 2009, at 11:11pm, Ken wrote:

> I'm getting a Disk I/O error when committing a transaction on an AIX  
> system.
> The file system is JFS.

Check all the obvious things:

Is there free space on drive ?
If you replace that transaction by a very simple INSERT, or by a  
DELETE, do you get the same error ?


> The extended result code is 1290. Which i believe means that the  
> extended code is a SQLITE_IOERR_DIR_FSYNC error.
>
> Any ideas why this is happening or how to track it down?

Get your database all the way up to that final transaction then have  
your program quit.
Start up the sqlite3 command-line tool.

Use the command that checks to see the database is not corrupt.
Enter your final transaction and commit it.
Do you get the same error you got in your own application ?

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

Re: [sqlite] Date Comparisons SQL

[Rick Ratchford]

Olaf tells me that it's stored as Text-String.

Either as '-mm-dd' or '-mm-dd hh:mm:ss', depending on how I decide
to store my VB type dates.

Anyway, the original problem was solved. I simply neglected to address the
need for 'quotes' around my date variable.

Thanks Rich.

:-)
Rick


 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard
#>Sent: Monday, August 03, 2009 5:18 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>On Mon, 3 Aug 2009, Rick Ratchford wrote:
#>
#>> It was set as String actually.
#>
#>Rick,
#>
#>   That's the storage class; well, TEXT is the storage class.
#>
#>> I believe this is a WRAPPER thing though. I'm programming 
#>in VB6 and 
#>> using Olaf's VB wrapper.
#>
#>   Oh. I know nothing about Microsoft languages (or operating 
#>systems for that matter), except that they're different. I do 
#>all my coding in C or Python.
#>
#>Rich
#>
#>-- 
#>Richard B. Shepard, Ph.D.   |  Integrity  
#>  Credibility
#>Applied Ecosystem Services, Inc.|Innovation
#> Voice: 503-667-4517  
#>Fax: 503-667-8863
#>___
#>sqlite-users mailing list
#>sqlite-users@sqlite.org
#>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
#>
#>


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

[sqlite] Merging blobs on disk without taking up memory???

[sorka]

Hi. I have a table that stores pieces of an image as a bunch of blobs. I get
the pieces out of order and store them in a table until I get all of the
pieces I need. I then want to assemble them in order and store the resulting
complete image in in another table entirely. 

Is there a smart way to go about this so that memory use is limited? A
select and order by the part number will return each blob in order. Is there
a way to use concat to build up the final blob in the other table without
having to store all the pieces in memory first?

Thanks.
-- 
View this message in context: 
http://www.nabble.com/Merging-blobs-on-disk-without-taking-up-memorytp24799151p24799151.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Date Comparisons SQL

[Rich Shepard]

On Mon, 3 Aug 2009, Rick Ratchford wrote:

> It was set as String actually.

Rick,

   That's the storage class; well, TEXT is the storage class.

> I believe this is a WRAPPER thing though. I'm programming in VB6 and using
> Olaf's VB wrapper.

   Oh. I know nothing about Microsoft languages (or operating systems for
that matter), except that they're different. I do all my coding in C or
Python.

Rich

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

[sqlite] Disk I/O error on AIX

[Ken]

Hi,

I'm getting a Disk I/O error when committing a transaction on an AIX system.
The file system is JFS.


The extended result code is 1290. Which i believe means that the extended code 
is a SQLITE_IOERR_DIR_FSYNC error.

Any ideas why this is happening or how to track it down?

Thanks,
Ken

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

Re: [sqlite] Date Comparisons SQL

[Rick Ratchford]

It was set as String actually.

I've since changed it to ShortDate today.

I believe this is a WRAPPER thing though. I'm programming in VB6 and using
Olaf's VB wrapper.

:)
Rick
 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard
#>Sent: Monday, August 03, 2009 3:45 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>On Mon, 3 Aug 2009, Rick Ratchford wrote:
#>
#>> The native Date in a table without any additional expressions is 
#>> '-mm-dd 00:00:00'.
#>
#>Rick,
#>
#>   That's a timestamp format. Did you specify the column as 
#>date or timestamp?
#>
#>Rich
#>
#>-- 
#>Richard B. Shepard, Ph.D.   |  Integrity  
#>  Credibility
#>Applied Ecosystem Services, Inc.|Innovation
#> Voice: 503-667-4517  
#>Fax: 503-667-8863
#>___
#>sqlite-users mailing list
#>sqlite-users@sqlite.org
#>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
#>
#>


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

[sqlite] help with inconsistent results and segfault

[Pavlos Christoforou]

Hello all,

We are currently evaluating sqlite for using it as the 
base engine for a financial reporting module. We have some 
fairly complex queries which yield strange results. We have 
tried to isolate the problem below. Please see select queries 
at the end and associated comments which detail the issue:

===

create table Test (x FLOAT);

insert into Test values (10);
insert into Test values (10);
insert into Test values (20);
insert into Test values (30);
insert into Test values (40);


create view TestViewWeird
as
select max(t1.x) as x,
   sum(t2.x) as cum_sum_x,
   -- bad sql below but is should yield consistent results IMO
   (select sum(x) from Test) as total
from Test as t1 inner join Test as t2
on t2.oid <= t1.oid 
-- only difference is expression below
and total * t1.x > 0
--
group by t1.oid
;

create view TestView
as
select max(t1.x) as x,
   sum(t2.x) as cum_sum_x,
   -- bad sql below but is should yield consistent results IMO
   (select sum(x) from Test) as total
from Test as t1 inner join Test as t2
on t2.oid <= t1.oid 
-- only difference is expression below
and (select sum(x) from Test) * t1.x > 0
--
group by t1.oid
;


create view TestViewSegFault as
select max(t1.x) as x,
   sum(t2.x) as cum_sum_x,
   -- only difference is expression below
   max((select sum(x) from Test)) as total
from Test as t1 inner join Test as t2
on t2.oid <= t1.oid 
and total * t1.x > 0
--
group by t1.oid
;


.headers on

-- below queries yield exactly the same results
select * from TestView;
select * from TestViewWeird;

-- the first query below returns a value but the second returns a null.
select sum(x) from TestView;
select sum(x) from TestViewWeird;

-- below seg faults
select * from TestViewSegFault;

==

what are we doing wrong? Any help or pointers to appropriate docs will be 
greatly appreciated.

Thanks

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

Re: [sqlite] Date Comparisons SQL

[Rich Shepard]

On Mon, 3 Aug 2009, Rick Ratchford wrote:

> The native Date in a table without any additional expressions is
> '-mm-dd 00:00:00'.

Rick,

   That's a timestamp format. Did you specify the column as date or
timestamp?

Rich

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

Re: [sqlite] Week

[helemacd]

Thank u very much!!! :)

the end of the week can be

select (case strftime('%w', T) when '0' then T else date(T, 'weekday 0')
end)
from (select date('now') as T); 

--or




Igor Tandetnik wrote:
> 
> helemacd wrote:
>> anybody know how to return the start of the week and end of the
>> week???
> 
> select (case strftime('%w', T) when '0' then T else date(T, 'weekday 0', 
> '-7 days') end)
> from (select date('now') as T);
> 
> -- or
> 
> select date(T, '-' || strftime('%w', T) || ' days')
> from (select date('now') as T);
> 
> This is start of the week, assuming the US convention of beginning the 
> week on Sundays. End of the week is left as an exercise for the reader.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Week-tp24795944p24796813.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Date Comparisons SQL

[Rick Ratchford]

And that, my friend, was the missing link!

Thank you!

Rick

 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of David Bicking
#>Sent: Monday, August 03, 2009 1:57 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>I think your problem is that you need to put the value in 
#>sDateTemp in quotes.
#>
#>"WHERE Date < '" & sDateTemp & "'"
#>
#>or "WHERE Format$(Date, "-mm-dd") < '"& sDateTemp & "'"
#>
#>Without the quote, I think sqlite is subtracting the day from 
#>the month from the year, and comparing that number with the 
#>Date string.
#>
#>David
#>
#>
#>--- On Mon, 8/3/09, Rick Ratchford  wrote:
#>
#>> From: Rick Ratchford 
#>> Subject: Re: [sqlite] Date Comparisons SQL
#>> To: "'General Discussion of SQLite Database'" 
#>> 
#>> Date: Monday, August 3, 2009, 2:51 PM
#>> Okay, I think I understand what you
#>> are saying.
#>> 
#>> The native Date in a table without any additional expressions is 
#>> '-mm-dd 00:00:00'.
#>> 
#>> Those "00:00:00" must be my problem.
#>> 
#>> Rick
#>> 
#>> 
#>> 
#>> 
#>> #>-Original Message-
#>> #>From: sqlite-users-boun...@sqlite.org
#>> 
#>> #>[mailto:sqlite-users-boun...@sqlite.org]
#>> On Behalf Of Rick Ratchford
#>> #>Sent: Monday, August 03, 2009 1:45 PM
#>> #>To: 'General Discussion of SQLite Database'
#>> #>Subject: Re: [sqlite] Date Comparisons SQL #> #>That's 
#>the clincer.
#>> #>
#>> #>The resulting DATE column is actually the format of the 
#>#>equation 
#>> as well.
#>> #>
#>> #>I've attached a view of the results from the working SQL 
#>#>statement 
#>> that does not perform the WHERE.
#>> #>
#>> #>"WHERE Format$(Date, '-mm-dd') < sDateTemp"
#>> does not work.
#>> #>
#>> #>Also, as stated in my previous post, I have sDateTemp
#>> 
#>> #>formatted in the same format as that which is in the table.
#>> #>
#>> #>That's why I'm puzzled.
#>> #>
#>> #>Rick
#>> #>
#>> #>
#>> #>
#>> #>#>-Original Message-
#>> #>#>From: sqlite-users-boun...@sqlite.org
#>> 
#>> #>#>[mailto:sqlite-users-boun...@sqlite.org]
#>> On Behalf Of Igor Tandetnik
#>> #>#>Sent: Monday, August 03, 2009 1:38 PM
#>> #>#>To: sqlite-users@sqlite.org
#>> #>#>Subject: Re: [sqlite] Date Comparisons SQL #> #>Rick 
#>#>Ratchford 
#>> wrote:
#>> #>#>> The Date is being stored as -mm-dd. Note the 
#>> #>"Format$(Date, #>> '-mm-dd') as Date" that assures this.
#>> #>#>
#>> #>#>The "Date" that appears in the WHERE clause is the value of 
#>> #>#>the Date column in the table, not the value of the #>expression 
#>> #>with the "Date"
#>> #>#>alias. You can't actually use aliases in the WHERE clause.
#>> #>#>You are confusing yourself by using the same identifier #>both 
#>> #>for the column name and for the alias.
#>> #>#>
#>> #>#>You could write
#>> #>#>
#>> #>#>WHERE Format$(Date, '-mm-dd') < sDateTemp #> #>Or else, 
#>> #>express sDateTemp in the same format that you have 
#>#>dates #>stored 
#>> in the table - the format you get when you just #>run 
#>#>"SELECT Date 
#>> from mytable".
#>> #>#>
#>> #>#>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
#>
#>


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

Re: [sqlite] Date Comparisons SQL

[David Bicking]

I think your problem is that you need to put the value in sDateTemp in quotes.

"WHERE Date < '" & sDateTemp & "'"

or "WHERE Format$(Date, "-mm-dd") < '"& sDateTemp & "'"

Without the quote, I think sqlite is subtracting the day from the month from 
the year, and comparing that number with the Date string.

David


--- On Mon, 8/3/09, Rick Ratchford  wrote:

> From: Rick Ratchford 
> Subject: Re: [sqlite] Date Comparisons SQL
> To: "'General Discussion of SQLite Database'" 
> Date: Monday, August 3, 2009, 2:51 PM
> Okay, I think I understand what you
> are saying.
> 
> The native Date in a table without any additional
> expressions is '-mm-dd
> 00:00:00'.
> 
> Those "00:00:00" must be my problem.
> 
> Rick
> 
> 
> 
> 
> #>-Original Message-
> #>From: sqlite-users-boun...@sqlite.org
> 
> #>[mailto:sqlite-users-boun...@sqlite.org]
> On Behalf Of Rick Ratchford
> #>Sent: Monday, August 03, 2009 1:45 PM
> #>To: 'General Discussion of SQLite Database'
> #>Subject: Re: [sqlite] Date Comparisons SQL
> #>
> #>That's the clincer.
> #>
> #>The resulting DATE column is actually the format of
> the 
> #>equation as well.
> #>
> #>I've attached a view of the results from the working
> SQL 
> #>statement that does not perform the WHERE.
> #>
> #>"WHERE Format$(Date, '-mm-dd') < sDateTemp"
> does not work.
> #>
> #>Also, as stated in my previous post, I have sDateTemp
> 
> #>formatted in the same format as that which is in the
> table.
> #>
> #>That's why I'm puzzled.
> #>
> #>Rick
> #> 
> #> 
> #>
> #>#>-Original Message-
> #>#>From: sqlite-users-boun...@sqlite.org
> 
> #>#>[mailto:sqlite-users-boun...@sqlite.org]
> On Behalf Of Igor Tandetnik
> #>#>Sent: Monday, August 03, 2009 1:38 PM
> #>#>To: sqlite-users@sqlite.org
> #>#>Subject: Re: [sqlite] Date Comparisons SQL #>
> #>Rick 
> #>Ratchford wrote:
> #>#>> The Date is being stored as -mm-dd. Note
> the 
> #>"Format$(Date, #>> '-mm-dd') as Date" that
> assures this.
> #>#>
> #>#>The "Date" that appears in the WHERE clause is
> the value of 
> #>#>the Date column in the table, not the value of
> the 
> #>expression #>with the "Date" 
> #>#>alias. You can't actually use aliases in the
> WHERE clause. 
> #>#>You are confusing yourself by using the same
> identifier 
> #>both #>for the column name and for the alias.
> #>#>
> #>#>You could write
> #>#>
> #>#>WHERE Format$(Date, '-mm-dd') < sDateTemp
> #> #>Or else, 
> #>express sDateTemp in the same format that you have
> #>dates 
> #>stored in the table - the format you get when you just
> #>run 
> #>"SELECT Date from mytable".
> #>#>
> #>#>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] Week

[Igor Tandetnik]

helemacd wrote:
> anybody know how to return the start of the week and end of the
> week???

select (case strftime('%w', T) when '0' then T else date(T, 'weekday 0', 
'-7 days') end)
from (select date('now') as T);

-- or

select date(T, '-' || strftime('%w', T) || ' days')
from (select date('now') as T);

This is start of the week, assuming the US convention of beginning the 
week on Sundays. End of the week is left as an exercise for the reader.

Igor Tandetnik 



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

Re: [sqlite] Date Comparisons SQL

[Rick Ratchford]

Okay, I think I understand what you are saying.

The native Date in a table without any additional expressions is '-mm-dd
00:00:00'.

Those "00:00:00" must be my problem.

Rick




#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
#>Sent: Monday, August 03, 2009 1:45 PM
#>To: 'General Discussion of SQLite Database'
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>That's the clincer.
#>
#>The resulting DATE column is actually the format of the 
#>equation as well.
#>
#>I've attached a view of the results from the working SQL 
#>statement that does not perform the WHERE.
#>
#>"WHERE Format$(Date, '-mm-dd') < sDateTemp" does not work.
#>
#>Also, as stated in my previous post, I have sDateTemp 
#>formatted in the same format as that which is in the table.
#>
#>That's why I'm puzzled.
#>
#>Rick
#> 
#> 
#>
#>#>-Original Message-
#>#>From: sqlite-users-boun...@sqlite.org 
#>#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>#>Sent: Monday, August 03, 2009 1:38 PM
#>#>To: sqlite-users@sqlite.org
#>#>Subject: Re: [sqlite] Date Comparisons SQL #> #>Rick 
#>Ratchford wrote:
#>#>> The Date is being stored as -mm-dd. Note the 
#>"Format$(Date, #>> '-mm-dd') as Date" that assures this.
#>#>
#>#>The "Date" that appears in the WHERE clause is the value of 
#>#>the Date column in the table, not the value of the 
#>expression #>with the "Date" 
#>#>alias. You can't actually use aliases in the WHERE clause. 
#>#>You are confusing yourself by using the same identifier 
#>both #>for the column name and for the alias.
#>#>
#>#>You could write
#>#>
#>#>WHERE Format$(Date, '-mm-dd') < sDateTemp #> #>Or else, 
#>express sDateTemp in the same format that you have #>dates 
#>stored in the table - the format you get when you just #>run 
#>"SELECT Date from mytable".
#>#>
#>#>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

Re: [sqlite] Date Comparisons SQL

[Pavel Ivanov]

> I've attached a view of the results from the working SQL statement that does
> not perform the WHERE.

Attachments do not come through to this list.

> Also, as stated in my previous post, I have sDateTemp formatted in the same
> format as that which is in the table.

Could you elaborate: your sDateTemp is "the same format as in the
table" or "formatted as '-mm-dd'"? If the former then what the
format is? And format exactly in the table?

Pavel

On Mon, Aug 3, 2009 at 2:44 PM, Rick Ratchford wrote:
> That's the clincer.
>
> The resulting DATE column is actually the format of the equation as well.
>
> I've attached a view of the results from the working SQL statement that does
> not perform the WHERE.
>
> "WHERE Format$(Date, '-mm-dd') < sDateTemp" does not work.
>
> Also, as stated in my previous post, I have sDateTemp formatted in the same
> format as that which is in the table.
>
> That's why I'm puzzled.
>
> Rick
>
>
>
> #>-Original Message-
> #>From: sqlite-users-boun...@sqlite.org
> #>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> #>Sent: Monday, August 03, 2009 1:38 PM
> #>To: sqlite-users@sqlite.org
> #>Subject: Re: [sqlite] Date Comparisons SQL
> #>
> #>Rick Ratchford wrote:
> #>> The Date is being stored as -mm-dd. Note the "Format$(Date,
> #>> '-mm-dd') as Date" that assures this.
> #>
> #>The "Date" that appears in the WHERE clause is the value of
> #>the Date column in the table, not the value of the expression
> #>with the "Date"
> #>alias. You can't actually use aliases in the WHERE clause.
> #>You are confusing yourself by using the same identifier both
> #>for the column name and for the alias.
> #>
> #>You could write
> #>
> #>WHERE Format$(Date, '-mm-dd') < sDateTemp
> #>
> #>Or else, express sDateTemp in the same format that you have
> #>dates stored in the table - the format you get when you just
> #>run "SELECT Date from mytable".
> #>
> #>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] Date Comparisons SQL

[Rick Ratchford]

That's the clincer.

The resulting DATE column is actually the format of the equation as well.

I've attached a view of the results from the working SQL statement that does
not perform the WHERE.

"WHERE Format$(Date, '-mm-dd') < sDateTemp" does not work.

Also, as stated in my previous post, I have sDateTemp formatted in the same
format as that which is in the table.

That's why I'm puzzled.

Rick
 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Monday, August 03, 2009 1:38 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>Rick Ratchford wrote:
#>> The Date is being stored as -mm-dd. Note the "Format$(Date,
#>> '-mm-dd') as Date" that assures this.
#>
#>The "Date" that appears in the WHERE clause is the value of 
#>the Date column in the table, not the value of the expression 
#>with the "Date" 
#>alias. You can't actually use aliases in the WHERE clause. 
#>You are confusing yourself by using the same identifier both 
#>for the column name and for the alias.
#>
#>You could write
#>
#>WHERE Format$(Date, '-mm-dd') < sDateTemp
#>
#>Or else, express sDateTemp in the same format that you have 
#>dates stored in the table - the format you get when you just 
#>run "SELECT Date from mytable".
#>
#>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] Week

[helemacd]

Hi,

anybody know how to return the start of the week and end of the week???

Thanks!!
-- 
View this message in context: 
http://www.nabble.com/Week-tp24795944p24795944.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Date Comparisons SQL

[Igor Tandetnik]

Rick Ratchford wrote:
> The Date is being stored as -mm-dd. Note the "Format$(Date,
> '-mm-dd') as Date" that assures this.

The "Date" that appears in the WHERE clause is the value of the Date 
column in the table, not the value of the expression with the "Date" 
alias. You can't actually use aliases in the WHERE clause. You are 
confusing yourself by using the same identifier both for the column name 
and for the alias.

You could write

WHERE Format$(Date, '-mm-dd') < sDateTemp

Or else, express sDateTemp in the same format that you have dates stored 
in the table - the format you get when you just run "SELECT Date from 
mytable".

Igor Tandetnik 



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

Re: [sqlite] Date Comparisons SQL

[Rick Ratchford]

That was a quick response!

The Date is being stored as -mm-dd. Note the "Format$(Date,
'-mm-dd') as Date" that assures this.

And I have it in this format for sDateTemp so that they would compare the
same.

But it does not work.

So what am I doing wrong?

Thanks.

Rick
 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
#>Sent: Monday, August 03, 2009 1:25 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>> How do you properly do a Date comparison in SELECT so that the only 
#>> rows returned are those that do not exceed the date found in my 
#>> sDateTemp variable?
#>
#>As a simple string comparison. You made it perfectly right 
#>except that your Date field should be stored in a format 
#>'-mm-dd' in database.
#>Without it comparison will not work.
#>
#>Pavel
#>
#>On Mon, Aug 3, 2009 at 2:19 PM, Rick 
#>Ratchford wrote:
#>> This works:
#>>
#>> SQLString = "SELECT Format$(Date, '-mm-dd') as Date, 
#>Year, Month, 
#>> Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as 
#>> IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as 
#>Delta1, Null 
#>> as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] 
#>GROUP BY 
#>> Year, Month, Day"
#>>
#>>
#>> This does not:
#>>
#>> SQLString = "SELECT Format$(Date, '-mm-dd') as Date, 
#>Year, Month, 
#>> Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as 
#>> IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as 
#>Delta1, Null 
#>> as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] 
#>WHERE Date 
#>> < " & sDateTemp & " GROUP BY Year, Month, Day"
#>>
#>> The difference is that I want the second statement to only retrieve 
#>> records (rows) that do not exceed the date listed in sDateTemp.
#>>
#>> I've converted the local date format to "-mm-dd" in sDateTemp 
#>> before using it in this SQL statement. But that didn't seem 
#>to solve 
#>> the problem. I'm still getting a single blank record.
#>>
#>> How do you properly do a Date comparison in SELECT so that the only 
#>> rows returned are those that do not exceed the date found in my 
#>> sDateTemp variable?
#>>
#>> Thanks.
#>> Rick
#>> ___
#>> 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] ANN: SQLite 3.6.16.C#

[Kosenko Max]

I need to apologize once again. Slow deletes explained as bug in SQLite tests
and flags of Perst compilation. Now they both head to head on basic ops with
2x on Perst selects (can be due to the ADO reader instantiations) 
http://www.nabble.com/file/p24795746/TestIndex.cs TestIndex.cs 

SQLITE TEST
  inserting 10 records: 4.4062500
  performing 20 index searches: 7.250
  iteration through 20 records: 1.1875000
  deleting 10 records: 3.9843750
PERST TEST
  inserting 10 records: 4.1562500
  performing 20 index searches: 2.5156250
  iteration through 20 records: 1.7343750
  deleting 10 records: 4.3437500

SQLITE TEST
  inserting 100 records: 49.7031250
  performing 200 index searches: 76.6718750
  iteration through 200 records: 11.9989795
  deleting 100 records: 46.7937535
PERST TEST
  inserting 100 records: 53.1406250
  performing 200 index searches: 29.9375000
  iteration through 200 records: 19.7187500
  deleting 100 records: 53.7206640

-
Best Regards.
Max Kosenko.
-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24795746.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Kosenko Max]

I need to apologize once again. Slow deletes explained as bug in SQLite tests
and flags of Perst compilation. Now they both head to head on basic ops with
2x on Perst selects (can be due to the ADO reader instantiations) 
http://www.nabble.com/file/p24795731/TestIndex.cs TestIndex.cs 

SQLITE TEST
  inserting 10 records: 4.4062500
  performing 20 index searches: 7.250
  iteration through 20 records: 1.1875000
  deleting 10 records: 3.9843750
PERST TEST
  inserting 10 records: 4.1562500
  performing 20 index searches: 2.5156250
  iteration through 20 records: 1.7343750
  deleting 10 records: 4.3437500

SQLITE TEST
  inserting 100 records: 49.7031250
  performing 200 index searches: 76.6718750
  iteration through 200 records: 11.9989795
  deleting 100 records: 46.7937535
PERST TEST
  inserting 100 records: 53.1406250
  performing 200 index searches: 29.9375000
  iteration through 200 records: 19.7187500
  deleting 100 records: 53.7206640

-
Best Regards.
Max Kosenko.
-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24795731.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Comparisons SQL

[Pavel Ivanov]

> How do you properly do a Date comparison in SELECT so that the only rows
> returned are those that do not exceed the date found in my sDateTemp
> variable?

As a simple string comparison. You made it perfectly right except that
your Date field should be stored in a format '-mm-dd' in database.
Without it comparison will not work.

Pavel

On Mon, Aug 3, 2009 at 2:19 PM, Rick Ratchford wrote:
> This works:
>
> SQLString = "SELECT Format$(Date, '-mm-dd') as Date, Year, Month,
> Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as
> IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null
> as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] GROUP BY
> Year, Month, Day"
>
>
> This does not:
>
> SQLString = "SELECT Format$(Date, '-mm-dd') as Date, Year, Month,
> Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as
> IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null
> as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] WHERE Date
> < " & sDateTemp & " GROUP BY Year, Month, Day"
>
> The difference is that I want the second statement to only retrieve
> records (rows) that do not exceed the date listed in sDateTemp.
>
> I've converted the local date format to "-mm-dd" in sDateTemp
> before using it in this SQL statement. But that didn't seem to solve
> the problem. I'm still getting a single blank record.
>
> How do you properly do a Date comparison in SELECT so that the only rows
> returned are those that do not exceed the date found in my sDateTemp
> variable?
>
> Thanks.
> Rick
> ___
> 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] Date Comparisons SQL

[Rick Ratchford]

This works:
 
SQLString = "SELECT Format$(Date, '-mm-dd') as Date, Year, Month,
Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as
IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null
as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] GROUP BY
Year, Month, Day"
 

This does not:
 
SQLString = "SELECT Format$(Date, '-mm-dd') as Date, Year, Month,
Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as
IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null
as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] WHERE Date
< " & sDateTemp & " GROUP BY Year, Month, Day"
 
The difference is that I want the second statement to only retrieve
records (rows) that do not exceed the date listed in sDateTemp.
 
I've converted the local date format to "-mm-dd" in sDateTemp
before using it in this SQL statement. But that didn't seem to solve
the problem. I'm still getting a single blank record.
 
How do you properly do a Date comparison in SELECT so that the only rows
returned are those that do not exceed the date found in my sDateTemp
variable?
 
Thanks.
Rick
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Richard Schmidt is out of the office.

[Richard . Schmidt]

I will be out of the office starting  08/03/2009 and will not return until
08/04/2009.


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

Re: [sqlite] Use of in memory db

[Igor Tandetnik]

Sharma, Gaurav wrote:
> Please clear my one more doubt. Is it true that either using the
> SQLITE_THREADSAFE=1 as compile time flag or using
> SQLITE_OPEN_FULLMUTEX with sqlite3_open_v2 are same thing. Both can
> be used interchangeably. Correct me if I am wrong.

Once you compile with SQLITE_THREADSAFE=0, all threading and mutex 
related code is removed at compile time, and SQLite can only be used in 
single-threaded mode.

If you compile with SQLITE_THREADSAFE=1 or 2, you can then switch 
between multi-threaded and serialized mode at run time, by specifying 
SQLITE_OPEN_NOMUTEX or SQLITE_OPEN_FULLMUTEX flags to sqlite3_open_v2. 
SQLITE_THREADSAFE determines the default setting when neither flag is 
specified. You can also change the default at run-time with 
sqlite3_config (but again, once you compile with SQLITE_THREADSAFE=0, 
there's no way to switch to any of the two thread-safe modes).

See http://www.sqlite.org/threadsafe.html

> Secondly, Is it possible by any mean that for shared connection
> amongst thread, the insert on one thread does not become part of the
> transaction on other thread.

No. A transaction is a property of a connection, not that of a thread. 
You'd have to use separate connections, perhaps in shared-cache mode: 
http://www.sqlite.org/sharedcache.html

> And one last thing just to confirm that it is not at all possible to
> open different in memory db connection from different threads?

Every time you open a connection to :memory:, a new in-memory db is 
created. So yes, it's possible to open multiple connections to in-memory 
databases (whether from multiple threads or otherwise) - but not to the 
same in-memory database.

Igor Tandetnik



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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Kosenko Max]

Sorry, test bug in SQLite select test.
http://www.nabble.com/file/p24789308/TestIndex.cs TestIndex.cs 

index searches:
  20: SQLITE 8.1635400 PERST 3.3406065
  200: SQLITE 1:10.6331745 PERST 54.9915975


-
Best Regards.
Max Kosenko.
-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24789308.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Use of in memory db

[Sharma, Gaurav]

Hi All,

Please clear my one more doubt. Is it true that either using the 
SQLITE_THREADSAFE=1 as compile time flag or using SQLITE_OPEN_FULLMUTEX with 
sqlite3_open_v2 are same thing. Both can be used interchangeably. Correct me if 
I am wrong.

Secondly, Is it possible by any mean that for shared connection amongst thread, 
the insert on one thread does not become part of the transaction on other 
thread.

And one last thing just to confirm that it is not at all possible to open 
different in memory db connection from different threads?

With Best Regards
Gaurav Sharma

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Thursday, July 30, 2009 5:10 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Use of in memory db

> What I assume is if I share an in memory db connection handle across threads 
> then it will not be sqlite's responsibility but the user's responsibility to 
> protect multiple insert/update statements. On the other hand if the 
> connection to physical db is shared amongst threads then sqlite takes care of 
> synchronization between multiple insert/update from multiple threads.

Your assumption is wrong. When you share the same connection among
several threads SQLite works with the same scenario no matter if you
connection to in-memory database or to physical file. And this
scenario depends on compilation options and flags given to
sqlite3_open. With default compilation options if you gave flag
SQLITE_OPEN_NOMUTEX then it's your responsibility to protect with
mutexes, if you gave flag SQLITE_OPEN_FULLMUTEX then SQLite makes this
work for you. What you meant to say maybe is that if you have
different connections in each thread to the same physical file then
SQLite can make more fine-grained synchronization between threads.
This scenario is not applicable to in-memory database because there's
no way you can open several connections to the same in-memory
database.

> Basically a broader question is that, Is there any way to avoid sharing of 
> connection handle and even then be able to work on the same memory db from 
> multiple threads.

As I've already said: no.

> Or any one can suggest how best the mutex can be used if I share the 
> connection and then perform transactions (insert, update).

If you have resource shared between threads there's only one way to
use it - protect each access to it with mutex. There's no other ways
to do it and no tricks can be done in here. The only trick that you
can do is to avoid sharing of the resource and develop some memory
structure that will allow you concurrent access from different threads
with the pattern your application needs. But SQLite is not your fellow
here - it doesn't offer this kind of memory structure.

Pavel

On Thu, Jul 30, 2009 at 7:16 AM, Sharma,
Gaurav wrote:
> Hi All,
>
> Is there any way through which without using the mutex lock mechanism 
> multiple threads can perform INSERT in bulk on same memory db. What I assume 
> is if I share an in memory db connection handle across threads then it will 
> not be sqlite's responsibility but the user's responsibility to protect 
> multiple insert/update statements. On the other hand if the connection to 
> physical db is shared amongst threads then sqlite takes care of 
> synchronization between multiple insert/update from multiple threads.
>
> Basically a broader question is that, Is there any way to avoid sharing of 
> connection handle and even then be able to work on the same memory db from 
> multiple threads. I really wish to avoid the burden of using mutex for every 
> (insert or update) in multi threaded scenario. That actually supposed to put 
> lot of overhead on my application.
>
> Or any one can suggest how best the mutex can be used if I share the 
> connection and then perform transactions (insert, update).
>
> With Best Regards
> Gaurav Sharma
>
>
> The information contained in this electronic mail transmission
> may be privileged and confidential, and therefore, protected
> from disclosure. If you have received this communication in
> error, please notify us immediately by replying to this
> message and deleting it from your computer without copying
> or disclosing 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
The information contained in this electronic mail transmission 
may be privileged and confidential, and therefore, protected 
from disclosure. If you have received this communication in 
error, please notify us immediately by replying to this 
message and deleting it from your computer without copying 
or disclosing it.


___
sqlite-use

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Kosenko Max]

Dan Kennedy-4 wrote:
> Earlier I just quoted the conclusions of the McObject report. Maybe I
> misunderstood. But now that I have read the benchmark code, I'm curious.
> Why is the SQL not being recompiled for each query? Is there some kind  of
> compiled query cache hiding behind the  [db.query()] call in Android?
> 
> Why would it not help to use a read-only transaction? Is SQLite using
> exclusive-locking mode? In the tests I've been working on today I've found
> that read-only transactions speed up SQLite considerably for this kind of
> workload, even when using exclusive-locking mode.

First of all - I was never referring to the published tests, so my
assumptions were that in Android test everything went ok. I've asked author
what's up. And it's clear that in Android SQLite API there are no options
now (or at least not found on test moment) to make SQLite faster. So that
can be resolved, but right now test results on Android true.

I was doing some personal tests long time ago and after this thread decided
to redo same used TestIndex for SQLite and Perst together.

SQLITE TEST
inserting 10 records: 00:00:05.2486875
performing 20 index searches: 00:00:05.6910420
iteration through 20 records: 00:00:00.6942915
deleting 10 records: 00:00:01.645

PERST TEST
inserting 10 records: 00:00:05.6158515
performing 20 index searches: 00:00:03.4675515
iteration through 20 records: 00:00:02.0994750
deleting 10 records: 00:00:05.9859450

SQLITE TEST
inserting 100 records: 00:00:44.6846400
performing 200 index searches: 00:00:59.2130070
iteration through 200 records: 00:00:07.8237180
deleting 100 records: 00:00:16.2304065

PERST TEST
inserting 100 records: 00:01:05.9596455
performing 200 index searches: 00:00:53.0913285
iteration through 200 records: 00:00:29.4795585
deleting 100 records: 00:01:51.0710160

Here is the test file:  http://www.nabble.com/file/p2477/TestIndex.cs
TestIndex.cs 

Everything optimized as much as possible (but staying ACID and doing same
operations) for SQLite. 
Both DB had same page cache size. Perst wasn't tweaked.

Difference is that Perst uses objects and for SQLite that was skipped so
iteration performance differs. Deletion is really slower because Perst uses
separate indices and requires more operations. I don't know whether this can
be tweaked, if test author will have a time - he will change a test. 

Other than deletion performance, everything else looks comparable to me. And
I don't know what it might be with Perst tweak. So again - that is just a
show that managed DB isn't something that must be much slower (but really
Perst/SQLite so different that we mostly measured different architectures).

-
Best Regards.
Max Kosenko.
-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p2477.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Kosenko Max]

Dan Kennedy-4 wrote:
> Are you by any chance the author of the report I'm reading?
I'm not an author of test or McObject staff/representative at all. But I can
give a link to this forum to author (still insisting that this is offtopic
here) to answer himself.

-
Best Regards.
Max Kosenko.
-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24786874.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Dan Kennedy]

On Aug 3, 2009, at 2:06 PM, Kosenko Max wrote:

>
>
> Dan Kennedy-4 wrote:
>> McObject CEO Steve Graves points out that because of limits of the  
>> API
>> they were using, SQLite performs each INSERT and DELETE in the test  
>> in a
>> separate transaction. So the reported times for these tests may be  
>> more of
>> a measure of the speed of the media than SQLite itself.
>>
>> The body of the report shows that SQLite scans (iterates through)  
>> rows
>> more quickly than Perst does (about 1.5 times faster). But Perst  
>> can look
>> up individual objects more quickly (about 6 times faster). The  
>> comments
>> in the report indicate that SQLite was not using pre-compiled SQL or
>> read-only transactions for the object-lookup test, so that gap could
>> probably be closed some by better use of the SQLite API. Perhaps more
>> importantly though,  Perst is an Object DB, so it is caching the  
>> actual
>> objects in-memory, whereas SQLite is only caching database pages. I
>> haven't much experience with them, but I think Object DBs are usually
>> faster for this kind of task. YMMV.
>
> While this is offtopic, I just want to comment that there could be a
> problems with any tests around, so you can propose changes to test  
> code if
> you feel it could be done better.

I have no issue with the content of the report or formulation of the  
tests.
I think the folks at McObject have done a good job of testing the two  
systems
and shown real integrity by explaining the results. Most commercial  
operations
are much less forthcoming.

> Read-only transaction and skipping single preparation wouldn't speed- 
> up
> SQLite in that test.

Earlier I just quoted the conclusions of the McObject report. Maybe I
misunderstood. But now that I have read the benchmark code, I'm curious.

Why is the SQL not being recompiled for each query? Is there some kind  
of
compiled query cache hiding behind the  [db.query()] call in Android?

Why would it not help to use a read-only transaction? Is SQLite using
exclusive-locking mode? In the tests I've been working on today I've
found that read-only transactions speed up SQLite considerably for this
kind of workload, even when using exclusive-locking mode.

Are you by any chance the author of the report I'm reading?

Dan.


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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Kosenko Max]

Dan Kennedy-4 wrote:
> McObject CEO Steve Graves points out that because of limits of the API 
> they were using, SQLite performs each INSERT and DELETE in the test in a
> separate transaction. So the reported times for these tests may be more of
> a measure of the speed of the media than SQLite itself.
> 
> The body of the report shows that SQLite scans (iterates through) rows
> more quickly than Perst does (about 1.5 times faster). But Perst can look
> up individual objects more quickly (about 6 times faster). The comments 
> in the report indicate that SQLite was not using pre-compiled SQL or
> read-only transactions for the object-lookup test, so that gap could
> probably be closed some by better use of the SQLite API. Perhaps more
> importantly though,  Perst is an Object DB, so it is caching the actual
> objects in-memory, whereas SQLite is only caching database pages. I
> haven't much experience with them, but I think Object DBs are usually
> faster for this kind of task. YMMV.

While this is offtopic, I just want to comment that there could be a
problems with any tests around, so you can propose changes to test code if
you feel it could be done better. Test isn't using recreation of commands
with each selects. Same it's not using any advantages given by Object DB
nature (which is only complimentary part of Perst). In reality it was even
strange to see such difference on select performance (even for author).
Read-only transaction and skipping single preparation wouldn't speed-up
SQLite in that test. But it's very possible it can be tweaked more or at
least optimized inside.

I'm not insisting that Perst DB is better (it doesn't have such open license
- it's GPL/commercial license and it's not full-size SQL capable at least),
that was just a sample to show that it's a mistake to think that pure
managed DBs always slower.

-
Best Regards.
Max Kosenko.
-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24786305.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