Web Application Development

Tools to create a simple web-editable database

• QSEE

• MySQL (or PHPMyAdmin)

• PHP

• TableEditor

Define ER model in QSEE

Generate SQL

Create DatabasemysQL

Write Script to use TableEditor

QSEE

• Multi-case diagramming tool– All UML diagrams– ++

• Entity-Relationship Diagrams– Generates SQL for various targets (including MySQL 4.0)– Implements relationships automatically

• 1- many : adds primary key on 1-side as columns in the many side to make foreign keys

• Many-many : adds an new link table with the primary keys from both sides as foreign keys (and a joint primary key)

• Dependent (weak) entities : foreign key becomes part of the primary key

– Sets up the appropriate integrity constraints• Action on delete and update for foreign keys

simple

bookISBN:CHARACTERTitle:VARCHARprice:DECIMAL

PHPMyAdmin

• GUI interface to MySQL server (shares)• GUI runs on stocks (the PHP server)• Full access to a database for

– Creating tables– Added data– Browsing the data– Import and Export of tables…– Execute SQL queries (DML) or definitions (DML)

• But ..

Command line MySQL

• Generated SQL comments are rejected by PHPMyAdmin

• login to shares directly using putty

• change directory to your project directory

• Enter:mysql –p

use database

source proj.sql

Editing a Table

• Table editor has to support the functions of:– Browsing the table– Inserting a new row (with validation and

defaults)– Deleting a row– Editing a row– Copying (or cloning) an existing row

Common Pattern

• Could write the script each time by hand• Could write a single ‘data-driven’ class

– Class must be able to read the table definition from the database (DESCRIBE command)

– Class must allow user to control the options offered

• Some open-source code exists– Here is one by Richard Heyes– http://www.phpguru.org/static/TableEditor.html

Main script for editor<?php

require_once('db.inc');require_once('TableEditor.php');

$editor = new TableEditor($dblink, 'Book');$editor->setConfig('allowPKEditing', true); $editor->setConfig('allowASearch', false);

$editor->setConfig('perPage', 5);$editor->setConfig('title', 'Edit Books');$editor->setRequiredFields('ISBN', 'Title');

$editor->display();

?>

Next weeks workshop

• Develop a simple table editor using these tools

More on SQL generationEntity Relationship Diagram

DepartmentDeptId:INTEGERDeptName:VARCHAR

EmployeeEmpId:INTEGEREmpSSN:CHARACTER

NonExemptEmployeeEmpHourlyRate:NUMERICEmpOvertimeRate:NUMERIC

ExemptEmployeeEmpMonthlyRate:NUMERICEmpVacationWeeks:INTEGER

UnionUnionId:INTEGERUnionName:VARCHAR

manages

manager

friend