Re: [sqlite] Changing ID's to UUID

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

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

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

Re: [sqlite] Changing ID's to UUID

> 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

Re: [sqlite] Encryption

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

Re: [sqlite] Changing ID's to UUID

> 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

Re: [sqlite] Advanced SQL course

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

Re: [sqlite] Changing ID's to UUID

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.

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

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

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

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

[sqlite] SQLite as a Shell Script

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:

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

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

Re: [sqlite] Changing ID's to UUID

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

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

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

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

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

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

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

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

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

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

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

Re: [sqlite] Changing ID's to UUID

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

Re: [sqlite] Changing ID's to UUID

> 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

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

> 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

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

> 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

Re: [sqlite] Changing ID's to UUID

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

Re: [sqlite] Changing ID's to UUID

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

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

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

Re: [sqlite] Changing ID's to UUID

> 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

[sqlite] Database corruption, and PRAGMA fullfsync on macOS

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

Re: [sqlite] Changing ID's to UUID

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

[sqlite] Advanced SQL course

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

Re: [sqlite] Changing ID's to UUID

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

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

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

Re: [sqlite] Encryption

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

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

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?

Re: [sqlite] Changing ID's to UUID

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

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

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

[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