[sqlite] Caveat entry

[Simon Slavin]

On 15 Apr 2016, at 10:05pm, Cecil Westerhof  wrote:

> I was ?bitten? by the fact that in SQLite primary keys can contain NULL
> values. As I understood it, I was certainly not the first person to who
> this happened. It would not surprise me if there are more deviations that
> could spell problems for users of SQLite. That is why I think it would be a
> good idea to add a caveat entry

It might help you to know that I can't remember the last time anyone raised 
problems associated with having NULLs in primary key columns.  Most users are 
aware that all SQL engines have a problem deciding whether two "NULL" values 
should be considered equal or not and this feeds directly into what happens 
when you try to make primary indexes with nulls.

I don't think adding anything to the 'about' page is going to help those having 
problems.  It wouldn't occur to me to look there if I found something 
unexpected with SQLite.  However you do raise an interesting possibility of 
creating a 'caveat' or 'gotcha' page, like the "How to corrupt" page.

The problems most commonly mentioned here seem to be (in no particular order)

* SQLite datatypes and how SQLite decides which datatype you want
* SQLite uses affinities not column types
* thinking you should index each column instead of indexes for queries
* consequences of various PRAGMAs which might appear to speed SQLite up
* consequences of the two different journal types (including file size)

Each of the above can be summarised and explained clearly with explanations 
which won't need to be changed over time.  If you want to add questions which 
are harder to answer and have answers which change over time then perhaps these 
should be considered:

* compilation errors/warnings in various tools/environments
* when you need the component source files not just the amalgamation
* problems using Windows DLL in some environments/versions
* problems not in SQLite but introduced by various SQLite shells/libraries when 
trying to bridge the gaps between SQLite and the way their language or data 
structure works (e.g. pretending that SQLite has a date/time type)

I should add that I ignore posts about the FTS capability of SQLite.  I don't 
know whether people post the same problems with it repeatedly or each post is 
about something different.  Perhaps someone will contribute an opinion.

The most common mistake which leads to posts to this list is -- still the 
winnah after five years ! -- premature optimization.  I can't believe how much 
coding time and software complication is wasted by people who spend hours on 
multi-thread, queueing and partitioning code instead of redesigning their 
schema or thinking through how indexes work.

Simon.

[sqlite] Primary key values can be NULL

[Cecil Westerhof]

2016-04-15 22:36 GMT+02:00 Cecil Westerhof :

> ?I use it for logging. It is much easier to find something, or delete the
> parts you do not need anymore.
>

An example:

https://www.linkedin.com/pulse/use-bash-store-disc-info-sqlite-cecil-westerhof

If I could do things better: let me know.

-- 
Cecil Westerhof

[sqlite] Primary key values can be NULL

[Cecil Westerhof]

2016-04-15 22:04 GMT+02:00 R Smith :

?I do not think it is. When you add something to the database to signify
>> that a primary key is not allowed to be NULL, then this is not in an old
>> database, ergo in the old database NULLs are allowed. Where does backward
>> compatibility get broken? As I see it, it is as with partial indexes.
>
>
>
> ?
>>
>>> B. Your suggestion would break backward compatibility, no matter how
>>> "light" you coat it.
>>>
>>> ?I really do not see this. Could you expand on that?
>>>
>>
> Imagine a program written 2 years ago, for instance works on all Apple
> computers, or perhaps Android phones. In fact, imagine several programs
> where the programmers used, either through conscious decision to employ a
> "feature" of SQLite, or perhaps simply out of ignorance, the NULL values
> allowed in PK situation.
> Some time later, SQLite gets updated with your requested new default.
> These programs get re-compiled with defaults, or even just use the packaged
> SQLite that are now updated inside OSX or Android, etc. Suddenly, their
> programs do no longer work, Keys fail to get inserted... Users have devices
> crashing everywhere. Apple perhaps quickly rolls back to a previous version
> that was not so damaged, but every compiled-in version of the SQLite code
> is out in the wild causing problems. SQLite runs on billions of devices and
> systems.
>

?It is clear as daylight now. Thank you for the explanation. I hope that I
was not to pesky.?




> This is what backwards-compatible means, that a system and data will still
> work as it always worked, even after you upgrade the engine. To get to your
> example of Partial indices - if a DB did not use them before, then it still
> doesn't use them, all is well. Only new DB's could use them.
>

?Yeah, I did not think it through enough. Luckily the maintainers think
better about consequences as I did.?




> So if you opt for a pragma that lets you avoid NULLs once you activate
> it... sure, but who will that really help? People will need to read the
> documentation to even know that pragma exists (which you pointed out they
> don't usually do in the first place), and simply /knowing/ the reason for
> that pragma, will obviate the need for it.
>

?It does not add much then no. The only thing is that people could keep
database definitions ?the same? for different databases.?


?The only change I would like is in the documentation. It should be
?impossible? to start using SQLite without knowing this pitfall. Not for
me, I know it now, but for future users.

?


> I am hoping that is as clear as possible with no hint of mocking - I
> honestly mean it well.


?Yes, I now understand it. Next time I should curb my enthusiasm. ;-)



C. The suggested work-around would introduce more complication than it is
>>> solving.
>>>
>>> ?I do not see that either. Could you enlighten me?
>>
>
> I trust this point was made above too.
>

?Certainly.?

?


> ?I like SQLite very much also. I even gave a presentation about it on
>> T-DOSE. As you can see from the plethora of questions I ask(ed) I want to
>> get serious with it. I do not use MySQL anymore and plan to migrate what I
>> still have in H2 to SQLite also. :-) I do not say there is never a reason
>> for another database, but I think that in my case there is not (at this
>> moment of time).
>>
>
> That's great news :)
> Let me just note that we do not really shun the likes of Postgress, MSSQL,
> MySQL etc. - those systems answer a different need.


?Me neither, but when SQLite is enough why add the complications of the
other type of database? At my work they use DB2: I do not think SQLite
would be a good replacement there. :-D

?
?I use it for logging. It is much easier to find something, or delete the
parts you do not need anymore.


Thanks for the patience.

-- 
Cecil Westerhof

[sqlite] System.Data.SQLite version 1.0.100.0 released

[Simon Slavin]

>> On Apr 15, 2016, at 12:46, Tim Uy  wrote:
>> 
>> that is a lot of 1s and 0s.

> On 15 Apr 2016, at 10:34pm, Brad Stiles  wrote:
> 
> It's *all* 1s and 0s. 

Hate them.

Simon.

[sqlite] Primary key values can be NULL

[R Smith]

On 2016/04/15 8:00 PM, Cecil Westerhof wrote:
> 2016-04-15 16:43 GMT+02:00 R Smith :
>
>
> ?Do you want me to tell you that you always read all the documentation of
> all the software before you use it? I surely do not. And I think most
> people do not. To be honest I think you do also not: it is nearly
> impossible.

I do want to tell you that, yes. it is not impossible. Maybe not when I 
am simply the user of some software, but very definitely when I am the 
programmer and my systems depend on a piece of software from a 3rd 
party, then I read every bit of documentation, more than once - and I am 
not special, I am sure that goes for most developers here, but perhaps 
not all. As for interpreting the documentation wrong, well, that I can 
do just as well as the next guy! - all the more reason to read it again.

> When reading what the documentation says, I interpret it as: we really
> would like to comply to the standard. But we cannot.

Yes, that is exactly what it says.

> I (think I) gave a way to give the maintainers what they want. If there is
> a reason that what I suggest will not work, I do not mind to hear it.

I'll try explain it more simply below.

>   
> just saying (in this case) you should have read the documentation, I find
> not helpful and to be honest a little cheap. Especially because the fix is
> mostly for new users. I know what to do now. I just want to make sure that
> SQLite gives the least surprise.
>  https://en.wikipedia.org/wiki/Principle_of_least_astonishment

It's not cheap - it's also not how I meant it, but I'll take your point 
and rest here.

> ?I do not think it is. When you add something to the database to 
> signify that a primary key is not allowed to be NULL, then this is not 
> in an old database, ergo in the old database NULLs are allowed. Where 
> does backward compatibility get broken? As I see it, it is as with 
> partial indexes. 

> ?
>> B. Your suggestion would break backward compatibility, no matter how
>> "light" you coat it.
>>
>> ?I really do not see this. Could you expand on that?

Imagine a program written 2 years ago, for instance works on all Apple 
computers, or perhaps Android phones. In fact, imagine several programs 
where the programmers used, either through conscious decision to employ 
a "feature" of SQLite, or perhaps simply out of ignorance, the NULL 
values allowed in PK situation.
Some time later, SQLite gets updated with your requested new default. 
These programs get re-compiled with defaults, or even just use the 
packaged SQLite that are now updated inside OSX or Android, etc. 
Suddenly, their programs do no longer work, Keys fail to get inserted... 
Users have devices crashing everywhere. Apple perhaps quickly rolls back 
to a previous version that was not so damaged, but every compiled-in 
version of the SQLite code is out in the wild causing problems. SQLite 
runs on billions of devices and systems.

This is what backwards-compatible means, that a system and data will 
still work as it always worked, even after you upgrade the engine. To 
get to your example of Partial indices - if a DB did not use them 
before, then it still doesn't use them, all is well. Only new DB's could 
use them.

So if you opt for a pragma that lets you avoid NULLs once you activate 
it... sure, but who will that really help? People will need to read the 
documentation to even know that pragma exists (which you pointed out 
they don't usually do in the first place), and simply /knowing/ the 
reason for that pragma, will obviate the need for it.

I am hoping that is as clear as possible with no hint of mocking - I 
honestly mean it well.



>> C. The suggested work-around would introduce more complication than it is 
>> solving.
>>
> ?I do not see that either. Could you enlighten me?

I trust this point was made above too.
?
> ?I like SQLite very much also. I even gave a presentation about it on 
> T-DOSE. As you can see from the plethora of questions I ask(ed) I want 
> to get serious with it. I do not use MySQL anymore and plan to migrate 
> what I still have in H2 to SQLite also. :-) I do not say there is 
> never a reason for another database, but I think that in my case there 
> is not (at this moment of time). 

That's great news :)
Let me just note that we do not really shun the likes of Postgress, 
MSSQL, MySQL etc. - those systems answer a different need. If you for 
instance want to store secret information with per-table user access 
controls, or run a networked client-server kind of data model, then you 
should not use SQLite, you should really use one of those. SQLite is 
much better for local storage though, and even as a software data 
storage for your own software - as Richard delightfully likes to point 
out (I'm paraphrasing a bit since I cannot recall the actual quote) - 
"We'd like you to not think of SQLite as a replacement for 
Postgress/MySQL/etc  We'd like you to think of it as a replacement 
for fOpen()."


Cheers,
Ryan

[sqlite] Primary key values can be NULL

[Cecil Westerhof]

2016-04-15 20:10 GMT+02:00 John McKown :

> On Fri, Apr 15, 2016 at 1:00 PM, Cecil Westerhof 
> wrote:
> ??
>
>
> >
> > ?I do not think it is. When you add something to the database to signify
> > that a primary key is not allowed to be NULL, then this is not in an old
> > database, ergo in the old database NULLs are allowed. Where does backward
> > compatibility get broken?
> >
>
> ?I am somewhat hesitant to ?join in to this, however briefly. What occurs
> to me on the breaking of backward in compatibility is an old application,
> which is dependent on NULLs in a primary key, creating a _new_ database.
> Perhaps because it has a "unload" and "reload" or "import" capability. Or
> even one which depends on the user using the sqlite3 command to do backups.
> If a PRAGMA were to be established as you have suggested, then it needs to
> default to the _old_ way of doing things simply because the aforementioned
> old application will not know of it and thus not use it.
>

?That would make sense. It looks strange to me to depend on NULLs in a
primary key, but you never know.

When the default is the old way, then the pragma would not be very
beneficial in my opinion. So I think we can take the pragma from the table.
On the other hand it could be handy that by using the pragma there is no
need for ?unnecessary? NOT NULLs, but that becomes a lot less interesting
as what I first thought.

The next best option is then to make the problem very clear, so people will
not be bitten.

-- 
Cecil Westerhof

[sqlite] Working with UUID

[Cecil Westerhof]

2016-04-15 19:47 GMT+02:00 Dominique Devienne :

> On Fri, Apr 15, 2016 at 3:56 PM, Cecil Westerhof 
> wrote:
>
> > 2016-04-15 8:45 GMT+02:00 Dominique Devienne :
> > > On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof <
> cldwesterhof at gmail.com>>
> > wrote:
> >
> > ?Is there a way to convert a hex string to a blob? I did not find it
> yet.?
> >
>
> I didn't find one either (see below), beside formatting a SQL statement
> with a blob literal,
> kinda like sprintf(..., "x'%s'", hex_string), which is hardly an option.
> I'm surprised there's no built-in function or SQL way to do it.
> I suspect there is, and I'm missing something. Hopefully someone will chime
> in.
>
> Perhaps a CTE could do it.


?I have to delve in that also. You are taking my sleep away. ;-)

?


> I'd reach for C/C++ and custom functions for
> such things.
>

?I am probably going to work with Java, but when exploring I am using Bash
and SQLite Browser.

In a program I have been testing with UUID, I had:
ps.setBytes(1, getRandomUUIDBlob());
and the function itself:
private static byte[] getRandomUUIDBlob() {
ByteBuffer  bb;
byte[]  bytes = new byte[16];
UUIDuuid  = UUID.randomUUID();

bb = ByteBuffer.wrap(bytes);
bb.putLong(uuid.getMostSignificantBits());
bb.putLong(uuid.getLeastSignificantBits());
return bytes;
}

This worked. I have to clean it up to show some strange things I noticed.
(In my eyes.)

?


> (I'd use a UDF for the printing part as well, I suspect it's faster that
> way, but didn't measure it).
>

?And something else to keep me awake. ;-)?




> C:\Users\DDevienne>sqlite3
> SQLite version 3.10.2 2016-01-20 15:27:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite>
> sqlite> select typeof(x'ab');
> blob
> sqlite> select typeof('ab');
> text
> sqlite> select typeof(cast('ab' as blob));
> blob
> sqlite> select hex(x'ab');
> AB
> sqlite> select hex(cast('ab' as blob));
> 6162
> sqlite>
>


?Here I get a blob:
sqlite> WITH UUIDTable AS (
   ...> SELECT '3DBA81DE-7AA7-412E-954F-5B2DA8D4AB6C' AS UUIDStr
   ...> )
   ...> SELECT TYPEOF(CAST(SUBSTR(UUIDStr,  1,  8) ||
   ...>SUBSTR(UUIDStr, 10,  4) ||
   ...>SUBSTR(UUIDStr, 15,  4) ||
   ...>SUBSTR(UUIDStr, 20,  4) ||
   ...>SUBSTR(UUIDStr, 25, 12) AS blob))
   ...> FROM UUIDTable
   ...> ;
blob

But when I do not use TYPEOF I get a string instead of a blob:
sqlite> WITH UUIDTable AS (
   ...> SELECT '3DBA81DE-7AA7-412E-954F-5B2DA8D4AB6C' AS UUIDStr
   ...> )
   ...> SELECT CAST(SUBSTR(UUIDStr,  1,  8) ||
   ...> SUBSTR(UUIDStr, 10,  4) ||
   ...> SUBSTR(UUIDStr, 15,  4) ||
   ...> SUBSTR(UUIDStr, 20,  4) ||
   ...> SUBSTR(UUIDStr, 25, 12) AS blob)
   ...> FROM UUIDTable
   ...> ;
3DBA81DE7AA7412E954F5B2DA8D4AB6C

What am I doing wrong?

-- 
Cecil Westerhof

[sqlite] Primary key values can be NULL

[Cecil Westerhof]

2016-04-15 16:43 GMT+02:00 R Smith :

>
>
> On 2016/04/15 2:09 PM, Cecil Westerhof wrote:
>
>> If you go to:
>>  https://www.sqlite.org/lang_createtable.html
>>
>> You will find:
>>  According to the SQL standard, PRIMARY KEY should always
>>  imply NOT NULL. Unfortunately, due to a bug in some early
>>  versions, this is not the case in SQLite. Unless the column
>>  is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID
>>  table or the column is declared NOT NULL, SQLite allows NULL
>>  values in a PRIMARY KEY column. SQLite could be fixed to
>>  conform to the standard, but doing so might break legacy
>>  applications. Hence, it has been decided to merely document
>>  the fact that SQLite allowing NULLs in most PRIMARY KEY
>>  columns.
>>
>
> I'm sorry, I think we are talking past each other - the above is exactly
> my point made. The documentation clearly explains how it works in SQLite,
> even if different to the other DBs (and yes, sadly the standard[1] too in
> this case) there is no reason to "expect" behaviour different to what is
> documented. My point was that your "expectation" was based on your beliefs
> and not based on what you've read in SQLite documentation, which can be the
> only real incitement of expectation from any "SQLite" system. There does
> not exist a single DB engine which implements the SQLite99 (or other)
> standard to the letter - PostGres and SQLite probably being some of the
> closest adherents.
>

?Do you want me to tell you that you always read all the documentation of
all the software before you use it? I surely do not. And I think most
people do not. To be honest I think you do also not: it is nearly
impossible.

When reading what the documentation says, I interpret it as: we really
would like to comply to the standard. But we cannot.
I (think I) gave a way to give the maintainers what they want. If there is
a reason that what I suggest will not work, I do not mind to hear it. But
just saying (in this case) you should have read the documentation, I find
not helpful and to be honest a little cheap. Especially because the fix is
mostly for new users. I know what to do now. I just want to make sure that
SQLite gives the least surprise.
https://en.wikipedia.org/wiki/Principle_of_least_astonishment



> And I am not the only one who has fallen for this problem. I found out
>
>> because I noticed people complaining about it. Then I checked and found
>> out
>> that it did not work as expected.
>>
>> If a car company creates a car where the brakes does not work when you
>> make
>> a left turn, but they put this in the documentation of the car. Do you
>> think it is the customers fault if the find out this when they want to
>> brake while making a left turn?
>>
>
> This example is a stick-man argument, brakes are legally required to be in
> the position they are, but your argument could work if moved sideways
> somewhat to, for instance, removing the headlight switch and making it
> automated. This would certainly be unexpected and probably not fall inside
> any standard, however, if it is documented correctly and fully (perhaps
> marked in red) and it is working as documented, nobody would have a real
> cause for dismay. This kind of thing does happen - and yes, I have seen
> many complaints as you noted, but 10 or even 20 complaints by people who
> are all not reading the documentation is still hard to hold up as
> validation for breaking backward compatibility to millions of systems. (And
> yes, this *is* what you are asking for still).


?I do not think it is. When you add something to the database to signify
that a primary key is not allowed to be NULL, then this is not in an old
database, ergo in the old database NULLs are allowed. Where does backward
compatibility get broken?
As I see it, it is as with partial indexes. That is a big change (I think),
but it did not break backward compatibility.


?I know that I am new here, but I do not think I should be mocked. You make
>> my suggestion sound needlessly complicated. SQLite could be made in such a
>> way that when it creates a new database, this is with the pragma. Then
>> everything works perfect. The old database does not brake, because it does
>> not have the pragma. And when creating a new database it confirms to the
>> SQL standard and those not create nasty surprises for the unaware.
>>
>
> Firstly, I am terribly sorry if my response came across as mocking, this
> was never my intent and probably only sounds that way cause you are new and
> have not seen many of my posts. Please allow me to assure you that I would
> never mock you and in no way thought that your suggestion was mock-worthy.


?Thank you for clarifying that up.

?


> I fully understand your need (as I've said, we've seen it many times
> before), and I am also not saying that your assertion and request is
> unfounded. What I /AM/ saying is that, even though it does bite -
> A. The documentation 

[sqlite] Working with UUID

[Dominique Devienne]

On Fri, Apr 15, 2016 at 3:56 PM, Cecil Westerhof 
wrote:

> 2016-04-15 8:45 GMT+02:00 Dominique Devienne :
> > On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof  > gmail.com>>
> wrote:
>
> ?Is there a way to convert a hex string to a blob? I did not find it yet.?
>

I didn't find one either (see below), beside formatting a SQL statement
with a blob literal,
kinda like sprintf(..., "x'%s'", hex_string), which is hardly an option.
I'm surprised there's no built-in function or SQL way to do it.
I suspect there is, and I'm missing something. Hopefully someone will chime
in.

Perhaps a CTE could do it. I'd reach for C/C++ and custom functions for
such things.
(I'd use a UDF for the printing part as well, I suspect it's faster that
way, but didn't measure it).

C:\Users\DDevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> select typeof(x'ab');
blob
sqlite> select typeof('ab');
text
sqlite> select typeof(cast('ab' as blob));
blob
sqlite> select hex(x'ab');
AB
sqlite> select hex(cast('ab' as blob));
6162
sqlite>

[sqlite] Patch that add table alias capability to delete/updatestatements.

[Domingo Alvarez Duarte]

Hello ! 

 Here is a patch that add table alias capability to delete/update statements.




https://github.com/mingodad/sqlite/commit/817636a48e5c92f7b747f3ddf487cbda21ec

 8fa5 

 Cheers !  

?

[sqlite] obtaining bracketing rows where rows are missing

[Rowan Worth]

sqlite> create table a(a integer primary key);

sqlite> SELECT a1.a, a2.a FROM a AS a1, a AS a2
WHERE a2.a > a1.a
GROUP BY a1.a
HAVING a2.a = min(a2.a)
AND a2.a <> a1.a + 1;

3|5
5|8
11|14

IIRC "HAVING x = min(x)" is not portable SQL but it seems to work in sqlite.

-Rowan



On 15 April 2016 at 18:04, Paul Sanderson 
wrote:

> I confused myself with that title.
>
> I have a DB with 300 odd rows with an integer primary key, there are a
> few breaks in the key numbering such that I have something like
>
> 1
> 2
> 3
> 5
> 8
> 9
> 10
> 11
> 14
>
> I can generate a list of misisng rows easily enough , eg
>
> 4
> 6
> 7
> 12
> 13
>
> but I would like a table of the rows that bracket the missing rows e.g.
>
> 3, 5
> 5, 8
> 11, 14
>
> any ideas how I might go about this?
>
> Cheers
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Primary key values can be NULL

[R Smith]

On 2016/04/15 2:09 PM, Cecil Westerhof wrote:
>
>> What makes you "expect" this?  Nothing in the SQLite documentation I hope.
>>> Is it perhaps habit based on how some other DBs do it?
>>>
>>> How will "those who don't know about NOT NULL" be better serviced by a
>>> pragma which they also don't know about?
>>> The documentation is very clear on the matter, even a cursory glance would 
>>> educate "the unaware".
>>>
>>> Making more things with more documentation to read, in order to help people 
>>> with other documentation they didn't read - not a salient course of action 
>>> I think.
> ?Maybe you should read the documentation of SQLite? ;-)
>
> If you go to:
>  https://www.sqlite.org/lang_createtable.html
>
> You will find:
>  According to the SQL standard, PRIMARY KEY should always
>  imply NOT NULL. Unfortunately, due to a bug in some early
>  versions, this is not the case in SQLite. Unless the column
>  is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID
>  table or the column is declared NOT NULL, SQLite allows NULL
>  values in a PRIMARY KEY column. SQLite could be fixed to
>  conform to the standard, but doing so might break legacy
>  applications. Hence, it has been decided to merely document
>  the fact that SQLite allowing NULLs in most PRIMARY KEY
>  columns.

I'm sorry, I think we are talking past each other - the above is exactly 
my point made. The documentation clearly explains how it works in 
SQLite, even if different to the other DBs (and yes, sadly the 
standard[1] too in this case) there is no reason to "expect" behaviour 
different to what is documented. My point was that your "expectation" 
was based on your beliefs and not based on what you've read in SQLite 
documentation, which can be the only real incitement of expectation from 
any "SQLite" system. There does not exist a single DB engine which 
implements the SQLite99 (or other) standard to the letter - PostGres and 
SQLite probably being some of the closest adherents.

[1] - Many other DBs also do not hold to the standard 100% here either, 
some allow NULL for instance, but only one, where SQLite would allow 
many, etc.

> And I am not the only one who has fallen for this problem. I found out
> because I noticed people complaining about it. Then I checked and found out
> that it did not work as expected.
>
> If a car company creates a car where the brakes does not work when you make
> a left turn, but they put this in the documentation of the car. Do you
> think it is the customers fault if the find out this when they want to
> brake while making a left turn?

This example is a stick-man argument, brakes are legally required to be 
in the position they are, but your argument could work if moved sideways 
somewhat to, for instance, removing the headlight switch and making it 
automated. This would certainly be unexpected and probably not fall 
inside any standard, however, if it is documented correctly and fully 
(perhaps marked in red) and it is working as documented, nobody would 
have a real cause for dismay. This kind of thing does happen - and yes, 
I have seen many complaints as you noted, but 10 or even 20 complaints 
by people who are all not reading the documentation is still hard to 
hold up as validation for breaking backward compatibility to millions of 
systems. (And yes, this *is* what you are asking for still).

>
> ?I know that I am new here, but I do not think I should be mocked. You make
> my suggestion sound needlessly complicated. SQLite could be made in such a
> way that when it creates a new database, this is with the pragma. Then
> everything works perfect. The old database does not brake, because it does
> not have the pragma. And when creating a new database it confirms to the
> SQL standard and those not create nasty surprises for the unaware.

Firstly, I am terribly sorry if my response came across as mocking, this 
was never my intent and probably only sounds that way cause you are new 
and have not seen many of my posts. Please allow me to assure you that I 
would never mock you and in no way thought that your suggestion was 
mock-worthy. I fully understand your need (as I've said, we've seen it 
many times before), and I am also not saying that your assertion and 
request is unfounded. What I /AM/ saying is that, even though it does 
bite -
A. The documentation clearly shows the truth, anyone can read it there.
B. Your suggestion would break backward compatibility, no matter how 
"light" you coat it.
C. The suggested work-around would introduce more complication than it 
is solving.

If I thought your suggestions or requests were silly, I would not bother 
to answer (luckily we have not many of those on this forum). Also, 
welcome to SQLite, it's one of the best things that exist in this World, 
and please do post all your questions, even if they do sound silly - 
chances are others watching the list might learn from the replies - I 
know I do. 

[sqlite] System.Data.SQLite version 1.0.100.0 released

[Brad Stiles]

It's *all* 1s and 0s. 

> On Apr 15, 2016, at 12:46, Tim Uy  wrote:
> 
> that is a lot of 1s and 0s.
> 
>> On Fri, Apr 15, 2016 at 10:42 AM, Joe Mistachkin  
>> wrote:
>> 
>> 
>> System.Data.SQLite version 1.0.100.0 (with SQLite 3.12.1) is now available
>> on the System.Data.SQLite website:
>> 
>> https://system.data.sqlite.org/
>> 
>> Further information about this release can be seen at:
>> 
>> https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki
>> 
>> Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you
>> encounter any problems with this release.
>> 
>> --
>> Joe Mistachkin
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Working with UUID

[Cecil Westerhof]

2016-04-15 8:45 GMT+02:00 Dominique Devienne :

> On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof 
> wrote:
>
> > I want to work with UUID's. (Version 4.)
>
>
> Honestly, your post is a little vague. But maybe the following will help.
>

?I am not always very good in asking questions. :'-(

?


> > What is the smartest way to put a check on this?
> >
>
> check what? CHECK constraint? SQLite is dynamically typed, and to enforce a
> column's
> type you can use check constraints.
>
> A blob UUID is 16 bytes, not 4.


?Eeeh. :-*

?


> So 36 vs 16 is a matter of choice.
> I prefer blob guids myself. See below for uid-related insert/select SQL.
>

?I myself also, but I am sometimes told I want to optimise to much. And
when using blobs the checks become much easier.

?


> PS: Note that these are random blobs, so the type-bits don't follow the
> UUID spec. I don't care personally. YMMV.
>

?I think I do and it looks like I got it working.

?


> sqlite> create table t (uid blob primary key CHECK(typeof(uid)='blob' and
> length(uid)=16));
>

?I will go for this. With checks it becomes:
?CREATE TABLE t (
UUID BLOB PRIMARY KEY CHECK(TYPEOF(UUID) = 'blob' AND LENGTH(UUID) = 16
AND substr(HEX(UUID), 13, 1) == '4' AND substr(HEX(UUID), 17, 1) IN ('8',
'9', 'A', 'B'))
)
?



> sqlite> select substr(u,1,8)||'-'||substr(u,9,4)||'-4'||substr(u,13,3)||
>...> '-'||substr(u,17,3)||'-'||substr(u,21,12) from (
>...> select hex(uid) as u from t
>...> );
>

?And this is the way to display them.

?Is there a way to convert a hex string to a blob? I did not find it yet.?


Most are rejected with:
insert into t values (randomblob(16));

Accepted are:
8E51A309-BC11-47CD-88C6-3F428D559B89
6C66572C-3FA1-4BEF-90D1-97678C30CB1D
3A713997-4035-4EDD-8E93-F7E3F579EF1D

Thus it looks like it works.

-- 
Cecil Westerhof

[sqlite] Primary key values can be NULL

[John McKown]

On Fri, Apr 15, 2016 at 3:04 PM, R Smith  wrote:

>
> That's great news :)
> Let me just note that we do not really shun the likes of Postgress, MSSQL,
> MySQL etc. - those systems answer a different need. If you for instance
> want to store secret information with per-table user access controls, or
> run a networked client-server kind of data model, then you should not use
> SQLite, you should really use one of those. SQLite is much better for local
> storage though, and even as a software data storage for your own software -
> as Richard delightfully likes to point out (I'm paraphrasing a bit since I
> cannot recall the actual quote) - "We'd like you to not think of SQLite as
> a replacement for Postgress/MySQL/etc  We'd like you to think of it as
> a replacement for fOpen()."
>

I am not really a developer. More of a dilettante programmer. ?But this
last is how I think of ?using SQLite. I use for permanent storage, in
preference to reading regular file and putting the data into something like
a Java Hashmap.



>
>
> Cheers,
> Ryan
>


-- 
"He must have a Teflon brain -- nothing sticks to it"
Phyllis Diller

Maranatha! <><
John McKown

[sqlite] System.Data.SQLite version 1.0.100.0 released

[Scott Robison]

I think it's an Elias omega code.

On Fri, Apr 15, 2016 at 11:46 AM, Tim Uy  wrote:

> that is a lot of 1s and 0s.
>
> On Fri, Apr 15, 2016 at 10:42 AM, Joe Mistachkin 
> wrote:
>
> >
> > System.Data.SQLite version 1.0.100.0 (with SQLite 3.12.1) is now
> available
> > on the System.Data.SQLite website:
> >
> >  https://system.data.sqlite.org/
> >
> > Further information about this release can be seen at:
> >
> >  https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki
> >
> > Please post on the SQLite mailing list (sqlite-users at sqlite.org) if
> you
> > encounter any problems with this release.
> >
> > --
> > Joe Mistachkin
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Scott Robison

[sqlite] Checks with dates

[Cecil Westerhof]

2016-04-15 2:29 GMT+02:00 Keith Medcalf :

>
> create table x
> (
>   ds text not null check(date(ds) is not null),
>   de text not null check(date(de) is not null),
>   check(ds < de)
> );
> ??
> ??
> insert into x values ('2016-04-15', '2016-04-13');
> insert into x values ('2016-04-15', '2016-04-17');
> insert into x values ('2016-04-15', '2016-04-32');
>
> The constraint on the column values is that date() is not null (ie, a
> valid date)
>

?Almost, but the dates are allowed to be NULL. But this led me to:
CREATE TABLE x (
ds TEXT CHECK(ds IS NULL OR COALESCE(DATE(ds) == ds, 0)),
de TEXT CHECK(de IS NULL OR COALESCE(DATE(de) == de, 0)),
CHECK(ds IS NULL OR de IS NULL OR ds < de)
)

And this does what I want.

Thanks.

-- 
Cecil Westerhof

[sqlite] Primary key values can be NULL

[Cecil Westerhof]

2016-04-15 1:19 GMT+02:00 J Decker :

> I would total expect any column I created without NOT NULL (double
> negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is applied
> additionallywhat database does otherwise?  MSSQL?
>

?Every database that is not SQLite, because it is the standard.?

-- 
Cecil Westerhof

[sqlite] Primary key values can be NULL

[Cecil Westerhof]

2016-04-15 1:19 GMT+02:00 J Decker :

> I would total expect any column I created without NOT NULL (double
> negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is applied
> additionallywhat database does otherwise?  MSSQL?
>
> On Thu, Apr 14, 2016 at 2:56 PM, R Smith  wrote:
> >
> >
> > On 2016/04/14 10:23 PM, Cecil Westerhof wrote:
> >>
> >> 2016-04-14 22:10 GMT+02:00 Richard Hipp :
> >>
> >>> On 4/14/16, Cecil Westerhof  wrote:
> 
>  Yes that makes sense. But could not a type of PRAGMA be used? So if
> the
>  PRAGMA is not defined the old functionality and your historical data
> is
>  save. And if the PRAGMA is defined the new functionality.
> 
> >>> Easier:  Just declare the columns in question as NOT NULL.  Or use a
> >>> WITHOUT ROWID table which *does* enforce NOT NULL.
> >>>
> >> The problem is that you do not expect that values in the primary key can
> >> be NULL, so you do not use NOT NULL. I now know that I need to do this
> and
> >> I do it now, but the unaware will be bitten, just as I was. It was not a
> >> big problem, because I just started playing with SQLite, but when you
> have
> >> a very big database which gets a lot of data every day and a year later
> >> you
> >> find out you are bitten by this ?
> >
> >
> > What makes you "expect" this?  Nothing in the SQLite documentation I
> hope.
> > Is it perhaps habit based on how some other DBs do it?
> >
> > How will "those who don't know about NOT NULL" be better serviced by a
> > pragma which they also don't know about?
> > The documentation is very clear on the matter, even a cursory glance
> would
> > educate "the unaware".
> >
> > Making more things with more documentation to read, in order to help
> people
> > with other documentation they didn't read - not a salient course of
> action I
> > think.
>

?Maybe you should read the documentation of SQLite? ;-)

If you go to:
https://www.sqlite.org/lang_createtable.html

You will find:
According to the SQL standard, PRIMARY KEY should always
imply NOT NULL. Unfortunately, due to a bug in some early
versions, this is not the case in SQLite. Unless the column
is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID
table or the column is declared NOT NULL, SQLite allows NULL
values in a PRIMARY KEY column. SQLite could be fixed to
conform to the standard, but doing so might break legacy
applications. Hence, it has been decided to merely document
the fact that SQLite allowing NULLs in most PRIMARY KEY
columns.

And I am not the only one who has fallen for this problem. I found out
because I noticed people complaining about it. Then I checked and found out
that it did not work as expected.

If a car company creates a car where the brakes does not work when you make
a left turn, but they put this in the documentation of the car. Do you
think it is the customers fault if the find out this when they want to
brake while making a left turn?

?I know that I am new here, but I do not think I should be mocked. You make
my suggestion sound needlessly complicated. SQLite could be made in such a
way that when it creates a new database, this is with the pragma. Then
everything works perfect. The old database does not brake, because it does
not have the pragma. And when creating a new database it confirms to the
SQL standard and those not create nasty surprises for the unaware.

-- 
Cecil Westerhof

[sqlite] Primary key values can be NULL

[John McKown]

On Fri, Apr 15, 2016 at 1:00 PM, Cecil Westerhof 
wrote:
??


>
> ?I do not think it is. When you add something to the database to signify
> that a primary key is not allowed to be NULL, then this is not in an old
> database, ergo in the old database NULLs are allowed. Where does backward
> compatibility get broken?
>

?I am somewhat hesitant to ?join in to this, however briefly. What occurs
to me on the breaking of backward in compatibility is an old application,
which is dependent on NULLs in a primary key, creating a _new_ database.
Perhaps because it has a "unload" and "reload" or "import" capability. Or
even one which depends on the user using the sqlite3 command to do backups.
If a PRAGMA were to be established as you have suggested, then it needs to
default to the _old_ way of doing things simply because the aforementioned
old application will not know of it and thus not use it.



> As I see it, it is as with partial indexes. That is a big change (I think),
> but it did not break backward compatibility.
>
>
-- 
"He must have a Teflon brain -- nothing sticks to it"
Phyllis Diller

Maranatha! <><
John McKown

[sqlite] Why SQLite allows to create table with unknown types?

[Vladimir Soldatov]

*Actually I have found that SQLite creates tables with unknown types
when I was investigating an issue about really bad performance during
execution DELETE requests. The root cause was a bug in our custom
protocol input stream decoder removing space symbols from input data,
so the input commands were converted to SQL request like CREATE
table(id INTEGERPRIMARYKEYNOTNULL) and next INSERT requests
performance was pretty good, but DELETE requests were extremely slow.*

*Anyway, thanks for the link above I will take a look.*


*Vladimir
*
> What makes you believe such a typo would affect performance > in any way?
> It should not make any difference.
> --
>Igor Tandetnik

[sqlite] Patch that add table alias capability to delete/update statements.

[Cezary H. Noweta]

Hello,

Would you be so kind as to use ``Write e-mail'' instead of ``Reply'' 
when you are beginning a new thread?

Thank you in advance.

-- best regards

Cezary H. Noweta

[sqlite] minor documentation typo

[Paul van Helden]

Also in partialindex.html:  The WHERE clause may *not* contains subqueries
should be contain

Very happy to have learned about partial indexes today!

On Fri, Mar 25, 2016 at 1:32 AM, Wolfgang Enzinger 
wrote:

> minor documentation typo here:
> https://www.sqlite.org/partialindex.html
>
> 3.0 Queries Using Partial Indexes
> [...]
> Example: Let the index by
>
> should be
>
> Example: Let the index be
>
> Greetings, Wolfgang
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Working with booleans

[James K. Lowden]

On Thu, 14 Apr 2016 20:10:08 -0400
"Keith Medcalf"  wrote:

> select  from  where isActive;

vs. 

> select  from  where isActive = 'T';

AFAIK, the SQL standard requires the second form.  You can't simply say
"WHERE variable"; you must say "WHERE expression".  

OP: I personally usually use the T/F or Y/N versions over 1/0 because my
interactive query results are just a little easier to interpret.  Use
what you like, because size and performance differences are likely to
be negligible.  

--jkl

[sqlite] Primary key values can be NULL

[James K. Lowden]

On Fri, 15 Apr 2016 14:13:12 +0200
Cecil Westerhof  wrote:

> 2016-04-15 1:19 GMT+02:00 J Decker :
> 
> > I would total expect any column I created without NOT NULL (double
> > negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is
> > applied additionallywhat database does otherwise?  MSSQL?
> >
> 
> ?Every database that is not SQLite, because it is the standard.?

Last I checked, Microsoft SQL Server raised an error if you declared a
table to have a primary key with a NULL column.  Whether NULL or NOT
NULL was the default depended on database and connection options, but
if the effect was to use a NULL column in a primary key, it failed.  

I stopped worrying about the default a long time ago.  I figure every
time I type NOT NULL, an angel gets its wings.  

--jkl

[sqlite] obtaining bracketing rows where rows are missing

[Paul Sanderson]

Thanks
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 15 April 2016 at 11:14, Rowan Worth  wrote:
> sqlite> create table a(a integer primary key);
>
> sqlite> SELECT a1.a, a2.a FROM a AS a1, a AS a2
> WHERE a2.a > a1.a
> GROUP BY a1.a
> HAVING a2.a = min(a2.a)
> AND a2.a <> a1.a + 1;
>
> 3|5
> 5|8
> 11|14
>
> IIRC "HAVING x = min(x)" is not portable SQL but it seems to work in sqlite.
>
> -Rowan
>
>
>
> On 15 April 2016 at 18:04, Paul Sanderson 
> wrote:
>
>> I confused myself with that title.
>>
>> I have a DB with 300 odd rows with an integer primary key, there are a
>> few breaks in the key numbering such that I have something like
>>
>> 1
>> 2
>> 3
>> 5
>> 8
>> 9
>> 10
>> 11
>> 14
>>
>> I can generate a list of misisng rows easily enough , eg
>>
>> 4
>> 6
>> 7
>> 12
>> 13
>>
>> but I would like a table of the rows that bracket the missing rows e.g.
>>
>> 3, 5
>> 5, 8
>> 11, 14
>>
>> any ideas how I might go about this?
>>
>> Cheers
>>
>> Paul
>> www.sandersonforensics.com
>> skype: r3scue193
>> twitter: @sandersonforens
>> Tel +44 (0)1326 572786
>> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
>> -Forensic Toolkit for SQLite
>> email from a work address for a fully functional demo licence
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] obtaining bracketing rows where rows are missing

[Paul Sanderson]

I confused myself with that title.

I have a DB with 300 odd rows with an integer primary key, there are a
few breaks in the key numbering such that I have something like

1
2
3
5
8
9
10
11
14

I can generate a list of misisng rows easily enough , eg

4
6
7
12
13

but I would like a table of the rows that bracket the missing rows e.g.

3, 5
5, 8
11, 14

any ideas how I might go about this?

Cheers

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

[sqlite] System.Data.SQLite version 1.0.100.0 released

[Tim Uy]

that is a lot of 1s and 0s.

On Fri, Apr 15, 2016 at 10:42 AM, Joe Mistachkin  wrote:

>
> System.Data.SQLite version 1.0.100.0 (with SQLite 3.12.1) is now available
> on the System.Data.SQLite website:
>
>  https://system.data.sqlite.org/
>
> Further information about this release can be seen at:
>
>  https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki
>
> Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you
> encounter any problems with this release.
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] System.Data.SQLite version 1.0.100.0 released

[Joe Mistachkin]

System.Data.SQLite version 1.0.100.0 (with SQLite 3.12.1) is now available
on the System.Data.SQLite website:

 https://system.data.sqlite.org/

Further information about this release can be seen at:

 https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you
encounter any problems with this release.

--
Joe Mistachkin

[sqlite] obtaining bracketing rows where rows are missing

[Igor Tandetnik]

On 4/15/2016 6:04 AM, Paul Sanderson wrote:
> but I would like a table of the rows that bracket the missing rows e.g.
>
> 3, 5
> 5, 8
> 11, 14

with Brackets as (
   select t1.id low, (select min(t2.id) from mytable t2 where t2.id > 
t1.id) high
   from mytable t1
)
select low, high from Brackets where high > low + 1;

-- 
Igor Tandetnik

[sqlite] Working with UUID

[Dominique Devienne]

On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof 
wrote:

> I want to work with UUID's. (Version 4.)


Honestly, your post is a little vague. But maybe the following will help.


> What is the smartest way to put a check on this?
>

check what? CHECK constraint? SQLite is dynamically typed, and to enforce a
column's
type you can use check constraints.

A blob UUID is 16 bytes, not 4. So 36 vs 16 is a matter of choice.
I prefer blob guids myself. See below for uid-related insert/select SQL.
--DD

PS: Note that these are random blobs, so the type-bits don't follow the
UUID spec. I don't care personally. YMMV.

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\DDevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> create table t (uid blob primary key);
sqlite> insert into t values (1), ('foo'), (x'abcd');
sqlite> select uid, typeof(uid), length(uid) from t;
1|integer|1
foo|text|3
??|blob|2

sqlite> create table t (uid blob primary key CHECK(typeof(uid)='blob' and
length(uid)=16));
sqlite> select uid, typeof(uid), length(uid) from t;
sqlite> insert into t values (1), ('foo'), (x'abcd');
Error: CHECK constraint failed: t
sqlite> select uid, typeof(uid), length(uid) from t;
sqlite> insert into t values (randomblob(16));
sqlite> insert into t values (randomblob(16));
sqlite> insert into t values (randomblob(16));
sqlite> select * from t;
?2?!???
o??T?&
e?rBq.???H??f?

sqlite> select quote(uid) from t;
X'674ED1D53CABCA0D86329A219F0EE8A4'
X'11F4BBE18CF36FC2C8159D9CEB54F126'
X'65F37242712E89A2E894480107F466A6'

sqlite> select hex(uid) from t;
674ED1D53CABCA0D86329A219F0EE8A4
11F4BBE18CF36FC2C8159D9CEB54F126
65F37242712E89A2E894480107F466A6

sqlite> select substr(u,1,8)||'-'||substr(u,9,4)||'-4'||substr(u,13,3)||
   ...> '-'||substr(u,17,3)||'-'||substr(u,21,12) from (
   ...> select hex(uid) as u from t
   ...> );
674ED1D5-3CAB-4CA0-863-9A219F0EE8A4
11F4BBE1-8CF3-46FC-C81-9D9CEB54F126
65F37242-712E-489A-E89-480107F466A6
sqlite>

[sqlite] Using SQLite for storing photo's

[Ketil Froyn]

I would definitely store the pictures in separate files. The main reason is
compatibility. If you store them as blobs in sqlite, you have to extract
them first to view or edit them in a another program.i Are you sure you've
covered all your use cases with your program?

Ketil
On 14 Apr 2016 7:37 p.m., "Cecil Westerhof"  wrote:

> I am thinking about storing my photo's in SQLite. This has to be done in a
> blob of-course, but I was wondering if there are any caveats, or if anyone
> has helpful tips.
>
> One thing I was wondering: what is the best way to make a backup? I would
> think a normal dump is not very handy. It would probably better to store
> the pictures them-self in one file pro picture and only store the
> (meta-)data in SQL. What would be a good way?
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Using SQLite for storing photo's

[R Smith]

On 2016/04/14 10:18 PM, Cecil Westerhof wrote:
> 2016-04-14 22:07 GMT+02:00 Clemens Ladisch :
>
>> Cecil Westerhof wrote:
>>> what is the best way to make a backup?
>> With the backup API: .
>> (Also available as .backup in the shell.)
>>
> ?I should be more precise in my communication. :'-(
>
> I prefer to make my backup with .dump. Then I have just a text based
> backup. This works fine for the regular data, but not for photos: they can
> be 13 MB big. (And I am not even using raw. When I am going to do that,
> they will become much bigger.) So I would like to have my photos split out
> when making the dump.
>
> But maybe it will be better to use external blobs.
>
> Something to think about.
>

The BLOBS and data inside SQLite is quite safe, and the backup API does 
a magnificent job, why would you prefer text dumps to backed up DB's?

Either way, you do not need to use the .dump to make data dumps, you can 
quite easily push data out using a query in which you can SELECT the 
fields you'd like, sans those you do not wish to dump. The caveat here 
is that you would not be able to reconstruct the DB perfectly from the 
dump, but then if you don't dump the BLOBs, that was never going to 
happen anyway.

I know from the old days with other DB engines, text dumps made one feel 
safe because nobody really knew what magic went on inside the files the 
DB engine used. With SQLite it's quite different though, it's very open, 
there's a myriad things that can read SQLite DBs, including the always 
free always available sqlite3.exe from the web site, and even some 
people here have data recovery systems for SQLite if you do succeed in 
hurting a DB file. Apart from maliciousness, there's really nothing that 
can hurt an SQLite file that cannot also hurt a text file - I don't 
think there is any legitimate reason to prefer text dumps over SQLite 
files anymore (especially for backups) - but that's up to you.

[sqlite] Primary key values can be NULL

[R Smith]

On 2016/04/14 10:23 PM, Cecil Westerhof wrote:
> 2016-04-14 22:10 GMT+02:00 Richard Hipp :
>
>> On 4/14/16, Cecil Westerhof  wrote:
>>> ?Yes that makes sense. But could not a type of PRAGMA be used? So if the
>>> PRAGMA is not defined the old functionality and your historical data is
>>> save. And if the PRAGMA is defined the new functionality.
>>>
>> Easier:  Just declare the columns in question as NOT NULL.  Or use a
>> WITHOUT ROWID table which *does* enforce NOT NULL.
>>
> ?The problem is that you do not expect that values in the primary key can
> be NULL, so you do not use NOT NULL. I now know that I need to do this and
> I do it now, but the unaware will be bitten, just as I was. It was not a
> big problem, because I just started playing with SQLite, but when you have
> a very big database which gets a lot of data every day and a year later you
> find out you are bitten by this ?

What makes you "expect" this?  Nothing in the SQLite documentation I 
hope. Is it perhaps habit based on how some other DBs do it?

How will "those who don't know about NOT NULL" be better serviced by a 
pragma which they also don't know about?
The documentation is very clear on the matter, even a cursory glance 
would educate "the unaware".

Making more things with more documentation to read, in order to help 
people with other documentation they didn't read - not a salient course 
of action I think.

Cheers,
Ryan

[sqlite] Patch that add table alias capability to delete/update statements.

[Domingo Alvarez Duarte]

Hello !  

Here is a patch that add table alias capability to delete/update statements. 


https://github.com/mingodad/sqlite/commit/817636a48e5c92f7b747f3ddf487cbda21ec
8fa5  

Cheers !