Re: [sqlite] Check constrain execution timing change? (Now a bug)

[Richard Hipp]

On 2/1/20, Thomas Kurz  wrote:
> Does this mean there will be no possibility to prevent inserting a string
> into an integer column anymore?
>
> create table x (x integer check (typeof(x) == 'integer'));
> insert into x values ('1');
>
> --> will pass in future versions???

I think that is what it means.  yes.

-- 
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] importing data to a table that has generated-columns

[James K. Lowden]

On Mon, 27 Jan 2020 12:00:52 -0700
"Keith Medcalf"  wrote:

> If you mean importing into a pre-existing table as above that is
> declared with "check typeof(a) in ('integer', 'real')" then yes.  The
> check constraint is run before affinity is applied rather than after
> (unfortunately) ...

Thank you, Keith.  Yes, that's what I was referring to.  

> >I imagine it's already being considered: if pragma table_info
> >included a column with the SQL for generated columns (and NULL)
> >otherwise, the shell could use that instead of SELECT * to determine
> >the number of insertable columns.
> 
> That data is already in the schema structures as is the assigned
> affinity:

Good to know.  I realize of course that computed columns are a new
feature.  Knowing that their "computed" property is easily ascertained,
I'm optimistic that the shell's .import will one day avail itself of
that information.  

What's old is always new again.  ISTR when Microsoft SQL Server added
computed columns, they also got in the way of bulk-loading at first.
The rule for a while was that the buik-copy utility (bcp) couldn't be
used with such tables.  

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

Re: [sqlite] New word to replace "serverless"

[James K. Lowden]

On Mon, 27 Jan 2020 17:18:45 -0500
Richard Hipp  wrote:

> But more recently, "serverless" has become a popular buzz-word that
> means "managed by my hosting provider rather than by me."  Many
> readers have internalized this new marketing-driven meaning for
> "serverless" and are hence confused when they see my claim that
> "SQLite is serverless".

It would be a mistake to discard the useful term "serverless" in favor
of some invented term.  Any invented term would have to be defined, in
which case you're back to where you started from.  

As you know, no terminology has context-free meaning.  (My favorite
example is time: "six o'clock" can mean several different things,
depending on context.)  The fact that some users impose inappropriate
context on "serverless" doesn't make that term less useful or
descriptive or meaningful.  

It seems to me the best course of action, to dispell the confusion, is
simply to define the term in some prominent location.  You already do
that at https://sqlite.org/serverless.html.  Maybe that page just
needs  3rd bullet point?  

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

Re: [sqlite] "Standard SQL" ?

[James K. Lowden]

On Thu, 30 Jan 2020 19:19:53 +
Simon Slavin  wrote:

> Have any of you been using this term for a meaning other than "Fully
> conforming to SQL:2019 (or whatever version you think current) ?  Do
> you have documentation somewhere ?  Or are my suspicions correct and
> there's no such thing ?

I often refer to "the SQL standard" as if there were only one.  For
many purposes, there might as well be just one.  

For example, some SQL DBMSs support "UPDATE FROM" to update one table
from another.  If we can agree that's nonstandard SQL, then surely it's
also "not standard SQL" and "is not defined by the SQL standard".  

Similarly, last I checked, no SQL standard supported LIMIT for SELECT.  

On the positive side, some parts of SQL haven't changed since the Late
Bronze Age.  "SELECT *" still means all columns; "FROM" still takes a
table argument, whether a tablename, view, or expression.  "WHERE"
operates on values "before" aggregation; "HAVING" on values "after"
aggregation.  Any implementation that operates any other way does not
implement standard SQL.  

SQL is hardly unique in this regard.  We also refer to "the" C standard
library, to "Posix", and to "the" C or C++ standard.  Like SQL, there
are many such and (also like SQL) some parts are unchanged since the
beginning while, just as usefully, some that weren't part of the
first standard haven't changed since they were introduced.  

--jkl


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

Re: [sqlite] Check constrain execution timing change? (Now a bug)

[Thomas Kurz]

Does this mean there will be no possibility to prevent inserting a string into 
an integer column anymore?

create table x (x integer check (typeof(x) == 'integer'));
insert into x values ('1');

--> will pass in future versions???


- Original Message - 
From: Richard Hipp 
To: SQLite mailing list 
Sent: Saturday, February 1, 2020, 00:09:07
Subject: [sqlite] Check constrain execution timing change? (Now a bug)

On 1/31/20, Keith Medcalf  wrote:

> That would elevate this to the status of a bug since it should be impossible
> to do this.


It is also not something that is fixable, so the solution will likely
be to simply document it.
-- 
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] single table data collapse with constraints

[Jean-Luc Hainaut]

Hi Robert,

I'm a bit late: I missed your post in the recent tsunami (of posts)!

Your question relates to one of the basic primitives of temporal 
databases, "coalescing". Basic but far from simple if you want to 
express it in pure SQL. On the contrary, the answer is much simpler if 
you code it as a loop in any host language.


You could consult this tutorial about the concepts of temporal databases:
https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Case09-Temporal-DB(1).pdf 



... and this one about temporal data manipulation, including coalescing:
https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Case10-Temporal-DB(2).pdf 



Regards

Jean-Luc Hainaut


On 29/01/2020 16:02, Golding Robert wrote:

Hi,

I am emailing to ask if someone could advise me how to create a query or 
queries which will collapse some data based on a limited number of constraints.
I am currently attempting to complete this task using DB Browser for SQLite. I  
have tried to write a WITH RECLUSIVE statement as I think this is the 
requirement but am struggling with both the abstraction and syntax.


I have data contained with a single table of structure:
CLS1 field (text)
CLS2 field (integer)
START field (integer)
END field (integer

I need to collapse the data based on the matching of values in fields CLS1 and 
CLS2; the final constraint is that if END and START values are continuous of 
the another record then they can be collapsed. Therefore records should only be 
collapsed if gaps do not exist. The results then need to written to a new 
table, leaving the original data as is.

Input data: assumptions

   1.  Data may or may not be ordered
   2.  Duplicates may or may not exist
   3.  Start and end values could be the same
   4.  Start values are normally lower that the end value, however the high 
value could be in the start field
   5.  Assume that there is no overlap in terms of start and end values (namely 
if they can be joined then one will stop where the next starts)

Input data:  example
CLS1,CLS2,START,END
ABC1,100,0,1
ABC1,100,1,1 (start and end values could be the same, in the first instance 
assume that they may be dissolved if possible, if they cannot the record need 
to be retained)
ABC1,100,1,3
ABC1,100,1,3 (duplicates may or may not be present, if present then they can be 
dissolved into a single instance)
ABC1,100,3,4
ABC1,100,4,3
ABC1,100,5,6
ABC1,100,6,20
ABC1,100,6,20(duplicates may or may not be present, if present then they can be 
dissolved into a single instance)
ABC1,500,4,19
ABC1,500,19,4 (start and end values could be inverted where Start is high and 
End id low (this is not the norm but it is legitimate) in this case start and 
end values may be inverted, and in this case dissolved into a single instance)
ABC2,300,4,4 (start and end values could be the same, in the first instance 
assume that they may be dissolved if possible, if they cannot the record need 
to be retained)



Output data: collapsed/merged expected output
CLS1,CLS2,START,END
ABC1,100,0,4
ABC1,100,5,20
ABC1,500,4,19
ABC2,300,4,4


I would be extremely grateful if anybody could help me with this issue.


Regards,

Rob





The content of this email (and any attachment) is confidential. It may also be 
legally privileged or otherwise protected from disclosure.
This email should not be used by anyone who is not an original intended 
recipient, nor may it be copied or disclosed to anyone who is not an original 
intended recipient.

If you have received this email by mistake please notify us by emailing the 
sender, and then delete the email and any copies from your system.

Liability cannot be accepted for statements made which are clearly the sender's 
own and not made on behalf of Network Rail.
Network Rail Infrastructure Limited registered in England and Wales No. 
2904587, registered office Network Rail, 2nd Floor, One Eversholt Street, 
London, NW1 2DN


___
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] Shadowing a table name with a common table expression

[William Chargin]

If we create a table and then declare a common table expression of the
same name, `SELECT`s seem to refer to the table, while `INSERT` targets
refer to the CTE. For example:

```
CREATE TABLE foo (x);
INSERT INTO foo SELECT 1;
WITH foo (x) AS (SELECT 10) INSERT INTO foo SELECT x + 1 FROM foo;
SELECT x FROM foo;
```

On my SQLite (tested version 3.11.0 and also v3.33.1 from Fossil head),
this prints 1 and 11. If the `foo` in `SELECT x + 1 FROM foo` had
referred to the underlying table, it would have printed 1 and 2 instead.

Clearly this is a bit confusing, and I should probably avoid doing it.
But is the behavior defined either by SQL or SQLite? I didn't see
anything relevant in .
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

[sub sk79]

Also these links which maybe easier to grok:
https://sqlite.org/tempfiles.html
 (sections  2.6 and 3)
https://sqlite.org/tempfiles.html#tempstore

-Neal

On Sat, Feb 1, 2020 at 10:09 AM J. King  wrote:

> On February 1, 2020 10:03:28 a.m. EST, Peng Yu 
> wrote:
> >But it doesn't explain what is "the temp database". Is it an actual
> >database file? Or it is just a in-memory database?
>
>
> It depends. See  for
> details ans links to further reading.
> --
> J. King
> ___
> 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] Is the table created by "create temp table" in the database file or in memory?

[J. King]

On February 1, 2020 10:03:28 a.m. EST, Peng Yu  wrote:
>But it doesn't explain what is "the temp database". Is it an actual
>database file? Or it is just a in-memory database?


It depends. See  for details 
ans links to further reading. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

[Peng Yu]

But it doesn't explain what is "the temp database". Is it an actual
database file? Or it is just a in-memory database?

On 2/1/20, Simon Slavin  wrote:
> On 1 Feb 2020, at 1:30pm, Peng Yu  wrote:
>
>> https://www.sqlite.org/lang_createtable.html
>>
>> "The database in which the new table is created. Tables may be created in
>> the main database, the temp database, or in any attached database."
>>
>> It is not clear to me in the doc. If a table is created by "create
>> temp table", is it temporarily a database file but later got deleted
>> upon termination of the sqlite3 session? Or it is in memory? Thanks.
>
> Further down the same page:
>
> " If the "TEMP" or "TEMPORARY" keyword occurs between the "CREATE" and
> "TABLE" then the new table is created in the temp database. "
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

[Simon Slavin]

On 1 Feb 2020, at 1:30pm, Peng Yu  wrote:

> https://www.sqlite.org/lang_createtable.html
> 
> "The database in which the new table is created. Tables may be created in the 
> main database, the temp database, or in any attached database."
> 
> It is not clear to me in the doc. If a table is created by "create
> temp table", is it temporarily a database file but later got deleted
> upon termination of the sqlite3 session? Or it is in memory? Thanks.

Further down the same page:

" If the "TEMP" or "TEMPORARY" keyword occurs between the "CREATE" and "TABLE" 
then the new table is created in the temp database. "
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is the table created by "create temp table" in the database file or in memory?

[Peng Yu]

Hi,

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

"The database in which the new table is created. Tables may be created
in the main database, the temp database, or in any attached database."

It is not clear to me in the doc. If a table is created by "create
temp table", is it temporarily a database file but later got deleted
upon termination of the sqlite3 session? Or it is in memory? Thanks.

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