import os
import MySQLdb
from HTMLTags import *
import settings
# file with MySQL connection info : host,user,password
mysql_settings_file = os.path.join(CONFIG.data_dir,'mysql_settings.py')
def _connection():
exec(open(mysql_settings_file).read())
return MySQLdb.connect(host=host,user=user,
passwd=password)
def _cursor():
exec(open(mysql_settings_file).read())
connection = MySQLdb.connect(host=host,user=user,
passwd=password)
return connection.cursor()
def _get_tables(db_name):
cursor = _cursor()
cursor.execute('USE %s' %db_name)
cursor.execute('SHOW TABLES')
return [ table[0] for table in cursor.fetchall() ]
def _get_fields(db_name,table_name):
infos = ('name','type','not_null','key','default','extra')
fields = []
cursor = _cursor()
cursor.execute('USE %s' %db_name)
try:
cursor.execute('DESCRIBE %s' %table_name)
for row in cursor.fetchall():
column = dict(zip(infos,row))
column['format'] = 'default'
column['not_null'] = column['not_null']=='NO'
if column['default'] is None:
column['default'] = ''
fields.append(column)
return fields
except:
return []
def _hidden(**kw):
return Sum([INPUT(Type="hidden",name=key,value=value)
for (key,value) in kw.iteritems()])
def _create_settings_file(db_name,table_name):
exec(open(mysql_settings_file).read())
settings_file = os.path.join(CONFIG.data_dir,'mysql_admin',
host,user,db_name,table_name,'settings.py')
os.makedirs(os.path.dirname(settings_file))
# save default view
settings.Settings(settings_file).save({'views':['default'],
'edit_forms':['default']})
default_view_file = os.path.join(CONFIG.data_dir,'mysql_admin',
host,user,db_name,table_name,'view_default.py')
# default values
fields = _get_fields(db_name,table_name)
field_names = [f['name'] for f in fields]
data = {'records_per_page':30,
'order_by' : '',
'condition':'',
'style' : 'h_table',
'table_headers' : str(Sum([TH(f) for f in field_names])),
'data_format' : str(Sum([TD('$'+f) for f in field_names]))
}
settings.Settings(default_view_file).save(data)
default_entry_file = os.path.join(CONFIG.data_dir,'mysql_admin',
host,user,db_name,table_name,'entry_default.py')
widgets = {}
for field in fields:
widgets[field['name']]={'type':'INPUT','size':10}
if field['type'].lower()=='date':
widgets[field['name']]={'type':'INPUT_DATE','size':10}
data = {'widgets':widgets}
settings.Settings(default_entry_file).save(data)
def _update_views_and_forms(db_name,table_name):
# used if a field was created or removed
exec(open(mysql_settings_file).read())
settings_file = os.path.join(CONFIG.data_dir,'mysql_admin',
host,user,db_name,table_name,'settings.py')
if not os.path.exists(settings_file):
_create_settings_file(db_name,table_name)
conf = settings.Settings(settings_file).load()
for entry in conf['edit_forms']:
entry_file = os.path.join(CONFIG.data_dir,'mysql_admin',
host,user,db_name,table_name,'entry_%s.py' %entry)
entry_conf = settings.Settings(entry_file).load()
widgets = entry_conf.get('widgets',{})
fields = _get_fields(db_name,table_name)
# new fields
new_fields = [ f for f in fields if not f['name'] in widgets ]
for field in new_fields:
widgets[field['name']]={'type':'INPUT','size':10}
if field['type'].lower()=='date':
widgets[field['name']]={'type':'INPUT_DATE','size':10}
# removed fields
field_names = [ f['name'] for f in fields ]
rem_fields = [ f for f in widgets if not f in field_names ]
for field in rem_fields:
del widgets[field]
entry_conf['widgets'] = widgets
settings.Settings(entry_file).save(entry_conf)
def _create_view_file(db_name,table_name,view):
exec(open(mysql_settings_file).read())
settings_file = os.path.join(CONFIG.data_dir,'mysql_admin',
host,user,db_name,table_name,'settings.py')
# save default view
conf = settings.Settings(settings_file).load()
if view in conf['views']:
raise Exception,'View %s already defined' %view
else:
conf['views'].append(view)
settings.Settings(settings_file).save(conf)
view_file = os.path.join(CONFIG.data_dir,'mysql_admin',
host,user,db_name,table_name,'view_%s.py' %view)
# default values
fields = _get_fields(db_name,table_name)
field_names = [f['name'] for f in fields]
data = {'records_per_page':30,
'order_by' : '',
'condition':'',
'style' : 'h_table',
'table_headers' : str(Sum([TH(f) for f in field_names])),
'data_format' : str(Sum([TD('$'+f) for f in field_names]))
}
widgets = {}
for field in fields:
widgets[field['name']]={'type':'INPUT','size':10}
if field['type'].lower()=='date':
widgets[field['name']]={'type':'INPUT_DATE','size':10}
data['widgets'] = widgets
settings.Settings(view_file).save(data)
def _make_entry(db_name,table_name,record=None):
exec(open(mysql_settings_file).read())
settings_file = os.path.join(CONFIG.data_dir,'mysql_admin',
host,user,db_name,table_name,'entry_default.py')
conf = settings.Settings(settings_file).load()
try:
return conf['entry_form']
except KeyError:
return _make_default_entry(db_name,table_name,record)
def _make_default_entry(db_name,table_name,record=None):
exec(open(mysql_settings_file).read())
entry_file = os.path.join(CONFIG.data_dir,'mysql_admin',
host,user,db_name,table_name,'entry_default.py')
conf = settings.Settings(entry_file).load()
widgets = conf['widgets']
fields = _get_fields(db_name,table_name)
field_names = [f['name'] for f in fields]
table = TABLE(Class="db_tables")
for i,field in enumerate(fields):
name = field['name']
if field['extra'].lower()=='auto_increment':
continue
if field['type'].lower() in ['datetime','timestamp'] \
and field['default'].lower()=='current_timestamp':
continue
cells = TH(name)
widget = widgets.setdefault(name,{'type':'INPUT','size':10})
if widget['type'] in ['INPUT','INPUT_DATE']:
entry = INPUT(name=name,Id=name,size=widget['size'],value='$%s' %name)
elif widget['type'] in ['TEXTAREA','WHIZZYWIG']:
entry = TEXTAREA('$%s' %name,name=name,Id=name,
rows=widget['rows'],cols=widget['cols'])
elif widget['type'] in ['SELECT','RADIO']:
ext_table = widget['table']
ext_fields_pattern = widget['fields']
template = string.Template(ext_fields_pattern)
cursor = _cursor()
# get auto increment field
ext_fields = _get_fields(db_name,ext_table)
ext_field_names = [f['name'] for f in ext_fields]
ext_id = [f for f in ext_fields
if f['extra'].lower()=='auto_increment'][0]['name']
# select records from external table
cursor.execute('SELECT * FROM %s.%s' %(db_name,ext_table))
options = []
for rec in cursor.fetchall():
rec_dict = dict(zip(ext_field_names,rec))
opt_value = rec_dict[ext_id]
opt_text = template.safe_substitute(rec_dict)
options.append((opt_text,opt_value))
if widget['type']=='SELECT':
entry = SELECT(name=name,Id=name)
for opt_text,opt_value in options:
entry <= OPTION(opt_text,value=opt_value)
elif widget['type']=='RADIO':
lines = []
for opt_text,opt_value in options:
line = INPUT(Type='radio',name=name,value=opt_value)
line += ' '+opt_text+BR()
lines.append(str(line))
entry = '<br>'.join(lines)
cells += TD(entry,Class="db_table")
table <= TR(cells)
return table
|