Database migration

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Database migration

Free Pascal - General mailing list
Hi all,

After spending the last 12 months working intensively on a PHP based project, I moved back to FPC once again for another project. One of the more interesting concepts working in PHP was migrations. These are simple scripts that define the columns in a database table including indexes and foreign key constraints. Next to that also seeding and factories are integrated in this design. A trivial example is shown below;
        Schema::create('flights', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('airline');
            $table->timestamps();
        });
I already did some tests in pascal and it seems quite doable to create a system like this. But before I proceed I would like to check here if something like this already exists and if other developers are interested in this. If so I would publish the sources on github.

Rgds, Darius

_______________________________________________
fpc-pascal maillist  -  [hidden email]
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Database migration

Free Pascal - General mailing list
> But before I proceed I would like to check here if something like this
already exists

None that I know of for native Pascal. As I no longer use ORM, Sqitch does
the job for me these days. Basically it's like a git for database schema
(surely you can do CRUD as well there, but it's mainly for schema). I have
an experience of building such a tool myself, not a good days. I'd rather
use what others have already made and proven to be working well.



--
Sent from: http://free-pascal-general.1045716.n5.nabble.com/
_______________________________________________
fpc-pascal maillist  -  [hidden email]
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Database migration

Jean SUZINEAU
In reply to this post by Free Pascal - General mailing list
Hello,
Le 19/06/2020 à 13:55, Darius Blaszyk via fpc-pascal a écrit :
Schema::create('flights', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('airline');
            $table->timestamps();
        });

I have something that looks a bit like this, a kind of ORM (sorry, part of my identifiers are in French, "champ" means "field")

... in the class declaration

  //champs persistants
  public
    nUser: Integer;
    nProject: Integer;
    Beginning: TDateTime; cBeginning: TChamp;
    End_     : TDateTime; cEnd      : TChamp;
    Description: String;

... in the implementation of the constructor :

     Champs.ChampDefinitions.NomTable:= 'Work';

     //champs persistants
     Integer_from_ ( nUser          , 'nUser'          );
     Integer_from_ ( nProject       , 'nProject'       );

     cBeginning:= DateTime_from_( Beginning      , 'Beginning'      );
     cBeginning.Definition.Format_DateTime:= 'yyyy/mm/dd" "hh:nn';

     cEnd:= DateTime_from_( End_           , 'End'            );
     cEnd.Definition.Format_DateTime:= 'yyyy/mm/dd" "hh:nn';

(this is extracted from class TblWork from https://github.com/jsuzineau/pascal_o_r_mapping/blob/master/jsWorks/Elements/Work/ublWork.pas )

This class is for a row of the table "Work", most of the time, the instance is initialized by reading a row of dataset, but I've started some code to read from some other source, like android database or JSON data.

I've made a source code generator which can query the structure of a database, and generate source codes from templates. Particularly it can create the skeleton of unit ublWork.pas which contains class TblWork

My last release of source code generator is at : https://github.com/jsuzineau/pascal_o_r_mapping/releases/tag/2019_03_30_Generateur_de_code

All the source is released under LGPL at https://github.com/jsuzineau/pascal_o_r_mapping/ , feel free to pick up code from it if you need.

--

Trader en karma

J'adore me regarder penser... ;-)

http://jean.suzineau.pagesperso-orange.fr/ : mes pages perso

http://www.mars42.com :  mes pages professionnelles, astronomie & informatique

_______________________________________________
fpc-pascal maillist  -  [hidden email]
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Database migration

Michael Van Canneyt


On Sun, 21 Jun 2020, Jean SUZINEAU wrote:

> Hello,
> Le 19/06/2020 à 13:55, Darius Blaszyk via fpc-pascal a écrit :
>       Schema::create('flights', function (Blueprint $table) {
>
>             $table->id();
>             $table->string('name');
>             $table->string('airline');
>             $table->timestamps();
>         });
>
> I have something that looks a bit like this, a kind of ORM (sorry, part of my identifiers are in French, "champ" means "field")
>
> ... in the class declaration
>
>   //champs persistants
>   public
>     nUser: Integer;
>     nProject: Integer;
>     Beginning: TDateTime; cBeginning: TChamp;
>     End_     : TDateTime; cEnd      : TChamp;
>     Description: String;
>
> ... in the implementation of the constructor :
>
>      Champs.ChampDefinitions.NomTable:= 'Work';
>
>      //champs persistants
>      Integer_from_ ( nUser          , 'nUser'          );
>      Integer_from_ ( nProject       , 'nProject'       );
>
>      cBeginning:= DateTime_from_( Beginning      , 'Beginning'      );
>      cBeginning.Definition.Format_DateTime:= 'yyyy/mm/dd" "hh:nn';
>
>      cEnd:= DateTime_from_( End_           , 'End'            );
>      cEnd.Definition.Format_DateTime:= 'yyyy/mm/dd" "hh:nn';
>
> (this is extracted from class TblWork from
> https://github.com/jsuzineau/pascal_o_r_mapping/blob/master/jsWorks/Elements/Work/ublWork.pas )
>
> This class is for a row of the table "Work", most of the time, the instance is initialized by reading a row of dataset, but I've
> started some code to read from some other source, like android database or JSON data.
>
> I've made a source code generator which can query the structure of a database, and generate source codes from templates.
> Particularly it can create the skeleton of unit ublWork.pas which contains class TblWork
FPC contains this as well since many years, you can drop the components on a form, but they
are exposed in the lazarus database desktop as well.

Michael.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Database migration

Jean SUZINEAU
Le 21/06/2020 à 00:30, Michael Van Canneyt a écrit :
> FPC contains this as well since many years, you can drop the
> components on a form, but they
> are exposed in the lazarus database desktop as well.

Very interesting, I didn't know this existed. I will have look at it.

In fact I developed my code around 2004 for Delphi 7 and migrated it to
fpc/lazarus around 2013 to be able to run it on linux server.

Unfortunately my code is progressively abandoned now.

--
Trader en karma

J'adore me regarder penser... ;-)

http://jean.suzineau.pagesperso-orange.fr/ : mes pages perso

http://www.mars42.com :  mes pages professionnelles, astronomie & informatique

_______________________________________________
fpc-pascal maillist  -  [hidden email]
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Database migration

Michael Van Canneyt


On Sun, 21 Jun 2020, Jean SUZINEAU wrote:

> Le 21/06/2020 à 00:30, Michael Van Canneyt a écrit :
>> FPC contains this as well since many years, you can drop the
>> components on a form, but they
>> are exposed in the lazarus database desktop as well.
>
> Very interesting, I didn't know this existed. I will have look at it.
>
> In fact I developed my code around 2004 for Delphi 7 and migrated it to
> fpc/lazarus around 2013 to be able to run it on linux server.
>
> Unfortunately my code is progressively abandoned now.
I use the functionality in the database desktop actively.
The generated code even runs in Delphi.

If you look at it and find ways to improve it, I'm all ears :-)

Michael.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Database migration

Jean SUZINEAU
Le 21/06/2020 à 09:55, Michael Van Canneyt a écrit :
I use the functionality in the database desktop actively.
The generated code even runs in Delphi.

If you look at it and find ways to improve it, I'm all ears :-)

I got a quick look at it (Lazarus 2.0.6, FPC 3.0.4).
I saw there is a distinct generator for each case:

fpcsrc/3.0.4/packages/fcl-db/src/codegen/fpcgdbcoll.pp
fpcsrc/3.0.4/packages/fcl-db/src/codegen/fpcgcreatedbf.pp
fpcsrc/3.0.4/packages/fcl-db/src/codegen/fpcgtiopf.pp

In my case, there is a single generator which is  based on StringReplace with key/values stored in a StringList.
My first versions back to 2004 where coded as a plug-in for ModelMaker (UML modeling tool) .
A few years later, I transformed it in a StarUML plug-in, and finally as a standalone freepascal program.

At the beginning the composition of lists of fields or lists of tables was hard coded for example to make a list of labels for each field of table on a form's dfm:
( https://github.com/jsuzineau/pascal_o_r_mapping/blob/TjsDataContexte/pascal_o_r_mapping/03_Data/Automatic/Code_Generation/JoinPoint/02_Pascal/ujpPascal_LabelsDFM.pas )

the key value in the template is :  Cle:= '    object lNomChamp: TLabel';
and a part of the generated value could be:

...

    object lNPROJECT: TLabel
      Caption = 'nProject'   
      Left = 16          
      Top = 26                         
      Height = 13                              
    end                                        
    object lBEGINNING: TLabel
      Caption = 'Beginning'   
      Left = 16          
      Top = 48                         
      Height = 13                              
    end                                        
    object lEND: TLabel
      Caption = 'End'   
      Left = 16          
      Top = 70                         
      Height = 13                              
    end                                        
...

In the case of the dfm, I cannot use a comment for the key value so I put a Tlabel named lNomChamp in the template dfm, and add a '    object lNomChamp: TLabel' at the end of the generated value to avoid breaking the dfm.

I ended up with a huge number of different kinds of hard coded lists for each situation in Pascal , C# and PHP.

A few years ago , while testing Google Angular 4, to avoid to have to modify the generator for each new kind of list,  I added the possibility to define a list with 4 files:

listname.01_key.* for definition of the key for list listname

listname.02_begin.* for definition of the header of  list listname

listname.03_element.* for definition of the body of each element of  list listname

listname.04_separateur.* for definition of the separator between each element of  list listname

listname.05_end.* for definition of the footer of  list listname

For example in Angular:

file Angular_TypeScript_RouterLinks.01_key.html contains "<!--Angular_TypeScript_RouterLinks-->"

file Angular_TypeScript_RouterLinks.02_begin.html is empty

file Angular_TypeScript_RouterLinks.03_element.html contains "  <a routerLink="/Classe.Nom_de_la_classes" routerLinkActive="active">Classe.Nom_de_la_classes</a>"

file Angular_TypeScript_RouterLinks.04_separateur.html contains a newline

file Angular_TypeScript_RouterLinks.05_end.html is empty.

(you can find these files at https://github.com/jsuzineau/pascal_o_r_mapping/tree/TjsDataContexte/jsWorks/Generateur_de_code/01_Listes/Tables )

Theses files are used in unit https://github.com/jsuzineau/pascal_o_r_mapping/blob/TjsDataContexte/pascal_o_r_mapping/03_Data/Automatic/Code_Generation/JoinPoint/ujpFile.pas .

I used the term "JoinPoint" with Aspect Oriented Programming in mind, though it's not strictly aspect oriented programming.


_______________________________________________
fpc-pascal maillist  -  [hidden email]
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Database migration

Michael Van Canneyt


On Mon, 22 Jun 2020, Jean SUZINEAU wrote:

> Le 21/06/2020 à 09:55, Michael Van Canneyt a écrit :
>       I use the functionality in the database desktop actively.
>       The generated code even runs in Delphi.
>
>       If you look at it and find ways to improve it, I'm all ears :-)
>
> I got a quick look at it (Lazarus 2.0.6, FPC 3.0.4).
> I saw there is a distinct generator for each case:
>
> fpcsrc/3.0.4/packages/fcl-db/src/codegen/fpcgdbcoll.pp
> fpcsrc/3.0.4/packages/fcl-db/src/codegen/fpcgcreatedbf.pp
> fpcsrc/3.0.4/packages/fcl-db/src/codegen/fpcgtiopf.pp
>
> In my case, there is a single generator which is  based on StringReplace with key/values stored in a StringList.
I've seen other code generators that use a similar approach as yours: use templates.
I considered but discarded that approach early on because there is too much logic involved;
I would end up with too many templates.

Michael.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal