def remake_table(conn, doctypeobj):
"""drop table and remake it, backing up the data first"""
import utils, os
name = doctypeobj.get('name')
data = conn.sql("""select * from `%s`""" % wn.cs(name), as_dict=1)
fname = utils.random_string(15) + '.txt'
with open(fname, 'w') as tmpfile:
tmpfile.write(str(data))
conn.sql("""set foreign_key_checks=0""")
conn.sql("""drop table `%s`""" % wn.cs(name))
make_table(doctypeobj)
conn.sql("""set foreign_key_checks=0""")
with open(fname, 'r') as tmpfile:
mega_list = eval(tmpfile.read())
for m in mega_list:
conn.begin()
conn.insert(m)
conn.commit()
conn.sql("""set foreign_key_checks=1""")
os.remove(fname)
def get_list(self, doctype, filters=None, start=None, limit=None, sortinfo=None,
all_properties = False):
"""return a list of records, filtered, with listable properties"""
if not filters: filters, filter_vals = '', None
if not start: start = 0
if not limit: limit = 20
if not sortinfo: sortinfo = ''
listables = all_properties and '*' or \
', '.join(wn.model.get('DocType', doctype).get_listables())
if filters:
filters, filter_vals = self.convert_to_conditions(filters)
filters = ' where ' + filters
if sortinfo:
sortinfo = ' sort by ' + ','.join(sortinfo)
doctype = wn.cs(doctype)
query = """select %(listables)s from `%(doctype)s`
%(filters)s limit %(start)s, %(limit)s %(sortinfo)s""" % locals()
return self.sql(query, filter_vals)
def insert(self, doc):
"""insert dict like object in database where property `doctype` is the table"""
import warnings
with warnings.catch_warnings(record=True) as w:
obj = self.filter_columns(doc)
self.sql("""insert into `%s` (`%s`) values (%s)""" % (wn.cs(doc.get('doctype')),
'`, `'.join(obj.keys()), (', %s' * len(obj.keys()))[2:]), obj.values())
# raise exception if mandatory is not
if w and (str(w[0].message).endswith('default value')):
raise wn.ValidationError, str(w[0].message)
def get(self, doctype, name=None):
"""get list of records from the backend,
pass filters in a dict or using (`doctype`, `name`)
add the "doctype" property by default
"""
if isinstance(doctype, basestring):
rec = self.sql("""select * from `%s` where name=%s""" % (wn.cs(doctype), '%s'), name)
if rec:
rec[0]['doctype'] = doctype
rec = rec + self.get_children(rec[0])
return rec
else:
filters = doctype
conditions, values = [], []
for key in filters:
if key=='doctype':
doctype = filters[key]
else:
conditions.append('`'+key+'`=%s')
values.append(filters[key])
return [d.update('doctype', filters['doctype']) for d in \
self.sql("""select * from `%s` where %s""" % (wn.cs(doctype),
' and '.join(conditions)), values)]
def create_table(conn, doctypeobj):
"""make table based on given info"""
import wn
template = """create table `%s` (%s) ENGINE=InnoDB CHARACTER SET=utf8"""
columns, constraints = [], []
def column_def():
"""make col definition from docfield"""
if not d.get('fieldtype').lower() in type_map:
return
column_def.db_type, column_def.db_length = type_map[d.get(
"fieldtype").lower()]
def set_length():
"""set length if specifed or take default"""
if column_def.db_length or d.get("length"):
column_def.db_length = '(%s)' % str(column_def.db_length
or d.get("length"))
def set_defaults():
"""set numeric types to default to 0"""
if d.get('fieldtype').lower() in (
'int', 'float', 'currency',
'check') and d.get('default') == None:
d['default'] = 0
def set_as_primary_key():
"""set name as primary key unless specified as an index"""
if d.get('fieldname') == 'name' and (not 'name' in doctypeobj.get(
'indexes', [])):
args['keys'] = ' primary key'
# auto_increment
if doctypeobj.get('autoname', '').lower() == 'autonumber':
args['fieldtype'] = 'mediumint'
args['length'] = ''
args['keys'] += ' auto_increment'
def make_args():
"""make column def commands"""
return {
"fieldtype": column_def.db_type,
"length": column_def.db_length,
"fieldname": d.get('fieldname'),
"default": d.get("default")!=None and (' not null default "%s"' %\
str(d.get('default')).replace('"', '\"')) or '',
"keys": '',
"not_null": d.get('reqd') and ' not null' or ''
}
def add_constraints():
"""add constraints for links and indexes"""
# constraints
if d.get('fieldtype') == 'Link':
constraints.append('constraint foreign key `%s`(`%s`) references `%s`(name)' % \
(d.get('fieldname'), d.get('fieldname'), d.get('options')))
if d.get('index'):
constraints.append('index `%s`(`%s`)' %
(d.get('fieldname'), d.get('fieldname')))
# start scrubbing
set_length()
set_defaults()
args = make_args()
set_as_primary_key()
add_constraints()
# add to columns
columns.append(
'`%(fieldname)s` %(fieldtype)s%(length)s%(not_null)s%(default)s%(keys)s'
% args)
# add std columns
if doctypeobj.get("std_fields") != "No":
for d in std_columns:
column_def()
# is table
for d in (doctypeobj.get('istable') and std_columns_table
or std_columns_main):
column_def()
# fields
for d in doctypeobj.get({"doctype": "DocField"}):
column_def()
# indexes
for i in doctypeobj.get('indexes', []):
constraints.append("index `%s`(%s)" % (wn.cs(i), i))
# run the query!
query = template % (wn.cs(
doctypeobj.get('name')), ',\n'.join(columns + constraints))
#print query
conn.sql("""set foreign_key_checks=0""")
try:
if wn.cs(doctypeobj.get('name')) in conn.get_tables():
conn.sql("""drop table `%s`""" % wn.cs(doctypeobj.get('name')))
conn.sql(query)
finally:
conn.sql("""set foreign_key_checks=1""")
def get_columns(self, table):
"""get table columns"""
if not table in self.column_map:
self.column_map[table] = [c[0] for c in self.sql("desc `%s`" % wn.cs(table), as_list=True)]
return self.column_map[table]
def get_value(self, doctype, name, key, default=None):
"""get single value"""
res = self.sql("""select `%s` from `%s` where name=%s""" % (key, wn.cs(doctype), '%s'),
name)
return res and res[0].get(key, default) or default
def exists(self, doctype, name):
"""return true if record exists"""
return self.sql("""select count(*) from `%s` where name=%s""" % (wn.cs(doctype), '%s'),
name, as_list=1)[0][0] and True or False
def update(self, doc):
"""update dict like object in database where property `doctype` is the table"""
obj = self.filter_columns(doc)
self.sql("""update `%s` set %s where name=%s""" % (wn.cs(doc.get('doctype')),
', '.join(["`%s`=%s" % (key, '%s') for key in obj.keys()]), '%s'),
obj.values() + [obj.get('name')])
