Database connectivity

From Cuttlefish Wiki
Jump to: navigation, search

Using Cuttlefish, users can explore networks. That is, the user can initially visualize a subset of the nodes and links of a network and then selectively explore the neighbourhoods of the nodes on the screen. This is particularly useful when the network is too large, and visualizing the entire network would not provide meaningful information, or when the user is interested only in a subset of the entire network.

Opening the network[edit]

As a first step, you need to open the network that you would like to explore. You can do this in two ways: (1) you can connect to a database and explore a database network, and (2) you can load a Cxf network, without visualizing the entire network.

Open a database network[edit]

Cuttlefish can connect to an SQL database by means of Java's JDBC SQL driver.

Connecting to the database

To open a database network with Cuttlefish, you need to know how to connect to the SQL database. In the graphical interface, open the Network menu on the top and find the Database network button located in the Open sub-menu. A window will prompt you to connect to a database. You need to specify the url to the database (e.g. hostname.com), the database schema name, and the username/password of your database account. Click on the Connect button and if the connection is successful you should see something similar to the screenshot below.

Database network


At this point, you are connected to the database and Cuttlefish finds out which tables in the database can be interpreted as networks. These networks are listed in the drop down box of the database toolbar. To explore a network you need to select one of those networks.

Database tables interpreted as networks[edit]

After connecting to the database, Cuttlefish reads the database meta data to find tables that can be interpreted as networks. Assume you have the following two tables in the database:

  Network1(id_origin, id_dest)
  Network1Nodes(id, label, size)

Cuttlefish assumes that the Network1 table describes the edges of a network, and table Network1Nodes describes the nodes of a network. It infers this by the column names, i.e. the id_origin and id_dest columns in the Network1 table and the id column in the Network1Nodes table. If the id_origin and id_dest columns in the Network1 table have the id column in the Network1 table as a foreign key, then Cuttlefish automatically concludes that the Network1Nodes table describes the nodes that correspond to the network described in the Network1 table.

The id_origin and id_dest columns are the only mandatory columns for a table describing a network. For tables describing the nodes of a network, the only mandatory column is id. For specifying attributes of edges, e.g. weight, width, etc., the table can use the following column names:

  Network1(id_origin, id_dest, weight, label, width, color, var1, var2, hide)

For specifying attributes of nodes, the table can use:

  Network1Nodes(id, label, color, borderColor, size, shape, width, hide, var1, var2, x, y, fixed)

Let's see an example about how the table describing the edges in the network can be defined:

CREATE TABLE CFEdges(
  id_origin INT(11),
  id_dest INT(11),
  weight INT(3),
  label VARCHAR(255),
  width INT(3),
  color VARCHAR(255),
  var1 VARCHAR(255),
  var2 VARCHAR(255),
  hide BOOLEAN,
  PRIMARY KEY(id_origin, id_dest),
  FOREIGN KEY(id_origin) REFERENCES CFNodes(id), 
  FOREIGN KEY(id_dest) REFERENCES CFNodes(id)
);

Note that if there exists a table describing the nodes in the network, the id_origin and id_dest fields should reference that table; see the foreign key definitions in the example above.

The table describing the nodes is optional, and can be declared as follows:

CREATE TABLE CFNodes(
  id INT(11) PRIMARY KEY NOT NULL,
  label VARCHAR(255),
  color VARCHAR(255),
  borderColor VARCHAR(255),
  size INT(3),
  shape VARCHAR(10),
  width INT(3),
  hide BOOLEAN,
  var1 VARCHAR(255),
  var2 VARCHAR(255),
  x FLOAT,
  y FLOAT,
  fixed BOOLEAN
);

To define whether the network is directed/undirected, you need to create the following view:

create view Directed as select true;

The values in the columns describing colors must have the format r,g,b, where r, g, and b are numbers between 0 and 1. A sample network describing edges is the following:

  +-----------+---------+--------+-------+----------------------------------+
  | id_origin | id_dest | weight | width | color                            |
  +-----------+---------+--------+-------+----------------------------------+
  |         0 |       4 |      0 |     2 | 0.62890625,0.484375,0.484375     |
  |         0 |      15 |      0 |     2 | 0.625,0.48046875,0.48046875      |
  |         0 |      31 |      0 |     4 | 0.5546875,0.42578125,0.42578125  |
  |         0 |      69 |      0 |     2 | 0.63671875,0.4921875,0.4921875   |
  |         1 |      17 |      2 |     2 | 1,0.2,0.8                        |
  |         1 |      79 |      0 |     3 | 0.58203125,0.4453125,0.4453125   |
  |         2 |       4 |      0 |     2 | 0.63671875,0.48828125,0.48828125 |
  |         2 |       8 |      0 |     2 | 0.62890625,0.484375,0.484375     |
  +-----------+---------+--------+-------+----------------------------------+

A sample network describing nodes:

  +----+-----------+---------------------------------+------+-------+
  | id | label     | color                           | size | width |
  +----+-----------+---------------------------------+------+-------+
  |  0 | MS        | 0.8984375,0.41796875,0.26953125 |    7 |     0 |
  |  1 | BOFA      | 0.8984375,0.3046875,0.26953125  |    2 |     0 |
  |  2 | CINC      | 0.8984375,0.5703125,0.26953125  |   14 |     0 |
  |  3 | AIG       | 0.8984375,0.3046875,0.26953125  |    2 |     0 |
  |  4 | GECC      | 0.8984375,0.53125,0.26953125    |   12 |     0 |
  |  5 | JPMCC     | 0.8984375,0.53125,0.26953125    |   12 |     0 |
  |  6 | GS        | 0.8984375,0.34375,0.26953125    |    4 |     0 |
  |  7 | MBI       | 0.8984375,0.609375,0.26953125   |   16 |     0 |
  +----+-----------+---------------------------------+------+-------+

To explore existing database data, you can create views on the top of the data that is already stored in the database:

  create view CFNodes as
  select db_id as id, db_label as label, db_color as color, db_borderColor as borderColor, db_size as size, db_shape as shape, db_width as width, db_hide as hide,  
  db_var1 as var1, db_var2 as var2
  from node_table order by id;
  create view CFEdges as
  select db_origin as id_origin, db_dest as id_dest, db_weight as weight,
  db_label as label, db_width as width, db_color as color, db_var1 as var1, db_var2 as var2, db_hide as hide
  from node_table as orig, node_table as dest, edge_table where orig.db_id=db_origin and dest.db_id=db_dest order by id_origin;

The fields db_* of the views should be replaced with the SQL tables or expressions that you want cuttlefish to interpret as in a cxf file. The view Directed informs whether the edges should be displayed with directionality or not.

Open a Cxf network[edit]

Cuttlefish can also explore a Cxf network. To do so, browse to Network -> Open -> Explore cxf network. A window appears asking you to select a file containing a network stored in the cxf format.

Exploring the network[edit]

To explore a network, you first need to open a subset of the network. You can either select a subset of the network using the Explore network button from the explore toolbar on the top, or using the Explore node button.

Explore network[edit]

Explore a network

You can explore a network by clicking on the 'Explore network'. A new window appears requesting for details about which part of the network should be visualized, see the screenshot to the right. To select a subset of the network, you can specify filters for nodes and links. Filters are predicates on the attributes of the nodes/links. For instance, to select only links with weight less than 10, you can add the predicate 'weight < 0.5'.

At any point, the filters can be removed by clicking on the 'Clear' button.

The selected number of nodes and links is displayed at the bottom of the window. In case the network is too large and there are many selected nodes or links, Cuttlefish displays a warning.

Explore node[edit]

Explore the neighborhood of a node

To explore the neighborhood of a node, you can click on the 'Explore node' button, where you can enter the id of the node that you like to explore and a distance. Then, all nodes at the specified distance from the source node are selected. Cuttlefish counts the number of selected nodes and links and displays this information at the bottom of the window.

Note that by default Cuttlefish looks at the outgoing edges of the source node. To consider also the incoming edges, you need to mark the ignore direction check box.

The explore network toolbar[edit]

The explore network toolbar always appears when you are exploring a network, the toolbar is similar when you explore a database network or a cxf network. The only difference is that when you explore a cxf network, there isn't a drop down menu for selecting the network, because only databases may have several networks for exploring.

Explore toolbar

There are four important buttons which are used for network exploration: expand node, expand node backwards, shrink node, shrink node backwards (see the screenshot above). To use the buttons, you need to select at least one node.

  • expand node: Cuttlefish visualizes all nodes which have an edges from a selected node.
  • expand node backwards: this button removes all nodes which have an edges from a selected node.
  • shrink node: similarly to expanding a node, but in the opposite direction - cuttlefish visualizes all nodes which have an edge to one of the selected nodes.
  • shrink node backwards: this button removes all nodes which have an edge to a selected node.


Dynamic networks[edit]

Pull for changes

When visualizing database networks, the data in the database might change over time. Cuttlefish can pull for changes in order to update the network with the new data, this is achieved using the three buttons highlighted with green in the screenshot to the right.

When the first button is clicked, Cuttlefish starts pulling for changes periodically. To adjust how often Cuttlefish should check the database for changes, you can click on the Settings button. The second button is used to do a single update.

Back to User documentation