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

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

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

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

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

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:

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

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

Re: [sqlite] INSERT OR REPLACE

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

Re: [sqlite] INSERT OR REPLACE

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

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

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

Re: [sqlite] INSERT OR REPLACE

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

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

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

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

-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

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

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

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

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

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

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

Re: [sqlite] INSERT OR REPLACE

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

Re: [sqlite] GUI for SQLite

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

Re: [sqlite] INSERT OR REPLACE

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

[sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

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

Re: [sqlite] INSERT OR REPLACE

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 >>

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

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

Re: [sqlite] INSERT OR REPLACE

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.

Re: [sqlite] 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. 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

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

> 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

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

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

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

-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

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

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

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

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

Re: [sqlite] INSERT OR REPLACE

> 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

[sqlite] Bug? Empty string stored as NULL?

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 -- ** * *

Re: [sqlite] INSERT OR REPLACE

> > 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

Re: [sqlite] INSERT OR REPLACE

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

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

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

[sqlite] INSERT OR REPLACE

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

Re: [sqlite] output mode formatting

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

Re: [sqlite] How efficient is this?

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

Re: [sqlite] GUI for SQLite

> 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

Re: [sqlite] How efficient is this?

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

Re: [sqlite] How efficient is this?

> > 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

Re: [sqlite] How efficient is this?

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

Re: [sqlite] How efficient is this?

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

Re: [sqlite] How efficient is this?

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

Re: [sqlite] How efficient is this?

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

Re: [sqlite] How efficient is this?

> > 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

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

> 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

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

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

Re: [sqlite] How efficient is this?

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

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

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

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

Re: [sqlite] How efficient is this?

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

Re: [sqlite] How efficient is this?

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 >

Re: [sqlite] How efficient is this?

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

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

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)) ==

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:

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

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

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

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

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

[sqlite] How efficient is this?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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? > >

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

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

[sqlite] Bug? Subtract Two Sum-Values

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

Re: [sqlite] GUI for SQLite

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 >

Re: [sqlite] processes stuck on database locked

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

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

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,

[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

Re: [sqlite] GUI for SQLite

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

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

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

Re: [sqlite] SQLite Amalgamation

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

Re: [sqlite] SQLite Amalgamation

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

[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,