Adding a Table¶
From the application dashboard, expand the "Build" option, then click Tables from the sub-menu. Now click on the "Action button" on the right, under the heading "Create another table". You will then be presented with the table editing interface.
Define Structure¶
Start by giving your table a title. Titles you give to tables will automatically be pluralised by Kumulos as a matter of convention.
When you’ve given the table a name, drag and drop the field types from the right hand column into the middle table editor pane. This will allow you to name the field and set various options.
Save¶
When you're happy with the structure you've defined, click the "Save" button.
Editing a Table¶
- From the Build service, select Tables
- Find the table you wish to delete
- Click the pencil edit icon
- Make edits and click "Save"
Deleting a Table¶
- From the Build service, select Tables
- Find the table you wish to delete
- Click the "-" delete icon
- Confirm deletion
Hookup¶
You may wish to leverage the power of Kumulos API service to access existing data on your own data server. This can be accomplished with Hookup. Hookup allows you to connect Kumulos to an existing MySQL database. This means that your existing tables and data can be accessed through Kumulos, and any new tables you create will be stored on your data servers.
To use Hookup, simply select the "Hookup an existing database" option during your Build service enablement step.
After that, when visiting the Tables section, you will be able to create new tables in your external database, or import the schema of existing tables in your external database.
To import schemas, just click the "Import" button from the Tables screen.
When you import a schema with Hookup, we mark the imported tables as 'read only' from the tables editor. We do this to prevent accidental modification of a schema that may be used by another application outside of Kumulos.
Deleting an imported schema will not drop the table from the external database.
Imported schemas behave just as regular tables in Kumulos, and you can use them in API methods or manage the records in them through the Kumulos console.
When you make changes to external schemas and you want to update Kumulos, simply delete the imported schema from Kumulos and import it again.
If you create new tables with Kumulos, they will be stored in the external database and support the full range of Kumulos editing options. If the table was created by Kumulos and you choose to delete it, the table (and all its data) will be dropped from the external database.
Notes on using Hookup¶
MySQL Requirements¶
- Server version 5 or greater
- A user account with INSERT, SELECT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX permissions
-
Access allowed from the following IP addresses:
-
46.101.151.15
- 138.68.84.140
- 138.68.87.27
- 138.68.96.231
- 165.232.66.80
- 165.232.66.87
Checking on Hookup status¶
The current state of the Hookup connection can be viewed from the Build dashboard. This widget will highlight any latency or connection issues that can affect your app's performance.
Any error state will also bubble up to the various dashboard views in the form of an alarm badge or status text.
It is possible to edit the Hookup connection details directly from here by clicking the cog icon on the Hookup card.
Note Hookup applications will not warn you when you have not backed up the data as this is the responsibility of the external database administrator.
Troubleshooting¶
We are aware of an issue where Hookup can't connect to your MySQL server even after allowing the required IP addresses.
The problem is caused by a MySQL server configuration value that uses an out-of-date password hashing scheme. This scheme is incompatible with the up-to-date scheme Kumulos uses.
A possible solution is as follows (replacing the username and password with your MySQL user details):
SET old_passwords = 0;
UPDATE mysql.USER SET Password = PASSWORD('testpass') WHERE USER = 'testuser' LIMIT 1;
SELECT LENGTH(Password) FROM mysql.USER WHERE USER = 'testuser';
FLUSH PRIVILEGES;
(Source: http://engin.bzzzt.biz/2010/11/12/php-mysql-insecure-authentication/)
The following hosts are known to have the issue:
- HostGator (unresolvable on shared hosting)
- Media Temple (resolvable by plan upgrade and the steps above)
- UK2.net (resolvable by the steps above)
Managing Data¶
Kumulos provides a data browser to manage records in your tables form the console. To use this interface, head to the Tables section of an app's Build service.
From here you can click the table title to open a data browser, or click the add record "+" button to create new records. From the data browser, it is possible to search, add, edit, and delete records.
Backing up Data¶
You can download the structure and contents of your tables at any time, regardless of the selected data provider. This allows you to, for example, restore an individual user of your app to a previous state, recover accidentally deleted data, or export the data for further analysis.
If you have selected to use your own external database provider via Hookup then you can still download and backup any tables and their data used by the Kumulos app. However, we do not take any responsibility for the maintenance or downtime of external databases.
For all apps using managed cloud databases in our Build service, Kumulos maintains both hot-standbys for failover and nightly backups for the purposes of disaster recovery guaranteeing that even in the worst case scenario, we will always be able to restore Kumulos to a point no more than 24 hours ago. However, Kumulos cannot offer the ability to restore an individual apps data to a point in the past. For this, use the Backup feature as described below.
To backup your data, go to the Build Dashboard for your app and click the "Download" button on the right. A temporary zip file will be created containing a CSV export that your browser can download.
To backup very large databases, you may need to contact technical support, who will be happy to assist.
Conventions¶
Field & Table Titles¶
Kumulos will take any input you give to it for table & field names and convert them to lowerCamelCase. This aids consistency and helps ensure that all of your titles are suitable for use in your mobile app’s source code.
Pluralisation¶
All tables created with Kumulos will have their names pluralised. This is a common convention for database table names, because they store collections of objects. For example, if you titled your table “user”, when saved, Kumulos would call it “users”
Magic Fields¶
When Kumulos creates a table, it always adds three fields to the structure for you. These fields are:
ID¶
Every table needs something called a primary key, which is unique for every record. To help you along the way Kumulos will add a field called yourTableNameID which will be a unique unsigned integer.
timeCreated¶
This field will store a timestamp in seconds (in UTC) for each record of when the record was initially created.
timeUpdated¶
The timeUpdated field will be a timestamp in seconds (in UTC) of the time the record was most recently edited.
Data types¶
These are the currently available data types for use when creating your tables:
Boolean¶
The boolean field represents true or false by using integer 1 or 0.
Integer¶
Kumulos' range for positive (unsigned) integer is: 0 to 18,446,744,073,709,551,615
Kumulos’ range for a signed integer is: -2,147,483,648 to 2,147,483,647
Float¶
Kumulos uses a storage medium for float that is precise up to 8 decimal places, and can support up to 14 digits before the point.
Date/Time¶
The date/time field type is a signed integer that is intended to store dates and times as seconds relative to (i.e. before or after) the Unix epoch of Thu, 1 Jan 1970 00:00:00.
This means the usable date range is from Fri, 13 Dec 1901 20:45:52 to Tue, 19 Jan 2038 03:14:07 (i.e -2,147,483,648 to 2,147,483,647).
For dates outside of this range, use a positive (unsigned) integer.
String¶
The string type will store strings up to 512 bytes long. The ASCII-compatible UTF8 encoding is used for storage.
Text¶
Text can store up to 64kB of text in UTF8 encoding.
Data¶
A data field will store up to 32MB of binary data such as files and images.
If you are using the Android SDK or the JSON RPC Interface then you must base64 encode the binary data before transport. Similarly, when you receive data from Kumulos, you will have to base64 decode it to access the original data. The iOS SDK does this automatically.
Belongs To¶
A Belongs To field stores positive integers, which are references to primary key fields in other tables and is used to express one-to-one and one-to-many relationships. This is discussed in detail in the next section.
Relationships¶
This section covers the basics of relational data modeling and may be skipped if you’re familiar and comfortable with the concepts of database relationship types, or jump ahead to Kumulos specific detail on implementation of relationships
Whilst defining a database structure, you will often need to model data that is related to each other in some way. These relationships occur naturally in everyday life but can be a little tricky to abstract and break down into a highly structured form.
To aid with modeling related data, some common paradigms are used in database design. Three relationship types allow us to express the dependencies and relationships between data in our database. These types of relationship are:
- One-to-one (commonly denoted as 1:1)
- One-to-many (1:M or M:1 for many-to-one)
- Many-to-many (commonly denoted as M:N)
Relationship Types¶
One-to-one¶
A one-to-one relationship is data that relates to exactly one other piece of data, and vice versa. For example, if your mobile application allows users to enter a biography, which you decide to store in a separate table (so its not returned by every API method that selects data about your users), this would be a one-to-one relationship between users and biographies.
One user has one biography and one biography belongs to one user. The data in your database would therefore look something like this.
One-to-many¶
Using an example of a mobile application that allowed users to upload and share photos, one user would obviously want to upload many photos. Thus the relationship between a user and their photos is one-to-many.
One user has many photos. The relationship between a photo and the user who uploaded it is many-to-one because a photo can only have one photographer. In your database, this would look something like:
See below for details of how to implement this, and all relationships, in Kumulos using the Belongs To data type. Alternatively, an example implementations of the tables and relationships that model photographers is illustrated below.
Many-to-many¶
Many-to-many is a composite relationship that is built out of two one-to-many relationships. This models data that can be related to many things at the same time, and vice versa.
For example, if you were compiling a database of people’s favorite meals, you would have a table that stores meals, and a table that stores people. Now, lots of people like the same meals, and food is so good that it is unreasonable to expect people to only have one favorite. So, we have a many-to-many relationship: many people like many meals.
So, how would that be modelled in a database? Well, you need to create another favorites table that stores which person likes which meal. This table is constructed with two one-to-many relationships and is illustrated below.
- One person has many entries in the favorites table i.e. one person likes many meals
- One meal has many entries in the favorites table i.e. one meal is liked by many people
The main reason for this process of turning many-to-many relationships into two one-to-many relationships (part of something called normalization) is to avoid data replication in the database. This way, if two people like the same meal you don’t store the same meal information twice.
Keep reading for details of how to implement this, and all relationships, in Kumulos using the Belongs To data type. Alternatively, an example implementations of the tables and relationships that model favorite meals is illustrated below.
Kumulos: Belongs To¶
To simplify things, Kumulos doesn’t explicitly deal with one-to-one, one-to-many, and many-to-many; instead, it uses a phrase “belongs to” to express one-to-one and one-to-many relationships This will be a familiar concept to anyone who has ever used Ruby on Rails.
When creating database tables using Kumulos, there’s a special field type called “Belongs To”. If you drop in a “Belongs To” field, you’ll be given three input boxes.
One-to-many¶
Lets go back to our example one-to-many relationship where we have a table storing users and a table storing their photos. The user/photo relationship can be modelled by saying "A photo belongs to a user; and a user has many photos.” users:photos is 1:M; and photos:users is M:1.
In your photos table, you would drop in a “Belongs To” and fill in the values so it reads:
This record belongs to ‘Users’ as (their) ‘photos’. This record has a ‘photographer’.
That relationship tells Kumulos you want to be able to join up to photos. “photographer” becomes a field in the photos table, and refers to the user who took the photograph. On the users table, a dynamic property called “photos” will be available when you define API methods that select users. Now when you select users, you can tick a box to indicate you’d like to get their photos too; and if you’re selecting photos, you can tick a box to get the photographer’s information.
One-to-one¶
On the other hand, using the example one-to-one relationship where users have a biography, you would say "A user has a biography; and a biography has a user". users:biographies is 1:1; and biographies:users is 1:1.
In your biographies table, you would drop in a “Belongs To” and fill in the values so it reads:
This record belongs to ‘Users’ as (their) ‘biography'. This record has a ‘user’.
By using the singular or plural form, Kumulos determines if it is a one-to-one (biography singular), or a one-to-many (photos plural) relationship.
Again, on the users table, a dynamic property called "biography” will be available when you define API methods that select users. Now when you select users, you can tick a box to indicate you’d like to get their biography too; and if you’re selecting biographies, you can tick a box to get the user’s information as well.
Later, when you create API Methods, you can filter the data returned by these dynamic properties.
What Happens When I Delete Records?¶
If you take the users/photos example, and you define an API method to delete a user, then anything that has been defined as ‘belonging to’ that user is also deleted at the same time. So, when deleting a user, all of their photos will also be deleted because in the photos table, they ‘belong to’ a user. This maintains referential integrity and ensures that no data gets ‘lost’ in the database.
Note that if there is a chain of ‘belongs to’ relationships then only the first level will be deleted. So if a photo had comments that ‘belong to’ it, and you delete a user then all the comments associated with that user’s photos would remain in the database.
To make sure you get all the comments, it is a good idea to first select all the photos of the user you’re deleting, and then delete all comments that match the photograph’s ID, and then delete the user (and by implication their photographs) after all comments have been removed.
How Kumulos Determines the Relationship Type¶
Kumulos determines the relationship type from whether or not the word in the “as” section is singular or plural. So, you must use phrases ending with either a plural or singular noun. The word you type in the left-hand box should be singular and the word in the right-hand box can be either singular or plural to express a one-to-one or one-to-many relationship respectively.
Manually Specifying Relationships¶
Kumulos provides the “Belongs To” terminology to try and automate and simplify the data modeling process, but if you’d rather just do it yourself then there’s nothing stopping you.
All of the table structures in Kumulos have an ID generated for them by default. These are unique (per record) and identify one record in each table. Kumulos stores these IDs as positive integers. So, if you wanted to model your relationships yourself you can just use a positive integer data type to store the ID of related records.
If you choose to model your relationships yourself it affords you greater control over what happens when you delete data (because nothing is automatically deleted), but it costs you the ability to automatically select or delete related data for a given record(s). To do that, you’d have to create your own API methods to handle related data and call them from your app.
So, the option to manually manage relationships is there, and in some situations may be advantageous, but if you’d rather stick to the more intuitive “Belongs To” method, that’s fine too!
Further Reading¶
There is a series of blog articles on the Kumulos web-site that describes how to design your app backend, implement this in Kumulos and then exploit in your mobile app:
- App Backend Design with Kumulos (Part 1 of 3)
- Implementing your App Backend in Kumulos (Part 2 of 3)
- App Backend Best Practice (Part 3 of 3)
For those less familiar with the concepts and terminology discussed here, you may find the following links are resources useful as you start to develop database driven applications. They will help you to structure your data more efficiently and understand the key concepts covered here in more depth.
Relationship Examples¶
This section will give you a few examples of tables and their relationships modelled with Kumulos’ “Belongs To”. Examples in this section include:
- Users, Photos, Photo Comments
- People, Meals, Favorites
Example 1: Users, Photos, Photo Comments¶
With this example, you will learn how to model one-to-many relationships by defining structures to store users, their photographs, and comments made on those photographs. Although it’s quite a trivial example it demonstrates a key data model that can be extended in many situations.
Users Table Structure¶
A pretty simple table definition that stores a user's name and the date of their birthday.
Photos Table Structure¶
A data field for the photo data, a title and description constitute the main elements of this table. The relationship expressed is that the photo has a photographer who took the photo, and any given user has a collection of photos.
When a user is deleted, any photos that they’ve taken will also be deleted, but not any comments made on those photos.
Photo Comments Table Structure¶
The comments table stores comments on photographs. This table is essentially just two relationships and the actual comment text.
First, we say that the comment has an associated photo, and the photo has a collection of comments. Next, we say that the comment also has a creator (a user who posted it) and therefore each user has a collection of posted comments.
Because a comment belongs to both a user and a photograph, when either the owning user or photo are deleted, the comment will also be deleted.
“postedComments” is used instead of “commentsPosted” because Kumulos determines the relationship type from whether or not the word in the “as” section is singular or plural. So, you must use phrases ending with either a plural or singular noun.
Example 2: People, Meals, and Favorites¶
This example will show how to model the many-to-many relationship between people and meals that they like.
People Table Structure¶
Here is another extremely simple table to store information about people. Essentially we want to know their name and why they like food.
Meals Table Structure¶
Another short table definition storing a title, description, and the name of a chef who is famous for the meal.
Favorite Meals Table Structure¶
Here’s where the magic of this data model happens. The favorite meals table stores a link between a person and a meal. It also stores what the person likes most about the meal.
The many-to-many relationship between people and meals has been captured by these two one-to-many relationships:
- One person has many favorite meals (through connoisseur)
- One meal is the favorite dish of many connoisseurs
So, to select a person’s favorite meals, you should select from favorite meals (including the meal record) and filter the select by the person’s ID. Similarly if you want to know who likes a particular meal, select from the favorite meals table (including the connoisseur) and filter by the meal ID.
Because a favorite meal belongs to both a person and a meal, if you delete a person then the entries for their favorites will be deleted but the meal itself wont. The same is true if you delete a meal. Any people who like that meal will stay put but any trace of them ever liking it will disappear.