replace sql

[buhman]

Though we proved last night that all of our current procedures other than paste_get_stats have something approaching constant-time complexity, I think the complexity of our current code is rather ridiculous.

---

Here are the problems so far:

• Three tables, each with roughly the same kind of object/thing

We did this because it made more sense than breaking indexing, or making special metadata columns that ultimately identify what kind of paste this is, then having to twiddle metadata bits in queries.

• SQL procedures separate from python code

There are multiple reasons for this, mostly because: 1) SQL injection is impossible, because no SQL is ever executed from the application 2) as a result, we also get the benefit of only parsing SQL once (on schema load), which makes queries faster

---

Here's why we got rid of the previous ORM (sqlalchemy):

• slow and clumsy

Enough said.

---

However, as you'll notice, we've only actually fixed the 'slow' part; clumsy is back, only in a different form. The way to fix the clumsiness (and the problem in its entirety), in my opinion, is to replace SQL entirely.

Mongo in particular fits our data model very well. First read the terminology comparison. In the first few seconds we learn:

• SQL is replaced entirely by query language that can be represented entirely in native python functions and dictionaries.
• We can have documents with different fields in the same collection--only those with fields that match (both what we wanted to match and what we want back) our query will be returned.
• We get unique primary keys for free--no need to fuck with AUTO_INCREMENT nonsense.

If you're not convinced, here's how MySQL doesn't help us whatsoever:

• There are no relationships between tables, thus the relational database model makes zero sense here.
• If our data isn't relational, it makes zero sense to express it in a relational language like SQL.

All of these facts combined, I'd like to replace entirely our use of MySQL with MongoDB.

[buhman]

One thing will break as a result of doing this: base66 paste IDs will be replaced with yet-another-scheme.

[buhman]

http://docs.mongodb.org/manual/core/index-sparse/#sparse-index-on-a-collection-cannot-return-complete-results

[jdppettit]

Let's do it