root/management/bs-admin/trunk/cache_and_sync.py

#!/usr/bin/python

# You'll need these set in your environment before running this script
# export DJANGO_SETTINGS_MODULE=webmanagement.settings
# export PYTHONPATH="/data/"
#
#
#

#generic python imports
from os import listdir
from ConfigParser import ConfigParser
import time,os
from time import strftime
from datetime import datetime
from difflib import *
from subprocess import *
from shutil import *
import re
from sets import Set

#trac imports
from trac.perm import PermissionSystem
from trac.env import open_environment

#django imports
from django.contrib.auth.models import User
from django.core.exceptions import ObjectDoesNotExist
from django.core.mail import send_mail
from webmanagement.project_request.models import Project, License

#psycopg imports
import psycopg2

#Directories
TRAC_PARENT_PATH = '/var/lib/trac/sites/'
SVN_PARENT_PATH = '/var/lib/svn/'
MANAGEMENT_DIR = '/data/management/'
SVN_INIT_PATH = '/data/master-svn/initial-data/' # should these go in /usr/share?
TRAC_INIT_PATH = '/data/master-trac/initial-data/' # ^^
APACHE_CONFIG_PATH = '/etc/apache2/vhosts.d/policies/'
SVN_AUTHZ_FILE = '/var/lib/svn/admin/conf/bs_authz'

def create_nonexistant_approved_projects():
    projects = Project.objects.all()
    for p in projects:
        if(p.status == 'A' and not os.path.exists(TRAC_PARENT_PATH+p.short_name) and not os.path.exists(SVN_PARENT_PATH+p.short_name) ): #The project has been approved, but doesn't exist
            #format users with no spaces and a comma between names

            userlist = ','.join([u.username for u in p.members.all()])

            if(userlist.count(str(p.owner)) < 1):
                userlist += ","+str(p.owner)

            cmd = [MANAGEMENT_DIR + 'bs-admin.py',
                p.short_name, 
                '--create', 
                '--trac-name=' + p.name, 
                '--users=' + userlist, 
                '--trac-desc=' + str(p.description), 
                '--trac-footer=This project licensed under the ' + str(p.license.name),
                '--owner-email=' + str(p.email), 
                '--owner-username=' + str(p.owner), 
                '--owner-name=' + p.owner.first_name + ' '  + p.owner.last_name, 
                '--visibility=' + p.visibility, 
                '--license=' + str(p.license.name),
                '--licensefile=' + p.license.filename, 
                '--svn-initial-data=' + SVN_INIT_PATH + p.license.filename +'.svn', 
            ]
            #execute the project creation command
            print cmd
            pipe = Popen(cmd, cwd=MANAGEMENT_DIR, shell=False, stdout=PIPE).stdout
            data = pipe.read()
            pipe.close()
            #Email the owner
            msg= "Your project, " + p.name + ", has been created. You may visit it at http://beaversource.oregonstate.edu/projects/" + p.short_name + "/ \n\nPlease contact us if you have any problems,\nBeaverSource Admins"
            send_mail('[Project Created]  ' + p.short_name, msg, 'request@beaversource.oregonstate.edu', [p.email], fail_silently=False)

            print data            


def add_admin_permissions(shortname, admin_list):

    """As part of ticket #3266 we're making it so that all projects have an 
    admin group with view permissions and the admins are members of the group.
    This will be very helpful when attempting to debug a project we're not a 
    member of"""

    #Get the list of people who already have admin view permissions
    env = open_environment(TRAC_PARENT_PATH + shortname)
    time.sleep(2)
    temp_list = [user for user,permission in PermissionSystem(env).get_all_permissions() if permission=='admin']

    if(temp_list == []):
       
        #Create an admin group with all view permissions
        cmd = ['/usr/bin/trac-admin', TRAC_PARENT_PATH + shortname, 'permission', 'add', 'admin', 'BROWSER_VIEW', 'CHANGESET_VIEW', 'CONFIG_VIEW', 'FILE_VIEW', 'LOG_VIEW', 'MILESTONE_VIEW', 'REPORT_VIEW', 'ROADMAP_VIEW', 'SEARCH_VIEW', 'STRACTISTICS_VIEW', 'TICKET_VIEW', 'TIMELINE_VIEW', 'WIKI_VIEW']
        pipe = Popen(cmd, cwd=MANAGEMENT_DIR, shell=False, stdout=PIPE).stdout
        data = pipe.read()
        pipe.close()
    
    delta_list = Set(admin_list) - Set(temp_list)
    #add all members of the admin project to that group
    for user in delta_list:
        cmd = ['/usr/bin/trac-admin', TRAC_PARENT_PATH + shortname, 'permission', 'add', user, 'admin']
        pipe = Popen(cmd, cwd=MANAGEMENT_DIR, shell=False, stdout=PIPE).stdout
        data = pipe.read()
        pipe.close()


def sync_project_data():
    #get the admin list once for the add_admin_permissions function.
    env = open_environment(TRAC_PARENT_PATH + 'admin')
    time.sleep(2)
    admin_list = [user for user,permission in PermissionSystem(env).get_all_permissions() if permission=='TRAC_ADMIN']

    for TRAC_ENV in listdir(TRAC_PARENT_PATH):
        member_set = set()
        config = ConfigParser()
        config.read(TRAC_PARENT_PATH + TRAC_ENV + '/conf/trac.ini')
        name = config.get('project', 'name')
        shortname = config.get('project', 'shortname')
        descr = config.get('project', 'descr')
        url = config.get('project', 'url')

        #While I'm here, I may as well remove the mainnav line from the individual project since we define it globally.
        if(config.has_option('trac', 'mainnav')):
            if(config.remove_option('trac','mainnav')):
                config.write(open(TRAC_PARENT_PATH + TRAC_ENV + '/conf/trac.ini', 'wb'))
        #We also define the project icon globally.
        if(config.has_option('project', 'icon')):
            if(config.remove_option('trac','icon')):
                config.write(open(TRAC_PARENT_PATH + TRAC_ENV + '/conf/trac.ini', 'wb'))

        #get a list of the members of the project
        env = open_environment(TRAC_PARENT_PATH + TRAC_ENV)
        time.sleep(2)
        member_list = [user for user,permission in PermissionSystem(env).get_all_permissions() if permission=='TRAC_ADMIN']

        #Make sure all the admins have view permission on this project.  I pass in the admin list so it doesn't need to be repeatedly found.
        add_admin_permissions(shortname, admin_list)

        #update project details
        #get project, update its details
        try:
            p = Project.objects.get(short_name = shortname)
        except ObjectDoesNotExist:
            print "[Exception] Project %s doesn't exist, Please create it." % shortname
            continue
        p.name = name
        p.description = descr
        
        #update members list
        for person in member_list:

            m, created = User.objects.get_or_create(username=person)
            if(created):
                print "[Info] user %s had to be created." % m
            member_set.add(m)

        p.members = member_set # Set notation implicitly clears previous entries
        p.save()
        time.sleep(3)

def create_authz():

    auth_header = """
### This file is an example authorization file for svnserve.
### Its format is identical to that of mod_authz_svn authorization
### files.
### As shown below each section defines authorizations for the path and
### (optional) repository specified by the section name.
### The authorizations follow. An authorization line can refer to a
### single user, to a group of users defined in a special [groups]
### section, or to anyone using the '*' wildcard.  Each definition can
### grant read ('r') access, read-write ('rw') access, or no access
### ('').

[groups]
# harry_and_sally = harry,sally

"""
    projects = Project.objects.all()
    for p in projects:
        if(p.visibility != 'A' and p.status == 'A'): #non-public projects
            auth_file = open(SVN_PARENT_PATH + p.short_name + "/conf/authz",'w')
            auth_file.write(auth_header)
            auth_file.write("## project " + p.name + " at /data/trac/sites/" + p.short_name  + "\n")
            auth_file.write("[/]\n")
            if(p.visibility == 'M'):
                for person in p.members.values():
                    auth_file.write(person['username'] + " = rw\n")
            else:
                auth_file.write("* = r\n")
            auth_file.write('\n')

            auth_file.close()





def create_apache_config():
    #create apache config that requires valid user for any non-public project
    if(os.path.exists(APACHE_CONFIG_PATH + 'bs_projects')):
        copyfile(APACHE_CONFIG_PATH + 'bs_projects', APACHE_CONFIG_PATH + 'bs_projects.old')

    config_file = open(APACHE_CONFIG_PATH + 'bs_projects','w')

    projects = Project.objects.all()
    for p in projects:
        if(p.visibility != 'A' and p.status == 'A'): #Any non-public project
            #make sure your apache config has "Include /path/to/these/files/" for this to apple

            config_file.write("""
        <Location /svn/""" + p.short_name + """/>
                AuthzSVNAccessFile """ + SVN_PARENT_PATH + p.short_name + """/conf/authz
                Require valid-user
        </Location>
            """)

    config_file.close()

#    #diff the new and old config and prompt to reload the server, if needed
#    if(os.path.exists(APACHE_CONFIG_PATH + 'bs_projects.old') and os.path.exists(APACHE_CONFIG_PATH + 'bs_projects')):
#        d = Differ()
#        result = list(d.compare(open(APACHE_CONFIG_PATH + 'bs_projects').readlines(), open(APACHE_CONFIG_PATH + 'bs_projects.old').readlines()))
#        if(len(result) != 0):
#            print "Project visibility config has changed, please reload server"





def check_license_wiki_files():
    #check that all available licenses except other have valid svn and wiki files
    licenses = License.objects.all()
    for l in licenses:
        license_file = TRAC_INIT_PATH + l.filename + ".wiki"
        if(not os.path.exists(license_file)):
            print "[Exception] %s not found" % license_file

        svn_file = SVN_INIT_PATH + l.filename + ".svn"
        if(not os.path.exists(svn_file)):
            print "[Exception] %s not found" % svn_file


def smart_truncate(content, length=35, suffix='...'):
  if len(content) <= length:
    return content
  else:
    return ' '.join(content[:length+1].split(' ')[0:-1]) + suffix

def sync_project_profiles():
  
  # Create Social DB
  file = open("/var/www/beaversource.oregonstate.edu/config.php")
  social_config = file.read()
  match = re.search('(?<=dbuser = ).*', social_config)
  elgg_user = match.group(0).strip('";')
  match = re.search('(?<=dbpass = ).*', social_config)
  elgg_pass = match.group(0).strip('";')
  match = re.search('(?<=dbhost = ).*', social_config)
  elgg_host = match.group(0).strip('";')
  match = re.search('(?<=dbname = ).*', social_config)
  elgg_dbname = match.group(0).strip('";')

  try:
    conn = psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s'" % (elgg_dbname, elgg_user, elgg_host, elgg_pass))
  except:
    print "Unable to connect to the db"
    
  cursor = conn.cursor()    

  #Trac DB
  config = ConfigParser()
  if config.read(MANAGEMENT_DIR + 'beaversource.ini'):
    tracuser = config.get('trac', 'dbadmin')
    tracpass = config.get('trac', 'dbpass')
  else:
    print "Unable to read config file"

  try:
    tracconn = psycopg2.connect("dbname='bsc_projects' user='%s' host='dogwood.osuosl.org' password='%s'" % (tracuser, tracpass))
  except:
    print "Unable to connect to the trac db"

  trac_cursor = tracconn.cursor()
  

  # New Profile Sync
  meta_list = []
  elgg_list = []
  diff_list = []

  sql = "SELECT username FROM elgg_users WHERE user_type='project'"
  cursor.execute(sql)
  elgg_projects = cursor.fetchall()
  for each in elgg_projects:
    elgg_list.append(each[0])

  projects = Project.objects.all()
  for p in projects:
    if(p.status == 'A'):
      meta_list.append(p.short_name)

  for item in meta_list:
    if not item in elgg_list:
      diff_list.append(item)

  for each in diff_list:
    p = Project.objects.get(short_name__exact=each)
    sql = "SELECT ident FROM elgg_users WHERE username='%s'" % (p.owner.username)
    cursor.execute(sql)
    value = cursor.fetchall() # returns a list of tuples
    if value:
      ownerid = value[0][0] # we only need the first record in the first tuple
      sql = "INSERT INTO elgg_users (username, name, active, owner, user_type, moderation) VALUES ('%s', '%s', 'yes', '%s', 'project', 'no')" % (each, p.name, ownerid)
      cursor.execute(sql)
      conn.commit()


    else:
      sql = "INSERT INTO elgg_users (username, active, owner, user_type, moderation) VALUES ('%s', 'yes', '-1', 'person', 'no')" % (p.owner.username)
      cursor.execute(sql)
      conn.commit()
      
      sql = "SELECT ident FROM elgg_users WHERE username='%s'" % (p.owner.username)
      cursor.execute(sql)
      value = cursor.fetchall() # returns a list of tuples
      if value:
        ownerid = value[0][0] # we only need the first record in the first tuple
        sql = "INSERT INTO elgg_users (username, name, active, owner, user_type, moderation) VALUES ('%s', '%s', 'yes', '%s', 'project', 'no')" % (each, p.name, ownerid)
        cursor.execute(sql)
        conn.commit()

  # end of new profile sync

  # trac data sync
  projects = Project.objects.all()

  for p in projects:
    if(p.status == 'A'):
      trac_name = p.name 
      shortname = p.short_name
      descr = p.description 
      url = "http://beaversource.oregonstate.edu/projects/%s" % (p.short_name)

      visibility = "PUBLIC"

      trac_member_list = []
      for each in p.members.all():
        trac_member_list.append(each.username)
      
      elgg_member_list = []
      diff_member_list = []
      
      sql = "SELECT ident FROM elgg_users WHERE username='%s'" % (p.short_name)
      cursor.execute(sql)
      value = cursor.fetchall()
      if value:
        project_id = value[0][0]

        sql = "SELECT owner FROM elgg_friends WHERE friend='%s'" % (project_id)
        cursor.execute(sql)
        value = cursor.fetchall()
        for each in value:
          sql = "SELECT username FROM elgg_users WHERE ident='%s'" % (each[0])
          cursor.execute(sql)
          value = cursor.fetchall()
          name = value[0][0]
          elgg_member_list.append(name)


        for each in trac_member_list:
          if not each in elgg_member_list:
            diff_member_list.append(each)

        for each in diff_member_list:
          sql = "SELECT ident FROM elgg_users WHERE username='%s'" % (each)
          cursor.execute(sql)
          value = cursor.fetchall()
          if value:
            user_id = value[0][0]
            
            sql = "INSERT INTO elgg_friends VALUES (DEFAULT, '%s', '%s')" % (user_id, project_id)
            cursor.execute(sql)
            conn.commit()

        sql = "SELECT value FROM elgg_profile_data WHERE owner='%s' AND name='license'" % (project_id)
        cursor.execute(sql)
        value = cursor.fetchall()
        if value:
          sql = "UPDATE elgg_profile_data SET value='%s', access='%s' WHERE owner='%s' AND name='license'" % (p.license, visibility, project_id)
          cursor.execute(sql)
          conn.commit()
        else:
          sql = "INSERT INTO elgg_profile_data VALUES (DEFAULT, '%s', '%s', 'license', '%s')" % (project_id, visibility, p.license)
          cursor.execute(sql)
          conn.commit()
        
        sql = "SELECT value FROM elgg_profile_data WHERE owner='%s' AND name='biography'" % (project_id)
        cursor.execute(sql)
        value = cursor.fetchall()
        if value:
          sql = "UPDATE elgg_profile_data SET value=%s, access=%s WHERE owner=%s AND name='biography'"
          cursor.execute(sql, (descr, visibility, project_id))
          conn.commit()
        else:
          sql = "INSERT INTO elgg_profile_data VALUES (DEFAULT, %s, %s, 'biography', %s)"
          cursor.execute(sql, (project_id, visibility, descr))
          conn.commit()
        
        sql = "SELECT value FROM elgg_profile_data WHERE owner='%s' AND name='minibio'" % (project_id)
        cursor.execute(sql)
        value = cursor.fetchall()
        if value:
          sql = "UPDATE elgg_profile_data SET value=%s, access=%s WHERE owner=%s AND name='minibio'"
          cursor.execute(sql, (smart_truncate(descr), visibility, project_id))
          conn.commit()
        else:
          sql = "INSERT INTO elgg_profile_data VALUES (DEFAULT, %s, %s, 'minibio', %s)"
          cursor.execute(sql, (project_id, visibility, smart_truncate(descr)))
          conn.commit()
        
        sql = "SELECT time FROM master_wiki WHERE name='WikiStart' AND author='trac' AND dbuser='bs_%s_user' LIMIT 1" % (p.short_name)
        trac_cursor.execute(sql)
        trac_start_time = trac_cursor.fetchone()[0]
        trac_time = datetime.fromtimestamp(trac_start_time)
        trac_db_time = trac_time.strftime("%b %d, %Y - %I:%M %p")
        sql = "SELECT value FROM elgg_profile_data WHERE owner='%s' AND name='start_time'" % (project_id)
        cursor.execute(sql)
        value = cursor.fetchone()
        if value:
          sql = "UPDATE elgg_profile_data SET value='%s', access='%s' WHERE owner='%s' AND name='start_time'" % (trac_db_time, visibility, project_id)
          cursor.execute(sql)
          conn.commit()
        else:
          sql = "INSERT INTO elgg_profile_data VALUES (DEFAULT, '%s', '%s', 'start_time', '%s')" % (project_id, visibility, trac_db_time)
          cursor.execute(sql)
          conn.commit()

        sql = "SELECT changetime FROM master_ticket WHERE dbuser='bs_%s_user' ORDER BY time DESC LIMIT 1" % (p.short_name)
        trac_cursor.execute(sql)
        trac_last_ticket_change_time = trac_cursor.fetchone()
        if trac_last_ticket_change_time:
          trac_time = datetime.fromtimestamp(trac_last_ticket_change_time[0])
          trac_db_time = trac_time.strftime("%b %d, %Y - %I:%M %p")
        else:
          trac_db_time = "None"
        sql = "SELECT value FROM elgg_profile_data WHERE owner='%s' AND name='last_ticket_change'" % (project_id)
        cursor.execute(sql)
        value = cursor.fetchone()
        if value:
          sql = "UPDATE elgg_profile_data SET value='%s', access='%s' WHERE owner='%s' AND name='last_ticket_change'" % (trac_db_time, visibility, project_id)
          cursor.execute(sql)
          conn.commit()
        else:
          sql = "INSERT INTO elgg_profile_data VALUES (DEFAULT, '%s', '%s', 'last_ticket_change', '%s')" % (project_id, visibility, trac_db_time)
          cursor.execute(sql)
          conn.commit()
        
        sql = "SELECT time FROM master_wiki WHERE dbuser='bs_%s_user' ORDER BY time DESC LIMIT 1" % (p.short_name)
        trac_cursor.execute(sql)
        trac_last_wiki_change_time = trac_cursor.fetchone()
        if trac_last_wiki_change_time:
          trac_time = datetime.fromtimestamp(trac_last_wiki_change_time[0])
          trac_db_time = trac_time.strftime("%b %d, %Y - %I:%M %p")
        else:
          trac_db_time = "None"
        sql = "SELECT value FROM elgg_profile_data WHERE owner='%s' AND name='last_wiki_change'" % (project_id)
        cursor.execute(sql)
        value = cursor.fetchone()
        if value:
          sql = "UPDATE elgg_profile_data SET value='%s', access='%s' WHERE owner='%s' AND name='last_wiki_change'" % (trac_db_time, visibility, project_id)
          cursor.execute(sql)
          conn.commit()
        else:
          sql = "INSERT INTO elgg_profile_data VALUES (DEFAULT, '%s', '%s', 'last_wiki_change', '%s')" % (project_id, visibility, trac_db_time)
          cursor.execute(sql)
          conn.commit()
        
        sql = "SELECT rev,time FROM master_revision WHERE dbuser='bs_%s_user' ORDER BY time DESC LIMIT 1" % (p.short_name)
        trac_cursor.execute(sql)
        trac_last_commit = trac_cursor.fetchone()
        if trac_last_commit:
          trac_time = datetime.fromtimestamp(trac_last_commit[1])
          trac_db_time = "<a href=\"/projects/%s/changeset/%s\">Revision %s</a> (%s)" % (p.short_name, trac_last_commit[0], trac_last_commit[0], trac_time.strftime("%b %d, %Y - %I:%M %p"))
        else:
          trac_db_time = "None"
        sql = "SELECT value FROM elgg_profile_data WHERE owner='%s' AND name='last_commit'" % (project_id)
        cursor.execute(sql)
        value = cursor.fetchone()
        if value:
          sql = "UPDATE elgg_profile_data SET value='%s', access='%s' WHERE owner='%s' AND name='last_commit'" % (trac_db_time, visibility, project_id)
          cursor.execute(sql)
          conn.commit()
        else:
          sql = "INSERT INTO elgg_profile_data VALUES (DEFAULT, '%s', '%s', 'last_commit', '%s')" % (project_id, visibility, trac_db_time)
          cursor.execute(sql)
          conn.commit()

        sql = "SELECT value FROM elgg_profile_data WHERE owner='%s' AND name='workweb'" % (project_id)
        cursor.execute(sql)
        value = cursor.fetchall()
        if value:
          sql = "UPDATE elgg_profile_data SET value='%s', access='%s' WHERE owner='%s' AND name='workweb'" % (url, visibility, project_id)
          cursor.execute(sql)
          conn.commit()
        else:
          sql = "INSERT INTO elgg_profile_data VALUES (DEFAULT, '%s', '%s', 'workweb', '%s')" % (project_id, visibility, url)
          cursor.execute(sql)
          conn.commit()

        sql = "UPDATE elgg_users SET name='%s' WHERE ident='%s'" % (trac_name, project_id)
        cursor.execute(sql)
        conn.commit()

#Call all the funcation
create_nonexistant_approved_projects()
sync_project_data()
create_authz()
create_apache_config()
check_license_wiki_files()
#sync_project_profiles()