SQL Queries Log in to Edit

Authors:

There is a newer version of this page. You can find it here.

This page contains a list of queries which display useful information.

Changelists by Day of Week

SQL	column name	row name(s)
SELECT Count(*) AS num, DATE_FORMAT(changeDate,"%W") AS day, DATE_FORMAT(changeDate,"%w") AS foo FROM changes GROUP BY foo ORDER BY foo	day	num

Files Edit Touches by User

SQL	column name	row name(s)
SELECT name, SUM(IF(action = 'add', 1, 0)) AS adds, SUM(IF(action = 'edit', 1, 0)) AS edits FROM users, changes, filechanges WHERE users.userID = changes.userID AND changes.changeID = filechanges.changeID GROUP BY users.userID ORDER BY edits DESC	name	edits

Current Line Ownership in //depot/main/code/%

SQL	column name	row name(s)
SELECT name, SUM(e_line - b_line + 1) AS num FROM users, ownerships, (SELECT filechanges.filechangeID AS filechangeID FROM filechanges, (SELECT filechanges.fileID AS fileID, MAX(revision) AS revision FROM files, filechanges WHERE files.fileID = filechanges.fileID AND fileName LIKE '//depot/Main/code/%' GROUP BY filechanges.fileID) t1 WHERE filechanges.fileID = t1.fileID AND filechanges.revision = t1.revision) t2 WHERE users.userID = ownerships.userID AND ownerships.filechangeID = t2.filechangeID GROUP BY users.userID ORDER BY num DESC	name	num

Top 20 Most Active Files

SQL	column name	row name(s)
SELECT fileName, SUM(IF(action = 'add', 1, 0)) AS adds, SUM(IF(action = 'delete', 1, 0)) AS deletes, SUM(IF(action = 'edit', 1, 0)) AS edits, SUM(IF(action = 'integrate', 1, 0)) AS integrates, SUM(IF(action = 'branch', 1, 0)) AS branches, SUM(IF(action = 'purge', 1, 0)) AS purges, SUM(IF(action = 'import', 1, 0)) AS imports, COUNT(*) AS num FROM files INNER JOIN filechanges ON files.fileID = filechanges.fileID GROUP BY filechanges.fileID ORDER BY num DESC LIMIT 20	fileName	adds,deletes,edits,integrates,branches,purges,imports

Discussion

Anonymous