Generating Graphs and Charts from Database Queries using
Generating Graphs and Charts from Database Queries using SVG Graciela Gonzalez Gaurav Dalal Dept. of Computer Science Sam Houston State University Huntsville, TX.
Structure n n n SQL+D Charon Architecture Examples Advantages 2
![SQL+D <query> <SQL Query> [DISPLAY <disp specs>] <disp specs> panel <id>, (<container panels> )*](http://slidetodoc.com/presentation_image_h2/fcd4fae5ff95abe0382b02519fc35bdc/image-3.jpg "SQL+D <query> <SQL Query> [DISPLAY <disp specs>] <disp specs> panel <id>, (<container panels> )*")
SQL+D <query> <SQL Query> [DISPLAY <disp specs>] <disp specs> panel <id>, (<container panels> )* WITH <disp element> <container panels> <panel list> ON <id>. <loc>[(<layout>)] <panel list> panel <id> (, panel <id>)* <loc> North | South | East | West | Center <layout> Horizontal | Vertical | Overlay <disp element> <graph specs> | <chart specs> < chart specs> (Attr. X, Attr. Y) AS <chart elem> ON <id> <graph specs> nodes(Attr 1, Attr 2, . . . , Attrn) AS text ON <id> | edge ( (Attr 1 TO Attr 2)| (Attr 1, Attr 2) ) [LABEL string | LABEL Attrn] ON <id> < chart elem> linechart | barchart | piechart | xyscatter 3
Size n n n 75 North 250 East n 250 400 Center West n Fixed Dimension: 400 x 400 Center: 250 x 250 North & South: 400 x 75 East & West: 75 x 250 Sub Panels 75 400 South 4
Charon n n Web Service Application Accepts Requests having: n n n Database Access Info SQL+D query Queries the Database Builds Multimedia Presentations based on Resultset. Sends Response in the desired output format. (e. g. , SMIL, SVG, other XML formats) 5
Architecture (a) Output Request Client (k) Output Response Database (c) Access Info & SQL Query (d) Results Database Interface External Applicati ons / Web services (h) Data and Formatting Details (i) Results (j) Formatted Output Merger (g) XSLbased stylesheet Output Gateway (f) Display & Output Details Display Generator Web Service (e) Flat XML (b) Access Info & SQL 6
(g) XSL-based stylesheet <? xml version="1. 0"? > <xsl: stylesheet version="1. 0" xmlns: xsl="http: //www. w 3. org/1999/XSL/Transform"> <xsl: template match=". /Results"> <Data> <File> <Name>smil 1. smil</Name> <contents> (i) Results <smil> <svg> <head> <meta content="SMIL" name="title"/> <rect fill="red" <layout> height="140" stroke="black" width="12" x="25" y="110. 0000" /> <root-layout height="400"stroke="black" width="400"/> width="12" x="50" y="125. 0000" /> <rect fill="red" height="125" <region height="400" id="main" width="400"/> (j) Formatted Output (f) Display & Output Details Client </layout> <Data> <text style="font-size: 12; stroke: black; writing-mode: tb; " x="25" y="260">Jan</text> DISPLAY PANEL main WITH (month, total) AS </head> <File> Architecture Web Service <text style="font-size: 12; stroke: black; writing-mode: tb; " x="50" y="260">Feb</text> barchart ON main. Center <Name>smil 1. smil</Name> <body> (k) Output <contents> <line<seq> stroke="black" x 1="12" x 2="12" y 1="12" y 2="250"/> Response (a)<line Output Request <barchart datafield="Sales. Amount" size="300" textfield="Sales. Month"/> <smil> stroke="black" x 1="12" x 2="250" y 1="250" y 2="250"/> </seq> <SQLD> <head> </body> <Connection> (e) Flat XML <meta content="SMIL" name="title"/> <line stroke="black" x 1="30" x 2="55" y 1="235" y 2="210" /> (c) Access </smil>. <url>jdbc: odbc: sales</url> <Results> Info &x 1="55" SQL <line stroke="black" x 2="80" y 1="210" y 2="225" /> </contents>. <username>gaurav</username> Output Query </File> Database </body> <password>12345</password> <Record Index="1"> </Data> Gateway <text style="font-size: 8; stroke: black; " x="0" y="250">0</text> Interface </smil> </Connection> </xsl: template> <Sales. Month>Jan</Sales. Month> (d) Results <text style="font-size: 8; stroke: black; " x="0" y="225">25</text> </xsl: stylesheet> </contents> <Query> <Sales. Amount>11000</Sales. Amount> <File> SELECT Sales. Month, </svg> Sales. Amount(b) FROM Product </Record> Access Info & SQL (h) JOIN Data and Formatting Details </File> INNER Sales ON Product. ID = Sales. Product. ID <Query> Barchart "Sales. Month", "Sales. Amount") <Name>bar 1. svg</Name> AND ("300", year = ‘ 2003’ AND Product. ID='3' DISPLAY PANEL main <Record Index="2"> <url>jdbc: odbc: movie</url> <contents> WITH (month, total) AS barchart ON main. Center; <Sales. Month>Feb</Sales. Month> <username>gaurav</username> <svg> </Query><password>12345</password> <Sales. Amount>55555</Sales. Amount> External Display Merger <rect fill="red" height="140" stroke="black" <Output> </Record> Applicati <SQL>SELECT Sales. Month, Sales. Amount FROM Product INNER JOIN Sales Generator width="12" ON x="25"Product. ID y="110. 0000"/> <Format>SMIL</SMIL> ons / = Sales. Product. ID AND year = ‘ 2003’ AND. <Size> </Results> Product. ID='3'; Web. <Width>400</Width> services </SQL> </svg><Height>400</Height> </Query> </contents> </Size> </File> </Output> </Data> </SQLD> 7
Examples Charts Passing the attributes of the resultset as input to a external XSLT application. n Graphs Running an external application to get the SVG image from SQL+D. n 8
Bar Charts n Required Inputs n n n Required Size (in pixels) Text Field Data Field (s) 9
Bar Charts-Query n n SALES (month, year, value) SELECT * FROM SALES WHERE year = ‘ 2003’ DISPLAY PANEL main WITH (month, total AS month total) barchart ON main. Center. 10
Bar Charts-Algorithm 1. 2. 3. 4. 5. Generate the X-axis and Y-axis given the current size. X-offset = Size / 2 * # of Records Y-offset = Max value / Size Generate each bar using the 'rect' element. Generate the text labels for each bar. • <rect • fill = “red” • stroke = “black” • width= “ 12. 5” • x = “ 25” • height = “ 140” • y=“ 110. 0000” /> y. Height X == Size 2 *==X-offset */ 2 position X-value offset • Width X-offset Y-offset =Height 250/ Y-offset 25000 /*250 10 == 12. 5 100 11
Line Charts n Required Inputs n n n Required Size (in pixels) Text Field Data Field (s) 12
Line Charts - Query n n PRICES (date, high, low, close). SELECT * FROM PRICES WHERE DATE => "05/28/2003" AND DATE <= "05/02/2003" DISPLAY PANEL main WITH date high, low, close) (date, close AS linechart ON main. Center 13
Line Charts - Algorithm 1. 2. 3. 4. 5. 6. Generate the X-axis and Y-axis given the current size. X-offset = Size / # of Records Y-offset = Max value / Size. For each data line, Generate each line using the 'line' element Generate the text label for each line. Generate the legend. • <line • stroke = “black” • x 2 = “ 50” • x 1 = “ 0” • y 1 = “ 235” • y 2 =“ 210” /> • X 2 Y-offset ==25000 250 Y 2 = X-offset Next Value /=Y-offset */ /5 position Y 1 X 1 Value / Y-offset (position • X-offset 250 50= 100 -1) 14
Pie Charts n Required Inputs n n n Required Size (in pixels) Text Field Data Field 15
Pie Charts - Query n n OCCUPATION (state, occupation, population). SELECT OCCUPATION. occupation , OCCUPATION. population FROM OCCUPATION WHERE OCCUPATION. state = "California" occupation DISPLAY PANEL main WITH (occupation, population) AS piechart ON main. Center population 16
![Pie Charts - Algorithm x = [cos (angle expressed in radians)-1]*size = [0. 866](http://slidetodoc.com/presentation_image_h2/fcd4fae5ff95abe0382b02519fc35bdc/image-17.jpg "Pie Charts - Algorithm x = [cos (angle expressed in radians)-1]*size = [0. 866")
Pie Charts - Algorithm x = [cos (angle expressed in radians)-1]*size = [0. 866 -1]*100 = -13. 39 • <path d=“ + ∏ • M 0, 0 ∏/12 radians ∏/6 radians /6 1. Ratio = ∑values * ∏/ • L 100, 0 180 • a 100, 0, 1 slice, 2. For 0, each ra di an s • -13. 39 + y = sin (angle expressed in radians)*size = 0. 5 * 100 = 50 • 50 • z“ 1. 2. Calculate the angle of each slice using the ratio. Generate each slice using the ‘path' element. 3. Generate the legend. • style="fill: blue“ • transform="rotate(60)“ /> 17
Graphs n n Nodes & Edges are defined using the attributes of the resultset. Created using a graph-generating application called Graph. Viz ©. 18
Graphs - Query 19
Graphs - Query SELECT REQ. Class. ID, PREREQ. prereq FROM PREREQ, REQ WHERE REQ. major = “BSCS” AND REQ. Class. ID = PREREQ. Class. ID DISPLAY PANEL main WITH Nodes (REQ. Class. ID, PREREQ. prereq) AS boxedtext ON main. Center, Edge (PREREQ. prereq TO REQ. classid) ON main. Center 20
Graphs -Algorithm Generate an XSL stylesheet which generates the dot format. 1. Nodes: a) i. ii. b) Tagged by a unique integer (i. e. , the row number) Labeled by the value of the data. Edges: Pair of Nodes identified through the tag numbers 21
Graphs - Query DISPLAY PANEL main WITH 2. Nodes Generating the. PREREQ. prereq) dot file. (REQ. classid, digraph GON { main. Center, AS boxedtext Edge(PREREQ. prereq TO REQ. classid) ON 1 -> 3; main. Center 2 -> 3; 1[label="MTH 171"] 2[label="CS 164"] 3[label="CS 165"] } 22
Graphs - Algorithm 3. Run the program through the Merger subcomponent using the dot file. 23
Graphs - Output 24
Advantages n n Simple to Learn Dynamic Code Generation Extensible High Level Of Implementation 25
Thank You
- Slides: 26