Jump to content

User:UninvitedCompany/user script

From Wikipedia, the free encyclopedia

steps

[edit]
  1. Download the split files for en.old from http://download.wikimedia.org. As the database grows there occasionally come to be more split files than there are links. Therefore, after downloading e.g. xaa, xab, xac, xad, xae through the links it also may be necessary to download e.g. xaf and xag.
  2. Compile wikitrunc.c with gcc
  3. $ cat xa? | bunzip2 | wikitrunc > trunc_old.sql
  4. $ mysql --database=test --user=administrator --password=
  5. At the sql prompt, @trunc_old.sql
  6. Allow a week or more for a typical pc.
  7. Run the pre.sql script (14 hours)
  8. Cut and paste Special:Listadmins to a local file named adminlist.txt.
  9. Edit the post.sql script to change minimums (if desired) and run it; you get a file called "list"
  10. Edit the fmtlist.c to change the cutoff date, compile with gcc
  11. fmtlist <list >list.wiki
  12. Paste to Wikipedia:Another list of Wikipedians in order of arrival

wikitrunc.c

[edit]
  #include <stdio.h>

  myerror( char *str )
  {
    fprintf( stderr, "%s\n", str );
    exit( 1 );
  }

  main()
  {
    static char values[] = "VALUES ";
    int state = 0, c, i;

    while( state < 7 && ( c = getchar() ) != EOF ) {
        putchar( c );
        if( values[ state ] == c )
            state++;
        else
            state = 0;
    }

    while ( c != EOF ) {
        do {
            // want (
            if( ( c = getchar() ) != '(' )
                myerror( "format error, expecting (" );
            putchar( c );

            // want ... , ... , ... ,
            for( i=0; i<3; i++ )
                copyfield();

            truncfield();

            while( (c=copyfield()) != ')' && c != EOF )
                ;

            if( c != ')' )
                myerror( "expecting )" );

            c = getchar();      // ,
            putchar( c );
        } while( c == ',' );

        // ; INSERT INTO old VALUES (

        state = i = 0;
        while( state < 7 && ( c = getchar() ) != EOF ) {
            putchar( c );
            if( values[ state ] == c )
                state++;
            else {
                state = 0;
                if( i++ > 100 )
                    error( "garbage looking for INSERT INTO old VALUES" );
            }
        }
    }

    // must be some trailing stuff, what to do?
    //
    //
    fprintf( stderr, "last char was %c (%d)\n", c, c );

    for( i=100; i--; )
        fprintf( stderr, "%c", getchar() );


    return 0;
  }


  copyfield()
  {
    int c;

    if( (c=getchar()) == '\'' ) {
        putchar( c );
        do {
            if( (c = getchar()) == '\\' ) {
                putchar( c );
                putchar( getchar() );
            } else {
                putchar( c );
            }
        } while( c != '\'' );
    } else
        putchar( c );

    while( c != EOF && c != ',' && c != ')' ) {
        c = getchar();
        putchar( c );
    }

    return c;
  }

  truncfield()
  {
    int c;
    int maxcopy = 40;

    if( (c=getchar()) == '\'' ) {
        putchar( c );
        do {
            if( (c = getchar()) == '\\' ) {
                if( maxcopy > 0 ) {
                    putchar( c );
                    putchar( getchar() );
                } else
                    getchar();
            } else {
                if( maxcopy > 0 || c == '\'' ) {
                    putchar( c );
                    maxcopy--;
                }
            }
        } while( c != '\'' );
    } else
        putchar( c );

    while( c != EOF && c != ',' && c != ')' ) {
        c = getchar();
        putchar( c );
    }

    return c;
  }

pre.sql

[edit]
  drop table if exists edit_months;
  drop table if exists track_users;
  create table track_users as select count(old_user_text) as total_edits, old_user_text from old
  group by old_user_text having total_edits > 199;
  # takes 32 minutes


  delete from track_users where old_user_text like '%.%.%.%';
  delete from track_users where total_edits < 200;
  # leaves about 2500 users of interest

  create table edit_months
  as select count(floor( old_timestamp/100000000 )) as month_edits,
  floor( old_timestamp/100000000 ) as yyyymm,
  old.old_user_text as user_text, total_edits
  from old, track_users
  where old.old_user_text = track_users.old_user_text
  group by yyyymm, user_text
  having month_edits > 4;

# 14 hours 30 minutes

post.sh

[edit]
  cat > adminadd.sql <<eof

  drop table if exists adminlist;
  create table adminlist ( user_text varchar(255) binary not null default '' ) TYPE=InnoDB;
  insert into adminlist values
  eof

  sed -e "s/.*ser:/(\'/" adminlist.txt | sed -e "s/$/'),/" >>adminadd.sql

  cat >> adminadd.sql <<eof
  ('bogus last record')
  ;

  delete from adminlist where user_text = 'bogus last record';
  eof

  mysql --database=test --user=administrator --password= <<eof
  source adminadd.sql

  drop table if exists tenure;

  create table tenure
    as select user_text, min( yyyymm ) as yyyymm_first, max( yyyymm ) as yyyymm_last, count( yyyymm ) as months_active, total_edits
    from edit_months
    group by user_text, total_edits
    order by yyyymm_first, user_text;

  alter table tenure add ( admin varchar (10) default 'N' );

  update tenure, adminlist set tenure.admin = 'Y' where adminlist.user_text = tenure.user_text;

  eof
 
  mysql --database=test --user=administrator --password= >list <<eof
  select * from tenure where (total_edits > 1000 and months_active > 5) or admin='Y' order by yyyymm_first, user_text;
  eof

  rm adminadd.sql

fmtlist.c

[edit]
  main()
  {
    static char *months[] = { "", "January", "February", "March", "April", "May",
        "June", "July", "August", "September", "October", "November", "December" };
    char buf[ 500 ];
    gets( buf );    // throw away header line

    char name[ 500 ], last[ 10 ], first[ 10 ], active[ 10 ], edits[ 10 ], *p, *q;
    int admin;

    char savefirst[ 10 ];
    savefirst[0] = '\0';

    printf( "== About this list ==\n"
    "This list was compiled using an [[User:UninvitedCompany/user script|automated script]].\n"
    "A [[Wikipedia:List of Wikipedians in order of arrival|manually-maintained list]] that includes "
    "some interesting commentary but that does not include all users is also available.\n"
    "\n"
    "The purpose of this list is to document the contributions of early contributors,"
    "to draw attention to those prolific contributors who have left the project so that we may better "
    "retain contributors in the future, \n"
    "and to serve as a resource for those who wish to better understand social trends at Wikipedia.\n\n"
    "The list includes [[Wikipedia:Wikipedians|Wikipedians]] who made more than 1000 edits and were active "
    "for more than six months."
    "[[Wikipedia:Administrators|Administrators]] are included for comparison even if they do not meet the "
    "edit count and acvity criteria.\n"
    "Edit counts are shown in parenthesis.  Administrators are indicated with a *.\n"
    "Wikipedians are listed as inactive when they no longer make at least five edits per month.\n"
    "Anonymous contributors are not shown.\n"
    "\n"
    "The list is based on data as of October 10, 2004.  Very recent edits -- those marked (top) in a user's "
    "contribution list -- are not included because of technical limitations of the script.\n\n" );

    while( gets( buf ) ) {
        for( p=buf, q=name; *p && *p != '\t'; p++ )
            *q++ = *p;
        *q = '\0';
        p++;
        for( q=first; *p && *p != '\t'; p++ )
            *q++ = *p;
        *q = '\0';
        p++;
        for( q=last; *p && *p != '\t'; p++ )
            *q++ = *p;
        *q = '\0';
        p++;
        for( q=active; *p && *p != '\t'; p++ )
            *q++ = *p;
        *q = '\0';
        p++;
        for( q=edits; *p && *p != '\t'; p++ )
            *q++ = *p;
   
       *q = '\0';
        p++;
        admin = *p == 'Y';

        if( strcmp( first, savefirst ) != 0 ) {
            printf( "\n== [[%s %.4s]] ==\n",
                    months[ atoi(first+4) ], first );
            strcpy( savefirst, first );
        }

        if( admin )
            printf( "#. [[User:%s|%s*]]", name, name );
        else
            printf( "#. [[User:%s|%s]]", name, name );

        printf( " (%s", edits );
        printf( ")" );

        if( atoi( last ) < 200408 )
            printf( " - inactive since %s %.4s",
                    months[ atoi(last+4) ], last );
        printf( "\n" );
    }
  }