class Task: #{{{
name = 'task'
immediately = False
def __init__(self, ref: ScheduleGenerate) -> None:
self.ref = ref
self.db = DB()
self.unit = Unit()
def __del__(self) -> None:
self.close()
def __call__(self) -> bool:
rc = False
if self.db.isopen():
self.ref.processtitle.push(self.name)
try:
self.execute()
self.ref.pendings()
except Exception as e:
logger.exception('failed due to: %s' % e)
else:
rc = True
finally:
self.close()
self.ref.processtitle.pop()
self.ref.lockTitle()
return rc
def close(self) -> None:
self.db.close()
def configuration(self, key: str, default: Any = None) -> Any:
return self.ref.configuration(key,
name=self.name,
default=self.default_for(key, default))
def title(self, title: Optional[str] = None) -> None:
self.ref.processtitle('%s%s' % (self.name,
(' %s' % title) if title else ''))
def default_for(self, key: str, default: Any) -> Any:
return default
#
# to overwrite
def execute(self) -> None:
pass
def executor(self) -> bool:
rc = True
with DB() as self.db:
self.timestamp = Timestamp(self.timestamp_name)
self.config: Dict[int, Parameter] = {}
self.companies: DefaultDict[int, Softbounce.Company] = defaultdict(
Softbounce.Company)
try:
with log('parameter'):
self.setup_parameter()
with log('remove-olds'):
self.remove_old_entries()
with log('timestamp'):
self.setup_timestamp()
with log('collect'):
self.collect_new_bounces()
with log('timestamp'):
self.finalize_timestamp(True)
with log('merge'):
self.merge_new_bounces()
with log('convert'):
self.convert_to_hardbounce()
except error as e:
logger.exception('Failed due to %s' % e)
self.finalize_timestamp(False)
rc = False
self.db.sync()
return rc
def get_ready_to_run(self) -> List[METAFile]:
(ready, stamps, finals) = self.scan_ready_to_run()
if ready:
for info in ready:
info.stamp = stamps[info.basename]
with DB() as db:
invalids: Set[int] = set()
for mailing in (Stream(ready).map(
lambda i: i.mailing).distinct().sorted()):
rq = db.querys(
'SELECT deleted '
'FROM mailing_tbl '
'WHERE mailing_id = :mailingID',
{'mailingID': mailing})
if rq is None:
logger.info('Mailing %d no more existing' % mailing)
invalids.add(mailing)
elif rq.deleted:
logger.info('Mailing %d is marked as deleted' %
mailing)
invalids.add(mailing)
if invalids:
for info in (Stream(ready).filter(
lambda i: i.mailing in invalids)):
self.move(info.path, self.deleted)
if info.stamp is not None:
self.move(info.stamp.path, self.deleted)
ready = (Stream(ready).filter(
lambda i: i.mailing not in invalids).list())
if ready:
logger.info(
'{count:,d} files are ready to send'.format(count=len(ready)))
return ready
def executor (self) -> bool:
active = 0
with DB () as db:
companies: Dict[int, str] = {}
now = datetime.now ()
for row in db.queryc (
'SELECT mdrop.status_id, mdrop.mailing_id, mdrop.genstatus, mdrop.genchange, mdrop.status_field, mt.shortname '
'FROM maildrop_status_tbl mdrop INNER JOIN mailing_tbl mt ON mt.mailing_id = mdrop.mailing_id '
'WHERE genchange > :limit AND (genstatus = 2 OR (genstatus = 1 AND status_field IN (\'A\', \'T\', \'W\')))',
{
'limit': datetime.fromordinal (now.toordinal () - 1)
}
):
if self.is_active (now, row.genstatus, row.genchange, row.status_field):
active += 1
rqm = db.querys (
'SELECT company_id, shortname '
'FROM mailing_tbl '
'WHERE mailing_id = :mailing_id',
{'mailing_id': row.mailing_id}
)
if rqm is not None:
try:
company = companies[rqm.company_id]
except KeyError:
rqc = db.querys (
'SELECT shortname '
'FROM company_tbl '
'WHERE company_id = :company_id',
{'company_id': rqm.company_id}
)
company = companies[rqm.company_id] = rqc.shortname if rqc is not None else f'#{rqm.company_id}'
status = 'is starting up to generate' if row.genstatus == 1 else 'is in generation'
rqt = db.querys (
'SELECT current_mails, total_mails '
'FROM mailing_backend_log_tbl '
'WHERE status_id = :status_id',
{'status_id': row.status_id}
)
if rqt is None and row.status_field == 'W':
rqt = db.querys (
'SELECT current_mails, total_mails '
'FROM world_mailing_backend_log_tbl '
'WHERE mailing_id = :mailing_id',
{'mailing_id': row.mailing_id}
)
if rqt is not None:
status += ' (%d of %d are created)' % (rqt.current_mails, rqt.total_mails)
else:
status += ' (nothing created until now)'
print ('Mailing %s (%d) for Company %s (%d) %s' % (row.shortname, row.mailing_id, company, rqm.company_id, status))
if active > 0:
print ('%d jobs still active' % active)
return False
return True
def log_release(self) -> None:
application = 'BACKEND-{id}'.format(id=self.id.upper())
now = datetime.now()
try:
with DB() as db:
data: Dict[str, Any] = {
'host_name': fqdn,
'application_name': application
}
rq = db.querys(
db.qselect(
oracle=
('SELECT version_number '
'FROM release_log_tbl '
'WHERE host_name = :host_name AND application_name = :application_name '
'ORDER BY startup_timestamp DESC'),
mysql=
('SELECT version_number '
'FROM release_log_tbl '
'WHERE host_name = :host_name AND application_name = :application_name '
'ORDER BY startup_timestamp DESC '
'LIMIT 1')), data)
if rq is None or rq.version_number != spec.version:
data.update({
'version_number': spec.version,
'startup_timestamp': now,
'build_time': spec.timestamp,
'build_host': spec.host,
'build_user': spec.user
})
count = db.update(
'INSERT INTO release_log_tbl '
' (host_name, application_name, version_number, startup_timestamp, build_time, build_host, build_user) '
'VALUES '
' (:host_name, :application_name, :version_number, :startup_timestamp, :build_time, :build_host, :build_user)',
data,
commit=True)
if count != 1:
raise error(
f'failed to create new record, expected 1 row, inserted {count} rows'
)
except (IOError, error) as e:
logger.debug(
f'log_release: failed to write to database ({e}), try to spool information'
)
log_path = os.path.join(base, 'log')
if os.path.isdir(log_path):
with open(os.path.join(log_path, 'release.log'), 'a') as fd:
fd.write(
f'{licence};{fqdn};{application};{spec.version};{now:%Y-%m-%d %H:%M:%S};{spec.timestamp:%Y-%m-%d %H:%M:%S};{spec.host};{spec.user}\n'
)
else:
logger.debug(
f'no path {log_path} exists, no information is written')
def __db_sanity (self, r: Report) -> None:
with DB () as db:
key = 'mask-envelope-from'
rq = db.querys (
'SELECT count(*) AS cnt '
'FROM company_info_tbl '
'WHERE company_id = 0 AND cname = :cname',
{'cname': key}
)
if rq is None or not rq.cnt:
count = db.update (
'INSERT INTO company_info_tbl ('
' company_id, cname, cvalue, description, creation_date, timestamp'
') VALUES ('
' 0, :cname, :cvalue, NULL, current_timestamp, current_timestamp'
')', {
'cname': key,
'cvalue': 'false'
}, commit = True
)
if count == 1:
logger.info ('Added configuration for envelope address')
else:
logger.error ('Failed to set configuration for envelope address: %s' % db.last_error ())
for (key, value) in [
('LOGLEVEL', 'DEBUG'),
('MAILDIR', '${home}/var/spool/ADMIN'),
('BOUNDARY', 'OPENEMM'),
('MAILER', 'OpenEMM ${ApplicationMajorVersion}.${ApplicationMinorVersion}')
]:
data: Dict[str, Optional[str]] = {
'cls': 'mailout',
'name': 'ini.{key}'.format (key = key.lower ())
}
rq = db.querys (
'SELECT value '
'FROM config_tbl '
'WHERE class = :cls AND name = :name AND hostname IS NULL',
data
)
if rq is not None:
if rq.value != value:
logger.info (f'{key}: keep DB value "{rq.value}" and not overwrite it with default value {value}')
else:
data['value'] = value
db.update (
'INSERT INTO config_tbl '
' (class, name, value, hostname, description, creation_date, change_date) '
'VALUES '
' (:cls, :name, :value, NULL, NULL, current_timestamp, current_timestamp)',
data,
commit = True
)
def setup(self) -> None:
self.delay = BavUpdate.unit.parse('3m')
self.fqdn = socket.getfqdn().lower()
if not self.fqdn:
self.fqdn = fqdn
self.filter_domain = syscfg.get_str('filter-name',
BavUpdate.default_filter_domain)
if self.filter_domain == BavUpdate.default_filter_domain:
with DB() as db:
rq = db.querys(
'SELECT mailloop_domain FROM company_tbl WHERE company_id = 1'
)
if rq is not None and rq.mailloop_domain:
self.filter_domain = rq.mailloop_domain
self.mta = MTA()
self.domains: List[str] = []
self.mtdom: Dict[str, int] = {}
self.prefix = 'aml_'
self.last = ''
self.autoresponder: List[Autoresponder] = []
self.read_mailertable()
try:
files = os.listdir(Autoresponder.directory)
for fname in files:
if len(fname
) > 8 and fname[:3] == 'ar_' and fname[-5:] == '.mail':
with Ignore(ValueError, OSError):
rid = int(fname[3:-5])
st = os.stat(
os.path.join(Autoresponder.directory, fname))
self.autoresponder.append(
Autoresponder(rid,
datetime.fromtimestamp(st.st_ctime),
None, None))
except OSError as e:
logger.error(
f'Unable to read directory {Autoresponder.directory}: {e}')
def __db_sanity(self, r: Report) -> None:
with DB() as db:
key = 'mask-envelope-from'
rq = db.querys(
'SELECT count(*) AS cnt '
'FROM company_info_tbl '
'WHERE company_id = 0 AND cname = :cname', {'cname': key})
if rq is None or not rq.cnt:
count = db.update(
'INSERT INTO company_info_tbl ('
' company_id, cname, cvalue, description, creation_date, timestamp'
') VALUES ('
' 0, :cname, :cvalue, NULL, current_timestamp, current_timestamp'
')', {
'cname': key,
'cvalue': 'false'
},
commit=True)
if count == 1:
logger.info('Added configuration for envelope address')
else:
logger.error(
'Failed to set configuration for envelope address: %s'
% db.last_error())
def read_database(self, auto: List[Autoresponder]) -> List[str]:
rc: List[str] = []
with DB() as db:
company_list: List[int] = []
new_domains: Dict[str, BavUpdate.RID] = {}
forwards: List[BavUpdate.Forward] = []
seen_domains: Set[str] = set()
accepted_forwards: Set[str] = set()
ctab: Dict[int, str] = {}
#
rc.append('fbl@%s\taccept:rid=unsubscribe' % self.fixdomain)
for domain in self.domains:
if domain not in seen_domains:
rc.append('fbl@%s\talias:fbl@%s' %
(domain, self.fixdomain))
seen_domains.add(domain)
if self.fixdomain not in seen_domains:
new_domains[self.fixdomain] = BavUpdate.RID(
rid=0, domain=self.fixdomain)
seen_domains.add(self.fixdomain)
#
missing = []
for row in db.query(
'SELECT company_id, mailloop_domain FROM company_tbl WHERE status = :status',
{'status': 'active'}):
if row.mailloop_domain:
ctab[row.company_id] = row.mailloop_domain
if row.mailloop_domain not in seen_domains:
rc.append('fbl@%s\talias:fbl@%s' %
(row.mailloop_domain, self.fixdomain))
if row.mailloop_domain not in self.mtdom and row.mailloop_domain.lower(
) != self.fqdn:
new_domains[row.mailloop_domain] = BavUpdate.RID(
rid=0, domain=row.mailloop_domain)
seen_domains.add(row.mailloop_domain)
else:
missing.append(row.company_id)
company_list.append(row.company_id)
if missing:
missing.sort()
logger.debug('Missing mailloop_domain for %s' %
', '.join([str(m) for m in missing]))
#
seen_rids: Set[int] = set()
for row in db.query(
'SELECT rid, shortname, company_id, filter_address, '
' forward_enable, forward, ar_enable, '
' subscribe_enable, mailinglist_id, form_id, timestamp, '
' spam_email, spam_required, spam_forward, '
' autoresponder_mailing_id, security_token '
'FROM mailloop_tbl'):
if row.company_id not in company_list or row.rid is None:
continue
seen_rids.add(row.rid)
domains: List[str] = [self.fixdomain]
aliases: List[str] = []
if row.filter_address is not None:
for alias in listsplit(row.filter_address):
if not alias.startswith(self.prefix):
with Ignore(ValueError):
domain_part = alias.split('@', 1)[-1]
if domain_part not in domains:
domains.append(domain_part)
if domain_part not in self.mtdom and domain_part not in new_domains:
new_domains[
domain_part] = BavUpdate.RID(
rid=row.rid,
domain=domain_part)
aliases.append(alias)
#
ar_enable = False
if row.ar_enable and row.autoresponder_mailing_id:
if not row.security_token:
logger.error(
'%s: Autoresponder has mailing id, but no security token'
% row.rid)
else:
auto.append(
Autoresponder(
row.rid, row.timestamp if row.timestamp
is not None else datetime.now(),
row.autoresponder_mailing_id,
row.security_token))
ar_enable = True
#
try:
cdomain = ctab[row.company_id]
if cdomain not in domains:
if cdomain in self.domains:
domains.append(cdomain)
else:
logger.debug(
'Company\'s domain "%s" not found in mailertable'
% cdomain)
except KeyError:
logger.debug(
'No domain for company found, further processing')
extra = ['rid=%s' % row.rid]
if row.company_id:
extra.append('cid=%d' % row.company_id)
if row.forward_enable and row.forward:
extra.append('fwd=%s' % row.forward)
forwards.append(
BavUpdate.Forward(rid=row.rid, address=row.forward))
if row.spam_email:
extra.append('spam_email=%s' % row.spam_email)
if row.spam_forward:
extra.append('spam_fwd=%d' % row.spam_forward)
if row.spam_required:
extra.append('spam_req=%d' % row.spam_required)
if ar_enable:
extra.append('ar=%s' % row.rid)
if row.autoresponder_mailing_id:
extra.append('armid=%d' % row.autoresponder_mailing_id)
if row.subscribe_enable and row.mailinglist_id and row.form_id:
extra.append('sub=%d:%d' %
(row.mailinglist_id, row.form_id))
for domain in domains:
line = '%s%s@%s\taccept:%s' % (self.prefix, row.rid,
domain, ','.join(extra))
logger.debug(f'Add line: {line}')
rc.append(line)
if aliases and domains:
for alias in aliases:
rc.append('%s\talias:%s%s@%s' %
(alias, self.prefix, row.rid, domains[0]))
accepted_forwards.add(alias)
#
if seen_rids:
rules: Dict[int, Dict[str, List[str]]] = {}
for row in db.query(
'SELECT rid, section, pattern FROM mailloop_rule_tbl'):
if row.rid in seen_rids:
try:
rule = rules[row.rid]
except KeyError:
rule = rules[row.rid] = {}
try:
sect = rule[row.section]
except KeyError:
sect = rule[row.section] = []
sect.append(row.pattern)
self.update_rules(rules)
#
for forward in forwards:
with Ignore(ValueError):
fdomain = (forward.address.split('@', 1)[-1]).lower()
for domain in self.mtdom:
if domain == fdomain and forward.address not in accepted_forwards:
logger.warning(
'%s: using address "%s" with local handled domain "%s"'
% (forward.rid, forward.address, domain))
refuse = []
for (domain,
new_domain) in ((_d, _n)
for (_d, _n) in new_domains.items()
if _d == fdomain):
logger.warning(
'%s: try to add new domain for already existing forward address "%s" in %s, refused'
% (new_domain.rid, forward.address, forward.rid))
refuse.append(domain)
for domain in refuse:
del new_domains[domain]
#
if new_domains:
if self.mta.mta == 'sendmail':
cmd = [self.control_sendmail, 'add']
for domain in new_domains:
cmd.append(domain)
logger.info(f'Found new domains, add them using {cmd}')
silent_call(*cmd)
logger.info('Restarting sendmail due to domain update')
silent_call(self.restart_sendmail)
self.read_mailertable(new_domains)
return rc
def read_mailertable(
self,
new_domains: Optional[Dict[str, BavUpdate.RID]] = None) -> None:
self.domains.clear()
self.mtdom.clear()
if self.mta.mta == 'postfix':
def find(key: str, default_value: str) -> BavUpdate.Filecontent:
rc = BavUpdate.Filecontent(path=None,
content=[],
modified=False,
hash=None)
with Ignore(KeyError):
for element in self.mta.getlist(key):
hash: Optional[str]
path: str
try:
(hash, path) = element.split(':', 1)
except ValueError:
(hash, path) = (None, element)
if path.startswith(base):
if rc.path is None:
rc.path = path
rc.hash = hash
if not os.path.isfile(path):
create_path(os.path.dirname(path))
open(path, 'w').close()
if hash is not None:
self.mta.postfix_make(path)
if rc.path is not None:
try:
with open(rc.path) as fd:
for line in (_l.strip() for _l in fd):
try:
(var, val) = [
_v.strip()
for _v in line.split(None, 1)
]
except ValueError:
var = line
val = default_value
rc.modified = True
if var not in [
_c.name for _c in rc.content
]:
rc.content.append(
BavUpdate.Domain(name=var,
value=val))
else:
rc.modified = True
logger.debug('Read %d lines from %s' %
(len(rc.content), rc.path))
except OSError as e:
logger.error('Failed to read %s: %s' %
(rc.path, e))
else:
logger.warning(
'No path for postfix parameter %s found' % key)
return rc
def save(ct: BavUpdate.Filecontent) -> None:
if ct.path is not None and (ct.modified
or not os.path.isfile(ct.path)):
try:
with open(ct.path, 'w') as fd:
if ct.content:
fd.write('\n'.join(
['%s\t%s' % _c
for _c in ct.content]) + '\n')
logger.info('Written %d lines to %s' %
(len(ct.content), ct.path))
if ct.hash is not None:
self.mta.postfix_make(ct.path)
except OSError as e:
logger.error('Failed to save %s: %s' % (ct.path, e))
#
relay_default_value = 'dummy'
relays = find('relay_domains', relay_default_value)
for d in relays.content:
self.mtdom[d.name] = 0
#
def add_relay_domain(domain_to_add: str) -> None:
if domain_to_add and domain_to_add not in self.mtdom:
relays.content.append(
BavUpdate.Domain(name=domain_to_add,
value=relay_default_value))
relays.modified = True
self.mtdom[domain_to_add] = 0
#
if new_domains:
for domain in new_domains:
add_relay_domain(domain)
transport_default_value = 'mailloop:'
transports = find('transport_maps', transport_default_value)
with DB() as db:
for row in db.query(
'SELECT mailloop_domain FROM company_tbl WHERE mailloop_domain IS NOT NULL AND status = :status',
{'status': 'active'}):
if row.mailloop_domain:
add_relay_domain(row.mailloop_domain.strip().lower())
transport_domains = set([_c[0] for _c in transports.content])
for d in relays.content[:]:
if d.name not in transport_domains:
transports.content.append(
BavUpdate.Domain(name=d.name,
value=transport_default_value))
transports.modified = True
self.mtdom[d.name] += 1
save(relays)
save(transports)
if relays.modified or transports.modified:
cmd = which('smctrl')
if cmd is not None:
n = silent_call(cmd, 'service', 'reload')
if n == 0:
logger.info('Reloaded')
else:
logger.error('Reloading failed: %d' % n)
self.domains = [_c.name for _c in relays.content]
else:
try:
for line in self.file_reader(
os.path.join(self.sendmail_base, 'mailertable')):
parts = line.split()
if len(parts) > 1 and not parts[0].startswith(
'.') and parts[1].startswith('procmail:'):
self.domains.append(parts[0])
self.mtdom[parts[0]] = 0
except IOError as e:
logger.error('Unable to read mailertable %s' % e)
try:
for line in self.file_reader(
os.path.join(self.sendmail_base, 'relay-domains')):
if line in self.mtdom:
self.mtdom[line] += 1
else:
logger.debug(
'We relay domain "%s" without catching it in mailertable'
% line)
for key in self.mtdom.keys():
if self.mtdom[key] == 0:
logger.debug(
'We define domain "%s" in mailertable, but do not relay it'
% key)
except IOError as e:
logger.error('Unable to read relay-domains %s' % e)
def prepare(self) -> None:
self.db = DB()
self.mailings: List[Mailing] = []
self.mailing_info: Dict[int, Recovery.MailingInfo] = {}
self.report: List[str] = []
self.db.check_open()
class Recovery(CLI): #{{{
@dataclass
class MailingInfo:
company_id: int
name: str
exists: bool
deleted: bool
def add_arguments(self, parser: argparse.ArgumentParser) -> None:
parser.add_argument('-n',
'--dryrun',
action='store_true',
help='just show')
parser.add_argument(
'-A',
'--age',
action="store",
type=int,
default=1,
help='set maximum age for a mailing to be recovered in days')
parser.add_argument(
'-D',
'--startup-delay',
action="store",
default="1m",
help=
'set delay to wait for startup of backend in seconds or an offset (e.g. "1m")',
dest='startup_delay')
parser.add_argument(
'parameter',
nargs='*',
help='optional list of mailing_ids to restrict recovery to')
def use_arguments(self, args: argparse.Namespace) -> None:
unit = Unit()
self.dryrun = args.dryrun
self.max_age = args.age
self.startup_delay = unit.parse(args.startup_delay)
self.restrict_to_mailings = set(
int(_p) for _p in args.parameter) if args.parameter else None
def prepare(self) -> None:
self.db = DB()
self.mailings: List[Mailing] = []
self.mailing_info: Dict[int, Recovery.MailingInfo] = {}
self.report: List[str] = []
self.db.check_open()
def cleanup(self, success: bool) -> None:
for m in self.mailings:
m.done()
self.db.close()
def executor(self) -> bool:
log.set_loglevel('debug')
try:
with Lock():
with log('collect'):
self.collect_mailings()
with log('recover'):
self.recover_mailings()
with log('report'):
self.report_mailings()
return True
except error as e:
logger.exception('Failed recovery: %s' % e)
return False
def __make_range(self, start: datetime, end: datetime) -> List[str]: #{{{
rc: List[str] = []
current_day = start.toordinal()
end_day = end.toordinal()
while current_day <= end_day:
day = datetime.fromordinal(current_day)
rc.append(f'{day.year:04d}{day.month:02d}{day.day:02d}')
current_day += 1
return rc
#}}}
def __mail(self, mailing_id: int) -> Recovery.MailingInfo: #{{{
with Ignore(KeyError):
return self.mailing_info[mailing_id]
#
rq = self.db.querys(
'SELECT company_id, shortname, deleted FROM mailing_tbl WHERE mailing_id = :mid',
{'mid': mailing_id})
self.mailing_info[mailing_id] = rc = Recovery.MailingInfo(
company_id=rq.company_id if rq is not None else 0,
name=rq.shortname
if rq is not None else f'#{mailing_id} not found',
exists=rq is not None,
deleted=bool(rq.deleted) if rq is not None else False)
return rc
#}}}
def __mailing_name(self, mailing_id: int) -> str: #{{{
return self.__mail(mailing_id).name
#}}}
def __mailing_exists(self, mailing_id: int) -> bool: #{{{
return self.__mail(mailing_id).exists
#}}}
def __mailing_deleted(self, mailing_id: int) -> bool: #{{{
return self.__mail(mailing_id).deleted
#}}}
def __mailing_valid(self, mailing_id: int) -> bool: #{{{
return self.__mailing_exists(
mailing_id) and not self.__mailing_deleted(mailing_id)
#}}}
def collect_mailings(self) -> None: #{{{
now = datetime.now()
expire = now - timedelta(days=self.max_age)
yesterday = now - timedelta(days=1)
query = (
'SELECT status_id, mailing_id '
'FROM maildrop_status_tbl '
'WHERE genstatus = 2 AND status_field = \'R\' AND genchange > :expire AND genchange < current_timestamp'
)
check_query = self.db.qselect(
oracle=
'SELECT count(*) FROM rulebased_sent_tbl WHERE mailing_id = :mid AND to_char (lastsent, \'YYYY-MM-DD\') = to_char (sysdate - 1, \'YYYY-MM-DD\')',
mysql=
'SELECT count(*) FROM rulebased_sent_tbl WHERE mailing_id = :mid AND date_format(lastsent, \'%%Y-%%m-%%d\') = \'%04d-%02d-%02d\''
% (yesterday.year, yesterday.month, yesterday.day))
update = ('UPDATE maildrop_status_tbl '
'SET genstatus = 1, genchange = current_timestamp '
'WHERE status_id = :sid')
for (status_id, mailing_id) in self.db.queryc(query,
{'expire': expire}):
if (self.restrict_to_mailings is None
or mailing_id in self.restrict_to_mailings
) and self.__mailing_valid(mailing_id):
count = self.db.querys(check_query, {'mid': mailing_id})
if count is not None and count[0] == 1:
logger.info('Reactivate rule based mailing %d: %s' %
(mailing_id, self.__mailing_name(mailing_id)))
if not self.dryrun:
self.db.update(update, {'sid': status_id})
self.report.append(
'%s [%d]: Reactivate rule based mailing' %
(self.__mailing_name(mailing_id), mailing_id))
else:
logger.warning(
'Rule based mailing %d (%s) not reactivated as it had not been sent out yesterday'
% (mailing_id, self.__mailing_name(mailing_id)))
self.report.append(
'%s [%d]: Not reactivating rule based mailing as it had not been sent out yesterday'
% (self.__mailing_name(mailing_id), mailing_id))
if not self.dryrun:
self.db.sync()
query = (
'SELECT status_id, mailing_id, company_id, status_field, senddate '
'FROM maildrop_status_tbl '
'WHERE genstatus IN (1, 2) AND genchange > :expire AND genchange < current_timestamp AND status_field = \'W\''
)
for (status_id, mailing_id, company_id, status_field,
senddate) in self.db.queryc(query, {'expire': expire}):
if (self.restrict_to_mailings is None
or mailing_id in self.restrict_to_mailings
) and self.__mailing_valid(mailing_id):
check = self.__make_range(senddate, now)
self.mailings.append(
Mailing(status_id, status_field, mailing_id, company_id,
check))
logger.info('Mark mailing %d (%s) for recovery' %
(mailing_id, self.__mailing_name(mailing_id)))
self.mailings.sort(key=lambda m: m.status_id)
logger.info('Found %d mailing(s) to recover' % len(self.mailings))
#}}}
def recover_mailings(self) -> None: #{{{
if not self.dryrun and self.mailings and self.startup_delay > 0:
logger.info('Wait for backend to start up')
n = self.startup_delay
while n > 0:
time.sleep(1)
n -= 1
if not self.running:
raise error('abort due to process termination')
for m in self.mailings:
m.collect_seen()
if self.dryrun:
print('%s: %d recipients already seen' %
(self.__mailing_name(m.mailing_id), len(m.seen)))
else:
m.create_filelist()
count = 0
for (total_mails, ) in self.db.query(
'SELECT total_mails FROM mailing_backend_log_tbl WHERE status_id = :sid',
{'sid': m.status_id}):
if not total_mails is None and total_mails > count:
count = total_mails
m.set_generated_count(count)
self.db.update(
'DELETE FROM mailing_backend_log_tbl WHERE status_id = :sid',
{'sid': m.status_id})
self.db.update(
'DELETE FROM world_mailing_backend_log_tbl WHERE mailing_id = :mid',
{'mid': m.mailing_id})
self.db.update(
'UPDATE maildrop_status_tbl SET genstatus = 1 WHERE status_id = :sid',
{'sid': m.status_id})
self.db.sync()
logger.info('Start backend using status_id %d for %s' %
(m.status_id, self.__mailing_name(m.mailing_id)))
starter = agn3.emm.mailing.Mailing()
if not starter.fire(status_id=m.status_id,
cursor=self.db.cursor):
logger.error('Failed to trigger mailing %d')
self.report.append(
'%s [%d]: Failed to trigger mailing' %
(self.__mailing_name(m.mailing_id), m.mailing_id))
break
self.db.sync()
self.report.append(
'%s [%d]: Start recovery using status_id %d' %
(self.__mailing_name(m.mailing_id), m.mailing_id, m.status_id))
if not self.dryrun:
query = 'SELECT genstatus FROM maildrop_status_tbl WHERE status_id = :status_id'
start = int(time.time())
ok = True
last_generation_status = 1
while self.running and m.active and ok:
now = int(time.time())
self.db.sync(False)
row = self.db.querys(query, {'status_id': m.status_id})
if row is None or row[0] is None:
logger.info('Failed to query status for mailing %d' %
m.mailing_id)
self.report.append(
'%s [%d]: Recovery failed due to missing status' %
(self.__mailing_name(m.mailing_id), m.mailing_id))
ok = False
else:
generation_status = row[0]
if generation_status != last_generation_status:
logger.info(
f'Mailings {m.mailing_id} generation status has changed from {last_generation_status} to {generation_status}'
)
last_generation_status = generation_status
if generation_status == 3:
logger.info('Mailing %d terminated as expected' %
m.mailing_id)
self.report.append(
'%s [%d]: Recovery finished' %
(self.__mailing_name(
m.mailing_id), m.mailing_id))
m.active = False
elif generation_status == 2:
if m.last:
current = 0
for (currentMails, ) in self.db.query(
'SELECT current_mails FROM mailing_backend_log_tbl WHERE status_id = :sid',
{'sid': m.status_id}):
if not currentMails is None:
current = currentMails
if current != m.current:
logger.debug(
f'Mailing {m.mailing_id} has created {current:,d} vs. {m.current:,d} when last checked'
)
m.current = current
m.last = now
else:
if (current > 0 and m.last + 1200 < now
) or (current == 0
and m.last + 3600 < now):
logger.info(
'Mailing %d terminated due to inactivity after %d mails'
% (m.mailing_id, current))
self.report.append(
'%s [%d]: Recovery timed out' %
(self.__mailing_name(
m.mailing_id), m.mailing_id))
ok = False
else:
m.last = now
elif generation_status == 1:
if start + 1800 < now:
logger.info(
'Mailing %d terminated while not starting up'
% m.mailing_id)
self.report.append(
'%s [%d]: Recovery not started' %
(self.__mailing_name(
m.mailing_id), m.mailing_id))
ok = False
elif generation_status > 3:
logger.info(
'Mailing %d terminated with status %d' %
(m.mailing_id, generation_status))
self.report.append(
'%s [%d]: Recovery ended with unexpected status %d'
% (self.__mailing_name(m.mailing_id),
m.mailing_id, generation_status))
m.active = False
if m.active and ok:
if start + 30 * 60 < now:
logger.info(
'Failed due to global timeout to recover %d' %
m.mailing_id)
self.report.append(
'%s [%d]: Recovery ended due to global timeout'
% (self.__mailing_name(
m.mailing_id), m.mailing_id))
ok = False
else:
time.sleep(1)
if not m.active:
count = 0
for (total_mails, ) in self.db.query(
'SELECT total_mails FROM mailing_backend_log_tbl WHERE status_id = :sid',
{'sid': m.status_id}):
if not total_mails is None:
count = total_mails
count += len(m.seen)
self.db.update(
'UPDATE mailing_backend_log_tbl SET total_mails = :cnt, current_mails = :cnt WHERE status_id = :sid',
{
'sid': m.status_id,
'cnt': count
})
self.db.update(
'UPDATE world_mailing_backend_log_tbl SET total_mails = :cnt, current_mails = :cnt WHERE mailing_id = :mid',
{
'mid': m.mailing_id,
'cnt': count
})
self.db.sync()
if not self.running or not ok:
break
#}}}
def report_mailings(self) -> None: #{{{
class MailInfo(NamedTuple):
status_id: int
status_field: str
mailing_id: int
mailing_name: str
company_id: int
deleted: bool
genchange: datetime
senddate: datetime
mails = []
query = 'SELECT status_id, mailing_id, genstatus, genchange, status_field, senddate FROM maildrop_status_tbl WHERE '
query += 'genstatus IN (1, 2) AND status_field IN (\'W\', \'R\', \'D\')'
for (status_id, mailing_id, genstatus, genchange, status_field,
senddate) in self.db.queryc(query):
if status_field in ('R', 'D') and genstatus == 1:
continue
info = self.__mail(mailing_id)
mails.append(
MailInfo(status_id=status_id,
status_field=status_field,
mailing_id=mailing_id,
mailing_name=info.name,
company_id=info.company_id,
deleted=info.deleted,
genchange=genchange,
senddate=senddate))
if self.report or mails:
template = os.path.join(base, 'scripts', 'recovery3.tmpl')
try:
with open(template, 'r') as fd:
content = fd.read()
ns = {'host': fqdn, 'report': self.report, 'mails': mails}
tmpl = Template(content)
try:
body = tmpl.fill(ns)
charset = tmpl.property('charset', default='UTF-8')
subject = tmpl.property('subject')
if not subject:
subject = tmpl['subject']
if not subject:
subject = 'Recovery report for %s' % ns['host']
else:
subject = Template(subject).fill(ns)
sender = tmpl.property('sender', f'{user}@{fqdn}')
receiver = tmpl.property('receiver')
if receiver:
receiver = Template(receiver).fill(ns)
if self.dryrun:
print('From: %s' % sender)
print('To: %s' % receiver)
print('Subject: %s' % subject)
print('')
print(body)
else:
EMail.force_encoding(charset, 'qp')
mail = EMail()
if sender:
mail.set_sender(sender)
for recv in [
_r.strip() for _r in receiver.split(',')
]:
if recv:
mail.add_to(recv)
if charset:
mail.set_charset(charset)
mail.set_subject(subject)
mail.set_text(body)
mail.send_mail()
except error as e:
logger.error('Failed to fill template "%s": %s' %
(template, e))
except IOError as e:
logger.error('Unable to find template "%s": %s' %
(template, e))
def read_database(self, auto: List[Autoresponder]) -> List[str]:
rc: List[str] = []
with DB() as db:
company_list: List[int] = []
new_domains: Dict[str, BavUpdate.RID] = {}
forwards: List[BavUpdate.Forward] = []
seen_domains: Set[str] = set()
accepted_forwards: Set[str] = set()
ctab: Dict[int, str] = {}
#
rc.append(f'fbl@{self.filter_domain}\taccept:rid=unsubscribe')
for domain in self.domains:
if domain not in seen_domains:
rc.append(f'fbl@{domain}\talias:fbl@{self.filter_domain}')
seen_domains.add(domain)
if self.filter_domain not in seen_domains:
new_domains[self.filter_domain] = BavUpdate.RID(
rid=0, domain=self.filter_domain)
seen_domains.add(self.filter_domain)
#
missing = []
for row in db.query(
'SELECT company_id, mailloop_domain FROM company_tbl WHERE status = :status',
{'status': 'active'}):
if row.mailloop_domain:
ctab[row.company_id] = row.mailloop_domain
if row.mailloop_domain not in seen_domains:
rc.append(
f'fbl@{row.mailloop_domain}\talias:fbl@{self.filter_domain}'
)
if row.mailloop_domain not in self.mtdom and row.mailloop_domain.lower(
) != self.fqdn:
new_domains[row.mailloop_domain] = BavUpdate.RID(
rid=0, domain=row.mailloop_domain)
seen_domains.add(row.mailloop_domain)
else:
missing.append(row.company_id)
company_list.append(row.company_id)
if missing:
logger.debug(
'Missing mailloop_domain for companies {companies}'.format(
companies=Stream(missing).sorted().join(', ')))
#
seen_rids: Set[int] = set()
seen_filter_addresses: Dict[str, str] = {}
for row in db.query(
'SELECT rid, shortname, company_id, filter_address, '
' forward_enable, forward, ar_enable, '
' subscribe_enable, mailinglist_id, form_id, timestamp, '
' spam_email, spam_required, spam_forward, '
' autoresponder_mailing_id, security_token '
'FROM mailloop_tbl '
'ORDER BY rid'):
if row.company_id not in company_list or row.rid is None:
if row.company_id not in company_list:
logger.debug('{row}: ignore due to inactive company')
elif row.rid is None:
logger.error(
'{row}: ignore due to empty rid, should never happen!'
)
continue
#
row_id = f'{row.rid} {row.shortname} [{row.company_id}]'
seen_rids.add(row.rid)
domains: List[str] = [self.filter_domain]
aliases: List[str] = []
if row.filter_address is not None:
for alias in listsplit(row.filter_address):
if not alias.startswith(self.prefix):
with Ignore(ValueError):
(local_part, domain_part) = alias.split('@', 1)
normalized_alias = '{local_part}@{domain_part}'.format(
local_part=local_part,
domain_part=domain_part.lower())
if normalized_alias in seen_filter_addresses:
logger.warning(
f'{row_id}: already seen "{alias}" as "{normalized_alias}" before ({seen_filter_addresses[normalized_alias]})'
)
else:
seen_filter_addresses[
normalized_alias] = row_id
if domain_part not in domains:
domains.append(domain_part)
if domain_part not in self.mtdom and domain_part not in new_domains:
new_domains[
domain_part] = BavUpdate.RID(
rid=row.rid,
domain=domain_part)
aliases.append(alias)
#
ar_enable = False
if row.ar_enable and row.autoresponder_mailing_id:
if not row.security_token:
logger.error(
f'{row_id}: Autoresponder has mailing id, but no security token, not used'
)
else:
auto.append(
Autoresponder(
row.rid, row.timestamp if row.timestamp
is not None else datetime.now(),
row.autoresponder_mailing_id,
row.security_token))
ar_enable = True
#
try:
cdomain = ctab[row.company_id]
if cdomain not in domains:
if cdomain in self.domains:
domains.append(cdomain)
else:
logger.debug(
f'{row_id}: company\'s domain "{cdomain}" not found in mailertable'
)
except KeyError:
logger.debug(
f'{row_id}: no domain for company found, further processing'
)
extra = [f'rid={row.rid}']
if row.company_id:
extra.append(f'cid={row.company_id}')
if row.forward_enable and row.forward:
forward = row.forward.strip()
if forward:
extra.append(f'fwd={forward}')
forwards.append(
BavUpdate.Forward(rid=row.rid, address=forward))
if row.spam_email:
extra.append(f'spam_email={row.spam_email}')
if row.spam_forward:
forward = row.spam_forward.strip()
if forward:
extra.append(f'spam_fwd={forward}')
if row.spam_required:
extra.append(f'spam_req={row.spam_required}')
if ar_enable:
extra.append(f'ar={row.rid}')
if row.autoresponder_mailing_id:
extra.append(f'armid={row.autoresponder_mailing_id}')
if row.subscribe_enable and row.mailinglist_id and row.form_id:
extra.append(f'sub={row.mailinglist_id}:{row.form_id}')
line = '{prefix}{rid}@{domain}\taccept:{extra}'.format(
prefix=self.prefix,
rid=row.rid,
domain=self.filter_domain,
extra=','.join([escape(_e) for _e in extra]))
logger.debug(f'{row_id}: add line: {line}')
rc.append(line)
if aliases:
for alias in aliases:
rc.append(
f'{alias}\talias:{self.prefix}{row.rid}@{self.filter_domain}'
)
accepted_forwards.add(alias)
#
if seen_rids:
rules: Dict[int, Dict[str, List[str]]] = {}
for row in db.query(
'SELECT rid, section, pattern FROM mailloop_rule_tbl'):
if row.rid in seen_rids:
try:
rule = rules[row.rid]
except KeyError:
rule = rules[row.rid] = {}
try:
sect = rule[row.section]
except KeyError:
sect = rule[row.section] = []
sect.append(row.pattern)
self.update_rules(rules)
#
for forward in forwards:
with Ignore(ValueError):
fdomain = (forward.address.split('@', 1)[-1]).lower()
for domain in self.mtdom:
if domain == fdomain and forward.address not in accepted_forwards:
logger.warning(
f'{forward.ird}: using address "{forward.address}" with local handled domain "{domain}"'
)
refuse = []
for (domain,
new_domain) in ((_d, _n)
for (_d, _n) in new_domains.items()
if _d == fdomain):
logger.warning(
f'{new_domain.rid}: try to add new domain for already existing forward address "{forward.address}" in {forward.rid}, refused'
)
refuse.append(domain)
for domain in refuse:
del new_domains[domain]
#
if new_domains:
if self.mta.mta == 'sendmail':
if os.access(BavUpdate.control_sendmail, os.X_OK):
cmd = [BavUpdate.control_sendmail, 'add']
for domain in new_domains:
cmd.append(domain)
logger.info(f'Found new domains, add them using {cmd}')
silent_call(*cmd)
if os.access(BavUpdate.restart_sendmail, os.X_OK):
logger.info(
'Restarting sendmail due to domain update')
silent_call(BavUpdate.restart_sendmail)
else:
logger.warning(
f'Missing {BavUpdate.restart_sendmail}, no restart of mta perfomed'
)
else:
logger.warning(
f'Missing {BavUpdate.control_sendmail}, no new domains are added'
)
self.read_mailertable(new_domains)
return rc
def executor(self) -> bool:
rc = True
(name, extension) = os.path.splitext(os.path.basename(self.filename))
if not self.language:
if extension.startswith('.'):
extension = extension[1:]
self.language = extension.lower()
with DB() as db, db.request() as cursor:
if not self.only_tags:
with open(self.filename) as fd:
code = fd.read()
if code.startswith('#!'):
code = code.split('\n', 1)[-1]
code = re.split('\n[^\n]*%%\n', code)[0]
rq = cursor.querys(
'SELECT tag_function_id, lang, description, code '
'FROM tag_function_tbl '
'WHERE name = :name AND company_id = :company_id', {
'name': name,
'company_id': self.company_id
})
if rq is None:
data = {
'name': name,
'company_id': self.company_id,
'lang': self.language,
'code': code,
'tdesc': self.description
}
query = cursor.qselect(
oracle=
('INSERT INTO tag_function_tbl '
' (tag_function_id, company_id, creation_date, timestamp, name, lang, description, code) '
'VALUES '
' (tag_function_tbl_seq.nextval, :company_id, current_timestamp, current_timestamp, :name, :lang, :tdesc, :code)'
),
mysql=
('INSERT INTO tag_function_tbl '
' (company_id, creation_date, timestamp, name, lang, description, code) '
'VALUES '
' (:company_id, current_timestamp, current_timestamp, :name, :lang, :tdesc, :code)'
))
if self.dryrun:
print(f'{name}: Would execute {query} using {data}')
else:
if db.dbms == 'oracle' and db.db is not None:
cursor.set_input_sizes(code=db.db.driver.CLOB)
rows = cursor.update(query, data)
if rows == 1:
if not self.quiet:
print(
f'{name}: code inserted setting language to "{self.language}" for company {self.company_id}'
)
else:
print(
'{name}: FAILED to insert code into database: {error}'
.format(name=name, error=db.last_error()))
rc = False
elif rq.lang == self.language and str(rq.code) == code and (
not self.description
or self.description == rq.description):
if not self.quiet:
print(f'{name}: no change')
else:
data = {
'fid': rq.tag_function_id,
'lang': self.language,
'code': code
}
if self.description:
data['tdesc'] = self.description
extra = ', description = :tdesc'
else:
extra = ''
query = (
'UPDATE tag_function_tbl '
f'SET code = :code, lang = :lang, timestamp = current_timestamp{extra} '
'WHERE tag_function_id = :fid')
if self.dryrun:
print(f'{name}: Would execute {query} using {data}')
else:
if db.dbms == 'oracle' and db.db is not None:
cursor.set_input_sizes(code=db.db.driver.CLOB)
rows = cursor.update(query, data)
if rows == 1:
if not self.quiet:
print(
'{name}: code updated using language "{self.language}"'
)
else:
print('{name}: FAILED to update code: {error}'.
format(name=name, error=db.last_error()))
rc = False
#
for tag in self.tags:
parts = tag.split(':', 2)
cdesc = name
tdesc: Optional[str] = None
if len(parts) > 1:
tag = parts[0]
if parts[1]:
cdesc = '%s:%s' % (name, parts[1])
if len(parts) == 3 and parts[2]:
tdesc = parts[2]
data = {'cdesc': cdesc}
rq = cursor.querys(
'SELECT tag_id, type, selectvalue, description '
'FROM tag_tbl '
'WHERE tagname = :tname AND company_id = :company_id', {
'tname': tag,
'company_id': self.company_id
})
if rq is None:
data['type'] = 'FUNCTION'
data['tname'] = tag
data['company_id'] = self.company_id
if tdesc is None:
tdesc = 'Created by script-tag'
data['tdesc'] = tdesc
query = cursor.qselect(
oracle=
('INSERT INTO tag_tbl '
' (tag_id, tagname, selectvalue, type, company_id, description, timestamp) '
'VALUES '
' (tag_tbl_seq.nextval, :tname, :cdesc, :type, :company_id, :tdesc, current_timestamp)'
),
mysql=
('INSERT INTO tag_tbl '
' (tagname, selectvalue, type, company_id, description, change_date) '
'VALUES '
' (:tname, :cdesc, :type, :company_id, :tdesc, current_timestamp)'
))
if self.dryrun:
print(f'Tag {tag}: Would execute {query} using {data}')
else:
rows = cursor.update(query, data)
if rows == 1:
if not self.quiet:
print(f'Tag {tag}: inserted into database')
else:
print(
'Tag {tag}: FAILED to insert into database: {error}'
.format(tag=tag, error=db.last_error()))
rc = False
elif rq.selectvalue == cdesc and (not tdesc
or rq.description == tdesc):
if not self.quiet:
print(f'Tag {tag}: no change')
else:
data['tid'] = rq.tag_id
if tdesc:
data['tdesc'] = tdesc
extra = ', description = :tdesc'
else:
extra = ''
if rq.type != 'FUNCTION':
if not self.quiet:
print(f'Tag {tag}: modify type from {rq.type}')
data['type'] = 'FUNCTION'
extra += ', type = :type'
query = cursor.qselect(
oracle=
('UPDATE tag_tbl '
f'SET selectvalue = :cdesc, timestamp = current_timestamp{extra} '
'WHERE tag_id = :tid'),
mysql=
('UPDATE tag_tbl '
f'SET selectvalue = :cdesc, change_date = current_timestamp{extra} '
'WHERE tag_id = :tid'))
if self.dryrun:
print(f'Tag {tag}: Would execute (query) using {data}')
else:
rows = cursor.update(query, data)
if rows == 1:
if not self.quiet:
print(f'Tag {tag}: updated')
else:
print(
'Tag {tag}: FAILED to update: {error}'.format(
tag=tag, error=db.last_error()))
rc = False
cursor.sync(not self.dryrun and rc)
return rc
def __init__ (self, ref: ScheduleGenerate) -> None: self.ref = ref self.db = DB () self.unit = Unit ()