def POST(self): user_data = web.input() cid = int(user_data.get('cid', '-1')) dir = user_data.get('dir', False) web.header('Content-Type', 'application/json') if cid == -1 or dir == False or (dir != 'inc' and dir != 'dec'): return json.dumps({'status':-1,'msg':'params error'}) ip = utils.ip2long(web.ctx['ip']) useragent = web.ctx.env['HTTP_USER_AGENT'] author = web.cookies().author email = web.cookies().email votes = db.select(T_COMMENT_VOTE, dict(id=cid,ip=ip), where="cid=$id and ipv4=$ip") if len(votes): return json.dumps({'status':0,'msg':'You have already voted'}) if dir == 'inc': db.update(T_COMMENT, where='id=$cid', upnum=web.sqlliteral('upnum+1'), vars=dict(cid=cid)) elif dir == 'dec': db.query("update comment set downnum = downnum+1 where id=$cid",vars=dict(cid=cid)) db.insert(T_COMMENT_VOTE, id=0, date=web.SQLLiteral("NOW()"), dir=dir, email = email, cid = cid, author = author, ipv4 = ip, useragent = useragent) return json.dumps({'status':1,'msg':'OK'})
delete_record(conn=conn, rid=rid) # Establish SQL connection. try: if settings.backend == 'ldap': conn = ira_tool_lib.get_db_conn('iredadmin') else: conn = ira_tool_lib.get_db_conn('vmail') except Exception, e: sys.exit( '<<< ERROR >>> Cannot connect to SQL database, aborted. Error: %s' % repr(e)) # Get pathes of all maildirs. sql_where = 'delete_date <= %s' % web.sqlquote(web.sqlliteral('NOW()')) if delete_null_date: sql_where = '(delete_date <= %s) OR (delete_date IS NULL)' % web.sqlquote( web.sqlliteral('NOW()')) qr = conn.select('deleted_mailboxes', where=sql_where) if qr: logger.info('Delete old mailboxes (%d in total).' % len(qr)) else: logger.debug('No mailbox is scheduled to be removed.') if not delete_null_date: logger.debug( "To remove mailboxes with empty schedule date, please run this script with argument '--delete-null-date'." )
def delete_custom_envs(self): common.db.update(self.table_nodes, "1", env=web.sqlliteral("NULL"))
import os, time import web, pusher import courier, simplethread REPO_STORAGE = '/tmp/dudley_repo' NOW = web.sqlliteral('now()') pushcloud = pusher.pusher_from_url() env = web.storage(os.environ) buildqueue = simplethread.Queue() def watchdb(db): while 1: try: buildqueue.get(timeout=60) except simplethread.Empty: pass for job in db.select('jobs', where="builder is null and done='f'"): simplethread.spawn(lambda: start_build(db, job)) def get_buildserver(db, build_id): buildservers = db.select('buildservers', where="building is null", limit=1).list() if buildservers and db.update('buildservers', building=build_id, where="building is null and id=$buildservers[0].id", vars=locals()): return buildservers[0] else: return False def claim_job(db, job_id, build_id): return db.update('jobs', builder=build_id, where="id=$job_id and builder is null", vars=locals()) def start_build(db, job): build_id = db.insert('builds', job_id=job.id, updated_at=NOW) if not claim_job(db, job.id, build_id): # someone else is already building this job
###################### # Regular expressions. # # Email. reEmail = r'''[\w\-][\w\-\.]*@[\w\-][\w\-\.]+[a-zA-Z]{2,15}''' # Domain. reDomain = r'''[\w\-][\w\-\.]*\.[a-z]{2,15}''' # End Regular expressions. #### ##################################### # Pre-defined values of SQL functions. sqlUnixTimestamp = web.sqlliteral('UNIX_TIMESTAMP()') ##### ############## # Validators # INVALID_EMAIL_CHARS = '~!#$%^&*()\\/\ ' INVALID_DOMAIN_CHARS = '~!#$%^&*()+\\/\ ' def is_email(s): s = str(s) if len(set(s) & set(INVALID_EMAIL_CHARS)) > 0 \ or '.' not in s \ or s.count('@') != 1:
def delete_domains(domains, keep_mailbox_days=0, conn=None): domains = [str(d).lower() for d in domains if iredutils.is_domain(d)] if not domains: return (True, ) if not conn: _wrap = SQLWrap() conn = _wrap.conn try: keep_mailbox_days = abs(int(keep_mailbox_days)) except: keep_mailbox_days = 0 if not session.get('is_global_admin'): _max_days = max(settings.DAYS_TO_KEEP_REMOVED_MAILBOX) if keep_mailbox_days > _max_days: # Get the max days keep_mailbox_days = _max_days # Keep mailboxes 'forever', set to 100 years. if keep_mailbox_days == 0: sql_keep_days = web.sqlliteral('Null') else: if settings.backend == 'pgsql': sql_keep_days = web.sqlliteral( """CURRENT_TIMESTAMP + INTERVAL '%d DAYS'""" % keep_mailbox_days) else: # settings.backend == 'mysql' sql_keep_days = web.sqlliteral( 'DATE_ADD(CURDATE(), INTERVAL %d DAY)' % keep_mailbox_days) sql_vars = { 'domains': domains, 'admin': session.get('username'), 'sql_keep_days': sql_keep_days } # Log maildir paths of existing users try: if settings.backend == 'pgsql': sql_raw = ''' INSERT INTO deleted_mailboxes (username, maildir, domain, admin, delete_date) SELECT username, \ storagebasedirectory || '/' || storagenode || '/' || maildir, \ domain, \ $admin, \ $sql_keep_days FROM mailbox WHERE domain IN $domains''' else: # settings.backend == 'mysql' sql_raw = ''' INSERT INTO deleted_mailboxes (username, maildir, domain, admin, delete_date) SELECT username, \ CONCAT(storagebasedirectory, '/', storagenode, '/', maildir) AS maildir, \ domain, \ $admin, \ $sql_keep_days FROM mailbox WHERE domain IN $domains''' conn.query(sql_raw, vars=sql_vars) except Exception as e: logger.error(e) try: # Delete domain name for tbl in [ 'domain', 'alias', 'domain_admins', 'mailbox', 'recipient_bcc_domain', 'recipient_bcc_user', 'sender_bcc_domain', 'sender_bcc_user', 'forwardings', 'moderators', settings.SQL_TBL_USED_QUOTA ]: conn.delete(tbl, vars=sql_vars, where='domain IN $domains') # Delete alias domain conn.delete( 'alias_domain', vars=sql_vars, where='alias_domain IN $domains OR target_domain IN $domains') # Delete domain admins for d in domains: conn.delete('domain_admins', vars={'domain': '%%@' + d}, where='username LIKE $domain') except Exception as e: return (False, repr(e)) for d in domains: log_activity(event='delete', domain=d, msg="Delete domain: %s." % d) return (True, )
###################### # Regular expressions. # # Email. reEmail = r"""[\w\-][\w\-\.]*@[\w\-][\w\-\.]+[a-zA-Z]{2,6}""" # Domain. reDomain = r"""[\w\-][\w\-\.]*\.[a-z]{2,6}""" # End Regular expressions. #### ##################################### # Pre-defined values of SQL functions. sqlNOW = web.sqlliteral('NOW()') sqlUnixTimestamp = web.sqlliteral('UNIX_TIMESTAMP()') ##### ############## # Validators # INVALID_EMAIL_CHARS = '~!#$%^&*()+\\/\ ' INVALID_DOMAIN_CHARS = '~!#$%^&*()+\\/\ ' def isEmail(s): s = str(s) if len(set(s) & set(INVALID_EMAIL_CHARS)) > 0 \ or '.' not in s \ or s.count('@') != 1:
import os, time import web, pusher import courier, simplethread REPO_STORAGE = '/tmp/dudley_repo' NOW = web.sqlliteral('now()') pushcloud = pusher.pusher_from_url() env = web.storage(os.environ) buildqueue = simplethread.Queue() def watchdb(db): while 1: try: buildqueue.get(timeout=60) except simplethread.Empty: pass for job in db.select('jobs', where="builder is null and done='f'"): simplethread.spawn(lambda: start_build(db, job)) def get_buildserver(db, build_id): buildservers = db.select('buildservers', where="building is null", limit=1).list() if buildservers and db.update( 'buildservers', building=build_id, where="building is null and id=$buildservers[0].id", vars=locals()): return buildservers[0] else:
def delete_users(accounts, keep_mailbox_days=0, conn=None): accounts = [str(v) for v in accounts if iredutils.is_email(v)] if not accounts: return (True, ) # Keep mailboxes 'forever', set to 100 years. try: keep_mailbox_days = abs(int(keep_mailbox_days)) except: if session.get('is_global_admin'): keep_mailbox_days = 0 else: _max_days = max(settings.DAYS_TO_KEEP_REMOVED_MAILBOX) if keep_mailbox_days > _max_days: # Get the max days keep_mailbox_days = _max_days if keep_mailbox_days == 0: sql_keep_days = web.sqlliteral('Null') else: if settings.backend == 'mysql': sql_keep_days = web.sqlliteral( 'DATE_ADD(CURDATE(), INTERVAL %d DAY)' % keep_mailbox_days) elif settings.backend == 'pgsql': sql_keep_days = web.sqlliteral( """CURRENT_TIMESTAMP + INTERVAL '%d DAYS'""" % keep_mailbox_days) sql_vars = { 'accounts': accounts, 'admin': session.get('username'), 'sql_keep_days': sql_keep_days } # Log maildir path of deleted users. if settings.backend == 'mysql': sql_raw = ''' INSERT INTO deleted_mailboxes (username, maildir, domain, admin, delete_date) SELECT username, \ CONCAT(storagebasedirectory, '/', storagenode, '/', maildir) AS maildir, \ SUBSTRING_INDEX(username, '@', -1), \ $admin, \ $sql_keep_days FROM mailbox WHERE username IN $accounts''' elif settings.backend == 'pgsql': sql_raw = ''' INSERT INTO deleted_mailboxes (username, maildir, domain, admin, delete_date) SELECT username, \ storagebasedirectory || '/' || storagenode || '/' || maildir, \ SPLIT_PART(username, '@', 2), \ $admin, \ $sql_keep_days FROM mailbox WHERE username IN $accounts''' try: if not conn: _wrap = SQLWrap() conn = _wrap.conn conn.query(sql_raw, vars=sql_vars) except: pass try: for tbl in [ 'mailbox', 'domain_admins', 'recipient_bcc_user', 'sender_bcc_user', settings.SQL_TBL_USED_QUOTA ]: conn.delete(tbl, vars=sql_vars, where='username IN $accounts') # remove destination bcc addresses. for tbl in [ 'recipient_bcc_user', 'sender_bcc_user', 'recipient_bcc_domain', 'sender_bcc_domain' ]: conn.delete(tbl, vars=sql_vars, where='bcc_address IN $accounts') # Remove user from `forwardings`, including: # - per-user mail forwardings # - per-domain catch-all account # - alias membership # - alias moderators conn.delete('forwardings', vars=sql_vars, where='address IN $accounts OR forwarding IN $accounts') # remove destination moderators. conn.delete('moderators', vars=sql_vars, where='moderator IN $accounts') except Exception as e: return (False, repr(e)) log_activity(event='delete', domain=accounts[0].split('@', 1)[-1], msg="Delete user: %s." % ', '.join(accounts)) return (True, )
import web from . import config db = web.database(config.DATABASE_URL) CURRENT_TIMESTAMP = web.sqlliteral("(current_timestamp at time zone 'utc')") def get_sketch(id): return db.where("sketch", id=id).first() def save_sketch(id, mode, title, code): db.update("sketch", mode=mode, title=title, code=code, last_updated=CURRENT_TIMESTAMP, where="id=$id", vars={"id": id}) def new_sketch(mode, title, code): """Creates a new sketch and returns the id. """ return db.insert("sketch", mode=mode, title=title, code=code)
# Delete record. delete_record(conn=conn, rid=rid) # Establish SQL connection. try: if settings.backend == 'ldap': conn = ira_tool_lib.get_db_conn('iredadmin') else: conn = ira_tool_lib.get_db_conn('vmail') except Exception, e: sys.exit('<<< ERROR >>> Cannot connect to SQL database, aborted. Error: %s' % repr(e)) # Get pathes of all maildirs. sql_where = 'delete_date <= %s' % web.sqlquote(web.sqlliteral('NOW()')) if delete_null_date: sql_where = '(delete_date <= %s) OR (delete_date IS NULL)' % web.sqlquote(web.sqlliteral('NOW()')) qr = conn.select('deleted_mailboxes', where=sql_where) if qr: logger.info('Delete old mailboxes (%d in total).' % len(qr)) else: logger.debug('No mailbox is scheduled to be removed.') if not delete_null_date: logger.debug("To remove mailboxes with empty schedule date, please run this script with argument '--delete-null-date'.") if not delete_without_timestamp: logger.debug("To remove mailboxes which don't contain a timesamp in maildir path, please run this script with argument '--delete-without-timestamp'.")
def add(self, domain, data): # Get domain name, username, cn. self.domain = web.safestr(data.get('domainName')).strip().lower() self.username = web.safestr(data.get('username')).strip().lower() self.mail = self.username + '@' + self.domain sql_vars = { 'mail': self.mail, } if not iredutils.isDomain(self.domain): return (False, 'INVALID_DOMAIN_NAME') if self.domain != domain: return (False, 'PERMISSION_DENIED') if not iredutils.isEmail(self.mail): return (False, 'INVALID_MAIL') # Check account existing. connutils = connUtils.Utils() if connutils.isEmailExists(self.mail): return (False, 'ALREADY_EXISTS') # Get domain profile. domainLib = domainlib.Domain() resultOfDomainProfile = domainLib.profile(domain=self.domain) if resultOfDomainProfile[0] is True: self.domainProfile = resultOfDomainProfile[1] else: return resultOfDomainProfile # Check account limit. adminLib = adminlib.Admin() numberOfExistAccounts = adminLib.getNumberOfManagedAccounts( accountType='user', domains=[self.domain]) if self.domainProfile.mailboxes == -1: return (False, 'NOT_ALLOWED') elif self.domainProfile.mailboxes > 0: if self.domainProfile.mailboxes <= numberOfExistAccounts: return (False, 'EXCEEDED_DOMAIN_ACCOUNT_LIMIT') columns = { 'userid': self.mail, 'domain': self.domain, } # Check spare quota and number of spare account limit. # Get quota from form. self.mailQuota = str(data.get('mailQuota')).strip() self.defaultUserQuota = self.domainProfile.get('defaultuserquota', 0) if self.mailQuota.isdigit(): self.mailQuota = int(self.mailQuota) else: self.mailQuota = self.defaultUserQuota # Re-calculate mail quota if this domain has max quota limit. if self.domainProfile.maxquota > 0: # Get used quota. qr = domainLib.getAllocatedQuotaSize(domain=self.domain) if qr[0] is True: self.allocatedQuota = qr[1] else: return qr spareQuota = self.domainProfile.maxquota - self.allocatedQuota / 1024 / 1024 if spareQuota > 0: if spareQuota < self.mailQuota: self.mailQuota = spareQuota else: # No enough quota. return (False, 'EXCEEDED_DOMAIN_QUOTA_SIZE') columns['maxmail_size'] = self.mailQuota * 1024 * 1024 # # Get password from <form>. # newpw = web.safestr(data.get('newpw', '')) confirmpw = web.safestr(data.get('confirmpw', '')) # Get password length limit from domain profile or global setting. self.minPasswordLength = self.domainProfile.get( 'minpasswordlength', cfg.general.get('min_passwd_length', '0')) self.maxPasswordLength = self.domainProfile.get( 'maxpasswordlength', cfg.general.get('max_passwd_length', '0')) resultOfPW = iredutils.verifyNewPasswords( newpw, confirmpw, min_passwd_length=self.minPasswordLength, max_passwd_length=self.maxPasswordLength, ) if resultOfPW[0] is True: if 'storePasswordInPlainText' in data: columns['passwd'] = iredutils.getSQLPassword(resultOfPW[1], pwscheme='PLAIN') columns['encryption_type'] = '' else: columns['passwd'] = iredutils.getSQLPassword(resultOfPW[1]) columns[ 'encryption_type'] = settings.SQL_DEFAULT_PASSWD_SCHEME.lower( ) else: return resultOfPW # Get display name from <form> columns['name'] = data.get('cn', '') # Assign new user to default mail aliases. assignedAliases = [ str(addr).lower() for addr in str(self.domainProfile.defaultuseraliases).split(',') if iredutils.isEmail(addr) ] try: # Store new user in SQL db. self.conn.insert('dbmail_users', **columns) # Get dbmail_users.user_idnr. qr = self.conn.select( 'dbmail_users', vars=sql_vars, what='user_idnr,client_idnr', where='userid=$mail', limit=1, ) p = qr[0] user_idnr, client_idnr = p.user_idnr, p.client_idnr self.conn.insert( 'dbmail_aliases', alias=self.mail, deliver_to=user_idnr, client_idnr=client_idnr, ) # Create and subscribe to default IMAP folders. if settings.DBMAIL_CREATE_DEFAULT_IMAP_FOLDERS: # Create default IMAP folders. imap_folders = [ '(%d, "%s")' % (user_idnr, fld) for fld in settings.DBMAIL_DEFAULT_IMAP_FOLDERS ] self.conn.query( '''INSERT INTO dbmail_mailboxes (owner_idnr, name) VALUES %s''' % ','.join(imap_folders)) # Subscribe to folders by default. self.conn.query( '''INSERT INTO dbmail_subscription (user_id, mailbox_id) SELECT owner_idnr, mailbox_idnr FROM dbmail_mailboxes WHERE owner_idnr = %d ''' % user_idnr) # Assign new user to default mail aliases. if len(assignedAliases) > 0: for ali in assignedAliases: try: self.conn.update( 'dbmail_aliases', vars={ 'mail': self.mail, 'ali': ali, 'user_idnr': user_idnr, }, where='alias = $ali AND deliver_to <> $user_idnr', deliver_to=web.sqlliteral( 'CONCAT($mail, ",", deliver_to)'), ) except: pass vars_addition_sql = { 'user_idnr': user_idnr, 'mail': self.mail, 'username': self.username, 'domain': self.domain, } # Execute addition SQL commands after successfully created new users. if settings.DBMAIL_SQL_FOR_NEWLY_CREATED_USER: try: for sql_cmd in settings.DBMAIL_SQL_FOR_NEWLY_CREATED_USER: self.conn.query(sql_cmd, vars=vars_addition_sql) except Exception: pass # Create Amavisd policy for newly created user. if settings.AMAVISD_EXECUTE_SQL_WITHOUT_ENABLED and settings.AMAVISD_SQL_FOR_NEWLY_CREATED_USER: try: from libs.amavisd.core import AmavisdWrap amwrap = AmavisdWrap() for sql_cmd in settings.AMAVISD_SQL_FOR_NEWLY_CREATED_USER: amwrap.db.query(sql_cmd, vars=vars_addition_sql) except: pass web.logger( msg="Create user: %s." % (self.mail), domain=self.domain, event='create', ) return (True, ) except Exception, e: return (False, str(e))
str(addr).lower() for addr in old_member_of_aliases if addr not in member_of_aliases ] # Remove user from aliases if not in both existing_aliases and new_aliases. # Assign user to new aliases. if newly_assigned_aliases: try: self.conn.update( 'dbmail_aliases', vars={ 'mail': self.mail, 'newly_assigned_aliases': newly_assigned_aliases, }, where='alias IN $newly_assigned_aliases', deliver_to=web.sqlliteral( 'CONCAT($mail, ",", deliver_to)'), ) except: pass # Remove user from old assigned aliases. if removed_aliases: # Get profiles of alias accounts. alias_profiles = self.conn.select( 'dbmail_aliases', vars={ 'removed_aliases': removed_aliases, }, what='alias,deliver_to', where='alias IN $removed_aliases', )