Re: [sqlite] Save text file content in db: lines or whole file?

[Abramo Bagnara]

Il 03/08/2018 23:53, Abroży Nieprzełoży ha scritto:
> -- One table with whole files
> CREATE TABLE content(
> id INTEGER PRIMARY KEY,
> data BLOB NOT NULL
> );
> -- And second table with line boundaries
> CREATE TABLE lines(
> id INTEGER NOT NULL REFERENCES content(id),
> line_no INTEGER NOT NULL,
> bytes_from INTEGER NOT NULL,
> bytes_to INTEGER NOT NULL,
> PRIMARY KEY(id, line_num)
> ) WITHOUT ROWID;
> -- Use Incremental BLOB I/O https://www.sqlite.org/c3ref/blob_open.html

Incremental BLOB I/O is faster than using substr(data, start, len) in a
SELECT?


-- 
Abramo Bagnara

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

[sqlite] virtual table module not found needs better logging

[Ben Manashirov]

 if( pMod==0 || pMod->pModule->xCreate==0 || pMod->pModule->xDestroy==0 ){
*pzErr = sqlite3MPrintf(db, "no such module: %s", zMod);
rc = SQLITE_ERROR;
}else{
rc = vtabCallConstructor(db, pTab, pMod, pMod->pModule->xCreate, pzErr);
}


That code doesn't tell me I have not implemented xDestroy.. I forgot to
implement xDestroy and was wondering for a while why it was returning no
such module. It would be better to add another check for xCreate & xDestroy
separately to report that the module is found but doesn't have the
requirement methods. Same goes for other mandatory methods. I haven't
tested if other mandatory methods are checked but for sqlite3_create_module
it should ideally check that the module has all the mandatory methods
implemented and report it to the log. xDestroy in version 3.18 is not
reported. I didn't check latest version of sqlite, but I looked at the
release notes all the way up to latest and didn't see anything mentioned
regarding this.

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

Re: [sqlite] Using CTE with date comparison

[Kees Nuyt]

On Fri, 3 Aug 2018 15:11:06 +0200, Csányi Pál
 wrote:

>The database is so far with only one table:
>CREATE TABLE MyLengthOfService (
>id  INT  PRIMARY KEY
> UNIQUE,
>WorkPlaceName   TEXT,

You shouldn't use UNIQUE for the PRIMARY KEY.
Any PRIMARY KEY is implicitly unique by itself.
Adding the UNIQUE keyword might create an extra, redundant,
index, which will eat file space and processing time without
having any added value. And it may confuse the query optimizer.

The idea is that any set (table) may have more than one key to
uniquely identify a tuple (row).
Each key is called a candidate key.

Only one of those keys can be choosen to be the PRIMARY KEY.
All other candidate keys can get honored by adding the UNIQUE
clause, to recognize them as alternative keys.

-- 
Regards,
Kees Nuyt

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

Re: [sqlite] Save text file content in db: lines or whole file?

[Richard Hipp]

On 8/3/18, Abroży Nieprzełoży  wrote:
>
> I would also consider keeping the largest files external to the
> database. https://www.sqlite.org/intern-v-extern-blob.html
>

See also https://www.sqlite.org/fasterthanfs.html

Yes, it is a little faster to store larger files directly in the
filesystem.  But not that much faster.  And it is certainly convenient
to have all your content in one place and to have transactional reads
and writes.  So, unless there is a compelling need for an extra 10%
performance, I would argue for keeping all content in the database
file.

FWIW, I also think you should store your files as a single big blob
and parse out the individual lines using C++ code when you need that.

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

Re: [sqlite] Save text file content in db: lines or whole file?

[Abroży Nieprzełoży]

-- One table with whole files
CREATE TABLE content(
id INTEGER PRIMARY KEY,
data BLOB NOT NULL
);
-- And second table with line boundaries
CREATE TABLE lines(
id INTEGER NOT NULL REFERENCES content(id),
line_no INTEGER NOT NULL,
bytes_from INTEGER NOT NULL,
bytes_to INTEGER NOT NULL,
PRIMARY KEY(id, line_num)
) WITHOUT ROWID;
-- Use Incremental BLOB I/O https://www.sqlite.org/c3ref/blob_open.html

I would also consider keeping the largest files external to the
database. https://www.sqlite.org/intern-v-extern-blob.html

2018-08-03 21:04 GMT+02:00, Abramo Bagnara :
>
> I need to save text files (let say between 1 KB to 20 MB) in a SQLite DB.
>
> I see two possibilities:
>
> 1) save all the content in a single column:
>
> create table content(id integer not null primary key,
>  text blob not null);
>
> 2) split the content in lines:
>
> create table line(content integer not null,
> line integer not null,
>   text blob not null,
>   primary key(content, line));
>
> Some queries will need to extract the whole file, while other queries
> will need to extract the text for a range of lines.
>
> According to your experience it is better/faster the first option, the
> second option or a smarter option I've not considered?
>
> My partial considerations are:
>
> - 1 is simpler
> - 1 leads to faster load
> - 1 is slower to retrieve a range of lines (not 100% sure)
>
> --
> Abramo Bagnara
> ___
> 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] Common index for multiple databases

[Keith Medcalf]

On Friday, 3 August, 2018 13:50, Warren Young  wrote:

>I’d be careful trying to apply your knowledge directly to SQLite.
>dBase comes out of the non-SQL world, so it’s going to have a
>different outlook in many areas.

>If the following is a fair description of how FoxPro for DOS indexes
>work, then I question how well your knowledge transfers to SQLite:

>   https://docs.microsoft.com/sql/odbc/microsoft/index-command

Not really.  This is documentation on the ODBC SQL interface.  What you really 
want is to refer to "native" xBase documentation:

http://www.dbase.com/help/Xbase/IDH_XBASE_INDEX.htm

>Compare:

>https://sqlite.org/lang_createindex.html

>The Venn diagram of these two documentation pages seems to have a
>pretty small region of overlap.

The overlap is actually 100% with only a few xBase specific differences due to 
the primitive nature of the bitty-boxen on which it was developed.

The only real differences are:
  in the definition of "UNIQUE" which in the xBase world does not mean 
"UNIQUE", the xBase equivalent is "DISTINCT"  
  xBase indexes are a single text field only of fixed length

This is so annoying that I had pre-processor macros that allowed you to define 
indexes using the SQL "CREATE INDEX" syntax and translated that into the 
expression format required by xBase in order to ensure that the primitive text 
indexes were generated coherently (which is much easier to do if you automate 
the process) and also to make the LOOKUP and SEEK functions work correctly ...


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

[sqlite] Save text file content in db: lines or whole file?

[Abramo Bagnara]

Il 03/08/2018 21:50, Simon Slavin ha scritto:
> On 3 Aug 2018, at 8:04pm, Abramo Bagnara  wrote:
> 
>> Some queries will need to extract the whole file, while other queries
>> will need to extract the text for a range of lines.
> 
> Can you give us an idea of how many lines you expect per text file ?  Minimum 
> and maximum for 90% of the files would be good.

They are (possibly preprocessed) source files. Then the size is not
known, as I wrote I assume it is in the range 1KB, 20MB, with most cases
under 1MB.

> 
> Also, in whatever programming language you're using, it is easy/fast or 
> difficult/slow to locate lines 21 to 41 of a 100-line text string ?

The language is C++. Without caching it needs a linear scan of bytes
from the beginning counting newlines for every lines range query. With
caching this counting can be done once and saved somewhere.

The lines will be served to an infinite scroll web app, so the retrieval
speed is important to reduce the latency.

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

Re: [sqlite] Common index for multiple databases

[Simon Slavin]

On 3 Aug 2018, at 7:52pm, John R. Sowden  wrote:

> My concern in using Sqlite is since the index is embedded into the database 
> file with various tables, if I am running multiple Sqlite database files, how 
> do I use a common index for the different database files.

Okay.  Thanks for that clarification.  From the above, the thing you're calling 
"index" is not the things we're calling "index" and this has led to a lot of 
confusion.

Can you please post an example of "a common index for the different database 
files", or point us to an example or some documentation on it ?  It's not a 
concept we're used to.

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

Re: [sqlite] mmap, madvise, mlock and performance

[Warren Young]

On Aug 3, 2018, at 1:36 PM, Shevek  wrote:
> 
> the database is read-only

In that case, I’d just create a :memory: DB on application startup, attach to 
the disk copy, use the INSERT FROM … SELECT pattern [1] to clone the data 
content within a single transaction, create the indexes, and detach from the 
on-disk copy of the DB.

[1]: https://stackoverflow.com/a/4291203
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] mmap, madvise, mlock and performance

[Simon Slavin]

On 3 Aug 2018, at 8:36pm, Shevek  wrote:

> We are running a 100Gb sqlite database, which we mmap entirely into RAM. We 
> are having trouble with parts of the disk file being evicted from RAM during 
> periods of low activity causing slow responses, particularly before 9am. Has 
> anybody played with mlock and/or madvise within the sqlite mmap subsystem to 
> improve this behaviour?

Is this a genuine Linux machine running on physical hardware, or is it a 
virtual machine ?

Are you intentionally doing anything that would contend for this memory ?  In 
other words, when a memory-mapped portion gets swapped out, does it make sense 
what replaced it, or is it pointless and weird ?

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

Re: [sqlite] Save text file content in db: lines or whole file?

[Simon Slavin]

On 3 Aug 2018, at 8:04pm, Abramo Bagnara  wrote:

> Some queries will need to extract the whole file, while other queries
> will need to extract the text for a range of lines.

Can you give us an idea of how many lines you expect per text file ?  Minimum 
and maximum for 90% of the files would be good.

Also, in whatever programming language you're using, it is easy/fast or 
difficult/slow to locate lines 21 to 41 of a 100-line text string ?

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

Re: [sqlite] Common index for multiple databases

[Warren Young]

On Aug 3, 2018, at 12:52 PM, John R. Sowden  wrote:
> 
> I have the xbase type of databases down tight

I’d be careful trying to apply your knowledge directly to SQLite.  dBase comes 
out of the non-SQL world, so it’s going to have a different outlook in many 
areas.

> I understand what an index is.

If the following is a fair description of how FoxPro for DOS indexes work, then 
I question how well your knowledge transfers to SQLite:

   https://docs.microsoft.com/sql/odbc/microsoft/index-command

Compare:

https://sqlite.org/lang_createindex.html

The Venn diagram of these two documentation pages seems to have a pretty small 
region of overlap.

> how do I use a common index for the different database files.

It makes no sense to talk about using an index across multiple SQLite DB files. 
 The index data structure in SQLite has page offsets within that file, which is 
how SQLite uses the index to look up the data the index entry refers to.  The 
same page offset in a different file will contain different data; the other 
file might not even *have* such a page number!

A book’s index cannot be used to look up information in other books for the 
same reason.  If a book’s index says the information you want is on page 42, it 
is not on page 42 in all books, only in *that* book.

> I won't provide technicians with accounts receivable databases, etc.

SQLite will let you put as many tables in a DB file as you want, from one table 
per DB file to all tables in one DB file, or anything in between.

A SQLite-based application can attach to multiple database files using a single 
connection, so that the application that has legitimate need of accounts data 
can attach to that DB file, while other applications can attach to the DB 
file(s) it needs.

https://www.sqlite.org/lang_attach.html

> My thinking is along the line of all mission critical clocks take their 
> accuracy from the US Naval Observatory in Fort Collins, CO, instead of 
> thousands of free running clocks, each with what it thinks is the correct 
> time.

I don’t see how that analogy applies to SQLite.

I think my book analogy is a reasonable high-level approximation to the way 
SQLite indexes work, once created, at a static level.

Once you start modifying data, the analogy breaks down, but then you can start 
to think about how a computerized book indexing program would work, and the 
analogy continues to function.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] mmap, madvise, mlock and performance

[Shevek]

Hi,

We are running a 100Gb sqlite database, which we mmap entirely into RAM. 
 We are having trouble with parts of the disk file being evicted from 
RAM during periods of low activity causing slow responses, particularly 
before 9am. Has anybody played with mlock and/or madvise within the 
sqlite mmap subsystem to improve this behaviour?


The system has a few hundred gig of RAM, no swap, the database is 
read-only, and we would prefer a page-out to a process crash, so mlock 
might not be ideal, but madvise might not be strong enough?


Thank you.

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

Re: [sqlite] Common index for multiple databases

[Keith Medcalf]

Not exactly.  The index is stored in the SAME FILE that contains the table.  
For example in dBase I (or II or III) you might have the following files:

Customer.DBF
CustNo.NDX
CustName.NDX

where the two NDX files index fields from the Customer.DBF file.  In FoxPro you 
can have "compound" indexes so that the two indexes above are stored in one 
file, giving you (for example)

Customer.DBF
Customer.CDF

The indexes in the Customer CDF file are generated from and only can be used 
with the Customer.DBF table (and the indexes must be in-sync with the data in 
the Customer.DBF table).

SQLite3 "requires" that you store the indexes in the same file as contains the 
table data to ensure that they are all updated as a unit and remain consistent, 
so you might have a file called Customer.db

Customer.db

This has nothing to do with indexes on OTHER TABLES/FILES such as you might 
have a file called

Accounts.DBF
AcctCust.NDX
AcctInvN.NDX

in which the AcctCust is an index on the Customer field in the Accounts table 
(and has nothing whatsoever to do with the Customer DBF or Indexes) even though 
it mayhaps contains the same customer numbers as the CustNo.NDX so that you can 
associate the "Customer" record from the Customer DBF with the matching record 
from the "Accounts" record contained in the Accounts DBF.

These might all be stored in a file called

Accounts.db

If you do not "give out" a copy of the Accounts.db then that information cannot 
be accessed.

>index is.  My concern in using Sqlite is since the index is embedded
>into the database file with various tables, if I am running multiple
>Sqlite database files, how do I use a common index for the different
>database files.  I won't provide technicians with accounts receivable
>databases, etc.

You cannot use a "common index" for anything, anywhere, at any time, ever in 
the history of the Universe.  The index "belongs" to the thing it is indexing 
and contains information from only that thing and no other source.  You may 
have multiple tables containing customer numbers, and multiple indexes indexing 
each of those customer numbers for each table containing them, but you cannot 
have a "common index" which indexes two data tables.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of John R. Sowden
>Sent: Friday, 3 August, 2018 12:52
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Common index for multiple databases
>
>I have the xbase type of databases down tight, having been using them
>since I bought my copy of dBASE II from George Tate of Ashton-Tate at
>a
>West Coast Computer Faire in 1981.  I have been writing applications
>for
>my alarm company, now through Foxpro 2.6 in DOS.  I understand what
>an
>index is.  My concern in using Sqlite is since the index is embedded
>into the database file with various tables, if I am running multiple
>Sqlite database files, how do I use a common index for the different
>database files.  I won't provide technicians with accounts receivable
>databases, etc.
>
>My thinking is along the line of all mission critical clocks take
>their
>accuracy from the US Naval Observatory in Fort Collins, CO, instead
>of
>thousands of free running clocks, each with what it thinks is the
>correct time.
>
>John
>
>On 08/03/2018 02:48 AM, Ling, Andy wrote:
>>> another point that I did not make clear. The accounting programs
>are not associated with the technical programs, different people,
>different security access. The tech databases and programs are in
>portable computers that go out in the field, but not the accounting,
>etc. There indexes would have to be updated when the computers are
>back at the office.
>> I have a feeling that what the OP is calling an index isn’t really.
>I think what he is talking about is the list of customer IDs.
>> So customer data can get updated “back at the office” and when the
>portable computers get back they need to be updated
>> with the changes.
>>
>> In sqlite terms, an index is a sorted list of the data in one or
>more columns of a table to help speed up access to the data in that
>table.
>> Once defined, the index is automatically updated by sqlite as the
>data in the table is changed.
>>
>> Perhaps we could get some clarification from the OP about what is
>actually wanted.
>>
>> Regards
>>
>> Andy Ling
>>
>>
>>
>>
>>
>>
>*
>*
>> DISCLAIMER:
>> Privileged and/or Confidential information may be contained in this
>message. If you are not the addressee of this message, you may not
>copy, use or deliver this message to anyone. In such event, you
>should destroy the message and kindly notify the sender by reply e-
>mail. It is understood that opinions or conclusions that do not
>relate to the official business of the company 

Re: [sqlite] Common index for multiple databases

[Richard Hipp]

On 8/3/18, John R. Sowden  wrote:
> I have the xbase type of databases down tight, having been using them
> since I bought my copy of dBASE II from George Tate of Ashton-Tate at a
> West Coast Computer Faire in 1981.

Are you using indexes to impose uniqueness constraints across multiple tables?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Save text file content in db: lines or whole file?

[Abramo Bagnara]

I need to save text files (let say between 1 KB to 20 MB) in a SQLite DB.

I see two possibilities:

1) save all the content in a single column:

create table content(id integer not null primary key,
 text blob not null);

2) split the content in lines:

create table line(content integer not null,
  line integer not null,
  text blob not null,
  primary key(content, line));

Some queries will need to extract the whole file, while other queries
will need to extract the text for a range of lines.

According to your experience it is better/faster the first option, the
second option or a smarter option I've not considered?

My partial considerations are:

- 1 is simpler
- 1 leads to faster load
- 1 is slower to retrieve a range of lines (not 100% sure)

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

Re: [sqlite] Common index for multiple databases

[John R. Sowden]

I have the xbase type of databases down tight, having been using them 
since I bought my copy of dBASE II from George Tate of Ashton-Tate at a 
West Coast Computer Faire in 1981.  I have been writing applications for 
my alarm company, now through Foxpro 2.6 in DOS.  I understand what an 
index is.  My concern in using Sqlite is since the index is embedded 
into the database file with various tables, if I am running multiple 
Sqlite database files, how do I use a common index for the different 
database files.  I won't provide technicians with accounts receivable 
databases, etc.


My thinking is along the line of all mission critical clocks take their 
accuracy from the US Naval Observatory in Fort Collins, CO, instead of 
thousands of free running clocks, each with what it thinks is the 
correct time.


John

On 08/03/2018 02:48 AM, Ling, Andy wrote:

another point that I did not make clear. The accounting programs are not 
associated with the technical programs, different people, different security 
access. The tech databases and programs are in portable computers that go out 
in the field, but not the accounting, etc. There indexes would have to be 
updated when the computers are back at the office.

I have a feeling that what the OP is calling an index isn’t really. I think 
what he is talking about is the list of customer IDs.
So customer data can get updated “back at the office” and when the portable 
computers get back they need to be updated
with the changes.

In sqlite terms, an index is a sorted list of the data in one or more columns 
of a table to help speed up access to the data in that table.
Once defined, the index is automatically updated by sqlite as the data in the 
table is changed.

Perhaps we could get some clarification from the OP about what is actually 
wanted.

Regards

Andy Ling





**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank You.
___
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] Using CTE with date comparison

[Csányi Pál]

2018-08-03 13:09 GMT+02:00 R Smith :
>
>
> On 2018/08/03 12:35 PM, Csányi Pál wrote:
>>
>> 2018-08-02 23:12 GMT+02:00 R Smith :
>>>
>>> On 2018/08/02 10:29 PM, Csányi Pál wrote:

 Hi,

 I just want to know why the following SQLite query does not work as I
 expected?

 WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
 date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
 date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
 2017-10-03|3
 which is not what I am expecting.

 I am expecting the followings:
 1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
 2. then it compares the two dates: 2016-10-03 with 2016-10-03
 3. because 2016-10-03 = 2016-10-03 it count 1
 4. then add to the result date 2016-10-03 once again 1 year which is
 2017-10-03
 5. then it compares the two dates: 2017-10-03 with 2016-10-03
 6. because 2017-10-03 > 2016-10-03 it does not count 2
 7. it should gives the following result:
 2016-10-03|1

 What am I doing wrong here?

>>> When the recursive Query starts up, the first value that it outputs is
>>> given
>>> by the very first part of the query, namely: VALUES('2015-10-03')
>>> So on the first iteration, it will produce one row like this:
>>> '2015-10-03'
>>> regardless of the rest of the Query. This row is pushed into the
>>> recursion
>>> buffer.
>>>
>>> After that it then reads a row from the recursion buffer and checks
>>> (within
>>> the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
>>> '2016-11-01', and finds that it definitely IS less, so continues to
>>> produce
>>> the another line of output.
>>>
>>> The output created is that date from the buffer (2015-10-03) which is put
>>> through the given calculation: date(dateD, '+1 year') to give:
>>> '2016-10-03'
>>>
>>> It then continues to push that next row into the recursion buffer and
>>> next
>>> reads again from it and again checks if it (2016-10-03) is <= than
>>> 2016-11-01, which again it is... so it continues to produce the next
>>> output
>>> row, which after calculation becomes:
>>> '2017-10-03'
>>>
>>> It then continues to push that again into the buffer and again read it
>>> and
>>> again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME,
>>> it
>>> isn't... so it stops right there.
>>>
>>> So in the end, it has produced 3 output rows namely:
>>> '2015-10-03'
>>> '2016-10-03'
>>> '2017-10-03'
>>>
>>> Which is exactly what you've asked for.
>>>
>>> Note: The first part of the query will ALWAYS reach the output buffer,
>>> even
>>> if it isn't a recursive query, and the UNION is NOT specified, you will
>>> get
>>> at least the '2015-10-03' value.
>>> Note: When comparing in the WHERE clause, you do not compare the newly
>>> calculated value (date(xxx, +1 year)), but indeed you compare the
>>> before-calculated value, i.e. the previous value in the buffer (the same
>>> as
>>> how your calculation is done on the PREVIOUS value in the buffer to yield
>>> the new date with.
>>>
>>> I hope that helps to make sense.
>>>
>>> Cheers,
>>> Ryan
>>
>> I think then that that the following SQL query gives to me the desired
>> result:
>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>> date('2016-11-01') ) SELECT max(dateD), count(dateD)-2 FROM dates;
>>
>> count(dateD)-2 does the math. Right?
>>
>
> That depends on what the desired result is. Do you want to count how many
> years elapsed (in full) since 3 October 2015?  Count()-2 is the worst hack
> for this (unless the question is specifically "what is 2 less than the
> number of years between x and y").
>
> Do you want a recursive query that will add all years between 3 October and
> some other date, but NOT the first year and NOT the last year?
>
> Maybe if you explain what is the question you are asking, and perhaps
> provide 2 examples, the one you already done is fine, but give another one
> where the dates are from 2015-10-03 to 2025-11-01 or such, how must that
> date-range be answered by the query?

I will try to explain what is the question and will provide two or
three examples.

I am working on a SQLite database which I want to use for calculation
for the Length Of Service for  me as a worker.
The database is so far with only one table:
CREATE TABLE MyLengthOfService (
id  INT  PRIMARY KEY
 UNIQUE,
WorkPlaceName   TEXT,
StartDate   DATE,
EndDate DATE,
WithWorkingTime INT,
Comment TEXT,
Years   INT,
RemainingMonths INT,
RemainingDays   INT
);

I want to create a Trigger which would calculate Years,
RemainingMonths, and RemainingDays for a WorkPlace.

So far the Trigger is this:

UPDATE MyLengthOfService SET Years = ( WITH RECURSIVE dates(dateD) AS
( SELECT StartDate FROM MyLengthOfService WHERE 

Re: [sqlite] Using CTE with date comparison

[R Smith]

On 2018/08/03 12:35 PM, Csányi Pál wrote:

2018-08-02 23:12 GMT+02:00 R Smith :

On 2018/08/02 10:29 PM, Csányi Pál wrote:

Hi,

I just want to know why the following SQLite query does not work as I
expected?

WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
2017-10-03|3
which is not what I am expecting.

I am expecting the followings:
1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
2. then it compares the two dates: 2016-10-03 with 2016-10-03
3. because 2016-10-03 = 2016-10-03 it count 1
4. then add to the result date 2016-10-03 once again 1 year which is
2017-10-03
5. then it compares the two dates: 2017-10-03 with 2016-10-03
6. because 2017-10-03 > 2016-10-03 it does not count 2
7. it should gives the following result:
2016-10-03|1

What am I doing wrong here?


When the recursive Query starts up, the first value that it outputs is given
by the very first part of the query, namely: VALUES('2015-10-03')
So on the first iteration, it will produce one row like this:
'2015-10-03'
regardless of the rest of the Query. This row is pushed into the recursion
buffer.

After that it then reads a row from the recursion buffer and checks (within
the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
'2016-11-01', and finds that it definitely IS less, so continues to produce
the another line of output.

The output created is that date from the buffer (2015-10-03) which is put
through the given calculation: date(dateD, '+1 year') to give:
'2016-10-03'

It then continues to push that next row into the recursion buffer and next
reads again from it and again checks if it (2016-10-03) is <= than
2016-11-01, which again it is... so it continues to produce the next output
row, which after calculation becomes:
'2017-10-03'

It then continues to push that again into the buffer and again read it and
again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME, it
isn't... so it stops right there.

So in the end, it has produced 3 output rows namely:
'2015-10-03'
'2016-10-03'
'2017-10-03'

Which is exactly what you've asked for.

Note: The first part of the query will ALWAYS reach the output buffer, even
if it isn't a recursive query, and the UNION is NOT specified, you will get
at least the '2015-10-03' value.
Note: When comparing in the WHERE clause, you do not compare the newly
calculated value (date(xxx, +1 year)), but indeed you compare the
before-calculated value, i.e. the previous value in the buffer (the same as
how your calculation is done on the PREVIOUS value in the buffer to yield
the new date with.

I hope that helps to make sense.

Cheers,
Ryan

I think then that that the following SQL query gives to me the desired result:
WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
date('2016-11-01') ) SELECT max(dateD), count(dateD)-2 FROM dates;

count(dateD)-2 does the math. Right?



That depends on what the desired result is. Do you want to count how 
many years elapsed (in full) since 3 October 2015?  Count()-2 is the 
worst hack for this (unless the question is specifically "what is 2 less 
than the number of years between x and y").


Do you want a recursive query that will add all years between 3 October 
and some other date, but NOT the first year and NOT the last year?


Maybe if you explain what is the question you are asking, and perhaps 
provide 2 examples, the one you already done is fine, but give another 
one where the dates are from 2015-10-03 to 2025-11-01 or such, how must 
that date-range be answered by the query?




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

Re: [sqlite] Using CTE with date comparison

[Csányi Pál]

2018-08-02 23:12 GMT+02:00 R Smith :
> On 2018/08/02 10:29 PM, Csányi Pál wrote:
>>
>> Hi,
>>
>> I just want to know why the following SQLite query does not work as I
>> expected?
>>
>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>> date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
>> 2017-10-03|3
>> which is not what I am expecting.
>>
>> I am expecting the followings:
>> 1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
>> 2. then it compares the two dates: 2016-10-03 with 2016-10-03
>> 3. because 2016-10-03 = 2016-10-03 it count 1
>> 4. then add to the result date 2016-10-03 once again 1 year which is
>> 2017-10-03
>> 5. then it compares the two dates: 2017-10-03 with 2016-10-03
>> 6. because 2017-10-03 > 2016-10-03 it does not count 2
>> 7. it should gives the following result:
>> 2016-10-03|1
>>
>> What am I doing wrong here?
>>
>
> When the recursive Query starts up, the first value that it outputs is given
> by the very first part of the query, namely: VALUES('2015-10-03')
> So on the first iteration, it will produce one row like this:
> '2015-10-03'
> regardless of the rest of the Query. This row is pushed into the recursion
> buffer.
>
> After that it then reads a row from the recursion buffer and checks (within
> the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
> '2016-11-01', and finds that it definitely IS less, so continues to produce
> the another line of output.
>
> The output created is that date from the buffer (2015-10-03) which is put
> through the given calculation: date(dateD, '+1 year') to give:
> '2016-10-03'
>
> It then continues to push that next row into the recursion buffer and next
> reads again from it and again checks if it (2016-10-03) is <= than
> 2016-11-01, which again it is... so it continues to produce the next output
> row, which after calculation becomes:
> '2017-10-03'
>
> It then continues to push that again into the buffer and again read it and
> again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME, it
> isn't... so it stops right there.
>
> So in the end, it has produced 3 output rows namely:
> '2015-10-03'
> '2016-10-03'
> '2017-10-03'
>
> Which is exactly what you've asked for.
>
> Note: The first part of the query will ALWAYS reach the output buffer, even
> if it isn't a recursive query, and the UNION is NOT specified, you will get
> at least the '2015-10-03' value.
> Note: When comparing in the WHERE clause, you do not compare the newly
> calculated value (date(xxx, +1 year)), but indeed you compare the
> before-calculated value, i.e. the previous value in the buffer (the same as
> how your calculation is done on the PREVIOUS value in the buffer to yield
> the new date with.
>
> I hope that helps to make sense.
>
> Cheers,
> Ryan

I think then that that the following SQL query gives to me the desired result:
WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
date('2016-11-01') ) SELECT max(dateD), count(dateD)-2 FROM dates;

count(dateD)-2 does the math. Right?

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

Re: [sqlite] Common index for multiple databases

[Paul Sanderson]

On 2 August 2018 at 20:08, Keith Medcalf  wrote:

Further to this, you can "emulate" the current structure by creating
multiple databases each containing only the tables needed for that "bit" of
your application.  For example, you can create a customers.db containing
the customers table and all the indexes associated with the customers
table.  You can also create an accounting.db containing all the accounting
tables (but not the customers table) and all the indexes that belong to
those tables.  Lather, Rinse, Repeat.  Create multiple databases and do not
duplicate table names -- that is each table goes in one and only one
database).

You then "open" a :memory: database and "ATTACH" your other database hunks
as required for the application.  You refer to the tables only by table
name and provided that you have not duplicated tables in multiple database
files, then SQLite3 will operate on the appropriate attached database.
Since the indexes associated with a table must be stored in the same
database file as the data table itself, all the indexes will be kept
up-to-date.  You will not be able to have the database enforce referential
integrity across multiple "attached" databases, nor use cross "attachment"
triggers (but you cannot do that now anyway).

---

If you do, for some reason, choose this approach then by default SQLite
limits the number of attached databases to 10, you can adjust this up to a
max of 125. More info below:

https://www.sqlite.org/limits.html

Paul
www.sandersonforensics.com
SQLite Forensics Book 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[Ling, Andy]

> another point that I did not make clear. The accounting programs are not 
> associated with the technical programs, different people, different security 
> access. The tech databases and programs are in portable computers that go out 
> in the field, but not the accounting, etc. There indexes would have to be 
> updated when the computers are back at the office.

I have a feeling that what the OP is calling an index isn’t really. I think 
what he is talking about is the list of customer IDs.
So customer data can get updated “back at the office” and when the portable 
computers get back they need to be updated
with the changes.

In sqlite terms, an index is a sorted list of the data in one or more columns 
of a table to help speed up access to the data in that table.
Once defined, the index is automatically updated by sqlite as the data in the 
table is changed.

Perhaps we could get some clarification from the OP about what is actually 
wanted.

Regards

Andy Ling





**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank You.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Common index for multiple databases

[Hick Gunter]

This is what I think you are asking:

- You have a "main office" computer that holds the current information on 
"everything"
- You have several different categories of users (technicians, accountant, ...) 
that require different subsets of the data
- Each user has his own computer, that may be disconnected from the "main 
office", e.g. for "field work"
- When a user's computer is "attached" to the "main office", it needs to be 
"synchronized".

If this is correct, then you require either a "distributed" DBMS that handles 
synchronization by itself, or you need to do some programming both inside and 
outside  of SQLite.

This may be appropriate for you:

- As already stated, SQLite has just 1 file to hold all tables and indexes of 
the schema. Make this identical for all users. You can always leave the tables 
empty with just minimal overhead.
- Downloading from "office" to "user" is accomplished by using ATTACH to make 
the "user" and "office" databases accessible. Just run the appropriate INSERT 
... INTO statements. Check the authorizer callback to allow different users to 
access only the tables/fields that they are allowed to see. Limiting the rows 
requires an appropriate WHERE clause.
- "Work" done by the user while offline needs to be saved in a worklog table.
- Uploading the "work" of a user would copy the new worklog records into the 
"office" worklog table, just another INSERT ... INTO, to be processed by a 
dedicated sync application.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von John R. Sowden
Gesendet: Donnerstag, 02. August 2018 19:12
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Common index for multiple databases

I have been reviewing sqlite for a couple of years, but still use foxpro.  I 
have a question regarding an index issue.

Currently I have several types of databases (in foxpro, one per file) that all 
point to an index of a common field, a customer account number.  The databases 
are for accounting, technical, general info lookup, etc.  \

I do not want these databases to all reside in one sqlite file.  How do I index 
each database on this customer account number when each database and associated 
index are in separate files?  Is this what seems to be referred to as an 
external file?  I assume that I would have to reindex each database each time 
it is opened, since a record could have been edited, etc.

tia,

John



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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[Ling, Andy]

> another point that I did not make clear. The accounting programs are not 
> associated with the technical programs, different people, different security 
> access. The tech databases and programs are in portable computers that go out 
> in the field, but not the accounting, etc. There indexes would have to be 
> updated when the computers are back at the office.

I have a feeling that what the OP is calling an index isn’t really. I think 
what he is talking about is the list of customer IDs.
So customer data can get updated “back at the office” and when the portable 
computers get back they need to be updated
with the changes.

In sqlite terms, an index is a sorted list of the data in one or more columns 
of a table to help speed up access to the data in that table.
Once defined, the index is automatically updated by sqlite as the data in the 
table is changed.

Perhaps we could get some clarification from the OP about what is actually 
wanted.

Regards

Andy Ling


**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank You.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using CTE with date comparison

[Csányi Pál]

2018-08-02 23:12 GMT+02:00 R Smith :
> On 2018/08/02 10:29 PM, Csányi Pál wrote:
>>
>> Hi,
>>
>> I just want to know why the following SQLite query does not work as I
>> expected?
>>
>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>> date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
>> 2017-10-03|3
>> which is not what I am expecting.
>>
>> I am expecting the followings:
>> 1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
>> 2. then it compares the two dates: 2016-10-03 with 2016-10-03
>> 3. because 2016-10-03 = 2016-10-03 it count 1
>> 4. then add to the result date 2016-10-03 once again 1 year which is
>> 2017-10-03
>> 5. then it compares the two dates: 2017-10-03 with 2016-10-03
>> 6. because 2017-10-03 > 2016-10-03 it does not count 2
>> 7. it should gives the following result:
>> 2016-10-03|1
>>
>> What am I doing wrong here?
>>
>
> When the recursive Query starts up, the first value that it outputs is given
> by the very first part of the query, namely: VALUES('2015-10-03')
> So on the first iteration, it will produce one row like this:
> '2015-10-03'
> regardless of the rest of the Query. This row is pushed into the recursion
> buffer.
>
> After that it then reads a row from the recursion buffer and checks (within
> the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
> '2016-11-01', and finds that it definitely IS less, so continues to produce
> the another line of output.
>
> The output created is that date from the buffer (2015-10-03) which is put
> through the given calculation: date(dateD, '+1 year') to give:
> '2016-10-03'
>
> It then continues to push that next row into the recursion buffer and next
> reads again from it and again checks if it (2016-10-03) is <= than
> 2016-11-01, which again it is... so it continues to produce the next output
> row, which after calculation becomes:
> '2017-10-03'
>
> It then continues to push that again into the buffer and again read it and
> again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME, it
> isn't... so it stops right there.
>
> So in the end, it has produced 3 output rows namely:
> '2015-10-03'
> '2016-10-03'
> '2017-10-03'
>
> Which is exactly what you've asked for.
>
> Note: The first part of the query will ALWAYS reach the output buffer, even
> if it isn't a recursive query, and the UNION is NOT specified, you will get
> at least the '2015-10-03' value.
> Note: When comparing in the WHERE clause, you do not compare the newly
> calculated value (date(xxx, +1 year)), but indeed you compare the
> before-calculated value, i.e. the previous value in the buffer (the same as
> how your calculation is done on the PREVIOUS value in the buffer to yield
> the new date with.
>
> I hope that helps to make sense.
>
> Cheers,
> Ryan

Ryan, thank you very much for the explanation!

Then, how can I implement steps from 1 to 7 in SQLite language?

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