NOTICE: By continued use of this site you understand and agree to the binding Terms of Service and Privacy Policy.
// ==UserScript==
// @name Perlmonks Saints in our Book to Oracle
// @namespace http://www.example.com/chacham/
// @description Turn the data in Perlmonks Saints in our Book into an Oracle query with CTEs
// @include http://perlmonks.org/?node_id=3559
// @version 1.2.0
// @grant none
// ==/UserScript==
// Lots of tabs added to prettify the output.
var query = "WITH\
\n\t-- From: http://perlmonks.org/?node_id=509805\
\n\tMonk_Level(Id, XP_Low, XP_High, Title)\
\nAS\
\n\t(\
\n\t SELECT 13, 003000, 03999, 'Curate' FROM Dual UNION ALL\
\n\t SELECT 14, 004000, 05399, 'Priest' FROM Dual UNION ALL\
\n\t SELECT 15, 005400, 06999, 'Vicar' FROM Dual UNION ALL\
\n\t SELECT 16, 007000, 08999, 'Parson' FROM Dual UNION ALL\
\n\t SELECT 17, 009000, 11999, 'Prior' FROM Dual UNION ALL\
\n\t SELECT 18, 012000, 15999, 'Monsignor' FROM Dual UNION ALL\
\n\t SELECT 19, 016000, 21999, 'Abbot' FROM Dual UNION ALL\
\n\t SELECT 20, 022000, 29999, 'Canon' FROM Dual UNION ALL\
\n\t SELECT 21, 030000, 39999, 'Chancellor' FROM Dual UNION ALL\
\n\t SELECT 22, 040000, 49999, 'Bishop' FROM Dual UNION ALL\
\n\t SELECT 23, 050000, 59999, 'Archbishop' FROM Dual UNION ALL\
\n\t SELECT 24, 060000, 69999, 'Cardinal' FROM Dual UNION ALL\
\n\t SELECT 25, 070000, 79999, 'Sage' FROM Dual UNION ALL\
\n\t SELECT 26, 080000, 89999, 'Saint' FROM Dual UNION ALL\
\n\t SELECT 27, 090000, 99999, 'Apostle' FROM Dual UNION ALL\
\n\t SELECT 28, 100000, NULL, 'Pope' FROM Dual\
\n\t),\
\n\tData(Ranking, Name, XP, Writeups, Login_First, Login_Last)\nAS\n\t(";
var table = document.getElementsByTagName('table')[4];
for(var row = 1; row < table.rows.length; row++)
{
query += "\n\t SELECT "
// #
+ table.rows[row].cells[0].textContent + ",\t'"
// User
+ table.rows[row].cells[1].textContent + "',\t";
// Currently, the longest username is 16 characters. Checking programmatically would be even more insane.
// This could be done more fancifully, but for just 2 statements, this is clearer.
// The 2 quotes and 1 comma take up space too.
if(table.rows[row].cells[1].textContent.length < 05) query += "\t";
if(table.rows[row].cells[1].textContent.length < 13) query += "\t";
// Experience
query += table.rows[row].cells[2].textContent + ",\t";
if(table.rows[row].cells[2].textContent.length < 07) query += "\t";
// Level - skipped because it is a name, not a number.
// Writeups
query += table.rows[row].cells[4].textContent.replace('None', 0) + ",\t";
// User Since
if(table.rows[row].cells[5].textContent == 'never') query += "NULL,\t\t\t\t\t\t\t";
else query += "TO_DATE('" + table.rows[row].cells[5].textContent + "', 'YYYY-MM-DD HH24:MI'),\t"
// Last Here - Even NodeReaper who was "never" here, has a Last Here date.
last = table.rows[row].cells[6].textContent.split(" ");
switch(last[1])
{
case 'second': case 'seconds': interval = last[0] + "'\t\tSECOND"; break;
case 'minute': case 'minutes': interval = last[0] + "'\t\tMINUTE"; break;
case 'hour': case 'hours': interval = last[0] + "'\t\tHOUR"; break;
case 'day': case 'days': interval = last[0] + "'\t\tDAY"; break;
// NodeReaper "never" logged in, yet last logged in a century ago...
case 'year': case 'years': interval = last[0] + "'\t\tYEAR(3)"; break;
// INTERVAL does not have week. SioB does not have month.
case 'week': case 'weeks': interval = (last[0] * 7) + "'";
if(last[0] < 15) interval += "\t";
interval += "\tDAY(3)";
}
query += "SYSDATE - INTERVAL '" + interval
+ "\tFROM Dual UNION ALL";
}
// Chop off the extra UNION ALL
query = query.slice(0, -10)
+ "\n\t),\
\n\tData_Level(Monk_Level, Title, Ranking, Name, XP, Writeups, Login_First, Login_Last)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\tMonk_Level.Id,\
\n\t\tMonk_Level.Title,\
\n\t\tData.Ranking,\
\n\t\tData.Name,\
\n\t\tData.XP,\
\n\t\tData.Writeups,\
\n\t\tData.Login_First,\
\n\t\tData.Login_Last\
\n\t FROM\
\n\t\tData,\
\n\t\tMonk_Level\
\n\t WHERE\
\n\t\tData.XP\tBETWEEN Monk_Level.XP_Low AND Monk_Level.XP_High\
\n\t OR\tData.XP >\tMonk_Level.XP_Low AND Monk_Level.XP_High IS NULL\
\n\t),\
\n\tData_Level_Calc(Monk_Level, Title, Ranking, Name, XP, Writeups, XP_Ratio, Exclude)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\tMonk_Level,\
\n\t\tTitle,\
\n\t\tRanking,\
\n\t\tName,\
\n\t\tXP,\
\n\t\tWriteups,\
\n\t\tXP / CASE Writeups WHEN 0 THEN 1 ELSE Writeups END,\
\n\t\t-- Monks with fabricated numbers or 0 Writeups mess up the calculations.\
\n\t\tCASE WHEN Name NOT IN ('vroom', 'NodeReaper') AND Writeups > 0 THEN 1 END\
\n\t FROM\
\n\t\tData_Level\
\n\t),\
\n\tXP_Ratio_By_Level(Monk_Level, Title, Min, Max, Avg, Min_X, Max_X, Avg_X)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\tMonk_Level,\
\n\t\tTitle,\
\n\t\tMIN(XP_Ratio),\
\n\t\tMAX(XP_Ratio),\
\n\t\tAVG(XP_Ratio),\
\n\t\t-- Exclude from functions by NULLing out the number.\
\n\t\tMIN(XP_Ratio * Exclude),\
\n\t\tMAX(XP_Ratio * Exclude),\
\n\t\tAVG(XP_Ratio * Exclude)\
\n\t FROM\
\n\t\tData_Level_Calc\
\n\t GROUP BY\
\n\t\tMonk_Level,\
\n\t\tTitle\
\n\t UNION ALL\
\n\t SELECT\
\n\t\tNULL,\
\n\t\t'(All)',\
\n\t\tMIN(XP_Ratio),\
\n\t\tMAX(XP_Ratio),\
\n\t\tAVG(XP_Ratio),\
\n\t\tMIN(XP_Ratio * Exclude),\
\n\t\tMAX(XP_Ratio * Exclude),\
\n\t\tAVG(XP_Ratio * Exclude)\
\n\t FROM\
\n\t\tData_Level_Calc\
\n\t),\
\n\tStep_XP_Writeups(Ranking, Name, XP, Writeups, XP_Diff, Writeups_Diff)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\tRanking,\
\n\t\tName,\
\n\t\tXP,\
\n\t\tWriteups,\
\n\t\tXP - LEAD(XP) OVER(ORDER BY Ranking),\
\n\t\tWriteups - LEAD(Writeups) OVER(ORDER BY Ranking)\
\n\t FROM\
\n\t\tData\
\n),\
\n\tClose(Ranking, XP_Diff, Writeups_Diff)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\tRanking,\
\n\t\tXP - LEAD(XP) OVER(ORDER BY Ranking),\
\n\t\tWriteups - LEAD(Writeups) OVER(ORDER BY Ranking)\
\n\t FROM\
\n\t\tStep_XP_Writeups\
\n\t WHERE\
\n\t\tXP_Diff\tBETWEEN -30 AND 30\
\n\t AND\tWriteups_Diff\tBETWEEN -5 AND 5\
\n),\
\n\tMMA(XP_Diff_Min, XP_Diff_Max, XP_Diff_Avg, Writeups_Diff_Min, Writeups_Diff_Max, Writeups_Diff_Avg)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\tMIN(XP_Diff),\
\n\t\tMAX(XP_Diff),\
\n\t\tAVG(XP_Diff),\
\n\t\tMIN(Writeups_Diff),\
\n\t\tMAX(Writeups_Diff),\
\n\t\tAVG(Writeups_Diff)\
\n\t FROM\
\n\t\tStep_XP_Writeups\
\n\t),\
\n\tStats(Both, Close, XP, XP_Diff_Min, XP_Diff_Max, XP_Diff_Avg, Writeups, Writeups_Diff_Min, Writeups_Diff_Max, Writeups_Diff_Avg)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\t(SELECT COUNT(COUNT(*)) FROM Data GROUP BY XP, Writeups HAVING Count(*) > 1),\
\n\t\t(SELECT COUNT(*) FROM Close),\
\n\t\t(SELECT COUNT(COUNT(*)) FROM Data GROUP BY XP HAVING Count(*) > 1),\
\n\t\tXP_Diff_Min,\
\n\t\tXP_Diff_Max,\
\n\t\tXP_Diff_Avg,\
\n\t\t(SELECT COUNT(COUNT(*)) FROM Data GROUP BY Writeups HAVING Count(*) > 1) Writeups,\
\n\t\tWriteups_Diff_Min,\
\n\t\tWriteups_Diff_Max,\
\n\t\tWriteups_Diff_Avg\
\n\t FROM\
\n\t\tMMA\n\t)\
\n-- XP Ratio\
\n--SELECT Monk_Level, Title, ROUND(Min, 2) Min, ROUND(Max, 2) Max, ROUND(Avg, 2) Avg, ROUND(Min_X, 2) Min_X, ROUND(Max_X, 2) Max_X, ROUND(Avg_X, 2) Avg_X FROM XP_Ratio_By_Level ORDER BY Monk_Level\
\n-- Years logged in\
\n--SELECT ROUND((Login_Last - Login_First) / 365) Years, COUNT(*) Amount FROM Data_Level GROUP BY ROUND((Login_Last - Login_First) / 365) ORDER BY 1\
\n-- Years logged in by level\
\n--SELECT Monk_Level, ROUND((Login_Last - Login_First) / 365) Years, COUNT(*) Amount FROM Data_Level GROUP BY Monk_Level, ROUND((Login_Last - Login_First) / 365)\
\n-- XP_Diff, Writeup_Dif stats\
\n--SELECT Both, Close, XP, XP_Diff_Min, XP_Diff_Max, XP_Diff_Avg, Writeups, Writeups_Diff_Min, Writeups_Diff_Max, Writeups_Diff_Avg FROM Stats\
\n-- SioB Monks close in XP and Writeups\
\nSELECT\
\n\tData.Ranking,\
\n\tData.Name,\
\n\tData.XP,\
\n\tData.Writeups\
\nFROM\
\n\tData,\n\tClose\
\nWHERE\
\n\tData.Ranking BETWEEN Close.Ranking AND Close.Ranking + 1\
\nORDER BY\
\n\tData.Ranking;";
var textarea = document.createElement('textarea');
textarea.cols = 100;
textarea.rows = 20;
textarea.value = query;
document.body.appendChild(textarea);