|
Has anyone used SQLite?
I've been looking at a very lightweight Relational database and I came across SQLite. Has anyone used this before? If so, any feedback would be appreciated.
I'm particularly interested in real performance or obvious gotcha's.
Thanks!
RN
Wednesday, March 17, 2004
Its a lovely little database. Im constantly being impressed with how much sql it actually implements..when I first started using it I expected to be hemmed by its limitations to a degree, <g> so far that hasn't happened at _all_
Its pretty quick, very stable when used correctly, overall its a wonderful little database that I would happily recommend to anyone looking for a singleuser/embedded database to use.
FullNameRequired
Wednesday, March 17, 2004
I am interested in using it, too. Is there a GUI interface to it or is it purely from command line?
entell
Wednesday, March 17, 2004
It will be included in the default PHP distribution, so there's probably going to be many more people using it very soon.
TomA
Wednesday, March 17, 2004
i've used it in a couple of console PHP applications. i didn't push it very hard, but it was virtually no different than using mysql. as the site says, it's typeless and i believe just stores everything as a string. actually didn't cause me any problems as 95% of data is string/integer.
here's a gui that i used. it was fine, got hung on a couple of large imports:
http://bobmanc.home.comcast.net/sqlitecc.html
bob
Wednesday, March 17, 2004
It made the 'quote of the week' at http://www.dbdebunk.com
Lite
Wednesday, March 17, 2004
What a curious site. This is a little uptight though:
"Please access/link to this site only via the designated domain name www. db debunk .com"
???
Matthew Lock
Wednesday, March 17, 2004
Presumably there have been problems with linking to the site through a redirect. What often happens is that the owner of the site that uses the redirect changes his setup and you find the redirect doesn't work anymore.
Both Date and Pascal are purists. Few people could reply to a poster with the words "Your main problem is that you don't have the least idea of the basic concepts involved" and then go on to explain why in painstaking detail, blithely unaware of the fact that the poster might be a little put out by the admonition.
They are however nearly always, if not always right, and I am sure that if any of us had spent thirty years refuting the same old tired misconceptions we too would be a little testy.
Stephen Jones
Wednesday, March 17, 2004
Very good experiences with it. The author is extremely responsive to support requests and bug reports. There is a mailing list which is active, polite, and not overwhelmingly busy.
There are a number of GUI front ends to SQLite, but no official one.
The code is very very clear and readable. Extremely well documented. It is in the public domain as well, so no copyright issues.
I find its featureset to be very good; its APIs well thought out. The SQL that it can run is a pretty good subset of what's out there (missing foreign keys and check constraint enforcement, last I checked. But that's minor, like most other missing features). There's a page on the SQLite site that details which features are missing.
I cannot recomend this package highly enough.
Damn that reads really dryly... Oh well, it is all true.
Mike Swieton
Wednesday, March 17, 2004
Pros: very easy and logical to use, embedded and lightweight, open license
Cons: Quirky bugs, performance is questionable outside of extremely simple queries. We had to index some things on our own because SQLite behaved slowly when we used it to query out data.
Richard Kuo
Wednesday, March 17, 2004
I have used it and it's real fast. There's a cool feature that lets you create a database in memory and run SQL against it, never writing to disk.
Gotcha: although there are data types they are not enforced.
Tom Vu
Wednesday, March 17, 2004
I just realized why the "untyped column" doesn't sound right - column datatypes document the column.
Writing SQL against a database implicitly types the column. By not putting that type on the column itself invites bad data that breaks the existing SQL (and applications), but also puts an onus on someone to indicate what data *should* be in the column.
Both Oracle and SQL Server have untyped columns - they're called varchar. You might notice that no respectable developers use tables which are 100% varchars. And it's not force of habit - I've worked on databases that were all varchars (actually all varchar(50) - I'm betting it was ported from Access). The database itself and its attendant applications had some real issues stemming from the lack of column typing.
I'd be interested to hear experience to the contrary, but my feeling is that the "untyped column" isn't a best practice...
Philo
Philo
Wednesday, March 17, 2004
"I'd be interested to hear experience to the contrary, but my feeling is that the "untyped column" isn't a best practice..."
I actually rather like it. SQLite accepts _any_ data type for each column and remembers what you called it, so the documentation side is a non-issue..I still create columns with varchar, integer, double, date etc etc types.
the only difference is that in actual fact everything is just stored as bytes.
searches on dates and numbers continue to work as I would expect...although Im sure that must fall down somewhere.
<shrug> it all works fine IMO, sqlite is a fast, lovely, simple, stable and reliable database.
FullNameRequired
Wednesday, March 17, 2004
Philo,
The untyped column issue strikes me as just one about static vs dynamic typing.
It's just not as important as dogma tells us. However it's certainly a nice safety net.
Koz
Wednesday, March 17, 2004
FullName:
You can't quite give it any arbitrary data you want: I believe that the data must always be a c-string, or at least that used to be the case. But there's code in the library already to escape it for you.
Mike Swieton
Thursday, March 18, 2004
Koz - there's a difference.
With static vs. dynamic typing, for the most part you're writing single-use code. Not necessarily that the app/module/class will only be used once, but rather that it's designed to do a specific thing, so typing can be defined by the coder and designed around.
Databases, on the other hand, are general-use. In most cases any database in production *will* be accessed by other code; code outside the control of the original coder.
So even though a field is labeled "int", some moron can come along later, forget a validation on that field, and allow a varchar to be inserted, breaking every single application that touches that field.
oops.
Philo
Philo
Thursday, March 18, 2004
"So even though a field is labeled "int", some moron can come along later, forget a validation on that field, and allow a varchar to be inserted, breaking every single application that touches that field."
how would it break the application?
I assuming that you are thinking about...say..."bugger" being inserted into a field intended for integers?
that wont break anything, at some point there has to be a conversion of the values returned in the data cursor to a number anyway, and any even vaguely reasonable conversion will convert "bugger" to 0
<shrug>
FullNameRequired
Thursday, March 18, 2004
How about if you do some arithmetic on a column, like a database containing products and you SUM all the products in a user's shopping cart? You will surely cause a problem is some bozo entered a non-numeric value in there. For example they put a dollar sign at the start of the field by accident.
Matthew Lock
Thursday, March 18, 2004
On the above example I mean arithmetic in the SQL select statement.
Matthew Lock
Thursday, March 18, 2004
" You will surely cause a problem is some bozo entered a non-numeric value in there."
<g> not to sound too arrogant, but if there is _anyone_ out there who is sending user input directly to the database without performing _some kind_ of sanity check then I hope to god I never get tricked into employing them :)
AFAICS the strong typing of most languages is an advantage here...the input will always have passed through strongly typed formatting before it gets to the database.
In my case it goes from input field, to class member, to database. and then back again, from database to class member to display/edit.
Certainly Ive had no problems whatsoever in either the area of documentation or badly formed input, and I dont expect to have any. (if I did I wouldn't be willing to touch sqlite with a barge-pole)
FullNameRequired
Thursday, March 18, 2004
hmm...I hadn't considered doing web/perl/php stuff with it, there everything is weakly typed.
good point :)
The point about validation of the input still stands I guess, but it would become rather more important.
interestingly enough Ive never even considered using sqlite for webpages/website backends, I would have expected most sites to require a larger/multiuser database for that scenario.
Certainly mySQL or postgreSQL would be my choice there.
FullNameRequired
Thursday, March 18, 2004
"Cons: Quirky bugs, performance is questionable outside of extremely simple queries. We had to index some things on our own because SQLite behaved slowly when we used it to query out data. "
Do you care to elaborate on that
Ignore my ignorance
Thursday, March 18, 2004
You will see a lot more web sites using SQLite once PHP5 comes out - with SQLite as it's default database.
Matthew Lock
Thursday, March 18, 2004
"You will see a lot more web sites using SQLite once PHP5 comes out - with SQLite as it's default database."
makes sense in some ways I guess, anyone with experience will know how to install mysql or whatever db they prefer, and anyone just starting out will find sqlite a lot easier to use straight off the bat, theres certainly _no_ config required.
Be interesting to see whether they continue with that plan though now that mySQL has added an exception to their license for php, they may decide to bundle both after all.
<g> I _still_ dont think that anyone will start using sqlite for websites that are more than trivial, sqlite is a _wonderful_ singleuser database that is ideally designed for embedding in an application, and would work fine for websites with a fairly low amount of traffic, but as the backend for a shopping site it would have...issues....
FullNameRequired
Thursday, March 18, 2004
"<g> I _still_ dont think that anyone will start using sqlite for websites that are more than trivial"
that hardly narrows the field down does it? ;-)
So is SQLLite is the JET of OSS?
i like i
Thursday, March 18, 2004
"So is SQLLite is the JET of OSS?"
ummm...I write closed source software if thats what you mean?
<g> and Ive not seriously used JET.
Valentina was my previous love, thats another _quality_ product.
FullNameRequired
Thursday, March 18, 2004
The whole point of a DBMS is to ensure that your data is correct so you don't have to. If you don’t care about data consistency and integrity and don’t mind having royally screwed up data in your database then by all means use this product.
SQLite is nothing more than a SQL interface to a flat file. There is little/no data management going on. There is no concurrency control (the whole DB is locked when someone wants to change some data) and ZERO integrity constraints.
Having typeless attributes in a DBMS is just about the stupidest thing one can imagine. An attribute type defines what operations can and cannot be performed on it and also enforces correctness. As has been mentioned before – how do you do addition on a string? What would the value of ‘ABCD’ mean in the attribute of ‘salary’ for an employee?
Further, SQLite has no referential integrity constraints, CHECK constraints, etc:
http://www.sqlite.org/omitted.html
Given that there are other embeddable DBMS products out there which *do* support constraints and datatypes, I see no reason why anyone should use this product.
MR
Thursday, March 18, 2004
"""SQLite is nothing more than a SQL interface to a flat file.... I see no reason why anyone should use this product."""
Well, what if you want an SQL interface to a flat file? :) There are plenty of odd jobs that could use this, such as desktop programs that want to store something in a single file, but want SQL. I don't think I'd use SQLite for anything but prototyping, or doing small reporting tasks (e.g. pull various summary data from "real" DBs and put them into a nice format for graphing, etc.). Certainly not anything with significant multiuser access.
But I would prefer using SQLite to MySQL if I had a small enough project, and PostgreSQL to MySQL for anything that was too big for SQLite.
Phillip J. Eby
Thursday, March 18, 2004
" If you don’t care about data consistency and integrity and don’t mind having royally screwed up data"
wow, thats harsh. I use it a fair bit now for various things and I have _never_ ended up with royally screwed data. what experiences have you had in this regard with sqlite?
" There is little/no data management going on"
<G> technically SQLite _is_ ACID compliant.
surely that counts for something?
"As has been mentioned before – how do you do addition on a string? "
<g> in some languages doing so concats it with another.
but thats not really your question, is it? why _would_ you do additional with a string? I use sqlite and have never done this.
How did you end up doing addition with a string?
why dont you tell us about your bad experience with sqlite?
"I see no reason why anyone should use this product."
well;, Im glad you cleared that up :)
FullNameRequired
Thursday, March 18, 2004
"SQLite is "typeless". This means that you can store any kind of data you want in any column of any table, regardless of the declared datatype of that column. (See the one exception to this rule in section 2.0 below.) This behavior is a feature, not a bug. A database is supposed to store and retrieve data and it should not matter to the database what format that data is in. The strong typing system found in most other SQL engines and codified in the SQL language spec is a misfeature - it is an example of the implementation showing through into the interface."
I only implemented linear searches in the application. All those fancy search algorithms are useless IMHO. They are to complicated and introduce leeching abstractions (read this on a website). Those academical guys like Knuth should go out of their ivory power more often. What do they know about real world programing anyway? FYI I have 2+ years PHP/MySQL development under my belt and I never used my algorithm textbook. Not once!
gunga
Thursday, March 18, 2004
> “Well, what if you want an SQL interface to a flat file?”
If you are willing to accept the (substantial) risks then by all means use it – although I would prefer you didn’t, for your sake and whoever relies on that data.
It could be used as a way to easily create reports (e.g. all selects) but it is not suitable for data management – don’t think it’s at all a replacement or even a substitute for proper DBMS products (of these, MySQL is not one). It lacks critical data-management functionality and is virtually one step up from you coding the file parsing routines yourself.
> “I use it a fair bit now for various things and I have _never_ ended up with royally screwed data.”
Maybe you’ve been lucky, or extra cautious. SQLite easily allows you to have royally screwed up data precisely because you have to roll your own constraints in application code and thus it is easily circumvented.
> “ACID compliant”
Well, sure, but there’s more to data management then simply locking the whole DB when you try and update something.
> “How did you end up doing addition with a string?”
The issue is not why you would attempt addition on a string – that is silly. The issue is that you can put any data in any column even if it violates the datatype constraint. I can, by accident or on purpose, insert the string ‘foo’ into a column which *supposedly* stores information that is of a numeric nature. The example I gave before is salary. What happens when you try and get employees with a certain salary, or sum up all the salary values? Who knows – summation and numeric comparison is undefined on a string.
Of course, you could write a datatype check for each and every attribute and check each before you insert it. I’d rather have the DBMS do that for me, like it is supposed to.
>“The strong typing system found in most other SQL engines and codified in the SQL language spec is a misfeature - it is an example of the implementation showing through into the interface.”
Wow, that is a symptom of knowing just enough information to think you know it all. :) I agree that SQL has many horrible exposures of implementation-specific aspects of the model. Types are, in the general sense, not one of them. Of course, SQL got the ‘domain’ idea completely wrong, so it’s not really saying much.
MR
Thursday, March 18, 2004
" If you don’t care about data consistency and integrity and don’t mind having royally screwed up data"
wow, thats harsh. I use it a fair bit now for various things and I have _never_ ended up with royally screwed data. what experiences have you had in this regard with sqlite?
******************
FullName, the issue isn't what you do with your pet database - it's what *other* people do with your pet database.
SELECT SUM(Items * UnitPrice)
FROM Items
WHERE CartID=%CartID
Works fine until Joe LeetCoder writes an ASP cart that also uses your data store for his app, but doesn't put type checking on the data entry. So some user enters "Free" in the price as a joke and hits submit...
...breaking every report you produce. *anything* that touches that cell will simply stop working right.
Remember that there's a camp championing loose typing in programming languages just as fervently as you're championing loose typing in your database.
In my experience, loosely-typed databases (i.e. RDBMS where every field is Varchar(50)) contain crap for data. Period.
Philo
Philo
Thursday, March 18, 2004
"It lacks critical data-management functionality"
maybe you should be more specific here...what 'critical data-management functionality' exactly does it lack? or are you referring _entirely_ to the fact it its typeless?
"Maybe you’ve been lucky, or extra cautious. "
no. I dont rely on luck, or mere caution to maintain data for my clients.
"Well, sure, but there’s more to data management then simply locking the whole DB when you try and update something."
??? not for a singleuser database which is all that sqlite purports to be. there is no point in locking each row when only one application is using it at a time.
"The issue is not why you would attempt addition on a string – that is silly."
as I said in an earlier poist, in real life you dont need database constraints because the data is coming from class data members that are strongly typed _anyway_
data goes from the user input -> c++ class data member -> database and back again the same way.
There is _no way_ that I, or anyone else, could possible trick a c++ data member defined as, for instance, int to accept "oops, Ive stuffed this up" as a value.
This problem is _not a problem_ in any reallife application.
"Of course, you could write a datatype check for each and every attribute and check each before you insert it."
which is effectively what happens in this case.
" I’d rather have the DBMS do that for me, like it is supposed to."
interesting. Im not entirely convinced that its the job of the database to validate your users input...maybe you can convince me?
"Works fine until Joe LeetCoder writes an ASP cart that also uses your data store for his app, but doesn't put type checking on the data entry. So some user enters "Free" in the price as a joke and hits submit..."
umm..what? Im using it for applications. SQLite is _not designed_ to be used as a serious web backend and anyone doing so deserves everything they get.
<g> frankly, if Im supposed to code in such a way as to prevent Joe LeetCoder from breaking something, sometime in the future, by using some unknown technique, then my job just got an awful lot harder.
But surely you are not suggesting I select the best tool for the job based on what Joe LeetCoder may, or may not do, with my code or my data in the future? that would be...stupid.
Or, to put it another way, Joe LeetCoder can take any database you have created philo, using any database system you choose, and write a website that will stuff it beyond recognition....does that indicate that you choose the wrong database? or that Joe LeetCoder is an incompetent buffoon?
"Remember that there's a camp championing loose typing in programming languages just as fervently as you're championing loose typing in your database."
<g> so we should all insist on strongly typed databases because at some point in the future there may be no strongly typed programming languages left? interesting idea...
Im _not_ championing loose typing in any database. Im saying that the points you have raised against the idea are not, in a real life situation, a problem.
the documentation side still works..the column types are still recorded. The "data validation" side (isn't necessarily the job of the database anyway, but thats prolly a different argument) still works fine in my experience because I use a strongly typed langauge that effectively performs that check anyway.
Your point of "some stupid bugger could write code that destroys your reports" is, of course, perfectly true, but in my experience thats an issue regardless of the database chosen, and weakly typed databases are hardly going to make it any worse.
Honestly, I could care less about weakly typed databases, you (philo) asked for any experiences and Ive given you mine. I was suspicious of sqlite, decided to try it, and I am now very impressed by it indeed <g> to the point where I waste my time defending it against random strangers on the internet.
Use it/dont use it I really dont care.
If you want to critique it (and it appears some of you _really_ do despite never having actually used it) then remember that in my experience documentation and data validation are not a problem.
'data management functionality' might be lacking....I dont really know (or care) what functionality is implied in that statement, I use it as a singleuser embedded database for my applications and it performs brilliantly...its reliable, stable, fast and easy to use. I suspect you would have problems if you tried to use it as a multi-user data source, but thats not what its designed for anyway so if you do that you have already proven yourself to be an incompetent buffoon ala Joe LeetCoder who wants to use it as a backend for his shopping cart.
If you _really_ want to critique it I suggest you try actually using it, then you will have an idea of what the issues actually _are_ instead of what you imagine they might be.
FullNameRequired
Thursday, March 18, 2004
"Databases, on the other hand, are general-use. In most cases any database in production *will* be accessed by other code; code outside the control of the original coder."
Well... yes, but SQLite isn't really designed for a large system. Or even a medium one. It's basically an embeddable database for applications, not for server use. They're not trying to replace MSSQL or even MySQL.
Although, knowing how the world works, I'm sure that SQLite will be thrown into some wildly inappropriate usage scenarios. <G>
I've been thinking of creating a picture-indexing/album-making application that is sort of like iPhoto. SQLite would be a perfect way to store picture metadata for a single-user app like that.
John Rose
Thursday, March 18, 2004
Gunga: So in 2 years experience *using* PHP and MySQL you haven't needed an algorithms book. That's not surprising, because you're not working on an algorithm-centric domain.
Consider SQLite: its algorithms take a lot from Knuth (check the mailing list archives if you don't believe me).
Just because you haven't personally cracked the cover doesn't mean that somebody at some level needed to know it. Knuth's ivory tower is directly applicable to the work of many. Perhaps not you, but that doesn't mean it's useless as you imply.
Mike Swieton
Thursday, March 18, 2004
About inserting 'Free' where an integer is expected... Inserting a 0 for the price would have the same effect in sum(row), and it's an integer; your type-checking RDBMS won't help with that.
DMC
Thursday, March 18, 2004
FullName, if you specified somewhere that SQLite's untyped data is fine *only* for small, single-use databases, I apologize - I missed it.
However, the developers of SQLite, as echoed by gunga, seem to believe that database columns should *never* be typed. Ever. "Everyone is lost but us" they proclaim.
I read that as including multi-user, scalable data stores intended to be accessed by multiple applications (written by multiple users).
I see that as a recipe for disaster. Period.
You opine that so long as the code is strongly-typed, it's not a problem. Except that most strongly-typed languages have an untyped type (variant in VB, object in .Net), and some modern languages are loosely-typed.
I believe it was McConnell who advocated using variants for everything in code...
So I'd call those two concepts are two freight trains on the same track...
Philo
Philo
Friday, March 19, 2004
Mike, I was trying to make a joke...
I left some hints (ivory power? leeching abstractions?).
I'm just not good at making jokes, I guess.
There have been 40 years of serious research and work in the database field and then you hear someone say that "strong typing isn't needed in fields" and you hear people resort to the "real world" mantra (in the real world = in my little self centered world).
You've got to know the rules (and why they are there) really good before atempting to break them.
gunga
Friday, March 19, 2004
>Inserting a 0 for the price would have the same effect in sum(row), and it's an integer; your type-checking RDBMS won't help with that.
It most certainly would:
CONSTRAINT fooConstraint CHECK( PRICE > 0 )
MR
Friday, March 19, 2004
>maybe you should be more specific here...what 'critical data-management functionality' exactly does it lack?
I’ve said it before. Are you not reading or just trolling?
>no. I dont rely on luck, or mere caution to maintain data for my clients.
With SQLite, you are.
>??? not for a singleuser database
You said that the transactions were ACID. I suggested that there is more to data management than ACID transactions.
>as I said in an earlier poist, in real life you dont need database constraints because the data is coming from class data members that are strongly typed _anyway_
data goes from the user input -> c++ class data member -> database and back again the same way.
There is _no way_ that I, or anyone else, could possible trick a c++ data member defined as, for instance, int to accept "oops, Ive stuffed this up" as a value.
>which is effectively what happens in this case.
So – instead of declaratively creating DBMS constraints WHICH CANNOT BE OVERRIDDEN you instead choose to roll your own DBMS constraints. That does not sound like an efficient use of your (or your client’s) time. Further, I hope your constraint code is not as buggy as your writing skills.
>interesting. Im not entirely convinced that its the job of the database to validate your users input...maybe you can convince me?
In the relational model it is the primary job of the RDBMS (note I did not say DB!) to ensure that your data is correct -- it does that via DBMS constraints (well, predicate logic and set theory). A DBMS is more than simply a place to stick your data!!
> Joe LeetCoder can take any database you have created … and … stuff it beyond recognition....does that indicate …
It indicates that you have not employed correctness-ensuring DBMS constraints. With a properly implemented DB model you can ensure your DB never becomes incorrect.
>so we should all insist on strongly typed databases because at some point in the future there may be no strongly typed programming languages left?
No, you insist on domain constraints because *it is the right thing to do*.
>'data management functionality' might be lacking....I dont really know (or care) what functionality is implied in that statement
You only don’t care *because* you are ignorant of data management fundamentals. Try picking up the book “Practical Issues in Database Management” by Fabian Pascal.
>If you _really_ want to critique it I suggest you try actually using it,
I don’t need to actually jump out of a plane without a parachute to know it’s wrong (and stupid).
MR
Friday, March 19, 2004
Hi Philo,
"FullName, if you specified somewhere that SQLite's untyped data is fine *only* for small, single-use databases, I apologize - I missed it."
nope, I never did. I _also_ never stated it was fine for _all_ databases :)
"However, the developers of SQLite, as echoed by gunga, seem to believe that database columns should *never* be typed. Ever. "Everyone is lost but us" they proclaim. "
:) I really dont care much what the developers of sqlite believe. Their beliefs in no way affect the quality of sqlite AFAICS.
Also, you are making some interesting assumptions based on a copy & pasted comment from _one_ of the developers that has very much been taken out of context.
(maybe they _do_ believe that, I wouldn't know, but you seem in an almost indecent hurry to believe so :)
"I read that as including multi-user, scalable data stores intended to be accessed by multiple applications (written by multiple users). "
I guess thats up to you..it feels like a bit of an assumption on your part, but feel free :)
"You opine that so long as the code is strongly-typed, it's not a problem. Except that most strongly-typed languages have an untyped type (variant in VB, object in .Net), and some modern languages are loosely-typed. "
umm...so? whats that got to do with me and my use of sqlite?
FullNameRequired
Saturday, March 20, 2004
"I’ve said it before. Are you not reading or just trolling?"
it was a genuine question, but feel free to make your own assumption on my motivation.
"With SQLite, you are."
and you know this because of your experiences with sqlite?
"You said that the transactions were ACID. I suggested that there is more to data management than ACID transactions."
I wasn't talking about 'data management', I was talking about using sqlilte :)
"So – instead of declaratively creating DBMS constraints WHICH CANNOT BE OVERRIDDEN you instead choose to roll your own DBMS constraints. "
interesting way of putting it, but yep, that sounds about right.
""Further, I hope your constraint code is not as buggy as your writing skills."
me to :)
"RDBMS (note I did not say DB!) to ensure that your data is correct"
right, sqlite is not a RDBMS, it is a DB.
"
"DBMS is more than simply a place to stick your data!!
"
right, a database management system is indeed more than simply a place to stick your data.
SQLite OTOH, is _not_ a DBMS, it does not claim to be, I do not use it as one.
SQLite is a _database_
" With a properly implemented DB model you can ensure your DB never becomes incorrect"
bollocks :) give me a database and access to it and I can guarantee that I can make it 'become incorrect'
"No, you insist on domain constraints because *it is the right thing to do*."
I dont :) but Im perfectly happy if you want to.
"You only don’t care *because* you are ignorant of data management fundamentals."
Call me ignorant again you arrogant twat and Ill jam that book up your fat ass. :)
"I don’t need to actually jump out of a plane without a parachute to know it’s wrong (and stupid)."
I put it to you that there is a difference betwee ntesting out a database and jumping out a plane without a parachute.
although maybe that depends on your level of self confidence.
FullNameRequired
Saturday, March 20, 2004
Let's use tools for the job they are intended for and not vilify them when they are misapplied elsewhere.
If you have some data and need to organise them on a single-user basis so that you can exploit the power of SQL-92 queries, with easy extension with your own SQL functions, then SQLite is for you. It has a tiny footprint and performs just fine on databases 10-1000mB for elaborate SQL queries. I've not attempted to use it for transactional purposes, but for data analysis and ad-hoc queries it is a miracle of economy and its author has a generosity that puts most of us to shame. It has been invaluable to me for several applications in this last year and has never put a foot wrong.
May I quote the author's copyright notice:
"The author disclaims copyright to this source code. In place of a legal notice, here is a blessing: May you do good and not evil; may you find forgiveness for yourself and forgive others;May you share freely, never taking more than you give."
The world needs more of an attitude like this, and please less aggression in response to the very simple question we are addressing here.
Pythonista
Saturday, March 20, 2004
"The world needs more of an attitude like this, and please less aggression in response to the very simple question we are addressing here."
Oh please!
I think that knowledge counts in software development. I think that learning what other people studied for years is valuable. When I read things about "self confidence", it makes me want to scream. There are things you have to *learn*.
gunga
Saturday, March 20, 2004
" think that knowledge counts in software development. I think that learning what other people studied for years is valuable. "
:) Ive _gained_ knowledge about sqlite by using it whereever it was suitable over the last 2 years.
FullNameRequired
Saturday, March 20, 2004
With respect, I was just trying to get this debate back on-topic. "has anyone used SQLIte?". Well yes, I have, and the experience was good. Do I know what I'm talking about? Well I think so, I've been developing commercial software systems for 30 years after a degree in Computer Science. I'm disappointed to find people arrogant enough to take the view "he thinks positively about this program therefore he must be ignorant". Horses for courses people, and politeness always. The world is full of appalling software we are obliged to use (no names, no pack drill); it is such a relief to find an excellent program that is there for no cost except the renunciation of your prejudices. Anyway I hope the poster of this question has enough information to decide whether to try out SQLite, there is a little here in these postings to wean out from the clashing of antlers.
Pythonista
Saturday, March 20, 2004
>“SQLite is a _database_”
A database is simply a collection of data. The data you insert into SQLite makes up a database – but SQLite provides tools to query, update, create, etc. database tuples so *by definition* it is a DBMS.
http://dictionary.reference.com/search?q=dbms
Of course, it lacks proper constraints so it is not a very good DBMS. :)
>“give me a database and access to it and I can guarantee that I can make it 'become incorrect'”
The caveat I mentioned is only if the DB is properly implemented. With proper constraints it cannot become incorrect (that’s the whole point of the constraint). And, of course, correctness refers to data *stored* in it – I have a feeling you’re thinking of deleting all of the rows or the like which the DBMS doesn’t care about provided it does not violate any constraints.
>“Call me ignorant again you arrogant twat and Ill jam that book up your fat ass.”
Look it up:
http://dictionary.reference.com/search?q=ignorant
*Lacking education or knowledge.
*Showing or arising from a lack of education or knowledge: an ignorant mistake.
*Unaware or uninformed.
Through your posts and actions it is painfully clear that you’re ignorant of data management fundamentals (I posted this before). What term would you rather I use?
> “I put it to you that there is a difference betwee ntesting out a database and jumping out a plane without a parachute.”
> “Let's use tools for the job they are intended for and not vilify them when they are misapplied elsewhere.”
If you are ignorant of data management fundamentals how in the world can you begin to use “the right tool for the job”? How can you properly evaluate the merits of a tool if you choose to remain ignorant?
All you know are tools and how to mechanically apply them!
> “Ive _gained_ knowledge about sqlite by using it whereever it was suitable over the last 2 years.”
> “I'm disappointed to find people arrogant enough to take the view "he thinks positively about this program therefore he must be ignorant".”
See the previous comments I made above vis-à-vis ignorance. My point is that in order to intelligently evaluate this tool one must fully understand data management. – only then can you make intelligent decisions. Otherwise, all you have is people who know enough to be dangerous making worthless recommendations.
You want to know why the IT world is full of “appalling software”? It’s because practitioners refuse to confront their own ignorance! As practitioners we owe it to our clients (employer) and ourselves to truly *learn* about what we are doing.
Practically speaking properly implemented databases in a proper DBMS results in faster development time (a declarative constraint is far simpler to create and maintain than equivalent application code), fewer bugs (because any time you are implementing constraints outside of the DBMS you have room for error) and ensure that your data remains correct.
Also note that “business rules” are a form of constraints, so anything I’ve said so far can be applied to business rules.
MR
Sunday, March 21, 2004
Just one remark MR, if you aren't going to update the database but merely query it, you don't need constraints (after all, if you can't get the INSERTs right, you will hardly get the constraints right anyway).
Constraints are a protective mechanism that is useful in an "enterprise" situation where you have a large database used by many diverse applications and a varying community of developers of varying skills.
Horses for courses again. Nothing is free. Fallible humans create constraints just as fallible humans create other SQL.
I agree in a transactional context constraints help keep things declarative rather than procedural; and that is good, indeed getting into a declarative domain for this writer at least is the best reason for adopting SQL at all.
I'm going to sign off from what has become an ideological rather than a useful discussion. There are no 'always' or 'nevers'.
Sometimes use flat files, sometime use relational DBMS, sometimes use XML flat files, sometimes use XML database, sometimes use O/o database, if using SQL sometimes use SQLite, sometimes use Oracle, sometimes use constraints sometimes not. It all depends on the context of the problem. Anyone who claims any one of these is never appropriate or always appropriate is the one showing ignorance and lack of maturity.
Pythonista
Sunday, March 21, 2004
"ut SQLite provides tools to query, update, create, etc. database tuples so *by definition* it is a DBMS."
oops. <g> blame a late night and a rushed response for that embarrassing little slip.
"I have a feeling you’re thinking of deleting all of the rows or the like which the DBMS doesn’t care about provided it does not violate any constraints."
of course, or redefining columns or altering data. The whole point of this part of the post was regarding the theory that Joe LeetCoder could at a later date stuff the database up by entering incorrect data if I used splite.
My point, which you have just enforced, was simply that I _cannot_ realistically work around things that Joe LeetCoder _might_ do, and that the choice of database in this care is irrelevant, whatever I use Joe Coder can still spoil it.
You have, I believe, just verified that point for me :)
"Through your posts and actions it is painfully clear that you’re ignorant of data management fundamentals"
my actions? good lord. <g> you've been watching me? or are you referring to the simple fact that I have _repeatedly_ chosen to use sqlite as a single-user embedded database for various applications?
Its a _good_ database product...fast, stable, robust....
"If you are ignorant of data management fundamentals how in the world can you begin to use “the right tool for the job”? How can you properly evaluate the merits of a tool if you choose to remain ignorant?"
a good question, and one that I will struggle with for the remainder of my days.
We can only hope that one day a solution will present itself to me.
"All you know are tools and how to mechanically apply them!"
:) thats not _entirely_ true...I have _some_ experience in various other things as well.
"My point is that in order to intelligently evaluate this tool one must fully understand data management."
wow. really? I need to do that before I can make the statement: "I have used this tool wherever its been appropriate for the last 2 years, and its consistently proven itself to be fast, reliable and robust (not to mention easy to use)" ?
That seems a little odd.
"Otherwise, all you have is people who know enough to be dangerous making worthless recommendations. "
I see your point...without a solid understanding of the fundamentals of data management (and total recall of the various buzzwords involved) making a statement like:
"I have used this tool wherever its been appropriate for the last 2 years, and its consistently proven itself to be fast, reliable and robust (not to mention easy to use)"
_is_ kind of worthless.
"As practitioners we owe it to our clients (employer) and ourselves to truly *learn* about what we are doing. "
I totally agree with this statement without reservation.
"Practically speaking properly implemented databases in a proper DBMS results in faster development time (a declarative constraint is far simpler to create and maintain than equivalent application code), fewer bugs (because any time you are implementing constraints outside of the DBMS you have room for error) and ensure that your data remains correct."
really? _every time_? _without fail_?
Thats a wonderful magic bullet you have there.
You are _truly_ a pompous, arrogant, overbearing little weenie :)
FullNameRequired
Sunday, March 21, 2004
Typically whenever I say “you” I mean the “royal” you – or more accurately the royal “we” which means IT practitioners in general.
> “if you aren't going to update the database but merely query it, you don't need constraints”
I’ll concede that point – although often constraints are used to also define logical relationships between tables. This gives the query optimizer more information as to how queries will be run which in turn allows it to optimize how it stores the data (it might store a joined copy, for example).
> “Constraints are a protective mechanism that is useful in an "enterprise" situation where you have a large database used by many diverse applications and a varying community of developers of varying skills.”
> “Nothing is free. Fallible humans create constraints just as fallible humans create other SQL.”
As they say: would you rather have cheap lies or expensive truth? :) But it is true that it’s harder to mess up declarative constraints than it is mess up procedural code.
I agree in a transactional context constraints help keep things declarative rather than procedural; and that is good, indeed getting into a declarative domain for this writer at least is the best reason for adopting SQL at all.
> “I'm going to sign off from what has become an ideological rather than a useful discussion. There are no 'always' or 'nevers'.”
I don’t think I’ve used always or never in the context that you seem to imply. However, I’ve suggested practical, useful implications of employing something like SQLite in an environment in which one is unaware of the limitations – namely that you’ll have to roll your own constraint and type checking, your own foreign key constraints, your own triggers, your own business logic, etc. This is far less preferable than having the DBMS do this for you – I can say that this is true 99% of the time (the 1% when it is not will probably be due to some goofy DBMS that has a horrible language and buggy implementation – in that case you’d better switch to a capable DBMS!).
> “It all depends on the context of the problem.”
I agree that the use or not of a particular tool depends on the situation we find ourselves in. But, without proper knowledge of *limitations* of the product (which is all I’ve been trying to say) we *cannot* (again by definition) accurately assess which tool to apply.
If we choose to remain ignorant then our decisions will be flawed – and someone will have to pay for them.
> “Anyone who claims any one of these is never appropriate or always appropriate is the one showing ignorance and lack of maturity.”
Would you say someone who claims 2 + 2 = 5 is never correct?
> “of course, or redefining columns or altering data.”
I would not consider redefining columns (what does that mean?) to be something that applications are allowed to do (e.g. you would not GRANT them access to change column types, or drop columns, or drop tables, etc.). However data validation is certainly within the realm of a constraint – it only depends on how granular you want your constraints to be.
>“My point, which you have just enforced, was simply that I _cannot_ realistically work around things that Joe LeetCoder _might_ do, and that the choice of database in this care is irrelevant, whatever I use Joe Coder can still spoil it.”
So you’re suggesting that because you can’t do everything you must do nothing by avoiding DBMS constraints altogether? Plus, I don’t think I reinforced that point because you can handle things like that with DBMS permissions. If you don’t want joe random application to be able to insert rows into XYZ table them you don’t give it to them. If you only want it to be able to update a certain attribute then you give them only that attribute. etc. etc. etc.
> “Its a _good_ database product...fast, stable, robust....”
> “wow. really? I need to do that before I can make the statement: "I have used this tool wherever its been appropriate …" ?”
(cue “When you only have a hammer…” cliché)
How can you define “appropriateness” if you don’t know what the product lacks? There is significant omission in your claim (as I’ve repeatedly outlined already).
> “I see your point...without a solid understanding of the fundamentals of data management (and total recall of the various buzzwords involved)”
Non-sequitur. Having a solid understanding of fundamentals does not require ‘total recall of … buzzwords’.
Are you a developer for SQLite or something? Your claims are unsubstantiated and worse misleading because of the omission issues I outlined earlier.
> “really? _every time_? _without fail_?”
99% of the time. Read above.
”You are _truly_ a pompous, arrogant, overbearing little weenie”
I love you, too. When did we get married? :)
MR
Monday, March 22, 2004
"So you’re suggesting that because you can’t do everything you must do nothing by avoiding DBMS constraints altogether?"
um...no, not at all. Im suggesting that the ability to us constraints is _not_ sufficiently important so as to stop me from using sqlite when I decide its the best DBMS to use.
"if you don’t know what the product lacks?"
umm, what? Ive used other databases many times for other putposes...<g> Ive even used constraints.
What I dont have is your word perfect recall of the various buzzwords required. <g> its only when I run into over-educated morons like yourself that I even miss them.
"Are you a developer for SQLite or something?"
LOL
nope, never worked on OSS in my life. (although I have worked with it from time to time).
I am just someone who has used SQLite wherever I felt it was appropriate for the last 2 yers and found it to be very fast, stable and robust. (not to mention easy to use).
I posted to this thread stating that fact and have found myself embroiled in a seemingly endless (and increasingly boring) argument over whether my use of that product proves my incompetence and/or ignorance.
"Your claims are unsubstantiated and worse misleading because of the omission issues I outlined earlier. "
??? which claims exactly? that I have used SQLite wherever I felt it was suitable for the last 2 years and have found it to be fast, stable and robust?
I was asked for my experiences..I have given them, if you do not believe their veracity then by all means test them yourself by using SQLite.
"I love you, too. When did we get married? :)"
FWIW I have been posting to JoS for..maybe 2 years now, and I have seldom (to never) felt the need to personally abuse someone with whom I was disagreeing.
A nice example is philo, he was able to discuss this topic without resorting to attacking the competency, integrity and coding skills of the other posters.
You may wish to work on your debating skills as assiduously as you have clearly studiest those data management textbooks.
FullNameRequired
Monday, March 22, 2004
 Recent Topics
Fog Creek Home
|
