[sqlite] SELECT DISTINCT question
I have the following query:
SELECT DISTINCT "EVALA112"("S#") AS "^" FROM "S" ORDER BY "S#" ASC ;
[This is generated code, not hand-written. The table S is from CJ Date
sample data.]
This query appears to work correctly. The function is an aggregation, and
requires the data to be sorted.
This same query fails in Postgres with: "for SELECT DISTINCT, ORDER BY
expressions must appear in select list".
In effect the reason is that a query in this form requires two sort
operations, and a single query can have only one. To get this to work
correctly, I shall either drop the DISTINCT or turn the second part into a
subselect. That I understand.
The question is: how does Sqlite interpret this query? Why is it not an
error? Is it because the query returns a single result, which does not
require a sort?
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
[sqlite] SELECT DISTINCT question
Just to add to the below:
S#| SNAME | STATUS | CITY
---
S1| Smith | 20 | London
S2| Jones | 10 | Paris
S3| Blake | 30 | Paris
S4| Clark | 20 | London
S5| Adams | 30 | Athens
SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
CITY
--
Paris
London
Athens
I don't find it easy to explain this result.
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of dandl
> Sent: Monday, 9 May 2016 11:28 AM
> To: 'SQLite mailing list'
> Subject: [sqlite] SELECT DISTINCT question
>
> I have the following query:
>
> SELECT DISTINCT "EVALA112"("S#") AS "^" FROM "S" ORDER BY "S#" ASC ;
>
> [This is generated code, not hand-written. The table S is from CJ Date
sample
> data.]
>
> This query appears to work correctly. The function is an aggregation, and
> requires the data to be sorted.
>
> This same query fails in Postgres with: "for SELECT DISTINCT, ORDER BY
> expressions must appear in select list".
>
> In effect the reason is that a query in this form requires two sort
> operations, and a single query can have only one. To get this to work
> correctly, I shall either drop the DISTINCT or turn the second part into a
> subselect. That I understand.
>
> The question is: how does Sqlite interpret this query? Why is it not an
> error? Is it because the query returns a single result, which does not
> require a sort?
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database is locked when using SQLite3 and MPI to generate different databases
On 8 May 2016 at 10:14, just_rookie <925345468 at qq.com> wrote: > Obviously, I did not do incompatible things with a database at the same > time. > You are attempting to drop a table in databases 300_500.db and 600_900.db. Obviously another process must create that table, since your test code isn't. Dropping a table involves writing to the database. To write the database it must be locked. If another process already has a write-lock your MPI tasks won't be able to obtain one and will fail with "database is locked". If another process is holding a read-lock, your MPI tasks will obtain a write-lock but won't be able to COMMIT until the read-lock is relinquished. If you're getting "database is locked" even with a busy timeout of 10 seconds, you have something other connection to the DB holding on to a lock. It could be that your other code is not calling sqlite3_finalize on every sqlite3_stmt, or you just have long running transactions. -Rowan
[sqlite] determining is-leap-year in sqlite
That suggests that the script is not consistently telling sqlite which TZ to use in all calculations. i will take a look at it as time allows. Probably just need to be sure to consistently pass the final argument to strftime(). - stephan (Sent from a mobile device, possibly from bed. Please excuse brevity, typos, and top-posting.) On May 8, 2016 21:34, "jungle Boogie" wrote: > On 8 May 2016 at 12:28, jungle Boogie wrote: > > I'll set the TZ on the pi to match and see what happens. > > We're on to something! > > pi time: > $ date > Sun May 8 12:29:54 PDT 2016 > > x86 time: > % date > Sun May 8 12:30:04 PDT 2016 > > > They match with cal.sql now! > http://kopy.io/GbbDR > > > So no problem with your script unless you're not using UTC time! > > -- > --- > inum: 883510009027723 > sip: jungleboogie at sip2sip.info > xmpp: jungle-boogie at jit.si > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] SELECT DISTINCT question
On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
> Just to add to the below:
>
> S#| SNAME | STATUS | CITY
> ---
> S1| Smith | 20 | London
> S2| Jones | 10 | Paris
> S3| Blake | 30 | Paris
> S4| Clark | 20 | London
> S5| Adams | 30 | Athens
>
> SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
>
> CITY
> --
> Paris
> London
> Athens
>
> I don't find it easy to explain this result.
>
My guess based on the available data is that, since you don't specify which
"S#" you want associated with each city, it is picking the max of each
(coincidentally). If you want the minimum S# value, this seems to work:
select distinct city from s group by city order by min("S#") asc;
I'm not sure if that *should* work per "standard" SQL, but it does with
SQLite. I'd have expected something like this to be necessary:
select city, min("S#") as x from s group by city order by x asc;
And if you only want the city:
select city from (select city, min("S#") as x from s group by city order by
x asc);
But I'm not a SQL master.
Distinct used with group by seems redundant, but again, I might just not
understand how they are useful together.
--
Scott Robison
[sqlite] determining is-leap-year in sqlite
On Mon, May 9, 2016 at 5:40 AM, Stephan Beal wrote: > That suggests that the script is not consistently telling sqlite which TZ > to use in all calculations. i will take a look at it as time > just fyi: i can now reproduce the problem on my x64, where my days are shifted 1 to the left. Not sure what's causing it, but probably won't be able to look at it until next weekend :/. i apparently broke it at some point without noticing. On May 8, 2016 21:34, "jungle Boogie" wrote: > >> On 8 May 2016 at 12:28, jungle Boogie wrote: >> > I'll set the TZ on the pi to match and see what happens. >> >> We're on to something! >> >> pi time: >> $ date >> Sun May 8 12:29:54 PDT 2016 >> >> x86 time: >> % date >> Sun May 8 12:30:04 PDT 2016 >> >> >> They match with cal.sql now! >> http://kopy.io/GbbDR >> >> >> So no problem with your script unless you're not using UTC time! >> >>
[sqlite] SELECT DISTINCT question
This is documented behaviour for SQLite:
SELECT a, MAX(b) table;
Will return (one of) the a value(s) that comes from the same row as the MAX(b).
If there are not exactly on of MIN or MAX aggregate functions, SQLite is free
to pick any row (within a group) to return non-aggregated columns from. Thus:
Select a,SUM(b), c from table group by a;
will return one of the c values from each group of a values.
The same thing applies for DISTINCT.
-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Scott
Robison
Gesendet: Montag, 09. Mai 2016 08:13
An: SQLite mailing list
Betreff: Re: [sqlite] SELECT DISTINCT question
On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
> Just to add to the below:
>
> S#| SNAME | STATUS | CITY
> ---
> S1| Smith | 20 | London
> S2| Jones | 10 | Paris
> S3| Blake | 30 | Paris
> S4| Clark | 20 | London
> S5| Adams | 30 | Athens
>
> SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
>
> CITY
> --
> Paris
> London
> Athens
>
> I don't find it easy to explain this result.
>
My guess based on the available data is that, since you don't specify which
"S#" you want associated with each city, it is picking the max of each
(coincidentally). If you want the minimum S# value, this seems to work:
select distinct city from s group by city order by min("S#") asc;
I'm not sure if that *should* work per "standard" SQL, but it does with SQLite.
I'd have expected something like this to be necessary:
select city, min("S#") as x from s group by city order by x asc;
And if you only want the city:
select city from (select city, min("S#") as x from s group by city order by x
asc);
But I'm not a SQL master.
Distinct used with group by seems redundant, but again, I might just not
understand how they are useful together.
--
Scott Robison
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Good way for CEIL, or is there a better way
I need to have a CEIL function in SQLite. This is the way I implemented it: WITH percentage AS ( SELECT date , 100.0 * rank / outOf AS percentage , CAST(100.0 * rank / outOf AS int) AS castedPercentage FROM ranking ) SELECT date , (CASE WHEN percentage = castedPercentage THEN castedPercentage ELSE castedPercentage + 1 END) AS percentage FROM percentage Is this a good way, or is there a better way? -- Cecil Westerhof
[sqlite] Good way for CEIL, or is there a better way
Cecil Westerhof wrote: > I need to have a CEIL function in SQLite. This is the way I implemented it: > WITH percentage AS ( > SELECT date > , 100.0 * rank / outOf AS percentage > , CAST(100.0 * rank / outOf AS int) AS castedPercentage > FROM ranking > ) > SELECT date > , (CASE WHEN percentage = castedPercentage > THEN castedPercentage > ELSE castedPercentage + 1 > END) AS percentage > FROM percentage > > Is this a good way, or is there a better way? Isn't Ceil(Value) simply Round(Value + 0.5) ?
[sqlite] Good way for CEIL, or is there a better way
> I need to have a CEIL function in SQLite. This is the way I implemented it:
> WITH percentage AS (
> SELECT date
> , 100.0 * rank / outOf AS percentage
> , CAST(100.0 * rank / outOf AS int) AS castedPercentage
> FROM ranking
> )
> SELECT date
> , (CASE WHEN percentage = castedPercentage
> THEN castedPercentage
> ELSE castedPercentage + 1
> END) AS percentage
> FROM percentage
>
> Is this a good way, or is there a better way?
>
Probably you can create your own function
void sqlite3_ceilFunc(sqlite3_context* context, int argc,
sqlite3_value** values) {
//yourcode
}
SQliteContext cContext; //any sqlite context
sqlite3* pDB; //your DB session
sqlite3_create_function(pDB, "CEIL", 1, SQLITE_UTF8, &cContext,
&sqlite3_ceilFunc, NULL, NULL);
[sqlite] Good way for CEIL, or is there a better way
2016-05-09 13:36 GMT+02:00 OBones : > Cecil Westerhof wrote: > >> I need to have a CEIL function in SQLite. This is the way I implemented >> it: >> WITH percentage AS ( >> SELECT date >> , 100.0 * rank / outOf AS percentage >> , CAST(100.0 * rank / outOf AS int) AS castedPercentage >> FROM ranking >> ) >> SELECT date >> , (CASE WHEN percentage = castedPercentage >> THEN castedPercentage >> ELSE castedPercentage + 1 >> END) AS percentage >> FROM percentage >> >> Is this a good way, or is there a better way? >> > Isn't Ceil(Value) simply Round(Value + 0.5) ? > ?That was my first thought. But when playing with it, I ran into some edge cases. But maybe I should not worry to much about those. ;-) -- Cecil Westerhof
[sqlite] Good way for CEIL, or is there a better way
2016-05-09 13:40 GMT+02:00 Michele Pradella : > I need to have a CEIL function in SQLite. This is the way I implemented it: >> WITH percentage AS ( >> SELECT date >> , 100.0 * rank / outOf AS percentage >> , CAST(100.0 * rank / outOf AS int) AS castedPercentage >> FROM ranking >> ) >> SELECT date >> , (CASE WHEN percentage = castedPercentage >> THEN castedPercentage >> ELSE castedPercentage + 1 >> END) AS percentage >> FROM percentage >> >> Is this a good way, or is there a better way? >> >> Probably you can create your own function > ?But I want it to be possible for ?everyone? to use the application. People need to implement my function then. Or am I wrong about that? -- Cecil Westerhof
[sqlite] Good way for CEIL, or is there a better way
> 2016-05-09 13:40 GMT+02:00 Michele Pradella : > >> I need to have a CEIL function in SQLite. This is the way I implemented it: >>> WITH percentage AS ( >>> SELECT date >>> , 100.0 * rank / outOf AS percentage >>> , CAST(100.0 * rank / outOf AS int) AS castedPercentage >>> FROM ranking >>> ) >>> SELECT date >>> , (CASE WHEN percentage = castedPercentage >>> THEN castedPercentage >>> ELSE castedPercentage + 1 >>> END) AS percentage >>> FROM percentage >>> >>> Is this a good way, or is there a better way? >>> >>> Probably you can create your own function > ?But I want it to be possible for ?everyone? to use the application. People > need to implement my function then. Or am I wrong about that? Just add CEIL function with (sqlite3_create_function) when you need in your code. I do not understand what do you mean with "everyone" anyway if your application has the definition of CEIL function everyone using your application will have the function
[sqlite] SQLite custom function for regular expression using c/c++
On Wed, May 4, 2016 at 2:52 PM, Richard Hipp wrote: > On 5/4/16, Bhagwat Balshetwar wrote: >> I want to write the custom function for regular expression using C/C++. > > You mean like this one: https://www.sqlite.org/src/artifact/a68d25c659bd2d89 Is there any reason this cannot be included as an option in the SQLite amalgamation? I suspect this could help a number of users who could use REGEXP support without the hassle of writing and maintaining extra integration code.
[sqlite] SQLite custom function for regular expression using c/c++
On 5/9/16, Chris Brody wrote: > On Wed, May 4, 2016 at 2:52 PM, Richard Hipp wrote: >> On 5/4/16, Bhagwat Balshetwar wrote: >>> I want to write the custom function for regular expression using C/C++. >> >> You mean like this one: >> https://www.sqlite.org/src/artifact/a68d25c659bd2d89 > > Is there any reason this cannot be included as an option in the SQLite > amalgamation? > The code above does not implement PCRE. And so some people would have issues with it. And then we'd end up having add PCRE support too, while continuing to support both the fast regular expression in the extension above for backwards compatibility. This is a nightmare that I don't want to get mixed up with if I don't have to. -- D. Richard Hipp drh at sqlite.org
[sqlite] Good way for CEIL, or is there a better way
On Mon, May 9, 2016 at 1:52 PM, Cecil Westerhof wrote: > ?But I want it to be possible for ?everyone? to use the application. People > need to implement my function then. Or am I wrong about that? > fyi, ceil(3) is c99, not c89, which is likely the (or a) reason it's not included in sqlite by default. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] Good way for CEIL, or is there a better way
2016-05-09 13:57 GMT+02:00 Michele Pradella : > > 2016-05-09 13:40 GMT+02:00 Michele Pradella : >> >> I need to have a CEIL function in SQLite. This is the way I implemented >>> it: >>> WITH percentage AS ( SELECT date , 100.0 * rank / outOf AS percentage , CAST(100.0 * rank / outOf AS int) AS castedPercentage FROM ranking ) SELECT date , (CASE WHEN percentage = castedPercentage THEN castedPercentage ELSE castedPercentage + 1 END) AS percentage FROM percentage Is this a good way, or is there a better way? Probably you can create your own function >>> ?But I want it to be possible for ?everyone? to use the application. >> People >> need to implement my function then. Or am I wrong about that? >> > Just add CEIL function with (sqlite3_create_function) when you need in > your code. I do not understand what do you mean with "everyone" anyway if > your application has the definition of CEIL function everyone using your > application will have the function ?It is not a real application yet, just thought about it today. But it will probably be a set of Bash scripts. If those Bash scripts depend on a modified SQLite, then it would not be easy to use. -- Cecil Westerhof
[sqlite] SELECT DISTINCT question
Why are you using BOTH distinct and group by on the same column? You only need
one or the other. If you are redundantly redundant I would hope that the
optimizer makes redundant (as in gets rid of, for those that are not English)
the redundancies ...
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of dandl
> Sent: Sunday, 8 May, 2016 19:45
> To: 'SQLite mailing list'
> Subject: Re: [sqlite] SELECT DISTINCT question
>
> Just to add to the below:
>
> S#| SNAME | STATUS | CITY
> ---
> S1| Smith | 20 | London
> S2| Jones | 10 | Paris
> S3| Blake | 30 | Paris
> S4| Clark | 20 | London
> S5| Adams | 30 | Athens
>
> SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
>
> CITY
> --
> Paris
> London
> Athens
>
> I don't find it easy to explain this result.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of dandl
> > Sent: Monday, 9 May 2016 11:28 AM
> > To: 'SQLite mailing list'
> > Subject: [sqlite] SELECT DISTINCT question
> >
> > I have the following query:
> >
> > SELECT DISTINCT "EVALA112"("S#") AS "^" FROM "S" ORDER BY "S#" ASC ;
> >
> > [This is generated code, not hand-written. The table S is from CJ Date
> sample
> > data.]
> >
> > This query appears to work correctly. The function is an aggregation,
> and
> > requires the data to be sorted.
> >
> > This same query fails in Postgres with: "for SELECT DISTINCT, ORDER BY
> > expressions must appear in select list".
> >
> > In effect the reason is that a query in this form requires two sort
> > operations, and a single query can have only one. To get this to work
> > correctly, I shall either drop the DISTINCT or turn the second part into
> a
> > subselect. That I understand.
> >
> > The question is: how does Sqlite interpret this query? Why is it not an
> > error? Is it because the query returns a single result, which does not
> > require a sort?
> >
> > Regards
> > David M Bennett FACS
> >
> > Andl - A New Database Language - andl.org
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite custom function for regular expression using c/c++
On Mon, May 9, 2016 at 2:20 PM, Richard Hipp wrote: > On 5/9/16, Chris Brody wrote: >> On Wed, May 4, 2016 at 2:52 PM, Richard Hipp wrote: >>> On 5/4/16, Bhagwat Balshetwar wrote: I want to write the custom function for regular expression using C/C++. >>> >>> You mean like this one: >>> https://www.sqlite.org/src/artifact/a68d25c659bd2d89 >> >> Is there any reason this cannot be included as an option in the SQLite >> amalgamation? >> > > The code above does not implement PCRE. And so some people would have > issues with it. And then we'd end up having add PCRE support too, > while continuing to support both the fast regular expression in the > extension above for backwards compatibility. This is a nightmare that > I don't want to get mixed up with if I don't have to. Understood, thanks for the answer. In general I wish there were an easier way to switch REGEXP implementations. I do notice that SQLite does support pluggable components and extensions, through I wonder if this could be made easier somehow.
[sqlite] Incremental backup/sync facility?
> From: Stephan Buchert [mailto:stephanb007 at gmail.com] > Sent: Saturday, May 07, 2016 12:10 AM > Copying the WAL files is probably more efficient than the SQL text solutions > (considering that roughly 5 GB of binary data are weekly added), and it seems > easy to implement, so I'll probably try this first. I guess that simply > opening the primary database for read before starting the insert/update > process would prevent WAL checkpointing until I have a chance to copy the WAL > (and SHM) files? It sounds like that would work. You would need to be careful with your process. At a minimum, you need to be sure that full checkpoints don't occur, except in conjunction with your copies. It would probably be very easy to do an "accidental" checkpoint simply running the sqlite3 command-line tool after a reboot. I believe the no-auto-checkpoint setting is per-application, not per-database. I suppose you could change the checkpoint-code so that after a full checkpoint, the WAL file is renamed (with a counter suffix), rather than deleted. You incremental backup would just need to copy (and perhaps delete) all of the renamed WAL files, and process them, in order. That would have the advantage that you don't need to let the WAL file grow so large, and you don't have to do anything to the main data base while you are doing the incremental backup. You'd still have to be careful with your process. Run some sqlite program that doesn't have your "changed" checkpoint-code, and you might lose a WAL file. Regards, Bill ** This e-mail and any attachments thereto may contain confidential information and/or information protected by intellectual property rights for the exclusive attention of the intended addressees named above. If you have received this transmission in error, please immediately notify the sender by return e-mail and delete this message and its attachments. Unauthorized use, copying or further full or partial distribution of this e-mail or its contents is prohibited. **
[sqlite] Which CHECK constraint failed
I made a table with a few CHECK constraints. When an INSERT is not possible, I would like to know which CHECK constraint fired. Is there a way to get this information? -- Cecil Westerhof
[sqlite] Which CHECK constraint failed
On Mon, May 9, 2016 at 3:21 PM, Cecil Westerhof wrote: > I made a table with a few CHECK constraints. When an INSERT is not > possible, I would like to know which CHECK constraint fired. Is there a way > to get this information? Just names your CHECK constraint, and use a recent version of SQLite. --DD
[sqlite] Good way for CEIL, or is there a better way
09-05-2016, OBones: > Isn't Ceil(Value) simply Round(Value + 0.5) ? But Round(0.5) = 1 May be Round(Value+0.4) is good enough?
[sqlite] Which CHECK constraint failed
On Mon, May 9, 2016 at 3:46 PM, Dominique Devienne wrote: > On Mon, May 9, 2016 at 3:21 PM, Cecil Westerhof > wrote: >> I made a table with a few CHECK constraints. When an INSERT is not >> possible, I would like to know which CHECK constraint fired. Is there a way >> to get this information? > > Just names your CHECK constraint, and use a recent version of SQLite. --DD C:\Users\ddevienne>sqlite3 SQLite version 3.10.2 2016-01-20 15:27:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t (v constraint is_bool check (v in (0, 1))); sqlite> insert into t values (1); sqlite> insert into t values (0); sqlite> insert into t values (2); Error: CHECK constraint failed: is_bool sqlite>
[sqlite] Good way for CEIL, or is there a better way
E.Pasma wrote: > 09-05-2016, OBones: >> Isn't Ceil(Value) simply Round(Value + 0.5) ? > But Round(0.5) = 1 > May be Round(Value+0.4) is good enough? Well, yes, there's an issue at 0, but for anything else positive, it should be good enough.
[sqlite] Fastest way to backup/copy database?
Re WAL mode trick. I think you would want to complete a checkpoint and then do the backup, ensuring that no check-points are done during your backup time. This way, you know that your committed transactions prior to the backup are in the file being backed up. regards, Adam On Sat, May 7, 2016 at 7:32 AM, Stadin, Benjamin < Benjamin.Stadin at heidelberg-mobil.com> wrote: > Hi Rob, > > I think Clemens suggestion may be worth investigating, in case you do not > want to stop the updates (which probably means a change in your workflow > and some effort at other places anyways). > > I think this may work: > - Use WAL, and turn off automatic checkpointing > (https://www.sqlite.org/wal.html). The default behavior is to do a commit > after 1000*4096(pagesize) which is round about 4MB. Instead of using the > default auto checkpoint, create a checkpoint every now and then on your > own in your code (e.g. simply after every n-th commit, every 10 minutes, > or whatever fits). > - Do *not* do checkpointing at the time you copy your db, in order to > avoid changing the db while copying the file. Changes are written to WAL > files exclusively at this time. I think it needs just reasonable effort to > trigger these event from the outside to have the app know when a backup > starts and stops - or it could be done as simple as implement within the > checkpoint code a rule like ?don?t make a checkpoint between 2:30am and > 4:00am?. > > Regards, > Ben > > > Am 04.05.16, 14:39 schrieb "sqlite-users-bounces at mailinglists.sqlite.org > on behalf of Rob Willett" unter > rob.sqlite at robertwillett.com>: > > >Clemens, > > > >We have 8GB of memory which is the most our VPS provider allows. We?d > >like 32GB but its not an option for us. Our desktops have more than > >that, but the VPS provider is reasonably priced :) > > > >We hadn?t considered the WAL mode, my conclusion is that a simple > >change to our workflow is actually simpler, we stop the database updates > >for 15 mins out of hours, cp and then restart. Its not ideal but we?re > >not running a nuclear power station or a 24/7 medical facility. Users > >*may* not get traffic updates for 15 mins at 03:00 in the morning. The > >world will keep spinning. > > > >Rob > > > >On 4 May 2016, at 12:58, Clemens Ladisch wrote: > > > >> Rob Willett wrote: > >>> We?re trying to backup a 10GB live running database > >>> ?as-fast-as-we- > >>> possibly-can? without stopping updates coming in. > >> > >> How much memory do you have? I guess you can't simply read the entire > >> database file to force it into the file cache? > >> > >> In WAL mode, a writer does not block readers. You have to decide > >> whether you can live with its restrictions: > >> http://www.sqlite.org/wal.html > >> > >> > >> Regards, > >> Clemens > >> ___ > >> sqlite-users mailing list > >> sqlite-users at mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >___ > >sqlite-users mailing list > >sqlite-users at mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
[sqlite] Which CHECK constraint failed
On Mon, May 9, 2016 at 3:48 PM, Dominique Devienne wrote: > On Mon, May 9, 2016 at 3:46 PM, Dominique Devienne > wrote: >> On Mon, May 9, 2016 at 3:21 PM, Cecil Westerhof >> wrote: >>> [...] Is there a way to get this information? >> >> Just name your CHECK constraint, and use a recent version of SQLite. --DD sqlite> create table tt (v check (v in (0, 1))); sqlite> insert into tt values (2); Error: CHECK constraint failed: tt Note that w/o a *named* constraint, it seems to just show the table name. And if you meant a programmatic way to know the failure is a check constraint, there's SQLITE_CONSTRAINT from https://sqlite.org/c3ref/c_abort.html SQLITE_CONSTRAINT_CHECK from https://sqlite.org/c3ref/c_abort_rollback.html but there's no way AFAIK to get the violated constraint name via an API, except by parsing the error message (subject to change I guess...), nor to reliably know it's a constraint name as opposed to a table name. --DD
[sqlite] 64bit DLL vs 32bit
On 2016-05-07 01:29, Simon Slavin wrote: > On 7 May 2016, at 3:28am, Keith Medcalf wrote: > >> I presume you mean that running 32-bit application on a 64-bit OS is >> slower than the same application run on a 32-bit OS. > > Hold on. The original poster was talking about using a 32-bit DLL, not > a 32-bit application. I don't know what Windows' limitations are. n > 64-bit windows can you run a 64-bit application which uses a 32-bit DLL > ? Or does the fact that the application uses a 32-bit DLL mean that it > must be 32-bit itself ? > > Either way, what I found is that 32-bit apps run at expected speed if > they're just doing stuff internally. The delays come when you meet a > 32/64 interface, for example if a 32-bit application is constantly > fetching data from a 64-bit source supplied by the 64-bit OS. > Sorry for the late reply... Well, I can not use the SQLite 64bit DLL in a 64bit environment with a 32bit application. So, this answer your questions, at least as SQLite DLL is concerned. What I am trying to find out is the speed difference from both 32bit and 64bit environment. I will have to look deeper, but, there are definitely differences. I will come back later and let you know the outcome, if I can find it. Thanks for your support. jos?
[sqlite] Which CHECK constraint failed
2016-05-09 16:18 GMT+02:00 Dominique Devienne : > On Mon, May 9, 2016 at 3:48 PM, Dominique Devienne > wrote: > > On Mon, May 9, 2016 at 3:46 PM, Dominique Devienne > wrote: > >> On Mon, May 9, 2016 at 3:21 PM, Cecil Westerhof > wrote: > >>> [...] Is there a way to get this information? > >> > >> Just name your CHECK constraint, and use a recent version of SQLite. > --DD > > sqlite> create table tt (v check (v in (0, 1))); > sqlite> insert into tt values (2); > Error: CHECK constraint failed: tt > ?That does the trick. Thanks. ? > Note that w/o a *named* constraint, it seems to just show the table name. > And if you meant a programmatic way to know the failure is a check > constraint, there's > ?Nope, I just wanted to see which of the many checks went wrong. -- Cecil Westerhof
[sqlite] determining is-leap-year in sqlite
On 8 May 2016 at 23:13, Stephan Beal wrote: > On Mon, May 9, 2016 at 5:40 AM, Stephan Beal wrote: > >> That suggests that the script is not consistently telling sqlite which TZ >> to use in all calculations. i will take a look at it as time >> > > just fyi: i can now reproduce the problem on my x64, where my days are > shifted 1 to the left. Not sure what's causing it, but probably won't be > able to look at it until next weekend :/. i apparently broke it at some > point without noticing. Not a problem. Thanks for checking into this and I'm happy to know this is not sqlite problem! -- --- inum: 883510009027723 sip: jungleboogie at sip2sip.info xmpp: jungle-boogie at jit.si
[sqlite] determining is-leap-year in sqlite
On Mon, May 9, 2016 at 7:00 PM, jungle Boogie wrote: > On 8 May 2016 at 23:13, Stephan Beal wrote: > > On Mon, May 9, 2016 at 5:40 AM, Stephan Beal > wrote: > > > >> That suggests that the script is not consistently telling sqlite which > TZ > >> to use in all calculations. i will take a look at it as time > >> > > > > just fyi: i can now reproduce the problem on my x64, where my days are > > shifted 1 to the left. Not sure what's causing it, but probably won't be > > able to look at it until next weekend :/. i apparently broke it at some > > point without noticing. > > > Not a problem. Thanks for checking into this and I'm happy to know > this is not sqlite problem! Something to try out, if you have a machine handy: change all of the strftime date creation references and add " 23:59". i.e. move them to the end of the day. That might offset any calculations being done with the default(?) time of midnight (the _start_ of the day). -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] SELECT DISTINCT question
Hick Gunter gave the documented explanation above.
On Mon, May 9, 2016 at 6:18 PM, dandl wrote:
> The interesting thing about this query is that you can drop any of
> DISTINCT,
> GROUP BY or ORDER BY and get the same result.
>
> But my question was not "how can I rewrite my query?". It was: how does
> Sqlite interpret this SQL, given that it's probably invalid?
>
> Andl generates code for both Sqlite and Postgres, and I need to know what
> that code does.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of Scott Robison
> > Sent: Monday, 9 May 2016 4:13 PM
> > To: SQLite mailing list
> > Subject: Re: [sqlite] SELECT DISTINCT question
> >
> > On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
> >
> > > Just to add to the below:
> > >
> > > S#| SNAME | STATUS | CITY
> > > ---
> > > S1| Smith | 20 | London
> > > S2| Jones | 10 | Paris
> > > S3| Blake | 30 | Paris
> > > S4| Clark | 20 | London
> > > S5| Adams | 30 | Athens
> > >
> > > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
> > >
> > > CITY
> > > --
> > > Paris
> > > London
> > > Athens
> > >
> > > I don't find it easy to explain this result.
> > >
> >
> > My guess based on the available data is that, since you don't specify
> which
> > "S#" you want associated with each city, it is picking the max of each
> > (coincidentally). If you want the minimum S# value, this seems to work:
> >
> > select distinct city from s group by city order by min("S#") asc;
> >
> > I'm not sure if that *should* work per "standard" SQL, but it does with
> > SQLite. I'd have expected something like this to be necessary:
> >
> > select city, min("S#") as x from s group by city order by x asc;
> >
> > And if you only want the city:
> >
> > select city from (select city, min("S#") as x from s group by city order
> by x
> > asc);
> >
> > But I'm not a SQL master.
> >
> > Distinct used with group by seems redundant, but again, I might just not
> > understand how they are useful together.
> >
> > --
> > Scott Robison
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Scott Robison
[sqlite] Good way for CEIL, or is there a better way
On 2016-05-09 4:24 AM, Cecil Westerhof wrote: > I need to have a CEIL function in SQLite. This is the way I implemented it: > WITH percentage AS ( > SELECT date > , 100.0 * rank / outOf AS percentage > , CAST(100.0 * rank / outOf AS int) AS castedPercentage > FROM ranking > ) > SELECT date > , (CASE WHEN percentage = castedPercentage > THEN castedPercentage > ELSE castedPercentage + 1 > END) AS percentage > FROM percentage > > Is this a good way, or is there a better way? The Ceiling function is not that simple, unless you know that your rank and outOf are always non-negative numbers. If they might be negative, you would -1 rather than +1 when the result is negative. -- Darren Duncan
[sqlite] SELECT DISTINCT question
At this point, backward compatibility. Enough people use it expecting it to work that it would be bad to change the behavior. On Mon, May 9, 2016 at 6:42 PM, dandl wrote: > > bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf > > > Why are you using BOTH distinct and group by on the same column? You > only > > need one or the other. If you are redundantly redundant I would hope > that > > the optimizer makes redundant (as in gets rid of, for those that are not > > English) the redundancies ... > > This is generated code. Since Andl does not allow any duplicate rows, every > SELECT gets a DISTINCT unless the query provably cannot generate > duplicates. > You need both GROUP BY and DISTINCT in cases where there is an aggregate > function (and some others). Say: > > SELECT DISTINCT SUM(X) AS Y FROM T GROUP BY Z; > > There is no way to predict from the query how many rows this will generate. > Without DISTINCT it can generate duplicates. > > My question was really about why Sqlite did not complain on what is > actually > not a valid query. [Andl is still a work in progress.] > > Regards > David M Bennett FACS > > Andl - A New Database Language - andl.org > > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Scott Robison
[sqlite] SELECT DISTINCT question
select col1, aggregateFunction(col2) from table group by col3 order by col1
returns the result of the aggregate function applied to all "col2" values in
the "col3" group. The col1 value is the last visited row in the group which
triggered the aggregate, with a special case for MIN and MAX, where the col1
value is from the last visited row which contained the min or max value of col2.
order by is, of course, applied to the result set after it is generated, and
may or may not affect the visitation order.
select col1, col2 from table group by col2
is identical to select col1, distinct col2 from table
in that the table is sorted by col2 and each col2 value is reported only once.
The col1 value is from the last visited row in each group. Order by may be
applied "after the fact" to order the result set. Order by may or may not
affect the visitation order.
(Note that "last visited" is often stated as "some random row" because the
visitation order is an implementation detail of the query planner and may
change from query to query based on the "shape" of the data and the particulars
of how the query is solved internally.)
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of dandl
> Sent: Monday, 9 May, 2016 18:19
> To: 'SQLite mailing list'
> Subject: Re: [sqlite] SELECT DISTINCT question
>
> The interesting thing about this query is that you can drop any of
> DISTINCT,
> GROUP BY or ORDER BY and get the same result.
>
> But my question was not "how can I rewrite my query?". It was: how does
> Sqlite interpret this SQL, given that it's probably invalid?
>
> Andl generates code for both Sqlite and Postgres, and I need to know what
> that code does.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of Scott Robison
> > Sent: Monday, 9 May 2016 4:13 PM
> > To: SQLite mailing list
> > Subject: Re: [sqlite] SELECT DISTINCT question
> >
> > On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
> >
> > > Just to add to the below:
> > >
> > > S#| SNAME | STATUS | CITY
> > > ---
> > > S1| Smith | 20 | London
> > > S2| Jones | 10 | Paris
> > > S3| Blake | 30 | Paris
> > > S4| Clark | 20 | London
> > > S5| Adams | 30 | Athens
> > >
> > > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
> > >
> > > CITY
> > > --
> > > Paris
> > > London
> > > Athens
> > >
> > > I don't find it easy to explain this result.
> > >
> >
> > My guess based on the available data is that, since you don't specify
> which
> > "S#" you want associated with each city, it is picking the max of each
> > (coincidentally). If you want the minimum S# value, this seems to work:
> >
> > select distinct city from s group by city order by min("S#") asc;
> >
> > I'm not sure if that *should* work per "standard" SQL, but it does with
> > SQLite. I'd have expected something like this to be necessary:
> >
> > select city, min("S#") as x from s group by city order by x asc;
> >
> > And if you only want the city:
> >
> > select city from (select city, min("S#") as x from s group by city order
> by x
> > asc);
> >
> > But I'm not a SQL master.
> >
> > Distinct used with group by seems redundant, but again, I might just not
> > understand how they are useful together.
> >
> > --
> > Scott Robison
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Good way for CEIL, or is there a better way
On 2016-05-09 7:54 PM, Rowan Worth wrote: > On 10 May 2016 at 08:31, Darren Duncan wrote: > >> The Ceiling function is not that simple, unless you know that your rank >> and outOf are always non-negative numbers. If they might be negative, you >> would -1 rather than +1 when the result is negative. -- Darren Duncan >> > > Yeah you can't always add one. But you never need to subtract one - ceil > always rounds towards positive infinity which is equivalent to integer > truncation over the range of negative reals. > > I can't see a better way to implement ceil/floor in sqlite than using an > int cast to truncate. Yeah, actually. So if we assume casting to an integer will always truncate aka round towards zero, then the answer is to add 1 to the casted amount if and only if the real percentage is positive and doesn't equal the cast amount. -- Darren Duncan
