Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

[Keith Medcalf]

On Thursday, 20 February, 2020 22:06, Andy KU7T  wrote:

>I admit I do not fully understand all the arguments. I am running on
>Windows. Are you saying the PRNG on Windows is not good enough to use
>randomblob(16) in Sqlite? All I need is a reasonable assurance that is
>are unique...

Yes, it is reasonably random.  To improve the entropy of the seed you should 
compile the amalgamation with -DSQLITE_WIN32_USE_UUID=1 and include RPCRT4.LIB 
in the link.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

[Andy KU7T]

I admit I do not fully understand all the arguments. I am running on Windows. 
Are you saying the PRNG on Windows is not good enough to use randomblob(16) in 
Sqlite? All I need is a reasonable assurance that is are unique...

Andy

Sent from my T-Mobile 4G LTE Device
Get Outlook for Android


From: sqlite-users  on behalf of 
Rowan Worth 
Sent: Thursday, February 20, 2020 7:00:20 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Is randomblob(16) a good guid generation across multiple 
computers?

On Fri, 21 Feb 2020 at 03:59, Jens Alfke  wrote:

> > On Feb 20, 2020, at 10:48 AM, Richard Hipp  wrote:
> >
> > That assumption is not correct for SQLite, which does you a
> > cryptographically strong PRNG.  And the SQLite PRNG is seeded from
> > /dev/random on unix.
>
> Not quite; I'm looking at the function unixRandomness() in SQLite 3.28.
> It's seeded from /dev/urandom, which on Linux "will produce lower quality
> output if the entropy pool drains, while /dev/random will prefer to block
> and wait for additional entropy to be collected." (I'm quoting the macOS
> man page, which goes on to say that on macOS it always returns high-quality
> randomness.)
>

There are a lot of myths in this area, but from what I gather /dev/urandom
is totally fine for cryptographic purposes in modern linux, and any
advantages of /dev/random are highly overstated.

https://www.2uo.de/myths-about-urandom/
 -Rowan
___
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] Performance Issue on Large Table

[Chip Beaulieu]

I have a table with 4.5 million records with full text indexing. Reads are very 
fast, but deleting / inserting  / updating takes on average about 50 seconds 
per record. I often do batches of 30,000 deletes / inserts at a time. The last 
batch took 10 hours to complete.

Here are the details:

Table Schema:

CREATE TABLE `Verses` (
`ID`integer PRIMARY KEY AUTOINCREMENT,
`BibleID`   integer NOT NULL,
`Book`  integer NOT NULL,
`Chapter`   integer NOT NULL,
`VerseNumber`   integer NOT NULL,
`Scripture` text,
`Comments`  text,
FOREIGN KEY(`BibleID`) REFERENCES `Bibles`(`BibleID`) ON DELETE CASCADE,
FOREIGN KEY(`Book`) REFERENCES `Books`(`ID`)
)

Indexes:

CREATE INDEX `INX_Verses_BID` ON `Verses` (`BibleID`)
CREATE INDEX `INX_Verses_BID_B_C` ON `Verses` (`BibleID`,`Book`,`Chapter`)
CREATE INDEX `INX_Verses_BI_B_C_V` ON `Verses` 
(`BibleID`,`Book`,`Chapter`,`VerseNumber`)
CREATE INDEX `INX_Verses_B_C_V` ON `Verses` (`Book`,`Chapter`,`VerseNumber`)

Triggers on table:

CREATE TRIGGER after_verses_delete AFTER DELETE ON Verses 
BEGIN
  —the scripture table is the FTS5 table
DELETE FROM scriptures WHERE ID = old.ID;   —the scripture table is the 
FTS5 table
END

CREATE TRIGGER after_verses_insert AFTER INSERT ON Verses
BEGIN
  —the scripture table is the FTS5 table
  INSERT INTO scriptures (ID,Scripture) VALUES(new.ID, new.Scripture);
END

CREATE TRIGGER after_verses_update UPDATE OF Scripture ON Verses
BEGIN
  —the scripture table is the FTS5 table
  UPDATE scriptures SET Scripture = new.Scripture WHERE ID = old.ID;
END

I run the set of transactions from the command line on a mac using the .read 
command. A common scenario deletes 30,000 records, then inserts a new set of  
30,000 records. Here are the parameters I set up for the transaction in the 
.read file. To be honest, I’ve not seen much of an improvement with these and 
since the batch takes so long to complete, I was concerned tweaking other 
pragma statements that might introduce risk of corruption on crashes or power 
failure.

select time('now','-5 hours');
pragma temp_store=2;  --memory
pragma jouurnal_mode=TRUNCATE; --default is DELETE
pragma locking_mode=EXCLUSIVE; --default is NORMAL
BEGIN TRANSACTION;
DELETE FROM Verses WHERE BibleID=38;
INSERT INTO VERSES (BibleID, Book, Chapter, VerseNumber, Scripture) 
VALUES(38,1,1,1,'·In the beginning God created the heavens and the earth.');

… repeat 30,000 times with other records

COMMIT;
select time('now','-5 hours’);

I also recently vacuumed the file. The issue really showed up after the FTS5 
was setup on the table. I suspect it’s got something to do with the triggers 
more than the indexes. I am definitely open to suggestions. I’ve  not been able 
to find much info on the internet to optimize updates to tables with FTS.

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

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

[Rowan Worth]

On Fri, 21 Feb 2020 at 03:59, Jens Alfke  wrote:

> > On Feb 20, 2020, at 10:48 AM, Richard Hipp  wrote:
> >
> > That assumption is not correct for SQLite, which does you a
> > cryptographically strong PRNG.  And the SQLite PRNG is seeded from
> > /dev/random on unix.
>
> Not quite; I'm looking at the function unixRandomness() in SQLite 3.28.
> It's seeded from /dev/urandom, which on Linux "will produce lower quality
> output if the entropy pool drains, while /dev/random will prefer to block
> and wait for additional entropy to be collected." (I'm quoting the macOS
> man page, which goes on to say that on macOS it always returns high-quality
> randomness.)
>

There are a lot of myths in this area, but from what I gather /dev/urandom
is totally fine for cryptographic purposes in modern linux, and any
advantages of /dev/random are highly overstated.

https://www.2uo.de/myths-about-urandom/
 -Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

[Jens Alfke]

> On Feb 20, 2020, at 10:48 AM, Richard Hipp  wrote:
> 
> That assumption is not correct for SQLite, which does you a
> cryptographically strong PRNG.  And the SQLite PRNG is seeded from
> /dev/random on unix.

Not quite; I'm looking at the function unixRandomness() in SQLite 3.28. It's 
seeded from /dev/urandom, which on Linux "will produce lower quality output if 
the entropy pool drains, while /dev/random will prefer to block and wait for 
additional entropy to be collected." (I'm quoting the macOS man page, which 
goes on to say that on macOS it always returns high-quality randomness.)

I'm a bit doubtful about how the function handles errors, too.

* If /dev/urandom can't be opened, it instead cobbles together some decidedly 
low-entropy bytes from the results of the time() and getpid() system calls. 
IIRC this is very much like the way Netscape seeded its RNG in their first 
implementation of SSL, which turned out to be easily crackable by guessing the 
seed.

* If there's a read error on /dev/urandom, it just returns a buffer full of 
zeros, which is about as non-random as you can get.

Admittedly these are unlikely scenarios, but failure to properly seed an RNG 
can be catastrophic for security. And a lot of security exploits come from 
'unlikely' scenarios that an attacker finds a way to force.

There's a disclaimer about this, actually, inside the source code of 
sqlite3_randomness():

>   /* Initialize the state of the random number generator once,
>   ** the first time this routine is called.  The seed value does
>   ** not need to contain a lot of randomness since we are not
>   ** trying to do secure encryption or anything like that…

That's kind of at odds with your calling it a cryptographically strong PRNG. :(

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

[sqlite] .dump

[Thomas Kurz]

I noticed that the .dump command in the CLI doesn't contain the "user_version" 
and "application_id" fields. I don't know whether this is intentional, but 
would you consider including these values in the output of .dump?

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

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

[Keith Medcalf]

On Thursday, 20 February, 2020 11:48, Richard Hipp  wrote:

>The author of that article, "Raymond", assumes that the random number
>generator in the SQL database engine is not cryptographically strong.

Actaully, what "Raymond" is on about is the fact that the original definition 
of a GUID, according to Microsoft, was what is now called, in standard 
parlance, a Version 1 Variant 2 UUID, only without the Version and Variant 
identifiers.  It used the "clock and sequence" based on the current clock in 
huns and a sequence number to "break ties" in case you tried to have Windows 
generate more than 1 GUID per hun.  The last 48-bits were the "Unique ID" of 
the computer as generated and stored in the registry by Microsoft when Windows 
was installed (with no generated-id flag as provided in the current standard).

It was always stored in little-endian order exclusively, even on big-endian 
processors.  There was no randomness at all.  Not even the slightest.  Ever.  
It was exclusively a (more or less) sequential number.

Several years ago Microsoft decided to replace the version 1 GUID, which 
contained an identifier traceable back to the computer on which it was created, 
with a truly random Version 4 type UUID (though still without a version number 
and still without variant encoding, and still always in little endian format) 
-- apparently Microsoft software loves storing "GUIDs" thither and yon like in 
Word and Excel documents, and Microsoft's Legal Department determined that this 
was a "liability" because the Justice Department would be able to prove which 
computer was used to compose a document, spreadsheet, or email message with 
trivial effort, thus exposing the company and its executives to liability which 
could be avoided by simply using "random" GUIDs rather than "machine specific" 
GUIDs.

A new API was introduced to permit the "old fashioned sequential GUIDs linked 
inexoribly to the computer" to be generated by those that wanted to still use 
them, but the default API changed internally to now providing version 4 UUIDs 
(though still without the standard UUID version and variant flags, and still in 
exclusively little endian byte order).

So it has nothing to do with randomness.  It has to do with the fact that a 
"GUID" contains two parts:  a time stamp in UTC and a sequence number, plus the 
(hopefully) unique ID of the computer generating the GUID.  It is not random.  
It is sequential.  And the "Global Uniqueness" part is determined solely by the 
hopefully "Global Uniqueness" of the machine identifier which created the GUID.

Only later did the "GUID" contain randomness by default though Windows was 
still capable of generating the old sequential GUIDs.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

[Richard Hipp]

On 2/20/20, Keith Medcalf  wrote:
>
> randomblob(16) does not generate a valid UUID (it does not set the version
> and variant flags in the resulting 16-bytes of random data).

If you need a UUID in the "standard format", rather than just an ID
that its universally unique, you can use the uuid.c extension:
https://www.sqlite.org/src/artifact/5bb2264c1b64d163


-- 
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] Is randomblob(16) a good guid generation across multiple computers?

[Richard Hipp]

On 2/20/20, Andy KU7T  wrote:
> Hi,
> I added a randomblob(16) to each record of a Sqlite table via a trigger with
> the goal of global uniqueness. Is that the correct approach or would it be
> better to pass Guid from .Net? I am using System.Data.Sqlite. The following
> article got me questioning the usage of randomblob:
> https://devblogs.microsoft.com/oldnewthing/20120523-00/?p=7553

The author of that article, "Raymond", assumes that the random number
generator in the SQL database engine is not cryptographically strong.
That assumption is not correct for SQLite, which does you a
cryptographically strong PRNG.  And the SQLite PRNG is seeded from
/dev/random on unix.  Now, on Windows systems, the seeding is not
quite as strong as it is on unix (unless you compile with
-DSQLITE_WIN32_USE_UUID) but it is still sufficient to reduce the
changes of a collision between two randomblob(16) calls to practically
zero.

So, I think randomblob(16) is a fine way to generate a UUID.

Though, I tend to use randomblob(20), and I often run it through hex()
too, so that it is human-readable.


-- 
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] Is randomblob(16) a good guid generation across multiple computers?

[Keith Medcalf]

randomblob(16) generates 16 random bytes.

randomblob(16) does not generate a valid UUID (it does not set the version and 
variant flags in the resulting 16-bytes of random data).  If you set the 
version to 4 and the variant to 1 then randomblob(16) does produce valid 
version 4 uuids with 122-bits of randomness.  The ext/misc/uuid.c extension 
does this, for example, generating valid version 4 variant 1 UUIDs.

The only other useful type of UUID to generate would be version 1.  Version 1 
is a pseudo-sequential clock based value in which the last 48-bits are the MAC 
address of the computer (or some fixed identifier of 48-bits for the computer) 
with the variant set appropriately (thus chopping a couple of bits) and the MAC 
type (thus chopping another bit, to identify whether the origin is a "real MAC 
unicast address" or a "fake ID -- multicast MAC address").  The clock and 
sequence is merely the current clock count plus a sequence number of the 
generated UUID.  Less the 4 bit version which is set to 1.

Microsoft does not generate valid UUIDs (either version 1 or version 4).  They 
do not have the version and variant set properly and are stored "little endian" 
rather than in network byte order.  If you pass a "standards based" UUID to a 
"Microsoft" renderer you will get a different result than if you pass the same 
UUID bytes to a standards compliant renderer.

Both version 1 and version 4 UUIDs are probabilisticly Universally Unique.  
Version 1 because the single-source generator uses a theoretically unique 
machine ID, and version 4 because hopefully the random 122-bits are in fact 
122-bits of entropy.

So really it boils down to a question of how you want these UUIDs to be 
represented.  There is the "Microsoft way" and the "Microsoft way" is 
incompatible with the "standard".  So if you choose the "standard" way, then 
you will have to forgo the "Microsoft way" and use only standard compliant 
handlers (and therefore will have standard compliant UUIDs on all platforms).  
Conversely, if you choose the "Microsoft way" then you will be limited to only 
ever being compliant with the "Microsoft way" and limited to Microsoft 
platforms.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Andy KU7T
>Sent: Thursday, 20 February, 2020 11:12
>To: SQLite mailing list 
>Subject: [sqlite] Is randomblob(16) a good guid generation across
>multiple computers?
>
>Hi,
>I added a randomblob(16) to each record of a Sqlite table via a trigger
>with the goal of global uniqueness. Is that the correct approach or would
>it be better to pass Guid from .Net? I am using System.Data.Sqlite. The
>following article got me questioning the usage of randomblob:
>https://devblogs.microsoft.com/oldnewthing/20120523-00/?p=7553
>
>Thanks
>Andy
>
>Sent from my T-Mobile 4G LTE Device
>Get Outlook for Android
>___
>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] Is randomblob(16) a good guid generation across multiple computers?

[Andy KU7T]

Hi,
I added a randomblob(16) to each record of a Sqlite table via a trigger with 
the goal of global uniqueness. Is that the correct approach or would it be 
better to pass Guid from .Net? I am using System.Data.Sqlite. The following 
article got me questioning the usage of randomblob: 
https://devblogs.microsoft.com/oldnewthing/20120523-00/?p=7553

Thanks
Andy

Sent from my T-Mobile 4G LTE Device
Get Outlook for Android
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DRH interview on why/how SQLite succeeded

[sky5walk]

In the ever expanding bloat of tooling, DRH is my hero.

On Wed, Feb 19, 2020 at 2:56 PM Stephen Chrzanowski 
wrote:

> I just finished listening to this.  Really cool.
>
> Thanks for ALL of your hard work SQLite team.  I appreciate it sincerely.
>
>
> On Wed, Feb 19, 2020 at 12:39 PM Simon Slavin 
> wrote:
>
> > 
> >
> > Podcast / transcription of DRH interview:
> >
> > " We talked to Richard about the history of SQLite, where it came from,
> > why it succeeded as a database, how its development is sustainably funded
> > and also how it’s the most widely deployed database engine in the world.
> "
> >
> > Not a formal treatment, but you can piece together what's said and get a
> > lot of information on how SQLite developed into what it is today, who is
> in
> > the development team, and how it is funded through the SQLite Consortium.
> > Plus random anecdotes about computing over the last 40 years.
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] rtrim and round functions unexpected result

[Jose Isaias Cabrera]

Ah, so rtrim(X,Y) removes all characters in the Y slot; NOT the string Y.  
Apologies.  I thought that it was the string that it removed.  Ok, replace it 
is, then.



From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Thursday, February 20, 2020 11:09 AM
To: SQLite mailing list 
Subject: Re: [sqlite] [EXTERNAL] rtrim and round functions unexpected result

Round(1299.6) returns the floating point number 1300.0,
passing 1300.0 to the rtrim function converts it tot he string '1300.0'
removing all '.' and '0' characters from '1300.0' yields 13
This is no suprise

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jose Isaias Cabrera
Gesendet: Donnerstag, 20. Februar 2020 17:03
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] rtrim and round functions unexpected result


Greetings.

Please take a look at the following:
sqlite> select rtrim(round(1235.6));
1236.0
This is expected.
sqlite> select rtrim(round(1235.6),'.0');
1236
Also expected.
sqlite> select rtrim(round(1299.6),'.0');
13
is not expected.  I was hoping for 1300.  Also, just rtrim,
sqlite> select rtrim('1000.0','.0');
1
sqlite> select rtrim('1000.0','0');
1000.

I know I can use replace for this,

sqlite> select replace('1.0','.0','');
1

but I wanted to see if there was an explanation for it. By the way, escaping 
the period (.) or dot also fails.

sqlite> select rtrim('1000.0','\.0');
1

Thanks.

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] rtrim and round functions unexpected result

[Hick Gunter]

Round(1299.6) returns the floating point number 1300.0,
passing 1300.0 to the rtrim function converts it tot he string '1300.0'
removing all '.' and '0' characters from '1300.0' yields 13
This is no suprise

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jose Isaias Cabrera
Gesendet: Donnerstag, 20. Februar 2020 17:03
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] rtrim and round functions unexpected result


Greetings.

Please take a look at the following:
sqlite> select rtrim(round(1235.6));
1236.0
This is expected.
sqlite> select rtrim(round(1235.6),'.0');
1236
Also expected.
sqlite> select rtrim(round(1299.6),'.0');
13
is not expected.  I was hoping for 1300.  Also, just rtrim,
sqlite> select rtrim('1000.0','.0');
1
sqlite> select rtrim('1000.0','0');
1000.

I know I can use replace for this,

sqlite> select replace('1.0','.0','');
1

but I wanted to see if there was an explanation for it. By the way, escaping 
the period (.) or dot also fails.

sqlite> select rtrim('1000.0','\.0');
1

Thanks.

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] rtrim and round functions unexpected result

[Jose Isaias Cabrera]

Greetings.

Please take a look at the following:
sqlite> select rtrim(round(1235.6));
1236.0
This is expected.
sqlite> select rtrim(round(1235.6),'.0');
1236
Also expected.
sqlite> select rtrim(round(1299.6),'.0');
13
is not expected.  I was hoping for 1300.  Also, just rtrim,
sqlite> select rtrim('1000.0','.0');
1
sqlite> select rtrim('1000.0','0');
1000.

I know I can use replace for this,

sqlite> select replace('1.0','.0','');
1

but I wanted to see if there was an explanation for it. By the way, escaping 
the period (.) or dot also fails.

sqlite> select rtrim('1000.0','\.0');
1

Thanks.

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

Re: [sqlite] question about INTEGER PRIMARY KEY AUTOINCREMENT

[Keith Medcalf]

On Wednesday, 19 February, 2020 21:24, ethan he  wrote:

>There is a SQLITE DATABASE has “MeslocallD”(INTEGER PRIMARY KEY
>AUTOINCREMENT),
>Is that possible to delete the data but still keep the MeslocallD
>consistence?

Assuming that by "consistence" you mean the high-water mark for inserted rowid 
then the answer is yes.  The high water mark rowid used for a table is stored, 
for each autoincrement rowid table, in a special table called sqlite_sequence.

If you delete all the data from the table, the sqlite_sequence will not be 
changed.  However, if you drop the table, the entry for that table will be 
removed from sqlite_sequence.  If you will be dropping the table and need to 
"remember" the high-water mark, you can select it from the sqlite_sequence 
table and re-insert it after re-creating the table.

sqlite> create table x(x integer primary key autoincrement);
sqlite> insert into x values (1);
sqlite> select * from sqlite_sequence;
x|1
sqlite> drop table x;
sqlite> select * from sqlite_sequence;
sqlite> create table x (x integer primary key autoincrement);
sqlite> select * from sqlite_sequence;
sqlite> insert into sqlite_sequence values ('x', 1);
sqlite> insert into x values (null);
sqlite> select * from x;
2
sqlite> select * from sqlite_sequence;
x|2

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] [EXTERNAL] question about INTEGER PRIMARY KEY AUTOINCREMENT

[Hick Gunter]

The next value for an INTEGER PRIMARY KEY AUTOINCREMENT does not depend on the 
current contents of the table, only its history. While ROWIDs are monotnically 
increasing, there may be gaps in the sequence, caused by rows that failed to 
insert due to constraint violations. However, ROWIDs that get rolled back will 
be reused.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von ethan he
Gesendet: Donnerstag, 20. Februar 2020 05:24
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] question about INTEGER PRIMARY KEY AUTOINCREMENT

Hi,

There is a SQLITE DATABASE has “MeslocallD”(INTEGER PRIMARY KEY AUTOINCREMENT), 
Is that possible to delete the data but still keep the  MeslocallD consistence?

Thanks for your help


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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] question about INTEGER PRIMARY KEY AUTOINCREMENT

[ethan he]

Hi,

There is a SQLITE DATABASE has “MeslocallD”(INTEGER PRIMARY KEY AUTOINCREMENT),
Is that possible to delete the data but still keep the  MeslocallD consistence?

Thanks for your help


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