[Gambas-user] PostgreSQL, how to create a new database

[Gianluigi]

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/46d72bb9/attachment.html>