Re: [sqlite] Hook / Trigger for Primary Key / Unique Error

[Petite Abeille]

On Dec 30, 2013, at 1:48 PM, Richard Hipp  wrote:

> One example:
> 
>   sqlite3_exec("INSERT OR IGNORE INTO ;");
>   if( sqlite3_changes()==0 ){
> sqlite3_exec("UPDATE ...");
>   }

This is rather error prone and may results in, well, unexpected results.

How to lose your data in two easy steps:

(1) IGNORE is triggers by a constraint other than unique resulting in 
sqlite3_changes()==0.
(2) The UPDATE silently updates, well, nothing at all.
(3) There is no step 3, nor any data.

I would personally not rely on such construct.

What SQLite needs is a proper MERGE statement.

 

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

Re: [sqlite] Hook / Trigger for Primary Key / Unique Error

[Raheel Gupta]

SCHEMA :
CREATE TABLE checksums
(i INTEGER,c VARCHAR(20) PRIMARY KEY) ;

CODE :

// Generates a Random string
QByteArray Randstr_B(int len) {
char chars[36] = {'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j',
'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y',
'z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9'};
QByteArray str;
for (int i = 0; i < len; i++) {
str.append(chars[qrand() % 35]);
}
return str;
}

// Converts  quint64 to Little Endian bytes
QByteArray toLE(quint64 num){
unsigned char bytes[8];
bytes[0] = (unsigned char)num;
bytes[1] = (unsigned char)(num >> 8);
bytes[2] = (unsigned char)(num >> 16);
bytes[3] = (unsigned char)(num >> 24);
bytes[4] = (unsigned char)(num >> 32);
bytes[5] = (unsigned char)(num >> 40);
bytes[6] = (unsigned char)(num >> 48);
bytes[7] = (unsigned char)(num >> 56);

return QByteArray((const char *)bytes, 8);
}

insQ->prepare("INSERT INTO checksums (i, c) VALUES (?, ?)");

I then do inserts in the loop (I am using QT) :
for(quint64 i = 1; i <= 1000; i++){
// Create the data RANDOMLY
QByteArray block = Randstr_B(4088).append(toLE(i)); // "toLE" is a
function which converts quint64 to Little Endian Bytes i.e. 8 bytes. I used
this to make each block unique
QByteArray csum = QCryptographicHash::hash(block,
QCryptographicHash::Sha1);
unsigned char C = (unsigned char)csum.at(0);

// Insert into the MAP
insQ->bindValue(0, i); // insQ is a QSqlQuery *
insQ->bindValue(1, csum);
if(!insQ->exec()){
qDebug() << "Error Inserting :" << i << csum.toHex().toUpper()
<< insQ->lastError().text();
//exit(0);
}
if((i % 1) == 0){
mapconn.commit(); // Database Connection "mapconn"
Q.exec("PRAGMA wal_checkpoint;"); // Checkpointing the WAL and
"Q" is also another QSqlQuery.
mapconn.transaction(); // Restarting Transaction
qDebug() "Committed :" << i << csum.toHex() << time();
}
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hook / Trigger for Primary Key / Unique Error

[Richard Hipp]

On Mon, Dec 30, 2013 at 8:56 AM, Raheel Gupta  wrote:

> Sir, I tested the method of the following :
> sqlite3_exec("INSERT OR IGNORE INTO ;");
> if( sqlite3_changes()==0 ){
> sqlite3_exec("UPDATE ...");
> }
> I have seen my performance degrade from 1 records / second to 1
> records / 10 seconds after 3 million UNIQUE Checksums inserted. Am I doing
> anything wrong ? BTW, there were only 10 CONFLICTS in 3 Million rows. So
> they are all unique 20 Byte Sha1 Checksums. I am committing at 1
> inserts.
>

Why don't you provide us with a test case of some kind, or at least your
specific schema and the SQL you are running?


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hook / Trigger for Primary Key / Unique Error

[Raheel Gupta]

Sir, I tested the method of the following :
sqlite3_exec("INSERT OR IGNORE INTO ;");
if( sqlite3_changes()==0 ){
sqlite3_exec("UPDATE ...");
}
I have seen my performance degrade from 1 records / second to 1
records / 10 seconds after 3 million UNIQUE Checksums inserted. Am I doing
anything wrong ? BTW, there were only 10 CONFLICTS in 3 Million rows. So
they are all unique 20 Byte Sha1 Checksums. I am committing at 1
inserts.


On Mon, Dec 30, 2013 at 6:38 PM, Richard Hipp  wrote:

> On Mon, Dec 30, 2013 at 7:48 AM, Richard Hipp  wrote:
>
> >
> > The wordcount.c test program does the above using prepared statements and
> > exceeds 348,000 inserts+updates per second on my desktop
> >
>
> Further information:  My desktop is a relatively new and fast SSD machine.
> A 3-year-old ubuntu machine with spinning disks did over 250,000
> inserts+updates per second and a beagleboard black (
> http://beagleboard.org/products/beaglebone%20black) did just shy of 20,000
> inserts+updates per second on the same test case of 791319 insert+updates.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hook / Trigger for Primary Key / Unique Error

[Richard Hipp]

On Mon, Dec 30, 2013 at 7:48 AM, Richard Hipp  wrote:

>
> The wordcount.c test program does the above using prepared statements and
> exceeds 348,000 inserts+updates per second on my desktop
>

Further information:  My desktop is a relatively new and fast SSD machine.
A 3-year-old ubuntu machine with spinning disks did over 250,000
inserts+updates per second and a beagleboard black (
http://beagleboard.org/products/beaglebone%20black) did just shy of 20,000
inserts+updates per second on the same test case of 791319 insert+updates.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hook / Trigger for Primary Key / Unique Error

[Richard Hipp]

On Mon, Dec 30, 2013 at 5:24 AM, Raheel Gupta  wrote:

> Hi,
>
> I have the following tables :
> CREATE TABLE checksums
> (i INTEGER,c VARCHAR(16) PRIMARY KEY) ;
>
> CREATE TABLE data
> (i INTEGER, data BLOB, isUnique INTEGER)
>
> My application needs to insert 1000s of rows/second into the data table
> hence I use transactions and prepare statements.
>
> Now is it possible that while inserting (using transactions) in the
> Checksums table when there is a CONFLICT as per the column "c" I update
> table "data" column isUnique with the corresponding ID "i" from table
> checksums.
>
> Something like :
> INSERT INTO checksums (1000, "abcdefghijklmnop") ON CONFLICT IGNORE BUT
> UPDATE data SET isUnique = OLD.checksums.i
>

The wordcount.c test program (see
http://www.sqlite.org/src/artifact/9915e06cb) demonstrates four different
ways to do this.  One example:

   sqlite3_exec("INSERT OR IGNORE INTO ;");
   if( sqlite3_changes()==0 ){
 sqlite3_exec("UPDATE ...");
   }

The wordcount.c test program does the above using prepared statements and
exceeds 348,000 inserts+updates per second on my desktop when compiled
using GCC version 4.8.1 and -Os. See
http://www.sqlite.org/src/artifact/9915e06cb?ln=432-445 for the inner loop.



>
> I know the above ON CONFLICT statement is wrong but I need a similar
> functionality.
>
> Is this possible with HOOKS / triggers or anything at all ?
> This will be a great feature if its there.
>
> Regards
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hook / Trigger for Primary Key / Unique Error

[Clemens Ladisch]

Raheel Gupta wrote:
>INSERT INTO checksums (1000, "abcdefghijklmnop") ON CONFLICT IGNORE BUT
>UPDATE data SET isUnique = OLD.checksums.i
>
>Is this possible with HOOKS / triggers or anything at all ?

It is possible with the obvious and simple solution: check for
uniqueness with a SELECT, then execute either INSERT or
UPDATE as needed.

Is there any reason that you do not want to use this?


Regards,
Clemens

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

[sqlite] Hook / Trigger for Primary Key / Unique Error

[Raheel Gupta]

Hi,

I have the following tables :
CREATE TABLE checksums
(i INTEGER,c VARCHAR(16) PRIMARY KEY) ;

CREATE TABLE data
(i INTEGER, data BLOB, isUnique INTEGER)

My application needs to insert 1000s of rows/second into the data table
hence I use transactions and prepare statements.

Now is it possible that while inserting (using transactions) in the
Checksums table when there is a CONFLICT as per the column "c" I update
table "data" column isUnique with the corresponding ID "i" from table
checksums.

Something like :
INSERT INTO checksums (1000, "abcdefghijklmnop") ON CONFLICT IGNORE BUT
UPDATE data SET isUnique = OLD.checksums.i

I know the above ON CONFLICT statement is wrong but I need a similar
functionality.

Is this possible with HOOKS / triggers or anything at all ?
This will be a great feature if its there.

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