Create Tables

The connector described in A Complete Synchronize Action always ingests every file, even those that have not changed since the last synchronize. You could improve this connector by implementing incremental synchronize.

To do this, you can create a Datastore table to store the modified date of each file. When the connector is about to ingest a file it can check this table and only ingest the file if it is new or has been modified:

Reference LastModified
C:\data\file1.txt 2013-08-05 18:05
C:\data\file2.txt 2013-07-30 14:36
C:\data\file3.txt 2012-12-24 09:00

You can create the table like this:

       Table docs = datastore.NewTable("Documents",
		new string[] { "Reference", "LastModified" });

This creates a new table called "Documents" with two columns, "Reference" and "LastModified", provided the table does not exist already. If there is already a table called "Documents", this line verifies the table has the expected structure.

This example creates two text columns, but a table can also store numeric columns. You can create numeric columns using a ColumnList that is then passed to NewTable.

Add Constraints

The table created above could contain multiple rows with the same value in the Reference column. The following sample creates the same table, but with the constraint that values in the Reference column must be unique:

       ColumnList columns = new ColumnList(
		new string[] { "Reference", "LastModified" });

       columns.AddUniqueReplaceConstraint(new string[] { "Reference" });

       Table docs = datastore.NewTable("Documents", columns);

If a record is inserted with the same Reference as an existing record, it replaces the old record. Without the constraint, both records would exist together.

You can specify that a combination of columns must be unique by including more elements in the array passed to AddUniqueReplaceConstraint. For example, a unique constraint over columns A and B would mean that no two rows can have the same value in column A and the same value in column B.

You can specify as many unique constraints as you like for a single table. Adding a constraint for a set of columns also adds an index for those columns. For more information about indexes, see Indexes.

Primary Keys and Automatic IDs

There is no formal way to specify a primary key, but you can specify a unique constraint, as described above. This ensures that no two rows have the same value for that column and makes querying by that column efficient. To create a multi-column primary key, add a unique constraint that includes all of the relevant columns.

In addition to the columns you specify when calling NewTable, each table also includes a column that holds a unique number to identify each row. This number is assigned automatically when a new record is inserted. The assigned values are expected to increase but are not guaranteed to be consecutive. The name of this column is specified in Table.DatastoreKey and is automatically included in the columns retrieved by the Select methods. The unique number contained in this column is a 64-bit integer so you can retrieve it from a record like this:

       long recordId = record.GetAsInt64(Table.DatastoreKey);