genet / db_schema.py

#!/usr/bin/env python
################################################################################
# $Id$
################################################################################
# Copyright (c) 2008 Santiago Paya Miralta <santiagopm::wanadoo.es>
#
# This file is part of mine.
#
# Mine 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., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
################################################################################

from db_connection import db

def startSchema():
    """
    Start the db schema. Must be ran only once because restarts the master
    sequence and the type tables.
    """
    
    ##
    # All identiers of networks elements has the same sequence source.
    # 
    st = """
    DROP SEQUENCE IF EXISTS "gn_xx_ids_seq" CASCADE;
    CREATE SEQUENCE "gn_xx_ids_seq" 
      INCREMENT 1 
      MINVALUE 100000000 
      NO MAXVALUE 
      NO CYCLE;
    """
    return st, db.query(st)
    
    ##
    # Out from basic schema all tables are referenced by this ones.
    #
    st = """
    DROP TABLE IF EXISTS "gn_xx_node_types" CASCADE;
    CREATE SEQUENCE "gn_xx_node_types_seq";
    CREATE TABLE "gn_xx_node_types" (
        "id" integer NOT NULL DEFAULT nextval('gn_xx_node_types_seq'),
        "level" varchar(32),
        "tablename" varchar(63), -- pg NAMEDATALEN is 63 currently.
        "description" varchar(126)
    ) WITH (OIDS = FALSE);
    ALTER TABLE "gn_xx_node_types" 
        ADD CONSTRAINT "gn_xx_node_types_id" 
        PRIMARY KEY("id");
    ALTER SEQUENCE "gn_xx_node_types_seq" OWNED BY gn_xx_node_types.id;
    
    DROP TABLE IF EXISTS "gn_xx_edge_types" CASCADE;
    CREATE SEQUENCE "gn_xx_edge_types_seq";
    CREATE TABLE "gn_xx_edge_types" (
        "id" integer NOT NULL DEFAULT nextval('gn_xx_edge_types_seq'),
        "level" varchar(32),
        "tablename" varchar(63), -- pg NAMEDATALEN is 63 currently.
        "description" varchar(126)
    ) WITH (OIDS = FALSE);
    ALTER TABLE "gn_xx_edge_types" 
        ADD CONSTRAINT "gn_xx_edge_types_id" 
        PRIMARY KEY("id");
    ALTER SEQUENCE "gn_xx_edge_types_seq" OWNED BY gn_xx_edge_types.id;
    """
    return st, db.query(st)

def createLevelTables(level):
    """
    Tables schema. Create one level in datastore.

    param: level is the level identifier string.
    """
    st = """
    CREATE TABLE "gn_""" + level + """_nodes" (
        "id" integer NOT NULL DEFAULT nextval('gn_xx_ids_seq'),
        "iddown" integer,
        "downlevel" varchar(32),
        "idtype" integer
    ) WITH (OIDS = FALSE);
    ALTER TABLE "gn_""" + level + """_nodes" 
        ADD CONSTRAINT "gn_""" + level + """_nodes_id" 
        PRIMARY KEY("id");
    ALTER TABLE "gn_""" + level + """_nodes" 
        ADD CONSTRAINT "gn_""" + level + """_nodes_fkey_type" 
        FOREIGN KEY ("idtype")
        REFERENCES "gn_xx_node_types"("id") 
        ON UPDATE RESTRICT ON DELETE RESTRICT;

    CREATE TABLE "gn_""" + level + """_edges" (
        "id" integer NOT NULL DEFAULT nextval('gn_xx_ids_seq'),
        "idna" integer,
        "idnb" integer,
        "iddown" integer,
        "downlevel" varchar(32),
        "idtype" integer
    ) WITH (OIDS = FALSE);
    ALTER TABLE "gn_""" + level + """_edges" 
        ADD CONSTRAINT "gn_""" + level + """_edges_id" 
        PRIMARY KEY("id");
    ALTER TABLE "gn_""" + level + """_edges" 
        ADD CONSTRAINT "gn_""" + level + """_edges_fkey_topoA" 
        FOREIGN KEY ("idna")
        REFERENCES "gn_""" + level + """_nodes"("id") 
        ON UPDATE RESTRICT ON DELETE RESTRICT;
    ALTER TABLE "gn_""" + level + """_edges" 
        ADD CONSTRAINT "gn_""" + level + """_edges_fkey_topoB" 
        FOREIGN KEY ("idnb") 
        REFERENCES "gn_""" + level + """_nodes"("id") 
        ON UPDATE RESTRICT ON DELETE RESTRICT;
    ALTER TABLE "gn_""" + level + """_edges" 
        ADD CONSTRAINT "gn_""" + level + """_edges_fkey_type" 
        FOREIGN KEY ("idtype")
        REFERENCES "gn_xx_edge_types"("id") 
        ON UPDATE RESTRICT ON DELETE RESTRICT;
    """
    return st, db.query(st)

def dropLevelTables(level):
    """
    The level tables dropping deletes all network elements in the level.

    param: level is the level identifier string.
    """
    st = """
    DROP TABLE IF EXISTS "gn_""" + level + """_nodes" CASCADE;
    DROP TABLE IF EXISTS "gn_""" + level + """_edges" CASCADE;
    """
    return st, db.query(st)

################################################################################
# Data procedures.
################################################################################

def insertNodeType(level, tablename, description):
    st = "INSERT INTO gn_xx_node_types(level, tablename, description) " \
        "VALUES ('" \
        + level + "', '" \
        + tablename + "', '" \
        + description + "') " \
        "RETURNING id;"
    return st, db.query(st).getresult()[0][0]

def insertEdgeType(level, tablename, description):
    st = "INSERT INTO gn_xx_edge_types(level, tablename, description) " \
        "VALUES ('" \
        + level + "', '" \
        + tablename + "', '" \
        + description + "') " \
        "RETURNING id;"
    return st, db.query(st).getresult()[0][0]
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.