def get_messages( dsconf, start, end, community ):
"""Get all messages between times 'start' and 'end' from the database."""
# Connect to database
cursor = mysql.get_cursor( dsconf )
# Community given?
where_clause = ''
where_arguments = ()
if community is not None:
where_clause += ' AND `posts`.`threadid` IN ('+\
'SELECT `threads`.`threadid` FROM `threads` '+\
'WHERE `threads`.`forumid` = %s)'
where_arguments = (community,)
# Execute query to get all users messages
query = 'SELECT `posts`.`postid` AS `id`,'+\
'`posts`.`userid` AS `user_id`,'+\
'`posts`.`threadid` AS `conversation_id`,'+\
'`posts`.`posteddate` AS `datetime`,'+\
'`replies`.`origpostid` AS `target_id`,'+\
'`replies`.`origdate` AS `target_datetime` '+\
'FROM `posts` '+\
'LEFT JOIN `replies` '+\
'ON (`replies`.`replyingpostid` = `posts`.`postid`) '+\
'WHERE `posts`.`posteddate` BETWEEN %s AND %s' + where_clause
cursor.execute( query , ( start , end ) + where_arguments )
results = cursor.fetchall()
cursor.close()
return results
def get_users( dsconf ):
"""Get all users from the database."""
# Connect to database
cursor = mysql.get_cursor( dsconf )
# Get the users from database
cursor.execute( 'SELECT `users`.`userid` AS `id` FROM `users`' )
results = cursor.fetchall()
cursor.close()
return results
def get_communities( dsconf ):
"""Get all available communities from the database."""
# Connect to database
cursor = mysql.get_cursor( dsconf )
# Get the communities from database
cursor.execute( 'SELECT 1 AS `id`,'+\
'"TiddlyWiki" AS `title`,'+\
'MIN(`posts`.`datetime`) AS `start_date`,'+\
'MAX(`posts`.`datetime`) AS `end_date` '+\
'FROM `posts`' )
results = cursor.fetchall()
cursor.close()
return results
def get_communities( dsconf ):
"""Get all available communities from the database."""
# Connect to database
cursor = mysql.get_cursor( dsconf )
# Get the community from database
cursor.execute( 'SELECT `cm`.`id`, `cm`.`title`,' +\
'MAX(`m`.`datetime`) AS `start_date`,' +\
'MIN(`m`.`datetime`) AS `end_date` ' +\
'FROM `communities` AS `cm` ' +\
'INNER JOIN `conversations` AS `c` ' +\
'ON (`cm`.`id` = `c`.`community_id`) ' +\
'INNER JOIN `messages` AS `m` ' +\
'ON (`c`.`id` = `m`.`conversation_id`) ' +\
'GROUP BY `cm`.`id`, `cm`.`title`' )
results = cursor.fetchall()
cursor.close()
return results
def get_conversations( dsconf, community ):
"""Get all available conversations from the database."""
# Connect to database
cursor = mysql.get_cursor( dsconf )
# Community given?
where_clause = ''
where_arguments = ()
if community is not None:
where_clause += ' WHERE `threads`.`forumid` = %s'
where_arguments = (community,)
# Get the conversations from database
cursor.execute( 'SELECT `threads`.`threadid` AS `id`,'+\
'`threads`.`forumid` AS `community_id`'+\
'FROM `threads`' + where_clause, where_arguments )
results = cursor.fetchall()
cursor.close()
return results
def get_communities( dsconf ):
"""Get all available communities from the database."""
# Connect to database
cursor = mysql.get_cursor( dsconf )
# Get the communities from database
cursor.execute( 'SELECT DISTINCT(`threads`.`forumid`) AS `id`,'+\
'`forums`.`title` AS `title`,'+\
'MIN(`posts`.`posteddate`) AS `start_date`,'+\
'MAX(`posts`.`posteddate`) AS `end_date` '+\
'FROM `threads` '+\
'LEFT JOIN `forums` '+\
'ON (`threads`.`forumid` = `forums`.`forumid`) '+\
'LEFT JOIN `posts` '+\
'ON (`posts`.`threadid` = `threads`.`threadid`) '+\
'GROUP BY `threads`.`forumid`' )
results = cursor.fetchall()
cursor.close()
return results
# Only community 1L is available
if community != 1L and community is not None:
return ()
return ((1L,1L),)
################################################################################
def get_messages( dsconf, start, end, community ):
"""Get all messages between times 'start' and 'end' from the database."""
# Only community 1L is available
if community != 1L and community is not None:
return ()
# Connect to database
cursor = mysql.get_cursor( dsconf )
# Execute query to get all users messages
query = 'SELECT `posts`.`id` AS `id`,'+\
'`posts`.`userid` AS `user_id`,'+\
'1 AS `conversation_id`,'+\
'`posts`.`datetime` AS `datetime`,'+\
'`thread`.`targetid` AS `target_id`,'+\
'`thread`.`replyDate` AS `target_datetime` '+\
'FROM `posts` '+\
'LEFT JOIN `thread` '+\
'ON (`thread`.`sourceid` = `posts`.`id`) '+\
'WHERE `posts`.`datetime` BETWEEN %s AND %s'
cursor.execute( query , ( start , end ) )
results = cursor.fetchall()
cursor.close()
