Slashdot Mirror

I think I'd rather see the opposite: That non-relation DBs become the mainstream, and they have SQL added for the odd occasion it is useful. Relational has some nice properties for ad-hoc querying, but for everything else they are a nuisance.

Berkeley DB is a very good non-relational database with multiple language bindings, several storage engines, and transaction support. It's been around for 24 years, and has seen some appreciable use.

But that use was nothing compared to the database explosion that SQLite brought about when it was released. SQLite is almost exactly like Berkeley DB, except that it has a SQL engine on top. Almost everyone is using SQLite, and many Berkeley DB users are moving over to it.

Why? Because SQLite is relational! That constitutes some serious evidence that relationship databases are more than "a nuisance".

Hi, I have tried different socket libraries for Windows and Linux applications. SharkEngine is a free C++ library for network, file and multi threading support. There are also higher level protocol classes so you don't need to reinvent HTTP and SMTP for example. Otherwise QT and Boost are obviously good choices, then there is "Alhem's C++ Sockets Library". I never used ACE, the documentation is confusing and I think John Snader warns in his TCP/IP Programming book about it. Here is a list with contenders:

* SharkEngine http://www.pichat.net/documentation/pichat_sdk_sharkengine
* QT http://doc.trolltech.com/2.3/network.html
* Boost http://www.boost.org/doc/libs/1_37_0/doc/html/boost_asio.html
* Alhem http://www.alhem.net/Sockets/

When it comes to small databases I know only SQLite, which is programmed in C and more powerfull than Berkeley-DB. See http://www.sqlite.org./

Qt and SQLite. Seriously. LGPL and kick-ass! Also, Qt can be compiled to have SQLite completely built-in. No extra libs/dlls needed. It handles networking, threading, IPC, SQL and much more. No need for platform-specific ifdefs.

You beat me to the suggestion.

SQLite is a very powerful SQL database engine that is highly powerful and comes in a single < 500K library. No server needed.

Interfaces to it exist for most modern languages.

Documentation for the C/C++ API is here

A more C++ friendly wrapper for SQLite is on CodeProject

You beat me to the suggestion.

SQLite is a very powerful SQL database engine that is highly powerful and comes in a single < 500K library. No server needed.

Interfaces to it exist for most modern languages.

Documentation for the C/C++ API is here

A more C++ friendly wrapper for SQLite is on CodeProject

SQLite dominates discussions of embedded databases these days, but Berkeley DB still has fans who don't need SQL. There are a lot of comparisons on the web.

The SQLite paid options include some extra features and support ("expedited bug fixes and fast, authoritative answers to common SQLite programming questions"). They do give you something for your money.

I suspect their test methodology isn't very good, in particular the SQLite tests. SQLite performance is largely based on when commits happen as at that point fsync is called at least twice and sometimes more (the database, journals and containing directory need to be consistent). The disk has to rotate to the relevant point and write outstanding data to the platters before returning. This takes a considerable amount of time relative to normal disk writing which is cached and write behind. If you don't use the same partition for testing then the differing amount of sectors per physical track will affect performance. Similarly a drive that lies about data being on the platters will seem to be faster, but is not safe should there be a power failure or similar abrupt stop.

Someone did file a ticket at SQLite but from the comments in there you can see that what Phoronix did is not reproducible.

So, someone makes the absolute bare minimum database that is nothing more than a glorified text file, but uses MySQL syntax.

Or more practically, a patch against the SQL parser of (public domain) SQLite to improve its support for MySQL syntax. SQLite has already accepted such patches: see REPLACE, an alias for SQLite's own INSERT OR REPLACE.

Just to be different, I run: Linux, Lighttpd, Sqlite, Kepler

PostgreSQL is still a *huge* player (in fact, they're pretty-much the only open-source, fully-transactional DB available).

I love PostgreSQL, but I have to acknowledge:

Firebird
H2
Ingres (ancestor of Postgres)
SQLite

None of these are Oracle-killers, but they are all robust, open-source SQL RDBMSs in their own right.

a product that only supports that db, or developers who aren't particularly familiar with what relational databases are supposed to be like

But then why bother with MySQL at all, when you can get use SQLite? It's fast-as-hell, 99% SQL92-complete, works with application servers (JDBC driver), doesn't require any DB services running in the background, etc.

(For J2EE developers...) You can even hook it up to your app with Hibernate. Excellent not just for small websites, but also good for running "demo apps" with dummy data (since you don't have to install a database server).

If you want "a little more" than a simple flat file, perhaps SQLite is the answer? The people on the Firefox team seem to think so, for example.

SQLite has been a pleasure to use for a small personal project involving a few Perl scripts. Granted my background is with SQL Server and Oracle, so perhaps I'm not the target audience, but I found it extremely easy to use and surprisingly efficient--and I didn't need to set up a server or anything. I didn't even need to explicitly create a database!

The READ.ME for this is at http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/genfkey.README You can access this functionality directly from the sqlite3 shell tool (type sqlite3 at the CLI). The syntax is .genfkey ?--no-drop? ?--ignore-errors? ?--exec?

Thank you for pointing out genfkey. Now that I have a keyword to go on, I can understand how one would "recompile" foreign key constraints. Don't get me wrong, I've been using SQLite 3 in a Python-based internal web site for about a year now; I just wanted to get straight what it does and does not do.

The SQLite source tree contains source code and documentation for a C program that will read an SQLite database, analyze the foreign key constraints, and generate appropriate triggers automatically.

The searches I tried in Synaptic on Ubuntu 8.04 LTS didn't appear to turn up any program to make sqlite triggers. Was this program added to Ubuntu only recently, or is it not in Ubuntu at all? And then the download page scares people away from downloading the source code packages: "Their use is not recommented. The SQLite developers do not use them. You should not use them either" (spelling and emphasis preserved).

For MySQL you generally needed InnoDB for referential integrity

But at least MySQL with InnoDB comes packaged for Ubuntu.

If you are looking for a single-app database try SQLite (mentioned in the tools section by the authors) http://sqlite.org/whentouse.html

SQLite's web site states that SQLite silently ignores REFERENCES (foreign key) constraints.

If you are looking for a single-app database try SQLite (mentioned in the tools section by the authors) http://sqlite.org/whentouse.html

SQLite's web site states that SQLite silently ignores REFERENCES (foreign key) constraints.

vacuuming (a PostgreSQL-specific database maintenance operation)

PostgreSQL isn't the only SQL DBMS that can defragment a database. SQLite has VACUUM, MySQL has OPTIMIZE TABLE that makes a dummy ALTERation, and Jet has "Compact and Repair".

Suggesting that you could replace a MS-SQL server with SQLite basically forces anybody in the know to ignore every other point you make.

You're assuming that the person using MS-SQL Server knows what they're doing. How do you know it's more than just a glorified Access database?

MySQL is good, unless you need a highly performent query analyzer.

In other words, the query analyzer is slow? Because the queries work well enough.

Postgres is good, unless you need actual replication features.

Like these?

SQLite is good, if your datastore is less than 1GB.

Another quick Google, and we find these limits -- by default, the maximum database size is just under 32 terabytes.

Not that I'm suggesting it's a good choice at that point, especially with multiple processes. But it does make it kind of hard to take you seriously with that kind of imagined limit, unless you're suggesting there's a practical, performance wall after 1 gig.

There aren't many reasons on a single-user desktop

Who says a desktop is single-user? See: NComputing.

for MySQL to be necessary over SQLite

SQLite, for one, doesn't enforce foreign key constraints. MySQL with InnoDB tables does. And has, say, MediaWiki been ported from MySQL to SQLite for things like a personal wiki on a USB drive?

At least according to them: http://www.sqlite.org/mostdeployed.html

I've never used it but:

Starting with version 3.3.0, SQLite includes a special "shared-cache" mode (disabled by default) intended for use in embedded servers.

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

At first glance it's hard for me to see where Drizzle would fit where SQLite doesn't.

MySQL may not be everyone's cup of tea and it does have some "gotchas" but it does at least have SOME "real" database features that SQLite doesn't. From their FAQ:
Q: SQLite lets me insert a string into a database column of type integer!
A: This is a feature, not a bug. SQLite does not enforce data type constraints. Any data can be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns.

Besides, not everyone needs everything. As you said, "One man's 'superfluous' is another man's key feature." So if they're key features for you, don't use it. There are scores of lowest-common-denominator web apps out there that don't need any of these features. I always use the analogy of a pickup truck and a tractor-trailer--yes, a real rig can do an order of magnitude more than a pickup can, but a pickup is still suitable for thousands of common tasks.

Not everyone needs "real" database features. It's a continuum and you pick the software from the list that fits your needs. Text files work for some things, SQLite works for others, then MySQL, Postgres, MS-SQL, Oracle, etc. The best tool for the job is often the one that does what you need and that you know how to use. I don't NEED to use some comp-sci-grad-approved enterprise-ready database for a damn phonelist. Using a "real" database does not instantly make me a "real" programmer and my choice of DB does not make my work "legit" or not.

Or better yet, SQLite, if you only store data locally.
www.sqlite.org

http://sqlite.org/faq.html#q5

This may be the source of your difficulty? Not sure.

Recognizing that Firefox 3 is only slated for release on Tuesday, I'd guess that you'll see more options forthcoming including an improved version of Weave.

> 10 minutes looking your database's xxxBindxxx functions would have showed
> you that you can bind database columns to binary data areas. IOW, RTFM.

Well, that's just lovely. So where do I find your fine manual? For example, sqlite documentation on its bind commands contains no examples of any kind. I can't even see the relevance there, as they seem to bind a value to a prepared statement, whatever that is. Like I said, I'm not a database guru, I'm just trying to see if there is any way I can use a database as storage for my app. If that requires weeks of hard studying, I think I'll just stick with my flat file, thank you very much.

In a networked database setup (i.e. Oracle on one machine, C++/Java/Python app on another), it is necessarily the case that at some level in your program stack a string conversion will have to be made. This is because the db data transmitted over the network (or between processes on the same machine) is formatted as strings, and must be converted to other data types. Effectively, you are right, at some level someone has to iterate over all the data returned by the db and format it into the data structures that are used by your application.

As a result of this requirement, there has been a proliferation of object-relational mapping frameworks created in recent years (Hibernate, WebObjects in Java, SQLAlchemy, Django in Python, and many more) the purpose of which is to abstract out all that conversion so that the programmer only has to deal with objects. In addition, in most lower-level db-access libraries that accept direct SQL calls, there is some built-in data conversion facility, so that you are getting Integer objects, for example, instead of getting text. I'm not sure about the availability of object-relational frameworks for C-style languages, though. One of the best used to be WebObject's Obj-C version, but that was discontinued years ago by Apple.

Now in the case of SQLite, no network transmission takes place, and all database manipulation happens within the memory space of your own application (which is linked to the SQLite c libraries). For this reason, there is no conversion from number to character data and back again as the data moves from disk through the network to your app. I am not familiar enough with the low-level data structures of SQLite to know whether number data is converted to string data before it is written to disk, but considering the efficiency of the library I would very seriously doubt it. What matters is that when you are iterating over your result set using the SQLite c functions (see: http://www.sqlite.org/quickstart.html) you are able to specify in what format you expect the data to be provided to you (char* or int, for example), and the system will provide it to you.

I think the problem that you might be having is that you are using C++ for your database application. I think that most people developing database applications these days are using Java, PHP, Python, etc. (interpreted languages) because they are easier to code in and because most deployments are not to individual users but to web application servers that are designed for use with those languages. Considering that client-server systems (user apps making direct database connections) are really inferior design-wise (vis-a-vis security, stability, maintainability) in relation to thin-client systems (where the server can most easily be coded in an interpreted language), the only reason that I can imagine for using SQL with C++ is for local data storage. SQLite would actually be perfect for that.

http://www.sqlite.org/

Probably one of the more advanced flat-file serverless SQL database systems with bindings for many languages, and source code to poke at if you feel the need. They say it weighs in at about 250KiB with everything enabled.

From the site:

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen().

How is this moderated as "informative" when it contains blatantly incorrect information? SQLite is most definitely present in all Python 2.5 installations. It's part of the Python source tree. It's as much a part of the Python distribution as the regular expressions library you use in your code. And if you insist on using an older version of Python SQLite is just one apt-get away.

In what specific ways does the supposed "bloat" of SQLite affect the user, exactly?

What kind of "maintenance" does the user need to do on the SQLite source code? It's maintained well enough for some some pretty big users. Who maintains your code and why should I trust you with my data?

How can SQLite be "difficult to use" when it's just standard SQL with a standard Python DBAPI interface?

It's really nice that you wrote a cute little datastore. But there's no need to badmouth what you perceive as the competition.

But sqlite is on a totally different level of reliability; the fact that it is at the core of the iPhone's data management points to the level of confidence that people have in it.

Wait a sec... That's not reliability; that's confidence. Reliability is a performance metric; confidence is not. I suspect — looking at some relevant SQLite pages — that dbtext might actually be more reliable, because my bug list is at zero and has never moved off zero since release, though it is used thousands of times every day just in my own venues. I have no idea how much others use it, though. ;-)

SQLite works from most languages and on most platforms (even embedded ones). It's perfect for most single user things -- although, it's ... er ... skewed toward SQL.

He doesn't need python. He just needs a database. He can download a precompiled binary for windows that allows one to work with the database at the command line. Python is not necessary.

And if the command line is too much, as others have noted there is already a convenient firefox extension for graphically interacting with a sqlite database.

He doesn't need python. He just needs a database. He can download a precompiled binary for windows that allows one to work with the database at the command line. Python is not necessary.

And if the command line is too much, as others have noted there is already a convenient firefox extension for graphically interacting with a sqlite database.

Yeah, you can't really get more permissive than public domain.

I think this would be perfect, I found it when I was looking for a DB which met similar criteria. I don't care about windows, but it does work under windows AFAIK.

  http://www.sqlite.org/

Use it all the time for everthing from trivial databases to several 10s of megabytes. Since query's can be entered on the command line it's quite simple to write an ascii results grabber.

Have you considered using SQLite at all? It's open-source, lightweight and works with Vista.

First thing that came to my mind was SQLite. It's used by FireFox. I have a couple of projects lined up I plan on using it for, when I get the time....

Get it http://www.sqlite.org/ here.
There are GUI clients that work fine for this sort of thing, SQL is simple for doing basic things. One file, one database.

From SQLite's Features page:

Minor niggle - it's 'SQLite', not 'SQLLite'.

Tried Telico or Treeline?

Looks very interesting... What kind of programmatic access does it provide? Can I send it a SQL query?

I disagree -- look at sqlite. It's a wildly successful project, and the authors offer consulting services for folks who want help making customizations (say, integrating it into their commercial projects).

As for me, I'm thrilled about DJB making this change, because it means I can actually use his software! (Something with no license to redistribute is effectively worthless to me -- if I can't even make copies within my own LAN, but need to download every copy separately from DJB, that's far too much hassle -- might as well be building on commercial dependencies).

Haven't the bookmarks been stored in an HTM file since Netscape 2?

Yes.

Aren't they still stored in bookmarks.html?

No. FF3 includes "Places", a new SQLite-based backend for managing things like bookmarks.

so where does SQLite fit in?

...SQLite i beleive (which I never used, so don't quote me, but I beleive that is a less server-centric open source DB?).

It's a non-server public domain database library. Yes, really public domain. Its databases live in files and there's no server component whatsoever; it's intended for use by a single application (although it supports file locking so that multiple processes can access the same database).

I use it in one of my apps. It's awesome. It's tiny (about 300kB), it's simple (no messing about with starting servers), it's blazing fast (for some tasks, it's up to an order of magnitude faster than PostgreSQL or MySQL --- for others, it's slower, though: benchmarks). More and more programs are starting to use it simply as a document store. It's great.

If you find yourself wanting to use SQL in a situation where only a small number of apps at a time are going to want access, or even if you just want to teach yourself SQL (it's got an excellent command line shell), do check it out.

MS Access is a database but it's also a complete program with a nice GUI front-end and lots of other stuff. You can make a whole form-based application with Access. MySQL itself is just a raw DB with nothing but a command-line interface out-of-the-box--in that sense, MySQL is roughly comparable to JET. (Scalability, stability, etc. issues aside.) SQLite is a great little thing but it's really just a step up from a flat text file--it responds to SQL syntax but it's not strict at all. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. Overall, the three are not really directly comparable. For some things, you could use any one of the three and not notice the difference. For anything else, only one might do.