How to use pQg

Ok, there are still a lot of bugs and most of the functionality is not yet implemented, but here is a HOW-TO posting about using pQg in your own PHP scripts at GAE.

pQg stands for PHP with SQL on Google App Engine, with pQg you can use SQL statements in PHP at Google App Engine.

Download and installation

You can download the sources here. To use this this zip file you must follow below three steps:

1 - Create a GAE project in Eclipse

See the "Java getting started" tutorial on the GAE website how to do this.

2 - Enable PHP in this GAE project

See my blog posting "Using PHP on GAE" how to do this.

3 - Copy pQg to this GAE project

Copy the files from the zip file to the same directory locations into the GAE project.

Introduction

Google uses BigTable as the datastore for GAE. BigTable is a key-value database, basicly it is just a big hash. It is written by Google for massive scalability in the Google Cloud. Compared to traditional relational databases like Oracle, DB2 & MySQL it has limited functionality.

Most PHP applications are LAMP (Linux, Apache, PHP & MySQL) The LA part of LAMP is done by Google with GAE. Caucho is doing P from LAMP with the excelent product Quercus.

So we have M left from LAMP, normaly this is a relational database like MySQL, on Google App Engine pQg tries to do this for you.

PHP is running inside Java at GAE. Google did make a JDO layer above BigTable. So Java programs (and PHP that runs in the Java environment) have a nice JDO interface to talk to BigTable. pQg is using this JDO layer.

JDO is object based. SQL is relational. Normaly the java folks are storing Java Objects in Relational databases like DB2, Oracle. They use ORM software for this, Object-Relational Mapping. pQg does the oposite, it translate SQL statements to JDO api calls. So pQg is a ROM, Relational to Object mapping :-)

Java JDO classes

First you must write a java JDO class with annotations for every database table you want to use. See below the one that I did wrote for the last demo.

package myDatabase;

import java.lang.reflect.Method;

import javax.jdo.annotations.IdGeneratorStrategy;
import javax.jdo.annotations.IdentityType;
import javax.jdo.annotations.PersistenceCapable;
import javax.jdo.annotations.Persistent;
import javax.jdo.annotations.PrimaryKey;

@PersistenceCapable(identityType = IdentityType.APPLICATION)

public class person {

  @PrimaryKey @Persistent(valueStrategy = IdGeneratorStrategy.IDENTITY)
    
                private   Long      id;
  @Persistent   private   String    firstName;
  @Persistent   private   String    lastName;
  @Persistent   private   String    email;
  @Persistent   private   Double    salary;

  public   Long     getId         ()             { return id;           }
  public   String   getFirstName  ()             { return firstName;    }
  public   String   getLastName   ()             { return lastName;     }
  public   String   getEmail      ()             { return email;        }
  public   Double   getSalary     ()             { return salary;       }
  
  public   void     setFirstName  ( String in )  { this.firstName = in; } 
  public   void     setLastName   ( String in )  { this.lastName  = in; } 
  public   void     setEmail      ( String in )  { this.email     = in; } 
  public   void     setSalary     ( Double in )  { this.salary    = in; } 

}

Above file has the same functionality same as below SQL "CREATE TABLE" statement. ( It is on my todo to automatic generate the JDO classes from a SQL "CREATE TABLE" statement )

CREATE TABLE myDatabase.person (
  id         BIGINT AUTO_INCREMENT ,
  firstName  TEXT ,
  lastName   TEXT ,
  email      TEXT ,
  salary     DOUBLE ,
  PRIMARY KEY (id)
)

pQg Rules for the java JDO class files

• The first field must be a Long with the name id or with the same name as the table. It will be uses as the primary key.


• Every field must have a getXXX and setXXX method (exception for the primary key, that one does not need a setXXX method)


• Only Long, String & Double are currently supported


• This java file must be store under src/ in your GAE project in Eclipse.

Including the pQg source

The best place for the pQg is somewhere under WEB-INF, below a template for a PHP script that loads pQg.

<?php

 // pQg - Import the JDO java classes for the database tables.
 
 import myDatabase.*;
 
 // pQg - Database structure
 
 $pQgCfgClass  ['person'] = 'myDatabase.person';
 $pQgCfgKey    ['person'] = 'id';
 $pQgCfgFields ['person'] = array('id',   'firstName', 'lastName', 'email',  'salary');
 $pQgCfgTypes  ['person'] = array('Long', 'String',    'String',   'String', 'Double'); 

 // pQg - settings
 
 define('PQG_CHECK', TRUE  );
 define('PQG_LOG',   FALSE );
 
 // pQg - Include 
 
 $pQgLocation = './WEB-INF/pQg';
 include "$pQgLocation/pQg.inc";

 
  < your PHP code goes here >

  
  // Close the pQg datastore connection (the open is done on first usage)
 
  pQgClose();
 
?>

Rules how to include the pQg source

• $pQgLocation must be set, pQg uses it.


• All the database tables used must be defined with $pQgCfgClass, $pQgCfgKey, $pQgCfgFields & $pQgCfgTypes


• Setting PQG_CHECK is optional. When set to TRUE pQg will check the SQL statement for correct syntax.


• Setting PQG_LOG is optional. When set to TRUE it will give a lot of information when pQg crashes.

The sql() function

It all about the sql() function of pQg, with ths function you can do the full SQL CRUD ( Create, Read, Update, Delete ).

sql("insert into demo (field1, field2) values(1, 'Yes it works')");

$rows = sql("select demo, field1, field2 from table order by id desc");

sql("update demo set field1 = $field1 where id = $did");

sql("delete from demo where id = $id");

Return value of the sql() function

The return value of the sql() function depends on the SQL statement executed:

• "create" returns the value of the primary key for the created record.


• "select" returns a 2 dimensional array. First dimension are records. This second dimension is an associative array with the field name as key.


• "update" returns the number of records that are updated.


• "delete" returns the number of records that are deleted

Wrappers around the sql() functions

There are 3 wrappers around the sql() function for use with the sql SELECT statement, this way you can control the return value.

• sqlField() - A single field


• sqlRecord() - An associative array


• sqlRecords() - An array with an associative array;

$salary = sqlField("select salary from person where  ... ");
echo $salary;

$record = sqlRecord("select * from person where ... ");
echo $record['firstName'] . ' ' . $record['lastName'];
 
$records = sqlRecords("select * from person ... ");
foreach ($records as $record)
  echo $record['firstName'] . ' ' . $record['lastName'] . '<br>';

SQL expression

There is no SQL engine, so you can not use SQL expressions, however we have a PHP engine, this makes PHP expressions possible. Most of the time you will not notice that it is PHP and not SQL that is evaluating your expression, for example below is the same in SQL and PHP.

set salary = salary + 1500

When using functions there are a lot of differences. For exampel, this SQL:

set abc = upper(substring(xyz, 2, 5))

in PHP this is:

set abc = strtoupper(substr(xyz, 3, 5))

So open 2 browser windows, one with the SQL reference and one with the PHP manual and start translating ...

The expresions in the SQL WHERE clause is the exception for this. Here you must use the JDO syntax, this part is given one on one to the JDO api.

Contents of the ZIP file

The zip file contains below files.

- readme.txt                                             - A small introduction
- war/pQg.php                                            - Testing pQg 
- war/WEB-INF/pQg/*                                      - The PHP sources for pQg
- war/WEB-INF/pQgTest/*                                  - Test files for pQg
- war/WEB-INF/logging.properties                         - Set the log level for pQg to INFO
- src/pQg/database.java                                  - Java source for the database connection
- src/pQg/log.java                                       - Java source for the logging
- src/myDatabase/demo.java                               - Database definition for the first demo
- src/myDatabase/person.java                             - Database definition for the second demo
- src/META-INF/services/com.caucho.quercus.QuercusModule - Tell Quercus about the log function

Status

It is working for the 2 demo scripts and some other test scripts :-) It works for 2 small web-app's from me. Will it work right now for your SQL scripts? Maybe, try it and send me the error reports :-)

Please let me know what you think about it, what you want that pQg must do.