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 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) )
