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