Source code for pysteg.sql.setup

#! /usr/bin/env python
## -*- coding: utf-8 -*-
## (C) 2012: Hans Georg Schaathun <georg@schaathun.net> 

"""
Functions to create the database tables.
"""

print "[pysteg.sql.setup]"

from .svmodel import *
from .tables import *
from .tables import VectorFeature,FeatureLog
from .queue import *
from .features import fsconfig,fvconfig

table_list = [ FeatureSet,
	       Feature,
	       FeatureVector,
               TestSet,
               ImageSet,
	       Image,
               FeatureValue,
	       FeatureLog,
	       VectorFeature,
	       TestImage,
	       ScaleModel,
	       Scaling,
	       SVModel,
	       Queue,
	       SVMPerformance,
	       ]

def dropTables(**kw):
   """Create all the tables."""
   for T in table_list[::-1]: 
      T.dropTable(ifExists=True,dropJoinTables=True)
   conn = Feature._connection
   for d in viewundef:
      conn.queryAll(d)
def createTables(**kw):
   """Create all the tables."""
   for T in table_list: T.createTable(ifNotExists=True)
   conn = Feature._connection
   for d in viewdef:
      conn.queryAll(d)

viewundef = [
      "DROP VIEW scaled_feature_value ;",
      "DROP VIEW feature_value_view ;",
      "DROP VIEW test_image_view ;",
      "DROP VIEW feature_log_view ;",
      ]
viewdef = [
"""
CREATE VIEW scaled_feature_value AS
  SELECT
    CAST(scaling.id as text) || '-' || CAST(feature_value.id as text) AS id,
    feature.key as key, 
    feature.id as fid, 
    (feature_value.value - scaling.addterm)*scaling.factor as value,
    feature_value.value as rawvalue,
    scaling.addterm as addterm,
    scaling.factor as factor,
    feature_value.image_id as image_id,
    scaling.model_id as scalemodel_id
  FROM scaling,feature_value,feature,image
  WHERE
    scaling.feature_id=feature_value.feature_id and
    scaling.feature_id=feature.id and
    image.id=feature_value.image_id ;
""", 
"""
CREATE VIEW feature_value_view AS
  SELECT
    feature_value.id AS id,
    feature.key as key, 
    feature.id as fid, 
    feature_value.value as rawvalue,
    feature_value.image_id as image_id,
    image.filename as filename
  FROM feature_value,feature,image
  WHERE
    feature_value.feature_id=feature.id and
    image.id=feature_value.image_id ;
""", 
"""
CREATE VIEW test_image_view AS
  SELECT 
    image.id as id,
    image.filename as filename,
    image.imageset_id as imageset_id,
    image.source_id as source_id,
    test_set.name as testset,
    test_image.label as label,
    test_image.response as response
  FROM
    image,test_image,test_set
  WHERE
    image.id=test_image.image_id and
    test_set.id=test_image.imageset_id ;
""", 
"""
CREATE VIEW feature_log_view AS
  SELECT 
    image.id as id,
    image.filename as filename,
    image_set.name as imageset,
    image.source_id as source_id,
    feature_set.key,
    feature_log.entered
  FROM
    image,feature_log,feature_set,image_set
  WHERE
    image.imageset_id=image_set.id and
    image.id=feature_log.image_id and
    feature_set.id=feature_log.fset_id ;
""", 
]