def work():
try:
db.execute_sql(r"""
CREATE OR REPLACE FUNCTION int2bytea(v_number bigint) RETURNS bytea AS $$
DECLARE
v_str text;
BEGIN
v_str = to_hex(v_number)::text;
return decode(concat(repeat('0', length(v_str) %% 2), v_str), 'hex');
END;
$$ LANGUAGE plpgsql;
CREATE SEQUENCE IF NOT EXISTS id_gen_seq NO MINVALUE NO MAXVALUE START 4096 NO CYCLE; /* 0x1000 */
CREATE SEQUENCE IF NOT EXISTS user_count_seq NO MINVALUE NO MAXVALUE START 1 NO CYCLE;
""")
# 请注意，这俩需要数据库的 superuser 权限，因此普通用户是做不到的
# 会提示 permission denied to create extension "hstore" 这样的错误
# db.execute_sql("""
# CREATE EXTENSION IF NOT EXISTS hstore;
# CREATE EXTENSION IF NOT EXISTS citext;
# """)
except peewee.ProgrammingError as e:
db.rollback()
traceback.print_exc()
quit()
sql_execute('alter table "user" drop column key')
sql_execute('alter table "user" drop column key_time')
def sql_execute(sql):
try:
db.execute_sql(sql)
except Exception as e:
print(e)
print('failed')
db.rollback()
def work():
try:
db.execute_sql(
'ALTER TABLE "topic" ADD COLUMN "update_time" BIGINT NULL DEFAULT 0;'
)
except Exception as e:
print(e)
print('failed')
db.rollback()
def work():
try:
db.execute_sql(
'ALTER TABLE public.comment ADD reply_to_cmt_id BYTEA NULL;')
except:
db.rollback()
for i in UserModel.select().execute():
try:
UserNotifRecord.create(id=i.id, update_time=int(time.time()))
except peewee.IntegrityError:
db.rollback()
def register():
if request.method == 'POST':
if check_creadentials(request.form['login'], request.form['password']):
return render_template('register.html', error="User exists")
else:
if Cache().check_password(str(request.form['password'])) == "1":
return render_template('register.html',
error="Your password is too common")
db.execute_sql('insert into users values(NULL, "{}", "{}")'.format(
request.form['login'], request.form['password']))
Cache().validate((request.form['login'], request.form['password']))
return render_template('login.html',
message="Successfully registered")
else:
return render_template('register.html')
def fetch_notif_of_reply(user_id, last_reply_id=b'\x00'):
# TODO: 还是仅支持文章
# 某某 在文章 某某某 中回复了你的评论： XXXXXX
# c2 是 user_id 的原评论，c 是回复评论的评论
cur = db.execute_sql(
'''
SELECT "c".time, "c"."id", "c"."related_id", "c"."related_type", "c".user_id,
"t"."title", left("c".content, 50), "u"."nickname"
FROM topic AS t, comment AS c, comment AS c2, "user" as u
WHERE c2.user_id = %s AND c2.state >= %s AND
c2.id = c.reply_to_cmt_id AND c.id > %s AND c.state >= %s AND t.id = c.related_id
AND u.id = c.user_id AND "c"."user_id" != "c2"."user_id" -- 不查自己
ORDER BY "c"."id" DESC
''', (user_id, POST_STATE.NORMAL, last_reply_id, POST_STATE.NORMAL))
# 时间，评论ID，文章ID，POST类型，用户ID，文章标题，前50个字，用户昵称
def wrap(i):
return {
'type': NOTIF_TYPE.BE_REPLIED,
'time': i[0],
'loc_post_type': i[3],
'loc_post_id': i[2],
'loc_post_title': i[5],
'sender_ids': (i[4], ),
'receiver_id': user_id,
'from_post_type': POST_TYPES.COMMENT,
'from_post_id': i[1],
'related_type': POST_TYPES.COMMENT,
'related_id': i[1],
'brief': i[6]
}
return map(wrap, cur.fetchall())
def fetch_notif_of_comment(user_id, last_comment_id=b'\x00'):
# 某某 评论了你的文章 某某某： XXXXXX
# 这个暂时不折叠了，全部显示在提醒中
cur = db.execute_sql(
'''
SELECT "c".time, "c"."id", "c"."related_id", "c"."related_type", "c".user_id,
"t"."title", left("c".content, 50), "u"."nickname"
FROM topic AS t, comment AS c, "user" as u
WHERE t.user_id = %s AND t.state >= %s AND t.id = c.related_id
AND c.id > %s AND c.state >= %s AND u.id = c.user_id AND "c"."user_id" != "t"."user_id"
ORDER BY "c"."id" DESC
''', (user_id, POST_STATE.CLOSE, last_comment_id, POST_STATE.NORMAL))
# 时间，评论ID，文章ID，POST类型，用户ID，文章标题，前50个字，用户昵称
def wrap(i):
return {
'type': NOTIF_TYPE.BE_COMMENTED,
'time': i[0],
'post': {
'id': i[2],
'type': i[3],
'title': i[5]
},
'comment': {
'id': i[1],
'brief': i[6],
'user': {
'id': i[4],
'nickname': i[7]
}
}
}
return map(wrap, cur.fetchall())
def set_read(cls, user_id):
cur = db.execute_sql('''
WITH updated_rows as (
UPDATE notif SET is_read = TRUE WHERE "receiver_id" = %s AND "is_read" = FALSE
RETURNING is_read
) SELECT count(is_read) FROM updated_rows;
''', (user_id,))
return cur.fetchone()[0]
def cabinet():
if not check_creadentials():
return redirect('/')
login = request.cookies.get('login')
if request.method == 'POST':
city, weather, day, night, official = request.form[
'city'], request.form['weather'], request.form[
'day'], request.form['night'], int(
request.form.get('official', 0))
db.execute_sql(
'insert into forecasts VALUES(NULL, "{}", "{}", "{}", "{}", "{}", "{}")'
.format(login, city, weather, day, night, official))
Search().add(city, weather, day, night, str(official))
cursor = db.execute_sql(
'select * from forecasts where author="{}"'.format(login))
return render_template('cabinet.html',
forecasts=[row for row in cursor.fetchall()])
def reset():
db.execute_sql("""
DROP TABLE IF EXISTS "board";
DROP TABLE IF EXISTS "comment";
DROP TABLE IF EXISTS "follow";
DROP TABLE IF EXISTS "notif";
DROP TABLE IF EXISTS "statistic";
DROP TABLE IF EXISTS "statistic24h";
DROP TABLE IF EXISTS "topic";
DROP TABLE IF EXISTS "user";
DROP TABLE IF EXISTS "user_notif_record";
DROP TABLE IF EXISTS "user_oauth";
DROP TABLE IF EXISTS "wiki_history";
DROP TABLE IF EXISTS "wiki_item";
DROP TABLE IF EXISTS "wiki_article";
DROP SEQUENCE IF EXISTS "id_gen_seq";
DROP SEQUENCE IF EXISTS "user_count_seq";
""")
def weight_inc(self):
""" 提升一点排序权重 """
try:
db.execute_sql(
"""
WITH
t1 as (SELECT "id", "weight" FROM "topic" WHERE "id" = %s),
t2 as (SELECT "t2"."id", "t2"."weight" FROM t1, "topic" AS t2
WHERE "t2"."weight" > "t1".weight ORDER BY "weight" ASC LIMIT 1)
UPDATE "topic"
set "weight" = (
CASE WHEN "topic"."id" = "t1"."id"
THEN "t2"."weight"
ELSE "t1"."weight" END
)
FROM t1, t2
WHERE "topic"."id" in ("t1"."id", "t2"."id");
""", (self.id, ))
except DatabaseError:
pass
def check_creadentials(login=None, password=None):
if not login and not password:
login = request.cookies.get('login')
password = request.cookies.get('password')
try:
cursor = db.execute_sql(
'select * from users where login="******" and password="******"'.format(
login, password))
for row in cursor.fetchall():
return True
except:
return False
def tearDown(self):
db.execute_sql(
"""DELETE FROM scry2.categories where name='["Aviation6", "Commercial Flights", "Airport Info"]';"""
)
def weight_gen(cls):
cur = db.execute_sql('select max(weight)+1 from "topic"')
return cur.fetchone()[0] or 0
async def weight_redis_init(cls):
cur = db.execute_sql('select max(weight)+1 from "topic"')
await redis.set(RK_TOPIC_WEIGHT_MAX, cur.fetchone()[0] or 0)
def work():
try:
db.execute_sql('ALTER TABLE "board" ADD COLUMN "parent_id" BYTEA NULL DEFAULT NULL;')
except:
print('failed')
db.rollback()
def get_user_count_seq():
return db.execute_sql("select nextval('user_count_seq')").fetchone()[0]
def work():
try:
db.execute_sql('DROP TABLE statistic24h;')
db.execute_sql('ALTER TABLE "user" ADD COLUMN "visible" INTEGER NULL DEFAULT NULL;')
db.execute_sql('ALTER TABLE "user" ADD COLUMN "user_id" BYTEA NULL DEFAULT NULL;')
db.execute_sql('ALTER TABLE "user" ADD COLUMN "reset_key" BYTEA NULL DEFAULT NULL;')
db.execute_sql('ALTER TABLE "user" ADD COLUMN "avatar" TEXT NULL DEFAULT NULL;')
db.execute_sql('ALTER TABLE "user" ADD COLUMN "type" INTEGER DEFAULT 0;')
db.execute_sql('ALTER TABLE "user" ADD COLUMN "url" TEXT NULL DEFAULT NULL;')
db.execute_sql('ALTER TABLE "user" ADD COLUMN "location" TEXT NULL DEFAULT NULL;')
db.execute_sql('ALTER TABLE "user" ADD COLUMN "access_time" BIGINT NULL DEFAULT NULL;')
db.execute_sql('ALTER TABLE "user" ADD COLUMN "last_check_in_time" BIGINT NULL DEFAULT NULL;')
db.execute_sql('ALTER TABLE "user" ADD COLUMN "check_in_his" INT DEFAULT 0;')
db.execute_sql('ALTER TABLE "user" ADD COLUMN "exp" INT DEFAULT 0;')
db.execute_sql('ALTER TABLE "user" RENAME "reg_time" TO "time";')
db.execute_sql('ALTER TABLE "board" RENAME "creator_id" TO "user_id";')
db.execute_sql('ALTER TABLE "comment" ADD COLUMN "post_number" INTEGER NULL;')
db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "bookmark_count" INTEGER DEFAULT 0;')
db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "upvote_count" INTEGER DEFAULT 0;')
db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "downvote_count" INTEGER DEFAULT 0;')
db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "thank_count" INTEGER DEFAULT 0;')
db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "vote_weight" BIGINT DEFAULT 0;')
db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "bookmarked_users" BYTEA[] NULL;')
db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "upvoted_users" BYTEA[] NULL;')
db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "downvoted_users" BYTEA[] NULL;')
db.execute_sql('ALTER TABLE "statistic" ADD COLUMN "thanked_users" BYTEA[] NULL;')
except Exception as e:
print(e)
print('failed')
db.rollback()
for i in Comment.select():
post_number = Comment.select().where(Comment.related_id == i.related_id, Comment.id <= i.id).count()
Comment.update(post_number=post_number).where(Comment.id == i.id).execute()
DROP TABLE IF EXISTS "wiki_history";
DROP TABLE IF EXISTS "wiki_item";
DROP TABLE IF EXISTS "wiki_article";
DROP SEQUENCE IF EXISTS "id_gen_seq";
DROP SEQUENCE IF EXISTS "user_count_seq";
""")
try:
db.execute_sql(r"""
CREATE OR REPLACE FUNCTION int2bytea(v_number bigint) RETURNS bytea AS $$
DECLARE
v_str text;
BEGIN
v_str = to_hex(v_number)::text;
return decode(concat(repeat('0', length(v_str) %% 2), v_str), 'hex');
END;
$$ LANGUAGE plpgsql;
CREATE SEQUENCE IF NOT EXISTS id_gen_seq NO MINVALUE NO MAXVALUE START 4096 NO CYCLE; /* 0x1000 */
CREATE SEQUENCE IF NOT EXISTS user_count_seq NO MINVALUE NO MAXVALUE START 1 NO CYCLE;
""")
db.execute_sql("""
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS citext;
""")
except peewee.ProgrammingError:
# permission denied to create extension "hstore"
db.rollback()
db.create_tables([
def index():
сursor = db.execute_sql(
'select forecasts.official, forecasts.city,(SELECT weather from forecasts WHERE city=f.city AND official=0 ORDER BY id DESC limit 1),(SELECT day from forecasts WHERE city=f.city AND official=0 ORDER BY id DESC limit 1),(SELECT night from forecasts WHERE city=f.city AND official=0 ORDER BY id DESC limit 1) from forecasts LEFT JOIN forecasts AS f ON f.city=forecasts.city group by city, official HAVING official=0;'
)
return render_template('index.html',
forecasts=[row for row in сursor.fetchall()])
def sql_execute(sql):
try:
db.execute_sql(sql)
except Exception as e:
db.rollback()
def populate_virt_table():
db.execute_sql(
'INSERT INTO searchproducts SELECT product || " " || brand FROM products'
)
