Re: [sqlite] Question about SQLite features.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/11/2010 06:26 AM, jeff archer wrote:
> The overall design and structure of applications using SQLite and
> therefor SQLite itself would benefit from SQLite supporting stored 
> procedures.  

SQLite includes mechanisms to implement almost anything, without forcing
particular policies.  For example it is relatively trivial to embed in
almost any programming language.  It is very easy to add your own functions,
collations and other forms of extension.  The code is public domain so it
means there is no restriction on you or anyone else making whatever changes
you want and distributing them however you want.  (The only restriction is
you can't call the result SQLite.)  In this example you can use the
auto_extension mechanism plus storing script code in the database to get a
lot of the way towards your goal.

Also consider that currently SQLite database files are safe - they are just
data.  You can have no fear of taking random SQLite data files off the net
and loading them into any program.  Adding some form of stored procedures
means that code can now run, and that code could be malicious (eg causing
the allocation of lots of memory, infinite loops to consume all cpu and not
return control back to the caller).

SQLite takes compatibility very seriously.  A program written and linking
against SQLite 3.0.0 will work today without recompilation against a dll
compiled with the latest SQLite code.  (A recompilation will also succeed.)

Anything added to SQLite is a commitment to update it, maintain it, test it
etc till SQLite 3 is end of lifed.  There are often requests to add things
to the SQLite core, but the Lite is there for a reason and the main job of
the developers is to say "no" - as in "small, fast, reliable - pick any three".

So why don't you add stored procedures to SQLite to demonstrate their
utility? And if you aren't a coder then convince coders to help you.  The
pool of people collaborating and using them would be a good argument towards
including the functionality in the core.

On the other hand if what you are really arguing is that the SQLite
developers should do this work for you, then I'm sure they'd be happy to
give you a quote.  Start here:

  http://www.hwaci.com/sw/sqlite/prosupport.html#mods

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

iEYEARECAAYFAkzc4usACgkQmOOfHg372QTcLACfQvu4sLjtf7ufoEHAVqUb+Y0D
cVYAniNn1OLI+uHcDFhVowDCDOXtAg8K
=NEhw
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Constraint name?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/11/2010 09:40 PM, 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

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

iEYEARECAAYFAkzc3MIACgkQmOOfHg372QS1HwCfU1uaHjsSzBDEVBQZs6fto3na
cWoAn1MS5UaYtLyW9J5PL/56ueAP8tpB
=rDbS
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Constraint name?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/11/2010 11:12 AM, Petite Abeille wrote:
> Is there a way, short of parsing the original DDL, to retrieve a constraint 
> name?
[..]
> How does one retrieve the unique constraint name, "foo_uk"?

A ticket that has been open on this topic since January 2006.

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

iEYEARECAAYFAkzc008ACgkQmOOfHg372QQ/PwCeOnP/uTGfD64q4ftx3opsjnxL
LbAAoOAoBdxBCMuNuYB0nnCS1O11152J
=bttU
-END PGP SIGNATURE-
___
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:
> "Petite Abeille" schrieb
>   
>> On Nov 11, 2010, at 8:30 PM, Olaf Schmidt wrote:
>>
>> 
>>> If such an "encapsulation of business-rules" is sitting in the
>>> DB itself - written in a proprietary "DB-dialect", then you
>>> cannot call such a thing a "business-layer" anymore.
>>>   
>> Nonsense :))
>> 
>
> Of course... ;-)
>
> Nah, seriously... you know, how I meant that and what
> the context was ... *if* somebody decides to handle
> DB-Interaction (and his "set of business-rules") *not*
> directly "in the client" (or alternatively "in the DB-Server") -
> then he obviously does so, to decouple the Client-Application
> from the DB(-Backend) - allowing then (if done right),
> to "connect" this intermediate layer to different clientside
> Implementations (GUIs) - as well as different DB-Backends.
>
> Encapsulated in a Dll with the right interfaces, one can
> use it either serverside (e.g. behind a WebServer, to
> talk to Browser-Clients - delivering JSON- or XML-
> serialized Resultset-Content) - or behind a "real AppServer",
> to talk to "Fat Clients" (delivering Resultset-Content
> in a serialized "Object-Container" - as for example
> disconnected ADO-Recordsets on Windows, which
> are then understood by a large Set of languages, easily
> bindable with mostly only one line of code to a
> DataGrid or whatever GUI-Widget).
> Heck, you can put such a layer-Dll even at the clientside,
> to support a standalone App or alternatively a more
> Client/Server-like approach, capable to work against
> a different set of DB-engines even then (in the standalone
> App for example, against SQLite).
>
>
> ["Helsinki Declaration(s)"...]
> Cannot disagree more with these articles, sorry.
> >From my experience his main-assumption is just
> not true, that it is more difficult to develop such
> a layer with "modern languages or environments" -
> compared with a proprietary DB-dialect and
> some DB-specific enhancements or features.
>
> Perhaps you should give an example of a certain
> stored procedure (not too complex, to keep
> things more simple), describe what it does - and
> then compare it with the implementaion-code,
> done in a "normal language", which does use
> ODBC/JDBC/ADO or whatever and is using
> only "common SQL-statements", to achieve
> the same thing in a DB-engine-independent way?
>
>
> Olaf
>
>
>   

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

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.

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

-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.

[Olaf Schmidt]

"Petite Abeille" schrieb
>
> On Nov 11, 2010, at 8:30 PM, Olaf Schmidt wrote:
>
> > If such an "encapsulation of business-rules" is sitting in the
> > DB itself - written in a proprietary "DB-dialect", then you
> > cannot call such a thing a "business-layer" anymore.
>
> Nonsense :))

Of course... ;-)

Nah, seriously... you know, how I meant that and what
the context was ... *if* somebody decides to handle
DB-Interaction (and his "set of business-rules") *not*
directly "in the client" (or alternatively "in the DB-Server") -
then he obviously does so, to decouple the Client-Application
from the DB(-Backend) - allowing then (if done right),
to "connect" this intermediate layer to different clientside
Implementations (GUIs) - as well as different DB-Backends.

Encapsulated in a Dll with the right interfaces, one can
use it either serverside (e.g. behind a WebServer, to
talk to Browser-Clients - delivering JSON- or XML-
serialized Resultset-Content) - or behind a "real AppServer",
to talk to "Fat Clients" (delivering Resultset-Content
in a serialized "Object-Container" - as for example
disconnected ADO-Recordsets on Windows, which
are then understood by a large Set of languages, easily
bindable with mostly only one line of code to a
DataGrid or whatever GUI-Widget).
Heck, you can put such a layer-Dll even at the clientside,
to support a standalone App or alternatively a more
Client/Server-like approach, capable to work against
a different set of DB-engines even then (in the standalone
App for example, against SQLite).


["Helsinki Declaration(s)"...]
Cannot disagree more with these articles, sorry.
>From my experience his main-assumption is just
not true, that it is more difficult to develop such
a layer with "modern languages or environments" -
compared with a proprietary DB-dialect and
some DB-specific enhancements or features.

Perhaps you should give an example of a certain
stored procedure (not too complex, to keep
things more simple), describe what it does - and
then compare it with the implementaion-code,
done in a "normal language", which does use
ODBC/JDBC/ADO or whatever and is using
only "common SQL-statements", to achieve
the same thing in a DB-engine-independent way?


Olaf



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

Re: [sqlite] Question about SQLite features.

[P Kishor]

On Thu, Nov 11, 2010 at 5:36 PM, Petite Abeille
 wrote:
>
> On Nov 12, 2010, at 12:31 AM, Jay A. Kreibich wrote:
>
>>  There have been many proposals to do just this, and in specific,
>>  with Lua.  Outside of some moderate technical issues, the
>>  big problem is the license.  Something like that would *never*
>>  be part of the SQLite core because the Lua license is not
>>  compatible with SQLite's license-- i.e. it is not in the public
>>  domain.
>
> hmmm... not wanting to go down lawyer lane, but... isn't Lua's MIT license as 
> liberal as it gets? Just curious.
>

Public domain is "as liberal as it gets." Nothing else is.

-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
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 12, 2010, at 12:31 AM, Jay A. Kreibich wrote:

>  There have been many proposals to do just this, and in specific,
>  with Lua.  Outside of some moderate technical issues, the
>  big problem is the license.  Something like that would *never*
>  be part of the SQLite core because the Lua license is not
>  compatible with SQLite's license-- i.e. it is not in the public
>  domain.

hmmm... not wanting to go down lawyer lane, but... isn't Lua's MIT license as 
liberal as it gets? Just curious.

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

Re: [sqlite] Question about SQLite features.

[Jay A. Kreibich]

On Thu, Nov 11, 2010 at 09:05:15PM +0100, Petite Abeille scratched on the wall:

> Or perhaps SQLite should embed Lua [1] as its powerful, fast,
> lightweight, scripting language and be done with it :)

  There have been many proposals to do just this, and in specific,
  with Lua.  Outside of some moderate technical issues, the
  big problem is the license.  Something like that would *never*
  be part of the SQLite core because the Lua license is not
  compatible with SQLite's license-- i.e. it is not in the public
  domain.

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

[Drake Wilson]

Quoth Petite Abeille , on 2010-11-11 21:05:15 +0100:
> Or perhaps SQLite should embed Lua [1] as its powerful, fast, lightweight, 
> scripting language and be done with it :)

Interestingly enough, there's problems with doing that with stock Lua:
SQLite insists on having both 64-bit integers and doubles, and stock
Lua only provides the latter, so (for instance) representing rowids
properly becomes a pain.  Recompiling to use integers instead of
floats is easy, but having both is nontrivial, though there exist
patches such as LNUM that will give you a variant numeric type.

I'd also be cautious about possible platforms where isolating the
resultant symbols from a host program that might be using an
incompatible Lua would be difficult.  Being a library results in
different constraints on dependencies than for an out-of-process
database engine, unfortunately.

These aren't unsolvable, but it's a little harder than it might look.

   ---> Drake Wilson
___
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 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

Re: [sqlite] Question about SQLite features.

[Petite Abeille]

On Nov 11, 2010, at 8:30 PM, Olaf Schmidt wrote:

> If such an "encapsulation of business-rules" is sitting in the
> DB itself - written in a proprietary "DB-dialect", then you
> cannot call such a thing a "business-layer" anymore.

Nonsense :))

In any case, for these of us who do want to be closer to their data, please 
refer back to Toon Koppelaars' "The Helsinki declaration" series:

[health warning: oracle centric]

http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declaration-observation-1.html
http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declaration-observation-2.html
http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declaration-observation-3.html
http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declaration-observation-4.html
http://thehelsinkideclaration.blogspot.com/2009/04/helsinki-code-layers-in-dbms.html
http://thehelsinkideclaration.blogspot.com/2009/06/continuing-with-part-2-of-helsinki.html



___
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]

"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".

All nice and well, but the problem is two-fold IMO.
Portability with regards to Application-Code
(exchangeability of "The DB" among different languages)
and on the other hand, portability of the Data (the DB,
the "Backend").

>From my experience one should use "DB-internal
mechanisms" only in a range, which is common
among different DB-engines (Triggers for example,
to ensure referential integrity at least).

But a "Stored-procedure-language" which is
more or less proprietary to a certain DB, does
not work out that well, if your Application (or
your Business-Layer with your "business-rules")
needs to be able, to talk to a different backend
(since your new customer "just made the wish"...;-)

Then backend-portability is required - and the less
voluminous and complex your "special code" in your
current DB-backend is, the faster will be your switch
to a new backend (using the App-language for the
business-rules then, supported by some sort of
"DB-Abstraction-Helper" of course - as e.g. JDBC,
ODBC - or ADO/OLEDB in the Windows-world).

And if you want to ensure (aside from easy backend-
portability), that your "business-rules" (your business-
layer) survives "App-language-switches" as well, then you
should encapsulate it in a component (a Dll for example) -
containing a public reachable API, which is usable/callable
from different languages then.

If such an "encapsulation of business-rules" is sitting in the
DB itself - written in a proprietary "DB-dialect", then you
cannot call such a thing a "business-layer" anymore.

A layer is a thing, sitting "in-between" (to abstract
from two sides, in this case the App-language(s) *and*
the Storage-Engine(s)) ... and not a thing "sitting inside
somewhere".

Just my 2cts...


Olaf



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

[sqlite] Constraint name?

[Petite Abeille]

Hello,

Is there a way, short of parsing the original DDL, to retrieve a constraint 
name?

For example, given:

create table foo
(
  bar text,

  constraint foo_uk unique( bar )
)

How does one retrieve the unique constraint name, "foo_uk"?

Thanks.

Cheers,

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

Re: [sqlite] Bugreport: sqlite dot commands should return a vaild returncode

[chris.com]

Jon,

I don't agree.
You may be right about the ATTACH behaviour.
But the fact remains : sqlite3 doesn't return a proper exit code for dot 
commands.
Furthermore the error messages for SQL commands appear on stdout

A dot command :
$ echo ".import /dev/null does_not_exist" | sqlite3 lala.db
Error: no such table: does_not_exist
$ echo $?
0
The error message appears on stderr

A SQL command:
$ echo "select * from does_not_exist;" | sqlite3 lala.db
SQL error near line 1: no such table: does_not_exist
$ echo $?
1
The error message appears on stdout

Regards
chriss

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

Re: [sqlite] 5 GB DB with a 51 GB wal file.

[Teg]

Hello Teg,

Thursday, November 11, 2010, 12:08:23 PM, you wrote:


T> I have a 5GB DB file, 5 simple tables. Several million entries. When I
T> tried to delete perhaps 10% of the entries. The WAL file blew up to
T> 51 GB, 10 times bigger than the DB file itself.  I understand the
T> problem I think, with long transactions and have since split the
T> transactions up into smaller chunks.

T> My question is, can I fix this DB? "integrity_check" says it's OK but,
T> when I "Pragma wal_checkpoint;" from Sqlite.exe, nothing seems to
T> happen. It completes and the WAL file is still 51 GB. I was expecting
T> it to integrate the changes in the WAL file back into the DB file
T> proper.


Sorry to reply to myself. False alarm. It did all the work but, I had to
manually refresh the display to see it.

-- 
Best regards,
 Tegmailto:t...@djii.com

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

[sqlite] 5 GB DB with a 51 GB wal file.

[Teg]

I have a 5GB DB file, 5 simple tables. Several million entries. When I
tried to delete perhaps 10% of the entries. The WAL file blew up to
51 GB, 10 times bigger than the DB file itself.  I understand the
problem I think, with long transactions and have since split the
transactions up into smaller chunks.

My question is, can I fix this DB? "integrity_check" says it's OK but,
when I "Pragma wal_checkpoint;" from Sqlite.exe, nothing seems to
happen. It completes and the WAL file is still 51 GB. I was expecting
it to integrate the changes in the WAL file back into the DB file
proper.



-- 
Best regards,
 Tegmailto:t...@djii.com

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

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

[Dennis Suehr]

Hello,

Apologies if an answer to my query exists elsewhere.  I have been searching
for a fair while without success.

I am building an SQLite interface as a C shared library and consequently
will have no knowledge or control of concurrent connections to any databases
which are created by it.

I need to implement a function which will be able to delete a previously
created database.  I plan to do this by obtaining an exclusive lock to the
DB, then closing my connection (which as I understand will still keep the DB
locked) and then removing the DB file (and related SQLite temp files) from
the underlying file system.

I have a couple of concerns regarding this operation, which are as follows:

1) Firstly, I can not seem to ascertain what would happen to another process
which held an open database handle to the now deleted DB if it tried to
perform a subsequent operation on it.  If some appropriate error code is
returned upon which I can act, then great.  However, it goes without saying
that a seg fault would not be an appropriate outcome

2) Ideally, I would like to be able to force close any open connections
before deleting the database file and if that's not possible then at least
being able to clean up any SQLite related memory resources from those other
connection afterwards.  However, I can not even find any existing SQLite
mechanism which would provide me with a list of open connection handles to a
given DB.

Any help in either of these regards will be much appreciated.  Thank you in
advance,

Regards,

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

Re: [sqlite] Question about SQLite features.

[Kees Nuyt]

On Thu, 11 Nov 2010 06:26:31 -0800 (PST), jeff archer
 wrote:

>>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.

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.

>Please don't anyone take this comment wrongly.  I really like SQLite and I am 
>very committed to its use in my projects.  Many thanks to all who make SQLite 
>possible.  It is an excellent product.  And the support available through this 
>mailing list is truly top notch.  I have paid (sometimes dearly) for a 
>lot less 
>competent and complete support.

I second this, of course!
-- 
  (  Kees Nuyt
  )
c[_]
___
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 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.

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 would have hoped that the tests 
for Sage version increments were as good as the ones for SQLite.

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.

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.

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.

[jeff archer]

>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.

Please don't anyone take this comment wrongly.  I really like SQLite and I am 
very committed to its use in my projects.  Many thanks to all who make SQLite 
possible.  It is an excellent product.  And the support available through this 
mailing list is truly top notch.  I have paid (sometimes dearly) for a lot less 
competent and complete support.

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

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

[Dr. David Kirkby]

On 11/10/10 08:58 PM, Samuel Adam wrote:
> On Wed, 10 Nov 2010 08:53:40 -0500, Dr. David Kirkby
>   wrote:
>
>> Someone recently said he felt that a test:code ratio of 1:1 is about
>> optimal, so
>
> Where do people get notions like this?  Cf.:

I don't know. In fact, I asked him if he called rand() to come up with the 
number!

Clearly for life-critical systems (medical, aeronautical etc), then the level 
of 
testing needs to be thorough than for a game for children to play.



> Samuel Adam

Thank you

Dave

___
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/10/10 04:28 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 11/10/2010 05:53 AM, Dr. David Kirkby wrote:
>> Someone recently said he felt that a test:code ratio of 1:1 is about optimal,
>
> That of course is bunk.

That was my feeling too.

> The optimal amount depends on what the project
> does, the resources available, the consequences of bugs, and the values of
> the developers.  For example for your project I'd guess that accuracy is
> very important while running out of memory is not.

Yes, both are true, though memory leaks are a sign of bad coding, and should be 
stopped.

> If the project is run on
> a buggy FDIV pentium would it even notice?

I doubt it. If I believed it was a serious issue, I could write a test for it, 
but there are FAR more serious issues to worry about in Sage than the 
possibility of someone having a really old Pentium and stumbling across the bug.

> SQLite is a library and hence has no control over the application it runs
> in.  There may be a lot of memory or it may be a small device with tens of
> kilobytes.  It may be an application that doesn't really matter if it
> crashes, or in one that should never crash.  The data may be randomly
> generated or very important.

Yes, I can see that.

> 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"

I really think that's a stupid approach, but it's an uphill struggle to get 
that 
message across. Part of the problem is most developers are mathmaticians and 
have very little knowledge of computer science. I try to encourage them to read 
about software engineering, but that too is difficult.

But as I noted before, I believe I've made some inroads, and the lead developer 
is taking this more seriously now.

>> Of course there are practical issues with having a lot of test code - if you
>> spend a lot of time writing that, you have less time to add functionality.
>
> If you have code that is unexercised by your test suite then does it make a
> sound when falling in the woods?

Sorry, I don't understand the question.

> Sorry I mean can you make any assumptions
> about it at all?

Not 100% reliable ones. But any non-trivial program will have bugs. I don't 
believe it is totally possible to eliminate all bugs in any non-trivial program.

> You are of course using your users as the inefficient test
> and hoping they would notice any problems.  Depending on the application
> this may be ok.

Well, its unfortunate that in some cases users wont notice the bug. If I use a 
word processor and it outputs incorrect text, assuming I proof read it, I will 
know about the bug.

In contrast, if I perform a calculation, such as an integral and the answer is 
wrong, I may well not know about it. I do personally see it as a serious 
problem, but I'm also aware that there needs to be an acceptance by users that 
you can't trust the software 100%.

> Note that the standard test suite is written in TCL.  Languages like TCL,
> Python, Lua etc are significantly more productive especially for being test
> harnesses.  There is generally no boilerplate since you can automate that 
> away.

> However some environments can not adequately run TCL to test SQLite which is
> why there is TH3 that does the tests in C.  That C code is generated with
> control over how it is done as for example having it all done at once may
> produce something to big for the platform to run at once.

I see.

> At the end of the day the best thing to do is be honest with your users.  On
> the web site say how much test code there is.  Say what percentage of the
> codebase it covers.  Say if that is just lines or decision points (MCDC).
> Say what kinds of testing there are (eg correctness, memory allocation, data
> integrity etc).

Thank you. That is a good suggestion.

We have well over 1000 open bugs. Someone who is an expert in testing computer 
algebra systems has agreed to use his closed-source code to find us more. Our 
bug list is public of course

http://trac.sagemath.org/

> Roger

Thank you Roger.

-- 
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in e-mail?

Dave

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

[sqlite] Corrupted database with duplicated primary keys

[Israel Lins Albuquerque]

Attached has a database corrupted. 
We use the version 3.6.23.1 in wince. 

the command: 
pragma integrity_check; 

show many errors and 

Duplicate pk was founded using: 
SELECT u_pkey, count(*) 
FROM tp_gpsdata 
GROUP BY u_pkey 
HAVING count(*) > 1 
; 

this returns only 1 record 
SELECT * 
FROM tp_gpsdata 
WHERE u_pkey IN (4684, 4879) 
ORDER BY u_pkey 
DESC LIMIT 10; 

deletes one record only 
DELETE FROM tp_gpsdata WHERE u_pkey IN (4684, 4879); 

vacuum; 
doesn't works because of pk constraints. 





I'm seeding because that can be a bug in OS or in sqlite and maybe someone can 
see that, 
Thanks for your time! 

-- 
Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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

[sqlite] new sqlite3_soft_heap_limit64 not available for extension libraries

[Gerry Kleinpenning]

Dear Sir / Madam,

Maybe not a real/serious bug but…

When I look at version 3.7.3 I see there is a new sqlite3_soft_heap_limit64, 
and that the old function sqlite3_soft_heap_limit is deprecated.

Extension libraries get pointers to the API functions from the 
sqlite3_api_routines structure. A pointer to the 64 bits version is missing, 
forcing the extension lib to use the old 32 bits version. Shouldn’t there be an 
extra pointer in the structure?

Kind regards,

Gerry Kleinpenning

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

Re: [sqlite] Strange WAL mode on 5GB+ database

[Dan Kennedy]

On Nov 11, 2010, at 3:47 AM, Alexey Pechnikov wrote:

> $ ls -lh merch.db*
> -rw-r--r-- 1 - -  5,8G Ноя 10 23:01 merch.db
> -rw-r--r-- 1 - -  32K Ноя 10 23:04 merch.db-shm
> -rw-r--r-- 1 - -  449M Ноя 10 23:01 merch.db-wal
>
> sqlite> pragma journal_mode;
> wal
> sqlite> pragma journal_mode=delete;
> Error: database is locked
> sqlite> pragma integrity_check;
> ok
> sqlite> pragma journal_mode=delete;
> delete

Is this a repeatable problem? Any other processes accessing the
database when you run the test?

Dan.


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