Subject: Re: [boost] [rdb] Uploaded 0.0.07
From: Jarrad Waterloo (jwaterloo_at_[hidden])
Date: 2009-09-25 10:30:42
> On Thu, Sep 24, 2009 at 4:44 PM, Jean-Louis Leroy <jl_at_[hidden]> wrote:
>>> Ah, useful. :)
>>> For note, Python is strongly-typed, have to be correct about what you
>>> send to the DB with regards to types, if you send an int to a string
>>> column, or a string to an int, or whatever, it will throw an
>> I know that it's typed - it's *dynamically* typed. C++ is *statically*
>> typed. "Type-safety" is a term that has a well-defined meaning in C++
>> parlance : it means that type checking happens at compile-time. And btw I am
>> *not* a static analysis bigot, as my other language of expertise is Perl and
>> my favorite is Lisp.
> Lisp is just wonderful. :)
> Actually, that link you did is exactly what the base of SQLAlchemy is
> (and mostly what I use, I rarely use the ORM parts). That is the part
> that is optimizes expressions specifically for the back-end. When you
> create the database it returns a refinement of that database, anything
> you create from it will be optimized specifically for that DB. it is
> a lot more work, but it allows it to be the most efficient SQL library
> for Python, and allows new SQL dialects to be added with no front-end
> Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost
I believe you are referring to when standard SQL is not enough either
because the database vendor supports it poorly or not at all or their is
some feature that application developers need which isn't standard SQL
but most databases have via there proprietary extensions. These examples
are a few examples that highlight the need for the generated SQL to vary
on the database type and version both of which if memory serves is
available from ODBC.
When standard SQL is not enough either because the database vendor
supports it poorly or not at all:
1) Consider table value constructors according to SQL-92 or SQL-99.
There are 3 ways insert data INSERT .. VALUES (...) for 1 row, INSERT
... SELECT for many rows and table value constructor which is similar to
VALUES but support multiple rows of static data; INSERT ... VALUES
(...),(...),(...),(...) ... Each of the (...) is considered a tuple.
Many databases support the standard but some don't support tuples
whether they be table or row value constructors. Consider Microsoft SQL
Server 2000 and 2005. Neither supports table value constructors but they
can emulate it with INSERT ... SELECT UNION [ALL] SELECT UNION [ALL]
SELECT. Microsoft SQL Server 2008 supports both table and row value
constructors but mistakenly call them both row value constructors. Table
valued constructor even if emulated is required by application
developers because it is significantly faster than running a INSERT
hundreds of times. Also because it is all in one statement, it mitigates
deadlocks since single statements are naturally ACID. I am not familiar
with SQLAlchemy but Hibernate requires on configuration specifying the
dialect being used. So for MSSQL 2000 and 2005 in the boolean field
'support table value constructor' would be set to false and the
framework would generate the correct SQL.
When it isn't standard SQL but most databases have via there proprietary
2) Auto increment is common practice though not necessarily standard. As
such after doing a single insert it would be nice to get the generated id.
MSSQL: SELECT SCOPE_IDENTITY()
MySQL: SELECT LAST_INSERT_ID()
SQLite: SELECT last_insert_rowid()
ORACLE: earlier versions unfortunately didn't support auto increment,
rather they used sequences which are named queryable number generators
Current I have only noticed JDBC providing the low level API's for doing
so and theirs an even work on mutiple insert. I am not sure how they
performed that in standard SQL if even possible and currently I only
know MSSQL 2005 and above supporting a select before and after image on
insert, updates and deletes.
3) At some point developers are going to want to make tools and will
need properly abstracted way of getting the metadata.
Because SQLite doesn't support INFORMATION_SCHEMA, its dialect would
require a more elaborate code based transformation and parsing to glean
the information from sqlite_master.
4) Paging support is potential complex but essential for developers
Many databases such as MySQL and SQLite make it easy by providing LIMIT
and OFFSET clauses that gets tacked onto ones SQL statement. Once again
MSSQL makes it unbearable by requiring one's SQL to be massively
transformed to generate row numbers and to filter on them.
I hope these examples help illustrate that some transformation needs to
be able to performed earlier rather than latter. By providing some minor
concessions in one library can developers from having to tackle these
issues over and over again thus resulting in more portable code. With
over a decade of database experience using Oracle, SQL Server, MySQL,
SQLite and others using ODBC, OLEDB, ADO, ADO.NET and JDBC and also
using Hibernate, NHibernate and LINQ: I would be glad to volunteer my
time to help identify and justify these and other requirements.