def get_columns(my, required_only=False): if my.search_type == 'sthpw/virtual': return [] search_type_obj = SearchType.get(my.search_type) table = search_type_obj.get_table() from pyasm.biz import Project db_resource = Project.get_db_resource_by_search_type(my.search_type) database_name = db_resource.get_database() db = DbContainer.get(db_resource) # table may not exist try: all_columns = db.get_columns(table) columns = [] if required_only: nullables = db.get_column_nullables(table) for column in all_columns: null_ok = nullables.get(column) if not null_ok: columns.append(column) # if there are no required columns if not columns: columns = all_columns else: columns = all_columns except SqlException: Environment.add_warning('missing table', 'Table [%s] does not exist in database [%s]' %(table, database_name)) return [] return columns
def get_data_type(cls, search_type, attr_type): search_type_obj = SearchType.get(search_type) db_resource = Project.get_db_resource_by_search_type(search_type) sql = DbContainer.get(db_resource) impl = sql.get_database_impl() # SearchType Manager and Add Widget Column use mixed upper and # lowercases for the following attr_type, so fix it at some point if not attr_type: attr_type = "varchar" if attr_type == "integer": data_type = impl.get_int() elif attr_type == "float": data_type = "float" elif attr_type == "boolean": data_type = impl.get_boolean() elif attr_type == "link": data_type = "text" elif attr_type.startswith('varchar'): data_type = attr_type elif attr_type == 'time': data_type = impl.get_timestamp() elif attr_type in ["Date", "date"]: data_type = impl.get_timestamp() elif attr_type == "Category": data_type = "varchar(256)" elif attr_type in ["text", "Text"]: data_type = impl.get_text() elif attr_type in ["Date Range", 'timestamp']: data_type = impl.get_timestamp() elif attr_type == "Checkbox": data_type = "varchar(256)" elif attr_type in ["Foreign Key", "foreign_key"]: data_type = "varchar(256)" elif attr_type in ["List", "list"]: data_type = "varchar(512)" elif attr_type == "Name/Code": data_type = "varchar(256)" elif attr_type == "Number": data_type = impl.get_int() elif attr_type in ["currency", "scientific", "percent"]: data_type = "float" elif attr_type == "timecode": data_type = impl.get_int() else: #data_type = "varchar(256)" data_type = impl.get_varchar() return data_type
def execute(my): search_type_obj = SearchType.get(my.search_type) table = search_type_obj.get_table() db_resource = Project.get_db_resource_by_search_type(my.search_type) sql = DbContainer.get(db_resource) index_name = "%s_%s_idx" % (table, my.column) if my.constraint == "unique": statement = 'ALTER TABLE "%s" add constraint "%s" UNIQUE ("%s")' % (table, index_name, my.column) else: statement = 'CREATE INDEX "%s" ON "%s" ("%s")' % (index_name, table, my.column) sql.do_update(statement) sql.commit()
def _add_column(my, column, type): # if there is no type, then no column is created for widget_config if type != "": # alter the table search_type_obj = SearchType.get(my.search_type) db_resource = Project.get_db_resource_by_search_type( my.search_type) sql = DbContainer.get(db_resource) impl = sql.get_database_impl() table = search_type_obj.get_table() columns = sql.get_columns(table) # if the column exists already, skip it if column in columns: print "skipping: ", column raise TacticException( '[%s] already existed in this table [%s]' % (column, table)) return # FIXME: database dependency should be in DatabaseImpl if sql.get_database_type() == "MongoDb": statement = None elif sql.get_database_type() == "MySQL": if type == "varchar": type = "varchar(256)" statement = 'ALTER TABLE "%s" ADD COLUMN "%s" %s' % \ (table, column, type) elif sql.get_database_type() == "Oracle": statement = 'ALTER TABLE "%s" ADD("%s" %s)' % \ (table, column, type) elif sql.get_database_type() == 'SQLServer': statement = 'ALTER TABLE [%s] ADD "%s" %s' % \ (table, column, type) else: statement = 'ALTER TABLE "%s" ADD COLUMN "%s" %s' % \ (table, column, type) if statement: if not my.nullable: statement = '%s NOT NULL' % statement sql.do_update(statement) AlterTableUndo.log_add(db_resource, table, column, type)
def execute(self): search_type_obj = SearchType.get(self.search_type) table = search_type_obj.get_table() db_resource = Project.get_db_resource_by_search_type(self.search_type) sql = DbContainer.get(db_resource) if self.constraint == "unique": index_name = "%s_%s_unique" % (table, self.column) statement = 'ALTER TABLE "%s" add constraint "%s" UNIQUE ("%s")' % (table, index_name, self.column) else: index_name = "%s_%s_idx" % (table, self.column) statement = 'CREATE INDEX "%s" ON "%s" ("%s")' % (index_name, table, self.column) sql.do_update(statement) sql.commit()
def _add_column(self, column, type): # if there is no type, then no column is created for widget_config if type != "": # alter the table search_type_obj = SearchType.get(self.search_type) db_resource = Project.get_db_resource_by_search_type(self.search_type) sql = DbContainer.get(db_resource) impl = sql.get_database_impl() table = search_type_obj.get_table() columns = sql.get_columns(table) # if the column exists already, skip it if column in columns: print "skipping: ", column raise TacticException('[%s] already existed in this table [%s]'%(column, table)) return # FIXME: database dependency should be in DatabaseImpl if sql.get_database_type() == "MongoDb": statement = None elif sql.get_database_type() == "MySQL": if type == "varchar": type = "varchar(256)" statement = 'ALTER TABLE "%s" ADD COLUMN "%s" %s' % \ (table, column, type) elif sql.get_database_type() == "Oracle": statement = 'ALTER TABLE "%s" ADD("%s" %s)' % \ (table, column, type) elif sql.get_database_type() == 'SQLServer': statement = 'ALTER TABLE [%s] ADD "%s" %s' % \ (table, column, type) else: statement = 'ALTER TABLE "%s" ADD COLUMN "%s" %s' % \ (table, column, type) if statement: if not self.nullable: statement = '%s NOT NULL' %statement sql.do_update(statement) AlterTableUndo.log_add(db_resource,table,column,type)
def execute(my): search_type_obj = SearchType.get(my.search_type) db_resource = Project.get_db_resource_by_search_type(my.search_type) sql = DbContainer.get(db_resource) impl = sql.get_database_impl() data_type = my.get_data_type(my.search_type, my.attr_type) # if there is no type, then no column is created for widget_config if my.attr_type == "Date Range": column1 = "%s_start_date" % my.attr_name column2 = "%s_end_date" % my.attr_name my._add_column(column1, data_type) my._add_column(column2, data_type) elif type != "": my._add_column(my.attr_name, data_type) my.add_description("Added attribute '%s' of type '%s'" % (my.attr_name, my.attr_type))
def execute(my): search_type_obj = SearchType.get(my.search_type) db_resource = Project.get_db_resource_by_search_type(my.search_type) sql = DbContainer.get(db_resource) impl = sql.get_database_impl() data_type = my.get_data_type(my.search_type, my.attr_type) # if there is no type, then no column is created for widget_config if my.attr_type == "Date Range": column1 = "%s_start_date" % my.attr_name column2 = "%s_end_date" % my.attr_name my._add_column(column1, data_type) my._add_column(column2, data_type) elif type != "": my._add_column(my.attr_name, data_type) my.add_description("Added attribute '%s' of type '%s'" % (my.attr_name, my.attr_type) )
def get_columns(self, required_only=False): if self.search_type == 'sthpw/virtual': return [] search_type_obj = SearchType.get(self.search_type) table = search_type_obj.get_table() from pyasm.biz import Project db_resource = Project.get_db_resource_by_search_type(self.search_type) database_name = db_resource.get_database() db = DbContainer.get(db_resource) # table may not exist try: all_columns = db.get_columns(table) columns = [] if required_only: nullables = db.get_column_nullables(table) for column in all_columns: null_ok = nullables.get(column) if not null_ok: columns.append(column) # if there are no required columns if not columns: columns = all_columns else: columns = all_columns except SqlException: Environment.add_warning( 'missing table', 'Table [%s] does not exist in database [%s]' % (table, database_name)) return [] return columns
def _test_project(self): from pyasm.biz import Project sql = Project.get_database_impl() # Don't bother running if you don't have sample3d if not sql.database_exists('sample3d'): return Project.set_project("sthpw") sobject = SearchType.create('prod/shot?project=sample3d', columns=['code', 'sequence_code', 'pipeline_code'], result=['S001','HT001','shot']) self.assertEquals("prod/shot?project=sample3d", sobject.get_search_type()) if sql.database_exists('sample3d'): db_resource = Project.get_db_resource_by_search_type('prod/bin?project=sample3d') exists= sql.table_exists( db_resource ,'bin') if exists: search = Search('prod/bin', project_code='sample3d') self.assertEquals("prod/bin?project=sample3d", search.get_search_type()) # check that a search type is properly created search_type = SearchType.get("prod/shot?project=sample3d") base_key = search_type.get_base_key() self.assertEquals("prod/shot", base_key) # NOTE: search_type get_full_key() method is deprecated. # test that the sobject maintains the search type sobject = SearchType.create("prod/shot?project=sample3d") search_type = sobject.get_search_type() self.assertEquals("prod/shot?project=sample3d", search_type) # set it back to unittest Project.set_project("unittest") # test current project is added when there is not project set sobject = SearchType.create("prod/shot") search_type = sobject.get_search_type() self.assertEquals("prod/shot?project=unittest", search_type) # test current project is added when there is not project set, even # when the project has changed sobject = SearchType.create("prod/shot") search_type = sobject.get_search_type() self.assertEquals("prod/shot?project=unittest", search_type) if sql.database_exists('sample3d'): Project.set_project("sample3d") project_code = Project.get_project_code() self.assertEquals("sample3d", project_code) # set it back to unittest project Project.set_project("unittest") # test the search if sql.database_exists('sample3d'): search_type = "prod/shot?project=sample3d" search = Search(search_type) project_code = search.get_project_code() self.assertEquals("sample3d", project_code) # test the search project code even though the project has hanved search_type = "prod/shot?project=sample3d" search = Search(search_type) project_code = search.get_project_code() self.assertEquals("sample3d", project_code) Project.set_project("admin") project_code = search.get_project_code() self.assertEquals("sample3d", project_code) project_code = Project.get_project_code() self.assertEquals("admin", project_code) # set it back to unittest project Project.set_project("unittest")
def execute(my): search_type_obj = SearchType.get(my.search_type) db_resource = Project.get_db_resource_by_search_type(my.search_type) sql = DbContainer.get(db_resource) impl = sql.get_database_impl() data_type = my.get_data_type(my.search_type, my.attr_type) """ # SearchType Manager and Add Widget Column use mixed upper and # lowercases for the following attr_type, so fix it at some point if not my.attr_type: my.attr_type = "varchar" if my.attr_type == "integer": type = impl.get_int() elif my.attr_type == "float": type = "float" elif my.attr_type == "boolean": type = impl.get_boolean() elif my.attr_type == "link": type = "text" elif my.attr_type.startswith('varchar'): type = my.attr_type elif my.attr_type == 'time': type = impl.get_timestamp() elif my.attr_type in ["Date", "date"]: type = impl.get_timestamp() elif my.attr_type == "Category": type = "varchar(256)" elif my.attr_type in ["text", "Text"]: type = impl.get_text() elif my.attr_type in ["Date Range", 'timestamp']: type = impl.get_timestamp() elif my.attr_type == "Checkbox": type = "varchar(256)" elif my.attr_type in ["Foreign Key", "foreign_key"]: type = "varchar(256)" elif my.attr_type in ["List", "list"]: type = "varchar(512)" elif my.attr_type == "Name/Code": type = "varchar(256)" elif my.attr_type == "Number": type = impl.get_int() elif my.attr_type in ["currency", "scientific", "percent"]: type = "float" elif my.attr_type == "timecode": type = impl.get_int() else: #type = "varchar(256)" type = impl.get_varchar() """ # if there is no type, then no column is created for widget_config if my.attr_type == "Date Range": column1 = "%s_start_date" % my.attr_name column2 = "%s_end_date" % my.attr_name my._add_column(column1, data_type) my._add_column(column2, data_type) elif type != "": my._add_column(my.attr_name, data_type) my.add_description("Added attribute '%s' of type '%s'" % (my.attr_name, my.attr_type) )