h1. Tutorial: Play/Siena GAE GoogleCloud SQL

bq. you can see the "demo application in GAE":http://tweetsql.appspot.com/

GAE introduced recently GoogleCloudSQL to provide RDBMS support within Google AppEngine infrastructure. It provides another way of storing data in GAE outside the classic NoSQL datastore. It's not the same usage and is not meant to manipulate data in high-concurrent/scalable services. Apparently GoogleCloudSQL is based on MySQL servers and its scalability is provided through replication of databases in different places. Its real usage and perimeter with respect to GAE Datastore will certainly be precised along time by using it in different solutions.
 
As Siena bridges NoSQL/Sql, supporting GoogleCloudSQL was quite natural. Here is a tutorial to use Siena + Play + GoogleCloudSql 

GoogleCloudSql implementation requires:
* play-gae module v1.6.0 at least
* play-siena module v2.0.6 at least

This tutorial is based on "Felipe Oliveira's Tweet tutorial":http://geeks.aretotally.in/twitter-playframework-heroku.

It enhances it a bit by adding:
* cache usage
* siena pagination mechanism
* "Twitter bootstrap UI toolkit":http://twitter.github.com/bootstrap/

This tutorial and the same application also exists for "Amazon SDB":./heroku_sdb_tutorial.textile

h2. <a name="requirements">Requirements</a>

h3. <a name="req-aws">Create GoogleCloud SQL Instance</a>

h4. Active the GoogleCloudSql service

You can activate the service in "Google Services console":https://code.google.com/apis/console


h4. Create/Authorize a new GAE application in your "GAE console":https://appengine.google.com/

Create an application called *tweetsql*


h4. Create a new Sql instance for your application and authorize your GAE application

* Follow instructions found on "Google's before_you_begin":http://code.google.com/intl/fr/apis/sql/docs/before_you_begin.html#request_a_sql_service_instance
* Create an instance such as *playsienatest:play-siena-test* 
* During instance creation, you can authorize your GAE application *tweetsql* for this instance.


h4. Go to SQL Prompt & create database/user & grant privileges 

bc.. CREATE DATABASE tweetdemo;
CREATE USER 'tweetdemo'@'localhost' identified by 'xxxxxx';
GRANT ALL PRIVILEGES ON tweetdemo.* TO 'tweetdemo'@'localhost'

p. Concerning the privileges, some information are given "there in the Google cloud sql doc":http://code.google.com/intl/fr/apis/sql/docs/commandline.html#the_sql_prompt


==<br/>==
 

h2. <a name="play">Initialize your Play project</a>

h3. <a name="play-create">Create the project</a>

bc. play new play-siena-gae

p. ==<br/>==

h3. <a name="play-dep">Add siena 2.0.6 + gae 1.6.0 dependency</a>

_*conf/dependencies.yml*_

bc.. require:
    - play [1.2.3,)
    - play -> crudsiena [2.0.1,)
    - play -> siena [2.0.6,)
    - play -> gae 1.6.0_b1		# current version of gae 1.6.0 is beta1 as it's being tested
    
p. *Do not forget to let play resolve deps:*

bc. play deps --sync
 

==<br/>==

h3. <a name="play-proj">Enhance your project for your Eclipse/IDEA/Netbean</a>

bc. play eclipsify/idealize/netbeansify


p. ==<br/>==

h3. <a name="play-conf">Configure SimpleDB</a>

_*conf/application.conf*_


bc.. 
# development configuration for local tests using MySQL
db=mysql://tweetsql:xxxxx@localhost/tweetdemo

# production configuration for GAE
%prod.db.url=jdbc:google:rdbms://playsienatest:play-siena-test/tweetdemo
%prod.db.driver=com.google.appengine.api.rdbms.AppEngineDriver
%prod.db.user=tweetsql
%prod.db.pass=xxxxxxx


p(note). Note that _playsienatest:play-siena-test_ is the name of the new instance create here before.

p(note). Note that _tweetdemo_ is the name of the new database create here before.
 
p(note). *IMPORTANT: remove all other _db=..._ configuration*
 
p. ==<br/>==

h3. <a name="play-model">Create the model</a>

_*app/models/Tweet.java*_

bc.. public class Tweet extends Model {

	@Id(Generator.AUTO_INCREMENT)
	public Long id;       

	@Required
	@MaxSize(140)
	public String tweet;

	@Required
	@DateTime
	public Date createDate = new Date();
	
	public String toString() {
		return tweet;
	}

	public static List<Tweet> findLatest() {
		return Model.all(Tweet.class).order("-createDate").fetch();
	}
}

p(note). *Note* the @findLatest@ function which is just a conversion to Siena syntax of the function written by Felipe in his tutorial.

p. ==<br/>==

h3. <a name="play-model-crud">Create the CRUD controller in app/controllers/Tweets.java</a>

bc.. @CRUD.For(Tweet.class)
public class Tweets extends controllers.CRUD {    

}

p. ==<br/>==

h3. <a name="play-controller-basic">Create a basic controller</a>

_*app/controllers/Application.java*_

bc.. public class Application extends Controller {

	// lists all tweets ordered descendant & cache it
	public static void index() {
		List<Tweet> tweets = (List<Tweet>)Cache.get("current"+session.getId());
		if(tweets == null){
			tweets = Model.all(Tweet.class).fetch();
			Cache.set("current"+session.getId(), tweets, "10mn");
		}

		render(tweets);
	}

	public static void count() {
		Integer count = (Integer)Cache.get("count");
		if(count == null){
			count = Model.all(Tweet.class).count();
			Cache.set("count", count, "10mn");
		}
		renderTemplate("Application/count.html", count);
	}

	public static void create(String msg) {
		Tweet tweet = new Tweet();
		tweet.tweet = msg;
		tweet.save();
		
		// delete current page from list
		Cache.delete("current"+session.getId());
		Cache.delete("count");
		renderTemplate("Application/create.html", tweet);
	}
}

p. 

p(note). *Note* index/count function cache the list/count for 10mn not to query to often the DB (I pay for this DB :) )

p(note). *Note* create function cleans the cache to force a refresh because something was added

p. ==<br/>==

h3. <a name="play-master">Customize main template with Twitter Bootstrap fixed toolbar</a>

_*app/views/main.html*_

bc.. <!DOCTYPE html>

<html>
    <head>
        <title>#{get 'title' /}</title>
        <meta charset="${_response_encoding}">
        <link rel="stylesheet" media="screen" href="@{'/public/stylesheets/main.css'}">
        #{get 'moreStyles' /}
        <link rel="shortcut icon" type="image/png" href="@{'/public/images/favicon.png'}">
        <script src="@{'/public/javascripts/jquery-1.5.2.min.js'}" type="text/javascript" charset="${_response_encoding}"></script>
        #{get 'moreScripts' /}
    </head>
    <body style="padding-top: 40px;">
    	<div class="container">
			<div class="topbar-wrapper" style="z-index: 5;">
				<div class="topbar">
					<div class="fill">
						<div class="container">
							<h3>
								<a href="#">Welcome to Tweeter</a>
							</h3>
						</div>
					</div>
				</div>
			</div>
	        #{doLayout /}
        </div>
    </body>
</html>

p(note). *Note* the @padding-top: 40px@ in the &lt;body&gt; tag to allow content to be under the toolbar

p. ==<br/>==

h3. <a name="play-index">Customize basic template to display tweet form + list of recent tweets</a>

_*app/views/application/index.html*_

bc.. #{extends 'main.html' /}
#{set title:'Welcome to Tweeter' /}

#{set 'moreScripts' }
<link rel="stylesheet" href="http://twitter.github.com/bootstrap/assets/css/bootstrap-1.1.1.min.css">
#{/set}

<div class="row">
</div>
<div class="row">
	<!-- Create Tweet Form --> 
	<form> 
		Enter your tweet: <input name="tweet" type="text" />
		<button class="btn primary">Tweet</button> 
		<div class="count alert-message info" style="float:right; margin-left:10px; height:13px; width:13px;"></div>
		<div class="msg" style="float:right"></div>
	</form>
</div>
<div class="row">
	<!-- Latest Tweets List -->
	<table class="zebra-striped">
		<thead>
			<tr>
				<th width="80%">Message</th>
				<th width="20%">Since</th>
			</tr>
		</thead>
		<tbody class="tweets">
	#{list tweets}	
		<tr><td>${_.tweet}</td><td>${_.createDate.since()}</td></tr>
	#{/list}
	#{else}
		<tr><td>No tweet</td><td></td></tr>
	#{/else}
		</tbody>
	</table>

</div>

<!-- JS -->
<script type="text/javascript">
// Capture Form Submit Event
$('form').submit(function() {
	// Define Create Action
	var createAction = #{jsAction @create(':tweet') /}
	
	$('.msg').load(createAction({ tweet: encodeURIComponent($('input:first').val()) }));

	// Don't let the browser redirect
	return false;
});

var currentAction = #{jsAction @currentPage() /}
var countAction = #{jsAction @count() /}

setInterval(function() {	
	$('.tweets').load(currentAction());
}, 5000);

setInterval(function() {	
	$('.count').load(countAction());
}, 5000);
</script>

p. 

p(note). *Note* the @encodeURIComponent@ to be able to type a message composed of several words

p(note). *Note* the @moreScripts@ containing the Twitter Bootstrap CSS link

p(note). *Note* the @<div class="count">@ containing the number of tweets refreshed by a @setInterval@ calling @countAction@

p(note). *Note* the @<div class="msg">@ containing the result of tweet creation

p(note). *Note* the @setInterval@ refreshing the list of tweets every 5sec (this asynchronous mechanism is required by SDB non consistent read)


p. ==<br/>==

h3. <a name="play-list">Create the list template to display only the content of the table of the latest tweets</a>

_*app/views/application/list.html*_

bc.. #{list tweets}
	<tr><td>${_.tweet}</td><td>${_.createDate.since()}</td></tr>
#{/list}
#{else}
	<tr><td>No more tweets</td><td></td></tr>
#{/else}

p. ==<br/>==

h3. <a name="play-create">Create the create template to display the result of a creation</a>

_*app/views/application/create.html*_

bc. <div class="alert-message success">Successfully added</div>

p. ==<br/>==

h3. <a name="play-count">Create the count template to display the result of a creation</a>

_*app/views/application/count.html*_

bc. ${count}


p. ==<br/>==

h3. <a name="play-test1">Test your app</a>

bc. play run

Go to *http://localhost:9000* 

p. ==<br/>==

h3. <a name="play-paginate">Add Siena pagination to the controller</a>

_*app/controllers/Application.java*_

bc.. public class Application extends Controller {
	static Query<Tweet> q;

	@Before
	public static void loadQuery() {
		q = (Query<Tweet>) Cache.get("q"+session.getId());
		if (q == null) {
			// stateful is just meant to use GAE cursors instead of offsets
			q = Model.all(Tweet.class).order("-createDate").paginate(10);
		}
	}

	@After
	public static void saveQuery() {
		Cache.set("q"+session.getId(), q);
	}

	public static void index() {
		List<Tweet> tweets = (List<Tweet>)Cache.get("current"+session.getId());
		if(tweets == null){
			tweets = q.fetch();
			Cache.set("current"+session.getId(), tweets, "10mn");
		}

		render(tweets);
	}

	public static void currentPage() {
		List<Tweet> tweets = (List<Tweet>)Cache.get("current"+session.getId());
		if(tweets == null){
			tweets = q.fetch();
			Cache.set("current"+session.getId(), tweets, "10mn");
		}
		renderTemplate("Application/list.html", tweets);
	}
	
	public static void count() {
		Integer count = (Integer)Cache.get("count");
		if(count == null){
			count = Model.all(Tweet.class).count();
			Cache.set("count", count, "10mn");
		}
		renderTemplate("Application/count.html", count);
	}
	
	public static void nextPage() {
		List<Tweet> tweets = q.nextPage().fetch();
		Cache.safeReplace("current"+session.getId(), tweets, "10mn");

		renderTemplate("Application/list.html", tweets);
	}

	public static void previousPage() {
		List<Tweet> tweets = q.previousPage().fetch();
		Cache.safeReplace("current"+session.getId(), tweets, "10mn");

		renderTemplate("Application/list.html", tweets);
	}

	public static void create(String msg) {
		Tweet tweet = new Tweet();
		tweet.tweet = msg;
		tweet.save();
		
		// releases query context
		q.release();
		// delete current page from list
		Cache.delete("current"+session.getId());
		Cache.delete("count");
		renderTemplate("Application/create.html", tweet);
	}
}

p. 

p. Have a look at this code and note especially:
* The static member @Query<Tweet> q@ representing the query which is serialized in the Cache associated to the user session. This query object can be reused and keeps the context for pagination.
* the 2 function annotated <code>@Before</code> & <code>@After</code> allowing to create the query object, retrieve it before each request and put it into the cache after.
* The 2 functions @nextPage@ & @previousPage@ calling the query pagination functions and rendering the list template.
* The function @currentPage@ simply retrieves the current page in cache or if not present, it refreshes.
* The @q.release()@ call in @create@ which tells the query to reset its context so that the displayed tweets are the latest ones including the newest one.
* The cache is still used as before but in index the query is used to keep current page.
 
p. ==<br/>==

h3. <a name="play-paginate-view">Add Siena pagination to the view index.html</a>

_*app/views/application/index.html*_

bc.. #{extends 'main.html' /}
#{set title:'Welcome to Tweeter' /}

#{set 'moreScripts' }
<link rel="stylesheet" href="http://twitter.github.com/bootstrap/assets/css/bootstrap-1.1.1.min.css">
#{/set}

<div class="row">
</div>
<div class="row">
	<!-- Create Tweet Form --> 
	<form> 
		Enter your tweet: <input name="tweet" type="text" />
		<button class="btn primary">Tweet</button> 
		<div class="count alert-message info" style="float:right; margin-left:10px; height:13px; width:13px;"></div>
		<div class="msg" style="float:right"></div>
	</form>
</div>
<div class="row">
	<!-- Latest Tweets List -->
	<div class="pagination">
		<ul> 
			<li class="prev"><a href="#">&larr; Previous</a></li>
			<li class="next"><a href="#">Next &rarr;</a></li>
		</ul>
	</div>		
	<table class="zebra-striped">
		<thead>
			<tr>
				<th width="80%">Message</th>
				<th width="20%">Since</th>
			</tr>
		</thead>
		<tbody class="tweets">
	#{list tweets}	
		<tr><td>${_.tweet}</td><td>${_.createDate.since()}</td></tr>
	#{/list}
	#{else}
		<tr><td>No tweet</td><td></td></tr>
	#{/else}
		</tbody>
	</table>
	<div class="pagination">
		<ul> 
			<li class="prev"><a href="#">&larr; Previous</a></li>
			<li class="next"><a href="#">Next &rarr;</a></li>
		</ul>
	</div>

</div>

<!-- JS -->
<script type="text/javascript">
// Capture Form Submit Event
$('form').submit(function() {
	// Define Create Action
	var createAction = #{jsAction @create(':tweet') /}
	
	$('.msg').load(createAction({ tweet: encodeURIComponent($('input:first').val()) }));

	setTimeout(function() {	
		$('.msg').empty();
	}, 3000);
	// Don't let the browser redirect
	return false;
});

$('li.prev > a').click(function() {
	var prevAction = #{jsAction @previousPage() /}
	
	$('.tweets').load(prevAction());

	// Don't let the browser redirect
	return false;
});

$('li.next > a').click(function() {
	var nextAction = #{jsAction @nextPage() /}
	
	// Call Create Action
	$('.tweets').load(nextAction());
	
	// Don't let the browser redirect
	return false;
});

var currentAction = #{jsAction @currentPage() /}
var countAction = #{jsAction @count() /}

setInterval(function() {	
	$('.tweets').load(currentAction());
}, 5000);

setInterval(function() {	
	$('.count').load(countAction());
}, 5000);

</script>

p. 

p. Have a look at this code and note especially:
* the 2 @<div class="pagination">@ containing the button Previous/Next
* the @$('li.prev > a').click@ containing the action executed when clicking on the pagination buttons and calling the @nextPage@ & @previousPage@ controller actions added in previous paragraph. It only reloads the list into the table.


p. ==<br/>==

h3. <a name="play-route">Add route for module crudsiena</a>

_*conf/routes*_

bc. *      	/admin             						module:crudsiena

p. ==<br/>==

h3. <a name="play-messages">Correct default play messages bug in Heroku Play integration</a>

I've discovered that default messages in Play framework itself (PLAY_PATH/resources/messages) are not provided by Heroku (certainly a bug which will be corrected soon). Until the correction, add the following in your messages file.

_*conf/messages*_

bc.. since.seconds = %s second%s ago
since.minutes = %s minute%s ago
since.hours   = %s hour%s ago
since.days    = %s day%s ago
since.months  = %s month%s ago
since.years   = %s year%s ago
since.format  = MMM d, yyyy

# Validation messages
validation.required=Required
validation.minSize=Minimum size is %2$d
validation.maxSize=Maximum size is %2$d
validation.email=Invalid email address
validation.range=Not in the range %2$d through %3$d
validation.min=Cannot be lower than %2$d
validation.max=Cannot be greater than %2$d
validation.match=Must match %2$s
validation.isTrue=Must be checked
validation.equals=Must be equal to &{%2$s}
validation.invalid=Incorrect value
validation.future=Must be in the future
validation.after=Must be after %2$s
validation.past=Must be in the past
validation.before=Must be before %2$s
validation.object=Validation failed
validation.notFound=Object not found for id %2$s
validation.url=Not a valid URL
validation.phone=Not a valid phone
validation.ipv4=Not a valid IP address
validation.ipv6=Not a valid IP v6 address

p. ==<br/>==

h3. <a name="play-test2">Test your app again</a>

bc. play run

Go to *http://localhost:9000* 

p. ==<br/>==

h2. <a name="deploy">Deploy on GAE</a>


bc.. > export GAE_PATH=/.../appengine-java-sdk-1.6.0/
> play gae:deploy --%prod


p. ==<br/>==

h2. HAVE FUN...
