www.digitalmars.com         C & C++   DMDScript  

digitalmars.D - Db access design - call for comments (& help)

reply Marcin Kuszczak <aarti_please_no spam_interia.pl> writes:
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/
-------------------------------------
Nov 13 2008
next sibling parent reply Christopher Wright <dhasenan gmail.com> writes:
Marcin Kuszczak wrote:
...

 My db access layer solves above problems.


You have me drooling in anticipation.


 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());


 From this, it looks like this would be a good base for anyone wanting 
to build an ORM package -- it'd mainly leave translation between objects 
and columns.


 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));


Hm. Fluent interfaces are good. Domain-specific languages can be easier 
to work with, but they take a lot more effort to create and maintain.


 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... :-)


I'm quite interested in this. I might have time to help out, but I'm not 
going to make any offers I might not be able to fulfill.
Nov 13 2008
parent Aarti_pl <aarti interia.pl> writes:
Christopher Wright pisze:

 Marcin Kuszczak wrote:
 ...

 My db access layer solves above problems.


 
 You have me drooling in anticipation.


Well, this solution works and really solves above mentioned problems for 
me. I use it with real application and it works great.

But indeed it really might be too strong statement, at least at the 
current stage of development.



 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());


 
  From this, it looks like this would be a good base for anyone wanting 
 to build an ORM package -- it'd mainly leave translation between objects 
 and columns.
 

 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));


 
 Hm. Fluent interfaces are good. Domain-specific languages can be easier 
 to work with, but they take a lot more effort to create and maintain.
 

 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... :-)


 
 I'm quite interested in this. I might have time to help out, but I'm not 
 going to make any offers I might not be able to fulfill.


That's fair enough.

I will prepare sources and put them somewhere and then give announcement 
here. But it can take some time, as I am quite busy currently...

Best Regards
Marcin Kuszczak
Nov 14 2008
prev sibling parent reply Lars Ivar Igesund <larsivar igesund.net> writes:
Marcin Kuszczak wrote:.


 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... :-)


Since I am working on DDBI, I was curious as to whether you are competing, or
building something that would/could live on top of it :)

-- 
Lars Ivar Igesund
blog at http://larsivi.net
DSource, #d.tango & #D: larsivi
Dancing the Tango
Nov 14 2008
parent Marcin Kuszczak <aarti_please_no spam_interia.pl> writes:
Lars Ivar Igesund wrote:


 Marcin Kuszczak wrote:.
 

 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... :-)


 
 Since I am working on DDBI, I was curious as to whether you are competing,
 or building something that would/could live on top of it :)
 


I would say that to make full usage of my framework people should drop using
SQL's as strings. I am unconvinced that all people will do that, even if using
string SQLs is very error prone. So in this area it will be just a layer on top
of DDBI.
In this area DDBI could provide interface to send data directly to databases as
structs of data, by-stepping SQL parsing.  That would probably make using SQL
objects faster than normal queries. Although I don't know if such a possibility
is implemented in any database. :-]

The second area is returning data from queries. In this area it can be done
much more to allow seamless integration with higher level container used to
pass data inside application. I see here two possibilities:

1. DDBI will provide only very thin layer over database interface. Probably it
should be kind of callback for "fetchRow" from database. It shouldn't be
container, as when using my framework it would be necessary to process data and
put them into another specific container, so there would be overhead. I use
'variant' as internal representation for containers, and it would desirable to
get same 'variant' from DDBI. Optionally it should be just a simple D type.

2. The second option is that DDBI queries will return containers, which are
somehow compatible with higher level containers used in my framework, so there
will be no overhead when transforming one into the other. But I think that it
is rather inferior solution to the first one.

DDBI can also provide some interface to get mapping between D type and database
type (D type --> db type string). I need this information to generate SQL
string queries before sending them to database. As the other way (database type
to D type) must be done anyway in DDBI,  so I think that D type to database
type should be also in the same layer.

Also getting metadata for queries in DDBI should not be "included by default"
with getting normal data. In my framework I don't have to rely  on metadata
from database *at all* as all metadata are defined much earlier in program and
they are fully known during queries. (So, there must be a way to get data from
column by its number and not only by its name).

When above conditions are fulfilled, then my framework will be just a higher
layer in D database access. If some of above conditions are not met, there will
be additional unnecessary cost when using my framework and functionalities will
cross, so they will compete.

-- 
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/
-------------------------------------
Nov 15 2008