MySQL DB

Ontario Open Data

Application

Use table Import tool to
create table and
get create statement

simplr codes to finishdownload
and db connection

adjust data type on each column

build a complete insert statement
successfully save a row

build a complete insert statement
successfully save a rowfor special
char "'". use
escape to deal with "'" as value

write for loop to try to insert
a few row

use try / catch to process error
and let process continue

use function to get maximum row
and insert complete data

Schedule

find out app executable and
run code

create a batch file to wrap up
command line, try run it.

add batch as action in windoes
scheduler

make sure worl.city has all the
cities in main table. will use city id
to replace city name

setup definition table structure for
professor, program, project, university..?

create function get current and
customized table name

export definition data from main table and
import into definition table, make sure AI
ID created

import IDs from definition tables into main
table. remove unnecessary columns, setup f
oreign keys

record all above scripts and creat stored procesures,
use a mian procedure to call those step procedure accordingly

use a table with current timestamp
to create a backup table, then backup
main table. This proccess sgould start a
s soon as the application finish inserting

Middle tier
- Web server

JDBC installation

create jdbc connection
library, with user-defined exception.
xport to jar

create xml service / json service

create JSP client

Front end
- Web report page

HTML basics

google chart library

Main page / iframe

ajax partial page refresh

AngularJS client

bootstrap library

learn to use dynamic SQL in mysql workbench
editor window and in udf / sp