jiql JDBC

Overview

jiql is a JDBC wrapper for accessing Google DataStore on Google App Engine for JAVA.

jiql supports the use of standard SQL as a method for accessing

the DataStore 

 

Architecture

jiql introduces a concept called "Table Leafs". Table Leafs

are DataStore Kinds that can be dynamically added or 

chained together in infinite amounts to represent a Table. 

 

Table Leafs provides a standard method, with SQL, to

retrieve entities of more than the "1000 limit" at a time.

 

For optimization, entities in the same Table Leaf, has

the same entity group parent. That means all of the

members in the Table Leaf, are located in the same

network node of the Google DataStore Cloud, for faster

access.

 

To disable Table Leaves, add the following at the end of the

CREATE TABLE statement:

tableleafs=false

 

 

Quickstart

Download jiql.jar java database engine and driver library and place under WEB-INF/lib/

NOTE: The appengine-api.jar must also be placed under WEB-INF/lib. This file comes

with the GAE SDK. Follow the steps below to create a JDBC connection with jiql on

GAE-J in your JAVA code:

 

String url = "jdbc:jiql://local";

String password = "";

String user = "";

 

Properties props = new Properties();

props.put("user",user);

props.put("password",password);

  

Class clazz = Class.forName("org.jiql.jdbc.Driver");

Driver driver = (Driver) clazz.newInstance();

Connection Conn = driver.connect(url,props);

Below is the link to a GAE-Ready JDBC guestbook application:

http://jiql.googlecode.com/files/jiql-jdbc-guestbook.war

 

After downloading jiql-jdbc-guestbook.war, unpack to your GAE Application directory.

Edit the ./WEB-INF/appengine-web.xml file and add your GAE Application ID.

Upload to Google App Engine for JAVA.

 

You must first run the following JSP, which will create the Database table.
http://yourapp.appspot.com/init.jsp

 

Then browse to http://yourapp.appspot.com/guestbook.jsp

to start using the JDBC guestbook application.

 

The source code may also be downloaded at the following URL:

http://jiql.googlecode.com/files/jiql-jdbc-guestbook.src.zip

 

NOTE: Sample applications may not contain the latest jiql.jar. To ensure you have the

latest download and add to application. Download the latest jiql.jar from the following URL:

http://jiql.googlecode.com/files/jiql.jar

 

 

Remote Access

You can access Google DataStore from a remote client via the JiqlServlet.

This requires setting up the GAE-J Server as well as the Client Application configuration.

GAE-J Server Configuration

First configure the JiqlServlet in the web.xml like so:

    <servlet>

        <servlet-name>jiqlservlet</servlet-name>

        <servlet-class>org.jiql.JiqlServlet</servlet-class>

    </servlet>

    <servlet-mapping>

        <servlet-name>jiqlservlet</servlet-name>

        <url-pattern>/jiqlservlet</url-pattern>

    </servlet-mapping>

 

Next add authenication information in the jiql.properties file:

File: ./WEB-INF/jiql.properties

Entries:

user=admin

password=jiql

 

Client Application Configuration

Follow the steps below to create a JDBC connection with jiql on the Client in your JAVA code:

String url = "jdbc:jiql:https://yourapp.appspot.com/jiqlservlet";

String user = "admin";

String password = "jiql";

//or

//String url = "jdbc:jiql:https://yourapp.appspot.com/jiqlservlet?user=admin&password=jiql";

 

Properties props = new Properties();

props.put("user",user);

props.put("password",password);

  

Class clazz = Class.forName("org.jiql.jdbc.Driver");

Driver driver = (Driver) clazz.newInstance();

Connection Conn = driver.connect(url,props);

 Required Libraries for Remote Client

jiql.jar

appengine-api.jar

Apache Commons HTTPClient - you may download the bundled JAR here.

 

Required Libraries for the Server

Apache Commons FileUpload available at the following URL:

http://commons.apache.org/downloads/download_fileupload.cgi

 

 

 

Transactions

jiql has limited support for Transactions.

Transactions support is based on the Google DataStore Transaction

engine. Google DataStore does not support Global Transactions.

A Transaction is limited to Entities/Rows within a Group or Table/Kind.

This mean you are not able to perform operations on multiple

Tables/Kinds within the same Transaction. To enable Transactions,

set the enable.transactions Connection property option to true.

NOTE: Transaction are NOT supported for Remote connections.

 

 

 

Limitations

FILTERS

For large data sets, the left most filter, should not return

a result set of more than about a couple thousand items.

A timeout may result. In this case you should try a narrower

filter. If the query is comprised of multiple filters, you may

place the filter that returns the least items, to the left of all

the other filters.

 

JOINS

When working with large data sets, JOINS should be combined with

at least 1 filter acting on a specific table. Otherwise, the query would

involve traversing the entirety of all the tables in the JOIN. Such

an operation is time consuming and may create a Google App Engine Timeout

if the operation exceeds 30 seconds.

 

 

Optional Properties

date.format

Specifies the Date Format of a DATETIME field.

e.g. date.format=yyyy/MM/dd

 

enable.transactions

Enable Transactions (please refer to the Transactions topic)

e.g. enable.transactions=true

 

DatabaseProductVersion

Used to emulate other Databases, such as MySQL.

e.g. DatabaseProductVersion=4.0.0 

 

DriverVersion

Used to emulate other Databases, such as MySQL.

e.g. DriverVersion=mysql-connector-java-5.0.4 ()

 

Catalog

Used to specify a Database Catalog.

e.g. Catalog=jiql 

 

MetaCache

Used for Caching MetaData, such as DatabaseMetaData.

e.g. MetaCache=true

 

 

 

jiql Functions

In addition to standard SQL functions, there are a number of jiql specific functions.

LOAD

LOAD DATA INTO TABLE table_name INTEXT column1,column2

1,'text1'

2,'text2'

 

tableleafs

specify whether to use Table Leafs or not (default true).

tableleafs=false

 

prefix

specify whether to add a prefix to Tables created by jiql (default true)

prefix=false

 

prefix_value

specify whether to add a customized prefix name to Tables created by jiql (default jiql)

prefix_value=jiql

 

 

PHP

How to run PHP with your same MySQL queries

Running PHP on Google App Engine for JAVA requires jiql with a modified version of Quercus,

a Java implementation of PHP 5. The following URL has the downloads:

jiql-quercus.war - The modified Quercus Application bundled with jiql.

jiql-quercus.src.zip - The modified Quercus source files.

 

After downloading jiql-quercus.war, unpack to your GAE Application directory.

Add your PHP files to the root of the unpacked Quercus application.

Edit the ./WEB-INF/appengine-web.xml file and add your GAE Application ID.

Upload to Google App Engine for JAVA.

 

Below is a simple PHP application from PLUS2NET as a starting PHP example:

http://www.plus2net.com/php_tutorial/guest_book.zip

 

NOTE: Sample applications may not contain the latest jiql.jar. To ensure you have the

latest download and add to application. Download the latest jiql.jar from the following URL:

http://jiql.googlecode.com/files/jiql.jar

 

 

JRuby on RAILS

Download jiql.jar java database engine and driver library and place under WEB-INF/lib/.

Configure your database.yml like so:

production:

  adapter: jdbc

  driver: org.jiql.jdbc.Driver

  url: jdbc:jiql:local

  username: admin

  password: appcloem

  encoding: utf8

Now of course Google App Engine for JAVA does not allow you to perform pre-deployment tasks,

such as running rake to setup your RAILS application database. You have 2 choices.

First configure your jiql for remote access. Then run the rake DB task from your PC.

For this copy of your Application, you would configure the database.yml like so:

production:

  adapter: jdbc

  driver: org.jiql.jdbc.Driver

  url: jdbc:jiql:https://myapp.appspot.com/jiqlservlet?user=admin&password=jiql

  username: admin

  password: appcloem

  encoding: utf8

The second choice is to export the Database to a SQL formatted script.

With the SQL script, you have a number of choices to import the SQL script to your

jiql Database, which includes the use of Eclipse Database Tools Project (DTP) or jiqlAdmin.

 

Below is the link to a GAE-Ready phonebook application WAR:

http://jiql.googlecode.com/files/jiql-jruby-rails-phonebook.war

 

After downloading jiql-ruby-rails-phonebook.war, unpack to your GAE Application directory.

Edit the ./WEB-INF/appengine-web.xml file and add your GAE Application ID.

Upload to Google App Engine for JAVA.

 

Since we are not able to run 'rake db:migrate' on Google App Engine,
you must first run the following JSP, which will create the Database table.
http://yourapp.appspot.com/init.jsp

 

Then browse to http://yourapp.appspot.com/people

to start using the RAILS phonebook application.

 

NOTE: Sample applications may not contain the latest jiql.jar. To ensure you have the

latest download and add to application. Download the latest jiql.jar from the following URL:

http://jiql.googlecode.com/files/jiql.jar

 

 

jiql Administration

As jiql uses the standard JDBC protocol to manage Google DataStore, there are a number

of tools that can be used locally or remotely to manage jiql. Available database management

tools include:

jiqlAdmin Data Querying tool for Google's DataStore.

Eclipse Database Tools Project (DTP). When configuring DTP for jiql,

the Eclipse Connection Type should be set to Generic JDBC_1.

 

 

 

Source - Compiling

 

- Download the source here

 

- JAVA JDK 1.6 or higher is required. The reason for this is that jiql implements

the latest JDBC API. The latest JDBC references SQL extensions that

are included with JDK 1.6. These SQL extensions are not included in

previous JAVA JDK releases. 

 

- There are 2 ways of compiling the source. But first an explanation

of the JAVA source tree.

 

org.jiql - is the core jiql source code.

tools. - are code for a general library used by jiql and other projects.

 

- Minimum dependencies for compiling:

appengine-api.jar

hibernate3.jar

commons-httpclient ( http://hc.apache.org/downloads.cgi
commons-fileupload ( http://commons.apache.org/downloads/download_fileupload.cgi

 

For minimum compiling:

Download the jiql.jar and uncompress to your output folder.

Add the output path to your classpath.

This will allow you to edit org.jiql.* code and rebuild.

 

- However to compile all code, including tools.*, the following dependencies must

be added to your classpath:

servlet-api.jar

ant.jar

 

NOTE: jiql depends on a number of properties files located in the jiql package

tree. These properties files are not included in the source.zip archive. The

properties files are included in the SVN source. They may also be extracted