(copied this post from General, looks like it's more appropriate here)

We would like one of our project to support various databases (MySql and Oracle to begin with) "out of the box".

Reading through the forums, it looks like this should be done by using one common DbGeneric, and multiple dedicated DbSpecifics. We have troubles generating that common DbGeneric.

By carefully setting up the databases (fields order, fields types...) we can generate almost identical DbGenerics. Only the FieldInfoProvider classes differ, more precisely the values of base.AddElementFieldInfo() maxLength, scale and precision parameters.

E.g. for an Int32 field we have - In MySql : int => 0, 0, 11 - In Oracle : NUMBER(9,0) => 22, 0, 9

For a boolean field we have - In MySql : tinyint(1) => 0, 0, 1 - In Oracle : NUMBER(1,0) => 22, 0, 1

For a "text" field we have - In MySql : text => length is 65535 - In Oracle : NCLOB => length is ... much bigger

Text fields are a good example because, no matter how much you tweak the database field definitions, it may very well be the case that database A and database B simply don't have matching max lengths.

So... we're a bit lost here. The only idea we have is to move bits of FieldInfoProvider back to DbSpecifics and somehow inject those bits back in DbGeneric... or to just plainly ignore the differences and pick one DbGeneric at random (no idea what would happen) or...

What do you think?

You can use Type Converters to allow you handle the differences between the databases.

Matt

MTrinder wrote:

You can use Type Converters to allow you handle the differences between the databases.

We do already use TypeConverters to map integers to booleans, or to enums. Using a TypeConverter, it makes no difference if the database field is a TINYINT(1) in MySql or a NUMBER(1) in Oracle, because the entity field will be a bool in both cases.

I.e., the FooEntity.cs files in both DbGeneric projects will be 100% identical.

The only file that has differences is FieldInfoProvider.cs because one will contain base.AddElementFieldInfo(..., 0, 0, 1); and the other will contain base.AddElementFieldInfo(..., 22, 0, 1);.

TypeConverters seem to come too late in the chain to change this, so we're left with our differences.

Picking one DbGeneric project at random seems to work, but we have no idea what the consequences may be.

?

sgay wrote:

(copied this post from General, looks like it's more appropriate here)

We would like one of our project to support various databases (MySql and Oracle to begin with) "out of the box".

Reading through the forums, it looks like this should be done by using one common DbGeneric, and multiple dedicated DbSpecifics. We have troubles generating that common DbGeneric.

By carefully setting up the databases (fields order, fields types...) we can generate almost identical DbGenerics. Only the FieldInfoProvider classes differ, more precisely the values of base.AddElementFieldInfo() maxLength, scale and precision parameters.

E.g. for an Int32 field we have - In MySql : int => 0, 0, 11 - In Oracle : NUMBER(9,0) => 22, 0, 9

Oracle is indeed a problem here, or more specifically, ODP.NET, which has a size for an integer which is 1 digit less than the rest.

For a boolean field we have - In MySql : tinyint(1) => 0, 0, 1 - In Oracle : NUMBER(1,0) => 22, 0, 1

For a "text" field we have - In MySql : text => length is 65535 - In Oracle : NCLOB => length is ... much bigger

That's because the fields aren't compatible. You should have used longtext in mysql as type, not text. MySql has 4 text types: tinytext, text, mediumtext and longtext. See: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

Text fields are a good example because, no matter how much you tweak the database field definitions, it may very well be the case that database A and database B simply don't have matching max lengths.

So... we're a bit lost here. The only idea we have is to move bits of FieldInfoProvider back to DbSpecifics and somehow inject those bits back in DbGeneric... or to just plainly ignore the differences and pick one DbGeneric at random (no idea what would happen) or... What do you think?

FieldInfo is used in-memory and only in-memory. It's not used for persistence at all, hence it's location in the dbgeneric project.

lengths for strings are used to verify whether a value fits in the db field. As you have a limited length on the mysql textfields, I'd opt for the mysql dbgeneric project. Lengths / precision aren't checked for integer typed fields, only for decimals

When a value is persisted, it goes this route: value -> typeconverter -> convertedvalue -> parameter, created from field's persistence info -> db

from typeconverter it's inside the dbspecific project area, and doesn't use field info.

So picking either one is fine, however as you have limited size on the mysql text fields, I'd go for that one as storing a string with a length of > 64KB will cause problems in the mysql db and when you pick the mysql project, this is caught in-memory by the validation logic built into the entity.

Otis wrote:

Oracle is indeed a problem here, or more specifically, ODP.NET, ...

There's a pattern here

Otis wrote:

FieldInfo is used in-memory and only in-memory. It's not used for persistence at all, hence it's location in the dbgeneric project.

lengths for strings are used to verify whether a value fits in the db field. As you have a limited length on the mysql textfields, I'd opt for the mysql dbgeneric project. Lengths / precision aren't checked for integer typed fields, only for decimals

When a value is persisted, it goes this route: value -> typeconverter -> convertedvalue -> parameter, created from field's persistence info -> db

from typeconverter it's inside the dbspecific project area, and doesn't use field info.

That's exactly what I was trying to figure out. So, we'll pick the most restrictive DbGeneric (or tweak one so it is the "least common DbGeneric"), and be safe.

Thanks!