def update_by_id(cls, h_rg_id, h_rg_diff): def query(): q_params = h_rg_diff q_params.update({ 'h_rg_id': h_rg_id, }) set_stmt = [] if q_params.get('cfg'): q_params['cfg'] = json.dumps(q_params['cfg']) set_stmt.append('cfg = :cfg') if q_params.get('is_enabled'): set_stmt.append('is_enabled = :is_enabled') q_stmt = ''' UPDATE "hook_registration" SET {} WHERE id = :h_rg_id RETURNING id'''.format(',\n'.join(set_stmt)) if not set_stmt: # nothing to update raise AssertionError('Nothing to update') return cls.rdbms_call(q_stmt, q_params) try: [r for r in query()].pop()[0] except exc.IntegrityError as e: raise StorageError('Some kind of IntegrityError') except IndexError: raise StorageError('no such hook_registration with id:{}'.format( h_rg_diff['h_rg_id'])) return cls.get_single(hook_registration_id=h_rg_id)
def insert(cls, proj): q_params = proj.as_dict() def query(): return cls.rdbms_call( ''' INSERT INTO project (name, descr, provider, lead ) VALUES (:name, :descr, :provider, :lead ) RETURNING id''', q_params) try: pk_id = [r for r in query()].pop()[0] except exc.IntegrityError as e: dupl_err = 'proj with name *{}* allready exists' if 'duplicate key' in str(e) and 'project_name_key' in str(e): raise StorageError(dupl_err.format(q_params.get('name'))) raise StorageError('Some kind of IntegrityError') return pk_id
def insert(cls, chart): kw = chart.as_dict() for filed in cls.json_fileds: kw.update({filed: json.dumps(kw[filed])}) def query(): return cls.rdbms_call( ''' INSERT INTO chart (test_id, ammo_tag, version, doc) VALUES (:test_id, :ammo_tag, :version, :doc) RETURNING test_id, ammo_tag''', kw) try: pk_id = [r for r in query()].pop()[0] except exc.IntegrityError as e: if 'violates unique constraint "chart_pkey"' in str(e): msg = [ 'chart entrie with such *test_id* and *ammo_tag*', 'allready exists.' ] raise StorageError(' '.join(msg)) raise StorageError('Some kind of IntegrityError') return pk_id
def insert(cls, user): q_params = user.as_dict() def query(): return cls.rdbms_call( ''' INSERT INTO "user" (login, first_name, last_name, email, is_staff, is_superuser, is_robot) VALUES (:login, :first_name, :last_name, :email, :is_staff, :is_superuser, :is_robot) RETURNING login''', q_params) try: login = [r for r in query()].pop()[0] except exc.IntegrityError as e: dupl_err = 'user with login {} allready exists' if 'already exists' in str(e): raise StorageError(dupl_err.format(q_params.get('login'))) raise StorageError('Some kind of IntegrityError') return login
def insert(cls, case): kw = case.as_dict() for filed in cls.json_fileds: kw.update({filed: json.dumps(kw[filed])}) def query(): return cls.rdbms_call(''' INSERT INTO "case" (name, descr, oracle, notification) VALUES (:name, :descr, :oracle, :notification) RETURNING id''', kw) try: pk_id = [r for r in query()].pop()[0] except exc.IntegrityError as e: if 'duplicate key' in str(e) and 'case_name_key' in str(e): raise StorageError('*Case* with such uniq name allready exists:{}'.format( kw.get('name'))) raise StorageError('Some kind of IntegrityError') return pk_id
def update_by_id(cls, case_id, case_diff): for filed in cls.json_fileds: if (filed in case_diff.keys()) and \ (not isinstance(case_diff[filed], basestring)): case_diff.update({filed: json.dumps(case_diff[filed])}) def query(): q_params = case_diff q_params.update({ 'case_id': case_id, }) top = '''UPDATE "case" SET ''' bottom = ''' WHERE id = :case_id RETURNING id''' update = cls.cmpl_update_sql(case_diff) return cls.rdbms_call(''.join([top, update, bottom]), q_params) try: pk_id = [r for r in query()].pop()[0] except exc.IntegrityError as e: if 'violates foreign key constraint "case_root_test_id_fkey"' in str(e): raise StorageError( 'unknown *root_test_id* value:{}'.format(case_diff.get('root_test_id'))) raise StorageError('Some kind of IntegrityError') return cls.get_by_id(pk_id)
def insert(cls, line): kw = LineAdaptor.to_dict(line) kw['dc_name'] = kw['dc']['name'] pp(kw) def query(): return cls.rdbms_call(''' INSERT INTO line ( id, name, dc_id ) VALUES ( :id, :name, (SELECT id FROM dc WHERE name = :dc_name) ) returning id''', kw) err_duplicate = 'line:{} allready exists'.format(kw.get('name')) try: pk_id = [r for r in query()].pop()[0] except exc.IntegrityError as e: if 'unique constraint "line_pkey"' in str(e): raise StorageError(err_duplicate) raise StorageError('Some kind of IntegrityError') return pk_id
def insert(cls, notifcn): kw = NotifcnAdaptor.to_dict(notifcn) for filed in cls.json_fileds: kw.update({filed: json.dumps(kw[filed])}) def query(): return cls.rdbms_call( ''' INSERT INTO "case_user_cc" (case_id, user_id, cfg) VALUES ( (SELECT id FROM "case" WHERE name = :case_name), (SELECT id FROM "user" WHERE login = :user_login), :cfg) RETURNING *''', kw) try: rv = query() except exc.IntegrityError as e: if 'already exists' in str(e): raise StorageError('Entrie allready exists') elif 'null value in column "user_id"' in str(e): raise StorageError('unknown *user_login* value:{}'.format( kw.get('user_login'))) raise StorageError('Some kind of IntegrityError') n = dict(zip(rv.keys(), rv.fetchall().pop())) n.update({ 'case_name': kw.get('case_name'), 'user_login': kw.get('user_login'), }) return NotifcnBuilder.from_row(**n)
def update_by_id(cls, ammo_id, ammo_diff): def query(): q_params = ammo_diff q_params.update({ 'ammo_id': ammo_id, }) set_stmt = [] if q_params.get('path'): set_stmt.append('path = :path') q_stmt = ''' UPDATE "ammo" SET {} WHERE id = :ammo_id RETURNING id'''.format(',\n'.join(set_stmt)) if not set_stmt: # nothing to update raise AssertionError('Nothing to update') return cls.rdbms_call(q_stmt, q_params) try: [r for r in query()].pop()[0] except exc.IntegrityError as e: if 'null value in column "t_status_id"' in str(e): raise StorageError( "unknown *status* value:{}".format(ammo_diff['status'])) raise StorageError('Some kind of IntegrityError') except IndexError: raise StorageError( 'no such test with id:{}'.format(ammo_diff['test_id'])) return cls.get_single(ammo_id=ammo_id)
def update_by_id(cls, test_id, test_diff): def query(): q_params = test_diff q_params.update({ 'test_id': test_id, }) set_stmt = [] if q_params.get('status'): set_stmt.append( 't_status_id=(SELECT id FROM t_status WHERE name = :status)' ) if 'finished_at' in q_params: set_stmt.append('finished_at = :finished_at') q_params['finished_at'] = dateutil.parser.parse( q_params['finished_at']) if 'started_at' in q_params: set_stmt.append('started_at = :started_at') q_params['started_at'] = dateutil.parser.parse( q_params['started_at']) if 'resolution' in q_params: set_stmt.append('resolution = :resolution') if 'lll' in q_params: if q_params['lll'].get('n'): q_params['lll_id'] = q_params['lunapark']['n'] set_stmt.append('lll_id = :lunapark_id') q_params['lll'] = json.dumps(q_params['lunapark']) set_stmt.append('lll = :lunapark') q_stmt = ''' UPDATE "test" SET {} WHERE id = :test_id RETURNING id'''.format(',\n'.join(set_stmt)) if not set_stmt: # nothing to update raise AssertionError('Nothing to update') return cls.rdbms_call(q_stmt, q_params) try: #pk_id = [r for r in query()].pop()[0] query() except exc.IntegrityError as e: if 'null value in column "t_status_id"' in str(e): raise StorageError("unknown *status* value:{}".format( test_diff['status'])) raise StorageError('Some kind of IntegrityError') except IndexError: raise StorageError('no such test with id:{}'.format( test_diff['test_id'])) return cls.get_by_id(test_id=test_id)
def get_many(cls, **kw): pagination_part = '\nORDER BY id DESC\nLIMIT :limit OFFSET :offset' param_per_page = kw.get('per_page') if param_per_page and (param_per_page <= cls.per_page_max): per_page = param_per_page else: per_page = cls.per_page_default page_num = kw.get('page') # page number starts from 1, page 0 and 1 mean the same - # first slice from data set. if page_num and isinstance(page_num, int) and (page_num >= 2): offset = (page_num - 1) * per_page next_page = page_num + 1 prev_page = page_num - 1 else: offset = 0 next_page = 2 prev_page = None query_params = { 'limit': per_page, 'offset': offset, } cond_part, q_params_up = SQLBuilder(**kw).cmpl_query() query_params.update(q_params_up) try: rv = cls.rdbms_call( ''.join([cls.select_part, cond_part, pagination_part]), query_params) rows = rv.fetchall() except exc.IntegrityError: raise StorageError('Some kind of IntegrityError') except exc.DataError as e: if 'invalid input syntax for type inet' in str(e): raise ValueError('Wrong *ip_addr* param type') raise StorageError('One of params malformed or has a wrong type') if len(rows) == 0: return None, None, None, None elif len(rows) < per_page: # last chunk of data next_page = None def create_host(row): h_kw = dict(zip(rv.keys(), row)) return HostBuilder.from_row(**h_kw) return map(create_host, rows), per_page, next_page, prev_page
def insert(cls, host): query_params = HostAdaptor.to_dict(host) try: rv = cls.rdbms_call(' '.join([cls.insert_part, 'RETURNING id']), query_params) pk_id = [r for r in rv].pop()[0] except exc.IntegrityError as e: if 'violates unique constraint "server_fqdn_key"' in str(e): raise StorageError( 'server with such *fqdn* allready exists:{}'.format( query_params.get('fqdn'))) raise StorageError('Some kind of IntegrityError') return pk_id
def insert(cls, issue): q_params = issue.as_dict() def query(): return cls.rdbms_call(''' INSERT INTO issue (name, title, descr, reporter, assignee, closed, provider, project_id ) VALUES (:name, :title, :descr, (SELECT id FROM "user" WHERE login = :reporter), (SELECT id FROM "user" WHERE login = :assignee), :closed, :provider, (SELECT id FROM "project" WHERE name = :project) ) RETURNING id''', q_params) try: pk_id = [r for r in query()].pop()[0] except exc.IntegrityError as e: err_msg = 'unknown *{}* value:{}' dupl_err = 'issue with name {} allready exists' if 'value in column "reporter"' in str(e): raise StorageError( err_msg.format('reporter', q_params.get('reporter')), missing_resource_type='user', missing_resource_value=q_params.get('reporter'),) if 'value in column "assignee"' in str(e): raise StorageError( err_msg.format('assignee', q_params.get('assignee')), missing_resource_type='user', missing_resource_value=q_params.get('assignee'),) if 'duplicate key' in str(e) and 'issue_name_key' in str(e): raise StorageError( dupl_err.format(q_params.get('name'))) if 'null value in column "project_id"' in str(e): raise StorageError( err_msg.format('project_id', q_params.get('project')), missing_resource_type='proj', missing_resource_value=q_params.get('project'),) raise StorageError('Some kind of IntegrityError') return pk_id
def update(cls, case_name, user_login, cfg): q_params = { 'case_name': case_name, 'user_login': user_login, 'cfg': json.dumps(cfg) } def query(): q_stmt = '''UPDATE "case_user_cc" SET cfg=:cfg WHERE user_id = (SELECT id FROM "user" WHERE login = :user_login) AND case_id = (SELECT id FROM "case" WHERE name = :case_name) RETURNING cfg''' return cls.rdbms_call(q_stmt, q_params) try: cfg = [r for r in query()].pop()[0] except exc.IntegrityError: raise StorageError('Some kind of IntegrityError') n = { 'case_name': case_name, 'user_login': user_login, 'cfg': cfg, } return NotifcnBuilder.from_row(**n)
def insert(cls, t_eval): kw = t_eval.as_dict() for filed in cls.json_fileds: kw.update({filed: json.dumps(kw[filed])}) def query(): return cls.rdbms_call( ''' INSERT INTO evaluation (test_id, oracle, result, passed) VALUES (:test_id, :oracle, :result, :passed) RETURNING id''', kw) try: pk_id = [r for r in query()].pop()[0] except exc.IntegrityError: raise StorageError('Some kind of IntegrityError') return pk_id
def insert(cls, h_reg): kw = HookRegistrationAdaptor.to_dict(h_reg) for filed in cls.json_fileds: kw.update({filed: json.dumps(kw[filed])}) def query(): return cls.rdbms_call( ''' INSERT INTO hook_registration (case_id, hook_id, descr, owner_id, is_enabled, cfg) VALUES ( :case_id, :hook_id, :descr, (SELECT id FROM "user" WHERE login = :owner), :is_enabled, :cfg) returning id''', kw) try: pk_id = [r for r in query()].pop()[0] except exc.IntegrityError as e: raise StorageError('Some kind of IntegrityError') return pk_id
def get_all(cls): try: rv = cls.rdbms_call('SELECT * from hook', {}) rows = rv.fetchall() except exc.IntegrityError: raise StorageError('Some kind of IntegrityError') except exc.DataError: raise StorageError('One of params malformed or has a wrong type') if len(rows) == 0: return None def create_h(row): h_kw = dict(zip(rv.keys(), row)) return HookBuilder.from_row(**h_kw) return map(create_h, rows)
def insert(cls, dc): kw = DcAdaptor.to_dict(dc) def query(): return cls.rdbms_call( ''' INSERT INTO dc (name) VALUES (:name) returning id''', kw) err_duplicate = 'datacenter:{} allready exists'.format(kw.get('name')) try: pk_id = [r for r in query()].pop()[0] except IndexError: raise StorageError(err_duplicate) except exc.IntegrityError as e: if 'unique constraint "dc_name_key"' in str(e): raise StorageError(err_duplicate) raise StorageError('Some kind of IntegrityError') return pk_id
def __init__(self, **kw): self.rule = [] self.q_params = {} for p, v in kw.iteritems(): if p not in self.params_allowed.keys(): continue elif isinstance(v, (unicode, basestring)): self.rule.append(self.params_allowed[p][0]) self.q_params.update({p: v}) else: raise StorageError('Wrong *{}* param type.'.format(p))
def update_by_name(cls, host_name, host_diff): q_params = host_diff q_params.update({ 'fqdn': host_name, }) try: cls.rdbms_call( cls.cmpl_update_sql(host_diff) + '\nRETURNING id', host_diff) except exc.IntegrityError: raise StorageError('Some kind of IntegrityError') return cls.get_by_fqdn(fqdn=host_name)
def delete(cls, h_reg_id): query_stmt = ''' DELETE FROM hook_registration WHERE id = :h_reg_id RETURNING id''' try: rv = cls.rdbms_call(query_stmt, {'h_reg_id': h_reg_id}) except exc.IntegrityError: raise StorageError('Some kind of IntegrityError') if len(rv.fetchall()) < 1: raise ValueError('Inconsistent query param hook_registration id') return True
def insert(cls, ammo, md5, sha256, size): kw = { 'ammo_hash': '{}|{}|{}'.format(md5, size, sha256) } kw.update(AmmoAdaptor.to_dict(ammo)) for filed in cls.json_fileds: kw.update({filed: json.dumps(kw[filed])}) def query(): return cls.rdbms_call(''' INSERT INTO ammo (case_id, owner_id, name, descr, hash) VALUES ( (SELECT id FROM "case" WHERE name = :case), (SELECT id FROM "user" WHERE login = :owner), :name, :descr, :ammo_hash) returning id''', kw) try: pk_id = [r for r in query()].pop()[0] except exc.IntegrityError as e: if 'violates unique constraint "ammo_hash_key"' in str(e): raise ValueError( 'ammo file exists, hash: {}'.format(kw.get('ammo_hash'))) if 'null value in column "case_id"' in str(e): raise StorageError( 'unknown *case* value:{}'.format(kw.get('case')), missing_resource_type='case', missing_resource_value=kw.get('case'),) raise StorageError('Some kind of IntegrityError') return pk_id
def __init__(self, **kw): self.rule = [] self.q_params = {} for p, v in kw.iteritems(): if p not in self.params_allowed.keys(): continue if isinstance(v, (list, tuple)): if p in self.cast_to_int: # autocast doesn't work for ARRAY v = [int(el) for el in v] self.rule.append(self.params_allowed[p][1]) self.q_params.update({p: v}) elif isinstance(v, (unicode, basestring)): self.rule.append(self.params_allowed[p][0]) self.q_params.update({p: v}) else: raise StorageError('Wrong *{}* param type.'.format(p))
def delete(cls, token_id, login): query_params = { 'token_id': token_id, 'login': login, } query_stmt = ''' DELETE FROM token WHERE owner_id = (SELECT id FROM "user" WHERE login = :login) AND id = :token_id RETURNING id''' try: rv = cls.rdbms_call(query_stmt, query_params) except exc.IntegrityError: raise StorageError('Some kind of IntegrityError') if len(rv.fetchall()) < 1: raise ValueError( 'Inconsistent pair of values: token_id and user login.') return True
def update_or_create(cls, line_entrie): q_params = LineAdaptor.to_dict(line_entrie) q_params['dc_name'] = q_params['dc']['name'] condition_part = ''' INSERT INTO line ( id, name, dc_id ) SELECT :id, :name, (SELECT id FROM dc WHERE name = :dc_name) WHERE NOT EXISTS (SELECT 1 FROM line WHERE id=:id)''' q_text = cls.cmpl_update_sql(q_params) + ';\n' + condition_part try: cls.rdbms_call(q_text + ' RETURNING id', q_params) except exc.IntegrityError as e: raise StorageError('Some kind of IntegrityError') return cls.get_single(line_id=line_entrie.id)
def insert(cls, token): kw = TokenAdaptor.to_dict(token) _orig_set = string.ascii_uppercase + string.lowercase + string.digits _rnd_str = lambda cnt: ''.join( random.choice(_orig_set) for x in xrange(cnt)) kw.update({ 'name': '{}_{}'.format(kw['login'], _rnd_str(15)), 'sault': _rnd_str(15), 'passwd': _rnd_str(15), }) kw['hash'] = cls.cmpl_hash([kw['name'], kw['passwd'], kw['sault']]) query_stmt = ''' INSERT INTO "token" (name, sault, hash, responsible_id, owner_id, permission_id, descr) VALUES (:name, :sault, :hash, (SELECT id from "user" WHERE login = :login), (SELECT id from "user" WHERE login = :login), 3, :descr) RETURNING id ''' try: rv = cls.rdbms_call(query_stmt, kw) except exc.IntegrityError: raise StorageError('Some kind of IntegrityError') t = dict(zip(rv.keys(), rv.fetchall().pop())) t.update({ 'name': kw.get('name'), 'passwd': kw.get('passwd'), 'descr': kw.get('descr'), }) return TokenBuilder.from_row(**t)
def insert(cls, test): kw = test.as_dict(cuted=False) for filed in cls.json_fileds: kw.update({filed: json.dumps(kw[filed])}) def query(parent_test_id): parent_str = \ '(SELECT root_test_id FROM "case" WHERE name = :case),' if parent_test_id and isinstance(parent_test_id, int): parent_str = '(SELECT {}),'.format(parent_test_id) return cls.rdbms_call( ''' INSERT INTO test (t_status_id, case_id, ammo_id, parent_id, engine_id, environment_id, lll_id, lll, initiator_id, "name", descr, issue_id, load_src_id, load_dst_id, started_at, finished_at, files, generator_cfg) VALUES ( (SELECT id FROM t_status WHERE name = :status), (SELECT id FROM "case" WHERE name = :case), (SELECT id FROM "ammo" WHERE path = :ammo_path), ''' + parent_str + ''' (SELECT id FROM engine WHERE name = :engine), (SELECT id FROM environment WHERE name = :env), :lll_id, :lll, (SELECT id FROM "user" WHERE login = :initiator), :name, :descr, (SELECT id FROM "issue" WHERE name = :issue), (SELECT id FROM server WHERE fqdn = :load_src), (SELECT id FROM server WHERE fqdn = :load_dst), :started_at, :finished_at, :files, :generator_cfg) returning id''', kw) try: pk_id = [r for r in query(kw.get('parent_id'))].pop()[0] except exc.IntegrityError as e: if 'null value in column "initiator_id"' in str(e): raise StorageError( 'unknown *initiator* value:{}'.format(kw.get('initiator')), missing_resource_type='user', missing_resource_value=kw.get('initiator'), ) if 'null value in column "case_id"' in str(e): raise StorageError( 'unknown *case* value:{}'.format(kw.get('case')), missing_resource_type='case', missing_resource_value=kw.get('case'), ) if 'null value in column "issue_id"' in str(e): raise StorageError( 'unknown *issue* value:{}'.format(kw.get('issue')), missing_resource_type='issue', missing_resource_value=kw.get('issue'), ) if 'null value in column "load_src_id"' in str(e): raise StorageError( 'unknown *load_src* value:{}'.format(kw.get('load_src')), missing_resource_type='host', missing_resource_value=kw.get('load_src'), ) if 'null value in column "load_dst_id"' in str(e): raise StorageError( 'unknown *load_dst* value:{}'.format(kw.get('load_dst')), missing_resource_type='host', missing_resource_value=kw.get('load_dst'), ) raise StorageError('Some kind of IntegrityError') return pk_id