def _createDBFields( entitycode, fieldDefs, entityname ):
conn = connectors.getDBConnection()
createCur = conn.cursor()
dbFields = _getDBFields( entitycode, entityname )
if not dbFields.has_key( "__retired" ):
query = "ALTER TABLE \"%s\" ADD COLUMN \"__retired\" BOOLEAN DEFAULT 'f'" % entitycode
createCur.execute( query )
for attribute in fieldDefs.keys():
datatype = fieldDefs[attribute]["data_type"]["value"]
postgresType = connectors.getPgType( datatype )
if postgresType == None:
debug.debug( "field %s.%s (%s) not handled" % ( entitycode, attribute, datatype ) )
continue
elif ( dbFields.has_key( attribute ) and dbFields[attribute] == postgresType ):
pass
elif ( dbFields.has_key( attribute ) and dbFields[attribute] != postgresType ):
debug.debug( "changing type %s to %s" % ( dbFields[attribute], postgresType ) )
query = "ALTER TABLE \"" + entitycode + "\" ALTER COLUMN \""
query += str( attribute )
query += "\" TYPE "
query += postgresType
debug.debug( query )
createCur.execute( query )
else:
query = "ALTER TABLE \"" + entitycode + "\" ADD COLUMN \""
query += str( attribute )
query += "\" "
query += postgresType
debug.debug( query )
createCur.execute( query )
def _connect():
conf = config.Configuration()
conn = connectors.getDBConnection()
cur = conn.cursor()
sg = Shotgun(conf.get(config.CONF_SHOTGUN_URL),
conf.get(config.CONF_SHOTGUN_SYNC_SKRIPT),
conf.get(config.CONF_SHOTGUN_SYNC_KEY))
return (conn, cur, sg)
def _getDBFields(entityType, entityName):
conn = connectors.getDBConnection()
queryCur = conn.cursor()
createCur = conn.cursor()
queryCur.execute(
"""SELECT *
FROM information_schema.columns
WHERE table_catalog=%s AND table_name=%s
ORDER BY ordinal_position""",
(config.DB_DATABASE, entityType))
allFields = {}
for record in queryCur:
column_name = record[3]
data_type = record[7]
if data_type == "USER-DEFINED":
#debug(queryCur.description)
#debug(record)
data_type = record[27]
elif data_type == "ARRAY":
data_type = record[27][1:] + "[]"
allFields[column_name] = data_type
if queryCur.rowcount == 0:
fieldstr = "\"__local_id\" SERIAL PRIMARY KEY"
query = "CREATE TABLE \"%s\" (%s)" % (entityType, fieldstr)
debug.debug(query)
createCur.execute(query)
for column in ["__retired", "id", "__local_id", "sg_link"]:
query = "CREATE INDEX %s ON \"%s\" (\"%s\")" % (
"%s_%s_idx" %
(entityType.lower(), column.lower()), entityType, column)
try:
debug.debug(query)
createCur.execute(query)
except psycopg2.ProgrammingError:
debug.debug("%s of %s does not exist or index already available" %
(column, entityType))
if entityName != entityType:
query = "COMMENT ON TABLE \"%s\" IS 'Entity name: %s'" % (entityType,
entityName)
else:
query = "COMMENT ON TABLE \"%s\" IS ''" % (entityType)
createCur.execute(query)
queryCur.close()
createCur.close()
return allFields
def _connect():
conf = config.Configuration()
conn = connectors.getDBConnection()
cur = conn.cursor()
sg = Shotgun(
conf.get(config.CONF_SHOTGUN_URL),
conf.get(config.CONF_SHOTGUN_SYNC_SKRIPT),
conf.get(config.CONF_SHOTGUN_SYNC_KEY),
)
return (conn, cur, sg)
def _getDBFields( entityType, entityName ):
conn = connectors.getDBConnection()
queryCur = conn.cursor()
createCur = conn.cursor()
queryCur.execute( """SELECT *
FROM information_schema.columns
WHERE table_catalog=%s AND table_name=%s
ORDER BY ordinal_position""",
( config.DB_DATABASE, entityType ) )
allFields = {}
for record in queryCur:
column_name = record[3]
data_type = record[7]
if data_type == "USER-DEFINED":
#debug(queryCur.description)
#debug(record)
data_type = record[27]
elif data_type == "ARRAY":
data_type = record[27][1:] + "[]"
allFields[column_name] = data_type
if queryCur.rowcount == 0:
fieldstr = "\"__local_id\" SERIAL PRIMARY KEY"
query = "CREATE TABLE \"%s\" (%s)" % ( entityType,
fieldstr )
debug.debug( query )
createCur.execute( query )
for column in [ "__retired", "id", "__local_id", "sg_link" ]:
query = "CREATE INDEX %s ON \"%s\" (\"%s\")" % ( "%s_%s_idx" % ( entityType.lower(), column.lower() ),
entityType,
column )
try:
debug.debug( query )
createCur.execute( query )
except psycopg2.ProgrammingError:
debug.debug( "%s of %s does not exist or index already available" % ( column, entityType ) )
if entityName != entityType:
query = "COMMENT ON TABLE \"%s\" IS 'Entity name: %s'" % ( entityType, entityName );
else:
query = "COMMENT ON TABLE \"%s\" IS ''" % ( entityType );
createCur.execute( query )
queryCur.close()
createCur.close()
return allFields
def _createDBFields(entitycode, fieldDefs, entityname):
conn = connectors.getDBConnection()
createCur = conn.cursor()
dbFields = _getDBFields(entitycode, entityname)
if not dbFields.has_key("__retired"):
query = "ALTER TABLE \"%s\" ADD COLUMN \"__retired\" BOOLEAN DEFAULT 'f'" % entitycode
createCur.execute(query)
for attribute in fieldDefs.keys():
datatype = fieldDefs[attribute]["data_type"]["value"]
postgresType = connectors.getPgType(datatype)
if postgresType == None:
debug.debug("field %s.%s (%s) not handled" %
(entitycode, attribute, datatype))
continue
elif (dbFields.has_key(attribute)
and dbFields[attribute] == postgresType):
pass
elif (dbFields.has_key(attribute)
and dbFields[attribute] != postgresType):
debug.debug("changing type %s to %s" %
(dbFields[attribute], postgresType))
query = "ALTER TABLE \"" + entitycode + "\" ALTER COLUMN \""
query += str(attribute)
query += "\" TYPE "
query += postgresType
debug.debug(query)
createCur.execute(query)
else:
query = "ALTER TABLE \"" + entitycode + "\" ADD COLUMN \""
query += str(attribute)
query += "\" "
query += postgresType
debug.debug(query)
createCur.execute(query)