flask_photo_scaling_app/examples/database

login to the psql command line

sudo -u postgres psql

format output

if you want to generate html output, the command is \pset format html
but you probably want the default format which is aligned

create database and role (psql command line)

CREATE DATABASE <application_database_name>;
CREATE ROLE <application_unix_user> WITH LOGIN;
\password <application_unix_user>
GRANT ALL PRIVILEGES ON DATABASE <application_database_name> TO <application_unix_user>;

allow app user in postgresql

add line above local all all peer

# /etc/postgresql/11/main/pg_hba.conf
local   <application_unix_user>       <application_unix_user>                 md5
local   all                           all                                     peer

and then restart postgresql systemctl restart postgresql

change database (psql command line)

\c <application_database_name>

create the database tables (bash command line)

sudo -u '<application_unix_user>' psql < create_database_tables.sql

sanity check

In order to be able to register, login to the psql command line and insert your email address into the email_white_list table.

verify the database table schemas against the models in models.py, and hopefully it matches what is below

describe database (psql command line)

<application_database_name>=# \d

List of relations

Schema	Name	Type	Owner
public	contributor	table	application_unix_user
public	contributor_id_seq	sequence	application_unix_user
public	email_white_list	table	application_unix_user
public	email_white_list_id_seq	sequence	application_unix_user
public	photo	table	application_unix_user
public	photo_id_seq	sequence	application_unix_user

(6 rows)

describe contributor table (psql command line)

<application_database_name>=# \d contributor

Table "public.contributor"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('contributor_id_seq'::regclass)
name	character varying(64)
email	character varying(120)
password_hash	character varying(128)
num_photos	integer
totp_key	character(16)
use_totp	boolean			false

Indexes:
    "contributor_pkey" PRIMARY KEY, btree (id)
    "contributor_email_key" UNIQUE CONSTRAINT, btree (email)
    "contributor_name_key" UNIQUE CONSTRAINT, btree (name)

describe photo table (psql command line)

<application_database_name>=# \d photo

Table "public.photo"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('photo_id_seq'::regclass)
photo_name	character varying(120)		not null
contributor_id	integer		not null
timestamp	timestamp without time zone
timestamp_int	bigint
photo_format	character(12)
photo_width	integer
photo_height	integer
photo_1280_width	integer
photo_1280_height	integer
photo_480_width	integer
photo_480_height	integer
Make	character varying
Model	character varying
Software	character varying
DateTime	timestamp without time zone
DateTimeOriginal	timestamp without time zone
DateTimeDigitized	timestamp without time zone
fnumber	numeric
DigitalZoomRatio	numeric
AspectRatio	numeric
photo_raw_size	bigint
photo_1280_size	bigint
photo_480_size	bigint
TimeZoneOffset	integer
GPSAltitude	numeric
GPSAboveSeaLevel	boolean
GPSLatitude	numeric
GPSLongitude	numeric

Indexes:
    "photo_pkey" PRIMARY KEY, btree (id)
    "photo_photo_name_key" UNIQUE CONSTRAINT, btree (photo_name)

describe email_white_list (psql command line)

<application_database_name>=# \d email_white_list

Table "public.email_white_list"

Column	Type	Collation	Nullable	Default
id	integer		not null	nextval('email_white_list_id_seq'::regclass)
email	character varying(120)

Indexes:
    "email_white_list_pkey" PRIMARY KEY, btree (id)
    "email_white_list_email_key" UNIQUE CONSTRAINT, btree (email)