Hello all
Hello all
What is the best solution to store and load enums value into database.
Till now, I've transtyped enum value to Integer but when adding another
or removing obsolete members, it can have some problem
Thanks
Andry
What is the best solution to store and load enums value into database.
Till now, I've transtyped enum value to Integer but when adding another
or removing obsolete members, it can have some problem
Thanks
Andry
You can use GetEnumName/GetEnumValue to store the enumeration value in plain text. Note that this will make problems when you change the names of some enumeration values in code.
ReplyDeleteThe best option you have is that of using two dictionaries: one for integer values and one for string descriptions.
ReplyDeleteIt is not too difficult and, if you want, I can find you an example.
Depends on what your definition of "best" is. Different solutions might be better in different settings.
ReplyDelete/sub
ReplyDeleteUsing a database metaphor, removal of an obsolete member translates to the removal of a primary key, which if there's other foreign keys still pointing to it will result in referential integrity violations or alternatively, if the "delete" is cascaded to maintain referential integrity, then this would imply that all records pointing to that foreign key would also be removed.
ReplyDeleteSince the latter is rarely desirable I would argue, and you likewise typically also do not want to just allow referential integrity violations in your database if you can help it, I'd therefore argue that the records representing your enum definition should be kept and instead marked via an additional column that indicates whether they have become obsolete and should not be used.
On the enum name/value question, I'd generally argue to store the ordinal ID values in a database rather than the string version of the enum. Otherwise you tie your code to some data in a database and hence run the risk of unpredictable breakage if someone unknowingly decides to refactor the enum name to something else. If the id's are stored this does not matter. And by the same token the name (description) stored in the database then doesn't have to match the name used in the code, unless you want it to.
I store the ordinal value. Easy and fast to make joins
ReplyDeleteSo suppose that actually I have
ReplyDeleteTPriorityType = (ptLow, ptMedium, ptHigh);
Then
Property Priority: TPriorityType read FPriority write FPriority;
To store the value into the database, I use
QryManager.ParamByName ( 'ISS_PRIORITY') AsiInteger: = TInteger (FPriority);
To load value from database :
FPriority: = TPriorityType (QryManager.FieldByName ( 'ISS_PRIORITY') AsiInteger);
The problem is that if ever I change TPriorityType to introduce a new member
TPriorityType = (ptNone, ptLow, ptMedium, ptHigh);
This will compromise the Priority values on my old data.
I know, I can also modify the schema of my database and include a Priority table and make a join but I have a lot of poroperties like this, so I need advice
Andry
You must keep "backwards compatibility" with old enum values. You must add always to the end.
ReplyDeleteOr your can just define "unused" values as fill and use them later
TPriorityType = (unused1, unused2, unused3, tpLow, unused4, tpMedium, ...)
Note you can explicitly define the ordinal values associated to an enum:
ReplyDeleteTPriorityType = (ptLow=1, ptMedium=2, ptHigh=3, ptNone=0);
Obviously the order of definition then doesn't match so you can write this if you prefer as:
TPriorityType = (ptNone=0, ptLow=1, ptMedium=2, ptHigh=3);
If you absolutely want to/must re-key the ordinal values (why??), then obviously you can theoretically do this if you execute the necessary updates on your database, everywhere the value occurs, or as required by your database implementation.
In theory, in an ideal world, if your database is set up with full referential integrity contstraints (foreign keys where relevant) and if these are set up to full cascade updates everywhere, then you should be able to do a simple update on only the key enum definition table to update (say) key value 1 to 2 (for example) and this change would then be cascaded everywhere else.
In general however (in my experience) DB's are usually not anywhere near this tidy and complete (often lacking foreign keys, or if they're present then often updates are not cascaded everywhere.)
It's therefore somewhat of a risky change to make if the key is used in very many other tables/places, and with dubious value as well since the id values don't actually carry any meaning anyway. (That is to say, what really do you gain by making such a change to your system?)
Consequently I would suggest you just use explicitly assigned ordinal values,and at most then order the definition entries in your enum code to suit your preferences but otherwise to not spend too much time on this and move on to more valuable work. My $0.02 anyway. :)
I suppose I should ask: Are you using the ordinal value of the enum for anything meaningful other than as an id value for the enum?
ReplyDeleteDavid Heffernan rightly pointed out that every use case may warrant a different solution, however, on balance, if you can, I'd just go with two generic dictionaries. Please find the example here: http://pastebin.com/vvn2EAjC .
ReplyDeleteNote that this link will expire in one month.
Okay,
ReplyDeleteAndrea Raimondi, I'll make a try to your code, thanks a lot.
Walter Prins, actually, there are no integrity check. Value are just stored into the field ISS_PRIORITY. Value is interpreted via code to the right enum value via a cast.
That code also allows for easy unit testing: if you add new values, it's easy to see if it breaks because they should not return "none".
ReplyDeleteDictionaries sound like a dreadful idea. Over engineered much.
ReplyDeleteI disagree: they provide a streamlined and consistent way to handle this sort of thing.
ReplyDeleteThe part that I think is most agreeable is that you can easily configure default values and eventually do other stuff
without having to fiddle too much. Plus, it allows, in my opinion, better encapsulation.
The main reason I am reluctant to use arrays is that every now and again you may need to have different
defaults for different contexts: in those cases, the code could become cumbersome without something like the
example I provided.
/sub
ReplyDeleteYou can use these techniques:
ReplyDeleteSystem.TypInfo.GetEnumName
function GetEnumName(TypeInfo: PTypeInfo; Value: Integer): string;
Returns the name of a Delphi enumerated type constant given its value.
Example:
var MyDataTypeName :string;
MyDataTypeName := GetEnumName(TypeInfo(TFieldType), Integer(prmField.DataType));
--------------------------------------------------------------------------------------------
System.TypInfo.GetEnumValue
function GetEnumValue(TypeInfo: PTypeInfo; const Name: string): Integer;
Returns the value of an enumerated type constant given its string representation.
Example:
case TFieldType(GetEnumValue(TypeInfo(TFieldType), FCurrentDataTypes[i])) of
ftFixedChar : ....
-------------------------------------------------------------------------------------
Now you have the manner of get the name of a member of an enumeration in string format and you can save it to DB as a varchar by example.
And, of course, you can take from DB a string and discover what is his correspondant member into the Enumeration.
You can know if the member recovered from DB was deleted recently from the enumeration:
"GetEnumValue returns the corresponding integer value, or -1 if Name does not match any constant in the supplied TypeInfo."
Good luck!!!
Continue to store it as ordinal/integer values. This is what DB likes, and is efficient.
ReplyDeleteThe idea is to NOT change the enumerate order, just add some new items, if necessary. If you need to change/merge enumerates, then it is up to your business logic to perform the modifications in the database, when upgrading the DB schema.
As for A. Bouchez suggestion, I too use ordinal/integer values. From a DB standpoint, managing integer is far more efficient than strings. Integers comparisons are faster than varchar comparisons, for the simple fact that ints take up much less space than varchars.
ReplyDeleteThis holds true both for unindexed and indexed access. The fastest way to go is an indexed int column.