![Børre Stenseth](http://www.it.hiof.no/~borres/allround/selfimages/bs.gif)
UEFA2
Databasen
|
Python
|
XSLT
|
Javascript
Databasen
Databasen (MySQL) er bygget opp på samme måte som i modulen uefa1 :
_Arenaer
DROP TABLE IF EXISTS `arenaer`; CREATE TABLE `arenaer` ( `ID` CHAR(20) NOT NULL, `Countrycode` CHAR(2) NOT NULL, `Name` VARCHAR(45) NOT NULL, `Wiki` VARCHAR(95) DEFAULT NULL, `Data` MEDIUMTEXT DEFAULT NULL, `Capasity` INT DEFAULT NULL, `Latitude` VARCHAR(30) DEFAULT NULL, `Longitude` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`ArenaID`) )ENGINE=InnoDB DEFAULT CHARSET=utf8; |
_Klubber
DROP TABLE IF EXISTS `klubber`; CREATE TABLE `klubber` ( `ID` CHAR(20) NOT NULL, `Countrycode` CHAR(2) NOT NULL, `SerieID` CHAR(4) NOT NULL, `Level` SMALLINT NOT NULL, `Name` VARCHAR(45) NOT NULL, `Data` MEDIUMTEXT DEFAULT NULL, `Web` VARCHAR(95) DEFAULT NULL, `Logo` VARCHAR(45) DEFAULT NULL, `Alternativenames` MEDIUMTEXT DEFAULT NULL, `StadionID` VARCHAR(20) DEFAULT NULL, `Wiki` VARCHAR(95) DEFAULT NULL, PRIMARY KEY (`ID`) )ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Python
Den involverte Python-koden som leverer Json er slik:
_select2XML.py
#! /usr/bin/python import cgi,sys from lxml import etree import dbutils import cgitb; cgitb.enable() """ select from database to json using mysql in a subprocess as implemented in utils """ # transform xml -> json XSLT_FILE='tojson.xsl' form=cgi.FieldStorage() """ Need connect. | separated string Sequence is important: user|password|host|database|table sql """ print 'Content-type: text/plain; charset=utf-8 \n' try: # always connect connect=form['connect'].value sql=form['sql'].value # set up parameters p=dbutils.prepareSelectParameters(connect,sql) if len(p) > 0: print dbutils.makeJson(p,'data/select.xml',XSLT_FILE) else: print 'error bad parameter list in connect' except: res=sys.exc_info() print 'error '+str(res[1])
som benytter samme biblioteket som løsningen i modulen uefa1 :
_dbutils.py
#! /usr/bin/python import cgi,sys import subprocess from lxml import etree """ Run a subprocess, used to perform: extract from database with mysqldump or mysql return ok or error.... """ def doProcess(params,targetfile): try: f=open(targetfile,'wb') p=subprocess.check_call(params,stdout=f,stderr=f,shell=False) f.flush() f.close() if p==0: return 'ok' else: return 'error in subprocess: '+str(p) except: res=sys.exc_info() return 'error '+ str(res[1]) """ extract from database and make an xslt transform to legal JSON return the JSON string or error.... """ def makeJson(params,xmlfile,xslfile): res=doProcess(params,xmlfile) if res.startswith('error'): return res try: tree=etree.parse(xmlfile) xsltree=etree.parse(xslfile) transform=etree.XSLT(xsltree) resulttree=transform(tree) return str(resulttree) except: res=sys.exc_info() return 'error '+ str(res[1]) """ extract from database, make an xslt transform to legal JSON and save the string as a compact Javascript string variable var obj='....' """ def saveJSVariable(params,xslfile,targetfile): S=makeJson(params,"data/tmp",xslfile) if S.startswith('error'): S='{"message":"S"}' else: S=S.replace('\r','').replace('\n','') T="var obj='"+S+"'" res=storeTextFile(targetfile,T) return res[0] """ Set up parameters for a use of mysqldump user|password|host|database|table """ def prepareDumpParameters(connect): p=[] parts=connect.split('|') if len(parts) == 5: p.append('mysqldump') p.append('--xml') p.append('--lock-tables=false')#when limited user rights p.append('--user='+parts[0]) p.append('--password='+parts[1]) p.append('--default-character-set=utf8') p.append('--host='+parts[2]) p.append(parts[3]) #database p.append(parts[4]) #table return p """ Set up parameters for a use of mysql user|password|host|database """ def prepareSelectParameters(connect,sql): p=[] parts=connect.split('|') if len(parts) == 4: p.append('mysql') p.append('-X') p.append('--execute='+sql) p.append('--user='+parts[0]) p.append('--password='+parts[1]) p.append('--default-character-set=utf8') p.append('--host='+parts[2]) p.append(parts[3]) #database return p """ Read / write text files """ def getTextFile(filename): try: file=open(filename,'r') res=file.read() file.close() return ('ok',res) except: res=sys.exc_info() return('error',res[1]) def storeTextFile(filename,txt): try: file=open(filename,'w') file.write(txt) file.close() return('ok','') except: res=sys.exc_info() return('error',res[1])
XSLT
Transformasjone som lager Json fra XML er den samme som i modulen uefa1 :
_Fra XML til Json
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="text" encoding="utf-8"/> <!-- Producing strait json either from a mysqldump of one table to xml or from mysql extract to xmlfile with sql Limitation: Replace " with /" in values, as from: http://stackoverflow.com/questions/9370633/xslt-replacing-double-quotes-with-escape-sequence no other cleaning or escaping alternativ stylesheet at: http://code.google.com/p/xml2json-xslt/ --> <xsl:template match="/"> {"list":[<xsl:apply-templates select="//row"/>]} </xsl:template> <xsl:template match="row"> {<xsl:apply-templates select="field"/> } <xsl:if test="position() < last()">,</xsl:if> </xsl:template> <xsl:template match="//row/field"> "<xsl:value-of select="@name"/>":"<xsl:call-template name="escapeQuote"/>"<xsl:if test="position() < last()">,</xsl:if> </xsl:template> <!-- as from http://stackoverflow.com/questions/9370633/xslt-replacing-double-quotes-with-escape-sequence --> <xsl:template name="escapeQuote"> <xsl:param name="pText" select="."/> <xsl:if test="string-length($pText) >0"> <xsl:value-of select="substring-before(concat($pText, '"'), '"')"/> <xsl:if test="contains($pText, '"')"> <xsl:text>\"</xsl:text> <xsl:call-template name="escapeQuote"> <xsl:with-param name="pText" select="substring-after($pText, '"')"/> </xsl:call-template> </xsl:if> </xsl:if> </xsl:template> </xsl:stylesheet>
Javascript
Javascriptet er slik:
_index2.js
// images var logoPath="http://www.it.hiof.no/~borres/allround/fotballogos/"; // the XSL-dom we will use to make json var TransDOM; // fast load of xsldom function getXSLDOM(address){ xhttp=new XMLHttpRequest(); xhttp.open("GET",address,false); xhttp.send(""); // report load elt=document.getElementById("dump"); elt.innerHTML=elt.innerHTML+"\n"+address+" : "+xhttp.statusText; if(xhttp.status!=200) return null; return xhttp.responseXML; } // make json-string from an XMLDom with a transfomation (transDOM) function getJsonStringFrom(XMLDom){ if (window.ActiveXObject){// code for IE T=XML_DOM.transformNode(transDOM); return T; //document.getElementById(eltID).innerHTML=ex; } // Mozilla, Firefox, Opera, etc. else if (document.implementation && document.implementation.createDocument){ xsltProcessor=new XSLTProcessor(); try{ xsltProcessor.importStylesheet(transDOM); resultDocument = xsltProcessor.transformToFragment(XML_DOM,document); ser=new XMLSerializer(); // ok since we have "rooted"-doc T=ser.serializeToString(resultDocument); return T; } catch(e){ document.getElementById("dump").innerHTML=e; return e; } } } // load xml from a database extract (sql) // transform it to json and disoplay the result function goGetIt(serie){ var index=serie.selectedIndex; var value=serie.value; var liganame=document.querySelector('select option:nth-child('+(index+1)+')').innerHTML; //alert(""+index+" : "+value+" : "+liganame); var sql="select klubber.Name,klubber.Logo,arenaer.Name as 'ArenaName',arenaer.Wiki as 'ArenaWiki' from klubber "+ "inner join arenaer on klubber.StadionID=arenaer.ID "+ "where SerieID='"+value+"' order by klubber.Name;"; var T=null; $.ajax({ // the python job url:"pykode/select2XML.py", data:"connect=student|student|frigg.hiof.no|bsdiverse&sql="+sql, async:false, success:function(data, textStatus, jqXHR) { temp=jqXHR.responseText; // while debugging:$('#dump1').html(temp); if(temp.indexOf('error')!=0){ XML_DOM=jqXHR.responseXML; T=getJsonStringFrom(XML_DOM); obj=JSON.parse(T); liste=obj.list; S=""+ liste.length+' lag\n\n'; S+='<ul>'; for(var ix=0;ix < liste.length;ix++){ S+='<li class="lstNormal" onclick="hilite('+ix+')">'+liste[ix].Name+'</li>'; } S+='</ul>'; $('#result').html(S); hilite(0); $('#header').html(liganame); } else $('#result').html(T); }, error:function(data) { $('#dump').html(data); } }); } // hilite a selected team function hilite(ix){ //alert(""+ix); S='<img class="logo" src="'+logoPath+liste[ix].Logo+'" alt="'+liste[ix].Logo+'" />'; S+='<div class="name">'+liste[ix].Name+'</div>'; S+='<div class="aname">'+liste[ix].ArenaName+'</div';; $('#hilited').html(S); var elt=document.getElementsByClassName('lstHilited'); if(elt[0]){ elt[0].className='lstNormal'; } elt=document.querySelector('ul li:nth-child('+(ix+1)+')'); if(elt){ elt.className='lstHilited'; } } // when we start, load xslDOM, get selected serie //and make an initial selection function init(){ transDOM=getXSLDOM('pykode/tojson.xsl'); goGetIt(document.forms["form1"].serie); hilite(0); }