3 # stats_cvs.pl - NIGHTLY SCRIPT
5 # Recurses through the /cvsroot directory tree and parses each projects
6 # '~/CVSROOT/history' file, and create and fill the sql table with
7 # modified, and added to each project.
12 use POSIX qw( strftime );
16 require("/usr/share/gforge/lib/include.pl");
17 my $cvsroot = "/var/lib/gforge/chroot/cvsroot";
23 db_drop_table_if_exists ("deb_cvs_dump") ;
24 db_drop_table_if_exists ("deb_cvs_group") ;
25 db_drop_table_if_exists ("deb_cvs_group_user") ;
28 sub create_dump_table {
30 $sql = "CREATE TABLE deb_cvs_dump (
32 year integer NOT NULL,
33 month integer NOT NULL,
35 time integer NOT NULL,
43 my ($year, $month, $day, $day_begin, $day_end);
45 print "Running tree at $cvsroot/\n";
47 chdir( "$cvsroot" ) || die("Unable to make $cvsroot the working directory.\n");
49 foreach $group ( glob("*") ) {
50 next if ( ! -d "$group" );
51 my ($cvs_co, $cvs_commit, $cvs_add, %usr_commit, %usr_add );
52 print "Parsing $group/\n";
54 open(HISTORY, "< $cvsroot/$group/CVSROOT/history") or print "E::Unable to open history for $group\n";
56 my ($time_parsed, $type, $cvstime, $user, $curdir, $module, $rev, $file );
58 ## Split the cvs history entry into it's 6 fields.
59 ($cvstime,$user,$curdir,$module,$rev,$file) = split(/\|/, $_, 6 );
61 ## log modified $type eq "M"
62 ## log added $type eq "A"
63 ## log others $type neq "A" neq "M"
64 $type = substr($cvstime, 0, 1);
65 $time_parsed = hex( substr($cvstime, 1, 8) );
66 $year = strftime("%Y", gmtime( $time_parsed ) );
67 $month = strftime("%m", gmtime( $time_parsed ) );
68 $day = strftime("%d", gmtime( $time_parsed ) );
69 $sql = "INSERT INTO deb_cvs_dump
70 (type,year,month,day,time,cvsuser,cvsgroup)
71 VALUES ('$type','$year','$month','$day','$time_parsed','$user','$group')";
82 # CVS doc says the meaning of the code letters.
85 #====== =========================================================
89 #W Update (no user file, remove from entries file)
90 #U Update (file overwrote unmodified user file)
91 #G Update (file was merged successfully into modified user file)
92 #C Update (file was merged, but conflicts w/ modified user file)
93 #M Commit (from modified file)
94 #A Commit (an added file)
95 #R Commit (the removal of a file)
98 CREATE TABLE deb_cvs_group_user AS
99 SELECT agg.cvsgroup,agg.cvsuser,agg.year,agg.month,agg.day,agg.total AS total,m.modified AS modified,a.added AS added,o.others AS others
101 SELECT cvsgroup,cvsuser,year,month,day,COUNT(*) AS total
103 GROUP BY year,month,day,cvsgroup,cvsuser
106 SELECT cvsgroup,cvsuser,year,month,day,COUNT(*) AS modified
109 GROUP BY year,month,day,cvsgroup,cvsuser
110 ) m USING (cvsgroup,cvsuser,year,month,day)
112 SELECT cvsgroup,cvsuser,year,month,day,COUNT(*) AS added
115 GROUP BY year,month,day,cvsgroup,cvsuser
116 ) a USING (cvsgroup,cvsuser,year,month,day)
118 SELECT cvsgroup,cvsuser,year,month,day,COUNT(*) AS others
120 WHERE type!='A' and type!='M'
121 GROUP BY year,month,day,cvsgroup,cvsuser
122 ) o USING (cvsgroup,cvsuser,year,month,day)
128 my ($sql,$res,$temp);
129 $sql = "SELECT * FROM deb_cvs_group_user order by year, month, day";
130 $res = $dbh->prepare($sql);
132 while ( my ($cvsgroup, $cvsuser, $year, $month, $day, $total, $modified, $added, $others) = $res->fetchrow()) {
133 print "$cvsgroup $cvsuser $year $month $day $total=$modified+$added+$others\n";
135 print "-----------------------------------------------------\n";
136 print "cvsgroup\tcvsuser\tmodified\tadded\tothers\n";
137 print "-----------------------------------------------------\n";
138 $sql = "SELECT cvsgroup, cvsuser, SUM(modified), SUM(added), SUM(others) FROM deb_cvs_group_user group by cvsgroup,cvsuser";
139 $res = $dbh->prepare($sql);
141 while ( my ($cvsgroup, $cvsuser, $modified, $added, $others) = $res->fetchrow()) {
142 print "$cvsgroup\t$cvsuser\t$modified\t$added\t$others\n";
144 print "-----------------------------------------------------\n";
147 sub cvs_stats_merge {
148 print "-----------------------------------------------------\n";
149 print "Inserting cvs data into\n";
150 print "-----------------------------------------------------\n";
151 my ($sql,$res,$temp);
152 $sql = "DELETE FROM stats_cvs_group
153 WHERE (month,day,group_id) IN (
154 SELECT d.month+ d.year*100,
156 FROM deb_cvs_group_user AS d, groups AS g
157 WHERE d.cvsgroup=g.unix_group_name
158 GROUP BY d.month,d.year,d.day,g.group_id
161 $sql = "INSERT INTO stats_cvs_group
162 SELECT d.month + d.year * 100,
164 sum(coalesce(d.others,0)),
165 sum(coalesce(d.modified,0)),
166 sum(COALESCE(d.added,0))
167 FROM deb_cvs_group_user AS d,groups AS g
168 WHERE d.cvsgroup=g.unix_group_name
169 and (d.month + d.year * 100,
172 SELECT month,day,group_id FROM stats_cvs_group
174 GROUP BY year,month,day,group_id
178 $sql = "DELETE FROM stats_cvs_user
179 WHERE (month,day,group_id,user_id) IN (
180 SELECT d.month+ d.year*100,
181 d.day,g.group_id,u.user_id
182 FROM deb_cvs_group_user AS d, groups AS g, users as u
183 WHERE d.cvsgroup=g.unix_group_name AND d.cvsuser=u.user_name
184 GROUP BY d.month,d.year,d.day,g.group_id,u.user_id
187 $sql = "INSERT INTO stats_cvs_user
188 SELECT d.month + d.year * 100,
192 sum(coalesce(d.others,0)),
193 sum(coalesce(d.modified,0)),
194 sum(COALESCE(d.added,0))
195 FROM deb_cvs_group_user AS d,groups AS g, users AS u
196 WHERE d.cvsgroup=g.unix_group_name and
197 d.cvsuser=u.user_name
198 and (d.month + d.year * 100,
202 SELECT month,day,group_id,user_id FROM stats_cvs_user
204 GROUP BY year,month,day,group_id,user_id
208 print " [ x ] Done\n";