8 ms·
"SQLite is not designed to replace Oracle. It is designed to replace fopen()."
- larrik 15y agoI 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 15y agoSQlite 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?
- rbranson 15y 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 15y agoIt's a deliberately simplified view of the problem.
- owenmarshall 15y agoWow. 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.
- rbranson 15y agoDamn, you are so right bro.
- andylei 15y agoreplace some instances of fopen(), not every instance of fopen()
- dchest 15y agoAcorn (image editor) uses SQLite as file format.
- techtalsky 15y agoEven 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 15y agoRegardless 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.
- techtalsky 15y agoBelieve 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.
- StavrosK 15y agoI worked with a thirty-million-row dataset for my thesis (after MongoDB famously choked and ate half my data more than once) with SQLite and it didn't bat an eyelid. It was fast as hell, too.
- 15y ago
- reduxredacted 15y agoBy 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 15y agoSo does that become S-Q-L-ite or S-Q-Lite, you think?
- reduxredacted 15y agoGood 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 http://blog.cleverly.com/permalinks/247.html
- palish 15y agoI say "S-Q-Lite".
- MostAwesomeDude 15y agoAs an aside, to follow Linus' pronunciation, it is Lee-nucks, from the Finnish way of pronouncing "Linus." Americans are used to a different pronunciation of the same name and thus generally adopted a different way of saying "Linux." Language is fascinating!
- Muzza 15y agoLinus is a native speaker of Swedish, not Finnish.
- malkia 15y agoWe 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 15y agoI 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 15y agoI 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 15y agoI 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 15y agoAgreed. And the database can grow surprisingly large (multi GB with many millions of rows) and it still performs quite well.
- kellishaver 15y agoThis 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.
- lsb 15y agoIn SQLite, autocommit is on, so if you weren't explicitly setting your transactions, you might have been commit after each update. Also, the standard disk cache is 2MB, so if you think your DB should have more memory than that, up it with default_cache_size.
- kellishaver 15y agoAh, I wasn't, so that combined with pilif's comments regarding the file being locked for writes would make a lot of sense as to why things were slowing down. Thanks for the info.
- 15y ago
- smharris65 15y agoCan 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 15y agoThey generally relate to NFS - e.g., Linux fcntl() used to lock NFS files only locally. http://en.wikipedia.org/wiki/File_locking#Problems http://en.wikipedia.org/wiki/File_locking#Problems Windows CIFS shares are fine and always have been AFAIK.
- derrickpetzold 15y agoCan 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 15y agoWhen 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).
- karambahh 15y agoWhen 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.
- jacques_chester 15y agoDB2 and Teradata boast comparable degrees of horizontal, multi-site scalability.
- karambahh 15y agoAnd DB2 cost pretty much the same as Oracle... :p
- jacques_chester 15y agoLess, usually, depending on the platform. IBM mostly uses DB2 to sell its fancier hardware. But occasionally you gotta spend money to make money.
- tomjen3 15y agoSupport. 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?
- alanh 15y agoWow, 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 15y agoLaughed a lot! :D