Re: [sqlite] Issue with updating database content (C++)

[Simon Slavin]

On 31 Aug 2017, at 12:36am, Ali Dorri  wrote:

> Great, thanks for your help. I will have a look at that.

See also this page to understand more about SQLite’s database file format:



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

Re: [sqlite] Issue with updating database content (C++)

[Ali Dorri]

Great, thanks for your help. I will have a look at that.


On Thu, Aug 31, 2017 at 9:28 AM, Simon Slavin  wrote:

>
>
> On 31 Aug 2017, at 12:10am, Ali Dorri  wrote:
>
> > It works. I think I should a way to calculate this reduction in the size
> of
> > the database as sometimes it seems there is no difference in size (after
> > VACUUM) between removing one row or two or three.
> > This is a research work so I need it to show me exactly how much data is
> > removed.
>
> SQLite databases are stored as pages of a certain length.  Each table,
> each index, is a sequence of pages.  You can delete some data but unless
> you free up an entire page, the file won’t get shorter.  You can find out
> how long a page is using this command
>
> PRAGMA page_size
>
> documented here:
>
> 
>
> Simon.
> ___
> 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] Issue with updating database content (C++)

[Simon Slavin]

On 31 Aug 2017, at 12:10am, Ali Dorri  wrote:

> It works. I think I should a way to calculate this reduction in the size of
> the database as sometimes it seems there is no difference in size (after
> VACUUM) between removing one row or two or three.
> This is a research work so I need it to show me exactly how much data is
> removed.

SQLite databases are stored as pages of a certain length.  Each table, each 
index, is a sequence of pages.  You can delete some data but unless you free up 
an entire page, the file won’t get shorter.  You can find out how long a page 
is using this command

PRAGMA page_size

documented here:



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

Re: [sqlite] Issue with updating database content (C++)

[Ali Dorri]

Thanks all,

It works. I think I should a way to calculate this reduction in the size of
the database as sometimes it seems there is no difference in size (after
VACUUM) between removing one row or two or three.
This is a research work so I need it to show me exactly how much data is
removed.

Any help would be much appreciated.

Regards
Ali

On Thu, Aug 31, 2017 at 1:56 AM, Dominique Devienne 
wrote:

> On Wed, Aug 30, 2017 at 5:48 PM, Jens Alfke  wrote:
>
> > > On Aug 29, 2017, at 6:22 PM, Ali Dorri  wrote:
> > >
> > > *char* *zSQL = *sqlite3_mprintf*("UPDATE BC set Signature = null  and
> PK
> > =
> > > null   where PK = '%q' ;", endoced_pub.c_str());
> >
> > FYI, your PK values are not being stored as blobs, rather as hex-encoded
> > strings. Maybe not a big deal since they're not very large; but f you do
> > want to store them as blobs, there's a special prefix before a string
> > literal (an "x"? Can't recall) that makes it a hex-encoded blob.
>
>
> it's x'abcd01'. But much better yet, don't use literal SQL and printf, you
> proper binding [1].
> you'll save yourself from SQL injections, and get better performance too.
> --DD
>
> [1] https://sqlite.org/c3ref/bind_blob.html
> ___
> 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] Issue with updating database content (C++)

[Dominique Devienne]

On Wed, Aug 30, 2017 at 5:48 PM, Jens Alfke  wrote:

> > On Aug 29, 2017, at 6:22 PM, Ali Dorri  wrote:
> >
> > *char* *zSQL = *sqlite3_mprintf*("UPDATE BC set Signature = null  and PK
> =
> > null   where PK = '%q' ;", endoced_pub.c_str());
>
> FYI, your PK values are not being stored as blobs, rather as hex-encoded
> strings. Maybe not a big deal since they're not very large; but f you do
> want to store them as blobs, there's a special prefix before a string
> literal (an "x"? Can't recall) that makes it a hex-encoded blob.


it's x'abcd01'. But much better yet, don't use literal SQL and printf, you
proper binding [1].
you'll save yourself from SQL injections, and get better performance too.
--DD

[1] https://sqlite.org/c3ref/bind_blob.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with updating database content (C++)

[Jens Alfke]

> On Aug 29, 2017, at 6:22 PM, Ali Dorri  wrote:
> 
> *char* *zSQL = *sqlite3_mprintf*("UPDATE BC set Signature = null  and PK =
> null   where PK = '%q' ;", endoced_pub.c_str());

FYI, your PK values are not being stored as blobs, rather as hex-encoded 
strings. Maybe not a big deal since they're not very large; but f you do want 
to store them as blobs, there's a special prefix before a string literal (an 
"x"? Can't recall) that makes it a hex-encoded blob.

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

Re: [sqlite] Issue with updating database content (C++)

[Simon Slavin]

On 30 Aug 2017, at 12:19pm, Ali Dorri  wrote:

> Thanks, now it works and removes all except for the PK. How can I remove
> the PK then? i.e. what is the correct way of doing the following?
>   UPDATE BC set Signature = null ,PK = null   where PK = '%q' ;

That syntax is correct.  In your previous example you were using "AND" where 
you should have had a comma.

> Another issue I have is that when I remove these entries, the size of the
> database does not decrease. 

The above command does not delete a row.  It overwrites a couple of fields in 
the row with nulls.  Which will cause confusion because one of those fields is 
your primary key and it won’t work.

To delete a row, which is probably what you should be doing, use the DELETE 
command, e.g.

DELETE FROM BS WHERE PK = '%q';

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

Re: [sqlite] Issue with updating database content (C++)

[R Smith]

Clarification:

When I said:




You can use this SQL:

VACUUM;

to get rid of empty space in the DB file and re-pack it correctly.


by "correctly" I really meant "tightly". There is nothing incorrect 
about the data before the vacuum, of course.



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

Re: [sqlite] Issue with updating database content (C++)

[R Smith]

Perhaps:

DELETE FROM BC WHERE PK = '%q';


Also note, the DB size may or may not decrease when deleting, it clears 
data, not space.


You can use this SQL:

VACUUM;

to get rid of empty space in the DB file and re-pack it correctly.



On 2017/08/30 1:19 PM, Ali Dorri wrote:

Hi,

Thanks, now it works and removes all except for the PK. How can I remove
the PK then? i.e. what is the correct way of doing the following?
UPDATE BC set Signature = null ,PK = null   where PK = '%q' ;

Another issue I have is that when I remove these entries, the size of the
database does not decrease. I do the VACUUM after the program, but it does
not work and the size of the database does not decrease while the data are
removed.
Can anyone help me in this regard?

Thanks

On Wed, Aug 30, 2017 at 8:49 PM, Clemens Ladisch  wrote:


Ali Dorri wrote:

I encode the PKs to base64
[...]
The PK is a BLOB type, i.e.,
sql = "CREATE TABLE BC("  \
 "PKBLOB," \

Why do you store a text value in a blob field?


"UPDATE BC set Signature = null  and PK = null   where PK = '%q' ;

That does not update the PK column.

UPDATE BC set Signature = null ,PK = null   where PK = '%q' ;
   

Regards,
Clemens
___
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] Issue with updating database content (C++)

[Andy Ling]

You're not deleting any rows, you're just changing the value of the data in the 
row.

What you probably want is something like

DELETE FROM BC WHERE PK = '%q';

HTH

Andy Ling


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Ali Dorri
Sent: Wed 30 August 2017 12:19
To: SQLite mailing list
Subject: Re: [sqlite] Issue with updating database content (C++)

Hi,

Thanks, now it works and removes all except for the PK. How can I remove
the PK then? i.e. what is the correct way of doing the following?
   UPDATE BC set Signature = null ,PK = null   where PK = '%q' ;

Another issue I have is that when I remove these entries, the size of the
database does not decrease. I do the VACUUM after the program, but it does
not work and the size of the database does not decrease while the data are
removed.
Can anyone help me in this regard?

Thanks

On Wed, Aug 30, 2017 at 8:49 PM, Clemens Ladisch <clem...@ladisch.de> wrote:

> Ali Dorri wrote:
> > I encode the PKs to base64
> > [...]
> > The PK is a BLOB type, i.e.,
> >sql = "CREATE TABLE BC("  \
> > "PKBLOB," \
>
> Why do you store a text value in a blob field?
>
> > "UPDATE BC set Signature = null  and PK = null   where PK = '%q' ;
>
> That does not update the PK column.
>
>UPDATE BC set Signature = null ,PK = null   where PK = '%q' ;
>   
>
> Regards,
> Clemens
> ___
> 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
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Issue with updating database content (C++)

[Ali Dorri]

Hi,

Thanks, now it works and removes all except for the PK. How can I remove
the PK then? i.e. what is the correct way of doing the following?
   UPDATE BC set Signature = null ,PK = null   where PK = '%q' ;

Another issue I have is that when I remove these entries, the size of the
database does not decrease. I do the VACUUM after the program, but it does
not work and the size of the database does not decrease while the data are
removed.
Can anyone help me in this regard?

Thanks

On Wed, Aug 30, 2017 at 8:49 PM, Clemens Ladisch  wrote:

> Ali Dorri wrote:
> > I encode the PKs to base64
> > [...]
> > The PK is a BLOB type, i.e.,
> >sql = "CREATE TABLE BC("  \
> > "PKBLOB," \
>
> Why do you store a text value in a blob field?
>
> > "UPDATE BC set Signature = null  and PK = null   where PK = '%q' ;
>
> That does not update the PK column.
>
>UPDATE BC set Signature = null ,PK = null   where PK = '%q' ;
>   
>
> Regards,
> Clemens
> ___
> 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] Issue with updating database content (C++)

[Clemens Ladisch]

Ali Dorri wrote:
> I encode the PKs to base64
> [...]
> The PK is a BLOB type, i.e.,
>sql = "CREATE TABLE BC("  \
> "PKBLOB," \

Why do you store a text value in a blob field?

> "UPDATE BC set Signature = null  and PK = null   where PK = '%q' ;

That does not update the PK column.

   UPDATE BC set Signature = null ,PK = null   where PK = '%q' ;
  

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

[sqlite] Issue with updating database content (C++)

[Ali Dorri]

Dear All,

I am using sqlite to store public key (PK), signature (Sig) and hashes
generated by crypto++ library in a C++ program. I encode the PKs to base64
strings and then store them in the database. Later in my program, I want to
update records of data by searching based on the PK, i.e.

*char* *zSQL = *sqlite3_mprintf*("UPDATE BC set Signature = null  and PK =
null   where PK = '%q' ;", endoced_pub.c_str());

 *sqlite3_prepare_v2*(db, zSQL, -1, , NULL);

where BC is the name of my table and endoced_pub is the string containing
the encoded PK. But, it does not find any match thus it does nothing :(

I connect to the database and see the PK values. They exactly match with
the PKs printed in the main program. Thus it should work.

The only thing that comes to my mind is that when the database stores the
PK , it does some other encodings on that, and thus it does not match with
the key I send in update request. The PK is a BLOB type, i.e.,

   sql = "CREATE TABLE BC("  \

"T_ID  TEXT," \

"P_T_ID   TEXT   ," \

"PKBLOB," \

"SignatureBLOB ," \

"Block_ID TEXT  );";

I also used the prepare command to ensure that my command is encoded based
on sqlite but it does not work.

sqlite3_stmt *stmt;


  *char* *zSQL = *sqlite3_mprintf*("UPDATE BC set Signature = null  and PK
= null   where PK = '%q' ;", endoced_pub.c_str());

  *sqlite3_prepare_v2*(db, zSQL, -1, , NULL);

  rc = *sqlite3_step*(stmt);


Here is how I store the PKs initially in database:

*char* *zSQL = *sqlite3_mprintf*("INSERT INTO BC ( T_ID , P_T_ID , PK ,
Signature , Block_ID ) VALUES ('%q','%q','%q','%q','%q');", BC.trans[l].
TransactionID.c_str() ,BC.trans[l].previousTransactionID.c_str() ,BC.trans
[l].PK.c_str() ,BC.trans[l].Sign.c_str(),BC.block_hash.c_str());


Any thoughts on how can I solve the issue?


Regards

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