« Storm, SQLAlchemy, and GeniusqlPlease don't use wsgiapp »

Looks like I reinvented the wheel again


Permalink 08:18:51 pm, by fumanchu Email , 528 words   English (US)
Categories: Dejavu

Looks like I reinvented the wheel again

I just followed a link from LtU to a paper on Safe Query Objects. The content of the paper was immediately familiar to me, since Geniusql does exactly what is described therein: use native language (Python) semantics to construct remote queries.

Notes while I read:

Despite their usefulness, call level interfaces have a number of significant problems. First, the embedded database programs are not checked until they are passed to the CLI at runtime. As a result, the syntax and types of database programs are not checked statically, but instead result in runtime errors. This is true despite the fact that the database structure is almost always static and known when the client program is compiled.

Geniusql deals with this by obtaining and checking a complete model of the database types. For example, an object with a unicode attribute would be mapped to a Table object with (say) an instance of the geniusql.providers.postgres.TEXT class.

Second, programs that use call level interfaces are difficult to write and maintain. There are important classes of queries that must be constructed dynamically at runtime. Manipulating programs as strings is complex and error-prone due to the complex rules for nesting expressions, quoting constants, and the interplay between embedded and host languages.

Which is why Geniusql eschews strings in favor of Python lambdas.

Concepts that are relatively straightforward to express in a language, like query parameters, are awkward to specify and invoke via an API. Query results are represented as untyped objects that are accessed by string names. There are many subtle but unchecked dependencies between the query being executed and the code that decodes its results.

Geniusql determines both the database type and the Python type of each result column, and provides adapters between them (which you can override if needed).

Finally, call level interfaces make it difficult to reuse queries; doing so involves complex manipulation of programs at runtime, while avoiding name conflicts and ensuring consistency of the resulting query.

True, but reuse can manifest itself at many levels. Quite frankly, I've found the gains from reuse of DB query plans to often be completely offset by the cost of libraries constructing and maintaining the requisite prepared statements. Geniusql caches the generated SQL for each AST instead.

In its simplest form, a safe query object is just an object containing a boolean method that can be used to filter a collection of candidate objects. ...syntax and types are checked at compile time: if manager is misspelled, a compile time error is produced.

Geniusql uses Python lambdas in a similar fashion. Of course, Python is dynamically typed, so we only get syntax checks at compile-time, but that's a huge benefit all by itself. And, because we're using Python, we can skip all the metaprogramming in the Java solution. Instead of writing:

class SalaryLimit instantiates RemoteQueryJDO
                  extends SafeQuery<Employee>
    double limit; /* parameter */
    SalaryLimit(double limit) {
        this.limit = limit;
    boolean filter(Employee employee) {
        return employee.salary > limit;

we can get it all done in one line:

Employee.select(lambda e: e.salary > limit)


Comment from: Christian Passwaters [Visitor]

Well that certainly puts things into perspective.

08/11/07 @ 18:37
Comment from: mike bayer [Visitor] · http://www.sqlalchemy.org

without reading the article since I'm a little PDF-averse tonight, I think the critique that SQL syntax errors are not checked at compile time and instead raise exceptions when passed to the database at runtime is something of a strawman. First of all, an application which generates SQL specific to a particular database is far more likely to get it right than a straight human-entered SQL string. But if syntactical incompatibilities still remain (which can happen say, if you use a SQL string using a MySQL5-specific feature, and youre on MySQL4.1), i certainly would want the database to tell me that something was incompatible, rather than the client program being tasked with "guessing" every possible vendor/version incompatibility (or even worse, detecting a non-5.0 database and then just throwing up its hands saying, "incompatible database version"). its very C++-like to classify every possible "runtime" error as "bad" (I would say Java-like too, but i give the java community more credit than that, being a card carrying member myself...).

08/11/07 @ 22:05
Comment from: fumanchu [Member] Email


I don't think the benefit of compile-time checking is so much about incompatibilities between versions as it is about normal programmer typos for simple expressions. For example, lots of ORMs allow you to write SQL in strings like:

TableA.select("TableA.Day = 3")

If the correct operator is ==, this won't be caught at compile-time, because the expression is not statically checked, it's dynamically checked when the statement is executed. Geniusql avoids this by decompiling Python lambdas; SQLAlchemy avoids them by overriding Python operators.

So it's not that every runtime error is bad, it's just that catching more errors earlier can be beneficial.

08/12/07 @ 00:39
Comment from: mike bayer [Visitor] · http://www.sqlalchemy.org

Agreed. the "versioning" thing was me trying to hypothesize a scenario where a constructed SQL expression could still raise a runtime error due to syntax.

08/12/07 @ 12:28

Leave a comment

Your email address will not be revealed on this site.

Your URL will be displayed.

Please enter the phrase "I am a real human." in the textbox above.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)
September 2017
Sun Mon Tue Wed Thu Fri Sat
 << <   > >>
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30


The requested Blog doesn't exist any more!

XML Feeds

powered by b2evolution free blog software