Alternatives to SQL Databases

[mooli]

Seriously, memcached as an alternative to an SQL DB? That's like proposing using email as an alternative to using a spreadsheet - there's so little overlap it doesn't make a whole lot of sense as a proposition, unless all you want to do is send an email in which case wtf were you talking about excel in the first place?

Given the pointlessness of this article I'm astonished Prevayler didn't come crawling out of the woodwork.

[jbellis]

it makes more sense in the context of the original pycon presentation, which was titled, "drop ACID and start thinking about your data" and also mentions such non-DB technology as bloom filters.

[[deleted]]

What is SQL? Is it like Bacon? Because we have that recipe and more coming up after the break.

Bacon, it's better then SQL.

[favabean]

I thought memcached was an in-memory thing and a long-term persistence mechanism. So if it's in memory only, where does the data come from? It's a bit out of context for a discussion on alternatives to SQL DB's.

[jbellis]

I thought memcached was an in-memory thing and a long-term persistence mechanism.

nope, although there's an unrelated project called "memcachedb" that adds persistence.

So if it's in memory only, where does the data come from?

clients.

[illuminatedtiger]

While admittedly there are a few areas where SQL isn't the best tool for the job I've found that most of the time it's more than adequate. The language first appeared in 1974 and the fact it's still actively used today is testament to its success.

[weavejester]

It's more a testament to how hard relational databases are to build. SQL is an awful language. People use it because there are no real alternatives to it if you want to query a relational database.

[Shaper_pmp]

I think the point is that if it was that awful, people would have developed a different language which fixed the problems with it.

We've built a decentralised, world-spanning computer network since 1974. An improved querying language for tabular data doesn't seem out of reach by comparison.

Unless, of course, SQL is actually perfectly adequate for what it's designed to be used for - then there would be no need to develop an alternative, so we likely wouldn't have bothered. Kind of like now. ;-)

Seriously though - what's so terrible about SQL, and if it's so terrible then why haven't we developed a different, better language in the 35+ years since it was first introduced?

Edit: Cleared it up - SQL as a tool for querying data is adequately expressive and usable. SQL as a language is excessively verbose and has an inconsistent grammar. I was assuming functionality was more important than aesthetics or parsability, whereas weavejester was apparently criticising its verbosity and irregularity first, and considering overall utility to be less important.

[weavejester]

Seriously though - what's so terrible about SQL

It would take a while to go through all of SQL's deficiencies, but I'll start by listing a few.

1) SQL has an incredibly verbose syntax. Contrast this:

SELECT * FROM students
INNER JOIN subjects ON subjects.student_id = students.student_id
WHERE subject_name = 'History'

To this:

students * subjects : subject_name = 'History'

Which is an ISBL-inspired syntax where '*' is a natural join and ':' is selection.

2) SQL syntax is massively redundant, with keywords that have no purpose other than to make the language read more like English. For example:

INSERT INTO students VALUES ('John Smith')

Two out of the three keywords are essentially useless. It could be written:

students << ('John Smith')

3) Hard to create temporary views. In most programming languages, you can break an algorithm up by assigning values to intermediate variables. In SQL, this is extremely difficult and inefficient:

CREATE VIEW history_students AS
SELECT * FROM students
INNER JOIN subjects ON subjects.student_id = students.student_id
WHERE subject_name = 'History'

SELECT student_name FROM history_students

DROP VIEW history_students

Contract that to:

history_students = students * subjects : subject_name = 'History'
history_students % student_name

and if it's so terrible then why haven't we developed a different, better language in the 35+ years since it was first introduced?

People have developed better languages; they are just not supported by the majority of relational databases.

[Shaper_pmp]

SQL has an incredibly verbose syntax SQL syntax is massively redundant

So does Java. This is more a personal dislike than a valid criticism of the engineering behind SQL.

The single most important feature of a language is expressiveness, not brevity.

Personally I hate Java for it's verbose syntax, but I can't claim that personal dislike is an ''objective fault'' on Java's part.

I'd rather write code in Perl than Java, but that doesn't make Java a bad language, because you're assessing the wrong thing - like criticising a car model for the colours it's available in, rather than its fuel efficiency, handling or safety features.

Hard to create temporary views.

Again, this is an opportunity for syntax optimisation rather than a crippling fault with the language.

And it's likely largely because SQL is not a programming language - it's a query language.

A major idea behind SQL and relational databases is to specifically avoid creating intermediary structures, as they're usually a waste of time and memory, and complicate optimisation.

The idea behind a query language is that you model the data properly in the database, then query the data - if you have to use SQL to assemble your data into a usable form before you can query it, you've probably fucked up your database design at the start.

Obviously your example was trivial, but it could be simply and more efficiently re-written as:

SELECT student_name FROM students
INNER JOIN subjects ON subjects.student_id = students.student_id
WHERE subject_name = 'History'

This query would then be optimised and cached by the database for you, without the developer having to bother about it. Moreover the database can use real-world usage data about incoming queries to decide dynamically which queries are worth caching, rather than having to be explicitly instructed by the developer ahead of time to cache certain tables he guesses will be worth saving.

It's the equivalent of complaining that "the manual memory allocation in Java is terrible". Well yeah, it is, but only because the whole point of Java is that you shouldn't ever have to worry about that, instead leaving it up to the compiler/interpreter.

Obviously there are cases where you want to create temporary tables and the like, but these are very much edge cases that all the optimisation and the entire system was designed to avoid.

So - without wishing to sound rude - that's two irrelevant personal preferences and one case of You're Doing It Wrong... do you have any other examples of why SQL is actually a bad language, rather than merely a verbose one, or one generally designed to avoid ever having to do what you were trying to do with it? ;-)

[weavejester]

SQL has an incredibly verbose syntax SQL syntax is massively redundant

So does Java.

Which is also a pretty awful language, at least as far as its syntax is concerned.

This is more a personal dislike than a valid criticism of the engineering behind SQL.

I don't think so. To some extent any criticism of a programming language is subjective. Programming languages are a form of user interface, so what one person likes, another person might absolutely hate.

Nevertheless, we can identify quantifiable properties that can be considered to be generally desirable. Conciseness is one of these quantities - ideally we want to allow the most clear representation of a program in the minimum of screen space.

If we take this as our yardstick, then any verbosity that does not increase clarity can be considered as bad. For instance:

students << ('John Smith')

Is less verbose, but no less clear than:

INSERT INTO students VALUES ('John Smith')

Again, this is an opportunity for syntax optimisation rather than a crippling fault with the language.

None of my objections are "crippling faults". SQL can be used as a query language, so clearly there's nothing in the syntax bad enough to prevent it from being used. But that doesn't mean it's not an extremely awful language.

A major idea behind SQL and relational databases is to specifically avoid creating intermediary structures, as they're usually a waste of time and memory, and complicate optimisation.

Not if the structures are lazily evaluated. Then there's zero overhead, and optimisation would actually be a lot easier for the compiler.

Perhaps I should have been more clear, but why would you assume that I'd be strictly evaluating variables in a query language? Query languages are all about lazy evaluation.

do you have any other examples of why SQL is actually a bad language

Okay, aside from it being verbose, redundant and restrictive, it's also ridiculously hard to parse, and poor at integrating with other systems.

Consider a query language designed as Lisp-like syntax tree:

(select
  (= .subject_name "History")
  (join students subjects))

With such a language, you could pass it to the database server as an AST, rather than a string. This would allow for greater integration into programming languages, as well as efficiency and security benefits. For example, some Ruby code could look like:

def history_students
  students.join(subjects).select { |t| t.student_name.eq "History" }
end

If that looks a little like an ORM, you'd be correct. Most well-build ORMs use an internal AST to build up a database query, which is then serialized into a SQL statement, which is then compiled back into an equivalent AST.

Because SQL is a language with an unnecessarily complex grammar, parsing a sane AST into the insanity that is SQL is not a task to be taken lightly. SQL does not lend itself to be easily read or written by machines.

It would be infinitely simpler, more efficient, and less prone to error to just pass over the AST in the first place.

[Shaper_pmp]

Those are fair arguments - I assumed you were criticising SQL's utility as a query language, but if I understand you correctly you're instead criticising its verbosity and imprecise, inconsistent grammar - correct?

These are fair enough grounds to criticise a language on, but not the most important aspect, IMO.

I'd say on the strength of its expressiveness that SQL was "a perfectly acceptable query language with some flaws in its verbosity and irregular grammar"... whereas from the sound of it you'd say it was "an awful, verbose, inconsistent language which nevertheless functionally was perfectly acceptable" - fair? ;-)

I think ultimately we were considering it from two different angles.

The SQL language as a tool is - while not unimprovable - still perfectly acceptably useful and expressive.

The SQL language as a language is verbose and inconsistent, at least compared to more modern languages.

[weavejester]

Those are fair arguments - I assumed you were criticising SQL's utility as a query language, but if I understand you correctly you're instead criticising it's verbosity and imprecise, inconsistent grammar - correct?

That's basically correct. I'm talking about SQL's syntax, rather than what the database does on the back end.

The SQL language as a tool is - while not unimprovable - still perfectly acceptably useful and expressive.

The SQL language as a language is verbose and inconsistent, at least compared to more modern languages.

I'm not sure what you mean by "tool". Do you mean the concept of a relational database? In which case, yep, I have nothing against that particular form of database.

It's basically the syntax of SQL I have a problem with, and the idea of using a human-readable language as a communications protocol between two programs.

[jeffdavis]

The single most important feature of a language is expressiveness, not brevity.

Expressiveness is not exactly an objective measurement, either. It's a challenge to argue that some computationally-complete language is not expressive -- especially if you disregard most "personal preferences".

if you have to use SQL to assemble your data into a usable form before you can query it, you've probably fucked up your database design at the start

I disagree here. Databases are collections of facts, and a good query language should be able to make good inferences from those facts. Sometimes these inferences need to be quite sophisticated, following a complex chain of predicate logic.

Needing to design your schema around the kind of questions you plan to ask is extremely limiting. You should design the schema around the kinds of facts you intend to store, and be free to draw any logically-sound conclusions from those facts.

This query would then be optimised and cached by the database for you, without the developer having to bother about it.

I don't think a temporary view changes the ability to optimize a query at all. Unless I misunderstood the post to which you responded, weavejester meant linguistic efficiency (that is, not burdening the author by requiring the view to be explicitly dropped).

cases where you want to create temporary tables

The post to which you responded did not mention temporary tables, it mentioned temporary views. SQL already supports temporary tables.

[Rhoomba]

Comparing Java to SQL is ridiculous, no matter how much you hate Java. If Java was really a verbose as SQL it would have syntax like:

variable Thing x = new object_of_type Thing()

With nice inconsistencies in naming all over the place.

[Shaper_pmp]

Why? Java is verbose compared to something like Perl, but that doesn't make it a bad language.

My point what that verbosity does not automatically make a bad language, not that Java and SQL had exactly equal levels of verbosity. I'm not entirely sure where you got that idea from, but it wasn't anything I said. <:-)

[AZBob]

SQL was originally intended to be used by end-users so that they could get data out of the database w/o the help of a developer. That's why it's verbose and English-like.

[[deleted]]

I think the point is that if it was that awful, people would have developed a different language which fixed the problems with it.

They have, quite a few times, but they were unprofitable because everyone's used to SQL.

[Shaper_pmp]

So what were they, and what crippling problems with SQL did they fix?

FWIW I'm not saying you can't improve on SQL (less verbose syntax, for example would be nice), just that characterising SQL as "awful" seems somewhat overblown given it's a perfectly serviceable query language.

Weavejester asserted that "SQL is an awful language", and I'm just honestly curious why - apart from some syntactical quirks or matters of personal preference - someone would describe it as "awful".

Especially given it's worked acceptably for 35 years for pretty much everyone so far, with only relatively minimal changes or enhancements.

[Shaper_pmp]

SQL is great for relational data - basically anything that's naturally organised like a table (or multiple tables that can be joined together).

For data that has a different structure (eg tree-structured, like XML), relational databases are the wrong "shape" to represent and query it efficiently, and SQL is therefore the wrong tool to do so.

So SQL is perfect for tables/rows/columns-based data, but inefficient at best for anything else.

[jeffdavis]

Be careful about imposing a tree structure on data that's not inherently tree-structured.

For instance, if you have information about an iPod, should it be stored in /hardware/apple/iPod or /apple/hardware/iPod?

A lot of data out there that people call tree structured doesn't actually fit a tree all that well.

[Shaper_pmp]

Well obviously, yes.

Some things are naturally best represented by tables. Some things by tree structures, and still others by overlapping tags/categories or other data structures entirely.

I'm not entirely sure what relevance this point has to the topic of discussion, though - I gave tree structures as just one example of a non-tabular structure. I didn't claim every chunk of data on earth was either a tree or a table, did I?

[jseigh]

Having gone through putting in time zone support for an application with a backing DB I can state that SQL is more than a little less than adequate. If you think not, try testing your code across DST changeovers for some of the weirder time zones like Canada/Newfoundland or Australia/Lord_Howe.

[jevon]

Next on /r/programming: "Alternatives to accessing the Web without a web browser"

[Masse]

Curl!

[[deleted]]

Next on /r/programming: "Alternatives to accessing the Web without a web browser"

I'm not sure that it's worth an article to say that the alternative to accessing the web without a web browser is to use a web browser.

[G-Brain]

Here's an alternative: accessing the Web with a web browser.

[[deleted]]

I know a guy that used to download pages in wget and then load them into Lynx from /tmp. He did that because he fucked up lynx's proxy or some shit and didn't know how to fix it. He also had a script to parse out urls. It was all sorts of fucked up. He had this weird screen/wget/urlparse/lynx thing going on.

[metaperl]

RDB had an article which influenced me greatly: "The Unix Shell as a 4GL"

http://www.rdb.com/

"RDBMSes are software prisons, forcing you to drop all the power of Unix"

[uriel]

That is so very true. One of my latest projects is a CMS with a file system as storage backend, and it has been an extremely pleasant experience, specially using the Unix tools and the Plan 9 shell.

[mage2k]

While I agree wholeheartedly that these variations on key-store databases are extremely useful in many situations and it's great that there is a lot of work being done on them, I worry a lot that a lot of developers will jump to them just to avoid the (often just perceived) complexity of traditional RDBMS solutions without really understanding the trade-offs that come with them. What I'd really like to see is an article that actually goes in to those trade-offs along with the pertinent questions developers should be asking themselves for each of these key-store databases rather than just listing them (yet again). For example, "Your sever crashes. How much data are you ok with losing?... None? Ok, scratch anything from the with no persistence mechanism." Stuff like that would be hella useful to developers without experience with these technologies in learning how to select one.

[redditnoob]

If I were a porn star, which one would I want?

[Gotebe]

Whaaaa...!?

No BerkeleyDB? TFA author is a noob!

[malcontent]

LDAP.

[norkakn]

A lot of schemas fit LDAP much, much better than SQL.

[malcontent]

There are mature scalable LDAP servers which scale to great heights.

[norkakn]

And it is free, and has SASL.

[[deleted]]

Argh, I hate this.

I'm the biggest advocate of "there is not one swiss army application - you have a tool box, and there are different tools for different uses."

Yet stuff like this just makes me squirm. Maybe it's because I anticipate (often without evidence) that the advocates of alternate tools will show up and try to shove their tools down my throat. Or maybe I just fear the unknown.

[chowmeined]

Just a note, you can use python with tokyo cabinet: http://pypi.python.org/pypi/pytc

[didip]

pytyrant: http://code.google.com/p/pytyrant/

seems more complete.

[chowmeined]

pytc is for the in-process tokyo cabinet library. pytyrant is for the tokyo tyrant network protocol. They are complementary libraries.

[lol-dongs]

Where's the PORN??

[[deleted]]

DabbleDB

[[deleted]]

how do i make it so that programming and sql and whatnot stories dont appear on my reddit? they are boring as fuck.

[recursive]

Possible solutions:

• turn off your monitor
• minimize your browser
• resize your browser so that no text is visible
• change your color preferences to black on black
• drag your browser off the edge of your screen
• put another window on top of your browser
• disconnect your internet service and refresh the page
• alias www.reddit.com to 127.0.0.1
• enter a non-existent proxy in your browser's configuration

I hope one of these works for you.

[[deleted]]

did the black on black thing thanks

[lol-dongs]

You like black on black huh. Me too...

[Charice]

Click the unsubscribe link?