Re: [sqlite] A sqlite c++ wrapper, sqlite3x

[ayck2008]

Thank you for your reply, I will contact the author. - 原文 - 发件人: Truls 
Haaland 主　题: Re: [sqlite] A sqlite c++ wrapper, sqlite3x时　间: 2012年2月7日  
14:53:36 Hello everyone, My project used sqlite3x, a 
sqlite c++ wrapper. Now for some reason, I wanna upgrade sqlite3 to version 
3.7.10. I search the Internet hours, but I don't find the sqlite3x 
that support 3.7. Have someone used sqlite3x? I need some advice.sqlite3x web 
site is at: http://wanderinghorse.net/computing/sqlite/The author can be 
contacted at: http://wanderinghorse.net/home/stephan/Send him an e-mail and see 
if he can 
help.Truls.___sqlite-users mailing 
listsqlite-users@sqlite.orghttp://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] A sqlite c++ wrapper, sqlite3x

[Truls Haaland]

> Hello everyone,    My project used sqlite3x, a sqlite c++ wrapper. Now for 
> some reason, I wanna upgrade sqlite3 to version 3.7.10.
>  I search the Internet hours, but I don't find the sqlite3x that support 3.7. 
>Have someone used sqlite3x? I need some advice.

sqlite3x web site is at: http://wanderinghorse.net/computing/sqlite/

The author can be contacted at: http://wanderinghorse.net/home/stephan/

Send him an e-mail and see if he can help.

Truls.

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

[sqlite] A sqlite c++ wrapper, sqlite3x

[ayck2008]

Hello everyone, My project used sqlite3x, a sqlite c++ wrapper. Now for some 
reason, I wanna upgrade sqlite3 to version 3.7.10. I search the Internet hours, 
but I don't find the sqlite3x that support 3.7. Have someone used sqlite3x? I 
need some advice.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About new ticket "Per page/region checksums"

[Simon Slavin]

On 6 Feb 2012, at 11:31pm, Roger Binns wrote:

> [nicely rebuffed my earlier post]

Okay, you convinced me.

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

[sqlite] Why does an RTREE leave its statement handles open after access?

[Peter Aronson]

I added some code before my call to sqlite3_close recently, like so:


hstmt = NULL;
while (hstmt = sqlite3_next_stmt (db,hstmt)) {
  sql = sqlite3_sql (hstmt);
  if (!sql) sql = "-"
  fprintf (log,"Unfinalized statement handle found on database close for 
[%s]\n",sql);
  sqlite3_finalize (hstmt);
}

This was really just in case code.  And to my surprise, I found that the 9 
statements that rtreeSqlInit (called by rtreeInit called by rtree's xConnect) 
prepares hadn't been finailized.  On the other hand, the statement handle used 
by my own virtual table module (used in the same query) was finalized.  Looking 
at the code, it seems that rtree uses a busy count (set to 1 by xConnect or 
xCreate, incremented by xUpdate and xFilter and decremented by xDisconnect or 
xDestroy), and until it hits 0, the statement handles are not finalized.  When 
I 
traced the behavior in the debugger, I saw the statements get finalized swhen 
qlite3_close was called.  Is this the expected behavior?  I could see it as an 
optimization, although I don't see equivalent logic in the FTS code.

Best regards,

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

Re: [sqlite] About new ticket "Per page/region checksums"

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/02/12 14:38, Simon Slavin wrote:
> File systems (FAT, NTFS, HTFS) already have block checksums.

Huh?  FAT has a checksum on the super block but not on datablocks.  NTFS
does not have block checksums.  That is why they are introducing ReFS.
What evidence do you have for your claims - I couldn't find any?

Some file formats do have checksums (eg zip and exe) but that isn't relevant.

There are some checksums but not particularly strong between drive
platters, firmware, drive controllers, and the host.  Quite simply if
existing systems had working checksums then the only times people would
get SQLITE_CORRUPT would be SQLite bugs.

> Problem is, what would you do if a checksum was wrong ?  Signal it in a
> newly-invented result code ?

Return SQLITE_CORRUPT -  a code that has existed forever.

> All apps would have to have some logic to cope with the situation.

You mean like they already have to do?  In fact something that is even
mentioned in the FAQ:

  http://www.sqlite.org/faq.html#q21

> I don't think it's worth doing this in a thin/light/fast system like
> SQLite.

Then don't use it.  Just because you use systems with perfect data
integrity doesn't mean the rest of us do.

> And the number of bugs that cause corruption that would be spotted this
> way seems to be low.

http://search.gmane.org/search.php?group=gmane.comp.db.sqlite.general=SQLITE_CORRUPT

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8wYsQACgkQmOOfHg372QRLtgCgxdtewXE/45VSF0pX80wGApm/
s5kAmwawvPSLPAjtRpCv5vUrS3/DUmJX
=xcIk
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About new ticket "Per page/region checksums"

[Nico Williams]

On Mon, Feb 6, 2012 at 4:38 PM, Simon Slavin  wrote:
> On 6 Feb 2012, at 9:49pm, Nico Williams wrote:
>> Encryption is not enough.  You really need block pointers to carry the
>> block checksum/hash/MAC/integrity tag.
>
> File systems (FAT, NTFS, HTFS) already have block checksums.  So do network 
> file systems (AFP, SMB, NFS).  Adding more to SQLite, whether at a row or 
> page level, would catch only cases where the communication was correct at the 
> storage level but wrong at the semantic level -- which /should/ mean it can 
> catch /only/ bugs in SQLite.

I don't know of any filesystems other than ZFS, btrfs, and the new one
from Microsoft, that store checksums of blocks *with the pointers*.

> Problem is, what would you do if a checksum was wrong ?  Signal it in a 
> newly-invented result code ?  Then what ?  All apps would have to have some 
> logic to cope with the situation.  What should a simple app do about it ?  
> How many more lines of code would that add to apps which use SQLite specially 
> because of its low overheads ?

If you have redundancy you can use the checksum to find the best copy
of a block, or, in the case of a RAID-5-like storage strategy, to
reconstruct the correct version of the block.

If you can't do any of that, then you can fail.  This is painful for
the user, but the alternative is to return garbage without anyone
knowing it.

> I don't think it's worth doing this in a thin/light/fast system like SQLite.  
> Not only does it all weight to the API, it also adds weight to any app which 
> uses it.  And the number of bugs that cause corruption that would be spotted 
> this way seems to be low.  Instead perhaps it would be better to look into 
> logic which does simple monitoring at the structure level, e.g. checking that 
> if a command should add three rows, it really does add three rows.  (Although 
> some sort of analysis of the corruption bugs previously found in SQLite 
> should be undertaken first.)  These can be enabled/disabled with a 
> compilation flag, whereas a checksum can't because you always need to 
> calculate it.

I would generally prefer to let the filesystem do this.  However,
Roger has a point that it will be years before a significant number of
SQLite3 databases are hosted on filesystems that provide strong
end-to-end integrity protection, and that is a good argument for doing
the protection in the DB.  (Well, it's also a strong argument for
asking the OS vendors just when they're going to get off their butts
and ship reliable filesystems.  But that's another story, and anyways,
it's likely all in the works at this time for all major OS vendors.)

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

Re: [sqlite] About new ticket "Per page/region checksums"

[Simon Slavin]

On 6 Feb 2012, at 9:49pm, Nico Williams wrote:

> On Mon, Feb 6, 2012 at 2:27 PM, Roger Binns  wrote:
>> Approximately zero percent of SQLite databases will be on btrfs for the
>> foreseeable future, even if you and I start using btrfs.  Android 3 and
>> above do support encrypting the filesystem which effectively gives you a
>> lot of the checksumming, although filesystem implementations tend to care
>> far more about metadata than file contents.
> 
> Encryption is not enough.  You really need block pointers to carry the
> block checksum/hash/MAC/integrity tag.

File systems (FAT, NTFS, HTFS) already have block checksums.  So do network 
file systems (AFP, SMB, NFS).  Adding more to SQLite, whether at a row or page 
level, would catch only cases where the communication was correct at the 
storage level but wrong at the semantic level -- which /should/ mean it can 
catch /only/ bugs in SQLite.

Problem is, what would you do if a checksum was wrong ?  Signal it in a 
newly-invented result code ?  Then what ?  All apps would have to have some 
logic to cope with the situation.  What should a simple app do about it ?  How 
many more lines of code would that add to apps which use SQLite specially 
because of its low overheads ?

I don't think it's worth doing this in a thin/light/fast system like SQLite.  
Not only does it all weight to the API, it also adds weight to any app which 
uses it.  And the number of bugs that cause corruption that would be spotted 
this way seems to be low.  Instead perhaps it would be better to look into 
logic which does simple monitoring at the structure level, e.g. checking that 
if a command should add three rows, it really does add three rows.  (Although 
some sort of analysis of the corruption bugs previously found in SQLite should 
be undertaken first.)  These can be enabled/disabled with a compilation flag, 
whereas a checksum can't because you always need to calculate it.

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

Re: [sqlite] About new ticket "Per page/region checksums"

[Nico Williams]

On Mon, Feb 6, 2012 at 2:27 PM, Roger Binns  wrote:
> On 06/02/12 11:35, Nico Williams wrote:
>> Indeed, but if you'd do integrity protection at the application layer
>> then surely you'd have have a backup/restore strategy to deal with
>> lower-layer corruption.
>
> Only if you know about that corruption! It is perfectly possible for stray
> zeroes to be written into the btrees in such a way that they are still
> considered valid, but you have effectively amputated a row.

No, you can do something about that: you could store a hash of the XOR
of the hashes of all the rows (per-table).  To check integrity simply
get all the rows, compute the running XOR of hashes, then when you're
done hash the result and then check that against the stored value for
the whole DB.

(You could also rebuild indexes after checking DB integrity.)

>> However, if you're going to have SQLite3 do it
>
> Note that SQLite already has a way of reserving space on a per page basis.
>  It is currently used by the encryption extension.  It is possible to
> store a checksum there instead which would still be readable but not
> writeable by older versions of SQLite.

Interesting.

> Since the btree pages would also have checksums things should work in most
> scenarios.  Any solution would be better than the current no solution,
> especially as checksums let you discover corruption early.

Yes.

>> Well, one can hope.  (I don't keep up with btrfs; perhaps it will
>> mature and become common on Android devices.)
>
> Approximately zero percent of SQLite databases will be on btrfs for the
> foreseeable future, even if you and I start using btrfs.  Android 3 and
> above do support encrypting the filesystem which effectively gives you a
> lot of the checksumming, although filesystem implementations tend to care
> far more about metadata than file contents.

Encryption is not enough.  You really need block pointers to carry the
block checksum/hash/MAC/integrity tag.

> Windows 8 server will be coming with a filesystem that does integrity
> checking.  However again approximately zero percent of SQLite databases
> will be stored on that filesystem for the foreseeable future.

Right.

> SQLite having its own checksumming will solve the integrity issues today,
> for hundreds of millions of users, especially when developers consider
> data very important.  It will also solve some of the corruption issue
> finger pointing - if a page has a correct checksum but SQLite considers
> the contents corrupt then the most likely explanation is a bug in SQLite,
> while an invalid checksum points to the contents being different than what
> SQLite wrote hence points fingers at the operating system and storage.

Agreed.  For this, again, I'd prefer page-level hashing, with hash
values stored with page pointers.  This way the overhead of integrity
checking would be paid once per-I/O.

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

Re: [sqlite] ALTER TABLE

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/02/12 12:50, Tim Streater wrote:
> Can that [pragma user_version] be relied upon, though?

It is used by both Firefox and Android.  The actual value is stored in the
SQLite header.  It would be astonishing and unprecedented for the SQLite
team to remove it, nor is there is any conceivable reason to do so.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8wQMcACgkQmOOfHg372QROzACgqxESS5vKgz1CK5GAxeHFsNPV
pq8An39N2qFS5OnWxCKcQ1dCEXxRehsT
=zsOv
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Nico Williams]

I'm pretty sure that the user_version pragma is considered stable.

That said, if your application is in full control of the DB then you
could just check the exact create statements logged in sqlite_master
(this is probably less stable, ironically enough).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Tim Streater]

On 06 Feb 2012 at 19:31, Ryan Johnson  wrote: 

> On 06/02/2012 1:59 PM, Bill McCormick wrote:

>> The order is not important. What is important is that I come up with
>> some way to manage version updates. I've tried doing something similar
>> in the past using an "alter tables" script (using a different DB). The
>> script assumed some base version of schema was present, and then
>> proceeded adding new schema if it didn't already exist. It probably
>> seemed like a good idea at the time (to whomever started it), but as
>> time went on this script grew more and more unmanageable and I dreaded
>> having to use it.

> You might exploit #pragma user_version to help you track future changes,
> though that wouldn't necessarily help with the existing mess.

Can that be relied upon, though? The doc explicitly states: "Specific pragma 
statements may be removed and others added in future releases of SQLite. There 
is no guarantee of backwards compatibility".

I keep my own version number in a master table and use that to indicate that a 
table needs updating.

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

Re: [sqlite] Relations in sqlite and checking consistency of existing data base

[Agrawal, Manish]

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Kees Nuyt
Sent: Monday, February 06, 2012 3:29 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Relations in sqlite and checking consistency of existing 
data base

On Sun, 5 Feb 2012 18:39:40 +0100, Marek Staniewski 
 wrote:

>Hello
>
>I am a kind of newbie in sqlite world.
>I have several questions about sqlite data base:
>
>1.  Is there any way to read relations inside  some sqlite data base 
>(some way of deduction from data base contents)

Not in the engine itself. Probably such tools can be found. I would guess most 
of htem are commercial (not for free).

Visual Studio can do it using System.Data.SQLite. I have not had much luck 
getting it to write to the scheme, but it can read and show the relationships 
very well.

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

Re: [sqlite] Relations in sqlite and checking consistency of existing data base

[Kees Nuyt]

On Sun, 5 Feb 2012 18:39:40 +0100, Marek Staniewski
 wrote:

>Hello
>
>I am a kind of newbie in sqlite world.
>I have several questions about sqlite data base:
>
>1.  Is there any way to read relations inside
> some sqlite data base (some way of deduction from data base contents)

Not in the engine itself. Probably such tools can be found. I would
guess most of htem are commercial (not for free).

>2.  Let say I would like to check consistency/errors of my sqlite
> data base. Is there any plan how to start with it.
> Interesting is for example how to search for lost unused records

Define REFERENCES constraints (foreign keys) for child tables to
reference the primary key of parent tables.

Example (untested):
Check with a join, something like:
CREATE TABLE Parent (
  pk INTEGER PRIMARY KEY,
  tx TEXT
);
CREATE TABLE Child (
  pk INTEGER PRIMARY KEY,
  fk INTEGER REFERENCES Parent(pk) 
 ON UPDATE CASCADE ON DELETE CASCADE,
  tx TEXT
);

-- some correct and false insert statements here
-- left as an exercise for the reader.

SELECT 'No parent with pk ' AS diag1, C.fk,
  ' for child with pk ' AS diag2, C.pk
  FROM Child AS C 
  LEFT OUTER JOIN Parent AS P ON P.pk = C.fk
  WHERE P.pk IS NULL
;
(pk=primary key column, fk=foreign key column)

Enforce referential integrity by switching foreign keys on on every
connection you make(!)

PRAGMA foreign_keys=ON;


>3.  Assume I have made some program using sqlite
> data base. This is used by my users, but they may use
> different versions of my program. How to update such
> program for checking database so it may check adequately
> to db version? in case there are some changes in data base.

Database version is reflected by 
PRAGMA schema_version;
which is incremented by sqlite.

Apart from that you can maintain a user version:
PRAGMA user_version[=value];
and use that in your application.

http://www.sqlite.org/pragma.html#pragma_schema_version

>4.  Is it difficult to switch my program
> from simple db use to client-server version. Does it need much work?

It may require a lot of work. You can manage it a little bit by staying
as close as possible to a common SQL standard, and by abstracting the
database access functions into classmethods or functions outside the
application logic.

Hope this helps.

>Marek Staniewski


-- 
Regards,

Kees Nuyt

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

Re: [sqlite] About new ticket "Per page/region checksums"

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/02/12 11:35, Nico Williams wrote:
> Indeed, but if you'd do integrity protection at the application layer 
> then surely you'd have have a backup/restore strategy to deal with 
> lower-layer corruption.

Only if you know about that corruption! It is perfectly possible for stray
zeroes to be written into the btrees in such a way that they are still
considered valid, but you have effectively amputated a row.

> Yes, this tends to argue that if you want row-level checksums

My point is that they are fairly useless since they only work if btrees
and indices work perfectly.  Anything that could affect rows could also
affect the other pieces of information SQLite is storing.

> However, if you're going to have SQLite3 do it

Note that SQLite already has a way of reserving space on a per page basis.
 It is currently used by the encryption extension.  It is possible to
store a checksum there instead which would still be readable but not
writeable by older versions of SQLite.

Since the btree pages would also have checksums things should work in most
scenarios.  Any solution would be better than the current no solution,
especially as checksums let you discover corruption early.

> Well, one can hope.  (I don't keep up with btrfs; perhaps it will 
> mature and become common on Android devices.)

Approximately zero percent of SQLite databases will be on btrfs for the
foreseeable future, even if you and I start using btrfs.  Android 3 and
above do support encrypting the filesystem which effectively gives you a
lot of the checksumming, although filesystem implementations tend to care
far more about metadata than file contents.

Windows 8 server will be coming with a filesystem that does integrity
checking.  However again approximately zero percent of SQLite databases
will be stored on that filesystem for the foreseeable future.


http://blogs.msdn.com/b/b8/archive/2012/01/16/building-the-next-generation-file-system-for-windows-refs.aspx

SQLite having its own checksumming will solve the integrity issues today,
for hundreds of millions of users, especially when developers consider
data very important.  It will also solve some of the corruption issue
finger pointing - if a page has a correct checksum but SQLite considers
the contents corrupt then the most likely explanation is a bug in SQLite,
while an invalid checksum points to the contents being different than what
SQLite wrote hence points fingers at the operating system and storage.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8wN6oACgkQmOOfHg372QTDnACgmHiKBZ43TbYlVGPRxN5a9UF5
uGkAoIbjVsjuA7IuATyC/0M2IDDE6IJN
=o4lD
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About new ticket "Per page/region checksums"

[Nico Williams]

On Mon, Feb 6, 2012 at 1:20 PM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 06/02/12 09:11, Nico Williams wrote:
>> A checksum per-row is certainly a valuable thing at the layer above the
>> RDBMS since it allows for integrity checking above the RBDMS, and in an
>> RBDMS-independent manner.
>
> It doesn't actually help that much since it is only over a portion of the
> database content.  While it will catch an individual row being corrupted
> it will not catch the btree that points to the table rows being corrupted.

Indeed, but if you'd do integrity protection at the application layer
then surely you'd have have a backup/restore strategy to deal with
lower-layer corruption.

>  If that container is "tweaked" then it could still be sufficiently valid
> but end up omitting the row so you will get wrong query results.  The same
> story applies to indices where a judicious tweak will not corrupt it, but
> will result in the wrong rows being selected.

Yes, this tends to argue that if you want row-level checksums you also
want to have SQLite3 do it as in Andy's solution.  However, if you're
going to have SQLite3 do it I'd rather it followed the ZFS pattern of
checksumming entire pages and storing the checksum with the page
pointers.

> It is nice that some filesystems are adding integrity protection (and in
> some cases recovery), but approximately zero percent of the systems out
> there running SQLite do not have the databases stored in such a configured
> filesystem today.

Well, one can hope.  (I don't keep up with btrfs; perhaps it will
mature and become common on Android devices.)

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

Re: [sqlite] ALTER TABLE

[Ryan Johnson]

On 06/02/2012 1:59 PM, Bill McCormick wrote:

Nico Williams wrote, On 2/6/2012 12:44 PM:
On Mon, Feb 6, 2012 at 9:36 AM, Bill 
McCormick  wrote:
Is there no way to force columns added to a table with alter table 
to be

added at certain column positions?

Alternatively, if there is some way to save the data in an existing 
table;
drop the table; re-create the table with the desired schema; and 
then reload
the data, this would be useful as well. However, I cannot see how to 
do this

simply.

If order of columns is only important for aesthetic reasons you might
just use a ALTER TABLE to add the column at the end of the list and
then a VIEW to provide the view you prefer.

The order is not important. What is important is that I come up with 
some way to manage version updates. I've tried doing something similar 
in the past using an "alter tables" script (using a different DB). The 
script assumed some base version of schema was present, and then 
proceeded adding new schema if it didn't already exist. It probably 
seemed like a good idea at the time (to whomever started it), but as 
time went on this script grew more and more unmanageable and I dreaded 
having to use it.
You might exploit #pragma user_version to help you track future changes, 
though that wouldn't necessarily help with the existing mess.


Ryan

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

Re: [sqlite] About new ticket "Per page/region checksums"

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/02/12 09:11, Nico Williams wrote:
> A checksum per-row is certainly a valuable thing at the layer above the
> RDBMS since it allows for integrity checking above the RBDMS, and in an
> RBDMS-independent manner.

It doesn't actually help that much since it is only over a portion of the
database content.  While it will catch an individual row being corrupted
it will not catch the btree that points to the table rows being corrupted.
 If that container is "tweaked" then it could still be sufficiently valid
but end up omitting the row so you will get wrong query results.  The same
story applies to indices where a judicious tweak will not corrupt it, but
will result in the wrong rows being selected.

Being able to integrity check the rows that are returned doesn't help if
they are the wrong rows!

It is nice that some filesystems are adding integrity protection (and in
some cases recovery), but approximately zero percent of the systems out
there running SQLite do not have the databases stored in such a configured
filesystem today.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8wJ/YACgkQmOOfHg372QQ9AACgp1xLCbo9tEIqyF+Ar7ZqaKxi
xjoAnjx6AEN0lP6ZP9QwVSKC642hpfao
=wC3P
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Bill McCormick]

Nico Williams wrote, On 2/6/2012 12:44 PM:

On Mon, Feb 6, 2012 at 9:36 AM, Bill McCormick  wrote:

Is there no way to force columns added to a table with alter table to be
added at certain column positions?

Alternatively, if there is some way to save the data in an existing table;
drop the table; re-create the table with the desired schema; and then reload
the data, this would be useful as well. However, I cannot see how to do this
simply.

If order of columns is only important for aesthetic reasons you might
just use a ALTER TABLE to add the column at the end of the list and
then a VIEW to provide the view you prefer.

The order is not important. What is important is that I come up with 
some way to manage version updates. I've tried doing something similar 
in the past using an "alter tables" script (using a different DB). The 
script assumed some base version of schema was present, and then 
proceeded adding new schema if it didn't already exist. It probably 
seemed like a good idea at the time (to whomever started it), but as 
time went on this script grew more and more unmanageable and I dreaded 
having to use it.


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

Re: [sqlite] ALTER TABLE

[Nico Williams]

On Mon, Feb 6, 2012 at 9:36 AM, Bill McCormick  wrote:
> Is there no way to force columns added to a table with alter table to be
> added at certain column positions?
>
> Alternatively, if there is some way to save the data in an existing table;
> drop the table; re-create the table with the desired schema; and then reload
> the data, this would be useful as well. However, I cannot see how to do this
> simply.

If order of columns is only important for aesthetic reasons you might
just use a ALTER TABLE to add the column at the end of the list and
then a VIEW to provide the view you prefer.

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

Re: [sqlite] ALTER TABLE

[Black, Michael (IS)]

Hmmmcould .dump also have the ability to put out the column names for the 
inserts?

That would solve this problem without having to write a special program to do 
it yourself.



I suppose somebody might already have made a utility to do this?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Gerry Snyder [mesmerizer...@gmail.com]
Sent: Monday, February 06, 2012 11:03 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] ALTER TABLE

On 2/6/2012 9:22 AM, Bill McCormick wrote:
> Sorry, I should have mentioned that I did see that, but it doesn't
> quite fit my application. I need a script that doesn't care what the
> existing table looks like. In my situation, I may have dozens of
> databases among different locations, perhaps not all at the same
> revision level. The script I need would be able to bring each up to
> the current revision.
>
> So, if I had a fist step:
>
> CREATE TEMPORARY TABLE t1_backup AS SELECT * FROM t1;
>
> and then
> DROP TABLE t1;
>
> and then add the table with it's latest schema revision
> CREATE TABLE t1( ... );
>
> It seems difficult to get the saved data back in ...
> INSERT INTO t1 SELECT * FROM t1_backup;
>
> ... without know what the previous schema looks like. It complains
> like this:
> Error: table prod has 27 columns but 25 values were supplied

Yes, the INSERT statement has to specify all of the original column
names in the proper order.

I wrote a general ALTER TABLE code in Tcl, and it is one of the largest
functions in the system. It includes moving columns within a table,
since I agree some times a simple spreadsheet-like display is useful.

Gerry
___
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] About new ticket "Per page/region checksums"

[Nico Williams]

A checksum per-row is certainly a valuable thing at the layer above
the RDBMS since it allows for integrity checking above the RBDMS, and
in an RBDMS-independent manner.  Of course, this approach is easiest
to graft into SQLite3 simply because you'd be adding a hidden column
and the machinery for maintaining and checking it fits into a handful
of VM opcodes, thus barely changing the on-disk format, but...

... for an integrity protection scheme within the RDBMS I would argue
that it's better to have per-page checksums.  The argument is based on
the ZFS argument for checksumming entire blocks (and storing the
checksum with the block pointers): that you then pay for the cost of
checksumming only once per-I/O (whereas with a per-row solution you
either pay the checksum penalty every time you read that row, even
from cache, or you must do some extra bookkeeping to ensure that you
checksum no more than once per-{I/O, row}).  I suppose this might not
work out as well for an RDBMS as for ZFS since ZFS is able to get
offsetting benefits, such as reduced I/O times by compressing blocks.

Which brings me to a different point, which is that if you can host a
SQLite3 DB on a filesystem like ZFS then you don't have to concern
yourself quite as much with doing data integrity protection within
SQLite3.  What's the best layer for integrity protection in your
application, then?  So far we have three possible layers: app-layer,
SQLite3, filesystem.  If you trust your storage to provide integrity
protection you might add that as a layer.  I'm not sure that there's a
one-size-fits-all answer here, and I find your own solution to be
rather clever (since easy to graft in and keep up to date).

One common issue in all cases is: how to report integrity check
failures, and what else to do about them.  This is another area where
ZFS gets benefits you're not likely to get from higher-layers: since
ZFS integrates volume management into the filesystem it can use
integrity protection checksums to help correct errors when there is
redundancy (e.g., mirroring) in the volume.

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

Re: [sqlite] ALTER TABLE

[Gerry Snyder]

On 2/6/2012 9:22 AM, Bill McCormick wrote:
Sorry, I should have mentioned that I did see that, but it doesn't 
quite fit my application. I need a script that doesn't care what the 
existing table looks like. In my situation, I may have dozens of 
databases among different locations, perhaps not all at the same 
revision level. The script I need would be able to bring each up to 
the current revision.


So, if I had a fist step:

CREATE TEMPORARY TABLE t1_backup AS SELECT * FROM t1;

and then
DROP TABLE t1;

and then add the table with it's latest schema revision
CREATE TABLE t1( ... );

It seems difficult to get the saved data back in ...
INSERT INTO t1 SELECT * FROM t1_backup;

... without know what the previous schema looks like. It complains 
like this:

Error: table prod has 27 columns but 25 values were supplied


Yes, the INSERT statement has to specify all of the original column 
names in the proper order.


I wrote a general ALTER TABLE code in Tcl, and it is one of the largest 
functions in the system. It includes moving columns within a table, 
since I agree some times a simple spreadsheet-like display is useful.


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

Re: [sqlite] ALTER TABLE

[Bill McCormick]

Gerry Snyder wrote, On 2/6/2012 9:48 AM:

On 2/6/2012 8:36 AM, Bill McCormick wrote:
Is there no way to force columns added to a table with alter table to 
be added at certain column positions?


Alternatively, if there is some way to save the data in an existing 
table; drop the table; re-create the table with the desired schema; 
and then reload the data, this would be useful as well. However, I 
cannot see how to do this simply.




A very quick search at the SQLite website (hint, hint) found:

http://www.sqlite.org/faq.html#q11
Sorry, I should have mentioned that I did see that, but it doesn't quite 
fit my application. I need a script that doesn't care what the existing 
table looks like. In my situation, I may have dozens of databases among 
different locations, perhaps not all at the same revision level. The 
script I need would be able to bring each up to the current revision.


So, if I had a fist step:

CREATE TEMPORARY TABLE t1_backup AS SELECT * FROM t1;

and then
DROP TABLE t1;

and then add the table with it's latest schema revision
CREATE TABLE t1( ... );

It seems difficult to get the saved data back in ...
INSERT INTO t1 SELECT * FROM t1_backup;

... without know what the previous schema looks like. It complains like this:
Error: table prod has 27 columns but 25 values were supplied

Any ideas?

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

Re: [sqlite] ALTER TABLE

[Gerry Snyder]

On 2/6/2012 8:36 AM, Bill McCormick wrote:
Is there no way to force columns added to a table with alter table to 
be added at certain column positions?


Alternatively, if there is some way to save the data in an existing 
table; drop the table; re-create the table with the desired schema; 
and then reload the data, this would be useful as well. However, I 
cannot see how to do this simply.




A very quick search at the SQLite website (hint, hint) found:

http://www.sqlite.org/faq.html#q11


HTH,

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

Re: [sqlite] ALTER TABLE

[Puneet Kishor]

On Feb 6, 2012, at 9:36 AM, Bill McCormick wrote:

> Is there no way to force columns added to a table with alter table to be 
> added at certain column positions?
> 
> Alternatively, if there is some way to save the data in an existing table; 
> drop the table; re-create the table with the desired schema; and then reload 
> the data, this would be useful as well. However, I cannot see how to do this 
> simply.

just do it exactly as you state above... 

dump
drop
recreate
reload

script it and it couldn't be any simpler. Although, ordinarily neither you nor 
the db should care about the order of the column. The results come out in the 
order you specify.

CREATE TABLE t (a, b, c);
SELECT b, c, a FROM t WHERE...

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

[sqlite] ALTER TABLE

[Bill McCormick]

Is there no way to force columns added to a table with alter table to be 
added at certain column positions?


Alternatively, if there is some way to save the data in an existing 
table; drop the table; re-create the table with the desired schema; and 
then reload the data, this would be useful as well. However, I cannot 
see how to do this simply.


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

Re: [sqlite] Efficiency of concatenation on UPDATE

[Hamish Allan]

On 6 February 2012 05:20, Dan Kennedy  wrote:

> I think when two doclists are merged both are loaded into memory.
> Some types of queries load the entire doclist for a term into
> memory too.

Hmm, sounds like I definitely need to stick with rows representing
document pages rather than whole documents. Thank you for the advice!

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

Re: [sqlite] Nested transaction

[Jay A. Kreibich]

On Mon, Feb 06, 2012 at 08:20:48PM +0530, Sreekumar TP scratched on the wall:
> so if stmt1 & stmt2 are executed on different threads , but on the same
> connection, they would not block each other?

  They would not block each other due to database access locks.

  They may or may not block each other on thread locks-- it depends on
  the current thread model (Multi-thread vs Serial.  For more info:
  http://sqlite.org/threadsafe.html)

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested transaction

[Sreekumar TP]

so if stmt1 & stmt2 are executed on different threads , but on the same
connection, they would not block each other?

Sreekumar
On Feb 6, 2012 8:08 PM, "Igor Tandetnik"  wrote:

> Sreekumar TP  wrote:
> > well, if stmt1 is a write transaction, it would aquire an exclusive lock.
> > if stmt2 is a read transaction, it would fail acquiring a shared lock
>
> The concept of a transaction exists on a per-connection basis, not
> per-statement. Transaction isolation applies to statements running on two
> separate connections. Multilple statements running on the same connection
> never block each other.
> --
> Igor Tandetnik
>
> ___
> 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] Nested transaction

[Igor Tandetnik]

Sreekumar TP  wrote:
> well, if stmt1 is a write transaction, it would aquire an exclusive lock.
> if stmt2 is a read transaction, it would fail acquiring a shared lock

The concept of a transaction exists on a per-connection basis, not 
per-statement. Transaction isolation applies to statements running on two 
separate connections. Multilple statements running on the same connection never 
block each other.
-- 
Igor Tandetnik

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

Re: [sqlite] Nested transaction

[Jay A. Kreibich]

On Mon, Feb 06, 2012 at 07:46:18PM +0530, Sreekumar TP scratched on the wall:
> well, if stmt1 is a write transaction, it would aquire an exclusive lock.
> if stmt2 is a read transaction, it would fail acquiring a shared lock since
> the exclusive lock is not released. . unless sqlite decides to 'downgrade'
> the exclusive lock to a 'shared' lock.

  You're running both statements through the same connection.  They're
  using the same set of locks, just as if it were a single, explicit
  transaction.

  If a statement is run when there is no explicit transaction open, an
  automatic transaction is open.  That transaction remains open until
  there are no outstanding statements.

   -j


> Sreekumar
> On Feb 6, 2012 7:07 PM, "Igor Tandetnik"  wrote:
> 
> > Sreekumar TP  wrote:
> > > Why is this treated as a a single transaction?
> >
> > Well, because that's how SQLite works. Why shouldn't it be?
> > --
> > Igor Tandetnik
> >
> > ___
> > 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

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested transaction

[Sreekumar TP]

well, if stmt1 is a write transaction, it would aquire an exclusive lock.
if stmt2 is a read transaction, it would fail acquiring a shared lock since
the exclusive lock is not released. . unless sqlite decides to 'downgrade'
the exclusive lock to a 'shared' lock.

Sreekumar
On Feb 6, 2012 7:07 PM, "Igor Tandetnik"  wrote:

> Sreekumar TP  wrote:
> > Why is this treated as a a single transaction?
>
> Well, because that's how SQLite works. Why shouldn't it be?
> --
> Igor Tandetnik
>
> ___
> 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] Nested transaction

[Igor Tandetnik]

Sreekumar TP  wrote:
> Why is this treated as a a single transaction?

Well, because that's how SQLite works. Why shouldn't it be?
-- 
Igor Tandetnik

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

Re: [sqlite] Nested transaction

[Sreekumar TP]

Why is this treated as a a single transaction?

Sreekumar
On Feb 6, 2012 6:27 PM, "Igor Tandetnik"  wrote:

> Sreekumar TP  wrote:
> > I have a nested transaction as follows -
> >
> > sqlite3_prepare_v2(db, stmt1..)
> > sqlite3_prepare_v2(db, stmt2..)
> >
> > sqlite3_step(stmt1)
> >   sqlite3_step(stmt2)
> >  sqlite3_reset(stmt2)
> > sqlite3_step(stmt1)
>
> There is only one transaction here - an implicit one, started by
> sqlite3_step(stmt1).
>
> > (1) SQLITE , as I understand does not support nested transaction
>
> Correct.
>
> > hence the
> > the above statement should have caused error
>
> Should not. The conclusion doesn't follow from the premise.
>
> > (2)  A transaction is complete only when sqlite3_reset is called.
>
> To be precise, an implicit transaction is committed when there are no more
> outstanding statements on a connection, that is, when the last active
> statement is reset or finalized. See
>
> http://sqlite.org/lang_transaction.html
>
> in particular the part about implicit transactions.
>
> > Hence the
> > sqlite3_step(stmt2) should have returned a "database locked" error.
>
> Should not. The conclusion doesn't follow from the premise.
>
> > Is SQLITE doing something under the hood ?
>
> SQLite is doing a lot under the hood. Is there anything in particular
> you'd like to know about?
> --
> Igor Tandetnik
>
> ___
> 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

[sqlite] Relations in sqlite and checking consistency of existing data base

[Marek Staniewski]

Hello

I am a kind of newbie in sqlite world.
I have several questions about sqlite data base:

1.   Is there any way to read relations inside some sqlite data base (some 
way of deduction from data base contents)

2.   Let say I would like to check consistency/errors of my sqlite data 
base. Is there any plan how to star with it. Interesting is for example how to 
search for lost unused records

3.   Assume I have made some program using sqlite data base. This is used 
by my users, but they may use different versions of my program. How to update 
such program for checking database so it may check adequately to db version? in 
case there are some changes in data base.

4.   Is it difficult to switch my program from simple db use to 
client-server version. Does it need may work?

Marek Staniewski



AMSORT Sp. z o.o.
01-309 Warszawa,  ul. Okr?towa 70 B, Poland
tel: +48 22 664 38 87, fax: +48 22 664 38 89., 
www.amsort.com




Tre?? tej wiadomo?ci jest poufna i prawnie chroniona. Odbiorc? mo?e by? jedynie 
jej adresat z wy??czeniem dost?pu os?b trzecich. Je?eli nie jeste? adresatem 
niniejszej wiadomo?ci, jej rozpowszechnianie, kopiowanie, rozprowadzanie lub 
inne dzia?anie o podobnym charakterze jest prawnie zabronione i mo?e by? 
karalne. Je?eli wiadomo?? ta jest adresowana do naszych Klient?w, jakakolwiek 
opinia lub porada w niej zawarta podlega warunkom wyra?onym w odpowiedniej 
umowie o ?wiadczenie us?ug na rzecz Klienta przez AMSORT Sp. z o.o.
Niniejsza wiadomo?? nie stanowi oferty w rozumieniu Kodeksu cywilnego.

The information in this email is confidential and may be legally privileged. It 
is intended solely for the addressee. Access to this email by anyone else is 
unauthorized. If you are not the intended recipient, any disclosure, copying, 
distribution or any action taken or omitted to be taken in reliance on it, is 
prohibited and may be unlawful. When addressed to our client, any opinion or 
advice contained in this email are subject to the terms and conditions 
expressed in the governing AMSORT Sp. z o.o. client engagement letter.


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

Re: [sqlite] Nested transaction

[Richard Hipp]

On Mon, Feb 6, 2012 at 7:57 AM, Igor Tandetnik  wrote:

> Sreekumar TP  wrote:
> > I have a nested transaction as follows -
> >
> > sqlite3_prepare_v2(db, stmt1..)
> > sqlite3_prepare_v2(db, stmt2..)
> >
> > sqlite3_step(stmt1)
> >   sqlite3_step(stmt2)
> >  sqlite3_reset(stmt2)
> > sqlite3_step(stmt1)
>
> There is only one transaction here - an implicit one, started by
> sqlite3_step(stmt1).
>
> > (1) SQLITE , as I understand does not support nested transaction
>
> Correct.
>

SQLite has supported SAVEPOINT
since 2009.  SAVEPOINT is
just a fancy name for a nested transaction.


>
> > hence the
> > the above statement should have caused error
>
> Should not. The conclusion doesn't follow from the premise.
>
> > (2)  A transaction is complete only when sqlite3_reset is called.
>
> To be precise, an implicit transaction is committed when there are no more
> outstanding statements on a connection, that is, when the last active
> statement is reset or finalized. See
>
> http://sqlite.org/lang_transaction.html
>
> in particular the part about implicit transactions.
>
> > Hence the
> > sqlite3_step(stmt2) should have returned a "database locked" error.
>
> Should not. The conclusion doesn't follow from the premise.
>
> > Is SQLITE doing something under the hood ?
>
> SQLite is doing a lot under the hood. Is there anything in particular
> you'd like to know about?
> --
> Igor Tandetnik
>
> ___
> 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] Nested transaction

[Igor Tandetnik]

Sreekumar TP  wrote:
> I have a nested transaction as follows -
> 
> sqlite3_prepare_v2(db, stmt1..)
> sqlite3_prepare_v2(db, stmt2..)
> 
> sqlite3_step(stmt1)
>   sqlite3_step(stmt2)
>  sqlite3_reset(stmt2)
> sqlite3_step(stmt1)

There is only one transaction here - an implicit one, started by 
sqlite3_step(stmt1).

> (1) SQLITE , as I understand does not support nested transaction

Correct.

> hence the
> the above statement should have caused error

Should not. The conclusion doesn't follow from the premise.

> (2)  A transaction is complete only when sqlite3_reset is called.

To be precise, an implicit transaction is committed when there are no more 
outstanding statements on a connection, that is, when the last active statement 
is reset or finalized. See

http://sqlite.org/lang_transaction.html

in particular the part about implicit transactions.

> Hence the
> sqlite3_step(stmt2) should have returned a "database locked" error.

Should not. The conclusion doesn't follow from the premise.

> Is SQLITE doing something under the hood ?

SQLite is doing a lot under the hood. Is there anything in particular you'd 
like to know about?
-- 
Igor Tandetnik

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

Re: [sqlite] About new ticket "Per page/region checksums"

[Andy Gibbs]

On 4th February 2012 12:51pm, Alexey Pechnikov wrote:

> It's very important but there are some questions about
> http://www.sqlite.org/src/info/72b01a982a
> Some times ago DRH wrote that checksum calculation don't slow down
> SQLite significantly.
> But can be this realized in current SQLite 3.x branch? When checksum
> can help to restore
> damaged pages/database? Does powersave overwrite feature conflicts
> with idea of per pages
> checksums?

Obviously I can't speak for DRH and co., but I can speak from my own 
experience, and maybe it will be beneficial to the debate.

I implemented a checksumming feature into sqlite back in 2008/9 and have 
maintained it ever since and now have it in my branch of 3.7.10.  This 
checksum is an 8-bit crc and is calculated *per row* on any table for which 
it is enabled.  My implementation adds two bytes overhead to each row of 
checksummed tables in the database.  I found that when used on a 400mhz 
PowerPC embedded platform there was a very small but measurable performance 
regression when dealing with hundreds of thousands of rows of data compared 
to a database file not performing the crc calculation.  On a desktop PC this 
performance regression was not measurable (i.e. insignificant).

I won't go into the details of exactly how it has been implemented, unless 
someone is particularly interested, but to say this:

Since I observed a slight performance regression on slow hardware I made it 
possible to turn the feature on and off on a per-table basis.  This meant a 
small addition to the SQL syntax for CREATE TABLE.  I wanted the checksum 
calculation to only occur when the row was created and only when checking 
for checksum errors in the database so that in general use performance 
wouldn't be affected.  I also wished the checksum to be "invisible" to the 
user in normal operation, e.g. when performing a SELECT * FROM table, or 
when using an INSERT or UPDATE statement.

In the implementation it became necessary to upgrade the file format to "5" 
to support all these features, and to remain compatible with old database 
files.  In addition to PRAGMA legacy_file_format, I also have PRAGMA 
compatible_file_format, so that the user can choose between 0, 4 and 5 as 
the format for the database file.  Only in format "5" can the checksum be 
used.

The user cannot find out the checksum of a row, but instead I implemented an 
additional SQL function which checks the checksum of a particular row and 
returns '0' or '1' depending on whether the row had become corrupted.  A 
simple statement like this:

SELECT rowid FROM table WHERE !valid_row_checksum(rowid);

is enough to find corrupted rows.

If the checksum feature were to be added to Sqlite as standard, as proposed 
by Roger Binns, I would strongly suggest that it should be possible to 
fine-grain the corruption detection to a single row of data.  To an actual 
column within the row would be nice, but I think too much of an overhead to 
be truly worth while.  My implementation also didn't create any checksums on 
data other than row data.  For me that wasn't an issue, but I could see that 
in other applications it may be a nice feature.

I have a complete patch for my implementation with documentation and 
test-suite additions.  If anyone is interested, even the Sqlite 
developers(!), then drop me an email and I'll post it along.  I have no 
restriction on me stopping this patch being released to the public domain.

Andy




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

Re: [sqlite] Problem of using amalgamation with Visual C++ 2005

[Richard Hipp]

On Mon, Feb 6, 2012 at 2:35 AM, ASURADA  wrote:

> I was test about insert sqlite library to mfc project.
> I used amalgamaion files of version 3.7.10.
>
> It was fail with Visual C++ 2005. but success with Visual C++ 2010.
>
> The error occured on 35393 line of sqlite3.c when It call the
> sqlite3_open().
>
> Why Visual C++ 2005 is different from 2010.
>

Without looking at your project, I will guess that the problem is the one
addressed at http://www.sqlite.org/src/info/1ce4d21d52 - namely that vs2005
fails to set the _WINNT_WIN32 C preprocessor macro correctly and hence
requires that either you set it yourself in your environment, or that you
using the work-around provided in the link above.



>
> What I have to do for use amalgmation source on Visual C++ 2005.
>
> "http://www.box.com/s/skyaz15c1f9dy9cpe4pn; is address of my test project.
> If you have interest my situation, please download and run the porject.
> Please find my fault.
> ___
> 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

[sqlite] Nested transaction

[Sreekumar TP]

Hi,


I have a nested transaction as follows -

sqlite3_prepare_v2(db, stmt1..)
sqlite3_prepare_v2(db, stmt2..)

sqlite3_step(stmt1)
   sqlite3_step(stmt2)
  sqlite3_reset(stmt2)
sqlite3_step(stmt1)


(1) SQLITE , as I understand does not support nested transaction, hence the
the above statement should have caused error
(2)  A transaction is complete only when sqlite3_reset is called. Hence the
sqlite3_step(stmt2) should have returned a "database locked" error.

However,the above statements succeed if stmt1 is a read and stmt2 is a
write.

Is SQLITE doing something under the hood ? (like upgrading the locks for ex
).


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