Re: [sqlite] Changing ID's to UUID

[Hick Gunter]

On Tuesday, 15 November, 2016 15:30, R Smith wrote:
>
>> On 2016/11/15 10:38 PM, Jens Alfke wrote:
>
>> >> On Nov 15, 2016, at 11:35 AM, Quan Yong Zhai  wrote:
>
>> >> Create a custom function MD5 ,
>> > If you’re going to go to this trouble, at least use SHA256!
>> >
>> > MD5 is broken. These days no one should be using it for anything,
>> > except
>> when needed for compatibility with legacy formats/protocols.
>>
>> This strikes me as one of those statements that, upon hearing that a
>> rocket failed to land upright, cries out: "OMG, this is the end of
>> Space exploration as we know it!".
>>
>> MD5 is not broken. What would broken mean? It still hashes a byte
>> array just as good as it always did producing an easy key to check and
>> compare against. Sure, for ultimate safety systems you /should/ note
>> that the hashes /could/ possibly be brute-forced to, in the minimum
>> case, reproduce a duplicate answer for a different byte-list.
>
>All hash functions that take an input that is larger (longer) that the output 
>must by definition have collisions.  There is no if, and, or but about it.  If 
>you input every 513 bit sequence >into SHA-512 you WILL WITH ABSOLUTE 
>CERTAINTY obtain the same 512 bit hash result at least twice.
>
This is the pigeon(hole) principle, attributed to the German mathematician 
Peter Gustav Lejeune Dirichlet, who is noted to have used this argument in 1834.


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

[Jens Alfke]

> On Nov 15, 2016, at 1:46 PM, Simon Slavin  wrote:
> 
> Do these people admit they're letting their phones run out of power ?  

There’s nothing wrong with letting your phone run out of power, and software 
should be resilient to it. I don’t think that’s the problem, though, because 
mobile OS’s perform a clean shutdown when power drops below a minimum 
threshold, so it’s not like pulling the plug on a desktop computer (or holding 
down the power button on a laptop because the kernel froze.)

> If not, I suspect some sort of programming error somewhere.  Not necessarily 
> in your own programming but sometimes in whatever database API you're using 
> which eventually results in calls to the SQLite API.
> So is your software in C or C++ and calling the SQLite API directly ?

It’s possible. Our Android library is written in Java and uses some Java 
wrapper library around SQLite. I don’t know the details myself.

The case I’m concerned about happens on Macs, as I said, and the developer says 
he can trigger it in his app via a power failure or a forced shutdown (holding 
the power button for 5 seconds.) This version of our library is one I wrote, in 
Objective-C; it uses a thin Obj-C wrapper around SQLite, but it doesn’t do 
anything magic, and although it started out as 3rd party code I’ve tweaked it 
enough that I know it inside and out.

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

Re: [sqlite] Changing ID's to UUID

[Jens Alfke]

> On Nov 15, 2016, at 1:11 PM, Scott Robison  wrote:
> 
> Completely depends on your needs. If your needs are not cryptographic, then
> there is no problem.

But there’s little reason to use MD5 instead of SHA-1; they’re roughly the same 
speed, but SHA-1 is considerably more collision-resistant.
Or if you don’t need a long digest, just use Adler32, which is about four times 
as fast.


> I still cannot fathom why anyone would assign random numbers or (even more 
> useless) long random blobs to use as psuedo-keys.  It just boggles the mind.

I take it you’re not a cryptographer :) All modern ciphers do this. For 
example, an RSA key pair is simply a pair of large random numbers (both prime) 
that meet certain criteria. Or if you use a more modern cipher like Curve25519, 
the private key is quite literally just any 256 bits of random data. You 
generate a key-pair by reading 32 bytes from /dev/random into the private key, 
and then performing a transformation on that to get the public key.

Obviously collisions are possible with long random numbers or digests, but 
secure systems are designed such that random collisions are vanishingly 
unlikely to occur for {insert large power of ten here} years, which makes the 
probability effectively zero.

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

Re: [sqlite] Encryption

[Richard Andersen]

Monday, November 14, 2016, 4:23:49 PM, you wrote:

Thanks a million Ulrich for all the useful information, I think I got
what I need now to make things work the way I want! :)

Richard

> Richard,

>> Well what I've done is to create an encrypted database with
>> SQLite2009 and then use that in my C# project. I just add the password to
>> the connection string in my app and then it works right away.

> According to the information on the SQLite2009 website
> (http://sqlite2009pro.azurewebsites.net/) SQLite2009 supports 2 encryption 
> methods:

> ** Encryption Method is now compatible with wxSQLite3 (AES-128
> bits) and SQLite3 ADO.NET Provider (RSA-MS Crypt) **

> I don't know SQLite2009 from own experience, but I assume that it
> allows you to choose which enryption method to use, when creating a
> new database. To be compatible with the ADO.NET provider
> System.Data.SQLite (http://system.data.sqlite.org) you obviously
> have to choose the corresponding encryption method in SQLite2009.

>> As far as
>> I understand (I'm new to all this) you can also create a database from
>> within your app if it's based on system.data.sqlite.
>> I just can't edit the table columns or add new ones in SQLite2009 once
>> the database is saved or reopened, I can only edit the record data. I
>> just read that once created, you can't (or only very limited) edit the
>> columns of an SQLite database, you have to create a new database with
>> the desired structure and copy the data over.

> I have a bit the impression that you mixed up the terms 'database'
> and 'table'. A 'database' can contain several tables, and adding new
> tables or removing existing tables is simple (and should be
> supported by any SQLite administration tool). However, changing the
> structure of an existing table in SQLite is more complicated, since
> SQLite only supports a limited set of operations to modify a table
> definition. Therefore, if you want to add or remove columns from a
> table definition, you usually have to create a new table with a
> different name, copy the data from the previous table to the new
> one, remove the previous table, and rename the new table to the previous name.

>> I think that's what DB Browser for SQLite does since you actually can
>> reopen and edit the columns and their parameters etc. with it.

> Under the hood DB Browser for SQLite performs the above mentioned steps for 
> you.

>> It can also create encrypted databases but the encryption scheme it uses
>> is not supported by system.data.sqlite it seems.

> Correct. DB Browser for SQLite supports SQLCipher
> (https://www.zetetic.net/sqlcipher/), an AES-256 encryption scheme.

>> But all in all it works fine, I'm just a bit concerned with the RSA
>> encryption in system.data.lite as I've heard it's slow and easy to
>> crack, so I'd prefer something else.

> The RSA encryption offered by System.Data.SQLite should not be
> used, if security is a concern for you. You should prefer an AES encryption 
> scheme.

>> SQLITE Expert looks interesting but I wonder if it also uses the
>> built-in RSA encryption in system.data.sqlite?

> According to the description on the website
> (http://www.sqliteexpert.com/features.html) SQLiteExpert "Supports
> password protected databases (requires third party SQLite library -
> not included)." That is, you have to provide a SQLite3 DLL
> supporting the encryption scheme of your choice. For example,

> - SQLCipher (https://github.com/sqlcipher/sqlcipher), you have to build the 
> DLL yourself
> - wxSQLite3 (https://github.com/utelle/wxsqlite3/releases), Windows binaries 
> are provided

> Other SQLite3 management tools that support the wxSQLite3 encryption scheme 
> are

> - SQLite Maestro
> (https://www.sqlmaestro.com/en/products/sqlite/maestro/about/)
> - wxSQLitePlus (https://github.com/guanlisheng/wxsqliteplus)

>> > Richard Andersen wrote:
>> > In DB Browser for SQlite I can edit the table but I'm not sure if the
>> > SQLCipher encryption used here can be made to work with
>> > System.Data.SQlite, or how to do if it can. Does anyone know anything
>> > about this?

> In principle, it should be possible to replace the SQLite
> encryption implementation in System.Data.SQLite by the SQLCipher or
> wxSQLite3 implementation, although it might not be trivial. The
> latter should be easier to accomplish, since the wxSQLite3
> encryption implementation is self-contained, while SQLCipher
> requires the OpenSSL library as well.

> Regards,

> Ulrich
> ___
> 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] Changing ID's to UUID

[Dominique Devienne]

> I still cannot fathom why anyone would assign random numbers or (even more
> useless) long random blobs to use as psuedo-keys.
>

Because it is decentralized. You can assign random uuids as immutable
surrogate keys to your entities without going to the db for a sequence
based integer sk. And that scales to concurrent clients. My $0.02
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advanced SQL course

[Dominique Devienne]

On Tuesday, 15 November 2016, Cecil Westerhof 
wrote:

> I can follow an advanced SQL course. It is tailored for the Oracle
> database which I do not use. I mostly use SQLite. ;-) Would it still
> be interesting to follow this course, or would it be a waste of time
>

Not a waste of time but Oracle is significantly different from SQLite so
only basic SQL is the same. UDTs  virtual columns  collections XMLDB
 Hierarchical queries  analytical queries  hints  model clause etc are all
advanced Oracle stuff that don't apply to SQLite imho. Depends on your
course of course. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing ID's to UUID

[Keith Medcalf]

On Tuesday, 15 November, 2016 15:30, R Smith wrote:

> On 2016/11/15 10:38 PM, Jens Alfke wrote:

> >> On Nov 15, 2016, at 11:35 AM, Quan Yong Zhai  wrote:

> >> Create a custom function MD5 ,
> > If you’re going to go to this trouble, at least use SHA256!
> >
> > MD5 is broken. These days no one should be using it for anything, except
> when needed for compatibility with legacy formats/protocols.
> 
> This strikes me as one of those statements that, upon hearing that a
> rocket failed to land upright, cries out: "OMG, this is the end of Space
> exploration as we know it!".
> 
> MD5 is not broken. What would broken mean? It still hashes a byte array
> just as good as it always did producing an easy key to check and compare
> against. Sure, for ultimate safety systems you /should/ note that the
> hashes /could/ possibly be brute-forced to, in the minimum case,
> reproduce a duplicate answer for a different byte-list.

All hash functions that take an input that is larger (longer) that the output 
must by definition have collisions.  There is no if, and, or but about it.  If 
you input every 513 bit sequence into SHA-512 you WILL WITH ABSOLUTE CERTAINTY 
obtain the same 512 bit hash result at least twice.

> So, if you provide public safety documents / programs / systems that may
> provide opportunity for nefarious intent individuals to abuse, please
> step up your hashing, but if you need unique keys for your program that
> keeps kids-party attendance lists, MD5 is as good a method (and as fast)
> as any - in the same way that it is still OK to use INTEGER PRIMARY KEY
> rather than a complete SHA256 primary ID column for your shopping list.
> 
> That said, I don't disagree with the notion that, IF the OP is going to
> go through the trouble of making custom functions to accomplish this
> task, might as well go full regalia.

I still cannot fathom why anyone would assign random numbers or (even more 
useless) long random blobs to use as psuedo-keys.  It just boggles the mind.




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

Re: [sqlite] Uninitialized memory reads (not likely false positives)

[Bernardo Sulzbach]

On 11/15/2016 08:53 PM, Nico Williams wrote:

Another one that I find difficult to analyze is a possible out-of-bounds
read in vdbeSorterCompareInt():

 85712 static const u8 aLen[] = {0, 1, 2, 3, 4, 6, 8 };
 85713 int i;
 85714 res = 0;
 85715 for(i=0; i0 && s1<7) || s1==8 || s1==9 );
 85701   assert( (s2>0 && s2<7) || s2==8 || s2==9 );
 85702
 85703   if( s1>7 && s2>7 ){
 85704 res = s1 - s2;
 85705   }else{
 85706 if( s1==s2 ){

At 85715 we know that (s1 <= 7 || s2 <= 7) && s1 == s2, and we also know
that either s1 or s2 can be 8 or 9, so aLen[s1] at 85715 could very well
have s1 > 6, which would read past the bounds of aLen[].

In both of these cases very detailed knowledge of the VDBE being handled
might show that these uninitialized reads do not happen.  If so, I don't
have that knowledge.

I'll hold off on other reports for the time being.

Nico



  if( s1>7 && s2>7 ){
res = s1 - s2;
  }else{
if( s1==s2 ){
  // Accesses to aLen as mentioned above

If s1 > 7 && s2 > 7 is false, then at least one of s1 and s2 is not 
above 7. If they are equal, then neither s1 nor s2 is above 7.


> and we also know that either s1 or s2 can be 8 or 9,

This is false, unless I am mistaken. See my reasoning above.

The issue is valid, and the message your analyzer (or compiler) wrote is 
correct: it is not guaranteed to be < 7, which it should be.


I am unsure whether or not this is actually a bug, but it almost 
certainly is a mistake.

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

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

[R Smith]

On 2016/11/15 10:34 PM, Jens Alfke wrote:



On Nov 15, 2016, at 10:57 AM, Simon Slavin  wrote:

sqlite> PRAGMA checkpoint_fullfsync;
1

I wasn’t aware of that pragma. Just tried it on my Mac (10.12.1), and its value 
is 1 even if I don’t first set pragma fullfsync; i.e. it defaults to 1. 
(Contradicting the docs.)

So it appears that on macOS, SQLite does use F_FULLFSYNC when checkpointing, 
but not at other times that it fsyncs. What does that mean in actual use, 
assuming that I always use WAL mode? Is there still an opportunity for 
corruption in the face of power failures?

(Sorry to be frothing at the mouth about this; but my team’s dealing with a few 
users/customers whose apps encounter db corruption, on Android as well as 
macOS, and we’re getting really frustrated trying to figure out what’s going 
on.)


Quite OK to be unsettled by learning that a flaw in the system that you 
assumed did not exist, might be the cause of your troubles. I think 
however something is missing in the complete understanding, so to be clear:


Calling F_FULLFSYNC when checkpointing or otherwise invokes a contract 
between the running software (your system) and the Operating System 
whereby the Operating system promises to A - put the current buffer's 
worth of written data INTO the BUS feeding the writable media, AND B - 
then asking said media to confirm the writing has happened (committed)  
BEFORE handing back control (moving your thread pointer along). This is 
not exactly the same for all OSes, but more or less similar.


This can slow down things, but sometimes the security is worth the price 
paid in latency. However, Hard drive manufacturers tend to sometimes lie 
about having committed your data. It is a well known and almost 
universally used method in standard desktop / laptop computers for the 
harddrive to tell the OS that: "YES indeed, I have committed" when in 
fact it is still piping data into the platters. Yes SSD's are better at 
this by simple virtue of lower latency from buffer-to-silicone, but they 
are not above lying either.


This means that unless you have a SERVER quality drive with typically 
its own battery-backup that guarantees ANY buffered writes to reach the 
platters, there simply is zero guarantee that all writes WILL go to 
disk, and any normal system that guarantees it lies.


This does not mean however that you should be experiencing corruption. 
SQLite might not be able to guarantee all writes reaching the disk, but 
in most cases, if a final write did not happen, the usual last step in 
committing a transaction is deleting / truncating a journal file or 
writing a checkpoint marker or such, which, if it did not happen, should 
have the entire write roll back (next time you open the DB) and leave 
you in a non-corrupt state. IF this does not happen it means a write may 
have happened out of order (not very common but can happen) or some 
other worse problem occurred - most importantly, FULL_FSYNC isn't the 
wild goose to be chasing. Whether or not any write happened is never an 
acceptable cause of corruption, so trying to wrestle with the thing that 
promises to make writes happen "more" as a causal relation to a 
corruption problem, is simply moot. (This is vigorously tested with 
every release of SQLite too).


If you can get the DB files (journals and all) from such a system where 
a user claims to be able to reproduce the corruption reliably, that 
would be an easy thing to check and the Devs here might learn something 
from it. You can simply make something that copy all the DB files before 
opening them at startup, until you have produced a corrupt DB, then 
those last copied files will be the corrupted DB files that can be 
investigated.


Good luck!
Ryan

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

[sqlite] SQLite as a Shell Script

[jungle Boogie]

Hi All,

Pretty interesting article:
https://www.invincealabs.com/blog/2016/11/sqlite-shell-script/

This post documents how we were able to create a SQLite database that
can be executed as an ash shell script purely from SQL queries.


Found here:
https://www.reddit.com/r/netsec/comments/5cwb07/sqlite_as_a_shell_script/



-- 
---
inum: 883510009027723
sip: jungleboo...@sip2sip.info
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Uninitialized memory reads (not likely false positives)

[Nico Williams]

I don't normally pay attention to warnings when compiling SQLite3, nor
to Coverity or other static analysis tools' output either, as I'm quite
aware that most of these are false positives and thus unwelcome noise
here.

However, I do sample them occasionally, and though usually such reports
are false positives, here are two that don't quite look like false
positives to me.  I get these from building the SQLite3 3.15.1
amalgamation.

Uninitialized pointer dereference:

115861 static void generateColumnTypes(
115862   Parse *pParse,  /* Parser context */
115863   SrcList *pTabList,  /* List of tables */
115864   ExprList *pEList/* Expressions defining the result set */
115865 ){
115866 #ifndef SQLITE_OMIT_DECLTYPE
115867   Vdbe *v = pParse->pVdbe;
115868   int i;

1. var_decl: Declaring variable sNC without initializer.
115869   NameContext sNC;
115870   sNC.pSrcList = pTabList;
115871   sNC.pParse = pParse;

2. Condition i < pEList->nExpr, taking true branch
115872   for(i=0; inExpr; i++){
115873 Expr *p = pEList->a[i].pExpr;
115874 const char *zType;
115875 #ifdef SQLITE_ENABLE_COLUMN_METADATA
115876 const char *zOrigDb = 0;
115877 const char *zOrigTab = 0;
115878 const char *zOrigCol = 0;
115879 zType = columnType(, p, , , , 0);
115880 
115881 /* The vdbe must make its own copy of the column-type and other 
115882 ** column specific strings, in case the schema is reset before 
this
115883 ** virtual machine is deleted.
115884 */
115885 sqlite3VdbeSetColName(v, i, COLNAME_DATABASE, zOrigDb, 
SQLITE_TRANSIENT);
115886 sqlite3VdbeSetColName(v, i, COLNAME_TABLE, zOrigTab, 
SQLITE_TRANSIENT);
115887 sqlite3VdbeSetColName(v, i, COLNAME_COLUMN, zOrigCol, 
SQLITE_TRANSIENT);
115888 #else

CID 12 301 (#1 of 1): Uninitialized pointer read (UNINIT)
3. uni nit_use_in_call: Using uninitialized value sNC.pNext when calling 
columnTypeImpl.
115889 zType = columnType(, p, 0, 0, 0, 0);

columnType() is a macro expanding to a call to columnTypeImpl().

Anyways, the analysis from here is non-trivial, and I can't convince
myself that sNC.pNext will not be dereferenced.

The obvious fix is to initialize sNC a bit more before the loop at
115872.  At least setting sNC.pNext = 0 seems like the right thing to
do.

Another one that I find difficult to analyze is a possible out-of-bounds
read in vdbeSorterCompareInt():

 85712 static const u8 aLen[] = {0, 1, 2, 3, 4, 6, 8 };
 85713 int i;
 85714 res = 0;
 85715 for(i=0; i0 && s1<7) || s1==8 || s1==9 );
 85701   assert( (s2>0 && s2<7) || s2==8 || s2==9 );
 85702
 85703   if( s1>7 && s2>7 ){
 85704 res = s1 - s2;
 85705   }else{
 85706 if( s1==s2 ){

At 85715 we know that (s1 <= 7 || s2 <= 7) && s1 == s2, and we also know
that either s1 or s2 can be 8 or 9, so aLen[s1] at 85715 could very well
have s1 > 6, which would read past the bounds of aLen[].

In both of these cases very detailed knowledge of the VDBE being handled
might show that these uninitialized reads do not happen.  If so, I don't
have that knowledge.

I'll hold off on other reports for the time being.

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

Re: [sqlite] Changing ID's to UUID

[R Smith]

On 2016/11/15 10:38 PM, Jens Alfke wrote:

On Nov 15, 2016, at 11:35 AM, Quan Yong Zhai  wrote:

Create a custom function MD5 ,

If you’re going to go to this trouble, at least use SHA256!

MD5 is broken. These days no one should be using it for anything, except when 
needed for compatibility with legacy formats/protocols.


This strikes me as one of those statements that, upon hearing that a 
rocket failed to land upright, cries out: "OMG, this is the end of Space 
exploration as we know it!".


MD5 is not broken. What would broken mean? It still hashes a byte array 
just as good as it always did producing an easy key to check and compare 
against. Sure, for ultimate safety systems you /should/ note that the 
hashes /could/ possibly be brute-forced to, in the minimum case, 
reproduce a duplicate answer for a different byte-list.


So, if you provide public safety documents / programs / systems that may 
provide opportunity for nefarious intent individuals to abuse, please 
step up your hashing, but if you need unique keys for your program that 
keeps kids-party attendance lists, MD5 is as good a method (and as fast) 
as any - in the same way that it is still OK to use INTEGER PRIMARY KEY 
rather than a complete SHA256 primary ID column for your shopping list.


That said, I don't disagree with the notion that, IF the OP is going to 
go through the trouble of making custom functions to accomplish this 
task, might as well go full regalia.


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

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

[Jean-Christophe Deschamps]

At 22:41 15/11/2016, you wrote:

So if you're truly worried about flush-to-disk what do you do 
?  Solution 1 is to buy hard disks rated for servers -- sometimes 
called "enterprise-class hard drives" -- and to set the DIP switches 
to tell them they're being used on a server.  Those things are 
intended for use in servers, and ACID can be important to a server, so 
they support it properly and do not lie.


It's even both best and worse than that.

I enjoy running an old and ugly diy machine with 8 SAS HP disks (15k 
rpm) arranged in RAID6 behind a serious hardware controler having a 
good share of battery-backed RAM. Those enterprise-class disks don't 
have any switch and will lie to you and the OS as much as they can if 
you let them do and don't ask for acknowledgement that each write is 
final, but the key piece is the Areca controler which actually manages 
the RAID array and knows when/if some write has actually landed on 
surface. Should the power supply vanish, it keeps a track of what still 
needs to be written and will silently update the disks when power comes 
back, even before the OS is loaded.


So no, even very expensive hard disks themselves don't take any step to 
avoid lying to you/OS if you/OS don't care over "details", but the 
controler (if a serious one) will fill the gap and insure that 
everything is fine.


To be honest I'm almost certain that there can exist extreme situations 
where, for instance, the RAID array is in tatters (e.g. more than 2 of 
6 disks simultaneously failing) and won't accept writes while the 
controler battery is going dangerously low.
But if your needs are thusly mission critical you probably can afford 
enough redundant sophisticated hardware to make the probability of a 
disaster (e.g. a one-bit flip) as low as required.



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

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

[Bob Friesenhahn]

On Tue, 15 Nov 2016, Simon Slavin wrote:


Modern storage subsystems (hard disk or SSD) intended for use in a 
normal user computer always lie to the OS about flushing to disk. 
The apparent increase in speed from doing this is so big that every 
manufacturer has to do it, or risk having every review harp on about 
how slow their computer performs in real life tasks.  There is no 
way to get these things to be honest.  Because not one person in ten 
thousand cares.  Most people are more interested in how fast they 
can launch Microsoft Word.


I don't think that things are as bad as you say.  Some modern 
filesystems (e.g. zfs) depend on hardware cache flush to work yet 
there has not been a rash of corrupted filesystems.  Many people use 
these filesystems on non-enterprise hardware.


There are some devices which do fail to flush their cache or write 
data properly.  Some SSDs likely re-write data while in use or idle 
due to wear leveling in a way which causes a possibility of loss.


Enterprise disks are more tolerant of vibration, are specified to 
have fewer uncorrected bit errors, and try for a bounded time to 
recover bad sectors.


MacOS's target market is not storage.  The useful mass storage 
offerings for hardware running MacOS is rather limited.


Operating systems where fsync() or fdatasync() do not truely commit 
data to hardware are broken.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

[Simon Slavin]

On 15 Nov 2016, at 8:34pm, Jens Alfke  wrote:

> (Sorry to be frothing at the mouth about this; but my team’s dealing with a 
> few users/customers whose apps encounter db corruption, on Android as well as 
> macOS, and we’re getting really frustrated trying to figure out what’s going 
> on.)

Do these people admit they're letting their phones run out of power ?  If not, 
I suspect some sort of programming error somewhere.  Not necessarily in your 
own programming but sometimes in whatever database API you're using which 
eventually results in calls to the SQLite API.

So is your software in C or C++ and calling the SQLite API directly ?

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

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

[Simon Slavin]

On 15 Nov 2016, at 8:18pm, Jens Alfke  wrote:

> The only way to guarantee a true barrier is to really-and-truly flush the 
> disk controller, which requires not simply flushing but resetting it. That’s 
> what F_FULLFSYNC on macOS does. (Unfortunately it makes the disk controller 
> unresponsive for a macroscopic amount of time while it resets, which is why 
> it’s slow. Worse, it adds latency to _all_ I/O on the system. Flush too often 
> on a desktop computer and your music player will glitch, video playback will 
> drop frames, etc.)

It's worse than that.

Modern storage subsystems (hard disk or SSD) intended for use in a normal user 
computer always lie to the OS about flushing to disk.  The apparent increase in 
speed from doing this is so big that every manufacturer has to do it, or risk 
having every review harp on about how slow their computer performs in real life 
tasks.  There is no way to get these things to be honest.  Because not one 
person in ten thousand cares.  Most people are more interested in how fast they 
can launch Microsoft Word.

So if you're truly worried about flush-to-disk what do you do ?  Solution 1 is 
to buy hard disks rated for servers -- sometimes called "enterprise-class hard 
drives" -- and to set the DIP switches to tell them they're being used on a 
server.  Those things are intended for use in servers, and ACID can be 
important to a server, so they support it properly and do not lie.

Solution 2 works on any hard drive.  To make it flush cache just don't do 
anything that involves disk access for a couple of seconds.  They note the 
inactivity, then decide they have time to write away cache.

But if all the contact you have with the hard disk is through the OS ?  Forget 
it.  If power to the computer is but you're probably depending on the amount of 
power left in the system to flush data to disk.  And given the tiny power 
consumption those things have these days most of the time that's enough.

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

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

[Simon Slavin]

On 15 Nov 2016, at 8:34pm, Jens Alfke  wrote:

> On Nov 15, 2016, at 10:57 AM, Simon Slavin  wrote:
> 
>> sqlite> PRAGMA checkpoint_fullfsync;
>> 1
> 
> I wasn’t aware of that pragma. Just tried it on my Mac (10.12.1), and its 
> value is 1 even if I don’t first set pragma fullfsync; i.e. it defaults to 1. 
> (Contradicting the docs.)
> 
> So it appears that on macOS, SQLite does use F_FULLFSYNC when checkpointing, 
> but not at other times that it fsyncs.

Yet the sequence I posted was from my own Macintosh, running 10.12.[something I 
can't talk about].  And as you see from my post the pragma starts off as 0 but 
if you set it to 1 it'll stay at 1.

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

Re: [sqlite] Changing ID's to UUID

[Scott Robison]

On Tue, Nov 15, 2016 at 1:38 PM, Jens Alfke  wrote:

>
> > On Nov 15, 2016, at 11:35 AM, Quan Yong Zhai  wrote:
> >
> > Create a custom function MD5 ,
>
> If you’re going to go to this trouble, at least use SHA256!
>
> MD5 is broken. These days no one should be using it for anything, except
> when needed for compatibility with legacy formats/protocols.
>

Completely depends on your needs. If your needs are not cryptographic, then
there is no problem. Just like there is no problem using a pseudo random
number generator for non cryptographic purposes.

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

Re: [sqlite] Changing ID's to UUID

[Jens Alfke]

> On Nov 15, 2016, at 11:35 AM, Quan Yong Zhai  wrote:
> 
> Create a custom function MD5 ,

If you’re going to go to this trouble, at least use SHA256!

MD5 is broken. These days no one should be using it for anything, except when 
needed for compatibility with legacy formats/protocols.

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

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

[Jens Alfke]

> On Nov 15, 2016, at 10:57 AM, Simon Slavin  wrote:
> 
> sqlite> PRAGMA checkpoint_fullfsync;
> 1

I wasn’t aware of that pragma. Just tried it on my Mac (10.12.1), and its value 
is 1 even if I don’t first set pragma fullfsync; i.e. it defaults to 1. 
(Contradicting the docs.)

So it appears that on macOS, SQLite does use F_FULLFSYNC when checkpointing, 
but not at other times that it fsyncs. What does that mean in actual use, 
assuming that I always use WAL mode? Is there still an opportunity for 
corruption in the face of power failures?

(Sorry to be frothing at the mouth about this; but my team’s dealing with a few 
users/customers whose apps encounter db corruption, on Android as well as 
macOS, and we’re getting really frustrated trying to figure out what’s going 
on.)

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

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

[Jens Alfke]

> On Nov 15, 2016, at 10:57 AM, Simon Slavin  wrote:
> 
> My understanding is that F_FULLFSYNC still works the way you describe on a 
> Mac and SQLite still uses it the way the documentation says.  But I'm not in 
> touch with either development group.


This seems like a strange decision, considering that in other respects SQLite 
(wisely) defaults to settings that favor safety and durability over 
performance, e.g. synchronous mode.

So perhaps the “How To Corrupt A SQLite Database” page should be updated to add 
“Use SQLite on a Mac without knowing the secret that you should enable `pragma 
fullfsync`?” :-(


Incidentally, that page (https://www.sqlite.org/howtocorrupt.html 
) says:

>> Actually, if one is only concerned with atomic and consistent writes and is 
>> willing to forego durable writes, the sync operation does not need to wait 
>> until the content is completely stored on persistent media. Instead, the 
>> sync operation can be thought of as an I/O barrier. As long as all writes 
>> that occur before the sync are completed before any write that happens after 
>> the sync, no database corruption will occur.

So … based on my understanding (what I was told by a filesystem guru, albeit 10 
years ago), a typical fsync does _not_ act as an I/O barrier. This is because 
it’s not really syncing (as the same page says, "most consumer-grade mass 
storage devices lie about syncing”). Disk controllers don’t write physical 
sectors in the order in which the writes are received; instead they buffer 
them, and then optimize the order of writes to minimize seeks. So if power is 
abruptly lost, it is not true that there’s a barrier before which all writes 
have been persisted and after which none have; instead the pattern is likely to 
be random. 

The only way to guarantee a true barrier is to really-and-truly flush the disk 
controller, which requires not simply flushing but resetting it. That’s what 
F_FULLFSYNC on macOS does. (Unfortunately it makes the disk controller 
unresponsive for a macroscopic amount of time while it resets, which is why 
it’s slow. Worse, it adds latency to _all_ I/O on the system. Flush too often 
on a desktop computer and your music player will glitch, video playback will 
drop frames, etc.)

I’m totally willing to believe that these behaviors are different with SSDs, 
especially ones that use newer types of interfaces or are soldered directly to 
the motherboard (as on some MacBooks.) I’d love to be educated about the 
current state of the art :)

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

Re: [sqlite] Changing ID's to UUID

[Quan Yong Zhai]

Create a custom function MD5 ,

Custum_uuid()  -> MD5(table_name  || Numeric_ID || "salt string" )

Update table tab1 set id= md5('tab1' || '$' || ID || '$' || 'My custom string')


Update table tab1 set ref_id= md5('tab2' || '$' || ref_id || '$' || 'My custom 
string')

发自我的 Windows Phone

发件人: Cecil Westerhof
发送时间: ‎2016/‎11/‎15 16:03
收件人: SQLite mailing list
主题: [sqlite] Changing ID's to UUID

I have several tables where a numeric ID is used. I want to change
those to UUID's. Is there a smart way to do this, or need I to do this
one by one?

--
Cecil Westerhof
___
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] Changing ID's to UUID

[Richard Hipp]

On 11/15/16, Jens Alfke  wrote:
>
> SQLite documentation does not describe which
> random number generator is used; it just calls it “pseudo-random”.

The SQLite PRNG uses RC4 and is seeded from /dev/random (on unix - the
seeding on windows is not as good).  So randomblob() does provide
decent randomness.  I would be hesitant to base a cryptosystem on it,
but it should be plenty adequate for UUIDs.

-- 
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] Database corruption, and PRAGMA fullfsync on macOS

[Simon Slavin]

On 15 Nov 2016, at 6:11pm, Jens Alfke  wrote:

> I verified in the built-in sqlite3 tool on macOS 10.12.1 that the result of 
> `pragma fullfsync` is 0.

The default setting is 0.  But you can change it.  On my Mac running 10.12.x,

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> PRAGMA fullfsync;
0
sqlite> PRAGMA fullfsync=1;
sqlite> PRAGMA fullfsync;
1
sqlite> PRAGMA checkpoint_fullfsync;
1
sqlite> 

My understanding is that F_FULLFSYNC still works the way you describe on a Mac 
and SQLite still uses it the way the documentation says.  But I'm not in touch 
with either development group.

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

Re: [sqlite] Changing ID's to UUID

[Jens Alfke]

> On Nov 15, 2016, at 3:02 AM, Simon Slavin  wrote:
> 
> SQLite has a randomblob function which can be used to select part of the 
> UUID, but you need to pick a UUID scheme suitable for your purposes to know 
> how much of it can be random.

In some use cases it’s important for security to guarantee that a UUID actually 
is unique and can’t be guessed*. In such cases I would be wary of using 
randomblob(), since the SQLite documentation does not describe which random 
number generator is used; it just calls it “pseudo-random”. If you generate the 
UUID yourself you can get the random bits from /dev/random or some other 
high-quality entropy source.

—Jens

* this isn’t just paranoia. There have been real-world vulnerabilities that 
stemmed from insufficiently random IDs.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database corruption, and PRAGMA fullfsync on macOS

[Jens Alfke]

I’m seeing conflicting information about SQLite’s use of F_FULLFSYNC on macOS 
when committing transactions. This is making me nervous about durability and 
the possibility of database corruption.

The SQLite docs for PRAGMA fullfsync 
(https://www.sqlite.org/pragma.html#pragma_fullfsync 
) say:
>  This flag determines whether or not the F_FULLFSYNC syncing method is used 
> on systems that support it. The default value of the fullfsync flag is off. 
> Only Mac OS X supports F_FULLFSYNC.

I verified in the built-in sqlite3 tool on macOS 10.12.1 that the result of 
`pragma fullfsync` is 0.

My understanding is that issuing a F_FULLFSYNC ioctl call is important for 
ensuring that all writes have been flushed to physical storage, since disk 
controllers may lie and ignore a regular sync instruction, leaving some writes 
in their volatile cache memory. (I was working at Apple, on a project using 
SQLite, during the development of OS X 10.4 circa 2006; there were a number of 
databases that got corrupted by kernel panics or forced power-off, until Apple 
had the F_FULLFSYNC call added to SQLite.)

Apple’s documentation for Core Data (which uses SQLite) says: 
(https://developer.apple.com/library/content/documentation/Cocoa/Conceptual/CoreData/PersistentStoreFeatures.html
 
)
> In OS X the fsync command does not guarantee that bytes are written, so 
> SQLite sends a F_FULLFSYNC request to the kernel to ensure that the bytes are 
> actually written through to the drive platter. This request causes the kernel 
> to flush all buffers to the drives and causes the drives to flush their track 
> caches. Without this, there is a significantly large window of time within 
> which data will reside in volatile memory. If system failure occurs you risk 
> data corruption.


This contradicts the SQLite docs, but it does match my understanding, and until 
today I thought that SQLite on macOS enabled fullfsync by default.

So which of these is true?
• F_FULLFSYNC is no longer necessary for full durability (due to some change in 
the way SQLite commits?)
or
• SQLite by default sacrifices durability and data integrity for performance by 
skipping the [slow] F_FULLFSYNC call

This is disturbing because we do have one developer using our framework who has 
been reporting occasional database corruption. He says it’s happened to users, 
and he claims to be able to make it happen by forcibly powering-down his laptop 
(though we haven’t been able to reproduce it.)

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

Re: [sqlite] Changing ID's to UUID

[Simon Slavin]

On 15 Nov 2016, at 12:59pm, Cecil Westerhof  wrote:

>> SQLite has a randomblob function which can be used to select part of the 
>> UUID, but you need to pick a UUID scheme suitable for your purposes to know 
>> how much of it can be random.
> 
> That is something to look into.

Lots of people use UUIDs of this form:



Pick your own value for 'y'.  And even pick your own value for some of the 
substrings, though some considerations of randomness and probability start to 
figure in if you do that.

Frankly I'd generate my random hex digits in my preferred programming language 
rather than have SQLite do it, but it does seem possible to have SQLite do it 
using randomblob().

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

[sqlite] Advanced SQL course

[Cecil Westerhof]

I can follow an advanced SQL course. It is tailored for the Oracle
database which I do not use. I mostly use SQLite. ;-) Would it still
be interesting to follow this course, or would it be a waste of time?

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

Re: [sqlite] Changing ID's to UUID

[Cecil Westerhof]

2016-11-15 12:02 GMT+01:00 Simon Slavin :
>
> On 15 Nov 2016, at 8:03am, Cecil Westerhof  wrote:
>
>> I have several tables where a numeric ID is used. I want to change
>> those to UUID's. Is there a smart way to do this, or need I to do this
>> one by one?
>
> I think you'll have to do it in software.

I thought so, but it never hurts to check.


> SQLite has a randomblob function which can be used to select part of the 
> UUID, but you need to pick a UUID scheme suitable for your purposes to know 
> how much of it can be random.

That is something to look into.

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

Re: [sqlite] CHECK contraint failure doesn't give information on which fields it been failed

[R Smith]

You can name your constraints, so you know which failed.


Examples:

Without named constraint:

  -- SQLite version 3.9.2  [ Release: 2015-11-02 ]  on SQLitespeed 
version 2.0.2.4.
  -- 



CREATE TABLE `a` (
  `b` INTEGER CHECK(typeof(`b`) = 'integer')
);

INSERT INTO a VALUES ('string');

  -- 2016-11-15 14:08:07.779  |  [ERROR]  CHECK constraint failed: a
  -- 





With named constraint:

  -- SQLite version 3.9.2  [ Release: 2015-11-02 ]  on SQLitespeed 
version 2.0.2.4.
  -- 



CREATE TABLE `a` (
  `b` INTEGER,
  CONSTRAINT b_Chk CHECK(typeof(`b`) = 'integer')
);

INSERT INTO a VALUES ('string');

  -- 2016-11-15 14:10:40.535  |  [ERROR]  CHECK constraint failed: 
b_Chk
  -- 






On 2016/11/14 1:49 PM, Api DupCheck wrote:

Hi,

CREATE TABLE `a` (`b` INTEGER CHECK(typeof(`b`) = 'integer'));
INSERT INTO a VALUES ('string');
CHECK constraint failed: a

But it should print:
CHECK constraint failed: a.b

Because if there are a lot fields how do a developer know on which field
CHECK failed?
___
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] Encryption

[Chris Locke]

Ulrich- a fantastically detailed post.

On Mon, Nov 14, 2016 at 3:23 PM, Ulrich Telle  wrote:

> Richard,
>
> > Well what I've done is to create an encrypted database with
> > SQLite2009 and then use that in my C# project. I just add the password to
> > the connection string in my app and then it works right away.
>
> According to the information on the SQLite2009 website (
> http://sqlite2009pro.azurewebsites.net/) SQLite2009 supports 2 encryption
> methods:
>
> ** Encryption Method is now compatible with wxSQLite3 (AES-128 bits) and
> SQLite3 ADO.NET Provider (RSA-MS Crypt) **
>
> I don't know SQLite2009 from own experience, but I assume that it allows
> you to choose which enryption method to use, when creating a new database.
> To be compatible with the ADO.NET provider System.Data.SQLite (
> http://system.data.sqlite.org) you obviously have to choose the
> corresponding encryption method in SQLite2009.
>
> > As far as
> > I understand (I'm new to all this) you can also create a database from
> > within your app if it's based on system.data.sqlite.
> > I just can't edit the table columns or add new ones in SQLite2009 once
> > the database is saved or reopened, I can only edit the record data. I
> > just read that once created, you can't (or only very limited) edit the
> > columns of an SQLite database, you have to create a new database with
> > the desired structure and copy the data over.
>
> I have a bit the impression that you mixed up the terms 'database' and
> 'table'. A 'database' can contain several tables, and adding new tables or
> removing existing tables is simple (and should be supported by any SQLite
> administration tool). However, changing the structure of an existing table
> in SQLite is more complicated, since SQLite only supports a limited set of
> operations to modify a table definition. Therefore, if you want to add or
> remove columns from a table definition, you usually have to create a new
> table with a different name, copy the data from the previous table to the
> new one, remove the previous table, and rename the new table to the
> previous name.
>
> > I think that's what DB Browser for SQLite does since you actually can
> > reopen and edit the columns and their parameters etc. with it.
>
> Under the hood DB Browser for SQLite performs the above mentioned steps
> for you.
>
> > It can also create encrypted databases but the encryption scheme it uses
> > is not supported by system.data.sqlite it seems.
>
> Correct. DB Browser for SQLite supports SQLCipher (
> https://www.zetetic.net/sqlcipher/), an AES-256 encryption scheme.
>
> > But all in all it works fine, I'm just a bit concerned with the RSA
> > encryption in system.data.lite as I've heard it's slow and easy to
> > crack, so I'd prefer something else.
>
> The RSA encryption offered by System.Data.SQLite should not be used, if
> security is a concern for you. You should prefer an AES encryption scheme.
>
> > SQLITE Expert looks interesting but I wonder if it also uses the
> > built-in RSA encryption in system.data.sqlite?
>
> According to the description on the website (http://www.sqliteexpert.com/
> features.html) SQLiteExpert "Supports password protected databases
> (requires third party SQLite library - not included)." That is, you have to
> provide a SQLite3 DLL supporting the encryption scheme of your choice. For
> example,
>
> - SQLCipher (https://github.com/sqlcipher/sqlcipher), you have to build
> the DLL yourself
> - wxSQLite3 (https://github.com/utelle/wxsqlite3/releases), Windows
> binaries are provided
>
> Other SQLite3 management tools that support the wxSQLite3 encryption
> scheme are
>
> - SQLite Maestro (https://www.sqlmaestro.com/en/products/sqlite/maestro/
> about/)
> - wxSQLitePlus (https://github.com/guanlisheng/wxsqliteplus)
>
> > > Richard Andersen wrote:
> > > In DB Browser for SQlite I can edit the table but I'm not sure if the
> > > SQLCipher encryption used here can be made to work with
> > > System.Data.SQlite, or how to do if it can. Does anyone know anything
> > > about this?
>
> In principle, it should be possible to replace the SQLite encryption
> implementation in System.Data.SQLite by the SQLCipher or wxSQLite3
> implementation, although it might not be trivial. The latter should be
> easier to accomplish, since the wxSQLite3 encryption implementation is
> self-contained, while SQLCipher requires the OpenSSL library as well.
>
> Regards,
>
> Ulrich
> ___
> 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] CHECK contraint failure doesn't give information on which fields it been failed

[Api DupCheck]

Hi,

CREATE TABLE `a` (`b` INTEGER CHECK(typeof(`b`) = 'integer'));
INSERT INTO a VALUES ('string');
CHECK constraint failed: a

But it should print:
CHECK constraint failed: a.b

Because if there are a lot fields how do a developer know on which field
CHECK failed?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing ID's to UUID

[Simon Slavin]

On 15 Nov 2016, at 8:03am, Cecil Westerhof  wrote:

> I have several tables where a numeric ID is used. I want to change
> those to UUID's. Is there a smart way to do this, or need I to do this
> one by one?

I think you'll have to do it in software.

SQLite has a randomblob function which can be used to select part of the UUID, 
but you need to pick a UUID scheme suitable for your purposes to know how much 
of it can be random.

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

Re: [sqlite] sqlite3 crashes mysteriously on 3.6.20-1

[pisymbol .]

On Mon, Nov 14, 2016 at 10:51 PM, James K. Lowden 
wrote:

> On Mon, 14 Nov 2016 20:30:57 -0500
> "pisymbol ."  wrote:
>
> > One last thing: This is during initialization and I access the
> > database through that query several times before hitting this crash.
> >
> > I thought it was memory corruption but it always the same line.
>
> Trying to be helpful, even if it doesn't sound like it: That doesn't
> exonerate your code!
>
> Presumably you do something with the results of those several queries.
> Probably what you do is highly deterministic, maybe identical, run upon
> run.  Likely is you're just corrupting memory in the same way each
> time.  Not corrupting as in "writing to random memory", but as in
> "writing in a determistic way to memory you don't mean to".  That the
> error is repeatable suggests it's not related to a race condition, but
> where threading is concerned that's never a culprit to be dismissed.
>
> I would run your code under valgrind first.  If that doesn't find
> anything, trap the segfault in gdb and find the basis for it, even if
> it's deep in the parser.  Some offset/pointer is wrong.  Find out what
> it is, and set a watchpoint on it.  If it's as determistic as you say,
> I'll lay odds gdb will stop at a *very* surprising place, in your
> code.
>
>
I totally agree and this is EXACTLY what I did.Thank you!

I found the bug. Sigh.

A thread was calling sqlite3_free() instead of sqlite3_finalize() on a
prepared statement which was causing an issue.

Anyway, thanks to everyone who gave me tips. Some days

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

[sqlite] Changing ID's to UUID

[Cecil Westerhof]

I have several tables where a numeric ID is used. I want to change
those to UUID's. Is there a smart way to do this, or need I to do this
one by one?

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