Desktop

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/Desktop/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 this sample is run you will see an empty table and a toolbar which contains a button and a pop-up. The button "DASQL Report" will execute the DA SQL clause on the server, the results of which will be used to populate the table. The "Servers" popup contains a list of available servers discovered by the Zeroconf discovery system. When the sample is run, it starts a Zeroconf discovery service that looks for instances of Relativity Server running on the local network. Any discovered servers will be added to the popup, if no servers are discovered then the dropbox on the toolbar will be empty. You can specify a server address yourself by clicking on the popup and then clicking on "Specify custom URL...".

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 four classes; AppDelegate, DataAccess, ServiceAccess, and RegisterServerWindowController.

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 RegisterServerWindowController class is a subclass of NSWindowController which handles the UI aspects of a user manually adding a server url.

Lastly the AppDelegate class handles the primary setup of the application, registers that it will listen for notifications broadcast by the ServiceAccess and DataAccess classes, and acts as a delegate to the RegisterServiceWindowController, DataAccess and NSTableView classes. The main thing of note here is the dataIsReady: method which is called when the NOTIFICATION_DATA_READY notification is received from DataAccess which causes the table to reload with the new data.

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

Using XML for a Dynamic Where clause

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];
        });
    });
}