def __init__(my, port=''): # It is possible on startup that the database is not running. from pyasm.search import DbContainer, DatabaseException, Sql try: sql = DbContainer.get("sthpw") if sql.get_database_type() != "MongoDb": # before batch, clean up the ticket with a NULL code if os.getenv('TACTIC_MODE') != 'production': sql.do_update('DELETE from "ticket" where "code" is NULL;') else: start_port = Config.get_value("services", "start_port") if start_port: start_port = int(start_port) else: start_port = 8081 if port and int(port) == start_port: sql.do_update('DELETE from "ticket" where "code" is NULL;') except DatabaseException, e: # TODO: need to work on this print "ERROR: could not connect to [sthpw] database" #os.environ["TACTIC_CONFIG_PATH"] = Config.get_default_config_path() #Sql.set_default_vendor("Sqlite") Config.set_tmp_config() Config.reload_config() # try connecting again try: sql = DbContainer.get("sthpw") except: print "Could not connect to the database." raise
def delete(my,log=False): column = my.get_value("name") search_type = my.get_value("search_type") search_type_obj = SearchType.get(search_type) table = search_type_obj.get_table() database = search_type_obj.get_database() # remove it from the table if log: AlterTableUndo.log_drop(database, table, column) sql = DbContainer.get(database) try: from pyasm.search.sql import Sql if Sql.get_database_type() == 'SQLServer': statement = 'ALTER TABLE [%s] DROP "%s" %s' % \ (table, column) else: statement = 'ALTER TABLE "%s" DROP COLUMN "%s"' % (table, column) sql.do_update(statement) except SqlException, e: print("WARNING: %s" % e )
def execute(my): database = "sthpw" sql = DbContainer.get(database) value_array = sql.do_query("select code, cc from (select code, count(code) as cc from file group by code order by cc desc) as X where cc > 1;") #value_array = sql.do_query("select code, cc from (select code, count(code) as cc from file group by code order by cc desc) as X;") print "found [%s] pairs" % len(value_array) for count, value_list in enumerate(value_array): if count >= BATCH: break # get the file object file_code = value_list[0] search = Search("sthpw/file") search.add_filter("code", file_code) files = search.get_sobjects() #if len(files) == 1: # continue for file in files: project_code = file.get_value("project_code") if not project_code: print "WARNING: file [%s] has no project_code" % file_code continue project = Project.get_by_code(project_code) initials = project.get_initials() id = file.get_id() new_file_code = "%s%s" % (id, initials) if file_code == new_file_code: continue print "-"*20 print "switching: ", file_code, "to", new_file_code snapshot_code = file.get_value("snapshot_code") snapshot = Snapshot.get_by_code(snapshot_code) assert snapshot snapshot_xml = snapshot.get_xml_value("snapshot") print snapshot_xml.to_string() node = snapshot_xml.get_node("snapshot/file[@file_code='%s']" % file_code) Xml.set_attribute(node, "file_code", new_file_code) print snapshot_xml.to_string() assert node # set the file_code file.set_value("code", new_file_code) file.commit() # set the snapshot snapshot.set_value("snapshot", snapshot_xml.to_string() ) snapshot.commit()
def get_tables_wdg(my): div = DivWdg() div.set_name("Tables") div.add("In order to fully register a database, you must bind it to a TACTIC project") div.add("<br/>") project_code = "mongodb" database = "test_database" db_resource = DbResource( server='localhost', vendor='MongoDb', database=database ) try: connect = DbContainer.get(db_resource) except Exception, e: div.add("Could not connect") div.add_style("padding: 30px") div.add("<br/>"*2) div.add(str(e)) return div
def fix_notification_login_id(): db = DbContainer.get("sthpw") sql = ''' BEGIN TRANSACTION; CREATE TABLE t_backup ( id integer PRIMARY KEY AUTOINCREMENT, notification_log_id integer, "login" character varying(256), "type" character varying(256), project_code character varying(256), "timestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP ); INSERT INTO t_backup SELECT id, notification_log_id, "login", "type", project_code, "timestamp" FROM %(table)s; DROP TABLE %(table)s; ALTER TABLE t_backup RENAME TO %(table)s; COMMIT; ''' % {"table": "notification_login"} conn = db.conn conn.executescript(sql)
def import_bootstrap(): print "Importing bootstrap ..." vendor = "SQLServer" impl = DatabaseImpl.get(vendor) impl.create_database("sthpw") upgrade_dir = Environment.get_upgrade_dir() for category in ['bootstrap', 'sthpw', 'config']: f = open("%s/%s/%s_schema.sql" % (upgrade_dir, vendor.lower(), category) ) data = f.read() f.close() data = data.split(";") cmds = [] for cmd in data: cmd = cmd.strip() if cmd == '': continue cmds.append(cmd) from pyasm.search import DbContainer sql = DbContainer.get("sthpw") for cmd in cmds: sql.do_update(cmd)
def __init__(my, num_processes=None): my.check_interval = 120 my.num_processes = num_processes my.dev_mode = False sql = DbContainer.get("sthpw") # before batch, clean up the ticket with a NULL code sql.do_update('DELETE from "ticket" where "code" is NULL;')
def get_columns(cls, db_resource, table): from pyasm.search import DbResource, DbContainer sql = DbContainer.get(db_resource) conn = sql.get_connection() collection = conn.get_collection(table) # FIXME: # This just gets the first one to discover the columns. This is # not accurate because each item in a collection can contain # different "attributes". The key here is to define a location # for where this "schema" description is stored result = collection.find_one() if not result: return ['_id'] else: columns = result.keys() # assume existence of both code and _id #if "code" in columns: # columns.remove("code") #columns.insert(0, "code") if "_id" in columns: columns.remove("_id") columns.insert(0, "_id") return columns
def fix_debug_log_id(): db = DbContainer.get("sthpw") sql = ''' BEGIN TRANSACTION; CREATE TABLE t_backup ( id integer PRIMARY KEY AUTOINCREMENT, "category" character varying(256), "level" character varying(256), "message" text, "timestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP, "login" character varying(256), "s_status" character varying(30) ); INSERT INTO t_backup SELECT "id", "category", "level", "message", "timestamp", "login", "s_status" FROM %(table)s; DROP TABLE %(table)s; ALTER TABLE t_backup RENAME TO %(table)s; COMMIT; ''' % {"table": "debug_log"} conn = db.conn conn.executescript(sql)
def execute(self): if not self.login_name: self.login_name = self.kwargs.get('login'); # invalidate the ticket security = Environment.get_security() ticket = security.get_ticket() if ticket == None: return login_name = ticket.get_value("login") print "Signing out: ", login_name # expire the ticket from pyasm.security import Site site = Site.get() if site: Site.set_site("default") try: from pyasm.search import Sql, DbContainer sql = DbContainer.get("sthpw") ticket.set_value("expiry", sql.get_timestamp_now(), quoted=False) ticket.commit() except: if site: Site.pop_site()
def has_table(my, search_type): if isinstance(search_type, basestring): search_type = SearchType.get(search_type) # in search type database == project project_code = search_type.get_project_code() # get the db_resource for this project db_resource = my.get_project_db_resource() # get the table table = search_type.get_table() if not table: return False try: # looking up a database's tables other than the current one sql = DbContainer.get(db_resource) tables = sql.get_tables() has_table = table in tables except Exception, e: print "WARNING: in Project.has_table(): table [%s] not found" % table print "Message: ", e has_table = False
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 _test_time(my): """ test timezone related behavior""" sobject = SearchType.create("sthpw/task") sobject.set_value("project_code", "unittest") sobject.set_value("bid_start_date", "2014-11-11 05:00:00") time = sobject.get_value("bid_start_date") my.assertEquals(time, "2014-11-11 05:00:00") sobject.commit() time = sobject.get_value("bid_start_date") my.assertEquals(time, "2014-11-11 05:00:00") from pyasm.search import DbContainer sql = DbContainer.get("sthpw") db_value = sql.do_query("SELECT bid_start_date from task where id = %s" % sobject.get_id()) # 2014-11-11 00:00:00 is actually written to the database my.assertEquals(db_value[0][0].strftime("%Y-%m-%d %H:%M:%S %Z"), "2014-11-11 00:00:00 ") # an sType specified without a project but with an id could be a common human error # but it should handle that fine obj1 = Search.eval('@SOBJECT(unittest/person?project=unittest["id", "%s"])' % sobject.get_id(), single=True) obj2 = Search.eval('@SOBJECT(unittest/person?id=2["id", "%s"])' % sobject.get_id(), single=True) obj3 = Search.eval('@SOBJECT(sthpw/task?id=2["id", "%s"])' % sobject.get_id(), single=True) task = Search.eval('@SOBJECT(sthpw/task["id", "%s"])' % sobject.get_id(), single=True) # EST and GMT diff is 5 hours my.assertEquals(task.get_value("bid_start_date"), "2014-11-11 05:00:00")
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 __init__(my, num_processes=None): my.check_interval = 120 my.num_processes = num_processes my.dev_mode = False import sys plugin_dir = Environment.get_plugin_dir() sys.path.insert(0, plugin_dir) sql = DbContainer.get("sthpw") # before batch, clean up the ticket with a NULL code sql.do_update('DELETE from "ticket" where "code" is NULL;')
def get_table_info(self, database): from pyasm.search import DbResource, DbContainer sql = DbContainer.get(database) conn = sql.get_connection() collections = conn.collection_names() table_info = {} for collection in collections: table_info[collection] = { } return table_info
def get_sobjects(self): columns = ['product_name', 'login_id', 'sample_size', 'labor_average'] sql = ''' select "product_name", "login_id", count("total_hours") "sample_size", avg("total_hours") "labor_average" from ( select "product_type"."id", "product_type"."product_name", "subtask_product"."product_quantity", "subtask"."subtask_letter", "subtask_labor"."total_hours", "personal_time_log"."login_id" FROM "product_type" LEFT OUTER JOIN "subtask_product" ON "product_type"."id" = "subtask_product"."product_type_id" INNER JOIN "subtask" ON "subtask"."id" = "subtask_product"."subtask_id" INNER JOIN "subtask_labor" ON "subtask"."id" = "subtask_labor"."subtask_id" INNER JOIN "personal_time_log" ON "subtask_labor"."id" = "personal_time_log"."subtask_labor_id" ) X GROUP BY X."product_name", X."login_id" ''' db = DbContainer.get("MMS") from pyasm.search import Select, Search search_type = self.kwargs.get('search_type') select = Select() select.set_statement(sql) statement = select.get_statement() search = Search(search_type) search.select = select sobjects = search.get_sobjects() for sobject in sobjects: sobject.set_value("id", -1) return sobjects
def _test_time(my): ''' test timezone related behavior''' sobject = SearchType.create('sthpw/task') sobject.set_value('project_code','unittest') sobject.set_value('bid_start_date', '2014-11-11 05:00:00') time = sobject.get_value('bid_start_date') my.assertEquals(time, '2014-11-11 05:00:00') sobject.commit() time = sobject.get_value('bid_start_date') my.assertEquals(time, '2014-11-11 05:00:00') from pyasm.search import DbContainer sql = DbContainer.get('sthpw') db_value = sql.do_query('SELECT bid_start_date from task where id = %s'%sobject.get_id()) # 2014-11-11 00:00:00 is actually written to the database my.assertEquals(db_value[0][0].strftime('%Y-%m-%d %H:%M:%S %Z'), '2014-11-11 00:00:00 ') # an sType specified without a project but with an id could be a common human error # but it should handle that fine obj1 = Search.eval('@SOBJECT(unittest/person?project=unittest["id", "%s"])'%sobject.get_id(), single=True) obj2= Search.eval('@SOBJECT(unittest/person?id=2["id", "%s"])'%sobject.get_id(), single=True) obj3 = Search.eval('@SOBJECT(sthpw/task?id=2["id", "%s"])'%sobject.get_id(), single=True) task = Search.eval('@SOBJECT(sthpw/task["id", "%s"])'%sobject.get_id(), single=True) # EST and GMT diff is 5 hours my.assertEquals(task.get_value('bid_start_date'), '2014-11-11 05:00:00') # test NOW() auto conversion sobj = SearchType.create('sthpw/note') sobj.set_value('process','TEST') sobj.set_value('note','123') my.assertEquals(sobj.get_value('timestamp'), "") sobj.commit() # this is local commited time converted back to GMT committed_time = sobj.get_value('timestamp') from dateutil import parser committed_time = parser.parse(committed_time) from pyasm.common import SPTDate now = SPTDate.now() diff = now - committed_time # should be roughly the same minute, not hours apart my.assertEquals(diff.seconds < 60, True)
def __init__(self, num_processes=None): self.check_interval = 120 self.startup = True self.num_processes = num_processes self.dev_mode = False import sys plugin_dir = Environment.get_plugin_dir() sys.path.insert(0, plugin_dir) sql = DbContainer.get("sthpw") # before batch, clean up the ticket with a NULL code sql.do_update('DELETE from "ticket" where "code" is NULL;') self.tactic_threads = [] self.mode = 'normal'
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 register(my, name, db_resource, tables=None): db_name = db_resource.get_database() project_code = "db_resource/%s" % db_name # create a virtual project project = SearchType.create("sthpw/project") # FIXME: what is this conflicts with an existing project project.set_value("code", project_code) project.set_value("db_resource", db_resource) # put this sobject into the cache from pyasm.biz import Project key = "sthpw/project|%s" % project_code Project.cache_sobject(key, project) if tables == None: # introspect and resister all of the tables sql = DbContainer.get(db_resource) table_info = sql.get_table_info() if table_info.get("spt_search_type"): print "has search_type" tables = table_info.keys() for table in tables: # FIXME: how is this created search_type = "table/%s?project=%s" % (table, db_name) #search_type_obj = SearchType.create("config/search_type") search_type_obj = SearchType.create("sthpw/search_type") search_type_obj.set_value("search_type", "table/%s" % table) search_type_obj.set_value("title", Common.get_display_title(table) ) search_type_obj.set_value("table_name", table) search_type_obj.set_value("database", db_name) SearchType.set_virtual(search_type, search_type_obj) my.db_resources[name] = db_resource
def on_insert(my): '''Function that should be run on insert/update. It's already automatically called during insert. On update, the caller needs to call this explicitly. It checks the search type this pipeline is associated with and if there is no pipeline code column, then update it. It updates the process table also.''' search_type = my.get_value('search_type') my.update_process_table(search_type=search_type) # don't do anything for task sType if search_type =='sthpw/task': return if not search_type: return if ProdSetting.get_value_by_key('autofill_pipeline_code') != 'false': try: columns = SearchType.get_columns(search_type) if not 'pipeline_code' in columns: # add the pipeline code column from pyasm.command import ColumnAddCmd cmd = ColumnAddCmd(search_type, "pipeline_code", "varchar") cmd.execute() except SqlException, e: print "Error creating column [pipeline_code] for %" %search_type pass # go through all of the sobjects and set all the empty ones # to the new pipeline search = Search(search_type) search.add_op("begin") search.add_filter("pipeline_code", "NULL", op='is', quoted=False) search.add_filter("pipeline_code", "") search.add_op("or") sobject_ids = search.get_sobject_ids() if sobject_ids: # this is much faster and memory efficient db_resource = SearchType.get_db_resource_by_search_type(search_type) sql = DbContainer.get(db_resource) tbl = search.get_table() sobject_ids = [str(x) for x in sobject_ids] pipeline_code = my.get_value("code") sql.do_update('''UPDATE "%s" SET "pipeline_code" = '%s' WHERE id in (%s) ''' %(tbl, pipeline_code, ','.join(sobject_ids))) """
def get_next_job(job_search_type="sthpw/queue", queue_type=None, server_code=None): sql = DbContainer.get("sthpw") search_type_obj = SearchType.get(job_search_type) table = search_type_obj.get_table() # get the entire queue search = Search(job_search_type) if queue_type: search.add_filter("queue", queue_type) if server_code: search.add_filter("server_code", server_code) search.add_filter("state", "pending") search.add_order_by("priority") search.add_order_by("timestamp") chunk = 10 search.add_limit(chunk) queues = search.get_sobjects() queue_id = 0 for queue in queues: queue_id = queue.get_id() # attempt to lock this queue # have to do this manually update = """UPDATE "%s" SET state = 'locked' where id = '%s' and state = 'pending'""" % (table, queue_id) sql.do_update(update) row_count = sql.get_row_count() if row_count == 1: break else: queue_id = 0 if queue_id: queue = Search.get_by_id(job_search_type, queue_id) return queue else: return None
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): if not my.login_name: my.login_name = my.kwargs.get('login'); print "Signing out %s" % my.login_name # invalidate the ticket security = Environment.get_security() ticket = security.get_ticket() if ticket == None: return # expire the ticket from pyasm.search import Sql, DbContainer sql = DbContainer.get("sthpw") ticket.set_value("expiry", sql.get_timestamp_now(), quoted=False) ticket.commit()
def get_display(self): widget = DivWdg() widget.add_style("text-align: center") search_type = self.get_current_sobject() project_code = search_type.get_value("database") if project_code == "{project}": # HACK: assumes database == project_code. Can't seem to get # around the {project} variable ... need to look at this # sometime project = Project.get() else: project = Project.get_by_code(project_code) if not project: widget.add( IconWdg("Exists", IconWdg.ERROR) ) widget.add( "Project does not exist") return widget table = search_type.get_table() if not table: return "" try: db_resource = project.get_project_db_resource() sql = DbContainer.get(db_resource) tables = sql.get_tables() has_table = table in tables except DatabaseException: has_table = False if has_table: widget.add( IconWdg("Exists", IconWdg.DOT_GREEN) ) else: widget.add( IconWdg("Does not Exist", IconWdg.DOT_RED) ) return widget
def init_cache(my): from pyasm.search import DatabaseImpl, DbContainer my.caches = {} data = {} for table in my.tables: column_data = DatabaseImpl.get().get_column_info(my.database, table) data[table] = column_data my.caches['data'] = data # get the order columns columns = {} sql = DbContainer.get(my.database) for table in my.tables: column_list = sql.get_columns(table) columns[table] = column_list my.caches = {} my.caches['data'] = data my.caches['columns'] = columns
def get_display(self): widget = DivWdg() widget.add_style("text-align: center") search_type = self.get_current_sobject() project_code = search_type.get_value("database") if project_code == "{project}": # HACK: assumes database == project_code. Can't seem to get # around the {project} variable ... need to look at this # sometime project = Project.get() else: project = Project.get_by_code(project_code) if not project: widget.add(IconWdg("Exists", IconWdg.ERROR)) widget.add("Project does not exist") return widget table = search_type.get_table() if not table: return "" try: db_resource = project.get_project_db_resource() sql = DbContainer.get(db_resource) tables = sql.get_tables() has_table = table in tables except DatabaseException: has_table = False if has_table: widget.add(IconWdg("Exists", IconWdg.DOT_GREEN)) else: widget.add(IconWdg("Does not Exist", IconWdg.DOT_RED)) return widget
def on_insert(my): '''Function that should be run on insert/update. It's already automatically called during insert. On update, the caller needs to call this explicitly. It checks the search type this pipeline is associated with and if there is no pipeline code column, then update it. it updates the process table also.''' my.update_process_table() search_type = my.get_value('search_type') # don't do anything for task table if search_type == 'sthpw/task': return columns = SearchType.get_columns(search_type) if not 'pipeline_code' in columns: # add the pipeline code column from pyasm.command import ColumnAddCmd cmd = ColumnAddCmd(search_type, "pipeline_code", "varchar") cmd.execute() if ProdSetting.get_value_by_key('autofill_pipeline_code') != 'false': # go through all of the sobjects and set all the empty ones # to the new pipeline search = Search(search_type) search.add_op("begin") search.add_filter("pipeline_code", "NULL", op='is', quoted=False) search.add_filter("pipeline_code", "") search.add_op("or") sobject_ids = search.get_sobject_ids() if sobject_ids: # this is much faster and memory efficient db_resource = SearchType.get_db_resource_by_search_type( search_type) sql = DbContainer.get(db_resource) tbl = search.get_table() sobject_ids = [str(x) for x in sobject_ids] pipeline_code = my.get_value("code") sql.do_update( '''UPDATE "%s" SET "pipeline_code" = '%s' WHERE id in (%s) ''' % (tbl, pipeline_code, ','.join(sobject_ids))) """
def execute(self): 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() data_type = self.get_data_type(self.search_type, self.attr_type) # if there is no type, then no column is created for widget_config if self.attr_type == "Date Range": column1 = "%s_start_date" % self.attr_name column2 = "%s_end_date" % self.attr_name self._add_column(column1, data_type) self._add_column(column2, data_type) elif type != "": self._add_column(self.attr_name, data_type) self.add_description("Added attribute '%s' of type '%s'" % (self.attr_name, self.attr_type) )
def run_sql(self, sql): ''' run an sql statement. self is an instance of the dynamically created <project_type>Upgrade class. If SqlException arise, it will record the error, and the user is advised to check if the error is a result of syntax error or the upgrade function is doing redundant work''' project = Project.get_by_code(self.project_code) db_resource = project.get_project_db_resource() db = DbContainer.get(db_resource) #if not self.quiet: # print sql try: db.do_update(sql, quiet=self.quiet) except SqlException, e: print "Error: ", e # TEST for Sqlite if str(e).startswith("duplicate column name:"): pass elif str(e).startswith("table") and str(e).endswith("already exists"): pass elif not self.quiet: print print "WARNING: Skipping due to SqlException..." print "Message: ", e print members = inspect.getmembers(self, predicate=inspect.ismethod) key = '%s|%s' %(self.project_code, self.upgrade_class) Container.append_seq(key, (self.upgrade_method, str(e))) """ for name, member in members: # there should only be 1 upgrade method if name.startswith('upgrade_v'): Container.append_seq(key, (self.upgrade_method, str(e))) break """ # to prevent sql error affecting query that follows the Upgrade #DbContainer.abort_thread_sql() DbContainer.release_thread_sql()
def import_bootstrap(): print "Importing bootstrap ..." vendor = "PostgreSQL" plugin_dir = Environment.get_plugin_dir() sys.path.insert(0, plugin_dir) impl = DatabaseImpl.get(vendor) impl.create_database("sthpw") upgrade_dir = Environment.get_upgrade_dir() for category in ['bootstrap', 'sthpw', 'config']: f = open("%s/%s/%s_schema.sql" % (upgrade_dir, vendor.lower(), category)) data = f.read() f.close() data = data.split(";") cmds = [] for cmd in data: lines = cmd.split("\n") lines2 = [] for line in lines: if line.startswith("--"): continue lines2.append(line) cmd = "\n".join(lines2) cmd = cmd.strip() if cmd == '': continue cmds.append(cmd) from pyasm.search import DbContainer sql = DbContainer.get("sthpw") for cmd in cmds: sql.do_update(cmd)
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 import_bootstrap(): print "Importing bootstrap ..." vendor = "PostgreSQL" plugin_dir = Environment.get_plugin_dir() sys.path.insert(0, plugin_dir) impl = DatabaseImpl.get(vendor) impl.create_database("sthpw") upgrade_dir = Environment.get_upgrade_dir() for category in ['bootstrap', 'sthpw', 'config']: f = open("%s/%s/%s_schema.sql" % (upgrade_dir, vendor.lower(), category) ) data = f.read() f.close() data = data.split(";") cmds = [] for cmd in data: lines = cmd.split("\n") lines2 = [] for line in lines: if line.startswith("--"): continue lines2.append(line) cmd = "\n".join(lines2) cmd = cmd.strip() if cmd == '': continue cmds.append(cmd) from pyasm.search import DbContainer, DbResource sql = DbContainer.get("sthpw") for cmd in cmds: sql.do_update(cmd)
def run_sql(my, sql): ''' run an sql statement. my is an instance of the dynamically created <project_type>Upgrade class. If SqlException arise, it will record the error, and the user is advised to check if the error is a result of syntax error or the upgrade function is doing redundant work''' project = Project.get_by_code(my.project_code) db_resource = project.get_project_db_resource() db = DbContainer.get(db_resource) #if not my.quiet: # print sql try: db.do_update(sql, quiet=my.quiet) except SqlException, e: print "Error: ", e # TEST for Sqlite if str(e).startswith("duplicate column name:"): pass elif str(e).startswith("table") and str(e).endswith("already exists"): pass elif not my.quiet: print print "WARNING: Skipping due to SqlException..." print "Message: ", e print members = inspect.getmembers(my, predicate=inspect.ismethod) key = '%s|%s' %(my.project_code, my.upgrade_class) Container.append_seq(key, (my.upgrade_method, str(e))) """ for name, member in members: # there should only be 1 upgrade method if name.startswith('upgrade_v'): Container.append_seq(key, (my.upgrade_method, str(e))) break """ # to prevent sql error affecting query that follows the Upgrade #DbContainer.abort_thread_sql() DbContainer.release_thread_sql()
def fix_work_hour_id(): db = DbContainer.get("sthpw") sql = ''' BEGIN TRANSACTION; CREATE TABLE t_backup ( id integer PRIMARY KEY AUTOINCREMENT, code character varying(256), project_code character varying(256), description text, category character varying(256), process character varying(256), "login" character varying(256), "day" timestamp without time zone, start_time timestamp without time zone, end_time timestamp without time zone, straight_time double precision, over_time double precision, search_type character varying(256), search_id integer, status character varying(256), task_code character varying(256), CONSTRAINT "work_hour_code_idx" UNIQUE (code) ); INSERT INTO t_backup SELECT id, code, project_code, description, category, process, "login", "day", start_time, end_time, straight_time, over_time, search_type, search_id, status, task_code FROM %(table)s; DROP TABLE %(table)s; ALTER TABLE t_backup RENAME TO %(table)s; COMMIT; ''' % { "table": "work_hour" } conn = db.conn conn.executescript(sql)
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 import_manifest(my, nodes): paths_read = [] for node in nodes: node_name = my.xml.get_node_name(node) if node_name == 'search_type': search_type = my.xml.get_attribute(node, 'code') # implicitly add the entry to the schema table. # Reset the cache every time to ensure that any updates to # the scehma are reflected here. schema = Schema.get(reset_cache=True) xml = schema.get_xml() schema_node = xml.get_node("schema/search_type[@name='%s']" % search_type) parent = xml.get_node("schema") if schema_node == None: schema_node = xml.create_element("search_type") xml.set_attribute(schema_node, "name", search_type) #parent = xml.get_parent(node) xml.append_child(parent, schema_node) schema.set_value('schema', xml.to_string() ) schema.commit() # TODO: connections? path = my.xml.get_attribute(node, "path") if not path: path = "%s.spt" % search_type.replace("/", "_") path = "%s/%s" % (my.plugin_dir, path) if path in paths_read: continue if my.verbose: print "Reading search_type: ", path # NOTE: priviledged knowledge of the order or return values jobs = my.import_data(path, commit=True) paths_read.append(path) if not jobs: continue search_type_obj = jobs[0] if len(jobs) == 1: # only the search type was defined table = None else: table = jobs[1] try: # check to see if the search type already exists search_type_chk = SearchType.get(search_type) if search_type_chk: if my.verbose: print 'WARNING: Search Type [%s] is already registered' % search_type_chk.get_value("search_type") else: search_type_obj.commit() except SearchException, e: if e.__str__().find('not registered') != -1: search_type_obj.commit() # check if table exists has_table = False if has_table: if my.verbose: print 'WARNING: Table [%s] already exists' elif table: #print table.get_statement() if table: database = table.get_database() table_name = table.get_table() TableUndo.log(search_type, database, table_name) elif node_name == 'sobject': path = my.xml.get_attribute(node, "path") search_type = my.xml.get_attribute(node, "search_type") seq_max = my.xml.get_attribute(node, "seq_max") try: if seq_max: seq_max = int(seq_max) except ValueError: seq_max = 0 if not path: if search_type: path = "%s.spt" % search_type.replace("/","_") if not path: raise TacticException("No path specified") path = "%s/%s" % (my.plugin_dir, path) if path in paths_read: continue unique = my.xml.get_attribute(node, "unique") if unique == 'true': unique = True else: unique = False if my.verbose: print "Reading: ", path # jobs doesn't matter for sobject node jobs = my.import_data(path, unique=unique) # compare sequence st_obj = SearchType.get(search_type) SearchType.sequence_nextval(search_type) cur_seq_id = SearchType.sequence_currval(search_type) sql = DbContainer.get("sthpw") if seq_max > 0 and seq_max > cur_seq_id: # TODO: SQL Server - Reseed the sequences instead of passing. if sql.get_database_type() == 'SQLServer': pass else: SearchType.sequence_setval(search_type, seq_max) else: cur_seq_id -= 1 # TODO: SQL Server - Reseed the sequences instead of passing. if sql.get_database_type() == 'SQLServer': pass else: # this is a db requirement if cur_seq_id > 0: SearchType.sequence_setval(search_type, cur_seq_id) paths_read.append(path)
class CreateProjectCmd(Command): def is_undoable(cls): return False is_undoable = classmethod(is_undoable) def get_title(my): return "Create Project" def get_args_keys(my): return { 'project_code': 'code of the new project', 'project_title': 'title of the new project', 'project_type': 'determines the type of project which specifies the initial schema and the naming conventions', #'copy_pipelines': 'flag to copy template site pipelines to project' } def check(my): project_code = my.kwargs.get('project_code') regexs = '^\d|\W' m = re.search(r'%s' % regexs, project_code) if m: if isinstance(project_code, unicode): project_code = project_code.encode('utf-8') else: project_code = unicode(project_code).encode('utf-8') raise TacticException( '<project_code> [%s] cannot contain special characters or start with a number.' % project_code) # check to see if this project already exists test_project = Project.get_by_code(project_code) if test_project: if test_project.get_value('s_status') == 'retired': raise TacticException( 'Project with code [%s] already exists but is retired.' % project_code) else: raise TacticException( 'Project with code [%s] already exists.' % project_code) return True def execute(my): project_code = my.kwargs.get('project_code') project_title = my.kwargs.get('project_title') project_type = my.kwargs.get('project_type') if not project_type: project_type = "simple" is_template = my.kwargs.get('is_template') project_theme = my.kwargs.get('project_theme') use_default_side_bar = my.kwargs.get('use_default_side_bar') if use_default_side_bar in [False, 'false']: use_default_side_bar = False else: use_default_side_bar = True assert project_code assert project_type if project_type: # check to see if it exists search = Search("sthpw/project_type") search.add_filter("code", project_type) project_type_sobj = search.get_sobject() if not project_type_sobj: # just create a default one in this case if it is named # after the project code if not is_template and project_type == project_code: project_type = 'default' # create a new project type search = Search("sthpw/project_type") search.add_filter("code", project_type) project_type_sobj = search.get_sobject() if not project_type_sobj: project_type_sobj = SearchType.create("sthpw/project_type") project_type_sobj.set_value("code", project_type) project_type_sobj.set_value("type", "simple") project_type_sobj.commit() # set the current project to Admin Project.set_project("admin") # create a new project sobject project = SearchType.create("sthpw/project") project.set_value("code", project_code) project.set_value("title", project_title) project.set_value("type", project_type) # set the update of the database to current (this is obsolete) #project.set_value("last_db_update", "now()") project.set_value("last_version_update", "2.5.0.v01") if is_template in ['true', True, 'True']: project.set_value("is_template", True) else: project.set_value("is_template", False) if project_type != "default": category = Common.get_display_title(project_type) project.set_value("category", category) project.commit() # if there is an image, check it in upload_path = my.kwargs.get("project_image_path") if upload_path: if not os.path.exists(upload_path): raise TacticException( "Cannot find upload image for project [%s]" % upload_path) file_type = 'main' file_paths = [upload_path] file_types = [file_type] source_paths = [upload_path] from pyasm.biz import IconCreator if os.path.isfile(upload_path): icon_creator = IconCreator(upload_path) icon_creator.execute() web_path = icon_creator.get_web_path() icon_path = icon_creator.get_icon_path() if web_path: file_paths = [upload_path, web_path, icon_path] file_types = [file_type, 'web', 'icon'] from pyasm.checkin import FileCheckin checkin = FileCheckin(project, context='icon', file_paths=file_paths, file_types=file_types) checkin.execute() # find project's base_type base_type = project.get_base_type() if not base_type and project_type == 'unittest': base_type = 'unittest' elif not base_type: base_type = 'simple' # get the database for this project db_resource = project.get_project_db_resource() database = db_resource.get_database_impl() #database = DatabaseImpl.get() database_type = database.get_database_type() if database_type == 'Oracle': raise TacticException( "Creation of project is not supported. Please create manually") # creating project database print "Creating database '%s' ..." % project_code try: # create the datbase database.create_database(db_resource) except Exception, e: print str(e) print "WARNING: Error creating database [%s]" % project_code # import the appropriate schema with config first database.import_schema(db_resource, base_type) my.create_schema(project_code) # before we upgrade, we have to commit the transaction # This is because upgrade actually run as separate processes # so if not commit has been made, the tables from importing the # schema will not have existed yet DbContainer.commit_thread_sql() my.upgrade() # import the appropriate data database.import_default_data(db_resource, base_type) # import default links if use_default_side_bar: my.import_default_side_bar() # create specified stypes my.create_search_types() # create theme if project_theme: my.create_theme(project_theme) # set as main project is_main_project = my.kwargs.get("is_main_project") if is_main_project in [True, 'true', 'on']: Config.set_value("install", "default_project", project_code) Config.save_config() Config.reload_config() # initiate the DbContainer DbContainer.get('sthpw') my.info['result'] = "Finished creating project [%s]." % project_code print "Done."
def execute(my): from pyasm.search import DbContainer project_code = my.kwargs.get("project_code") if project_code: project = Project.get_by_code(project_code) else: search_key = my.kwargs.get("search_key") project = Search.get_by_search_key(search_key) project_code = project.get_code() assert project_code assert project # dump the database # remove all dependencies the sthpw database related_types = my.kwargs.get("related_types") if related_types: for related_type in related_types: search = Search(related_type) if related_type == "sthpw/schema": search.add_filter("code", project_code) else: search.add_filter("project_code", project_code) count = search.get_count() sobjects = search.get_sobjects() for sobject in sobjects: if related_type == 'sthpw/snapshot': my.delete_snapshot(sobject) else: sobject.delete() sthpw_project = Project.get_by_code('sthpw') # delete the database sthpw_db_resource = sthpw_project.get_project_db_resource() db_resource = project.get_project_db_resource() impl = sthpw_db_resource.get_database_impl() deleted_impl = db_resource.get_database_impl() if not impl.database_exists(db_resource): # remove the project entry project.delete() return # close this connection to the project to be deleted sql = DbContainer.get(db_resource) sql.close() if sql.get_database_type() == 'Sqlite': DbContainer.release_thread_sql() result = impl.drop_database(db_resource) # this is just extra check if result and "failed" in result: raise TacticException(result) Container.put("Sql:database_exists:%s" % db_resource.get_key(), None) sql = DbContainer.get(db_resource, connect=True) if sql: try: if sql.get_database_type() != 'Sqlite': if sql.get_connection() and sql.connect(): raise TacticException( "Database [%s] still exists. There could still be connections to it." % project_code) except SqlException, e: pass
def execute(self): from pyasm.search import DbContainer from pyasm.security import Security delete_group = "admin" security = Environment.get_security() if not security.is_in_group(delete_group): raise Exception("Only users in [%s] can delete projects" % delete_group) project_code = self.kwargs.get("project_code") if project_code: project = Project.get_by_code(project_code) else: search_key = self.kwargs.get("search_key") project = Search.get_by_search_key(search_key) project_code = project.get_code() assert project_code assert project # dump the database # remove all dependencies the sthpw database related_types = self.kwargs.get("related_types") if related_types: for related_type in related_types: search = Search(related_type) if related_type == "sthpw/schema": search.add_filter("code", project_code) else: search.add_filter("project_code", project_code) count = search.get_count() sobjects = search.get_sobjects() for sobject in sobjects: if related_type == 'sthpw/snapshot': self.delete_snapshot(sobject) else: sobject.delete() sthpw_project = Project.get_by_code('sthpw') # delete the database sthpw_db_resource = sthpw_project.get_project_db_resource() db_resource = project.get_project_db_resource() impl = sthpw_db_resource.get_database_impl() deleted_impl = db_resource.get_database_impl() if not impl.database_exists(db_resource): # remove the project entry project.delete() return # close this connection to the project to be deleted sql = DbContainer.get(db_resource) sql.close() if sql.get_database_type() == 'Sqlite': DbContainer.release_thread_sql() result = impl.drop_database(db_resource) # this is just extra check if result and "failed" in result: raise TacticException(result) Container.put("Sql:database_exists:%s" % db_resource.get_key(), None) sql = DbContainer.get(db_resource, connect=True) if sql: try: if sql.get_database_type() != 'Sqlite': if sql.get_connection() and sql.connect(): raise TacticException( "Database [%s] still exists. There could still be connections to it." % project_code) except SqlException as e: pass # remove the project entry project.delete(triggers=False) schema = Schema.get_by_code(project_code) if schema: schema.delete() # Delete project specific login group and login in group entries expr = "@SOBJECT(sthpw/login_group['project_code','%s'])" % project_code expr2 = "@SOBJECT(sthpw/login_group['project_code','%s'].sthpw/login_in_group)" % project_code sobjs = Search.eval(expr2) for sobj in sobjs: sobj.delete() sobjs = Search.eval(expr) for sobj in sobjs: sobj.delete() return
def get_display(self): from pyasm.biz import Project security = Environment.get_security() if not security.check_access("builtin", "side_bar_schema", "allow", default="deny"): return DivWdg() section_div = LabeledHidableWdg(label="Schema Views") section_div.set_attr('spt_class_name', Common.get_full_class_name(self) ) palette = Palette.get() color = palette.color("background3") project_div = RoundedCornerDivWdg(hex_color_code=color,corner_size="10") project_div.set_dimensions( width_str='175px', content_height_str='100px' ) project = Project.get() project_code = project.get_code() project_type = project.get_type() div = DivWdg() section_div.add(project_div) project_div.add(div) # get project type schema schema = Schema.get_by_code(project_code) if schema: div.add( self.get_schema_wdg(schema) ) #if not project_type: # raise SetupException("Project type not found for this [%s]" %project_code) if project_type: schema = Schema.get_predefined_schema(project_type) if schema: div.add( self.get_schema_wdg(schema) ) schema = Schema.get_predefined_schema('config') div.add( self.get_schema_wdg(schema) ) schema = Schema.get_admin_schema() div.add( self.get_schema_wdg(schema) ) return section_div # create a fake schema project = Project.get() db_name = project.get_database() sql = DbContainer.get(db_name) tables = sql.get_tables() tables.sort() tables_str = "\n".join( ['<search_type name="%s"/>'%x for x in tables] ) # look at all of the search objects for mapped tables search = Search("sthpw/search_object") #search.add_where('''"namespace" = 'MMS' or namespace = '{project}' ''') search.add_filter("namespace", 'MMS') search.add_filter("namespace", '{project}') search.add_where("or") search_types = search.get_sobjects() schema_xml = ''' <schema> %s </schema> ''' % tables_str schema = SearchType.create("sthpw/schema") schema.set_value("code", "table") schema.set_value("schema", schema_xml) #div.add( self.get_schema_wdg(schema) ) return section_div
def get_database_type(self): project = Project.get_by_code(self.project_code) db_resource = project.get_project_db_resource() db = DbContainer.get(db_resource) return db.get_database_type()
def __init__(self, port=''): # It is possible on startup that the database is not running. from pyasm.common import Environment from pyasm.search import DbContainer, DatabaseException, Sql plugin_dir = Environment.get_plugin_dir() sys.path.insert(0, plugin_dir) try: sql = DbContainer.get("sthpw") if sql.get_database_type() != "MongoDb": # before batch, clean up the ticket with a NULL code if os.getenv('TACTIC_MODE') != 'production': sql.do_update('DELETE from "ticket" where "code" is NULL') else: start_port = Config.get_value("services", "start_port") if start_port: start_port = int(start_port) else: start_port = 8081 if port and int(port) == start_port: sql.do_update( 'DELETE from "ticket" where "code" is NULL') except DatabaseException as e: # TODO: need to work on this print("ERROR: could not connect to [sthpw] database") #os.environ["TACTIC_CONFIG_PATH"] = Config.get_default_config_path() #Sql.set_default_vendor("Sqlite") Config.set_tmp_config() Config.reload_config() # try connecting again try: sql = DbContainer.get("sthpw") except: print "Could not connect to the database." raise # is it CherryPyStartup's responsibility to start batch? from pyasm.security import Batch Batch() self.site_dir = os.getenv("TACTIC_SITE_DIR") self.install_dir = os.getenv("TACTIC_INSTALL_DIR") # set up a simple environment. May need a more complex one later self.env = Environment() self.setup_env() self.config = self.setup_sites() self.init_only = False cherrypy.startup = self # this initializes the web. # - sets up virtual implied tiggers from web_init import WebInit WebInit().execute() # Windows should handle fine #start up the caching system if it's not windows cache_mode = Config.get_value("install", "cache_mode") if not cache_mode: cache_mode = 'complete' if os.name == 'nt': cache_mode = 'basic' from cache_startup import CacheStartup cmd = CacheStartup(mode=cache_mode) cmd.execute() cmd.init_scheduler() # DEPRECATED (but keeping it around" """ # start up the queue system ... if Config.get_value("sync", "enabled") == "true": # start up the sync system ... print("Starting Transaction Sync ...") from tactic.command import TransactionQueueManager TransactionQueueManager.start() # start up the sync system ... print("Starting Watch Folder Service ...") from tactic.command import WatchServerFolderTask WatchServerFolderTask.start() """ # start up scheduled triggers #from tactic.command import ScheduledTriggerMonitor #ScheduledTriggerMonitor.start() #from pyasm.web import Translation #Translation.install() # close all the threads in this startup thread from pyasm.search import DbContainer DbContainer.close_thread_sql() version = Environment.get_release_version() print("") print("Starting TACTIC v%s ..." % version) print("")
def execute(self): project_code = self.kwargs.get('project_code') project_title = self.kwargs.get('project_title') project_type = self.kwargs.get('project_type') project_description = self.kwargs.get("description") if not project_type: project_type = "simple" is_template = self.kwargs.get('is_template') project_theme = self.kwargs.get('project_theme') use_default_side_bar = self.kwargs.get('use_default_side_bar') if use_default_side_bar in [False, 'false']: use_default_side_bar = False else: use_default_side_bar = True assert project_code assert project_type if project_type: # check to see if it exists search = Search("sthpw/project_type") search.add_filter("code", project_type) project_type_sobj = search.get_sobject() if not project_type_sobj: # just create a default one in this case if it is named # after the project code if not is_template and project_type == project_code: project_type = 'default' # create a new project type search = Search("sthpw/project_type") search.add_filter("code", project_type) project_type_sobj = search.get_sobject() if not project_type_sobj: project_type_sobj = SearchType.create("sthpw/project_type") project_type_sobj.set_value("code", project_type) project_type_sobj.set_value("type", "simple") project_type_sobj.commit() # set the current project to Admin Project.set_project("admin") # create a new project sobject project = SearchType.create("sthpw/project") project.set_value("code", project_code) project.set_value("title", project_title) project.set_value("type", project_type) if project_description: project.set_value("description", project_description) # set the update of the database to current (this is obsolete) #project.set_value("last_db_update", "now()") project.set_value("last_version_update", "2.5.0.v01") if is_template in ['true', True, 'True']: project.set_value("is_template", True) else: project.set_value("is_template", False) if project_type != "default": category = Common.get_display_title(project_type) project.set_value("category", category) project.commit() # if there is an image, check it in upload_path = self.kwargs.get("project_image_path") if upload_path: if not os.path.exists(upload_path): raise TacticException( "Cannot find upload image for project [%s]" % upload_path) file_type = 'main' file_paths = [upload_path] file_types = [file_type] source_paths = [upload_path] from pyasm.biz import IconCreator if os.path.isfile(upload_path): icon_creator = IconCreator(upload_path) icon_creator.execute() web_path = icon_creator.get_web_path() icon_path = icon_creator.get_icon_path() if web_path: file_paths = [upload_path, web_path, icon_path] file_types = [file_type, 'web', 'icon'] from pyasm.checkin import FileCheckin checkin = FileCheckin(project, context='icon', file_paths=file_paths, file_types=file_types) checkin.execute() # find project's base_type base_type = project.get_base_type() if not base_type and project_type == 'unittest': base_type = 'unittest' elif not base_type: base_type = 'simple' # get the database for this project db_resource = project.get_project_db_resource() database = db_resource.get_database_impl() #database = DatabaseImpl.get() database_type = database.get_database_type() if database_type == 'Oracle': raise TacticException( "Creation of project is not supported. Please create manually") # creating project database print "Creating database '%s' ..." % project_code try: # create the datbase database.create_database(db_resource) except Exception as e: print str(e) print "WARNING: Error creating database [%s]" % project_code # import the appropriate schema with config first database.import_schema(db_resource, base_type) self.create_schema(project_code) # before we upgrade, we have to commit the transaction # This is because upgrade actually run as separate processes # so if not commit has been made, the tables from importing the # schema will not have existed yet DbContainer.commit_thread_sql() self.upgrade() # import the appropriate data database.import_default_data(db_resource, base_type) # import default links if use_default_side_bar: self.import_default_side_bar() # create specified stypes self.create_search_types() # create theme if project_theme: self.create_theme(project_theme) # set as main project is_main_project = self.kwargs.get("is_main_project") if is_main_project in [True, 'true', 'on']: Config.set_value("install", "default_project", project_code) Config.save_config() Config.reload_config() # initiate the DbContainer DbContainer.get('sthpw') self.info['result'] = "Finished creating project [%s]." % project_code print "Done."
def get_sql(my): db_resource = my.get_project_db_resource() from pyasm.search import DbContainer sql = DbContainer.get(db_resource) return sql
def get_sobjects(my): columns = [ 'product_name', 'sample_size', 'labor_average', 'material_average_cost', 'average_time_taken' ] sql = ''' SELECT "product_name", "number_of_each_product_type" "sample_size", "average_time_taken" "labor_average", sum("material_cost_per_item") "material_average_cost", sum("vendor_cost_per_item") "vender_average_cost" FROM ( SELECT "product_name", "subtask"."id" "subtask_id", "number_of_each_product_type", "average_time_taken", "product_quantity", --"total_material_cost", "total_vendor_costs", "total_material_cost" / "product_quantity" "material_cost_per_item", "total_vendor_costs" / "product_quantity" "vendor_cost_per_item" FROM "product_type" LEFT OUTER JOIN "product_type_aggrgt" ON "product_type"."id" = "product_type_aggrgt"."product_type_id" LEFT OUTER JOIN "subtask_product" ON "product_type"."id" = "subtask_product"."product_type_id" LEFT OUTER JOIN "subtask" ON "subtask"."id" = "subtask_product"."subtask_id" LEFT OUTER JOIN "subtask_material_aggrgt" ON "subtask"."id" = "subtask_material_aggrgt"."subtask_id" LEFT OUTER JOIN "subtask_vndrcost_aggrgt" ON "subtask"."id" = "subtask_vndrcost_aggrgt"."subtask_id" ) X group by X."product_name", X."number_of_each_product_type", X."average_time_taken", X."product_quantity", "subtask_id" order by X."product_name" ''' db = DbContainer.get("MMS") from pyasm.search import Select, Search search_type = my.kwargs.get('search_type') select = Select() select.set_statement(sql) statement = select.get_statement() search = Search(search_type) search.select = select sobjects = search.get_sobjects() for sobject in sobjects: sobject.set_value("id", -1) return sobjects
def get_tables_wdg(self): div = DivWdg() div.set_name("Tables") div.add( "In order to fully register a database, you must bind it to a TACTIC project" ) div.add("<br/>") project_code = "mongodb" database = "test_database" db_resource = DbResource(server='localhost', vendor='MongoDb', database=database) try: connect = DbContainer.get(db_resource) except Exception as e: div.add("Could not connect") div.add_style("padding: 30px") div.add("<br/>" * 2) div.add(str(e)) return div # Bind project to this resource database_text = TextWdg("database") div.add("Database: ") div.add(database_text) div.add("<br/>" * 2) project_text = TextWdg("project") div.add("Project Code: ") div.add(project_text) div.add("<br/>") div.add("<hr/>") # connect and introspect the tables in this database tables = connect.get_tables() table = Table() div.add(table) table.set_max_width() for table_name in tables: table.add_row() search_type = "table/%s?project=%s" % (table_name, project_code) td = table.add_cell() icon = IconWdg("View Table", IconWdg.FOLDER_GO) td.add(icon) icon.add_behavior({ 'type': 'click_up', 'search_type': search_type, 'cbjs_action': ''' var class_name = 'tactic.ui.panel.ViewPanelWdg'; var kwargs = { search_type: bvr.search_type } spt.panel.load_popup("table", class_name, kwargs); ''' }) td = table.add_cell() td.add(table_name) td = table.add_cell() search = Search(search_type) count = search.get_count() td.add(" %s item/s" % count) columns = search.get_columns() td = table.add_cell() td.add(columns) # search_type td = table.add_cell() text = TextWdg("search_type") td.add(text) new_search_type = "%s/%s" % (project_code, table_name) text.set_value(new_search_type) register_div = DivWdg() div.add(register_div) register_div.add_style("padding: 20px") button = ActionButtonWdg(title="Register") register_div.add(button) return div
def get_database_impl(): project = Project.get() db_resource = project.get_db_resource() sql = DbContainer.get(db_resource) return sql.get_database_impl()
cmd.execute() if __name__ == '__main__': batch = Batch() msg = "\nThis script will delete the existing entries in your sobject_list table used for storing keywords for searching purpose " \ "and renew them with the updated entries for all projects" answer = raw_input(" %s. Continue (y/n): " %msg) if answer == "y": sthpw_sql = DbContainer.get("sthpw") statement = 'DELETE from sobject_list;' sthpw_sql.do_update(statement) print print "Deleting of existing entries finished.\n" elif answer == 'n': sys.exit(0) else: print "Anwer y or n. Exit..." sys.exit(0) command = PopulateSObjectListCmd() Command.execute_cmd(command) print print "Finished updating sobject_list table."
def run_sql(self, sql): from pyasm.search import DatabaseImpl, DbContainer db = DbContainer.get("sthpw") db.do_update(sql)
def dump_to_tactic(my, path=None, mode='sql'): from pyasm.search import SearchType, Sql, DbContainer search_type_obj = SearchType.get(my.search_type) database = search_type_obj.get_database() table = search_type_obj.get_table() db_resource = SearchType.get_db_resource_by_search_type(my.search_type) sql = DbContainer.get(db_resource) exists = sql.table_exists(table) if not exists: return info = sql.get_column_info(table) impl = sql.get_database_impl() columns = info.keys() columns.sort() # if the table does not exist, there are no columns if not columns: return if path: import os f = codecs.getwriter('utf8')(open(path, 'ab')) else: import sys f = sys.stdout if not my.delimiter: my.delimiter = "--" my.end_delimiter = my.delimiter f.write( "%s\n" % my.delimiter ) if mode == 'sobject': f.write("table = CreateTable('%s')\n" % my.search_type) else: f.write("table = CreateTable()\n") f.write("table.set_table('%s')\n" % table) # Hard code this - all search types have id as the primary key at the # moment primary_key_col = 'id' for column in columns: if column in my.ignore_columns: continue col_info = info[column] #print col_info space = " "*(25-len(column)) data_type = col_info['data_type'] is_nullable = col_info['nullable'] if column == "id": # A dump will output a database independent serial #data_type = impl.get_serial() <--- Database depenedent data_type = 'serial' f.write("table.add('%s',%s'%s', primary_key=True)\n" % (column, space, data_type) ) continue elif data_type in ['varchar','char','nvarchar']: size = col_info.get('size') if size == -1: size = 'max' if not size: size = 256 if is_nullable: f.write("table.add('%s',%s'%s', length='%s' )\n" % (column, space, data_type, size)) else: f.write("table.add('%s',%s'%s', length='%s', not_null=True )\n" % (column, space, data_type, size)) continue if is_nullable: f.write("table.add('%s',%s'%s' )\n" % (column, space, data_type)) else: f.write("table.add('%s',%s'%s', not_null=True )\n" % (column, space, data_type)) # add the constraints constraints = impl.get_constraints(db_resource, table) for constraint in constraints: name = constraint.get("name") columns = constraint.get("columns") mode = constraint.get("mode") if not name: name = "%s_%s_idx" % (name, "_".join(columns)) f.write('''table.add_constraint(%s, mode="%s")\n''' % (columns, mode)) #def add_constraint(my, columns, mode="UNIQUE"): #f.write("table.set_primary_key('id')\n") # Disable commit for now #if mode == 'sobject': # f.write("table.commit()\n") f.write( "%s\n" % my.end_delimiter ) f.write("\n")
def execute(self): database = "sthpw" sql = DbContainer.get(database) value_array = sql.do_query( "select code, cc from (select code, count(code) as cc from file group by code order by cc desc) as X where cc > 1;" ) #value_array = sql.do_query("select code, cc from (select code, count(code) as cc from file group by code order by cc desc) as X;") print "found [%s] pairs" % len(value_array) for count, value_list in enumerate(value_array): if count >= BATCH: break # get the file object file_code = value_list[0] search = Search("sthpw/file") search.add_filter("code", file_code) files = search.get_sobjects() #if len(files) == 1: # continue for file in files: project_code = file.get_value("project_code") if not project_code: print "WARNING: file [%s] has no project_code" % file_code continue project = Project.get_by_code(project_code) initials = project.get_initials() id = file.get_id() new_file_code = "%s%s" % (id, initials) if file_code == new_file_code: continue print "-" * 20 print "switching: ", file_code, "to", new_file_code snapshot_code = file.get_value("snapshot_code") snapshot = Snapshot.get_by_code(snapshot_code) assert snapshot snapshot_xml = snapshot.get_xml_value("snapshot") print snapshot_xml.to_string() node = snapshot_xml.get_node("snapshot/file[@file_code='%s']" % file_code) Xml.set_attribute(node, "file_code", new_file_code) print snapshot_xml.to_string() assert node # set the file_code file.set_value("code", new_file_code) file.commit() # set the snapshot snapshot.set_value("snapshot", snapshot_xml.to_string()) snapshot.commit()