tiOPF Tip – Wiring Up A Simple Look-Up Table

I have a requirement for several simple database centric look-up tables in one of my projects.  They are no more complex than name-value pairs [EA-Each, CT-Carton, ... ] that get stored in the database and are fully editable by the users.  I needed to find a way around the tiOPF [the OPF of choice] default of using GUID OID values for any object you plan to persist because,  to my mind, there is something seriously wrong with storing “6FFC3175-37CD-4682-A599-EF6B86E1F397” over and over to represent “Each” where “EA” is more than up to performing the task.


Although the default of the tiOPF is to generate GUID OID values for objects, it is built to handle pretty much any OID value type you want to throw at it.  What I want/need to do is a bit of an edge case use where simply replacing the generated GUID OID string with one that is not only considerably shorter but makes infinitely more sense in context, will suffice quite nicely.  I had seen the process explained before but, of course, now that I want to use it, the information can’t be found.  That’s fine ... time to play.
The example I used in my test project was a Countries look-up table.  There are at least three standards used by [at least] as many organizations that provide a two, if not three character unique ID code for each country in the world.  For my purposes, any of them will do nicely.  I chose the ISO 3166-1 Country Codes which provides not only a two and a three character unique ID for each Country but a three digit numerical code as well.  For the example I’ll use the three digit character code.
The database table, appropriately named “Countries”, will have two fields “ISO” [3] and “Name” [36],  the object we define will have one new property/attribute “CountryName".
In a nutshell, all there is to the solution is;  you map the object OID to the ISO field and add a bit code such that whenever you create a new object you replace the automatically generated GUID OID with an appropriate three digit character code.  The GUID assigned to the OID property is simply a string, we plan on swapping it out with ISO which is also a string – size doesn’t get involved until we try and shove the data into the database fields we created to persist them.
So, here we have the simple TCountry object that we can use to create/store our country names and the unique key to each.  Notice that I’ve implemented an overloaded CreateNew method [constructor] from the parent object.  Notice as well, that I’m passing two parameters even though the object technically only indicates support for one – the other one is the OID which you usually don’t mess with.

TCountry = class(TtiObject)
  private
    FCountryName: string;
    procedure SetCountryName(const Value: string);
  public
    constructor CreateNew(const AISO, ACountryName: string); reintroduce; overload;
  published
    property CountryName: string read FCountryName write SetCountryName;
  end;
{ ... irrelevant code clipped ... }

The CreateNew constructor get’s coded out as follows;

constructor TCountry.CreateNew(const AISO, ACountryName: string);
begin
  inherited CreateNew;          {<< create a new default object        }
  OID.AsString := AISO;         {<< swapping out the GUID OID with AISO}
  FCountryName := ACountryName;
end;

I am using AutoMapping for these particular objects, which is perfect, as all we need do [in this demo] is load the whole table anyway.  Down in the initialization section of the unit, notice that we map the OID object attribute to the ISO database field.

initialization

  with GTIOPFManager.ClassDBMappingMgr do
    begin
      {               Object,    TableName,   AttrName,      ColName   PKInfo }
      RegisterMapping(TCountry, 'Countries', 'OID',         'ISO'  ,   [pktDB]);
      RegisterMapping(TCountry, 'Countries', 'CountryName', 'Name'            );
      { ... irrelevant code clipped ... } 
    end;

Now somewhere out in your project you create and persist a bunch of objects;

procedure AddABunchOfRecords;
var
  Country: TCountry;
  CountryList: TCountryList;

begin
  CountryList := TCountryList.Create;

  Country := TCountry.CreateNew('CAN', 'Canada');
  CountryList.Add(Country);

  Country := TCountry.CreateNew('GBR', 'United Kingdom');
  CountryList.Add(Country);

  Country := TCountry.CreateNew('USA', 'United States');
  CountryList.Add(Country);

  Country := TCountry.CreateNew('BRA', 'Brazil');
  CountryList.Add(Country);

  Country := TCountry.CreateNew('HND', 'Honduras');
  CountryList.Add(Country);

  Country := TCountry.CreateNew('IND', 'India');
  CountryList.Add(Country);

  GTIOPFManager.Save(CountryList);

end;

Although it has taken me a relatively long time to get there, it all seems pretty simple once you have seen it done.  I hope you find this helpful, if you see any errors or have any questions I’d appreciate it if you let me know.
Thanks for stopping by ...
Dave