Re: [sqlite] how to use group_concat uniquely

[Noah Hart]

Igor Tandetnik wrote:
> 
> On 1/19/2011 12:51 PM, Noah Hart wrote:
>>> select E, replace(group_concat(distinct T), ',', ';'),
>>> replace(group_concat(distinct P), ',', ';') from MyTable;
>>
>>Ahhh
>> the DISTINCT keyword was what I'm missing. However, this may be a bug,
>> when I use group_concat(DISTINCT T, ';') I get the error message
>> DISTINCT aggregates must have exactly one argument
> 
> That's why I had to muck around with replace(). The syntax only allows 
> DISTINCT keyword in aggregate functions taking exactly one parameter. 
> group_concat defaults to comma as a separator when called with one 
> parameter.
> -- 
> Igor Tandetnik
> 

Well, I went and read the page on the Aggregate Functions, and it is very
clear in the documentation.

Thanks again,

Noah

-- 
View this message in context: 
http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30716460.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] How can we use POWER(2,32) like function in SQLite

[Sunil Bhardwaj]

Hi

Please suggest, How can we use POWER(2,32) like function in SQLite?

Thanks
Sunil Bhardwaj
Ext. 1125 (0120-2567001)
9818868910
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL on a separate filesystem?

[Drake Wilson]

Quoth Simon Slavin , on 2011-01-20 00:55:18 +:
> Agreed.  I was imagining that one file was on an external hard disk
> and the other was on an internal disk.  If the power to the external
> hard disk goes off, one file will get updated but the other won't.
> I don't know enough about the inner workings of SQLite to know if it
> can cope.

I suspect that for the files themselves it's okay so long as fsync is
actually a hard sync (and not just a reordering barrier), which it's
supposed to be anyway.  I don't believe SQLite can be making any
assumptions regarding the reordering of unsynchronized writes between
files on a single device; if fsync is a hard sync then any ordering
semantics are already propagated through the application to the other
device in turn.

However, if either file ever gets created or deleted during normal
operation, then you have the problem of needing to sync the directory,
and that probably won't work because SQLite won't hit the correct
directory for whichever file is actually somewhere else.  Also, if one
file is a symlink and it gets deleted and recreated, then it will be
recreated on the wrong filesystem.  This suggests that actually the
main database should be the symlink if one goes that route, and it
should be precreated as an empty file (and synchronized to storage)
before any operations begin.

Either way, it's an unsupported configuration and should only be used
with extreme caution, especially if the SQLite component may later be
replaced with the expectation of backwards compatibility.

> Simon.

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

Re: [sqlite] WAL on a separate filesystem?

[Simon Slavin]

On 20 Jan 2011, at 12:49am, Scott Hess wrote:

> The biggest concern I would have would be whether there are subtle
> atomicity guarantees which are served by being on the same filesystem
> which cannot be served by being split across multiple filesystems.

Agreed.  I was imagining that one file was on an external hard disk and the 
other was on an internal disk.  If the power to the external hard disk goes 
off, one file will get updated but the other won't.  I don't know enough about 
the inner workings of SQLite to know if it can cope.

Simon.

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

Re: [sqlite] WAL on a separate filesystem?

[Scott Hess]

On Wed, Jan 19, 2011 at 12:15 PM, Dustin Sallings  wrote:
> This isn't a question so much about value judgment (I've already
> argued that some, though mentioning maintenance tools is helpful
> there, too).  It comes down to whether reliability of SQLite itself
> would be reduced if a WAL existed on a different partition -- whether
> there are any assumptions WAL makes that would be invalid across
> a filesystem boundary.

You either would have to hack the filesystem namespace to look like
you want it to look using mount tricks, or modify SQLite's VFS (or
write your own) to accomplish what you want.  Since you cannot trust
the contents of the database itself, you would need to provide some
means of specifying the alternate location in code.

The biggest concern I would have would be whether there are subtle
atomicity guarantees which are served by being on the same filesystem
which cannot be served by being split across multiple filesystems.

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

Re: [sqlite] WAL on a separate filesystem?

[Simon Slavin]

On 19 Jan 2011, at 8:15pm, Dustin Sallings wrote:

> On Jan 19, 2011, at 11:01, Simon Slavin wrote:
> 
>> If you do something special to keep your journal file in a different place, 
>> these other sqlite3 applications won't find it.  So they'll just find a 
>> corrupt database file, and are less likely to be able to figure out how to 
>> restore to a COMMIT point or a SAVEPOINT.
> 
> 
>   I understand this concern and think it's a valid point.  I can assume 
> for the purpose of this usage that only tools I provide will be used to 
> access the DB (I ship a sqlite3 binary since I'm using WAL and I've got users 
> on CentOS which ships sqlite 1.2 for all I know).

This solution depends on what facilities your operating system has for creating 
an alias, soft link, shortcut, or whatever it calls them.  If your operating 
system does these correctly, the sqlite3 open function should correctly 
understand it and use it.  If CentOS is just another flavour of Linux you will 
probably want to use soft links.  Experiment with soft links (or whatever is 
appropriate) first, to make sure you understand what they are and how they work.



Given two different filespaces ...

one you want the database file on (call it D)
one you want the journal file on (call it J)

Step 1
--
Set up your application to use J for both files.
Start your application or whatever other tool you have to create the database 
file and put at least a little data in it.
Quit the application.

Step 2
--
Copy your database file from J to D.
Delete (or rename) the original copy of the file in J.
Create an alias/link/shortcut/whatever in J, linked to the file that is now in 
D.

Step 3
--
Restart your application.
Make sure it is correctly following the link and finding the data you have 
moved to D.

Because your application thinks that the database file is on J, it should 
create the journal file on J.


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

Re: [sqlite] Export sqlite table in textpad/notepad

[Simon Slavin]

On 19 Jan 2011, at 7:51pm, Alok Singh wrote:

> how to Export sqlite table in textpad/notepad using VB.NET application ?
> Any direct command which can help me.

Ask in a VB forum.  Your problem is more to do with VB than with SQLite.

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

Re: [sqlite] WAL on a separate filesystem?

[Dustin Sallings]

On Jan 19, 2011, at 11:01, Simon Slavin wrote:

> If you do something special to keep your journal file in a different place, 
> these other sqlite3 applications won't find it.  So they'll just find a 
> corrupt database file, and are less likely to be able to figure out how to 
> restore to a COMMIT point or a SAVEPOINT.


I understand this concern and think it's a valid point.  I can assume 
for the purpose of this usage that only tools I provide will be used to access 
the DB (I ship a sqlite3 binary since I'm using WAL and I've got users on 
CentOS which ships sqlite 1.2 for all I know).

I'm a bit of a proxy of this question.  I wrote software that uses 
SQLite under some pretty high volumes and I have a user wanting to split stuff 
up across multiple filesystems.  I already have the ability to do data 
partitioning in the application, but the user is wanting to separate the WAL 
out as well.

This isn't a question so much about value judgment (I've already argued 
that some, though mentioning maintenance tools is helpful there, too).  It 
comes down to whether reliability of SQLite itself would be reduced if a WAL 
existed on a different partition -- whether there are any assumptions WAL makes 
that would be invalid across a filesystem boundary.

-- 
dustin sallings

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

[sqlite] Export sqlite table in textpad/notepad

[Alok Singh]

Hi Sqlite Team,

my project at its last stage.

*can you help me on :*

how to Export sqlite table in textpad/notepad using VB.NET application ?
Any direct command which can help me.

or

shall i use select command and write into textpad/notepad line by line ?

which one will be best way, or anyother way if anyone having please advice
me.

Kindly help me, its urgent for me, after 8 hour i am having demo
presentation on my software.

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

Re: [sqlite] WAL on a separate filesystem?

[Simon Slavin]

On 19 Jan 2011, at 6:43pm, Dustin Sallings wrote:

> On Jan 19, 2011, at 2:35, Richard Hipp wrote:
> 
>> No.  The WAL has to be in the same directory as the original database.
>> Otherwise, the process that tries to recover from a crash or power failure
>> won't know where to find the WAL file.
> 
>   I understand how it's opening it.  This is more about how the file is 
> used.
> 
>   If I could convince SQLite to open the WAL in a location other than in 
> the same location as the main db, would this cause reliability problems?

The problem is not with the session which creates the WAL file, its with the 
one that has to clear up after any crash.  With things as they stand, every 
application which uses sqlite3 looks for a journal file in the same place.  So 
if your application (or the computer) crashes and someone uses a different 
application next to open the database file, it will definitely be able to find 
the journal file and restore the database to an uncorrupted state, right after 
a COMMIT, just where you'd want it.

This happens a lot: after a crash instead of restarting the normal application, 
someone uses the sqlite3 command-line utility or some sort of database viewer 
to see what state the data is in.  With the journal in a predictable location 
SQLite will handle this correctly no matter what application they use.

If you do something special to keep your journal file in a different place, 
these other sqlite3 applications won't find it.  So they'll just find a corrupt 
database file, and are less likely to be able to figure out how to restore to a 
COMMIT point or a SAVEPOINT.

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

Re: [sqlite] WAL on a separate filesystem?

[Dustin Sallings]

On Jan 19, 2011, at 2:35, Richard Hipp wrote:

> No.  The WAL has to be in the same directory as the original database.
> Otherwise, the process that tries to recover from a crash or power failure
> won't know where to find the WAL file.


I understand how it's opening it.  This is more about how the file is 
used.

If I could convince SQLite to open the WAL in a location other than in 
the same location as the main db, would this cause reliability problems?

-- 
dustin sallings

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

Re: [sqlite] Effect of commit transaction while in process of sqlite3_step'ing?

[James Berry]

On Jan 19, 2011, at 10:03 AM, Richard Hipp wrote:

> On Wed, Jan 19, 2011 at 12:35 PM, James Berry  wrote:
> 
>> I'm trying to understand whether there's any problem with committing a
>> transaction while in the process of stepping over results.
> 
> The ability to do this was added to SQLite in version 3.6.5 in November of
> 2008.

Thanks Richard, for the clarification.

James



>> BEGIN DEFERRED TRANSACTION
>> 
>> SELECT * FROM a
>> sqlite3_step over select results
>> {
>>   COMMIT
>>   BEGIN DEFERRED TRANSACTION
>> 
>>   INSERT INTO b
>> }
>> 
>> COMMIT

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

Re: [sqlite] how to use group_concat uniquely

[Igor Tandetnik]

On 1/19/2011 12:51 PM, Noah Hart wrote:
>> select E, replace(group_concat(distinct T), ',', ';'),
>> replace(group_concat(distinct P), ',', ';') from MyTable;
>
>Ahhh
> the DISTINCT keyword was what I'm missing. However, this may be a bug,
> when I use group_concat(DISTINCT T, ';') I get the error message
> DISTINCT aggregates must have exactly one argument

That's why I had to muck around with replace(). The syntax only allows 
DISTINCT keyword in aggregate functions taking exactly one parameter. 
group_concat defaults to comma as a separator when called with one 
parameter.
-- 
Igor Tandetnik

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

Re: [sqlite] Effect of commit transaction while in process of sqlite3_step'ing?

[Simon Slavin]

On 19 Jan 2011, at 5:35pm, James Berry wrote:

> I'm trying to understand whether there's any problem with committing a 
> transaction while in the process of stepping over results.

Does the transaction you're committing have anything at all to do with the 
SELECT you're stepping through ?  If you do the same SELECT after the 
transaction might the results have changed ?  If so, it's a bad idea to do the 
two things at the same time.

Read the results of the SELECT into one or more variables.  Once you've 
finalized the SELECT, then make your changes.

Better still, if possible phrase your changes as an UPDATE command with a WHERE 
clause.  That way you don't need any SELECT statement at all.

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

Re: [sqlite] Effect of commit transaction while in process of sqlite3_step'ing?

[Richard Hipp]

On Wed, Jan 19, 2011 at 12:35 PM, James Berry  wrote:

> I'm trying to understand whether there's any problem with committing a
> transaction while in the process of stepping over results.
>
> The following loop, in some kind of weird hybrid pseudo-code, tries to
> illustrate what I'm doing: while stepping over results from a select
> statement, doing inserts into another table. During that process, I want to
> break up the current transaction.
>
> Is there any effect on the outer select/step by committing and restarting
> the transaction while the outer statement is still being used? (In my actual
> use-case, the inner commit/begin is not performed on every iteration).
>
> The codes seems to be working for me, but I want to make sure it's not
> working in the realm of undefined operation.
>

The ability to do this was added to SQLite in version 3.6.5 in November of
2008.


>
> James
>
>
> BEGIN DEFERRED TRANSACTION
>
> SELECT * FROM a
> sqlite3_step over select results
> {
>COMMIT
>BEGIN DEFERRED TRANSACTION
>
>INSERT INTO b
> }
>
> COMMIT
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Effect of commit transaction while in process of sqlite3_step'ing?

[Igor Tandetnik]

On 1/19/2011 12:49 PM, James Berry wrote:
>
> On Jan 19, 2011, at 9:43 AM, Igor Tandetnik wrote:
>
>> On 1/19/2011 12:35 PM, James Berry wrote:
>>> I'm trying to understand whether there's any problem with committing a 
>>> transaction while in the process of stepping over results.
>>
>> I believe COMMIT would fail while there is an outstanding statement on
>> the connection.
>
> If that's the case, then is the following pseudo code any better?

It should make no difference. You shouldn't be able to commit while 
there's a statement that wasn't reset or finalized.
-- 
Igor Tandetnik

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

Re: [sqlite] how to use group_concat uniquely

[Noah Hart]

On 1/19/2011 12:19 PM, Noah Hart wrote:
> I am having difficulty trying to return data in a very compact form.  Here
> is
> a simple example that will explain the problem:
>
> I have a table with column E, T and P containing data such as
> A, 1, R
> A, 1, S
> A, 2, R
> A, 2, S
>
> Trying the query
> select E, group_concat(T,';'), group_concat(P,';') give me the expected
> results
> A 1;1;2;2 R;S;R;S
>
> However, I would like to create a query will return the results
>
> A 1;2 R;S

select E, replace(group_concat(distinct T), ',', ';'), 
replace(group_concat(distinct P), ',', ';') from MyTable;

Wouldn't work if values in T or P contain commas.

Here's a more verbose query that doesn't have this limitation (but is 
likely much slower):

select E,
   (select group_concat(T, ';') from (select distinct T from MyTable 
where E=AllE.E)),
   (select group_concat(P, ';') from (select distinct P from MyTable 
where E=AllE.E))
from (select distinct E from MyTable) AllE;

-- 
Igor Tandetnik

___
Ahhh the DISTINCT keyword was what I'm missing.

However, this may be a bug, when I use group_concat(DISTINCT T, ';') I get
the error message
 DISTINCT aggregates must have exactly one argument

Noah
-- 
View this message in context: 
http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30712310.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] Effect of commit transaction while in process of sqlite3_step'ing?

[James Berry]

On Jan 19, 2011, at 9:43 AM, Igor Tandetnik wrote:

> On 1/19/2011 12:35 PM, James Berry wrote:
>> I'm trying to understand whether there's any problem with committing a 
>> transaction while in the process of stepping over results.
> 
> I believe COMMIT would fail while there is an outstanding statement on 
> the connection.

If that's the case, then is the following pseudo code any better?

SELECT * FROM a
count == 0
sqlite3_step over select results
{
if (count == 0)
BEGIN DEFERRED TRANSACTION
else if (count % transactionSize == 0) {
COMMIT
BEGIN DEFERRED TRANSACTION
}
++count

INSERT INTO b
}

COMMIT


> 
>> The codes seems to be working for me
> 
> Check return values from various SQLite calls.
> -- 
> 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] Effect of commit transaction while in process of sqlite3_step'ing?

[Igor Tandetnik]

On 1/19/2011 12:35 PM, James Berry wrote:
> I'm trying to understand whether there's any problem with committing a 
> transaction while in the process of stepping over results.

I believe COMMIT would fail while there is an outstanding statement on 
the connection.

> The codes seems to be working for me

Check return values from various SQLite calls.
-- 
Igor Tandetnik

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

Re: [sqlite] how to use group_concat uniquely

[Igor Tandetnik]

On 1/19/2011 12:19 PM, Noah Hart wrote:
> I am having difficulty trying to return data in a very compact form.  Here is
> a simple example that will explain the problem:
>
> I have a table with column E, T and P containing data such as
> A, 1, R
> A, 1, S
> A, 2, R
> A, 2, S
>
> Trying the query
> select E, group_concat(T,';'), group_concat(P,';') give me the expected
> results
> A 1;1;2;2 R;S;R;S
>
> However, I would like to create a query will return the results
>
> A 1;2 R;S

select E, replace(group_concat(distinct T), ',', ';'), 
replace(group_concat(distinct P), ',', ';') from MyTable;

Wouldn't work if values in T or P contain commas.

Here's a more verbose query that doesn't have this limitation (but is 
likely much slower):

select E,
   (select group_concat(T, ';') from (select distinct T from MyTable 
where E=AllE.E)),
   (select group_concat(P, ';') from (select distinct P from MyTable 
where E=AllE.E))
from (select distinct E from MyTable) AllE;

-- 
Igor Tandetnik

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

[sqlite] Effect of commit transaction while in process of sqlite3_step'ing?

[James Berry]

I'm trying to understand whether there's any problem with committing a 
transaction while in the process of stepping over results.

The following loop, in some kind of weird hybrid pseudo-code, tries to 
illustrate what I'm doing: while stepping over results from a select statement, 
doing inserts into another table. During that process, I want to break up the 
current transaction.

Is there any effect on the outer select/step by committing and restarting the 
transaction while the outer statement is still being used? (In my actual 
use-case, the inner commit/begin is not performed on every iteration).

The codes seems to be working for me, but I want to make sure it's not working 
in the realm of undefined operation.

James


BEGIN DEFERRED TRANSACTION

SELECT * FROM a
sqlite3_step over select results
{
COMMIT
BEGIN DEFERRED TRANSACTION

INSERT INTO b
}

COMMIT


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

Re: [sqlite] how to use group_concat uniquely

[Nicolas Williams]

On Wed, Jan 19, 2011 at 09:19:54AM -0800, Noah Hart wrote:
> Any ideas?

You have two columns to sub-group by independently, as it were.  You
need correlated sub-queries to get that done:

sqlite> SELECT f1.e, (SELECT group_concat(f2.t, ';')
   ...> FROM foo f2 WHERE f1.e = f2.e GROUP BY f2.p), (SELECT 
group_concat(f2.p, ';')
   ...> FROM foo f2 WHERE f1.e = f2.e GROUP BY f2.t) FROM foo f1 GROUP BY f1.e;
A|1;2|R;S
B|1;2|R;S
sqlite> 

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

[sqlite] how to use group_concat uniquely

[Noah Hart]

I am having difficulty trying to return data in a very compact form.  Here is
a simple example that will explain the problem:

I have a table with column E, T and P containing data such as
A, 1, R
A, 1, S
A, 2, R
A, 2, S

Trying the query
select E, group_concat(T,';'), group_concat(P,';') give me the expected
results
A 1;1;2;2 R;S;R;S

However, I would like to create a query will return the results

A 1;2 R;S

Any ideas?

Noah Hart
-- 
View this message in context: 
http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30712025.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] Patch: FTS3 Snawball Stemmer support

[Alexey Pechnikov]

Hello!

May be this will be useful for somebody too. The code is not well tested yet
but demonstrate the solution.

http://sqlite.mobigroup.ru/wiki?name=FTS3+Snowball+Stemmer

-- 
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] WAL on a separate filesystem?

[Philip Graham Willoughby]

On 19 Jan 2011, at 11:53, Russell Leighton wrote:

> Perhaps that could be the default and a pragma could be used to  
> override this default and specify the directory holding the WAL.
> 
> This could be useful in cases  that users want to put the WAL  
> someplace else (like an SSD).

I think you're getting way beyond the 'Lite' world with that one.

On modern Linux systems you can use

mount --bind /path/to/existing/file /alternate/path/to/that/file

Which makes any access of /alternate/path/to/that/file an access of 
/path/to/existing/file. Both must exist; I recommend using touch to create the 
file at the alternate path, as it's mostrously confusing if it contains any 
data when the mount has not been run.

You could use this facility to make the SQLite database appear to be on your 
SSD. For safety's sake you should also make the WAL file appear to be colocated 
with the database, so that the WAL file can be found by an application which 
accesses the database with either path. This may or may not be safe depending 
on whether SQLite uses the path to the database to perform any locking.

To be clear: I won't be doing this myself…

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

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

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] Query or table

[Ian Hardingham]

Many thanks for the advice Phil, I'll follow it.

Ian

On 19/01/2011 13:06, Philip Graham Willoughby wrote:
> Ian,
>
> On 18 Jan 2011, at 16:40, Ian Hardingham wrote:
>
>> In general, my server is too slow.  It has to run many operations a
>> second, and many DB operations, so the exact definition of "too slow" is
>> a little arbitrary.
> Nothing in that paragraph implies that the DB speed is a problem, or that 
> this specific query is relevant.
>
> For your own sake, measure the performance precisely and accurately so that 
> you know how much time is spent in each area of your application. If all you 
> know is that the overall performance is slow, you don't know enough. You also 
> need to give yourself a target speed that you regard as fast enough, not 
> least so you know when to stop working on this and do something else.
>
> Once you know that it is the database that's using most of the time and you 
> have identified which queries are using most of that time you can use EXPLAIN 
> QUERY PLAN to ask SQLite how it will execute your worst queries; you can use 
> that information to make a more informed decision about how best to optimise. 
> If any table in the query lacks an appropriate index, it would benefit you to 
> add one for instance. It is generally cheaper to run a CREATE INDEX command 
> than to redesign your tables/queries/application logic. And make sure you 
> have issued ANALYZE at least once since your database took the size/shape it 
> has now.
>
> Chapter 2 of O'Reilly's High Performance MySQL (Finding Bottlenecks: 
> Benchmarking and Profiling) is a good introduction to this field.
>
>> I'm intruiged that you feel a pre-computed table is not a good idea.  Is
>> there an expectation that that would not be especially faster than my
>> current method?
> It would be faster at query-time than your current method. It would be much 
> slower at insert/update time, and more likely to wind up out-of-step with the 
> original table and leave inaccurate results. It would consume vast amounts of 
> extra space making it less likely that your database will fit in caches 
> (because you haven't normalised your schema at all, see below).
>
> Since you have not shown us your schema there is a limited amount anyone on 
> this list can comment on: we do not know what indexes you already have, and 
> you have not shown us the output of EXPLAIN QUERY PLAN or EXPLAIN for either 
> of the queries which concern you.
>
>  From what we can see in the query you posted it seems you have not 
> normalised your database at all. If you stored a table mapping INTEGER 
> PRIMARY KEY playerID numbers to player names with a UNIQUE INDEX over the 
> player names and used the IDs instead of the names in multiturnTable your 
> query would go much faster, even if it required a full-table scan or two. 
> This is because the engine would need to read and compare fewer bytes of data 
> per row to determine if the row was a match. You would get more rows per page 
> and therefore less I/O would be required. The entire database would also be 
> smaller and therefore more likely to fit in the OS and hardware caches in the 
> machine you're using.
>
> Best Regards,
>
> Phil Willoughby

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

Re: [sqlite] Query or table

[Philip Graham Willoughby]

Ian,

On 18 Jan 2011, at 16:40, Ian Hardingham wrote:

> In general, my server is too slow.  It has to run many operations a 
> second, and many DB operations, so the exact definition of "too slow" is 
> a little arbitrary.

Nothing in that paragraph implies that the DB speed is a problem, or that this 
specific query is relevant.

For your own sake, measure the performance precisely and accurately so that you 
know how much time is spent in each area of your application. If all you know 
is that the overall performance is slow, you don't know enough. You also need 
to give yourself a target speed that you regard as fast enough, not least so 
you know when to stop working on this and do something else.

Once you know that it is the database that's using most of the time and you 
have identified which queries are using most of that time you can use EXPLAIN 
QUERY PLAN to ask SQLite how it will execute your worst queries; you can use 
that information to make a more informed decision about how best to optimise. 
If any table in the query lacks an appropriate index, it would benefit you to 
add one for instance. It is generally cheaper to run a CREATE INDEX command 
than to redesign your tables/queries/application logic. And make sure you have 
issued ANALYZE at least once since your database took the size/shape it has now.

Chapter 2 of O'Reilly's High Performance MySQL (Finding Bottlenecks: 
Benchmarking and Profiling) is a good introduction to this field.

> I'm intruiged that you feel a pre-computed table is not a good idea.  Is 
> there an expectation that that would not be especially faster than my 
> current method?

It would be faster at query-time than your current method. It would be much 
slower at insert/update time, and more likely to wind up out-of-step with the 
original table and leave inaccurate results. It would consume vast amounts of 
extra space making it less likely that your database will fit in caches 
(because you haven't normalised your schema at all, see below).

Since you have not shown us your schema there is a limited amount anyone on 
this list can comment on: we do not know what indexes you already have, and you 
have not shown us the output of EXPLAIN QUERY PLAN or EXPLAIN for either of the 
queries which concern you.

>From what we can see in the query you posted it seems you have not normalised 
>your database at all. If you stored a table mapping INTEGER PRIMARY KEY 
>playerID numbers to player names with a UNIQUE INDEX over the player names and 
>used the IDs instead of the names in multiturnTable your query would go much 
>faster, even if it required a full-table scan or two. This is because the 
>engine would need to read and compare fewer bytes of data per row to determine 
>if the row was a match. You would get more rows per page and therefore less 
>I/O would be required. The entire database would also be smaller and therefore 
>more likely to fit in the OS and hardware caches in the machine you're using.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

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

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] insert into DB fail on NAND flash

[Simon Slavin]

On 19 Jan 2011, at 9:10am, Gigin Jose wrote:

> I am working on ARM9 based s3c2440 embedded platform on linux OS. The 
> database I use is SQLITE over QT application.  The "insert into" query is 
> failing once I try to insert values into a database. The database resides on 
> a NAND flash with yaffs2 formated filesystem.

I don't have time to explore all the peculiarities of a new file system, so 
you're going to have to narrow it down a little.  Please try exactly the same 
procedure, including deleting the data file, on a conventional hard disk using 
a conventional file system, and tell us if it fails there.

>  The following is the query which I execute:
> QString stat = QString("insert into user20 values('%0', '%1','%2' 
> )").arg(id).arg(name).arg(address);
> k = query.exec(stat);
> if(k == false)qDebug("K is false for record ,%s 
> \n",id.toAscii().data());
> After continuous read/write the query is failing.

Your problem could be anything up to running out of space on the drive.

SQLite returns a result status, not just true/false.  Please tell us the result 
code:

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

If possible please also obtain the text form of the error message:

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

since this often gives even more precise information about the problem.

> Once I restart my target (s3c2440), the query is again executed fine. Before 
> the query is executed, I delete the database file, if it exist, so that 
> ideally the record insertion can happen.

You delete the file while you have a handle to it open in your target ?  That 
will upset anything trying to access the file.  Delete the file only when your 
application does not have a connection to it.

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

Re: [sqlite] Date operations when date is a TEXT

[Simon Davies]

On 19 January 2011 10:06, Ian Hardingham  wrote:
> Hey guys.
>
> Probably unwisely, I store dates in the following format:
>
> "year month day hour minute"
>
> For example:
>
> "11 1 4 16 22"
>
> I wish to find all rows in a table which are more than 8 days old.  Is
> there a way of doing this in SQLite or should I just do it in my own code?

You need to construct ISO8601 style date string fro SQLite's date functions...

SQLite version 3.6.11
Enter ".help" for instructions
sqlite>
sqlite>
sqlite> create table tst( id integer primary key, time text );
sqlite>
sqlite> create view tstView1 as select *,
   ...> rtrim( rtrim(
   ...>   rtrim( rtrim(
   ...> rtrim( rtrim(
   ...>   rtrim( rtrim( time,
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' ),
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' )
   ...>  yr,
   ...> ltrim( ltrim(
   ...>   rtrim( rtrim(
   ...> rtrim( rtrim(
   ...>   rtrim( rtrim( time,
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' ),
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' )
   ...>  mth,
   ...> ltrim( ltrim(
   ...>   ltrim( ltrim(
   ...> rtrim( rtrim(
   ...>   rtrim( rtrim( time,
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' ),
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' )
   ...>  day,
   ...> ltrim( ltrim(
   ...>   ltrim( ltrim(
   ...> ltrim( ltrim(
   ...>   rtrim( rtrim( time,
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' ),
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' )
   ...>  hr,
   ...> ltrim( ltrim(
   ...>   ltrim( ltrim(
   ...> ltrim( ltrim(
   ...>   ltrim( ltrim( time,
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' ),
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' )
   ...>  min
   ...> from tst;
sqlite>
sqlite> create view tstView2 as
   ...> select *, '20' ||
   ...>   case length( yr ) == 1  when 1 then '0' || yr else yr end || '-' ||
   ...>   case length( mth ) == 1  when 1 then '0' || mth else mth end || '-' ||
   ...>   case length( day ) == 1  when 1 then '0' || day else day end || ' ' ||
   ...>   case length( hr ) == 1  when 1 then '0' || hr else hr end || ':' ||
   ...>   case length( min ) == 1  when 1 then '0' || min else min end
|| ':00' dateStr
   ...> from tstView1;
sqlite>
sqlite> create view tstView3 as
   ...> select *, julianday( dateStr ) julian
   ...> from tstView2;
sqlite>
sqlite>
sqlite> insert into tst( time ) values( '11 1 4 16 22' );
sqlite> insert into tst( time ) values( '1 11 14 6 2' );
sqlite> insert into tst( time ) values( '21 1 24 1 3' );
sqlite> insert into tst( time ) values( '13 3 24 16 22' );
sqlite> insert into tst( time ) values( '10 4 1 16 22' );
sqlite>
sqlite> insert into tst( time ) values( '11 1 11 10 22' );
sqlite> insert into tst( time ) values( '11 1 12 16 22' );
sqlite>
sqlite> select dateStr from tstview3 where julianday( 'now', '-8 days'
) > julian;
2011-01-04 16:22:00
2001-11-14 06:02:00
2010-04-01 16:22:00
2011-01-11 10:22:00
sqlite> select dateStr from tstview3 where julianday( 'now', '-8 days'
) < julian;
2021-01-24 01:03:00
2013-03-24 16:22:00
2011-01-12 16:22:00
sqlite>
sqlite> select datetime( 'now' );
2011-01-19 11:53:40
sqlite>

>
> Thanks,
> Ian

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

Re: [sqlite] WAL on a separate filesystem?

[Russell Leighton]

On Jan 19, 2011, at 5:35 AM, Richard Hipp wrote:

> On Wed, Jan 19, 2011 at 2:52 AM, Dustin Sallings   
> wrote:
>
>>
>>   Is it possible without violating any assumptions that would  
>> lead to
>> reliability problems to have a DB's WAL exist on a separate  
>> filesystem?
>>
>
> No.  The WAL has to be in the same directory as the original database.
> Otherwise, the process that tries to recover from a crash or power  
> failure
> won't know where to find the WAL file.
>

Perhaps that could be the default and a pragma could be used to  
override this default and specify the directory holding the WAL.

This could be useful in cases  that users want to put the WAL  
someplace else (like an SSD).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date operations when date is a TEXT

[Richard Hipp]

On Wed, Jan 19, 2011 at 5:06 AM, Ian Hardingham  wrote:

> Hey guys.
>
> Probably unwisely, I store dates in the following format:
>
> "year month day hour minute"
>
> For example:
>
> "11 1 4 16 22"
>

Perhaps you could write an extension function (using
sqlite3_create_function()) that will convert your data format into ISO8601:
" -MM-DD HH:MM", or in your case, "2011-01-04 16:22".  Then the strings
will compare in date order, the dates will be more easily human-readable,
you can leverage the built-in date/time functions of SQLite, and you will be
in compliance with an international standard.



>
> I wish to find all rows in a table which are more than 8 days old.  Is
> there a way of doing this in SQLite or should I just do it in my own code?
>
> Thanks,
> Ian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SQL-query execution bug

[Richard Hipp]

On Tue, Jan 18, 2011 at 12:07 PM, Vadim Smirnov wrote:

> Hello!
> I've found a bug in execution queries like this:
> SELECT ... FROM table T JOIN table2 T2 ON T2.child=T.master
> WHERE T2.attr in(SELECT value FROM table3 T3 JOIN (SELECT group, MAX(value)
> FROM table4 T4 WHERE T4.date_value<=T2.date_value GROUP BY group) G ON
> G.group=T3.group)
> Such queries returns empty resultset because of invalid evaluation WHERE
> T4.date_value<=T2.date_value. It seems that SQLite evaluates WHERE
> T4.date_value<=null in fact.
>

It works when I try it.

Why don't you send us a specific example that does not work for  you
(including CREATE TABLE statements and INSERTs to fill the tables with data)
and we'll have another look.



> If we replace "WHERE T4.date_value<=T2.date_value" with "WHERE
> T4.date_value<=" everything will be okey.
>
> Sincerely Yours, Wadim Smirnov
> System architect
> Positive Technologies CJSC, Russia
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] WAL on a separate filesystem?

[Richard Hipp]

On Wed, Jan 19, 2011 at 2:52 AM, Dustin Sallings  wrote:

>
>Is it possible without violating any assumptions that would lead to
> reliability problems to have a DB's WAL exist on a separate filesystem?
>

No.  The WAL has to be in the same directory as the original database.
Otherwise, the process that tries to recover from a crash or power failure
won't know where to find the WAL file.


>
> --
> dustin sallings
>
> ___
> 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] SQL-query execution bug

[Vadim Smirnov]

Hello!
I've found a bug in execution queries like this:
SELECT ... FROM table T JOIN table2 T2 ON T2.child=T.master
WHERE T2.attr in(SELECT value FROM table3 T3 JOIN (SELECT group, MAX(value) 
FROM table4 T4 WHERE T4.date_value<=T2.date_value GROUP BY group) G ON 
G.group=T3.group)
Such queries returns empty resultset because of invalid evaluation WHERE 
T4.date_value<=T2.date_value. It seems that SQLite evaluates WHERE 
T4.date_value<=null in fact.
If we replace "WHERE T4.date_value<=T2.date_value" with "WHERE 
T4.date_value<=" everything will be okey.

Sincerely Yours, Wadim Smirnov
System architect
Positive Technologies CJSC, Russia
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Date operations when date is a TEXT

[Ian Hardingham]

Hey guys.

Probably unwisely, I store dates in the following format:

"year month day hour minute"

For example:

"11 1 4 16 22"

I wish to find all rows in a table which are more than 8 days old.  Is 
there a way of doing this in SQLite or should I just do it in my own code?

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

[sqlite] insert into DB fail on NAND flash

[Gigin Jose]

Hi ,
I am working on ARM9 based s3c2440 embedded platform on linux OS. The database 
I use is SQLITE over QT application.  The "insert into" query is failing once I 
try to insert values into a database. The database resides on a NAND flash with 
yaffs2 formated filesystem. The following is the query which I execute:
QString stat = QString("insert into user20 values('%0', '%1','%2' 
)").arg(id).arg(name).arg(address);
        k = query.exec(stat);
        if(k == false)            qDebug("K is false for record ,%s 
\n",id.toAscii().data());
After continuous read/write the query is failing. Once I restart my target 
(s3c2440), the query is again executed fine. Before the query is executed, I 
delete the database file, if it exist, so that ideally the record insertion can 
happen. What can be the problem of query failure ? Is the continuous read/write 
in NAND flash corrupting the memory. ? Please help. 
With RegardsGIGIN


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