Home » Articles posted by eixerits

Author Archives: eixerits

Encrypting passwords in AppEngine

About this tutorial

This post explains how to encrypt passwords in order to implement a login system in AppEngine.

When we set up a login system our main concern is security. After all this is what login systems are about. And security means making it as hard as possible for a hacker to fake the system in order to get information from other users. In the world of information security the ability for our app to resist hacker attacks is called resilience.

Most of our efforts regarding resilience should be addressed to make it hard for the hacker to find out the passwords in case he’s able to gain access to the users database. And a great way to achieve this is by encrypting the passwords.

Enough obvious introductions, since this post intends to be eminently practical. Those interested in deepening further in this fascinating world can skip the remainder of the post and go directly to the excellent article by Brian M. Hunt Storing passwords using Python which in turn is based on the theory explained in the Q & A about Information Security from Stack Exchange.

What are we building

We’re going to publish the necessary services to store encrypted passwords and to verify the validity of a user-password combination.

The guys above have performed 95% of my task, so my job was reduced to some adaptations in order to make it work in a real app. But since these adaptations took me quite a few hours I’ve thought this post might be useful for someone else. My humble contribution can be summarized as:

  • Create the full datastore model for users database
  • Create the set of services necessary for accessing to the datastore from a client app
  • Wrap the services so that they can be called from a JQuery client
  • Solve some issues at the time of using the pycrypto library in Windows

Some screenshots


As we can see, the sources contain a test page where we can try our services. We’ll typically follow 3 steps:

  1. Create a user including its password among other information
  2. Verify the user can login with this password
  3. Inspect the datastore in order to check how information is stored



How to install

The steps to follow are the usual ones for any AppEngine project:

  1. Download the sources in any folder
  2. Create a project in AppEngine Console (https://console.cloud.google.com/)
  3. Edit the app.yaml file in order to indicate the name of the project you’ve just created
  4. Deploy the project (e.g. using AppEngineLauncher or typing py update . from the folder you’ve created in step 1)

At this point, you should be able to execute the test page in the URL:


If you have any problems with the installation, the post (PENDING) contains some more details.

Once the installation is ok, you should delve into the sources in order to understand the code and adapt it to your specific needs. At the time of testing the services locally you may encounter some problems with the pycrypto library as it happened to me. If this is the case, you can have a look at this post (PENDING) where I explain several ways to solve them.

How it works

All the encryption stuff is encapsulated in the encrypt.py module, which is copied with nearly no modifications from the afore mentioned M. Hunt’s post. The algorythm is perfectly explained there so I’ll simply show the code:

This code should work for you without any adaptation, but you should pay attention to the ITERATIONS_2015 constant since it determines the hashing speed and hence the time it takes for the user to log in. Take into account that hashing must be slow for the system to be secure, but you can of course decide how long are your users willing to wait. In my case, I’ve made it slightly faster than current recommendations. Theoretically, you should make this adjustment only once since the algorythm increases the iterations periodically as a counterpart to growing computation power. However, you’ll probably want to review it from time to time based on real experience.

Once we have the encryption module, developing our services is fairly easy. First off we’ll define our user’s datamodel:

class User(ndb.Model):
  username = ndb.StringProperty()
  password = ndb.StructuredProperty(Credentials)
  name = ndb.StringProperty()
  mail = ndb.StringProperty()
  icon = ndb.StringProperty()

Notice we could have integrated User and Credential entities into a single one since we’re actually going to access to them together. However, I decided to maintain them separate because it can eventually allow me to reuse Credentials in other systems.

Now let’s see how to add a new account:

  newuser = User();
  newuser.name = name
  newuser.username = acct
  newuser.mail = mail
  newuser.icon = icon
  c = Credentials()
  newuser.password = c
  pkey = newuser.put()

And this is how we can validate a user – password combination:

  acct = self.request.get('username')
  pwd = self.request.get('password')
  results = User.query(User.username == acct).fetch(limit=1)
  if len(results) > 0 :
    c = results[0].password
    if not c.verify(pwd):
      # ok
      # error 

And that’s all. Of course I’m omitting the validations and other stuff. You can find everything in the code any it should be trivial to understand, but if anything remains obscure I’ll be pleased to clarify it. Just a couple of final remarks about the implementation:

  • The services are prepared for being called from a JQuery app. For this reason, if they are called with a “callback” parameter, the response includes this parameter in the format expected by JQuery
  • For debugging convenience, services can be called indistinctly via GET or POST. In your real app, you’ll definitely prefer to enable only POST. This is as simple as removing the get function from each class.

Further ideas

The whole login system has a great deal of possible enhancements, which are mentioned in the post (PENDING). As for the specific ones concerning password encryption, I’ll simply address you to those mentioned by M. Hunt in the  post used as a reference:

  • Keep track of failed attempts (and take some action once a certain threshold is surpassed
  • Store a list of computers (or IP addresses) authorized and perform the corresponding validation when a login is attempted
  • Two factor authentication as an additional security mechanism for critical operations. Particularly, Mobile two-factor authentication is currently receiving great attention and it would be a nice enhancement for my apps
  • Avoid storing the iterations and salt with the derived key (since the hacker will probably gain access to all of them and his decryption task will be eased)

I think Hunt’s job is already excellent without this stuff, but we’ll have to be attentive to his progresses.

A chat service for JQM


This post explains how to implement a chat system using JQuerymobile in the client side and AppEngine in the server side. The chat will be integrated into a more general app which is explained in A JQM real app. The present post focuses on the chat functionalities but the whole app explanation and its sources can be found in the post above.

About this post

This is not a step by step tutorial. It includes some code excerpts that should be enough to understand how the module works but that will not work directly. If you want to use, adapt or further analyze this code I recommend you to download the sources as explained in the download chapter.


When facing the requirement of implementing a chat system for an app, I considered two alternative approaches:

  1. Push: whenever a user types a chat, all the addressees will receive a notification and some kind of alert will be displayed in their device
  2. Pull: when a user types a chat, it’s stored on a database but nobody is notified. Clients are responsible to periodically inspect the database in order to find out new chats and display them

I decided to develop a couple of prototypes to help me choose the right alternative. I counted on the valuable help of two Google tutorials:

  1. Push: The CloudGuestBook post explains how to create a push app that is able to send messages. It’s supposed to take only 20 minutes though it will be quite a few more if you encounter problems of library versions as it happened to me. Anyway,when you overcome these issues you’ll finally get an HTML app that is able to capture inputs and send them via a push system. The remaining work is integrating this code into your environment, which in my case was JQuerymobile
  2. Pull: The SendGuest post explains how to create a web app to exchange messages among several browsers. It will likely take you 20 minutes to get a running app, and once again the pending job is to integrate it into your environment

At this point, practical reasons led me to the pull option because I had the sensation the push app would be quite more difficult to integrate into a JQuerymobile app and specially to maintain it compatible with all platforms. I mean, with the first alternative the app is natively integrated into the device platform and hence can be affected by any change in libraries, packages, etc.

So this tutorial will not cover the push system, even though I admit this is the “professional” option used in whatsapp and similar apps. Its main advantage is that it builds on top of the device notification services so it’s not necessary for the app to be permanently running in order to get notifications of new messages. However, a pull solution covers equally all the app requirements and looks fairly easier to build and evolve.

What are we building

The chat system will allow users to contact each other in order to ask questions, organize meetings, etc. The functional requirements will be:

  • Chats are messages that form part of a conversation. User can either add a message to an existing conversation or create a new conversation
  • Conversations can be either general purpose or associated to a specific event
  • Conversations are public. There is currently no way to restrict a conversation to a group of users
  • The user can “like” an answer so that best comments can be easily spotted

Some screenshots


As explained above the sources for this post can be found in Download chapter of the A JQM real app post.

How it works

The module is inspired in the tutorial SendGuest as mentioned above. However there’s an important structural change in that we’re not going to use server templates to generate HTML. The reason is that it’s easier to get a homogeneous interface with the whole app and also that it will be easier to provide offline capabilities (see the Further ideas chapter). A full explanation of this decision can be found in the post An architecture for JQM.

Our data structure will consist of 3 entities:


  • Group: General classification of conversations. There’s a group for each event and a special one for the general chat
  • Conversation: Group of related messages.
  • Message: The proper chat messages. They are always inside a conversation

These entities will reside in the datastore and we’ll provide the following services to maintain them:

  • getmessages(conversation_id, since): retrieves all messages belonging to a given conversation. The optional parameter since allows to get only messages older than a certain date. This is useful when to optimize traffic because we can avoid sending messages that are already read in a previous call
  • addmessage(author, username, message, conversation_id): adds a new message to a given conversation. The username and author parameters allow identifying the author of the message
  • delmessage(message): deletes a given message
  • getconv(group): retrieves all conversations belonging to a given group
  • addconv (group, author, title): adds a new conversation to a given group
  • delconv(conversation_id): deletes a given conversation
  • addlike(username, delta, message): adds or removes a like to a given message (delta = -1 to remove)

Conversations screen


The conversations screen shows the list of conversations associated to the current event. The screen is accessed via a “Chat” button:


The conversations list is shown in the chatlist page, which contains a chatlist_ul listview initially filled with a static text “Loading…” and dynamically replaced by the list of conversations using the following javascript:

An important point to remark is that we’re obtaining the list of conversations calling the getconv service. Here’s the python implementation of this service:

This code can be better understood having a look at Tips&tricks post. Here are the most remarkable aspects:

  • We start creating a Conversation entity according to the data model defined above
  • The service itself can be accessed by either get or post
  • gql_json_parser and obj_format are utility functions that handle specific formats (keys, dates, etc.) when converting the datastore return into a JSON string

It’s necessary to insert the callback received in the answer in order for JQueryMobile to handle the answer correctly

Chat screen


The chat screen provides 3 functionalities:

  • show the list of messages associated to the current conversation
  • allow to add new messages to the current conversation
  • add or remove a like to a message

This window is a

with “data-role = page”, accessed via a link from the conversations screen:

Remarkable points:

  • There is button at the top-right to allow scrolling to the bottom of the page. This is achieved with the javascript code $.silentScroll(15000)
  • The chat list is shown in the chat_ul listview initially empty and dynamically filled using the javascript below
  • The chat_id hidden input contains the current conversation. It’s informed externally before showing the window (see the addlist function above)

The chat_message button adds the content of the chat_message textarea to the chat list using the javascript below. The action is also performed when the user types [CR] by means of the following code:

The chat messages are obtained when showing the page, and after this moment we start a timer in order to periodically refresh the content:

We’re using the services getmessages and addlike to obtain the list of conversations and to add a like to a message. Here’s the python implementation of these services:

Finally here’s the code associated to the chat_send button:

Further ideas

The current app requires an internet connection to work properly, but the architecture is prepared to overcome this restriction. The post An architecture for JQM with AppEngine explains how to adapt the current app in order to provide offline capabilities.

An architecture for jQueryMobile with AppEngine


HTML5 is becoming extremely popular as the development environment for mobile apps. It’s standard, multiplatform, and easy to learn because it builds on top of consolidated web technologies such as HTML and Javascript.

However, it’s not enough to decide on using HTML5. Some more details need to be taken into account at the moment of structuring our apps. I’d highlight three basic decisions:

  • Libraries to use (JQueryMobile…)
  • Framework (Phonegap, Sencha…)
  • Server side programming (PHP, Python…)

If you choose the most complex choices (specially frameworks), the whole architecture will probably be determined. But the simpler choices will still leave some alternatives open. This is the case for the sample app presented in this site because it uses no framework, only JQueryMobile library and the server side is programmed in Python. This particular choice might appear to lead us to a traditional web approach, but as we’re going to see, this involves some limitations which make it preferable limiting the web server role to data access. For the comparison of both methods, we’ll follow the example of a chat system like the one developed in the sample app.

The web approach

Next diagram shows the typical approach to handle dynamic contents in web applications:


The important point to remark is that dynamic view is delegated to the server. For example, next diagram shows what happens when the user types a chat message:


We typically use a template renderer such as Django, Ninja or PHP to produce HTML in the server side. For example, the template to show the list of messages would look like this:

{% for chat in chat_list %}
<ul data-role=listview data-filter=true>
<li data-icon=false>
<p>{{ chat.text }}
{% endfor %}

The problem is that the HTML generated will not appear nicely in the browser, so we need to enhance it in the client side with a Javascript like this:


Client-side approach

I have to admit that initially I didn’t stop much to think about architecture and started developing the app following the scheme above. However, as the app evolved I started to find some difficulties to achieve a homogeneous interface between the client HTML and the one generated in the server side (such as the Javascript used to enhance the listview in the code above). I finally achieved an acceptable result using CSS (sometimes slightly redundant) and some other tricks, but I had the feeling that someday I should find out an alternative approach. And the day arrived when I started worrying about offline capabilities.

Indeed it’s usually desirable for a mobile app to work, at least partially, when no connection is available. The fact of using HTML might suggest that only online can be supported, and actually this is true if we follow the architecture depicted above. But this architecture is not the only alternative. If we think in terms of the pattern known as Model-View-Controller (MVC), this architecture delegates to the server the whole model task and also the dynamic part of view. If we want to support offline, we should allow the client to undertake these tasks when the server is not available. In other words, we need a couple of adjustments:

  • Decouple the view from the server
  • Provide a mechanism to read and write data when no connection is available

The architecture presented in this post solves only the first part, but leaves the structure well prepared for the second one. The Further ideas paragraph explains the solution I have in mind for it.

So, how can we decouple the dynamic view from the web server? The problem in the architecture below is that the template pseudo-language needs to be interpreted in the server in order to get its HTML translation. While a client side renderer can be attempted, I find preferable to forget about templates and use Javascript and Ajax to achieve dynamism. The new architecture would look like this:


The server is now just a service provider that receives requests to read and write information, while control and view is fully managed in the client side. This approach can remind us of SOA architectures, where two servers communicate with each other playing publisher-consumer roles. The difference is that in this case the consumer is a client, but the idea remains the same. We simply need the publisher server to perform some tasks, mostly related to database access.

The sequence of events when the user types a chat message would now be:


The example above can be converted into something like this:

$.getJSON (SERVER_URL + “/” + service + “&callback=?”, {format: “jsonp”}, function(data){
var li = “”;
$.each(data, function(index, d){
li +=     ‘<li><p>’ + d.text + ‘</p></li>’;

This code looks as simple as the web server solution (actually simpler because the content is generated and enhanced in the same place), but unluckily there are other places where the solution becomes uglier. For example, this is the code required to show a list of chats when a button is pressed:

$( “#chat_button_event” ).on(“click”, function() {
current_chat_group = this.parentNode.parentNode.childNodes[0].childNodes[0].childNodes[0].textContent;
$(‘#chatlist_title’)[0].innerText = current_chat_group;

There are smarter solutions based on encapsulating code in order to make it easier to read and maintain, but that’s obviously more difficult when compared to the web server solution where a template would make the code simpler. That’s the price to pay for a better integrated, potentially offline app. At least I was pleased to pay it in my app.

Implementation note

It’s not the goal of this post to go deeper into implementation details, but as a short introduction it may be useful to remark that the simplest way to achieve the server role as a service provider is via JSONP calls, which allow to overcome cross-origin limitations. The Tips&Tricks post covers some details about this solution and for a fully detailed overview you can look up at sources of the sample app mentioned above.


Next table summarizes the pros and cons of both alternatives examined in this post:

Web approach Client side
Easy to learn for those familiarized with web development Actually not so different. Just some additional Javascript skills required
Less complex javascript Try to encapsulate code into reusable functions easier to read and maintain
Offline hardly achievable Offline viable
Complex to integrate the HTML generated in client and server The client handles all the HTML and hence it’s easier to achieve a homogeneous interface and behaviour

Further ideas

Actually the main benefit of the client side approach shown in this post is that it enables offline capabilities. The idea is to use local storage to read and write data when there is no connection:


This local data should be stored while in online transactions. For example, each time we get the list of messages we store a copy in local storage so that when we have no connection we can get data from this local copy. The following diagram shows what would occur if we get the chat list and then try to get it again after losing connection:


I intend to implement this idea in further apps and when this occurs I’ll update this post correspondingly. Hope to see you here!

A jQueryMobile real app

About this tutorial

The posts in this site have a different degree of detail when compared to most tutorials. I mean such tutorials use to provide the full code with detailed explanations but, for a matter of space, they do not build full applications. So they are very useful to find the solution to specific problems but fail to cover the nuts and bolts for passing from a prototype into a production application. On the contrary, this site intends to help people solve real issues faced in practice, and the best way I know to achieve this goal is presenting a real app and explaining how the trickiest issues were solved.

This broader scope comes at the cost of less detailed explanations, but my goal is to provide enough information for an intermediate reader to understand the module and be able to build or adapt it on his own. However sometimes it’s difficult to find the right balance so if anyone is interested in any clarification please leave a comment or contact me and I’ll be pleased to answer. Also remember that all details omitted in the explanations can be discovered by inspecting the sources.

What are we building

The app was born as a school entrepreneurship project. The requirement was to develop and publish a marketable app that could potentially attract thousands of downloads. The latter part is not achieved yet, but I’d like to think the pending job is more commercial than technical, so that shouldn’t bother us for the moment.

The app is intended to help tourists enjoy the visit to a city. It’s initially prepared only for Barcelona but it should be easily adaptable to any other city. As a differentiator factor, we decided the app should be very “social”. We’re going to see its three main aspects:

  • Chat system: allows users to contact each other in order to ask questions, organize meetings, etc. There is both a general chat and a specific one for each event and attraction. The user can “like” an answer so that best comments can be easily spotted
  • Events page: it’s maintained dynamically via a spreadsheet. The user can join any number of events and as a confirmation the app sends him an automatic email with an itinerary attached
  • Login system: the user can optionally identify himself into the app. Some options are only available to identified users as a way to enforce some kind of commitment

The app has also some static content, but we’re not going to see it since it’s quite trivial to create. The sources provided do not contain this content so we’ll obtain a somewhat simpler app than the real one.

Some screenshots



Client app: Sources of JQueryMobile app, including both HTML, Javascript and static resource files Download
Server: Sources of the server side. This is python code and some static files ready to be published in AppEngine Download
Standalone version for web: If you want to have a quick look at the app, here is a (somewhat different) version for web Try
Google Play app: The fastest way to test the app in your mobile, but of course you’ll miss the chance of enjoying its development! Download

How to install the app

The sources downloaded should allow you to run the app both in your computer and in your phone, except for the following considerations:

  • The chat will point to a demonstration server. You must inform your server URL editing the touristapp.js file located in the client /js folder and changing the SERVER_URL variable
  • The events page will show some demonstration events. You can create your own events via a googledocs spreadsheet as explained in the Integrating Google forms with JQM post

Once this is done there are many ways to execute the app. You’ll probably have your own method, but just in case here is a summary of the methods I use depending on my needs:

Server: After downloading the zip file, there are typically to ways of testing it:

  • Rapid try&error changes: unzip the server file into any folder and then start appdevxxx on that folder or open AppEngine launcher and create a project pointing to it (see instructions). If you use this option, you’ll have to inform the assigned URL (typically something like http://localhost:20080) in the SERVER_URL variable indicated above
  • Once you’re sure everything is ok: use appdevxxx or the Deploy option of AppEngine launcher.

Client: After downloading the zip file you can test it in several ways:

  • Interface tests: unzip the client file into any folder and double-click at the index.html file. The app will be shown in your local browser but without functionalities (e.g. if you go to the events page you’ll get a never ending “Loading…” message
  • Rapid try&error changes: unzip the file into the web server directory. For example, if you’re using an Apache web server you’ll typically have an httdocs folder, so you need to unzip the app into this folder and type into the browser the URL http://localhost/touristapp_client
  • Local mobile (rapid mobile test): once you’ve done the previous step, if you plug your phone to the computer you’ll be able to run the app into the mobile browser by typing the URL assigned to the connection (e.g.
  • Once you’re sure everything is ok: Generate the executable file and copy it into your phone. The way to do this will depend on your device, but you may want to try Phonegap, specially the cloud service that allows you to generate a multiplatform app without need to install anything in your computer

In case you have any doubts, this video explains a simple way to publish an app in Google Play using Phonegap to pack it in the Android format (.apk).

How it works

Finally, let’s go to the code. In order to keep this post to a reasonable extension, the explanations are organized as separate links. They are more or less independent so if you’re just interested in one topic, it should be possible jump directly to it. However, I’d recommend having a look at the first post to understand the app architecture.

  • An architecture for JQM with APPENGINE
  • Integrating Google forms with JQM [PENDING]
  • A chat service for JQM [PENDING]
  • A login template for JQM [PENDING]
  • Some nice APIs for JQM [PENDING]
  • Tips and tricks [PENDING]

Further ideas

The current app requires an internet connection to work properly, but the architecture is prepared to overcome this restriction. The post An architecture for JQM with AppEngine explains how to adapt the current app in order to provide offline capabilities.

Life beyond App Inventor

I made my first steps in mobile app development with App Inventor, and I was surprised to discover it was far more powerful than I had initially suspected.

Anyway, the environment has some limits. My latest experience has been translating an app from AI classic to AI2, and I’ve been pleased to realize some of these shortcomings have been overcome (local variables, several screens at design time, integrated block editor…). That’s a good job, but there are some structural aspects remaining and I don’t think it’s in the evolution plan to overcome them. The two key points are:

  • Limited possibilities regarding graphical design
  • Restriction to Android devices

I recently needed to surpass these limits and started looking for alternatives. I tried the whole spectrum ranging from direct java programming with Eclipse to popular frameworks such as Angular JS, Phonegap and Sencha Touch. Rather than discussing the pros and cons of each one, let’s open directly the envelope. And the winner is… JQuery Mobile!


The best way I can find to demonstrate the framework strong points is to explain the experience with my first JQM app. It was actually my 14-years old son who developed the app for a school project, and I just had to complement it with some programming – mostly shown in the JQM page. And no, unluckily I’m not the parent of a new Zuckerberg. He’s got zero programming skills and just a basic knowledge of HTML acquired at school. I know this is the perfect profile for an AI user and he’s actually quite fond of it, but in this case multiplatform and a rich graphical design were necessary issues.

I’ll use this experience to exemplify the three stronger points I find in JQM:

  • Ease of learn and use: For those who are new to JQM the learning curve is quite low, although it will obviously depend on your background especially in HTML5 and Javascript or similar. For example I was not very familiar with them but I had a good knowledge of HMTL and Java, which simplified my learning process
  • Ease of deployment: JQM is inherently portable to any platform supporting HTML5 and from here there are several ways to deploy it in all currently dominating platforms. For example, here’s a video showing the steps to follow for publishing an app in Google Play (and mostly valid also for any other platform)
  • Documentation: A vast community of users maintains an extremely dynamic source of knowledge. Whatever doubt you have, just think of a way to express it in two words and type in Google JQM followed by these two words. There are 90% chances for the answer to appear in the first page. You should first choose the Stackoverflow answer, usually accurate and effective.

In sum, for those who feel AI is falling short for any of their projects, I’d consider JQM as a good alternative. To get started, I’d recommend you to visit the JQM demos site and then follow a tutorial such as the JQM tutorial.

So, thanks a lot to the guys from the MIT for the great moments with App Inventor. I suspect my next steps will be with JQM but AI will be forever in my toolbox and I’m pretty sure I’ll go back to it in future projects. When this occurs I’ll be pleased to add new posts in this site so as to increase my humble contribution to the AI community.

Centralized or decentralized services? This is the question


This post exposes some aspects to be considered at the moment of encapsulating the services we provide via App Engine taking into account performance, bottlenecks, quotas, ease of maintenance, etc.

Concretely, we’ll discuss whether it’s better to deploy projects with specialized services to be consumed by several applications or to deploy these services in the same application project. Both alternatives are based on including more than one service in the same AppEngine project. We’re not going to see how this is technically achieved, but the idea is simply to merge the contents of the respective scripts (e.g. main.py) and the descriptor file app.yaml. You can see an example in this post.


Since an image is worth more than a thousand words, we’ll show the alternatives with an example. Imagine we have two App Inventor apps (or any other client) that require the following services:

  • Mail sending
  • Database storage
  • A specific service for each application (e.g. service1 and service2)

In a decentralized model we would set up two App Engine projects, each providing the three services:


Instead, in a centralized model we would set up specialized services for data storage and mail sending, and then a separate service for each specific service:


Which model is better? Well, I’m afraid there’s not a universal answer because each approach has advantages and disadvantages. So the decision must be taken analyzing our concrete situation. Here are the key points I’d remark for each model.

Decentralized model

In this model we need to replicate the service code in all our projects, so maintenance is clearly an issue. The mail sending or data access will have to be included in every project, and if we want to make a change to a service we’ll have to modify and deploy again all our projects. On the good side, existing apps are somewhat more stable since we can test new versions of the service in new projects without affecting those that are currently in production. It’s also easier to make slight customizations to the service in order to adapt it to specific requirements for each app.

From a practical point of view, this model is likely to make a better usage of Google’s quotas. For example, since there’s a limit of daily mails per application, the quota would be quickly consumed if all mails were sent from the same service, whereas in the decentralized model each application can use its own quota. We could suspect performance to be also affected (e.g. if a single service has to send many mails from diverse apps it might become a bottleneck in pick traffic moments) but I think this is not the case because AppEngine handles scalability gracefully thanks to instances (see this post about request handling in instances) and even more with multithreading support enabled since Python 2.7.

Also in the economical aspect, Google’s courtesy free apps are limited to 10 (see this post) so in a decentralized model we know we’ll have to pay from the eleventh app deployed, whereas in a centralized model this cannot be worked out so directly.

Sharing is also an important aspect to take into account. The clearest example is database storage. If we set up a storage service per app, each database will have its own data and sharing it with other apps will not be simple. For example, if we want to have a unique user’s database it’s necessary to use a specific service for it.

Centralized model

Obviously, the advantages and disadvantages of a centralized model are opposite to the ones we’ve just seen.

First of all, the code is optimally distributed and therefore there’s a single point of maintenance which greatly eases the services evolution. This is the encapsulation model traditionally used in software development where code duplication was anathema (forbidden to copy&paste). But nowadays we’re all learning to live with new concepts such as reduncancy and scaleability so nothing is forbidden anymore.

We’ve already mentioned that here we’ll have to care more about quotas because a centralized mail system is likely to consume quickly the free quota and hence require the premium service much before than in the decentralized model. However this is just theory to be confronted with reality, because a centralized model may also benefit from App Engine optimizations and hence reduce quotas by another side. In practice you should track your usage on the Administration Console Dashboard.

About the 10 applications limit, we’ll consume one for each app providing specific services plus one for each generic service. The latter can be somewhat reduced by grouping several generic services into a single project (for example a generic project could provide both mailing and data storage).


We’ve seen that each model has pros and cons, so we’ll have to decide which one is better suited to our budget, expected traffic, sharing requirements, scalability, maintenance model, etc. The following table is a kind of summary:

Centralized Decentralized
Maintenance V
Quotas optimisation V
Sharing V
Customization V

As a general rule, a decentralized model would be appropriate for small services reasonably stabilized whereas we can choose a centralized model for larger services without critical quotas. In practice the best configuration will probably be a hybrid model. For example we could set up a generic service for data storage, but duplicate the mailing service in every app mixed with specific services:


Anyway, if you’re new to App Engine my advice is to start by a centralized approach, i.e. one project for each service. This will save you work since these services are likely to vary quite often until they stabilize, and with a centralized model adaptations are made in a single place. This way you can do several free trials until you reach the 10 projects, and when you get to that point you’ll have a better idea of your true requirements. You may then discover that some redundancy is necessary, but this will be already tuned code with less chances or requiring maintenance.


Python 2.7 migration demystified

If you’ve been using app Engine for a while or you’ve inherited some old code, you’re very likely to get the following message when deploying some of your projects:

WARNING: This application is using the Python 2.5 runtime, which is deprecated! It should be updated to the Python 2.7 runtime as soon as possible, which offers performance improvements and many new features. Learn how simple it is to migrate your application to Python 2.7 at https://developers.google.com/appengine/docs/python/python25/migrate2

You’ve probably thought like me “hum, it seems I’ve got an old version of something but the service works as usual so there’s no need to bother for the moment”. Well, if this is the case you should start thinking that the moment of bothering has arrived since Google has announced Python 2.5 is not going to be supported anymore starting from January 2014.

Actually this is good news because, as explained in the post, Python 2.7 comes with some very exciting new features. But upgrading apps is always a headache. Some projects may be old and you’re not really interested in evolving them. You just want them to keep working and don’t care if the new features might enhance them. At least this is what happened to me with most projects and that’s why I delayed migration up to now. If I had known it was so easy I would have done it much before! So I’ve written this post explaining in plain text the four simple steps I’ve followed to migrate my projects. Obviously I don’t intend to emulate Google’s handy migration guide which is much more complete and instructive, but I think this simplified version can help those who have as simple apps as mine and are too busy to get through the guide.

The first thing to remark is that you probably needn’t install anything because version 2.7 has already been around for some time and you’re likely to have it already installed. So the first thing to do is check your versions:

  • Python: A simple way to check that you’re in the right version is opening Python from the command prompt and look at the version indicated in the console. Or even simpler, open the “README” file in Python installation folder and look for the version in the first line.
  • App Engine: Open the App engine Launcher and select the “About Launcher” option in the “Help menu”. The window’s last line should read “python27”.

If these two requisites are ok you can proceed with the migration 4 steps:

  1. In the app.yaml file, indicate you intend to use python27 runtime version and threadsafe, and change the scripts extension from *.py to *.app:appengine01

    By the way, I also advice you to increase the version parameter. This way you’ll be able to deploy a temporary version without losing the current one until you’re sure the migration is successful.

  2. In the main.py file, change the way to call the WSGI application:appengine02
  3. In the main.py file, change the parameter in all classes to indicate they extend from webapp2 rather than webapp:appengine03
  4. In the main.py file, change the obsolete imports by their newer path. You can find the full list in this post but in my case only a few lines were affected:


And that’s it. Try to deploy again your project and hopefully you won’t see anymore the warning message. If you have any problem you can always refer to Google’s migration guide which covers all the possible situations. Also, if you think I can help you, you know where I am.

Multiple ORDER BY and massive UPDATE for fusiontables


This tutorial explains how to overcome some Fusiontables current shortcomings, more precisely the issue 584 and issue 417 which affect the following:

  • Order by multiple fields: Currently the ORDER BY clause admits specifying only one column. We’ll add support for multiple columns
  • Massive update: Currently an UPDATE sentence admits specifying only one row, indicating the ROWID in the WHERE clause. We’ll add support for specifying a condition with the same format as any SELECT sentence and therefore with the possibility of updating several rows with a single sentence
  • Massive delete: Same limitation and same solution as the previous point

Additionally, in the Further ideas chapter we’ll mention some possible extensions for the service to include some other functionalities and we’ll see how to create a multipurpose service that provides 3 important services in a single application: the enhanced Fusiontables from this tutorial, a mail sending functionality and the typical custom TinywebDB.

This tutorial can be seen as a continuation of the post Fast & Safe Fusiontables using AI where we explained how to set up a service to access fusiontables and how to call it from App Inventor. The present tutorial has independent sources but some explanations are not repeated. Therefore, for an introduction to the service infrastructure you may prefer to start with a look at first part. Actually in this second part I’ve changed the code quite a lot in order to include the new functionality in a smarter way and also to provide a web interface. After this restyling, there are not many reminiscences left from ShivalWolf’s project (http://ai.kittywolf.net/index.php/WolfFUSIONdb), but the core infrastructure from our service is still based on his excellent job.

What are we going to build

Our goal is to set up a service extending fusiontables functionalities as explained above and also to provide a couple of ways to test the service: from AppInventor and from a Web interface:


Both interfaces work in the same way:

  • Introduce an SQL sentence for reading or manipulating a fusiontable
  • Press a button to execute the sentence
  • Show in the screen the results returned by our service

They also provide some samples to test the service with a very simple fusiontable. In the AppInventor app just select one of the four options available in the list picker Examples, whereas in the web interface you can press on the Syntax link and then the Try it link.

The sources from both interfaces are provided below, so it should not be difficult for you to adapt your own applications and access the service in a similar way. Anyway, in the above-mentioned tutorial there’s an explanation about how to adapt an AppInventor application.

The syntax allowed is the same standard fusion tables syntax except for the following extensions:

  • ORDER BY: The standard clause only accepts one field whereas our service admits several numbers separated by “,” corresponding to the desired ordering columns. The data is returned as a JSON string in the form:

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

A current limitation of this function is that all columns are ordered alphabetically regardless of their type. For example, if we order by a numeric column with values (1, 2, 10) the results will be (1, 10, 2)


[Try it] (this test will access to a temporary service. Please make just a short try and then set up your own service as explained below)

  • UPDATE: The standard clause only accepts updating a single row through the ROWID specified in the WHERE clause, whereas service admits updating several fields in a single sentence by specifying a condition with the same syntax as any SELECT sentence:

ExampleUPDATE TABLE_ID SET Inventory = 357 WHERE Product = ‘Black Shoes’

[Try it] (it will try to update one row but will fail because this is a Google sample fusiontable for which we have no writing privileges)

  • DELETE: The standard clause only accepts deleting a single row through the ROWID specified in the WHERE clause, whereas service admits deleting several fields in a single sentence by specifying a condition with the same syntax as any SELECT sentence:

ExampleDELETE FROM TABLE_ID SET Inventory = 357 WHERE Product = ‘Black Shoes’

[Try it] (it will find one row to delete but won’t do so because of writing privileges)


Here are the sources for our project:

For a detailed explanation of installation steps you can look at the first part. As a summary, after setting up both projects you need to configure the following:

  • AppEngine: Edit the main.py file and replace the contents of the variables google_user, google_password, api_key and AuthKey:

############ 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)

  • AppInventor: In the blocks code modify the variables marked with a comment:

USR_KEY: indicate the same value as in the main.py file (see AuthKey above)

SERVICE_URL: the URL where you’ve deployed the service

You can also inform an SQL sentence into the  TextBoxQuery and the ID of a fusiontable in the TextboxTableId. This way, you can test with your own data instead of the sample.

How it works

We’re just going to see the part of the service that takes care of the enhanced functionality. You can find some more hints about the whole code in the first part, and if you’re interested in further explanations you can leave a comment that I’ll be pleased to answer.

The idea for providing the extended functionalities is to perform some actions in the server side in order to complement the work from the database. Concretely we’ll follow 3 steps:

  • Parse the input sentence in order to detect if any extended functionality is required
  • Call the standard fusiontable service
  • Perform the extra work required by extended functionalitaties, i.e. reorder the rows for extended order by and execute a loop of sentences for extended update/delete

Or course these extra actions will not run as fast as if the work were performed by the database, but according to my benchmarks results are acceptable, at least for my requirements. And they have no comparison with the cost we would incur if we had to do the same job in App Inventor (see image below).

So let’s see how to implement the functionalities in the server. We’ll start presenting three auxiliary functions and then see the algorithms for both the order by and the update/delete.

Auxiliary functions

  • sql_parse: takes care of parsing the input SQL sentence and returning the information about the enhanced functionality required:

Type of action (0 – standard, 1 – update, 2 – delete, 3 – order by)

New SQL sentence (or the original one if no enhanced functionality is required)

Identifier of the table we’re working with

SET clause (in case the sentence is an UPDATE)

WHERE clause (if any)

Special ORDER BY (if any): List of column numbers

URL access method (always GET except for standard UPDATE / DELETE

We’re not going to see the parsing algorithm because it consists of simple string manipulations. Indeed it could be improved in order to provide better information in case of wrong parameters.

  • sql_exec: executes an SQL sentence received as a parameter by calling the fusiontables API. Results are returned in CSV format.

We’re not going to see this code either since it uses the standard mechanism to work with URL by means of the urllib and urllib2 functions. Some more details were provided in part 1.

  • doRender: presents the output for the web interface, using the method explained in the excellent book Using Google App Enginese from Charles Severance.


We’re trying to add support for ordering data by more than one field. Since the standard component is not doing it for us, we could consider 3 alternative methods

  1. .Let the database do the job. The idea is adding to the table a calculated field with a formula to achieve that this field corresponds to the actual ordering we’re intending to obtain. For example, if we want to order by two numeric fields A and B and we know B is lower than 1000, we could use the formula ‘(1000 * A) + B’. You can see an example of this approach in the post A multilingual & multidevice template (part 2).
  2. Let the client do the job. In our case, we should program an ordering algorithm in AppInventor.
  3. Let the server do the job. After receiving the results from the database, reorder the rows before sending them to the client

The first option would probably be the most performant but it’s not pleasant to modify the database for our convenience (probably in many cases we won’t have authorization to do it). Another limitation is that such an ordering would be fixed, i.e. we wouldn’t be able to establish dynamically the order criteria (unless we created a calculated field for each combination).

The second approach is really complicated to achieve with AppInventor. We should implement an ordering algorithm such as the bubble sort, which seems to me as a fascinating but really hard task (and very likely to result in poor performance).

So we’re going to use the third approach. Since we’ve got a service that captures the database results and sends them to the client, it’s easy to “intercept” these results and work on them. Luckily Python provides multiple ways to sort lists (see for example this site). Among these methods I was looking for a solution accomplishing two goals:

  • Good performance
  • Support for a variable number of keys

After searching inspiration on the web (see for example this post) I found no exact solution, but after combining ideas I arrived to an extremely simple solution:

if sqltype == 3: #ORDER BY
resp = sorted(resp, key = itemgetter(*orderby))

Python’s magic! Just a single line and our rows are reordered by the columns specified. Really simple and performant because sorting by key encompasses processing each row only once (i.e. as much calls to itemgetter as rows returned by the sentence).


Here the idea is to perform several SQL sentences (one for each row meeting the filter specified in the WHERE clause). In this case I don’t see how could we solve it in the database, so there are two options remaining:

  1. Let the client do the job. Start with a query to obtain a list of ROWID’s meeting the filter specified in the WHERE clause, and then program a loop that performs an UPDATE / DELETE for each row
  2. Let the server do the job. The same SELECT and loop as in the first option, but without need for the intermediate information to travel between server and client

In this case I wouldn’t say the first option is out of question, because I actually used it in an app when I did not count on the current service. It was a really hard job taking into account that answers from fusiontables are asynchronous. I would not do it again but if you’re interested in see how I solved it, here is how it looked like:


Letting aside the difficulty, the client side solution has the problem of performance. As shown in next image, it involves about double network traffic than our server-side solution. Moreover the additional messages are from the android device, i.e. the most costly.


Imagine what would happen if we were to update 1000 rows rather than 3!

So let’s see how to program the update/delete loop in the server. This time it’s somewhat more than a single line, but not much more difficult:

if sqltype == 1 or sqltype = 2: #UPDATE or DELETE
success = 0
processed = 0
prefix = (‘UPDATE ‘ if sqltype == 1 else ‘DELETE FROM ‘)
for row in resp:
processed += 1
sql = ‘%s %s %s WHERE ROWID = \’%s\” % (prefix, table, updateset, row[0])
csvfile, err = sql_exec(sql, token, “POST”)
if not err:
success += 1
err = ‘Found: ‘ + str(processed) + ‘, Processed: ‘ + str(success)

Testing application

User interface

We’ll create a simple interface to test the service:


  • TextboxQuery: Contains the SQL sentence to be sent. It can be typed directly or filled automatically using the listpicker
  • ListpickerExamples: Shows a list of predefined examples (order by, update and delete). After choosing one of them, the textbox is replaced by the corresponding sentence
  • TextboxTableId: It’s used to generate the sample sentences. The identifier from this textbox is merged into the generated sentence
  • ButtonExecute: Calls the service
  • LabelLog: Shows the results
  • 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 code is quite simple, but some explanations about it can be found in part 1.

Further ideas

The whole idea of our service is to perform some work in the server side to provide functionalities not currently supported by fusiontables. So the same solution can be considered for any other shortcoming that affects us. A good way to start could be extending the syntax for the WHERE clause. For example:

  • ‘ROWID IN (list)’. Requested in issue 417 (and quite easy to implement)
  • GROUP BY: Requested in issue 584 (python is also likely to come to our help. See for example this post)
  • <Condition1> OR <Condition2>
  • <Condition1> AND <Condition2>

Of course some of these extensions may not be trivial to implement but they will always be faster than in the client side.

Another idea you might have if you’ve arrived up to here is that, once overcome the fear to customize the server side, we can profit to provide other services in the same application (remember Google AppEngine’s limitation to 10 free services). In my case, I use the following functionalities:

In order to combine these services, you must merge the code into a single project. You can see an example in the How to send direct mails tutorial and a discussion of whether it’s a good idea to group services in the post Cerntralized or Decentralized services?.

Finally, there are a couple of limitations I intend to overcome in the future. On the one side, differentiate the column type in the order by (e.g. order numbers by its ordinal value rather than as strings). On the other side, it seems the ClientLogin is somewhat outdated and might be discontinued so the service should be adapted to Oauth2.