web analytics

Advanced Table Manipulation with the Code Module

Composable offers a wide range of tools for working with your most important data tables, regardless of how large or complex they might be. But even with our toolbox of helpful table modules at your disposal,  some dataflow applications might demand dynamic manipulation that could be tedious to accomplish using general-purpose tools. That’s where the Code Module comes in. This tutorial shows how to access the data in any table and build tables from scratch using our libraries for the Code Module.

While the Code Module supports other CLR languages, this tutorial will use C#.

Reading a Table

Let’s assume you have a table inside Composable that you’ll be working on. There are many ways to get one: as the result of one of our query modules, manual input via the Table Editor or Table Creator, or reading in a CSV or Excel spreadsheet, to name a few. Once you have your table ready at a module output, add a Code Module to your app. Connect your table output to the “Inputs” of the Code Module, and remove the default input values from the box to the right.

Now, we’re ready to edit the code. First, we want to tell our method that it will be accepting a single argument of type “Table,” corresponding to the Table object we connected to the module’s input. To do this, we’ll need to add a reference to the CompAnalytics.Contracts.Tables namespace, which contains our internal implementation of the SQLite tables flowing through Composable applications. Add this line to the top section of the file under the existing “using” statements:  using CompAnalytics.Contracts.Tables;. Now that the class has access to our Table libraries, indicate that the method should accept a Table object by replacing the arguments int x, string y in the method declaration to read  Table table.

We’re going to use a TableReader to dive into the contents of our table, but before we begin, there are a few properties of the Table object itself that are useful. Keeping in mind that our table object is called “table”, here they are:

  • table.Headers is a list of strings containing the names of each column in the table. You can iterate over this or access an element just like any other IEnumerable, by using the  table.Headers[columnNumber] syntax.
  • table.Columns is a TableColumnCollection object that includes details about the columns themselves. This can be iterated over or indexed into using the column name (which you can obtain from the Headers list). Each TableColumn in the TableColumnCollection has Name and Type fields, which can be useful, as for example  table.Columns["Name of Column"].Type would refer to the SQLite type of each column (e.g. “VARCHAR”).

Because table contents are stored out of memory, on disk, to actually view the data inside the rows of the table, we need to use a TableReader. To obtain a valid TableReader object for our input table, add the following code to the body of the method:

You’ll also need to add  using CompAnalytics.Contracts.Isolation; to the top of your file to gain access to the necessary classes.

Now that our table is ready, we can begin reading the table row by row using a foreach loop:

Remember that the “row” object is a list of objects, so you’ll need to cast the value to the appropriate type if you want to perform any manipulation on it. The types in  table.Columns can be useful for this, as is the GetType() method.

Now, you should be able to access all of the data in the table in whichever access pattern works best for your use case. The full code should appear as below:

Creating a New Table

Tables in Composable are immutable, so if you want to make changes to a table, you’ll need to create a new table to output from the code module. First, change the return type of your method to Table. Then, we’ll need to create 3 objects: a TableColumnCollection with column names and types, a Table object using that TableColumnCollection as its schema, and a TableWriter to create the rows of data.

Keep the ITableOperations object we called “ops” as before. Create the TableColumnCollection and Table objects first, specifying the name and SQLite type of each column:

If you’re combining a TableWriter with a TableReader, use the same ITableOperations object. Our using block looks similar, as below:

If you’re doing reading and writing at the same time, combine the using blocks as below:

To write the table’s contents, we create one row at a time, then add them using the writer. Each row should be a List of objects with the same length as the number of columns in your table. To create and add a single row to the table, we can do something like this:

Once the writer is closed, all we need to do is return the retTable  object.

Putting that all together, here is the code for simply duplicating the input table to a new output table:

Combining these strategies, you can perform any kind of manipulation you’d like on your tables.

Leave a Reply

Your email address will not be published. Required fields are marked *