def mt_upload(login, batch_nr, sqls):
global UPL_POOL_CONNECTIONS
if is_empty(sqls):
print('UPL: %3d, sqls = %4d, rows = %6d' % (batch_nr, -1, -1))
return 0, 0
name = multiprocessing.current_process().name
if name not in UPL_POOL_CONNECTIONS:
print('CREATING TRG DB CONNECTION FOR: ' + name)
UPL_POOL_CONNECTIONS[name] = Jdbc(login)
jdbc = UPL_POOL_CONNECTIONS[name]
rc1 = 0
rc2 = 0
with InputParser(sqls) as parser:
cursor = None
for sql in parser.parse():
rc1 += 1
try:
sql.encode().decode('ascii')
c = jdbc.execute(sql, cursor=cursor)
cursor = c
except Exception as ex:
rc2 += 1
print('%2d %3d %s' % (batch_nr, rc1, sql))
#if cursor is not None:
# rc2 = cursor.rowcount
print('UPL: %3d, sqls = %4d, rows = %6d' % (batch_nr, rc1, rc2))
if cursor is not None:
jdbc.commit()
return rc1, rc2
def set_log_stream(self):
if is_empty(self.args.log):
return None
elif self.args.log.lower() == 'stdin':
return sys.stdin
elif self.args.log.lower() == 'stdout':
return sys.stdout
else:
return open(self.args.log, 'w')
def main():
# if (len(sys.argv) > 1) and (sys.argv[1].lower() == '--version'):
# print('%s, version: %s' % (os.path.basename(sys.argv[0]), __version__))
# sys.exit(0)
# args = parser.parse_args()
# if args.version:
# print('%s, version: %s' % (os.path.basename(sys.argv[0]), __version__))
# sys.exit(0)
included_tables = []
# if args.tables is not None:
# for t in args.tables.split(','):
# t = t.strip().upper()
# if (len(t) > 0) and (t not in included_tables):
# included_tables.append(t)
# excluded_tables = []
# n_excluded = 0
# if args.exclude is not None:
# try:
# n_excluded = int(args.exclude)
# except ValueError:
# for t in args.exclude.split(','):
# t = t.strip().upper()
# if (len(t) > 0) and (t not in excluded_tables):
# excluded_tables.append(t)
jdbc = {SRC: args.login_source, TRG: args.login_target}
# information on table constraints and references
table_info = dict()
# information on the primary keys of tables
pk_info = dict()
for key in [SRC, TRG]:
login = jdbc[key]
try:
con = lwetl.Jdbc(login)
except (lwetl.ServiceNotFoundException, lwetl.DriverNotFoundException,
ConnectionError) as login_error:
print("ERROR for '%s': %s" % (jdbc[key], str(login_error)))
sys.exit(1)
jdbc[key] = con
tag_connection(key, con)
if con.type not in content_queries:
print("ERROR: database type '%s' not supported." % con.type)
sys.exit(1)
sql = content_queries[con.type]
if '@SCHEMA@' in sql:
sql = sql.replace('@SCHEMA@', con.schema)
print('Query %s database: %s' % (key.upper(), login))
try:
con.execute(sql)
except lwetl.SQLExcecuteException as exec_error:
print('ERROR: cannot retrieve database info for: ' + login)
print(exec_error)
sys.exit(1)
tables = dict()
pk_col = dict()
for d in con.get_data(return_type=OrderedDict):
table_name = d['TABLE_NAME'].upper()
if table_name not in tables:
tables[table_name] = dict()
if not is_empty(d.get('FK_TABLE', None)):
tables[table_name][d['COLUMN_NAME'].upper(
)] = d['FK_TABLE'].upper(), d['CONSTRAINT_NAME'].upper()
if d.get('KEY_TYPE', None) == 'PK':
pk_col[table_name] = d['COLUMN_NAME'].upper()
table_info[key] = tables
pk_info[key] = pk_col
table_admin = dict()
for t in [COMMON, EMPTY, IGNORED, MISSING, NO_SOURCE]:
table_admin[t] = []
table_count = dict()
for t in sorted(
[k for k in table_info[SRC].keys() if k in table_info[TRG]]):
sql = 'SELECT COUNT(*) FROM ' + t
n1 = jdbc[SRC].get_int(sql)
n2 = jdbc[TRG].get_int(sql)
table_count[t] = n1, n2
if (t not in excluded_tables) and \
((len(included_tables) == 0) or (t in included_tables)):
if n1 == 0:
table_admin[EMPTY].append(t)
else:
if (n2 == 0) or (args.mode != COPY_EMPTY):
table_admin[COMMON].append(t)
else:
table_admin[IGNORED].append(t)
else:
table_admin[IGNORED].append(t)
print_list('Tables to copy', table_admin[COMMON], table_count)
print_list('Tables ignored (not empty or marked)', table_admin[IGNORED],
table_count)
print_list('Empty source', table_admin[EMPTY], table_count)
missing_tables = sorted(
[k for k in table_info[SRC].keys() if k not in table_info[TRG]])
print_list('Tables not defined on target', missing_tables)
nosource_tables = sorted(
[k for k in table_info[TRG].keys() if k not in table_info[SRC]])
print_list('Missing source:', nosource_tables)
common_tables = table_admin[COMMON]
if len(common_tables) < 2:
copy_list = common_tables
else:
# re-order the list of tables, to avoid FK violations
copy_list = []
while len(copy_list) < len(common_tables):
not_added = [t for t in common_tables if t not in copy_list]
refered_tables = referring_tables(not_added, table_info[TRG],
copy_list)
while len(refered_tables) > 0:
not_added = [t for t in refered_tables]
refered_tables = referring_tables(not_added, table_info[TRG],
copy_list)
copy_list += not_added
if n_excluded > 0:
print_list('Skipped tables', copy_list[:n_excluded], table_count)
copy_list = copy_list[n_excluded:]
copy_list = [t for t in copy_list if t not in table_admin[IGNORED]]
print_list('Copy process will use the following order', copy_list,
table_count)
if args.list:
clean_exit(jdbc, args, 0)
elif len(copy_list) == 0:
print('No tables to copy found: exiting.')
clean_exit(jdbc, args, 0)
commit_mode = lwetl.UPLOAD_MODE_ROLLBACK
if args.activate:
print('Activating upload.')
commit_mode = lwetl.UPLOAD_MODE_COMMIT
if (args.mode in [COPY_AND_UPDATE, COPY_AND_SYNC]) and (args.driver
== DRIVER_MULTI):
print(
'WARNING: multi mode not supported for updates. Switching to single.'
)
args.mode = DRIVER_SINGLE
counters = {CNT_COPIED_TABLES: 0, CNT_FAIL: 0}
too_many_errors = False
is_update = args.mode in [COPY_AND_UPDATE, COPY_AND_SYNC]
for t in copy_list:
counters[CNT_COPIED_TABLES] += 1
n, n2 = table_count[t]
print("CC %3d. of %d: copy %-30s n = %6d values (PK = %s) ......." %
(counters[CNT_COPIED_TABLES], len(copy_list), t, n,
pk_info[SRC][t]))
existing_records = []
# target primary key
pk_trg = pk_info[TRG][t]
if (n2 > 0) and (args.mode != COPY_EMPTY):
for r in jdbc[TRG].query("SELECT {0} FROM {1} ORDER BY {0}".format(
pk_trg, t)):
existing_records.append(r[0])
print('Found %d existing records from %s to %s' %
(len(existing_records), min(existing_records),
max(existing_records)))
existing_records = set(existing_records)
try:
if args.reverse_insert or args.update_fast:
pk_order = 'DESC'
else:
pk_order = 'ASC'
cursor = jdbc[SRC].execute(
'SELECT * FROM {} ORDER BY {} {}'.format(
t, pk_info[SRC][t], pk_order),
cursor=None)
except lwetl.SQLExcecuteException as exec_error:
print('ERROR: table %s skipped on SQL retrieve error: ' +
str(exec_error))
too_many_errors = True
cursor = None
if too_many_errors:
break
row_count = 0
skp_count = 0
upd_count = 0
new_count = 0
found_records = []
t0_table = datetime.now()
try:
with UPLOADERS[args.driver](jdbc[TRG],
t.lower(),
commit_mode=commit_mode) as uploader:
for d in jdbc[SRC].get_data(cursor=cursor,
return_type=dict,
include_none=is_update):
row_count += 1
pk = d[pk_trg]
if args.mode == COPY_AND_SYNC:
found_records.append(pk)
record_exists = (pk in existing_records)
if record_exists and (not is_update):
skp_count += 1
if args.update_fast:
print(
'Huristic fast update of %s. Skipping at rowcount %d'
% (t, row_count))
break
else:
try:
if record_exists:
del d[pk_trg]
uploader.update(d, {pk_trg: pk})
upd_count += 1
else:
if is_update:
none_keys = [
c for c, v in d.items() if v is None
]
for k in none_keys:
del d[k]
uploader.insert(d)
new_count += 1
except lwetl.SQLExcecuteException as insert_exception:
counters[CNT_FAIL] += 1
print('Insert error (%d) on row %d: %s' %
(counters[CNT_FAIL], row_count,
str(insert_exception)))
if (args.max_fail >= 0) and (counters[CNT_FAIL] >
args.max_fail):
print('Too many errors: terminating.')
too_many_errors = True
if too_many_errors:
raise TooMayErrorsException(
'Insert or Update failed %d times' %
counters[CNT_FAIL])
has_commit = False
if uploader.row_count >= args.commit_nr:
uploader.commit()
has_commit = True
if has_commit or ((row_count % args.commit_nr) == 0):
print(
'%8d. %5.1f %% of %d records, new: %8d, upd: %8d, ign: %8d. %s. Est. remaining time: %s'
% (row_count, (100.0 * row_count / n), n,
new_count, upd_count, skp_count, t,
estimate_remaining(t0_table, row_count, n)))
if (args.max_rows > 0) and (
(new_count + upd_count) > args.max_rows):
print('Terminating after %d uploads on user request.' %
row_count)
break
if uploader.row_count > 0:
uploader.commit()
print(
'%8d. %5.1f %% of %d records, new: %8d, upd: %8d, ign: %8d. %s. finished'
% (row_count, (100.0 * row_count / n), n, new_count,
upd_count, skp_count, t))
else:
print(
'Update of %s finished, No further commits. rc = %d' %
(t, row_count))
if (new_count + upd_count) > 0:
dt = datetime.now() - t0_table
dt_sec = dt.total_seconds()
if dt_sec > 0:
rec_per_sec = int(round(1.0 * n / dt_sec))
else:
rec_per_sec = 0
print(
'%8d. %5.1f %% of %d records, new: %8d, upd: %8d, ign: %8d. %s. Used time: %s (%d rec/s)'
% (row_count,
(100.0 * row_count / n), n, new_count, upd_count,
skp_count, t, timedelta_to_string(dt), rec_per_sec))
if args.mode == COPY_AND_SYNC:
to_delete = list(existing_records - set(found_records))
if len(to_delete) > 0:
print('Sync: removing %d obsolete records in %s (target)' %
(len(to_delete), t))
while len(to_delete) > 0:
if len(to_delete) > 500:
delete_list = to_delete[0:500]
to_delete = to_delete[500:]
else:
delete_list = [pk for pk in to_delete]
to_delete = []
par_list = ['?'] * len(delete_list)
sql = 'DELETE FROM {0} WHERE {1} IN ({2})'.format(
t, pk_trg, ','.join(par_list))
try:
jdbc[TRG].execute(sql, delete_list, cursor=None)
except lwetl.SQLExcecuteException as delete_exception:
counters[CNT_FAIL] += len(delete_list)
print(delete_exception)
print('Delete error (%d) in table %s on rows %s' %
(counters[CNT_FAIL], t, ', '.join(
[str(pk) for pk in delete_list])))
if (args.max_fail >= 0) and (counters[CNT_FAIL] >
args.max_fail):
print('Too many errors: terminating.')
too_many_errors = True
if too_many_errors:
raise TooMayErrorsException(
'Insert, Update, and Delete failed %d times'
% counters[CNT_FAIL])
if commit_mode == lwetl.UPLOAD_MODE_COMMIT:
jdbc[TRG].commit()
else:
jdbc[TRG].rollback()
except lwetl.CommitException as ce:
counters[CNT_FAIL] += 1
if not (args.ignore_commit_errors and (args.max_fail > 0) and
(counters[CNT_FAIL] <= args.max_fail)):
print(
'Upload encountered a commit exception row {}. Further processing ignored: {}'
.format(row_count, str(ce)),
file=sys.stderr)
too_many_errors = True
except TooMayErrorsException as tee:
print(
'Upload encountered on row {}. Further processing ignored: {}'.
format(row_count, str(tee)),
file=sys.stderr)
too_many_errors = True
if too_many_errors:
break
if counters[CNT_FAIL] > 0:
print('WARNING: not all data has been transfered. Errors = %d' %
counters[CNT_FAIL])
rc = 1 if too_many_errors else 0
clean_exit(jdbc, args, rc)
def main():
if (len(sys.argv) > 1) and (sys.argv[1].lower() == '--version'):
# skip parsing
return show_version()
args = parser.parse_args()
if args.version:
return show_version()
if args.activate:
args.commit_mode = lwetl.UPLOAD_MODE_COMMIT
if args.login.strip().lower() == 'list':
lwetl.print_info()
return 0
# check input command or login alias
if len(args.login.strip()) == 0:
print('ERROR: login credentials not defined.')
parser.print_help()
return 1
try:
jdbc = lwetl.Jdbc(args.login)
except (lwetl.ServiceNotFoundException, lwetl.DriverNotFoundException,
ConnectionError) as login_error:
print('ERROR - %s - %s' %
(type(login_error).__name__, str(login_error)),
file=sys.stderr)
return 1
sql = None
if is_empty(args.command_or_sql):
print('Command or SQL not specified: using the stdin')
elif args.command_or_sql.strip().lower() == 'jdbc_info':
return show_jdbc_info(args.login)
elif args.command_or_sql.strip().lower() == 'table_info':
try:
sql = get_table_info_sql(jdbc)
except LookupError as le:
print(le)
return 1
elif os.path.isfile(args.command_or_sql):
input_file = args.command_or_sql
elif ' ' not in args.command_or_sql.strip():
# the input might be a table name -> test this
table_name = args.command_or_sql.strip()
try:
jdbc.query("SELECT * FROM {0} WHERE 0=1".format(table_name))
except lwetl.SQLExcecuteException:
table_name = None
if table_name is not None:
if args.file_name is None:
sql = 'SELECT * FROM ' + table_name
elif os.path.isfile(args.file_name):
return upload_table(jdbc, args.commit_mode, args.commit_nr,
args.max_rows, table_name, args.file_name,
args.format, args.separator, args.log_file)
else:
print('ERROR: specified input file not found: ' +
args.file_name)
return 1
else:
sql = args.command_or_sql
if sql is None:
sql = sys.stdin
return parse_sql_commands(jdbc, sql, args)
if uploader.row_count >= batch_size:
commit_nr += 1
commits[commit_nr] = pool.apply_async(
mt_upload, (login_target, commit_nr, uploader.commit()))
# insert the new associations
for id_client, found_names in client_usernames.items():
uploader.insert({
'ID': id_client,
'USERNAME': '******'.join(sorted(set(found_names)))
})
counter.inserted += 1
if uploader.row_count >= batch_size:
commit_nr += 1
commits[commit_nr] = pool.apply_async(
mt_upload, (login_target, commit_nr, uploader.commit()))
sqls = uploader.commit()
if not is_empty(sqls):
commit_nr += 1
commits[commit_nr] = pool.apply_async(mt_upload,
(login_target, commit_nr, sqls))
sql_count, row_count = get_pool_results(commits)
counter.committed += sql_count
counter.rowcount += row_count
print('Done: ' + str(counter))
print(get_execution_statistics())