def build_columns(self, con):
"""Return columns from the table."""
if not con.sys_schema:
tbname = self.name.split('.')[-1]
ldebug('build_columns {}'.format(tbname))
cols = []
typs = []
cmd = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{}'"\
.format(tbname)
execute(con, cmd)
totalcol = 0
while True:
row = con.cursor.fetchone()
if not row:
break
totalcol += 1
col = row[3].encode('utf8')
typ = row[7]
if self.icols is not None and col not in self.icols:
continue
elif self.ecols is not None and col in self.ecols:
continue
cols.append(col)
typs.append(typ)
# ldebug("col: {} [{}]".format(col, typ))
if len(cols) < totalcol:
self.str_cols = ', '.join(cols)
self.columns = cols
self.types = typs
def build_columns(self, con):
"""Return columns from the table."""
if not con.sys_schema:
tbname = self.name.split('.')[-1]
ldebug('build_columns {}'.format(tbname))
cols = []
typs = []
cmd = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{}'"\
.format(tbname)
execute(con, cmd)
totalcol = 0
while True:
row = con.cursor.fetchone()
if not row:
break
totalcol += 1
col = row[3].encode('utf8')
typ = row[7]
if self.icols is not None and col not in self.icols:
continue
elif self.ecols is not None and col in self.ecols:
continue
cols.append(col)
typs.append(typ)
# ldebug("col: {} [{}]".format(col, typ))
if len(cols) < totalcol:
self.str_cols = ', '.join(cols)
self.columns = cols
self.types = typs
def table_rowcnt_by_date(con, tbname, date):
ldebug("table_rowcnt_by_date {} of {}".format(tbname, date))
cmd = "SELECT COUNT(*) FROM {} WHERE CAST(LogTime as DATE)"\
" = CAST('{}' as DATETIME);".format(tbname, date)
execute(con, cmd)
rows = con.cursor.fetchall()
return int(rows[0][0])
def table_rowcnt_by_date(con, tbname, date):
ldebug("table_rowcnt_by_date {} of {}".format(tbname, date))
cmd = "SELECT COUNT(*) FROM {} WHERE CAST(LogTime as DATE)"\
" = CAST('{}' as DATETIME);".format(tbname, date)
execute(con, cmd)
rows = con.cursor.fetchall()
return int(rows[0][0])
def updated_day_tables(dcfg, con, date):
ldebug("updated_day_tables {}".format(date))
res, rpath = read_table_info(dcfg)
tbnames = con.table_names
for tbname in tbnames:
curcnt = table_rowcnt_by_date(con, tbname, date)
dtable = "{}_{}".format(str(tbname), normalize_date_str(date))
changed = res is None or curcnt != res.get(dtable, -1)
if changed:
yield tbname
def updated_day_tables(dcfg, con, date):
ldebug("updated_day_tables {}".format(date))
res, rpath = read_table_info(dcfg)
tbnames = con.table_names
for tbname in tbnames:
curcnt = table_rowcnt_by_date(con, tbname, date)
dtable = "{}_{}".format(str(tbname), normalize_date_str(date))
changed = res is None or curcnt != res.get(dtable, -1)
if changed:
yield tbname
def _sync_files(scfg):
bfolder = scfg['base_folder']
recurse = scfg['recurse']
ptrn = scfg['filename_pattern']
to_url = scfg['to_url']
ldebug("Sync files: {} {} {}".format(bfolder, ptrn, recurse))
from wdfwd.sync import find_file_by_ptrn
files = find_file_by_ptrn(bfolder, ptrn, recurse)
from wdfwd.sync import sync_files
sync_files(bfolder, files, to_url)
def _sync_files(scfg):
bfolder = scfg['base_folder']
recurse = scfg['recurse']
ptrn = scfg['filename_pattern']
to_url = scfg['to_url']
ldebug("Sync files: {} {} {}".format(bfolder, ptrn, recurse))
from wdfwd.sync import find_file_by_ptrn
files = find_file_by_ptrn(bfolder, ptrn, recurse)
from wdfwd.sync import sync_files
sync_files(bfolder, files, to_url)
def start_tailing():
linfo("start_tailing")
supress_boto3_log()
if not tailc:
ldebug("no tailing config. return")
return
for i, ti in enumerate(iter_tail_info(tailc)):
if isinstance(ti, TableTailInfo):
linfo("start table tail - {}".format(ti))
tailer = TableTailer(
tailc,
ti.table,
ti.tag,
ti.pos_dir,
ti.scfg,
ti.datefmt,
ti.col_names,
send_term=ti.send_term,
encoding=ti.encoding,
lines_on_start=ti.lines_on_start,
max_between_data=ti.max_between_data,
millisec_ndigit=ti.millisec_ndigit,
key_idx=ti.key_idx,
start_key_sp=ti.start_key_sp,
latest_rows_sp=ti.latest_rows_sp
)
elif isinstance(ti, FileTailInfo):
linfo("start file tail - {}".format(ti))
tailer = FileTailer(
ti.bdir,
ti.ptrn,
ti.tag,
ti.pos_dir,
ti.scfg,
send_term=ti.send_term,
update_term=ti.update_term,
elatest=ti.latest,
encoding=ti.file_enc,
lines_on_start=ti.lines_on_start,
max_between_data=ti.max_between_data,
format=ti.format,
parser=ti.parser,
order_ptrn=ti.order_ptrn,
reverse_order=ti.reverse_order)
name = ti.tag
linfo("create & start {} thread".format(name))
trd = TailThread(name, tailer)
tail_threads.append(trd)
trd.start()
def parse_line(self, line):
ldebug("parse_line")
if self.encoding:
line = decode(line, self.encoding)
for fmt in self.formats:
if fmt.parse(line, True):
self.parsed = fmt.taken
self.completed += 1
return True
self.parsed = None
return False
def parse_line(self, line):
ldebug("parse_line")
if self.encoding:
line = decode(line, self.encoding)
for fmt in self.formats:
if fmt.parse(line, True):
self.parsed = fmt.taken
self.completed += 1
return True
self.parsed = None
return False
def start_tailing():
linfo("start_tailing")
supress_boto3_log()
if not tailc:
ldebug("no tailing config. return")
return
for i, ti in enumerate(iter_tail_info(tailc)):
if isinstance(ti, TableTailInfo):
linfo("start table tail - {}".format(ti))
tailer = TableTailer(tailc,
ti.table,
ti.tag,
ti.pos_dir,
ti.scfg,
ti.datefmt,
ti.col_names,
send_term=ti.send_term,
encoding=ti.encoding,
lines_on_start=ti.lines_on_start,
max_between_data=ti.max_between_data,
millisec_ndigit=ti.millisec_ndigit,
key_idx=ti.key_idx,
start_key_sp=ti.start_key_sp,
latest_rows_sp=ti.latest_rows_sp)
elif isinstance(ti, FileTailInfo):
linfo("start file tail - {}".format(ti))
tailer = FileTailer(ti.bdir,
ti.ptrn,
ti.tag,
ti.pos_dir,
ti.scfg,
send_term=ti.send_term,
update_term=ti.update_term,
elatest=ti.latest,
encoding=ti.file_enc,
lines_on_start=ti.lines_on_start,
max_between_data=ti.max_between_data,
format=ti.format,
parser=ti.parser,
order_ptrn=ti.order_ptrn,
reverse_order=ti.reverse_order)
name = ti.tag
linfo("create & start {} thread".format(name))
trd = TailThread(name, tailer)
tail_threads.append(trd)
trd.start()
def __enter__(self):
global pyodbc
ldebug('db.Connector enter')
acs = ''
if self.trustcon:
acs = 'Trusted_Connection=yes'
elif self.uid is not None and self.passwd is not None:
acs = 'UID=%s;PWD=%s' % (self.uid, self.passwd)
cs = "DRIVER=%s;Server=%s;Database=%s;%s;" % (self.driver, self.server,
self.database, acs)
try:
conn = pyodbc.connect(cs)
except pyodbc.Error as e:
lerror(e[1])
return
else:
self.conn = conn
self.cursor = conn.cursor()
self.cursor.execute("SET DATEFORMAT ymd")
if self.read_uncommit:
ldebug("set read uncommited")
ldebug(" old isolation option: {}".format(self.txn_iso_level))
cmd = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
self.cursor.execute(cmd)
ldebug(" new isolation option: {}".format(self.txn_iso_level))
return self
def __enter__(self):
global pyodbc
ldebug('db.Connector enter')
acs = ''
if self.trustcon:
acs = 'Trusted_Connection=yes'
elif self.uid is not None and self.passwd is not None:
acs = 'UID=%s;PWD=%s' % (self.uid, self.passwd)
cs = "DRIVER=%s;Server=%s;Database=%s;%s;" % (self.driver, self.server,
self.database, acs)
try:
conn = pyodbc.connect(cs)
except pyodbc.Error as e:
lerror(e[1])
return
else:
self.conn = conn
self.cursor = conn.cursor()
self.cursor.execute("SET DATEFORMAT ymd")
if self.read_uncommit:
ldebug("set read uncommited")
ldebug(" old isolation option: {}".format(self.txn_iso_level))
cmd = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
self.cursor.execute(cmd)
ldebug(" new isolation option: {}".format(self.txn_iso_level))
return self
def _sync_log(lcfg):
global logcnt
# sync log
if 'handlers' in lcfg and 'file' in lcfg['handlers']:
logcnt += 1
if logcnt < LOG_SYNC_CNT:
return
logcnt = 0
if 'to_url' in lcfg:
lpath = lcfg['handlers']['file']['filename']
ldebug('log path: ' + lpath)
to_url = lcfg['to_url']
sync_file(lpath, to_url)
else:
ldebug('No URL to sync log file to')
def _sync_log(lcfg):
global logcnt
# sync log
if 'handlers' in lcfg and 'file' in lcfg['handlers']:
logcnt += 1
if logcnt < LOG_SYNC_CNT:
return
logcnt = 0
if 'to_url' in lcfg:
lpath = lcfg['handlers']['file']['filename']
ldebug('log path: ' + lpath)
to_url = lcfg['to_url']
sync_file(lpath, to_url)
else:
ldebug('No URL to sync log file to')
def get_table_rowcnt(con, tbname):
ldebug('get_table_rowcnt ' + tbname)
if not con.sys_schema:
tbname = tbname.split('.')[-1]
ldebug('get_table_rowcnt')
cmd = '''
select i.rows
from sysindexes i
join sysobjects o on o.id = i.id
where i.indid < 2
and o.name <> 'sysdiagrams'
and o.xtype = 'U'
and o.name = '%s'
''' % tbname
execute(con, cmd)
rv = con.cursor.fetchone()
return rv[0] if rv is not None else 0
def apply_tfunc(taken, token, tname):
"""apply token transform, save into target"""
ldebug("apply_tfunc")
tvar = taken[tname]
if tvar is None:
ret = ''
else:
token.tfunc_lmap['_'] = tvar
ret = eval(token.tfunc_s, token.tfunc_gmap, token.tfunc_lmap)
if type(ret) is dict:
del taken[tname]
taken.update(ret)
else:
taken[tname] = ret
return ret
def apply_tfunc(taken, token, tname):
"""apply token transform, save into target"""
ldebug("apply_tfunc")
tvar = taken[tname]
if tvar is None:
ret = ''
else:
token.tfunc_lmap['_'] = tvar
ret = eval(token.tfunc_s, token.tfunc_gmap, token.tfunc_lmap)
if type(ret) is dict:
del taken[tname]
taken.update(ret)
else:
taken[tname] = ret
return ret
def get_table_rowcnt(con, tbname):
ldebug('get_table_rowcnt ' + tbname)
if not con.sys_schema:
tbname = tbname.split('.')[-1]
ldebug('get_table_rowcnt')
cmd = '''
select i.rows
from sysindexes i
join sysobjects o on o.id = i.id
where i.indid < 2
and o.name <> 'sysdiagrams'
and o.xtype = 'U'
and o.name = '%s'
''' % tbname
execute(con, cmd)
rv = con.cursor.fetchone()
return rv[0] if rv is not None else 0
def tables_by_names(con, skip_last_subtable=None):
ldebug('tables_by_names')
if skip_last_subtable is None:
skip_last_subtable = con.skip_last
tables = []
tbnames = _escape_underscore(con.table_names)
counts = {}
for tbname in tbnames:
subtables = table_array(con, tbname)
# ldebug('subtables: ' + str(subtables))
counts[tbname] = len(subtables)
if skip_last_subtable and len(subtables) > 1:
# skip last subtable which might be using now.
ldebug('skip last table')
subtables = subtables[:-1]
tables += subtables
if len(set(counts.values())) > 1:
lwarning("Sub-table length mismatch! " + str(counts))
return tables
def tables_by_names(con, skip_last_subtable=None):
ldebug('tables_by_names')
if skip_last_subtable is None:
skip_last_subtable = con.skip_last
tables = []
tbnames = _escape_underscore(con.table_names)
counts = {}
for tbname in tbnames:
subtables = table_array(con, tbname)
# ldebug('subtables: ' + str(subtables))
counts[tbname] = len(subtables)
if skip_last_subtable and len(subtables) > 1:
# skip last subtable which might be using now.
ldebug('skip last table')
subtables = subtables[:-1]
tables += subtables
if len(set(counts.values())) > 1:
lwarning("Sub-table length mismatch! " + str(counts))
return tables
def table_rows(con, tbinfo, date=None, max_fetch=None):
"""Return all rows from the table."""
ldebug('table_rows')
if date is None:
cmd = "SELECT {} FROM {}".format(tbinfo.str_cols, tbinfo)
else:
cmd = "SELECT {} FROM {} WHERE CAST(LogTime AS DATE)"\
" = CAST('{}' as DATETIME);".format(tbinfo.str_cols, tbinfo, date)
ldebug('cmd: ' + cmd)
execute(con, cmd)
fetch_cnt = 0
while True:
if max_fetch is not None and fetch_cnt >= max_fetch:
break
# Actually, fetchmany has little advantage over fetch iteration.
rows = con.cursor.fetchmany(con.fetchsize)
fetch_cnt += 1
if not rows:
break
yield rows
def table_rows(con, tbinfo, date=None, max_fetch=None):
"""Return all rows from the table."""
ldebug('table_rows')
if date is None:
cmd = "SELECT {} FROM {}".format(tbinfo.str_cols, tbinfo)
else:
cmd = "SELECT {} FROM {} WHERE CAST(LogTime AS DATE)"\
" = CAST('{}' as DATETIME);".format(tbinfo.str_cols, tbinfo, date)
ldebug('cmd: ' + cmd)
execute(con, cmd)
fetch_cnt = 0
while True:
if max_fetch is not None and fetch_cnt >= max_fetch:
break
# Actually, fetchmany has little advantage over fetch iteration.
rows = con.cursor.fetchmany(con.fetchsize)
fetch_cnt += 1
if not rows:
break
yield rows
def daily_tables_by_change(dcfg, con, skip_last_subtable=None):
ldebug("daily_tables_by_change")
if skip_last_subtable is None:
skip_last_subtable = con.skip_last
dates = collect_dates(con, skip_last_subtable)
# ldebug("dates: " + str(dates))
daily_tables = daily_tables_from_dates(con, dates)
# ldebug("daily_tables: " + str(daily_tables))
res, rpath = read_table_info(dcfg)
if res is None:
return daily_tables
changed_daily_tables = []
for tables in daily_tables:
tmp = []
for table in tables:
oldcnt = res.get(str(table), -1)
curcnt = get_table_rowcnt(con, table)
ldebug("check row cnt for '{}' {} - {}".format(
table, oldcnt, curcnt))
if oldcnt != curcnt:
ldebug('append')
tmp.append(table)
if tmp:
changed_daily_tables.append(tmp)
return changed_daily_tables
def daily_tables_by_change(dcfg, con, skip_last_subtable=None):
ldebug("daily_tables_by_change")
if skip_last_subtable is None:
skip_last_subtable = con.skip_last
dates = collect_dates(con, skip_last_subtable)
# ldebug("dates: " + str(dates))
daily_tables = daily_tables_from_dates(con, dates)
# ldebug("daily_tables: " + str(daily_tables))
res, rpath = read_table_info(dcfg)
if res is None:
return daily_tables
changed_daily_tables = []
for tables in daily_tables:
tmp = []
for table in tables:
oldcnt = res.get(str(table), -1)
curcnt = get_table_rowcnt(con, table)
ldebug("check row cnt for '{}' {} - {}".format(table, oldcnt,
curcnt))
if oldcnt != curcnt:
ldebug('append')
tmp.append(table)
if tmp:
changed_daily_tables.append(tmp)
return changed_daily_tables
def __exit__(self, _type, value, tb):
ldebug('db.Connector exit')
if self.cursor is not None:
ldebug('cursor.close()')
self.cursor.close()
if self.conn is not None:
ldebug('conn.close()')
self.conn.close()
def __exit__(self, _type, value, tb):
ldebug('db.Connector exit')
if self.cursor is not None:
ldebug('cursor.close()')
self.cursor.close()
if self.conn is not None:
ldebug('conn.close()')
self.conn.close()
def table_array(con, prefix):
"""Return table name array by matching prefix."""
# wild schema for table select
if not con.sys_schema:
prefix = '%' + prefix.split('.')[-1]
ldebug('table_array')
cmd = "SELECT NAME FROM SYS.TABLES WHERE NAME"\
" LIKE '%s%%'" % prefix
execute(con, cmd)
ldebug('cmd: ' + cmd)
rows = con.cursor.fetchall()
ldebug('rowcnt: {}'.format(len(rows)))
res = []
if rows is not None:
res = sorted([row[0] for row in rows])
return res
def table_array(con, prefix):
"""Return table name array by matching prefix."""
# wild schema for table select
if not con.sys_schema:
prefix = '%' + prefix.split('.')[-1]
ldebug('table_array')
cmd = "SELECT NAME FROM SYS.TABLES WHERE NAME"\
" LIKE '%s%%'" % prefix
execute(con, cmd)
ldebug('cmd: ' + cmd)
rows = con.cursor.fetchall()
ldebug('rowcnt: {}'.format(len(rows)))
res = []
if rows is not None:
res = sorted([row[0] for row in rows])
return res
def create_parser(cfg, encoding=None):
ldebug("create_parser {}".format(cfg))
if 'custom' in cfg:
ldebug("custom parser '{}'".format(cfg['custom']))
from wdfwd.parser import custom
cuspar = custom.create_parser(cfg['custom'], encoding)
ldebug("custom parser created '{}'".format(cuspar))
return cuspar
ps = Parser(encoding)
tokens = cfg.get('tokens')
unresolved = True
while unresolved and tokens:
unresolved = False
for k, v in tokens.iteritems():
try:
ps.Token(k, v, encoding=encoding)
except KeyError:
unresolved = True
except ValueError, e:
if 'is already exist' not in str(e):
raise
def create_parser(cfg, encoding=None):
ldebug("create_parser {}".format(cfg))
if 'custom' in cfg:
ldebug("custom parser '{}'".format(cfg['custom']))
from wdfwd.parser import custom
cuspar = custom.create_parser(cfg['custom'], encoding)
ldebug("custom parser created '{}'".format(cuspar))
return cuspar
ps = Parser(encoding)
tokens = cfg.get('tokens')
unresolved = True
while unresolved and tokens:
unresolved = False
for k, v in tokens.iteritems():
try:
ps.Token(k, v, encoding=encoding)
except KeyError:
unresolved = True
except ValueError, e:
if 'is already exist' not in str(e):
raise
def _run_tasks(tasks):
ldebug('_run_tasks')
for task in tasks:
st = time.time()
cmd = task.keys()[0]
ldebug('cmd: ' + cmd)
if cmd == 'sync_folder':
scfg = task['sync_folder']
_sync_folder(scfg)
elif cmd == 'sync_files':
scfg = task['sync_files']
_sync_files(scfg)
elif cmd == 'sync_file':
scfg = task['sync_file']
_sync_file(scfg)
elif cmd == 'sync_db_dump':
scfg = task['sync_db_dump']
if 'db' in scfg:
# dump db
from wdfwd.dump import check_dump_db_and_sync
check_dump_db_and_sync(scfg)
ldebug("elapsed: {}".format(time.time() - st))
def _run_tasks(tasks):
ldebug('_run_tasks')
for task in tasks:
st = time.time()
cmd = task.keys()[0]
ldebug('cmd: ' + cmd)
if cmd == 'sync_folder':
scfg = task['sync_folder']
_sync_folder(scfg)
elif cmd == 'sync_files':
scfg = task['sync_files']
_sync_files(scfg)
elif cmd == 'sync_file':
scfg = task['sync_file']
_sync_file(scfg)
elif cmd == 'sync_db_dump':
scfg = task['sync_db_dump']
if 'db' in scfg:
# dump db
from wdfwd.dump import check_dump_db_and_sync
check_dump_db_and_sync(scfg)
ldebug("elapsed: {}".format(time.time() - st))
def stop_tailing():
ldebug("stop_tailing")
time.sleep(2)
for trd in tail_threads:
trd.exit()
def _sync_file(scfg):
path = scfg['filepath']
to_url = scfg['to_url']
ldebug("Sync single file: {} {}".format(path, to_url))
sync_file(path, to_url)
def _sync_folder(scfg):
folder = scfg['folder']
to_url = scfg['to_url']
ldebug("Sync folders: " + folder)
from wdfwd.sync import sync_folder
sync_folder(folder, to_url)
def __init__(self, args):
ldebug("__init__ " + str(args))
win32serviceutil.ServiceFramework.__init__(self, args)
self.haltEvent = win32event.CreateEvent(None, 0, 0, None)
lheader("Service Start")
def stop_tailing():
ldebug("stop_tailing")
time.sleep(2)
for trd in tail_threads:
trd.exit()
def _sync_file(scfg):
path = scfg['filepath']
to_url = scfg['to_url']
ldebug("Sync single file: {} {}".format(path, to_url))
sync_file(path, to_url)
def collect_dates(con, skip_last_subtable=None):
ldebug('collect_dates')
if skip_last_subtable is None:
skip_last_subtable = con.skip_last
tables = tables_by_names(con, skip_last_subtable)
return _collect_dates(con, tables)
def _sync_folder(scfg):
folder = scfg['folder']
to_url = scfg['to_url']
ldebug("Sync folders: " + folder)
from wdfwd.sync import sync_folder
sync_folder(folder, to_url)
def collect_dates(con, skip_last_subtable=None):
ldebug('collect_dates')
if skip_last_subtable is None:
skip_last_subtable = con.skip_last
tables = tables_by_names(con, skip_last_subtable)
return _collect_dates(con, tables)
def run_scheduled():
"""Run application main."""
global next_dt, force_first_run
if not next_dt:
return
ldebug("run_scheduled {}".format(next_dt))
linfo("{} run {}".format(appc['service']['name'], time.time()))
now = datetime.now()
ldebug('start_dt: ' + str(start_dt))
ldebug('next_dt: ' + str(next_dt))
ldebug('now: ' + str(now))
if now > next_dt or force_first_run:
if force_first_run:
ldebug('Running force first')
if 'tasks' in cfg:
try:
_run_tasks(cfg['tasks'])
except Exception as e:
lerror(traceback.format_exc())
if force_first_run:
force_first_run = False
else:
next_dt = cit.next(datetime)
ldebug('next_dt: ' + str(next_dt))
if 'log' in cfg:
lcfg = cfg['log']
try:
_sync_log(lcfg)
except Exception as e:
lerror(str(e))
lerror(traceback.format_exc())
def run_scheduled():
"""Run application main."""
global next_dt, force_first_run
if not next_dt:
return
ldebug("run_scheduled {}".format(next_dt))
linfo("{} run {}".format(appc['service']['name'], time.time()))
now = datetime.now()
ldebug('start_dt: ' + str(start_dt))
ldebug('next_dt: ' + str(next_dt))
ldebug('now: ' + str(now))
if now > next_dt or force_first_run:
if force_first_run:
ldebug('Running force first')
if 'tasks' in cfg:
try:
_run_tasks(cfg['tasks'])
except Exception as e:
lerror(traceback.format_exc())
if force_first_run:
force_first_run = False
else:
next_dt = cit.next(datetime)
ldebug('next_dt: ' + str(next_dt))
if 'log' in cfg:
lcfg = cfg['log']
try:
_sync_log(lcfg)
except Exception as e:
lerror(str(e))
lerror(traceback.format_exc())
def __init__(self, args):
ldebug("__init__ " + str(args))
win32serviceutil.ServiceFramework.__init__(self, args)
self.haltEvent = win32event.CreateEvent(None, 0, 0, None)
lheader("Service Start")