Mobile

The DASQL console sample demonstrates using DA SQL to retrieve data from an instance of Relativity Server. DA SQL is a technology introduced to Data Abstract that gives clients the full flexibility of SQL queries to describe their data needs which will be run against the middle tier rather than against the back-end database(s), using the table and field names that are defined in the schema rather than those defined in the back-end database.

Getting Started

The sample is typically located in /Developer/RemObjects Software/Samples/Data Abstract/Mobile/DASQL, though you may have installed the Data Abstract for Cocoa and it's samples in another location.

To build it, you will of course need Xcode, and like all the samples provided with Data Abstract for Cocoa you will need to be running the Relativity Server with the DASamples Domain and the Simple Schema available.

Running the Sample

This sample allows you to see the results of running a pre-defined complex DA SQL clause that retrieves a table where the rows are comprised of 3 fields which are assigned different names (Group, Product and Sold) from 3 different tables (OrderDetails, Products and ProductGroups) where the order Type was "2", and the order date was between "2013-01-01" and "2013-12-12" and the data is grouped together and ordered by two columns, first by column "3" (i.e. the amount sold) and then by the "Desc" column.

When the sample loads it starts a Zeroconf discovery service that looks for instances of Relativity Server running on the local network. Any discovered servers will appear in the initial table, if no servers are discovered then you can specify a server address yourself by pressing the "+" button; you only need to supply the address the port number and http:// will be added for you.

Touching a server name will trigger the DA SQL statement to be created and executed, and the results displayed on the table that appears. The returned data is grouped by group type, and then sorted by the value in the Sold field.

Examining the Code

The code for creating the connection to the server and handling logging in is covered by other samples. In this section we shall focus solely on creating the DA SQL statement and retrieving the records using the getDataTable:withSQL: method of DARemoteDataAdapter. The Console sample demonstrates the alternate methods getDataTableWithSQL: and beginGetDataTable:withSQL:withBlock: that are also available.

App Structure

The sample is built around five classes; AppDelegate, DataAccess, ServiceAccess, DASQLViewController, and ServersViewController.

The DataAccess class handles everything related to interacting with the Data Abstract SDK; including retrieving data from an instance of Relativity Server and applying changes back to the server.

The ServiceAccess class handles the discovery of any instances of Relativity Server that are available on the local network using ROZeroConf which is a feature available with the Remoting SDK that Data Abstract is built upon. ServiceAccess sets up a ROZeroConfBrowser Class object which searches for any servers that broadcast a value matching the value defined for RELATIVITY_SERVICE_NAME. When a service is found, or indeed disappears, the server list is updated and the popup with the list of available servers is also updated. It also handles the registration of custom server addresses. To explore further the ServiceAccess class and Zeroconf discovery see the article: The ServiceAccess Class and Zeroconf discovery.

The DASQLViewController class handles calling DataAccess to execute the DA SQL statement, and displaying the results. The main thing of note here is the dataReady: method which is called when the NOTIFICATION_DATA_READY notification is received from DataAccess which causes the UITableView to reload with the new data. The other thing is that the call to load the data occurs in viewWillAppear:

The ServersViewController class handles displaying the table of available servers (be they either discovered by Zeroconf, or manually added) and presenting a UIAlert to enter a server address when the "+" button is pressed. When an row is pressed, it configures the DataAccess class to set up a connection to the selected server and passes the name of that server to the SimpleDataOperationsViewController by implementing prepareForSegue:sender:.

Lastly the AppDelegate class is a stock version and has no customization.

All of the code below can be found in DataAccess.m.

Creating a DA SQL Statement and executing it

A DA SQL statement is simply a normal SQL statement that uses the table & field names from the schema rather than the ones in the back-end Database. There is no special class required, simply put the DA SQL statement in an NSString and pass it to the relevant method. DA SQL statements aren't limited to basic requests, you can compose as complex a request as you need by using a multi-line NSString.

Here we create a statement that retrieves a table where the rows are comprised of 3 fields which are assigned different names (Group, Product and Sold) from 3 different tables (OrderDetails, Products and ProductGroups) where the order Type was "2", and the order date was between "2013-01-01" and "2013-12-12" and the data is grouped together and ordered by two columns, first by column "3" (i.e. the amount sold) and then by the "Desc" column.

The DA SQL statement is passed as the second argument to the getDataTable:withSQL: method; the first argument is the name to be assigned to the returned table, while it can be whatever you wish it is recommended that you don't make it the same as other tables in the schema. The call is executed in a background queue and when it returns a notification (NOTIFICATION_DATA_READY) is dispatched to the main queue. The AppDelegate is listening for that notification and will reload the NSTableView with the new returned data.

If you aren't interested in assigning your own table name to the retrieved table you could instead use the method getDataTableWithSQL:. This method simply takes the DA SQL statement to be executed and it will assign the table the name "@dasqldata".

//DataAccess.m
- (void)beginDASQLReport {

    NSString *fromDate = @"2013-01-01";
    NSString *toDate = @"2013-12-12";

    NSString *DASQLTemplate =
    @"\n"
    @"SELECT     g.Name        AS [Group],   \n"
    @"           p.Name        AS Product,   \n"
    @"           sum(d.Amount) AS Sold       \n"
    @"FROM                                   \n"
    @"           OrderDetails d              \n"
    @"INNER JOIN Orders o                    \n"
    @"        ON o.Id = d.[Order]            \n"
    @"INNER JOIN Products p                  \n"
    @"        ON p.Id = d.Product            \n"
    @"INNER JOIN ProductGroups g             \n"
    @"        ON g.Id = p.[Group]            \n"
    @"WHERE                                  \n"
    @"           o.Type = %d                 \n"
    @"AND        o.Date > '%@'               \n"
    @"AND        o.Date <= '%@'              \n"
    @"GROUP BY                               \n"
    @"           g.Id,                       \n"
    @"           g.Name,                     \n"
    @"           p.Name                      \n"
    @"ORDER BY                               \n"
    @"           3 Desc                      \n";

    NSString *DASQL = [NSString stringWithFormat:DASQLTemplate, ORDER_TYPE_SALE, fromDate, toDate];

    NSLog(@"DASQL: %@", DASQL);
    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_BACKGROUND, 0), ^{

        DADataTable *result = nil;

        @try {
            result = [self.dataAdapter getDataTable:@"Sales" withSQL:DASQL];
        }
        @catch (NSException *exception) {
            NSString *error = @"Error when downloading data";
            NSString *message = [NSString stringWithFormat:@"%@: %@", error, [exception reason]];
            NSLog(@"%@", message);
            [self.delegate alertError:error withDetails:[exception reason]];
        }

        dispatch_async(dispatch_get_main_queue(), ^{
            self.dataTable = result;
            [[NSNotificationCenter defaultCenter] postNotificationName:NOTIFICATION_DATA_READY object:self];
        });
    });
}