Db access design - call for comments (& help)

[Marcin Kuszczak]

Julio César Carrascal Urquijo pisze:
 > Hello Aarti_pl,
 >
 >> ...and this high-level design is IMHO mistake. Especially mapping
 >> relations from db to objects.
 >>
 >> Well maybe someone will give me examples where domain objects are more
 >> useful than relations? From my observations presentation layer (GUI)
 >> is also relational, so I don't see a sense with making conversions:
 >> relation -> object -> relation.
 >>
 >> I am working on db access framework which makes use of relations
 >> rather than creating objects. And it makes it in typesafe way...
 >>
 >> BR
 >> Marcin Kuszczak
 >> (aarti_pl)
 >
 > Agree. I haven't been satisfied with any ORM I've tryied until now. The
 > best until now has been LINQ to SQL but Microsoft it's killing it.
 >
 > Can you provide us any details on your project? It sounds a lot like
 > .NET typed datasets.
 >
 > Thanks

Sure.

Basically my idea is to solve problems with using raw SQL in program,
but not to remove relational model from development.

I identified following problems with using raw string SQLs (probably not
all existing, but just these which I found very annoying in my
development; in no special order):

- writing SQL's as strings is very error prone; it's easy to put not
existing columns into it; it's easy to put wrong types when creating sql
queries; and you get information that something is wrong only on runtime

- information from SQL query can be very useful to implement cache'ing
of db information. But when sql is in form of string all information is
hidden in it

- there is no easy way to refactor program when Sql's are written as strings

- presentation layer should ask questions to database, as it knows best
what data it needs; but because of above mentioned problems there is
rather trend to put sql's into lower layers

- program has to identify specific pieces of data from database.
Normally, you have to define such identifiers twice: ones for database,
and the second time for usage in program - it seems to be wasteful

- there are differences between SQL dialects for different databases -
it is another problem with SQL strings

- there is often need for additional processing on data retrieved from
db. Partially it is possible to achieve it through stored procedures.
but sometimes it is also nice to make on client side with real 
programming language capabilities and access them like normal column.

My db access layer solves above problems.

----

How does it work?

1. First it is necessary to define database schema. For this purpose I 
use special classes derived from IColumn. In this case it would be 
IDbTypedColumn. When defining database schema I define types of columns, 
and also constraints for db column. In D it is possible to generate 
schema classes from schema definition file on compile time, and in fact 
it is already working in my project Doost (see: db package).

public DbTypedColumn<Integer> id = new DbTypedColumn<Integer>("id", 
visitcards, new Integer[0], new DbConstraints().primaryKey());

2. Column defined in db schema are used when defining queries to 
database. Because column definitions are typed, it is also possible to 
check types of arguments when defining queries.

Script script = Create(vcards_db.instance()).IfNotExists();
Query query = Select(visitcards).Where(Equals(visitcards.id, 5));

3. Then queries/scripts defined in such a way can be processed into 
string SQL form using generators, and they finally can be executed

SqliteGenerator generator = new SqliteGenerator();
String sql = generator.evaluate(script);
sql = generator.evaluate(query);

SqliteDbExecutor db = new SqliteDbExecutor(new ConnectionData());
DbMatrix matrix = db.execute(script);

4. Resulting matrix can be accessed with already defined db columns, but 
it is also possible to add other columns (also virtual columns) and 
remove them. Access to columns is typesafe:

Integer value = matrix.get(0, visitcards.id);

5. Virtual columns must adhere some specific interface, besides of that they are indistinguishable from other columns. 

----

As I noticed, it has indeed some similarities to NET solution. But my
solution should be simpler, but also powerful.

Current implementation is in Java, but I want to translate it to D in 
future when architecture will be finished. Currently it is much easier 
to make refactorings in Eclipse.

If you or someone else wanted to help in such a framework (I will use 
probably BSD license) I will put code somewhere, so it will be possible 
to work on it together. In such a case please drop me an email. First 
thing to do is to rethink architecture and found even better ways of 
doing things... :-)

-- 
Regards
Marcin Kuszczak (Aarti_pl)
-------------------------------------
Ask me why I believe in Jesus - http://www.zapytajmnie.com (en/pl)
Doost (port of few Boost libraries) - http://www.dsource.org/projects/doost/
-------------------------------------