Re: [sqlite] Stored procedures

[Olaf Schmidt]

"BareFeetWare"   schrieb
> On 13/11/2010, at 10:33 AM, Olaf Schmidt wrote:

> >> If you have code (in either environment) that is
> >> looping or grabbing one result and sticking it in a
> >> second query, then your approach is probably flawed.
> >
> > As you say: "probably" ... because "it depends" ...
> > (maybe your application wants to show "details"
> > in a delayed fashion - e.g. when a Node in a
> > Treeview gets "expanded").
>
> Good example. At the point where the user requests expanded
> details (eg by clicking on a button), that obviously kicks
> off application code. As far as its interaction with the
> database, simplistically, it would have to do something
> like get the key values in the current row, check whether
> it exists in the details set, get the detail data. What I'm
> saying is that this should be done as one SQL call, which
> most likely doesn't require any branching logic within that call.
>
> So it should look something like this, with just one SQL call:
>
> array filteredDetailResults  = execQuery(
> select "Product Code", "Product Name", Quantity, Price
> from "Invoice Items"
> join "Invoice List" on "Invoice Items"."Invoice Number" = "Invoice
List"."Invoice Number"
> left join "Item List" on "Invoice Items"."Product Code" = "Item
List"."Product Code"
> where "GUI Selection Flag" = 1
> )
> show filteredDetailResults

Nah ... come on - Joins are common things - don't
know which DB-Application-developer (who worth
its salt) is not making use of it (instead of falling back
to loops).

And in my App I would have put the above SQL
into a View-Definition beforehand (that's another
common thing, most DB-Engines share and which
is well-portable).

Ending up with something like that...

SQL = "Select * From InvoiceDetailView Where CustomerID = " & _
CurrentNode.ID

' get a new Recordset-Instance, containing the set, defined above
Set Rs = Cnn.OpenRecordset(SQL)

' GUI-Refresh over DataBinding against the Rs-Instance
Set MyGUIWidget.DataSource = Rs

--
And in case I'd have put the above (already View-based)
SQL-Code into a CommandObject first, then I would only
need to set the Command-Parameter (typed, and without
fiddling around with String-Concats).

InvoicesCmd.SetInteger !CustomerID, CurrentNode.ID
Set MyGUIWidget.DataSource = InvoicesCmd.Execute


Olaf



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

Re: [sqlite] improvement for the shell

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/12/2010 04:46 PM, Dave Dyer wrote:
> The standard shell behaves very badly if you accidentally
> select some BLOB data or excessively long strings.  I recommend
> this change:

You are free to change your copy and to redistribute that change in any way
you deem fit.

> #define MAX_STRING_SIZE 200
[..]
> and substitute
> 
>   cautious_print_string(p->out,z);
> 
> for the fprintf in the display loop.

Who says it is printing to screen?  It could be a file or a device expecting
the correct values.

I suggest adding to your copy of the code, perhaps a dot command to set the
cautious length, make it freely available and see how many people pounce on
your new functionality :-)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkzd78MACgkQmOOfHg372QRgAgCgjKgR2MFiv/yTGBg5CLeBO1eC
cq8Anjokw4Tlejj35VuKr4Fb7bkWHH2X
=3bmb
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Busy handler not called

[Prakash Reddy Bande]

Hi,

Got it. I did read this, but probably did not understand very well.
Yes, it makes sense why busy handler is not called. May be it is a good idea to 
site some examples in the documentation...

Regards,
 
Prakash Bande
Altair Engg. Inc. 
Troy MI
Ph: 248-614-2400 ext 489
Cell: 248-404-0292
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jay A. Kreibich
Sent: Friday, November 12, 2010 7:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Busy handler not called

On Fri, Nov 12, 2010 at 04:51:11PM -0500, Prakash Reddy Bande scratched on the 
wall:
> Hi,
> 
> I have set a busy handler.
> 
> int ret = sqlite3_open(dbname.c_str(), _ppDb);
> sqlite3_busy_handler(m_ppDb, , 0);
> 
> However it is not getting called.

  As the docs for sqlite3_busy_handler() point out, this is exactly how
  it is expected to work:

  

  The presence of a busy handler does not guarantee that it will be
  invoked when there is lock contention. If SQLite determines that
  invoking the busy handler could result in a deadlock, it will go
  ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of
  invoking the busy handler. Consider a scenario where one process
  is holding a read lock that it is trying to promote to a reserved
  lock and a second process is holding a reserved lock that it is
  trying to promote to an exclusive lock. The first process cannot
  proceed because it is blocked by the second and the second
  process cannot proceed because it is blocked by the first. If
  both processes invoke the busy handlers, neither will make any
  progress. Therefore, SQLite returns SQLITE_BUSY for the first
  process, hoping that this will induce the first process to
  release its read lock and allow the second process to proceed.



> 1. Using the sqlite3.exe run the following commands
> begin transaction;
> update users set name="hello"
> 
> 2. Note I have not yet commited.

  This will grab the reserved lock, and knows it will want to get an
  exclusive to finish the transaction.

> From my program I call
> 
> int ret = sqlite3_exec(m_ppDb, query.c_str(), , , 
> );
> 
> I get SQLITE_BUSY and hwLMsqlite3BusyHandler is not getting called.

  You said this is another update, so same thing... Any attempt to
  promote to a reserved lock will sense a possible deadlock and
  skip the busy handler.  At this point, there is no way out of
  this situation other than having the second query rollback.
  Exec is likely to do this automatically, aborting the second
  UPDATE, and returning the original SQLITE_BUSY error.


-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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL code vs application code (was: Stored procedures, was: Question about SQLite features.)

[BareFeetWare]

On 13/11/2010, at 11:14 AM, Scott Hess wrote:

> On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare  
> wrote:
>> IMO, if you're implementing database logic (ie constraints and triggers) in 
>> application code, then you're reinventing the wheel, making your package 
>> unnecessarily complex and grossly inefficient. If you're just using SQLite 
>> to store your data but doing all the logic outside of SQLite, then you might 
>> as well just be saving your data to a CSV file or XML. See my previous post 
>> for reasoning behind this.
> 
> From http://www.sqlite.org/about.html :
>> Think of SQLite not as a replacement for Oracle but as a replacement for 
>> fopen()

The full paragraph from that page reads:

>> SQLite is an embedded SQL database engine. Unlike most other SQL databases, 
>> SQLite does not have a separate server process. SQLite reads and writes 
>> directly to ordinary disk files. A complete SQL database with multiple 
>> tables, indices, triggers, and views, is contained in a single disk file. 
>> The database file format is cross-platform - you can freely copy a database 
>> between 32-bit and 64-bit systems or between big-endian and little-endian 
>> architectures. These features make SQLite a popular choice as an Application 
>> File Format. Think of SQLite not as a replacement for Oracle but as a 
>> replacement for fopen()

So I think it's referring to how SQLite stores its data in a local file, rather 
than on a remote server with which it communicates indirectly. ie "SQLite does 
not have a separate server process". In that way, SQLite is like fopen rather 
than Oracle. The same paragraphs mentions SQLite "triggers, and views", freely 
copying a [self contained] SQLite database between architectures, which allude 
to my point about putting the logic in the database itself so you can move the 
whole database between architectures.

> So, yes, you might as well just be saving your data to a CSV or XML file.  
> And I'm sure if you had a package to do that, someone would be arguing about 
> whether your XML should allow for embedded transforms.

What do you gain by implementing database logic in the application layer, when 
it could be done far more efficiently and reliably in the SQL schema? The only 
thing I can think of is avoiding the (shallow) learning curve. Why re-invent 
and roll your own integrity checking etc when it's already available and in a 
way much closer to the data than your application code can get?

See my previous post for the contrary argument:
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html

Basically, database logic in the database itself is much faster, causes less 
error, optimises queries, makes the database portable between application 
environments or GUI apps. What's not to love?

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Stored procedures

[BareFeetWare]

On 13/11/2010, at 10:33 AM, Olaf Schmidt wrote:

 From: "Olaf Schmidt"
 Wednesday, November 10, 2010 9:07:19 AM
 
>>> There was a somewhat similar sounding post (from BareFeetWare,
>>> sent on 20.Oct to this list) who also encouraged, to include
>>> "more logic" into the SQLite-Files itself, to reach more "portability".
>> 
>> That post of mine is archived here:
>> 
> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html
> 
> Yep, that was it.
> 
>> For the record, I (for BareFeetWare) was advocating implementing database 
>> logic in constraints and triggers, rather than in application code. I was 
>> not actually talking about stored procedures.

> After reading your posting again, it seems I had a "wrong image" in the back 
> of my mind, regarding your post, sorry.
> 
> Since you are talking about *database-logic* - our opinions do not differ 
> that much I'd say...

> ...use "DB-internal mechanisms" only in a range, which is common among 
> different DB-engines (Triggers for example, to ensure referential integrity 
> at least)."
> 
> And constraints, which I left out, belong into the same category, since they 
> are "common enough" too (among different DB-engines).

> Nothing wrong with Triggers and Constraints (the "database logic", as you 
> call it). Things which ensure (force) consistency on your Data belong into 
> the DB. They are usually well portable between different Engines (good 
> DB-Import-Tools can recognize and understand most of these - where importing 
> vendor-specific Stored-Procedures into another Backend can be adventurous at 
> least, if we talk about larger or complex procedures).

OK, we'll agree to... agree ;-) Database internal logic (integrity checks etc) 
are best handled in the database code (SQL), such as in constraints and 
triggers. I didn't know what you meant by "range".

> "business-logic" or "business layer", then that's something, what in my 
> opinion belongs into a shareable Component (usually a Dll), written
> in a normal language - but I think I already made my points, let's not repeat 
> them again - and it's after all only a personal opinion.

I guess we both agree that for business logic (as opposed to database logic, 
discussed above), a procedure of some kind is obviously required. In this 
situation, I advocate that as much as possible is done in SQL, by using "where" 
clauses and manipulating sets rather than using "if/then" branching structures 
and iterating through loops, respectively. This is more efficient and exploits 
SQL data storage optimisations. In fact I think that the availability of 
if/then and repeat loops in a database often entices people into bad habits of 
procedural programming where set programming should be used instead.

Do you have procedures that require branching structures? Can it not be better 
achieved by set logic?

If there's no other way than to have branching structures, then I think I see 
what you're saying: that it's better to take care of the branching structures 
in an external code layer than in an SQL procedural language (which SQLite 
doesn't have, currently). I think you're saying that SQL procedure branching 
structures aren't standard enough across SQL flavours to make it portable. In 
short, if you're more likely to change the choice of SQL flavor (eg SQLite to 
MySQL) than application code (eg perl vs python vs PHP vs Objective-C) then 
you're better off putting the branching structures in the application code. 
Correct? This choice would boil down to the likelihood of changing each (ie SQL 
flavor vs application code environment).

>> Why would we want to perform stored procedures?
> 
> Ehhmm, because a "bunch of well-organized and consistent data-records" is not 
> (yet) an Application? ;-)

LOL, yes, true. The application code has to drive the GUI or html input/output 
or whatever, but when the application code gets to the point of getting or 
setting data in the database, then it should ideally be done each time as one 
SQL call, rather than having a pile of application branches that makes several 
SQL calls.

>> If you have code (in either environment) that is looping or grabbing one 
>> result and sticking it in a second query, then your approach is probably 
>> flawed.

> As you say: "probably" ... because "it depends" ... (maybe your application 
> wants to show "details" in a delayed fashion - e.g. when a Node in a Treeview 
> gets "expanded").

Good example. At the point where the user requests expanded details (eg by 
clicking on a button), that obviously kicks off application code. As far as its 
interaction with the database, simplistically, it would have to do something 
like get the key values in the current row, check whether it exists in the 
details set, get the detail data. What I'm saying is that this should be done 
as one SQL call, which most likely doesn't require any branching logic within 
that call.

So it should look something like this, 

[sqlite] proxy locking

[Dave Dyer]

Suppose you have a sqlite DB which might be accessed over a network, and might 
also be accessed locally.  My understanding is that it would be disastrous if 
one client used proxy locking and the other did not - there would effectively 
be no lock.

The "prefer proxy locking" logic has a strange mode where it uses the runtime 
environment to override the default behavior, which is to use proxy locking 
only for remote file systems.   

I think this is too important to be left to a runtime environment variable.  If 
the application opens the database with SQLITE_OPEN_AUTOPROXY then proxy 
locking should be use unconditionally.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] improvement for the shell

[Dave Dyer]

The standard shell behaves very badly if you accidentally
select some BLOB data or excessively long strings.  I recommend
this change:


#define MAX_STRING_SIZE 200

static void cautious_print_string(FILE *f,char *str)
{   char buffer[MAX_STRING_SIZE+3];
int i;
int exit = 0;
for(i=0; !exit && (i=127)) { ch = '.'; };
break;
}
buffer[i]=ch;
}

if(i>=sizeof(buffer))   // filled the max buffer
{   i = sizeof(buffer)-1;
buffer[i--] = 0;
buffer[i--] = '.';
buffer[i--] = '.';
buffer[i--] = '.';
}
   fprintf(f, "%s", buffer);
}

and substitute

cautious_print_string(p->out,z);

for the fprintf in the display loop.

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

Re: [sqlite] Question about SQLite features.

[Olaf Schmidt]

"Chris Wolf" schrieb

[Nested Recordsets/Resultsets as an alternative to Joins,
 to "shape-off" redundancy in "hierarchical requests"...
 as a native DB-Feature usable over Sybase StoredProcs...
 ...and the ADO-ShapeProvider as an example for an
 alternative to use "these things" in a DBEngine-independent
 way (at least on the Win-Platform)]

> > If you request the construction of such a nested
> > Recordset at the serverside (over the Shape-
> > Provider), then there are no network-roundtrips
> > involved, in case the DB-Server and the
> > AppServer do run on the same machine.
> >
> > But we digress ... ;-)
> >
> In your scenario, here, even though the middle-tier
> ("business-layer-code") is collocated with
> the database, it looks to me like ADO is still a client-server
> technology (I'm relatively unfamiliar with it) you still need
> a "Connection" object to connect with the database, ...

You're basically right - ADO-Resultsets are the "storage-
container" (with an integrated Cursor-Engine), that
"looks always the same" to your Application-Code -
and the "ADO-Connection-Object" connects (involving
the OLEDB-driver) to the DBEngine in question.

And in case the DBengine runs on the same machine as
the OLEDB-Driver, then faster IPC-mechanisms
(than sockets) are usually involved.

> ...so I assume there's still a protocol stack, through which,
> the client-based "SHAPE" mechanism must make multiple
> (local) round trips.
The "protocoll-stack" is thin in this (local) case - but if
you want to put it this way, then yes - the SHAPE-Provider
will probably perform "multiple Requests" - how efficient
it does this (compared with "native Sybase") would need
to be tested.
At least I could imagine some Optimizations (working
over Joins, which include only the Parents Primary Key
in the Joined result, to avoid overboarding redundancy,
before the final Resultset is constructed and delivered
into the "hierarchical ADO-Recordset-container".

> Even though the ADO Connection is not as heavy-weight
> as HTTP, or even TCP/IP (I'm assuming for local connections
> it may be via named pipes) ...
...or Shared Memory...

> There's still serialization/deserialization of the client-server
> protocol stack.
No, would not think so - since most OLEDB-providers
(as well as the ADO-Recordsets) also support so
called "serverside cursors" - the "looping over incoming
records" can be done efficiently "in place", building the
hierarchy "on the fly" then (no larger buffer-conversion
or -swapping going on IMO).


> With stored procedures, the multiple open cursors to different
> tables are right there, running in the same process/thread
> space of that stored proc - no connection, no protocol
> stack, so it's going to be "much" faster.
I would not bet on your "much"-attribute that much... ;-)
After all there is an overhead of course - but the DB-engine
itself will need a larger amount of time internally, to perform
the "joining" (the key-lookups on the dependent tables).
How much faster native Sybase is, compared with the more
generic "ADO-Shaper" ... only a test can tell (in case you're
running on windows, this should be relative easy to do).

But the general point you made is understood I think -
Stored procedures, which do make use of builtin
specialities can (or better: usually) offer a better performance -
the question is, "how badly" an application is in need
of that advantage (or if it is large enough, to be noticable
at all).


Olaf



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

Re: [sqlite] Busy handler not called

[Jay A. Kreibich]

On Fri, Nov 12, 2010 at 04:51:11PM -0500, Prakash Reddy Bande scratched on the 
wall:
> Hi,
> 
> I have set a busy handler.
> 
> int ret = sqlite3_open(dbname.c_str(), _ppDb);
> sqlite3_busy_handler(m_ppDb, , 0);
> 
> However it is not getting called.

  As the docs for sqlite3_busy_handler() point out, this is exactly how
  it is expected to work:

  

  The presence of a busy handler does not guarantee that it will be
  invoked when there is lock contention. If SQLite determines that
  invoking the busy handler could result in a deadlock, it will go
  ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of
  invoking the busy handler. Consider a scenario where one process
  is holding a read lock that it is trying to promote to a reserved
  lock and a second process is holding a reserved lock that it is
  trying to promote to an exclusive lock. The first process cannot
  proceed because it is blocked by the second and the second
  process cannot proceed because it is blocked by the first. If
  both processes invoke the busy handlers, neither will make any
  progress. Therefore, SQLite returns SQLITE_BUSY for the first
  process, hoping that this will induce the first process to
  release its read lock and allow the second process to proceed.



> 1. Using the sqlite3.exe run the following commands
> begin transaction;
> update users set name="hello"
> 
> 2. Note I have not yet commited.

  This will grab the reserved lock, and knows it will want to get an
  exclusive to finish the transaction.

> From my program I call
> 
> int ret = sqlite3_exec(m_ppDb, query.c_str(), , , 
> );
> 
> I get SQLITE_BUSY and hwLMsqlite3BusyHandler is not getting called.

  You said this is another update, so same thing... Any attempt to
  promote to a reserved lock will sense a possible deadlock and
  skip the busy handler.  At this point, there is no way out of
  this situation other than having the second query rollback.
  Exec is likely to do this automatically, aborting the second
  UPDATE, and returning the original SQLITE_BUSY error.


-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] Stored procedures (was: Question about SQLite features.)

[Scott Hess]

On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare  wrote:
> IMO, if you're implementing database logic (ie constraints and triggers) in
> application code, then you're reinventing the wheel, making your package
> unnecessarily complex and grossly inefficient. If you're just using SQLite
> to store your data but doing all the logic outside of SQLite, then you might
> as well just be saving your data to a CSV file or XML. See my previous
> post for reasoning behind this.

>From http://www.sqlite.org/about.html :
> Think of SQLite not as a replacement for Oracle but as a replacement
> for fopen()

So, yes, you might as well just be saving your data to a CSV or XML
file.  And I'm sure if you had a package to do that, someone would be
arguing about whether your XML should allow for embedded transforms.

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

Re: [sqlite] Stored procedures (was: Question about SQLite features.)

[Olaf Schmidt]

"BareFeetWare"  schrieb
> On 12/11/2010, at 6:30 AM, Olaf Schmidt wrote:
>
> > "jeff archer"  schrieb
> >> From: "Olaf Schmidt"
> >> Wednesday, November 10, 2010 9:07:19 AM
> >>
> >>> [Stored procedures in SQLite]
> >>>
> >>> IMO stored procedure-support only makes
> >>> sense in "Server-Instances" which run on their own...
> >
> >> I disagree. The overall design and structure of applications
> >> using SQLite and therefor SQLite itself would benefit
> >> from SQLite supporting stored procedures.
> >> This would allow all code necessary for enforcing
> >> the business rules of the data to be stored in the
> >> database itself. This is just a good basic design principal.
> >
> > There was a somewhat similar sounding post (from BareFeetWare,
> > sent on 20.Oct to this list) who also encouraged, to include
> > "more logic" into the SQLite-Files itself, to reach more "portability".
>
> That post of mine is archived here:
>
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html

Yep, that was it.

> For the record, I (for BareFeetWare) was advocating implementing
> database logic in constraints and triggers, rather than in
> application code. I was not actually talking about stored
> procedures.
> In order of preference, I would implement logic in:
>
> 1. Constraints
> 2. Triggers
> 9. Pure SQL (eg stored procedures)
> 10. Application code (ie an external language that
> calls SQL snippets)

After reading your posting again, it seems I had a
"wrong image" in the back of my mind, regarding
your post, sorry.

Since you are talking about *database-logic* -
our opinions do not differ that much I'd say...
>From my posting you were replying to ... I wrote:
  "...use "DB-internal mechanisms" only in a
   range, which is common among different
   DB-engines (Triggers for example,
   to ensure referential integrity at least)."

And constraints, which I left out, belong into the
same category, since they are "common enough"
too (among different DB-engines).

Maybe I also misunderstood Jeff Archer in this
regard, but I think he meant a different thing with:
  "...code necessary for enforcing the business rules..."

If he meant "business-logic" or "business layer", then
that's something, what in my opinion belongs into
a shareable Component (usually a Dll), written
in a normal language - but I think I already made
my points, let's not repeat them again - and it's
after all only a personal opinion.

> IMO, if you're implementing database logic (ie constraints
> and triggers) in application code, then you're reinventing
> the wheel, ...
As said, that was not what I was "complaining about".

[layout of a handmade "stored procedure mechanism for the poor"
 in SQLite ;-)]

> But my question is: why?
>
> Why would we want to perform stored procedures?
> Or why would we want to perform application code,
> if you're on that side of the "war"? ;-)

Ehhmm, because a "bunch of well-organized and consistent
data-records" is not (yet) an Application? ;-)

> In most cases, I suggest that you should be implementing your
> database logic in constraints and triggers, not in procedural
> code (ie not in SQL store procedures and not in application code).
Here you go again... ;-)
Nothing wrong with Triggers and Constraints (the
"database logic", as you call it). Things which ensure
(force) consistency on your Data belong into the DB.
They are usually well portable between different
Engines (good DB-Import-Tools can recognize and
understand most of these - where importing vendor-
specific Stored-Procedures into another Backend
can be adventurous at least, if we talk about larger
or complex procedures).

> If you have code (in either environment) that is looping or
> grabbing one result and sticking it in a second query,
> then your approach is probably flawed.
As you say: "probably" ... because "it depends" ...
(maybe your application wants to show "details"
 in a delayed fashion - e.g. when a Node in a
 Treeview gets "expanded").

Anyways, hope my position is more clear to you now,
and - (since you made this request) - you feel
"enlightened enough" in the meantime .

Olaf



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

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

[Nicolas Williams]

On Tue, Oct 19, 2010 at 09:39:44AM -0400, Richard Hipp wrote:
> On Mon, Oct 18, 2010 at 8:19 AM,  wrote:
> 
> > I made an error in my SQL when I did not include one of my non-aggregate
> > columns in my group.  I was surprised that Sqlite did not catch this, and
> > even more surprised when the docs spelled out this behavior.
> >
> > Is everyone ok with this?
> > Do any other SQL engines allow this?
> > (DB2 does not)
> >
> 
> I was going to change this at one point, so that it raised an error, but
> that suggestion raised such an outcry that I decided to leave it.
> Apparently, there are many applications out there that depend on this
> behavior.
> 
> I think the big use case is as a substitute for DISTINCT.

Perhaps there should be aggregate functions whose purpose is pick the
first/last value of some expression/column as ordered by some
expression (including by random).

Something like:

SELECT average(a), agg_random(b) FROM some_table GROUP BY c;
SELECT average(a), agg_first(b ORDER BY b ASC) FROM some_table GROUP BY c;
SELECT average(a), agg_last(b ORDER BY rowid ASC) FROM some_table GROUP BY c;

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

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

[Alexey Pechnikov]

2010/11/11 Drake Wilson 

>
> It looks like you're right, and the resulting row selected is only
> arbitrary (though often the one with the largest rowid).  This
> suggests that unless I'm misunderstanding the comparison, comparing
> SQLite's behavior of permitting this type of SELECT with Postgres's
> DISTINCT ON (as an earlier post in this thread did) is misleading,
> since DISTINCT ON is guaranteed to be semantically after ORDER BY
> processing and therefore allows controlling which row from a group is
> selected, if one is careful.
>

Is needed the sub-query with "order by" for this.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Busy handler not called

[Prakash Reddy Bande]

Hi,

Basically I am doing the same query i.e.
int ret = sqlite3_exec(m_ppDb, "begin transaction", , 
, );

int ret = sqlite3_exec(m_ppDb, "update users set name=\"something\"", 
, , );

The second one return SQLITE_BUSY as expected (since begin transaction does no 
locking as per documentation of sqlite)

Regards,
 
Prakash Bande
Altair Engg. Inc. 
Troy MI
Ph: 248-614-2400 ext 489
Cell: 248-404-0292

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Friday, November 12, 2010 4:57 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Busy handler not called

> int ret = sqlite3_exec(m_ppDb, query.c_str(), , , 
> );
>
> I get SQLITE_BUSY and hwLMsqlite3BusyHandler is not getting called.

What query do you use?


Pavel

On Fri, Nov 12, 2010 at 4:51 PM, Prakash Reddy Bande
 wrote:
> Hi,
>
> I have set a busy handler.
>
> int ret = sqlite3_open(dbname.c_str(), _ppDb);
> sqlite3_busy_handler(m_ppDb, , 0);
>
> However it is not getting called. Here is what I am doing:
> 1. Using the sqlite3.exe run the following commands
> begin transaction;
> update users set name="hello"
>
> 2. Note I have not yet commited.
>
> From my program I call
>
> int ret = sqlite3_exec(m_ppDb, query.c_str(), , , 
> );
>
> I get SQLITE_BUSY and hwLMsqlite3BusyHandler is not getting called.
>
> I am using is 3.7.2
>
> Regards,
>
> Prakash Bande
> Altair Engg. Inc.
> Troy MI
> Ph: 248-614-2400 ext 489
> Cell: 248-404-0292
>
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Busy handler not called

[Pavel Ivanov]

> int ret = sqlite3_exec(m_ppDb, query.c_str(), , , 
> );
>
> I get SQLITE_BUSY and hwLMsqlite3BusyHandler is not getting called.

What query do you use?


Pavel

On Fri, Nov 12, 2010 at 4:51 PM, Prakash Reddy Bande
 wrote:
> Hi,
>
> I have set a busy handler.
>
> int ret = sqlite3_open(dbname.c_str(), _ppDb);
> sqlite3_busy_handler(m_ppDb, , 0);
>
> However it is not getting called. Here is what I am doing:
> 1. Using the sqlite3.exe run the following commands
> begin transaction;
> update users set name="hello"
>
> 2. Note I have not yet commited.
>
> From my program I call
>
> int ret = sqlite3_exec(m_ppDb, query.c_str(), , , 
> );
>
> I get SQLITE_BUSY and hwLMsqlite3BusyHandler is not getting called.
>
> I am using is 3.7.2
>
> Regards,
>
> Prakash Bande
> Altair Engg. Inc.
> Troy MI
> Ph: 248-614-2400 ext 489
> Cell: 248-404-0292
>
> ___
> 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] Busy handler not called

[Prakash Reddy Bande]

Hi,

I have set a busy handler.

int ret = sqlite3_open(dbname.c_str(), _ppDb);
sqlite3_busy_handler(m_ppDb, , 0);

However it is not getting called. Here is what I am doing:
1. Using the sqlite3.exe run the following commands
begin transaction;
update users set name="hello"

2. Note I have not yet commited.

>From my program I call

int ret = sqlite3_exec(m_ppDb, query.c_str(), , , 
);

I get SQLITE_BUSY and hwLMsqlite3BusyHandler is not getting called.

I am using is 3.7.2

Regards,

Prakash Bande
Altair Engg. Inc.
Troy MI
Ph: 248-614-2400 ext 489
Cell: 248-404-0292

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

Re: [sqlite] File Locking And Concurrency In SQLite Version 3

[Prakash Reddy Bande]

Hi,

Thanks for the response. That is what I was planning to do, i.e. write some 
code that does the verification before I call sqlite3_open.

However, I am not sure what code should I write. I mean the code I write to do 
the verification should be a representation of what sqlite3 does.

Regards,
 
Prakash Bande
Altair Engg. Inc. 
Troy MI
Ph: 248-614-2400 ext 489
Cell: 248-404-0292

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Friday, November 12, 2010 1:06 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] File Locking And Concurrency In SQLite Version 3


On 12 Nov 2010, at 5:58pm, Prakash Reddy Bande wrote:

> My question: Is it possible to verify if the network drive on which the DB is 
> place can be used?

Great question.  Unfortunately LockFile() generally does /not/ work.  If you 
want to perform a check there are too many combinations of access methods and 
version numbers to use logic.  I can only recommend you write some code that 
actually tries to do the locking and checks to see whether it worked.

I have an application which does something similar in a Unix setup.  It 
requires that the user runs two test applications at the same time -- either on 
the same computer or on two different computers.

Simon.
___
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] Constraint name?

[Petite Abeille]

On Nov 12, 2010, at 8:18 PM, Roger Binns wrote:

> Your definition of "proper" appears to be some ISO standard :-)

Nah, not specially fond of that ISO standard, more for illustration purpose 
really.

> SQLite's approach is certainly acceptable to most (evidence: if not
> there would be a lot of requests for improvement).

Fair enough, I guess...  rather annoying that one cannot select from pragmas 
though, which make them unusable in a query :/

> But again SQLite does provide the functionality if you want to implement
> this yourself.  For example you can implement a set of virtual tables.

Yes, yes, write my own, I know, I know :))




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

Re: [sqlite] Ratio of test code to database code

[Samuel Adam]

On Fri, 12 Nov 2010 13:09:07 -0500, Simon Slavin   
wrote:

> On 12 Nov 2010, at 4:25pm, Dr. David Kirkby wrote:
>
>> I think in general mathematical software like Sage is very difficult to  
>> test.
>
> Yes, I read your response and agree with this.  But it's worth pointing  
> out that commercial maths applications like MatLab /do/ have testing  
> like that.  That's one of the reasons they're so expensive.  I'm sure  
> they don't test every possible value, but they are thorough and they are  
> run for every point version.

I think I know what you mean, and I have no wish to be snide hereby; yet  
resist the obvious, I cannot:

If considering the set of real numbers, wouldn’t testing “every possible  
value” require at least א -sub-1 tests?  With א -sub-1 tests betwixt each  
test?  Ad infinitum, literally?

Someone please answer this urgent question, as I am bad at math and I’d  
like to get to the bottom of it.

free(OT); return 0; }

Samuel Adam 
763 Montgomery Road
Hillsborough, NJ  08844-1304
United States
http://certifound.com/

([0] …with extraneous spaces because I doubt everybody’s MUA supports BIDI  
overrides properly.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange corruptions

[Pirmin Walthert]

Am 12.11.2010 14:40, schrieb Pirmin Walthert:
> Am 12.11.2010 14:19, schrieb Black, Michael (IS):
>> Do a "sum" on the files to make sure they are identical.
>>
>> #1 Show all the files in the directorty
>> #2 How are you copying?
>>
>> Basically...show us ALL the commands and files you are using...
>>
>>
>> Michael D. Black
>> Senior Scientist
>> Advanced Analytics Directorate
>> Northrop Grumman Information Systems
>>
>>
>> 
>>
>> From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert
>> Sent: Fri 11/12/2010 6:42 AM
>> To: sqlite-users@sqlite.org
>> Subject: EXTERNAL:Re: [sqlite] Strange corruptions
>>
>>
>>
>> Am 12.11.2010 13:06, schrieb Simon Slavin:
>>> On 12 Nov 2010, at 7:55am, Pirmin Walthert wrote:
>>>
 Some months ago we changed to uclibc-git (nptl support), kernel
 2.6.32.X, busybox>1.16 and at the moment sqlite 3.7.2.
>>> Are you accessing your databases straight from a hard disk or across a 
>>> network mount ?
>>>
>>> Please tell us the filing system (either hard disk FS or network FS) you're 
>>> using.
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> Both (the one with the source and the one with the dst database) are
>> local (ext3 loopback fs). I doubt that it has to do with the FS because
>> if do the following, the same thing happens:
>>
>> - copy the corrupted DB to /tmp (tmpfs)
>> - checking the db with sqlite3 /tmp/baddb "PRAGMA integrity_check;" =>
>> this still shows me ok
>> - making a backup of /tmp/baddb to /tmp/backupdb (or whatever)
>> - checking the destionation db now gives me the same errors again
>>
>> Pirmin
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>>
>> ___
>> sqlite-users mailing list~ # md5sum /mnt/sipbad.db
> here you have a sequence that works and one that doesn't work with
> exactly the same file. once without vacuum, once with vacuum
>
> a343370269988b912d1efdf02ffcbcd1  /mnt/sipbad.db
> ~ # cp /mnt/sipbad.db /tmp/copy.db
> ~ # md5sum /tmp/copy.db
> a343370269988b912d1efdf02ffcbcd1  /tmp/copy.db
> ~ # sqlite3 /tmp/copy.db "PRAGMA integrity_check;"
> ok
> ~ # sqlite3 /tmp/copy.db ".backup main /tmp/backup.db"
> ~ # sqlite3 /tmp/backup.db  "PRAGMA integrity_check;"
> *** in database main ***
> On page 26 at right child: invalid page number 954
> On tree page 21 cell 0: invalid page number 956
> ~ # ls -l /tmp/copy.db
> -rw-r--r--1 root root984064 Nov 12 14:28 /tmp/copy.db
> ~ # ls -l /tmp/backup.db
> -rw-r--r--1 root root984064 Nov 12 14:29 /tmp/backup.db
> ~ # sqlite3 /tmp/copy.db "PRAGMA integrity_check;"
> ok
> ~ # md5sum /tmp/copy.db
> a343370269988b912d1efdf02ffcbcd1  /tmp/copy.db
> ~ # md5sum /tmp/backup.db
> 1b0c6d02b5851e707267903da39a2d0c  /tmp/backup.db
>
>
> ~ # sqlite3 /tmp/copy.db "vacuum"
> ~ # md5sum /tmp/copy.db
> 716555badc876d4e4ae452c741c41bfd  /tmp/copy.db
> ~ # sqlite3  /tmp/copy.db "PRAGMA integrity_check;"
> ok
> ~ # sqlite3 /tmp/copy.db ".backup main /tmp/backup.db"
> ~ # sqlite3 /tmp/backup.db  "PRAGMA integrity_check;"
> ok
> ~ # md5sum /tmp/backup.db
> 9e65a7c2683083a5d36f3f58af587f1d  /tmp/backup.db
>
> oh well. You also want an output of all files in the directory ;) well I
> don't know how this could help, but here you have it ;)
>
> ~ # ls -l /tmp/
> -rw-r--r--1 root root  2925 Nov 12 09:27 Master.csv
> -rw-r--r--1 root root968704 Nov 12 14:33 backup.db
> -rw-r--r--1 root root968704 Nov 12 14:31 copy.db
> -rw-r--r--1 root root  5174 Nov 12 14:13 dbCheck
> -rw-r--r--1 root root59 Nov  5 11:35 defRoute
> -rwxr-xr-x1 root root  7436 Nov  5 11:35 netScript.sh
> -rwxr-xr-x1 root root   252 Nov 12 14:35 tc.sh
> -rw-r--r--1 root root509952 Nov 12 14:36 temp.db
> -rw-r--r--1 root root   455 Nov  5 11:35 udhcpc.lease
> -rw-r--r--1 root root 0 Nov 12 11:35 udhcpc.log
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


More info:

I copied the file to my notebook (Ubuntu 10.10, sqlite 3.7.2, 64bit, 
while the other system is a 32bit system). Here exactly the same thing 
happens like on the uclibc/busybox system. (integrity_check on the src 
db shows ok, the .backup command gives no error but the integrity_check 
on the backup-file shows errors).

No I copied exactly the same file to a CPE that still runs the old 
version (sqlite 3.6.23). Here everything works as expected:

~ # md5sum /tmp/bad.db
a343370269988b912d1efdf02ffcbcd1  /tmp/bad.db
~ # sqlite3 

Re: [sqlite] Constraint name?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/12/2010 10:04 AM, Petite Abeille wrote:
> Thanks for that. Since 2006? High priority indeed :)

That the ticket averages a small number of comments per year is an
indication that perhaps its priority is about right.  Many other ticket
tracking systems do have voting features so at least people can chime in
with 'me toos'.

> That said, maybe SQLite has reached a point where it would benefit from a 
> proper data dictionary of sort,

Note that is unrelated to your original issue.  No matter how fancy a
data dictionary there is, it won't affect getting the name of a failed
constraint out.  (The technical reason is that under the hood all the
constraints are anded together so detecting which one failed would
require re-running each one separately.)

> But altogether, nothing really comprehensive, nor very accessible (i.e. 
> multitude of non queryable pragmas or raw DDL). 
> 
> Has anyone attempted to outfit SQLite with a proper data dictionary? 

Your definition of "proper" appears to be some ISO standard :-)
SQLite's approach is certainly acceptable to most (evidence: if not
there would be a lot of requests for improvement).  Also remember that
SQLite is not used on a server with lots of different databases,
hundreds of tables each, potentially huge numbers of users, dedicated
paid support staff etc.

But again SQLite does provide the functionality if you want to implement
this yourself.  For example you can implement a set of virtual tables.
This is very easy to do: plug - my Python wrapper:

  http://apidoc.apsw.googlecode.com/hg/vtable.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkzdkvAACgkQmOOfHg372QQaxACgn7VBLSOxYc9mtKAeHnuWIrIU
hT8AoK8GZ8AsAili0R52OFl+opaPe2pW
=ugAe
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] uncertainty how load_extension is supposed to work

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/12/2010 09:08 AM, Chris Wolf wrote:
> If so, does that mean we can't use the out-of-the box shell and must
> re-compile with a 'C' code change to invoke this?  If that's true, why wasn't 
> this
> simply controlled via an environment variable?

C code has to enable extension loading.  If you use the SQLite shell
then it always calls the C function to enable extension loading.
Remember that SQLite is always a library in part of a larger process.
It is up to that process to decide policy on extension with the safe
default being to have it disabled.

> Assuming this *had* worked, I assume the shared library naming convention is
> that of the dlopen library call,

Correct.  Look for UnixDlOpen and WinDlOpen in the SQLite source where
you see that they just directly call dlopen and LoadLibraryW.  To my
knowledge there is no convention for file names for SQLite extensions.
My test extension has the imaginative filename under all operating
systems of testextension.sqlext.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkzdhfUACgkQmOOfHg372QT9AACcDS4Qmtoc93lggQyF5krhMThq
Db8An24hI6UDBNwuazQjEHeUqmbcc3MR
=+B/F
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE3 - sqlite3_step bug?

[Richard Hipp]

On Fri, Nov 12, 2010 at 1:06 PM, David Levinson wrote:

>  I have an 11GB database and when I attempt to query the database for the
> max(column) value the code within sqlite3_step() gets stuck in a loop for
> hours and hours and never seems to return.
>
>
>
> Do you know why this is the case and how it can be resolved? I stepped into
> the code and all I see page allocations getting created over and over and
> over. I just updated to the latest sqlite3 updates and the issue continues.
>
>
>
> My sense is that this is something related to 64 bit indexes. Could this
> be?
>

If column is not indexed, then SQLite has to loop through all 11GB of your
database file looking for the maximum value.  That can take time (though
measured in 10s of seconds, not hours).

What does it say if you prepend EXPLAIN QUERY PLAN to the beginning of your
query?





-- 
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] Ratio of test code to database code

[Simon Slavin]

On 12 Nov 2010, at 4:25pm, Dr. David Kirkby wrote:

> I think in general mathematical software like Sage is very difficult to test. 

Yes, I read your response and agree with this.  But it's worth pointing out 
that commercial maths applications like MatLab /do/ have testing like that.  
That's one of the reasons they're so expensive.  I'm sure they don't test every 
possible value, but they are thorough and they are run for every point version.

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

Re: [sqlite] File Locking And Concurrency In SQLite Version 3

[Simon Slavin]

On 12 Nov 2010, at 5:58pm, Prakash Reddy Bande wrote:

> My question: Is it possible to verify if the network drive on which the DB is 
> place can be used?

Great question.  Unfortunately LockFile() generally does /not/ work.  If you 
want to perform a check there are too many combinations of access methods and 
version numbers to use logic.  I can only recommend you write some code that 
actually tries to do the locking and checks to see whether it worked.

I have an application which does something similar in a Unix setup.  It 
requires that the user runs two test applications at the same time -- either on 
the same computer or on two different computers.

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

Re: [sqlite] Constraint name?

[Petite Abeille]

On Nov 12, 2010, at 7:20 AM, Roger Binns wrote:

>> A ticket that has been open on this topic since January 2006.
> 
> Oops, forgot to paste:
> 
>  http://www.sqlite.org/src/tktview?name=23b2128201

Thanks for that. Since 2006? High priority indeed :)

That said, maybe SQLite has reached a point where it would benefit from a 
proper data dictionary of sort, perhaps something along the lines of ANSI ISO 
9075 Information Schema and Definition Schema (aka INFORMATION_SCHEMA) [1] or 
such . Something a bit more full fledged than the one on the wiki though [2] :)

Right now, there are bits and pieces here and there:

pragma database_list ~= information_schema.schemata
sqlite_[temp_]master ~= information_schema.tables
pragma table_info ~= information_schema.columns
pragma foreign_key_list ~= 
information_schema.referential_constraints/key_column_usage

Other pieces of information could be inferred from pragma 
index_list/index_info. And that's about it at first glance.

But altogether, nothing really comprehensive, nor very accessible (i.e. 
multitude of non queryable pragmas or raw DDL). 

Has anyone attempted to outfit SQLite with a proper data dictionary? 


[1] http://en.wikipedia.org/wiki/Information_schema 
[2] http://www.sqlite.org/cvstrac/wiki?p=InformationSchema

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

Re: [sqlite] Database deleted from file system while other open connections exist.

[Simon Slavin]

On 12 Nov 2010, at 4:44pm, Dennis Suehr wrote:

> Firstly, a bit more background information.  The system will be running on 
> Linux (kernel > 2.6.0) and all accesses to the database will be via local 
> disk (ext3 or similar).

This allows me to be a little more precise about my answers, I think.

> 1) I appreciate your explanation about any any open connections to the file 
> still being able to write to it until they close it.  I suppose that 'fuser' 
> could be used to send a SIGTERM or similar to the processes holding open file 
> handles.  I will have to give some additional thought to how I go about this 
> 'delete' operation. 

Yes, you can certainly kill any processes holding the file open.  I assume your 
processes will be intelligent about how they respond to SIGTERM, issuing 
sqlite3_close() if but only if appropriate.  Otherwise you will have trouble 
with journal files and/or WAL files being left behind.
 
> 2) Regarding, SQLite and open handles to a database, that is fair enough that 
> it does not have a mechanism send alerts to all processes connected to a 
> given DB.  However, I am surprised that it does not at least possess a list 
> of all open handles to a given database.  If I had that, then I could close 
> all DB connections either before the delete or after.  Are you sure that such 
> a list does not exist?  Wouldn't it use it for mechanisms like 'unlock 
> notify' and 'busy handler', etc?

SQLite is an unusual SQL engine in that it has no server/client architecture.  
In most SQL engines, for example MySQL, all the real work is done by a server 
process, and all applications just communicate with this process: they have no 
contact with the files on the disk.  In SQLite, all applications are peers: 
they actually handle the disk file and don't know about one-another.  No one 
process runs the show, and they communicate only by using the file system's 
locking routines.  All one process knows is that some part of the disk file is 
locked.  It doesn't know what locked it or even whether it was locked by a 
SQLite library call or by some arbitrary application treating that file as 
bytes.

> 3) Finally, am I correct in thinking that if I used 'pragma locking 
> exclusive' that I can lock the entire database even after closing my 
> connection?

Closing your connection releases the lock.  (The mechanism used is just the 
normal file locking one and it's unusual for an OS to let you lock a file you 
don't have open.)   For more information see

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

Just in passing, all locks in SQLite are locks of the entire database.  SQLite 
doesn't lock individual rows or ranges of rows.  So that's a reason not to use 
locking when you don't need to.

If you are doing serious concurrent multi-process access, then you might want 
to explore other solutions.  You might want to use a SQL engine with a 
server/client architecture.  Or instead of deleting the disk file you might 
want to not shuffle files at all: issue a 'DELETE FROM tableName' which is very 
fast, and use the form of INSERT that copies your data from another table in 
another database you are accessing using ATTACH.

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

[sqlite] File Locking And Concurrency In SQLite Version 3

[Prakash Reddy Bande]

Hello,

I want to use sqlite as a db on a shared network drive (windows) (user would 
map the network drive to say T:). I read the documentation section "File 
Locking And Concurrency In SQLite Version 3" 
(http://www.sqlite.org/lockingv3.html) 


Where following is clearly mentioned:
"SQLite uses POSIX advisory locks to implement locking on Unix. On Windows it 
uses the LockFile(), LockFileEx(), and UnlockFile() system calls. SQLite 
assumes that these system calls all work as advertised. If that is not the 
case, then database corruption can result. One should note that POSIX advisory 
locking is known to be buggy or even unimplemented on many NFS implementations 
(including recent versions of Mac OS X) and that there are reports of locking 
problems for network filesystems under Windows. Your best defense is to not use 
SQLite for files on a network filesystem."

My question: Is it possible to verify if the network drive on which the DB is 
place can be used? What I would like to do is when user tries to load the db 
(sqlite3_open) first run the verification and inform the user that database is 
placed on a drive that does not support the locking mechanism required by our 
product.

I am using sqlite-3.7.2

Regards,
 
Prakash Bande
Altair Engg. Inc. 
Troy MI
Ph: 248-614-2400 ext 489
Cell: 248-404-0292
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database deleted from file system while other open connections exist.

[Pavel Ivanov]

> However, I am surprised that it does not at least possess a
> list of all open handles to a given database.  If I had that, then I could
> close all DB connections either before the delete or after.  Are you sure
> that such a list does not exist?

If SQLite was able to obtain such list (not in the same process but
among several different processes, remember?) that would be a serious
security breach in the kernel. Let alone the possibility of closing
file handles in other processes.

> Wouldn't it use it for mechanisms like
> 'unlock notify' and 'busy handler', etc?

It's implemented using simple polling technique: try to lock; if it's
unsuccessful sleep for some small time; try to lock again and repeat
that until lock is successful or the total waiting time is too long.


Pavel

On Fri, Nov 12, 2010 at 11:44 AM, Dennis Suehr  wrote:
> Simon,
>
> Many thanks for your prompt and thorough response.
>
> Just a couple of follow-up questions if I may:
>
> Firstly, a bit more background information.  The system will be running on
> Linux (kernel > 2.6.0) and all accesses to the database will be via local
> disk (ext3 or similar).
>
> 1) I appreciate your explanation about any any open connections to the file
> still being able to write to it until they close it.  I suppose that 'fuser'
> could be used to send a SIGTERM or similar to the processes holding open
> file handles.  I will have to give some additional thought to how I go about
> this 'delete' operation.
>
> 2) Regarding, SQLite and open handles to a database, that is fair enough
> that it does not have a mechanism send alerts to all processes connected to
> a given DB.  However, I am surprised that it does not at least possess a
> list of all open handles to a given database.  If I had that, then I could
> close all DB connections either before the delete or after.  Are you sure
> that such a list does not exist?  Wouldn't it use it for mechanisms like
> 'unlock notify' and 'busy handler', etc?
>
> 3) Finally, am I correct in thinking that if I used 'pragma locking
> exclusive' that I can lock the entire database even after closing my
> connection?
>
> Cheers,
>
> Dennis
> ___
> 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] uncertainty how load_extension is supposed to work

[Chris Wolf]

I tried to explicitly load an extension via:

sqlite> select load_extension('mylib');
SQL error: no such function: load_extension


Is this because of the default setting of "enable_load_extension"?
http://www.sqlite.org/c3ref/enable_load_extension.html

If so, does that mean we can't use the out-of-the box shell and must
re-compile
with a 'C' code change to invoke this?  If that's true, why wasn't this
simply
controlled via an environment variable?

Assuming this *had* worked, I assume the shared library naming convention is
that of the dlopen library call, although this behavior may not jive
with Windows.  Maybe it's like Java's LoadLibrary?  i.e. ignore
the file extension (.so, .dll, .dylib) and drop the "lib" prefix.  

e.g. to load "libmylib.dylib" on Mac, it's:
select load_extension('mylib');

...which would correspond to "libmylib.dll" on Windows, etc.

Is that how it is?  

Can it be documented on this page? :

http://www.sqlite.org/lang_corefunc.html#load_extension


Thanks,


   -Chris


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

Re: [sqlite] Question about SQLite features.

[Petite Abeille]

On Nov 11, 2010, at 9:38 PM, Ian Hardingham wrote:

> Haha! Sqlite is embedded by others. It NEVER embeds.

SQLite's tagline of the week :P

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

Re: [sqlite] uncertainty how load_extension is supposed to work

[Richard Hipp]

On Fri, Nov 12, 2010 at 12:08 PM, Chris Wolf  wrote:

> I tried to explicitly load an extension via:
>
> sqlite> select load_extension('mylib');
> SQL error: no such function: load_extension
>

Use the ".load" command in the sqlite3.exe command-line shell.


>
>
> Is this because of the default setting of "enable_load_extension"?
> http://www.sqlite.org/c3ref/enable_load_extension.html
>
> If so, does that mean we can't use the out-of-the box shell and must
> re-compile
> with a 'C' code change to invoke this?  If that's true, why wasn't this
> simply
> controlled via an environment variable?
>
> Assuming this *had* worked, I assume the shared library naming convention
> is
> that of the dlopen library call, although this behavior may not jive
> with Windows.  Maybe it's like Java's LoadLibrary?  i.e. ignore
> the file extension (.so, .dll, .dylib) and drop the "lib" prefix.
>
> e.g. to load "libmylib.dylib" on Mac, it's:
> select load_extension('mylib');
>
> ...which would correspond to "libmylib.dll" on Windows, etc.
>
> Is that how it is?
>
> Can it be documented on this page? :
>
> http://www.sqlite.org/lang_corefunc.html#load_extension
>
>
> Thanks,
>
>
>   -Chris
>
>
> ___
> 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] Database deleted from file system while other open connections exist.

[Dennis Suehr]

Simon,

Many thanks for your prompt and thorough response.

Just a couple of follow-up questions if I may:

Firstly, a bit more background information.  The system will be running on
Linux (kernel > 2.6.0) and all accesses to the database will be via local
disk (ext3 or similar).

1) I appreciate your explanation about any any open connections to the file
still being able to write to it until they close it.  I suppose that 'fuser'
could be used to send a SIGTERM or similar to the processes holding open
file handles.  I will have to give some additional thought to how I go about
this 'delete' operation.

2) Regarding, SQLite and open handles to a database, that is fair enough
that it does not have a mechanism send alerts to all processes connected to
a given DB.  However, I am surprised that it does not at least possess a
list of all open handles to a given database.  If I had that, then I could
close all DB connections either before the delete or after.  Are you sure
that such a list does not exist?  Wouldn't it use it for mechanisms like
'unlock notify' and 'busy handler', etc?

3) Finally, am I correct in thinking that if I used 'pragma locking
exclusive' that I can lock the entire database even after closing my
connection?

Cheers,

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

Re: [sqlite] Ratio of test code to database code

[Dr. David Kirkby]

On 11/11/10 02:32 PM, Simon Slavin wrote:
>
> On 11 Nov 2010, at 1:41pm, Dr. David Kirkby wrote:
>
>> On 11/10/10 04:28 PM, Roger Binns wrote:
>>
>>> The SQLite developers decided their library will always be reliable and
>>> greatly care about data integrity hence the amount of testing.
>>
>> I wish the Sage developers would take as much care. One recently said 
>> something
>> to the effect of "I'd rather not spend hours worrying about how code might 
>> fail,
>> when it is so easy to create patches when someone reports a bug"
>
> Well, you just put me off on using Sage for the foreseeable future.

The situation is improving. The main developer is seeing the need to improve 
quality, and is organising to just get bugs fixed. There is also a commerical 
company willing to provide test software to find more bugs. Standards of coding 
are improving too.

> Bugs do not get spotted frequently in complicated maths software like Sage.  
> The vast majority of users put numbers in, get numbers out, and assume the 
> software works correctly.  Bugs are rarely even spotted, much less reported, 
> unless the numbers get graphed and the graph looks wrong, or when the error 
> is so big the result falls outside a plausible range (e.g. a percentage 
> bigger than 100%).  Almost no users will report bugs even if they do find 
> them if you make the bug-reporting process too annoying.

I don't think the bug reporting system is annoying. Just email will do. We 
intending making the reporting of bug anonymous too. Likewise, the test suite, 
which has a few thousands tests, will hopefully soon be reported automatically.

> I would have hoped that the tests for Sage version increments were as good as 
> the ones for SQLite.

I'm afraid they are not. I don't think there's the appetite for such extensive 
testing among a sufficient number of developers.

I very much doubt the commercial software packages (Maple, Mathematica, MATLAB 
etc) have such extensive testing. Certainly automated testing by one individual 
has uncovered thousands of bugs in the commercial software.

I think in general mathematical software like Sage is very difficult to test. 
Whilst some things are easy to test, others are far less so. You can supply an 
input, and get an output, but there's no way to verify if that output is 
correct 
or not.

Where possible, I also test on some of the rarer platforms like AIX and HP-UX.

We always test on Solaris SPARC, Solaris x86, OpenSolaris, Linux and OS X.

> I worked with share dealing software for years.  A mistake in our code could 
> have cost a dealer millions of dollars.  Designing the test code was part of 
> designing any new feature: this is what the data will look like, this is how 
> the user-interface will work, this is how it'll talk to other systems and 
> here are the things we can test to make sure it's working right.  A factor of 
> 1:1 (lines of code in the module vs. lines of code in the test module) was 
> not unusual, but it could easily be 30:1 or 1:30 depending on how ingenious 
> we were feeling at the design stage and how many kinds of unexpected input we 
> felt like inserting specific tests for.  Unfortunately fuzzing tests weren't 
> invented until after I left that company but we'd have loved them.

Again, I think the testing is probaby easier than with very complex maths 
software.

> Our customers loved us because we packaged the test suite with the 
> application.  When auditors came around the customer could show the auditor 
> all the tests 'they'd' run for bad input, calculation overflows, etc. and the 
> auditors would go away impressed.

Sage does too. We have thousands of tests. But there are certainly inputs for 
which I don't believe one can verify if the output is correct - that said, I'm 
not a mathematician.

Sometimes the outputs are correct, but not in the simplest possible form.

> Simon.


Dave

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

Re: [sqlite] btreeInitPage() returns error code 11

[Wilson, Ronald]

> > I've got a 500 mb database with one table that I set up to log system
> > information over night and I arrive this morning to find this situation.
> >  The database was generated on windows using vbscript and the sqliteodbc
> > driver based on sqlite 3.7.2.  Do I have any hope of recovery?
> 
> Probably not.  I'll bet if you look at the database file beginning at page
> 140481 you see that it has been overwritten with something that is not
> database information, perhaps with all zeros.
> 
> You can probably use the ".dump" utility to extract most or all of the
> information prior to page 140481, though.  So you can get some of your
> database back.  Have you tried that?

Thank you.  That recovered 5.5 hours of data; or about 1/3 of the dataset.  I 
can work with that.

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division 
assuredcommunications(tm)


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

Re: [sqlite] btreeInitPage() returns error code 11

[Richard Hipp]

On Fri, Nov 12, 2010 at 10:31 AM, Wilson, Ronald wrote:

> I've got a 500 mb database with one table that I set up to log system
> information over night and I arrive this morning to find this situation.
>  The database was generated on windows using vbscript and the sqliteodbc
> driver based on sqlite 3.7.2.  Do I have any hope of recovery?
>

Probably not.  I'll bet if you look at the database file beginning at page
140481 you see that it has been overwritten with something that is not
database information, perhaps with all zeros.

You can probably use the ".dump" utility to extract most or all of the
information prior to page 140481, though.  So you can get some of your
database back.  Have you tried that?


>
> SQLite version 3.6.22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> pragma integrity_check;
> *** in database main ***
> Page 140481: btreeInitPage() returns error code 11
> On tree page 140458 cell 65: Child page depth differs
> Page 140482: btreeInitPage() returns error code 11
> Page 140483: btreeInitPage() returns error code 11
> Page 140484: btreeInitPage() returns error code 11
> Page 140485: btreeInitPage() returns error code 11
> Page 140486: btreeInitPage() returns error code 11
> Page 140487: btreeInitPage() returns error code 11
> Page 140488: btreeInitPage() returns error code 11
> Page 140489: btreeInitPage() returns error code 11
> Page 140490: btreeInitPage() returns error code 11
> Page 140491: btreeInitPage() returns error code 11
> Page 140492: btreeInitPage() returns error code 11
> Page 140493: btreeInitPage() returns error code 11
> Page 140494: btreeInitPage() returns error code 11
> Page 140495: btreeInitPage() returns error code 11
> Page 140496: btreeInitPage() returns error code 11
> Page 140497: btreeInitPage() returns error code 11
> Page 140498: btreeInitPage() returns error code 11
> Page 140499: btreeInitPage() returns error code 11
> Page 140500: btreeInitPage() returns error code 11
> Page 140501: btreeInitPage() returns error code 11
> Page 140502: btreeInitPage() returns error code 11
> Page 140503: btreeInitPage() returns error code 11
> Page 140504: btreeInitPage() returns error code 11
> Page 140505: btreeInitPage() returns error code 11
> Page 140506: btreeInitPage() returns error code 11
> Page 140507: btreeInitPage() returns error code 11
> Page 140508: btreeInitPage() returns error code 11
> Page 140509: btreeInitPage() returns error code 11
> Page 140510: btreeInitPage() returns error code 11
> Page 140511: btreeInitPage() returns error code 11
> Page 140512: btreeInitPage() returns error code 11
> Page 140513: btreeInitPage() returns error code 11
> Page 140514: btreeInitPage() returns error code 11
> On tree page 143459 cell 12: Child page depth differs
> Page 140515: btreeInitPage() returns error code 11
> Page 140516: btreeInitPage() returns error code 11
> Page 140517: btreeInitPage() returns error code 11
> Page 140518: btreeInitPage() returns error code 11
> Page 140519: btreeInitPage() returns error code 11
> Page 140520: btreeInitPage() returns error code 11
> Page 140521: btreeInitPage() returns error code 11
> Page 140522: btreeInitPage() returns error code 11
> Page 140523: btreeInitPage() returns error code 11
> Page 140524: btreeInitPage() returns error code 11
> Page 140525: btreeInitPage() returns error code 11
> Page 140526: btreeInitPage() returns error code 11
> Page 140527: btreeInitPage() returns error code 11
> Page 140528: btreeInitPage() returns error code 11
> Page 140529: btreeInitPage() returns error code 11
> Page 140530: btreeInitPage() returns error code 11
> Page 140531: btreeInitPage() returns error code 11
> Page 140532: btreeInitPage() returns error code 11
> Page 140533: btreeInitPage() returns error code 11
> Page 140534: btreeInitPage() returns error code 11
> Page 140535: btreeInitPage() returns error code 11
> Page 140536: btreeInitPage() returns error code 11
> Page 140537: btreeInitPage() returns error code 11
> Page 140538: btreeInitPage() returns error code 11
> Page 140539: btreeInitPage() returns error code 11
> Page 140540: btreeInitPage() returns error code 11
> Page 140541: btreeInitPage() returns error code 11
> Page 140542: btreeInitPage() returns error code 11
> Page 140543: btreeInitPage() returns error code 11
> Page 140544: btreeInitPage() returns error code 11
> On tree page 140558 cell 30: Child page depth differs
> On tree page 143459 cell 13: Child page depth differs
> Page 161860: btreeInitPage() returns error code 11
> On tree page 163261 cell 28: Child page depth differs
> Page 161917: btreeInitPage() returns error code 11
> Page 161918: btreeInitPage() returns error code 11
> Page 161919: btreeInitPage() returns error code 11
> Page 161920: btreeInitPage() returns error code 11
> On tree page 161960 cell 4: Child page depth differs
> On tree page 163261 cell 29: Child page depth differs
> Page 162113: btreeInitPage() returns 

[sqlite] btreeInitPage() returns error code 11

[Wilson, Ronald]

I've got a 500 mb database with one table that I set up to log system 
information over night and I arrive this morning to find this situation.  The 
database was generated on windows using vbscript and the sqliteodbc driver 
based on sqlite 3.7.2.  Do I have any hope of recovery?

SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma integrity_check;
*** in database main ***
Page 140481: btreeInitPage() returns error code 11
On tree page 140458 cell 65: Child page depth differs
Page 140482: btreeInitPage() returns error code 11
Page 140483: btreeInitPage() returns error code 11
Page 140484: btreeInitPage() returns error code 11
Page 140485: btreeInitPage() returns error code 11
Page 140486: btreeInitPage() returns error code 11
Page 140487: btreeInitPage() returns error code 11
Page 140488: btreeInitPage() returns error code 11
Page 140489: btreeInitPage() returns error code 11
Page 140490: btreeInitPage() returns error code 11
Page 140491: btreeInitPage() returns error code 11
Page 140492: btreeInitPage() returns error code 11
Page 140493: btreeInitPage() returns error code 11
Page 140494: btreeInitPage() returns error code 11
Page 140495: btreeInitPage() returns error code 11
Page 140496: btreeInitPage() returns error code 11
Page 140497: btreeInitPage() returns error code 11
Page 140498: btreeInitPage() returns error code 11
Page 140499: btreeInitPage() returns error code 11
Page 140500: btreeInitPage() returns error code 11
Page 140501: btreeInitPage() returns error code 11
Page 140502: btreeInitPage() returns error code 11
Page 140503: btreeInitPage() returns error code 11
Page 140504: btreeInitPage() returns error code 11
Page 140505: btreeInitPage() returns error code 11
Page 140506: btreeInitPage() returns error code 11
Page 140507: btreeInitPage() returns error code 11
Page 140508: btreeInitPage() returns error code 11
Page 140509: btreeInitPage() returns error code 11
Page 140510: btreeInitPage() returns error code 11
Page 140511: btreeInitPage() returns error code 11
Page 140512: btreeInitPage() returns error code 11
Page 140513: btreeInitPage() returns error code 11
Page 140514: btreeInitPage() returns error code 11
On tree page 143459 cell 12: Child page depth differs
Page 140515: btreeInitPage() returns error code 11
Page 140516: btreeInitPage() returns error code 11
Page 140517: btreeInitPage() returns error code 11
Page 140518: btreeInitPage() returns error code 11
Page 140519: btreeInitPage() returns error code 11
Page 140520: btreeInitPage() returns error code 11
Page 140521: btreeInitPage() returns error code 11
Page 140522: btreeInitPage() returns error code 11
Page 140523: btreeInitPage() returns error code 11
Page 140524: btreeInitPage() returns error code 11
Page 140525: btreeInitPage() returns error code 11
Page 140526: btreeInitPage() returns error code 11
Page 140527: btreeInitPage() returns error code 11
Page 140528: btreeInitPage() returns error code 11
Page 140529: btreeInitPage() returns error code 11
Page 140530: btreeInitPage() returns error code 11
Page 140531: btreeInitPage() returns error code 11
Page 140532: btreeInitPage() returns error code 11
Page 140533: btreeInitPage() returns error code 11
Page 140534: btreeInitPage() returns error code 11
Page 140535: btreeInitPage() returns error code 11
Page 140536: btreeInitPage() returns error code 11
Page 140537: btreeInitPage() returns error code 11
Page 140538: btreeInitPage() returns error code 11
Page 140539: btreeInitPage() returns error code 11
Page 140540: btreeInitPage() returns error code 11
Page 140541: btreeInitPage() returns error code 11
Page 140542: btreeInitPage() returns error code 11
Page 140543: btreeInitPage() returns error code 11
Page 140544: btreeInitPage() returns error code 11
On tree page 140558 cell 30: Child page depth differs
On tree page 143459 cell 13: Child page depth differs
Page 161860: btreeInitPage() returns error code 11
On tree page 163261 cell 28: Child page depth differs
Page 161917: btreeInitPage() returns error code 11
Page 161918: btreeInitPage() returns error code 11
Page 161919: btreeInitPage() returns error code 11
Page 161920: btreeInitPage() returns error code 11
On tree page 161960 cell 4: Child page depth differs
On tree page 163261 cell 29: Child page depth differs
Page 162113: btreeInitPage() returns error code 11
On tree page 162060 cell 95: Child page depth differs
Page 162114: btreeInitPage() returns error code 11
Page 162115: btreeInitPage() returns error code 11
Page 162116: btreeInitPage() returns error code 11
On tree page 163261 cell 30: Child page depth differs
Page 162160: btreeInitPage() returns error code 11
On tree page 163261 cell 31: Child page depth differs
Page 162217: btreeInitPage() returns error code 11
Page 162218: btreeInitPage() returns error code 11
Page 162219: btreeInitPage() returns error code 11
Page 162220: btreeInitPage() returns error code 11
Page 162221: btreeInitPage() returns error 

Re: [sqlite] INSERT OR UPDATE

[BareFeetWare]

On 10/11/2010, at 7:19 PM, Michele Pradella wrote:

> In-Reply-To:  <4cda28ea.5030...@gmail.com>


Firstly, please start a post to this mail list as a new message, not a reply to 
a previous unrelated message. That's known as "thread hijacking" and confuses 
discussions.

> Hi all, I have to INSERT a row in a DB but I have first to check if the Key 
> I'm inserting already exist.

Good question.

> Now I'm doing a "SELECT count..." first to check if the key exist and then 
> INSERT or UPDATE records.

I can see two problems with this approach:

1. It sounds like you're executing an SQL select, then dropping out of SQL to 
perform a logic test on the result, then executing a new SQL statement 
depending on the logic result. It's more efficient and reliable to do it all in 
SQL.

2. Using "select count" will test the entire table, even if the key exists in 
the first row it tests. You should use "in" or "exists" instead of "count". The 
hit is reduced if the column is indexed, but it's still poor SQL.

> Do you know  if there's a better or faster way to do that?

You could:

begin immediate
;
update MyTable set Detail = newDetail where ID = newID
;
insert into MyTable(ID, Detail) select newID, newDetail where newID not in 
(select ID from MyTable)
;
commit
;

It may be faster to change the insert line to:

insert into MyTable(ID, Detail) select newID, newDetail where changes() > 0 and 
newID not in (select ID from MyTable)

but I don't know, since I haven't really played with changes().

Alternatively, you could use "insert or ignore" or "insert or replace" 
depending on what you want to do if the key exists. Note that "insert or 
replace" will actually delete the old row and insert a new one, something that 
can make a mess if, for instance, you wanted to keep the value of other columns 
or you have internal database logic that is initiated by deletes and inserts, 
so I tend to avoid it.

HTH,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Question about SQLite features.

[jeff archer]

I hope this doesn't get posted twice.  I don't think it will since I sent form 
wrong account first time.  Sorry.

>From: "Kees Nuyt" 
>Thursday, November 11, 2010 10:34:51 AM
> 
>Stored procedures don't enforce business rules by
>themselves. Constraints and triggers do.
>To enforce business rules stored as procedures in the
>database, one would need an access system which prevents
>direct modification of table data. 
>As an embedded database SQLite definately is in an other
>niche of the DBMS market.
>Stored procedures can often be replaced by INSTEAD OF
>triggers on views, that's quite powerful.

OK, they can't truly enforce business rules but nothing enforces good design on 
my C++ code either.
What it does is allow the business rules to be packaged as a complete unit with 
the data and I claim that would be better design and more maintainable over the 
life of the project.  


Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Stored procedures (was: Question about SQLite features.)

[BareFeetWare]

On 12/11/2010, at 6:30 AM, Olaf Schmidt wrote:

> "jeff archer"  schrieb
>> From: "Olaf Schmidt"
>> Wednesday, November 10, 2010 9:07:19 AM
>> 
>>> [Stored procedures in SQLite]
>>> 
>>> IMO stored procedure-support only makes sense in "Server-Instances" which 
>>> run on their own...
> 
>> I disagree. The overall design and structure of applications using SQLite 
>> and therefor SQLite itself would benefit from SQLite supporting stored 
>> procedures. This would allow all code necessary for enforcing the business 
>> rules of the data to be stored in the database itself. This is just a good 
>> basic design principal.
> 
> There was a somewhat similar sounding post (from BareFeetWare, sent on 20.Oct 
> to this list) who also encouraged, to include "more logic" into the 
> SQLite-Files itself, to reach more "portability".

That post of mine is archived here:
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html

For the record, I (for BareFeetWare) was advocating implementing database logic 
in constraints and triggers, rather than in application code. I was not 
actually talking about stored procedures. In order of preference, I would 
implement logic in:

1. Constraints
2. Triggers
9. Pure SQL (eg stored procedures)
10. Application code (ie an external language that calls SQL snippets)

IMO, if you're implementing database logic (ie constraints and triggers) in 
application code, then you're reinventing the wheel, making your package 
unnecessarily complex and grossly inefficient. If you're just using SQLite to 
store your data but doing all the logic outside of SQLite, then you might as 
well just be saving your data to a CSV file or XML. See my previous post for 
reasoning behind this.

Now, as for stored procedures (which I wasn't discussing before), I will chime 
in on this new discussion:

It is fairly trivial to add your own table to your database for storing 
procedures, such as:

create table "Procedures"
(   ID integer primary key
,   Name text unique
,   Description text
,   "SQL" text
)
;

In this you can store and name SQL procedures, using whatever external 
application code to simply get the SQL of the desired Procedure by Name and 
execute it. It will run as one connection within SQLite, utilise the begin 
commit/rollback mechanism etc. You can combine, say, an update and select, to 
update one table based on results from another very efficiently totally within 
SQL, without reinjecting the results of one query into a second query via an 
application code layer. And so on.

Such an after market stored procedure mechanism isn't as good as could be 
achieved via a native stored procedure facility built into SQLite, but it 
fulfils many of the objectives. It does lack variables, procedural nesting etc, 
but those can usually be accomplished by a set theory approach (which is 
desirable anyway) and a similarly contrived "Variables" table. It also has to 
convert raw text SQL into object/machine code on each call.

But my question is: why?

Why would we want to perform stored procedures? Or why would we want to perform 
application code, if you're on that side of the "war"? ;-)

In most cases, I suggest that you should be implementing your database logic in 
constraints and triggers, not in procedural code (ie not in SQL store 
procedures and not in application code). If you have code (in either 
environment) that is looping or grabbing one result and sticking it in a second 
query, then your approach is probably flawed. You really should be approaching 
the data as sets and not something over which code should be iterated.

Having said that, there are some needs for procedural code that aren't just 
misplaced and inefficient attempts to implementing internal logic. For example, 
I have procedural scripts that:

1. Import data from one table to another (eg in a different database).
2. Periodically clean, flush or delete redundant or outdated entries from a 
logging table
3. Change the data in one table to match a data set proposed by best estimate 
calculations (eg budget forecasts or assignments based on most recent choices)

They do implement "business logic" but not what I would call "database logic". 
These procedures are actions initiated by the user or business schedule, such 
as importing a new sales catalog, end of cycle data cleansing, or a preliminary 
entry of "best guess" data before the data entry operator or bookkeeper manual 
enters hundreds of records. These procedures are triggered by an external 
event. By contrast, database logic implements data integrity and is initiated 
by an internal event such as an update, insert or delete.

If your experience differs to this, please enlighten me. Do you have or need 
procedures (either stored SQL or in application code) that aren't one of:

1. Enforce data integrity such as restricting entries or cascading changes, 
which could/should be instead accomplished through constraints and 

Re: [sqlite] Strange corruptions

[Pirmin Walthert]

Am 12.11.2010 14:19, schrieb Black, Michael (IS):
> Do a "sum" on the files to make sure they are identical.
>
> #1 Show all the files in the directorty
> #2 How are you copying?
>
> Basically...show us ALL the commands and files you are using...
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert
> Sent: Fri 11/12/2010 6:42 AM
> To: sqlite-users@sqlite.org
> Subject: EXTERNAL:Re: [sqlite] Strange corruptions
>
>
>
> Am 12.11.2010 13:06, schrieb Simon Slavin:
>> On 12 Nov 2010, at 7:55am, Pirmin Walthert wrote:
>>
>>> Some months ago we changed to uclibc-git (nptl support), kernel
>>> 2.6.32.X, busybox>   1.16 and at the moment sqlite 3.7.2.
>> Are you accessing your databases straight from a hard disk or across a 
>> network mount ?
>>
>> Please tell us the filing system (either hard disk FS or network FS) you're 
>> using.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> Both (the one with the source and the one with the dst database) are
> local (ext3 loopback fs). I doubt that it has to do with the FS because
> if do the following, the same thing happens:
>
> - copy the corrupted DB to /tmp (tmpfs)
> - checking the db with sqlite3 /tmp/baddb "PRAGMA integrity_check;" =>
> this still shows me ok
> - making a backup of /tmp/baddb to /tmp/backupdb (or whatever)
> - checking the destionation db now gives me the same errors again
>
> Pirmin
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> ___
> sqlite-users mailing list~ # md5sum /mnt/sipbad.db

here you have a sequence that works and one that doesn't work with 
exactly the same file. once without vacuum, once with vacuum

a343370269988b912d1efdf02ffcbcd1  /mnt/sipbad.db
~ # cp /mnt/sipbad.db /tmp/copy.db
~ # md5sum /tmp/copy.db
a343370269988b912d1efdf02ffcbcd1  /tmp/copy.db
~ # sqlite3 /tmp/copy.db "PRAGMA integrity_check;"
ok
~ # sqlite3 /tmp/copy.db ".backup main /tmp/backup.db"
~ # sqlite3 /tmp/backup.db  "PRAGMA integrity_check;"
*** in database main ***
On page 26 at right child: invalid page number 954
On tree page 21 cell 0: invalid page number 956
~ # ls -l /tmp/copy.db
-rw-r--r--1 root root984064 Nov 12 14:28 /tmp/copy.db
~ # ls -l /tmp/backup.db
-rw-r--r--1 root root984064 Nov 12 14:29 /tmp/backup.db
~ # sqlite3 /tmp/copy.db "PRAGMA integrity_check;"
ok
~ # md5sum /tmp/copy.db
a343370269988b912d1efdf02ffcbcd1  /tmp/copy.db
~ # md5sum /tmp/backup.db
1b0c6d02b5851e707267903da39a2d0c  /tmp/backup.db


~ # sqlite3 /tmp/copy.db "vacuum"
~ # md5sum /tmp/copy.db
716555badc876d4e4ae452c741c41bfd  /tmp/copy.db
~ # sqlite3  /tmp/copy.db "PRAGMA integrity_check;"
ok
~ # sqlite3 /tmp/copy.db ".backup main /tmp/backup.db"
~ # sqlite3 /tmp/backup.db  "PRAGMA integrity_check;"
ok
~ # md5sum /tmp/backup.db
9e65a7c2683083a5d36f3f58af587f1d  /tmp/backup.db

oh well. You also want an output of all files in the directory ;) well I 
don't know how this could help, but here you have it ;)

~ # ls -l /tmp/
-rw-r--r--1 root root  2925 Nov 12 09:27 Master.csv
-rw-r--r--1 root root968704 Nov 12 14:33 backup.db
-rw-r--r--1 root root968704 Nov 12 14:31 copy.db
-rw-r--r--1 root root  5174 Nov 12 14:13 dbCheck
-rw-r--r--1 root root59 Nov  5 11:35 defRoute
-rwxr-xr-x1 root root  7436 Nov  5 11:35 netScript.sh
-rwxr-xr-x1 root root   252 Nov 12 14:35 tc.sh
-rw-r--r--1 root root509952 Nov 12 14:36 temp.db
-rw-r--r--1 root root   455 Nov  5 11:35 udhcpc.lease
-rw-r--r--1 root root 0 Nov 12 11:35 udhcpc.log


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

Re: [sqlite] Strange corruptions

[Black, Michael (IS)]

Do a "sum" on the files to make sure they are identical.
 
#1 Show all the files in the directorty
#2 How are you copying?
 
Basically...show us ALL the commands and files you are using...
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert
Sent: Fri 11/12/2010 6:42 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] Strange corruptions



Am 12.11.2010 13:06, schrieb Simon Slavin:
> On 12 Nov 2010, at 7:55am, Pirmin Walthert wrote:
>
>> Some months ago we changed to uclibc-git (nptl support), kernel
>> 2.6.32.X, busybox>  1.16 and at the moment sqlite 3.7.2.
> Are you accessing your databases straight from a hard disk or across a 
> network mount ?
>
> Please tell us the filing system (either hard disk FS or network FS) you're 
> using.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Both (the one with the source and the one with the dst database) are
local (ext3 loopback fs). I doubt that it has to do with the FS because
if do the following, the same thing happens:

- copy the corrupted DB to /tmp (tmpfs)
- checking the db with sqlite3 /tmp/baddb "PRAGMA integrity_check;" =>
this still shows me ok
- making a backup of /tmp/baddb to /tmp/backupdb (or whatever)
- checking the destionation db now gives me the same errors again

Pirmin
___
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] Strange corruptions

[Simon Slavin]

On 12 Nov 2010, at 12:42pm, Pirmin Walthert wrote:

> Both (the one with the source and the one with the dst database) are 
> local (ext3 loopback fs). I doubt that it has to do with the FS because 
> if do the following, the same thing happen

That's fine.  I was just testing out a current theory of mine but your setup 
does not include it.

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

Re: [sqlite] Question about SQLite features.

[Chris Wolf]

Olaf Schmidt wrote:
> "Chris Wolf"  schrieb
>
>   
>> I can't resist adding my little opinion to yet another
>> "business logic in stored procs vs. app layer" holy war...
>> 
>
> ... yeah, seems this thread is evolving nicely in this
> regard ...
>
>   
>> I usually prefer keeping the business logic in the application
>> layer and leaving the DB tier to be just a data provider.
>> In actual practice, this is not always practical.
>>
>> Let's say you implement a service where the client can
>> retrieve a set of top-level records,
>> each has an id; then for each id, you get additional
>> detail records from numerous detail/line-item tables.
>> If you implement this as a collection of fine-grained services,
>> i.e. each piece is a round trip from client, through web
>> services layer, through to db layer; and for each top-level id
>> in the result set - the performance will be abysmal.
>>
>> With Sybase stored procs, you can stack multiple result
>> sets in one call, so in the above scenario, you invoke the
>> lookup proc for each top-level id and the proc performs
>> all the secondary detail queries and stacks it all together
>> in a multiple-results  result-set, such that there's only one
>> round-trip through the tiers for each top-level id in the set.
>> 
>
> But that is a common problem, which can be solved
> directly in the business-layer-code at the serverside
> as well, also achieving a "stacked serialisation
> of resultsets" in one roundtrip.
> One can either code such a thing (a stacked serialization)
> "by hand" (based on XML for example, which is well-nestable) -
> or on a given platform (e.g. on Windows) one can avoid
> these hand-coded parts by making use of one of the
> already mentioned "DB-abstraction-helpers".
> E.g. ADO does support so called "shaped, hierarchical
> Recordsets" for a long time now ... over the "DataShape-
> Provider" which is part of ADO (and plays together with
> a lot of OLEDB-providers from different vendors...
> not sure if the Sybase-Provider is one of those, which is
> "Shape-Provider-capable").
> http://support.microsoft.com/kb/189657
>
> If you request the construction of such a nested
> Recordset at the serverside (over the Shape-
> Provider), then there are no network-roundtrips
> involved, in case the DB-Server and the
> AppServer do run on the same machine.
>
> But we digress ... ;-)
>
>   
In your scenario, here, even though the middle-tier
("business-layer-code") is collocated with
the database, it looks to me like ADO is still a client-server
technology (I'm relatively
unfamiliar with it) you still need a "Connection" object to connect with
the database,
so I assume there's still a protocol stack, through which, the
client-based "SHAPE" mechanism must make
multiple (local) round trips.   Even though the ADO Connection is not as
heavy-weight
as HTTP, or even TCP/IP (I'm assuming for local connections it may be
via named pipes)
There's still serialization/deserialization of the client-server
protocol stack. 

With stored procedures, the multiple open cursors to different tables
are right there,
running in the same process/thread space of that stored proc - no
connection, no protocol
stack, so it's going to be "much" faster.

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

Re: [sqlite] Strange corruptions

[Pirmin Walthert]

Am 12.11.2010 13:06, schrieb Simon Slavin:
> On 12 Nov 2010, at 7:55am, Pirmin Walthert wrote:
>
>> Some months ago we changed to uclibc-git (nptl support), kernel
>> 2.6.32.X, busybox>  1.16 and at the moment sqlite 3.7.2.
> Are you accessing your databases straight from a hard disk or across a 
> network mount ?
>
> Please tell us the filing system (either hard disk FS or network FS) you're 
> using.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Both (the one with the source and the one with the dst database) are 
local (ext3 loopback fs). I doubt that it has to do with the FS because 
if do the following, the same thing happens:

- copy the corrupted DB to /tmp (tmpfs)
- checking the db with sqlite3 /tmp/baddb "PRAGMA integrity_check;" => 
this still shows me ok
- making a backup of /tmp/baddb to /tmp/backupdb (or whatever)
- checking the destionation db now gives me the same errors again

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

Re: [sqlite] Question about SQLite features.

[Olaf Schmidt]

"Chris Wolf"  schrieb

> I can't resist adding my little opinion to yet another
> "business logic in stored procs vs. app layer" holy war...

... yeah, seems this thread is evolving nicely in this
regard ...

> I usually prefer keeping the business logic in the application
> layer and leaving the DB tier to be just a data provider.
> In actual practice, this is not always practical.
>
> Let's say you implement a service where the client can
> retrieve a set of top-level records,
> each has an id; then for each id, you get additional
> detail records from numerous detail/line-item tables.
> If you implement this as a collection of fine-grained services,
> i.e. each piece is a round trip from client, through web
> services layer, through to db layer; and for each top-level id
> in the result set - the performance will be abysmal.
>
> With Sybase stored procs, you can stack multiple result
> sets in one call, so in the above scenario, you invoke the
> lookup proc for each top-level id and the proc performs
> all the secondary detail queries and stacks it all together
> in a multiple-results  result-set, such that there's only one
> round-trip through the tiers for each top-level id in the set.

But that is a common problem, which can be solved
directly in the business-layer-code at the serverside
as well, also achieving a "stacked serialisation
of resultsets" in one roundtrip.
One can either code such a thing (a stacked serialization)
"by hand" (based on XML for example, which is well-nestable) -
or on a given platform (e.g. on Windows) one can avoid
these hand-coded parts by making use of one of the
already mentioned "DB-abstraction-helpers".
E.g. ADO does support so called "shaped, hierarchical
Recordsets" for a long time now ... over the "DataShape-
Provider" which is part of ADO (and plays together with
a lot of OLEDB-providers from different vendors...
not sure if the Sybase-Provider is one of those, which is
"Shape-Provider-capable").
http://support.microsoft.com/kb/189657

If you request the construction of such a nested
Recordset at the serverside (over the Shape-
Provider), then there are no network-roundtrips
involved, in case the DB-Server and the
AppServer do run on the same machine.

But we digress ... ;-)

> I don't see how this is pertinent to SQLite, since it's
> generally not used in multi-user client-server configurations.


I'm using it here in (smaller to midsized) multi-user scenarios -
my COM-wrapper (Dll) contains fast serialization-mechanisms
and a built-in AppServer for that "mode".

And I know that some Readers of this list also use
SQLite behind Servers (mostly over http then)
(the Fossil-repository is just another example).

These solutions are certainly not "large enterprise-stuff" -
but reliable and fast enough for ones own "special
applications".

Olaf



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

Re: [sqlite] Strange corruptions

[Simon Slavin]

On 12 Nov 2010, at 7:55am, Pirmin Walthert wrote:

> Some months ago we changed to uclibc-git (nptl support), kernel 
> 2.6.32.X, busybox > 1.16 and at the moment sqlite 3.7.2.

Are you accessing your databases straight from a hard disk or across a network 
mount ?

Please tell us the filing system (either hard disk FS or network FS) you're 
using.

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

[sqlite] Strange corruptions

[Pirmin Walthert]

Hello

I'm working on a CPE project where we use sqlite for configuration 
storage. We develop our own firmware which is based on uclibc and busybox.

We never had troubles with the sqlite-databases during the first 1.5 
years (kernel 2.6.27 series, uclibc 0.9.30, busybox 1.14.1, sqlite up to 
3.6.23). Some months ago we changed to uclibc-git (nptl support), kernel 
2.6.32.X, busybox > 1.16 and at the moment sqlite 3.7.2. Each 30 minutes 
a backup of the config-database is made if there was a change. This is 
done using the SQLite Backup API. With the new version we have quite 
often (on different machines) the same strange behaviour:

- sqlite3 /databasePath "PRAGMA integrity_check;" will give ok
- a backup is made without an error showing up (directly through our own 
binary using the backup api or by using the ".backup" function of the 
sqlite3 command line tool. We always delete the destination file before 
doing the backup
- checking the new database with PRAGMA integrity_check will give an 
error like:

On page 26 at right child: invalid page number 954
On tree page 21 cell 0: invalid page number 956

I played a bit with a database that seems to be ok where the backup is 
always corrupted and can reproduce rather strange things:

- "vaccum" the source database before doing the backup fixes the error
- deleting a row from a table (no matter what table!) in the source 
database also fixes the error
- "vaccum" the corrupted destination database also fixes the error

Does anybody have some ideas what could be wrong here? Should I 
downgrade to a 3.6.X version?

Maybe you will blame the git version of uclibc... I've to mention here 
that we run quite a lot of programs (some of them quite thread-oriented) 
on this platform without any troubles (samba, php, openvpn, asterisk and 
many others) and the uclibc-developers are discussing about making a 
0.9.32 release based on this code during the next weeks.

Best regards,

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

Re: [sqlite] Question about SQLite features.

[Ian Hardingham]

Haha! Sqlite is embedded by others. It NEVER embeds.


- Original message -
> 
> On Nov 10, 2010, at 11:05 AM, Andy Gibbs wrote:
> 
> > > That's I don't know SQLite have stored procedure support?
> > > 
> > 
> > How're your C skills?
> 
> Or perhaps SQLite should embed Lua [1] as its powerful, fast,
> lightweight, scripting language and be done with it :)
> 
> [1] http://www.lua.org/about.html
> 
> ___
> 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