Re: [sqlite] Question about SQLite features.
-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?
-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?
-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.
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.
"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.
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.
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.
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.)
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.
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.
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.
"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?
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
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.
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
Re: [sqlite] Database deleted from file system while other open connections exist.
On 11 Nov 2010, at 4:05pm, Dennis Suehr wrote: > 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. The reason you can't find this in SQLite documentation is that it's done at the OS level. The answer depends on which operating system and which file system you're using. Let us assume that you are accessing these files directly from a hard disk rather than across a network. Then you are using perhaps FAT, NTFS, ext3, HFS+, or some other disk filing system. Under early versions of Windows using FAT or NTFS, if you had a file open without an active lock, the file could be deleted. The application with an open handle would receive an error the next time it tried to do anything with that handle (sometimes including 'close' !). There have been two other changes since early versions of Windows, but I don't remember where they each happened. However, current versions of Windows are POSIX compliant and act the same as Unix systems. POSIX systems (which include all the versions of *n*x you're likely to find including Mac OS X) use a system of hard links: one file on disk may have many hard links to it (appear under different names and in different directories). So a file should be deleted only when the last hard link to it has been deleted. However, a hard link is created each time an application opens the file: that's what the file handle points to. So if two apps have the file open there would be three hard links to the file, and deleting its entry in a disk directory leaves two hard links, so it doesn't delete the file and reclaim the disk space. BUT ... the file will no longer be shown if you list the contents of its directory. There's nothing to stop someone in the meantime creating a new file with the same name, and using an app to open that one. So it's possible for two applications to have open two different files of the same name in the same directory. Other (embedded ?) disk operating systems and can all have their own rules about this. And as I wrote earlier, network filing systems have their own rules about this. To complicate things, one computer might be accessing the file directly from disk, but another might be accessing the same file through a SMB/SAMBA/NFS/whatever network connection. Which is one reason we tell people never to do that. So assuming direct access from a hard disk and a fairly recent OS we get the following answers: 1) The file would not actually be deleted yet. The original file would still exist and the process can continue to read/write it. When the last app closes the file the OS will notice, really delete the file, and reclaim the filespace as unused space. No seg faults because there are no errors. 2) No. There's no way to tell all applications with a certain file open that it's going to disappear. If you write all those apps yourself you could come up with any number of semaphore mechanisms, but most OSen don't provide anything like that. SQLite definitely can't tell what files it really has open because there's no way for the OS to tell it: it works differently for each OS. The above is simplified for brevity (e.g. Mac OS X actually does have a notification mechanism for a file disappearing) but should give you something to go on. If you have specific questions, get back to us. Simon. ___ 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.
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.
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.
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
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.
>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
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
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
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
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
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