mORMot Open Source

yogiyang

Member

Registered: 2010-08-18

Posts: 23

How would it be possible to manage such a structure of DB

I have a legacy app (that was developed by someone before I came to this particular company) that is originally written in VB6.

I want to rewrite it in Delphi using SQLite3 Framework.

But I am having a tough time trying to convert it. The software is very small but the data structure is giving me fits.

It is basically related to Catering industry.

This software will allow a user to create menus for a function order and then based on the menu it will print a list of all the raw material that will be required to cook food items as selected by user as Menu Items.

My question is as to how to convert this whole thing to classes in Delphi?

Is there something which will automatically generate all required classes from existing DB?

Here is the data structure.

-------------

CREATE TABLE [RawMaterialMaster] (
  [rm_id] integer NOT NULL PRIMARY KEY, 
  [rm_name] TEXT);

CREATE TABLE [OrderMaster] (
  [OM_id] INTEGER NOT NULL PRIMARY KEY, 
  [OM_order_date] DATE NOT NULL,
  [OM_party_name] TEXT NOT NULL, 
  [OM_party_address] TEXT NOT NULL, 
  [OM_party_contect_no] NUMBER, 
  [OM_hall_name] TEXT,
  [OM_hall_address] TEXT,  
  [OM_function_date] DATE, 
  [OM_members_morning] NUMBER, 
  [OM_members_afternoon] NUMBER, 
  [OM_members_evening] NUMBER, 
  [OM_price_m] MONEY, 
  [OM_price_a] MONEY, 
  [OM_price_e] MONEY, 
  [OM_notes] MONEY);

CREATE TABLE [RecipeMaster] (
  [Recipe_ID] INTEGER NOT NULL PRIMARY KEY, 
  [Recipe_name] TEXT);

CREATE TABLE [RecipeDetails] (
  [RecipeDetails_ID] INTEGER NOT NULL PRIMARY KEY, 
  [RecipeDetails_recepi_id] INTEGER,
  [RecipeDetails_item_id] INTEGER, 
  [RecipeDetails_wt_kg] INTEGER, 
  [RecipeDetails_wt_gram] INTEGER);

CREATE TABLE [OrderDetails] (
  [OD_id] INTEGER NOT NULL PRIMARY KEY, 
  [OD_om_id] INTEGER,  
  [OD_recipe_id] INTEGER,
  [OD_menu_for] INTEGER);

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: How would it be possible to manage such a structure of DB

Make a DUMP of your database into a SQL UTF-8 file. Just keep the INSERT statements, and make them SQLite3 ready.

Create the Delphi classes corresponding to every table.

Convert TEXT into RawUTF8, MONEY into Currency, DATE into TDateTime and the _id files into the TSQL.... types of your class.

Something like that (I've done some research/replacement do produce this):

  TSQLRawMaterialMaster = class(TSQLRecord)
  published
    rm_name: RawUTF8;
  end;

  TSQLOrderMaster = class(TSQLRecord)
  published
    OM_order_date: TDateTime;
    OM_party_name: RawUTF8; 
    OM_party_address: RawUTF8; 
    OM_party_contect_no: integer; 
    OM_hall_name: RawUTF8;
    OM_hall_address: RawUTF8;  
    OM_function_date: TDateTime; 
    OM_members_morning: integer; 
    OM_members_afternoon: integer; 
    OM_members_evening: integer; 
    OM_price_m: currency;
    OM_price_a: currency;
    OM_price_e: currency;
    OM_notes: currency;
  end;

  TSQLRecipeMaster = class(TSQLRecord)
  published
    Recipe_name: RawUTF8;
  end;

  TSQLRecipeDetails = class(TSQLRecord)
  published
    RecipeDetails_recepi_id: TSQLRecipeMaster ;
    RecipeDetails_item_id: ????;
    RecipeDetails_wt_kg: ????;
    RecipeDetails_wt_gram: ????;
  end;

  TSQLOrderDetails = class(TSQLRecord)
  published
    OD_om_id: ????;
    OD_recipe_id: ????;
    OD_menu_for: ????;
  end;

Add the private values, and the getters and setters for every published property of course.

Your database structure was not complete, but I'm sure you've got the point.

Then the framework will call the appropriate CREATE SQL statement for creating the tables.
Then import your SQL dump into SQLite3.

I've nothing automated for doing this.
Because it's a bit tricky: you'll have to adapt the conversion for every database engine used... SQL is not so standard, especially the field types in CREATE statements.

yogiyang

Member

Registered: 2010-08-18

Posts: 23

Re: How would it be possible to manage such a structure of DB

Thanks for your reply.

I will give it a try and if I do run into any problems I will surely contact you.

Regards,

Yogi Yang