The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database portability

I would like to know if someone knows something about "SourcePro DB". It is a Database layer that it allow you to move to different DataBase (SQL Server, Oracle, ...) without code changes.

In my company we are thinking if it may be good for us.


Thanks.
Miguel Diez Send private email
Wednesday, April 20, 2005
 
 
You still have to make sure that your SQL follows the standards instead of using vendor-specific constructs.

Three years ago, I was on a project where the entire database layer worked beautifully... I beleive we based it on SQL 99 specs.  Anyway, we could *literally* point our system at an Oracle 8i backend or our mySQL backend without problem.

If that tool does translations between the constructs, it might be a whole other issue.
KC Send private email
Wednesday, April 20, 2005
 
 
==>You still have to make sure that your SQL follows the standards instead of using vendor-specific constructs.

ummm... No ya don't. Some of these tools *do*, in fact use vendor specific features/optimizations. You use the tool and its feature set, and it translates that into the generated code. I've seen tools that you don't even use SQL -- you use some hokey 4GL that then emits SQL targeted to your specific platform(s).


In reality, though, he's right. You get stuck with a "lowest common denominator" situation.
Sgt.Sausage
Wednesday, April 20, 2005
 
 
IMHO dtabase portability means "Runs poorly on one platform and runs hardly at all on others".
David Aldridge Send private email
Thursday, April 21, 2005
 
 
Couldn't agree more on the subject. Not understanding the features and differences between different Databases will result in poor performance, in most cases on at least one of the DB's and in some cases all.

We had a product that was written in Visual FoxPro, we converted to Ms SQL Server and then Oracle. The Oracle version ran like a dog, it was really really bad.

If you can get a copy of Tom Kytes Expert One-To-One the first chapters are a real eye opener on the subject.
gilf Send private email
Thursday, April 21, 2005
 
 
Yeap, the 'portability' is built into your own model of the data, instead of the SQL that does the hard work.

Look at http://www.joelonsoftware.com/articles/CityDeskEntityClasses.html
i like i
Thursday, April 21, 2005
 
 
Hi, thanks for your comments.

This product, apparently, has two parts: the "Data interface module" and the "Data Access Module". There is a only "data interaface module" and one "Data Access Model" for each database supported.

It is in C++. It seems well structured and I think its predecessor product name was called  "Tools++.h"


Thanks again for your help.
Miguel Diez Send private email
Thursday, April 21, 2005
 
 
At all the places I've been it's been a mess, we just tap into the raw ADO/System.Data in textbook manner and scramble when it comes time to refactor or maintain. Now I am wiser and I am using Provider Models(1) and custom entity classes/collections(2). These two ideas takes us all the way to poorman's DB portability.

(1) Provider Design Pattern

http://msdn.microsoft.com/asp.net/default.aspx?pull=/library/en-us/dnaspnet/html/asp02182004.asp

http://msdn.microsoft.com/asp.net/default.aspx?pull=/library/en-us/dnaspnet/html/asp04212004.asp

(2) Introducing Custom Entity Classes

http://msdn.microsoft.com/asp.net/default.aspx?pull=/library/en-us/dnaspp/html/CustEntCls.asp

Karl Seguin (author of (2)) recommends Martin Fowler's "Patterns of Enterprise Application Architecture" for the conceptual background.
Li-fan Chen Send private email
Monday, April 25, 2005
 
 
Sgt. Sausage, I noted:

"If that tool does translations between the constructs, it might be a whole other issue."

Yes, if you have a tool that does db-specific to db-specific tranforms, then it will help you.  But I've learned to never count on those working 100%.
KC Send private email
Wednesday, April 27, 2005
 
 
Realized the articles quoted are ASP.NET-oriented, my apologies.
Li-fan Chen Send private email
Wednesday, April 27, 2005
 
 
Couldn't agree more on the reality; couldn't agree less on the theory.

Writing directly to the vendor's particular implementation is always faster, just like compiled code is always faster than interpreted code.  What, you wanted portability *and* native execution speed?  Sorry, classic trade-off.

However, if you write *most* of the work in ANSI-SQL, you can re-code your hotspots in a vendor-specific manner, blah blah blah, and try to make it mostly-portable.

I think my biggest shock came not from DB2->Sybase, but from Sybase->Oracle.  I couldn't find ANYTHING in Oracle to save my life, and nothing seemed to work as expected.  It was easier for me to adopt, in 9i, the new SQL COALESCE/ CASE/ EXTRACT constructs than to ever figure out Oracle's data functions or decode.
Christopher Wanko Send private email
Friday, April 29, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz