Re: [sqlite] Efficient random sampling in a large table using builtinfunctions.

[Benoit Mortgat]

On Thu, Mar 8, 2012 at 15:02, Igor Tandetnik 
wrote:
> Benoit Mortgat  wrote:
>> * Generate N, random, row numbers between 1 and (SELECT COUNT(*) FROM
>>   the_table_name). Maybe using remainder operator % and builtin ABS()
>>   and RANDOM() functions can help (see below) * SELECT FROM the_table
>> WHERE rowid IN (those random numbers)
>
> That is not equivalent. First, duplicate random numbers may be
> generated. Second, rowid values are not necessarily sequential. Thus,
> you may end up with fewer than N rows.

Oh, I assumed rowids were sequential. If that's not the case, then
you're right and my query is bad. Thanks for pointing that out.

> You can implement this algorithm in your application's code, if you
> are so inclined.

Still, what I told is that I use that query only to get a random sample
for displaying. I do that from inside the SQLite shell, for
vizualization purposes, and as soon as I get approximately N results,
this is satisfactory to me. I am not integrating anything into an
application.

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

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Alan Chandler]

On 09/03/12 00:29, Richard Hipp wrote:

On Thu, Mar 8, 2012 at 6:47 PM, Alan Chandlerwrote:


The complete database schema is here

https://github.com/akc42/**AKCMoney/blob/master/app/inc/**database.sql

My database is full of private financial data so I would rather not just
post it publically.  If you really need the data I could mail it to you
privately (its only 366kb big)


Can you scrub the data (replace numbers with values from random(), and all
strings with random text?)  If not, email the database directly to me.



I have done some randomisation and sent it to you privately

--
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] xcopy deployment using System.Data.SQLite for Windows x86, x64, and Mono/Unix?

[Joe Mistachkin]

Andrew Rondeau wrote:
>
> Are there any versions of System.Data.SQLite.dll that I can download that
> will work on both x86 and x64 Windows; AND Mac/Linux via Mono?
>

Mono is supported.  However, there is no pre-compiled binary package for it.
See the following for more information:

http://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q6

--
Joe Mistachkin

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

[sqlite] xcopy deployment using System.Data.SQLite for Windows x86, x64, and Mono/Unix?

[Andrew Rondeau]

I currently have a project that I developed in C# using SQLite. The project
deploys successfully to Windows, Mac, and Ubuntu Linux via XCopy deployment.

At the moment, I use an older System.Data.SQLite.dll from PHX, and a
sqlite3.dll from sqlite.net. These I downloaded in early 2009, and have
been happily using them ever since. It appears that when I run my project
on Mac or Ubuntu Linux, PHX's older System.Data.SQLite magically binds to
whatever is already installed on the operating system. :)

The problem that I'm having is that I'd now like to support 64-bit Windows,
and there is no 64-bit sqlite3.dll. There are both 32-bit and 64-bit
SQLite.Interop.dlls that the current System.Data.SQLite.dll bind to. I can
successfully XCopy deploy my project on Windows using these libraries, but
they do not work when I try to use them on Mac with Mono.

Are there any versions of System.Data.SQLite.dll that I can download that
will work on both x86 and x64 Windows; AND Mac/Linux via Mono? I plan on
sticking with XCopy deployment of "Any CPU" managed code; with the
exception of picking the right native sqlite .dll on Windows. If possible,
I'd like to avoid building my own SQLite.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR REPLACE

[Mario Becroft]

Alek Paunov  writes:

> So maybe it's worth to give it a try ... Is there someone else, which
> is interested to work on VDBE dump/load to assembler representation?
> Once these base tools are available, maybe other people would reuse
> bytecode instrumentation for other purposes also ;-)

I don't have time to work on this at the moment, but here is my input:

I think the improved update functionality is important, and presumably
easily supported by sqlite's existing virtual machine.

I disagree that databases should not be used to perform
calculations. Actually SQL can be very concise for expressing
calculations, and in fact the database engine may be able to optimise
the query better than what a client could achieve by issuing separate
SELECT and UPDATE queries. Also, by making use of views, triggers and
stored procedures, the database can provide a useful abstraction
layer. Sqlite already does this very well with views and triggers, but
lacks parameterised view/stored procedure functionality. Another reason
to use the database for doing calculations (including iterative or
recursive ones) is that you can use the native SQL data representation,
including things like NULL support which are often missing from other
languages, plus it can be faster because of not needing to convert types
between sqlite's internal representation and another language
representation.

It has been noted that sqlite already has a turing-complete virtual
machine, so why not provide the front-end facilities to make full use of
it? I expect it wouldn't take much effort to implement a procedural
language and/or recursive joins based on the current virtual machine.

I realise this is a 'lite' database, but it seems as if the heavy
lifting has already been done. In researching this previously I noted an
existing effort at adding a procedural language to sqlite:

1. http://www.sqliteconcepts.org/PL_index.html

This effort looks quite good although I think it needs some
refinement. It adds the ability to execute procedural code immediately
or in a stored procedure. Both would be exceptionally useful additions
to sqlite, even if available only as an optional module.

I would suggest using PostgreSQL as a reference for implementing a
procedural language, since it has a robust procedural language
implementation, and I and I suspect others already use both postgres and
sqlite.

I realise I have gone off in a somewhat different direction from the
VDBE dump/load capability. I think this would be a useful feature as
well, but more useful for experimenting with the development of new
functionality, such as compiling new language constructs. Probably both
could could be done at once.

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

Re: [sqlite] INSERT OR REPLACE

[Alek Paunov]

On 09.03.2012 02:22, Nico Williams wrote:

On Thu, Mar 8, 2012 at 5:57 PM, Alek Paunov  wrote:

Let suppose hypothetical function:

asm(sql): returns VDBE assembler code for the sql parameter (like 'explain'
but with full instruction attributes)

I am curious, Is it evaluable (to proper VDBE code):

asm(update with complex join)
as Transformation(
asm(select with the same join),
asm(same update without join)
)

?

What the "internals" gurus think? :-)


It most definitely is.  (I'm not a SQLite3 internals guru, but I've
played enough with the internals to believe that I can make that
assertion with high confidence.  I could be wrong however.  Caveat
emptor.)


So maybe it's worth to give it a try ... Is there someone else, which is 
interested to work on VDBE dump/load to assembler representation? Once 
these base tools are available, maybe other people would reuse bytecode 
instrumentation for other purposes also ;-)

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

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Richard Hipp]

On Thu, Mar 8, 2012 at 6:47 PM, Alan Chandler wrote:

> The complete database schema is here
>
> https://github.com/akc42/**AKCMoney/blob/master/app/inc/**database.sql
>
> My database is full of private financial data so I would rather not just
> post it publically.  If you really need the data I could mail it to you
> privately (its only 366kb big)
>

Can you scrub the data (replace numbers with values from random(), and all
strings with random text?)  If not, email the database directly to me.


-- 
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] INSERT OR REPLACE

[Alek Paunov]

Hi List,

On 09.03.2012 01:15, Nico Williams wrote:

SQL was not, originally, a Turing complete language (unless one were
to consider cross self joins of large tables for driving iteration as
good enough), but nowadays it pretty much is, therefore it  is a
programming language.  The language supported by SQLite3 is Turing
complete, FYI, since you have conditionals (WHERE, WHEN, CASE, ...)
and iteration (e.g., via recursive triggers).  And the SQLite3 VM most
assuredly is Turing complete.


Let suppose hypothetical function:

asm(sql): returns VDBE assembler code for the sql parameter (like 
'explain' but with full instruction attributes)


I am curious, Is it evaluable (to proper VDBE code):

asm(update with complex join)
as Transformation(
asm(select with the same join),
asm(same update without join)
)

?

What the "internals" gurus think? :-)

Thank you,
Alek

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

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Alan Chandler]

On 08/03/12 23:32, Richard Hipp wrote:

On Thu, Mar 8, 2012 at 5:44 PM, Alan Chandlerwrote:


I have been running a financial management application application I wrote
for a number of years.  My "production" version runs on Debian stable
system as a result is running sqlite v3.7.3.  My personal development
machine is running Debian unstable and as a result has sqlite 3.7.10.

Earlier this week I discovered a bug in a rarely used part of the
application, so took a copy of the production database and ran it on my
development machine. I quickly found that and fixed it, but another major
element of the application appeared to give some strange results.

I have spend some down tracking down what caused the problem, and it seems
to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 processes the sql.
  It seems to me that the later release gets things wrong - but it might be
that the newer version has some sort of PRAGMA that I am not using right.
  So I would like to ask here where I am going wrong.

The basic issue is around a view on a table called "xaction" - the
transactions processed.  It has optional "source" and "destination"
accounts (must be at least one or the other but can also have both) and
optional "codes" that relate to classes of transaction as they appear in
the account.  I put a view on top of this which normalises the currency for
use in my accounts. The schema for the view is ...

CREATE VIEW dfxaction AS
SELECT t.id,t.date,t.version, src, srccode, dst,
dstcode,t.description, rno, repeat,
CASE
WHEN t.currency = 'GBP' THEN t.amount
WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN
t.srcamount
WHEN t.dstamount IS NOT NULL AND da.currency = 'GBP' THEN
t.dstamount
ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS INTEGER)
END AS dfamount
FROM
xaction AS t
LEFT JOIN account AS sa ON t.src = sa.name
LEFT JOIN account AS da ON t.dst = da.name
LEFT JOIN currency ON
t.currency != 'GBP' AND
(t.srcamount IS NULL OR sa.currency != 'GBP') AND
(t.dstamount IS NULL OR da.currency != 'GBP') AND
t.currency = currency.name;


The query that results in differences between the two versions of sqlite
(I have cut this down to the minimum I could find showed the difference).

SELECT
c.id AS id, c.type AS type, c.description AS description, t.*
FROM
dfxaction AS t, code AS c
WHERE
t.date BETWEEN 129384 AND 1325375999
AND ((t.src IS NOT NULL AND t.srccode = c.id)  OR t.dstcode = c.id )
ORDER BY
c.description COLLATE NOCASE ASC;

The little part "t.src IS NOT NULL AND" seems to be the trigger for
cutting down the records to almost none from the full amount because when I
remove it I got more like the correct number of records.  Most of the
records excluded by putting the clause in DO NOT have t.src of NULL.

AND OF COURSE ON sqlite 3.7.3 I get all the records I expect.

The other important aspect.  If I don't use a view, but instead replace
"FROM dfxaction" with "FROM xaction" using the raw table then 3.7.10
delivers all the records I would expect.

So I am completely perplexed as to why there are changes between
behaviour.  Can anyone help me understand.


Maybe the change at http://www.sqlite.org/src/info/b23ae13187 broke
something.  Can you get us a complete schema with enough data to actually
run a test case that shows the problem?

The complete database schema is here

https://github.com/akc42/AKCMoney/blob/master/app/inc/database.sql

My database is full of private financial data so I would rather not just 
post it publically.  If you really need the data I could mail it to you 
privately (its only 366kb big)



--
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Bug? Empty string stored as NULL?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/03/12 11:59, Marc L. Allen wrote:
> I use Navicat Lite, and it apparently treats an empty string as a NULL
> when displaying.

It is far more likely to display the empty string as an empty string and
to also display null as an empty string (by default) otherwise the nulls
will litter your output which often isn't helpful.  The SQLite shell does
exactly the same thing by default.

You can however tell the SQLite shell to display null as a string of your
choice, but then you have the problem of telling the difference between
that and a string of the same value.  Navicat probably has a similar
setting somewhere.

Incidentally my Python SQLite wrapper includes a shell, which you can use
without knowing anything about Python:

  http://apidoc.apsw.googlecode.com/hg/shell.html

One of the things it does is to colour output which means you could tell
the difference between the null value (shown in red) and a string (shown
in yellowish).  You can also tell the difference between the integer 3 and
the string "3" due to the colour.  Makes life a lot easier.

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

iEYEARECAAYFAk9ZQ1kACgkQmOOfHg372QQbdACg1CrjbyGXu0UaZX+DzFDD+mCY
/ssAoIct31OJ5JDZRilBuPtN9+6x9cro
=066G
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Richard Hipp]

On Thu, Mar 8, 2012 at 6:27 PM, Alan Chandler wrote:

> On 08/03/12 22:44, Alan Chandler wrote:
>
>> I have been running a financial management application application I
>> wrote for a number of years.  My "production" version runs on Debian stable
>> system as a result is running sqlite v3.7.3.  My personal development
>> machine is running Debian unstable and as a result has sqlite 3.7.10.
>>
>> Earlier this week I discovered a bug in a rarely used part of the
>> application, so took a copy of the production database and ran it on my
>> development machine. I quickly found that and fixed it, but another major
>> element of the application appeared to give some strange results.
>>
>> I have spend some down tracking down what caused the problem, and it
>> seems to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 processes
>> the sql.  It seems to me that the later release gets things wrong - but it
>> might be that the newer version has some sort of PRAGMA that I am not using
>> right.  So I would like to ask here where I am going wrong.
>>
>>
> Things have now got stranger.  I just saw the post on the e-mail list for
> sqlitestudio and thought that looks interesting, so I have now downloaded
> it.  It seems to be using sqlite 3.7.8
>
> It works correctly, and the very same sql using sqlite manager in Mozilla
> goes wrong (this is linked to sqlite 3.7.10)


The suspect change at http://www.sqlite.org/src/info/b23ae13187 first
appeared in version 3.7.10.


>
>
> --
> Alan Chandler
> http://www.chandlerfamily.org.**uk 
>
> __**_
> 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] Strange difference between sqlite 3.7.3 and 3.7.10

[Richard Hipp]

On Thu, Mar 8, 2012 at 5:44 PM, Alan Chandler wrote:

> I have been running a financial management application application I wrote
> for a number of years.  My "production" version runs on Debian stable
> system as a result is running sqlite v3.7.3.  My personal development
> machine is running Debian unstable and as a result has sqlite 3.7.10.
>
> Earlier this week I discovered a bug in a rarely used part of the
> application, so took a copy of the production database and ran it on my
> development machine. I quickly found that and fixed it, but another major
> element of the application appeared to give some strange results.
>
> I have spend some down tracking down what caused the problem, and it seems
> to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 processes the sql.
>  It seems to me that the later release gets things wrong - but it might be
> that the newer version has some sort of PRAGMA that I am not using right.
>  So I would like to ask here where I am going wrong.
>
> The basic issue is around a view on a table called "xaction" - the
> transactions processed.  It has optional "source" and "destination"
> accounts (must be at least one or the other but can also have both) and
> optional "codes" that relate to classes of transaction as they appear in
> the account.  I put a view on top of this which normalises the currency for
> use in my accounts. The schema for the view is ...
>
> CREATE VIEW dfxaction AS
>SELECT t.id,t.date,t.version, src, srccode, dst,
> dstcode,t.description, rno, repeat,
>CASE
>WHEN t.currency = 'GBP' THEN t.amount
>WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN
> t.srcamount
>WHEN t.dstamount IS NOT NULL AND da.currency = 'GBP' THEN
> t.dstamount
>ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS INTEGER)
>END AS dfamount
>FROM
>xaction AS t
>LEFT JOIN account AS sa ON t.src = sa.name
>LEFT JOIN account AS da ON t.dst = da.name
>LEFT JOIN currency ON
>t.currency != 'GBP' AND
>(t.srcamount IS NULL OR sa.currency != 'GBP') AND
>(t.dstamount IS NULL OR da.currency != 'GBP') AND
>t.currency = currency.name;
>
>
> The query that results in differences between the two versions of sqlite
> (I have cut this down to the minimum I could find showed the difference).
>
> SELECT
>c.id AS id, c.type AS type, c.description AS description, t.*
> FROM
>dfxaction AS t, code AS c
> WHERE
>t.date BETWEEN 129384 AND 1325375999
>AND ((t.src IS NOT NULL AND t.srccode = c.id)  OR t.dstcode = c.id )
> ORDER BY
>c.description COLLATE NOCASE ASC;
>
> The little part "t.src IS NOT NULL AND" seems to be the trigger for
> cutting down the records to almost none from the full amount because when I
> remove it I got more like the correct number of records.  Most of the
> records excluded by putting the clause in DO NOT have t.src of NULL.
>
> AND OF COURSE ON sqlite 3.7.3 I get all the records I expect.
>
> The other important aspect.  If I don't use a view, but instead replace
> "FROM dfxaction" with "FROM xaction" using the raw table then 3.7.10
> delivers all the records I would expect.
>
> So I am completely perplexed as to why there are changes between
> behaviour.  Can anyone help me understand.
>

Maybe the change at http://www.sqlite.org/src/info/b23ae13187 broke
something.  Can you get us a complete schema with enough data to actually
run a test case that shows the problem?



>
> Thanks
>
> --
> Alan Chandler
> http://www.chandlerfamily.org.**uk 
>
> __**_
> 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] Strange difference between sqlite 3.7.3 and 3.7.10

[Alan Chandler]

On 08/03/12 22:44, Alan Chandler wrote:
I have been running a financial management application application I 
wrote for a number of years.  My "production" version runs on Debian 
stable system as a result is running sqlite v3.7.3.  My personal 
development machine is running Debian unstable and as a result has 
sqlite 3.7.10.


Earlier this week I discovered a bug in a rarely used part of the 
application, so took a copy of the production database and ran it on 
my development machine. I quickly found that and fixed it, but another 
major element of the application appeared to give some strange results.


I have spend some down tracking down what caused the problem, and it 
seems to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 
processes the sql.  It seems to me that the later release gets things 
wrong - but it might be that the newer version has some sort of PRAGMA 
that I am not using right.  So I would like to ask here where I am 
going wrong.




Things have now got stranger.  I just saw the post on the e-mail list 
for sqlitestudio and thought that looks interesting, so I have now 
downloaded it.  It seems to be using sqlite 3.7.8


It works correctly, and the very same sql using sqlite manager in 
Mozilla goes wrong (this is linked to sqlite 3.7.10)


--
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] INSERT OR REPLACE

[Nico Williams]

On Thu, Mar 8, 2012 at 4:37 PM, Simon Slavin  wrote:
> That's why you don't make a DBMS (SQL) do the job of a programming language.  
> Use your programming
language to to retrieve the values you need to make your calculations.
 Then use your programming language to figure out the new values and
execute the UPDATE commands needed to make the changes.

Lots of people want to and do use SQL to do a lot of what a regular
programming language can do.

Some are even taking SQL and applying it to new problems.  See ql.io
for example.

One very nice thing about using SQL to do more is that you can do a
lot of prototyping from a SQL shell and then start on the scaffolding
around it in whatever other language, and if you do it this way you'll
probably find yourself doing a lot of programming in SQL.

> SQL is not a programming language.  Trying to use it like one leads to 
> ridiculous contortions which take longer to run than doing it all in SQL 
> would take.  There's nothing magically fast about SQL, it still has to do the 
> same amount of work in the end.

SQL was not, originally, a Turing complete language (unless one were
to consider cross self joins of large tables for driving iteration as
good enough), but nowadays it pretty much is, therefore it  is a
programming language.  The language supported by SQLite3 is Turing
complete, FYI, since you have conditionals (WHERE, WHEN, CASE, ...)
and iteration (e.g., via recursive triggers).  And the SQLite3 VM most
assuredly is Turing complete.

But, really, it's quite a hole that I can INSERT INTO table SELECT
 but I can't UPDATE table .
INSERT OR REPLACE is almost a very good alternative.

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

Re: [sqlite] GUI for SQLite

[Alek Paunov]

On 08.03.2012 15:13, gregorinator wrote:

I've been happy with SQLite Studio:

http://sqlitestudio.one.pl/


Just tried Sqlite Studio following your advice - Great tool:

 * open source
 * implemented in scripting language (Tcl/Tk)
 * available as single executable
 * SQL editor with highlighting and autocompletion
 * browsing big tables
 * convenient editing of the data directly in the result grid
 * user defined functions in tcl and sql, plugins
 * many, many other features ...

I was Sqlite Manager user past years (Firefox plugin) but now I think I 
have a better tool :-)


Thank you Pawel!
Alek

P.S. If anyone have troubles to use the app on Fedora/CentOS, feel free 
to drop me a line.

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

Re: [sqlite] INSERT OR REPLACE

[BareFeetWare]

I suggest only using "insert or replace" if you genuinely want to delete and 
replace with a new row. This is generally not hat you want if there are any 
foreign keys.

Only use "insert or ignore" if you are inserting exactly the same row as what 
might already be there.

Otherwise use a combination of insert and update.

So, for example, if you are wanting to add a person that may not already be in 
your table:

insert or ignore into "Person" ("First Name", "Last Name", "Company", "Email")
select 'Tom', 'Brodhurst-Hill', 'BareFeetWare', 'develo...@barefeetware.com')
;
update "Person"
set "Email" = 'develo...@barefeetware.com'
where "First Name" = 'Tom' and "Last Name" = 'Brodhurst-Hill'
;

This will insert if not already there and update if it is.

If you post your specific schema and non-idea insert or replace, I can show you 
in that context.

Thanks,
Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

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

[sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Alan Chandler]

I have been running a financial management application application I 
wrote for a number of years.  My "production" version runs on Debian 
stable system as a result is running sqlite v3.7.3.  My personal 
development machine is running Debian unstable and as a result has 
sqlite 3.7.10.


Earlier this week I discovered a bug in a rarely used part of the 
application, so took a copy of the production database and ran it on my 
development machine. I quickly found that and fixed it, but another 
major element of the application appeared to give some strange results.


I have spend some down tracking down what caused the problem, and it 
seems to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 processes 
the sql.  It seems to me that the later release gets things wrong - but 
it might be that the newer version has some sort of PRAGMA that I am not 
using right.  So I would like to ask here where I am going wrong.


The basic issue is around a view on a table called "xaction" - the 
transactions processed.  It has optional "source" and "destination" 
accounts (must be at least one or the other but can also have both) and 
optional "codes" that relate to classes of transaction as they appear in 
the account.  I put a view on top of this which normalises the currency 
for use in my accounts. The schema for the view is ...


CREATE VIEW dfxaction AS
SELECT t.id,t.date,t.version, src, srccode, dst, 
dstcode,t.description, rno, repeat,

CASE
WHEN t.currency = 'GBP' THEN t.amount
WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN 
t.srcamount
WHEN t.dstamount IS NOT NULL AND da.currency = 'GBP' THEN 
t.dstamount
ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS 
INTEGER)

END AS dfamount
FROM
xaction AS t
LEFT JOIN account AS sa ON t.src = sa.name
LEFT JOIN account AS da ON t.dst = da.name
LEFT JOIN currency ON
t.currency != 'GBP' AND
(t.srcamount IS NULL OR sa.currency != 'GBP') AND
(t.dstamount IS NULL OR da.currency != 'GBP') AND
t.currency = currency.name;


The query that results in differences between the two versions of sqlite 
(I have cut this down to the minimum I could find showed the difference).


SELECT
c.id AS id, c.type AS type, c.description AS description, t.*
FROM
dfxaction AS t, code AS c
WHERE
t.date BETWEEN 129384 AND 1325375999
AND ((t.src IS NOT NULL AND t.srccode = c.id)  OR t.dstcode = c.id )
ORDER BY
c.description COLLATE NOCASE ASC;

The little part "t.src IS NOT NULL AND" seems to be the trigger for 
cutting down the records to almost none from the full amount because 
when I remove it I got more like the correct number of records.  Most of 
the records excluded by putting the clause in DO NOT have t.src of NULL.


AND OF COURSE ON sqlite 3.7.3 I get all the records I expect.

The other important aspect.  If I don't use a view, but instead replace 
"FROM dfxaction" with "FROM xaction" using the raw table then 3.7.10 
delivers all the records I would expect.


So I am completely perplexed as to why there are changes between 
behaviour.  Can anyone help me understand.


Thanks

--
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] INSERT OR REPLACE

[Simon Slavin]

On 8 Mar 2012, at 7:43pm, Marc L. Allen  wrote:

>>> What happens if the intermediate delete breaks a foreign key
>>> constraint?  And does it happen if the insert restores the foreign key?
>> 
>> The constraint stops the DELETE, and the operation fails.  Just like
>> you were worried about.  So instead of doing
>> 
>> INSERT OR REPLACE ...
>> 
>> you do
>> 
>> INSERT OR IGNORE ...
>> UPDATE ...
> 
> But then I am back to the problem of large, ugly, and unwieldy UPDATE 
> statements for multiple columns and joins.

That's why you don't make a DBMS (SQL) do the job of a programming language.  
Use your programming language to to retrieve the values you need to make your 
calculations.  Then use your programming language to figure out the new values 
and execute the UPDATE commands needed to make the changes.

SQL is not a programming language.  Trying to use it like one leads to 
ridiculous contortions which take longer to run than doing it all in SQL would 
take.  There's nothing magically fast about SQL, it still has to do the same 
amount of work in the end.

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

Re: [sqlite] C# How to getorun autoincrement value

[Joe D]

On 2012-03-08 11:07, Joe D wrote:

On 2012-03-06 15:20, Pawl wrote:


Hi,

I don't know how to make this EASY thing (for me side) I found only this,
but they said it is solverd, but it isn;t
http://stackoverflow.com/questions/3828098/solved-how-to-return-autoincrement-value-in-insert-query-in-sqlite


I don;t know what I get exception Not supported when I tried to setup
ParameterDirection another then input!

I add this "sqa.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;" before. But without any result!


Try this.

SQLiteCommand command = new SQLiteCommand("INSERT INTO TABLE (FIELD1,
FIELD2) VALUES (:FIELD1, :FIELD2); SELECT LAST_INSERT_ROWID();");

... append parameters ...

int nextValue = command.ExecuteScalar();


Or if you're looking for a specific column and not the ROWID, try this:

SQLiteCommand command = new SQLiteCommand("INSERT INTO TABLE (FIELD1, 
FIELD2) VALUES (:FIELD1, :FIELD2); SELECT FIELD1 FROM TABLE WHERE ROWID 
= LAST_INSERT_ROWID();");


... append parameters ...

int nextValue = command.ExecuteScalar();

Never tried that, but it looks like it should work...

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

Re: [sqlite] INSERT OR REPLACE

[Nico Williams]

On Thu, Mar 8, 2012 at 1:52 PM, Pavel Ivanov  wrote:
>> So, please.. add INSERT OR UPDATE.  I'd ask that INSERT OR REPLACE be 
>> modified to do an UPDATE to the record if the UNIQUE Constraint is violated 
>> instead of the delete, but that would break existing applications.
>>
>> Wasn't that the original intent of INSERT OR REPLACE?

BTW, you could use DEFERRED foreign key references to avoid some of
the worst aspects of this issue.

> No, because INSERT OR REPLACE can delete several rows and insert one
> row instead of that. This kind of operation cannot be covered by
> UPDATE in any way.

When the conflict that leads to REPLACE is found via a non-primary key
UNIQUE constraint violation then I agree, the REPLACE must be treated
like DELETE-then-INSERT.  Otherwise it should be treated as an UPDATE.
 *But*, of course, there may be multiple rows that could replace the
same row, and the order in which the insertions are done may not be
specified, so there's an ambiguity that should result in
non-deterministic / undefined behavior (sometimes you get REPLACE as
DELETE-then-INSERT, sometimes as UPDATE).  This ambiguity can be
resolved trivially if there are no non-primary key unique constraints,
of course, but that's a very special case.  And when I say "unique
constraints" I mean "including unique indexes".

There might be some odd interactions with any future feature that adds
deferred unique constraints to worry about too.  Though that actually
would help resolve the ambiguity quite nicely if it applied only to
non-primary key uniqueness constraint violations, though at the price
of getting INSERT-then-DELETE in some cases instead of
DELETE-then-INSERT.

Very interesting problem...

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

Re: [sqlite] INSERT OR REPLACE

[Marc L. Allen]

> No, because INSERT OR REPLACE can delete several rows and insert one
> row instead of that. This kind of operation cannot be covered by UPDATE
> in any way.

I hadn't thought of that.. I suppose if two independent unique indexes had a 
constraint violation by the same record, it would delete two records and 
replace with one.

I've never had to worry about that.  For my purposes, I'd rather it UPDATE if 
only one record is affected.

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

Re: [sqlite] Bug? Empty string stored as NULL?

[Marc L. Allen]

> A good rule of thumb is that if you claim a bug in another component
> you are almost certainly wrong.

I don't have the command line version on this machine.  I use Navicat Lite, and 
it apparently treats an empty string as a NULL when displaying.

I always check my own code.  I didn't expect Navicat to make such an error.

Sorry.. and thanks.

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

Re: [sqlite] Bug? Empty string stored as NULL?

[Luuk]

On 08-03-2012 20:48, Marc L. Allen wrote:
> Is that expected?  To me, '' is different than NULL.
> 
> create table a
> (
> a text
> );
> 
> insert into a select  '';
> select * from a;
> 
> Is there a way to store an empty string?
> 

insert into a values(null);

.nullvalue NULL
select * from a;
a|length(a)
|0
NULL|NULL


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

Re: [sqlite] Bug? Empty string stored as NULL?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

A good rule of thumb is that if you claim a bug in another component you
are almost certainly wrong.

On 08/03/12 11:48, Marc L. Allen wrote:
> Is that expected?  To me, '' is different than NULL.

They are indeed different.

> Is there a way to store an empty string?

Yes, store an empty string.

sqlite> create table a(a text);
sqlite> insert into a select '';
sqlite> insert into a select null;
sqlite> select * from a;


sqlite> select typeof(a) from a;
text
null
sqlite> select 'X' || a || 'Y' from a;
XY

sqlite>


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

iEYEARECAAYFAk9ZDqoACgkQmOOfHg372QSsAgCgjkuP3HrMs1eQA9IznpTEMYpw
KTsAoMsDH9DONdp0eJJn82TWW5ZtQeT1
=nrBP
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? Empty string stored as NULL?

[Pavel Ivanov]

Where do you see NULL? I see empty string.

SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a (t text);
sqlite> insert into a select '';
sqlite> .nullvalue NULL
sqlite> select * from a;

sqlite>


Pavel


On Thu, Mar 8, 2012 at 2:48 PM, Marc L. Allen
 wrote:
> Is that expected?  To me, '' is different than NULL.
>
> create table a
> (
>                a text
> );
>
> insert into a select  '';
> select * from a;
>
> Is there a way to store an empty string?
>
> Marc
>
> --
> **
> *                             *                                  *
> * Marc L. Allen               *  "... so many things are         *
> *                             *  possible just as long as you    *
> * Outsite Networks, Inc.      *  don't know they're impossible." *
> * (757) 853-3000 #215         *                                  *
> *                             *                                  *
> * mlal...@outsitenetworks.com *       -- The Phantom Tollbooth   *
> *                             *                                  *
> **
> ___
> 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] Bug? Empty string stored as NULL?

[Puneet Kishor]

On Mar 8, 2012, at 1:48 PM, Marc L. Allen wrote:

> Is that expected?  To me, '' is different than NULL.
> 
> create table a
> (
>a text
> );
> 
> insert into a select  '';
> select * from a;
> 
> Is there a way to store an empty string?
> 


works for me

punkish@mumbai ~$sqlite3
SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t (a TEXT NOT NULL);
sqlite> INSERT INTO t SELECT '';
sqlite> SELECT * FROM t;

sqlite> SELECT Count(*) FROM t;
1
sqlite> 

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

Re: [sqlite] INSERT OR REPLACE

[Pavel Ivanov]

> So, please.. add INSERT OR UPDATE.  I'd ask that INSERT OR REPLACE be 
> modified to do an UPDATE to the record if the UNIQUE Constraint is violated 
> instead of the delete, but that would break existing applications.
>
> Wasn't that the original intent of INSERT OR REPLACE?

No, because INSERT OR REPLACE can delete several rows and insert one
row instead of that. This kind of operation cannot be covered by
UPDATE in any way.


Pavel


On Thu, Mar 8, 2012 at 2:43 PM, Marc L. Allen
 wrote:
>> > What happens if the intermediate delete breaks a foreign key
>> constraint?  And does it happen if the insert restores the foreign key?
>>
>> The constraint stops the DELETE, and the operation fails.  Just like
>> you were worried about.  So instead of doing
>>
>> INSERT OR REPLACE ...
>>
>> you do
>>
>> INSERT OR IGNORE ...
>> UPDATE ...
>
> But then I am back to the problem of large, ugly, and unwieldy UPDATE 
> statements for multiple columns and joins.
>
> I guess the easiest thing is to do the select and then iterate over the 
> records doing an UPDATE for each one.
>
> So, please.. add INSERT OR UPDATE.  I'd ask that INSERT OR REPLACE be 
> modified to do an UPDATE to the record if the UNIQUE Constraint is violated 
> instead of the delete, but that would break existing applications.
>
> Wasn't that the original intent of INSERT OR REPLACE?
>
> Marc
> ___
> 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] Bug? Empty string stored as NULL?

[Marc L. Allen]

Is that expected?  To me, '' is different than NULL.

create table a
(
a text
);

insert into a select  '';
select * from a;

Is there a way to store an empty string?

Marc

--
**
* *  *
* Marc L. Allen   *  "... so many things are *
* *  possible just as long as you*
* Outsite Networks, Inc.  *  don't know they're impossible." *
* (757) 853-3000 #215 *  *
* *  *
* mlal...@outsitenetworks.com *   -- The Phantom Tollbooth   *
* *  *
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR REPLACE

[Marc L. Allen]

> > What happens if the intermediate delete breaks a foreign key
> constraint?  And does it happen if the insert restores the foreign key?
> 
> The constraint stops the DELETE, and the operation fails.  Just like
> you were worried about.  So instead of doing
> 
> INSERT OR REPLACE ...
> 
> you do
> 
> INSERT OR IGNORE ...
> UPDATE ...

But then I am back to the problem of large, ugly, and unwieldy UPDATE 
statements for multiple columns and joins.

I guess the easiest thing is to do the select and then iterate over the records 
doing an UPDATE for each one.

So, please.. add INSERT OR UPDATE.  I'd ask that INSERT OR REPLACE be modified 
to do an UPDATE to the record if the UNIQUE Constraint is violated instead of 
the delete, but that would break existing applications.

Wasn't that the original intent of INSERT OR REPLACE?  

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

Re: [sqlite] INSERT OR REPLACE

[Simon Slavin]

On 8 Mar 2012, at 6:39pm, Marc L. Allen  wrote:

> What happens if the intermediate delete breaks a foreign key constraint?  And 
> does it happen if the insert restores the foreign key?

The constraint stops the DELETE, and the operation fails.  Just like you were 
worried about.  So instead of doing

INSERT OR REPLACE ...

you do

INSERT OR IGNORE ...
UPDATE ...

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

Re: [sqlite] SIGBUS error in case of disk full with WAL mode

[Scott Hess]

On Thu, Mar 8, 2012 at 9:28 AM, Pavel Ivanov  wrote:
>> Question:  Does anybody know of a better way to get memory shared among
>> processes other than to create a fake file and mmap() it?  Are there some
>> magic options to mmap() (perhaps Linux-only options) that prevent it from
>> actually writing to disk?
>
> Why don't you use shm_open() instead of a real file? I'm not sure
> though how it behaves with chroot jail.

I do not recall the full semantics of shm_open(), but I _think_ that
it shares the sysv shared-memory problem where the memory sticks
around until explicitly deleted.  Using a fake file with mmap() will
create a segment which only exists until the last process using it
goes away.  This can become a resource issue on some systems.  Also,
the name would have to be carefully constructed to prevent conflicts,
as the namespace may not be the same as the file path namespace (even
path-like names may have different length restrictions).  Like maybe
base64(hash(canonical_path(dbpath))).

BTW, the SQLite docs indicate that as of 3.7.4, you can arrange to use
an exclusive mode which allows WAL to work without shm.  That may be a
reasonable approach for some subset of users with this kind of
problem.

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

[sqlite] INSERT OR REPLACE

[Marc L. Allen]

What happens if the intermediate delete breaks a foreign key constraint?  And 
does it happen if the insert restores the foreign key?

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

Re: [sqlite] output mode formatting

[Simon Slavin]

On 8 Mar 2012, at 5:19pm, Rita  wrote:

> I am doing a echo "SELECT * from TABLE;" | sqlite3 database and the output
> comes out
> 
> Is it possible to do printf style formatting so other value dont take up
> too much space? So I want to have a value of only "%7s" and no more.

If you want only one of the columns, why are you doing a SELECT * ?

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

Re: [sqlite] How efficient is this?

[Nico Williams]

On Thu, Mar 8, 2012 at 12:00 PM, Marc L. Allen
 wrote:
>> This particular query need not be very inefficient if the pages needed
>> to do the second sub-query are left in the cache from the first sub-
>> query...
>
> No.  That's true.  But this is just a simple example.  It's possible that the 
> sub-select is much more complicated.

Right, which is why I said "this particular query".  And you're
completely right to want something better.  I think the simplest thing
that could be done is to change the trigger sematincs of INSERT OR
REPLACE to fire update triggers when the replacing row has the same
rowid and/or primary key as the replaced row.

> By the way, the syntax diagram doesn't allow "+=" so how would the above be 
> coded in reality?

Oops, I missed that.

> This way?
>
> UPDATE t1 SET a = (SELECT t1.a + t2.a FROM t2 WHERE t1.id = t2.id),
>              b = (SELECT t1.b + t2.b FROM t2 WHERE t1.id = t2.id)

UPDATE t1 SET a = a +  ...;

:)

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

Re: [sqlite] GUI for SQLite

[danap]

> Message: 8
> Date: Wed, 7 Mar 2012 21:47:28 +
> From: "Rose, John B" 
> To: "sqlite-users@sqlite.org" 
> Subject: [sqlite] GUI for SQLite
> Message-ID: 
> Content-Type: text/plain; charset="us-ascii"
>
> We are new to SQLite and have experimented with a few GUIs, Firefox
> plugin, SQLite DB Browser, and Navicat. Is there a single GUI that is
> considered the best, with the most features?
>
> Thanks

Guess I can't speak for which SQLite GUI is best, but I have been
working on supporting SQLite with MyJSQLView. Got most of the basic
features in place like add, editing, deleting, import and export
of data. In addition I have created a plugin environment for the
application to support various analysis. Currently the app comes
with two a TableFieldProfiler and Heatmap plotter. You can create
your own plugin, tutorial outlines how.

Plugin Use Examples:

Table Field Pattern Analysis Map:
http://dandymadeproductions.com/projects/MyJSQLView/analysis/latitude_pattern.html

Ocean Temperatures Heat Map:
http://dandymadeproductions.com/temp/ocean_temperatures.jpg

MyJSQLView requires the Java Runtime Environment 1.5+, SQLiteJDBC,
and is free open source tool.

See http://www.xerial.org for SQLiteJDBC. Install into jre/lib/ext/

Dana M. Proctor
MyJSQLView Project Manager
myjsqlview.org

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

Re: [sqlite] How efficient is this?

[Igor Tandetnik]

On 3/8/2012 1:00 PM, Marc L. Allen wrote:

By the way, the syntax diagram doesn't allow "+=" so how would the above be 
coded in reality?


SET a = a + whatever
--
Igor Tandetnik

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

Re: [sqlite] How efficient is this?

[Marc L. Allen]

> > I'm trying to do a multi-column update, adding values from another
> table.  Something like:
> >
> > UPDATE t1 SET a += (SELECT a FROM t2 WHERE t1.id = t2.id),
> >                              b += (SELECT b FROM t2 WHERE t1.id =
> > t2.id);
> 
> This particular query need not be very inefficient if the pages needed
> to do the second sub-query are left in the cache from the first sub-
> query...

No.  That's true.  But this is just a simple example.  It's possible that the 
sub-select is much more complicated.

By the way, the syntax diagram doesn't allow "+=" so how would the above be 
coded in reality?

This way? 

UPDATE t1 SET a = (SELECT t1.a + t2.a FROM t2 WHERE t1.id = t2.id),
  b = (SELECT t1.b + t2.b FROM t2 WHERE t1.id = t2.id)

Or...

UPDATE t1 SET a = t1.a + (SELECT t2.a FROM t2 WHERE t1.id = t2.id),
  b = t1.b + (SELECT t2.b FROM t2 WHERE t1.id = t2.id)

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

Re: [sqlite] How efficient is this?

[Nico Williams]

On Thu, Mar 8, 2012 at 11:56 AM, Nico Williams  wrote:
> On Thu, Mar 8, 2012 at 11:47 AM, Marc L. Allen
>  wrote:
>> But this is extremely inefficient as well.  Since each record is, in fact, 
>> an update, you're actually performing a delete/insert for each record, 
>> activating any recursive triggers you have as well.  On top of that, any 
>> UPDATE trigger would not fire.  Now, if it were an 'insert or update' it 
>> might better.
>
> Well, it's not inefficient in the same way as your UPDATE statement in
> your original post.  I do think that INSERT OR REPLACE should fire
> insert or update triggers, not delete and/or insert triggers -- I
> really want a pragma by which to request this.  And it might not be
> inefficient at all.

Specifically I want this behavior only when the replacing row has the
same primary key and/or rowid as the old one.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How efficient is this?

[Nico Williams]

On Thu, Mar 8, 2012 at 9:53 AM, Marc L. Allen
 wrote:
> I'm trying to do a multi-column update, adding values from another table.  
> Something like:
>
> UPDATE t1 SET a += (SELECT a FROM t2 WHERE t1.id = t2.id),
>                              b += (SELECT b FROM t2 WHERE t1.id = t2.id);

This particular query need not be very inefficient if the pages needed
to do the second sub-query are left in the cache from the first
sub-query...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How efficient is this?

[Nico Williams]

On Thu, Mar 8, 2012 at 11:47 AM, Marc L. Allen
 wrote:
> But this is extremely inefficient as well.  Since each record is, in fact, an 
> update, you're actually performing a delete/insert for each record, 
> activating any recursive triggers you have as well.  On top of that, any 
> UPDATE trigger would not fire.  Now, if it were an 'insert or update' it 
> might better.

Well, it's not inefficient in the same way as your UPDATE statement in
your original post.  I do think that INSERT OR REPLACE should fire
insert or update triggers, not delete and/or insert triggers -- I
really want a pragma by which to request this.  And it might not be
inefficient at all.

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

Re: [sqlite] How efficient is this?

[Nico Williams]

On Thu, Mar 8, 2012 at 11:22 AM, Nico Williams  wrote:
> I use this all the time as it's the only way in SQLite3 to do UPDATEs
> with JOINs.  I use it with an INTEGER PRIMARY KEY column.  SQLite3
> tables always have a rowid somehow that's suitable for this, but it's
> not a good assumption to bake into an application -- the application
> should make sure that there's an INTEGER PRIMARY KEY or other suitable
> PRIMARY KEY or UNIQUE columns.

I should clarify that this idiom works for: a) using a JOIN as a
source of some of the values to update, b), if the table/view has an
INSTEAD OF INSERT trigger then that can turn the insert of a row in a
single table/view into an insert/update into multiple tables/views.

I find this a very useful pattern.

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

Re: [sqlite] How efficient is this?

[Marc L. Allen]

> > insert or replace into t1(id, a, b, otherColumns) select t1.id, t1.a
> +
> > t2.a, t1.b + t2.b, t1.otherColumns from t1 left join t2 on (t1.id =
> > t2.id);
> >
> > (this assumes id column is declared unique, or primary key; replace
> > with ROWID otherwise). I suspect this cure might be worse than the
> disease.
> 
> I use this all the time as it's the only way in SQLite3 to do UPDATEs
> with JOINs.  I use it with an INTEGER PRIMARY KEY column.  SQLite3
> tables always have a rowid somehow that's suitable for this, but it's
> not a good assumption to bake into an application -- the application
> should make sure that there's an INTEGER PRIMARY KEY or other suitable
> PRIMARY KEY or UNIQUE columns.

But this is extremely inefficient as well.  Since each record is, in fact, an 
update, you're actually performing a delete/insert for each record, activating 
any recursive triggers you have as well.  On top of that, any UPDATE trigger 
would not fire.  Now, if it were an 'insert or update' it might better.

If the syntax cannot be modified to add either UPDATE FROM or the Oracle syntax 
(which I like very much), then I wonder if it would be possible for the parser 
to detect when expressions will return the same rows.  I know it couldn't work 
all the time, but could it handle the simple case where each expression is 
SELECT column FROM/JOIN/WHERE and the FROM/JOIN/WHERE is the same for each?

Marc

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

Re: [sqlite] SIGBUS error in case of disk full with WAL mode

[Pavel Ivanov]

> Question:  Does anybody know of a better way to get memory shared among
> processes other than to create a fake file and mmap() it?  Are there some
> magic options to mmap() (perhaps Linux-only options) that prevent it from
> actually writing to disk?

Why don't you use shm_open() instead of a real file? I'm not sure
though how it behaves with chroot jail.


Pavel


On Thu, Mar 8, 2012 at 11:35 AM, Richard Hipp  wrote:
> On Thu, Mar 8, 2012 at 10:58 AM, Yongiljang  wrote:
>
>> Dear all,
>> I'm an android developer in charge of sqlite database.
>>
>> Some days ago, I'd got a SIGBUS error from sqlite when there is no space
>> in current partition and WAL journal mode is used.
>> This error was occurred from memset function in libc that was called by
>> libsqlite and debugging information shows shm file was related to this
>> issue.
>>
>> Following sequence shows how to generate this error.
>>
>> 1) Make disk full
>> 2) Reboot device - wal and shm files are remained in some applications
>> folder
>> 3) Do 1) ~ 2) until free space remained under 32KB
>> 4) SIGBUS error is occurred randomly
>>
>> I'd tried to solve this problem and guessed following scenario.
>>
>> 1) shm file is truncated to zero size by calling robust_truncate function
>> when a new connection is opened in unixOpenSharedMemory
>> 2) shm file is extended to 32KB by calling robust_truncate function in
>> unixShmMap
>> 3) mmap function is called with 32KB length
>>
>> In my guess, problem is occurred from robust_truncate or mmap functions,
>> because of they didn't returned error code whether shm file is extended to
>> 32KB or not on disk full status.
>> robust_truncate and mmap may caused illegal memset operation because of
>> shm file actually doesn't have 32KB size, it may less than 32KB.
>> Interesting point is when I tested it with above scenario, there was a shm
>> file that has 32KB size.
>> It is impossible because of there was no space to make 32KB sized file in
>> current partiton.
>>
>
> We do not want to really make a file.  The purpose of the -shm file is
> merely to give a name to a block of memory that various processes accessing
> the database can share between themselves using mmap().  Ideally, the
> content of the -shm file remains the OS page cache and is never written to
> disk.
>
> Can you please try this experiment for us:  Beginning with a standard
> SQLite build (without your patches) recompile using
> -DSQLITE_SHM_DIRECTORY="/dev/shm".  That option will cause the shared
> memory file to be created in the /dev/shm directory rather than in the same
> directory as the database.  Since /dev/shm is not backed by disk (or flash)
> the problem should be solved.
>
> FWIW:  We looked at always putting the -shm files in a special directory
> like this when we were first designing WAL.  But we noticed that design
> fails if two programs in different chroot jails try to access the database
> at the same time, and so we switched to the current design of using the
> -shm file in the same directory as the database.
>
> Question:  Does anybody know of a better way to get memory shared among
> processes other than to create a fake file and mmap() it?  Are there some
> magic options to mmap() (perhaps Linux-only options) that prevent it from
> actually writing to disk?
>
>
>>
>> Whatever, I'd changed unixOpenSharedMemory to solve it.
>>
>> 1) make a shm file and write null data until 32KB if this file doesn't
>> exists
>>    - return SQLITE_FULL error when write operation is failed
>> 2) write null data until 32KB if this file exists and got write lock
>> instead of calling truncate function to shrink shm file to zero size
>>   - return same error code when it failed
>>
>> By changed source, I could solve this problem.
>> Sqlite returns SQLITE_FULL errors only without SIGBUS core dump.
>>
>> However, this instant code changing may not be a good solution.
>> I wish to get better comment or source patches from here.
>>
>> Thank you for reading this including my poor english. :)
>>
>> Best wishes,
>> Jang.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SIGBUS error in case of disk full with WAL mode

[Black, Michael (IS)]

I guess you could go the IPC methods described in the same reference?  Been a 
long time since I've used those.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Thursday, March 08, 2012 11:03 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] SIGBUS error in case of disk full with WAL mode

On Thu, Mar 8, 2012 at 11:45 AM, Black, Michael (IS)  wrote:

> Looks like this should work...
>

No, it won't work.  The memory has to be shared in common among all
connections to a particular database.  If two separate processes connection
to the same database, they must get the same block of shared memory.  If
they connect to different databases, they must get different blocks of
shared memory.



>
>
>
> From http://www.cs.cf.ac.uk/Dave/C/node27.html
>
>
>
> The following code fragment demonstrates a use of this to create a block
> of scratch storage in a program, at an address that the system chooses.:
>
> int fd;
> caddr_t result;
> if ((fd = open("/dev/zero", O_RDWR)) == -1)
>   return ((caddr_t)-1);
>
> result = mmap(0, len, PROT_READ|PROT_WRITE, MAP_SHARED, fd, 0);
> (void) close(fd);
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Richard Hipp [d...@sqlite.org]
> Sent: Thursday, March 08, 2012 10:35 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] SIGBUS error in case of disk full with WAL mode
>
> On Thu, Mar 8, 2012 at 10:58 AM, Yongiljang  wrote:
>
> > Dear all,
> > I'm an android developer in charge of sqlite database.
> >
> > Some days ago, I'd got a SIGBUS error from sqlite when there is no space
> > in current partition and WAL journal mode is used.
> > This error was occurred from memset function in libc that was called by
> > libsqlite and debugging information shows shm file was related to this
> > issue.
> >
> > Following sequence shows how to generate this error.
> >
> > 1) Make disk full
> > 2) Reboot device - wal and shm files are remained in some applications
> > folder
> > 3) Do 1) ~ 2) until free space remained under 32KB
> > 4) SIGBUS error is occurred randomly
> >
> > I'd tried to solve this problem and guessed following scenario.
> >
> > 1) shm file is truncated to zero size by calling robust_truncate function
> > when a new connection is opened in unixOpenSharedMemory
> > 2) shm file is extended to 32KB by calling robust_truncate function in
> > unixShmMap
> > 3) mmap function is called with 32KB length
> >
> > In my guess, problem is occurred from robust_truncate or mmap functions,
> > because of they didn't returned error code whether shm file is extended
> to
> > 32KB or not on disk full status.
> > robust_truncate and mmap may caused illegal memset operation because of
> > shm file actually doesn't have 32KB size, it may less than 32KB.
> > Interesting point is when I tested it with above scenario, there was a
> shm
> > file that has 32KB size.
> > It is impossible because of there was no space to make 32KB sized file in
> > current partiton.
> >
>
> We do not want to really make a file.  The purpose of the -shm file is
> merely to give a name to a block of memory that various processes accessing
> the database can share between themselves using mmap().  Ideally, the
> content of the -shm file remains the OS page cache and is never written to
> disk.
>
> Can you please try this experiment for us:  Beginning with a standard
> SQLite build (without your patches) recompile using
> -DSQLITE_SHM_DIRECTORY="/dev/shm".  That option will cause the shared
> memory file to be created in the /dev/shm directory rather than in the same
> directory as the database.  Since /dev/shm is not backed by disk (or flash)
> the problem should be solved.
>
> FWIW:  We looked at always putting the -shm files in a special directory
> like this when we were first designing WAL.  But we noticed that design
> fails if two programs in different chroot jails try to access the database
> at the same time, and so we switched to the current design of using the
> -shm file in the same directory as the database.
>
> Question:  Does anybody know of a better way to get memory shared among
> processes other than to create a fake file and mmap() it?  Are there some
> magic options to mmap() (perhaps Linux-only options) that prevent it from
> actually writing to disk?
>
>
> >
> > Whatever, I'd changed unixOpenSharedMemory to solve it.
> >
> > 1) make a shm file and write null data until 32KB if this file doesn't
> > exists
> >- 

Re: [sqlite] How efficient is this?

[Nico Williams]

On Thu, Mar 8, 2012 at 10:43 AM, Igor Tandetnik  wrote:
> Unfortunately, there are no good alternatives for this statement. I wish
> SQLite supported UPDATE ... FROM syntax (non-standard, used by some other
> DBMS). Here's one not-so-good alternative:
>
> insert or replace into t1(id, a, b, otherColumns)
> select t1.id, t1.a + t2.a, t1.b + t2.b, t1.otherColumns
> from t1 left join t2 on (t1.id = t2.id);
>
> (this assumes id column is declared unique, or primary key; replace with
> ROWID otherwise). I suspect this cure might be worse than the disease.

I use this all the time as it's the only way in SQLite3 to do UPDATEs
with JOINs.  I use it with an INTEGER PRIMARY KEY column.  SQLite3
tables always have a rowid somehow that's suitable for this, but it's
not a good assumption to bake into an application -- the application
should make sure that there's an INTEGER PRIMARY KEY or other suitable
PRIMARY KEY or UNIQUE columns.

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

Re: [sqlite] C# How to getorun autoincrement value

[Joe D]

On 2012-03-06 15:20, Pawl wrote:


Hi,

I don't know how to make this EASY thing (for me side) I found only this,
but they said it is solverd, but it isn;t
http://stackoverflow.com/questions/3828098/solved-how-to-return-autoincrement-value-in-insert-query-in-sqlite

I don;t know what I get exception Not supported when I tried to setup
ParameterDirection another then input!

I add this "sqa.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;" before. But without any result!


Try this.

SQLiteCommand command = new SQLiteCommand("INSERT INTO TABLE (FIELD1, 
FIELD2) VALUES (:FIELD1, :FIELD2); SELECT LAST_INSERT_ROWID();");


... append parameters ...

int nextValue = command.ExecuteScalar();

Joe D


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

Re: [sqlite] SIGBUS error in case of disk full with WAL mode

[Richard Hipp]

On Thu, Mar 8, 2012 at 11:45 AM, Black, Michael (IS)  wrote:

> Looks like this should work...
>

No, it won't work.  The memory has to be shared in common among all
connections to a particular database.  If two separate processes connection
to the same database, they must get the same block of shared memory.  If
they connect to different databases, they must get different blocks of
shared memory.



>
>
>
> From http://www.cs.cf.ac.uk/Dave/C/node27.html
>
>
>
> The following code fragment demonstrates a use of this to create a block
> of scratch storage in a program, at an address that the system chooses.:
>
> int fd;
> caddr_t result;
> if ((fd = open("/dev/zero", O_RDWR)) == -1)
>   return ((caddr_t)-1);
>
> result = mmap(0, len, PROT_READ|PROT_WRITE, MAP_SHARED, fd, 0);
> (void) close(fd);
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Richard Hipp [d...@sqlite.org]
> Sent: Thursday, March 08, 2012 10:35 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] SIGBUS error in case of disk full with WAL mode
>
> On Thu, Mar 8, 2012 at 10:58 AM, Yongiljang  wrote:
>
> > Dear all,
> > I'm an android developer in charge of sqlite database.
> >
> > Some days ago, I'd got a SIGBUS error from sqlite when there is no space
> > in current partition and WAL journal mode is used.
> > This error was occurred from memset function in libc that was called by
> > libsqlite and debugging information shows shm file was related to this
> > issue.
> >
> > Following sequence shows how to generate this error.
> >
> > 1) Make disk full
> > 2) Reboot device - wal and shm files are remained in some applications
> > folder
> > 3) Do 1) ~ 2) until free space remained under 32KB
> > 4) SIGBUS error is occurred randomly
> >
> > I'd tried to solve this problem and guessed following scenario.
> >
> > 1) shm file is truncated to zero size by calling robust_truncate function
> > when a new connection is opened in unixOpenSharedMemory
> > 2) shm file is extended to 32KB by calling robust_truncate function in
> > unixShmMap
> > 3) mmap function is called with 32KB length
> >
> > In my guess, problem is occurred from robust_truncate or mmap functions,
> > because of they didn't returned error code whether shm file is extended
> to
> > 32KB or not on disk full status.
> > robust_truncate and mmap may caused illegal memset operation because of
> > shm file actually doesn't have 32KB size, it may less than 32KB.
> > Interesting point is when I tested it with above scenario, there was a
> shm
> > file that has 32KB size.
> > It is impossible because of there was no space to make 32KB sized file in
> > current partiton.
> >
>
> We do not want to really make a file.  The purpose of the -shm file is
> merely to give a name to a block of memory that various processes accessing
> the database can share between themselves using mmap().  Ideally, the
> content of the -shm file remains the OS page cache and is never written to
> disk.
>
> Can you please try this experiment for us:  Beginning with a standard
> SQLite build (without your patches) recompile using
> -DSQLITE_SHM_DIRECTORY="/dev/shm".  That option will cause the shared
> memory file to be created in the /dev/shm directory rather than in the same
> directory as the database.  Since /dev/shm is not backed by disk (or flash)
> the problem should be solved.
>
> FWIW:  We looked at always putting the -shm files in a special directory
> like this when we were first designing WAL.  But we noticed that design
> fails if two programs in different chroot jails try to access the database
> at the same time, and so we switched to the current design of using the
> -shm file in the same directory as the database.
>
> Question:  Does anybody know of a better way to get memory shared among
> processes other than to create a fake file and mmap() it?  Are there some
> magic options to mmap() (perhaps Linux-only options) that prevent it from
> actually writing to disk?
>
>
> >
> > Whatever, I'd changed unixOpenSharedMemory to solve it.
> >
> > 1) make a shm file and write null data until 32KB if this file doesn't
> > exists
> >- return SQLITE_FULL error when write operation is failed
> > 2) write null data until 32KB if this file exists and got write lock
> > instead of calling truncate function to shrink shm file to zero size
> >   - return same error code when it failed
> >
> > By changed source, I could solve this problem.
> > Sqlite returns SQLITE_FULL errors only without SIGBUS core dump.
> >
> > However, this instant code changing may not be a good solution.
> > I wish to get better comment or source patches from here.
> >
> > Thank you for reading this including my poor english. :)
> >

Re: [sqlite] How efficient is this?

[Igor Tandetnik]

On 3/8/2012 10:53 AM, Marc L. Allen wrote:

I'm trying to do a multi-column update, adding values from another table.  
Something like:

UPDATE t1 SET a += (SELECT a FROM t2 WHERE t1.id = t2.id),
   b += (SELECT b FROM t2 WHERE t1.id = t2.id);

Note that both a and b are updated from the a and b of the same record.  (Or 
it's supposed to be that way, ignoring any syntax errors from above.)
Does SQLite really do two lookups?  Is there a more efficient way of doing this?


I believe SQLite does technically perform two lookups, but the second 
one is likely to be must faster than the first as the row is already 
present in the cache.


Unfortunately, there are no good alternatives for this statement. I wish 
SQLite supported UPDATE ... FROM syntax (non-standard, used by some 
other DBMS). Here's one not-so-good alternative:


insert or replace into t1(id, a, b, otherColumns)
select t1.id, t1.a + t2.a, t1.b + t2.b, t1.otherColumns
from t1 left join t2 on (t1.id = t2.id);

(this assumes id column is declared unique, or primary key; replace with 
ROWID otherwise). I suspect this cure might be worse than the disease.

--
Igor Tandetnik

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

Re: [sqlite] How efficient is this?

[Marc L. Allen]

I don't think so...

That's not shown in the syntax diagram.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Sebastian Bermudez
> Sent: Thursday, March 08, 2012 11:43 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How efficient is this?
> 
> 
> you can do:
> update t1 set (a,b)=(select a, b from t2 where t1.id=t2.id)
> 
> just like oracle do
> 
> 
> 
> 
> --- On Thu, 3/8/12, Marc L. Allen  wrote:
> 
> > From: Marc L. Allen 
> > Subject: [sqlite] How efficient is this?
> > To: "sqlite-users@sqlite.org" 
> > Date: Thursday, March 8, 2012, 12:53 PM I'm trying to do a
> > multi-column update, adding values from another table.  Something
> > like:
> >
> > UPDATE t1 SET a += (SELECT a FROM t2 WHERE t1.id = t2.id),
> >
> >               b +=
> > (SELECT b FROM t2 WHERE t1.id = t2.id);
> >
> > Note that both a and b are updated from the a and b of the same
> > record.  (Or it's supposed to be that way, ignoring any syntax errors
> > from above.) Does SQLite really do two lookups?  Is there a more
> > efficient way of doing this?
> >
> > Thanks,
> >
> > Marc
> >
> > --
> > **
> > *
> >              *
> >
> >                 *
> > * Marc L. Allen
> >    *  "... so many things are
> >        *
> > *
> >              *
> > possible just as long as you    *
> > * Outsite Networks, Inc.      *  don't know they're impossible." *
> > * (757) 853-3000 #215
> >    *
> >
> >       *
> > *
> >              *
> >
> >                 *
> > * mlal...@outsitenetworks.com
> > *       -- The Phantom
> > Tollbooth   *
> > *
> >              *
> >
> >                 *
> > **
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How efficient is this?

[Igor Tandetnik]

On 3/8/2012 11:42 AM, Sebastian Bermudez wrote:

you can do:
update t1 set (a,b)=(select a, b from t2 where t1.id=t2.id)

just like oracle do


You can't - SQLite doesn't accept this syntax.
--
Igor Tandetnik

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

Re: [sqlite] SIGBUS error in case of disk full with WAL mode

[Black, Michael (IS)]

Looks like this should work...



>From http://www.cs.cf.ac.uk/Dave/C/node27.html



The following code fragment demonstrates a use of this to create a block of 
scratch storage in a program, at an address that the system chooses.:

int fd;
caddr_t result;
if ((fd = open("/dev/zero", O_RDWR)) == -1)
   return ((caddr_t)-1);

result = mmap(0, len, PROT_READ|PROT_WRITE, MAP_SHARED, fd, 0);
(void) close(fd);




Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Thursday, March 08, 2012 10:35 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] SIGBUS error in case of disk full with WAL mode

On Thu, Mar 8, 2012 at 10:58 AM, Yongiljang  wrote:

> Dear all,
> I'm an android developer in charge of sqlite database.
>
> Some days ago, I'd got a SIGBUS error from sqlite when there is no space
> in current partition and WAL journal mode is used.
> This error was occurred from memset function in libc that was called by
> libsqlite and debugging information shows shm file was related to this
> issue.
>
> Following sequence shows how to generate this error.
>
> 1) Make disk full
> 2) Reboot device - wal and shm files are remained in some applications
> folder
> 3) Do 1) ~ 2) until free space remained under 32KB
> 4) SIGBUS error is occurred randomly
>
> I'd tried to solve this problem and guessed following scenario.
>
> 1) shm file is truncated to zero size by calling robust_truncate function
> when a new connection is opened in unixOpenSharedMemory
> 2) shm file is extended to 32KB by calling robust_truncate function in
> unixShmMap
> 3) mmap function is called with 32KB length
>
> In my guess, problem is occurred from robust_truncate or mmap functions,
> because of they didn't returned error code whether shm file is extended to
> 32KB or not on disk full status.
> robust_truncate and mmap may caused illegal memset operation because of
> shm file actually doesn't have 32KB size, it may less than 32KB.
> Interesting point is when I tested it with above scenario, there was a shm
> file that has 32KB size.
> It is impossible because of there was no space to make 32KB sized file in
> current partiton.
>

We do not want to really make a file.  The purpose of the -shm file is
merely to give a name to a block of memory that various processes accessing
the database can share between themselves using mmap().  Ideally, the
content of the -shm file remains the OS page cache and is never written to
disk.

Can you please try this experiment for us:  Beginning with a standard
SQLite build (without your patches) recompile using
-DSQLITE_SHM_DIRECTORY="/dev/shm".  That option will cause the shared
memory file to be created in the /dev/shm directory rather than in the same
directory as the database.  Since /dev/shm is not backed by disk (or flash)
the problem should be solved.

FWIW:  We looked at always putting the -shm files in a special directory
like this when we were first designing WAL.  But we noticed that design
fails if two programs in different chroot jails try to access the database
at the same time, and so we switched to the current design of using the
-shm file in the same directory as the database.

Question:  Does anybody know of a better way to get memory shared among
processes other than to create a fake file and mmap() it?  Are there some
magic options to mmap() (perhaps Linux-only options) that prevent it from
actually writing to disk?


>
> Whatever, I'd changed unixOpenSharedMemory to solve it.
>
> 1) make a shm file and write null data until 32KB if this file doesn't
> exists
>- return SQLITE_FULL error when write operation is failed
> 2) write null data until 32KB if this file exists and got write lock
> instead of calling truncate function to shrink shm file to zero size
>   - return same error code when it failed
>
> By changed source, I could solve this problem.
> Sqlite returns SQLITE_FULL errors only without SIGBUS core dump.
>
> However, this instant code changing may not be a good solution.
> I wish to get better comment or source patches from here.
>
> Thank you for reading this including my poor english. :)
>
> Best wishes,
> Jang.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] How efficient is this?

[Sebastian Bermudez]

you can do:
update t1 set (a,b)=(select a, b from t2 where t1.id=t2.id)

just like oracle do




--- On Thu, 3/8/12, Marc L. Allen  wrote:

> From: Marc L. Allen 
> Subject: [sqlite] How efficient is this?
> To: "sqlite-users@sqlite.org" 
> Date: Thursday, March 8, 2012, 12:53 PM
> I'm trying to do a multi-column
> update, adding values from another table.  Something
> like:
> 
> UPDATE t1 SET a += (SELECT a FROM t2 WHERE t1.id = t2.id),
>                
>               b +=
> (SELECT b FROM t2 WHERE t1.id = t2.id);
> 
> Note that both a and b are updated from the a and b of the
> same record.  (Or it's supposed to be that way,
> ignoring any syntax errors from above.)
> Does SQLite really do two lookups?  Is there a more
> efficient way of doing this?
> 
> Thanks,
> 
> Marc
> 
> --
> **
> *               
>              * 
>                
>                 *
> * Marc L. Allen           
>    *  "... so many things are 
>        *
> *               
>              * 
> possible just as long as you    *
> * Outsite Networks, Inc.      *  don't
> know they're impossible." *
> * (757) 853-3000 #215     
>    *           
>                
>       *
> *               
>              * 
>                
>                 *
> * mlal...@outsitenetworks.com
> *       -- The Phantom
> Tollbooth   *
> *               
>              * 
>                
>                 *
> **
> ___
> 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] SIGBUS error in case of disk full with WAL mode

[Richard Hipp]

On Thu, Mar 8, 2012 at 10:58 AM, Yongiljang  wrote:

> Dear all,
> I'm an android developer in charge of sqlite database.
>
> Some days ago, I'd got a SIGBUS error from sqlite when there is no space
> in current partition and WAL journal mode is used.
> This error was occurred from memset function in libc that was called by
> libsqlite and debugging information shows shm file was related to this
> issue.
>
> Following sequence shows how to generate this error.
>
> 1) Make disk full
> 2) Reboot device - wal and shm files are remained in some applications
> folder
> 3) Do 1) ~ 2) until free space remained under 32KB
> 4) SIGBUS error is occurred randomly
>
> I'd tried to solve this problem and guessed following scenario.
>
> 1) shm file is truncated to zero size by calling robust_truncate function
> when a new connection is opened in unixOpenSharedMemory
> 2) shm file is extended to 32KB by calling robust_truncate function in
> unixShmMap
> 3) mmap function is called with 32KB length
>
> In my guess, problem is occurred from robust_truncate or mmap functions,
> because of they didn't returned error code whether shm file is extended to
> 32KB or not on disk full status.
> robust_truncate and mmap may caused illegal memset operation because of
> shm file actually doesn't have 32KB size, it may less than 32KB.
> Interesting point is when I tested it with above scenario, there was a shm
> file that has 32KB size.
> It is impossible because of there was no space to make 32KB sized file in
> current partiton.
>

We do not want to really make a file.  The purpose of the -shm file is
merely to give a name to a block of memory that various processes accessing
the database can share between themselves using mmap().  Ideally, the
content of the -shm file remains the OS page cache and is never written to
disk.

Can you please try this experiment for us:  Beginning with a standard
SQLite build (without your patches) recompile using
-DSQLITE_SHM_DIRECTORY="/dev/shm".  That option will cause the shared
memory file to be created in the /dev/shm directory rather than in the same
directory as the database.  Since /dev/shm is not backed by disk (or flash)
the problem should be solved.

FWIW:  We looked at always putting the -shm files in a special directory
like this when we were first designing WAL.  But we noticed that design
fails if two programs in different chroot jails try to access the database
at the same time, and so we switched to the current design of using the
-shm file in the same directory as the database.

Question:  Does anybody know of a better way to get memory shared among
processes other than to create a fake file and mmap() it?  Are there some
magic options to mmap() (perhaps Linux-only options) that prevent it from
actually writing to disk?


>
> Whatever, I'd changed unixOpenSharedMemory to solve it.
>
> 1) make a shm file and write null data until 32KB if this file doesn't
> exists
>- return SQLITE_FULL error when write operation is failed
> 2) write null data until 32KB if this file exists and got write lock
> instead of calling truncate function to shrink shm file to zero size
>   - return same error code when it failed
>
> By changed source, I could solve this problem.
> Sqlite returns SQLITE_FULL errors only without SIGBUS core dump.
>
> However, this instant code changing may not be a good solution.
> I wish to get better comment or source patches from here.
>
> Thank you for reading this including my poor english. :)
>
> Best wishes,
> Jang.
> ___
> 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] Efficient random sampling in a large table using builtinfunctions.

[Nico Williams]

On Thu, Mar 8, 2012 at 8:02 AM, Igor Tandetnik  wrote:
> You can implement this algorithm in your application's code, if you are so 
> inclined. Just prepare a query lile
>
> select * from the_table where rowid=?;
>
> then run it N times, binding different random numbers to the parameter. Of 
> course you still need to deal with the two issues I mentioned above (perhaps 
> you know that in your particular application, rowids are always sequential).

There's probably no good way to do this, sadly.  The distribution of
results with your approach depends on the distribution of rowids.
Suppose the btree interfaces in SQLite3 provided an interface for
picking a random row from a random page in a table (meaning, use
random numbers to walk down the b-tree)... but then the distribution
of results would depend on the distribution of row sizes and on the
distribution of unallocated space in b-tree pages.

But at least with your approach the OP can make sure that all rowids
are contiguous, then it works out.

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

[sqlite] SIGBUS error in case of disk full with WAL mode

[Yongiljang]

Dear all,
I'm an android developer in charge of sqlite database.

Some days ago, I'd got a SIGBUS error from sqlite when there is no space in 
current partition and WAL journal mode is used.
This error was occurred from memset function in libc that was called by 
libsqlite and debugging information shows shm file was related to this issue.

Following sequence shows how to generate this error.

1) Make disk full
2) Reboot device - wal and shm files are remained in some applications folder
3) Do 1) ~ 2) until free space remained under 32KB
4) SIGBUS error is occurred randomly

I'd tried to solve this problem and guessed following scenario.

1) shm file is truncated to zero size by calling robust_truncate function when 
a new connection is opened in unixOpenSharedMemory
2) shm file is extended to 32KB by calling robust_truncate function in 
unixShmMap
3) mmap function is called with 32KB length

In my guess, problem is occurred from robust_truncate or mmap functions, 
because of they didn't returned error code whether shm file is extended to 32KB 
or not on disk full status.
robust_truncate and mmap may caused illegal memset operation because of shm 
file actually doesn't have 32KB size, it may less than 32KB.
Interesting point is when I tested it with above scenario, there was a shm file 
that has 32KB size.
It is impossible because of there was no space to make 32KB sized file in 
current partiton.

Whatever, I'd changed unixOpenSharedMemory to solve it.

1) make a shm file and write null data until 32KB if this file doesn't exists
- return SQLITE_FULL error when write operation is failed
2) write null data until 32KB if this file exists and got write lock instead of 
calling truncate function to shrink shm file to zero size
   - return same error code when it failed

By changed source, I could solve this problem.
Sqlite returns SQLITE_FULL errors only without SIGBUS core dump.

However, this instant code changing may not be a good solution.
I wish to get better comment or source patches from here.

Thank you for reading this including my poor english. :)

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

[sqlite] How efficient is this?

[Marc L. Allen]

I'm trying to do a multi-column update, adding values from another table.  
Something like:

UPDATE t1 SET a += (SELECT a FROM t2 WHERE t1.id = t2.id),
  b += (SELECT b FROM t2 WHERE t1.id = t2.id);

Note that both a and b are updated from the a and b of the same record.  (Or 
it's supposed to be that way, ignoring any syntax errors from above.)
Does SQLite really do two lookups?  Is there a more efficient way of doing this?

Thanks,

Marc

--
**
* *  *
* Marc L. Allen   *  "... so many things are *
* *  possible just as long as you*
* Outsite Networks, Inc.  *  don't know they're impossible." *
* (757) 853-3000 #215 *  *
* *  *
* mlal...@outsitenetworks.com *   -- The Phantom Tollbooth   *
* *  *
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient random sampling in a large table using builtin functions.

[Black, Michael (IS)]

random() in the core functions still needs a link to that I would think.



And the way I read it this means that you don't ever (for all practical 
purposes) get a repeating sequence.  Makes testing kind of hard unless you use 
the C api to see it yourself.



Could random() be modified to pass in a seed?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Davies [simon.james.dav...@gmail.com]
Sent: Thursday, March 08, 2012 8:47 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Efficient random sampling in a large table using 
builtin functions.

On 8 March 2012 14:37, Black, Michael (IS)  wrote:
> Glad to know thatcould that possibly be mentioned in the random() notes 
> on the core functions?  Thought that is (apparently) a C function and not SQL 
> accessible?

Core SQL random() and randomblob() functions use sqlite3_randomness(),
according to the page quoted.

>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Simon Davies [simon.james.dav...@gmail.com]
> Sent: Thursday, March 08, 2012 8:33 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Efficient random sampling in a large table using 
> builtin functions.
>
> On 8 March 2012 14:20, Black, Michael (IS)  wrote:
>> You don't say what language you are working in.  IN C++ I would just declare 
>> a "set" and put random row numbers in it until I had enough.  Then use that 
>> set to build the SQL.
>>
>> SQLite's random() doesn't have a seed function so you don't really get very 
>> random numbers from run-to-run and have no good way of controlling it that I 
>> can find in the docs.  You want to use your language's random function if 
>> you want anything close to real randomness.
>
> http://www.sqlite.org/c3ref/randomness.html claims high-quality PRNG
>
>>
>> Hopefully your language has a similar data structure you can use.
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> Advanced Analytics Directorate
>>
>> Advanced GEOINT Solutions Operating Unit
>>
>> Northrop Grumman Information Systems
>>
> ___
> 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] Efficient random sampling in a large table using builtin functions.

[Simon Davies]

On 8 March 2012 14:37, Black, Michael (IS)  wrote:
> Glad to know thatcould that possibly be mentioned in the random() notes 
> on the core functions?  Thought that is (apparently) a C function and not SQL 
> accessible?

Core SQL random() and randomblob() functions use sqlite3_randomness(),
according to the page quoted.

>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Simon Davies [simon.james.dav...@gmail.com]
> Sent: Thursday, March 08, 2012 8:33 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Efficient random sampling in a large table using 
> builtin functions.
>
> On 8 March 2012 14:20, Black, Michael (IS)  wrote:
>> You don't say what language you are working in.  IN C++ I would just declare 
>> a "set" and put random row numbers in it until I had enough.  Then use that 
>> set to build the SQL.
>>
>> SQLite's random() doesn't have a seed function so you don't really get very 
>> random numbers from run-to-run and have no good way of controlling it that I 
>> can find in the docs.  You want to use your language's random function if 
>> you want anything close to real randomness.
>
> http://www.sqlite.org/c3ref/randomness.html claims high-quality PRNG
>
>>
>> Hopefully your language has a similar data structure you can use.
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> Advanced Analytics Directorate
>>
>> Advanced GEOINT Solutions Operating Unit
>>
>> Northrop Grumman Information Systems
>>
> ___
> 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] Efficient random sampling in a large table using builtin functions.

[Black, Michael (IS)]

Glad to know thatcould that possibly be mentioned in the random() notes on 
the core functions?  Thought that is (apparently) a C function and not SQL 
accessible?







Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Davies [simon.james.dav...@gmail.com]
Sent: Thursday, March 08, 2012 8:33 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Efficient random sampling in a large table using 
builtin functions.

On 8 March 2012 14:20, Black, Michael (IS)  wrote:
> You don't say what language you are working in.  IN C++ I would just declare 
> a "set" and put random row numbers in it until I had enough.  Then use that 
> set to build the SQL.
>
> SQLite's random() doesn't have a seed function so you don't really get very 
> random numbers from run-to-run and have no good way of controlling it that I 
> can find in the docs.  You want to use your language's random function if you 
> want anything close to real randomness.

http://www.sqlite.org/c3ref/randomness.html claims high-quality PRNG

>
> Hopefully your language has a similar data structure you can use.
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
___
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] Bug? Subtract Two Sum-Values

[Markus Gehringer]

Here is the Testcase with exact the same Table and Data

All other Subtract-Results of the Sum-Values which i controlled are ok, only 
with kto=3020 its the wrong value

Markus 
___
Ihr WEB.DE Postfach immer dabei: die kostenlose WEB.DE Mail App für iPhone und 
Android.
https://produkte.web.de/freemail_mobile_startseite/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient random sampling in a large table using builtin functions.

[Simon Davies]

On 8 March 2012 14:20, Black, Michael (IS)  wrote:
> You don't say what language you are working in.  IN C++ I would just declare 
> a "set" and put random row numbers in it until I had enough.  Then use that 
> set to build the SQL.
>
> SQLite's random() doesn't have a seed function so you don't really get very 
> random numbers from run-to-run and have no good way of controlling it that I 
> can find in the docs.  You want to use your language's random function if you 
> want anything close to real randomness.

http://www.sqlite.org/c3ref/randomness.html claims high-quality PRNG

>
> Hopefully your language has a similar data structure you can use.
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient random sampling in a large table using builtin functions.

[Black, Michael (IS)]

You don't say what language you are working in.  IN C++ I would just declare a 
"set" and put random row numbers in it until I had enough.  Then use that set 
to build the SQL.



SQLite's random() doesn't have a seed function so you don't really get very 
random numbers from run-to-run and have no good way of controlling it that I 
can find in the docs.  You want to use your language's random function if you 
want anything close to real randomness.



Hopefully your language has a similar data structure you can use.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Benoit Mortgat [mort...@gmail.com]
Sent: Thursday, March 08, 2012 4:10 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Efficient random sampling in a large table using builtin 
functions.

I have a table with millions of records.

When I run a query with

ORDER BY random() LIMIT N;

the RANDOM() function is evaluated against all rows of my result set,
then sorting occurs, and as a result the query is slow.

In this case the query could be rewritten as:

 * Generate N, random, row numbers between 1 and (SELECT COUNT(*) FROM
   the_table_name). Maybe using remainder operator % and builtin ABS()
   and RANDOM() functions can help (see below)
 * SELECT FROM the_table WHERE rowid IN (those random numbers)

For the moment the most simple query I can think of is:

   SELECT *
 FROM my_table
WHERE rowid IN
( SELECT 1 + (ABS(r.x) % c.num_rows)
FROM (SELECT COUNT(*) AS num_rows
FROM my_table
 ) AS c
   CROSS JOIN
 (SELECT random() x
FROM my_table
   LIMIT N
 ) AS r
);

This can however return less than N rows if by chance two random numbers
have the same remainder modulo COUNT(*) FROM my_table.

Note that the generation of N random numbers is quick because there is
no ORDER BY involved that would require computation of as many random
numbers as there are rows in the original table.

This could maybe be optimized inside SQLite if those conditions are met:

 * random() hasn't been overridden with sqlite_create_function() nor
   sqlite_create_function_v2()
 * the user SELECTs FROM a table without joins and w/o WHERE conditions.

--
Benoit Mortgat
___
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] Efficient random sampling in a large table using builtinfunctions.

[Igor Tandetnik]

Benoit Mortgat  wrote:
> I have a table with millions of records.
> 
> When I run a query with
> 
>ORDER BY random() LIMIT N;
> 
> the RANDOM() function is evaluated against all rows of my result set,
> then sorting occurs, and as a result the query is slow.
> 
> In this case the query could be rewritten as:
> 
> * Generate N, random, row numbers between 1 and (SELECT COUNT(*) FROM
>   the_table_name). Maybe using remainder operator % and builtin ABS()
>   and RANDOM() functions can help (see below)
> * SELECT FROM the_table WHERE rowid IN (those random numbers)

That is not equivalent. First, duplicate random numbers may be generated. 
Second, rowid values are not necessarily sequential. Thus, you may end up with 
fewer than N rows.

You can implement this algorithm in your application's code, if you are so 
inclined. Just prepare a query lile

select * from the_table where rowid=?;

then run it N times, binding different random numbers to the parameter. Of 
course you still need to deal with the two issues I mentioned above (perhaps 
you know that in your particular application, rowids are always sequential).

> This can however return less than N rows if by chance two random numbers
> have the same remainder modulo COUNT(*) FROM my_table.

Precisely.
-- 
Igor Tandetnik

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

Re: [sqlite] What's the best way to organize this database?

[Simon Slavin]

On 7 Mar 2012, at 5:13pm, John Salerno <johnj...@gmail.com> wrote:

> On Mar 7, 7:33 am, Simon Slavin <slav...@bigfraud.org> wrote:
> 
>> I don't understand the purpose of the table you originally described.  If 
>> you are listing the same tracks again and again, listing different times it 
>> was played, then it would be useful to have three tables (artists, tracks, 
>> plays). If each track is listed just once then I don't think you need a 
>> separate table for tracks.
>> 
>> Having three tables would make it very easy to list some statistics you 
>> might not have originally planned on getting.  You could, for instance, very 
>> quickly list how many times each track had been played.  Having people type 
>> the full track name in every time would inevitably lead to them typing it 
>> slightly differently sometimes, which would make full reporting 
>> untrustworthy.
> 
> Do you mean have a table that contains just the artist names, with an
> ID for each; then another table with song names, an ID for each song,
> and the ID for the artist; and then another table with play dates and
> times, with the ID of the song connecting it? I'm trying to figure out
> how it will all be connected.

Yes, this would be the 'normalised' form.  Something like

artists: id, name, sortOrder
songs: id, artistID, title
plays: id, songID, playDate, playTime

When looking up things you would use JOIN a lot.  For instance, to list all the 
plays of a song

SELECT plays.playDate,plays.playTime FROM plays WHERE songID=345

To list everything you played on one day, in time order:

SELECT plays.playTime,artists.name,songs.title
FROM plays
JOIN songs ON songs.id=plays.songID
JOIN artists ON artists.name=songs.artistID
WHERE playDate = '20120308'
ORDER BY plays.playTime

Listing every song, and how many times it was played, in decreasing order of 
number of times it was played, is harder.  But see 'count(*)' in

http://www.sqlite.org/lang_aggfunc.html

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

Re: [sqlite] Bug? Subtract Two Sum-Values

[Richard Hipp]

On Thu, Mar 8, 2012 at 8:39 AM,  wrote:

> Hi,
> I have a problem with a Select which subtract two Sum-Values.
> Normally the correct value of my Select should be 0 but when ich run my
> Select Sqlite bring back as Result 9.09494
>

Do you have a test case that we can run?


>
> This is my Select:
> select kto,sum(neg-pos)  from
> (
> select kto1 kto,sum(betrag) pos,sum(0) neg from buchungen where kto1 =3020
> group by kto1
> union
> select kto2 kto,sum(0) pos, sum(betrag) neg from buchungen where kto2
> =3020 group by kto2
> )
> group by kto;
>
> If I Only Run the inner Select
> select kto1 kto,sum(betrag) pos,sum(0) neg from buchungen
> where kto1 =3020 group by kto1
> union
> select kto2 kto,sum(0) pos, sum(betrag) neg from buchungen
> where kto2 =3020 group by kto2
>
> Sqlite give me the correct values
> 3020|0 |7154.79
> 3020|7154.79   |0
> ___
> Ihr WEB.DE Postfach immer dabei: die kostenlose WEB.DE Mail App für
> iPhone und Android.
> https://produkte.web.de/freemail_mobile_startseite/
> ___
> 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] BUG REPORT: database disk image is malformed ---- Page 96 is never used

[Richard Hipp]

On Wed, Mar 7, 2012 at 9:30 AM, 跃峰 潘  wrote:

>
> hi, We had a problem with sqlite 3.7.3. And we had no idea to solve it
> or avoid it. So we write to you for reporting this bug.
> The problem is described as follow.1. We used sqlite 3.7.3 on suse-11
> linux to create a database called 'rofsagent.db', which had only one
> table.2. The schema is as follow:CREATE TABLE agent(  path
>  VARCHAR(256),  ipaddr  VARCHAR(16),
>  port  INTEGER,
>  get  VARCHAR(256),
>   set  VARCHAR(256),
>   time INTEGER,  type INTEGER)
> 3. After inserting about 250 records, the db file did not work. It
> reported 'database disk image is malformed'.
>

For information on the possible reasons why an SQLite databases go corrupt,
see http://www.sqlite.org/howtocorrupt.html


>
> 4. Using the command "PRAGMA quick_check;", the result is as:*** in
> database main ***Main freelist: 2nd reference to page 112On tree page 26
> cell 3: Rowid 14315 out of order (previous was 14320)On tree page 60 cell
> 1: Rowid 14007 out of order (previous was 14322)On tree page 61 cell 40:
> Rowid 14264 out of order (previous was 15276)On tree page 65 cell 3: Rowid
> 15280 out of order (max larger than parent max of 14264)On tree page 71
> cell 1: Rowid 14198 out of order (previous was 14345)On tree page 71 cell
> 2: Rowid 14345 out of order (min less than parent min of 15280)On tree page
> 115 cell 4: Rowid 14760 out of order (max larger than parent max of
> 14335)On tree page 31 cell 1: Rowid 14323 out of order (previous was
> 14339)On tree page 31 cell 4: Rowid 15023 out of order (previous was
> 15425)On tree page 31 cell 4: Rowid 14339 out of order (min less than
> parent min of 14760)On tree page 34 cell 3: Rowid 14960 out of order (min
> less than parent min of 15023)On page 87 at right child
>  : 2nd reference to page 87Page 57 is never usedPage 96 is never used
> 5. When we tried to use 'dump'  command to recover the data, some records
> lost.
> Looking forward to hear from you soon.
> Best wishes.
>
>
> ___
> 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] Bug? Subtract Two Sum-Values

[markusge]

Hi,
I have a problem with a Select which subtract two Sum-Values.
Normally the correct value of my Select should be 0 but when ich run my Select 
Sqlite bring back as Result 9.09494
 
This is my Select:
select kto,sum(neg-pos)  from
(
select kto1 kto,sum(betrag) pos,sum(0) neg from buchungen where kto1 =3020 
group by kto1
union
select kto2 kto,sum(0) pos, sum(betrag) neg from buchungen where kto2 =3020 
group by kto2
)
group by kto;
 
If I Only Run the inner Select
select kto1 kto,sum(betrag) pos,sum(0) neg from buchungen
where kto1 =3020 group by kto1
union
select kto2 kto,sum(0) pos, sum(betrag) neg from buchungen
where kto2 =3020 group by kto2
 
Sqlite give me the correct values
3020|    0 |7154.79
3020|    7154.79   |0
___
Ihr WEB.DE Postfach immer dabei: die kostenlose WEB.DE Mail App für iPhone und 
Android.
https://produkte.web.de/freemail_mobile_startseite/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI for SQLite

[Fabio Spadaro]

Il giorno 08 marzo 2012 14:13, gregorinator  ha
scritto:

> I've been happy with SQLite Studio:
>
> http://sqlitestudio.one.pl/
>
> One of the things I like about it is that it can do table edits not
> supported by SQLite's ALTER TABLE -- it automatically creates a new
> table and copies the existing data.  Saves me a lot of effort.  Other
> SQLite managers may be able to do this, too -- I don't know -- but
> after using SQLite Studio I would consider this a must-have feature.
>

You might try Sqlite Root  that can change the
tables by inserting / deleting / modifying fields in any position with the
possibility of keeping the data (via form "keep data" yes or no)
-- 
Fabio Spadaro

Try Sqlite Root a GUI Admin Tools for manage Sqlite Database:
www.sqliteroot.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] processes stuck on database locked

[Mikołaj Radwan]

Wow, this was actually quite obvious, I don't know how I missed that.

Thanks a lot,
Mikolaj

On 03/07/12 14:41, Pavel Ivanov wrote:

First your second process gets a SHARED lock on the database to read
it, then your first process gets RESERVED lock on the database to
indicate that it will change it. Then your second process tries to
promote its SHARED lock to RESERVED one, sees that RESERVED lock has
been already taken and can't proceed (returns SQLITE_BUSY). At this
point first process can't commit its transaction because there's
SHARED lock on it and second process can't proceed with its
transaction because there's RESERVED lock on it. To continue you have
to rollback transaction in the second process and start it over again.
Another option is to start IMMEDIATE transaction in the second process
to avoid this course of action altogether.


Pavel


2012/3/7 Mikołaj Radwan:

Hi all,

For a couple days now I've been trying to figure out this problem and
can't seem to manage.

I have two processes, both of which connect to the same SQLite database
and do the following:

process 1:

# opens database
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = PERSIST;
INSERT INTO sometable (somecolumns) VALUES (somevalues);
# closes database

process 2:

# opens database
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = PERSIST;
BEGIN DEFERRED TRANSACTION;
SELECT somecolumns FROM sometable WHERE someconditions;
DELETE FROM sometable WHERE someconditions;
COMMIT TRANSACTION;
# closes database

After a couple iterations both processes start getting the "database
locked" error and never work again unless one of them is killed.

The funny thing is that I tested this on two configurations. One is a
32-bit Debian box with libsqlite-dev 3.7.3-1 installed. The other is a
64-bit Gentoo box with sqlite 3.7.7.1. The problem only arises on the
first one. When I noticed it I looked through the version history but
have not seen any mention of a bug like that being solved.

Any ideas?

Thanks in advance,
Mikolaj
___
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] What's the best way to organize this database?

[John Salerno]

On Mar 7, 7:33 am, Simon Slavin  wrote:

> I don't understand the purpose of the table you originally described.  If you 
> are listing the same tracks again and again, listing different times it was 
> played, then it would be useful to have three tables (artists, tracks, 
> plays). If each track is listed just once then I don't think you need a 
> separate table for tracks.
>
> Having three tables would make it very easy to list some statistics you might 
> not have originally planned on getting.  You could, for instance, very 
> quickly list how many times each track had been played.  Having people type 
> the full track name in every time would inevitably lead to them typing it 
> slightly differently sometimes, which would make full reporting untrustworthy.

Do you mean have a table that contains just the artist names, with an
ID for each; then another table with song names, an ID for each song,
and the ID for the artist; and then another table with play dates and
times, with the ID of the song connecting it? I'm trying to figure out
how it will all be connected.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG REPORT: database disk image is malformed ---- Page 96 is never used

[跃峰 潘]

hi, We had a problem with sqlite 3.7.3. And we had no idea to solve it or 
avoid it. So we write to you for reporting this bug.
The problem is described as follow.1. We used sqlite 3.7.3 on suse-11 linux to 
create a database called 'rofsagent.db', which had only one table.2. The schema 
is as follow:CREATE TABLE agent(  path  VARCHAR(256),  ipaddr  
VARCHAR(16),
  port  INTEGER,
  get  VARCHAR(256),
   set  VARCHAR(256),
   time INTEGER,  type INTEGER)
3. After inserting about 250 records, the db file did not work. It reported 
'database disk image is malformed'.

4. Using the command "PRAGMA quick_check;", the result is as:*** in database 
main ***Main freelist: 2nd reference to page 112On tree page 26 cell 3: Rowid 
14315 out of order (previous was 14320)On tree page 60 cell 1: Rowid 14007 out 
of order (previous was 14322)On tree page 61 cell 40: Rowid 14264 out of order 
(previous was 15276)On tree page 65 cell 3: Rowid 15280 out of order (max 
larger than parent max of 14264)On tree page 71 cell 1: Rowid 14198 out of 
order (previous was 14345)On tree page 71 cell 2: Rowid 14345 out of order (min 
less than parent min of 15280)On tree page 115 cell 4: Rowid 14760 out of order 
(max larger than parent max of 14335)On tree page 31 cell 1: Rowid 14323 out of 
order (previous was 14339)On tree page 31 cell 4: Rowid 15023 out of order 
(previous was 15425)On tree page 31 cell 4: Rowid 14339 out of order (min less 
than parent min of 14760)On tree page 34 cell 3: Rowid 14960 out of order (min 
less than parent min of 15023)On page 87 at right child
 : 2nd reference to page 87Page 57 is never usedPage 96 is never used
5. When we tried to use 'dump'  command to recover the data, some records lost.
Looking forward to hear from you soon.
Best wishes.

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

Re: [sqlite] GUI for SQLite

[gregorinator]

I've been happy with SQLite Studio:

http://sqlitestudio.one.pl/

One of the things I like about it is that it can do table edits not
supported by SQLite's ALTER TABLE -- it automatically creates a new
table and copies the existing data.  Saves me a lot of effort.  Other
SQLite managers may be able to do this, too -- I don't know -- but
after using SQLite Studio I would consider this a must-have feature.

gs

On 3/7/12, BareFeetWare  wrote:
> On 08/03/2012, at 8:47 AM, Rose, John B wrote:
>
>> We are new to SQLite and have experimented with a few GUIs, Firefox
>> plugin, SQLite DB Browser, and Navicat. Is there a single GUI that is
>> considered the best, with the most features?
>
> Some time back, I compared several SQLite GUI editors, mainly for Mac, and
> published at:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> Tom
>
> Tom Brodhurst-Hill
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
> --
> Twitter: http://twitter.com/barefeetware/
> Facebook: http://www.facebook.com/BareFeetWare
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What's the best way to organize this database?

[Larry Knibb]

It sounds like you want a log of timestamps for the time that each
song is played. You also want to store who listened to the song and
other information about the song such as the artist.

Since the only variant is the timestamp, if you want to normalise the
data you can isolate all the other textual items to separate tables
and reference those by ID. For example:

Table: Listener
ID=1 Name=Larry

Table: Song
ID=1 Title="Hippy Hippy Shake" Artist="The Beatles"

Table: Log
Listener=1 Song=1 Timestamp=8 March 2012 20:59

So your Log table can grow and for each new record you only store two
integers - the references to the Listener ID and the Song ID. The
timestamp is the only bit you store in full each time there.

If a new Listener comes along, you give them an ID and then you can
create Log entries for the new Listener.
If a new Song comes along, you give it an ID and ... you get it.
You are free to extend the Listener table and Song table without
impacting the Log table (so you can extend the functionality of your
database), e.g. if you decide you want to know the Year that the Song
was released. You only need to enter it once in the Song table.
You could even break-out the Artist to a separate table, e.g.

Table: Artist
ID=1 Name=The Beatles

Then the Song table becomes:

Table: Song
ID=1 Title="Hippy Hippy Shake" Artist=1

So to put it all together again, you need to use a SQL JOIN statement, e.g.

SELECT Listener.Name, Song.Title, Artist.Name, Log.Timestamp
FROM Log
JOIN Listener on Listener.ID = Log.Listener -- this maps the Listener
table so you can SELECT the Listener.Name above
JOIN Song on Song.ID = Log.Song -- this maps the Song table so you can
SELECT the Song.Title above
JOIN Artist on Artist.ID = Song.Artist -- this maps the Artist table
(using the Song mapping) so you can SELECT the Artist.Name above
WHERE Listener.Name = "Larry"

The point with normalisation is to remove the invariant data to
another table, assign it an ID and then reference it by ID instead of
duplicating the invariant text. It also allows you to "encapsulate"
your data (think OOP) so for example, Song info is in the Song table
(class) and you use an ID (pointer) to each row (instance) of Song.

Hope that clears things up.

Cheers,
Larry

On 7 March 2012 04:48, John Salerno  wrote:
> Thanks to both of you. The question of normalization was one thing I was
> considering, I just wasn't sure how it should be done. A separate table
> with Artist ID and Artist Name fields could be useful. Would it be good to
> make an equivalent table for the songs, or should the songs simply be
> listed individually in the main table? I assume the dates and times have to
> be individual entries each time.
>
> However, while reading the Wikipedia page on normalization, I noticed that
> some of the examples of a normalized table actually lists people's names as
> separate entries in the table. Of course, the alternative was some kind of
> nested tables, that looked messy, so perhaps the separate listings were the
> first step toward normalization, the next step being multiple tables with
> IDs for the names?
>
> Thanks.
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Amalgamation

[Eduardo Morras]

At 04:58 08/03/2012, you wrote:

Hi guys,

Decided to open a discussion on SQLite amalgamation. Please forgive 
us, Windows users, who unlucky enough to use Microsoft Visual 
Studio. As many of you know, it is year 2012 now, and Visual Studio 
is up to version 10, but the guys at Microsoft still use 16-bit 
indices for the debugger. As a result, the source files with the 
line count greater than 65535 cannot be debugged.


It is very annoying, and we, Windows users, should use a 
non-recommended source for SQLite in our projects.


Can you guys create a version with source split into files with each 
of them not greater than 65535 lines (call it as you like, VC 
special version, semi-amalgamated, etc.), but without the words "not 
recommended".


This would be very helpful.

Regards,

Andrew Cherednik

Senior Analyst / Application Developer | Healthscope Limited Level 
1, 312 St Kilda Road | Melbourne Victoria 3004


You can delete all comments and blank lines, this way sqlite3.c will 
be circa 7 lines.


HTH 



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

Re: [sqlite] SQLite Amalgamation

[Richard Hipp]

On Wed, Mar 7, 2012 at 10:58 PM, Andrew Cherednik <
andrew.chered...@healthscope.com.au> wrote:

> Hi guys,
>
>
>
> Decided to open a discussion on SQLite amalgamation. Please forgive us,
> Windows users, who unlucky enough to use Microsoft Visual Studio. As many
> of you know, it is year 2012 now, and Visual Studio is up to version 10,
> but the guys at Microsoft still use 16-bit indices for the debugger. As a
> result, the source files with the line count greater than 65535 cannot be
> debugged.
>


Get a copy of the
split-sqlite3c.tclscript
and a copy of the amalgamation.  Run

tclsh split-sqlite3.c

You may need to install tclsh from http://www.activestate.com/activetcl

The script above will produce these files:

sqlite3-all.c
sqlite3-1.c
sqlite3-2.c
sqlite3-3.c
sqlite3-4.c
sqlite3-5.c

You compile with sqlite3-all.c and it will #include all the others.


>
>
>
> It is very annoying, and we, Windows users, should use a non-recommended
> source for SQLite in our projects.
>
>
>
> Can you guys create a version with source split into files with each of
> them not greater than 65535 lines (call it as you like, VC special version,
> semi-amalgamated, etc.), but without the words "not recommended".
>
>
>
> This would be very helpful.
>
>
>
> Regards,
>
>
>
> Andrew Cherednik
>
> Senior Analyst / Application Developer | Healthscope Limited Level 1, 312
> St Kilda Road | Melbourne Victoria 3004
>
>
>
> "IMPORTANT - This email contains confidential information intended only
> for the person named above
> and may be subject to legal privilege. If you are not the intended
> recipient, any disclosure, copying or use
> of this information is prohibited. Healthscope provides no guarantee that
> this communication is free of
> virus or that it has not been intercepted or interfered with. If you have
> received this email in error or have
> any other concerns regarding its transmission, please notify
> postmas...@healthscope.com.au. You must
> destroy the original transmission and its contents. Any views expressed
> within this communication are
> those of the individual sender, except where the sender specifically
> states them to be the views of
> Healthscope. If this document is not required for record keeping purposes
> please consider the
> environment before storing or printing. This communication should not be
> copied or disseminated without
> permission".
>
>
> ___
> 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] Efficient random sampling in a large table using builtin functions.

[Benoit Mortgat]

I have a table with millions of records.

When I run a query with

ORDER BY random() LIMIT N;

the RANDOM() function is evaluated against all rows of my result set,
then sorting occurs, and as a result the query is slow.

In this case the query could be rewritten as:

 * Generate N, random, row numbers between 1 and (SELECT COUNT(*) FROM
   the_table_name). Maybe using remainder operator % and builtin ABS()
   and RANDOM() functions can help (see below)
 * SELECT FROM the_table WHERE rowid IN (those random numbers)

For the moment the most simple query I can think of is:

   SELECT *
 FROM my_table
WHERE rowid IN
( SELECT 1 + (ABS(r.x) % c.num_rows)
FROM (SELECT COUNT(*) AS num_rows
FROM my_table
 ) AS c
   CROSS JOIN
 (SELECT random() x
FROM my_table
   LIMIT N
 ) AS r
);

This can however return less than N rows if by chance two random numbers
have the same remainder modulo COUNT(*) FROM my_table.

Note that the generation of N random numbers is quick because there is
no ORDER BY involved that would require computation of as many random
numbers as there are rows in the original table.

This could maybe be optimized inside SQLite if those conditions are met:

 * random() hasn't been overridden with sqlite_create_function() nor
   sqlite_create_function_v2()
 * the user SELECTs FROM a table without joins and w/o WHERE conditions.

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