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

[Hick Gunter]

Please try to avoid using keywords as names, especially if they conflict with 
the intended datatype. "text blob not null" creates a field of name "text" 
whose content is a blob and yet you intend to store text data (with embedded 
newlines) in it.

If you store the lines separately, you can always group_concat() them together 
on retrieval.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Abramo Bagnara
Gesendet: Freitag, 03. August 2018 21:04
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Save text file content in db: lines or whole file?


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


___
 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

[sqlite] Default Values Pragma bug

[R Smith]

This is a small niggle, perhaps not worthy of the term "bug".

The Default values when specified in a CREATE TABLE statement will 
include C-style commented text in the output of the pragma table_info() 
(or its t.v.f. derivative) while the actual default value handling will 
parse it out.


Minimal demonstration:

  -- SQLite version 3.24.0  [ Release: 2018-06-04 ]  on SQLitespeed 
version 2.1.1.16.
  -- 



CREATE TABLE t(
  a INTEGER PRIMARY KEY /* This is the PK */,
  b INT DEFAULT 10  /* The Int val   */,
  c TEXT DEFAULT 'Ten'  /* The Text val */
);

INSERT INTO t DEFAULT VALUES;

SELECT * FROM t;

  --   a  |   b  |  c
  --  |  | ---
  --   1  |  10  | Ten   <-- Correct


PRAGMA table_info(t);

  -- cid | name | type    | notnull | dflt_value  |  pk
  -- --- |  | --- | --- | --- | ---
  --  0  |   a  | INTEGER |    0 | |  1
  --  1  |   b  | INT |    0    | 10  /* The Int val   */ |  0 <--
  --  2  |   c  | TEXT    |    0    | 'Ten'  /* The Text val */   |  
0   <-- Not correct



SELECT name, dflt_value FROM pragma_table_info('t');

  -- name | dflt_value
  --  | ---
  --   a  |
  --   b  | 10  /* The Int val   */  <--
  --   c  | 'Ten'  /* The Text val */    <-- Not correct

DROP TABLE t;

  -- 



Cheers,
Ryan

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

[sqlite] shell edit() trips on Windows newline kink

[Larry Brasfield]

I was experimenting today with a v3.25.0 variant and encountered this bug,
on my Windows 10 system, when I used the SQLite CLI shell's edit()
function.  Because I had put a newline in the text with the invoked editor,
and it was written and read back as a text file, the following code got
unhappy:
  fseek(f, 0, SEEK_END);
  sz = ftell(f);
  ...
x = fread(p, 1, sz, f);
  ...
  if( x!=sz ){
sqlite3_result_error(context, "could not read back the whole file", -1);
goto edit_func_end;
  }
The problem is that, on the Windows platform, newlines are stored on disk
as CR LF character pairs but, for text mode file I/O, are translated to a
single LF character in the C buffered file I/O library.  In the above
check, this causes x to be less than sz by the number of newlines so
translated.  There is an additional related problem whereby the 0
terminator on the read-in string (in text mode) is put in the wrong place.

Here is fossil diff output showing (what I believe to be) an effective fix,
which I have tested:
===
   if( bBin ){
 x = fread(p, 1, sz, f);
   }else{
 x = fread(p, 1, sz, f);
-p[sz] = 0;
+p[x] = 0;
   }
-  fclose(f);
-  f = 0;
-  if( x!=sz ){
+  if( ftell(f)!=sz ){
 sqlite3_result_error(context, "could not read back the whole file",
-1);
 goto edit_func_end;
   }
===
(I omit line number marking because my unrelated changes make them
inapplicable to source in the SQLite sources.)

Cheers,
--

Larry Brasfield
___
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

[Keith Medcalf]

>Some where in the WITH clause above I want to put '+1 day' in the
>command out there.

That is because the query does not count the StartDate but does count the 
EndDate, so if your EndDate is the next day from the StartDate you get 1 day, 
not two.  You need to move the fencepost created by the StartDate to counteract 
this (so that, effectively, the starting point becomes the day before the first 
day, or "day 0") ... This makes the dateY / dateM reflect the date on which, at 
the completion of that day, the year or month respectively was completed:

WITH RECURSIVE
 dates (StartDate, EndDate) as
   (
select '2004-02-02', '2004-02-02'
   ),
 yearC (dateY) AS
   (
SELECT date(StartDate, '+1 year', '-1 day')
  FROM dates
 WHERE date(StartDate, '+1 year', '-1 day') <= EndDate
 UNION ALL
SELECT date(dateY, '+1 year')
  FROM yearC, dates
 WHERE date(dateY, '+1 year') <= EndDate
   ),
 years (dateY, years) as
   (
SELECT coalesce((SELECT max(dateY)
   FROM yearC), date(StartDate, '-1 day')),
   coalesce((SELECT count(*)
   FROM yearC), 0)
  FROM dates
   ),
 monthC (dateM) as
   (
SELECT date(dateY, '+1 month')
  FROM years, dates
 WHERE date(dateY, '+1 month') <= EndDate
 UNION ALL
SELECT date(dateM, '+1 month')
  FROM monthC, dates
 WHERE date(dateM, '+1 month') <= EndDate
   ),
 months (dateM, months) as
   (
SELECT coalesce((SELECT max(dateM)
   FROM monthC), dateY),
   coalesce((SELECT count(*)
   FROM monthC), 0)
  FROM years
   ),
 dayC (dateD) as
   (
SELECT date(dateM, '+1 day')
  FROM months, dates
 WHERE date(dateM, '+1 day') <= EndDate
 UNION ALL
SELECT date(dateD, '+1 day')
  FROM dayC, dates
 WHERE date(dateD, '+1 day') <= EndDate
   ),
 days (dateD, days) as
   (
SELECT coalesce((SELECT max(dateD)
   FROM dayC), DateM),
   coalesce((SELECT count(*)
   FROM dayC), 0)
  FROM months
   )
SELECT StartDate,
   DateY,
   DateM,
   DateD,
   EndDate,
   years,
   months,
   days
  FROM dates, years, months, days;

StartDate   dateY   dateM   dateD   EndDate years   months  
days
--  --  --  --  --  --  
--  --
2004-02-02  2018-02-01  2018-08-01  2018-08-03  2018-08-03  14  6   
2

---
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 pali
>Sent: Sunday, 5 August, 2018 07:35
>To: SQLite mailing list
>Subject: Re: [sqlite] Using CTE with date comparison
>
>On Sun, Aug 05, 2018 at 05:25:02AM -0600, Keith Medcalf wrote:
>>
>> :StartDate and :EndDate are NAMED PARAMETERS for when your
>application executes the statement (that is, they are substituted
>with the values you want for the StartDate and EndDate respectively.
>
>I understand now. Thank you!
>
>> I take it you want to compute YEARS MONTHS and DAYS between two
>dates:
>
>Exactly that is what I want.
>
>> WITH RECURSIVE
>>  dates (StartDate, EndDate) as
>>(
>> select '2004-02-02', '2018-08-03'
>>),
>>  yearC (dateY) AS
>>(
>> SELECT date(StartDate, '+1 year')
>>   FROM dates
>>  WHERE date(StartDate, '+1 year') <= EndDate
>>  UNION ALL
>> SELECT date(dateY, '+1 year')
>>   FROM yearC, dates
>>  WHERE date(dateY, '+1 year') <= EndDate
>>),
>>  years (dateY, years) as
>>(
>> SELECT coalesce((SELECT max(dateY)
>>FROM yearC), StartDate),
>>coalesce((SELECT count(*)
>>FROM yearC), 0)
>>   FROM dates
>>),
>>  monthC (dateM) as
>>(
>> SELECT date(dateY, '+1 month')
>>   FROM years, dates
>>  WHERE date(dateY, '+1 month') <= EndDate
>>  UNION ALL
>> SELECT date(dateM, '+1 month')
>>   FROM monthC, dates
>>  WHERE date(dateM, '+1 month') <= EndDate
>>),
>>  months (dateM, months) as
>>(
>> SELECT coalesce((SELECT max(dateM)
>>FROM monthC), dateY),
>>coalesce((SELECT count(*)
>>FROM monthC), 0)
>>   FROM years
>>),
>>  dayC (dateD) as
>>(
>> SELECT date(dateM, '+1 day')
>>   FROM months, dates
>>  WHERE date(dateM, '+1 day') <= EndDate
>>  UNION ALL
>> SELECT date(dateD, '+1 day')
>>   FROM dayC, dates
>>  WHERE date(dateD, '+1 day') <= EndDate
>>),
>>  days (dateD, days) as
>>(
>> SELECT coalesce((SELECT max(dateD)
>>FROM dayC), DateM),
>>coalesce((SELECT count(*)
>>FROM dayC), 0)
>>   FROM months
>>)
>> SELECT StartDate,
>>DateY,
>>DateM,
>>

Re: [sqlite] Using CTE with date comparison

[pali]

On Sun, Aug 05, 2018 at 05:25:02AM -0600, Keith Medcalf wrote:
> 
> :StartDate and :EndDate are NAMED PARAMETERS for when your application 
> executes the statement (that is, they are substituted with the values you 
> want for the StartDate and EndDate respectively.

I understand now. Thank you!

> I take it you want to compute YEARS MONTHS and DAYS between two dates:

Exactly that is what I want.

> WITH RECURSIVE
>  dates (StartDate, EndDate) as
>(
> select '2004-02-02', '2018-08-03'
>),
>  yearC (dateY) AS
>(
> SELECT date(StartDate, '+1 year')
>   FROM dates
>  WHERE date(StartDate, '+1 year') <= EndDate
>  UNION ALL
> SELECT date(dateY, '+1 year')
>   FROM yearC, dates
>  WHERE date(dateY, '+1 year') <= EndDate
>),
>  years (dateY, years) as
>(
> SELECT coalesce((SELECT max(dateY)
>FROM yearC), StartDate),
>coalesce((SELECT count(*)
>FROM yearC), 0)
>   FROM dates
>),
>  monthC (dateM) as
>(
> SELECT date(dateY, '+1 month')
>   FROM years, dates
>  WHERE date(dateY, '+1 month') <= EndDate
>  UNION ALL
> SELECT date(dateM, '+1 month')
>   FROM monthC, dates
>  WHERE date(dateM, '+1 month') <= EndDate
>),
>  months (dateM, months) as
>(
> SELECT coalesce((SELECT max(dateM)
>FROM monthC), dateY),
>coalesce((SELECT count(*)
>FROM monthC), 0)
>   FROM years
>),
>  dayC (dateD) as
>(
> SELECT date(dateM, '+1 day')
>   FROM months, dates
>  WHERE date(dateM, '+1 day') <= EndDate
>  UNION ALL
> SELECT date(dateD, '+1 day')
>   FROM dayC, dates
>  WHERE date(dateD, '+1 day') <= EndDate
>),
>  days (dateD, days) as
>(
> SELECT coalesce((SELECT max(dateD)
>FROM dayC), DateM),
>coalesce((SELECT count(*)
>FROM dayC), 0)
>   FROM months
>)
> SELECT StartDate,
>DateY,
>DateM,
>DateD,
>EndDate,
>years,
>months,
>days
>   FROM dates, years, months, days;
> 
> StartDate   dateY   dateM   dateD   EndDate years   
> months  days
> --  --  --  --  --  --  
> --  --
> 2004-02-02  2018-02-02  2018-08-02  2018-08-03  2018-08-03  14  6 
>   1

Thank you very much!
That's what I wanted, albeit with a small addition: I should get one day more.

This is due the fact that when calculated a month, days which belongs
to a month are as follows, e.g. for a month which is 31 days long: 1.,
2., 3., ..., 30., 31.

Some where in the WITH clause above I want to put '+1 day' in the
command out there.

I am going to study this command which I want to use in a Trigger.
Thank you very much again for the solution!

--
Best, Pali
___
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

[Jim Callahan]

Back off from the index semantics for a second.

If Gunter Hick has captured at the application level of what you are trying
to do (remote databases),

I think the name of the concept we are looking for is: "Eventual
Consistency".
 https://en.wikipedia.org/wiki/Eventual_consistency

SQL databases (as opposed to Xbase) are built around the concept of ACID
transactions which
implies a centralized database where everything can be reconciled
immediately. The delayed processing
of "Eventual Consistency" is implemented at the application level; for
example there are applications written
for PostgreSQL to make complete replica databases "eventually consistent".
That would be overkill in
your case, but the concept that "eventual consistency" has to be
implemented in the application space
above the core SQL level.

So, at the application level, what you want to do is create subset tables
for a particular purpose.
Good news! SQL is fantastic at creating of subsets of rows and columns of
data.

In a new SQLite database ATTACH the main database and create a query that
describes the subset
of data you need and then wrap that query in a "CREATE TABLE AS query;"
statement. That with create a subset of the data (without indexes). Rebuild
the indexes you
need in the local table (do not attempt to copy indexes!).

The application logic needs to use the subset database to build a
time-stamped transaction
to run against the main database.

Your application needs a module that accepts all the remote time stamped
transactions
and queue them up to feed into the main database. You have to decide how
your
application should handle conflicting transactions (see the "eventual
consistency"
article).
https://en.wikipedia.org/wiki/Eventual_consistency

Then run your consistent application level transaction log against the main
database.

Don't worry about "copying" indexes.  As Dr. Hipp suggests, copying indexes
is a non-starter in the SQL world.
Just copy the data and rebuild your indexes on the subset data.  If you
want to assure you don't
create a duplicate customer number; copy the column of customer numbers to
a separate table;
reindex it and join it to your subset table.

So, in short, you can't copy indexes, but you can copy any subset of data
and re-index that subset.
"Eventual consistency" has to be handled at the application level above the
SQL core (which only
handles "ACID consistency").

HTH

Jim Callahan
Callahan Data Science LLC
Orlando, FL




On Fri, Aug 3, 2018 at 5:41 AM, Hick Gunter  wrote:

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

Re: [sqlite] Using CTE with date comparison

[Keith Medcalf]

:StartDate and :EndDate are NAMED PARAMETERS for when your application executes 
the statement (that is, they are substituted with the values you want for the 
StartDate and EndDate respectively.

I take it you want to compute YEARS MONTHS and DAYS between two dates:

WITH RECURSIVE
 dates (StartDate, EndDate) as
   (
select '2004-02-02', '2018-08-03'
   ),
 yearC (dateY) AS
   (
SELECT date(StartDate, '+1 year')
  FROM dates
 WHERE date(StartDate, '+1 year') <= EndDate
 UNION ALL
SELECT date(dateY, '+1 year')
  FROM yearC, dates
 WHERE date(dateY, '+1 year') <= EndDate
   ),
 years (dateY, years) as
   (
SELECT coalesce((SELECT max(dateY)
   FROM yearC), StartDate),
   coalesce((SELECT count(*)
   FROM yearC), 0)
  FROM dates
   ),
 monthC (dateM) as
   (
SELECT date(dateY, '+1 month')
  FROM years, dates
 WHERE date(dateY, '+1 month') <= EndDate
 UNION ALL
SELECT date(dateM, '+1 month')
  FROM monthC, dates
 WHERE date(dateM, '+1 month') <= EndDate
   ),
 months (dateM, months) as
   (
SELECT coalesce((SELECT max(dateM)
   FROM monthC), dateY),
   coalesce((SELECT count(*)
   FROM monthC), 0)
  FROM years
   ),
 dayC (dateD) as
   (
SELECT date(dateM, '+1 day')
  FROM months, dates
 WHERE date(dateM, '+1 day') <= EndDate
 UNION ALL
SELECT date(dateD, '+1 day')
  FROM dayC, dates
 WHERE date(dateD, '+1 day') <= EndDate
   ),
 days (dateD, days) as
   (
SELECT coalesce((SELECT max(dateD)
   FROM dayC), DateM),
   coalesce((SELECT count(*)
   FROM dayC), 0)
  FROM months
   )
SELECT StartDate,
   DateY,
   DateM,
   DateD,
   EndDate,
   years,
   months,
   days
  FROM dates, years, months, days;

StartDate   dateY   dateM   dateD   EndDate years   months  
days
--  --  --  --  --  --  
--  --
2004-02-02  2018-02-02  2018-08-02  2018-08-03  2018-08-03  14  6   
1

---
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 Csányi Pál
>Sent: Sunday, 5 August, 2018 02:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Using CTE with date comparison
>
>2018-08-05 0:18 GMT+02:00 Keith Medcalf :
>>
>> WITH RECURSIVE
>>  dates(dateD) AS (VALUES(:StartDate)
>> UNION ALL
>>   SELECT date(dateD, '+1 year')
>> FROM dates
>>WHERE date(dateD, '+1 year') <= :EndDate
>>  )
>> SELECT max(dateD), count(*) FROM dates;
>
>How do I interpret the ':StartDate' and ':EndDate'?
>
>Should I replace for example the ':StartDate' with '1983-07-11' like
>this:
>
>sqlite> WITH RECURSIVE dates(dateD) AS (VALUES('1983-07-11') UNION
>ALL
>SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD, '+1 year')
><= '1984-08-31' ) SELECT max(dateD), count(*) FROM dates;
>1984-07-11|2
>
>But this is ot what I want, because between 1983-07-11 and 1984-08-31
>there is exactly one whole year out there, namely: from 1983-07-11 to
>1984-07-11. So the result value '2' above is wrong in the sense that
>there is 1 year and not 2 years out there.
>
>The following SQL commands gives to me the right output, what I
>desired:
>sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1983-07-11','+1
>year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
>date(dateD, '+1 year') <= '1984-08-31' ) SELECT max(dateD), count(*)
>FROM dates;
>1984-07-11|1
>sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1984-11-01','+1
>year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
>date(dateD, '+1 year') <= '1986-01-15' ) SELECT max(dateD), count(*)
>FROM dates;
>1985-11-01|1
>sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1986-01-16','+1
>year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
>date(dateD, '+1 year') <= '1999-07-16' ) SELECT max(dateD), count(*)
>FROM dates;
>1999-01-16|13
>
>but not in the following case:
>sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('2000-02-01','+1
>year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
>date(dateD, '+1 year') <= '2000-08-31' ) SELECT max(dateD), count(*)
>FROM dates;
>2001-02-01|1
>
>because here in the last example there should be '0' and not '1' in
>the output of that command.
>
>I am still trying to find the proper way to do this, what I desired,
>if it is possible at all.
>
>--
>Best, Pali
>___
>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-05 0:18 GMT+02:00 Keith Medcalf :
>
> WITH RECURSIVE
>  dates(dateD) AS (VALUES(:StartDate)
> UNION ALL
>   SELECT date(dateD, '+1 year')
> FROM dates
>WHERE date(dateD, '+1 year') <= :EndDate
>  )
> SELECT max(dateD), count(*) FROM dates;

How do I interpret the ':StartDate' and ':EndDate'?

Should I replace for example the ':StartDate' with '1983-07-11' like this:

sqlite> WITH RECURSIVE dates(dateD) AS (VALUES('1983-07-11') UNION ALL
SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD, '+1 year')
<= '1984-08-31' ) SELECT max(dateD), count(*) FROM dates;
1984-07-11|2

But this is ot what I want, because between 1983-07-11 and 1984-08-31
there is exactly one whole year out there, namely: from 1983-07-11 to
1984-07-11. So the result value '2' above is wrong in the sense that
there is 1 year and not 2 years out there.

The following SQL commands gives to me the right output, what I desired:
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1983-07-11','+1
year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD, '+1 year') <= '1984-08-31' ) SELECT max(dateD), count(*)
FROM dates;
1984-07-11|1
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1984-11-01','+1
year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD, '+1 year') <= '1986-01-15' ) SELECT max(dateD), count(*)
FROM dates;
1985-11-01|1
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1986-01-16','+1
year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD, '+1 year') <= '1999-07-16' ) SELECT max(dateD), count(*)
FROM dates;
1999-01-16|13

but not in the following case:
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('2000-02-01','+1
year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD, '+1 year') <= '2000-08-31' ) SELECT max(dateD), count(*)
FROM dates;
2001-02-01|1

because here in the last example there should be '0' and not '1' in
the output of that command.

I am still trying to find the proper way to do this, what I desired,
if it is possible at all.

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

Re: [sqlite] "Cheating" at making common tables

[Stephen Chrzanowski]

Simon;

Interesting approach that I'd forgotten about.

The tables aren't "variable".  This is a "beginning of the project,
one-time execution" thing I was hoping to get at database initialization.
Meaning, 0-byte SQLite file size kind of initialization, with not a single
line of application code has been written.  I do want the tables to exist
and be static in the database file, and my application will reference those
exact table names.

I'm just looking for a shortcut that can trim down the time to do the
initial creation when I need to make 10 or so tables that have the exact
same structure, but different meaning for the content.  So basically
something I can whip out of a text file I have laying around somewhere, or
on a wiki I keep locally, paste it into my SQL editor of choice, change the
values in one place or on one line, press execute, poof, my tables are
created.  This would be executed after I've done the
pen-and-paper-proof-of-concept-schema design.

Obviously misunderstood by all, this whole post is more about using
different methodologies to get to the desired end result.  As Keith
mentions, a script can do this in a heartbeat, which is the true, but,
going CTE (Or other) routes may spark a new direction for me, or help
clarify something I may not quite understand right about CTEs.

That said, with your post below, you've reminded me that I'm actually using
this kind of methodology for an "Options" or "Preferences" database wrapper
for some of my applications that I share between machines.  This "Options"
database is a dedicated database file containing a single table with three
fields that have the machine name, the options keyword and the options
value as fields.  When the app looks for an 'option', the app does its look
up based on the machines name.  If the machines name doesn't exist, it'll
look for the same keyword substituting the hosts name as "DEFAULT".  If
that still doesn't find anything, then it goes and relies on the hard coded
default value.  It'll suck a tiny little bit when I run across a computer
with the name "DEFAULT" that actually uses this mechanism, but, the risk is
low, and even if it happens, the app still runs, pending hard coded
defaults blows something up I don't expect.

On Sat, Aug 4, 2018 at 10:52 PM, Simon Slavin  wrote:

>
> The name of a table should not be variable.  SQL has tables with fixed
> names and variable contents, and the entire support stack is designed to
> assist this.  So move the names of your tables, which are variable, into a
> table.
>
> In the case of the above schema, the fix would be this:
>
> TABLE InfoStore (
> infoType TEXT,
> ID INTEGER,
> name TEXT);
>
> CREATE UNIQUE INDEX IS_InfoType_ID ON InfoStore (infoType, ID);
>
> Assign an IDs for a new 'SolarSystem' row by calculating
>
> * 1 + max(SELECT ID FROM InfoStore WHERE infoType='SolarSystem')*


I'd rather a random 32-bit GUID and have the application die horribly due
to an almost impossible conflict, rather than offer a race condition like
this and successfully write wrong data.

The ID can be anything as it'll never be visible to the user.  It'll only
be visible to the code that needs to know how to update the data in the
database.  As I've spent a couple hours on this email alone (The rewrites..
ohhh the rewrites), I'm starting to fade with this thinking thing, but I'll
come up with something that works and isn't a possible race condition
probably on Monday (Out of town, out of internet service range, and I'm on
a week long vacation)


> Everything goes into one big table which has a fixed name.  Magically you
> no longer need weird things like CTEs, and all access can be done by
> binding column values.
>
> Simon.
>





On Sat, Aug 4, 2018 at 10:41 PM, Keith Medcalf  wrote:

> 1) Why are you using AUTOINCREMENT?

2) The datatype CHAR does not exist.  The correct name is TEXT
> 3) Why are you creating a separate UNIQUE constraint rather than just
> specifying the column as UNIQUE?
> 4) Are you sure the text string is case sensitive for comparisons rather
> than merely case-preserving (that is, did you forget COLLATE NOCASE)?
> 5) You should not be creating duplicate UNIQUE indexes.
>
>
1) 100% guaranteed uniqueness, its an identifier my UI uses in list boxes,
combo boxes, text fields, and anything else that represents a reference to
a row within the database.  I'm limited to 32-bit Windows applications due
to the choice of **not* *spending $3k on a language and a 64-bit IDE I'm
comfortable with.  (But I will one of these days when any one of my
applications make more than $1,000/year, I'll have to.  .. so far, I'm up
to a cup of coffee worth... from a coworker who appreciated the timer I
wrote for them)  The UI components can take any signed (32-bit)-1 integer
(-1 represents NULL or unassigned as an object type and seemingly makes my
programs perform bad life choices when I reference that type of object as a
number).  My application will not ever control or change