AspectData - Documentation

Installation

Data Sources

Projects & Folders

Query Analyzer

ER Diagrams

Object Inspector

Schema Comparison

Table Data Comparison

Schema Export

DAO Generation

 

Installation

After downloading the software run the installation program. This will install or update you current installation on your computer. The software requires at least JRE 1.4 or greater to run. To test if you have java installed on your computer and what version that is run the following command from the command window (or shell):

java -version

You can download and install the latest java at java.sun.com.

After the installation is done run the "run.bat" or "run.sh" batch/script that will launch the software. For Windows and Linux environment a shortcut will be created by the installation software.

Data Sources

After installing the software you have to define your data source(s).

AspectData uses JDBC drivers to make database connections to the appropriate database. AspectData comes packaged with an appropriate JDBC driver for the most popular databases. The included drivers work for most database versions and setups.

To open the dialog that allows for new data source definition you can

- use File | New | Data Source menu or right-click on the Data Source item in the workspace panel

In the dialog box fill in the appropriate information:

To remove a data source select it into the workspace panel and either right click on it and select "Remove" from the popup menu, File | Remove or press Ctrl + <DEL> where <DEL> is the delete key.

You can also duplicate a data source, which will save you from the effort of entering same of the parameters parameters. In order to do that right click on the data source you want to use as model and select Duplicate from the popup menu.

You can modify the parameters of an existing database by opening the data source dialog: double-click on the data source or right-click and select Open from the popup menu.

Projects & Folders

Projects is not a new concept. You should use projects and folders to keep your work organized. To define a new project you can use the File | New | Project or Folder menu or by pressing Ctrl + SHIFT + P to create a new project.

To create a folder right click on a project or another folder and select New | Folder. Each folder creates a corresponding directory on the file system.

Query Analyzer

A query analyzer windows allows users to run SQL statements and see the results. To open a new query analyzer window you can press Ctrl + Shift + Q from the menu select File | New | Query Analyzer . The query analyzer window is divided into 2 panes, the upper one allowing for SQL statements editing while the lower one displays the results of the queries.

Auto completion is offered for both for tables or column by pressing Ctrl + SPACE. The window also shows the auto completion popup when you press the ".".

Join auto complete is a feature that allows you to automatically fill in the join conditions in a query based on the existence of foreign key between the tables. When you press Ctrl + SPACE after the word where in a SQL statement the popup shows the first option as ...complete join... Have fun.

To execute a SQL statement you can press Ctrl + ENTER keys while having the text cursor somewhere inside the statement to be executed. Alternatively you can use the toolbar button .

To execute a set of SQL statements (mainly updates or inserts) highlight the statement you want to execute and from the toolbar select . The output of each command will be seen in the log windows. The execution of the SQL queries is asynchronous. That does not mean much for the regular, short lived queries but it shows to be an advantage for queries that take a long time to complete. In such cases it leaves the GUI unlocked so you have the freedom to continue your work, in other windows.

The connection allocated for a query analyzer is not in auto-commit mode. This is a precaution taken for you: in case you accidentally run the wrong statement you can easily rollback the transaction. To commit or rollback use these buttons: commit and rollback located on the toolbar. You can change this behavior by checking the auto-commit check box: by doing that you indicate that each statement will be committed as soon as it is executed. Consequently the commit and rollback buttons will be grayed-out.

The Max Results drop down allows you to specify the maximum number of results to be return by a query. By keeping this to a low number helps save memory and increase speed of the application. Obviously you have the liberty to select UNLIMITED which will bring from the server all the records your query might select. We advice to use a limited number of rows for the reasons indicated before.

To refresh the schema objects used for auto completion use the refresh button . This is useful after you or a parallel activity changes the structure of your schema.

To export data from return by a query you can

• Copy & Paste directly from the grid into another document
• From the menus (popup or regular menu) select export data. This will popup-up the following dialog allowing you to export the data either as a delimited file (comma, tab, etc.) or as a set of INSERT statements.

ER Diagram

Another important feature of AspectData is the ER Diagrams. You can create, save, reload, create images or copy them into other documents such as Word documents. They are also a great tool for gaining insight into the schema structure and understanding the relationships among tables. AspectData help by providing an auto-layout feature.

One way to create an ERD is from the Schema Browser pane: select a table or a set of tables, right-click and from the menu choose Visualizer | Add Selected Tables or Visualizer | Add Tables and Related.

If there is no ERD currently active you will be asked if you want to open a new graphical window. You can also create such window by selecting Visualizer | New Anonymous Diagram. The graphical windows created in this way are not related to a specific project and are named "Untitled n". Eventually you can decide to save them and attach them to a particular project.

The second option Add Table and Related will add the tables that you selected to the diagram and it will also add the tables that are related through foreign keys to the initial ones. This is a valuable tool for quickly understanding how the tables relate to one another. You can do the same thing after the ERD is created. You have the choice, through the right-click menu, to add to the ERD:

• all tables related to the selected table.
• all tables that hold references to the selected table.
• all tables that are referred by the selected table.

The same popup menu allows you to remove the table from the diagram or to open the Inspector window for that table. Assuming that you select Add Related Tables the result is the following:

Once you create a ERD you can save it and retrieve it at later time no database connection being required for that. Assuming that there were modifications to the structure of the tables, or the tables have been dropped AspectData offers the possibility to synchronize the ERD with the data source .

You can zoom in, zoom out the diagram and you can also enlarge or reduce its size relatively to the page, which is useful in case you want to print it. At any time you can zoom back to 100%.

The diagram is being re-arranged every time you add more tables to it or when you synchronize it. To inhibit this behavior you can deselect the Auto-Layout checkbox. When auto-layout is disable the diagram is arranged only when you press the layout button .

You can copy the currently selected tables into the OS clipboard by using Ctrl+C. You can also select to save the current ERD as an image. The choice of image formats is dependent on the JRE implementation and the current settings on your computer. For Windows for instance you have a choice to save as jpeg or png files.

Object Inspector

AspectData offers the object inspector to allow you to inspect the structure and data of database objects such as tables, views, procedures etc. To inspect an object double click on its name in the schema browser or select the menu item from the right click menu. Also you can inspect an object that you select in an ERD.

The object inspector offers information on

• COLUMNS
• CONSTRAINTS
• DATA
• INDEXES
• TABLE PRIVILEGES

Each of the panes contains a table. By clicking on the table header you can choose to reorder the rows in the table ascending or descending.

The DATA panel offers functionality than ordering the data; you can also filter the data that you want to see.

Schema Comparison

AspectData allows for schema comparison, a feature useful when deploying in multiple environments. One scenario would be moving from development environment to integration to QA to UAT and to production. Usually changes and quick fixes are done only in a couple of these environments such as QA and Production and after a while the schemas are out of sync.

Select the schemas you want to compare, the types you want to compare and run the test. After the test is done you can see a HTML report of the comparison.

Some schemas contain multiple sets of tables, if you do not wish to compare every object you can use a filter, as a regular expression to filter the objects you want to compare.

The comparison compares the same data that you one can see in the object inspector. It does not compare the code for stored procedures for instances but it will report on missing columns, constraints or missing objects.

Table Data Comparison

The same scenario that can lead to schemas being out os sync can apply to tables holding reference data. These tables are usually read-only small to medium size tables. The table compare feature of AspectData addresses these tables. Because it is an in-memory comparison the number of rows that are being compared is limited to 10000. If you need to compare tables with more rows please let us know about your special requirement, we are already working on a data quality/data testing tool and we are definitely interested in hearing from you and maybe we can offer an interim solution.

To compare two tables both tables have to have the same structure and the same primary key columns. The comparison is done based on the primary keys. Columns such as BLOBs or CLOBs are not being compared only simple SQL types.

The results of the table comparison are presented as a set of 4 tables.

1. A Notes panel that names the tables being compared and the number of rows in each table.
2. Differences panel displays the differences between table A and table B. The rows are presented in different color and the differences are shown in red.
3. The third panel displays records present only in table A
4. The forth panel displays records present only in table B

When done you can export the result as HTML and create a report.

Schema Export

AspectData allows to export the schema objects into XML or HTML format into a file on the file system. You can select the types of the objects to be exported using the following dialog:

Using regular expressions you can specify what tables are included in the export and which are excluded. By default all are included.

DAO Generation