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,
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
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
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
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
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
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,
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
Il giorno 08 marzo 2012 14:13, gregorinator gregorina...@gmail.com 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
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
On Wed, Mar 7, 2012 at 9:30 AM, 跃峰 潘 pyfs...@hotmail.com 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
On Thu, Mar 8, 2012 at 8:39 AM, marku...@web.de 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
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
Benoit Mortgat mort...@gmail.com 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
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
On 8 March 2012 14:20, Black, Michael (IS) michael.bla...@ngc.com 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
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
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
On 8 March 2012 14:37, Black, Michael (IS) michael.bla...@ngc.com 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
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
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
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
On Thu, Mar 8, 2012 at 8:02 AM, Igor Tandetnik itandet...@mvps.org 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
On Thu, Mar 8, 2012 at 10:58 AM, Yongiljang yongilj...@gmail.com 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
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 mlal...@outsitenetworks.com wrote:
From: Marc L. Allen mlal...@outsitenetworks.com
Subject: [sqlite] How efficient is this?
To: sqlite-users@sqlite.org
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)
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
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:
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
On Thu, Mar 8, 2012 at 11:45 AM, Black, Michael (IS) michael.bla...@ngc.com
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
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
On Thu, Mar 8, 2012 at 10:43 AM, Igor Tandetnik itandet...@mvps.org 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
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
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
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
On Thu, Mar 8, 2012 at 11:22 AM, Nico Williams n...@cryptonector.com 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
On Thu, Mar 8, 2012 at 11:47 AM, Marc L. Allen
mlal...@outsitenetworks.com 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
On Thu, Mar 8, 2012 at 9:53 AM, Marc L. Allen
mlal...@outsitenetworks.com 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
On Thu, Mar 8, 2012 at 11:56 AM, Nico Williams n...@cryptonector.com wrote:
On Thu, Mar 8, 2012 at 11:47 AM, Marc L. Allen
mlal...@outsitenetworks.com wrote:
But this is extremely inefficient as well. Since each record is, in fact,
an update, you're actually performing a delete/insert for
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
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
Message: 8
Date: Wed, 7 Mar 2012 21:47:28 +
From: Rose, John B jbr...@utk.edu
To: sqlite-users@sqlite.org sqlite-users@sqlite.org
Subject: [sqlite] GUI for SQLite
Message-ID: cb7d419f.20e24%jbr...@utk.edu
Content-Type: text/plain; charset=us-ascii
We are new to SQLite and have
On Thu, Mar 8, 2012 at 12:00 PM, Marc L. Allen
mlal...@outsitenetworks.com 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
On 8 Mar 2012, at 5:19pm, Rita rmorgan...@gmail.com 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
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
On Thu, Mar 8, 2012 at 9:28 AM, Pavel Ivanov paiva...@gmail.com 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
On 8 Mar 2012, at 6:39pm, Marc L. Allen mlal...@outsitenetworks.com 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
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
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
--
**
* *
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
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
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,
-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
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 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
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
On Thu, Mar 8, 2012 at 1:52 PM, Pavel Ivanov paiva...@gmail.com 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
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
On 8 Mar 2012, at 7:43pm, Marc L. Allen mlal...@outsitenetworks.com 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
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
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
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
On Thu, Mar 8, 2012 at 4:37 PM, Simon Slavin slav...@bigfraud.org 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
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
On Thu, Mar 8, 2012 at 5:44 PM, Alan Chandler a...@chandlerfamily.org.ukwrote:
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
On Thu, Mar 8, 2012 at 6:27 PM, Alan Chandler a...@chandlerfamily.org.ukwrote:
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
-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
On 08/03/12 23:32, Richard Hipp wrote:
On Thu, Mar 8, 2012 at 5:44 PM, Alan Chandlera...@chandlerfamily.org.ukwrote:
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
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
On Thu, Mar 8, 2012 at 6:47 PM, Alan Chandler a...@chandlerfamily.org.ukwrote:
The complete database schema is here
https://github.com/akc42/**AKCMoney/blob/master/app/inc/**database.sqlhttps://github.com/akc42/AKCMoney/blob/master/app/inc/database.sql
My database is full of private
On 09.03.2012 02:22, Nico Williams wrote:
On Thu, Mar 8, 2012 at 5:57 PM, Alek Paunova...@declera.com 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
Alek Paunov a...@declera.com 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 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
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:
On 09/03/12 00:29, Richard Hipp wrote:
On Thu, Mar 8, 2012 at 6:47 PM, Alan Chandlera...@chandlerfamily.org.ukwrote:
The complete database schema is here
https://github.com/akc42/**AKCMoney/blob/master/app/inc/**database.sqlhttps://github.com/akc42/AKCMoney/blob/master/app/inc/database.sql
On Thu, Mar 8, 2012 at 15:02, Igor Tandetnik itandet...@mvps.org
wrote:
Benoit Mortgat mort...@gmail.com 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
76 matches
Mail list logo