Forum > Databases

PostgreSQL: create database from template

(1/1)

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


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---Primary Error: CREATE DATABASE cannot run inside a transaction block)
and without:


--- Code: Text  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---Transaction not set
This works:


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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?

Чебурашка:

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


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---Primary Error: CREATE DATABASE cannot run inside a transaction block)
and without:


--- Code: Text  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---Transaction not set
This works:


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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?

--- End quote ---

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


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---TSub = class(TPGConnect) procedure RecallExecuteDirectPG() -> inherited ExecuteDirectPGend. 
Might work ?

But this is not direct TPGConnection...

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


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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; 
 :)

Чебурашка:

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

--- End quote ---

Good if you can subclass...


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

--- End quote ---

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:

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

--- End quote ---

Good if you can subclass...

--- End quote ---

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

Navigation

[0] Message Index