def _Run(self, **values):
result = 1
codepage = "utf-8"
app = self.app
datapool = app.db
conf = app.dbConfiguration
conn = datapool.connection
system = values.get("excludeSystem")
self.filename = app.configuration.id + ".sql"
if not conn:
self.stream.write(
_(u"Database connection error (${name})\n",
mapping={u"name": app.dbConfiguration.context}))
return 0
if not conn.IsConnected():
self.stream.write(
_(u"Database connection error (${name})\n",
mapping={u"name": app.dbConfiguration.context}))
return 0
def mapfields(fields):
a = []
for f in fields:
a.append(f.id)
return a
export = [(MetaTbl, mapfields(app.GetAllMetaFlds(ignoreSystem=False)))]
for t in app.GetAllObjectConfs():
export.append((t.dbparam, ["id"] + mapfields(t.data)))
for t in Structure.items():
export.append((t[0], mapfields(t[1]["fields"])))
for table in export:
#tablename
tablename = table[0]
if system and tablename in system:
continue
#fields
fields = table[1]
columns = (",").join(fields)
sql = "select %s from %s" % (columns, tablename)
c = conn.cursor()
c.execute(sql)
for rec in c.fetchall():
data = []
for col in rec:
data.append(conn.FmtParam(col))
data = (",").join(data)
value = u"INSERT INTO %s (%s) VALUES (%s);\n" % (tablename,
columns, data)
value = value.encode(codepage)
self.stream.write(value)
return 1
def _Run(self, **values):
result = 1
codepage="utf-8"
app = self.app
datapool = app.db
conf = app.dbConfiguration
conn = datapool.connection
system = values.get("excludeSystem")
self.filename = app.configuration.id + ".json"
if not conn:
self.stream.write(_(u"Database connection error (${name})\n", mapping={u"name": app.dbConfiguration.context}))
return 0
if not conn.IsConnected():
self.stream.write(_(u"Database connection error (${name})\n", mapping={u"name": app.dbConfiguration.context}))
return 0
def mapfields(fields):
a=[]
for f in fields:
a.append(f.id)
return a
export = [(MetaTbl,mapfields(app.GetAllMetaFlds(ignoreSystem=False)))]
for t in app.GetAllObjectConfs():
export.append((t.dbparam, ["id"]+mapfields(t.data)))
for t in Structure.items():
export.append((t[0], mapfields(t[1]["fields"])))
data = {}
for table in export:
#tablename
tablename=table[0]
if system and tablename in system:
continue
#fields
fields=table[1]
columns = (",").join(fields)
sql="select %s from %s" % (columns, tablename)
c = conn.cursor()
c.execute(sql)
tvalues = []
for rec in c.fetchall():
recvalue = {}
pos = 0
for col in rec:
recvalue[fields[pos]] = col
pos+=1
tvalues.append(recvalue)
data[tablename] = tvalues
self.stream.write(JsonDataEncoder().encode(data))
return 1
def _Run(self, **values):
result = 1
codepage="utf-8"
app = self.app
datapool = app.db
conf = app.dbConfiguration
conn = datapool.connection
system = values.get("excludeSystem")
self.filename = app.configuration.id + ".sql"
if not conn:
self.stream.write(_(u"Database connection error (${name})\n", mapping={u"name": app.dbConfiguration.context}))
return 0
if not conn.IsConnected():
self.stream.write(_(u"Database connection error (${name})\n", mapping={u"name": app.dbConfiguration.context}))
return 0
def mapfields(fields):
a=[]
for f in fields:
a.append(f.id)
return a
export = [(MetaTbl,mapfields(app.GetAllMetaFlds(ignoreSystem=False)))]
for t in app.GetAllObjectConfs():
export.append((t.dbparam, ["id"]+mapfields(t.data)))
for t in Structure.items():
export.append((t[0], mapfields(t[1]["fields"])))
for table in export:
#tablename
tablename=table[0]
if system and tablename in system:
continue
#fields
fields=table[1]
columns = (",").join(fields)
sql="select %s from %s" % (columns, tablename)
c = conn.cursor()
c.execute(sql)
for rec in c.fetchall():
data = []
for col in rec:
data.append(conn.FmtParam(col))
data = (",").join(data)
if not isinstance(data, unicode):
data = unicode(data, codepage)
value = u"INSERT INTO %s (%s) VALUES (%s);\n"%(tablename, columns, data)
value = value.encode(codepage)
self.stream.write(value)
return 1
class dbStructureUpdater(Tool):
def _Run(self, **values):
result = 1
importWf = 1
importSecurity = 0
showSystem = values.get("showSystem")
modify = values.get("modify")
request = values["original"]
try:
localizer = get_localizer(get_current_request())
except:
localizer = FakeLocalizer()
#localizer.translate(term)
text = _(u""" <div class="well">
This tool compares the physically existing database structure (tables, columns) with the current configuration settings.
The database structure is shown on the left, configuration settings on the right. <br><br>
Existing database columns will only be altered if manually selected in the 'Modify' column. Modifying a table may destroy the data
stored (e.g if converted from string to integer), so don't forget to create backups of the database before modifying anything.<br>
By default this tool will only create new tables and columns and never delete any column.
</div> """)
self.stream.write(localizer.translate(_(text)))
self.stream.write(u"""<form action="" method="post">
<input type="hidden" name="tag" value="dbStructureUpdater">
<input type="hidden" name="modify" value="1">""")
app = self.app
try:
conf = app.dbConfiguration
connection = app.NewConnection()
if not connection:
self.stream.write(
localizer.translate(
_(u"""<div class="alert alert-error">No database connection configured</div>"""
)))
return 0
except OperationalError, e:
self.stream.write(
localizer.translate(
_(u"""<div class="alert alert-error">No database connection configured</div>"""
)))
return 0
db = connection.GetDBManager()
self.stream.write(
localizer.translate(
_(u"<h4>Database '${name}' ${host} </h4><br>",
mapping={
"host": conf.host,
"name": conf.dbName
})))
if not db:
self.stream.write(
localizer.translate(
_(u"<div class='alert alert-error'>Database connection error (${name})</div>",
mapping={"name": app.dbConfiguration.context})))
return 0
# check database exists
if not db.IsDatabase(conf.get("dbName")):
db.CreateDatabase(conf.get("dbName"))
self.stream.write(u"")
self.stream.write(u"")
self.stream.write(
localizer.translate(
_(u"<div class='alert alert-success'>Database created: '${name}'</div>",
mapping={"name": conf.dbName})))
db.dbConn.commit()
db.UseDatabase(conf.get("dbName"))
# check types for data tables -------------------------------------------------------------
aTypes = app.GetAllObjectConfs()
for aT in aTypes:
fmt = aT["data"]
if (fmt == []):
continue
m = None
if modify:
m = request.get(aT["dbparam"])
if isinstance(m, basestring):
m = [m]
if not db.UpdateStructure(aT["dbparam"], fmt, m):
self.stream.write(u"")
self.stream.write(
localizer.translate(
_(u"<div class='alert alert-error'>Table update failed: ${dbparam} (type: ${name})</div>",
mapping=aT)))
result = 0
continue
db.dbConn.commit()
self.printStructure(db.GetColumns(aT["dbparam"], fmt),
aT["dbparam"], fmt, db, localizer)
# check meta table exists and update ---------------------------------------------------------------
meta = app.GetAllMetaFlds(ignoreSystem=False)
tableName = MetaTbl
if not db.IsTable(tableName):
if not db.CreateTable(tableName, columns=meta):
self.stream.write(
localizer.translate(
_(u"<div class='alert alert-error'>Table creation failed (pool_meta)</div>"
)))
return 0
db.dbConn.commit()
# create and check modified fields
m = None
if modify:
m = request.get(tableName)
if isinstance(m, basestring):
m = [m]
if not db.UpdateStructure(tableName, meta, m):
self.stream.write(
localizer.translate(
_(u"<div class='alert alert-error'>Table update failed (pool_meta)</div>"
)))
result = 0
db.dbConn.commit()
self.printStructure(db.GetColumns(tableName, meta), tableName, meta,
db, localizer)
# check structure tables exist and update ------------------------------------------------------------
for table in Structure.items():
tableName = table[0]
fields = table[1]["fields"]
identity = table[1]["identity"]
if not db.IsTable(tableName):
if not db.CreateTable(tableName,
columns=fields,
createIdentity=bool(identity),
primaryKeyName=identity):
self.stream.write(
localizer.translate(
_(u"<div class='alert alert-error'>Table creation failed (${name})</div>",
mapping={"name": tableName})))
return 0
db.dbConn.commit()
# create and check modified fields
m = None
if modify:
m = request.get(tableName)
if isinstance(m, basestring):
m = [m]
if not db.UpdateStructure(
tableName, fields, m, createIdentity=bool(identity)):
self.stream.write(
localizer.translate(
_(u"<div class='alert alert-error'>Table creation failed (${name})</div>",
mapping={"name": tableName})))
result = 0
db.dbConn.commit()
if showSystem:
self.printStructure(db.GetColumns(tableName, fields),
tableName, fields, db, localizer)
if db.modifyColumns:
self.stream.write(
u"""<div class="alert alert-error"><input class="btn" type="submit" name="submit" value=" %s "><br>%s</div>"""
%
(localizer.translate(_(u"Modify selected columns")),
localizer.translate(
_(u"Changes on existing columns have to be applied manually. This will write selected 'Configuration settings' to the database.<br> <b>Warning: This may destroy something!</b>"
))))
self.stream.write(localizer.translate(u"</form>"))
connection.close()
return result
def _Run(self, **values):
result = 1
codepage = "utf-8"
app = self.app
datapool = app.db
conf = app.dbConfiguration
conn = datapool.connection
system = values.get("excludeSystem")
self.filename = app.configuration.id + ".json"
if not conn:
self.stream.write(
_(u"Database connection error (${name})\n",
mapping={u"name": app.dbConfiguration.context}))
return 0
if not conn.IsConnected():
self.stream.write(
_(u"Database connection error (${name})\n",
mapping={u"name": app.dbConfiguration.context}))
return 0
def mapfields(fields):
a = []
for f in fields:
a.append(f.id)
return a
export = [(MetaTbl, mapfields(app.GetAllMetaFlds(ignoreSystem=False)))]
for t in app.GetAllObjectConfs():
export.append((t.dbparam, ["id"] + mapfields(t.data)))
for t in Structure.items():
export.append((t[0], mapfields(t[1]["fields"])))
data = {}
for table in export:
#tablename
tablename = table[0]
if system and tablename in system:
continue
#fields
fields = table[1]
columns = (",").join(fields)
sql = "select %s from %s" % (columns, tablename)
c = conn.cursor()
c.execute(sql)
tvalues = []
for rec in c.fetchall():
recvalue = {}
pos = 0
for col in rec:
recvalue[fields[pos]] = col
pos += 1
tvalues.append(recvalue)
data[tablename] = tvalues
self.stream.write(JsonDataEncoder().encode(data))
return 1
