def xls_to_dict(xls_filename, pages=None, skip=False):
"""Make JSON out of an XLS sheet of device definitions."""
import xlrd
xls = xlrd.open_workbook(xls_filename)
definitions = AppendingDict()
if not pages: # if no pages given, assume all pages are wanted
pages = xls.sheet_names()
else:
# Always include Dynamics and ParamConfig as they only add stuff
# to devices already configured anyway.
if "Dynamics" not in pages and "Dynamics" in xls.sheet_names():
pages.append("Dynamics")
if "ParamConfig" not in pages and "ParamConfig" in xls.sheet_names():
pages.append("ParamConfig")
for page in pages:
print >> sys.stderr, "\nPage: %s" % page
sheet = xls.sheet_by_name(page)
rows = [sheet.row_values(i) for i in xrange(sheet.nrows)]
if not rows:
continue # ignore empty pages
errors = convert(rows,
definitions,
skip=skip,
dynamic=(page == "Dynamics"),
config=(page == "ParamConfig"))
print_errors(errors)
return definitions
def get_attribute_properties(row):
if "attribute" in row:
attribute = row["attribute"]
prop_dict = AppendingDict()
if "attributeproperties" in row:
properties = row["attributeproperties"]
try:
for prop in properties.split(";"):
name, value = prop.split("=")
name = name.strip()
if name not in SPECIAL_ATTRIBUTE_PROPERTIES:
raise ValueError(
"'%s' is not a valid attribute property" % name)
value = value.decode("string-escape") # for linebreaks
prop_dict[name.strip()] = [
v.strip() for v in value.split("\n")
]
except ValueError:
raise ValueError("could not parse AttributeProperties")
for col_name, value in row.items():
match = re.match("attrprop:(.*)", col_name, re.IGNORECASE)
if match and value:
name, = match.groups()
name = make_db_name(name.strip())
if name not in SPECIAL_ATTRIBUTE_PROPERTIES:
raise ValueError("'%s' it not a valid attribute property" %
name)
value = str(value).decode("string-escape")
values = [v.strip() for v in value.split("\n")]
prop_dict[name] = values
return {attribute: prop_dict}
def get_device_attribute_property_values(dbproxy, device, name="*"):
query = ("SELECT attribute, name, value FROM property_attribute_device "
"WHERE device = '%s' AND name LIKE '%s'")
_, result = dbproxy.command_inout("DbMySqlSelect",
query % (device, name.replace("*", "%")))
data = AppendingDict()
for attr, prop, row in izip(result[::3], result[1::3], result[2::3]):
data[attr][prop] = row
return data
def get_classes_properties(dbproxy,
server='*',
cls_properties=True,
cls_attribute_properties=True,
timeout=10):
"""
Get all classes properties from server wildcard
"""
# Mysql wildcards
server = server.replace("*", "%")
# Change device proxy timeout
dbproxy.set_timeout_millis(timeout * 1000)
# Classes output dict
classes = AppendingDict()
# Get class properties
if cls_properties:
querry = ("select DISTINCT property_class.class, "
"property_class.name, "
"property_class.value "
"FROM property_class "
"INNER JOIN device "
"ON property_class.class = device.class "
"WHERE server like '%s' "
"AND device.class != 'DServer' "
"AND device.class != 'TangoAccessControl'")
_, result = dbproxy.command_inout("DbMySqlSelect", querry % (server))
# Build the output based on: class, property: value
for c, p, v in nwise(result, 3):
# the properties are encoded in latin-1; we want utf-8
decoded_value = v.decode('iso-8859-1').encode('utf8')
classes[c].properties[p] = decoded_value
# Get class attribute properties
if cls_attribute_properties:
querry = ("select DISTINCT property_attribute_class.class, "
"property_attribute_class.attribute, "
"property_attribute_class.name, "
"property_attribute_class.value "
"FROM property_attribute_class "
"INNER JOIN device "
"ON property_attribute_class.class = device.class "
"WHERE server like '%s' "
"AND device.class != 'DServer' "
"AND device.class != 'TangoAccessControl'")
_, result = dbproxy.command_inout("DbMySqlSelect", querry % (server))
# Build output: class, attribute, property: value
for c, a, p, v in nwise(result, 4):
# the properties are encoded in latin-1; we want utf-8
decoded_value = v.decode('iso-8859-1').encode('utf8')
classes[c].attribute_properties[a][p] = decoded_value
# Return classes collection
return classes
def get_properties(row):
"Find property definitions on a row"
prop_dict = AppendingDict()
# "Properties" column
# The cell value is expected to be on the form
# "property1=value1;property2=value2" etc
# Note: In this case we cannot know the type of the value so we will
# use the string as it is. This should be safe for e.g. numbers,
# as long as the format of the string is correct
if "properties" in row:
properties = row["properties"]
try:
for prop in properties.split(";"):
name, value = prop.split("=")
# need to decode the string, otherwise any linebreaks
# will be escaped.
value = value.decode("string-escape")
# Support inline multiline properties using "\n"
prop_dict[name.strip()] = [
v.strip() for v in value.split("\n")
]
except ValueError:
raise ValueError("could not parse Properties")
# "Property:xyz" and "Property(type):xyz columns
# The main issue here is that spreadsheet programs treat numeric cells
# as floats. If the number must be inserterd as an int, use the "(INT)"
# modifier. There does not seem to be a way to force a numeric cell to
# be interpreted as a string.
for col_name, value in row.items():
match = re.match("property(?:\((.*)\))?:(.*)", col_name, re.IGNORECASE)
if match and (value is not None): # protect against zero, false...
type_, name = match.groups()
if type_:
convert = TYPE_MAPPING[type_]
values = [convert(value)]
else:
value = str(value).decode("string-escape")
values = [v.strip() for v in value.split("\n")]
prop_dict[name] = values
return prop_dict
def get_dynamic(row):
"Find dynamic definitions on a row"
prop_dict = AppendingDict()
try:
formula = row["formula"].strip()
if "type" in row:
# TODO: Sanity check type?
formula = "%s(%s)" % (row["type"], formula)
check_formula(formula)
mode = str(row["mode"])
if mode.lower() == "status":
dyn = formula
else:
dyn = "%s=%s" % (row["name"], formula)
prop_dict[MODE_MAPPING[mode]] = dyn
except KeyError as e:
raise ValueError("Problem with formula: %s" % e)
return prop_dict
def get_servers_with_filters(dbproxy, server="*", clss="*", device="*",
properties=True, attribute_properties=True,
aliases=True, dservers=False,
subdevices=False, uppercase_devices=False,
timeout=10):
"""
A performant way to get servers and devices in bulk from the DB
by direct SQL statements and joins, instead of e.g. using one
query to get the properties of each device.
TODO: are there any length restrictions on the query results? In
that case, use limit and offset to get page by page.
"""
server = server.replace("*", "%") # mysql wildcards
clss = clss.replace("*", "%")
device = device.replace("*", "%")
devices = AppendingDict()
# Queries can sometimes take more than de default 3 s, so it's
# good to increase the timeout a bit.
# TODO: maybe instead use automatic retry and increase timeout
# each time?
dbproxy.set_timeout_millis(timeout*1000)
if properties:
# Get all relevant device properties
query = (
"SELECT device, property_device.name, property_device.value"
" FROM property_device"
" INNER JOIN device ON property_device.device = device.name"
" WHERE server LIKE '%s' AND class LIKE '%s' AND device LIKE '%s'")
if not dservers:
query += " AND class != 'DServer'"
if not subdevices:
query += " AND property_device.name != '__SubDevices'"
_, result = dbproxy.command_inout("DbMySqlSelect",
query % (server, clss, device))
for d, p, v in nwise(result, 3):
# the properties are encoded in latin-1; we want utf-8
decoded_value = v.decode('iso-8859-1').encode('utf8')
devices[maybe_upper(d, uppercase_devices)].properties[p] = decoded_value
if attribute_properties:
# Get all relevant attribute properties
query = (
"SELECT device, attribute, property_attribute_device.name,"
" property_attribute_device.value"
" FROM property_attribute_device"
" INNER JOIN device ON property_attribute_device.device ="
" device.name"
" WHERE server LIKE '%s' AND class LIKE '%s' AND device LIKE '%s'")
if not dservers:
query += " AND class != 'DServer'"
_, result = dbproxy.command_inout("DbMySqlSelect", query % (server, clss, device))
for d, a, p, v in nwise(result, 4):
dev = devices[maybe_upper(d, uppercase_devices)]
# the properties are encoded in latin-1; we want utf-8
decoded_value = v.decode('iso-8859-1').encode('utf8')
dev.attribute_properties[a][p] = decoded_value
devices = devices.to_dict()
# dump relevant servers
query = (
"SELECT server, class, name, alias FROM device"
" WHERE server LIKE '%s' AND class LIKE '%s' AND name LIKE '%s'")
if not dservers:
query += " AND class != 'DServer'"
_, result = dbproxy.command_inout("DbMySqlSelect", query % (server, clss, device))
# combine all the information we have
servers = SetterDict()
for s, c, d, a in nwise(result, 4):
try:
srv, inst = s.split("/")
except ValueError:
# Malformed server name? It can happen!
continue
devname = maybe_upper(d, uppercase_devices)
device = devices.get(devname, {})
if a and aliases:
device["alias"] = a
servers[srv][inst][c][devname] = device
return servers