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 2

Goals

The goal of this deliverable is:

  • To create a Web application that will let users create tables, link them, and import the SQL statements that are used to create the tables and foreign keys.

Description of the application

Users can click on the 'Create new table' button to create a new table. Upon clicking the button, a form will appear. In this form, users will be able to enter the name of the table and the required attributes for this table. The button 'Add more fields' will allow users to keep adding more attributes for the table. Once the user is satisfied with the table, he/she can click on the 'Create table' button and the graphical presentation (diagram) of the table will appear on the right panel. Clicking on the [X] symbol on the upper right corner of the diagram will allow user to delete this table. Clicking on the [E] symbol will allow user to edit the table name and the attributes of that table.

After creating all the tables, user will be able to link all of the tables. (ie: creating a relationship between them by using foreign key). To do this, user will drag the foreign key field from table 1 to the field in table 2. If the relationship is successfully created, a line will be drawn between the two tables. (Note that we still have not implemented the automatic creation of the foreign key. This means that the user will have to manually create the foreign key field in table 1).

Finally, if the user wants to get the SQL statements, he/she can click on the 'Export table as SQL script' button. Upon clicking that button, all of the SQL statements used to create the tables and relationships will show up on the left panel.

Implementation

For every table that user has created, we will create a Table object that stores the name and attributes of that table. The attributes of a table will also be represented by an Attribute object. Each attribute object will have the field name, field type, field value, the index type (primary key or not), and a value that tells whether the field has auto increment or not. When a user links two tables together, a Relationship object will be created. This object will have the source table, destination table, table 1 foreign key field, and table 2 field.


    // DB class
    function DB(db_name)
    {
      this.db_name = db_name;
      this.tables = [];
      this.relationships = []
    }
    DB.prototype.get_db_name = function()
    {
      return this.db_name;
    }
    DB.prototype.get_tbls_count = function()
    {
      return this.tables.length;
    }

    DB.prototype.add_table = function(table)
    {
      this.tables.push(table);
    }
    DB.prototype.find_table = function(table)
    {
      var result = $.grep(this.tables, function(value)
        { return value.get_table_name() == table; }
      );
      return (result.length == 1 ? result[0] : null);
    }
    DB.prototype.delete_table = function(table)
    {
      this.tables = $.grep(this.tables, function(value) 
      { return value.get_table_name() != table; }
      );
    }
    DB.prototype.set_db_name = function(db_name)
    {
      this.db_name = db_name;
    }
    DB.prototype.get_all_tables = function()
    {
      return this.tables;
    }
    DB.prototype.show_all_tables = function()
    {
      for(j in this.tables)
      {
        alert(this.tables[j].get_table_name());
      }
    }
    DB.prototype.add_relationship = function(relationship)
    {
      this.relationships.push(relationship);
    }
    DB.prototype.get_all_relationships = function()
    {
      return this.relationships;
    }
    // Table class
    function Table(table_name)
    {
      this.table_name = table_name;
      this.attributes = [];
    }
    Table.prototype.get_table_name = function()
    {
      return this.table_name;
    }
    Table.prototype.add_attribute = function(attribute)
    {
      this.attributes.push(attribute);
    }
    Table.prototype.find_attribute = function(attribute)
    {
      var result = $.grep(this.attributes, function(value)
        { return value.get_field_name() == attribute }
       );
      return (result.length == 1 ? result[0] : null);
    }
    Table.prototype.delete_attribute = function(attribute)
    {
      this.attributes = $.grep(this.attributes, function(value) 
       { return value.get_field_name() != attribute }
      );
    }
    Table.prototype.get_all_attributes = function()
    {
      return this.attributes;
    }
    Table.prototype.set_table_name = function(table_name)
    {
      this.table_name = table_name;
    }
    // Attribute class
    function Attribute(field_name,field_type,field_value,index_type,auto_increment)
    {
      this.field_name = field_name;
      this.field_type = field_type;
      this.field_value = field_value;
      this.index_type = index_type;
      this.auto_increment = auto_increment;
    }
    Attribute.prototype.get_field_name = function()
    {
      return this.field_name;
    }
    Attribute.prototype.get_field_type = function()
    {
      return this.field_type;
    }
    Attribute.prototype.get_field_value = function()
    {
      return this.field_value;
    }
    Attribute.prototype.get_index_type = function()
    {
      return this.index_type;
    }
    Attribute.prototype.get_auto_increment = function()
    {
      return this.auto_increment;
    }
    Attribute.prototype.to_string = function()
    {
      return this.field_name + ', '+this.field_type+', '+this.field_value+', '+
                  this.index_type+', '+this.auto_increment;
    }
    Attribute.prototype.set_field_name = function(field_name)
    {
      this.field_name = field_name;
    }
    Attribute.prototype.set_field_type = function(field_type)
    {
      this.field_type = field_type;
    }
    Attribute.prototype.set_field_value = function(field_value)
    {
      this.field_value = field_value;
    }
    Attribute.prototype.set_index_type = function(index_type)
    {
      this.index_type = index_type;
    }
    Attribute.prototype.set_auto_increment = function(auto_increment)
    {
      this.auto_increment = auto_increment;
    }
    // Table relationship object
    function Relationship(tbl_a, tbl_b, fk_a, pk_b)
    {
      this.source_tbl = tbl_a;
      this.dest_tbl = tbl_b;
      this.source_key = fk_a;
      this.dest_key = pk_b;
    }
    Relationship.prototype.get_source_tbl = function()
    {
      return this.source_tbl;
    }
    Relationship.prototype.get_dest_tbl = function()
    {
      return this.dest_tbl;
    }
    Relationship.prototype.get_source_key = function()
    {
      return this.source_key;
    }
    Relationship.prototype.get_dest_key = function()
    {
      return this.dest_key;
    }

  

Snapshots

Creating table
Fig 1: Form to create a table
The graphical presentation of table
Fig 2: The graphical presentation of table
Editing table
Fig 3: Editing table
Deleting table
Fig 4: Deleting table
Importing tables to SQL statements
Fig 5: Importing the tables and relationships to SQL statements

Relevant files