[std.database]

Jacob Carlborg doob at me.com
Thu Oct 13 08:48:23 PDT 2011


On 2011-10-13 09:51, Kapps wrote:
> 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.


That looks nice.

-- 
/Jacob Carlborg


More information about the Digitalmars-d mailing list