ucampostgresvro 0.1.2

To connect with postgresql for the billing report

Script for VRA database for Costing

Developing

1. Install docker-compose.
2. Docker will run the postgres on port 5455 so, ensure the system has the port available

# run pytest testing 
./developer.sh pytest start

# run flake8 testing 
./developer.sh flake8 start 

# delete the testing environment
./developer.sh pytest stop

# delete the flake8 environment
./developer.sh flake8 stop

Package usage

• To setup database

from ucampostgresvro.utils import pre_setupconfig
from ucampostgresvro.DBA import DB
db_params = {
    "dbname": "vrapricing",
    "user": "postgres",
    "password": <1Password: vrapricingpostgres>,
    "host": "infra-db.srv.uis.cam.ac.uk", 
    "port": "5432",
    "sslmode": "require",
    "sslrootcert": "./ca.crt",  # path to your client certificate
}
db = DB(db_params)
pre_setupconfig(db_params)

• To perform CRUD operation

from ucampostgresvro.DBA import DB
db_params = {
    "dbname": "vrapricing",
    "user": "postgres",
    "password": <1Password: vrapricingpostgres>,
    "host": "infra-db.srv.uis.cam.ac.uk", 
    "port": "5432",
    "sslmode": "require",
    "sslrootcert": "./ca.crt",  # path to your client certificate
}
db = DB(db_params)

# CRUD on user DB./

# create user
db.insert_vrauser("ll220", "Ling-Yan Lau")

# read user
print(db.get_vrauser())

# update user
db.update_vrauser("ll220", "bda20", 'Ben Argyle')

# delete user
db.remove_vrauser('bda20')

# create vra deploymentid 
db.insert_deployment_id("1231ee112ad11212")

# read vra deployment id
print(db.get_deployment_id("1231a"))

# update vra deployment id
db.update_deployment_id("1231ee112ad1", "1231a")

# delete vra deployment id
db.remove_deployment_id('1231a')

# create project 
db.insert_project("0001",1,100.0)

# read project 
print(db.get_project())

# update project 
db.update_project("0001", "0002", 4, 200)

# delete project
# db.remove_project("0002")

# create grant 
db.insert_grant("0001",1,100.0)

# read grant
print(db.get_grant())

# update grant 
db.update_grant("0001", "0002", 4, 200)

# delete grant
db.remove_grant("0002")

# create costing 
db.insert_costing(2, "Initial Resource", project_id=4, grant_id=None)

# read costing 
print(db.get_costing())

# update costing 
db.update_costing(2, "Duration Expansion", old_grant_id=4, old_project_id=None, grant_id=4, project_id=None)

# delete costing 
db.remove_costing(2, "Duration Expansion", 4, None)

# to close db connection
db.closedb()

---

Design

- VRAUSER table

vrapricing=# \d vrauser;
                                    Table "public.vrauser"
 Column |          Type          | Collation | Nullable |               Default
--------+------------------------+-----------+----------+-------------------------------------
 id     | integer                |           | not null | nextval('vrauser_id_seq'::regclass)
 crsid  | character varying(255) |           |          |
 name   | character varying(255) |           |          |
Indexes:
    "vrauser_pkey" PRIMARY KEY, btree (id)
    "vrauser_crsid_key" UNIQUE CONSTRAINT, btree (crsid)
Referenced by:
    TABLE "purchaseorder" CONSTRAINT "purchaseorder_paid_by_fkey" FOREIGN KEY (paid_by) REFERENCES vrauser(id)
    TABLE "grant" CONSTRAINT "grant_paid_by_fkey" FOREIGN KEY (paid_by) REFERENCES vrauser(id)

- VRA Deployment ID tabel

vrapricing=# \d deploymentid;
                                      Table "public.deploymentid"
    Column    |         Type          | Collation | Nullable |                 Default
--------------+-----------------------+-----------+----------+------------------------------------------
 id           | integer               |           | not null | nextval('deploymentid_id_seq'::regclass)
 deploymentid | character varying(50) |           |          |
Indexes:
    "deploymentid_pkey" PRIMARY KEY, btree (id)
    "deploymentid_deploymentid_key" UNIQUE CONSTRAINT, btree (deploymentid)
Referenced by:
    TABLE "costing" CONSTRAINT "costing_deployment_id_fkey" FOREIGN KEY (deployment_id) REFERENCES deploymentid(id)

- project table

vrapricing=# \d projects;
                                        Table "public.projects"
     Column     |          Type          | Collation | Nullable |               Default
----------------+------------------------+-----------+----------+--------------------------------------
 id             | integer                |           | not null | nextval('projects_id_seq'::regclass)
 project_number | character varying(255) |           |          |
 paid_by        | integer                |           |          |
 amount         | double precision       |           | not null |
Indexes:
    "projects_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "projects_paid_by_fkey" FOREIGN KEY (paid_by) REFERENCES vrauser(id)
Referenced by:
    TABLE "costing" CONSTRAINT "costing_project_id_fkey" FOREIGN KEY (project_id) REFERENCES projects(id)

- grants table

vrapricing=# \d grant;
                                        Table "public.grant"
     Column     |          Type          | Collation | Nullable |               Default
----------------+------------------------+-----------+----------+--------------------------------------
 id             | integer                |           | not null | nextval('grant_id_seq'::regclass)
 voucher_number | character varying(255) |           |          |
 paid_by        | integer                |           |          |
 amount         | double precision       |           | not null |
Indexes:
    "grant_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "grant_paid_by_fkey" FOREIGN KEY (paid_by) REFERENCES vrauser(id)
Referenced by:
    TABLE "costing" CONSTRAINT "costing_voucher_id_fkey" FOREIGN KEY (voucher_id) REFERENCES grant(id)

- Costing table

vrapricing=# \d costing;
                                       Table "public.costing"
    Column     |          Type          | Collation | Nullable |               Default
---------------+------------------------+-----------+----------+-------------------------------------
 id            | integer                |           | not null | nextval('costing_id_seq'::regclass)
 deployment_id | integer                |           |          |
 type          | character varying(100) |           |          |
 po_number_id  | integer                |           |          |
 voucher_id    | integer                |           |          |
Indexes:
    "costing_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "costing_type_check" CHECK (type::text = ANY (ARRAY['Resource Expansion'::character varying, 'Duration Expansion'::character varying, 'Initial Resource'::character varying]::text[]))
Foreign-key constraints:
    "costing_deployment_id_fkey" FOREIGN KEY (deployment_id) REFERENCES deploymentid(id)
    "costing_po_number_id_fkey" FOREIGN KEY (po_number_id) REFERENCES purchaseorder(id)
    "costing_voucher_id_fkey" FOREIGN KEY (voucher_id) REFERENCES grant(id)