Introduction
Database.com is Salesforce.com’s multitenant Database as a Service platform that aims to be the cloud database engine for application developers. As opposed to Force.com, the Platform as a Service offering from Salesforce.com, it does not support user interface elements such as page layouts or custom views, there is no support for VisualForce, it has no Visual Workflows capabilities and there are no reports and dashboards available. Database.com is focusing on advanced relational database functionalities and supports Salesforce Object Query Language (SOQL) and Saleforce Object Search Language (SOSL) that proved to be popular in enterprise cloud applications development.
Database.com offers a REST API which makes it ideal for mobile and social applications that require data storage with state-of-the-art security model and identity and access management.
Creating objects in Database.com
If you are familiar with Force.com, using Database.com can naturally be related to those experiences. First of all, the user has to sign up at database.com. Once the registration has been completed, the user can login to the platform. The first webpage is a System Overview providing details about number of objects and data records, API usage, etc.
Then we can create our custom objects. In our example we will create a stockprice object (which is essentially a table in traditional RDBMS speak) that will store stock price information such as open and close price, volume, etc. We need to navigate to Create->Objects and click either on New Custom Object or Schema Builder button. In our example we are going to show how to use Schema Builder.
By clicking on the button Schema Builder will open and that is where we can define the object name and a few other parameters:
Once the object is created, we can then start defining the fields. We can use the palette on the left hand side of the Schema Builder and just drag and drop the appropriate data types such as number or date onto the canvas.
.
We can also define validation rules to ensure that the values in the fields (colums in RDBMS world) fulfill the requirements.
Once we are done, we can check our object, it has seven custom fields: AdjClose, Close, Date, High, Low, Open and Volume.
Loading data into Database.com objects
Now, as we have the object created, the next step is to load data into it. In principle we could insert data using the Workbench tool but in this example we are going to use Salesforce.com’s bulk tool called Data Loader that can be very helpful for uploading massive amount of data from our computer. Data Loader is a Windows application that can be downloaded under Data Management->Data Loader menu:
Once it is installed, we can use it to load data in our StockPrice object. The financial data was retrieved from http://finance.yahoo.com.
When we start up the Data Loader, we need to login first. The username is that same that we have used to login to Database.com, whilst the password is the concatenated string of the password for Database.com and the security token that can be generated under My Personal Information menu within Database.com.
From the Data Loader then we can select which object we want to use and we can also the specify the file to be uploaded – it has to be in CSV format with a header. In our case the file format was as follows:
Date,Open,High,Low,Close,Volume,Adj Close 2013-10-09,856.28,862.65,842.98,855.86,2651300,855.86 2013-10-08,865.32,865.98,851.63,853.67,1943700,853.67 2013-10-07,867.45,873.99,864.11,865.74,1293600,865.74 2013-10-04,875,877.51,870,872.35,1358000,872.35
We can also define mapping between file column headers and the field names if we want to.
When the data has been uploaded, we can open Developer Console from Database.com to validate whether all the data are successfully inserted. We need to go to Query Editor, enter our SOQL query like SELECT Close__c, Volume__c from StockPrice__c and click on the Excute button:
Please, note that the API name for the custom fields and the custom table is Close__c, Volume__c and StockPrice__c, indicating that they are custom entities.
Remote access for Database.com
Now, that we have our data loaded into our custom object, the last step is to configure remote access for the remote applications (e.g. our imaginary mobile applications) who wish to run SOQL queries against our object using REST API. The authentication is based on OAuth standard. More details about the Database.com authentication concepts can be read here.
In order to enable remote access we need to go to Develop->Remote Access menu and configure the required parameters. In the Integration section the callback URL is mandatory, in our example we set it to http://localhost:5000/_auth. That is needed for Web Server flow which is the standard authentication method used within the Java template provided by Salesforce.com as a boilerplate application for remote Database.com access.
Accessing Database.com objects from a remote application
Database.com uses OAuth 2.0 authentication to allow users to securely access data. Various authentication flows are supported such as web server flow, user-agent flow, username-password flow. Depending on the actual authentication flow, there are different Database.com endpoints to use. For authorization it is https://login.database.com/services/oauth2/authorize. For token request, the endpoint is https://login.database.com/services/oauth2/token.
Our first example is based on username-password authentication flow. In this case the user already has credentials (username/password) and it is sent as part of the request, togethr with the customer key and customer secret. The customer key and customer segment can be retrieved from Remote Access, we need to navigate to Developer->Remote Access and select the client.
The username is the same that we used to login Database.com, whilst the password is the concated string of the Database.com password and the security token (as you may remember, this is the very same notion that we used to login to Data Loader).
The first step is to request the token from Database.com, we demonstrate the REST query using curl command line tool:
$ curl --data "grant_type=password&client_id=CLIEN_ID&client_secret=CLIENT_SECTER&username=USERNAME&password=PASSWORD" https://login.database.com/services/oauth2/token
This returns a JSON output containing the access_token:
{"id":"https://login.salesforce.com/id/00000001234567/0000000ABCDEFG","issued_at":"12345678","instance_url":"https://computing-business-8114.database.com","signature":"ABCDEFGH","access_token":"12345678abcdefgh"
Then we can submit our SOQL query together with this access token (12345678abcdefgh in this example):
$ curl https://computing-business-8114.database.com/services/data/v22.0/query/?q=SELECT+Date__c,+Volume__c,+Close__c+from+StockPrice__c+where+Date__c=2010-02-04 --header "Content-Type:application/json" --header "X-PrettyPrint:1" --header "Authorization: OAuth 12345678abcdefgh"
The query will return the Date, Volume and Close fields from StockPrice object in JSON format where the date is February 4th, 2010.
{
"totalSize" : 1,
"done" : true,
"records" : [ {
"attributes" : {
"type" : "StockPrice__c",
"url" : "/services/data/v22.0/sobjects/StockPrice__c/0000000aaaaabbbb"
},
"Date__c" : "2010-02-04",
"Volume__c" : 3377700.0,
"Close__c" : 526.78
} ]
}
Salesforce.com also provide a Java template that can be downloaded from Database.com website. This is a sample application running on Jetty and it uses web server flow based on AuthFilter class for OAuth authentication. When we enter http://localhost:5000/, an authentication page will be presented to her:
If the user clicks on Allow button then she will be sent to the main home page where a SOQL query can be entered:
We can then enter the query:
select Date__c, Close__c, Volume__c from StockPrice__c where Date__c = 2010-02-04
The result page is supposed to look like this:
Conclusion
As we have seen, Database.com is an ideal cloud database engine for mobile and social applications. It offers the same enterprise security and identity model that is used by other Salesforce.com platforms, making it a robust database platform choice for cloud developers. Since it is based on REST API, it can be accessed from any programming languages such as Java, C, C#, Ruby, Python, PHP, etc. Salesforce.com has also created a Java SDK for Database.com and Force.com that can be used to create Spring MVC applications quickly from a template.
BigHadoop 