def plominoSqlSync(session, plominoDocument, **table_infos):
"""
table_infos = dict(schema='<schema_table>')
"""
if isinstance(session, basestring):
session = named_scoped_session(session)
engine = session.get_bind()
db = SqlSoup(engine, session=session)
table_name = plominoDocument.Form
main_table = db.entity(table_name, **table_infos)
values = dict()
plominoItems = plominoDocument.getItems()
for column in main_table.c:
if column.key == u"id":
continue
if column.key in plominoItems and (plominoDocument.getItem(column.key, None) != None):
values[column.key] = plominoDocument.getItem(column.key)
plominoDatabase = plominoDocument.getParentDatabase()
values[u"modified_by"] = plominoDatabase.getCurrentUser().id
values[u"last_update"] = plominoDocument.plomino_modification_time.asdatetime()
if plominoDocument.isNewDocument():
values[u"plominoId"] = plominoDocument.id
main_table.insert(**values)
else:
if not main_table.filter(main_table.plominoId == plominoDocument.id).update(values):
values[u"plominoId"] = plominoDocument.id
main_table.insert(**values)
plominoForm = plominoDatabase.getForm(plominoDocument.Form)
plominoFields = plominoForm.getFormFields(includesubforms=True, applyhidewhen=True)
for field in plominoFields:
if field.getFieldType() in ("DATAGRID",) and (plominoDocument.getItem(field.id) != None):
gridItem = plominoDocument.getItem(field.id)
grid_table_name = field.id # oppure field.getSettings(key='associated_form')
try:
grid_table = db.entity(grid_table_name, **table_infos)
except NoSuchTableError, err:
pass
else:
grid_table.filter(grid_table.parentId == plominoDocument.id).delete()
vals = dict()
for record in gridItem:
field_mapping = field.getSettings(key="field_mapping").split(",")
for idx, key in enumerate(field_mapping):
if record[idx] != None:
vals[key] = record[idx]
vals[u"parentId"] = plominoDocument.id
grid_table.insert(**vals)
def testconnection(self, param):
'''
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port, Limit, Table, Map
Where, Encoding, conParams
'''
num=-1
desc=None
port = ""
password = ""
conParams = ""
if param.get("Port"):
port = ":%s" % param.get("Port")
if param.get("Password"):
password = "******" % param.get("Password")
if param.get("conParams"):
conParams = "?%s" % param.get("conParams")
connect_string = "%s://%s%s%s%s%s/%s%s" % (param.get("Driver"),
param.get("User"),
password,
"@" if (param.get("User") or password) else "",
param.get("Server"),
port,
param.get("Database"),
conParams)
log.info("using the connect string %s" % connect_string)
engine = create_engine(connect_string)
# create a configured "Session" class
session = sessionmaker(bind=engine)()
db = SqlSoup(engine)
TABLE = db.entity(param.get("Table"))
try:
result = session.query(TABLE).count()
num = result
desc = "Found %i users." % num
except Exception as exx:
desc = "failed to retrieve users: %s" % exx
return (num, desc)
def suggestFromTable(sessionname, name, columnname, others=[], schema="public", tip="", **filters):
"""
utile per l'implementazione di semplici servizi di auto-suggest da tabella.
sessionname: neme della sessione
name: nome della tabella
columnname: nome della colonna da cui attingere
others: altre colonne cui si è interessati al valore. Usare '' per tutte.
schema: nome dello schema di appartenenza della tabella
tip: "suggerimento"
filters: filtri aggiuntivi del tipo <chiave>=<valore>
"""
session = get_session(sessionname)
engine = session.get_bind()
db = SqlSoup(engine)
table = db.entity(name, schema=schema)
if isinstance(others, (list, tuple)):
# you can submit a list of column values to return, at least an empty list
query = session.query(*[table.c[col] for col in [columnname] + list(others)])
elif others == "filters_only":
# easter egg: useful?
query = session.query(*[table.c[col] for col in [columnname] + filters.keys()])
else:
# otherwise all columns will be returned
query = table
column = table.c[columnname]
tip = tip.rstrip()
# ilike '%(tip)s%%'
where = or_(
column.startswith(tip),
column.startswith(tip.capitalize()),
column.startswith(tip.title()),
column.startswith(tip.lower()),
column.startswith(tip.upper()),
)
# other simple filters
where = and_(where, *[(table.c[k] == v) for k, v in filters.items() if k in table.c.keys()])
return [row.__dict__ for row in query.filter(where).all()]
class IdResolver (UserIdResolver):
searchFields = {
"username": "******",
"userid": "numeric",
"phone": "text",
"mobile": "text",
"surname": "text",
"givenname": "text",
"email": "text"
}
@classmethod
def setup(cls, config=None, cache_dir=None):
'''
this setup hook is triggered, when the server
starts to serve the first request
:param config: the privacyidea config
:type config: the privacyidea config dict
'''
log.info("Setting up the SQLResolver")
return
def __init__(self):
self.resolverId = ""
self.server = ""
self.driver = ""
self.database = ""
self.port = 0
self.limit = 100
self.user = ""
self.password = ""
self.table = ""
self.map = {}
self.reverse_map = {}
self.where = ""
self.encoding = ""
self.conParams = ""
self.connect_string = ""
self.session = None
return
def getSearchFields(self):
return self.searchFields
def checkPass(self, uid, password):
"""
This function checks the password for a given uid.
- returns true in case of success
- false if password does not match
"""
def _check_ssha(pw_hash, password, hashfunc, length):
pw_hash_bin = b64decode(pw_hash.split("}")[1])
digest = pw_hash_bin[:length]
salt = pw_hash_bin[length:]
hr = hashfunc(password)
hr.update(salt)
return digest == hr.digest()
def _check_sha(pw_hash, password):
pw_hash_bin = b64decode(pw_hash[6:])
digest = pw_hash_bin[:20]
hr = hashlib.sha1(password)
return digest == hr.digest()
def _otrs_sha256(pw_hash, password):
hr = hashlib.sha256(password)
digest = binascii.hexlify(hr.digest())
return pw_hash == digest
res = False
userinfo = self.getUserInfo(uid)
database_pw = userinfo.get("password", "XXXXXXX")
if database_pw[:2] == "$P":
# We have a phpass (wordpress) password
PH = PasswordHash()
res = PH.check_password(password, userinfo.get("password"))
# check salted hashed passwords
elif database_pw[:6].upper() == "{SSHA}":
res = _check_ssha(database_pw, password, hashlib.sha1, 20)
elif database_pw[:6].upper() == "{SSHA256}":
res = _check_ssha(database_pw, password, hashlib.sha256, 32)
elif database_pw[:6].upper() == "{SSHA512}":
res = _check_ssha(database_pw, password, hashlib.sha512, 64)
# check for hashed password.
elif userinfo.get("password", "XXXXX")[:5].upper() == "{SHA}":
res = _check_sha(database_pw, password)
elif len(userinfo.get("password"))==64:
# OTRS sha256 password
res = _otrs_sha256(database_pw, password)
return res
def getUserInfo(self,userId):
'''
This function returns all user info for a given userid/object.
:param userId: The userid of the object
:type userId: string
:return: A dictionary with the keys defined in self.map
:rtype: dict
'''
userinfo = {}
try:
conditions=[]
column = self.map.get("userid")
conditions.append( getattr(self.TABLE, column).like(userId) )
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition)
for r in result:
if len(userinfo.keys())>0:
raise Exception("More than one user with userid %s found!" % userId)
userinfo = self._get_user_from_mapped_object(r)
except Exception as exx:
log.error("Could not get the userinformation: %r" % exx)
return userinfo
def getUsername(self,userId):
'''
returns true, if a user id exists
'''
info = self.getUserInfo(userId)
return info.has_key('username')
def getUserId(self, LoginName):
'''
resolve the loginname to the userid.
:param LoginName: The login name from the credentials
:type LoginName: string
:return: UserId as found for the LoginName
'''
userid = ""
try:
conditions=[]
column = self.map.get("username")
conditions.append( getattr(self.TABLE, column).like(LoginName) )
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition)
for r in result:
if userid != "":
raise Exception("More than one user with loginname %s found!" % LoginName)
user = self._get_user_from_mapped_object(r)
userid = user["id"]
except Exception as exx:
log.error("Could not get the userinformation: %r" % exx)
return userid
def _get_user_from_mapped_object(self, ro):
'''
:param r: row
:type r: Mapped Object
:return: User
:rtype: dict
'''
r = ro.__dict__
user = {}
try:
if r.has_key(self.map.get("userid")):
user["id"] = r[self.map.get("userid")]
if r.has_key(self.map.get("username")):
user["username"] = r[self.map.get("username")].decode(self.encoding)
if r.has_key(self.map.get("surname")):
user["surname"] = r[self.map.get("surname")].decode(self.encoding)
if r.has_key(self.map.get("givenname")):
user["givenname"] = r[self.map.get("givenname")].decode(self.encoding)
if r.has_key(self.map.get("email")):
user["email"] = r[self.map.get("email")].decode(self.encoding)
if r.has_key(self.map.get("mobile")):
user["mobile"] = r[self.map.get("mobile")].decode(self.encoding)
if r.has_key(self.map.get("phone")):
user["phone"] = r[self.map.get("phone")].decode(self.encoding)
if r.has_key(self.map.get("password")):
user["password"] = r[self.map.get("password")]
except:
log.error("Failed to convert user: %r" % r)
log.error(traceback.format_exc())
return user
def getUserList(self, searchDict=None):
'''
:param searchDict: A dictionary with search parameters
:type searchDict: dict
:return: list of users, where each user is a dictionary
'''
users = []
conditions = []
if searchDict == None:
searchDict = {}
for key in searchDict.keys():
column = self.map.get(key)
value = searchDict.get(key)
value = value.replace("*", "%")
conditions.append( getattr(self.TABLE, column).like(value) )
if self.where:
# this might result in erros if the
# administrator enters nonsense
(w_column, w_cond, w_value) = self.where.split()
if w_cond.lower() == "like":
conditions.append( getattr(self.TABLE, w_column).like(w_value))
elif w_cond == "==":
conditions.append(getattr(self.TABLE, w_column) == w_value)
elif w_cond == ">":
conditions.append(getattr(self.TABLE, w_column) > w_value)
elif w_cond == "<":
conditions.append(getattr(self.TABLE, w_column) < w_value)
filter_condition = and_(*conditions)
result = self.session.query(self.TABLE).filter(filter_condition).limit(self.limit)
for r in result:
user = self._get_user_from_mapped_object(r)
if user.has_key("id"):
users.append(user)
return users
def getResolverId(self):
'''
Returns the resolver Id
This should be an Identifier of the resolver, preferable the type and the name of the resolver.
'''
return "sqlresolver." + self.resolverId
@classmethod
def getResolverClassType(cls):
return 'sqlresolver'
def getResolverType(self):
return IdResolver.getResolverClassType()
def loadConfig(self, config, conf):
'''
Load the config from conf.
:param config: The configuration from the Config Table
:type config: dict
:param conf: the instance of the configuration
:type conf: string
The information which config entries we need to load is taken from
manage.js: function save_sql_config
'''
self.resolverId = conf
self.server = self.getConfigEntry(config, 'privacyidea.sqlresolver.Server', conf)
self.driver = self.getConfigEntry(config, 'privacyidea.sqlresolver.Driver', conf)
self.database = self.getConfigEntry(config, 'privacyidea.sqlresolver.Database', conf)
self.port = self.getConfigEntry(config, 'privacyidea.sqlresolver.Port', conf, required=False)
self.limit = self.getConfigEntry(config, 'privacyidea.sqlresolver.Limit', conf, required=False, default=100)
self.user = self.getConfigEntry(config, 'privacyidea.sqlresolver.User', conf)
self.password = self.getConfigEntry(config, 'privacyidea.sqlresolver.Password', conf, required=False)
self.table = self.getConfigEntry(config, 'privacyidea.sqlresolver.Table', conf)
usermap = self.getConfigEntry(config, 'privacyidea.sqlresolver.Map', conf)
self.map = yaml.load(usermap)
self.reverse_map = dict([[v,k] for k,v in self.map.items()])
self.where = self.getConfigEntry(config, 'privacyidea.sqlresolver.Where', conf, required=False)
self.encoding = self.getConfigEntry(config, 'privacyidea.sqlresolver.Encoding', conf, required=False, default="latin1")
self.conParams = self.getConfigEntry(config, 'privacyidea.sqlresolver.conParams', conf, required=False)
# create the connectstring like
# driver://user:passwd@seerver/database?conParams
port = ""
password = ""
if self.port:
port = ":%s" % self.port
if self.password:
password = "******" % self.password
self.connect_string = "%s://%s%s@%s%s/%s?%s" % (self.driver,
self.user,
password,
self.server,
port,
self.database,
self.conParams)
log.info("using the connect string %s" % self.connect_string)
self.engine = create_engine(self.connect_string, encoding=str(self.encoding))
# create a configured "Session" class
Session = sessionmaker(bind=self.engine)
# create a Session
self.session = Session()
self.db = SqlSoup(self.engine)
self.TABLE = self.db.entity(self.table)
return self
def getResolverDescriptor(self):
descriptor = {}
typ = self.getResolverType()
descriptor['clazz'] = "useridresolver.SQLIdResolver.IdResolver"
descriptor['config'] = {'Server' : 'string',
'Driver' : 'string',
'Database' : 'string',
'User' : 'string',
'Password' : 'string',
'Port' : 'int',
'Limit' : 'int',
'Table' : 'string',
'Map' : 'string',
'Where' : 'string',
'Encoding' : 'string',
'conParams' : 'string'}
return {typ : descriptor}
def getConfigEntry(self, config, key, conf, required=True, default=None):
ckey = key
cval = ""
if conf != "" or None:
ckey = ckey + "." + conf
if config.has_key(ckey):
cval = config[ckey]
if cval == "":
if config.has_key(key):
cval = config[key]
if cval == "" and required == True:
raise Exception("missing config entry: " + key)
if cval == "" and default:
cval = default
return cval
@classmethod
def testconnection(self, param):
'''
This function lets you test the to be saved SQL connection.
:param param: A dictionary with all necessary parameter to test the connection.
:type param: dict
:return: Tuple of success and a description
:rtype: (bool, string)
Parameters are: Server, Driver, Database, User, Password, Port, Limit, Table, Map
Where, Encoding, conParams
'''
num=-1
desc=None
port = ""
password = ""
conParams = ""
if param.get("Port"):
port = ":%s" % param.get("Port")
if param.get("Password"):
password = "******" % param.get("Password")
if param.get("conParams"):
conParams = "?%s" % param.get("conParams")
connect_string = "%s://%s%s%s%s%s/%s%s" % (param.get("Driver"),
param.get("User"),
password,
"@" if (param.get("User") or password) else "",
param.get("Server"),
port,
param.get("Database"),
conParams)
log.info("using the connect string %s" % connect_string)
engine = create_engine(connect_string)
# create a configured "Session" class
session = sessionmaker(bind=engine)()
db = SqlSoup(engine)
TABLE = db.entity(param.get("Table"))
try:
result = session.query(TABLE).count()
num = result
desc = "Found %i users." % num
except Exception as exx:
desc = "failed to retrieve users: %s" % exx
return (num, desc)
class SincroInventario(object):
""" Finestra di gestione esdportazione variazioni Database
"""
def __init__(self):
print " ACCENDIAMO I MOTORI "
self.runBatch()
def connectDbRemote(self):
""" effettua la connessione al DB remoto """
engine = create_engine('postgres:'+'//'
+USER_REMOTO+':'
+ PASSWORD_REMOTO+ '@'
+ HOST_REMOTO + ':'
+ PORT_REMOTO + '/'
+ DATABASE_REMOTO,
encoding='utf-8',
convert_unicode=True )
tipo_eng = engine.name
engine.echo = False
self.metaRemote = MetaData(engine)
self.pg_db_server_remote = SqlSoup(self.metaRemote)
self.pg_db_server_remote.schema = SCHEMA_REMOTO
self.pg_db_server_main_remote = SqlSoup(self.metaRemote)
self.pg_db_server_main_remote.schema = MAINSCHEMA_REMOTO
SessionRemote = scoped_session(sessionmaker(bind=engine))
self.sessionRemote = SessionRemote()
print ">>>> CONNESSO AL DB REMOTO : %s IP: %s PORTA: %s SCHEMA %s <<<<< " %(DATABASE_REMOTO, HOST_REMOTO, PORT_REMOTO, SCHEMA_REMOTO)
def connectDbLocale(self):
""" effettua la connessione al DB locale """
engineLocale = create_engine('postgres:'+'//'
+ USER_LOCALE +':'
+ PASSWORD_LOCALE + '@'
+ HOST_LOCALE + ':'
+ PORT_LOCALE + '/'
+ DATABASE_LOCALE,
encoding='utf-8',
convert_unicode=True )
tipo_eng = engineLocale.name
engineLocale.echo = False
self.metaLocale = MetaData(engineLocale)
self.pg_db_server_locale = SqlSoup(self.metaLocale)
self.pg_db_server_locale.schema = SCHEMA_LOCALE
self.pg_db_server_main_locale = SqlSoup(self.metaLocale)
self.pg_db_server_main_locale.schema = mainschema_locale
SessionLocale = scoped_session(sessionmaker(bind=engineLocale))
self.engineLocale = engineLocale
self.sessionLocale = SessionLocale()
print ">>>> CONNESSO AL DB LOCALE : %s IP: %s PORTA: %s SCHEMA %s <<<<< " %(DATABASE_LOCALE, HOST_LOCALE, PORT_LOCALE, SCHEMA_LOCALE)
def dammiSoupLocale(self, dao):
soupLocale = None
soupLocale = self.pg_db_server_locale
return soupLocale
def dammiSoupRemoto(self, dao):
soupRemoto = None
soupRemoto = self.pg_db_server_remoto
return soupRemoto
def daosScheme(self):
""" Crea le liste delle query ciclando nelle tabelle """
blocSize = OFFSET
conteggia = self.pg_db_server_remote.entity(TABELLA).count() # serve per poter affettare le select
print "NUMERO DEI RECORD PRESENTI:", conteggia
if conteggia >= blocSize:
blocchi = abs(conteggia/blocSize)
for j in range(0,blocchi+1):
offset = j*blocSize
print "OFFSET", offset , datetime.datetime.now(), "TABELLA", TABELLA
exec ("remote=self.pg_db_server_remote.%s.order_by(self.pg_db_server_remote.%s.%s).limit(blocSize).offset(offset).all()") %(dg[0],dg[0],dg[1])
exec ("locale=self.pg_db_server_locale.%s.order_by(self.pg_db_server_locale.%s.%s).limit(blocSize).offset(offset).all()") %(dg[0],dg[0],dg[1])
self.logica(remote=remote, locale=locale,dao=TABELLA, all=True, offset=offset)
elif conteggia < blocSize: # SI FA LA QUERY IN UN UNICI BOCCONE
exec ("remote=self.pg_db_server_remote.%s.order_by(self.pg_db_server_remote.%s.%s).all()") %(TABELLA,TABELLA,ORDERBY)
exec ("locale=self.pg_db_server_locale.%s.order_by(self.pg_db_server_locale.%s.%s).all()") %(TABELLA,TABELLA,ORDERBY)
self.logica(remote=remote, locale=locale,dao=TABELLA, all=True)
class SincroDB(GladeWidget):
""" Finestra di gestione esdportazione variazioni Database
"""
def __init__(self, conf=None, batch=False, schema=None, fileconf=None, wear= False):
GladeWidget.__init__(self, root='sincro_dialog',
path='sincro_dialog.glade')
self.placeWindow(self.getTopLevel())
self.batch = batch
self.wear = wear
self.promeid = None
if batch: #versione senza interfaccia grafica
print " MI ACCINGO A CARICARE IL FILE configure dalla cartella '%s' ed usare lo schema '%s'" %(fileconf, schema)
Environment.conf = conf
Environment.params["schema"] = schema
#self.batch = batch
def on_tuttecose_checkbutton_toggled(self,toggled):
""" check delle anag da esportare ...le seleziona tutte """
if self.tuttecose_checkbutton.get_active():
self.articoli_togglebutton.set_active(True)
self.clienti_togglebutton.set_active(True)
self.parametri_togglebutton.set_active(True)
self.magazzini_togglebutton.set_active(True)
else:
self.articoli_togglebutton.set_active(False)
self.clienti_togglebutton.set_active(False)
self.parametri_togglebutton.set_active(False)
self.magazzini_togglebutton.set_active(False)
def connectDbRemote(self):
""" effettua la connessione al DB remoto """
SessionRemote = None
self.metaRemote = None
# self.sessionRemote.close_all()
# self.engine.dispose() # added this
# del self.sessionRemote
# del self.engine
mainschema_remoto = Environment.conf.SincroDB.mainschema_remoto
user_remoto = Environment.conf.SincroDB.user_remoto
password_remoto = Environment.conf.SincroDB.password_remoto
host_remoto = Environment.conf.SincroDB.host_remoto
port_remoto = Environment.conf.SincroDB.port_remoto
database_remoto = Environment.conf.SincroDB.database_remoto
engine = create_engine('postgres:'+'//'
+user_remoto+':'
+ password_remoto+ '@'
+ host_remoto + ':'
+ port_remoto + '/'
+ database_remoto,
encoding='utf-8',
convert_unicode=True )
tipo_eng = engine.name
engine.echo = False
self.metaRemote = MetaData(engine)
self.pg_db_server_remote = SqlSoup(self.metaRemote)
self.pg_db_server_remote.schema = Environment.params["schema"]
self.pg_db_server_main_remote = SqlSoup(self.metaRemote)
self.pg_db_server_main_remote.schema = mainschema_remoto
SessionRemote = scoped_session(sessionmaker(bind=engine))
self.sessionRemote = SessionRemote()
print ">>>> CONNESSO AL DB REMOTO : %s IP: %s PORTA: %s SCHEMA %s <<<<< " %(database_remoto, host_remoto, port_remoto, Environment.params["schema"])
def connectDbLocale(self):
""" effettua la connessione al DB locale """
SessionLocale = None
self.engineLocale = None
self.metaLocale = None
# self.sessionLocale.close_all()
# self.engineLocale.dispose() # added this
# del self.sessionLocale
# del self.engineLocale
mainschema_locale = Environment.conf.SincroDB.mainschema_locale
user_locale = Environment.conf.SincroDB.user_locale
password_locale = Environment.conf.SincroDB.password_locale
host_locale = Environment.conf.SincroDB.host_locale
port_locale = Environment.conf.SincroDB.port_locale
database_locale = Environment.conf.SincroDB.database_locale
engineLocale = create_engine('postgres:'+'//'
+user_locale+':'
+ password_locale+ '@'
+ host_locale + ':'
+ port_locale + '/'
+ database_locale,
encoding='utf-8',
convert_unicode=True )
tipo_eng = engineLocale.name
engineLocale.echo = False
self.metaLocale = MetaData(engineLocale)
self.pg_db_server_locale = SqlSoup(self.metaLocale)
self.pg_db_server_locale.schema = Environment.params["schema"]
self.pg_db_server_main_locale = SqlSoup(self.metaLocale)
self.pg_db_server_main_locale.schema = mainschema_locale
SessionLocale = scoped_session(sessionmaker(bind=engineLocale))
self.engineLocale = engineLocale
self.sessionLocale = SessionLocale()
print ">>>> CONNESSO AL DB LOCALE : %s IP: %s PORTA: %s SCHEMA %s <<<<< " %(database_locale, host_locale, port_locale, Environment.params["schema"])
def daosMain(self, tables=None):
""" Crea le liste delle query ciclando nelle tabelle principali"""
for dg in tables:
self.table_label.set_text(str(dg[0]).upper())
self.avanzamento_pgbar.pulse()
exec ("remote=self.pg_db_server_main_remote.%s.order_by((self.pg_db_server_main_remote.%s.%s).asc()).all()") %(dg[0], dg[0], dg[1])
exec ("locale=self.pg_db_server_main_locale.%s.order_by((self.pg_db_server_main_locale.%s.%s).asc()).all()") %(dg[0], dg[0], dg[1])
print "QUESTO E' IL DAO DELLE TABELLE MAIN IN LAVORAZIONE ...", dg[0]
self.logica(remote=remote, locale=locale, dao=dg[0], all=True)
print "<<<<<<<< FINITO CON LO SCHEMA PRINCIPALE >>>>>>>>", datetime.datetime.now()
def dammiSoupLocale(self, dao):
soupLocale = None
if dao in ["operazione","tipo_aliquota_iva","stato_articolo","unita_base",
"tipo_recapito","denominazione"]:
soupLocale = self.pg_db_server_main_locale
else:
soupLocale = self.pg_db_server_locale
return soupLocale
def gestisciListinoArticolo(self, dg=None):
blocSize = int(Environment.conf.SincroDB.offset)
listini=self.pg_db_server_remote.listino.order_by(self.pg_db_server_remote.listino.id).all()
for li in listini:
self.avanzamento_pgbar.pulse()
conteggia = self.pg_db_server_remote.entity("listino_articolo").filter_by(id_listino=li.id).count()
conteggia_locale = self.pg_db_server_locale.entity("listino_articolo").filter_by(id_listino=li.id).count()
safer = False
if conteggia_locale>conteggia:
safer=True
print "GLI ARTICOLI SONO:",conteggia, "ID LISTINO", li.id
if conteggia >= blocSize:
blocchi = abs(conteggia/blocSize)
for j in range(0,blocchi+1):
self.avanzamento_pgbar.pulse()
offset = j*blocSize
print "SPEZZETTO IL LISTINO OFFSET", offset, datetime.datetime.now(), "TABELLA", dg[0]
remote=self.pg_db_server_remote.listino_articolo.\
filter_by(id_listino=li.id).\
order_by(self.pg_db_server_remote.listino_articolo.id_articolo,
self.pg_db_server_remote.listino_articolo.data_listino_articolo).\
limit(blocSize).\
offset(offset).all()
locale=self.pg_db_server_locale.\
listino_articolo.\
filter_by(id_listino=li.id).\
order_by(self.pg_db_server_remote.listino_articolo.id_articolo,
self.pg_db_server_locale.listino_articolo.data_listino_articolo).\
limit(blocSize).\
offset(offset).all()
print "REMOTEEEE", len(remote), "LOCALEEEEEE", len(locale)
if len(locale)>len(remote):
print "ATTENZIONEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE", len(remote), len(locale)
self.manageListinoArticoloSafe(remote, locale, safer=safer)
# self.logica(remote=remote, locale=locale,dao=dg[0], all=True, offset=None)
elif conteggia < blocSize:
remote=self.pg_db_server_remote.listino_articolo.filter_by(id_listino=li.id).order_by(self.pg_db_server_remote.listino_articolo.id_articolo,self.pg_db_server_remote.listino_articolo.data_listino_articolo).all()
locale=self.pg_db_server_locale.listino_articolo.filter_by(id_listino=li.id).order_by(self.pg_db_server_remote.listino_articolo.id_articolo,self.pg_db_server_locale.listino_articolo.data_listino_articolo).all()
print "REMOTEEEE", len(remote), "LOCALEEEEEE", len(locale), "NON SPACCHETTATO"
if len(locale)>len(remote):
print "ATTENZIONEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE", len(remote), len(locale)
self.manageListinoArticoloSafe(remote, locale, safer=safer)
# self.logica(remote=remote, locale=locale,dao=dg[0], all=True)
return True
def daosScheme(self, tables=None, scarto=0):
""" Crea le liste delle query ciclando nelle tabelle """
blocSize = int(Environment.conf.SincroDB.offset)
#blocSize = 500
for dg in tables:
self.table_label.set_text(str(dg[0]).upper())
#listino lo gestisco facendo select per id listino così gestisco meglio i nuovi record
if dg[0] =="listino_articolo":
self.gestisciListinoArticolo(dg)
else:
# self.avanzamento_pgbar.pulse()
print "TABELLA IN LAVORAZIONE :", dg[0]
conteggia = self.pg_db_server_remote.entity(dg[0]).count() # serve per poter affettare le select
conteggia_locale = self.pg_db_server_locale.entity(dg[0]).count()
safer = False
if conteggia_locale>conteggia:
safer=True
print "NUMERO DEI RECORD PRESENTI:", conteggia
if conteggia >= blocSize:
blocchi = abs(conteggia/blocSize)
for j in range(0,blocchi+1):
offset = j*blocSize
print "OFFSET", offset , datetime.datetime.now(), "TABELLA", dg[0]
exec ("remote=self.pg_db_server_remote.%s.order_by(self.pg_db_server_remote.%s.%s).limit(blocSize).offset(offset).all()") %(dg[0],dg[0],dg[1])
exec ("locale=self.pg_db_server_locale.%s.order_by(self.pg_db_server_locale.%s.%s).limit(blocSize).offset(offset).all()") %(dg[0],dg[0],dg[1])
if str(remote[0]._table).split(".")[1] =="articolo":
self.manageArticoloSafe(remote)
elif str(remote[0]._table).split(".")[1] =="codice_a_barre_articolo":
self.manageCodBarreSafe(remote, locale, safer=safer)
elif str(remote[0]._table).split(".")[1] =="fornitura":
self.manageFornituraSafe(remote)
elif str(remote[0]._table).split(".")[1] =="sconto":
self.manageScontoSafe(remote, locale, safer=safer)
elif str(remote[0]._table).split(".")[1] =="listino_complesso_listino":
self.manageListinoComplessoListinoSafe(remote)
elif str(remote[0]._table).split(".")[1] =="stoccaggio":
self.manageStoccaggioSafe(remote)
elif str(remote[0]._table).split(".")[1] =="sconti_vendita_dettaglio":
self.manageScontiVenditaDettaglioSafe(remote, locale, safer=safer)
elif str(remote[0]._table).split(".")[1] =="sconti_vendita_ingrosso":
self.manageScontiVenditaIngrossoSafe(remote)
elif str(remote[0]._table).split(".")[1] =="listino_magazzino":
self.manageListinoMagazzinoSafe(remote)
else:
self.logica(remote=remote, locale=locale,dao=dg[0], all=True, offset=offset)
elif conteggia < blocSize: # SI FA LA QUERY IN UN UNICI BOCCONE
exec ("remote=self.pg_db_server_remote.%s.order_by(self.pg_db_server_remote.%s.%s).all()") %(dg[0],dg[0],dg[1])
exec ("locale=self.pg_db_server_locale.%s.order_by(self.pg_db_server_locale.%s.%s).all()") %(dg[0],dg[0],dg[1])
self.logica(remote=remote, locale=locale,dao=dg[0], all=True)
print "<<<<<<<< FINITO CON LO SCHEMA AZIENDA >>>>>>>>"
print "<<<<<<< INIZIATO :", self.tempo_inizio, " FINITO:", datetime.datetime.now() , ">>>>>>>>>>>>>"
self.run =False
if not self.batch:
gobject.source_remove(self.timer)
self.timer = 0
self.avanzamento_pgbar.destroy()
self.table_label.set_text("SINCRONIZZAZIONE TERMINATA CON SUCCESSO")
else:
print "SINCRONIZZAZIONE TERMINATA CON SUCCESSO"
def logica(self,remote=None, locale=None,dao=None,all=False,offset=None):
""" cicla le righe della tabella e decide cosa fare
diciamo che è la funzione principale
"""
soupLocale = self.dammiSoupLocale(dao)
deleteRow=False
if not remote and not locale:
return False
# comparazione tra le query locali e quelle remote.
# print "VEDIAMO", str(list(remote))
if str(list(remote)) != str(list(locale)):
if len(remote) == len(locale):
print "STESSO NUMERO DI RECORD", len(remote)
elif len(remote) > len(locale):
print "IL DB REMOTO CONTIENE PIU' RECORD", len(remote), "vs", len(locale)
else:
print "IL DB LOCALE CONTIENE PIU' RECORD", len(remote), "vs", len(locale)
deleteRow=True
if 5==3:
pass
else:
for i in range(0,(len(remote))):
self.tabe = str(remote[i]._table).split(".")[1]
if i <= (len(locale)-1):
# try:
# comparo gli elementi con lo stesso indice che è
# la cosa più semplice
do = False
dao_locale_ex = None
# print "LEN LOCALE",i
rem_dic = remote[i].__dict__
loc_dic = locale[i].__dict__
# print rem_dic, loc_dic
for k,v in rem_dic.items():
if k != "_sa_instance_state":
if rem_dic[k] != loc_dic[k]:
# print "ORAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA", rem_dic[k], loc_dic[k]
do = True
tabe = str(remote[i]._table).split(".")[1]
if tabe == "articolo" and k =="denominazione":
dao_locale_ex = locale[i]
elif tabe == "listino_articolo":
dao_locale_ex = locale[i]
break
if do:
print "PROCEDO CON UN UPDATE", str(remote[i]._table).split(".")[1]
# print
# print "LOCALE:", locale[i]
# print
# print "REMOTE:", remote[i]
self.fixToTable(soupLocale=soupLocale,
rowMaster=remote[i],
rowSlave=locale[i],
op="UPDATE",
dao=str(remote[i]._table).split(".")[1],
save=True,
offset=offset,
dao_locale_ex = dao_locale_ex)
# print " FATTO"
do = False
continue
else:
print " ", str(remote[i]._table).split(".")[1], "INSERT", i
#print " RIGA REMOTE", remote[i]
self.fixToTable(soupLocale=soupLocale,
rowMaster=remote[i],
op="INSERT",
dao=str(remote[i]._table).split(".")[1],
save=False)
if self.tabe != "articolo":
try:
sqlalchemy.ext.sqlsoup.Session.commit()
except Exception, e:
if self.tabe=="listino_articolo":
print "ERRORE NEI LISTINI", e
sqlalchemy.ext.sqlsoup.Session.rollback()
record_id1 = self.pg_db_server_locale.listino_articolo.filter_by(id_listino=remote[i].id_listino).all()
if record_id1:
for r in record_id1:
sqlalchemy.ext.sqlsoup.Session.delete(r)
sqlalchemy.ext.sqlsoup.Session.commit()
self.daosScheme(tables=[("listino_articolo","id_listino")])
else:
print "ERRORE NEL SALVATAGGIO DEI RECORD", e
sqlalchemy.ext.sqlsoup.Session.rollback()
self.azzeraTable(table=dao)
if deleteRow and self.tabe !="articolo":
for i in range(len(remote),len(locale)):
print "QUESTA È LA RIGA DA rimuovere ", str(locale[i]._table).split(".")[1], "Operazione DELETE"
self.fixToTable(soupLocale=soupLocale,
#row=locale[i],
rowSlave = locale[i],
op="DELETE",
dao=str(locale[i]._table).split(".")[1],
save=True)
else:
