Craton_inventory_init.py

"""craton_inventory_init

Revision ID: ffdc1a500db1
Revises:
Create Date: 2016-06-03 09:52:55.302936

"""

# revision identifiers, used by Alembic.
revision = 'ffdc1a500db1'
down_revision = None
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa
import sqlalchemy_utils

# MAX_SHORT_UTF8MB4 is set to 191 since it uses 4 byte encoding
MAX_SHORT_UTF8MB4 = 191


def craton_create_table(table_name, *columns, **kw):
    table_columns = [
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.Column('updated_at', sa.DateTime(), nullable=True)]
    table_columns.extend(*columns)
    kw.update(
        mysql_engine='InnoDB',
        mysql_charset='utf8mb4')
    op.create_table(table_name, *table_columns, **kw)


access_secrets_columns=sa.Column('id', sa.Integer(), nullable=False), sa.Column('cert', sa.Text(), nullable=True), sa.PrimaryKeyConstraint('id')

labels_columns=sa.Column('id', sa.Integer(), nullable=False), sa.Column('label', sa.String(length=MAX_SHORT_UTF8MB4), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('label')

projects_columns=sa.Column('id', sa.Integer(), nullable=False), sa.Column('name', sa.String(length=MAX_SHORT_UTF8MB4), nullable=True), sa.PrimaryKeyConstraint('id')

label_variables_columns=sa.Column('parent_id', sa.Integer(), nullable=False), sa.Column('key', sa.String(length=MAX_SHORT_UTF8MB4), nullable=False), sa.Column('value', sqlalchemy_utils.types.json.JSONType(), nullable=True), sa.ForeignKeyConstraint(['parent_id'], ['labels.id'], ), sa.PrimaryKeyConstraint('parent_id', 'key')

regions_columns=sa.Column('id', sa.Integer(), nullable=False), sa.Column('project_id', sa.Integer(), nullable=False), sa.Column('name', sa.String(length=MAX_SHORT_UTF8MB4), nullable=True), sa.Column('note', sa.Text(), nullable=True), sa.ForeignKeyConstraint(['project_id'], ['projects.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('project_id', 'name', name='uq_region0projectid0name')

users_columns=sa.Column('id', sa.Integer(), nullable=False),
sa.Column('project_id', sa.Integer(), nullable=False), sa.Column('username', sa.String(length=MAX_SHORT_UTF8MB4), nullable=True), sa.Column('api_key', sa.String(length=36), nullable=True), sa.Column('is_root', sa.Boolean(), nullable=True), sa.Column('is_admin', sa.Boolean(), nullable=True), sa.Column('roles', sqlalchemy_utils.types.json.JSONType(), nullable=True), sa.ForeignKeyConstraint(['project_id'], ['projects.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('username', 'project_id', name='uq_user0username0project')

cells_columns=sa.Column('id', sa.Integer(), nullable=False), sa.Column('region_id', sa.Integer(), nullable=False), sa.Column('project_id', sa.Integer(), nullable=False), sa.Column('name', sa.String(length=MAX_SHORT_UTF8MB4), nullable=True), sa.Column('note', sa.Text(), nullable=True), sa.ForeignKeyConstraint(['project_id'], ['projects.id'], ), sa.ForeignKeyConstraint(['region_id'], ['regions.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('region_id', 'name', name='uq_cell0regionid0name')

region_variables_columns=sa.Column('parent_id', sa.Integer(), nullable=False), sa.Column('key', sa.String(length=MAX_SHORT_UTF8MB4), nullable=False), sa.Column('value', sqlalchemy_utils.types.json.JSONType(), nullable=True), sa.ForeignKeyConstraint(['parent_id'], ['regions.id'], ), sa.PrimaryKeyConstraint('parent_id', 'key')

cell_variables_columns=sa.Column('parent_id', sa.Integer(), nullable=False), sa.Column('key', sa.String(length=MAX_SHORT_UTF8MB4), nullable=False), sa.Column('value', sqlalchemy_utils.types.json.JSONType(), nullable=True), sa.ForeignKeyConstraint(['parent_id'], ['cells.id'], ), sa.PrimaryKeyConstraint('parent_id', 'key')

devices_columns=sa.Column('id', sa.Integer(), nullable=False), sa.Column('type', sa.String(length=50), nullable=True), sa.Column('device_type', sa.String(length=MAX_SHORT_UTF8MB4), nullable=False), sa.Column('name', sa.String(length=MAX_SHORT_UTF8MB4), nullable=False), sa.Column('region_id', sa.Integer(), nullable=False), sa.Column('cell_id', sa.Integer(), nullable=True), sa.Column('project_id', sa.Integer(), nullable=False), sa.Column('ip_address', sqlalchemy_utils.types.IPAddressType(length=64), nullable=False), sa.Column('active', sa.Boolean(), nullable=True), sa.Column('note', sa.Text(), nullable=True), sa.ForeignKeyConstraint(['cell_id'], ['cells.id'], ), sa.ForeignKeyConstraint(['project_id'], ['projects.id'], ), sa.ForeignKeyConstraint(['region_id'], ['regions.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('region_id', 'name', name='uq_device0regionid0name')

device_labels_columns=sa.Column('device_id', sa.Integer(), nullable=False), sa.Column('label_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['device_id'], ['devices.id'], ), sa.ForeignKeyConstraint(['label_id'], ['labels.id'], ), sa.PrimaryKeyConstraint('device_id', 'label_id')

device_variables_columns=sa.Column('id', sa.Integer(), nullable=False), sa.Column('type', sa.String(length=50), nullable=True), sa.Column('device_type', sa.String(length=MAX_SHORT_UTF8MB4), nullable=False), sa.Column('name', sa.String(length=MAX_SHORT_UTF8MB4), nullable=False), sa.Column('region_id', sa.Integer(), nullable=False), sa.Column('cell_id', sa.Integer(), nullable=True), sa.Column('project_id', sa.Integer(), nullable=False), sa.Column('ip_address', sqlalchemy_utils.types.IPAddressType(length=64), nullable=False), sa.Column('active', sa.Boolean(), nullable=True), sa.Column('note', sa.Text(), nullable=True), sa.ForeignKeyConstraint(['cell_id'], ['cells.id'], ), sa.ForeignKeyConstraint(['project_id'], ['projects.id'], ), sa.ForeignKeyConstraint(['region_id'], ['regions.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('region_id', 'name', name='uq_device0regionid0name')

hosts_columns=sa.Column('id', sa.Integer(), nullable=False), sa.Column('access_secret_id', sa.Integer(), nullable=True), sa.Column('parent_id', sa.Integer(), nullable=True), sa.ForeignKeyConstraint(['access_secret_id'], ['access_secrets.id'], ), sa.ForeignKeyConstraint(['id'], ['devices.id'], ), sa.ForeignKeyConstraint(['parent_id'], ['hosts.id'], ), sa.PrimaryKeyConstraint('id')

def upgrade():
    # commands auto generated by Alembic - please adjust!
    craton_create_table(
         'access_secrets',
         access_secrets_columns)
    craton_create_table(
        'labels',
         labels_columns)
    craton_create_table(
        'projects',
    projects_columns)
    craton_create_table(
        'label variable', 
    label_variables_columns)
    craton_create_table(
        'regions',
    regions_columns)
    op.create_index(op.f('ix_regions_project_id'),
                    'regions', ['project_id'], unique=False)
    craton_create_table(
        'users',
    users_columns)
    op.create_index(op.f('ix_users_project_id'), 'users', ['project_id'],
                    unique=False)
    craton_create_table(
        'cells',
    cells_columns)
    op.create_index(op.f('ix_cells_project_id'), 'cells', ['project_id'],
                    unique=False)
    op.create_index(op.f('ix_cells_region_id'), 'cells', ['region_id'],
                    unique=False)
    craton_create_table(
        'region_variables',
    region_variables_columns)
    craton_create_table(
        'cell_variables',
    cell_variables_columns)
    craton_create_table(
        'devices',
    devices_columns)
    op.create_index(op.f('ix_devices_cell_id'), 'devices', ['cell_id'],
                    unique=False)
    op.create_index(op.f('ix_devices_project_id'), 'devices', ['project_id'],
                    unique=False)
    op.create_index(op.f('ix_devices_region_id'), 'devices', ['region_id'],
                    unique=False)