Chris Pollett > Students >
Sugi
    ( Print View )

    [Bio]

    [Project Blog]

    [CS 297 Proposal]

    [Intro to jQuery and YUI - PDF]

    [Deliverable 1]

    [Deliverable 2]

    [Deliverable 3]

    [Deliverable 4]

    [CS 297 Report - PDF]

    [CS 298 Proposal]

    [CS 298 Report - PDF]

    [CS 298 Slides - PPT]

    [CS 298 Codes - Zip] 

                          

























Deliverable 3

Goals

The goal of this deliverable is:

  • To create a Web application that will allow users to link two or more tables and to construct SQL queries by using QBE.

Description of the application

In this application, we are creating three static tables 'Employee', 'Department', and 'Building.
Users will be able to link these three tables. Once two tables are linked together, their attributes will show up in the QBE form in the middle panel.
Using the QBE form, users will be able to construct SQL queries by specifying:
  • The alias for each attribute.
  • Whether to show a certain attribute or not
  • The sorting by certain attribute (either descending or ascending)
  • Specifying the condition for each attribute
Users will be able to grab the SQL queries from the bottom panel.

Implementation

  
    var db = new DB();
    var defaultStroke = 1;
    var defaultLineColor = '#000000';
    var unique_tables = [];
    var number_comp = ['>=', '=', '<=', '!='];
    var char_comp = ['!=', '='];

    function drawLineBetweenBox(div1, div2)
    {
      var pos1 = $('#'+div1).offset();  // Position relative to the document
      var pos2 = $('#'+div2).offset();
      var height1 = $('#'+div1).height();
      var height2 = $('#'+div2).height();
      var width1 = $('#'+div1).width();
      var center1x = pos1.left + width1;
      var center1y = pos1.top + (height1 / 2);
      var center2x = pos2.left;
      var center2y = pos2.top + (height2 / 2);
      var js = new jsGraphics('canvas');
      js.setColor(defaultLineColor);
      js.setStroke(defaultStroke);
      js.drawLine(center1x,center1y,center2x,center2y);
      js.paint();
    }

    function init()
    {
      var emp_tbl = new Table('Employee');
      var emp_tbl_id = new Attribute('id', 'int', '', 1, 1);
      var emp_tbl_name = new Attribute('empName', 'varchar', 255, 0, 0);
      var emp_tbl_deptId = new Attribute('empDeptId', 'int', '', 0, 0);
      emp_tbl.add_attribute(emp_tbl_id);
      emp_tbl.add_attribute(emp_tbl_name);
      emp_tbl.add_attribute(emp_tbl_deptId);

      var dept_tbl = new Table('Department');
      var dept_tbl_id = new Attribute('id', 'int', '', 1, 1);
      var dept_tbl_name = new Attribute('deptName', 'varchar', 255, 0, 0);
      var dept_tbl_buildingId = new Attribute('deptBuildingId', 'int', '', 0, 0);
      dept_tbl.add_attribute(dept_tbl_id);
      dept_tbl.add_attribute(dept_tbl_name);
      dept_tbl.add_attribute(dept_tbl_buildingId);

      var bld_tbl = new Table('Building');
      var bld_tbl_id = new Attribute('id', 'int', '', 1, 1);
      var bld_tbl_name = new Attribute('buildingName', 'varchar', 255, 0, 0);
      bld_tbl.add_attribute(bld_tbl_id);
      bld_tbl.add_attribute(bld_tbl_name);

      db.add_table(emp_tbl);
      db.add_table(dept_tbl);
      db.add_table(bld_tbl);

      //var emp_dept_rel = new Relationship(emp_tbl, dept_tbl, 'empDeptId', 'id');
      //var dept_build_rel = new Relationship(dept_tbl, bld_tbl, 'empBuildingId', 'id');
    }

    function draw_tbls()
    {
      var all_tables = db.get_all_tables();
      for(t in all_tables)
      {
        var this_table = all_tables[t];
        var tbl_name =  this_table.get_table_name();
        var new_tbl_div = '<div class="db_tbl" id="'+tbl_name+'_tbl"><div class="db_tbl_name"><span id="'+
          tbl_name+'_tbl_name">'+tbl_name+
          '</span><div class="clear"></div></div><div class="db_attrs">';
        var all_attributes = this_table.get_all_attributes();
        for(a in all_attributes)
        {
          var this_attribute = all_attributes[a];
          var field_name = this_attribute.get_field_name();
          var is_pri_key = this_attribute.get_index_type();
          var pri_key_class = ' db_attrs_row_pri_key';
          if(is_pri_key == 0)
            pri_key_class = '';
          new_tbl_div += '<div class="db_attrs_row'+pri_key_class+'" id="'+
            tbl_name+'_tbl_'+field_name+'">'+field_name+'</div>';
        }
        new_tbl_div += '</div>';
        $('#tbls_diagram').append(new_tbl_div);
        $('#'+tbl_name+'_tbl').draggable({ containment: 'parent' }).resizable();
        $('.db_attrs_row').draggable({ helper:'clone'}).droppable({
          drop: function(ui, event)
          {
            var source = $(event.draggable).clone().attr("id");
            var dest = $(this).attr("id");
            var source_temp = source.split("_");
            var dest_temp = dest.split("_");
            drawLineBetweenBox(source, dest);
            var rel = new Relationship(source_temp[0], dest_temp[0], source_temp[2], dest_temp[2]);
            db.add_relationship(rel);
            construct_qbe_form();
            $('#sql_dump').append('Successfully linking table '+source_temp[0]+' to table '+dest_temp[0]+'<br />');
          }});
      }
    }

    function construct_qbe_form()
    {
      var rels = db.get_all_relationships();
      if(rels.length == 0) return;
      unique_tables = [];
      for(r in rels)
      {
        var from = rels[r].get_source_tbl();
        var to = rels[r].get_dest_tbl();
        if($.inArray(from, unique_tables) == -1)
          unique_tables.push(from);
          if($.inArray(to, unique_tables) == -1)
            unique_tables.push(to);
      }
      var qbe_form_tbl = '<table border="1"><tr id="tbl_name"><th>Table</th></tr><tr 
        id="field_name"><th>Field</th></tr><tr id="field_alias"><th>
        Alias</th></tr><tr id="field_show"><th>Show</th></tr><
        tr id="field_sort"><th>SORT</th></tr><tr id="field_criteria"><th>Criteria</th></tr></table>';
      $('#qbe_form_tbl').html(qbe_form_tbl);
      for(tbl in unique_tables)
      {
        var this_tbl = db.find_table(unique_tables[tbl]);
        var this_tbl_name = this_tbl.get_table_name();
        var this_tbl_attrs = this_tbl.get_all_attributes();
        for(attr in this_tbl_attrs)
        {
          var this_attr = this_tbl_attrs[attr];
          $('#qbe_form_tbl #tbl_name').append('<td id="tbl_'+this_tbl_name+'_'+attr+'">'+this_tbl_name+'</td>');
          var attr_name = this_attr.get_field_name();
          var attr_type = this_attr.get_field_type();
          $('#qbe_form_tbl #field_name').append('<td id="'+this_tbl_name+'_'+attr_name+'">'+attr_name+'</td>');
          $('#qbe_form_tbl #field_alias').append('<td><input type="text" id="'+
            this_tbl_name+'_'+attr_name+'_alias" /></td>');
          $('#qbe_form_tbl #field_show').append('<td><input type="checkbox" id="'+
            this_tbl_name+'_'+attr_name+'_Show" /></td>');
          $('#qbe_form_tbl #field_sort').append('<td><select id="'+this_tbl_name+'_'+
            attr_name+'_Sort"><option value="">—</option><
            option value="ASC">ascending</option><option value="DESC">descending</option></select></td>');
          var comp_select = '<select id="'+this_tbl_name+'_'+attr_name+'_Comp_Criteria">';
          if(attr_type == 'char' || attr_type == 'varchar')
          {
            for(comp in char_comp)
            {
              comp_select += '<option value="'+char_comp[comp]+'">'+char_comp[comp]+'</option>';
            }
          }
          else
          {
            for(comp in number_comp)
            {
              comp_select += '<option value="'+number_comp[comp]+'">'+number_comp[comp]+'</option>';
            }
          }
          comp_select += '</select>';
          $('#qbe_form_tbl #field_criteria').append('<td>'+comp_select+' <input type="text" size="10" id="'
            +this_tbl_name+'_'+attr_name+'_Criteria" /></td>');
        }
      }
    }

    function create_sql_query()
    {
      var rels = db.get_all_relationships();
      if(rels.length == 0)
      {
        alert('Please link at least two tables first!');
        return;
      }
      var show_fields = [];
      $('#field_show').children('td').each(function(index)
      {
        $(this).children('input').each(function(iindex)
        {
          var this_input = $(this).attr("id");
          var this_input_checked = $(this).attr("checked");
          if(this_input_checked)
          {
            var splitter = this_input.split('_');
            var this_column = splitter[0]+'.'+splitter[1];
            var this_column_alias = $('#'+splitter[0]+'_'+splitter[1]+'_alias').val();
            if(this_column_alias != '')
              this_column += ' as '+this_column_alias;
            show_fields.push(this_column);  
          }
        });
      });
      if(show_fields.length == 0)
        alert("Please select at least 1 field to display");
      else
      {
        var all_rels = [];
        for(r in rels)
        {
          var this_rel = rels[r];
          var this_source_tbl = this_rel.get_source_tbl();
          var this_dest_tbl = this_rel.get_dest_tbl();
          var this_source_key = this_rel.get_source_key();
          var this_dest_key = this_rel.get_dest_key();
          if(r > 0)
            all_rels += ' join '+this_dest_tbl+" on "+this_source_tbl+'.'+this_source_key+
              ' = '+this_dest_tbl+'.'+this_dest_key;
          else
            all_rels = this_source_tbl+' join '+this_dest_tbl+" on "+this_source_tbl+
              '.'+this_source_key+' = '+this_dest_tbl+'.'+this_dest_key;
        }
        var sql_query = 'select ';
        sql_query += show_fields.join(', ') +' from '+all_rels;
        var criteria_fields = [];
        $('#field_criteria').children('td').each(function(index)
        {
          var this_comp = $($(this).children()[0]);
          var this_criteria = $($(this).children()[1]);
          var this_input_val = this_criteria.val();
          if(this_input_val != '')
          {
            var this_comp_val = this_comp.val();
            var splitter = this_criteria.attr("id").split('_');
            var this_attribute_type = db.find_table(splitter[0]).find_attribute(splitter[1]).get_field_type();
            if(this_attribute_type == 'char' || this_attribute_type == 'varchar')
              this_input_val = "'"+this_input_val+"'";
            criteria_fields.push(splitter[0]+'.'+splitter[1]+' '+this_comp_val+' '+this_input_val);
          }
        });
        if(criteria_fields.length > 0)
          sql_query += ' where '+criteria_fields.join(' and ')+' ';
        else
          sql_query += ' ';
        var sort_fields = [];
        $('#field_sort').children('td').each(function(index)
        {
          $(this).children('select').each(function(iindex)
          {
            var this_input = $(this).attr("id");
            var this_input_val = $(this).val();
            if(this_input_val != '')
            {
              var splitter = this_input.split('_');
              sort_fields.push(splitter[0]+'.'+splitter[1]+' '+this_input_val);
            }
          });
        });
        if(sort_fields.length > 0)
          sql_query += ' order by '+sort_fields.join(', ');
      }
      $('#sql_dump').append(sql_query+'<br />');
      return false;
    }

    $(document).ready(function()
    {
      init();
      draw_tbls();
      $('#b1').bind('click', function() { create_sql_query(); });
    });

Snapshots

QBE form
Fig 1: The QBE form and the resulting SQL queries

Relevant files