31 mei 2009

pQg = PHP with SQL on GAE


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)

  email     firstName     lastName     salary     id  
  jane@aol.com     Jane     Doe     5500     3  
  john@gmail.com     John     Doe     5000     2  
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 )

3 opmerkingen:

Anoniem zei

RPG is a horrible name because it conflicts with RPG: report program generator in google queries.

Anoniem zei

Indeed, I must find a new name, a name that has something with SQL and PHP in it, so that you see at once what it is. Maybe just as simple as "php-sql-gae"

Sara Pall zei

Hey
The work seems to be great
and keep on doing this finally you will end up with great application....best of luck actually i have just completed my studies from CISSP and really working hard on my projects...!!