Database.com – Salesforce.com’s Cloud Database

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.

Introduction to NuoDB – An Elastically Scalable Cloud Database

Introduction

Traditional relational databases are built upon a synchronous, client-server architecture that is often limited in terms of scalability requirements that are posed by distributed computing systems. As a result, various sharding, caching, and replication techniques emerged to cope with these demands. On the other hand, NoSQL solutions have emerged on the ground of the CAP theorem. Data management systems like BigTable, HBase, MongoDB, Cassandra, and Dynamo offer different capabilities depending on how they balance consistency, availability, and partition tolerance. However, they gave up supporting SQL and ACID properties, which are critical in the relational database world.

NuoDB is a complete re-think of relational databases that is built on a new foundation; partial, on-demand replication. Under the hood, NuoDB is an asynchronous, decentralized, peer-to-peer database. It uses the concept of Atoms, these are objects that are being replicated . In NuoDB everything is an Atom; database, schema, sequence, table, index, records, blobs, data are all Atoms. NuoDB holds a patent on this peer-to-peer object replication.

NuoDB Architecture

NuoDB architecture has three layers: management layer, SQL layer and data layer. The management layer is comprised of an agent that manages the NuoDB processes running on a particular computer, it starts and stops them and it also collects statistics from the transaction and storage engines. Certain agents are configured to be a broker – brokers communicate with the client initially and then the broker introduces the client to the transaction engine. From then on the client can communicate directly with the transaction engines. NuoDB management layer also offers a command line and a web-based management tool to manage the databases. NuoDB also offer a command line loader for exporting and importing data.

At the  SQL layer NuoDB has transaction engines that provide access to a single database.The transaction engine parses, compiles, optimizes and executes the SQL statements on behalf of the clients.

At the data layer NuoDB has storage managers that provide persistence of the data. A storage manager uses key/value pairs to store the information but it can also use more sophisticated stores e.g. HDFS.

In case of a minimal configuration we can run every components (broker, transaction engine and storage manager) on the same machine. NuoDB can be easily scaled out and can be made redundant by adding multiple brokers, transaction engines and storage managers. In more complex scenarios we can run NuoDB in the AWS cloud or across multiple corporate datacenters providing geo-redundancy. Below is an example of a redundant architecture with two brokers, two transaction engines and two storage managers.

Getting Started

NuoDB is available on multiple platforms like Windows 32 and 64-bit, Linux 64-bit (Ubuntu, RHEL, Suse), Mac OSX 10.7, Solaris 11 (Inter 64-bit).  For this article we used a Ubuntu 12.04 LTS virtual machine.

First we need to start up the components as discussed above; the broker/agent, then from the command line management tool we can start up the transaction engine and the storage manager. We also need to configure the properties file to contain the settings for the domain.

$ vi ./etc/stock.properties
# A flag specifying whether this agent should be run as a connection broker
broker = true

# The name used to identify the domain that this agent is a part of 
domain = stock

# The default administrative password, and the secret used by agents to
# setup and maintain the domain securely
domainPassword = stock

# Start agent
$ java -DpropertiesUrl=file:///home/notroot/nuodb/etc/stock.properties -jar jar/nuoagent.jar --verbose &>/tmp/stock.log &

# Start command line manager
$ java -jar jar/nuodbmanager.jar --broker localhost --password stock
nuodb [stock] > show domain summary

Hosts:
[broker] localhost/127.0.0.1:48004

## Create a new domain administrator user
nuodb [stock] > create domain administrator user istvan password istvan

## Start Storage Manager
nuodb [stock] > start process sm
Database: stock
Host: localhost
Process command-line options: --dba-user stock --dba-password stock
Archive directory: /home/notroot/nuodb/data
Initialize archive: true

Started: [SM] ubuntu/127.0.1.1:59914 [ pid = 3467 ] ACTIVE

## ps -ef | grep nuodb
## notroot   3467  3396  0 12:01 pts/0    00:00:00 /home/notroot/nuodb-1.0.1.128.linux.x86_64/bin/nuodb --connect-key 7124934669079864995

## Start Transaction Engine
nuodb [stock/stock] > start process te
Host: localhost
Process command-line options: --dba-user stock --dba-password stock

Started: [TE] ubuntu/127.0.1.1:60545 [ pid = 3494 ] ACTIVE

## ps -ef| grep nuodb
## notroot   3494  3396  0 12:06 pts/0    00:00:00 /home/notroot/nuodb-1.0.1.128.linux.x86_64/bin/nuodb --connect-key 8587006928753483386

Note, that we started the storage manager with initialize yes option. This is only for the first time, any subsequent startup shall use initialize no option, otherwise the data will be overwritten.

Then we can connect to the database using nuosql client – the first argument is the name of the database (stock), and we need to specify the database admin username/password. After login we can set the schema with use command to stock.:

$ bin/nuosql stock --user stock --password stock
SQL> use stock
SQL> show
	autocommit state is on
	semicolon completion is required
	current schema is STOCK
SQL> show tables

	No tables found in schema STOCK

SQL> create table Stock
   > (
   >    Id             Integer not NULL generated always as identity primary key,
   >    StockDate      Date,
   >    StockOpen      Decimal(8,2),
   >    StockHigh      Decimal(8,2),
   >    StockLow       Decimal(8,2),
   >    StockClose     Decimal(8,2),
   >    StockVolume    Integer,
   >    StockAdjClose  Decimal(8,2)
   > );
SQL> show tables

	Tables in schema STOCK

		STOCK

We can then load the data stored in csv file format into the database table. The CSV file – google.csv for stock information – was downloaded from http://finance.yahoo.com

$ bin/nuoloader --schema stock --user stock --password stock --import "/home/notroot/nuodb/samples/stock/google.csv",skip --to "insert into Stock values(default,?,?,?,?,?,?,?)" stock &> /tmp/nuoloader.log

Imported 2163 rows, failed 0 rows, size 101897 bytes from /home/notroot/nuodb/sa
mples/stock/google.csv

Then we can login again using nuosql and run a regular SQL query to retrieve the top 10 stock values and the corresponding date (ordered by adj close value):

notroot@ubuntu:~/nuodb$ bin/nuosql stock --user stock --password stockSQL> use stock
SQL> select count(*) from stock;
 COUNT  
 ------ 
  2163  

SQL> select StockDate, StockOpen,StockClose, StockVolume, StockAdjClose from stock order by StockAdjClose desc limit 10;

 STOCKDATE  STOCKOPEN  STOCKCLOSE  STOCKVOLUME  STOCKADJCLOSE  
 ---------- ---------- ----------- ------------ -------------- 

 2013-03-05   828.93     838.60      4044100        838.60     
 2013-03-11   831.69     834.82      1594700        834.82     
 2013-03-07   834.06     832.60      2052700        832.60     
 2013-03-08   834.50     831.52      2911900        831.52     
 2013-03-06   841.03     831.38      2873000        831.38     
 2013-03-12   830.71     827.61      2008300        827.61     
 2013-03-13   827.90     825.31      1641300        825.31     
 2013-03-14   826.99     821.54      1651200        821.54     
 2013-03-04   805.30     821.50      2775600        821.50     
 2013-03-20   816.83     814.71      1463800        814.71

Java Client – JDBC for NuoDB

NuoDB supports various programming languages for client applications such as Java, .NET, PHP, Ruby and Node.js. In this section we demonstrate that NuoDB supports JDBC in the same way that it is available for traditional relational databases. The Java program needs to add nuodbjdbc.jar to its classpath.

Below is an example Java code (StockDB.java) to retrieve the highest stock value ever (ordered by adj close) and the related date:

$ cat StockDB.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class StockDB {

    /** The driver class provided by NimbusDB. */
    public static final String DRIVER_CLASS =
	"com.nuodb.jdbc.Driver";

    /** The base URL for connecting to a local database server. */
    public static final String DATABASE_URL =
	"jdbc:com.nuodb://localhost/";

    // the established connection to a local server
    private final Connection dbConnection;

    /**
     * Creates an instance of DB and connects to a local server,
     * as the given user, to work with the given named database
     *
     * @param user the user name for the connection
     * @param password the password for the given user
     * @param dbName the name of the database at the server to use
     */
    public StockDB(String user, String password, String dbName)
	throws SQLException
    {
	Properties properties = new Properties();
	properties.put("user", user);
	properties.put("password", password);
	properties.put("schema", "stock");

	dbConnection =
	    DriverManager.getConnection(DATABASE_URL + dbName, properties);
    }

    /** Closes the connection to the server. */
    public void close() throws SQLException {
	dbConnection.close();
    }

    /**
     * Gets the name for the given id, or null if no name exists.
     *
     * @param an identifier
     * @return the name associate with the identifier, or null
     */
    public String getDateAndAdjClose() throws SQLException {
	Statement stmt = dbConnection.createStatement();
	ResultSet rs = stmt.
	    executeQuery("select stockdate, stockadjclose from stock order by stockadjclose desc limit 1");
	try {
	    if (rs.next())
		return rs.getString(1) + ", " + rs.getString(2);
	    return null;
	} finally {
	    rs.close();
	    stmt.close();
	}
    }

    /** Main-line for this example. */
    public static void main(String [] args) throws Exception {
	Class.forName(DRIVER_CLASS);

	StockDB stockDB = new StockDB("stock", "stock", "stock");
	System.out.println("Date and AdjClose: "  + stockDB.getDateAndAdjClose());

	stockDB.close();
    }

}

Then we can run the Java program as follows:

notroot@ubuntu:~/nuodb/samples/java$ javac StockDB.java 
notroot@ubuntu:~/nuodb/samples/java$ java -classpath .:../../jar/nuodbjdbc.jar StockDB
Date and AdjClose: 2013-03-05, 838.60