def show_active_users_count(month='', year=''):
"""
Show the number of users logged in within 3months
"""
# NOTE: Fabric parameters are always strings
# Get the month's
now = datetime.utcnow()
month = int(month) if month else now.month
year = int(year) if year else now.year
weekday, endday = calendar.monthrange(year, month)
start_date = datetime(year=year, month=month, day=1, hour=0, minute=0, second=0)
end_date = datetime(year=year, month=month, day=endday, hour=23, minute=59, second=59)
startstr = start_date.strftime('%Y-%m-%d %H:%M:%S')
endstr = end_date.strftime('%Y-%m-%d %H:%M:%S')
dbm = DatabaseManager('trac_admin')
sql = '''
SELECT COUNT(*)
FROM user
WHERE
last_login >= TIMESTAMP('%s')
AND
last_login <= TIMESTAMP('%s')\G
''' % (startstr, endstr)
output = dbm.run(sql, re.compile(r': (\d+)'))
logger.info('Date range: %s - %s ' % (startstr, endstr))
logger.info('Active users: %s' % output)
def show_users_count():
"""
Show the number of registered users
"""
dbm = DatabaseManager('trac_admin')
sql = 'SELECT COUNT(*) FROM user\G'
output = dbm.run(sql, re.compile(r': (\d+)'))
logger.info('Users: %s' % output)
def show_projects_top_weekly_downloads(week='', year='', limit='50'):
"""
Show the number of downloads done within selected week
Example query::
fab -H hostname service.show_projects_top_downloads
fab -H hostname service.show_projects_top_downloads:week=10
fab -H hostname service.show_projects_top_downloads:week=9,year=2011,limit=10
"""
# NOTE: Fabric parameters are always strings
# Get the month's start and end date
now = datetime.utcnow()
# isocalendar returns tuple year, week, weekday
week = int(week) if week else now.isocalendar()[1]
year = int(year) if year else now.year
first_day = datetime(year=year,month=1,day=1)
# If first day is > Thu, next week is the first week
if first_day.isoweekday() > 4:
first_day = first_day + timedelta(days=7-first_day.weekday())
else:
first_day = first_day - timedelta(days=first_day.weekday())
start_date = first_day + timedelta(weeks=week-1)
end_date = start_date + timedelta(weeks=1)
startstr = start_date.strftime('%Y-%m-%d %H:%M:%S')
endstr = end_date.strftime('%Y-%m-%d %H:%M:%S')
dbm = DatabaseManager('trac_analytical')
sql = '''
SELECT project_dim.project_name AS Project, COUNT(*) AS Downloads
FROM event_fact, project_dim
WHERE event_sk = (
SELECT event_sk
FROM event_dim
WHERE action_name = 'release_downloaded'
)
AND (
timestamp >= TIMESTAMP('%(startdate)s')
AND
timestamp < TIMESTAMPADD(WEEK, 1, '%(startdate)s')
)
AND project_dim.project_sk = event_fact.project_sk
GROUP BY event_fact.project_sk
ORDER BY Downloads DESC
LIMIT %(limit)s;
''' % dict(startdate=startstr, limit=limit)
output = dbm.run(sql)
logger.info('Date range: week %s, %s - %s ' % (str(week), startstr, endstr))
logger.info('Top %s downloads:\n %s' % (limit, output))
def get_database(name):
"""
Dumps and loads the specified database dump
Parameters:
name = Name of the database to retrieve
"""
assert name, 'Please provide database name as param: name=<name>'
# Dump the database. Dumpdir is the location in remote environment
dbm = DatabaseManager(name)
dump_path = dbm.dump()
# Retrieve dump to current folder, with same name as the remote has it
get(dump_path, os.path.basename(dump_path))
def get_database(name):
"""
Dumps and loads the specified database dump
Parameters:
name = Name of the database to retrieve
"""
assert name, 'Please provide database name as param: name=<name>'
# Dump the database. Dumpdir is the location in remote environment
dbm = DatabaseManager(name)
dump_path = dbm.dump()
# Retrieve dump to current folder, with same name as the remote has it
get(dump_path, os.path.basename(dump_path))
def show_projects_top_downloads(month='', year='', limit='50'):
"""
Show the number of downloads done within selected month
Example query::
fab -H hostname service.show_projects_top_downloads
fab -H hostname service.show_projects_top_downloads:month=10
fab -H hostname service.show_projects_top_downloads:month=9,year=2011,limit=10
"""
# NOTE: Fabric parameters are always strings
# Get the month's start and end date
now = datetime.utcnow()
month = int(month) if month else now.month
year = int(year) if year else now.year
weekday, endday = calendar.monthrange(year, month)
start_date = datetime(year=year, month=month, day=1, hour=0, minute=0, second=0)
end_date = datetime(year=year, month=month, day=endday, hour=23, minute=59, second=59)
startstr = start_date.strftime('%Y-%m-%d %H:%M:%S')
endstr = end_date.strftime('%Y-%m-%d %H:%M:%S')
dbm = DatabaseManager('trac_analytical')
sql = '''
SELECT project_dim.project_name AS Project, COUNT(*) AS Downloads
FROM event_fact, project_dim
WHERE event_sk = (
SELECT event_sk
FROM event_dim
WHERE action_name = 'release_downloaded'
)
AND (
timestamp >= TIMESTAMP('%(startdate)s')
AND
timestamp < TIMESTAMPADD(MONTH, 1, '%(startdate)s')
)
AND project_dim.project_sk = event_fact.project_sk
GROUP BY event_fact.project_sk
ORDER BY Downloads DESC
LIMIT %(limit)s;
''' % dict(startdate=startstr, limit=limit)
output = dbm.run(sql)
logger.info('Date range: %s - %s ' % (startstr, endstr))
logger.info('Top %s downloads:\n %s' % (limit, output))
def project_downloaders(from_date='', to_date='', projects=''):
"""
Get list user emails that have downloaded from a project within given time.
Example query::
fab -H hostname "service.project_downloaders:from_date=2011-07-01,to_date=2011-12-31,projects=foo;bar"
Note the date YYYY-MM-DD
"""
for param in (from_date, to_date):
if not re.match(r'\d{4}-\d{2}-\d{2}', param):
print 'Argument %s is not a valid date' % param
return
# split from ; and quote with ''
projects = projects.split(';')
print 'Projects: %s' % ', '.join(projects)
projects = ["'%s'" % p for p in projects]
sql = '''
SELECT user_dim.mail FROM event_fact, user_dim
WHERE event_fact.event_sk = (
SELECT event_sk
FROM event_dim
WHERE action_name
LIKE 'release_downloaded'
)
AND event_fact.user_sk = user_dim.user_sk
AND event_fact.user_sk NOT IN (
SELECT user_sk FROM user_dim
WHERE username IN ('anonymous', '<Inapplicable>')
)
AND user_dim.mail != ''
AND event_fact.project_sk IN (
SELECT DISTINCT project_sk FROM project_dim WHERE identifier in (%(projects)s)
)
AND event_fact.timestamp >= TIMESTAMP('%(from_date)s 0:0:0')
AND event_fact.timestamp < TIMESTAMP('%(to_date)s 23:59:59')
GROUP BY user_dim.user_sk;
''' % {'from_date': from_date, 'to_date': to_date, 'projects': ','.join(projects) }
dbm = DatabaseManager('trac_analytical')
output = dbm.run(sql)
print output
def show_projects_downloads(month='', year=''):
"""
Show the number of downloads done within selected month
"""
# NOTE: Fabric parameters are always strings
# Get the month's start and end date
now = datetime.utcnow()
month = int(month) if month else now.month
year = int(year) if year else now.year
weekday, endday = calendar.monthrange(year, month)
start_date = datetime(year=year, month=month, day=1, hour=0, minute=0, second=0)
end_date = datetime(year=year, month=month, day=endday, hour=23, minute=59, second=59)
startstr = start_date.strftime('%Y-%m-%d %H:%M:%S')
endstr = end_date.strftime('%Y-%m-%d %H:%M:%S')
dbm = DatabaseManager('trac_analytical')
sql = '''
SELECT COUNT(*)
FROM event_fact AS ef
WHERE
event_sk = (
SELECT event_sk
FROM event_dim
WHERE action_name = 'release_downloaded'
)
AND
ef.timestamp >= TIMESTAMP('%s')
AND
ef.timestamp <= TIMESTAMP('%s')\G
''' % (startstr, endstr)
output = dbm.run(sql, re.compile(r': (\d+)'))
logger.info('Date range: %s - %s ' % (startstr, endstr))
logger.info('Project downloads: %s' % output)
