def product(id):
cursor = DB.cursor(dictionary=True)
cursor.execute(
"SELECT p.*, COUNT(o.id) AS opinions FROM products p LEFT JOIN opinions o ON p.id = o.product_id WHERE p.id=%s",
(id, ))
product = cursor.fetchall()
cursor.execute("SELECT * FROM opinions WHERE product_id=%s", (id, ))
opinions = cursor.fetchall()
for opinion in opinions:
cursor.execute("SELECT text from pros WHERE opinion_id = %s",
(opinion['id'], ))
pros = cursor.fetchall()
opinion['pros'] = pros
for opinion in opinions:
cursor.execute("SELECT text from cons WHERE opinion_id = %s",
(opinion['id'], ))
cons = cursor.fetchall()
opinion['cons'] = cons
return render_template("product.html",
subname=f"Produkt - {id} - {product[0]['name']}",
id=id,
mod="product",
opinions=opinions,
product=product[0])
def on_load(self):
self._item_ids = set()
self._item_types = {}
self._changes = set()
# Now load the contents of the container
my_id = self.identity()
cur = DB.cursor()
cur.execute(
'SELECT item.id, item.type' + ' FROM item, item_owner' +
' WHERE item.id = item_owner.item_id' +
' AND item_owner.owner_type = %(owner_type)s' +
' AND item_owner.owner_id = %(owner_id)s' +
' AND item_owner.container = %(container)s', {
'owner_type': my_id[0],
'owner_id': my_id[1],
'container': my_id[2]
})
row = cur.fetchone()
while row is not None:
# Update the list by ID
self._item_ids.add(row[0])
# Update the list by name
iset = self._item_types.setdefault(row[1], set())
iset.add(row[0])
row = cur.fetchone()
def load_by_pos(cls, pos, allprops=False):
"""Load a complete location stack by position"""
rpos = repr(pos)
if rpos in Location.cache_by_pos:
stack = Location.cache_by_pos[rpos]
overlays = stack.keys()
overlays.sort()
return stack[overlays[-1]]
cur = DB.cursor()
cur.execute(
'SELECT id, state FROM location ' +
'WHERE x=%(x)s AND y=%(y)s AND layer=%(layer)s ' +
'ORDER BY overlay', pos.as_dict())
row = cur.fetchone()
location = None
while row is not None:
# Load the overlay
tmploc = cls._load_from_row(row, allprops)
# Set up a doubly-linked list
tmploc._underneath = location
if location is not None:
location._above = tmploc
# Push the underlying location down
location = tmploc
row = cur.fetchone()
if location is not None:
location._above = None
return location
def touch_account(accid):
cur = DB.cursor()
cur.execute(
"UPDATE account SET last_seen = %{now}s" +
" WHERE account_id = %{id}s", {
'now': time.time(),
'id': accid
})
def get_id_from_name(name):
cur = DB.cursor()
cur.execute(
"SELECT account_id FROM account" + " WHERE username = %{username}s",
{'username': display_identifier})
row = cur.fetchone()
if row is None:
return None
return row[0]
def set_session_key(accid, key):
cur = DB.cursor()
cur.execute(
"UPDATE account " + " SET key = %{key}s, " +
" last_seen = %{now}s" + " WHERE account_id = %{id}s", {
'key': key,
'id': accid,
'now': time.time()
})
def create_account(name, email, nickname=None):
cur = DB.cursor()
cur.execute(
"INSERT INTO account (username, email, nickname)" +
" VALUES (%{username}s, %{email}s, %{nick}s", {
'username': name,
'email': email,
'nick': nickname
})
def recommendation_ratio(id):
cursor = DB.cursor()
cursor.execute("SELECT recommendation FROM opinions WHERE product_id=%s",
(id, ))
rec = cursor.fetchall()
cursor.close()
return jsonify(rec)
def message(self, message, msg_type='message'):
"""Write a message to the actor's message log"""
cur = DB.cursor()
cur.execute("INSERT INTO actor_message"
+ " (stamp, actor_id, msg_type, message)"
+ " VALUES (%(stamp)s, %(id)s, %(msg_type)s, %(message)s)",
{ 'stamp': time.time(),
'id': self._id,
'msg_type': msg_type,
'message': message })
def opinions_per_stars(id):
cursor = DB.cursor(dictionary=True)
cursor.execute(
"SELECT stars, count(id) AS opinions FROM opinions WHERE product_id=%s GROUP BY stars ORDER BY stars ASC",
(id, ))
stars = cursor.fetchall()
cursor.close()
return jsonify(stars)
def __init__(self):
# Create a new record in the database for this object and get
# its ID
cur = DB.cursor()
cur.execute('SELECT nextval(\'' + self._table + '_id_seq\');')
row = cur.fetchone()
self._id = row[0]
cur.execute(
'INSERT INTO ' + self._table +
' (id, state) VALUES (%(id)s, NULL)', {'id': self._id})
self._setup()
Triggerable.__init__(self)
def log_item_event(etype,
item_id,
other_item=None,
container=None,
orig_quantity=0,
new_quantity=0):
"""Log an item event"""
cur = DB.cursor()
sql_params = {
'stamp': log_ctx.time,
'req': log_ctx.id,
'id': item_id,
'type': etype,
}
if etype == ITEM_MERGE or etype == ITEM_SPLIT:
sql_params['oid'] = other_item
sql_params['orig_q'] = orig_quantity
sql_params['new_q'] = new_quantity
cur.execute(
"INSERT INTO log_item_event" +
" (stamp, request_id, item_id, type," +
" other_id, orig_q, new_q)" +
" VALUES (%(stamp)s, %(req)s, %(id)s, %(type)s," +
" %(oid)s, %(orig_q)s, %(new_q)s)", sql_params)
Logger.log.info(
(Logger.header + "ITEM %(type)s %(id)d with %(oid)d") % sql_params)
elif etype == ITEM_ADD or etype == ITEM_REMOVE:
cid = container.identity()
sql_params['ctyp'] = cid[0]
sql_params['cid'] = cid[1]
sql_params['cname'] = cid[2]
cur.execute(
"INSERT INTO log_item_event" +
" (stamp, request_id, item_id, type, " +
" owner_type, owner_id, container)" +
" VALUES (%(stamp)s, %(req)s, %(id)s, %(type)s," +
" %(ctyp)s, %(cid)s, %(cname)s)", sql_params)
if etype == ITEM_ADD:
sql_params['preposition'] = "into"
else:
sql_params['preposition'] = "from"
Logger.log.info(
(Logger.header +
"ITEM %(type)s %(id)d %(preposition)s %(ctyp)s/%(cid)d/%(cname)s")
% sql_params)
elif etype == ITEM_CREATE or etype == ITEM_DESTROY:
cur.execute(
"INSERT INTO log_item_event" +
" (stamp, request_id, item_id, type)" +
" VALUES (%(stamp)s, %(req)s, %(id)s, %(type)s)", sql_params)
Logger.log.info((Logger.header + "ITEM %(type)s %(id)d") % sql_params)
def charts(id):
cursor = DB.cursor(dictionary=True)
cursor.execute(
"SELECT p.*\
FROM products p\
WHERE p.id=%s", (id, ))
product = cursor.fetchall()
cursor.close()
return render_template(
"charts.html",
subname=f"Wykresy produktu - {product[0]['id']} - {product[0]['name']}",
mod="charts",
product=product[0])
def load_by_name(cls, name, allprops=False):
"""Additional function to load a player by name instead of by
ID"""
if name in cls.cache_by_name:
return cls.cache_by_name[name]
cur = DB.cursor()
cur.execute("SELECT id FROM " + cls._table + " WHERE name = %(name)s",
{'name': name})
row = cur.fetchone()
if row is None:
return None
#log.debug("Loading player %s (=%d)" % (name, row[0]))
return cls.load(row[0], allprops)
def _demand_load_property(self, key):
cur = DB.cursor()
cur.execute(
'SELECT key, type, ivalue, fvalue, tvalue' + ' FROM ' +
self._table + '_properties' + ' WHERE ' + self._table +
'_id = %(id)s' + ' AND key = %(key)s', {
'id': self._id,
'key': key
})
row = cur.fetchone()
if row is not None:
self._set_prop_from_row(row)
return True
else:
return False
def actor_ids(self):
"""Return a list of the actor IDs on this location"""
ret = []
cur = DB.cursor()
cur.execute(
"SELECT id FROM actor" +
" WHERE x=%(x)s AND y=%(y)s AND layer=%(layer)s",
self.pos.as_dict())
row = cur.fetchone()
while row is not None:
ret.append(row[0])
row = cur.fetchone()
return ret
def setUp(self):
GameUtil.flush_cache()
self.cur = DB.cursor()
self.cur.execute("DELETE FROM account")
self.cur.execute("DELETE FROM account_actor")
self.cur.execute("DELETE FROM actor")
self.cur.execute("DELETE FROM actor_message")
self.cur.execute("DELETE FROM actor_properties")
self.cur.execute("DELETE FROM item")
self.cur.execute("DELETE FROM item_owner")
self.cur.execute("DELETE FROM item_properties")
self.cur.execute("DELETE FROM location")
self.cur.execute("DELETE FROM location_properties")
self.cur.execute("DELETE FROM log_raw_action")
DB.commit()
def insert_to_database(self):
cursor = DB.cursor()
query = "INSERT INTO products (id, name, subname, price, score)\
VALUES(%s, %s, %s, %s, %s)"
values = (self.id, self.name, self.subname, self.price, self.score)
try:
cursor.execute(query, values)
except mysql.connector.errors.DataError:
pass
except mysql.connector.errors.IntegrityError:
pass
self.opinions.insert_to_database()
DB.commit()
def products():
cursor = DB.cursor(dictionary=True)
cursor.execute("SELECT p.*, COUNT(o.id) AS opinions,\
COUNT(pr.text) AS pros, COUNT(c.text) AS cons\
FROM products p\
LEFT JOIN opinions o ON p.id = o.product_id\
LEFT JOIN pros pr ON pr.opinion_id = o.id\
LEFT JOIN cons c ON c.opinion_id = o.id\
GROUP BY p.id")
products = cursor.fetchall()
print(products)
return render_template("products.html",
subname="Lista produktów",
mod="products",
products=products)
def save(self):
"""Called when its parent object is save()d"""
# Update the database, using our list of changed fields
cur = DB.cursor()
params = {}
my_id = self.identity()
params['owner_type'] = my_id[0]
params['owner_id'] = my_id[1]
params['container'] = my_id[2]
for itemid in self._changes:
params['id'] = itemid
if itemid in self._item_ids:
# The item has been added to this container, so we
# need to add it to the database
try:
# We try to insert. If it fails, we've already got
# it in here.
cur.execute('SAVEPOINT item_update')
cur.execute(
'INSERT INTO item_owner' +
' (item_id, owner_type, owner_id, container)' +
' VALUES (%(id)s, %(owner_type)s,' +
' %(owner_id)s, %(container)s)', params)
except psycopg2.Error, ex:
# If the insert failed, we roll back the savepoint
# just to keep it all sane.
cur.execute('ROLLBACK TO SAVEPOINT item_update')
else:
# If the insert succeeded, we close the
# savepoint. If it failed, we've rolled it back
# already.
cur.execute('RELEASE SAVEPOINT item_update')
else:
# The item is no longer in this container, so we need
# to delete it from the database
cur.execute(
'DELETE FROM item_owner' + ' WHERE item_id = %(id)s' +
' AND owner_type = %(owner_type)s' +
' AND owner_id = %(owner_id)s' +
' AND container_name = %(container)s', params)
# Make sure to remove the item from our internal
# collections as well
self._item_ids.remove(item_id)
for item_ids in self._item_types.values():
if item_id in item_ids:
item_ids.remove(item_id)
def get_messages(self, since):
"""Get messages from this actor's message log"""
cur = DB.cursor()
cur.execute("SELECT stamp, msg_type, message"
+ " FROM actor_message"
+ " WHERE stamp >= %(since)s"
+ " AND actor_id = %(id)s"
+ " ORDER BY stamp DESC"
+ " LIMIT %(limit)s",
{ 'since': since,
'id': self._id,
'limit': 1024 })
result = []
row = cur.fetchone()
while row is not None:
result.append(row)
row = cur.fetchone()
return result
def _load_from_row(cls, row, allprops):
obj = pickle.loads(row[1])
if allprops:
# Fetch all properties of the object immediately (rather
# than on demand)
try:
cur = DB.cursor()
cur.execute(
'SELECT key, type, ivalue, fvalue, tvalue' + ' FROM ' +
cls._table + '_properties' + ' WHERE ' + cls._table +
'_id=%(id)s', {'id': row[0]})
prop = cur.fetchone()
while prop is not None:
obj._set_prop_from_row(prop)
prop = cur.fetchone()
except psycopg2.Error, dbex:
exception_log.info("Database exception:" + str(dbex) + "\n")
return None
def load(cls, id, allprops=False):
"""Get the SerObject with the given id from the database"""
#log.debug("Request to load " + str(id) + " from " + cls._table)
if id in cls.cache_by_id:
obj = cls.cache_by_id[id]
#log.debug("... found in cache, returning " + str(obj))
#log.debug("Cache is " + str(cls.cache_by_id))
return obj
try:
cur = DB.cursor()
cur.execute(
'SELECT id, state FROM ' + cls._table + ' WHERE id=%(id)s',
{'id': id})
row = cur.fetchone()
except psycopg2.Error, dbex:
exception_log.info("Database exception:" + str(dbex) + "\n")
return None
def log_raw_action(actid, params):
"""Log a request for an action"""
cur = DB.cursor()
param_pairs = map(lambda k, v: str(k) + "=" + repr(v), params.iterkeys(),
params.iteritems())
sql_params = {
'stamp': log_ctx.time,
'req': log_ctx.id,
'act': actid,
'name': actid.split('.')[2],
'params': param_pairs.join(' ')
}
cur.execute(
"INSERT INTO log_raw_action" +
" (stamp, request_id, action_id, action_name, parameters)" +
" VALUES (%(stamp)s, %(req)s, %(act)s, %(name)s, %(params)s)",
sql_params)
Logger.log.info((header + "ACTION %(act)s %(params)s") % sql_params)
def insert_to_database(self):
cursor = DB.cursor()
query = "INSERT INTO opinions (\
id, product_id, author, recommendation, stars, content,\
useful, useless, purchased, review_date, purchase_date\
)\
VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
values = (self.id, self.product_id, self.author, self.recommendation, self.stars, self.content,
self.useful, self.useless, self.purchased, self.review_date, self.purchase_date)
try:
cursor.execute(query, values)
except mysql.connector.errors.DataError:
pass
except mysql.connector.errors.IntegrityError:
pass
if self.pros is not None:
for pro in self.pros.strip().split("\n"):
query = "INSERT INTO pros (opinion_id, text) VALUES( %s, %s)"
values = (self.id, pro)
try:
cursor.execute(query, values)
print("s")
except mysql.connector.errors.DataError:
pass
except mysql.connector.errors.IntegrityError:
pass
if self.cons is not None:
for con in self.cons.strip().split("\n"):
query = "INSERT INTO cons (opinion_id, text) VALUES( %s, %s)"
values = (self.id, con)
try:
cursor.execute(query, values)
except mysql.connector.errors.DataError:
pass
except mysql.connector.errors.IntegrityError:
pass
DB.commit()
def product_json(id):
cursor = DB.cursor(dictionary=True)
cursor.execute("SELECT * FROM products p WHERE id=%s", (id, ))
product = cursor.fetchall()
cursor.execute("SELECT * FROM opinions WHERE product_id=%s", (id, ))
opinions = cursor.fetchall()
for opinion in opinions:
cursor.execute("SELECT text from pros WHERE opinion_id = %s",
(opinion['id'], ))
pros = cursor.fetchall()
opinion['pros'] = pros
for opinion in opinions:
cursor.execute("SELECT text from cons WHERE opinion_id = %s",
(opinion['id'], ))
cons = cursor.fetchall()
opinion['cons'] = cons
json = {'product': product, 'opinions': opinions}
return jsonify(json)
def api_handler_core(req):
"""This is the core function for the REST API. All requests pass
through this function. It sets up the global AuthZ context for
this request, parses the URL of the request and checks it for
sanity, and then calls a suitable handler for it.
When we reach this function, we have already checked
authentication, so this is partly an authorisation handler."""
# Wrap everything up in an exception handler
try:
req.get_basic_auth_pw() # Get the username as well
account = req.user
components = req.uri.split('/')
if components[0] != '':
# The request didn't even have a leading slash -- something's
# screwy
req.status = apache.HTTP_INTERNAL_SERVER_ERROR
req.write("No leading slash on request for URL '" + req.uri + "'")
return apache.OK
components.pop(0)
if components[0] != 'api':
req.status = apache.HTTP_INTERNAL_SERVER_ERROR
req.write("Incorrect path prefix on request for URL '" + req.uri +
"'")
return apache.OK
components.pop(0)
# We need to set up the logging/request context here
Context.log_ctx.generate()
Logger.log.debug("Request components: " + str(components))
if components[0] == '':
apache.redirect(req, 'http://worldofrodney.org/')
# Does not return
elif components[0] == 'actors':
# Get the list of actors owned by this account
req.content_type = "text/plain"
cur = DB.cursor()
cur.execute(
"SELECT actor.id, actor.name" +
" FROM actor, account_actor, account" +
" WHERE actor.id = account_actor.actor_id" +
" AND account_actor.account_id = account.account_id" +
" AND account.username = %(username)s",
{'username': account})
row = cur.fetchone()
while row is not None:
req.write("id:%d\n" % row[0])
req.write("name:%s\n" % row[1])
req.write("-\n")
row = cur.fetchone()
return apache.OK
elif components[0] == 'items':
# Get information on items: class/name mapping, for example
if components[1] == 'names':
return retry_process(
lambda: hd_item.item_names_handler(req, components[2:]))
else:
return apache.HTTP_NOT_FOUND
else:
act_id = check_actor(req)
player = Player.load(act_id)
if player is None:
req.status = apache.HTTP_FORBIDDEN
req.write("No context found for actor id " + str(act_id))
return apache.OK
if components[0] == 'actor':
if components[1] == 'self':
# We're using self: id is actor
target = act_id
elif components[1].isdigit():
target = int(components[1])
else:
req.status = apache.HTTP_NOT_FOUND
req.write("Actor: actor not found")
req.write(str(components))
return apache.OK
return retry_process(lambda: hd_actor.actor_handler(
req, player, target, components[2:]))
elif components[0] == 'location':
if components[1] == 'here':
target = player.loc()._id
elif components[1] == 'neighbourhood':
target = player.loc()._id
return retry_process(
lambda: hd_location.neighbourhood_handler(
req, player, target, components[2:]))
elif components[1].isdigit():
target = int(components[1])
else:
req.status = apache.HTTP_NOT_FOUND
req.write("Location: location not found")
return apache.OK
return retry_process(lambda: hd_location.location_handler(
req, player, target, components[2:]))
elif components[0] == 'item':
if components[1].isdigit():
target = int(components[1])
else:
req.status = apache.HTTP_NOT_FOUND
req.write("Item: item not found")
return apache.OK
return retry_process(lambda: hd_item.item_handler(
req, player, target, components[2:]))
else:
# It's not one of our expected URIs
return apache.HTTP_NOT_FOUND
except apache.SERVER_RETURN, ex:
# Catch and re-raise apache/mod_python exceptions here
raise
def save(self, force=False):
"""Save to the database the parts of the object that have changed"""
if not force and not self._changed:
return
if self._deleted:
cur = DB.cursor()
cur.execute('DELETE FROM ' + self._table + ' WHERE id=%(id)s',
{'id': self._id})
self._changed = False
return
#log.debug("save " + self.ob_type() + str(self._id) + ": changed is " + str(self._changed_props))
#log.debug("Full dump: " + str(self.__dict__))
# The only time pickle() gets called is during save. We set up
# for that event by constructing a set of property names that
# we should pickle (rather than dump to the database table)
self._pickle = set()
# First, we iterate through the elements of the object that
# have changed and save them. Anything which is not an atomic
# type is punted for later
params = {'id': self._id}
cur = DB.cursor()
for key in self.__dict__.iterkeys():
params['key'] = key
params['value'] = self.__dict__[key]
# Skip properties which begin with _
if key[0] == '_':
continue
# Ignore/delete properties which are None
if self.__dict__[key] is None:
cur.execute(
'DELETE FROM ' + self._table + '_properties' + ' WHERE ' +
self._table + '_id=%(id)s' + ' AND key=%(key)s', params)
continue
# Work out the type (and hence the DB serialisation) of
# the object we're looking at
typ = type(self.__dict__[key])
if typ is int:
params['type'] = 'i'
value_field = 'ivalue'
elif typ is float:
params['type'] = 'f'
value_field = 'fvalue'
elif typ is str or typ is unicode:
params['type'] = 't'
value_field = 'tvalue'
elif typ is bool:
params['type'] = 'b'
value_field = 'ivalue'
elif issubclass(typ, SerObject):
# This is a Bad Thing: complain about it lots
log.error(
"The attribute '%s' contains a SerObject (%s). This combination cannot be pickled. Your attribute has not been stored at all. You should fix this as soon as possible."
% (key, str(self.__dict__[key])))
elif hasattr(self.__dict__[key], 'save'):
# If the object has its own save() method, call that
# as well, but still pickle it
self.__dict__[key].save()
self._pickle.add(key)
continue
else:
# It's not an atomic type that we know about, or which
# wants to handle itself, so we're going to pickle this
# property into the central store, not write to the DB
self._pickle.add(key)
continue
# If the key wasn't changed, we don't need to do anything
if key not in self._changed_props:
continue
# At this point, we've got an atomic type we understand
# and can put into *_properties, so we do so. The
# following code is idiomatic for an insert-or-update in
# postgres (as per
# http://www.postgresql.org/docs/8.3/static/sql-update.html).
cur.execute('SAVEPOINT update1')
try:
# Try an insert first.
sql = 'INSERT INTO ' + self._table + '_properties'
sql += ' (' + self._table + '_id, key, '
sql += 'type, ' + value_field + ')'
sql += 'VALUES (%(id)s, %(key)s, %(type)s, %(value)s)'
cur.execute(sql, params)
except psycopg2.Error, ex:
# If the insert failed (due to a primary key
# uniqueness violation), skip back and try an update
# instead
cur.execute('ROLLBACK TO SAVEPOINT update1')
cur.execute(
'UPDATE ' + self._table + '_properties' + ' SET ' +
value_field + ' = %(value)s,' + ' type = %(type)s' +
' WHERE ' + self._table + '_id = %(id)s' +
' AND key = %(key)s', params)
else:
# If the insert succeeded, we need to tie off the
# savepoint. If it failed, we don't have to do this,
# as it was rolled back in the except: section above.
cur.execute('RELEASE SAVEPOINT update1')
sql = 'UPDATE ' + self._table + ' SET state=%(state)s'
# We find out what additional indices we need to write to the
# DB, and construct some SQL for them
idx = self._save_indices()
for iname in idx.iterkeys():
sql += ', %(iname)s=%%(%(iname)s)s' % {'iname': iname}
sql += ' WHERE id=%(identity)s'
# Add in the required parameters of identity and state
idx['identity'] = self._id
idx['state'] = psycopg2.Binary(state)
# Update the DB
cur = DB.cursor()
cur.execute(sql, idx)
self._changed = False
def _save_indices(self):
"""Return a dict of DB field names and values to write as
additional indexes to the database"""
return {}
@classmethod
def save_cache(cls):
for oid, obj in cls.cache_by_id.iteritems():
obj.save()
####