SENG2021

3.2 - Tools 🔨

Advanced Persistence

In this lecture

 

  • Ways of storing data
  • Basics of SQL (Structured Query Language)
  • ORMs (Object Relational Mapping)

 

 

Persistence (Recap)

 

  • Persistence: When program state outlives the process that created it. This is achieved by storing the state as data in computer data storage.
  • Database: is an organized collection of structured information, or data, typically stored electronically in a computer system.

 

 

 

Simple Persistence

 

  • File storage formats
    • JSON
    • XML
    • CSV
    • Serialisation (Python pickle)
  • What happens when we need to scale up?
    • Performance
    • Concurrency
    • Recovery

Relational Databases

 

  • Store a series of tables
  • Tables are related to one another
  • Can specify:
    • Data types
    • Constraints
    • Relationships
  • Efficient storage and retrieval of data
  • Allows multiple simultaneous users
  • Maintains reliable access to stored data

 

Structured Query Language

 

  • Declarative language
  • Allows us to run queries on relational databases

Creating a Table

CREATE TABLE Student (
    zID int,
    first_name varchar(255),
    last_name varchar(255)
);

Inserting Data

INSERT INTO Student (zid, first_name, last_name)
VALUES
	(5157372, 'Adam', 'Stucci'),
	(5255918, 'An Thy', 'Tran'),
	(5204829, 'Andrew', 'Han'),
	(5204996, 'Braedon', 'Wooding'),
	(5122521, 'Carlin', 'Williamson'),
	(5259819, 'Chloe', 'Cheong'),
	(5260633, 'Clarence', 'Feng'),
	(5208437, 'Dominic', 'Wong'),
	(5169811, 'Eddie', 'Qi'),
	(5263737, 'Esther', 'Wong'),
	(5210932, 'Kaiqi', 'Liang'),
	(5113139, 'Max', 'Kelly'),
	(5169779, 'Nick', 'Patrikeos'),
	(5169766, 'Noa', 'Challis'),
	(5214750, 'Vivian', 'Shen'),
	(5260889, 'Weijie', 'Wang');

Reading

-- Select all
SELECT * FROM Student;

-- Select a specific column
SELECT zID FROM Student;

-- Filter by a column
SELECT first_name, last_name FROM Student WHERE zID = 5169779;

Updating

UPDATE Student SET zID = 555555 WHERE first_name = 'Nick';

Deleting

DELETE FROM Student WHERE zID = 555555;

Primary and Foreign Keys

CREATE TABLE IF NOT EXISTS Program (
    program_id int primary key
);

CREATE TABLE Students (
    zID int primary key,
    firstname varchar(255),
    lastname varchar(255),
    program_id int references Program(program_id)
);

Object Relational Mapping

 

  • A way for us to connect the Data Layer and Application Layer
  • A technique that allows us to query and manipulate database using an Object-Oriented paradigm
  • No raw SQL - all encapsulated in library functions
  • Tables = classes

Pros & Cons

 

  • Pros
    • DRY - easy to maintain, use and update
    • Abstractions which handle legwork
    • Avoids SQL strings floating around your codebase
    • Security via sanitisation - using prepared statements via method call - minimises SQLi
    • Allows us to use OOP
  • Cons
    • Another tool to learn
    • Another dependency 
    • Performance can be affected / abstracted

Creating the model

import peewee as pw

db = pw.SqliteDatabase('students.db', pragmas={'foreign_keys': 'on'})

class Entity(pw.Model):
    class Meta:
        database = db # which database are we connected to?

class Program(Entity):
    program_id = pw.IntegerField(primary_key=True)

class Student(Entity):
    zID = pw.IntegerField(primary_key=True)
    first_name = pw.TextField()
    last_name = pw.TextField()
    program_id = pw.ForeignKeyField(Program)

if __name__ == '__main__':
    db.connect()
    db.create_tables([Program, Student], safe=True)
    db.close()

Querying

 

from model import Program, Student

def get_programs():
    return Program.select().dicts()

def get_student_by_id(id: int):
    result = Student.select().where(Student.zID == id).dicts()
    if result:
        return result[0]

def get_students_and_program_by_program(program_id: int):
    return Student.select().join(Program)
    			  .where(Program.program_id == program_id)
    			  .dicts()

Documentation

 

http://docs.peewee-orm.com/en/latest/

Considerations

  • How will your database run / be stored when you deploy your service?
  • Deployment services provide support for different types of databases
if "HEROKU" in os.environ:
    url = urlparse(os.environ["DATABASE_URL"])
    db = PostgresqlDatabase(
        database=url.path[1:],
        user=url.username,
        password=url.password,
        host=url.hostname,
        port=url.port,
    )
else:
    db = SqliteDatabase(
        'test.db',
        pragmas={
            'foreign_keys': 'on'
        }
    )