class TestSetup(unittest.TestCase):
"""docstring for TestSetUp"""
def setUp(self):
#self.driver = webdriver.Firefox()
self.driver = webdriver.Remote(command_executor='http://0.0.0.0:4444/wd/hub',
desired_capabilities=DesiredCapabilities.FIREFOX)
self.submit_button = "//input[@value='Submit']"
db_username_postgres = 'postgres'
db_password_postgres = '1234'
db_postgres_url = 'postgres://' + db_username_postgres + ':' + db_password_postgres + '@localhost/dev'
path_to_database = path.join(path.curdir, "../databases")
self.db_test = DAL(db_postgres_url, folder=path_to_database)
self.db_test.import_table_definitions(path_to_database)
def tearDown(self):
self.driver.quit()
def submit_form(self):
self.driver.find_element_by_xpath(self.submit_button).click()
def limpa_dados_tabela(self, nome_tabela):
self.db_test.executesql('delete from ' + nome_tabela)
self.db_test.commit()
def qq():
db = DAL('postgres://*****:*****@localhost/Lean',pool_size=5,check_reserved=['postgres'], folder=None, bigint_id=True
) #fake_migrate_all=True migrate_enabled=True
db.executesql('SET search_path TO public, admins, model_gen, history, tasks, trac;')
db.define_table('a_tbl', Field('name'))
db.define_table('b_tbl', Field('id', 'reference a_tbl') ,Field('name'), primarykey=['id'])
db.define_table('c_tbl', Field('id', 'reference b_tbl') ,Field('name'), primarykey=['id'])
class DocDB(object):
"""Creates instances of DocDB. Each instance will have a db connection.
Extracted from web2py docs:
---------------------------
Here are examples of connection strings for specific types of supported back-end databases
(in all cases, we assume the database is running from localhost on its default port and is named "test"):
SQLite sqlite://storage.db
MySQL mysql://username:password@localhost/test
PostgreSQL postgres://username:password@localhost/test
MSSQL mssql://username:password@localhost/test
FireBird firebird://username:password@localhost/test
Oracle oracle://username/password@test
DB2 db2://username:password@test
Ingres ingres://username:password@localhost/test
Informix informix://username:password@test
Google App Engine/SQL google:sql
Google App Engine/NoSQL google:datastore
Notice that in SQLite the database consists of a single file. If it does not exist, it is created.
This file is locked every time it is accessed. In the case of MySQL, PostgreSQL, MSSQL, FireBird,
Oracle, DB2, Ingres and Informix the database "test" MUST exist.
"""
def __init__(self, conn='sqlite://temp.db', pool_size=0, migrate=True):
"""
Generates a connection with the given DB.
@connection: system path to the sqlite file to use or DB connection string. If None given,
a default temp.db file will be created.
"""
if '://' not in conn:
print """Connection string needed!\n \
Some examples:\n \
SQLite sqlite://storage.db
MySQL mysql://username:password@localhost/test \
PostgreSQL postgres://username:password@localhost/test
"""
sys.exit(2)
self._conn = conn
self._db = DAL(conn, folder=PATH, pool_size=pool_size)
self._db.define_table('documents',
Field('key'),
Field('data', 'text'),
Field('valid', 'boolean'),
migrate = migrate)
if not self._db(self._db.documents).count():
try:
self._db.executesql('CREATE INDEX keyx ON documents (key)') #CREATE INDEX IF NOT EXISTS
except Exception:
self._db.rollback()
def get(self, key):
"""
Searches and returns the doc for a given key.
"""
db = self._db
doc = db((db.documents.key==key) & (db.documents.valid==True)).select(db.documents.data).first()
if doc:
return json.loads(doc['data'])
return None
def set(self, key, doc):
"""
Inserts a document (doc) into the DB.
@doc: can be of any python data structure (string, number, dict, list, ...
"""
db = self._db
data = json.dumps(doc)
db((db.documents.key==key) & (db.documents.valid==True)).update(valid=False)
db.documents.insert(key=key, data=data, valid=True)
db.commit()
return True
def mset(self, docs):
"""
Inserts a set of documents into the DB.
Example:
>>> l = [('key1', {'key': 'value'}), ('key2', {'key': 'value'}), ('key3', {'key': 'value'})]
>>> db.mset(*l)
"""
db = self._db
counter = 0
for doc in docs:
key, data = doc
data = json.dumps(data)
db((db.documents.key==key) & (db.documents.valid==True)).update(valid=False)
db.documents.insert(key=key, data=data, valid=True)
counter += 1
if counter > 1000:
db.commit()
counter = 0
db.commit()
return True
def versions(self, key):
"""
Lists all the documents related to a key.
"""
db = self._db
results = []
for doc in db(db.documents.key == key).select():
id, data, valid = doc['id'], json.loads(doc['data']), doc['valid']
results.append((id, data, valid))
return results
def revert(self, key, version):
"""
Reverts to a previous version of the document.
"""
db = self._db
vers = self.versions(key)
for doc in vers:
id, data, valid = doc
if id == version:
db((db.documents.key==key) & (db.documents.valid==True)).update(valid=False)
db(db.documents.id==id).update(valid=True)
db.commit()
return True
return False
def info(self):
"""
Returns a dict with info about the current DB (including db filesize, number of keys, etc.).
"""
dbsize = -1
db = self._db
if "postgres" in self._conn:
dbsize = db.executesql("SELECT pg_size_pretty(pg_database_size('%s'));" % self.dbname)[0][0]
num_keys = db(db.documents.valid==True).count()
return dict(keys=num_keys, dbsize=dbsize)
def keys(self):
"""
Returns a list of ALL the keys in the current DB.
"""
db = self._db
return [doc['key'] for doc in db(db.documents.valid==True).select(db.documents.key)]
def flushall(self):
"""
Deletes ALL the content from the DB.
TODO: Use truncate.
"""
self._db.documents.truncate()
self._db.commit()
#self.__init__()
return True
def compact(self, key=None):
"""
Deletes ALL the versions for a given document or the entire DB.
"""
db = self._db
if key:
db((db.documents.key==key) & (db.documents.valid==False)).delete()
else:
db(db.documents.valid==False).delete()
db.commit()
return True
def __contains__(self, item):
if self.get(item): return True
return False
def __getitem__(self, item):
return self.get(item)
def __setitem__(self, key, doc):
return self.set(key, doc)
def __delitem__(self, key):
self._db.execute('UPDATE document SET valid == 0 WHERE key == ?', (key,))
self._db.commit()
return True
session.comprobado = True
# initialize admin user and roles group:
useradmin = db(db.auth_user.id == 1).select()
if len(useradmin) == 0:
db.Sede.insert(name="Sede de pruebas")
db.Almacen.insert(name="AlmacenTest")
my_crypt = CRYPT(key=auth.settings.hmac_key)
crypted_passwd = my_crypt('password_malo')[0]
db.commit()
# k = db.auth_user.insert(email='*****@*****.**', first_name='Administrator',
# password=crypted_passwd, almacen=1)
k = db.auth_user.insert(email='*****@*****.**', first_name='Administrator',
password=crypted_passwd)
if str(k) != '1':
db.executesql('update auth_user set id=1 where id=' + str(k))
k = auth.add_group('admins', 'Administradores de la aplicación')
if str(k) != '1':
db.executesql('update auth_group set id=1 where id=' + str(k))
auth.add_membership(1, 1)
auth.add_permission(1, 'admins', db.auth_user)
db.commit()
# Temporal, para importar datos de bb.dd. anterior ###
import importacion
locs = db().select(db.poblacion.ALL, limitby=(0, 1))
if not locs:
# importacion.rellena_familias()
# importacion.rellena_subfamilias()
db = DAL('mysql://*****:*****@localhost/wantu', pool_size=10, db_codec='latin1')#, migrate=False, migrate_enabled=False)
db.define_table('urlresource',
Field('id', 'id'),
Field('url', 'string'),
Field('pagetype', 'integer'),
Field('referrerurl', 'string'),
Field('description', 'string'),
Field('savedpath', 'string'),
Field('createtime', 'integer'),
Field('lastmodtime', 'integer'),
Field('isfinished', 'integer', default=0),
Field('ispublished', 'integer', default=0))#,
#migrate=False)
db.executesql("set autocommit=True")
def refresh_context(db):
'''
change isolation mode to load recent changes
'''
#end current active transaction
#db.executesql("commit")
#change isolation mode to read-committed
#db.executesql("set @@tx_isolation='read-uncommitted'")
pass
def save_url(url, **fields):
db.executesql("set autocommit=True")
row = db(db.urlresource.url == url).select().first()
fields.pop('createtime', None)
class UpdateFromRDB():
"""This class update all the tables necesary to the simulation
"""
def __init__(self, mDatabase, mUid, inDGF=True):
"""Class initialization function
:param mDatabase: local database (MoSiVo).
:type mDatabase: DAL connection.
:param mUid: Mosivo user id.
:type inDGF: Integer.
:param inDGF: App is in DGF.
:type inDGF: Bool.
"""
# mosivo database
self._db = mDatabase
# mosivo app user id
self._muid = mUid
# DGF connection. Please use read only user
user = '******'
passw = ''
if inDGF:
self.rdb = DAL("mssql://%s:%[email protected]/DGF" % (user, passw),
migrate_enabled=False,
migrate=False)
else:
self.rdb = DAL('sqlite://dgf_database.db',
migrate_enabled=False,
migrate=False)
def uPlanTmp(self):
"""Inserta todas las carpetas no nulas y que no esten con bajas en una tabla
temporal de MoSiVo (plantmp)
"""
sql = "SELECT DISTINCT cp.Nro_Carpeta, d.Numero, cp.Cod_Sj, cp.Longitud, cp.Latitud " \
"FROM Carpetas_P cp, Deptos d " \
"WHERE d.Codigo=cp.Cod_Depto AND cp.Baja=0 AND cp.Nro_Carpeta IS NOT NULL AND " \
"cp.Cod_Depto IS NOT NULL AND cp.Cod_Sj IS NOT NULL AND cp.Cod_Sj>0 ORDER BY cp.Nro_Carpeta"
rows = self.rdb.executesql(sql)
if len(rows) > 0:
# Delete all data in table carpeta for this user
self._db.executesql("DELETE FROM plantmp WHERE cby=%i" %
self._muid)
self._db.commit()
try:
for r in rows:
self._db['plantmp'].insert(
ncarpeta=r[0],
depto=r[1],
sj=r[2],
lon=r[3],
lat=r[4],
cby=self._muid,
)
self._db.commit()
return True
except Exception as e:
print "Error: %s" % e
self._db.rollback()
return False
def uPT2P(self):
"""Pasa de plantmp a plan
"""
# Delete all data in table plan for this user
self._db.executesql("DELETE FROM plan WHERE cby=%i" % self._muid)
sql = "INSERT INTO plan(ncarpeta,sjudicial,lon,lat,cby) "
sql += "(SELECT pt.ncarpeta, sj.id, pt.lon, pt.lat, sj.cby "
sql += "FROM plantmp pt, seccionjudicial sj "
sql += "WHERE pt.depto=sj.departamento AND pt.sj=sj.nombre "
sql += "AND pt.cby=%i AND sj.cby=%i" % (self._muid, self._muid)
sql += "ORDER BY pt.ncarpeta)"
try:
rows = self._db.executesql(sql)
return True
except Exception as e:
print "Error: %s" % e
return False
def uPlan(self):
"""Actualiza en dos etapas plan desde DGF
"""
if self.uPlanTmp():
if self.uPT2P():
return True
else:
return False
else:
return False
def uRodalDTmp(self):
"""Inserta rodales declarados para cada carpeta
"""
self._db.executesql("DELETE FROM rodaldtmp WHERE cby=%i" % self._muid)
'''
sql = "SELECT cp.Nro_Carpeta, pl.Genero, pl.Especie, p.Ano_Dec, p.Ha_Dec "
sql += "FROM Planes p, Planes_Pro pp, Carpetas_P cp, Plantas pl "
sql += "WHERE p.CodG_Dec IS NOT NULL AND p.CodE_Dec IS NOT NULL AND " \
"pp.Codigo_Cp=cp.Nro_Carpeta AND pp.Codigo=p.Codigo_Plan_Pro AND " \
"p.Ano_Dec>0 AND p.Ha_Dec>0 AND " \
"pl.CodG=p.CodG_Dec AND pl.CodE=p.CodE_Dec ORDER BY cp.Nro_Carpeta"
'''
#####################################################################
# RFPV - Muy importante:
# Planes_Pro.Codigo_Cp=Carpetas_P.Nro_Carpeta
# Planes_Pro.Codigo=Planes.Codigo_Plan_Pro
# RFPV - Muy importante
####################################################################
sql = "SELECT cp.Nro_Carpeta, pl.Genero, pl.Especie, p.Anio_Dec, p.Ha_Dec "
sql += "FROM Planes_View p, Planes_Pro pp, Carpetas_P cp, Plantas pl "
sql += "WHERE p.CodG_Dec IS NOT NULL AND p.CodE_Dec IS NOT NULL AND " \
"pp.Codigo_Cp=cp.Nro_Carpeta AND pp.Codigo=p.Codigo_Plan_Pro AND " \
"p.Anio_Dec>0 AND p.Ha_Dec>0 AND " \
"pl.CodG=p.CodG_Dec AND pl.CodE=p.CodE_Dec ORDER BY cp.Nro_Carpeta"
rows = self.rdb.executesql(sql)
self._db.commit()
try:
for r in rows:
self._db['rodaldtmp'].insert(
ncarpeta=r[0],
ngen=r[1].strip(),
nesp=r[2].strip(),
anioplant=int(r[3]),
areaafect=float(r[4]),
cby=self._muid,
)
self._db.commit()
return True
except Exception as e:
print "Error: %s" % e
self._db.rollback()
return False
def uRDT2RD(self):
"""Pasa de rodaldtmp a rodald
"""
self._db.commit()
try:
# Delete all data in table rodald for this user
self._db.executesql(
"DELETE FROM rodald WHERE plan IN (SELECT id FROM plan WHERE cby=%i)"
% self._muid)
sql = "INSERT INTO rodald(plan,especie,anioplant,areaafect) "
sql += "(SELECT p.id, e.id, rdt.anioplant, rdt.areaafect "
sql += "FROM plan p, especie e, genero g, rodaldtmp rdt "
sql += "WHERE rdt.ncarpeta=p.ncarpeta AND e.nombre=rdt.nesp AND g.nombre=rdt.ngen AND e.genero=g.id "
sql += "AND p.cby=%i AND g.cby=%i AND rdt.cby=%i " % (
self._muid, self._muid, self._muid)
sql += "ORDER BY rdt.ncarpeta)"
rows = self._db.executesql(sql)
self._db.commit()
self.__uUbicacionRodalD()
return True
except Exception as e:
print "Error: %s" % e
self._db.rollback()
return False
def uRodalD(self):
"""Actualiza en dos etapas rodald desde DGF
"""
if self.uRodalDTmp():
if self.uRDT2RD():
return True
else:
return False
else:
return False
def uGenero(self):
"""Get genero data from DGF.Plantas
"""
sql = "SELECT DISTINCT CodG,Genero FROM Plantas WHERE Genero IS NOT NULL ORDER BY Genero"
rows = self.rdb.executesql(sql)
if len(rows) > 0:
# Delete all data in table genero
self._db.executesql("DELETE FROM genero WHERE cby=%i" % self._muid)
self._db.commit()
try:
for r in rows:
self._db.genero.insert(
nombre=str(r[1]).strip(),
codigo=str(r[0]).strip(),
cby=self._muid,
)
self._db.commit()
return True
except Exception as e:
print "Error: %s" % e
self._db.rollback()
return False
def uEspecie(self):
"""Get especie data from DGF.Plantas
"""
lrows = self._db((self._db['genero']['id'] > 0)
& (self._db['genero']['cby'] == self._muid)).select(
self._db['genero']['id'],
self._db['genero']['nombre'],
self._db['genero']['codigo'])
if len(lrows) > 0:
# Delete all data in table especie
# self._db.executesql("DELETE FROM especie WHERE cby=%i" % self._muid)
self._db.executesql(
"DELETE FROM especie WHERE genero IN (SELECT id FROM genero WHERE cby=%i)"
% self._muid)
self._db.commit()
try:
for lr in lrows:
id = int(lr['id'])
genero = lr['nombre']
sql = 'SELECT CodE, Especie FROM Plantas WHERE Genero LIKE \'' + str(
genero) + '%\''
print "UpdateFromRDB.uEspecie.sql: %s" % sql
rows = self.rdb.executesql(sql)
try:
for r in rows:
self._db.especie.insert(
genero=id,
nombre=str(r[1]).strip(),
codigo=str(r[0]).strip(),
# cby = self._muid,
)
except Exception as e:
print "Error: %s" % e
raise Exception('Especie error')
self._db.commit()
return True
except Exception as e:
print "Error: %s" % e
self._db.rollback()
return False
def __uUbicacionRodalD(self):
"""Fill ubicacionrodald (private function - autoused)
"""
self._db.commit()
try:
# SQL is short and fast
sql1 = "SELECT rd.id, p.sjudicial, p.lon, p.lat FROM rodald rd, plan p " \
"WHERE rd.plan=p.id AND p.cby=%i" \
"ORDER BY rd.id" % self._muid
sql = "INSERT INTO ubicacionrodald(rodal,sjudicial,lon,lat) (%s)" % sql1
print "UpdateFromRDB.__uUbicacionRodalD.sql: %s" % sql
self._db.executesql(sql)
self._db.commit()
return True
except Exception as e:
print "Error: %s!" % e
self._db.rollback()
return False
def uAll(self):
"""Util function to update all the data from remote database to local database
"""
# Order is important
return [self.uGenero(), self.uEspecie(), self.uPlan(), self.uRodalD()]
class UpdateFromRDB():
"""This class update all the tables necesary to the simulation
"""
def __init__(self, mDatabase, mUid, inDGF=True):
"""Class initialization function
:param mDatabase: local database (MoSiVo).
:type mDatabase: DAL connection.
:param mUid: Mosivo user id.
:type inDGF: Integer.
:param inDGF: App is in DGF.
:type inDGF: Bool.
"""
# mosivo database
self._db = mDatabase
# mosivo app user id
self._muid = mUid
# DGF connection. Please use read only user
user='******'
passw=''
if inDGF:
self.rdb = DAL("mssql://%s:%[email protected]/DGF" % (user, passw), migrate_enabled=False, migrate=False)
else:
self.rdb = DAL('sqlite://dgf_database.db', migrate_enabled=False, migrate=False)
def uPlanTmp(self):
"""Inserta todas las carpetas no nulas y que no esten con bajas en una tabla
temporal de MoSiVo (plantmp)
"""
sql = "SELECT DISTINCT cp.Nro_Carpeta, d.Numero, cp.Cod_Sj, cp.Longitud, cp.Latitud " \
"FROM Carpetas_P cp, Deptos d " \
"WHERE d.Codigo=cp.Cod_Depto AND cp.Baja=0 AND cp.Nro_Carpeta IS NOT NULL AND " \
"cp.Cod_Depto IS NOT NULL AND cp.Cod_Sj IS NOT NULL AND cp.Cod_Sj>0 ORDER BY cp.Nro_Carpeta"
rows = self.rdb.executesql(sql)
if len(rows) > 0:
# Delete all data in table carpeta for this user
self._db.executesql("DELETE FROM plantmp WHERE cby=%i" % self._muid)
self._db.commit()
try:
for r in rows:
self._db['plantmp'].insert(
ncarpeta=r[0],
depto=r[1],
sj=r[2],
lon = r[3],
lat = r[4],
cby = self._muid,
)
self._db.commit()
return True
except Exception as e:
print "Error: %s" % e
self._db.rollback()
return False
def uPT2P(self):
"""Pasa de plantmp a plan
"""
# Delete all data in table plan for this user
self._db.executesql("DELETE FROM plan WHERE cby=%i" % self._muid)
sql = "INSERT INTO plan(ncarpeta,sjudicial,lon,lat,cby) "
sql += "(SELECT pt.ncarpeta, sj.id, pt.lon, pt.lat, sj.cby "
sql += "FROM plantmp pt, seccionjudicial sj "
sql += "WHERE pt.depto=sj.departamento AND pt.sj=sj.nombre "
sql += "AND pt.cby=%i AND sj.cby=%i" % (self._muid,self._muid)
sql += "ORDER BY pt.ncarpeta)"
try:
rows = self._db.executesql(sql)
return True
except Exception as e:
print "Error: %s" % e
return False
def uPlan(self):
"""Actualiza en dos etapas plan desde DGF
"""
if self.uPlanTmp():
if self.uPT2P():
return True
else:
return False
else:
return False
def uRodalDTmp(self):
"""Inserta rodales declarados para cada carpeta
"""
self._db.executesql("DELETE FROM rodaldtmp WHERE cby=%i" % self._muid)
'''
sql = "SELECT cp.Nro_Carpeta, pl.Genero, pl.Especie, p.Ano_Dec, p.Ha_Dec "
sql += "FROM Planes p, Planes_Pro pp, Carpetas_P cp, Plantas pl "
sql += "WHERE p.CodG_Dec IS NOT NULL AND p.CodE_Dec IS NOT NULL AND " \
"pp.Codigo_Cp=cp.Nro_Carpeta AND pp.Codigo=p.Codigo_Plan_Pro AND " \
"p.Ano_Dec>0 AND p.Ha_Dec>0 AND " \
"pl.CodG=p.CodG_Dec AND pl.CodE=p.CodE_Dec ORDER BY cp.Nro_Carpeta"
'''
#####################################################################
# RFPV - Muy importante:
# Planes_Pro.Codigo_Cp=Carpetas_P.Nro_Carpeta
# Planes_Pro.Codigo=Planes.Codigo_Plan_Pro
# RFPV - Muy importante
####################################################################
sql = "SELECT cp.Nro_Carpeta, pl.Genero, pl.Especie, p.Anio_Dec, p.Ha_Dec "
sql += "FROM Planes_View p, Planes_Pro pp, Carpetas_P cp, Plantas pl "
sql += "WHERE p.CodG_Dec IS NOT NULL AND p.CodE_Dec IS NOT NULL AND " \
"pp.Codigo_Cp=cp.Nro_Carpeta AND pp.Codigo=p.Codigo_Plan_Pro AND " \
"p.Anio_Dec>0 AND p.Ha_Dec>0 AND " \
"pl.CodG=p.CodG_Dec AND pl.CodE=p.CodE_Dec ORDER BY cp.Nro_Carpeta"
rows = self.rdb.executesql(sql)
self._db.commit()
try:
for r in rows:
self._db['rodaldtmp'].insert(
ncarpeta=r[0],
ngen=r[1].strip(),
nesp=r[2].strip(),
anioplant=int(r[3]),
areaafect=float(r[4]),
cby = self._muid,
)
self._db.commit()
return True
except Exception as e:
print "Error: %s" % e
self._db.rollback()
return False
def uRDT2RD(self):
"""Pasa de rodaldtmp a rodald
"""
self._db.commit()
try:
# Delete all data in table rodald for this user
self._db.executesql("DELETE FROM rodald WHERE plan IN (SELECT id FROM plan WHERE cby=%i)" % self._muid)
sql = "INSERT INTO rodald(plan,especie,anioplant,areaafect) "
sql += "(SELECT p.id, e.id, rdt.anioplant, rdt.areaafect "
sql += "FROM plan p, especie e, genero g, rodaldtmp rdt "
sql += "WHERE rdt.ncarpeta=p.ncarpeta AND e.nombre=rdt.nesp AND g.nombre=rdt.ngen AND e.genero=g.id "
sql += "AND p.cby=%i AND g.cby=%i AND rdt.cby=%i " % (self._muid, self._muid, self._muid)
sql += "ORDER BY rdt.ncarpeta)"
rows = self._db.executesql(sql)
self._db.commit()
self.__uUbicacionRodalD()
return True
except Exception as e:
print "Error: %s" % e
self._db.rollback()
return False
def uRodalD(self):
"""Actualiza en dos etapas rodald desde DGF
"""
if self.uRodalDTmp():
if self.uRDT2RD():
return True
else:
return False
else:
return False
def uGenero(self):
"""Get genero data from DGF.Plantas
"""
sql = "SELECT DISTINCT CodG,Genero FROM Plantas WHERE Genero IS NOT NULL ORDER BY Genero"
rows = self.rdb.executesql(sql)
if len(rows) > 0:
# Delete all data in table genero
self._db.executesql("DELETE FROM genero WHERE cby=%i" % self._muid)
self._db.commit()
try:
for r in rows:
self._db.genero.insert(
nombre=str(r[1]).strip(),
codigo=str(r[0]).strip(),
cby = self._muid,
)
self._db.commit()
return True
except Exception as e:
print "Error: %s" % e
self._db.rollback()
return False
def uEspecie(self):
"""Get especie data from DGF.Plantas
"""
lrows = self._db(
( self._db['genero']['id']>0 ) &
( self._db['genero']['cby'] == self._muid )
).select (
self._db['genero']['id'],
self._db['genero']['nombre'],
self._db['genero']['codigo']
)
if len(lrows)>0:
# Delete all data in table especie
# self._db.executesql("DELETE FROM especie WHERE cby=%i" % self._muid)
self._db.executesql( "DELETE FROM especie WHERE genero IN (SELECT id FROM genero WHERE cby=%i)" % self._muid )
self._db.commit()
try:
for lr in lrows:
id=int( lr['id'] )
genero=lr['nombre']
sql = 'SELECT CodE, Especie FROM Plantas WHERE Genero LIKE \'' + str(genero) + '%\''
print "UpdateFromRDB.uEspecie.sql: %s" % sql
rows=self.rdb.executesql(sql)
try:
for r in rows:
self._db.especie.insert(
genero = id,
nombre = str(r[1]).strip(),
codigo = str(r[0]).strip(),
# cby = self._muid,
)
except Exception as e:
print "Error: %s" % e
raise Exception('Especie error')
self._db.commit()
return True
except Exception as e:
print "Error: %s" % e
self._db.rollback()
return False
def __uUbicacionRodalD(self):
"""Fill ubicacionrodald (private function - autoused)
"""
self._db.commit()
try:
# SQL is short and fast
sql1 = "SELECT rd.id, p.sjudicial, p.lon, p.lat FROM rodald rd, plan p " \
"WHERE rd.plan=p.id AND p.cby=%i" \
"ORDER BY rd.id" % self._muid
sql ="INSERT INTO ubicacionrodald(rodal,sjudicial,lon,lat) (%s)" % sql1
print "UpdateFromRDB.__uUbicacionRodalD.sql: %s" % sql
self._db.executesql(sql)
self._db.commit()
return True
except Exception as e:
print "Error: %s!" % e
self._db.rollback()
return False
def uAll(self):
"""Util function to update all the data from remote database to local database
"""
# Order is important
return [
self.uGenero(),
self.uEspecie(),
self.uPlan(),
self.uRodalD()
]
def main_0():
migrate=True
db = DAL('postgres://*****:*****@localhost/Lean',pool_size=5,check_reserved=['postgres'], folder=None, bigint_id=True
) #fake_migrate_all=True migrate_enabled=True
db.executesql('SET search_path TO public, admins, model_gen, history, tasks, trac;')
db.define_table('person',
Field('id',
type = 'id',
required = True,
),
Field('name',
type = 'string',
required = True,
),
Field('surname',
type = 'string',
required = True,
),
Field('patroname',
type = 'string',
required = True,
),
Field('is_hidden',
type = 'boolean',
default = False,
required = True,
),
migrate=False
)
band_type = Enum(db, 'band_type', code_field=True, migrate=False)
div_type = Enum(db, 'division_type', code_field=True, migrate=False)
div_fntype = Enum(db, 'division_fntype', code_field=True, migrate=False)
def_PG_table(db, 'band',
Field('name'),
ref_field(band_type, nocache=True),
alt_key=['name', band_type.FK_name],
migrate=False)
def_PG_table(db, 'a_comp',
Field('id', 'reference a_bands'),
Field('comp_name'),
primarykey=['id'],
migrate=False)
def_PG_table(db, 'a_dept',
Field('id', 'reference a_bands'),
Field('dept_name'),
primarykey=['id'],
migrate=False)
def_PG_table(db, 'a_men',
Field('name'),
migrate=False)
Entity.init_cls(db)
band_ent = Entity(db.a_bands, D_field=band_type.FK_name, tracking=True)
comp_ent = Entity(db.a_comp, parent = band_ent, D_value=1)
dept_ent = Entity(db.a_dept, parent = band_ent, D_value=2)
men_ent= Entity(db.a_men )
band_ent.def_option('region',Field('name'))
band_ent.def_option('brunch', Field('id'), Field('name'))
"""
dept_ent.update(10, dept_name='вапвап', **{'region.name':'dept_ert',
'brunch.*':[
{'id':14, 'name':''},
]
})
dept_ent.insert(dept_name='вапвап new', **{'region.name':'dept_ert_new',
'brunch.*':[
{'id':14, 'name':'new34'},
]}
)
comp_ent.insert(name='prnt_name_cmp8', comp_name='comp_name_8',
**{'brunch.*':[
{'name':'brunch_name6'},
{'name':'brunch_name7'}
]
}
)
"""
band_ent.create_all_views(migrate=True)
comp_ent.create_all_views(migrate=True)
dept_ent.create_all_views(migrate=True)
men_ent.def_option('age', Field('age', 'integer'))
men_ent.create_all_views(migrate=True)
#men_ent.insert(name='Петя fg', **{'age.age':20} )
band_ent.def_history()
band_ent._write_history(1)
band_men = Relation([db.a_bands, db.a_men], Field('men_role'), alt_key=['a_bands_id', 'a_men_id'])
#band_men.insert(a_bands_id=1, a_men_id=1)
#band_men.insert(a_bands_id=1, a_men_id=2 )
#band_men.insert(a_bands_id=2, a_men_id=2 )
r= band_men._select(None, db.a_men_vw_agg.id, json_agg_sql([db.a_men_vw_agg.name, db.a_bands_vw_agg.name], as_alias='ff'),
groupby=[db.a_men_vw_agg.id] )
#band_ent.create_view( migrate=True )
#dept_ent.insert(name='prnt_dpt_name', dept_name='dept_name')
#enm_c=Enum(db, 'atstc_type', code_field=True, migrate=True)
#enm_clr=Enum(db, 'aclr_type', code_field=True, migrate=True)
#enm_clr.own.insert(id=1, name='Red', code='#red')
#enm_clr.own.insert(id=2, name='Blue', code='#bl')
#enm_clr.own.insert(id=3, name='Green', code='#gr')
"""
db.define_table('a_entst',
Field('name'),
ref_field(enm_clr, nocache=True),
migrate=True)
ent= Entity(db.a_entst)
ent.def_option('opt', Field('color'), Field('shape') , migrate=True)
ent.def_option('plan', Field('start', 'date'), Field('finish', 'date') , migrate=True)
ent.def_option('opt_lst', Field('id'), Field('this'), Field('that') , migrate=True)
#db.plan._entopt.vw_pref='pln'
#db.plan.start._entopt=Storage(vw_name='start_')
ent.create_view( migrate=True )
ent.create_view_json( migrate=True)
ent.create_view_agg(migrate=True)
r = ent.update_many(db.a_entst._id<5, del_insert = True,
**{
'clr':2,
'opt.color':None,
'opt.shape':'',
'opt_lst.*': [dict(id=30, this='', that=''), dict(id=30, this='df0', that='that30')]
})
#ent.update(7, **{'name':'Val', 'opt_lst.*': [dict(id=2 , this='', that='')
# ]})
#ent.insert(**{'opt.color':'red'})
"""
db.commit()
rows = db().select(comp_ent.view_join_parent.ALL)
print rows.as_list()
#db.person._entopt=Storage(vw_pref='prsn')
#db.person.name._entopt=Storage(vw_name='name')
#g=Entity._vw_opt_fld_name(db.person.surname)
pass
