The way this discussion is going we're going to have four layers, with the top one written by Jacob.

I'm lost with this ORM stuff, and I suspect that the vast majority of D users would feel the same.

My MySQL experiment is coming along quite well and would probably cope with most of the other suggestions I've seen for the current top layer.

On 2011-10-12 20:13, Steve Teale wrote:
> The way this discussion is going we're going to have four layers, with
> the top one written by Jacob.

Hehe. As long as there are database connections available in Phobos and a fairly good API with different levels available it should be possible to create an ORM API as a separate project.

> I'm lost with this ORM stuff, and I suspect that the vast majority of D
> users would feel the same.
>
> My MySQL experiment is coming along quite well and would probably cope
> with most of the other suggestions I've seen for the current top layer.


-- 
/Jacob Carlborg

There's been a fair amount of discussion along the lines of:

auto thingie = getThingie("AC/DC", "user", "pwd", "schema");
Row[] rows = thingie.exec(meets(Subject("Peter), Object("Roberta")));
// Print out some rows (if you have the energy left)

The focus points have been how to specify the environment - AC/DC, or MySQL, or ODBC, or whatever, and the nature of meets(...), and whether it might generate SQL, or something completely different.

Are we really working on std.database without conceptual limits, or can I assume that we can use an incremental approach, and that for the moment we are really talking about std.sql?

Also, while we're on the use-case track, can some of you please think about prepared statements, and what binding variables to them might look like?

I'm working along the multiple lines of:

setParam(T)(ref T t, enumDirection d);
setParams(T...)(enumDirection d, T args);
setParams(S)(S s, enumDirection d) if (is(S == struct));
setParams(Variant[] va, enumDirection d);

Do we have to assume that parameters that are IN, OUT, or INOUT will be required in some cases for stored procedure support?

There's also the question of dealing with large column data. JDBC, I think, uses streams to interface to such things, but I wonder if providing delegates as part of the binding process might be better. Any thoughts?

Thanks
Steve

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.

Am 12.10.2011 20:13, schrieb Steve Teale:
> The way this discussion is going we're going to have four layers, with
> the top one written by Jacob.

1 = etc.c.
2 = Database classes
3 = ??
4 = ORM

Please explain ?

>
> I'm lost with this ORM stuff, and I suspect that the vast majority of D
> users would feel the same.


IMO, SQLAlchemy (Python) is a very clean and powerful ORM. Easy to use and provides the _Data Mapper_ pattern (not everybody is enthusiastic about the Active Record pattern) as well as the _Unit Of Work_ pattern.

The UOW pattern organizes pending insert/update/delete operations into queues and flushes them all in one batch. (All or nothing way, in other words transactional.

just in case : http://www.sqlalchemy.org/docs/orm/tutorial.html

On Thu, 13 Oct 2011 11:22:20 +0200, bls wrote:

> Am 12.10.2011 20:13, schrieb Steve Teale:
>> The way this discussion is going we're going to have four layers, with the top one written by Jacob.
> 
> 1 = etc.c.
> 2 = Database classes
> 3 = ??
> 4 = ORM
> 
> Please explain ?

What I meant was that there might be a layer here that would provide a common interface to the supported databases in terms of literal SQL commands, as embodied in Andrei's example

> 
>> I'm lost with this ORM stuff, and I suspect that the vast majority of D users would feel the same.
> 
> 
> IMO, SQLAlchemy (Python) is a very clean and powerful ORM. Easy to use and provides the _Data Mapper_ pattern (not everybody is enthusiastic about the Active Record pattern) as well as the _Unit Of Work_ pattern.
> 
> The UOW pattern organizes pending insert/update/delete operations into queues and flushes them all in one batch. (All or nothing way, in other words transactional.
> 
> just in case : http://www.sqlalchemy.org/docs/orm/tutorial.html

On Thu, 13 Oct 2011 05:42:04 +0100, Steve Teale <steve.teale@britseyeview.com> wrote:
> Also, while we're on the use-case track, can some of you please think
> about prepared statements, and what binding variables to them might look
> like?
>
> I'm working along the multiple lines of:
>
> setParam(T)(ref T t, enumDirection d);
> setParams(T...)(enumDirection d, T args);
> setParams(S)(S s, enumDirection d) if (is(S == struct));
> setParams(Variant[] va, enumDirection d);
>
> Do we have to assume that parameters that are IN, OUT, or INOUT will be
> required in some cases for stored procedure support?

I've used JDBC and some custom C++ code.  I think the JDBC approach is convenient/easy to understand and the C++ approach we used was very similar.  Both had the concept of specifying the parameter by index, so the code looked a bit like:

int index = 0;

setParam(index++, ..);
setParam(index++, ..);
setParam(index++, ..);
setParam(index++, ..);

It *might* be useful to retain the index idea to allow the setting of parameters in any order, but that might simply be flexibility that no-one really *needs* as I can't think of a reason why you would be forced to specify one before another.  The underlying API might require it however, but that wouldn't be a problem as internally we could just keep a parameter index and assign them in the order given.

As for IN, OUT, INOUT.  I've never had cause to use INOUT.  I bind input using SQLBindParameter (specifying INPUT and not INPUT_OUTPUT or OUTPUT) and obtain results using SQLBindCol (as a select effectively), occasionally binding a column for a COUNT or similar 'result'.  I believe the use case for INOUT will be store procedures as you've mentioned, all mine are handled by IN params and colum/select output (a single result in my case).  I think we're going to need to handle all 3 types for maximum flexibility.

> There's also the question of dealing with large column data. JDBC, I
> think, uses streams to interface to such things, but I wonder if
> providing delegates as part of the binding process might be better. Any
> thoughts?

I've never used streams, but then I've never used large columns where it might be appropriate - or I've had the memory/a reason to load the complete column up front.  But, if I was going to I would probably use a stream, it makes sense to me.

What about ranges, could they be used?

R

-- 
Using Opera's revolutionary email client: http://www.opera.com/mail/

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

On 13.10.2011 11:53, Steve Teale wrote:
> On Thu, 13 Oct 2011 11:22:20 +0200, bls wrote:
>
>> >  Am 12.10.2011 20:13, schrieb Steve Teale:
>>> >>  The way this discussion is going we're going to have four layers, with
>>> >>  the top one written by Jacob.
>> >
>> >  1 = etc.c.
>> >  2 = Database classes
>> >  3 = ??
>> >  4 = ORM
>> >
>> >  Please explain ?
> What I meant was that there might be a layer here that would provide a
> common interface to the supported databases in terms of literal SQL
> commands, as embodied in Andrei's example
>

I was thinking more like
1) etc.c.mysql/pg - c interface
2) std.sql.mysql/pg - d interface using templates, ranges etc
3) Database classes - common interface
4) ORM

Am 13.10.2011 18:44, schrieb simendsjo:
> On 13.10.2011 11:53, Steve Teale wrote:
>> On Thu, 13 Oct 2011 11:22:20 +0200, bls wrote:
>>
>>> > Am 12.10.2011 20:13, schrieb Steve Teale:
>>>> >> The way this discussion is going we're going to have four layers,
>>>> with
>>>> >> the top one written by Jacob.
>>> >
>>> > 1 = etc.c.
>>> > 2 = Database classes
>>> > 3 = ??
>>> > 4 = ORM
>>> >
>>> > Please explain ?
>> What I meant was that there might be a layer here that would provide a
>> common interface to the supported databases in terms of literal SQL
>> commands, as embodied in Andrei's example
>>
>
> I was thinking more like
> 1) etc.c.mysql/pg - c interface
> 2) std.sql.mysql/pg - d interface using templates, ranges etc
> 3) Database classes - common interface
> 4) ORM

Sorry, I don't get it. IMHO 2) and 3) are at the same level.. F.I.
a draft :

http://pastebin.com/n8H0QF8X