PostgreSQL: create database from template
lazarus / 16-Databases

----------------------------------------------------------------------
SymbolicFrank · 2023-05-23 12:36

If you want to duplicate a database, this is the way to do it:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
MyConnection: TPQConnection; // in the form procedure
TMainForm.StartButtonClick(Sender: TObject);begin 
MyConnection.HostName := ServerEdit.Text;  //MyConnection.Transaction
:= MyTransaction;  MyConnection.UserName := UserEdit.Text; 
MyConnection.Password := PassEdit.Text;  MyConnection.Connected :=
True;  MyConnection.ExecuteDirect('create database test_copy template
test_org;');end;
Unfortunately, this doesn't work. With the transaction you get:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Primary
Error: CREATE DATABASE cannot run inside a transaction block)
and without:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Transaction
not set
This works:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TMainForm.StartButtonClick(Sender: TObject);begin 
MyConnection.HostName := ServerEdit.Text;  MyConnection.Transaction :=
MyTransaction;  MyConnection.UserName := UserEdit.Text; 
MyConnection.Password := PassEdit.Text;  //MyConnection.Connected :=
True;  MyConnection.DatabaseName := 'test_copy'; 
MyConnection.CreateDB;end; 
But that doesn't duplicate the database, so it's no use.

So, is this possible with the TPQConnection at all, or do I have to do
that low-level?

----------------------------------------------------------------------
Чебурашка · 2023-05-23 12:57

Quote from: SymbolicFrank on May 23, 2023, 02:36:28 pmIf you want to
duplicate a database, this is the way to do it:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
MyConnection: TPQConnection; // in the form procedure
TMainForm.StartButtonClick(Sender: TObject);begin 
MyConnection.HostName := ServerEdit.Text;  //MyConnection.Transaction
:= MyTransaction;  MyConnection.UserName := UserEdit.Text; 
MyConnection.Password := PassEdit.Text;  MyConnection.Connected :=
True;  MyConnection.ExecuteDirect('create database test_copy template
test_org;');end;
Unfortunately, this doesn't work. With the transaction you get:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Primary
Error: CREATE DATABASE cannot run inside a transaction block)
and without:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Transaction
not set
This works:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TMainForm.StartButtonClick(Sender: TObject);begin 
MyConnection.HostName := ServerEdit.Text;  MyConnection.Transaction :=
MyTransaction;  MyConnection.UserName := UserEdit.Text; 
MyConnection.Password := PassEdit.Text;  //MyConnection.Connected :=
True;  MyConnection.DatabaseName := 'test_copy'; 
MyConnection.CreateDB;end; 
But that doesn't duplicate the database, so it's no use.

So, is this possible with the TPQConnection at all, or do I have to do
that low-level?

I see that createDB calls ExecuteDirectPG, but is protected. Maybe
descending class and recalling it?

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i TSub =
class(TPGConnect) procedure RecallExecuteDirectPG() -> inherited
ExecuteDirectPGend. 
Might work ?

But this is not direct TPGConnection...

----------------------------------------------------------------------
SymbolicFrank · 2023-05-23 12:58

@Чебурашка: yes, this works:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i type 
SpecialConnection = class(TPQConnection)  public    procedure
ExecuteDirectPG(const Query : String);  end; // I removed the
definition of the form implementation procedure
SpecialConnection.ExecuteDirectPG(const Query: String);begin 
inherited;end; begin  MyConnection.HostName := ServerEdit.Text; 
MyConnection.Transaction := MyTransaction;  MyConnection.UserName :=
UserEdit.Text;  MyConnection.Password := PassEdit.Text; 
MyConnection.ExecuteDirectPG('create database temp_copy template
temp_org;');  Msg('OK!');end;

----------------------------------------------------------------------
Чебурашка · 2023-05-23 13:00

Quote from: SymbolicFrank on May 23, 2023, 02:58:12 pm@Чебурашка: yes,
this works:


Good if you can subclass...

Quote from: SymbolicFrank on May 23, 2023, 02:58:12 pmEdit: I didn't
know you can inherit a method like that. Interesting.

In reality I was thinking to create a separate method that calls the
base method (this is a trick to republish a hidden method on a local
class, i.e. without having base class to publish it). In the way you
did it, I think you are hiding a base class method, and if you call
using the base class I think you do not call the overridden method,
but the base one.

----------------------------------------------------------------------
SymbolicFrank · 2023-05-23 13:03

Quote from: Чебурашка on May 23, 2023, 03:00:46 pmQuote from:
SymbolicFrank on May 23, 2023, 02:58:12 pm@Чебурашка: yes, this works:


Good if you can subclass...

Yes, I agree, many things in the database classes are private.