I have made some progress on my SQL to JDO wrapper for PHP at GAE. There is a new example PHP script with lots of neat SQL statements.
The SQL to JDO wrapper has now a new name. It is pQg. What means PHP with SQL on Google App Engine.
Look at below PHP script. Look at the many SQL statements in it, they are working!
<?php
//
// pQg-Demo.php
//
echo "<html><body><pre>";
// Import the JDO classes for the database
import myDatabase.person;
// The database in use (must be the package for above classes)
$pQg_database = 'myDatabase';
// Include the ROM-PHP-GAE source file.
include "WEB-INF/pQg.inc";
// Start our demo with a empty table.
sql("delete from person");
// Fill the database with the test set.
$jan = sql("insert into person (firstName, lastName, email, salary )
values ('Jan', 'Modaal', 'j.modaal@het.net', 2500 ) ");
echo "Added Jan to the database with primary key $jan \n";
$john = sql("insert into person (firstName, lastName, email, salary )
values ('John', 'Doe', 'JOHN@GMAIL.COM', 5000 ) ");
echo "Added John to the database with primary key $john \n";
$jane = sql("insert into person (firstName, lastName, email, salary )
values ('Jane', 'Doe', 'JANE@AOL.COM', 4000 ) ");
echo "Added Jane to the database with primary key $jane \n\n";
// Let's see what is in the table right now
print_table();
// getField, get a single field.
$salary = getField(" select salary from person where firstName = 'John' and lastName = 'Doe' ");
echo "The salary of John is $salary \n\n";
// getRecord, retrieve fields from 1 records.
$record = getRecord(" select strtoupper(FiRsTnAmE) as FIRST, strtoupper(LaStNaMe) as LAST
from person where id = $jane ");
echo "Who is number $jane? It is: " . $record['FIRST'] . ' ' . $record['LAST'] . "\n\n";
// Jane is getting a raise.
sql("update person set salary = salary + 1500 where id = $jane");
// Make url's from the emails
$count = sql("update person set email =
'<a href=\"mailto:' . strtolower(email) . '\">' . strtolower(email) . '</a>'
");
echo "Transform emails: Updated $count record(s) \n\n";
// Jan is fired
$count = sql("delete from person where id = $jan");
echo "Fire Jan: Deleted $count record(s) \n\n";
// This is the end result.
print_table();
// Close the Datastore connection (the open is done on first usage)
pQg_close();
echo "</pre></body></html>"
// Print all records in the person table.
function print_table() {
// getRecords, retrieve n records from the database.
$records = getRecords("select * from person order by salary desc");
echo '<p><table border="1">';
// Print the fields name by using the key's from the first record.
echo "<tr>";
foreach($records[0] as $key => $field)
echo "<td> <b> $key </b> </td>";
echo "</tr>";
foreach ($records as $record) {
echo "<tr>";
foreach($record as $field)
echo "<td> $field </td>";
echo "</tr>";
}
echo "</table></p>";
}
?>
Below the output of this script.
Added Jan to the database with primary key 1
Added John to the database with primary key 2
Added Jane to the database with primary key 3
| email | firstName | lastName | salary | id |
| JOHN@GMAIL.COM | John | Doe | 5000 | 2 |
| JANE@AOL.COM | Jane | Doe | 4000 | 3 |
| j.modaal@het.net | Jan | Modaal | 2500 | 1 |
The salary of John is 5000
Who is number 3? It is: JANE DOE
Transform emails: Updated 3 record(s)
Fire Jan: Deleted 1 record(s)
I will upload this script and a zip files with sources soon to the GAE site for it. First I must look into indexes. It works fine on the development environment. However on GAE it complains that there are no indexes. Somehow those indexes are not auto-generated on my development machine.
For now you can find the sources here
To use this zip file you must first create a PHP enabled GAE project inside Eclipse and then copy the sources from the zip file to the same directory locations as in the zip file. ( Click here for instructions how to setup an PHP enabled GAE project in Eclipse )
