MediaWiki:Common.js

From SQLZOO
Revision as of 11:19, 3 August 2012 by Connor (talk | contribs)
Jump to navigation Jump to search

Note: After saving, you may have to bypass your browser's cache to see the changes.

  • Firefox / Safari: Hold Shift while clicking Reload, or press either Ctrl-F5 or Ctrl-R (⌘-R on a Mac)
  • Google Chrome: Press Ctrl-Shift-R (⌘-Shift-R on a Mac)
  • Internet Explorer / Edge: Hold Ctrl while clicking Refresh, or press Ctrl-F5
  • Opera: Press Ctrl-F5.
/* Any JavaScript here will be loaded for all users on every page load. */
$(function(){
  var engine = $('<div class="portal"/>')
    .append($('<h5>Engine</h5>'))
    .append($('<div class=body/>')
      .append($('<ul/>')
        .append($('<li/>')
          .append($('<select id=engine/>')
            .append('<option value=mysql>MySQL</option>')
            .append('<option value=oracle>Oracle</option>')
            .append('<option value=sqlserver>SQL Server</option>')
            .append('<option value=postgres>PostgreSQL</option>')
            .append('<option id=ingres>Ingres</option>')
            .append('<option id=db2>DB2</option>')
            .change(function(){/*alert(42);*/ setDefaultText();})
          )
        )
      )
    )
  $('#p-Reference').before(engine);
  
	// Reset localStorage
	var rst = $('.lsclear');
	rst.click(function(e){
			   if (localStorage) localStorage.clear();
			   alert("Local storage has been cleared.");});
	var numberOfQuestions = 0;
	var numberOfAnswered = 0;
	var numberOfCorrect = 0;
	var qu = $('.qu,.ht,.err');
	for(var i=0;i<qu.length;i++){
		var id = i+1;
		var q = qu[i];
		var lsName = wgPageName + '_' + 'frm__' + id;
		
		var def = $('.def',q);
		if (def.length>1){
			var curEng = $('#engine').val();
			var pick = def.filter(function(){
				var clss = $(this).attr('class').split(' ');
				for(var k=0;k<clss.length;k++)
					if (clss[k].match("^e-")) return false;
				return true
				});
			for(var j=0;j<def.length;j++)
			  if ($(def[j]).hasClass('e-'+curEng))
				pick = $(def[j]);
			def = pick;
		}
		
		//Hints
		var hint = $('.hint', q);
		hint.hide();
		hint.each(function(){
			var htitle = $(this).attr("title");
			var hnt = $('<div/>', {'class':'hnt', 'text':htitle});
			var hidden = $(this);
			hnt.click(function(e){
			   e.preventDefault();
			   hidden.toggle("slow");});
			$(this).before(hnt);
		});
		
		
		var txt = def.text();
		var ans = $('.ans',q).text();
		var tdy = $('.tidy',q).text();
		var frm = $('<form/>',{name:'frm__'+id,id:'frm__'+id})
			.append($('<div/>',{'class':'quf'})
			   .append($('<textarea></textarea>',
				 {value:$.trim(txt),
				  rows:2+Math.max(Math.max(4 ,txt.split(/[\n\r]+/).length),ans.split(/[\n\r]+/).length),
				  cols:2+Math.max(Math.max(45,maxlen(txt.split(/[\n\r]+/))),maxlen(ans.split(/[\n\r]+/))),
				  'class':'sql',
				  id:'txtar_'+id}))
			   .append($('<br/>'))
			   .append($('<button/>',{text:'Submit SQL','class':'submitSQL',click:goBaby}))
			);
		
		def.after(frm);
		
		
		// Display completion information
		if (localStorage)
		{
			if (!localStorage.getItem(lsName))
				localStorage.setItem(lsName, "unanswered");
			if (localStorage.getItem(lsName) == "answered")
			{
				//$(".completion", q).html("You have answered this question <u>incorrectly</u> before.");
				numberOfAnswered++;
				var qcorr = $('<div/>', {'class':'qincorrect', 'title':'You have answered this question incorrectly before.'});
				def.before(qcorr);
			}
			else if (localStorage.getItem(lsName) == "correct")
			{
				//$(".completion", q).html("You have answered this question <u>correctly</u> before.");
				numberOfCorrect++;
				numberOfAnswered++;
				
				var qcorr = $('<div/>', {'class':'qcorrect', 'title':'You have answered this question correctly before.'});
				def.before(qcorr);
			}
			else
			{
				//$(".completion", q).html("You have not answered this question yet.");
			}
		}
		
		var lhs = $('<div/>',{css:{width:'60ex',marginRight:'2ex','float':'left'}});
		lhs.append($('<span/>',{text:id+'.','class':'id'}));
		lhs.append($(q).children());
		$(q).append(lhs);
                // This line caused problems when styling - I removed the css margin-left from it - M
		//$(q).append($('<div/>',{text:'result','class':'res',css:{'margin-left':'1ex'}}));
                $(q).append($('<div/>',{text:'result','class':'res'}));
		
		//Show additional info if available for active angine
		var ecomm = $('.ecomm,.link',q);
		var ecomm1 = ecomm.filter(false);
		if (ecomm.length>0){
			var curEng = $('#engine').val();
			for(var j=0;j<ecomm.length;j++)
			  if ($(ecomm[j]).hasClass('e-'+curEng))
				ecomm1 = $(ecomm[j]);
		}
		for (var j = 0; j < ecomm.length; j++)
		{
			if ($(ecomm[j]).get(0) == ecomm1.get(0))
			{
				$(ecomm[j]).show();
			}
			else
			{
				$(ecomm[j]).hide();
			}
		}
		
		var ecomm = $('.link',q);
		var ecomm1 = def.filter(function(){
				var clss = $(this).attr('class').split(' ');
				for(var k=0;k<clss.length;k++)
					if (clss[k].match("^e-")) return false;
				return true
				});
		if (ecomm.length>0){
			var curEng = $('#engine').val();
			for(var j=0;j<ecomm.length;j++)
			  if ($(ecomm[j]).hasClass('e-'+curEng))
				ecomm1 = $(ecomm[j]);
		}
		for (var j = 0; j < ecomm.length; j++)
		{
			if ($(ecomm[j]).get(0) == ecomm1.get(0))
			{
				$(ecomm[j]).show();
			}
			else
			{
				$(ecomm[j]).hide();
			}
		}
		numberOfQuestions = id;
	}
	
	// Display completion info
	$(".summary").html("You have answered "+numberOfAnswered+" out of "+numberOfQuestions+" questions on this page.<br/>"+numberOfCorrect+" of your answers were correct.");
	// Save completion info
	if (localStorage)
	{
		localStorage.setItem(wgPageName+'_numberOfQuestions', numberOfQuestions);
		localStorage.setItem(wgPageName+'_numberOfCorrect', numberOfCorrect);
	}
	
	//Progress bar
	var maxWidth = $('.progressbar').css('width');
	var barModification = parseFloat(maxWidth) * numberOfCorrect / numberOfQuestions;
	
	$('.progressbar').css('width', barModification);
	
})

function setDefaultText() {
	var qu = $('.ht,.err');
	for(var i=0;i<qu.length;i++){
		var id = i+1;
		var q = qu[i];
		
		var def = $('.def',q);
		if (def.length>1){
			var curEng = $('#engine').val();
			var pick = def.filter(function(){
				var clss = $(this).attr('class').split(' ');
				for(var k=0;k<clss.length;k++)
					if (clss[k].match("^e-")) return false;
				return true
				});
			for(var j=0;j<def.length;j++)
			  if ($(def[j]).hasClass('e-'+curEng))
				pick = $(def[j]);
			def = pick;
		}
		
		var txt = def.text();
		$('textarea#txtar_'+id).val(txt);
		
		//Show additional info if available for chosen engine
		var ecomm = $('.ecomm',q);
		var ecomm1 = ecomm.filter(false);
		if (ecomm.length>0){
			var curEng = $('#engine').val();
			for(var j=0;j<ecomm.length;j++)
			  if ($(ecomm[j]).hasClass('e-'+curEng))
				ecomm1 = $(ecomm[j]);
		}
		for (var j = 0; j < ecomm.length; j++)
		{
			if ($(ecomm[j]).get(0) == ecomm1.get(0))
			{
				$(ecomm[j]).show();
			}
			else
			{
				$(ecomm[j]).hide();
			}
		}
		
		//Show headers for chosen engine
		var ecomm = $('.link',q);
		var ecomm1 = def.filter(function(){
				var clss = $(this).attr('class').split(' ');
				for(var k=0;k<clss.length;k++)
					if (clss[k].match("^e-")) return false;
				return true
				});
		if (ecomm.length>0){
			var curEng = $('#engine').val();
			for(var j=0;j<ecomm.length;j++)
			  if ($(ecomm[j]).hasClass('e-'+curEng))
				ecomm1 = $(ecomm[j]);
		}
		for (var j = 0; j < ecomm.length; j++)
		{
			if ($(ecomm[j]).get(0) == ecomm1.get(0))
			{
				$(ecomm[j]).show();
			}
			else
			{
				$(ecomm[j]).hide();
			}
		}
		
	}
}
	

function goBaby(){
  var qu = $(this).parents('.qu, .ht, .err');
  var lsUse = qu[0].getAttribute('class') != 'ht';
  var lsName = wgPageName + '_' + $(this).parents('form').attr('id');
  if (lsUse && localStorage && (localStorage.getItem(lsName) != "correct"))
	localStorage.setItem(lsName, "answered");
  var sql = qu.find('textarea.sql').val();
  var parlst = $('.params').text().split(';');
  var params = {};
  for(var i=0;i<parlst.length;i++){
    var pair = parlst[i].split(':');
    params[pair[0]]=pair[1];
  }
  qu.find('.res').addClass('waiting');
  $.ajax({url:'/sqlgo.pl',cache:false,'type':'post',dataType:'json',
          data:{sql:sql.replace(/\xA0/g,' '),  //Mediawiki inserts &nbsp; before a %. We need to change it back to a space.CM 13/6/12
                format:'json',
                question:$('.id',qu).text(),
                wgUserName:wgUserName,
                cookie:$.cookie('oliver'),
                page:wgPageName,
                server:$('#engine').val(),
                setup:$('.setup',qu).text().replace(/\xA0/g,' '),
                tidy:$('.tidy',qu).text().replace(/\xA0/g,' '),
                answer:$('.ans',qu).text().replace(/\xA0/g,' '),
                schema:params['schema']
               },
          success:function(d){
            var res = qu.find('.res');
            res.empty().removeClass('waiting')
            if (d.error){
              res.append($('<h1/>',{text:'SQLZoo System Error:'}))
              res.append($('<div/>',{text:d.error}))
              return;
            }
			var headerPresent = false;
            for (var i = 0; i < d.sql.length; i++)
				{
				if (!d || !d.sql || !d.sql[i]){
				  res.append($('<h1/>',{text:'SQLZoo System Error:'}))
				  res.append($('<div/>',{text:"Problem with d or d.sql or d.sql[0]"}))
				  return;
				}
				if (d.sql[i].error){
				  res.append($('<h1/>',{text:'Error:'}))
				  res.append($('<div/>',{text:d.sql[i].error}))
				  return;
				}
				var legend = "Result:";
				if (d.score && d.answer && d.answer.length==1 && d.answer[0].fields){
				  if (d.score == 100){
					legend = 'Correct answer';
					if (lsUse && localStorage)
						localStorage.setItem(lsName, "correct");
			      }
				  else if (d.answer[0].fields.length>d.sql[0].fields.length)
					legend = 'Too few columns';
				  else if (d.answer[0].fields.length<d.sql[0].fields.length)
					legend = 'Too many columns';
				  else if (d.answer[0].rows.length>d.sql[0].rows.length)
					legend = 'Too few rows';
				  else if (d.answer[0].rows.length<d.sql[0].rows.length)
					legend = 'Too many rows';

				}
				if (!headerPresent)
				{
					res.append($('<h1/>',{text:legend}));
					headerPresent = true;
				}
				var t = mkTable(d.sql[i]);
				t.addClass('sqlmine')
				 .appendTo(res);
				if (d.answer && d.answer.length>0){
				  res.append($('<div/>',{text:'Show correct result','class':'showtxt'})
					.click(function(){
					  $(this).next().show('slow');
					})
				  );
				  var a = mkTable(d.answer[0]);
				  a.addClass('sqlans');
				  a.appendTo(res);
				}
			}
          },
          error:function(jqXHR,textStatus,errorThrown){
            qu.find('.res').empty().removeClass('waiting')
              .append($('<h1/>',{'class':'syserr',text:'SQLZOO system error:'}))
              .append($('<div/>',{text:textStatus}))
              .append($('<div/>',{text:errorThrown}))
              .append($('<div/>').html(jqXHR.responseText))
          }
         });
  return false;
}
function maxlen(l){
  var r = 0;
  for(var i=0;i<l.length;i++)
    r = Math.max(r,l[i].length);
  return r;
}
function truncate(s){
  if (s.length<15) return s;
  return s.substring(0,13)+"..";
}
function mkTable(a){
  var t = $('<table/>');
  t.append($('<tr/>'));
  var isnum = [];
  if (!a.fields || !a.rows) return t;
  for(var i=0;i<a.fields.length;i++){
    $('tr',t).append($('<th/>',{text:truncate(a.fields[i])}));
    var allNum = 1;
    for(var j=0;j<a.rows.length;j++){
      if (a.rows[j] && a.rows[j][i] && !a.rows[j][i].match(/^[0-9.]*$/))
        allNum = 0;
    }
    isnum.push(allNum);
  }
  for(var j=0;j<a.rows.length;j++){
    var tr = $('<tr/>').appendTo(t);
    for(var k=0;k<a.rows[j].length;k++){
      var td = $('<td/>',{text:a.rows[j][k]});
      if (isnum[k]) td.addClass('r');
      td.appendTo(tr);
    }
  }
  return t;
}

/* === Designer js starts here === */

// display site logo
$(function(){
  $('<a/>',{href:'http://sqlzoo.net/w/index.php',id:'mp-logo',title:'SQLzoo Main Page'})
    .append($('<img/>',{src:'http://socweb8.napier.ac.uk/~09011004/zoo_tutorials/sql_zoo_logo_busy.png',alt:'SQLzoo logo'}))
    .appendTo($('#mw-head-base'))
});
 
// display site description
$(document).ready(function(){
  $(function(){
    $("#mp-logo").append(" <h1 id='logo-desc'>Interactive <span>SQL <span>Tutorial</span></span></h1>");
  });
});

// assemble and display main nav menu
$(function(){
    var ml1 =
    [['1 SELECT basics',  'SELECT_basics','Some simple queries to get you started']
    ,['2 SELECT from BBC','SELECT_from_BBC_Tutorial','Finding facts about countries']
    ,['3 SELECT from Nobel','SELECT_from_Nobel_Tutorial','More practice with SELECT statements']
    ,['4 SELECT within SELECT','SELECT_within_SELECT_Tutorial','Using the results of one query inside another']
    ,['5 SUM and COUNT','SUM_and_COUNT','Apply aggregate functions']
    ,['6 JOIN','The_JOIN_operation','Gathering data from more than one table']
    ,['7 More JOIN','More_JOIN_operations','Getting data from the movie database']
    ,['8 Using NULL','Using_Null','Dealing with missing data']
    ,['9 Self JOIN','Self_join','Dealing with missing data']
    ];
    var mm1 = $('<ul/>',{id:'mm1','class':'mm_sub'});
    for(var i=0;i<ml1.length;i++){
	  var maxWidth = 50; // adjust the value so that it fits menu well
	  var NoQ = localStorage.getItem(ml1[i][1] + '_numberOfQuestions');
	  if (!NoQ) NoQ = 1;
	  var NoC = localStorage.getItem(ml1[i][1] + '_numberOfCorrect');
	  if (!NoC) NoC = 0;	  
	  var pbWidth = parseFloat(maxWidth) * NoC / NoQ;
	  var mprogbar = $('<div/>',{'class':'progressbarbg1'}).append($('<div/>',{'class':'progressbar1', 'id':ml1[i][1], css:{'width':pbWidth}}));
          mm1.append($('<li/>').append($('<a/>',{href:ml1[i][1],text:ml1[i][0]})).append(ml1[i][2]).append(mprogbar));
    }
    var mm2 = $('<ul/>',{id:'mm2','class':'mm_sub'});
        mm2.append('<li><a href="#">1 AdventureWorks</a> Flogging sports gear. Assessment for CO22008 2007/8</li>');
        mm2.append('<li><a href="#">2 Neeps</a> A timetable database</li>');
        mm2.append('<li><a href="#">3 Musicians</a> Concerts and compositions</li>');
        mm2.append('<li><a href="#">4 Southwind</a> Buying and selling</li>');
        mm2.append('<li><a href="#">5 Dressmaker</a> Constructing clothing</li>');
        mm2.append('<li><a href="#">6 Congestion Charging</a> Monitoring traffic (old questions)</li>');
        mm2.append('<li><a href="#">7 Weather data for Southhampton</a></li>');
        mm2.append('<li><a href="#">8 Album Tracks Style</a> Music data</li>');
 
    var mm3 = $('<ul/>',{id:'mm3','class':'mm_sub'});
        mm3.append('<li><a href="#">SELECT</a>How to read the data from a database.</li>');
        mm3.append('<li><a href="#">CREATE and DROP</a>How to create tables, indexes, views and other things. How to get rid of them.</li>');
        mm3.append('<li><a href="#">INSERT and DELETE</a>How to put records into a table, change them and how to take them out again.</li>');
        mm3.append('<li><a href="#">DATE and TIME</a>How to work with dates; adding, subtracting and formatting.</li>');
        mm3.append('<li><a href="#">Functions</a>How to use string functions, logical functions and mathematical functions.</li>');
        mm3.append('<li><a href="#">Users</a>How to create users, GRANT and DENY access, get at other peoples tables. How to find processes and kill them.</li>');
        mm3.append('<li><a href="#">Meta Data</a>How to find out what tables and columns exist. How to count and limit the rows return.</li>');
 
    var mm = $('<ul/>',{id:'main_menu'}).appendTo('#mw-head-base');
        mm.append('<li id="mm1"><a href="#">Tutorials</a></li>');
        mm.append('<li id="mm2"><a href="#">Assessments</a></li>');
        mm.append('<li id="mm3"><a href="#">Reference</a></li>');
 
    $('#mm1').append(mm1);
    $('#mm2').append(mm2);
    $('#mm3').append(mm3);
});

// stick on top elements that need to be visible
$(function(){
    $("#main_menu").addClass("stickableMenu");
    $("#p-Reference").addClass("stickableRef");
    $(".ref_section").addClass("stickableDbRef");
});

$(document).scroll(function() {
    var useFixedMenu = $(document).scrollTop() > 175;
    $('.stickableMenu').toggleClass('fixedMenu', useFixedMenu);
    
    var useFixedRef = $(document).scrollTop() > 275;
    $('.stickableRef').toggleClass('fixedRef', useFixedRef);

    var useFixedDbRef = $(document).scrollTop() > 275;
    $('.stickableDbRef').toggleClass('fixedDbRef', useFixedRef);
});

// swap classes on external links to change their side icons
$(function(){
 $(".external").addClass("zoo_external");
 $(".external").removeClass("external");
});

// this script helps to manage browser inconsistency across different os
function css_browser_selector(u){var ua=u.toLowerCase(),is=function(t){return ua.indexOf(t)>-1},g='gecko',w='webkit',s='safari',o='opera',m='mobile',h=document.documentElement,b=[(!(/opera|webtv/i.test(ua))&&/msie\s(\d)/.test(ua))?('ie ie'+RegExp.$1):is('firefox/2')?g+' ff2':is('firefox/3.5')?g+' ff3 ff3_5':is('firefox/3.6')?g+' ff3 ff3_6':is('firefox/3')?g+' ff3':is('gecko/')?g:is('opera')?o+(/version\/(\d+)/.test(ua)?' '+o+RegExp.$1:(/opera(\s|\/)(\d+)/.test(ua)?' '+o+RegExp.$2:'')):is('konqueror')?'konqueror':is('blackberry')?m+' blackberry':is('android')?m+' android':is('chrome')?w+' chrome':is('iron')?w+' iron':is('applewebkit/')?w+' '+s+(/version\/(\d+)/.test(ua)?' '+s+RegExp.$1:''):is('mozilla/')?g:'',is('j2me')?m+' j2me':is('iphone')?m+' iphone':is('ipod')?m+' ipod':is('ipad')?m+' ipad':is('mac')?'mac':is('darwin')?'mac':is('webtv')?'webtv':is('win')?'win'+(is('windows nt 6.0')?' vista':''):is('freebsd')?'freebsd':(is('x11')||is('linux'))?'linux':'','js']; c = b.join(' '); h.className += ' '+c; return c;}; css_browser_selector(navigator.userAgent);

$(function(){
  $('input.check').each(function(i,e){
    $(this).attr('id','quiz_d_'+i);
  });
  $('tr.proposal').each(function(i,e){
    var tds = $('td',$(this));
    var txt = $(tds[1]).text();
    $(tds[1]).html($('<label/>',{text:txt,'for':$('input',$(this)).attr('id') }));
  })
})
//Connor 23/7/12 creates the labels for the multiple choice questions allowing them to be highlighted.


//Analytics code
  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'UA-33860668-1']);
  _gaq.push(['_trackPageview']);

  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();