def paginate(cls, where={}, orderBy=Model._sentinel, page=1, perPage=20):
cur = db.cursor()
if not "search" in where:
bleats = super(Bleat, cls).paginate(where, orderBy, page, perPage)
else:
if orderBy is Model._sentinel:
# By number of matches, then by time and id
orderBy = "length(offsets(bleat_content)) - length(replace(offsets(bleat_content), \" \", \"\")) desc, timestamp desc, id desc"
query = [
"select bleat.*, offsets(bleat_content)",
"from bleat_content inner join bleat on id = docid"
]
where["bleat_content"] = ("match", where.pop("search"))
where = cls._buildWhereClause(where)
query.append("where {0}".format(" and ".join(where.keys())))
if orderBy:
query.append("order by {0}".format(orderBy))
if perPage:
# Get total number of records so we can work out pages
cur.execute(" ".join(["select count(*)"] + query[1:]),
where.values())
totalRecords = cur.fetchone()[0]
query.append("limit {0:d}".format(perPage))
if page > 1:
query.append("offset {0:d}".format(perPage * (page - 1)))
cur.execute(" ".join(query), where.values())
records = map(cls, cur.fetchall())
if not perPage:
totalRecords = len(records)
totalPages = (
totalRecords +
(perPage - 1)) / perPage if perPage else 1 # Ceiling division
bleats = {
"records": records,
"page": page,
"totalRecords": totalRecords,
"totalPages": totalPages
}
# Joins? Nah, too lazy
for bleat in bleats["records"]:
cur.execute("select user from bleat_mention where bleat = ?",
(bleat.id, ))
setattr(bleat, "mentions",
set(map(lambda row: row["user"], cur.fetchall())))
cur.execute("select file from bleat_attachment where bleat = ?",
(bleat.id, ))
setattr(bleat, "attachments",
set(map(lambda row: row["file"], cur.fetchall())))
return bleats
def delete(cls, where):
where = cls._buildWhereClause(where)
cur = db.cursor()
cur.execute(
"delete from {0} where {1}".format(
cls._toTableName(cls.__name__),
" and ".join(where.keys()),
), where.values())
return cur.rowcount
def findOne(cls, where={}):
query = "select * from {0}".format(cls._toTableName(cls.__name__))
if where:
where = cls._buildWhereClause(where)
query += " where {0}".format(" and ".join(where.keys()))
cur = db.cursor()
cur.execute(query + " limit 1", where.values())
result = cur.fetchone()
return cls(result) if result else None
def findOne(cls, where = {}):
query = "select * from {0}".format(cls._toTableName(cls.__name__))
if where:
where = cls._buildWhereClause(where)
query += " where {0}".format(" and ".join(where.keys()))
cur = db.cursor()
cur.execute(query + " limit 1", where.values())
result = cur.fetchone()
return cls(result) if result else None
def create(cls, properties):
cur = db.cursor()
if properties:
cur.execute(
"insert into {0} ({1}) values ({2})".format(
cls._toTableName(cls.__name__),
", ".join(map(cls._toTableName, properties.keys())),
", ".join(["?"] * len(properties))), properties.values())
else:
cur.execute("insert into {0} default values".format(
cls._toTableName(cls.__name__)))
return cls.findOne({"id": cur.lastrowid})
def findOne(cls, where={}):
bleat = super(Bleat, cls).findOne(where)
if bleat:
cur = db.cursor()
cur.execute("select user from bleat_mention where bleat = ?",
(bleat.id, ))
setattr(bleat, "mentions",
set(map(lambda row: row["user"], cur.fetchall())))
cur.execute("select file from bleat_attachment where bleat = ?",
(bleat.id, ))
setattr(bleat, "attachments",
set(map(lambda row: row["file"], cur.fetchall())))
return bleat
def delete(cls, where):
where = cls._buildWhereClause(where)
cur = db.cursor()
cur.execute(
"delete from {0} where {1}".format(
cls._toTableName(cls.__name__),
" and ".join(where.keys()),
),
where.values()
)
return cur.rowcount
def populate(self, attribute):
cur = db.cursor()
if attribute == "bleats":
cur.execute("select id from bleat where user = ? order by timestamp desc", (self.id,))
setattr(self, "bleats", map(lambda row: row["id"], cur.fetchall()))
elif attribute == "listeningTo":
cur.execute("select to_ from user_listen where by = ?", (self.id,))
setattr(self, "listeningTo", set(map(lambda row: row["to_"], cur.fetchall())))
elif attribute == "listenedBy":
cur.execute("select by from user_listen where to_ = ?", (self.id,))
setattr(self, "listenedBy", set(map(lambda row: row["by"], cur.fetchall())))
else:
raise LookupError("User models do not contain {0} relations".format(attribute))
def create(cls, properties):
properties["id"] = base64.b64encode(os.urandom(16), "-_")
properties["csrf_token"] = base64.b64encode(os.urandom(16), "-_")
cur = db.cursor()
cur.execute(
"insert into session ({0}) values ({1})".format(
", ".join(map(cls._toTableName, properties.keys())),
", ".join(["?"] * len(properties))
),
properties.values()
)
return cls.findOne({"rowid": cur.lastrowid})
def update(cls, where, update):
where = cls._buildWhereClause(where)
cur = db.cursor()
cur.execute(
"update {0} set {1} where {2}".format(
cls._toTableName(cls.__name__),
", ".join(map(lambda key: cls._toTableName(key) + " = ?", update.keys())),
" and ".join(where.keys()),
),
update.values() + where.values()
)
return cur.rowcount
def update(cls, where, update):
where = cls._buildWhereClause(where)
cur = db.cursor()
cur.execute(
"update {0} set {1} where {2}".format(
cls._toTableName(cls.__name__),
", ".join(
map(lambda key: cls._toTableName(key) + " = ?",
update.keys())),
" and ".join(where.keys()),
),
update.values() + where.values())
return cur.rowcount
def create(cls, properties):
cur = db.cursor()
if properties:
cur.execute(
"insert into {0} ({1}) values ({2})".format(
cls._toTableName(cls.__name__),
", ".join(map(cls._toTableName, properties.keys())),
", ".join(["?"] * len(properties))
),
properties.values()
)
else:
cur.execute("insert into {0} default values".format(cls._toTableName(cls.__name__)))
return cls.findOne({"id": cur.lastrowid})
def update(cls, where, update):
where = cls._buildWhereClause(where)
cur = db.cursor()
cur.execute(
"select id from user where {0}".format(" and ".join(where.keys())),
where.values()
)
ids = map(lambda row: row["id"], cur.fetchall())
if len(ids) == 0:
return
if "username" in update:
update["canonicalUsername"] = canonicaliseUsername(update["username"], throws = False)
if "password" in update:
update["passwordHash"] = hashPassword(update.pop("password"))
if "listeningTo" in update:
cur.execute("delete from user_listen where by in ({0})".format(", ".join(["?"] * len(ids))), ids)
if update["listeningTo"]:
cur.execute(
"insert into user_listen (by, to_) values {0}".format(", ".join(["(?, ?)"] * len(update["listeningTo"]))),
list(itertools.chain.from_iterable(itertools.product(ids, update["listeningTo"])))
)
del update["listeningTo"]
if "listenedBy" in update:
cur.execute("delete from user_listen where to_ in ({0})".format(", ".join(["?"] * len(ids))), ids)
if update["listenedBy"]:
cur.execute(
"insert into user_listen (by, to_) values {0}".format(", ".join(["(?, ?)"] * len(update["listenedBy"]))),
list(itertools.chain.from_iterable(itertools.product(update["listenedBy"], ids)))
)
del update["listenedBy"]
cur.execute(
"update user set {0} where id in ({1})".format(
", ".join(map(lambda key: cls._toTableName(key) + " = ?", update.keys())),
", ".join(["?"] * len(ids)),
),
update.values() + ids
)
return cur.rowcount
def paginate(cls, where={}, orderBy=_sentinel, page=1, perPage=20):
if orderBy is cls._sentinel:
orderBy = cls.defaultOrderBy
cur = db.cursor()
query = ["select *", "from {0}".format(cls._toTableName(cls.__name__))]
if where:
where = cls._buildWhereClause(where)
query.append("where {0}".format(" and ".join(where.keys())))
if orderBy:
query.append("order by {0}".format(orderBy))
if perPage:
# Get total number of records so we can work out pages
cur.execute(" ".join(["select count(*)"] + query[1:]),
where.values())
totalRecords = cur.fetchone()[0]
query.append("limit {0:d}".format(perPage))
if page > 1:
query.append("offset {0:d}".format(perPage * (page - 1)))
cur.execute(" ".join(query), where.values())
records = map(cls, cur.fetchall())
if not perPage:
totalRecords = len(records)
totalPages = (
totalRecords +
(perPage - 1)) / perPage if perPage else 1 # Ceiling division
return {
"records": records,
"page": page,
"totalRecords": totalRecords,
"totalPages": totalPages
}
def paginate(cls, where = {}, orderBy = _sentinel, page = 1, perPage = 20):
if orderBy is cls._sentinel:
orderBy = cls.defaultOrderBy
cur = db.cursor()
query = [
"select *",
"from {0}".format(cls._toTableName(cls.__name__))
]
if where:
where = cls._buildWhereClause(where)
query.append("where {0}".format(" and ".join(where.keys())))
if orderBy:
query.append("order by {0}".format(orderBy))
if perPage:
# Get total number of records so we can work out pages
cur.execute(" ".join(["select count(*)"] + query[1:]), where.values())
totalRecords = cur.fetchone()[0]
query.append("limit {0:d}".format(perPage))
if page > 1:
query.append("offset {0:d}".format(perPage * (page - 1)))
cur.execute(" ".join(query), where.values())
records = map(cls, cur.fetchall())
if not perPage:
totalRecords = len(records)
totalPages = (totalRecords + (perPage - 1)) / perPage if perPage else 1 # Ceiling division
return {
"records": records,
"page": page,
"totalRecords": totalRecords,
"totalPages": totalPages
}
