1
|
- Week 10
- LBSC 690
- Information Technology
|
2
|
- Questions
- PHP
- Joomla!
- Mythical person-month
- Advanced Database topics
|
3
|
- Joins are automatic if field names are same
- Otherwise, drag a line between the fields
- Sort order is easy to specify
- Queries form the basis for reports
- Reports give good control over layout
- Use the report wizard - the formats are complex
- Forms manage input better than raw tables
- Invalid data can be identified when input
- Graphics can be incorporated
|
4
|
- Attention to detail!
- Careful where you place that comma, semi-colon, etc.
- Write a little bit of code at a time
- Add some functionality, make sure it works, move on
- Don’t try to write a large program all at once
- Debug by viewing the “state” of your program
- Print values of variables using document.write
- Is the value what you expected?
|
5
|
- Google “javascript”
- Tutorials: to learn to write programs
- Code: to do things you want to do
- Engineering and Physical Sciences Library
|
6
|
- Static: Written in a markup language
- Dynamic: Generated using a program
- Common Gateway Interface [Perl] (.cgi)
- Java servlets
- Dynamic: Generated from a database
- Cold Fusion (.cfm)
- PHP (.php)
|
7
|
- Remote access to a database
- Client does not need the database software
- Serve rapidly changing information
- e.g., Airline reservation systems
- Provide multiple “access points”
- By subject, by date, by author, …
- Record user responses in the database
|
8
|
- Benefits
- Multiple views
- Data reuse
- Scalable
- Access control
- Costs
- Formal modeling
- Complex (learn, design, implement, debug)
- Brittle (relies on multiple communicating servers)
- Not crawlable
|
9
|
- Brittle
- Depends on multiple servers
- Complex
- Learning, design, implementation, debugging
- Formally modeled
|
10
|
|
11
|
- Displays database content on Web pages
- Not very useful for changing database content
- Drag-and-drop design in Microsoft Access
- “Reports” are designed for printing
- “Pages” are designed for the Web
- Requirements:
- Microsoft Web Server (not Apache)
- IE 5 or higher Web browser (not Firefox)
- “Office Web Components” on client machine
- IE 7 fails gracelessly without them!
|
12
|
|
13
|
|
14
|
- You need three systems on the same server:
- PHP (programming language)
- MySQL (DBMS)
- Apache (Web server)
- XAMPP Server
- OTAL (Sun Unix) supports Web deployment
- Requires a text editor (e.g., emacs) or FTP
|
15
|
- ----- HTML stuff -----
- <?php
- ----- PHP stuff -----
- ?>
- ----- HTML stuff -----
- http://---URL stuff---/xxxxx.php
|
16
|
- Download and install XAMPP
- http://www.apachefriends.org/en/xampp.html
- Check to install Apache and MySQL as services
- Check the Web server at http://localhost/
- Download and install Joomla!
- http://www.joomla.org
- Unzip to c:\xampp\htdocs\joomla15
- Configure it at http://localhost/joomla15/
- Host Name: localhost
- User Name: root
- Database Name: joomla15
- DO NOT install sample data
- Delete c:\xampp\htdocs\joomla15\installation
|
17
|
- On XAMPP:
- $dbc=3Dmysql_connect (‘localhost’, ‘userid’,
‘password’);
- On OTAL:
- $dbc=3Dmysql_connect(‘:/export/software/otal/mysql/run/mysqld.=
sock’,
- ‘userid’, ‘password’);
|
18
|
- “root” user creates database + grants permissions
- Using the XAMPP console (or mysql –u root –p)
- root has no initial password; just hit <enter> when asked
- By the system administrator on OTAL (otal.umd.edu)
-
CREATE DATABASE project;
-
GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, =
DROP
ON project.* TO ‘foo’@’localhost’ IDENTIFIE=
D BY
‘bar’;
-
FLUSH PRIVILEGES;
- Start mysql
- MySQL console for XAMPP, ssh for OTAL
- &nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p;
mysql –u foo –p bar
- Connect to your database
|
19
|
- CREATE TABLE contacts (
- ckey MEDIUMINT UNSIGN=
ED NOT
NULL AUTO_INCREMENT,
- id MEDIUMINT UNSIGNE=
D NOT
NULL,
- ctype SMALLINT UNSIGN=
ED NOT
NULL,
- cstring VARCHAR(40) N=
OT
NULL,
- FOREIGN KEY (id) REFE=
RENCES
persons(id) ON DELETE CASCADE,
- FOREIGN KEY (ctype)
REFERENCES ctlabels(ctype) ON DELETE RESTRICT,
- PRIMARY KEY (ckey)
- ) ENGINE=3DINNODB;
- To delete: DROP TABLE contacts;
|
20
|
- INSERT INTO ctlabels
- (string) VALUES
- ('primary email'),
- ('alternate email'),<=
/li>
- ('home phone'),
- ('cell phone'),
- ('work phone'),
- ('AOL IM'),
- ('Yahoo Chat'),
- ('MSN Messenger'),
- (‘other’)=
;
- To empty a table: DELETE FROM ctlabels;
|
21
|
- SHOW DATABASES;
- SHOW TABLES;
- DESCRIBE tablename;
- SELECT * FROM tablename;
|
22
|
|
23
|
|
24
|
- SELECT Company.CompanyName, Company.CompanyPhone, Flight.Origin,
Flight.DepartureTime
- FROM Flight,Company
- WHERE Flight.CompanyName=3DCompany.CompanyName
- AND
Flight.AvailableSeats>3;
|
25
|
- Sequential
- {…; …;…;}
- Semicolons are required at the end of every statement
- Conditional
- if (3=3D=3Di) {…} else {…}
- Loop
- for ($i=3D0; $i<10; $i++) {…}
- while ($row=3Dmysql_fetch_array(…)) {…}
- foreach ($array as $key =3D> $value) {…}
- Braces are optional around a single statement
|
26
|
- Name starts with a $
- Case sensitive (assume everything could be!)
- Hold a value
- Number (integer, float)
- String (double quotes, \ escape character)
- TRUE, FLASE
- NULL
- Need not be declared (automatically “cast”)
|
27
|
- Arithmetic operators
- Logical operators
- < <=3D =3D=3D !=3D >=3D > && || !
- String operator
|
28
|
- A set of key-element pairs
- $days =3D array(“Jan”->31, “Feb”=3D>2=
8,
…);
- $months =3D explode(“/”,
“Jan/Feb/Mar/…/Dec”);
- $_POST
- Each element is accessed by the key
- {$days[“Jan”]}
- $months[0];
|
29
|
- Declaration
- function multiply($a, $b=3D3){return $a*$b;}
- Invoking a method
- All variables in a function have only local scope
- Unless declared as global in the function
|
30
|
- <form action=3D“formResponseDemo.php”,
method=3D“post”>
- email: <input type=3D“text”, name=3D“emailR=
21;, value=3D“<?php
echo $email ?>”, size=3D30 />
- <input type=3D“radio”, name=3D“sure”,
value=3D“yes” /> Yes
- <input type=3D“radio”, name=3D“sure”,
value=3D“no” /> No
- <input type=3D“submit”, name=3D“submit”,
value=3D“Submit” />
- <input type=3D“hidden”, name=3D“submitted̶=
1;,
value=3D“TRUE” />
- </form>
- if (isset($_POST[“submitted”])) {
- echo “Your email address is $email.”;
- } else {
- echo “Error: page reached without proper form submission!R=
21;;
- }
|
31
|
|
32
|
|
33
|
|
34
|
|
35
|
- Major divisions
- Content
- Navigation
|
36
|
- Why is software development different from manufacturing car?
- If it would take one person three months, why does it take four peop=
le
SIX months?
|
37
|
- Rules of thumb
- 1/3 specification
- 1/6 coding
- 1/2 test planning, testing, and fixing!
- Add time for coding to learn as you go, but don’t take time aw=
ay
from the other parts!
- Reread the section on “gutless estimating” if you are
tempted
|
38
|
|
39
|
|
40
|
|
41
|
- Google Maps
- Google Suggest
- http://www.google.com/webhp?complete=3D1&hl=3Den
- Sajax Tables
- http://labs.revision10.com/?p=3D5
- Sajax
- http://www.modernmethod.com/sajax/
|
42
|
- Sequential constraints
- Communication
- Training
|
43
|
- Some typical database applications:
- Banking (e.g., saving/checking accounts)
- Trading (e.g., stocks)
- Airline reservations
- Characteristics:
- Lots of data
- Lots of concurrent access
- Must have fast access
- “Mission critical”
|
44
|
|
45
|
- Registrar database must be internally consistent
- Enrolled students must have an entry in student table
- Courses must have a name
- What happens:
- When a student withdraws from the university?
- When a course is taken off the books?
|
46
|
- Conditions that must always be true
- Specified when the database is designed
- Checked when the database is modified
- RDBMS ensures integrity constraints are respected
- So database contents remain faithful to real world
- Helps avoid data entry errors
|
47
|
- Foreign key values must exist in other table
- If not, those records cannot be joined
- Can be enforced when data is added
- Associate a primary key with each foreign key
- Helps avoid erroneous data
- Only need to ensure data quality for primary keys
|
48
|
- Thought experiment: You and your project partner are editing the same
file…
- Scenario 1: you both save it at the same time
- Scenario 2: you save first, but before it’s done saving, your
partner saves
|
49
|
- Possible actions on a checking account
- Deposit check (read balance, write new balance)
- Cash check (read balance, write new balance)
- Scenario:
- Current balance: $500
- You try to deposit a $50 check and someone tries to cash a $100 che=
ck
at the same time
- Possible sequences: (what happens in each case?)
|
50
|
- Transaction: sequence of grouped database actions
- e.g., transfer $500 from checking to savings
- “ACID” properties
- Atomicity
- Consistency
- Each transaction must take the DB between consistent states.
- Isolation:
- Concurrent transactions must appear to run in isolation
- Durability
- Results of transactions must survive even if systems crash
|
51
|
- Idea: keep a log (history) of all actions carried out while executing
transactions
- Before a change is made to the database, the corresponding log entr=
y is
forced to a safe location
- Recovering from a crash:
- Effects of partially executed transactions are undone
- Effects of committed transactions are redone
|
52
|
- On a sheet of paper, answer the following (ungraded) question (no
names, please):
- What was the muddiest point in today’s class?
|