Re: [sqlite] SQL question, move union to second column

[Bart Smissaert]

Ah, yes, thanks.

RBS

On Thu, Mar 14, 2019 at 12:55 AM Igor Tandetnik  wrote:

> On 3/13/2019 8:32 PM, Bart Smissaert wrote:
> > Sorry, ignore that, can see now that all is a reserved word.
>
> You can enclose it in double quotes, as in "All", if you really want it.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question, move union to second column

[Igor Tandetnik]

On 3/13/2019 8:32 PM, Bart Smissaert wrote:

Sorry, ignore that, can see now that all is a reserved word.


You can enclose it in double quotes, as in "All", if you really want it.
--
Igor Tandetnik


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

Re: [sqlite] SQL question, move union to second column

[Bart Smissaert]

Sorry, ignore that, can see now that all is a reserved word.

RBS

On Thu, Mar 14, 2019 at 12:30 AM Bart Smissaert 
wrote:

> Thanks; this works fine:
>
> select PLACE,
> sum(ID not in (select ID from ATTENDED)),
> count(ID)
>  from PERSONS group by PLACE
>
> But if I add the aliases I get:
>
> near "All": syntax error
> Result of sqlite3_prepare16_v3: 1
> select PLACE, sum(ID not in (select ID from ATTENDED)) Not_Attended,
> count(*) All from PERSONS group by PLACE
>
> Otherwise very neat indeed though!
>
> RBS
>
>
>
> On Thu, Mar 14, 2019 at 12:21 AM Igor Tandetnik 
> wrote:
>
>> On 3/13/2019 8:08 PM, Bart Smissaert wrote:
>> > But I would like the result to be in 3 columns, so result in this case
>> > would be:
>> >
>> > Place Not_Attended All
>> > ---
>> > A   3  7
>> > B   2  3
>>
>> Something like this (not tested):
>>
>> select PLACE,
>>sum(ID not in (select ID from ATTENDED)) Not_Attended,
>>count(*) All
>> from PERSONS group by PLACE;
>>
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question, move union to second column

[Bart Smissaert]

Thanks; this works fine:

select PLACE,
sum(ID not in (select ID from ATTENDED)),
count(ID)
 from PERSONS group by PLACE

But if I add the aliases I get:

near "All": syntax error
Result of sqlite3_prepare16_v3: 1
select PLACE, sum(ID not in (select ID from ATTENDED)) Not_Attended,
count(*) All from PERSONS group by PLACE

Otherwise very neat indeed though!

RBS



On Thu, Mar 14, 2019 at 12:21 AM Igor Tandetnik  wrote:

> On 3/13/2019 8:08 PM, Bart Smissaert wrote:
> > But I would like the result to be in 3 columns, so result in this case
> > would be:
> >
> > Place Not_Attended All
> > ---
> > A   3  7
> > B   2  3
>
> Something like this (not tested):
>
> select PLACE,
>sum(ID not in (select ID from ATTENDED)) Not_Attended,
>count(*) All
> from PERSONS group by PLACE;
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question, move union to second column

[Igor Tandetnik]

On 3/13/2019 8:08 PM, Bart Smissaert wrote:

But I would like the result to be in 3 columns, so result in this case
would be:

Place Not_Attended All
---
A   3  7
B   2  3


Something like this (not tested):

select PLACE,
  sum(ID not in (select ID from ATTENDED)) Not_Attended,
  count(*) All
from PERSONS group by PLACE;

--
Igor Tandetnik

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

Re: [sqlite] Is it possible to use sqlite online-backup system in an ongoing manner?

[Barry Smith]

I think the sessions extension can be used for what you want to do. I haven't 
used it myself, but from it's documented behaviour it looks like you could 
record change sets for every three second interval then apply them back to your 
database on disk. If your app is multi-threaded it might be a pain (unless 
there's an atomic 'stop this changeset and immediately start another one'

> On 13 Mar 2019, at 8:40 am, Simon Slavin  wrote:
> 
>> On 13 Mar 2019, at 2:31pm, John Smith  wrote:
>> 
>> I am working with IN-MEMORY database.
>> When my program starts I load data from file-system DB into my IN-MEMORY DB.
>> All other SQL operations are performed directly on my IN-MEMORY database.
>> This is in order to keep performance high.
> 
> First, make sure you really need to do this.  SQLite performance is normally 
> very high, even without taking special measures.  You may be wasting 
> programming time and introducing complexity which will be difficult to debug. 
>  Run some time-trials.  Of course, you may have already run some time-trials.
> 
>>// Save only intermediate changes (?)
>>sqlite3_backup_step(p, -1); // Backup all modifications from last time
> 
> You cannot combine these two things.  The Online Backup API backs up an 
> entire database.  It does it page by page, without understanding individual 
> rows of data.  It cannot select only changes.  So you might want to use it, 
> but if you do you'll create a new copy of the entire database every time.
> 
> You might want to instead use the Resumable Bulk Update extension:
> 
> 
> 
> " An RBU Update is a bulk update of a database file that may include many 
> insert, update and delete operations on one or more tables. "
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL question, move union to second column

[Bart Smissaert]

Have 2 tables:

PERSONS:
CREATE TABLE [PERSONS]([ID] INTEGER PRIMARY KEY, [Place] TEXT)

ATTENDED:
CREATE TABLE [ATTENDED]([ID] INTEGER)

Sample date like this:

PERSONS:

ID Place
---
1 A
2 A
3 B
4 A
5 A
6 A
7 B
8 B
9 A
10 A

ATTENDED:

ID
-
1
5
6
1
1
8
9
5
1
5
8
1
6
8
9
9
1
5
6
1

Now I would like to show the counts of persons that not attended, grouped
by place and in a third column the counts of all persons, again grouped by
place.

I can do it differently in 2 columns with a union:

SELECT P.PLACE, COUNT(P.ID) AS P_COUNT FROM PERSONS P LEFT JOIN ATTENDED A
ON(P.ID = A.ID)
WHERE A.ID IS NULL GROUP BY P.PLACE
UNION ALL
SELECT PLACE, COUNT(ID) AS P_COUNT FROM PERSONS
GROUP BY PLACE

But I would like the result to be in 3 columns, so result in this case
would be:

Place Not_Attended All
---
A   3  7
B   2  3

Probably simple, but I can't work it out and thanks for any assistance.

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

[sqlite] Typo - https://www.sqlite.org/see/doc/trunk/www/index.wiki

[Chris Locke]

On the page https://www.sqlite.org/see/doc/trunk/www/index.wiki in the 'key
links' section, there is a link to 'Files in the lastest release of SEE'.
This should be 'latest' and not 'lastest'.


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

Re: [sqlite] SEE

[Simon Slavin]

On 13 Mar 2019, at 10:50pm, Williams, Kevin  wrote:

> Any articles out there giving some hint how we use the SEE version of SQLite 
> we compiled with our C# program that references System.Data.SQLite?

Try this:



and come back if you have specific questions.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SEE

[Williams, Kevin]

Any articles out there giving some hint how we use the SEE version of SQLite we 
compiled with our C# program that references System.Data.SQLite?

Thanks in advance!

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

[sqlite] sqlite3_db_filename returns an empty string when null pointer is promised

[Alex Alabuzhev]

Hi,

https://www.sqlite.org/c3ref/db_filename.html:

> If there is no attached database N on the database connection D, or if
database N is a temporary or in-memory database, then a NULL pointer is
returned.

However, when called for :memory: db the function actually returns "".

Looking at the code:

/*
** Return the full pathname of the database file.
**
** Except, if the pager is in-memory only, then return an empty string if
** nullIfMemDb is true.  This routine is called with nullIfMemDb==1 when
** used to report the filename to the user, for compatibility with legacy
** behavior.  But when the Btree needs to know the filename for matching to
** shared cache, it uses nullIfMemDb==0 so that in-memory databases can
** participate in shared-cache.
*/
SQLITE_PRIVATE const char *sqlite3PagerFilename(Pager *pPager, int
nullIfMemDb){
  return (nullIfMemDb && pPager->memDb) ? "" : pPager->zFilename;
}

- as the comment says, it returns an empty string in case of in-memory mode
(although "nullIfMemDb" confusingly implies null).

I have no idea who is correct here - the code or the documentation - but
one of them should probably be corrected?

Thanks.

-- 
Best regards,
  Alex
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Developer questions about the Online Backup API

[Simon Slavin]

If the source database is changed while the Online Backup API is running, it 
returns to the beginning of the database and starts again.  I have a couple of 
questions which might be useful, especially if the database is changed only by 
the same connection as it performing the backup.

There's a chunk of headers at the beginning of the database which changes 
frequently.  Ignoring that chunk for a moment ... two questions:

1) Suppose the first page of the source database which is modified is  after 
the point that the backup has reached.  Is it necessary to restart ?  Could 
this be detected somehow ?

2) Suppose the first page of the source database which is modified is before 
the point that the backup has reached.  Could the backup not return just to 
that point rather than to the very beginning ?

As for the chunk of headers at the very beginning of the database, this should 
not change size.  It could be updated at the end.

I'm perfectly happy to be told that these optimizations cannot be performed for 
reasons I don't understand.

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

Re: [sqlite] Is it possible to use sqlite online-backup system in an ongoing manner?

[Simon Slavin]

On 13 Mar 2019, at 2:31pm, John Smith  wrote:

> I am working with IN-MEMORY database.
> When my program starts I load data from file-system DB into my IN-MEMORY DB.
> All other SQL operations are performed directly on my IN-MEMORY database.
> This is in order to keep performance high.

First, make sure you really need to do this.  SQLite performance is normally 
very high, even without taking special measures.  You may be wasting 
programming time and introducing complexity which will be difficult to debug.  
Run some time-trials.  Of course, you may have already run some time-trials.

> // Save only intermediate changes (?)
> sqlite3_backup_step(p, -1); // Backup all modifications from last time

You cannot combine these two things.  The Online Backup API backs up an entire 
database.  It does it page by page, without understanding individual rows of 
data.  It cannot select only changes.  So you might want to use it, but if you 
do you'll create a new copy of the entire database every time.

You might want to instead use the Resumable Bulk Update extension:



" An RBU Update is a bulk update of a database file that may include many 
insert, update and delete operations on one or more tables. "

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

[sqlite] Is it possible to use sqlite online-backup system in an ongoing manner?

[John Smith]

I am working with IN-MEMORY database.
When my program starts I load data from file-system DB into my IN-MEMORY DB.
All other SQL operations are performed directly on my IN-MEMORY database.
This is in order to keep performance high.

However, I have a requirement that my original file-system database will remain 
updated with the program modifications every few seconds.
My idea to implement this was to have a worker-thread that will work as follows:

void WorkerThread()
{
// Initialize SQLite online-backup ONCE:
p = sqlite3_backup_init(...);

loop{
Sleep(5 seconds);

 // Save only intermediate changes (?)
 sqlite3_backup_step(p, -1); // Backup all modifications from last time

}  while( program is running);

// No program is exiting...
// Release object resources
sqlite3_backup_finish(p);
}

The problem is that I see that first time around all data is saved, but all 
follwing calls to 'sqlite3_backup_step()' do not save anything.

My question:
Is there a way to use this online-backup system in an incremental way: that it 
will save only difference from last time BUT ALL the difference from last time?

Many thanks, John
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Apparent power fail data loss in embedded use - SQLite newbie

[Hick Gunter]

If the original author bothered to set TEMP_STORE to "always memory", I would 
take that as a strong indication that they are using TEMP tables for 
performance reasons.

Off the top of my head I would probably do:

CREATE TABLE log (id integer primary key, timestamp integer, data_item_1 float, 
...);
CREATE TEMP TABLE log_buffer (id integer primary key, timestamp integer, 
data_item_1 float, ...);

Insert 256 rows into log_buffer with id 0..255.

Keep track of the current id in software.

Logging executes the prepared statement: UPDATE log_buffer SET ... WHERE id = 
:CURR; and increments the rowid

If the current rowid hits 128 (or 256, where it wraps to 0 without extra action 
if you use an uint8_t):

BEGIN;
INSERT INTO log (SELECT * FROM log_buffer WHERE id < 128);
UPDATE log_buffer SET ... WHERE id <128);
COMMIT;

This would copy the buffered records to persistent storage while significantly 
reduding the IO load to flash. The copy over is fast and thus reduces the risk 
of corruption.  Any data in the TEMP table would be lost during a hard power 
fail. Too bad. A copy over interrupted by the hard power fail would be rolled 
back on recovery. Hard luck. But the database would be quite resistant to 
corruption

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Ted Goldblatt
Gesendet: Dienstag, 12. März 2019 22:42
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Apparent power fail data loss in embedded use 
- SQLite newbie

On Tue, Mar 12, 2019 at 12:17 PM Simon Slavin  wrote:

> Other posters have taken care of very important aspects of your
> circumstances, but I wanted to mention one I didn't see anyone mention.
> Settings.
>
> If you compile SQLite without changing compilation settings, and use
> it without changing defaults, SQLite is extremely good at avoiding
> corruption, and at recovering after corruption.  This includes
> corruption due to power-loss at any stage while changes are being made to the 
> database.
>
> However, settings can be made which improve SQLite for some specific
> uses.  They make it faster.  Or use less memory.  Or use less
> filespace while working.  Unfortunately some of them all sacrifice
> harness against corruption.
>
> These settings can be made at three (or more ?  not sure) different places:
>
> 1) Compilation settings when the SQLite API is compiled
> 2) Extra parameters passed when the database is opened
> 3) PRAGMA settings made at any time while the database is open
>
> To assess how 'hard' your use of SQLite is against corruption, you
> would have to track down whether any of the above three have been done.
>
> 1) May or may not be easy.  Do you know how SQLite is included in your
> project ?  Is it part of a library downloaded from somewhere or did
> your programmer compile it themself ?  If the former, you can assume
> that whoever prepared the library didn't mess with default settings.
> If the latter, can you track down the compilation settings they used ?
>
SQLite is built from (the combined) source as part of the project build. I 
don't see signs in the project options that any special (SQLite) settings are 
used.  However, the interface is done through the CppSQLite3 interface layer.  
(There have been some local changes to CppSQLite3, but they appear to be 
limited to adding error logging.)  There is also a local header file (that is, 
one written as part of the project) with what appear to be SQLite compilation 
options (SQLITE_OMIT_xxx, SQLITE_DEFAULT_CACHE_SIZE, SQLITE_THREADSAFE, etc.  
One of these is SQLITE_TEMP_STORE, which is set to "Always use memory" which 
strikes me a suspicious relative to a power fail problem.  However, I cannot 
find anything that seems to reference most of these, and specifically not the 
TEMP_STORE define.  And this file is only included by a project specific DB 
interface file that invokes CppSQLite3 methods but not by sqlite3.c, so I'm not 
sure of the point.

>
> 2) Do you have the source code for your project ?  Can you find all
> places where a database is opened ?  If it uses the SQLite API
> directly you can just search for "sqlite3_open".  See whether you can
> spot whether anything except file name & path are passed.
>
I have full sources.   The DB opens are directly by CppSQLite3 (which uses
sqlite_open_v2()), and all of those calls have OPEN_READONLY or OPEN_READWRITE 
as the 3rd param and 0 as the 4th.  The CppSQLite3 methods themselves take only 
a filename.

3) Do you have the source code for your project ?  Can you do a global
> search for "PRAGMA" ?  Only a few of the PRAGMAs reduce integrity.
> Most of them are fine.  But you can look them up and see for yourself.
>
 It doesn't appear that any PRAGMAs appear outside the CppSQLite3_16.cpp and 
sqlite3.h files.

>
> The whole of the above is merely me being picky.  Millions of SQLite
> users just leave all settings at their defaults.  But it seems to be
> the sort of thing