def __init__(self, name, data):
self.name = name
self.table = get_table(name)
self.method = data.pop("method", "create").lower()
self.data = data
# updated during the generation of queries to indicate which SQL
# statement is to be used (e.g. INSERT or UPDATE with the MERGE method)
self.stmt_used = None
self.process()
def __init__(self, name, data):
self.name = name
self.table = get_table(name)
self.method = data.pop("method", "create").lower()
self.data = data
# updated during the generation of queries to indicate which SQL
# statement is to be used (e.g. INSERT or UPDATE with the MERGE method)
self.stmt_used = None
self.process()
def _query(model, what, where):
"""
Internal. Retrieves the appropriate table from a **model** name, and selects
a single column (**what**) restricted by a dictionary representing the
**WHERE** clause.
"""
table = get_table(model)
if table is None:
log.error("invalid model name: {0}".format(model))
raise
_where = []
for col_name, value in where.items():
_where.append(get_cmp(table, col_name, value)[0])
what = getattr(table.c, what)
if not isinstance(what, list):
what = [what]
query = select(what, and_(*_where))
return query
def validate(self):
"""
Provide rigid validation for YAML schema.
"""
if self.method == "update" and "where" not in self.data:
raise schema.SchemaError("Update method requires where dict")
def _build_all_cols(data, table, use_expr=False):
all_cols = {}
for column in table.columns:
# schema breaks on values expected to be strings which are None
if (column.name in data and column.type.python_type == str
and data[column.name] is None):
data[column.name] = ""
all_cols[schema.Optional(column.name)] = (
column.type.python_type if not use_expr else
lambda o: isinstance(o, (list, _BinaryExpression)))
return all_cols
all_cols = _build_all_cols(self.data, self.table)
if self.method == "update":
where = all_cols["where"] = {}
for col_name, value in all_cols.items():
if col_name == "where": continue
if isinstance(col_name, schema.Optional):
col_name = col_name._schema
value = lambda o: isinstance(o, (list, [(_BinaryExpression, str
)]))
where[schema.Optional(col_name)] = value
schema.Schema(all_cols["where"]).validate(self.data["where"])
all_cols[schema.Optional("comment")] = basestring
all_cols[schema.Optional("items")] = list
all_cols[schema.Optional("merge-from")] = schema.Or(dict, int)
all_cols[schema.Optional("extended_costs")] = [{
schema.Optional("where"):
_build_all_cols(self.data.get("extended_costs", {}),
get_table("ItemTemplate"), True),
"cost":
int
}]
schema.Schema(all_cols).validate(self.data)
def validate(self):
"""
Provide rigid validation for YAML schema.
"""
if self.method == "update" and "where" not in self.data:
raise schema.SchemaError("Update method requires where dict")
def _build_all_cols(data, table, use_expr=False):
all_cols = {}
for column in table.columns:
# schema breaks on values expected to be strings which are None
if (column.name in data and column.type.python_type == str and
data[column.name] is None):
data[column.name] = ""
all_cols[schema.Optional(column.name)] = (
column.type.python_type
if not use_expr
else lambda o: isinstance(o, (list, _BinaryExpression))
)
return all_cols
all_cols = _build_all_cols(self.data, self.table)
if self.method == "update":
where = all_cols["where"] = {}
for col_name, value in all_cols.items():
if col_name == "where": continue
if isinstance(col_name, schema.Optional):
col_name = col_name._schema
value = lambda o: isinstance(o, (list, [(_BinaryExpression, str)]))
where[schema.Optional(col_name)] = value
schema.Schema(all_cols["where"]).validate(self.data["where"])
all_cols[schema.Optional("comment")] = basestring
all_cols[schema.Optional("items")] = list
all_cols[schema.Optional("merge-from")] = schema.Or(dict, int)
all_cols[schema.Optional("extended_costs")] = [{
schema.Optional("where"): _build_all_cols(
self.data.get("extended_costs", {}),
get_table("ItemTemplate"), True),
"cost": int
}]
schema.Schema(all_cols).validate(self.data)
def table(self):
return get_table(self.model)
def query_generator(self):
"""
Generate query objects that can either be printed in SQL form or
executed using a DB connection.
"""
queries = []
where = []
if self.method == "update":
values = {}
for column in self.table.columns:
if column.name in self.data:
values[column] = self.data[column.name]
self.stmt_used = "update"
query = self.table.update().values(values)
where = []
for name, exprs in self.data["where"].items():
binexprs = zip(*exprs)[0]
if len(exprs) > 1:
where.append(or_(*binexprs))
else:
where.append(binexprs[0])
if len(where) > 1:
yield query.where(and_(*where))
else:
yield query.where(*where)
elif self.method == "merge" and "merge-from" in self.data:
merge_from = self.data["merge-from"]
if isinstance(merge_from, dict):
for col_name, value in merge_from.items():
merge_from[col_name] = get_cmp(self.table, col_name,
value)[0]
query = self.table.select().where(*merge_from.values())
else:
query = self.table.select().where({"entry": merge_from})
templ = dict(self.table.bind.execute(query).fetchone())
for col_name, value in self.data.items():
if col_name in templ:
templ[col_name] = value
if (self._execute(
select([
exists().where(self.table.c.entry == templ["entry"])
])).scalar()):
self.stmt_used = "update"
yield self.table.update()\
.where(self.table.c.entry == templ["entry"])\
.values(**templ)
else:
self.stmt_used = "insert"
yield self.table.insert().values(**templ)
elif self.method == "create":
data = dict([(k, v) for k, v in self.data.items()
if k in self.table.columns])
pk = None
for col_name, value in data.items():
if getattr(getattr(self.table.c, col_name), "primary_key"):
pk = col_name
assert pk, "Could not find a primary key for model `{0}`, use "\
"the MERGE or UPDATE methods instead.".format(self.name)
pk_col = getattr(self.table.c, pk)
if (self._execute(select([exists().where(pk_col == data[pk])
])).scalar()):
self.stmt_used = "update"
yield self.table.update()\
.where(pk_col == data[pk])\
.values(**data)
else:
self.stmt_used = "insert"
yield self.table.insert().values(**data)
vendor = get_table("NpcVendor")
entry = self.data["entry"] if "entry" in self.data else None
if "items" in self.data:
if entry is None and "where" in self.data:
query = select([self.table.columns.entry])
where = []
for name, exprs in self.data["where"].items():
binexprs = zip(*exprs)[0]
if len(binexprs) > 1:
where.append(or_(*binexprs))
else:
where.append(binexprs[0])
if len(where) > 1:
query = query.where(and_(*where))
else:
query = query.where(*where)
entry = self._execute(query).fetchone()["entry"]
assert entry is not None, "missing creature_template entry"
slot = 0
yield vendor.delete().where(vendor.columns.entry == entry)
for item in self.data["items"]:
yield vendor.insert().values(
entry=entry,
slot=slot,
item=item,
maxcount=0,
incrtime=0,
# extended costs are handled separately as it is more likely
# one will want to apply extended costs in bulk rather than
# item by item.
ExtendedCost=0)
slot += 1
if "extended_costs" in self.data:
for cost_spec in self.data["extended_costs"]:
item_table = get_table("ItemTemplate")
query = select([item_table.c.entry])
where = []
if "where" in cost_spec:
for name, exprs in cost_spec["where"].items():
binexprs = zip(*exprs)[0]
if len(exprs) > 1:
where.append(or_(*binexprs))
else:
where.append(binexprs[0])
where.append(item_table.c.entry.in_(self.data["items"]))
query = query.where(
and_(*where) if len(where) > 1 else where[0])
item_ids = self._execute(query)
for row in item_ids:
yield vendor.update()\
.values(ExtendedCost=cost_spec["cost"])\
.where(and_(
vendor.c.entry == entry,
vendor.c.item == row[0]
))
def process(self):
"""
Preprocess DBC flags, WHERE clauses, and item lists for vendors.
"""
def _process_where(data, table):
if not "where" in data:
return
for where_col_name, values in data["where"].items():
if not isinstance(values, list):
values = [values]
data["where"][where_col_name] = []
for value in values:
if isinstance(value, SelectQueryBuilder):
rows = flatten(self._execute(value.build()).fetchall())
for field in rows:
data["where"][where_col_name].append(
get_cmp(table, where_col_name, field))
else:
data["where"][where_col_name].append(
get_cmp(table, where_col_name, value))
for col_name, values in self.data.items():
flags = get_flags(col_name, values)
# DBC flags
if flags is not None:
self.data[col_name] = flags
# vendor items
elif col_name == "items" and values is not None:
items = []
item_table = get_table("ItemTemplate")
if not isinstance(values, list):
values = [values]
for item in values:
query_append = None
if isinstance(item, basestring):
if item.endswith("^"):
item = item[:-1]
query_append = lambda q: \
q.order_by(desc(item_table.c.ItemLevel))
query = select([item_table.c.entry])\
.where(get_cmp(item_table, "name", item)[0])
if query_append:
query = query_append(query)
items.append(self._execute(query).fetchone())
if items[-1] is None:
log.error("could not find entry for '%s'" % item)
elif isinstance(item, int):
items.append(item)
elif isinstance(item, SelectQueryBuilder):
items += item_table.bind.execute(
item.build()).fetchall()
self.data["items"] = flatten(items)
elif col_name == "extended_costs":
costs = self.data["extended_costs"]
costs = [costs] if not isinstance(costs, list) else costs
for cost_spec in costs:
_process_where(cost_spec, get_table("ItemTemplate"))
_process_where(self.data, self.table)
def query_generator(self):
"""
Generate query objects that can either be printed in SQL form or
executed using a DB connection.
"""
queries = []
where = []
if self.method == "update":
values = {}
for column in self.table.columns:
if column.name in self.data:
values[column] = self.data[column.name]
self.stmt_used = "update"
query = self.table.update().values(values)
where = []
for name, exprs in self.data["where"].items():
binexprs = zip(*exprs)[0]
if len(exprs) > 1:
where.append(or_(*binexprs))
else:
where.append(binexprs[0])
if len(where) > 1:
yield query.where(and_(*where))
else:
yield query.where(*where)
elif self.method == "merge" and "merge-from" in self.data:
merge_from = self.data["merge-from"]
if isinstance(merge_from, dict):
for col_name, value in merge_from.items():
merge_from[col_name] = get_cmp(self.table, col_name, value)[0]
query = self.table.select().where(*merge_from.values())
else:
query = self.table.select().where({"entry": merge_from})
templ = dict(self.table.bind.execute(query).fetchone())
for col_name, value in self.data.items():
if col_name in templ:
templ[col_name] = value
if (self._execute(select([exists().where(
self.table.c.entry == templ["entry"])])).scalar()):
self.stmt_used = "update"
yield self.table.update()\
.where(self.table.c.entry == templ["entry"])\
.values(**templ)
else:
self.stmt_used = "insert"
yield self.table.insert().values(**templ)
elif self.method == "create":
data = dict([(k, v) for k, v in self.data.items()
if k in self.table.columns])
pk = None
for col_name, value in data.items():
if getattr(getattr(self.table.c, col_name), "primary_key"):
pk = col_name
assert pk, "Could not find a primary key for model `{0}`, use "\
"the MERGE or UPDATE methods instead.".format(self.name)
pk_col = getattr(self.table.c, pk)
if (self._execute(select([exists().where(pk_col == data[pk])])).scalar()):
self.stmt_used = "update"
yield self.table.update()\
.where(pk_col == data[pk])\
.values(**data)
else:
self.stmt_used = "insert"
yield self.table.insert().values(**data)
vendor = get_table("NpcVendor")
entry = self.data["entry"] if "entry" in self.data else None
if "items" in self.data:
if entry is None and "where" in self.data:
query = select([self.table.columns.entry])
where = []
for name, exprs in self.data["where"].items():
binexprs = zip(*exprs)[0]
if len(binexprs) > 1:
where.append(or_(*binexprs))
else:
where.append(binexprs[0])
if len(where) > 1:
query = query.where(and_(*where))
else:
query = query.where(*where)
entry = self._execute(query).fetchone()["entry"]
assert entry is not None, "missing creature_template entry"
slot = 0
yield vendor.delete().where(vendor.columns.entry == entry)
for item in self.data["items"]:
yield vendor.insert().values(
entry=entry,
slot=slot,
item=item,
maxcount=0,
incrtime=0,
# extended costs are handled separately as it is more likely
# one will want to apply extended costs in bulk rather than
# item by item.
ExtendedCost=0
)
slot += 1
if "extended_costs" in self.data:
for cost_spec in self.data["extended_costs"]:
item_table = get_table("ItemTemplate")
query = select([item_table.c.entry])
where = []
if "where" in cost_spec:
for name, exprs in cost_spec["where"].items():
binexprs = zip(*exprs)[0]
if len(exprs) > 1:
where.append(or_(*binexprs))
else:
where.append(binexprs[0])
where.append(item_table.c.entry.in_(self.data["items"]))
query = query.where(and_(*where) if len(where) > 1 else where[0])
item_ids = self._execute(query)
for row in item_ids:
yield vendor.update()\
.values(ExtendedCost=cost_spec["cost"])\
.where(and_(
vendor.c.entry == entry,
vendor.c.item == row[0]
))
def process(self):
"""
Preprocess DBC flags, WHERE clauses, and item lists for vendors.
"""
def _process_where(data, table):
if not "where" in data:
return
for where_col_name, values in data["where"].items():
if not isinstance(values, list):
values = [values]
data["where"][where_col_name] = []
for value in values:
if isinstance(value, SelectQueryBuilder):
rows = flatten(self._execute(value.build()).fetchall())
for field in rows:
data["where"][where_col_name].append(
get_cmp(table, where_col_name, field)
)
else:
data["where"][where_col_name].append(
get_cmp(table, where_col_name, value)
)
for col_name, values in self.data.items():
flags = get_flags(col_name, values)
# DBC flags
if flags is not None:
self.data[col_name] = flags
# vendor items
elif col_name == "items" and values is not None:
items = []
item_table = get_table("ItemTemplate")
if not isinstance(values, list):
values = [values]
for item in values:
query_append = None
if isinstance(item, basestring):
if item.endswith("^"):
item = item[:-1]
query_append = lambda q: \
q.order_by(desc(item_table.c.ItemLevel))
query = select([item_table.c.entry])\
.where(get_cmp(item_table, "name", item)[0])
if query_append:
query = query_append(query)
items.append(self._execute(query).fetchone())
if items[-1] is None:
log.error("could not find entry for '%s'" % item)
elif isinstance(item, int):
items.append(item)
elif isinstance(item, SelectQueryBuilder):
items += item_table.bind.execute(item.build()).fetchall()
self.data["items"] = flatten(items)
elif col_name == "extended_costs":
costs = self.data["extended_costs"]
costs = [costs] if not isinstance(costs, list) else costs
for cost_spec in costs:
_process_where(cost_spec, get_table("ItemTemplate"))
_process_where(self.data, self.table)
