Model design / scaffolding survey

[Ralph Schindler]

Survey - The topic of scaffolding.

When it comes to modeling objects to database records, which does
everyone prefer in the design stage:




1) creating a meta file in a pseudo-language or xml (like propel) that
will then be used by a program to generate objects as well as create the
database structure?

- or -

2) create a database structure, then run a program that will read the
schema and create objects based off of what it found.



-ralph

[Jens Kleikamp]

Ralph Schindler wrote:

> Survey - The topic of scaffolding.
>
> When it comes to modeling objects to database records, which does
> everyone prefer in the design stage:
>
>
>
>
> 1) creating a meta file in a pseudo-language or xml (like propel) that
> will then be used by a program to generate objects as well as create the
> database structure?
>
> - or -
>
> 2) create a database structure, then run a program that will read the
> schema and create objects based off of what it found.
>
>
>
> -ralph
>

Hai Ralph,

I´m in favour of creating model classes by hand because I don´t need a
class(file) for every table in the database.

just my 0.002 cents

//Jens

[Steven Van Poeck]

Personally, I prefer the second: create a database and run a script on
it to provide you with the corresponding classes.
If, for any reason, there are classes you don't need, I suppose you can
just delete them (if they're not related).

Best regards,
Steven Van Poeck

Ralph Schindler a écrit :

> Survey - The topic of scaffolding.
>
> When it comes to modeling objects to database records, which does
> everyone prefer in the design stage:
>
>
>
>
> 1) creating a meta file in a pseudo-language or xml (like propel) that
> will then be used by a program to generate objects as well as create the
> database structure?
>
> - or -
>
> 2) create a database structure, then run a program that will read the
> schema and create objects based off of what it found.
>
>
>
> -ralph
>
>
>

[Nayana Hettiarachchi - GMI]

I'd like to throw in a question along these lines to all your experts on this subject. Conventionally I have been told ( by old skoolers of course) that I should always write out my queries in the DB layer ( yes this is a very time consuming task) I like the idea of having an abstract class that generate the queries and pull data based on your parameters,

When it comes to CPU usage, having php doing the dynamic query generation is it so neglective when it comes to the modern day high powered computers? What are your thoughts on this matter?




Personally, I prefer the second: create a database and run a script on
it to provide you with the corresponding classes.
If, for any reason, there are classes you don't need, I suppose you can
just delete them (if they're not related).

Best regards,
Steven Van Poeck

Ralph Schindler a écrit :

> Survey - The topic of scaffolding.
>
> When it comes to modeling objects to database records, which does
> everyone prefer in the design stage:
>
>
>
>
> 1) creating a meta file in a pseudo-language or xml (like propel) that
> will then be used by a program to generate objects as well as create the
> database structure?
>
> - or -
>
> 2) create a database structure, then run a program that will read the
> schema and create objects based off of what it found.
>
>
>
> -ralph
>
>
>

[GavinZend]

Although the questions were asked in a general context, I'll answer in
the context of ZF.

Briefly, for the ZF approach to using modeling objects with data backed
by a RDBMS, we'd like to:
- avoid auto-generating any code or configuration files,
- avoid requiring the developer to write code specific to implementing
an ActiveRecord, when the database schema can support a clean mapping of
a table's rows to ActiveRecords (
http://www.martinfowler.com/eaaCatalog/activeRecord.html )
- avoid requiring developers to scatter their "code" across
configuration files and PHP source files,
- rely on database introspection,
- automatically publish an extensible API providing access to underlying
tables that follow naming and structural conventions (similar to most  
interpretations of ActiveRecord).  

My quick summary of the ZF's approach to achieving its goals (
http://framework.zend.com/roadmap ):
- Keep it simple!  Why?  So ZF developers can get their work done
quickly with as little pain as possible.
- Solve the 80% first.  What?  We need to first make the ZF solve the
most frequent and common problems frameworks are supposed to solve first.
- Interoperability. Why? The ZF should make extra efforts and smart
compromises to create unusually flexible, well-integrated, and
"pluggable" components that "play well" with each other, including
mixing the use of "ActiveRecord" style access for some tables, and other
access styles for other tables.

I'm also experimenting with mixing ActiveRecord and DAO patterns, as the
latter better accommodate more complex object modeling needs when the
underlying schema doesn't quite fit the desired object domain patterns.

Ok, so how does all that answer the question that was asked?  I needed a
little context and common understanding of terminology first ...

If the ZF supports an interpretation of ActiveRecord, yet allows some
flexibility with DAO-like capabilities, then a ZF developer could design
by creating an object domain and a supporting schema, relying on
ActiveRecord-style tables/classes when there is naturally a close
correspondence between tables and desired classes, but fall back to one
of the other ZF mechanisms for working with other situations, all within
the same application.  Hint: Debate, suggestions, feedback, opinions,
and more are most welcome.

Alternative 1 (Mapping)
=====================
For non-trivial domain models, the underlying auto-generated database
schema resulting can be quite complex.  If not auto-generated /
synchronized / maintained, then the effort required to specify,
maintain, and enhance the configuration information (e.g. like propel)
throughout an application's life cycle does not seem to fit the ZF's
approach to solving the stated goals ( http://framework.zend.com/roadmap ).

I found this person's experience (
http://www.kuro5hin.org/story/2006/3/11/1001/81803 ) with alternative
(1) roughly consistent with my experiences.  Although long, the blog
entry doesn't require the reader to possess a deep past experience with
Java or ORMs, and uses humor to keep the read entertaining.

Alternative 2 (Wrapping)
=====================
ActiveRecord.
Overview of Ruby's interpretation:
-
http://www-128.ibm.com/developerworks/java/library/j-cb03076/?ca=dgr-lnxw01ActiveRecord

Alternative 3 (DAO)
=====================
A Java/Sun-centric interpretation of DAO:
- http://java.sun.com/blueprints/patterns/DAO.html

Using a framework, much/most of the business logic does not need to
reside in DAOs, and can reside in classes that work with the data
abstractions created by the DAOs.  DAOs can provide a type of manual
mapping of schemas to objects and vice-versa, such that objects need not
exactly resemble the underlying schema (e.g. Class 1-to-1 Table).  Thus,
I consider DAO as something not identical to either the ORM mapping or
Active-Record style wrapping approaches.

Cheers,
Gavin


Ralph Schindler wrote:

> Survey - The topic of scaffolding.
>
> When it comes to modeling objects to database records, which does
> everyone prefer in the design stage:
>
>
>
>
> 1) creating a meta file in a pseudo-language or xml (like propel) that
> will then be used by a program to generate objects as well as create
> the database structure?
>
> - or -
>
> 2) create a database structure, then run a program that will read the
> schema and create objects based off of what it found.
>
>
>
> -ralph
>
>

[Markus Wolff-4]

Ralph Schindler schrieb:

> Survey - The topic of scaffolding.
>
> When it comes to modeling objects to database records, which does
> everyone prefer in the design stage:
>
> 1) creating a meta file in a pseudo-language or xml (like propel) that
> will then be used by a program to generate objects as well as create the
> database structure?
>
> - or -
>
> 2) create a database structure, then run a program that will read the
> schema and create objects based off of what it found.

Normally 2, but 1 is okay as well if optional. Option 1 can specifially
come in handy for doing something like Rails' migration feature
(up-/downgrading from one schema version to another without having to
write the needed SQL by hand). PEAR's MDB2 has something like this in
place already.

CU
  Markus

[Markus Wolff-4]

Gavin Vess schrieb:
> Briefly, for the ZF approach to using modeling objects with data backed
> by a RDBMS, we'd like to:
> - avoid auto-generating any code or configuration files,
[...]
> - rely on database introspection,

By avoiding auto-generation, I assume that you would not like to have
new code generated at runtime, but having a commandline tool to generate
code after applying changes to the database schema would be okay?

Otherwise, if no code is generated at all, and the implementation relies
on database introspection, that would basically mean looking up table
information on-the-fly at runtime... uuh that'd be kinda slow :-)

> If the ZF supports an interpretation of ActiveRecord, yet allows some
> flexibility with DAO-like capabilities, then a ZF developer could design
> by creating an object domain and a supporting schema, relying on
> ActiveRecord-style tables/classes when there is naturally a close
> correspondence between tables and desired classes, but fall back to one
> of the other ZF mechanisms for working with other situations, all within
> the same application.  Hint: Debate, suggestions, feedback, opinions,
> and more are most welcome.

I like that approach, especially that you're not forced to use the
ActiveRecord objects in every situation - sometimes it just makes sense
to write a more complex query by hand, or, alternatively, let the
ActiveRecord object generate a query for you and then iterate through
the raw resultset.

As a matter of fact I have done most of my Database work with PEAR's
DB_DataObject over the past few years and I am now trying to move to a
PHP5-only solution. Propel, although very appealing at first glance,
seems way too complicated to me - it is relatively difficult to install,
it relies on a custom database abstraction layer nobody else seems to
use, and there's just too big an API to remember while coding.

I have also taken a look at Rails and its ActiveRecord implementation.
Nice. Very nice, especially the zero configuration approach, dynamic
properties for related tables and the migration features. Unfortunately,
wrong language :-D

Thus, I have started to write my own implementation, the first few lines
of code can be seen here:
https://ssl.limbourg.com/svn/activerecord/trunk/

(see test directory and look at unit tests for examples)

It's not a lot yet, but in the end I would like to see a combination of
the best features of DB_DataObject and Rails' ActiveRecord. The main
difference to Rails' solution is that, like DBDO, I like to define all
table fields as class properties, so I can have code-completion in Zend
Studio. Using overloading, this also helps in distinguishing table
fields from dynamic properties, which will later, like in Rails, be used
to dynamically link in related tables on-the-fly.

The class currently only supports read operations, and you need to write
the table classes which extend this base class yourself (yet). But if
anyone has the time to take a look at what's already there I'd sure like
to hear what you think about it (and, who knows, if enough people like
it and it fits into the ZF philosophy, I'd have no problem with
continuing development therein).

CU
  Markus

[Markus Wolff-4]

Am Saturday 27 May 2006 19:54 schrieb Markus Wolff:
> Thus, I have started to write my own implementation, the first few
> lines of code can be seen here:
> https://ssl.limbourg.com/svn/activerecord/trunk/
[...]
> The class currently only supports read operations, [...]

As I have just begun to implement write support (INSERT/UPDATE queries),
I am also interested in hearing opinions about generating new IDs for
records.

Most database abstraction layers solve this by always using sequences
instead of auto-increment columns, thus having to emulate sequences for
database systems that don't support them natively. This is usually done
by creating extra tables (in place of sequences) with just one
auto-increment column, performing an INSERT statement on them, fetching
the inserted ID and returning it, leading to quite a bit of overhead,
especially when considering bulk insert operations.

What I have always liked about DB_DataObject is that it detects the
database driver and then decides, depending on the underlying DBMS,
whether to use sequences or auto-increments. This way, you never have
to fall back on cheesy sequence emulations, which can also cause
acceptance problems (my former boss didn't want me using DataObject
with Microsoft SQL Server back then until I introduced a patch that
made MSSQLs auto-increment fields work with it because of the table
clutter it generated).

A special case are DBMS like PostgreSQL, that support sequences, but
also support an auto-increment-alike datatype (SERIAL), that you can
use in a way similar to those in MySQL, SQLite or MSSQL. The question
here is: What should one use if the ActiveRecord implementation
autodetects the PostgreSQL driver and must decide whether or not to use
sequences?

So, what would be the ZF's take on this subject? And most importantly,
what do the users think about this?

CU
 Markus

[Sergej-2]

Sequences are good. Expecialy when you need to know ID before you insert

--
http://www.mif.vu.lt/~sean3322/other/signature

[Markus Wolff-4]

Am Tuesday 30 May 2006 19:14 schrieb Sergej Andrejev:
> Sequences are good. Expecialy when you need to know ID before you
> insert

Hi Sergej,

thanks for your input. Generally, for the case you're describing, I'd
agree.

But where in an ActiveRecord implementation would I need to know the ID
before the insert? The question is not: Could I ever possibly need
this? The question is: Will I need it within the constraints of an
ActiveRecord implementation?

Also, this doesn't cover the issues raised with DBMS that do not support
sequences natively - should we bother with emulating sequences? Do we
really need them, do we really need to know the ID before inserting
anything (again, just within ActiveRecords...)? Is it worth the table
clutter this will generate? Not to mention possible interoperability
problems in cases where PHP just provides a Web frontend for working
with data that is also accessed by another application that does not
use PHP and doesn't know about the sequence emulation?

CU
 Markus

[Sergej-2]

Hi Marcus
> Also, this doesn't cover the issues raised with DBMS that do not
> support sequences natively - should we bother with emulating sequences?
How about changing framework requirements to use DBMS with sequences.
Anyway, we are not writing a new Database Abstraction which should be
portable with all databases.


> Do we really need them, do we really need to know the ID before
> inserting anything (again, just within ActiveRecords...)? Is it worth
> the table clutter this will generate?
But even with active records I thing there are cases where you need to
know some kind of ID (no necessarily before you insert). Example:
Creating some sort of document (not in DB) which is associated with
specific record. Perhaps that's not the best example, but I hope you got
the point. So Active record should use some sort of sequences.


> Not to mention possible interoperability problems in cases where PHP
> just provides a Web frontend for working with data that is also
> accessed by another application that does not use PHP and doesn't know
> about the sequence emulation?
> Markus
Yes, than is quite possible. Creating object with ID generated by DBMS
and then updating some fields using that ID would be safer. In any case
in both cases we are using equal number of SQL queries


P.S. I don't have much of experience in developing a framework. I am
just trying to express my opinion as a user


--
http://www.mif.vu.lt/~sean3322/other/signature

[Markus Wolff-4]

Sergej Andrejev schrieb:
> Hi Marcus
>
>> Also, this doesn't cover the issues raised with DBMS that do not
>> support sequences natively - should we bother with emulating sequences?
>
> How about changing framework requirements to use DBMS with sequences.
> Anyway, we are not writing a new Database Abstraction which should be
> portable with all databases.

Sure, but that would effectively rule out MySQL, Microsoft SQL Server,
Microsoft Access, SQLite ... and probably more that I don't know of.
That's not really a list of databases with low market share. In fact,
I'd think that would prevent the ZF from being used in about 80% of all
websites out there.

>> Do we really need them, do we really need to know the ID before
>> inserting anything (again, just within ActiveRecords...)? Is it worth
>> the table clutter this will generate?
>
> But even with active records I thing there are cases where you need to
> know some kind of ID (no necessarily before you insert). Example:
> Creating some sort of document (not in DB) which is associated with
> specific record. Perhaps that's not the best example, but I hope you got
> the point. So Active record should use some sort of sequences.

Sorry, I didn't get what you mean here. Sure, we need IDs. Doesn't
matter much if they come from a sequence or an auto-increment field. The
example I don't get... I'd have to create one ActiveRecord for the
document, save it, get the ID (off the AR object, doesn't matter how it
aquired it) and use that ID with another AR object to make the
association... don't see what the way the ActiveRecords retrieve the ID
internally would have to do with anything.

>> Not to mention possible interoperability problems in cases where PHP
>> just provides a Web frontend for working with data that is also
>> accessed by another application that does not use PHP and doesn't know
>> about the sequence emulation?
>> Markus
>
> Yes, than is quite possible. Creating object with ID generated by DBMS
> and then updating some fields using that ID would be safer. In any case
> in both cases we are using equal number of SQL queries

Not quite. Not if the DBMS doesn't know about sequences. To be clear, I
have nothing against sequences in particular. I am just very sceptical
of the usefulness of sequence emulation for databases that don't support
them. That's what I'm pondering - does it make any sense to do that kind
of thing in an ActiveRecord implementation? I think it doesn't - but I'm
open to being proven wrong (wouldn't be the first time :-D).

> P.S. I don't have much of experience in developing a framework. I am
> just trying to express my opinion as a user

And your opinion is very welcome :-)

CU
  Markus

[Sergej-2]

Hi Marcus
> Also, this doesn't cover the issues raised with DBMS that do not
> support sequences natively - should we bother with emulating sequences?
How about changing framework requirements to use DBMS with sequences.
Anyway, we are not writing a new Database Abstraction which should be
portable with all databases.

> Do we really need them, do we really need to know the ID before
> inserting anything (again, just within ActiveRecords...)? Is it worth
> the table clutter this will generate?
But even with active records I thing there are cases where you need to
know some kind of ID (no necessarily before you insert). Example:
Creating some sort of document (not in DB) which is associated with
specific record. Perhaps that's not the best example, but I hope you got
the point. So Active record should use some sort of sequences.

> Not to mention possible interoperability problems in cases where PHP
> just provides a Web frontend for working with data that is also
> accessed by another application that does not use PHP and doesn't know
> about the sequence emulation?
> Markus
Yes, than is quite possible. Creating object with ID generated by DBMS
and then updating some fields using that ID would be safer. In any case
in both cases we are using equal number of SQL queries


P.S. I don't have much of experience in developing a framework. I am
just trying to express my opinion as a user
>


--
http://www.mif.vu.lt/~sean3322/other/signature

[Andi Gutmans]

At 10:54 AM 5/27/2006, Markus Wolff wrote:

>Gavin Vess schrieb:
>>Briefly, for the ZF approach to using modeling objects with data
>>backed by a RDBMS, we'd like to:
>>- avoid auto-generating any code or configuration files,
>[...]
>>- rely on database introspection,
>
>By avoiding auto-generation, I assume that you would not like to
>have new code generated at runtime, but having a commandline tool to
>generate code after applying changes to the database schema would be okay?

Nope, we'd actually prefer to save the step of having to require the
user to run a code generation tool.


>Otherwise, if no code is generated at all, and the implementation
>relies on database introspection, that would basically mean looking
>up table information on-the-fly at runtime... uuh that'd be kinda slow :-)

Zend_Cache solves that.

Andi

[Markus Wolff-4]

Andi Gutmans schrieb:

>> By avoiding auto-generation, I assume that you would not like to have
>> new code generated at runtime, but having a commandline tool to
>> generate code after applying changes to the database schema would be
>> okay?
>
> Nope, we'd actually prefer to save the step of having to require the
> user to run a code generation tool.
>
>> Otherwise, if no code is generated at all, and the implementation
>> relies on database introspection, that would basically mean looking up
>> table information on-the-fly at runtime... uuh that'd be kinda slow :-)
>
> Zend_Cache solves that.

Mh, okay, thanks for clearing that up. In this case, I'll be patiently
waiting to see what you guys will come up with and continue developing
my own thingy in the meantime :-)

CU
  Markus