Connecting Google cloud SQL from App engine.

Google cloud platform support PHP application in its app engine. App Engine is an elastic container it automatically scales up and down based on the traffic.

Here is the https://cloud.google.com/appengine/docs/standard/php7/quickstart explaining how to deploy PHP application into GCP app.

The scope of this article is how to connect to the cloud sql in your PHP from app engine. To deploy into app engine we need to configure some meta information in app.yaml

If we are connecting by dsn we don't need any special configuration. we can refer to the database directly like

mysql:unix_socket=/cloudsql/<MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>;dbname=<my_db>

I am assuming both your app engine and cloud database in the same project. Else we have to explicitly give access.

$dbdsn = "mysql:unix_socket=/cloudsql/<MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>;dbname=<my_db>";

$conn = new PDO($dbdsn, $username, $password);

We can add the dsn in app.yaml read as an environment variable.

In app.yaml

env_variables:

  DB_DSN: mysql:unix_socket=/cloudsql/<MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>;dbname=<my_db>

DB_USER: my-db-user

DB_PASS: my-db-pass

DB_NAME: my_db 

DB_SERVER: my_server

And in the code

$dbdsn = getenv('DB_DSN');

$username = getenv('DB_USER');

$password = getenv('DB_PASS');

$conn = new PDO($dbdsn, $username, $password);

If we want to do tcp connection by providing host and port

$conn = new mysqli($servername, $username, $password, $dbname);

Then app.yaml we have to add 

beta_settings:

  cloud_sql_instances: <INSTANCE_CONNECTION_NAME>=tcp:<PORT>

And in the code $servername= 172.17.0.1:<PORT>;

For more details # https://cloud.google.com/sql/docs/mysql/connect-app-engine

My example code # https://github.com/pallabrath/myexpjava/tree/master/vlog/gcp/php-mysql-appEngine-clouddb%20/src

Rest apis powered by GraphQL

We can understand "Rest Api" means any system expose its functionality over http. So the consumer of api can make a http request to avail the functionalities. JSON is the data format in which client and server communicate over http.

GraphQL is a query language for api which enable the client of rest apis to ask what they need exactly. The same time server side its super easy to expose more functionaries in a generic way and cater lots of specialized request. 

more details # https://graphql.org/

GarphQL in Java # https://www.graphql-java.com/

In this article we will see an example where GraphQL is used with JAX-RS (Jersey) based rest implementation. And we will use Hibernate as Data Fetcher for GraphQL.

Its a standard gradle project, created with eclipse.  Here is the link to my github.

https://github.com/pallabrath/myexpjava/tree/master/GqlDemo

1. Lets start with build.gradle

We have the dependencies for jersey, graphql, hibernate, ojdbc driver and some helpers like gson and gauva.

2. In web.xml we have initialised the jersey servlet, there we have specified init parameters to look for com.rest package.

3. Inside source we have com.rest.Employee.java which expose the rest end point GqlDemo/rest/employee

We have the Query() method to handle the incoming http post request for graph ql queries.

4. We have created GraphQl provider which load the schema definitions from resources/schema.graphqls
we have defined the wiring for query employeeById.

5. In GraphQLDataFetcher we have the implementation for employeeById. where we get the request parameter value and process. Here I have used hibernate to query the database.

Here is a screen shot of postman request/response.

I will try to answer if any question on this. Please let me know your feedback.

Read ZipEntry form ZipInputStream as InputStream

To read ZipEntry from ZipInputStream

public class ZipEntityDemo {
	public InputStream extract(ZipInputStream zis, String entityName) throws Exception {
	ByteArrayOutputStream out = new ByteArrayOutputStream();
	ZipEntry ze = null;
	do {
	ze = zis.getNextEntry();
	if (ze != null && ze.getName().equalsIgnoreCase(entityName)) {
	byte[] buffer = new byte[1];
	while (zis.available() == 1) {
	zis.read(buffer);
	out.write(buffer, 0, 1);
	}
	out.close();
	return new ByteArrayInputStream(out.toByteArray());
	}
	} while (ze != null);
	return null;
	}
	}

Here zis.getNextEntry() will return us the beginning of an entry. If the name of the entry match to our desired name we can start reading. Here we are reading byte by byte in a loop till last byte. We could have improved by using ze.getSize(), and we could read multiple bytes till the size. But the getSize() methods some time returns  -1 for some zipfiles. So it wouldn't be reliable. With out knowing the entry size only way is to read byte by byte till last.