###########
DataBases
###########

PostgreSQL
*************

Postgres Shell
============================
In Ubuntu use sudo -u
::

    sudo -u postgres psql
    # or \list will list all the databases, their owner and access privileges
    postgres=# \l

Create and Delete a PostgreSql DB
==================================

Use terminal to go to django directory where DB should be located. Create a user if there is not
one already created. Create DB.
::

    postgres=# CREATE USER <username>;
    postgres=# ALTER ROLE <user> WITH PASSWORD; <password> # change userpassword
    postgres=# CREATE DATABASE <dbname> OWNER <username>;
    # 'IF EXISTS' prevent an error from removing database
    postgres=# DROP DATABASE [IF EXiSTS] <dbname>;
    # If active connections
    postgres=# SELECT * FROM pg_stat_activity WHERE datname = '<dbname>';
    # returns pif
    postgres=# SELECT pg_terminate_backend (pid)
    postgres=# FROM pg_stat_activity
    postgres=# WHERE pg.stat_activity.datname = '<dbname>'
    postgres=# DROP DATABASE <dbname>;

`Drop postgresdb <https://www.postgresqltutorial.com/postgresql-drop-database>`_

Update Settings in Django
===========================

Find <mysite>/settings.py file. Go down to the DATABASES section and you should see the following.
::

    DATABASES = {
        'default': {
            'ENGINE" 'django.db.backends.sqlite3',
            'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
        }
    }

Replace with this-
::

    DATABASES = {
        'default': {
            'ENGINE" 'django.db.backends.postgresql',
            'NAME': '<db_name>',
            'USER': '<db_username>'
            'PASSWORD': '<db_password'
            'HOST': 'localhost',
            'PORT': ''
        }
    }

You will also need to install **psycopg2** into your virtual environment. This is needed for
django to access the postgreSQL database. You may run into an error -
'*./psycopg/psycopg.h:36:10: fatal error: libpq-fe.h: No such file or directory*'. To
fix this error you will need to install **ligpq-dev**. **libpq-dev** can't be installed with
pip because it is not part of PyPi. You will need to install it using
**sudo apt install libpq-dev**. After installing **libpq-dev** you should be able to do a
**pip install psycopg2**.

After replacing the DB settings you will also have to

::

    (venv) #:python manage.py makemigrations <project name>
    (venv) #:python manage.py migrate <project name>

ER Diagrams and Tools
**********************

My requirements:

* free
* reverse engineer from a database

What I found 8/21/20

#. `dbdiagram.io <https://dbdiagram.io/home>`_  web interface
#. `pgModeler <https://pgmodeler.io>`_ simple, not free
#. `DBeaver <https://dbeaver.io>`_ complex

Install DBeaver
=================

::

    $ sudo snap install dbeaver-ce

Installed DBeaver and created ER diagram. Diagram did not default to standard ER graphic notation.
Also would be nicer if I could tell 1-many and many-many relationships on Table Columns. Still
everything seems to work ok.