"SQLite is not designed to replace Oracle. It is designed to replace fopen()."

http://www.sqlite.org/whentouse.html

by ma2rten • 15 years ago

249 57 15 years ago

larrik• 15 years ago

I first used SQLite because I thought (in C++) "Man, instead of writing crazy datastructures, I wish I could just use SQL on a spot in memory" SQLite came up in my Google search, and ta da!

Of course, this was back in 2003 or so, and that project never turned into anything more than a fancy wrapper over SQLite and Lua.

Still, SQLite == awesome, when it is appropriate.

diminish• 15 years ago

SQlite is a gem; when we develop web sites for clients, we first try to use sqlite, and if not we use MySQL and PostGres; we only use mssql, oracle when the customer has already a legacy database. What about you?

rbranson2• 15 years ago

... because the only use for file I/O in applications is structured tables of data? What about audio/video, documents, graphs, log-structured data, object caches, unstructured flat files, etc? Don't get me wrong, SQLite is awesome, but this is a classic example of the relational database world's pervasive attitude that they are the superior solution for any data storage problem.

william42• 15 years ago

It's a deliberately simplified view of the problem.

owenmarshall• 15 years ago

Wow. Not only did you manage to completely miss the point of that statement, but you cast it up as a hasty strawman to boot. I'm quite impressed.

The choice of fopen() and Oracle was meant to convey simplicity: SQLite solves problems on the simple end of the difficulty spectrum -- problems you would normally solve with fopen(). It is not for problems at the complex end, like Oracle. It's a simplified expression; reading it literally is foolish.

rbranson2• 15 years ago

Damn, you are so right bro.

andylei• 15 years ago

replace some instances of fopen(), not every instance of fopen()

dchest• 15 years ago

Acorn (image editor) uses SQLite as file format.

techtalsky2• 15 years ago

Even though SQLite isn't designed to replace Oracle, I'm certain there's plenty of companies that have purchased expensive Oracle licenses for projects where SQLite would have worked perfectly well. I've often used it for web-based custom CMS's of one kind or another where many people would have used MySQL and the client would never know the difference. I'm sure many people have used Oracle for web-based internal tools like this with less than a million rows of data.

apl• 15 years ago

Regardless of how much fun lamenting the assumed technical incompetence of enterprise software development can be, I strongly doubt that this has ever happened. Oracle is very expensive, and SQLite is very limited outside its clearly delineated use cases.

Oracle versus PostgreSQL or possibly MySQL -- maybe. But don't think Oracle sells snake juice; their reporting tools, for instance, are top-notch and way beyond what the open source world can provide.

techtalsky2• 15 years ago

Believe me, I wasn't trying to have fun at someone's expense. I think factors other than incompetence can account for this, like just simple ignorance.

I was more speaking to how easy it is to underestimate SQLite's flexibility and power and not realize how wide its use case actually is.

Of course for many use cases Oracle's tools are the best choice, but SQLite can handle quite a serious dataset in some use cases. A million records sounds like a lot. I can easily imagine crews used to working with Oracle using it for project far below its ideal capacity when they can buy licenses on a big corporate account.

nodata• 15 years ago

I disagree with you completely.

I've worked places where there is a rule that "The Corporate Database is Oracle and Oracle Only". Any mention of database _at_all_ will mean someone will ensure that The Corporate Database is used. It doesn't matter if Postgres or SQLite would be a cheaper/better choice.

reduxredacted2• 15 years ago

By the use of "an", I learned that I have been pronouncing SQLite wrong. The maintainers clearly prefer the spelling out of SQL in the title.

I've always been on the "sequel" side of pronouncing SQL (hey, it's no worse than "scuzzy" for SCSI), which morphed SQLite into "Sequelite". I never realized how bizarre that sounded (almost more like a material than a database).

ebzlo• 15 years ago

So does that become S-Q-L-ite or S-Q-Lite, you think?

reduxredacted2• 15 years ago

Good point. I was going for SQL-Lite. But being wrong (and hearing enough people say "lie-nux" [and to be clear, I am not mocking ... we read things and form our own pronunciation because we often don't hear them said] ...) I decided to google it.

This isn't necessarily authoritative or well researched, but it appears that S-Q-L-Lite is correct: http://blog.cleverly.com/permalinks/247.html

StavrosK4• 15 years ago

I say S-Q-L-ite.

kelnos• 15 years ago

I and most people I know call it "sequelite". That works for me...

StavrosK4• 15 years ago

Esquelite sounds pretty good too...

alastairpat• 15 years ago

From what I can understand, that's actually a difference between Commonwealth and American English.

I'm Australian and always spell out acronyms when speaking them - saying something like 'sequel' or 'scuzzy' just sounds weird to me.

petercooper• 15 years ago

Just in case anyone cares because I did the research on this a few weeks ago (including watching its creator say it numerous times) it's S-Q-L-Lite. Yeah, that seems like a duplication of the L but there you have it.

nhebb• 15 years ago

I've always pronounced it that way in my head, which has lead to a few typos searching for "SQLlite" or coding up a "SQLliteConnection". If I'd just learned to pronounce it wrong I would have been typing it right all along.

malkia• 15 years ago

We use SQLite exactly for that. At some point we were generating +50000 shader (.hlsl) files for 3 different platforms (so much due to various techsets and techniques).

Loading them back from the tools/game took a long time, for that reason they were put in small SQLite db - which is a little bit more complex than this "CREATE TABLE files (key, value)"

This sped up us significantly. We might be using this idea for more and more small files lying around.

peterwwillis• 15 years ago

I suppose it depends on your definition of "simplicity". My go-to simplified data storage container is Berkeley DB. (Little did I realize Oracle recently shipped a Berkeley DB with SQLite inside it... heh)

pacaro• 15 years ago

I love the example that this provides with respect to the conflation of "Storage" with "FileSystem", while data may indeed be stored on a filesystem somewhere, the habitual thinking that appends " to file" to the phrase "I need to save these data" seems unintentionally limiting to me.

Instead of trying to answer questions like "what should our file format look like?" It seems more interesting and valuable to ask first, "what storage is most appropriate", the answer may be REST, database, file, /dev/null, or any abstraction over one or more of these. You may then find that questions about file format, or structure simply disappear, it is the role of the storage mechanism chosen to efficently store and recover the data, use the hard work that the team that built that solution invested, and apply your own hard work to solving your own problems.

With cloud and mobile being added to the set of common target platforms (however ill-defined), I hear/read people asking "how do I read/write a file" increasingly often, when on those platforms (and many other) the concept of a file (at the application layer) may not be useful at all...

dendory• 15 years ago

I use SQLite exclusively in my projects, I love it. Easy to use, easy to backup (1 file), doesn't rely on a SQL database running, no security concern with username/password for the database engine (altho make sure your database file isn't web accessible)..

For any small/medium site it's great.

DNeb• 15 years ago

Agreed. And the database can grow surprisingly large (multi GB with many millions of rows) and it still performs quite well.

kellishaver2• 15 years ago

This surprises me. Though I've used it with great success for several small projects, my only experience with SQLite on anything remotely large was a Rails app with maybe a million records in the DB. Over time, performance became very slow and switching to MySQL made a world of difference.

At the time, I attributed it to SQLite, but now I'm wondering if it was Rails or (more likely) my inexperience at optimizing performance of the app at that time.

It was about three years ago, though, so I am sure performance in SQLite (and Rails, too, for that matter) has also likely improved a lot during that time.

smharris65• 15 years ago

Can anyone provide more info when the site says "...the file locking logic of many network filesystems implementation contains bugs (on both Unix and Windows)"?

grandpa• 15 years ago

They generally relate to NFS - e.g., Linux fcntl() used to lock NFS files only locally. http://en.wikipedia.org/wiki/File_locking#Problems

Windows CIFS shares are fine and always have been AFAIK.

derrickpetzold2• 15 years ago

Can anyone say when Oracle is justified? I have yet to encounter a problem the postgres or mysql didn't support but alas I have not worked on everything.

jodrellblank• 15 years ago

When you need "nobody ever got fired for buying IBM" levels of nontechnical legitimacy?

(I too wonder what technical marvels it can perform which justify the cost).

karambahh2• 15 years ago

When you need multiple spatially distributed active/active write nodes (multiple masters...) you pretty much need Oracle RAC. When confronted with the cost, you end up designing something where writes are pushed to a caching layer, which deals with an active/passive setup, with enough buffer to switch passive to active if something goes wrong.

tomjen3• 15 years ago

Support. You can properly find some solution to your issue with postgres online. Oracle can send an Oracle certified engineer who can solve your problem.

Consistency. Your enterprise depends on terabytes of hyper valuable information (Wallmart with their sales data), can you guarantee that you won't end up with corruption issues? Or that the next version will work with your system too?

That said as long as you make less than 20 mil/year, Oracle isn't likely to be the best solution.

Just a quick question: which engine do you use for your MySQL system?

derrickpetzold2• 15 years ago

InnoDB. I understand your support viewpoint but both of those dbs are opensource thereby leaving support open. From what I understand when the support comes into play is when you can sue the other party for not providing the service expected but there is company support for mysql and postgres so that still leaves me in the dark and I have never experienced corruption issues with either. Though my db experience is limited and I don't claim to be an expert.

Groxx• 15 years ago

Mostly echoing tomjen3 in this, but:

Oracle isn't selling databases. They're selling database support. Similar to how IBM operates - they make good stuff, but you're really not paying for the hardware. If you were, they'd be outrageously expensive compared to something built more simply - see Backblaze, for an example: http://blog.backblaze.com/2009/09/01/petabytes-on-a-budget-h... .

bdunbar• 15 years ago

Can anyone say when Oracle is justified?

When the ERP application you are installing has in it's specs

- Supported database: Oracle

I am sure a crack Postgres guy can bash and file that sucker to work. But the vendor will not support the result, and some places, some situations, that really counts for a great deal.

alanh• 15 years ago

Wow, more projects should have a page similar to this one. Upvoted because it’s good to keep in mind when thinking of sqlite and when documenting your own project.

alleri• 15 years ago

Laughed a lot! :D

"SQLite is not designed to replace Oracle. It is designed to replace fopen()."

Related Stories