Accessing More Advanced Features of Mysql

Last week, we started discussing interacting with a database using PHP.
As we mentioned before, mysqli can be used in both a procedural and object-oriented fashion.
We illustrate this by showing another way we could do queries in mysql: Using prepared statements.
When one does a mysqli_query, the database has to compile the query each time it gets it, it can't compile the query, optimize it, and store it for the next time. This is because the argument to the mysql query might completely change between calls.

Using prepared statements, we can reuse a query we have already compiled and set up with fixed slots that may change. For example:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "my_db"); 
// note: here we are using the OO interface to mysqli
// we'll talk more about OO in PHP later today; for now, pretend its like Java/C++
$stmt =  $mysqli->stmt_init();
if ($stmt->prepare("SELECT FNAME FROM EMPLOYEE WHERE LNAME=?")) {
    $lnames = ["Smith", "Jones", "Pollett"];
    foreach($lnames as $lname) {
       $stmt->bind_param("s", $lname); //s == string, i == int, d==double
       $stmt->execute();
       $stmt->bind_result($fname);
       $stmt->fetch();
       print("The first person I found with last name $lname was  $fname\n");
    }
    $stmt->close();
}
$mysqli->close();

Another advantage of the above is that there is less of an issue about properly escaping the parameters.

Other Database Interfaces in PHP

As I may have mentioned, PHP has support for the usual database suspects: Oracle, DB2, Postgres, Sybase, etc, each having their own suite of functions orcl_, db2_, pg_, sybase_ ...
PHP's default build has also includes in it support for Sqlite 2 and Sqlite 3 databases.
Sqlite is a tiny highly optimized and tested C program to allow you to create SQL databases.
It stores databases in a flat file and is used as an embedded persistence layer in a wide variety of platforms: Firefox, iPhone, Android, Adobe, etc.
Sqlite database files created on one of these platforms can be read by any other. Further, since the database is stored in a flat file, it can be very useful for storing default data needed in deploying an app.
Sqlite2 in PHP uses a function style interface: $dbh = sqlite_open($filename, $write_mode); $result = sqlite_query($query); sqlite_fetch_array($result); sqlite_close($dbh);
Sqlite3 in PHP uses an OO style interface: $dbh = new SQLite3($filename, $mode); $results=$dbh->query($query); $data = $results->fetch_array(); $dbh->close();
Finally, PHP has an OO interface known as PDO, which can be used to access any DBMS database for which PDO drivers have been written. This includes all the major databases listed above.

Some Quick Remarks on Database Design

It is possible to store all of your data in a big table with lots of columns.
This is typically not done because it is wasteful of space. For instance, if we had a big table with Employee names, ssns, and children of employee names, we'd have to repeat the full Employee name for each child name; whereas, if we had a separate table called dependents we could just have columns in it: Employee ssn, child names, and not have to repeat the Employee name for each child of that employee.
One goal in DB design is to split the database into tables to store the data as efficiently as possible; yet, on the other hand, be sure we are preserving the original information, and are not introducing any false information when we join tables together.
An artistic approach to this problem can be done by trying to figure out the classes/entities that need to be stored in the database and the corresponding relationships between them.
After doing this modeling phase, people typically map each of the entities and relationships that were found to tables.
To check the result, a notion called functional dependency is used. A set of columns Y functionally depends on a set of columns X, if when we know the values for X we know the values of Y.
A dependency like X fixes X (or some subset of X's columns) is considered trivial.
We would like our tables to have the property that the only nontrivial dependencies X fixes Y on our tables involve X's which fix whole rows. (Boyce-Codd Normal Form).

Example

As an example, consider the table EMP_PROJ(SSN, PNUMBER, HOURS, ENAME, PNAME, PLOCATION).

Suppose the functional dependencies are:

SSN, PNUMBER--> HOURS
SSN --> ENAME
PNUMBER --> PNAME, PLOCATION.

Some draw backs of this table are that:
- To insert an employee before they have a project means, null padding rows.
- Deleting the last employee from a project might end up deleting the project.
- When we add an employee to a second project we are duplicating ENAME unnecessarily, as once we know the SSN we've already fixed the ENAME.
The above table is not in BCNF as SSN --> ENAME is a non-trivial FD, yet knowing the SSN does not fix the whole row as someone might work on multiple projects.
We can make a database schema with the above attribute which is in BCNF using the following tables: EMPLOYEE(SSN, ENAME), PROJECT(PNUMBER, PNAME, PLOCATION), WORKS_ON(SSN, PNUMBER, HOURS).
Notice this database schema avoids the three issues above, giving evidence to the idea that putting things into BCNF avoids the kind of DB anomalies we don't want.

Quiz

Which of the following statements is true?

A web server can set a cookie by sending a Set-Cookie header as part of its response message to a web request.
A SQL WHERE clause is used to list the database tables involved in a query.
MVC is another name for the model view adapter design pattern.

Classes in PHP

Classes in PHP can be defined with a syntax like:

class MyClass
{
   public $field_var1;
   public $field_var2 = 5;
   ...
   const $constant1;
   const $constant2;
   ...

   function __construct($arg1, $arg2) {
     //constructor code
   }

   function myFun1() {
     // code
   }
   ...
}

Typically, you put the code for your class into a file and then use a line like:
```
require("MyClass.php");
//or more likely
require_once("MyClass.php");
```
We mentioned this earlier, but I'll just remind people: The require syntax is similar to include except when it fails it gives a fatal error rather than a warning. Also you cannot use require to include remote files even if allow_url_fopen is enabled.
require_once will not re-include the file if it has already been included.
PHP comes with several built-in classes and interfaces.
Several common data structures are also implemented in the Standard PHP Library (SPL).

Constructors/Destructors

We saw we could set up an initial value of a field variable of a class when we declare it:
```
public $my_variable = 0;
```
You can also have a functions __construct and __destruct to do initialization and clean-up.
```
function __construct($n=0) {
     $this->my_variable = $n;
}
```

Private, Protected, Public

Member variables and member functions can be declared private, protected or public:
```
private $my_field;
protected function myMethod() { /* some code*/}
```
Methods without any declaration are the same as public. It is a good habit thought to explicitly state the visibility
private means only visible within the class.
protected is visible within the class or within subclasses.
public means variable or method is visible to anyone. There is an older way to declare public fields:
```
var $my_field;
```
This is less clear than the public syntax so should be avoided.

Static and Const

The static keyword creates one instance of the field or method for the class.
```
class Foo{ static $bob=1;} 
echo "bob: ".Foo::$bob;
```
Within the class use self:: to refer to static members.
The const keyword can be used to define constants for a class:
```
class Goo{ const blob=1;}
```
Notice no dollar sign. Can refer to this using
```
Goo::blob
```
Values of constants cannot be changed.
You can create constants not within a class using define:
```
define("PI", 22/7);
echo PI;
```

Cloning

PHP has a command clone for cloning objects:
```
$my_copy = clone $my_obj;
```
To specify how the copying is done you can write a __clone() method for your class.

Inheritance

So far we've described how to make a PHP class, constructors, destructors, and cloning.
We've said unlike the default copy-on-write semantics of array assignment, object assignment in PHP is by reference. Let's now turn to inheritance.
Inheritance in PHP is very similar to Java.
A PHP class can extend one other class.
```
class A{}
class B extends A {}
```

PHP also has a notion of interface:

interface MyInterface
{
    function method1($a, $b);
}

A PHP class can implement multiple interfaces:

class C implements MyInterfaceA, MyInterfaceB {}

If you want to have a class with some but not all of its methods defined. You can use the keyword abstract on those methods which will be overriden in subclasses.

Referring to Parents, Final

Consider:

class A {function foo(){} }
class B extends A {function foo(){} }

Within B, self::foo() refers to the redefinition of foo given in (2). (Instance methods can also be called using $this->method())
Within B, parent::foo() refers to class A's version of foo.
To prevent a function from being overridden in a subclass you can use the keyword final.
```
class A {final function foo() {} /* can't override*/ }
```

Traits

Traits are a PHP language feature added in PHP 5.4, probably deriving from Python/Common Lisp mix-ins.

They allow one to define a collection of method implementations and then reuse them in several classes:

trait MyMethods 
{
    function getFoo() { /* code for getFoo */ }
    function getGoo() { /* code for getFoo */ }
}

class SomeClassA extends NeedsMethods 
{
    use MyMethods; //as if I had written the code in MyMethods here
    /* rest of SomeClassA code */
}

class SomeClassB extends NeedsMethods 
{
    use MyMethods;
    /* rest of SomeClassB code */
}

$a = new SomeClassA();
$a->getFoo();

You can import more that one trait by comma separating them:
```
class A  
{
   use MyMethodsA, MyMethodsB;
   // ...
}
```
Suppose both MyMethodsA and MyMethodsB define a function named foo and one named goo. This will result in a fatal error unless one explicitly resolves the conflict using the insteadof operation to say which to use, or uses the as operator to rename one:
```
use MyMethodsA, MyMethodsB {
    MyMethodsA::foo insteadof MyMethodsB;
    MyMethodsB::goo insteadof MyMethodsA;
    MyMethodsA::goo as gooey;
    MyMethodsB::foo as protected fooey; //notice can change visibility if want
}
```

Namespaces

Like C++, PHP supports a notion of namespace. This is similar to the Java notion of package. It allows one to group together a collection of functions, constants, classes, interfaces under one name.
Namespaces must be declared at the start of the file they are in (only the declare command can occur earlier)
A namespace might be declared with a line like:
```
namespace TheNameOfMyNamespace;
// code for namespace
class A
{
  //code for class A
}
function foo()
{
}
```
This declaration with the same name can be at the top of multiple files if you would like to group the contents of these files into the same namespace.
A sub-namespace of a namespace can be declared using backslashes:
```
namespace TheNameOfMyNamespace\MySubNameSpace;
//code
```
Within one file you can switch what namespace you are defining, but it is not recommended:
```
namespace D {
  // code for D
}
namespace E {
  // code for E
}
```

Using Namespaces

Function or class names without qualifications are assumed to come from the current namespace:
```
namespace N;
function foo()
{
  //some code
}

foo(); //assume N namespace.
```

Before function, interface, class, or constant names we can also give the namespace path (either relative or absolute):

M\foo(); /*in context of above we are in N namespace already, 
          so M\foo() means the foo() in N\M namespace. */
\N\M\foo();
\time(); //time function in the global (default) namespace.

You can import a namespace using the use keyword:

use A\B\C;
//rather than typing A\B\C\foo(); could type foo();
use D\E\F as G;
//rather than typing D\E\F\goo(); could type G\goo();

Exceptions

PHP supports try catch blocks like Java.

try {} catch(MyException $e){} catch(Exception $ee){}

You can use the keyword throw to throw an exception

if($denom == 0){
    throw new Exception("divide by zero");
}

You can subclass Exception to create custom exceptions.

Generators

Generators are a PHP 5.5 feature that allow one to generate a sequence of values via function. They are similar to Python generators.
They are suitable for use in statements like foreach where the array argument might go.
Generator functions execute until a yield statement is reached.
This statement is like a return statement except that the context of the function is preserved. The next time the generator is called execution resumes in this context after where the yield statement was.

For example, in PHP, range(0, 10000) returns an array of elements 0 - 9999 which we could use in a foreach. A similar effect could be achieved with the code:

function my_gen($start, $end) {
    for ($i = $start; $i < $end; $i++) {
        // $i is preserved between yields.
        yield $i;
    }
}

$generator = my_gen(0, 10000);
foreach ($generator as $value) {
    echo "$value\n";
}

The advantage of the above code is that it uses less memory.
You can yield key value pairs with a syntax like:
```
yield $k => $v;
```
A yield statement without an argument returns a pair NUM_TIME_YIELD_CALLED => null.

Version Control Systems

A version control system solves the problem of how many people can work on the same code base at the same time.
There are many different such systems on the market, the oldest is Source Code Control System developed at Bell Labs in 1972.
It was subsequently supplanted in popularity by RCS (Revision Control System) which in turn was supplanted by CVS (Concurrent Version System).
Nowadays, there are many different versions control systems on the on the market.
There are traditional central repository systems: CVS, Subversion (open source); BitKeeper, Perforce, ClearCase ( proprietary)
There are also distributed version control systems: Bazaar, git, Mercurial.
Here is a round-up of the different features of these systems http://en.wikipedia.org/wiki/Comparison_of_revision_control_software
We are going to look at an example of each of these major type of systems.
We will look at Subversion because: (a) it uses http and https as its means of accessing the repository; (b) it is open source and has been around along enough that it seems for real; and (c) According to Eclipse Community Survey 2013, at least among Eclipse user's it has a 38% market share (CVS has about 4.5%).
In comparison, GIT/GitHub has a 36% share and is currently the most popular distributed version control system.
On Wednesday we will talk about Git.

Prepared Database Queries, PHP Classes

Outline