Ejemplo n.º 1
0
 def __total_page_init_author(self, cursor, table):
     """
     Total number of different pages started per author
     We count the number of different pages a certain author edited for the first time
     The opposite is useless, since only one author init every article
     """
     ##    Total number of different pages started by a logged author
     dbaccess.dropView(cursor, table + "_init_pages_author_logged")
     dbaccess.createView(cursor, view=table+"_init_pages_author_logged",\
     columns="author, init_pages", query= "SELECT author, COUNT(DISTINCT page_id) AS init_pages FROM "+\
     table+"_page_min_timestamp_logged WHERE author!=0 GROUP BY author ORDER BY init_pages")
     ##    The same for annons
     dbaccess.dropView(cursor, table + "_init_pages_author_annons")
     dbaccess.createView(cursor, view=table+"_init_pages_author_annons",\
     columns="author_text, init_pages", query= "SELECT author_text, COUNT(DISTINCT page_id)"+\
     " AS init_pages FROM "+table+"_page_min_timestamp_annons GROUP BY author_text ORDER BY init_pages")
Ejemplo n.º 2
0
 def __total_page_init_author(self,cursor, table):
     """
     Total number of different pages started per author
     We count the number of different pages a certain author edited for the first time
     The opposite is useless, since only one author init every article
     """
 ##    Total number of different pages started by a logged author
     dbaccess.dropView(cursor, table+"_init_pages_author_logged")
     dbaccess.createView(cursor, view=table+"_init_pages_author_logged",\
     columns="author, init_pages", query= "SELECT author, COUNT(DISTINCT page_id) AS init_pages FROM "+\
     table+"_page_min_timestamp_logged WHERE author!=0 GROUP BY author ORDER BY init_pages")
 ##    The same for annons
     dbaccess.dropView(cursor, table+"_init_pages_author_annons")
     dbaccess.createView(cursor, view=table+"_init_pages_author_annons",\
     columns="author_text, init_pages", query= "SELECT author_text, COUNT(DISTINCT page_id)"+\
     " AS init_pages FROM "+table+"_page_min_timestamp_annons GROUP BY author_text ORDER BY init_pages")
Ejemplo n.º 3
0
 def __total_page_init_author_time(self,cursor,interval,table):
     ##	Total number of different articles initiated per author
     ##	for different time intervals
     if interval in self.type_interval_select:
         ##    Total number of different pages started by a logged author
         dbaccess.dropView(cursor, table+"_init_pages_author_logged_"+interval)
         dbaccess.createView(cursor, view=table+"_init_pages_author_logged_"+interval,\
         columns="author, init_pages, "+self.type_interval_columns[interval],\
         query= "SELECT author, COUNT(DISTINCT page_id) AS init_pages, " +self.type_interval_select[interval]+\
         " FROM "+table+"_page_min_timestamp_logged WHERE author!=0 GROUP BY author, "+\
         self.type_interval_group[interval]+" ORDER BY init_pages")
         ##    The same for annons
         dbaccess.dropView(cursor, table+"_init_pages_author_annons_"+interval)
         dbaccess.createView(cursor, view=table+"_init_pages_author_annons_"+interval,\
         columns="author_text, init_pages, "+self.type_interval_columns[interval],\
         query= "SELECT author_text, COUNT(DISTINCT page_id) AS init_pages, " +\
         self.type_interval_select[interval]+" FROM "+table+"_page_min_timestamp_annons GROUP BY author_text, "+\
         self.type_interval_group[interval]+" ORDER BY init_pages")
     else:
         print "You chose an unsupported time interval.\n"
         print "Please choose one of the following [days, weeks, moths, quarters, years]\n"
Ejemplo n.º 4
0
 def __total_rev_time(self,cursor,interval,table, target="author"):
 ##    target=author Total number of revisions per author in a certain time interval
 ##    target=article Total number of revisions per article received in a certain time interval
     if interval in self.type_interval_select:
 ##        logged users
         dbaccess.dropView(cursor, table+"_revs_"+target+"_logged_"+interval)
         dbaccess.createView(cursor, view=table+"_revs_"+target+"_logged_"+interval, columns=target+\
         ", tot_revisions, "+self.type_interval_columns[interval], query="SELECT "+target+\
         ", count(*) AS tot_revisions, "+self.type_interval_select[interval]+" FROM "+table+\
         " WHERE author!=0 GROUP BY "+target+", "+self.type_interval_group[interval]+" ORDER BY tot_revisions")
 ##        all users per page_id
         if target=="page_id":
             dbaccess.dropView(cursor, table+"_revs_"+target+"_all_"+interval)
             dbaccess.createView(cursor, view=table+"_revs_"+target+"_all_"+interval, columns=target+\
             ", tot_revisions, "+self.type_interval_columns[interval], query="SELECT "+target+\
             ", count(*) AS tot_revisions, "+self.type_interval_select[interval]+" FROM "+table+" GROUP BY "+\
             target+", "+self.type_interval_group[interval]+" ORDER BY tot_revisions")
 ##        annons
         if target == "author":
             target = "author_text"
         dbaccess.dropView(cursor, table+"_revs_"+target+"_annons_"+interval)
         dbaccess.createView(cursor, view=table+"_revs_"+target+"_annons_"+interval, columns=target+\
         ", tot_revisions, "+self.type_interval_columns[interval], query="SELECT "+target+\
         ", count(*) AS tot_revisions, "+self.type_interval_select[interval]+" FROM "+table+\
         " WHERE author=0 GROUP BY "+target+", "+self.type_interval_group[interval]+" ORDER BY tot_revisions")
     else:
         print "You chose an unsupported time interval.\n"
         print "Please choose one of the following [days, weeks, moths, quarters, years]\n"
Ejemplo n.º 5
0
    def __total_rev_time(self, cursor, interval, table, target="author"):
        ##    target=author Total number of revisions per author in a certain time interval
        ##    target=article Total number of revisions per article received in a certain time interval
        if interval in self.type_interval_select:
            ##        logged users
            dbaccess.dropView(
                cursor, table + "_revs_" + target + "_logged_" + interval)
            dbaccess.createView(cursor, view=table+"_revs_"+target+"_logged_"+interval, columns=target+\
            ", tot_revisions, "+self.type_interval_columns[interval], query="SELECT "+target+\
            ", count(*) AS tot_revisions, "+self.type_interval_select[interval]+" FROM "+table+\
            " WHERE author!=0 GROUP BY "+target+", "+self.type_interval_group[interval]+" ORDER BY tot_revisions")
            ##        all users per page_id
            if target == "page_id":
                dbaccess.dropView(
                    cursor, table + "_revs_" + target + "_all_" + interval)
                dbaccess.createView(cursor, view=table+"_revs_"+target+"_all_"+interval, columns=target+\
                ", tot_revisions, "+self.type_interval_columns[interval], query="SELECT "+target+\
                ", count(*) AS tot_revisions, "+self.type_interval_select[interval]+" FROM "+table+" GROUP BY "+\
                target+", "+self.type_interval_group[interval]+" ORDER BY tot_revisions")

    ##        annons
            if target == "author":
                target = "author_text"
            dbaccess.dropView(
                cursor, table + "_revs_" + target + "_annons_" + interval)
            dbaccess.createView(cursor, view=table+"_revs_"+target+"_annons_"+interval, columns=target+\
            ", tot_revisions, "+self.type_interval_columns[interval], query="SELECT "+target+\
            ", count(*) AS tot_revisions, "+self.type_interval_select[interval]+" FROM "+table+\
            " WHERE author=0 GROUP BY "+target+", "+self.type_interval_group[interval]+" ORDER BY tot_revisions")
        else:
            print "You chose an unsupported time interval.\n"
            print "Please choose one of the following [days, weeks, moths, quarters, years]\n"
Ejemplo n.º 6
0
 def __total_rev_diff(self,cursor, table, target="author"):
 
     ##	target=author Total number of different pages per author
     ##	target=page_id Total number of different authors per page
     if target=="author":
         field_distinct="page_id"
     elif target=="page_id":
         field_distinct="author"
         
     dbaccess.dropView(cursor, table+"_diff_"+field_distinct+"_"+target+"_logged")
     dbaccess.createView(cursor, view=table+"_diff_"+field_distinct+"_"+target+"_logged",\
     columns=target+", diff_"+field_distinct, query="SELECT "+target+", COUNT(DISTINCT "+\
     field_distinct+") AS diff_"+field_distinct+" FROM "+table+" WHERE author!=0 GROUP BY "+\
     target+" ORDER BY diff_"+field_distinct)
 ##        Total number of different authors per page (annons included)
     if target=="page_id":
         dbaccess.dropView(cursor, table+"_diff_"+field_distinct+"_"+target+"_all")
         dbaccess.createView(cursor, view=table+"_diff_"+field_distinct+"_"+target+"_all",\
         columns=target+", diff_"+field_distinct, query="SELECT "+target+", COUNT(DISTINCT "+\
         field_distinct+") AS diff_"+field_distinct+" FROM "+table+" GROUP BY "+\
         target+" ORDER BY diff_"+field_distinct)
 ##        target=author Total number of different pages per annon author
 ##        target=page_id Total number of different annons per page
     if target == "author":
         target = "author_text"
     dbaccess.dropView(cursor, table+"_diff_"+field_distinct+"_"+target+"_annons")
     dbaccess.createView(cursor, view=table+"_diff_"+field_distinct+"_"+target+"_annons",\
     columns=target+", diff_"+field_distinct, query="SELECT "+target+", COUNT(DISTINCT "+\
     field_distinct+") AS diff_"+field_distinct+" FROM "+table+" WHERE author=0 GROUP BY "+\
     target+" ORDER BY diff_"+field_distinct)
Ejemplo n.º 7
0
 def __total_page_init_author_time(self, cursor, interval, table):
     ##	Total number of different articles initiated per author
     ##	for different time intervals
     if interval in self.type_interval_select:
         ##    Total number of different pages started by a logged author
         dbaccess.dropView(cursor,
                           table + "_init_pages_author_logged_" + interval)
         dbaccess.createView(cursor, view=table+"_init_pages_author_logged_"+interval,\
         columns="author, init_pages, "+self.type_interval_columns[interval],\
         query= "SELECT author, COUNT(DISTINCT page_id) AS init_pages, " +self.type_interval_select[interval]+\
         " FROM "+table+"_page_min_timestamp_logged WHERE author!=0 GROUP BY author, "+\
         self.type_interval_group[interval]+" ORDER BY init_pages")
         ##    The same for annons
         dbaccess.dropView(cursor,
                           table + "_init_pages_author_annons_" + interval)
         dbaccess.createView(cursor, view=table+"_init_pages_author_annons_"+interval,\
         columns="author_text, init_pages, "+self.type_interval_columns[interval],\
         query= "SELECT author_text, COUNT(DISTINCT page_id) AS init_pages, " +\
         self.type_interval_select[interval]+" FROM "+table+"_page_min_timestamp_annons GROUP BY author_text, "+\
         self.type_interval_group[interval]+" ORDER BY init_pages")
     else:
         print "You chose an unsupported time interval.\n"
         print "Please choose one of the following [days, weeks, moths, quarters, years]\n"
Ejemplo n.º 8
0
    def __total_rev_diff_time(self, cursor, interval, table, target="author"):
        """
        target = author Recovers total num of different articles contributed by every author
        classifying results in temporal intervals
        target = article Recovers number of different authors who revised every article
        classifying results in temporal intervals
        """
        if target == "author":
            field_distinct = "page_id"
        elif target == "page_id":
            field_distinct = "author"

        if interval in self.type_interval_select:
            dbaccess.dropView(
                cursor, table + "_diff_" + field_distinct + "_" + target +
                "_logged_" + interval)
            dbaccess.createView(cursor, view=table+"_diff_"+field_distinct+"_"+target+"_logged_"+interval,\
            columns=target+", diff_"+field_distinct+", "+self.type_interval_columns[interval], query="SELECT "+\
            target+", COUNT(DISTINCT "+field_distinct+") AS diff_"+field_distinct+", "+\
            self.type_interval_select[interval]+" FROM "+table+" WHERE author!=0 GROUP BY "+target+", "+\
            self.type_interval_group[interval]+" ORDER BY diff_"+field_distinct)
            if target == "page_id":
                dbaccess.dropView(
                    cursor, table + "_diff_" + field_distinct + "_" + target +
                    "_all_" + interval)
                dbaccess.createView(cursor, view=table+"_diff_"+field_distinct+"_"+target+"_all_"+\
                interval, columns=target+", diff_"+field_distinct+", "+self.type_interval_columns[interval],\
                query="SELECT "+target+", COUNT(DISTINCT "+field_distinct+") AS diff_"+field_distinct+", "+\
                self.type_interval_select[interval]+" FROM "+table+" GROUP BY "+target+", "+\
                self.type_interval_group[interval]+" ORDER BY diff_"+field_distinct)

            if target == "author":
                target = "author_text"
            dbaccess.dropView(
                cursor, table + "_diff_" + field_distinct + "_" + target +
                "_annons_" + interval)
            dbaccess.createView(cursor, view=table+"_diff_"+field_distinct+"_"+target+"_annons_"+\
            interval, columns=target+", diff_"+field_distinct+", "+self.type_interval_columns[interval],\
            query="SELECT "+target+", COUNT(DISTINCT "+field_distinct+") AS diff_"+field_distinct+", "+\
            self.type_interval_select[interval]+" FROM "+table+" WHERE author=0 GROUP BY "+target+", "+\
            self.type_interval_group[interval]+" ORDER BY diff_"+field_distinct)
        else:
            print "You chose an unsupported time interval.\n"
            print "Please choose one of the following [days, weeks, moths, quarters, years]\n"
Ejemplo n.º 9
0
 def __total_rev(self,cursor, table, target="author"):
 ##    target=author --> Contributions per logged user, without annons
 ##    target=page_id --> Contributions per page, without annons
     dbaccess.dropView(cursor, table+"_revs_"+target+"_logged")
     dbaccess.createView(cursor, view=table+"_revs_"+target+"_logged",\
     columns=target+", tot_revisions", query="SELECT "+target+", count(*) AS tot_revisions FROM "+\
     table+" WHERE author!=0 GROUP BY "+target+" ORDER BY tot_revisions")
 ##        Idem , sum up annons and registered users (only for pages)
     if target=="page_id":
         dbaccess.dropView(cursor, table+"_revs_"+target+"_all")
         dbaccess.createView(cursor, view=table+"_revs_"+target+"_all",\
         columns=target+", tot_revisions", query="SELECT "+target+", count(*) AS tot_revisions FROM "+\
         table+" GROUP BY "+target+" ORDER BY tot_revisions")
 ##        Idem only with annons edits, order by IP address
     if target == "author":
         target = "author_text"
     dbaccess.dropView(cursor, table+"_revs_"+target+"_annons")
     dbaccess.createView(cursor, view=table+"_revs_"+target+"_annons",\
     columns=target+", tot_revisions", query="SELECT "+target+", count(*) AS tot_revisions FROM "+\
     table+" WHERE author=0 GROUP BY "+target+" ORDER BY tot_revisions")
Ejemplo n.º 10
0
    def __total_rev(self, cursor, table, target="author"):
        ##    target=author --> Contributions per logged user, without annons
        ##    target=page_id --> Contributions per page, without annons
        dbaccess.dropView(cursor, table + "_revs_" + target + "_logged")
        dbaccess.createView(cursor, view=table+"_revs_"+target+"_logged",\
        columns=target+", tot_revisions", query="SELECT "+target+", count(*) AS tot_revisions FROM "+\
        table+" WHERE author!=0 GROUP BY "+target+" ORDER BY tot_revisions")
        ##        Idem , sum up annons and registered users (only for pages)
        if target == "page_id":
            dbaccess.dropView(cursor, table + "_revs_" + target + "_all")
            dbaccess.createView(cursor, view=table+"_revs_"+target+"_all",\
            columns=target+", tot_revisions", query="SELECT "+target+", count(*) AS tot_revisions FROM "+\
            table+" GROUP BY "+target+" ORDER BY tot_revisions")

    ##        Idem only with annons edits, order by IP address
        if target == "author":
            target = "author_text"
        dbaccess.dropView(cursor, table + "_revs_" + target + "_annons")
        dbaccess.createView(cursor, view=table+"_revs_"+target+"_annons",\
        columns=target+", tot_revisions", query="SELECT "+target+", count(*) AS tot_revisions FROM "+\
        table+" WHERE author=0 GROUP BY "+target+" ORDER BY tot_revisions")
Ejemplo n.º 11
0
 def __total_rev_diff_time(self,cursor,interval,table, target="author"):
     """
     target = author Recovers total num of different articles contributed by every author
     classifying results in temporal intervals
     target = article Recovers number of different authors who revised every article
     classifying results in temporal intervals
     """
     if target=="author":
         field_distinct="page_id"
     elif target=="page_id":
         field_distinct="author"
     
     if interval in self.type_interval_select:
         dbaccess.dropView(cursor, table+"_diff_"+field_distinct+"_"+target+"_logged_"+interval)
         dbaccess.createView(cursor, view=table+"_diff_"+field_distinct+"_"+target+"_logged_"+interval,\
         columns=target+", diff_"+field_distinct+", "+self.type_interval_columns[interval], query="SELECT "+\
         target+", COUNT(DISTINCT "+field_distinct+") AS diff_"+field_distinct+", "+\
         self.type_interval_select[interval]+" FROM "+table+" WHERE author!=0 GROUP BY "+target+", "+\
         self.type_interval_group[interval]+" ORDER BY diff_"+field_distinct)
         if target=="page_id":
             dbaccess.dropView(cursor, table+"_diff_"+field_distinct+"_"+target+"_all_"+interval)
             dbaccess.createView(cursor, view=table+"_diff_"+field_distinct+"_"+target+"_all_"+\
             interval, columns=target+", diff_"+field_distinct+", "+self.type_interval_columns[interval],\
             query="SELECT "+target+", COUNT(DISTINCT "+field_distinct+") AS diff_"+field_distinct+", "+\
             self.type_interval_select[interval]+" FROM "+table+" GROUP BY "+target+", "+\
             self.type_interval_group[interval]+" ORDER BY diff_"+field_distinct)
         
         if target == "author":
             target = "author_text"
         dbaccess.dropView(cursor, table+"_diff_"+field_distinct+"_"+target+"_annons_"+interval)
         dbaccess.createView(cursor, view=table+"_diff_"+field_distinct+"_"+target+"_annons_"+\
         interval, columns=target+", diff_"+field_distinct+", "+self.type_interval_columns[interval],\
         query="SELECT "+target+", COUNT(DISTINCT "+field_distinct+") AS diff_"+field_distinct+", "+\
         self.type_interval_select[interval]+" FROM "+table+" WHERE author=0 GROUP BY "+target+", "+\
         self.type_interval_group[interval]+" ORDER BY diff_"+field_distinct)
     else:
         print "You chose an unsupported time interval.\n"
         print "Please choose one of the following [days, weeks, moths, quarters, years]\n"
Ejemplo n.º 12
0
    def __total_rev_diff(self, cursor, table, target="author"):

        ##	target=author Total number of different pages per author
        ##	target=page_id Total number of different authors per page
        if target == "author":
            field_distinct = "page_id"
        elif target == "page_id":
            field_distinct = "author"

        dbaccess.dropView(
            cursor,
            table + "_diff_" + field_distinct + "_" + target + "_logged")
        dbaccess.createView(cursor, view=table+"_diff_"+field_distinct+"_"+target+"_logged",\
        columns=target+", diff_"+field_distinct, query="SELECT "+target+", COUNT(DISTINCT "+\
        field_distinct+") AS diff_"+field_distinct+" FROM "+table+" WHERE author!=0 GROUP BY "+\
        target+" ORDER BY diff_"+field_distinct)
        ##        Total number of different authors per page (annons included)
        if target == "page_id":
            dbaccess.dropView(
                cursor,
                table + "_diff_" + field_distinct + "_" + target + "_all")
            dbaccess.createView(cursor, view=table+"_diff_"+field_distinct+"_"+target+"_all",\
            columns=target+", diff_"+field_distinct, query="SELECT "+target+", COUNT(DISTINCT "+\
            field_distinct+") AS diff_"+field_distinct+" FROM "+table+" GROUP BY "+\
            target+" ORDER BY diff_"+field_distinct)

    ##        target=author Total number of different pages per annon author
    ##        target=page_id Total number of different annons per page
        if target == "author":
            target = "author_text"
        dbaccess.dropView(
            cursor,
            table + "_diff_" + field_distinct + "_" + target + "_annons")
        dbaccess.createView(cursor, view=table+"_diff_"+field_distinct+"_"+target+"_annons",\
        columns=target+", diff_"+field_distinct, query="SELECT "+target+", COUNT(DISTINCT "+\
        field_distinct+") AS diff_"+field_distinct+" FROM "+table+" WHERE author=0 GROUP BY "+\
        target+" ORDER BY diff_"+field_distinct)
Ejemplo n.º 13
0
    def UserNumContribsGroup(self, cursor):
        """
        A class to plot comparative graphics with contributions from 
        different groups
        """
        ###Reproduction of the article Power of the few...
        ##        Admins and bots IDs can be retrieved from DB as subselects in the where clause
        ##########################
        ##Drop bots contribs from DB source view
        ##########################
        ##CREATE VIEW FOR PERIODS FROM 0 IN MONTHS
        minYear = dbaccess.query_SQL(
            cursor, select="MIN(year)", tables="stats_Contrib_NoAnnons_months_author_" + self.language
        )

        minMonth = dbaccess.query_SQL(
            cursor,
            select="MIN(month)",
            tables="stats_Contrib_NoAnnons_months_author_" + self.language,
            where="year=" + str(int(minYear[0][0])),
        )

        dbaccess.createView(
            cursor,
            view="contribs_period_author_" + self.language,
            columns="period, author, contribs",
            query="SELECT ((year*12)+month-("
            + str(int(minYear[0][0]))
            + "*12)-"
            + str(int(minMonth[0][0]))
            + ") as period, author, theCount FROM "
            + "stats_Contrib_NoAnnons_months_author_"
            + self.language
            + " WHERE author NOT IN "
            + "(SELECT DISTINCT(ug_user) FROM user_groups WHERE ug_group='bot') ORDER BY period",
        )

        ##        Retrieve number of revision made by admins per month
        revsAdminsPerMonth = dbaccess.query_SQL(
            cursor,
            select="period, SUM(contribs)",
            tables="contribs_period_author_" + self.language,
            where="author IN (SELECT ug_user FROM user_groups where ug_group='sysop')",
            group="period",
            order="period",
        )
        ##        Plot FIG 2
        self.simpleGraph.createGraphic(
            "revs_admins_per_month",
            (revsAdminsPerMonth,),
            xlabst="Months",
            ylabst="Revisions",
            mainTitle="Revisions per month for admins " + self.language,
            graphType=self.graphType,
            log=False,
        )

        contribsMonth = dbaccess.query_SQL(
            cursor,
            select="period, SUM(contribs)",
            tables="contribs_period_author_" + self.language,
            group="period",
            order="period",
        )

        ##        divide element by element
        ##        Supposedly, there is at least one rev per month made by an admin
        percContribsAdmins = []
        for totAdminContrib in revsAdminsPerMonth:
            for totContrib in contribsMonth:
                if totAdminContrib[0] == totContrib[0]:
                    ##            append (period, adminsContribs/totContribs)
                    perc = float(totAdminContrib[1]) / float(totContrib[1])
                    percContribsAdmins.append((totAdminContrib[0], perc * 100))
                    break
        ##        Plot FIG 1 % of total revs per month made by admins
        self.simpleGraph.createGraphic(
            "perc_revs_admins_per_month",
            (percContribsAdmins,),
            xlabst="Months",
            ylabst="% revisions",
            mainTitle="% of total revisions per month made by admins " + self.language,
            graphType=self.graphType,
            log=False,
        )

        ##    FIG 4 TOTAL EDITS MADE BY USERS WITH DIFFERENT EDIT LEVELS
        ##    CREATE WHERE CLAUSES FOR CLUSTER OF USERS IDENTIFIED BY CONTRIBUTIONS LEVEL
        ##    5 LEVELS: <100, 100-1K, 1K-5K, 5K-10K, >10K
        usersLevel1 = (
            "author IN (SELECT DISTINCT(author) FROM stats_Contrib_NoAnnons_author_"
            + self.language
            + " WHERE theCount<=100 AND author NOT IN "
            + "(SELECT DISTINCT(ug_user) FROM user_groups WHERE ug_group='bot'))"
        )
        usersLevel2 = (
            "author IN (SELECT DISTINCT(author) FROM stats_Contrib_NoAnnons_author_"
            + self.language
            + " WHERE theCount BETWEEN 101 AND 1000 AND author NOT IN"
            + "(SELECT DISTINCT(ug_user) FROM user_groups WHERE ug_group='bot'))"
        )
        usersLevel3 = (
            "author IN (SELECT DISTINCT(author) FROM stats_Contrib_NoAnnons_author_"
            + self.language
            + " WHERE theCount BETWEEN 1001 AND 5000 AND author NOT IN "
            + "(SELECT DISTINCT(ug_user) FROM user_groups WHERE ug_group='bot'))"
        )
        usersLevel4 = (
            "author IN (SELECT DISTINCT(author) FROM stats_Contrib_NoAnnons_author_"
            + self.language
            + " WHERE theCount BETWEEN 5001 AND 10000 AND author NOT IN "
            + "(SELECT DISTINCT(ug_user) FROM user_groups WHERE ug_group='bot'))"
        )
        usersLevel5 = (
            "author IN (SELECT DISTINCT(author) FROM stats_Contrib_NoAnnons_author_"
            + self.language
            + " WHERE theCount>10000 AND author NOT IN "
            + "(SELECT DISTINCT(ug_user) FROM user_groups WHERE ug_group='bot'))"
        )

        ##        Some vars used in for iterations
        levels = (usersLevel1, usersLevel2, usersLevel3, usersLevel4, usersLevel5)
        listContribsLevel = []
        listAvgContribsLevel = []
        listPercContribsLevel = []
        listUsersLevel = []
        listPercUsersLevel = []

        ##        Retrieve tot num of users per month
        usersMonth = dbaccess.query_SQL(
            cursor,
            select="period, COUNT(DISTINCT(author))",
            tables="contribs_period_author_" + self.language,
            group="period",
            order="period",
        )

        for level in levels:
            ##            Retrieve contribs per month for this level
            contribsLevelMonth = dbaccess.query_SQL(
                cursor,
                select="period, SUM(contribs)",
                tables="contribs_period_author_" + self.language,
                where=level,
                group="period",
                order="period",
            )
            listContribsLevel.append(contribsLevelMonth)

            percContribsLevel = []
            ##            Append (period, contribsLevel/totContribs) checking periods correspondence
            for totLevelContrib in contribsLevelMonth:
                for totContrib in contribsMonth:
                    if totLevelContrib[0] == totContrib[0]:
                        perc = float(totLevelContrib[1]) / float(totContrib[1])
                        percContribsLevel.append((totLevelContrib[0], perc * 100))
                        break
            listPercContribsLevel.append(percContribsLevel)

            ##            Retrieve number of users per level per month
            usersLevelMonth = dbaccess.query_SQL(
                cursor,
                select="period, COUNT(DISTINCT(author))",
                tables="contribs_period_author_" + self.language,
                where=level,
                group="period",
                order="period",
            )
            ##Append to the list of users per level per month
            listUsersLevel.append(usersLevelMonth)

            avgUsersLevel = []
            ##            Retrieve avg number of revs per user in each group, per month
            for contribs in contribsLevelMonth:
                for totUsers in usersLevelMonth:
                    if contribs[0] == totUsers[0]:
                        avg = float(contribs[1]) / float(totUsers[1])
                        avgUsersLevel.append((contribs[0], avg))
            listAvgContribsLevel.append(avgUsersLevel)

            percUsersLevel = []
            ##            Append (period, usersLevel/totUsers)
            for users, totUsers in zip(usersLevelMonth, usersMonth):
                for totUsers in usersMonth:
                    if users[0] == totUsers[0]:
                        perc = float(users[1]) / float(totUsers[1])
                        percUsersLevel.append((users[0], perc * 100))
                        break
            listPercUsersLevel.append(percUsersLevel)

        ##        2D graph for FIG 4
        self.multiGraph.createGraphic(
            "perc_revs_per_userlevel_month",
            listPercContribsLevel,
            xlabst="months",
            ylabst="% revisions",
            mainTitle="% of total revs per user level per month",
            graphType=self.graphType,
            format=[],
            log=False,
        )

        ##        Plot 2D multi graph (FIG 5)
        self.multiGraph.createGraphic(
            "revs_per_userlevel_month",
            listContribsLevel,
            xlabst="months",
            ylabst="revisions",
            mainTitle="Total revisions per user level per month",
            graphType=self.graphType,
            format=[],
            log=True,
        )

        ##    FIG 6 AVERAGE NUMBER OF EDITS PER USER PER MONTH FOR EACH LEVEL
        self.multiGraph.createGraphic(
            "avg_revs_per_userlevel_month",
            listAvgContribsLevel,
            xlabst="months",
            ylabst="avg. revisions",
            mainTitle="Avg revisions per user level per month",
            graphType=self.graphType,
            format=[],
            log=True,
        )

        ##        FIG 7 POPULATION GROWTH FOR EACH USER GROUP
        self.multiGraph.createGraphic(
            "users_per_level_month",
            listUsersLevel,
            xlabst="months",
            ylabst="log(num users)",
            mainTitle="Growth of each user group per month",
            graphType=self.graphType,
            format=[],
            log=True,
        )
        ##        FIG 8 % OF TOTAL POPULATION OF EACH USER GROUP
        self.multiGraph.createGraphic(
            "perc_users_per_level_month",
            listPercUsersLevel,
            xlabst="months",
            ylabst="% users",
            mainTitle="% of users in each user group per month",
            graphType=self.graphType,
            format=[],
            log=False,
        )

        """
Ejemplo n.º 14
0
 def __gral_stats(self,cursor, table):
     ##BEFORE CALLING THIS METHOD, YOU HAVE TO CALL __content_evolution
     ##    Total num of pages with at least one edit in that month, total number of contribs, 
     ##    total num of users who made at least 1 edit in that month (alive_users)
     #  IT IS MANDATORY TO CALL __content_evolution BEFORE CALLING THIS METHOD
     ##  Total num of pages with at least one edit in that month, total number of contribs, 
     ##  total num of users who made at least 1 edit in that month (alive_users)
     dbaccess.dropView(cursor, table+"_overall_statistics1_months")
     dbaccess.createView(cursor, view=table+"_overall_statistics1_months",\
     columns="month, year, page_count, tot_contribs, alive_users",\
     query="SELECT MONTH(rev_timestamp) AS month, YEAR(rev_timestamp) AS year, COUNT(DISTINCT page_id),"+\
     " COUNT(DISTINCT rev_id), COUNT(DISTINCT author) FROM "+table+" GROUP BY year, month ORDER BY "+\
     " year, month")
     
     ####################################
     ## Parameters from Wikistats by Erik Zachte
     ####################################
     ## Wikipedians: contributors
     ## Wikipedians who edited at least 10 times since they arrived
     ## Warning!! This view does not deal with dead wikipedians, those who did not
     ## came back in subsequent months
     dbaccess.dropView(cursor, table+"_author_contrib_till_month")
     dbaccess.createView(cursor, view=table+"_author_contrib_till_month",\
     columns="author, sum_contribs, bmonth, byear",\
     query="SELECT author, SUM(tot_revisions), b.month, b.year"+\
     " FROM "+table+"_revs_author_logged_months"+" AS a, "+table+"_list_months"+\
     " AS b WHERE(a.month<=b.month) AND (a.year<=b.year)"+\
     " GROUP BY b.year, b.month, author")
     
     dbaccess.dropView(cursor, table+"_contributors_evol_months")
     dbaccess.createView(cursor, view=table+"_contributors_evol_months",\
     columns="month, year, contributors_sum",query="SELECT bmonth, byear,"+\
     " COUNT(DISTINCT(author)) FROM "+table+"_author_contrib_till_month "+\
     " WHERE sum_contribs>=10 GROUP BY byear, bmonth ORDER BY byear, bmonth")
     
     ## Wikipedians: active wikipedians
     # Wikipedians who contributed 5 times or more in this month
     dbaccess.dropView(cursor, table+"_active_authors_months")
     dbaccess.createView(cursor, view=table+"_active_authors_months",\
     columns="month, year, active_authors",\
     query="SELECT month, year, COUNT(DISTINCT(author)) FROM "+table+\
     "_revs_author_logged_months WHERE tot_revisions>=5 GROUP BY year, month")
     
     ## Wikipedians: very active wikipedians
     # Wikipedians who contributed 100 times or more in this month
     dbaccess.dropView(cursor, table+"_very_active_authors_months")
     dbaccess.createView(cursor, view=table+"_very_active_authors_months",\
     columns="month, year, active_authors",\
     query="SELECT month, year, COUNT(DISTINCT(author)) FROM "+table+\
     "_revs_author_logged_months WHERE tot_revisions>=100 GROUP BY year, month")
     
     ## Articles: Total number of pages (official count and alternative count)
     ## Offical count --> Articles that contain at least one internal link
     ## Alternative count --> Articles that contain at least one internal link and 200 characters readable text,
     ## disregarding wiki- and html codes, hidden links, etc.; also headers do not count
     ## (other columns of wikistats are based on the official count method)
     # TODO: jfelipe- Think about implementing strip of wiki text and special HTML codes
     # But it seems not to be very straightforward
     # SKIPPING STRIPPED VERSIONS OF ARTICLES' CONTENTS BY THE MOMENT
     
     ## IMPORTANT NOTE!!
     ## WIkiXRay will compute tables for both official articles and the complete set of articles
     ## Measurements regarding official articles will have a page_off_ suffix
     # OFFICIAL ARTICLES IDENTIFICATION NOT YET IMPLEMENTED
     
     
     ## Articles: new articles per day in current month (MEAN)
     ## new articles per day in current month (MEAN)
     
     
     
     ## Articles: edits per article; per month
     # Mean number of revisions per article; per month
     dbaccess.dropView(cursor, table+"_revs_per_page_id_months")
     dbaccess.createView(cursor, view=table+"_revs_per_page_id_months",\
     columns="month, year, revs_per_article", query="SELECT month, year,"+\
     " (SUM(tot_revisions)/COUNT(DISTINCT(page_id))) FROM "+table+\
     "_revs_page_id_logged_months GROUP BY year, month"+\
     " ORDER BY year, month")
     
     ## Articles: bytes per article; per month
     # Mean size of article in bytes; per month
     dbaccess.dropView(cursor, table+"_bytes_per_article_months")
     dbaccess.createView(cursor, view=table+"_bytes_per_article_months",\
     columns="month, year, bytes_per_article", query="SELECT a.month, a.year,"+\
     " (b.page_len_sum/a.page_num) FROM "+table+"_page_num_evol_months AS a, "+table+\
     "_page_len_evol_months AS b"+\
     " WHERE (a.month=b.month) AND (a.year=b.year) ORDER BY a.year, a.month")
     
     ## Articles: articles over 0.5 Kb (%); per month
     # Percentage of articles with at least 0.5 Kb readable text; per month
     ## BY THE MOMENT, WE DON'T PRECISELY IDENTIFY READABLE TEXT
     dbaccess.dropView(cursor, table+"_pages_over_05k_months")
     dbaccess.createView(cursor, view=table+"_pages_over_05k_months",
     columns="month, year, page_perc", query="SELECT b.month, b.year,"+\
     " (COUNT(DISTINCT(a.page_id))/b.page_num) FROM "+table+"_page_len_till_month AS a, "+\
     table+"_page_num_evol_months AS b WHERE (a.bmonth=b.month) AND (a.Byear=b.year) GROUP BY"+\
     " b.year, b.month, a.page_id ORDER BY b.year, b.month")
     
     ## Articles: articles over 2 Kb (%); per month
     # Percentage of articles with at least 2 Kb readable text; per month
     ## BY THE MOMENT, WE DON'T PRECISELY IDENTIFY READABLE TEXT, SO WE COUNT RAW CONTENTS
     dbaccess.dropView(cursor, table+"_pages_over_2k_months")
     dbaccess.createView(cursor, view=table+"_pages_over_2k_months",
     columns="month, year, page_perc", query="SELECT b.month, b.year,"+\
     " (COUNT(DISTINCT(a.page_id))/b.page_num) FROM "+table+"_page_len_till_month AS a, "+\
     table+"_page_num_evol_months AS b WHERE (a.bmonth=b.month) AND (a.byear=b.year) GROUP BY"+\
     " b.year, b.month, a.page_id ORDER BY b.year, b.month")
     
     ## Database: edits per month
     # Edits in past month (incl. redirects, incl. unregistered contributors, incl. bots)
     # SEE: table _overall_statistics1_months previously generated in this method
     ## Database: database size
     # Combined size of all articles (incl. redirects)
     # SEE: table _page_len_evol_months previously generated in method __content_evolution
     
     ## Database: words
     # Total number of words (excl. redirects, html/wiki codes and hidden links)
     #TODO:
     ## We are still not excluding links or tags so far
     
     
     ## Links: Internal links
     # Total number of internal links (excl. redirects, stubs and link lists)
     #TODO:
     ## To implement when we were able to identify links
     
     
     ## Links: Links to other Wikipedias
     # Total number of links to other Wikipedias
     #TODO:
     ## To implement when we were able to identify links
     
     ## Links: images
     # Total number of images presented
     #TODO:
     ## To implement when we were able to identify links
     
     ## Links: external links
     # Total number of links to other sites
     #TODO:
     ## To implement when we were able to identify links
     
     
     ## Links: redirects
     # Total number of redirects
     ## This is correctly implemented  at present time
     # Already generated
     # Just retrieve _page_num_evol_months or any other adhoc view for 
     # the special purpose namespace redirects
     
     ##    Total size of contribs; per month
     dbaccess.dropView(cursor, table+"_tot_contribs_len_months")
     dbaccess.createView(cursor, view=table+"_tot_contrib_len_evol_months",\
     columns="month, year, tot_contribs_len", query="SELECT  month,"+\
     " year, SUM(sum_contrib_len) FROM "+table+"_contrib_len_evol_months GROUP BY year, month")
     
     ##Size of pages and number of different authors who have edited them
     dbaccess.dropView(cursor, table+"_stats_pagelen_difauthors")
     dbaccess.createView(cursor, view=table+"_stats_pagelen_difauthors",\
     columns="page_id, page_len, diff_authors", query="SELECT p.page_id, p.page_len, "+\
     "t.diff_author FROM page as p, "+table+"_diff_author_page_id_logged"+\
     " AS t WHERE p.page_id=t.page_id")
Ejemplo n.º 15
0
    def __content_evolution(self,cursor, table):
    ##    Create some views to make data retrieving and graphics depicting easier
    ##    Evolution in time of the page len at the beginning of every month/quarter
        dbaccess.dropView(cursor, table+"_page_temp_months")
        dbaccess.createView(cursor, view=table+"_page_temp_months",\
        columns="page_id, rev_id, rev_len, month, year", query="SELECT page_id, rev_id, rev_len,"+\
        " MONTH(rev_timestamp), YEAR(rev_timestamp) FROM "+table)
        
##        dbaccess.dropView(cursor, table+"_page_temp_quarters")
##        dbaccess.createView(cursor, view=table+"_page_temp_quarters",\
##        columns="page_id, rev_id, rev_len, quarter, year", query="SELECT page_id, rev_id, rev_len,"+\
##        " QUARTER(rev_timestamp), YEAR(rev_timestamp) FROM "+table)
        
        dbaccess.dropView(cursor, table+"_page_maxrev_till_month")
        dbaccess.createView(cursor, view=table+"_page_maxrev_till_month",\
        columns="page_id, max_rev_id, month, year",\
        query="SELECT page_id, max(rev_id), b.month, b.year"+\
        " FROM "+table+"_page_temp_months"+" AS a, "+table+"_list_months"+\
        " AS b WHERE(a.month<=b.month) AND (a.year<=b.year)"+\
        " GROUP BY b.year, b.month, page_id")
        
        dbaccess.dropView(cursor, table+"_page_len_till_month")
        dbaccess.createView(cursor, view=table+"_page_len_till_month",\
        columns="page_id, max_rev_id, rev_len, amonth, ayear, bmonth, byear",\
        query="SELECT b.page_id, b.max_rev_id, a.rev_len, a.month, a.year, b.month, b.year"+\
        " FROM "+table+"_page_temp_months"+" AS a, "+table+"_page_maxrev_till_month"+\
        " AS b WHERE (a.rev_id=b.max_rev_id) ORDER BY b.year, b.month, page_id")
        
        dbaccess.dropView(cursor, table+"_page_len_evol_months")
        dbaccess.createView(cursor, view=table+"_page_len_evol_months",\
        columns="month, year, page_len_sum",query="SELECT bmonth, byear, SUM(rev_len) FROM "+\
        table+"_page_len_till_month GROUP BY byear, bmonth ORDER BY byear, bmonth")
        
        dbaccess.dropView(cursor, table+"_page_num_evol_months")
        dbaccess.createView(cursor, view=table+"_page_num_evol_months",\
        columns="month, year, page_num",\
        query="SELECT b.month, b.year, COUNT(DISTINCT(page_id))"+\
        " FROM "+table+"_page_temp_months"+" AS a, "+table+"_list_months"+\
        " AS b WHERE(a.month<=b.month) AND (a.year<=b.year)"+\
        " GROUP BY b.year, b.month")
        
##        dbaccess.dropView(cursor, table+"_page_maxrev_till_quarter")
##        dbaccess.createView(cursor, view=table+"_page_maxrev_till_quarter",\
##        columns="page_id, max_rev_id, quarter, year",\
##        query="SELECT page_id, max(rev_id), b.quarter, b.year"+\
##        " FROM "+table+"_page_temp_quarters"+" AS a, "+table+"_list_quarters"+\
##        " AS b WHERE(a.quarter<=b.quarter) AND (a.year<=b.year)"+\
##        " GROUP BY b.year, b.month, page_id")
        
##        dbaccess.dropView(cursor, table+"_page_len_till_quarter")
##        dbaccess.createView(cursor, view=table+"_page_len_till_quarter",\
##        columns="page_id, max_rev_id, rev_len, aquarter, ayear, bquarter, byear",\
##        query="SELECT b.page_id, b.max_rev_id, a.rev_len, a.quarter, a.year, b.quarter, b.year"+\
##        " FROM "+table+"_page_temp_quarters"+" AS a, "+table+"_page_maxrev_till_quarter"+\
##        " AS b WHERE (a.rev_id=b.max_rev_id) ORDER BY b.year, b.quarter, page_id")
##        
##        dbaccess.dropView(cursor, table+"_page_len_evol_quarters")
##        dbaccess.createView(cursor, view=table+"_page_len_evol_quarters",\
##        columns="quarter, year, page_len_sum",query="SELECT bquarter, byear, SUM(rev_len) FROM "+\
##        table+"_page_len_till_quarter GROUP BY byear, bquarter ORDER BY byear, bmonth")
        
    ##    Now for revisions. We have to deal with the first revision of each tree.
    ##    In the first revision of every page, rev_parent==NULL. 
    ##    Later, UNION that group with the first revisions group //Must prove it against testbed dump
    
    #    Length in bytes of the current rev from the previous one and time interval between edits (both per page)
    #    Note: we use a precision of seconds for timestampdiff because that is the smallest interval stored in the dump's timestamps
        dbaccess.dropView(cursor, table+"_contrib_len_interval")
        dbaccess.createView(cursor, view=table+"_contrib_len_interval",\
        columns="rev_id, page_id, author, contrib_len, timestamp, interval",\
        query="(SELECT cur.rev_id, cur.page_id, cur.author, (cur.rev_len-prev.rev_len) AS contrib_len, "+\
        " cur.rev_timestamp, TIMESTAMPDIFF(SECOND,prev.rev_timestamp,cur.rev_timestamp) as interval"+\
        " FROM "+table+" AS cur, "+table+\
        " AS prev WHERE cur.rev_parent_id IS NOT NULL AND cur.rev_parent_id=prev.rev_id)"+\
        " UNION (SELECT rev_id, page_id, author, rev_len, rev_timestamp FROM "+table+\
        " WHERE rev_parent_id IS NULL)")

    #    Sum of the length of the contributions for every author; per month
        dbaccess.dropView(cursor, table+"_contrib_len_evol_months")
        dbaccess.createView(cursor, view=table+"_contrib_len_evol_months",\
        columns="author, sum_contrib_len, month, year",\
        query="SELECT author, SUM(contrib_len), MONTH(timestamp) as month,"+\
        " YEAR(timestamp) as year FROM "+table+\
        "_contrib_len WHERE author !=0 GROUP BY author, year, month ORDER BY author, year, month")
    #    The same per quarter
        dbaccess.dropView(cursor, table+"_contrib_len_evol_quarters")
        dbaccess.createView(cursor, view=table+"contrib_len_evol_quarters",\
        columns="author, sum_contrib_len, quarter, year",\
        query="SELECT author, SUM(contrib_len), QUARTER(timestamp) as quarter,"+\
        " YEAR(timestamp) as year FROM "+table+\
        "_contrib_len WHERE author!=0 GROUP BY author, year, quarter ORDER BY author, year, quarter")
Ejemplo n.º 16
0
    def __content_evolution(self, cursor, table):
        ##    Create some views to make data retrieving and graphics depicting easier
        ##    Evolution in time of the page len at the beginning of every month/quarter
        dbaccess.dropView(cursor, table + "_page_temp_months")
        dbaccess.createView(cursor, view=table+"_page_temp_months",\
        columns="page_id, rev_id, rev_len, month, year", query="SELECT page_id, rev_id, rev_len,"+\
        " MONTH(rev_timestamp), YEAR(rev_timestamp) FROM "+table)

        ##        dbaccess.dropView(cursor, table+"_page_temp_quarters")
        ##        dbaccess.createView(cursor, view=table+"_page_temp_quarters",\
        ##        columns="page_id, rev_id, rev_len, quarter, year", query="SELECT page_id, rev_id, rev_len,"+\
        ##        " QUARTER(rev_timestamp), YEAR(rev_timestamp) FROM "+table)

        dbaccess.dropView(cursor, table + "_page_maxrev_till_month")
        dbaccess.createView(cursor, view=table+"_page_maxrev_till_month",\
        columns="page_id, max_rev_id, month, year",\
        query="SELECT page_id, max(rev_id), b.month, b.year"+\
        " FROM "+table+"_page_temp_months"+" AS a, "+table+"_list_months"+\
        " AS b WHERE(a.month<=b.month) AND (a.year<=b.year)"+\
        " GROUP BY b.year, b.month, page_id")

        dbaccess.dropView(cursor, table + "_page_len_till_month")
        dbaccess.createView(cursor, view=table+"_page_len_till_month",\
        columns="page_id, max_rev_id, rev_len, amonth, ayear, bmonth, byear",\
        query="SELECT b.page_id, b.max_rev_id, a.rev_len, a.month, a.year, b.month, b.year"+\
        " FROM "+table+"_page_temp_months"+" AS a, "+table+"_page_maxrev_till_month"+\
        " AS b WHERE (a.rev_id=b.max_rev_id) ORDER BY b.year, b.month, page_id")

        dbaccess.dropView(cursor, table + "_page_len_evol_months")
        dbaccess.createView(cursor, view=table+"_page_len_evol_months",\
        columns="month, year, page_len_sum",query="SELECT bmonth, byear, SUM(rev_len) FROM "+\
        table+"_page_len_till_month GROUP BY byear, bmonth ORDER BY byear, bmonth")

        dbaccess.dropView(cursor, table + "_page_num_evol_months")
        dbaccess.createView(cursor, view=table+"_page_num_evol_months",\
        columns="month, year, page_num",\
        query="SELECT b.month, b.year, COUNT(DISTINCT(page_id))"+\
        " FROM "+table+"_page_temp_months"+" AS a, "+table+"_list_months"+\
        " AS b WHERE(a.month<=b.month) AND (a.year<=b.year)"+\
        " GROUP BY b.year, b.month")

        ##        dbaccess.dropView(cursor, table+"_page_maxrev_till_quarter")
        ##        dbaccess.createView(cursor, view=table+"_page_maxrev_till_quarter",\
        ##        columns="page_id, max_rev_id, quarter, year",\
        ##        query="SELECT page_id, max(rev_id), b.quarter, b.year"+\
        ##        " FROM "+table+"_page_temp_quarters"+" AS a, "+table+"_list_quarters"+\
        ##        " AS b WHERE(a.quarter<=b.quarter) AND (a.year<=b.year)"+\
        ##        " GROUP BY b.year, b.month, page_id")

        ##        dbaccess.dropView(cursor, table+"_page_len_till_quarter")
        ##        dbaccess.createView(cursor, view=table+"_page_len_till_quarter",\
        ##        columns="page_id, max_rev_id, rev_len, aquarter, ayear, bquarter, byear",\
        ##        query="SELECT b.page_id, b.max_rev_id, a.rev_len, a.quarter, a.year, b.quarter, b.year"+\
        ##        " FROM "+table+"_page_temp_quarters"+" AS a, "+table+"_page_maxrev_till_quarter"+\
        ##        " AS b WHERE (a.rev_id=b.max_rev_id) ORDER BY b.year, b.quarter, page_id")
        ##
        ##        dbaccess.dropView(cursor, table+"_page_len_evol_quarters")
        ##        dbaccess.createView(cursor, view=table+"_page_len_evol_quarters",\
        ##        columns="quarter, year, page_len_sum",query="SELECT bquarter, byear, SUM(rev_len) FROM "+\
        ##        table+"_page_len_till_quarter GROUP BY byear, bquarter ORDER BY byear, bmonth")

        ##    Now for revisions. We have to deal with the first revision of each tree.
        ##    In the first revision of every page, rev_parent==NULL.
        ##    Later, UNION that group with the first revisions group //Must prove it against testbed dump

        #    Length in bytes of the current rev from the previous one and time interval between edits (both per page)
        #    Note: we use a precision of seconds for timestampdiff because that is the smallest interval stored in the dump's timestamps
        dbaccess.dropView(cursor, table + "_contrib_len_interval")
        dbaccess.createView(cursor, view=table+"_contrib_len_interval",\
        columns="rev_id, page_id, author, contrib_len, timestamp, interval",\
        query="(SELECT cur.rev_id, cur.page_id, cur.author, (cur.rev_len-prev.rev_len) AS contrib_len, "+\
        " cur.rev_timestamp, TIMESTAMPDIFF(SECOND,prev.rev_timestamp,cur.rev_timestamp) as interval"+\
        " FROM "+table+" AS cur, "+table+\
        " AS prev WHERE cur.rev_parent_id IS NOT NULL AND cur.rev_parent_id=prev.rev_id)"+\
        " UNION (SELECT rev_id, page_id, author, rev_len, rev_timestamp FROM "+table+\
        " WHERE rev_parent_id IS NULL)")

        #    Sum of the length of the contributions for every author; per month
        dbaccess.dropView(cursor, table + "_contrib_len_evol_months")
        dbaccess.createView(cursor, view=table+"_contrib_len_evol_months",\
        columns="author, sum_contrib_len, month, year",\
        query="SELECT author, SUM(contrib_len), MONTH(timestamp) as month,"+\
        " YEAR(timestamp) as year FROM "+table+\
        "_contrib_len WHERE author !=0 GROUP BY author, year, month ORDER BY author, year, month")
        #    The same per quarter
        dbaccess.dropView(cursor, table + "_contrib_len_evol_quarters")
        dbaccess.createView(cursor, view=table+"contrib_len_evol_quarters",\
        columns="author, sum_contrib_len, quarter, year",\
        query="SELECT author, SUM(contrib_len), QUARTER(timestamp) as quarter,"+\
        " YEAR(timestamp) as year FROM "+table+\
        "_contrib_len WHERE author!=0 GROUP BY author, year, quarter ORDER BY author, year, quarter")
Ejemplo n.º 17
0
    def __gral_stats(self, cursor, table):
        ##BEFORE CALLING THIS METHOD, YOU HAVE TO CALL __content_evolution
        ##    Total num of pages with at least one edit in that month, total number of contribs,
        ##    total num of users who made at least 1 edit in that month (alive_users)
        #  IT IS MANDATORY TO CALL __content_evolution BEFORE CALLING THIS METHOD
        ##  Total num of pages with at least one edit in that month, total number of contribs,
        ##  total num of users who made at least 1 edit in that month (alive_users)
        dbaccess.dropView(cursor, table + "_overall_statistics1_months")
        dbaccess.createView(cursor, view=table+"_overall_statistics1_months",\
        columns="month, year, page_count, tot_contribs, alive_users",\
        query="SELECT MONTH(rev_timestamp) AS month, YEAR(rev_timestamp) AS year, COUNT(DISTINCT page_id),"+\
        " COUNT(DISTINCT rev_id), COUNT(DISTINCT author) FROM "+table+" GROUP BY year, month ORDER BY "+\
        " year, month")

        ####################################
        ## Parameters from Wikistats by Erik Zachte
        ####################################
        ## Wikipedians: contributors
        ## Wikipedians who edited at least 10 times since they arrived
        ## Warning!! This view does not deal with dead wikipedians, those who did not
        ## came back in subsequent months
        dbaccess.dropView(cursor, table + "_author_contrib_till_month")
        dbaccess.createView(cursor, view=table+"_author_contrib_till_month",\
        columns="author, sum_contribs, bmonth, byear",\
        query="SELECT author, SUM(tot_revisions), b.month, b.year"+\
        " FROM "+table+"_revs_author_logged_months"+" AS a, "+table+"_list_months"+\
        " AS b WHERE(a.month<=b.month) AND (a.year<=b.year)"+\
        " GROUP BY b.year, b.month, author")

        dbaccess.dropView(cursor, table + "_contributors_evol_months")
        dbaccess.createView(cursor, view=table+"_contributors_evol_months",\
        columns="month, year, contributors_sum",query="SELECT bmonth, byear,"+\
        " COUNT(DISTINCT(author)) FROM "+table+"_author_contrib_till_month "+\
        " WHERE sum_contribs>=10 GROUP BY byear, bmonth ORDER BY byear, bmonth")

        ## Wikipedians: active wikipedians
        # Wikipedians who contributed 5 times or more in this month
        dbaccess.dropView(cursor, table + "_active_authors_months")
        dbaccess.createView(cursor, view=table+"_active_authors_months",\
        columns="month, year, active_authors",\
        query="SELECT month, year, COUNT(DISTINCT(author)) FROM "+table+\
        "_revs_author_logged_months WHERE tot_revisions>=5 GROUP BY year, month")

        ## Wikipedians: very active wikipedians
        # Wikipedians who contributed 100 times or more in this month
        dbaccess.dropView(cursor, table + "_very_active_authors_months")
        dbaccess.createView(cursor, view=table+"_very_active_authors_months",\
        columns="month, year, active_authors",\
        query="SELECT month, year, COUNT(DISTINCT(author)) FROM "+table+\
        "_revs_author_logged_months WHERE tot_revisions>=100 GROUP BY year, month")

        ## Articles: Total number of pages (official count and alternative count)
        ## Offical count --> Articles that contain at least one internal link
        ## Alternative count --> Articles that contain at least one internal link and 200 characters readable text,
        ## disregarding wiki- and html codes, hidden links, etc.; also headers do not count
        ## (other columns of wikistats are based on the official count method)
        # TODO: jfelipe- Think about implementing strip of wiki text and special HTML codes
        # But it seems not to be very straightforward
        # SKIPPING STRIPPED VERSIONS OF ARTICLES' CONTENTS BY THE MOMENT

        ## IMPORTANT NOTE!!
        ## WIkiXRay will compute tables for both official articles and the complete set of articles
        ## Measurements regarding official articles will have a page_off_ suffix
        # OFFICIAL ARTICLES IDENTIFICATION NOT YET IMPLEMENTED

        ## Articles: new articles per day in current month (MEAN)
        ## new articles per day in current month (MEAN)

        ## Articles: edits per article; per month
        # Mean number of revisions per article; per month
        dbaccess.dropView(cursor, table + "_revs_per_page_id_months")
        dbaccess.createView(cursor, view=table+"_revs_per_page_id_months",\
        columns="month, year, revs_per_article", query="SELECT month, year,"+\
        " (SUM(tot_revisions)/COUNT(DISTINCT(page_id))) FROM "+table+\
        "_revs_page_id_logged_months GROUP BY year, month"+\
        " ORDER BY year, month")

        ## Articles: bytes per article; per month
        # Mean size of article in bytes; per month
        dbaccess.dropView(cursor, table + "_bytes_per_article_months")
        dbaccess.createView(cursor, view=table+"_bytes_per_article_months",\
        columns="month, year, bytes_per_article", query="SELECT a.month, a.year,"+\
        " (b.page_len_sum/a.page_num) FROM "+table+"_page_num_evol_months AS a, "+table+\
        "_page_len_evol_months AS b"+\
        " WHERE (a.month=b.month) AND (a.year=b.year) ORDER BY a.year, a.month")

        ## Articles: articles over 0.5 Kb (%); per month
        # Percentage of articles with at least 0.5 Kb readable text; per month
        ## BY THE MOMENT, WE DON'T PRECISELY IDENTIFY READABLE TEXT
        dbaccess.dropView(cursor, table + "_pages_over_05k_months")
        dbaccess.createView(cursor, view=table+"_pages_over_05k_months",
        columns="month, year, page_perc", query="SELECT b.month, b.year,"+\
        " (COUNT(DISTINCT(a.page_id))/b.page_num) FROM "+table+"_page_len_till_month AS a, "+\
        table+"_page_num_evol_months AS b WHERE (a.bmonth=b.month) AND (a.Byear=b.year) GROUP BY"+\
        " b.year, b.month, a.page_id ORDER BY b.year, b.month")

        ## Articles: articles over 2 Kb (%); per month
        # Percentage of articles with at least 2 Kb readable text; per month
        ## BY THE MOMENT, WE DON'T PRECISELY IDENTIFY READABLE TEXT, SO WE COUNT RAW CONTENTS
        dbaccess.dropView(cursor, table + "_pages_over_2k_months")
        dbaccess.createView(cursor, view=table+"_pages_over_2k_months",
        columns="month, year, page_perc", query="SELECT b.month, b.year,"+\
        " (COUNT(DISTINCT(a.page_id))/b.page_num) FROM "+table+"_page_len_till_month AS a, "+\
        table+"_page_num_evol_months AS b WHERE (a.bmonth=b.month) AND (a.byear=b.year) GROUP BY"+\
        " b.year, b.month, a.page_id ORDER BY b.year, b.month")

        ## Database: edits per month
        # Edits in past month (incl. redirects, incl. unregistered contributors, incl. bots)
        # SEE: table _overall_statistics1_months previously generated in this method
        ## Database: database size
        # Combined size of all articles (incl. redirects)
        # SEE: table _page_len_evol_months previously generated in method __content_evolution

        ## Database: words
        # Total number of words (excl. redirects, html/wiki codes and hidden links)
        #TODO:
        ## We are still not excluding links or tags so far

        ## Links: Internal links
        # Total number of internal links (excl. redirects, stubs and link lists)
        #TODO:
        ## To implement when we were able to identify links

        ## Links: Links to other Wikipedias
        # Total number of links to other Wikipedias
        #TODO:
        ## To implement when we were able to identify links

        ## Links: images
        # Total number of images presented
        #TODO:
        ## To implement when we were able to identify links

        ## Links: external links
        # Total number of links to other sites
        #TODO:
        ## To implement when we were able to identify links

        ## Links: redirects
        # Total number of redirects
        ## This is correctly implemented  at present time
        # Already generated
        # Just retrieve _page_num_evol_months or any other adhoc view for
        # the special purpose namespace redirects

        ##    Total size of contribs; per month
        dbaccess.dropView(cursor, table + "_tot_contribs_len_months")
        dbaccess.createView(cursor, view=table+"_tot_contrib_len_evol_months",\
        columns="month, year, tot_contribs_len", query="SELECT  month,"+\
        " year, SUM(sum_contrib_len) FROM "+table+"_contrib_len_evol_months GROUP BY year, month")

        ##Size of pages and number of different authors who have edited them
        dbaccess.dropView(cursor, table + "_stats_pagelen_difauthors")
        dbaccess.createView(cursor, view=table+"_stats_pagelen_difauthors",\
        columns="page_id, page_len, diff_authors", query="SELECT p.page_id, p.page_len, "+\
        "t.diff_author FROM page as p, "+table+"_diff_author_page_id_logged"+\
        " AS t WHERE p.page_id=t.page_id")
Ejemplo n.º 18
0
    def UserNumContribsGroup(self, cursor):
        """
        A class to plot comparative graphics with contributions from 
        different groups
        """
        ###Reproduction of the article Power of the few...
        ##        Admins and bots IDs can be retrieved from DB as subselects in the where clause
        ##########################
        ##Drop bots contribs from DB source view
        ##########################
        ##CREATE VIEW FOR PERIODS FROM 0 IN MONTHS
        minYear=dbaccess.query_SQL(cursor, select="MIN(year)",\
        tables="stats_Contrib_NoAnnons_months_author_"+self.language)

        minMonth=dbaccess.query_SQL(cursor, select="MIN(month)",\
        tables="stats_Contrib_NoAnnons_months_author_"+self.language,\
        where="year="+str(int(minYear[0][0])))

        dbaccess.createView(cursor, view="contribs_period_author_"+self.language,\
        columns="period, author, contribs",\
        query="SELECT ((year*12)+month-("+str(int(minYear[0][0]))+"*12)-"+str(int(minMonth[0][0]))\
        +") as period, author, theCount FROM "+\
        "stats_Contrib_NoAnnons_months_author_"+self.language+" WHERE author NOT IN "+\
        "(SELECT DISTINCT(ug_user) FROM user_groups WHERE ug_group='bot') ORDER BY period")

        ##        Retrieve number of revision made by admins per month
        revsAdminsPerMonth=dbaccess.query_SQL(cursor,select="period, SUM(contribs)",\
        tables="contribs_period_author_"+self.language,\
        where="author IN (SELECT ug_user FROM user_groups where ug_group='sysop')",\
        group="period",order="period")
        ##        Plot FIG 2
        self.simpleGraph.createGraphic("revs_admins_per_month", (revsAdminsPerMonth,),\
        xlabst="Months", ylabst="Revisions",mainTitle="Revisions per month for admins "+\
        self.language, graphType=self.graphType, log=False)

        contribsMonth=dbaccess.query_SQL(cursor,\
        select="period, SUM(contribs)",\
        tables="contribs_period_author_"+self.language,\
        group="period",order="period")

        ##        divide element by element
        ##        Supposedly, there is at least one rev per month made by an admin
        percContribsAdmins = []
        for totAdminContrib in revsAdminsPerMonth:
            for totContrib in contribsMonth:
                if totAdminContrib[0] == totContrib[0]:
                    ##            append (period, adminsContribs/totContribs)
                    perc = float(totAdminContrib[1]) / float(totContrib[1])
                    percContribsAdmins.append((totAdminContrib[0], perc * 100))
                    break
##        Plot FIG 1 % of total revs per month made by admins
        self.simpleGraph.createGraphic("perc_revs_admins_per_month", (percContribsAdmins,),\
        xlabst="Months", ylabst="% revisions",\
        mainTitle="% of total revisions per month made by admins "+self.language,\
        graphType=self.graphType, log=False)

        ##    FIG 4 TOTAL EDITS MADE BY USERS WITH DIFFERENT EDIT LEVELS
        ##    CREATE WHERE CLAUSES FOR CLUSTER OF USERS IDENTIFIED BY CONTRIBUTIONS LEVEL
        ##    5 LEVELS: <100, 100-1K, 1K-5K, 5K-10K, >10K
        usersLevel1="author IN (SELECT DISTINCT(author) FROM stats_Contrib_NoAnnons_author_"+\
        self.language+" WHERE theCount<=100 AND author NOT IN "+\
        "(SELECT DISTINCT(ug_user) FROM user_groups WHERE ug_group='bot'))"
        usersLevel2="author IN (SELECT DISTINCT(author) FROM stats_Contrib_NoAnnons_author_"+\
        self.language+" WHERE theCount BETWEEN 101 AND 1000 AND author NOT IN" +\
        "(SELECT DISTINCT(ug_user) FROM user_groups WHERE ug_group='bot'))"
        usersLevel3="author IN (SELECT DISTINCT(author) FROM stats_Contrib_NoAnnons_author_"+\
        self.language+" WHERE theCount BETWEEN 1001 AND 5000 AND author NOT IN "+\
        "(SELECT DISTINCT(ug_user) FROM user_groups WHERE ug_group='bot'))"
        usersLevel4="author IN (SELECT DISTINCT(author) FROM stats_Contrib_NoAnnons_author_"+\
        self.language+" WHERE theCount BETWEEN 5001 AND 10000 AND author NOT IN "+\
        "(SELECT DISTINCT(ug_user) FROM user_groups WHERE ug_group='bot'))"
        usersLevel5="author IN (SELECT DISTINCT(author) FROM stats_Contrib_NoAnnons_author_"+\
        self.language+" WHERE theCount>10000 AND author NOT IN "+\
        "(SELECT DISTINCT(ug_user) FROM user_groups WHERE ug_group='bot'))"

        ##        Some vars used in for iterations
        levels = (usersLevel1, usersLevel2, usersLevel3, usersLevel4,
                  usersLevel5)
        listContribsLevel = []
        listAvgContribsLevel = []
        listPercContribsLevel = []
        listUsersLevel = []
        listPercUsersLevel = []

        ##        Retrieve tot num of users per month
        usersMonth=dbaccess.query_SQL(cursor,\
        select="period, COUNT(DISTINCT(author))",\
        tables="contribs_period_author_"+self.language,\
        group="period",order="period")

        for level in levels:
            ##            Retrieve contribs per month for this level
            contribsLevelMonth=dbaccess.query_SQL(cursor,\
            select="period, SUM(contribs)",\
            tables="contribs_period_author_"+self.language,\
            where=level, group="period", order="period")
            listContribsLevel.append(contribsLevelMonth)

            percContribsLevel = []
            ##            Append (period, contribsLevel/totContribs) checking periods correspondence
            for totLevelContrib in contribsLevelMonth:
                for totContrib in contribsMonth:
                    if totLevelContrib[0] == totContrib[0]:
                        perc = float(totLevelContrib[1]) / float(totContrib[1])
                        percContribsLevel.append(
                            (totLevelContrib[0], perc * 100))
                        break
            listPercContribsLevel.append(percContribsLevel)

            ##            Retrieve number of users per level per month
            usersLevelMonth=dbaccess.query_SQL(cursor,\
            select="period, COUNT(DISTINCT(author))",\
            tables="contribs_period_author_"+self.language,\
            where=level, group="period",order="period")
            ##Append to the list of users per level per month
            listUsersLevel.append(usersLevelMonth)

            avgUsersLevel = []
            ##            Retrieve avg number of revs per user in each group, per month
            for contribs in contribsLevelMonth:
                for totUsers in usersLevelMonth:
                    if contribs[0] == totUsers[0]:
                        avg = float(contribs[1]) / float(totUsers[1])
                        avgUsersLevel.append((contribs[0], avg))
            listAvgContribsLevel.append(avgUsersLevel)

            percUsersLevel = []
            ##            Append (period, usersLevel/totUsers)
            for users, totUsers in zip(usersLevelMonth, usersMonth):
                for totUsers in usersMonth:
                    if users[0] == totUsers[0]:
                        perc = float(users[1]) / float(totUsers[1])
                        percUsersLevel.append((users[0], perc * 100))
                        break
            listPercUsersLevel.append(percUsersLevel)

##        2D graph for FIG 4
        self.multiGraph.createGraphic("perc_revs_per_userlevel_month",\
        listPercContribsLevel, xlabst="months", ylabst="% revisions",\
        mainTitle="% of total revs per user level per month", graphType=self.graphType,\
        format=[],log=False)

        ##        Plot 2D multi graph (FIG 5)
        self.multiGraph.createGraphic("revs_per_userlevel_month",\
        listContribsLevel, xlabst="months", ylabst="revisions",\
        mainTitle="Total revisions per user level per month", graphType=self.graphType,\
        format=[],log=True)

        ##    FIG 6 AVERAGE NUMBER OF EDITS PER USER PER MONTH FOR EACH LEVEL
        self.multiGraph.createGraphic("avg_revs_per_userlevel_month",\
        listAvgContribsLevel, xlabst="months", ylabst="avg. revisions",\
        mainTitle="Avg revisions per user level per month", graphType=self.graphType,\
        format=[],log=True)

        ##        FIG 7 POPULATION GROWTH FOR EACH USER GROUP
        self.multiGraph.createGraphic("users_per_level_month",\
        listUsersLevel, xlabst="months", ylabst="log(num users)",\
        mainTitle="Growth of each user group per month", graphType=self.graphType,\
        format=[],log=True)
        ##        FIG 8 % OF TOTAL POPULATION OF EACH USER GROUP
        self.multiGraph.createGraphic("perc_users_per_level_month",\
        listPercUsersLevel, xlabst="months", ylabst="% users",\
        mainTitle="% of users in each user group per month", graphType=self.graphType,\
        format=[],log=False)
        """
Ejemplo n.º 19
0
 def __init__(self, conf, language="furwiki"):
     """
     Creates multiple views to create a convenient interface to access quantitative data
     It also generates necessary tables and views to store intermidiate results, so that other methods
     can later store data directly. 
     """
     self.conf=conf
     self.language=language
     ##List of namespaces to analyse. We have added new special namespaces (e.g. subsets of main)
     self.nspaces=["all","ns0","articles","redirects","cur_redirects","cur_stubs","stubs","talk",\
     "pageUser", "userTalk","meta", "metaTalk", "image", "imageTalk", "mediawiki",\
     "mediawikiTalk", "template", "templateTalk", "help", "helpTalk", "category", "categoryTalk"]
     
     ##Some fancy lists to work with time intervals in some private methods following
     self.type_interval_columns={"days":"day, year", "weeks":"week, year", "months":"month, year",\
     "quarters":"quarter, year", "years":"year"}
     self.type_interval_select={"days":"DAYOFYEAR(rev_timestamp) AS day, YEAR(rev_timestamp) AS year ",\
     "weeks":"WEEK(rev_timestamp,1) AS week, YEAR(rev_timestamp) AS year ",\
     "months":"MONTH(rev_timestamp) AS month, YEAR(rev_timestamp) AS year ",\
     "quarters":"QUARTER(rev_timestamp) AS quarter, YEAR(rev_timestamp) AS year ",\
     "years":"YEAR(rev_timestamp) AS year "}
     self.type_interval_group={"days":"year, day", "weeks":"year, week", "months":"year, month",\
     "quarters":"year, quarter", "years":"year"}
     
     ##	Get new DB connection
     self.acceso = dbaccess.get_Connection("localhost", 3306, self.conf.msqlu, self.conf.msqlp,\
     "wx_"+self.language+"_"+self.conf.dumptype)
     
     ##    Delete previous versions of views
     for nspace in self.nspaces:
         dbaccess.dropView(self.acceso[1], nspace+"_"+self.language)
     
     ##    Create updated versions for views from revision table
     #View sumarizing all info for every revision (linking with info from table page)
     dbaccess.createView(self.acceso[1], view="all_"+self.language,\
     columns="rev_id, page_id, rev_len, page_ns, page_len, is_redirect, author, author_text,"+\
     " rev_timestamp, rev_parent_id",
     query="SELECT rev_id, rev_page, rev_len, page_namespace, page_len, rev_is_redirect,"+\
     " rev_user, rev_user_text, rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id")
     #View sumarizing info regarding pages in namespace=0 (including articles, stubs and redirects)
     dbaccess.createView(self.acceso[1], view="ns0_"+self.language,\
     columns="rev_id, page_id, rev_len, page_len, page_title, is_redirect, author, author_text,"+\
     " rev_timestamp, rev_parent_id",
     query="SELECT rev_id, rev_page, rev_len, page_len, page_title, page_is_redirect, rev_user,"+\
     " rev_user_text, rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id"+\
     " AND page_namespace=0")
     #View sumarizing info for articles (excluding pages that currently are redirects and stubs)
     dbaccess.createView(self.acceso[1], view="articles_"+self.language,\
     columns="rev_id, page_id, rev_len, page_len, page_title, author, author_text, rev_timestamp, "+\
     "rev_parent_id",
     query="SELECT rev_id, rev_page, rev_len, page_len, page_title, rev_user, rev_user_text,"+\
     " rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id AND page_namespace=0 AND "+\
     "page_is_redirect=0 AND page_is_stub=0")
     #View with info only for redirects (pages that were redirects when that revision was made)
     dbaccess.createView(self.acceso[1], view="redirects_"+self.language,\
     columns="rev_id, page_id, rev_len, page_len, page_title, author, author_text, rev_timestamp, "+\
     "rev_parent_id",
     query="SELECT rev_id, rev_page, rev_len, page_len, page_title, rev_user, rev_user_text, "+\
     "rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id AND "+\
     "page_namespace=0 AND rev_is_redirect=1")
     #View with info only for current redirects
     dbaccess.createView(self.acceso[1], view="cur_redirects_"+self.language,\
     columns="rev_id, page_id, rev_len, page_len, page_title, author, author_text, rev_timestamp, "+\
     "rev_parent_id",
     query="SELECT rev_id, rev_page, rev_len, page_len, page_title, rev_user, rev_user_text, "+\
     "rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id AND "+\
     "page_namespace=0 AND page_is_redirect=1")
     #View with info only for revisions of stub pages (pages that were stubs when that revision was made)
     dbaccess.createView(self.acceso[1], view="stubs_"+self.language,\
     columns="rev_id, page_id, rev_len, page_len, page_title, author, author_text, rev_timestamp,"\
     " rev_parent_id",
     query="SELECT rev_id, rev_page, rev_len, page_len, page_title, rev_user, rev_user_text, "+\
     "rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id AND"+\
     " page_namespace=0 AND rev_is_stub=1")
     #View with info only for revisions of current stub pages
     dbaccess.createView(self.acceso[1], view="cur_stubs_"+self.language,\
     columns="rev_id, page_id, rev_len, page_len, page_title, author, author_text, rev_timestamp,"\
     " rev_parent_id",
     query="SELECT rev_id, rev_page, rev_len, page_len, page_title, rev_user, rev_user_text, "+\
     "rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id AND"+\
     " page_namespace=0 AND page_is_stub=1")
     #From this point on, automatically create views for the set of pages included in the remaining namespaces in MediaWiki
     for nspace, nsnum in zip(self.nspaces[7:], range(1,16)):
         dbaccess.createView(self.acceso[1], view=nspace+"_"+self.language,\
         columns="rev_id, page_id, rev_len, page_len, page_title, author, author_text, rev_timestamp,"+\
         " rev_parent_id",
         query="SELECT rev_id, rev_page, rev_len, page_len, page_title, rev_user, rev_user_text, "+\
         "rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id AND"+\
         " page_namespace="+str(nsnum))
         
     #View sumarizing the distribution of pages among namespaces
     dbaccess.dropView(self.acceso[1], "nspaces_"+self.language)
     dbaccess.createView(self.acceso[1],view="nspaces_"+self.language, columns="namespace, pages_in_nspace",\
     query="SELECT page_namespace, COUNT(*) FROM page GROUP BY page_namespace")
 
     ##    Intermidiate views for the minimun timestamp of every page [annons, and logged users]
     ## And other useful intermediate views regarding page evolution
     for nspace in self.nspaces:
         dbaccess.dropView(self.acceso[1], nspace+"_"+self.language+\
         "_page_min_timestamp_logged")
         dbaccess.createView(self.acceso[1], view=nspace+"_"+self.language+\
         "_page_min_timestamp_logged", columns="page_id, rev_id, author, rev_timestamp",\
         query="SELECT page_id, rev_id,author, MIN(rev_timestamp) FROM "+\
         nspace+"_"+self.language+" WHERE author!=0 GROUP BY page_id")
         dbaccess.dropView(self.acceso[1], nspace+"_"+self.language+\
         "_page_min_timestamp_annons")
         dbaccess.createView(self.acceso[1], view=nspace+"_"+self.language+\
         "_page_min_timestamp_annons",\
         columns="page_id, rev_id, author_text, rev_timestamp",\
         query="SELECT page_id,rev_id,author_text, MIN(rev_timestamp) FROM "+\
         nspace+"_"+self.language+" WHERE author=0 GROUP BY page_id")
         
         dbaccess.dropView(self.acceso[1],nspace+"_"+self.language+"_list_months")
         dbaccess.createView(self.acceso[1],view=nspace+"_"+self.language+"_list_months",\
         columns="month, year",query="SELECT MONTH(rev_timestamp) as month, "+\
         "YEAR(rev_timestamp) as year"+\
         " FROM "+nspace+"_"+self.language+" GROUP BY year, month ORDER BY year, month")
         
         dbaccess.dropView(self.acceso[1],nspace+"_"+self.language+"_list_quarters")
         dbaccess.createView(self.acceso[1],view=nspace+"_"+self.language+"_list_quarters",\
         columns="quarter, year",query="SELECT QUARTER(rev_timestamp) as quarter, "+\
         "YEAR(rev_timestamp) as year FROM "+nspace+"_"+self.language+" GROUP BY year,"+\
         " quarter ORDER BY year, quarter")
     
 ##    Close DB connection
     dbaccess.close_Connection(self.acceso[0])
Ejemplo n.º 20
0
    def __init__(self, conf, language="furwiki"):
        """
        Creates multiple views to create a convenient interface to access quantitative data
        It also generates necessary tables and views to store intermidiate results, so that other methods
        can later store data directly. 
        """
        self.conf = conf
        self.language = language
        ##List of namespaces to analyse. We have added new special namespaces (e.g. subsets of main)
        self.nspaces=["all","ns0","articles","redirects","cur_redirects","cur_stubs","stubs","talk",\
        "pageUser", "userTalk","meta", "metaTalk", "image", "imageTalk", "mediawiki",\
        "mediawikiTalk", "template", "templateTalk", "help", "helpTalk", "category", "categoryTalk"]

        ##Some fancy lists to work with time intervals in some private methods following
        self.type_interval_columns={"days":"day, year", "weeks":"week, year", "months":"month, year",\
        "quarters":"quarter, year", "years":"year"}
        self.type_interval_select={"days":"DAYOFYEAR(rev_timestamp) AS day, YEAR(rev_timestamp) AS year ",\
        "weeks":"WEEK(rev_timestamp,1) AS week, YEAR(rev_timestamp) AS year ",\
        "months":"MONTH(rev_timestamp) AS month, YEAR(rev_timestamp) AS year ",\
        "quarters":"QUARTER(rev_timestamp) AS quarter, YEAR(rev_timestamp) AS year ",\
        "years":"YEAR(rev_timestamp) AS year "}
        self.type_interval_group={"days":"year, day", "weeks":"year, week", "months":"year, month",\
        "quarters":"year, quarter", "years":"year"}

        ##	Get new DB connection
        self.acceso = dbaccess.get_Connection("localhost", 3306, self.conf.msqlu, self.conf.msqlp,\
        "wx_"+self.language+"_"+self.conf.dumptype)

        ##    Delete previous versions of views
        for nspace in self.nspaces:
            dbaccess.dropView(self.acceso[1], nspace + "_" + self.language)

        ##    Create updated versions for views from revision table
        #View sumarizing all info for every revision (linking with info from table page)
        dbaccess.createView(self.acceso[1], view="all_"+self.language,\
        columns="rev_id, page_id, rev_len, page_ns, page_len, is_redirect, author, author_text,"+\
        " rev_timestamp, rev_parent_id",
        query="SELECT rev_id, rev_page, rev_len, page_namespace, page_len, rev_is_redirect,"+\
        " rev_user, rev_user_text, rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id")
        #View sumarizing info regarding pages in namespace=0 (including articles, stubs and redirects)
        dbaccess.createView(self.acceso[1], view="ns0_"+self.language,\
        columns="rev_id, page_id, rev_len, page_len, page_title, is_redirect, author, author_text,"+\
        " rev_timestamp, rev_parent_id",
        query="SELECT rev_id, rev_page, rev_len, page_len, page_title, page_is_redirect, rev_user,"+\
        " rev_user_text, rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id"+\
        " AND page_namespace=0")
        #View sumarizing info for articles (excluding pages that currently are redirects and stubs)
        dbaccess.createView(self.acceso[1], view="articles_"+self.language,\
        columns="rev_id, page_id, rev_len, page_len, page_title, author, author_text, rev_timestamp, "+\
        "rev_parent_id",
        query="SELECT rev_id, rev_page, rev_len, page_len, page_title, rev_user, rev_user_text,"+\
        " rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id AND page_namespace=0 AND "+\
        "page_is_redirect=0 AND page_is_stub=0")
        #View with info only for redirects (pages that were redirects when that revision was made)
        dbaccess.createView(self.acceso[1], view="redirects_"+self.language,\
        columns="rev_id, page_id, rev_len, page_len, page_title, author, author_text, rev_timestamp, "+\
        "rev_parent_id",
        query="SELECT rev_id, rev_page, rev_len, page_len, page_title, rev_user, rev_user_text, "+\
        "rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id AND "+\
        "page_namespace=0 AND rev_is_redirect=1")
        #View with info only for current redirects
        dbaccess.createView(self.acceso[1], view="cur_redirects_"+self.language,\
        columns="rev_id, page_id, rev_len, page_len, page_title, author, author_text, rev_timestamp, "+\
        "rev_parent_id",
        query="SELECT rev_id, rev_page, rev_len, page_len, page_title, rev_user, rev_user_text, "+\
        "rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id AND "+\
        "page_namespace=0 AND page_is_redirect=1")
        #View with info only for revisions of stub pages (pages that were stubs when that revision was made)
        dbaccess.createView(self.acceso[1], view="stubs_"+self.language,\
        columns="rev_id, page_id, rev_len, page_len, page_title, author, author_text, rev_timestamp,"\
        " rev_parent_id",
        query="SELECT rev_id, rev_page, rev_len, page_len, page_title, rev_user, rev_user_text, "+\
        "rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id AND"+\
        " page_namespace=0 AND rev_is_stub=1")
        #View with info only for revisions of current stub pages
        dbaccess.createView(self.acceso[1], view="cur_stubs_"+self.language,\
        columns="rev_id, page_id, rev_len, page_len, page_title, author, author_text, rev_timestamp,"\
        " rev_parent_id",
        query="SELECT rev_id, rev_page, rev_len, page_len, page_title, rev_user, rev_user_text, "+\
        "rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id AND"+\
        " page_namespace=0 AND page_is_stub=1")
        #From this point on, automatically create views for the set of pages included in the remaining namespaces in MediaWiki
        for nspace, nsnum in zip(self.nspaces[7:], range(1, 16)):
            dbaccess.createView(self.acceso[1], view=nspace+"_"+self.language,\
            columns="rev_id, page_id, rev_len, page_len, page_title, author, author_text, rev_timestamp,"+\
            " rev_parent_id",
            query="SELECT rev_id, rev_page, rev_len, page_len, page_title, rev_user, rev_user_text, "+\
            "rev_timestamp, rev_parent_id FROM revision, page WHERE rev_page=page_id AND"+\
            " page_namespace="+str(nsnum))

        #View sumarizing the distribution of pages among namespaces
        dbaccess.dropView(self.acceso[1], "nspaces_" + self.language)
        dbaccess.createView(self.acceso[1],view="nspaces_"+self.language, columns="namespace, pages_in_nspace",\
        query="SELECT page_namespace, COUNT(*) FROM page GROUP BY page_namespace")

        ##    Intermidiate views for the minimun timestamp of every page [annons, and logged users]
        ## And other useful intermediate views regarding page evolution
        for nspace in self.nspaces:
            dbaccess.dropView(self.acceso[1], nspace+"_"+self.language+\
            "_page_min_timestamp_logged")
            dbaccess.createView(self.acceso[1], view=nspace+"_"+self.language+\
            "_page_min_timestamp_logged", columns="page_id, rev_id, author, rev_timestamp",\
            query="SELECT page_id, rev_id,author, MIN(rev_timestamp) FROM "+\
            nspace+"_"+self.language+" WHERE author!=0 GROUP BY page_id")
            dbaccess.dropView(self.acceso[1], nspace+"_"+self.language+\
            "_page_min_timestamp_annons")
            dbaccess.createView(self.acceso[1], view=nspace+"_"+self.language+\
            "_page_min_timestamp_annons",\
            columns="page_id, rev_id, author_text, rev_timestamp",\
            query="SELECT page_id,rev_id,author_text, MIN(rev_timestamp) FROM "+\
            nspace+"_"+self.language+" WHERE author=0 GROUP BY page_id")

            dbaccess.dropView(self.acceso[1],
                              nspace + "_" + self.language + "_list_months")
            dbaccess.createView(self.acceso[1],view=nspace+"_"+self.language+"_list_months",\
            columns="month, year",query="SELECT MONTH(rev_timestamp) as month, "+\
            "YEAR(rev_timestamp) as year"+\
            " FROM "+nspace+"_"+self.language+" GROUP BY year, month ORDER BY year, month")

            dbaccess.dropView(self.acceso[1],
                              nspace + "_" + self.language + "_list_quarters")
            dbaccess.createView(self.acceso[1],view=nspace+"_"+self.language+"_list_quarters",\
            columns="quarter, year",query="SELECT QUARTER(rev_timestamp) as quarter, "+\
            "YEAR(rev_timestamp) as year FROM "+nspace+"_"+self.language+" GROUP BY year,"+\
            " quarter ORDER BY year, quarter")

    ##    Close DB connection
        dbaccess.close_Connection(self.acceso[0])