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)