#1 2016-06-29 01:25:07

oraclei
Member
Registered: 2016-06-06
Posts: 18

Many duplicate code, how to optimize?

I have 8 TSQLRecord class, I want  use TSQLModel.Create once with just one RESTRestRootName, but I am a newbie on mORMot, I found the VirtualTableExternalRegisterAll can not specify external table name, so I have to use VirtualTableExternalMap, this lead to duplicate code, and I don't know how to specify REST URI root name  in  TSQLModel.Create([....], REST_URI_ROOT_NAME) just only once, please help me,thanks a lot.

unit uORM;

interface

uses
    Classes, SynCommons, mORMot,SynLog, mORMotSQLite3, mORMotHttpClient,mORMotDB, mORMotHttpServer, SynDB,
    SynDBODBC;

type
    TRestServers = array of TSQLRestServer;

    TOrganization  = class(TSQLRecord)
    private
        fName:String;
        fType:String;
        fParent: TOrganization;
        fParentIds:String;
        fWeight:Integer;
        fEnabled:Boolean;
        fMemo:String;
    published
        property name: string read fName write fName;
        property typ: string read fType  write fType;
        property Parent: TOrganization read fParent write fParent;
        property parentIds: string read fParentIds write fParentIds;
        property weight: integer read fWeight write fWeight;
        property isEnabled: boolean read fEnabled write fEnabled;
        property memo: string read fMemo write fMemo;
    end;

    TNationality = class(TSQLRecord)
    private
        fName:String;
        fCode:String;
    published
        property name:String read fName write fName;
        property code:String read fCode write fCode;
    end;

    TEmployeePost = class(TSQLRecord)
    private
        fName:String;
    published
        property name:String read fName write fName;
    end;

    TEmployeeCategory = class(TSQLRecord)
    private
        fName:String;
        fFlag:Byte;
    published
        property name:String read fName write fName;
        property flag:Byte read fFlag write fFlag;
    end;

    TEmployee =  class(TSQLRecord)
    private
        fName:string;
        fCode:string;
        fOrganization:TOrganization;
        fIdCard:string;
        fGender:boolean;
        fBirthday:TDatetime;
        fAddress:String;
        fNationality:String;
        fMobilePhone:String;
        fGeneralPhone:String;
        fOfficePhone:String;
        fHomePhone:String;
        fEmail:String;
        fQQNumber:String;
        fCategory:TEmployeeCategory;
        fPost:TEmployeePost;
        fBankName:String;
        fBankAccount:String;
        fDeleted:Boolean;
        fMemo:String;
    published
        property name:string read fName write fName;
        property code:string read fCode write fCode;
        property organization:TOrganization read fOrganization write fOrganization;
        property idCard:string read fIdCard write fIdCard;
        property gender:boolean read fGender write fGender;
        property birthday:TDatetime read fBirthday write fBirthday;
        property address:String read fAddress write fAddress;
        property nationality:String read fNationality write fNationality;
        property mobilePhone:String read fMobilePhone write fMobilePhone;
        property generalPhone:String read fGeneralPhone write fGeneralPhone;
        property officePhone:String read fOfficePhone write fOfficePhone;
        property homePhone:String read fHomePhone write fHomePhone;
        property email:String read fEmail write fEmail;
        property qqNumber:String read fQQNumber write fQQNumber;
        property category:TEmployeeCategory read fCategory write fCategory;
        property post:TEmployeePost read fPost write fPost;
        property bankName:String read fBankName write fBankName;
        property bankAccount:String read fBankAccount write fBankAccount;
        property isDeleted:Boolean read fDeleted write fDeleted;
        property memo:String read fMemo write fMemo;
    end;

    TRoute = class(TSQLRecord)
    private
        fOperatorName: string;
        fOperatorDate: TDateTime;
        fTripNumber: string; //班次
        fLine: string; //旅游线路
        fDepartureDate: TDateTime; //发车日期、时间
        fGuide: string; //导游
        fNote: string; //备注
    published
        property operatorName: string read fOperatorName write fOperatorName;
        property operatorDate: TDateTime read fOperatorDate write fOperatorDate;
        property tripNumber: string read fTripNumber write fTripNumber;
        property line: string read fLine write fLine;
        property departureDate: TDateTime read fDepartureDate write fDepartureDate;
        property guide: string read fGuide write fGuide;
        property note: string read fNote write fNote;
    end;

    TTouristPriceType = (ptAdult, ptOldPeople, ptChildren);

    TTourist = class(TSQLRecord)
    private
        fOperatorName: string;
        fOperatorDate: TDateTime;
        fRoute: TRoute;
        fName: string; //游客姓名
        fIdCard: string; //游客身份证
        fMobilePhone: string; //游客手机
        fGeneralPhone: string; //游客常用电话
        fSeat: Integer; //座号
        fPrice: Integer; //价格
        fPriceType: TTouristPriceType; //票价类型,成人/老人/儿童
        fSalesman: string; //推销员
        fIsFromAdvertising: Boolean; //是否来自于广告
        fIsFromSales: Boolean;  //是否来自于营销
        fIsFromHotline: Boolean;  //是否来自于热线咨询
        fIsFromWeb: Boolean; //是否来自于互联网
        fIsFromMobileApp: Boolean;  //是否来自于手机App
        fIsFromWeiXin: Boolean;  //是否来自于微信
        fNote: string; //备注
    published
        property operatorName: string read fOperatorName write fOperatorName;
        property operatorDate: TDateTime read fOperatorDate write fOperatorDate;
        property route: TRoute read fRoute write fRoute;
        property name: string read fName write fName;
        property idCard: string read fIdCard write fIdCard;
        property mobilePhone: string read fMobilePhone write fMobilePhone;
        property generalPhone: string read fGeneralPhone write fGeneralPhone;
        property seat: Integer read fSeat write fSeat;
        property price: Integer read fPrice write fPrice;
        property priceType: TTouristPriceType read fPriceType write fPriceType;
        property salesman: string read fSalesman write fSalesman;
        property isFromAdvertising: Boolean read fIsFromAdvertising write fIsFromAdvertising;
        property isFromSales: Boolean read fIsFromSales write fIsFromSales;
        property isFromHotline: Boolean read fISFromHotline write fIsFromHotline;
        property isFromWeb: Boolean read fIsFromWeb write fIsFromWeb;
        property isFromMobileApp: Boolean read fIsFromMobileApp write fIsFromMobileApp;
        property isFromWeiXin: Boolean read fIsFromWeiXin write fIsFromWeiXin;
        property note: string read fNote write fNote;
    end;

    TSurvey = class(TSQLRecord)
    private
        fOperatorName: string;
        fOperatorDate: TDateTime;
        fRoute: TRoute; //调查的旅游线路
        fTourist: TTourist; //接受调查的相关旅客
        fSatisfaction: Integer; //满意度
        fOpinion: string; //旅客意见
        fTreatment: string; //处理办法
        fResults: string; //处理结果
        fNote: string; //备注
    published
        property operatorName: string read fOperatorName write fOperatorName;
        property operatorDate: TDateTime read fOperatorDate write fOperatorDate;
        property route: TRoute read fRoute write fRoute;
        property tourist: TTourist read fTourist write fTourist;
        property satisfaction: Integer read fSatisfaction write fSatisfaction;
        property opinion: string read fOpinion write fOpinion;
        property treatment: string read fTreatment write fTreatment;
        property results: string read fResults write fResults;
        property note: string read fNote write fNote;
    end;

const
    RESTRestRootName = 'Tour';         //Automobile Passenger Transportation Rest
    OrganizationServiceUri = 'organization';
    NationalityServiceUri = 'nationality';
    EmployeeCategoryServiceUri = 'employeeCategory';
    EmployeePostServiceUri = 'employeePost';
    EmployeeServiceUri = 'employee';
    RouteServiceUri = 'royte';
    TouristServiceUri = 'tourist';
    SurveyServiceUri = 'survey';


function CreateRestServers(Conn: TODBCConnectionProperties):TRestServers;

implementation

function CreateRestServers(Conn: TODBCConnectionProperties):TRestServers;
var
    organizationModel : TSQLModel;
    organizationRest : TSQLRestServerDB;

    nationalityModel : TSQLModel;
    nationalityRest : TSQLRestServerDB;

    empCategoryModel : TSQLModel;
    empCategoryRest : TSQLRestServerDB;

    empPostModel : TSQLModel;
    empPostRest : TSQLRestServerDB;

    employeeModel : TSQLModel;
    employeeRest : TSQLRestServerDB;

    routeModel : TSQLModel;
    routeRest : TSQLRestServerDB;

    touristModel : TSQLModel;
    touristRest : TSQLRestServerDB;

    surveyModel: TSQLModel;
    surveyRest: TSQLRestServerDB;
begin
    organizationModel := TSQLModel.Create([TOrganization], OrganizationServiceUri);
    VirtualTableExternalMap(organizationModel, TOrganization, Conn, 'sys_organization')
        .MapField('name','name')
        .MapField('typ','type')
        .MapField('parent', 'parent_id')
        .MapField('parentIds','parent_ids')
        .MapField('weight', 'weight')
        .MapField('isEnabled', 'enabled')
        .MapField('memo', 'memo');
    organizationRest := TSQLRestServerDB.Create(organizationModel, ':memory:', False); // authentication=false
    organizationRest.CreateMissingTables;

    nationalityModel := TSQLModel.Create([TNationality], NationalityServiceUri);
    VirtualTableExternalRegister(nationalityModel, TNationality, Conn, 'hr_nationality');
    nationalityRest := TSQLRestServerDB.Create(nationalityModel, ':memory:', False);
    nationalityRest.CreateMissingTables;

    empCategoryModel := TSQLModel.Create([TEmployeeCategory], EmployeeCategoryServiceUri);
    VirtualTableExternalMap(empCategoryModel, TEmployeeCategory, Conn, 'hr_category');
    empCategoryRest := TSQLRestServerDB.Create(empCategoryModel, ':memory:', False);
    empCategoryRest.CreateMissingTables;

    empPostModel := TSQLModel.Create([TEmployeePost], EmployeePostServiceUri);
    VirtualTableExternalMap(empPostModel, TEmployeePost, Conn, 'hr_post');
    empPostRest := TSQLRestServerDB.Create(empPostModel, ':memory:', False);
    empPostRest.CreateMissingTables;

    employeeModel := TSQLModel.Create([TEmployee], EmployeeServiceUri);
    VirtualTableExternalMap(employeeModel,TEmployee,Conn,'hr_employee')
        .MapField('name','name')
        .MapField('code','code')
        .MapField('organization','organization_id')
        .MapField('idcard', 'id_card')
        .MapField('gender', 'gender')
        .MapField('birthday', 'birthday')
        .MapField('address','address')
        .MapField('nationality','nationality')
        .MapField('mobilePhone','mobile_phone')
        .MapField('generalPhone','general_phone')
        .MapField('officePhone', 'office_phone')
        .MapField('homePhone', 'home_phone')
        .MapField('email','email')
        .MapField('qqNumber','qq_number')
        .MapField('category','category_id')
        .MapField('post','post_id')
        .MapField('bankName','bank_name')
        .MapField('bankAccount','bank_account')
        .MapField('isDeleted','deleted')
        .MapField('memo','memo');
    employeeRest := TSQLRestServerDB.Create(employeeModel, ':memory:', False);
    employeeRest.CreateMissingTables;

    routeModel := TSQLModel.Create([TRoute], RouteServiceUri);
    VirtualTableExternalMap(routeModel,TRoute,Conn,'tour_route')
        .MapField('operatorName', 'op_name')
        .MapField('operatorDate', 'op_date')
        .MapField('tripNumber', 'trip_number')
        .MapField('line', 'line')
        .MapField('departureDate', 'departure_date')
        .MapField('guide', 'guide')
        .MapField('note', 'note');
    routeRest := TSQLRestServerDB.Create(routeModel, ':memory:', False);
    routeRest.CreateMissingTables;

    touristModel := TSQLModel.Create([TTourist], TouristServiceUri);
    VirtualTableExternalMap(touristModel,TTourist,Conn,'tour_tourist')
        .MapField('operatorName', 'op_name')
        .MapField('operatorDate', 'op_date')
        .MapField('route', 'route_id')
        .MapField('name', 'name')
        .MapField('idcard', 'idcard')
        .MapField('mobilePhone', 'mobile_phone')
        .MapField('generalPhone', 'general_phone')
        .MapField('seat', 'seat')
        .MapField('price', 'price')
        .MapField('priceType', 'price_type')
        .MapField('salesman', 'salesman')
        .MapField('isFromAdvertising', 'is_from_advertising')
        .MapField('isFromSales', 'is_from_sales')
        .MapField('isFromHotline', 'is_from_hotline')
        .MapField('isFromWeb', 'is_from_web')
        .MapField('isFromMobileApp', 'is_from_mobile_app')
        .MapField('isFromWeiXin', 'is_from_weixin')
        .MapField('note', 'note');
    touristRest := TSQLRestServerDB.Create(touristModel, ':memory:', False);
    touristRest.CreateMissingTables;

    surveyModel := TSQLModel.Create([TSurvey], SurveyServiceUri);
    VirtualTableExternalMap(surveyModel,TSurvey,Conn,'tour_survey')
        .MapField('operatorName', 'op_name')
        .MapField('operatorDate', 'op_date')
        .MapField('route', 'route_id')
        .MapField('tourist', 'tourist_id')
        .MapField('satisfaction', 'satisfaction')
        .MapField('opinion', 'opinion')
        .MapField('treatment', 'treatment')
        .MapField('results', 'results')
        .MapField('note', 'note');
    surveyRest := TSQLRestServerDB.Create(surveyModel, ':memory:', False);
    surveyRest.CreateMissingTables;

    SetLength(result, 8);
    Result[0] := organizationRest;
    Result[1] := nationalityRest;
    Result[2] := empCategoryRest;
    Result[3] := empPostRest;
    Result[4] := employeeRest;
    Result[5] := routeRest;
    Result[6] := touristRest;
    Result[7] := surveyRest;
end;

end.
procedure TMainForm.actStartExecute(Sender: TObject);
var
    connectionString: String;
    portString: String;
    rests :TRestServers;
begin

        connectionString := Format('Driver=%s;Database=%s;Server=%s;Port=%d;UID=%s;Pwd=%s', [ODBCDriver,fDatabase,fServer,fPort,fUsername,fPassword]);

    fConn := TODBCConnectionProperties.Create('',connectionString, '', '');
    rests := CreateRestServers(fConn);
    fHttpServer := TSQLHttpServer.Create(fListenPort, rests, '+', useHttpApiRegisteringURI);
    fHttpServer.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries

end;

Offline

#2 2016-06-29 06:47:16

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Many duplicate code, how to optimize?

Two ideas:

1. You can let your TSQLRecord types inherit from some abstract parents, which may not be published in the model.
So you would inherit the properties.

2. It does not mean much to write e.g. MapField('name','name'), since both field names are identical...

Offline

#3 2016-06-29 12:48:44

oraclei
Member
Registered: 2016-06-06
Posts: 18

Re: Many duplicate code, how to optimize?

Is this pseudo code correct ?

model := TSQLModel.Create([TOrganization,TEmployee,TRoute...],RESTRestRootName );
VirtualTableExternalMap(model, TOrganization, Conn, 'sys_organization').MapField...;
VirtualTableExternalMap(model, TEmployee,Conn, 'hr_employee').MapField...;
VirtualTableExternalMap(model, TRoute,Conn, 'tour_route').MapField...;

restServer := TSQLRestServerDB.Create(model, ':memory:', False);
restServer.CreateMissingTables;
fHttpServer := TSQLHttpServer.Create(fListenPort, [restServer], '+', useHttpApiRegisteringURI);

so I can only use just one RESTRestRootName .

Last edited by oraclei (2016-06-29 12:50:29)

Offline

#4 2016-06-29 16:24:10

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Many duplicate code, how to optimize?

Yes, it should be correct, if I understand well what you expect.

Offline

Board footer

Powered by FluxBB