Source

django-spotnet / spotnet / stats / functions.py

from datetime import date as date_base, timedelta
from django.utils.translation import ugettext as _
from django.conf import settings as django_settings
from spotnet.models import Post
from spotnet.settings import CATEGORY_MAPPING


def date(year, month, day):
    year_int, month_int, day_int = int(year), int(month), int(day)
    return date_base(year_int, month_int, day_int)


def posts_per_day():
    """Returns the number of posts for each day.

    Returns an iterable of tuples (year, day-of-year, number-of-posts)
    for each day the database has posts.
    """

    from django.db import connection, transaction
    cursor = connection.cursor()

    if cursor.db.vendor == 'sqlite':
        query = """
SELECT
  django_extract('year',  "posted") AS year,
  django_extract('month', "posted") AS month,
  django_extract('day',   "posted") AS day,
  COUNT(*) AS posts
FROM %s
GROUP BY year, month, day
ORDER BY year, month, day""" % (Post._meta.db_table, )
    elif cursor.db.vendor == 'mysql':
        query = """
SELECT
  YEAR(posted)  AS year,
  MONTH(posted) AS month,
  DAY(posted)   AS day,
  COUNT(*) AS posts
FROM %s
GROUP BY year, month, day
ORDER BY year, month, day""" % (Post._meta.db_table, )
    elif cursor.db.vendor == 'postgresql':
        query = """
SELECT
  EXTRACT(YEAR  FROM posted) AS year,
  EXTRACT(MONTH FROM posted) AS month,
  EXTRACT(DAY   FROM posted) AS day,
  COUNT(*) AS posts
FROM %s
GROUP BY year, month, day
ORDER BY year, month, day""" % (Post._meta.db_table, )
    else:
        raise Exception("Unsupported database type")

    cursor.execute(query)
    data = cursor.fetchall()

    date_start = date(*data[0][0:3])
    date_end  = date(*data[-1][0:3])
    dayspan = (date_end - date_start).days
    one_day = timedelta(days=1)

    postings = []
    total_posts = 0
    prev_date = date_start - one_day
    for year, month, day, posts in data:
        cur_date = date(year, month, day)
        while (cur_date - prev_date).days > 1:
            postings.append(0)
            prev_date += one_day
        postings.append(posts)
        total_posts += posts
        prev_date = cur_date

    assert len(postings) == dayspan + 1

    return postings, date_start, date_end, total_posts, dayspan


def posts_per_category():
    from django.db import connection, transaction
    cursor = connection.cursor()

    if cursor.db.vendor == 'postgresql':
        query = """
SELECT
  category,
  COUNT(*) AS posts,
  SUM(size) AS size
FROM %s
GROUP BY category
ORDER BY posts DESC""" % (Post._meta.db_table, )
    else:
        raise Exception("Unsupported database type")

    cursor.execute(query)
    data = cursor.fetchall()

    results = []
    unknown_category = (0, 0)
    for cat, posts, size in data:
        if cat is None:
            assert unknown_category == (0, 0)
            unknown_category = (posts, size)
        else:
            results.append((CATEGORY_MAPPING.get(cat, _('Invalid')), posts, size))

    results.append((_('Unknown'), ) + unknown_category)

    return results


def spotnet_table_filesize():
    "Get the filesize of the table storing the spotnet posts"

    from django.db import connection, transaction
    cursor = connection.cursor()

    if cursor.db.vendor == 'sqlite':
        return None
    elif cursor.db.vendor == 'mysql':
        query = """
SELECT
  (DATA_LENGTH + INDEX_LENGTH - DATA_FREE
FROM INFORMATION_SCHEMA.TABLES
WHERE
  TABLE_SCHEMA = '%s'
AND
  TABLE_NAME = '%s'""" % (
        django_settings.DATABASES['default']['name'],
        Post._meta.db_table,
    )
    elif cursor.db.vendor == 'postgresql':
        query = "SELECT pg_total_relation_size('%s')" % (Post._meta.db_table, )
    else:
        raise Exception("Unsupported database type")

    cursor.execute(query)
    data = cursor.fetchone()

    return data[0]
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.