[Gambas-user] PostgreSQL, how to create a new database
Gianluigi
bagonergi at gmail.com
Mon Sep 2 18:31:48 CEST 2019
Hi guys,
I solved thanks to the suggestions of Berserker79 of the Italian forum [0].
I restored template1 thanks to this guide [1]
and I changed the connection string restoring the commented code.
$hConn.Close()
$hConn.Type = Lower(DBType)
$hConn.Host = DBHost
$hConn.Name = DBName
$hConn.Login = UserName
$hConn.Password = UserPassword
$hConn.Port = "5432"
and now all is OK.
Regards
Gianluigi
[0] https://www.gambas-it.org/smf/index.php?topic=7253.msg46682#msg46682
[1] https://wiki.postgresql.org/wiki/Adventures_in_PostgreSQL,_Episode_1
Il giorno lun 2 set 2019 alle ore 15:22 Gianluigi <bagonergi at gmail.com> ha
scritto:
> Hi everyone,
>
> I was creating a postgresql test database to verify the steps.
> Unfortunately I must have done something wrong because at the final
> verification I get these results, which means that, if I understand
> correctly, instead of having populated the new "rubinettofelice" database
> with tables, I directly populated the template1.
>
> This is how much is returned to me from the terminal:
>
> =======================================================================
> ~$ psql -U postgres
> Password for user postgres:
> psql (11.5 (Ubuntu 11.5-0ubuntu0.19.04.1))
> Type "help" for help.
>
> postgres=# \l
>
> Name | Owner | Encoding | Collate | Ctype |
> Access privileges
>
> -----------------+-----------+----------+-------------+-------------+-----------------------
> postgres | postgres | UTF8 | it_IT.UTF-8 | it_IT.UTF-8 |
> rubinettofelice | gianluigi | UTF8 | it_IT.UTF-8 | it_IT.UTF-8 |
> template0 | postgres | UTF8 | it_IT.UTF-8 | it_IT.UTF-8 |
> =c/postgres +
> | | | | |
> postgres=CTc/postgres
> template1 | postgres | UTF8 | it_IT.UTF-8 | it_IT.UTF-8 |
> =c/postgres +
> | | | | |
> postgres=CTc/postgres
> (4 rows)
>
> postgres=# \du
> List of roles
> Role name | Attributes |
> Member of
>
> -----------+------------------------------------------------------------+-----------
> gianluigi | Create role, Create DB |
> {}
> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
> {}
>
> postgres=# \c rubinettofelice
> You are now connected to database "rubinettofelice" as user "postgres".
> rubinettofelice=# \d
> Did not find any relations.
> <<========================================SIGH!
>
> rubinettofelice=# \dn
> List of schemas
> Name | Owner
> --------+----------
> public | postgres
> (1 row)
>
> rubinettofelice=# \c template1
> You are now connected to database "template1" as user "postgres".
> template1=# \d
> List of relations <<================
> ???
> Schema | Name | Type | Owner
> --------+-------------------+----------+-----------
> public | fipart | table | gianluigi
> public | tagent | table | gianluigi
> public | tagent_idagen_seq | sequence | gianluigi
> public | tfilms | table | gianluigi
> public | tfilms_idfilm_seq | sequence | gianluigi
> public | tst_fi | table | gianluigi
> public | tstars | table | gianluigi
> public | tstars_idstar_seq | sequence | gianluigi
> (8 rows)
> =======================================================================
>
> I created the database from the terminal with these steps.
>
> 1 - First I created the user:
>
> =======================================================================
> ~$ psql -U postgres -d template1
> Password for user postgres:
> psql sql (11.5 (Ubuntu 11.5-0ubuntu0.19.04.1))
> Type "help" for help.
>
> template1=# CREATE ROLE gianluigi WITH CREATEDB CREATEROLE LOGIN ENCRYPTED
> PASSWORD 'miapassword';
> CREATE ROLE
> =======================================================================
>
> 2 - Then I created the new "rubinettofelice" database:
>
> =======================================================================
> ~$ psql -U test -d template1
> Password for user test:
> psql sql (11.5 (Ubuntu 11.5-0ubuntu0.19.04.1))
> Type "help" for help.
>
> template1=> CREATE DATABASE rubinettofelice WITH OWNER=gianluigi TEMPLATE=
> template1 ENCODING='utf-8';
> CREATE DATABASE
> =======================================================================
>
> 3 - At this point he created a Gambas project with a module (MBase.module):
>
> =======================================================================
> ' Gambas module file
>
>
> Private $hConn As New Connection
>
> Public Sub OpenDB(DBType As String, DBHost As String, DBName As String,
> UserName As String, UserPassword As String)
>
> $hConn.Close()
> $hConn.Type = Lower(DBType)
> $hConn.Host = DBHost
> '$hConn.Name = ""
> $hConn.Login = UserName
> $hConn.Password = UserPassword
> '$hConn.Port = "5432"
>
> $hConn.Open()
>
> If $hConn.Databases.Exist(DBName) Then
> MakeTable()
> Else
> Error.Raise(("Database not found.\n\nAre you sure that the database
> exist?"))
> Endif
>
> $hConn.Close()
> Catch
> Message.Error(Error.Text & "\n\n" & Error.Where)
> FMain.Close()
>
> End
>
> Public Sub CloseDB()
>
> $hConn.Close()
>
> End
>
> Private Sub MakeTable()
>
> Dim hTable As Table
>
> $hConn.Begin
>
> If Not $hConn.Tables.Exist("tstars") Then
> hTable = $hConn.Tables.Add("tstars")
>
> hTable.Fields.Add("idstar", db.Serial)
> hTable.Fields.Add("stname", db.String, 40)
> hTable.Fields.Add("stsurn", db.String, 40)
> hTable.Fields.Add("stheig", db.Integer)
> hTable.Fields.Add("stweig", db.Integer)
> hTable.Fields.Add("stbirt", db.Date)
> hTable.Fields.Add("sttele", db.String, 13)
> hTable.Fields.Add("idagen", db.integer)
> hTable.Fields.Add("stimag", db.Blob)
> hTable.PrimaryKey = ["idstar"]
> hTable.Update
>
> Endif
>
> If Not $hConn.Tables.Exist("tagent") Then
> hTable = $hConn.Tables.Add("tagent")
>
> hTable.Fields.Add("idagen", db.Serial)
> hTable.Fields.Add("agname", db.String, 40)
> hTable.Fields.Add("agsurn", db.String, 40)
> hTable.Fields.Add("agaddr", db.string, 80)
> hTable.Fields.Add("agcity", db.string, 40)
> hTable.Fields.Add("agstat", db.string, 40)
> hTable.Fields.Add("agcoun", db.string, 40)
> hTable.Fields.Add("agpost", db.string, 10)
> hTable.Fields.Add("agtele", db.String, 13)
> hTable.Fields.Add("agmail", db.String, 30)
> hTable.Fields.Add("agwebs", db.String, 30)
> hTable.PrimaryKey = ["idagen"]
> hTable.Update
> Endif
>
> If Not $hConn.Tables.Exist("tfilms") Then
> hTable = $hConn.Tables.Add("tfilms")
>
> hTable.Fields.Add("idfilm", db.Serial)
> hTable.Fields.Add("finame", db.String, 100)
> hTable.Fields.Add("fiyear", db.Date)
> hTable.Fields.Add("fiprod", db.string, 100)
> hTable.Fields.Add("fiboxo", db.Float)
> hTable.PrimaryKey = ["idfilm"]
> hTable.Update
> Endif
>
> If Not $hConn.Tables.Exist("tst_fi") Then
> hTable = $hConn.Tables.Add("tst_fi")
>
> hTable.Fields.Add("idstar", db.Integer)
> hTable.Fields.Add("idfilm", db.Integer)
> hTable.Fields.Add("sfpart", db.String, 20, "ND")
> 'hTable.Indexes.Add("x_stfi", ["idstar", "idfilm"])
> hTable.Update
> Endif
>
> If Not $hConn.Tables.Exist("fipart") Then
> hTable = $hConn.Tables.Add("fipart")
>
> hTable.Fields.Add("paname", db.String, 10)
> hTable.Update
> Endif
>
> $hConn.Commit
> Catch
> $hConn.Rollback
> CloseDB()
> Debug Error.Where, Error.Text, Error.Code
>
> End
> =======================================================================
>
> 4 - In FMain I open a connection window that creates something similar and
> starts the module code:
>
> MBase.OpenDB("postgresql", "localhost", "rubinettofelice", "gianluigi",
> "miapassword")
>
> Can anyone help me figure out where I made the mistakes?
>
> Regards
> Gianluigi
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.gambas-basic.org/pipermail/user/attachments/20190902/5cf5318a/attachment-0001.html>
More information about the User
mailing list