Home » AppEngine » Fast and safe fusiontables with App Inventor

Fast and safe fusiontables with App Inventor

Introduction

Fusiontables are the mechanism provided by App Inventor to operate on spreadsheet-like data. The component works out-of-the-box and is extremely simple to use, so if you’re new to App Inventor it’s highly advisable that you get familiarized with it following for example the  pizza party tutorial from the AI website. However, once overcome this initial level, you’ll discover some annoying inconveniences in the AI component:

  • Security: You need to grant public access to fusiontables in order for App Inventor to be able to access them
  • Performance: The response takes a while to arrive and the message “Fusiontables: processing query…” is not the kind of experience you want to give to the user while he’s waiting
  • Functionality: There are some syntax limitations that make it quite burdensome to perform some typical access operations (such as a performing a massive UPDATE or estalishing an ORDERBY using more than one field)

This tutorial explains how to set up a service to access fusiontables improving the first two issues (security and performance). The functionality enhancements will be explained in the second part. It’s also important to note that the service deployed in this first part is compatible with the AI fusiontables component whereas the service from the second part has some slight differences in the format of both input parameters and return value.

The core of the solution we’re going to explain is based on this ShivalWolf’s project, but I decided it could be helpful to write this post in order to complement Shiva’s excellent job with some points:

  • Explain how to migrate existing applications to the new method
  • Compare the service with the standard fusiontables from AI
  • Add some functionalities
  • Create a multipurpose service providing 3 important services in a single application: the enhanced Fusiontables from this tutorial, a mail sending functionality and the typical custom TinywebDB.

This first part covers the first two points.

What are we going to build

In this first part we’ll develop and deploy into App Engine a service that allows accessing fusiontables and then we’ll create a simple screen to access a fusiontable using both our new service and standard component from App Inventor:

fusion_01

Both access methods return the same contents because our web component works in a very similar way as the standard component. Very similar means that it allows accessing to data by means of the same SQL-like syntax. Therefore, if you have a working application and wish to adapt it to the new service, you just need to add a web component to the screen (informing its URL as https://yourservice.appspot.com/fusiontable) and make the following adaptations in the code blocks:

  App Inventor standard component New method
Component Fusiontable Web
How to send the SQL Sentence Fusiontable.Query Web.PostTextWithEncoding
Return event Fusiontable.GetValue Web.GotValue
How to parse the return List from CSV table Web.JsonTextDecode

See the code below for further details about these adaptations.

Installation

Fusiontables web service (App Engine)

    1. Download App Engine for Python at http://code.google.com/appengine/. After installing it, run the GoogleAppEngineLauncher by clicking its icon
    2. Download these sources . It is a zip file containg the source code for the Fusiontables web service
    3. Unzip the downloaded zip file. It will create a folder named EnhancedFusionAE_light . You can rename it if you want but then you’ll have to change it also in the app.yaml file
    4. Edit the main.py and replace the contents of the variables google_user, google_password, api_key and AuthKey as explained below
    5. In the GoogleAppEngineLauncher, choose File | Add Existing Application. Browse to set the Path to the folder you just unzipped.
    6. [Optional] Click the Run button to launch a test web service on your local machine.
    7. In the GoogleAppEngineLauncher, choose Deploy

############ SETTINGS ############
google_user = “me@gmail.com#indicate here the gmail address for your app engine account
google_password = “yourpassword#put your gmail password here (I suggest using 2 factor auth and making a separate password for this app
api_key = “yourkey#put the API key from Google API console
AuthKey = “randomkey#make this match the authkey in your code (provides limited protection to your interface to fusion tables)

Client side (App Inventor)

    1. Download these sources and upload them into App Inventor
    2. Open the project with App Inventor. Inform the Text attribute in the TextBoxQuery component with a sentence in a SQL format, including the Id of your fusiontable. You can obtain it opening the table from Google drive and choosing the option About this table. The “Id” field shows the table key
    3. In the blocks code inform modify the initialization of the following variables (marked with a comment)

USR_KEY: indicate the same value you’ve informed in the main.py file (step 4 above)

SERVICE_URL: indicate the URL where you’ve deployed the service (typically something like https://yourapp.appspot.com)

How it works

The server side is a service that receives a SQL string using the syntax allowed by fusiontables and returns the data as a JSON string in the format:

[[row1_field1 row1_field2 …][row2_field1 row2_field2…]…] 

I’m not going to explain the python code (which is not mine), but just remark the key aspects from the main.py file.

  • We query if an authorization token exists and obtain it otherwise:

entry = db.GglQuery(“SELECT * FROM StoredDate WHERE tag = ‘token'”).get()
if entry:
token = entry.value
else:
token = ClientLogin().authorize(google_user, google_password)

  • We access fusion table data as if we were calling google’s URL with the SQL sentence as a post data, and adding the authorization token to the request header:

details = tag.split(“|||”)
URL = “https//www.google.com/fusiontables/api/query”
POST_DATA = urllib.encode({‘sql’: details[1]})
request = urllib2.Request(URL, data = POST_DATA)
request.add_header = (“Authorization”, “GoogleLogin auth=” + token)
request = urllib2.Request(URL, data = POST_DATA)
response = urllib2.urlopen(request)

  • Finally we return the string obtained from the response:

csvfile.write(rsponse.read())
reader = csv.reader(csvfile)
header = reader.next()
outvalue = json.dumps([row for row in reader])
self.response.out.write(outvalue)

Regarding security, we just need to grant access to the fusiontable to the user account we’ve informed in the properties (since this is the account sent to fusiontable request). Typically this account will be the administrator of your AppEngine application so you can restrict access only to administrator informing the value ‘login: admin‘ in the handlers section of app.yaml file:

handlers:
- url: /fusiontable
  script: main.app
  login: admin

That’s all regarding the service. The client side is simple to implement in App Inventor since the web component allows calling a web service and obtaining its return value as the parameter of the gotValue event. The easiest way to work with this data is converting it into a list using the json.decode function as shown below.

User interface

We’ll create an app that allows accessing a table using both the standard Fusiontable component and our service. It will look like this:

fusion_02

The graphical components are self-explanatory. The remaining components are:

  • FusiontablesControl1: The standard AI component we’ll use for comparison
  • Web1: The Web component we’ll use to call our service
  • Clock1: A timer to measure the response time of both accessing methods

Code blocks

The first thing to do is indicate the value for the two configurations variables:

  • SERVICE_URL: You must replace this variable by the URL where you’ve set up the service
  • USR_KEY: Replace it by the authentication key you’ve informed in the main.py file

fusion_03

We have also 4 additional variables:

  • lstResult: Used to store the list obtained from the response (in both access methods)
  • iRows: Contains the number of rows read, in order to show it in the output message
  • iBench1, iBench2:  Store the time before and after obtaining data in order to show the delay time in the output message

And here are the actual functions:

fusion_04

The right hand functions are those that call the standard component and therefore are not object of this tutorial. However they can be useful to see the differences among both access methods.

Our web component requires just two functions (one for the request and the other one to capture the response):

  • Button1,click: When we press the button, we access the fusiontable simply posting to our web service a string with two pieces separated by ‘|||‘:
    1. Authentication key
    2. SQL sentence
  • Web1.GotText: The response is obtained in JSON format, which can be easily converted into a list thanks to the useful JsonTextDecode function provided by the web component

Finally, though it’s not relevant for this tutorial, here’s the code used to show the results in the output message:

fusion_05

Summary

That’s it. If you’ve arrived up to here you should have available a service compatible with the AI fusiontables component, and a sample AppInventor application that can access to it.

So what have we gained? Well, first off security. It’s not necesssary anymore to grant public access to the fusiontable for AI to be able to access to it. There’s also a performance issue. Actually it’s difficult to demonstrate that the current version of our service is faster than the standard component since it will give different results at each attempt, but at least our component does not show the message “processing query” which is not obviously the best way to entertain user’s waiting time. You’ll probably prefer a nicer progress bar or even show no message since the user can continue working without need to wait for the answer. For example, in our sample app you can press the “Web component” button and start editing the textbox before the results arrive. This is the magic of event-driven programming (see this related nice article from Jos Flores).

However, as demonstrated in this snippet from Puravida, App Inventor’s web component would also allow us to overcome both performance and security issues. So why should we bother to deploy a service? Well, the reason is that such a service opens the door for some nice features that can only be achieved in the server side. Indeed in part two we’re going to modify this service in order to add support for some candy functionalities leading to significant gains in performance and code reduction. That’s when the job we’ve done up to now will get more sense!

Advertisements

4 Comments

  1. Dev N says:

    Would this work with AI2

  2. Developer 1 says:

    Thank you for such a good explanation on FusionTables. I am still new to programming in general and have a couple of issues. I have built a data entry app, the primary purpose being for users in different parts of the world being able to enter data. So I need a web database that I don’t have to give anyone special access to be able to enter data. This is an issue with FusionTables. I couldn’t gather from your article, if that is an issue that is resolved using the service that you have created. Thanks in advance for your input and help.

    • eixerits says:

      Hi, indeed my service would allow this. The service reads and stores data in the fusiontable using a username and password stored in the server code, so if your app calls the service there’s no need for it to introduce a username/password

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: