App Inventor and Databases

FusionDB: Using Fusion Tables with App Inventor as a Database

Note: Google was modernizing OAuth interactions in Native Apps for Better Usability and Security, which means, this solution to receive an access token via webview unfortunately does not work anymore starting from April 20, 2017.
Use the built-in fusiontable controls instead!

The built-in Fusiontable controls offer access to Google Fusiontables. Alternatively it is also possible to use OAuth to access the Fusion Tables directly with the web component.

This example uses the Google Fusiontables API Version 2. In this example the user can select, insert, update and delete records from a fusiontable without need to login. My Fusiontable will be used public database for everybody who uses this example.

Why using the web component instead of the built-in Fusiontable controls?

1. It offers different access types:
   a) access the fusiontable of the user with your app (user needs to authorize your app) or
   b) let all users access your fusiontable without need to grant access to users individually
2. Get rid of the Fusiontable controls rotating icon
3. More flexibility and possibilities, see the overview below.
• Working with rows

  See the API documentation: Working with rows

  Task	App Inventor - Fusion Tables Interface	built-in Fusion Tables control blocks	Notes
  SELECT, INSERT, UPDATE, DELETE			see the examples for INSERT, SELECT, UPDATE and DELETE

• Working with tables

  See the API documentation: Working with tables

  Task	App Inventor - Fusion Tables Interface	built-in Fusion Tables control blocks	Notes
  Retrieving a list of tables			see the SHOW TABLES example (App Invventor Classic)
  Retrieving a table
  Creating a table		*)	see the CREATE TABLE example
  Updating the structure of a table
  Deleting a table
  Copying a table
  Importing rows into a table			see the IMPORT ROWS example
  Replacing a table			new in API Version 2: a combination of "delete all rows" and "import rows"

  *) As Gary found out here, it also is possible to create a table with the Fusiontable Controls.

• Working with columns

  See the API documentation: Working with columns

  Task	App Inventor - Fusion Tables Interface	built-in Fusion Tables control blocks	Notes
  Retrieving a list of columns
  Retrieving a column
  Creating a column
  Updating a column
  Deleting a column

• Working with templates

  See the API documentation: Working with templates

  Task	App Inventor - Fusion Tables Interface	built-in Fusion Tables control blocks	Notes
  Retrieving a list of templates
  Retrieving a template
  Creating a template
  Updating a template
  Deleting a template

• Working with styles

  See the API documentation: Working with styles

  Task	App Inventor - Fusion Tables Interface	built-in Fusion Tables control blocks	Notes
  Retrieving a list of styles
  Retrieving a style
  Creating a style
  Updating a style
  Deleting a style

1. Authenticate via OAuth 2.0

For your own app, first register your application as described here, to be able to use OAuth to access Google APIs.
Then use these blocks for the further basic steps of the OAuth process.

Access Types

a) access the fusiontable of the user with your app

In this case you only add your client id and client secret into the app. Then a Google OAuth dialog will be displayed to the user, asking them to authorize the application to manage their Fusion tables.

b) let all users access your fusiontable

To prepare the login free access, do a run with the interface with your login and store the received refresh token as variable in your app. Also add the tableId of your table.

Then you can build your app, publish it and run the interface with the stored authorization without need to login anymore. The user will then access your private fusion table with owner permissions.
Note: Google recommends to create a separate application-owned account instead of using your own Google account.

API Examples

Example: CREATE TABLE

The following snippet creates a table on first run of the app, in case no tableId is defined.

Example: INSERT

The following snippet inserts a row into the table following the SQL Reference Documentation of the Fusion Tables API.
Before the query is executed, it will be checked, if the product and quantity textboxes are not empty.

Recommendation: to find out, if the SQL statement is correct, right click onto the join block of the sql statement and select Do it. Forgetting spaces is a frequent error....




The runQuery procedure executes the SQL statement using the web component.
If the request succeeds, the server responds with the 200 OK status code and the requested results in typed JSON format by default. You can also obtain CSV formatted data with the query parameter alt=csv, which I did here in the example.

Example: SELECT

The following snippet queries the table following the SQL Reference Documentation of the Fusion Tables API.
Recommendation: to find out, if the SQL statement is correct, right click onto the join block of the sql statement and select Do it. Forgetting spaces is a frequent error....


The result will be displayed in a listpicker.

After picking an item from the listpicker, the selected data will be displayed on the screen.

Example: UPDATE

The following snippet updates a row following the SQL Reference Documentation of the Fusion Tables API.

For UPDATE statements the first step to be done is to get the ROWID of the row to be updated with a SELECT statement. The second step is to do the UPDATE.

Recommendation: to find out, if the SQL statement is correct, right click onto the join block of the sql statement and select Do it. Forgetting spaces is a frequent error....

Example: DELETE

The following snippet deletes a row following the SQL Reference Documentation of the Fusion Tables API.

For DELETE statements the first step to be done is to get the ROWID of the row to be updated with a SELECT statement. The second step is to do the DELETE.

Recommendation: to find out, if the SQL statement is correct, right click onto the join block of the sql statement and select Do it. Forgetting spaces is a frequent error....

Example: IMPORT ROWS

Thank you Jim for being the sponsor of the IMPORT ROWS method.
The following snippet provides bulk row import following the Google Fusion Tables API Reference Documentaion.
Maximum file size is 100 MB.

All Blocks of the example (Screen2)

Result table

I changed the sharing settings of the table to "Anyone who has the link can view" to be able to display the result below.

Note

On each run of the app, the refresh token is used to get a new access token. The access token is used to access the Fusiontables API. Once the access token expires, the application can obtain a new one again with the refresh token. The access token normally exires after 1 hour.

Test

Tested successfully on Nexus 5 running Android 5.1.

Questions and Answers

Q1:Can this be used to develop an app and give it to any user or fusion tables stays something that simply helps personal app development? I mean... Can you build an app with fusion tables and publish it on the market?
A: This solution can be used as a nice alternative for TinyWebDB and of course you can publish the app...

Q2:Very interesting but... In that case the fusion table would be on my google docs, right? Or in user's? And... All users will put data in the same fusion table?
A: The fusionDB example is designed to access fusion tables, which are in the Google Docs of the developer of the app. As already mentioned, Google recommends to create a seperate application-owned account instead of using your own Google account. All users will use the same fusion tables and can execute insert, update, select and delete statements, depending on how your app is designed. It is also possible, that each user uses his own fusion tables. In this case the user has to authorize your app to be able to manage the fusion tables of the user.

Q3: In FusionDB tutorial, I don't need built-in Fusiontables control right? But is the token really important, because I don't know how to configure runQuery procedure. The runQuery part is the only misunderstanding I'm doing.
A:Correct, as the example is designed, instead of the built-in fusiontable control it uses the web component and OAuth to have a login free access to the table and to get rid of the rotating waiting icon of the fusiontable control. With this solution, the access token is very important to access the table, because else it will not work. After buying the source code you will see, how the OAuth process works in Screen1.
Alternatively you also can use the SQL statements from the example together with the fusiontable control. Just use the FusiontablesControl.Query block to assign the SQL statement and call the FusiontablesControl.SendQuery procedure, see also the Pizza Party tutorial.

Q4: Does 'solution to receive an access token via webview unfortunately does not work anymore starting from April 20, 2017' this affect the FusionDrive mashup? In other words will I need to start looking for a better solution? I have a few apps that I use with the Fusion Drive mashup. Will they stop working after April 20?
A: This will affect all OAuth examples. As far as I can see, only authorization requests using the webview are not possible anymore, which means, if you already have your refresh token, then you still should be able to use the refresh token to get a new access token...

Back to top of page ...