[std.database]

[Kapps]

On 12/10/2011 12:57 AM, Jacob Carlborg wrote:
> On 2011-10-11 23:31, Andrei Alexandrescu wrote:
>> On 10/11/11 3:05 PM, Jacob Carlborg wrote:
>>> If we're talking use cases and high level interfaces I would go with
>>> something like:
>> [snip]
>>> I recommend that everyone take a good look at ActiveRecord in Ruby on
>>> Rails:
>>>
>>> http://guides.rubyonrails.org/active_record_querying.html
>>> http://guides.rubyonrails.org/association_basics.html
>>> http://guides.rubyonrails.org/active_record_validations_callbacks.html
>>
>> I confess the example you gave looks very foreign to me. From consulting
>> http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
>> active records esentially recode relational algebra in Ruby (as for the
>> constructs the equivalent SQL is shown).
>
> Yes, exactly. The point is to have as much as possible in functions
> instead of string literals and have a more Ruby like API than an SQL
> looking API.
>
> connection.select("*").from("users");
>
> instead of
>
> connection.execute("select * from users")
>
> Then they wrap everything in an object oriented API.
>
>> For a variety of reasons, this would be tenuous in D. One simple reason
>> is that e.g. lambdas don't offer access to textual representation, which
>> would be necessary to translate lambda-based conditions into SQL text.
>
> ActiveRecord doesn't support these lambda-based conditions out of the
> box. It is possible with the help of plugins, which uses a Ruby parser
> to get things done.
>
> I though that it might be possible to do in D, without the use of a
> parser. Take this for example:
>
> Post.where(p => p.title == "asd")
>
> "p" would be some kind of object/struct that overloads opDispatch. The
> opDispatch method would return an object/struct that overloads opCmp and
> opEquals. opCmp/opEquals would then return an object/struct that have
> recorded the comparison. The "where" method can then translate it in to
> raw SQL.
>
> To this to work opCmp/opEquals need to be able to return a struct or an
> object, I don't know if this is possible.
>
>> I might be narrow-minded, but I thought we're still looking at writing
>> and executing good old SQL code.
>>
>>
>> Andrei
>
> That would of course still be needed. I would consider that interface
> sit in the middle layer, above the lower driver level and below a higher
> ORM like level.
>
> Everyone is of course free to choose at which layer they want to
> interface with the database.
>

Agreed, I don't think it's unreasonable to have an API for easier 
interoperability between databases that use slightly different syntax. 
The implementation isn't exactly difficult, and it makes for a very nice 
benefit and alternative to writing raw SQL (plus, prevents you from 
doing things like making a typo on a WHERE clause or missing a comma or 
semi-colon).

My approach to this was to just have a Query structure, and internally 
each database parses it as is. The query structure basically just has an 
array of where clauses, selects, etc, all of which are strings, with 
methods such as where or select that simply append to the appropriate 
clause and return the same query for chaining. The db then generates SQL 
for it, and returns a prepared statement. I rather like this approach, 
as (in my opinion) it looks cleaner, is more verifiable and, for my 
projects, I use it instead of actually writing my sql queries when possible.

Example:
query q = query("Customers").select("LastName","MiddleName")
.where("FirstName = ?")
.limit(10)
.select("CustomerID")
.order("LastName", Ascending)

The above would be passed in to a prepared statement, and for MySql 
would generate the statement "SELECT LastName, MiddleName, CustomerID 
FROM Customers WHERE FirstName = ? LIMIT 0, 10;". For something like SQL 
Server though, it could use TOP 10 instead of LIMIT 0, 10.

One of the other nice things is that it makes it simpler to make even 
higher level APIs for it. For example, you could generate a statement 
that populates a struct by just using
'query("Customers").select!(MyCustomerStruct)().where("Name=?")',
which would just call Select on all the fields without having to worry 
about how to generate the SQL for it.