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: