def _do_fetch_task(queue): task = None current = time.time() with db.transaction(): tasks = db.select('select * from tasks where execution_time<? and queue=? and status=? order by execution_time limit ?', current, queue, _PENDING, 1) if tasks: task = tasks[0] if not task: return None expires = current + task.timeout with db.transaction(): if 0==db.update('update tasks set status=?, execution_start_time=?, execution_expired_time=?, version=version+1 where id=? and version=?', _EXECUTING, current, expires, task.id, task.version): raise ConflictError() return Dict(id=task.id, queue=task.queue, name=task.name, task_data=task.task_data, version=task.version+1)
def test_with(self): with db.transaction() as t: t.delete('users').condition('uid', 1).execute() res = db.select('users').condition('uid', 1).execute() self.assertEqual(len(res), 1) res = db.select('users').condition('uid', 1).execute() self.assertEqual(len(res), 0)
def tag(thing,tags): if not isinstance(tags,Taglist): derp = Taglist() try: derp.posi = set(tags) except OverflowError: print('???',tags) raise tags = derp implied = os.environ.get('tags') if implied: implied = parse(implied) with db.transaction(): if tags.nega: tags.nega = toids_nega(tags.nega) note("nega",tags.nega) if implied: tags.update(implied) implied = None disconnect(thing,tags.nega) if tags.posi: tags.posi = toids_posi(tags.posi) note("nega",tags.nega) if implied: tags.update(implied) note("1tomany",tags.posi) db.execute("SELECT connectOneToMany($1,$2)",(thing,tags.posi)) note("many2one") db.execute("SELECT connectManyToOne($1,$2)",(tags.posi,thing))
def merge(dest,source,inferior=True): "source is destroyed, its info sent to dest" with db.transaction(): db.execute("UPDATE things SET neighbors = array(SELECT unnest(neighbors) UNION SELECT unnest(things2.neighbors) FROM things as things2 where things2.id = $2) WHERE id = $1", (dest,source)) # this will make it appear as if the newer medium is kept, but possibly with the older id db.execute("SELECT mergeAdded($1,$2)",(dest,source)) db.execute("""UPDATE media as m1 SET sources = array(SELECT unnest(m1.sources) UNION SELECT unnest(m2.sources)), created = LEAST(m1.created,m2.created), modified = LEAST(m1.modified,m2.modified) FROM media AS m2 WHERE m2.id = $2 AND m1.id = $1""", (dest,source)) # created/modified not unique, so can just smash them through db.execute("UPDATE media SET sources = NULL WHERE id = $1",(source,)) # don't delete the sources, they pass to the dest! db.execute("UPDATE comicpage SET medium = $1 WHERE medium = $2", (dest,source)) db.execute("UPDATE desktops SET id = $1 WHERE id = $2 AND NOT EXISTS(SELECT id FROM desktops WHERE id = $1)", (dest,source)) db.execute("""WITH updoot AS (UPDATE visited as v1 SET visits = v1.visits + v2.visits FROM visited as v2 WHERE v1.medium = $1 AND v2.medium = $2 AND v1.uzer = v2.uzer RETURNING v2.id) DELETE FROM visited WHERE id IN (SELECT id FROM updoot)""", (dest,source)) # the following statement won't hit unique violations, because we updated/deleted them # in the staement above. db.execute("UPDATE visited SET medium = $2 WHERE medium = $1", (dest,source)) db.execute("UPDATE uploads as u1 SET media = $1 WHERE media = $2 AND NOT EXISTS(SELECT * FROM uploads as u2 WHERE media = $1 AND uzer = u1.uzer)", (dest,source)) print('deletan') # the leftover uploads will be deleted by cascade delete.dupe(dest, source, inferior)
def internet(download,media,tags,primarySource,otherSources,name=None): if not name: name = media.uri.rsplit('/',1) if len(name) == 2: name = name[1] else: name = name[0] uniqueSources = set() uniqueSources.add(media) uniqueSources.add(primarySource) if not uniqueSources: raise RuntimeError("No unique sources in this attempt to create?") note('name is',name) otherSources = set(otherSources) for source in otherSources: source.isUnique = False sources = uniqueSources.union(otherSources) with db.transaction(): id,was_created = getanId(sources,uniqueSources,download,name) print('got id',hex(id),was_created) if not was_created: note("Old medium with id {:x}".format(id)) #input() if "nocheck" in os.environ: return id, was_created note("update") update(id,sources,tags,name) note("done update") return id, was_created
def update_contest_statistics(result): """ 更新比赛统计数据 :param result: :return: """ count = db.select_int('select count(*) from t_ranking where contest_id=? and problem_id=? and username=?', result['contest_id'], result['problem_id'], result['user_id']) if count == 0: record = dict(contest_id=result['contest_id'], problem_id=result['problem_id'], username=result['user_id']) db.insert('t_ranking', **record) p_time = db.select_one('select AC_time from t_ranking where contest_id=? and problem_id=? and username=?', result['contest_id'], result['problem_id'], result['user_id']).get('AC_time') if p_time == 0: # 本题还没有AC if result['result'] == configs.result_code.AC: # 本题初次AC with db.connection(): submit_time = db.select_one('select submit_time from t_submission where submission_id=?', result['solution_id']).get('submit_time') # 提交时间, datetime类型 date_time = db.select_one('select contest_date, start_time from t_contest where contest_id=?', result['contest_id']) c_date = date_time.get('contest_date') # 比赛开始日期, date类型 c_time = date_time.get('start_time') # 比赛开始时间, timedelta类型 # 转换为比赛开始时间, datetime类型 contest_time = datetime.strptime(c_date.strftime('%Y-%m-%d %H:%M:%S'), '%Y-%m-%d %H:%M:%S') + c_time ac_time = (submit_time - contest_time).total_seconds() / 60 # 本题初次AC所用时间, 单位为分钟, float类型 with db.transaction(): db.update('update t_ranking set AC_time=? where contest_id=? and problem_id=? and username=?', ac_time, result['contest_id'], result['problem_id'], result['user_id']) # AC题目所用时间 else: db.update('update t_ranking set wrong_submit = wrong_submit + 1 where contest_id=? and problem_id=? ' 'and username=?', result['contest_id'], result['problem_id'], result['user_id'])
def addInfoToMedia(form,media=None): if not media: media = form.get('media') if not media: return media = int(media[0],0x10) dertags = form.get('tags') if not dertags: return sources = form.get('sources') if not sources: return mime = form.get('type') # XXX: should do something with type if not mime: return dertags = tags.parse(dertags[0]) sources = sources[0].split('\n') with db.transaction(): db.execute('UPDATE things SET neighbors = array(SELECT unnest(neighbors) UNION SELECT unnest($2::INTEGER[]) EXCEPT SELECT unnest($3::INTEGER[])) WHERE id = $1', (media,dertags.posi,dertags.nega)) derp = [] for source in sources: operation = 'UNION' if source and source[0] == '-': operation = 'EXCEPT' source = source[1:] derp.append(create.sourceId(source)) db.execute("UPDATE media SET sources = array(SELECT unnest(sources) "+operation+" SELECT unnest($1::INTEGER[])) WHERE id = $2",(derp,media)) db.execute("UPDATE uploads SET checked = TRUE WHERE uzer = $1 AND media = $2",(User.id,media))
def process1(): with transaction() as tran: tran.execute('UPDATE users SET balance = balance + 10 WHERE id = 3;') sleep(1) tran.execute( 'UPDATE users SET balance = balance + 10 WHERE id = 1 RETURNING pg_sleep(1);' )
def findMedium(comic,which,medium=None): if medium: medium,created = findMediumDerp(comic,which,medium) return medium for tries in range(2): medium,created = findMediumDerp(comic,which) if medium: return medium else: note.yellow('No medium for ',comic,which) np = pages(comic) if which == 0 and np == 0: return 0x5c911 if which >= np: print('wnpt',which,np) # XXX: this should be in pages.py if which == 0: raise Redirect("../") raise Redirect("../0/") print("Time to reorder!") with db.transaction(): db.execute("CREATE TEMPORARY TABLE orderincomix AS SELECT id,(row_number() OVER (partition by comic order by which))-1 AS which FROM comicpage") db.execute("UPDATE comicpage SET which = orderincomix.which FROM orderincomix WHERE orderincomix.id = comicpage.id") db.execute("DROP TABLE orderincomix") raise Error("I give up. The comic {:x} is messed up on page {:x}!".format(com,which))
def get(query,args,docache=True): #db.c.verbose = True if hasattr(args,'values'): vals = sorted(n+str(v) for n,v in args.items()) else: args = list(args) vals = args name = hashlib.sha1((query + ''.join(str(arg) for arg in vals)).encode('utf-8')) name = base64.b64encode(name.digest(),altchars=b'-_').decode().replace('=','') if docache == False: return name print(query) print("caching",args) with db.transaction(): exists = db.execute("SELECT resultCache.cleanQuery($1)",(name,))[0][0] if exists: print("already cached") else: try: db.execute('CREATE TABLE resultCache."q'+name+'" AS '+query,args) except db.ProgrammingError as e: if not 'already exists' in e.info['message'].decode('utf-8'): raise db.execute('SELECT resultCache.updateQuery($1)',(name,)) return name;
def commitDoomed(): start("tediously clearing neighbors") # it's way less lag if we break this hella up with db.transaction(): note("find things with doomed neighbors") db.execute(""" CREATE TEMPORARY TABLE doomed_neighbors ON COMMIT DROP AS SELECT id FROM things WHERE neighbors && array(SELECT id FROM doomed)""") note("remove doomed from neighbors") db.execute("""UPDATE things SET neighbors = array(SELECT unnest(neighbors) EXCEPT SELECT id FROM doomed) WHERE id IN (select id FROM doomed_neighbors)""") note("remove sources for doomed media") db.execute(""" DELETE FROM sources USING media WHERE media.id in (select id from doomed) AND sources.id = ANY(media.sources)""") note("delete doomed media") db.execute("DELETE FROM things WHERE id in (select id from doomed)") done()
def set(news): news = tuple(news) names = tuple(new[0] for new in news) values = tuple(new[1] for new in news) names = tuple("{} = ${}".format(name,i+1) for i,name in enumerate(names)) stmt = "UPDATE uzers SET "+", ".join(names) + " WHERE id = ${}".format(len(names)+1) with db.transaction(): db.execute(stmt,values+(User.id,))
def test_begin_commit(self): t = db.transaction() t.begin() t.delete('users').condition('uid', 1).execute() res = db.select('users').condition('uid', 1).execute() self.assertEqual(len(res), 1) t.commit() res = db.select('users').condition('uid', 1).execute() self.assertEqual(len(res), 0)
def _do_fetch_task(queue, _debug=False): task = None current = time.time() with db.transaction(): tasks = db.select('select * from tasks where execution_plan_time<? and queue=? and status=? order by execution_plan_time limit ?', current, queue, _PENDING, 1) if tasks: task = tasks[0] if not task: return None if _debug: time.sleep(1) expires = current + task.timeout execution_id = db.next_str() with db.transaction(): if 0==db.update('update tasks set status=?, execution_id=?, execution_start_time=?, execution_expired_time=?, version=version+1 where id=? and version=?', _EXECUTING, execution_id, current, expires, task.id, task.version): logging.info('version conflict: expect %d.' % task.version) raise ConflictError() return Dict(id=task.id, execution_id=execution_id, queue=task.queue, name=task.name, task_data=task.task_data, version=task.version+1)
def _create_ranking_table(result): """ 创建比赛排名表及触发器 db.update('drop table if exists t_?', result['contest_id']) :return: """ sql_trigger1 = ["create trigger calculate_submission after insert on t_submission for each row " "begin update t_problem set submit = submit + 1 where problem_id = new.problem_id; " "update t_user set submit = submit + 1 where username = new.username; " "if new.contest_id is not null then update t_contest_problem set submit = submit + 1 " "where contest_id = new.contest_id and problem_id = new.problem_id; end if; end;"] sql_trigger2 = ["create trigger calculate_AC before update on t_submission for each row begin " "declare num int; if new.result = ? then " "set num = (select count(*) from t_submission where problem_id = new.problem_id and " "username = new.username and result = ?); if num = 0 then " "update t_problem set solved = solved + 1 where problem_id = new.problem_id; " "update t_user set solved = solved + 1 where username = new.username;" "end if; if new.contest_id is not null then " "set num = (select count(*) from t_submission where problem_id = new.problem_id " "and username = new.username and contest_id = new.contest_id and result = ?); " "if num = 0 then " "update t_contest_problem set solved = solved + 1 where contest_id = new.contest_id " "and problem_id = new.problem_id; end if; end if; end if; end;"] sql_trigger3 = ["create trigger calculate_penalty_time before update on t_ranking for each row " "begin set new.penalty = new.AC_time + (new.AC_time xor 0) * new.wrong_submit * 20;end;"] db.update('drop table if exists t_?', result['contest_id']) sql_table = ["create table if not exists t_? (ranking_id int(11) unsigned primary key auto_increment, " "username char(30), solved int(11) default '0', total_time float unsigned default '0',"] sql_trigger = ["create trigger calculate_penalty before update on t_? for each row " "begin declare total float; set total = "] l_problem = db.select('select problem_id from t_contest_problem where contest_id=?', result['contest_id']) table_param = [result['contest_id']] trigger_param = [result['contest_id']] for problem in l_problem: problem_id = problem.get('problem_id') table_param.extend([problem_id for i in range(2)]) trigger_param.extend([problem_id for i in range(3)]) sql_table.extend(["?_time float unsigned default '0', ?_wrong smallint(6) default '0',"] * len(l_problem)) sql_table.append("foreign key (username) references t_user(username)) default charset=utf8") sql_trigger.append('+'.join(["new.?_time + (new.?_time xor 0) * new.?_wrong * 1200 "] * len(l_problem))) sql_trigger.append("; set new.total_time = total; end;") with db.transaction(): db.update(' '.join(sql_table), *table_param) db.update('drop trigger if exists calculate_penalty') db.update(' '.join(sql_trigger), *trigger_param) db.update('drop trigger if exists calculate_submission') db.update(''.join(sql_trigger1)) db.update('drop trigger if exists calculate_AC') db.update(''.join(sql_trigger2), configs.result_code.AC, configs.result_code.AC, configs.result_code.AC) db.update('drop trigger if exists calculate_penalty_time') db.update(''.join(sql_trigger3))
def request_info(): # It should print the web requested print request.data # history of each referer domain with transaction() as session: information = session.query(Information).filter(Information.domain_url == request.data).all() return jsonify({'entrances': [{'creative_size': entrance.creative_size, 'referer_url': entrance.referer_url} for entrance in information]})
def run(self): """ Main function for thread that will """ # It might be good to create a process rather than a thread per file, and to create a multithreading environment # to process the list rather than a single thread for all of it (test under big file environment). # One json per line my_list = self.buf_file.splitlines() for line in my_list: # Ignoring if there is an empty entrance in the list if line: # load json json_line = json.loads(line) # we are going to look for creative_size, if it does not exist we will get the information from ad_width # and ad_height creative_size = find_key("creative_size", json_line) if not creative_size: value_width = find_key("ad_width", json_line) value_height = find_key("ad_height", json_line) if value_width and value_height: creative_size = [value_width[0] + "x" + value_height[0]] # We are going to look for the keys page_url and Referer referer = find_key("Referer", json_line) url = find_key("page_url", json_line) # If the three elements were found, introduce them in the DB. if creative_size and referer and url: with transaction() as session: added = False # Check the existence of the entrance before introducing a repetitive one if not session.query(Domain).filter(Domain.url==url[0]).first(): session.add(Domain(url[0])) added = True if not session.query(Referer).filter(Referer.url==referer[0]).first(): session.add(Referer(referer[0])) added = True session.flush() # If one of the previous tables has a new entry. No need to check of existence in here. if added: session.add(Information(domain_url=url[0], referer_url=referer[0], creative_size=creative_size[0])) elif not session.query(Information).filter(Information.domain_url==url[0])\ .filter(Information.referer_url==referer[0])\ .filter(Information.creative_size==creative_size[0]).first(): session.add(Information(domain_url=url[0], referer_url=referer[0], creative_size=creative_size[0])) print "Database updated with information from file: %s" % self.f_name
def findMediumDerp(comic,which,medium=None): rows = db.execute("SELECT medium FROM comicPage WHERE comic = $1 AND which = $2",(comic,which)) if len(rows)==0: if medium: with db.transaction(): db.execute("INSERT INTO comicPage (comic,which,medium) VALUES ($1,$2,$3)",(comic,which,medium)) db.execute("UPDATE comics SET added = now() WHERE id = $1",(comic,)) return medium,True return None,False #note.yellow('medium existed',rows,medium) return rows[0][0],False
def churn(category,tags,limit=chunkOPics): print("churning...") stmt,arg = withtags.tagStatement(tags,limit=limit) cat = arg(category) # [with.base] -> limit.clause -> order.clause -> select base = stmt.body if hasattr(stmt,'body') else stmt base = base.clause # order (.clause -> select) notSeen = IS('randomSeen.media','NULL') base.clause.where = AND(base.clause.where,notSeen) if base.clause.where else notSeen base.clause.From = OuterJoin(base.clause.From, AS(Select('media','randomSeen', EQ('category',cat)), 'randomSeen'), EQ('randomSeen.media','media.id')) base.clause.what = ('media.id',cat) base.order = 'random(),'+arg(random.random()) stmt = With( Select('count(*)','rows'), rows=(None,'INSERT INTO randomSeen (media, category) ' + stmt.sql() + '\nRETURNING 1')).sql() args = arg.args #print(stmt.replace(' ','.')) #print(args) #raise SystemExit while True: try: num = db.execute(stmt,args)[0][0] except db.ProgrammingError as e: derp = 0 lines = stmt.split('\n') import math wid = int(1+math.log(len(lines)) / math.log(10)) wid = '{:'+str(wid)+'}' def num(): nonlocal derp ret = wid.format(derp)+' ' derp += 1 return ret print('\n'.join(num()+line for line in lines)) print(e.info['message'].decode('utf-8')) import traceback traceback.print_exc() raise SystemExit if num > 0: break # out of media, better throw some back into the pot with db.transaction(): db.execute('DELETE FROM randomSeen WHERE category = $1 AND id < (SELECT AVG(id) FROM randomSeen WHERE category = $1)',(category,)) # this shouldn't violate unique, since more than 1/2 were deleted # or... should it be SELECT MEDIAN(id) or something above? db.execute('UPDATE randomSeen SET id = id - (SELECT MIN(id) FROM randomSeen WHERE category = $1) WHERE category = $1',(category,)) db.execute("SELECT setval('randomSeen_id_seq',(SELECT MAX(id) FROM randomSeen WHERE category = $1))",(category,))
def _do_fetch_task(queue): task = None current = time.time() with db.transaction(): tasks = db.select( 'select * from tasks where execution_time<? and queue=? and status=? order by execution_time limit ?', current, queue, _PENDING, 1) if tasks: task = tasks[0] if not task: return None expires = current + task.timeout with db.transaction(): if 0 == db.update( 'update tasks set status=?, execution_start_time=?, execution_expired_time=?, version=version+1 where id=? and version=?', _EXECUTING, current, expires, task.id, task.version): raise ConflictError() return Dict(id=task.id, queue=task.queue, name=task.name, task_data=task.task_data, version=task.version + 1)
def wrapper(*a,**kw): try: with db.transaction(): note.blue('locking',table) try: db.execute('LOCK TABLE '+table+' IN EXCLUSIVE MODE NOWAIT') except db.ProgrammingError: # something else is handling this URL... raise note.blue('locked',table,f,a,kw) return f(*a,**kw) finally: note.alarm('unlocked',table)
def user(path,params,data): assert(data is None) params = dict(params) note('updating user',params) def check(name): l = params.get(name) if l: return l[0] def checkB(name): if check(name): return True return False rescale_width = check('rescale_width') if rescale_width: rescale_width = int(rescale_width) if rescale_width > 2048: rescale_width = 2048 elif rescale_width < 400: rescale_width = 400 else: rescale_width = 800 assert rescale_width news = { 'rescaleimages': checkB('rescale'), 'nocomics': checkB('comic'), 'navigate': checkB('navigate'), 'loadjs': checkB('loadjs'), 'rescale_width': rescale_width, } newtags = check('tags') note('updating user tags',newtags) news['defaultTags'] = False self = derp.currentUser() with db.transaction(): # XXX: tasteless db.execute("DELETE FROM uzertags WHERE uzer = $1",(self.id,)) if newtags: tags = tagsModule.parse(newtags[0]) if tags.posi: db.execute('INSERT INTO uzertags (tag,uzer,nega) SELECT unnest(array(SELECT unnest($1::INTEGER[]) EXCEPT SELECT tag FROM uzertags WHERE uzer = $2)),$2,FALSE',(tags.posi,self.id)) if tags.nega: db.execute('INSERT INTO uzertags (tag,uzer,nega) SELECT unnest(array(SELECT unnest($1::INTEGER[]) EXCEPT SELECT tag FROM uzertags WHERE uzer = $2)),$2,TRUE',(tags.nega,self.id)) db.execute('UPDATE uzers SET defaultTags = FALSE WHERE id = $1',(self.id,)) derp.set(news.items()) return ""
def sourceId(source,isUnique=True,hasTags=False): assert source is not None assert not isinstance(source,int) if source == '': return None if source[0] == '/': return None # todo: file sources? with db.transaction(): id = db.execute("SELECT id FROM urisources WHERE uri = $1",(source,)) if id: return id[0][0] else: id = db.execute("INSERT INTO sources (hasTags,uniquelyIdentifies) VALUES ($1,$2) RETURNING id",(hasTags,isUnique)) id = id[0][0] db.execute("INSERT INTO urisources (id,uri) VALUES ($1,$2) RETURNING id",(id,source)) return id
def update(id,sources,tags,name): donetags = [] with db.transaction(): db.execute("UPDATE media SET name = coalesce($3,name), sources = array(SELECT unnest(sources) from media where id = $2 UNION SELECT unnest($1::INTEGER[])), modified = clock_timestamp() WHERE id = $2",([source.id for source in sources],id,name)) oldTags = db.execute("SELECT neighbors FROM things WHERE id = $1",(id,))[0][0] tags = tagsModule.toids_posi(tags) if oldTags: # only new tags please note.yellow(tags,"old",oldTags) tags = set(tags)-set(oldTags) if tags: for tag in tags: assert tag is not None, tags note("setting tags",tags) tagsModule.tag(id,tags)
def test_database_operations(self): #initialization of engine db.create_engine('test.db') #create Table db.update('drop table if exists User') db.update('create table User(id int primary key, name varchar(20),password varchar(20),gender varchar(8))') #insert r1 = db.insert('User',id=db.get_id(),name='user1',password='******',gender='male') r2 = db.insert('User',id=db.get_id(),name='user2',password='******',gender='female') r3 = db.insert('User',id=db.get_id(),name='user3',password='******',gender='male') self.assertEquals(r1,1) self.assertEquals(r2,1) self.assertEquals(r3,1) #test select r4 = db.select_one('select name from User where gender=?','male') r5 = db.select_all('select name from User where gender=?','male') self.assertIsInstance(r4,dict) self.assertIsInstance(r5,list) r6 = db.select_one('select name from User where gender=?','asldfkj') r7 = db.select_all('select name from User where gender=?','asldfkj') self.assertIsNone(r6) self.assertEquals(r7,[]) #test update r8 = db.update('update User SET gender=? where name=?','male','user1') r9 = db.update('update User SET gender=? where name=?','male','asdfas') r10 = db.update('update User SET name =? where gender=?','haha','male') self.assertEquals(r8,1) self.assertEquals(r9,0) self.assertEquals(r10,2) #test transactions with db.transaction(): db.insert('User',id=db.get_id(),name='user5',password='******',gender='female') db.insert('User',id=db.get_id(),name='user5',password='******',gender='male') r12 = db.select_all('select * from User where name=?','user5') self.assertEquals(len(r12),2) db.engine = None
def handle(description): nonlocal rows with db.transaction(): rows = db.execute("SELECT id FROM comics WHERE title = $1", (title,)) if rows: # nehh, race condition, something else created this comic! return rows[0][0] try: rows = db.execute("INSERT INTO comics (title,description) VALUES ($1,$2) RETURNING id", (title, description)) except db.ProgrammingError: db.retransaction(rollback=True) # the counter doesn't get auto-incremented when we specify the comic ID ourselves db.execute("SELECT setval('comics_id_seq'::regclass,MAX(id),true) FROM comics") rows = db.execute("INSERT INTO comics (title,description) VALUES ($1,$2) RETURNING id", (title, description))
def update_contest_ranking(result): """ 更新比赛统计数据 :param result: :return: """ rowcount = db.update("show tables like 't_?'", result['contest_id']) if rowcount == 0: PutTask._create_ranking_table(result) count = db.select_int('select count(*) from t_? where username=?', result['contest_id'], result['user_id']) if count == 0: user = dict(username=result['user_id']) db.insert('t_%s' % result['contest_id'], **user) p_time = db.select_one('select ?_time from t_? where username=?', result['problem_id'], result['contest_id'], result['user_id'])\ .get('%s_time' % result['problem_id']) if p_time == 0: # 本题还没有AC if result['result'] == configs.result_code.AC: # 本题初次AC with db.connection(): submit_time = db.select_one('select submit_time from t_submission where submission_id=?', result['solution_id']).get('submit_time') # 提交时间, datetime类型 date_time = db.select_one('select contest_date, start_time from t_contest where contest_id=?', result['contest_id']) c_date = date_time.get('contest_date') # 比赛开始日期, date类型 c_time = date_time.get('start_time') # 比赛开始时间, timedelta类型 # 转换为比赛开始时间, datetime类型 contest_time = datetime.strptime(c_date.strftime('%Y-%m-%d %H:%M:%S'), '%Y-%m-%d %H:%M:%S') + c_time ac_time = (submit_time - contest_time).total_seconds() # 本题初次AC所用时间, 单位为秒, float类型 with db.transaction(): db.update('update t_? set solved = solved + 1 where username=?', result['contest_id'], result['user_id']) # AC题数 + 1 db.update('update t_? set ?_time=? where username=?', result['contest_id'], result['problem_id'], ac_time, result['user_id']) # AC题目所用时间 else: db.update('update t_? set ?_wrong = ?_wrong + 1 where username=?', result['contest_id'], result['problem_id'], result['problem_id'], result['user_id'])
def result(title, description, source, tags): with db.transaction(): db.execute("INSERT INTO comics (id,title,description) VALUES ($1,$2,$3) RETURNING id",(id,title,description)) if next: return next(title,description,source,tags)
def getanId(sources,uniqueSources,download,name): for uniqueSource in uniqueSources: result = db.execute("SELECT id FROM media where media.sources @> ARRAY[$1::integer]",(uniqueSource.id,)) if result: return result[0][0], False md5 = None for i,source in enumerate(sources): if source.uri: m = findMD5.search(source.uri) if m: md5 = m.group(0) result = db.execute("SELECT id FROM media WHERE md5 = $1", (md5,)) if result: return result[0][0],False # don't return here! # we found an md5 and it wasn't in our database # so download it! note("downloading to get an id") with filedb.mediaBecomer() as data: created,mimetype = download(data) note('cerated',created) data.seek(0,0) digest = mediaHash(data) result = db.execute("SELECT id FROM media WHERE hash = $1",(digest,)) if result: print("Oops, we already had this one, from another source!") return result[0][0],False result = db.execute("SELECT medium FROM dupes WHERE hash = $1",(digest,)) if result: id = result[0][0] print("Dupe of {:x}".format(id)) return id, False result = db.execute("SELECT id FROM blacklist WHERE hash = $1",(digest,)) if result: # this hash is blacklisted raise NoGood("blacklisted",digest) if md5 is None: data.seek(0,0) md5 = MD5() shutil.copyfileobj(data,writer(md5.update)) md5 = md5.hexdigest() with db.transaction(): id = db.execute("INSERT INTO things DEFAULT VALUES RETURNING id") id = id[0][0] image = None data.seek(0,0) savedData = data image,derp = openImage(data) if mimetype is None: mimetype = derp if mimetype is None: if not image: note('we hafe to guess') try: mimetype = magic.guess_type(data.name)[0] except TypeError: mimetype = manuallyGetType(data, mimetype) else: if mimetype is None or mimetype == 'binary': mimetype = manuallyGetType(data,mimetype) else: mimetype = mimetype.split('\\012')[0] note.blue('guessed mimetype',repr(mimetype),type(mimetype)) if not isGood(mimetype): mimetype = manuallyGetType(data,mimetype) if not '.' in name: name += '.' + magic.guess_extension(mimetype) note("New {} with id {:x} ({})".format(mimetype,id,name)) sources = set([source.id for source in sources]) db.execute("INSERT INTO media (id,name,hash,created,size,type,md5,sources) VALUES ($1,$2,$3,$4,$5,$6,$7,$8)",( id,name,digest,created, os.fstat(data.fileno()).st_size,mimetype,md5,sources)) if image: createImageDBEntry(id,image) else: if mimetype.startswith('video'): movie.isMovie(id,data) else: print(RuntimeError('WARNING NOT AN IMAGE OR MOVIE {:x}' .format(id))) data.flush() if hasattr(created,'timestamp'): timestamp = created.timestamp() else: import time timestamp = time.mktime(created.timetuple()) os.utime(data.name,(timestamp,timestamp)) data.become(id) # create thumbnail proactively # don't bother waiting for it to appear # filedb.incoming(id) return id,True
import favorites.parse import favorites.parsers import setupurllib def setup(): user.User.noComics = False withtags.tagsWhat = ["media.id"] images,args = withtags.tagStatement(tags.parse("comic:a dash of peppermint, comic:mommy issues"),limit=0x100) # With(body) > Limit(clause) sql = "CREATE TABLE IF NOT EXISTS need_retagging AS " + images.sql() db.execute(sql,args.args) setup() with db.transaction(): db.execute("UPDATE things SET neighbors = array(SELECT unnest(neighbors) EXCEPT SELECT id FROM need_retagging) WHERE id in (select id from things where neighbors && array(SELECT id FROM need_retagging) INTERSECT SELECT id FROM tags WHERE name LIKE 'comic:%')") print("boop") db.execute("UPDATE things SET neighbors = array(SELECT unnest(neighbors) EXCEPT SELECT id FROM tags WHERE name LIKE 'comic:%') WHERE id IN (SELECT id FROM need_retagging)") for id, in db.execute("select id from need_retagging"): for source, in db.execute("SELECT urisources.uri FROM urisources inner join sources on sources.id = urisources.id WHERE uniquelyidentifies AND sources.id IN (SELECT unnest(sources) FROM media where id = $1)",(id,)): if source.startswith("https://derpicdn.net"): continue if source.startswith("https://static1.e621.net"): continue print("retagging",hex(id),source) try: favorites.parse.parse(source) except setupurllib.URLError as e: print(e.args[0]) continue; except favorites.parse.ParseError as e: print(e) continue
def tag(comic,tags): with db.transaction(): db.execute('UPDATE comics SET tags = array(select unnest(tags) UNION select unnest($1::INTEGER[]) EXCEPT select unnest($2::INTEGER[])) WHERE id = $3',(tags.posi,tags.nega,comic))
def wrapper(*a,**kw): with transaction(): f(*a,**kw)