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