db/setup_db.py

#! /usr/bin/python

#  Copyright 2014-2018 Linaro Limited
#  Authors: Dave Pigott <dave.pigot@linaro.org>,
#           Steve McIntyre <steve.mcintyre@linaro.org>
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program; if not, write to the Free Software
#  Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
#  MA 02110-1301, USA.

# First of all, create the vland user
# Next - create the vland database

# Create the switch, port, vlan, trunk and state tables

import datetime
from psycopg2 import connect

DATABASE_SCHEMA_VERSION = 1

conn = connect(database="postgres", user="postgres", password="postgres")

cur = conn.cursor()
cur.execute("CREATE USER vland WITH SUPERUSER")
cur.execute("CREATE DATABASE vland WITH OWNER = vland PASSWORD 'vland'")
conn.close()

conn = connect(database="vland", user="vland", password="vland")
cur = conn.cursor()

cur.execute("CREATE TABLE switch (switch_id SERIAL, name VARCHAR(64))")
cur.execute("CREATE TABLE port (port_id SERIAL, name VARCHAR(64),"
            "switch_id INTEGER, is_locked BOOLEAN,"
            "is_trunk BOOLEAN, base_vlan_id INTEGER,"
            "current_vlan_id INTEGER, number INTEGER, trunk_id INTEGER)")
cur.execute("CREATE TABLE vlan (vlan_id SERIAL, name VARCHAR(32),"
            "tag INTEGER, is_base_vlan BOOLEAN, creation_time TIMESTAMP)")
cur.execute("CREATE TABLE trunk (trunk_id SERIAL,"
            "creation_time TIMESTAMP)")
cur.execute("CREATE TABLE state (last_modified TIMESTAMP, schema_version INTEGER)")
cur.execute("INSERT INTO state (last_modified, schema_version) VALUES (%s, %s)" % (datetime.datetime.now(), DATABASE_SCHEMA_VERSION))
cur.execute("COMMIT;")

# Do not make any more changes here - the database code will cope with upgrades
# from this V1 database as they're needed.