REST easy with kbmMW #6 – Database 2

Now our fine REST server has been running for some time, and we start to understand we need to expand it with some more data.

Adding an additional table for new info is easy, as its done the same way as shown in  REST easy with kbmMW #2 – Database

However what if we need to add additional fields to the TContact class? What do we do with the data storage?

Until upcoming release of kbmMW, we would have had to make our own table update mechanism, which often is easy to do, as long as you add new fields, and those fields should not be part of primary keys and such.

But from next release of kbmMW, we also release a new beta feature in the kbmMW ORM. The ability to determine if the data storage is compatible with the class, and the ability to automatically update the data storage to match the new look of the class.

It sounds so deceptively simple to do so, but when we also want it to work across different databases, handling indexes and more, it suddenly starts to require quite detailed understanding of databases and their metadata.

So as a spin off of this new feature, kbmMW also comes with vastly improved database metadata handling, even better understanding of SQL query variants, more features in our in house SQL parser and much more.

Ok.. enough of the sales talk…. How do you do it then?

The original TContact class was defined like this:

unit Unit9;

interface

uses
 DB,
 System.Generics.Collections,
 kbmMWRTTI,
 kbmMWORM,
 kbmMWNullable;

type

 [kbmMW_Table('name:contact')]
 TContact = class
 private
  FID:kbmMWNullable<string>;
  FName:kbmMWNullable<string>;
  FAddress:kbmMWNullable<string>;
  FZipCode:kbmMWNullable<integer>;
  FCity:kbmMWNullable<string>;
  FComments:kbmMWNullable<string>;
 public
  [kbmMW_Field('primary:true, generator:shortGuid',ftString,40)]
  property ID:kbmMWNullable<string> read FID write FID;

  [kbmMW_Field('name:name',ftString,50)]
  property Name:kbmMWNullable<string> read FName write FName;

  [kbmMW_Field('name:address',ftString,80)]
  property Address:kbmMWNullable<string> read FAddress write FAddress;

  [kbmMW_Field('name:zipCode',ftInteger)]
  property ZipCode:kbmMWNullable<integer> read FZipCode write FZipCode;

  [kbmMW_Field('name:city',ftString,50)]
  property City:kbmMWNullable<string> read FCity write FCity;

  [kbmMW_Field('name:comments',ftMemo)]
  property Comments:kbmMWNullable<string> read FComments write FComments;
 end;

implementation

initialization
 kbmMWRegisterKnownClasses([TContact,TObjectList]);
end.

Lets add a Gender field, change the Name field to be unique (just for fun), and change the zip code field to be a string type matching the property type (previously we, perhaps incorrectly, defined it as an integer data storage field, where storing it as a string might have been better).

unit Unit9;

interface

uses
 DB,
 System.Generics.Collections,
 kbmMWRTTI,
 kbmMWORM,
 kbmMWNullable;

type

 [kbmMW_Table('name:contact')]
 TContact = class
 private
  FID:kbmMWNullable<string>;
  FName:kbmMWNullable<string>;
  FAddress:kbmMWNullable<string>;
  FZipCode:kbmMWNullable<string>;
  FCity:kbmMWNullable<string>;
  FGender:kbmMWNullable<string>;
  FComments:kbmMWNullable<string>;
 public
  [kbmMW_Field('primary:true, generator:shortGuid',ftString,40)]
  property ID:kbmMWNullable<string> read FID write FID;

  [kbmMW_Field('name:name, unique:true',ftString,50)]
  property Name:kbmMWNullable<string> read FName write FName;

  [kbmMW_Field('name:address',ftString,80)]
  property Address:kbmMWNullable<string> read FAddress write FAddress;

  [kbmMW_Field('name:zipCode',ftString,20)]
  property ZipCode:kbmMWNullable<string> read FZipCode write FZipCode;

  [kbmMW_Field('name:city',ftString,50)]
  property City:kbmMWNullable<string> read FCity write FCity;

  [kbmMW_Field('name:gender',ftString,1)]
  property Gender:kbmMWNullable<string> read FGender write FGender;

  [kbmMW_Field('name:comments',ftMemo)]
  property Comments:kbmMWNullable<string> read FComments write FComments;
 end;

implementation

initialization
 kbmMWRegisterKnownClasses([TContact,TObjectList]);
end.

Usually these changes in the class would render that class incompatible with the data storage. In fact we can now ask the ORM if the data storage is compatible with the class we have.

if not FORM.CompatibleTable(TContact) then
  raise Exception.Create('TContact is not compatible with the datastore');

When we call CompatibleTable this way, it compares using the strictest comparison method, which means that storage fields and index definitions must be not only compatible, but identical.

By adding an TkbmMWONObject instance to the call, we can be told what problems there are:

var
  issues:TkbmMWONObject;
begin
 issues:=TkbmMWONObject.Create;
 try
  if not FORM.CompatibleTable(TContact,issues) then
  begin
   // Decipher issues object.
   // There may be 3 properties in the object, named add, modify, delete
   // and each of those will be an array of objects with properties for name, unique, primary, required, size, precision and dataType.
  end;
 finally
   issues.Free;
 end;
end;

This way you get detailed information about the changes needed to make the data storage compatible with your class.

It is possible to tune exactly what to compare and how, and thus limit the strictness of the comparison mechanism. This is done by adding one additional argument to CompatibleTable, namely the ACompatibleFlags:TkbmMWORMCompatibleTableFlags.

TkbmMWORMCompatibleTableFlags is a set of flags including:

• mwoctfBasic – Basic strictness. Translates to mwfdctDataType, mwfdctPrecision, mwfdctSize
• mwoctfStrict – Strongest strictness. Translates to mwfdctPrimary, mwfdctUnique, mwfdctRequired, mwfdctDataType, mwfdctStrictPrecision, mwfdctStrictSize
• mwoctfConstraints – Include constaints validation. Translates to mwfdctPrimary, mwfdctUnique, mwfdctRequired
• mwoctfType – Include generic data type validation. Translates to mwfdctSize, mwfdctPrecision, mwfdctDataType.

The translated comparing flags cant be provided directly, but is used internally, and only shown for completeness.

• mwfdctPrimary – Primary key definition must match.
• mwfdctUnique – Unique field constraint must match.
• mwfdctRequired – Required field constraint (not null) must match.
• mwfdctDataType – Exact data type must match.
• mwfdctStrictPrecision – Field precision must match exactly. If not specified the data storage may have a larger field precision than required.
• mwfdctStrictSize – Field size must match exactly. If not specified the data storage may have a larger field size than required.

Walking thru the issues table can be fun, but even more fun would be not to have to do so.

var
  sl:TStringList;
begin
 sl:=FORM.GetUpgradeTableStatements(TTable2);
 try
  // It will make a strict comparison (arguments can be added to choose non strict comparison),
  // and generate a list of statements in generic kbmMemTable SQL format
  // that can be used to transform the data storage to be compatible with
  // the class.
  // It could be ALTER TABLE xxx DROP COLUMN yyy
 finally
  sl.Free;
 end;

Now the observant reader may say: “Thats all fine, but I for a fact know that SQLite do not support ALTER TABLE DROP COLUMN statements! So it wont work!”

You are right…. about the SQLite limitation. However remember that kbmMW will translate the statements into something acceptable by the target database type, so SQLite will in fact suddenly be able to have a column dropped from a table containing data. kbmMW will do its best to make it happen.

If you would like to see the rewritten SQL. In other words generic kbmMemTable SQL converted to specific target database syntax, then do like this:

var
 sl:TStringList;
begin
 sl:=FORM.GetUpgradeTableStatements(TTable2,false);
 try
  // Now the list of statements will have been converted to
  // the specific target database.
 finally
 sl.Free;
 end;

And after all this gibberish then how to make the data store compatible with the new class?

FORM.UpgradeTable(TTable2);

After running this, the table “contact” in the database will have been made compatible with the class, with all remaining data retained.

If you have huge tables with billions of rows, then it might be better to get inspiration from the output from GetUpgradeTableStatements, and apply the changes under human supervision.

Although kbmMW attempts to do things in a safe way, I also recommends backing up the data storage before attempting an automatic upgrade.

Currently kbmMW contains SQL rewriters that targets  SQLite, MySQL/MariaDB, PostgreSQL, MSSQL 2008/2012+, Oracle 9+,  Interbase/Firebird and generic SQL92 and SQL2003 compatible databases.

When the beta of this upgrade mechanism is released we urge people to test it upgrading capabilities carefully before deploying to production.

best regards

Kim/C4D