PART V: Databases and Networks

In this section I will cover:

Databasics: The exam covers databases at an abstract level. No specific implementation
SQL-92 standards only
Only the basics of database design and programming are actually required
Table creation/population/manipulation, Data extraction, Reference integrity
Joins / Grouping / Aggregates
Relational databases: Called because the relationship among different entities is its foundation
• Schemas/databases
• Tables
• Rows
Data types
• Int
• Float
• Char/varchar
• BlOBs
Indices: Indices organize data, Useful to enforce integrity
Essential to performance
Indices can be created on one or more columns
More rows == bigger index
Columns that are part of indices are called keys
Indices can be of two types: unique or not unique
Unique indices make it possible to ensure that no two combination of the same keys exist in the table. Non-unique indices simply speed up the retrieval of
information
Creating Schemas and Tables: Schemas are created with CREATE DATABASE:
CREATE DATABASE database_name
Tables are created with CREATE TABLE:
CREATE TABLE table_name (
column1 column1_type,
...)
Table names are unique. This is true on a per-schema basis
Each table must contain at least one column. Most DBMSs implement some sort of limits to the size of a row, but that is not part of the standard
Creating Indices: Indices are created using CREATE INDEX:
CREATE [UNIQUE] INDEX index_name (
column1,
...)
Index names must be unique, On a per-schema basis
Primary keys are special unique indices that indicate the “primary” method of accessing a table. There can only be one primary key per table. Generally, the primary key indicates the way the data is physically sorted in storage
Creating Good Indices: A good index provides maximum performance at minimum cost. Create only indices that reflect database usage. Implement the minimum number of columns per index. Create as few indices as possible
Many DBMSs can only use one index per query. Make sure you understand how your DBMS uses indices Analyze, analyze, analyze. Continue analyzing once you’re done!
Foreign Keys: A foreign key establishes a relationship between two tables:
CREATE TABLE A (ID INT NOT NULL PRIMARY KEY)
CREATE TABLE B (A_ID INT NOT NULL REFERENCES A(ID))
Foreign keys enforce referential integrity. They ensure that you cannot add rows to table B with values for A_ID that do not exist in table A. It also ensures that you cannot delete from table A if there are TABLE B rows that still reference it
Some DBMSs do not support foreign keys. Notably, MySQL until version 5.0
Inserting, Updating and Deleting: Rows are inserted in a table using the INSERT INTO statement:
INSERT INTO TABLE A (ID) VALUES (123)
INSERT INTO TABLE A VALUES (123)
Updates are performed using UPDATE:
UPDATE A SET ID = 124
Deletions are performed using DELETE:
DELETE FROM A
Both additions and deletion can be limited by a WHERE clause:
UPDATE A SET ID = 124 WHERE ID = 123
Retrieving Data: Data is retrieved using the SELECT FROM statement:
SELECT * FROM A
SELECT ID FROM A
SELECT statements can also be limited by a WHERE clause
SELECT * FROM A WHERE ID = 123
SELECT ID FROM A WHERE ID = 123
Where clauses are what makes indices so important
Joins: A join makes it possible to... join together the results from two tables:
SELECT * FROM A INNER JOIN B ON A.ID = B.A_ID
Inner Joins require that both tables return rows for a particular set of keys
Outer Joins require that either table return rows for a particular set of keys
SELECT * FROM A LEFT JOIN B
ON A.ID = B.A_ID
SELECT A.ID, B.* FROM A RIGHT JOIN B
ON A.ID = B.A_ID

Joins don’t always work the way you expect them to
SELECT * FROM A INNER JOIN B
WHERE A.ID <> B.A_ID
This won’t return a list of the rows that A and B do not have in common
It will return a list of all the rows that each row of A does not have in common with B!
Joins also rely on indices
Joins can be stacked, and they are executed from left to right
Grouping and Aggregates: The GROUP BY clause can be used to group return sets according to one or more columns:
SELECT A_ID FROM B GROUP BY A_ID
Grouped result sets can then be used with aggregates to perform statistical analysis on data:
SELECT A_ID, COUNT(A_ID) FROM B GROUP BY A_ID
When using GROUP BY, only aggregates and columns that appear in the GROUP BY clause can be extracted. This is the standard, but it’s not always respect (notably by MySQL)
Aggregates: Sum of all rows
SUM(column_name)
Count of rows returned
COUNT(column_name)
COUNT(*)
Arithmetic average:
AVG(column_name)
Maximum / minimum
MAX (column_name)
MIN (column_name)
Not all aggregates can be sped up by proper indexing
Sorting: Result sets can be sorted using the ORDER BY clause
SELECT * FROM A ORDER BY ID
This is superfluous — ID is the primary key!
SELECT * FROM A ORDER BY ID DESC
SELECT * FROM B ORDER BY A_ID DESC, ID
Sorting performance is affected by indexing
Transactions: Transaction create atomic sets of operations that can be committed or rolled back without any chaange to the underlying data
BEGIN TRANSACTION
DELETE FROM A
DELETE FROM B
ROLLBACK TRANSACTION
BEGIN TRANSACTION
UPDATE A SET ID = 124 WHERE ID = 123
UPDATE B SET A_ID = 124 WHERE ID = 123
COMMIT TRANSACTION
Not all DBMSs support transactions
For example, MySQL only supports them with InnoDB
SQL and Dates: Most DBMSs can handle dates much better than PHP Extended range Higher resolution
Therefore, you should keep all date operations within your DBMS for as long as possible
File Wrappers: File wrappers extend PHP’s file handling use fopen(), fread() and all other file functions with something other than files For example, access HTTP, FTP, ZLIB and so on
Built-in wrappers, or your own Simply define your own wrapper class:
class wrap {
function stream_open($path, $mode, $options, &$opened_path) {}
function stream_read($count) {}
function stream_write($data) {}
function stream_tell() {}
function stream_eof() {}
function stream_seek($offset, $whence) {}
}
stream_wrapper_register("wrap", "wrap"); // register wrapper
$fp = fopen("wrap://some_file", "r+"); // open file via new wrapper
Not all file wrappers support all operations
For example, HTTP is read-only
Remote file access may be turned off
Use the allow_furl_open PHP.INI directive
Some wrappers are write-only
For example: php://stdout and php://stderr
Some wrappers do not support appending
For example ftp://
Only the “file://” wrapper allows simultaneous read and write operations
File Wrappers: File wrappers support information retrieval via stat() and fstat()
This is only implemented for file://
Remember, however, that SMB and NFS files are “local” as far as the operating system is concerned
Deleting and renaming is also supported. Renaming only supported for local file (but see above. Both require write access
You can also access and manipulate directories. Supported only for local files
Remember to close unused wrapper instance. Not necessary, but often a good idea
Streams: Streams represent access to network services
File wrapper
One or two pipelines
Context
Metadata
• Pipelines
Established to allow for the actual streaming of data
Can be one only (read or write) or two (read and
write)
• Context
Provides access to advanced options
• For example, under HTTP you can set additional headers
• Metadata
Contains “out-of-band” information provided by the
stream
• print_r(stream_get_meta_data(fopen("http://www.php.net", "r")));
/* Array (
[wrapper_data] => Array (
[0] => HTTP/1.1 200 OK
[1] => Date: Wed, 25 Aug 2004 22:19:57 GMT
[2] => Server: Apache/1.3.26 (Unix) mod_gzip/1.3.26.1a PHP/4.3.3-dev
[3] => X-Powered-By: PHP/4.3.3-dev
[4] => Last-Modified: Wed, 25 Aug 2004 21:12:17 GMT
[5] => Content-language: en
[8] => Content-Type: text/html;charset=ISO-8859-1
)
[wrapper_type] => HTTP
[stream_type] => socket
[unread_bytes] => 1067
[timed_out] =>
[blocked] => 1
[eof] =>
Sockets: Sockets provide the lowest-level form of network communication. Because of this, you should use them only when strictly necessary
Several transports are supported:
TCP/UPD
SSL
TLS
UNIX
UDG
You can’t switch transports mid-stream, Sometimes problematic for TLS
Sockets: Opening:
$fp = fsockopen ($location, $port, &$errno, &$errstr)
You can then use fwrite, fread(), fgets(), etc.
• Opening persistend sockets:
$fp = pfsockopen ($location, $port, &$errno, &$errstr)
Persistent sockets will only work for persistent APIs, like mod_php on Apache and FastCGI. Connections can also be terminated from the remote host because of lack of network activity. Use with care—lots of potential pitfalls!
Socket Timeout: An optional fifth parameter to fsockopen() indicates timeout
$fp = fsockopen("www.php.net", 80, $errno, $errstr, 30);
Timeout is in seconds
Default is stored in default_socket_timeout PHP.INI setting
Timeout must be set separately for network activity:
socket_set_timeout ($socket, $timeout)
Sockets can be blocking or non-blocking stream_set_blocking ($socket, FALSE);
This needs a pre-existing socket!