Database Reset

Now that we have defined all our classes, we need to create the database tables, views, foreign keys, triggers, etc. We will initialize our application and tell the score.sa.orm module to initialize the database:

>>> from score.init import init_from_file
>>> score = init_from_file('dev.conf')
>>> score.orm.create()

This should silently generate all required database entities. We can now connect to the database and inspect it through the console:

$ sqlite3 database.sqlite3

Let’s first look at the tables and views:

sqlite> .tables
_article  _blogger  _user     article   blogger   user
sqlite> .schema _user
CREATE TABLE _user (
    username VARCHAR(100) NOT NULL,
    password VARBINARY(1137),
    _type VARCHAR(100) NOT NULL,
    id INTEGER NOT NULL,
    PRIMARY KEY (id)
);

We have a table, as well as a view for each class we created earlier. These automatically created views are a feature of the score.sa.orm module. They will make your life easier whenever you ever have to meddle with the database manually. They are also the reason why database tables start with an underscore: The more-readable name without the leading underscore is reserved for humans, where all members of all parent classes are aggregated into a convenient view.

sqlite> .schema _blogger
CREATE TABLE _blogger (
    id INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(id) REFERENCES _user (id)
);
CREATE TRIGGER autodel_blogger AFTER DELETE ON _blogger
FOR EACH ROW BEGIN
    DELETE FROM _user WHERE id = OLD.id;
END;
sqlite> .schema blogger
CREATE VIEW "blogger" AS SELECT _user.password, _user._type, _blogger.id, _user.username
FROM _blogger JOIN _user ON _user.id = _blogger.id;

When you’re done rejoicing in your marvellous database setup, you can leave your sqlite shell with a simple .quit command …

sqlite> .quit

… and head over to the next section to fill your database with some data.