This page is a guide on how to use Moose to analyze your relational database using FamixSQL.
What is FamixSQL?
FamixSQL is a meta-model and a Famix importer that can be use to create a model of a database. The FamixSQL importer relies on ODBC to create the model, thus, it is compatible with a wide range of RDBMS such as Oracle, PostgreSQL, and MSSQL.
The goal of FamixSQL is to give you tools to better understand your database, the dependencies between the tables, and the health of your schema. To do so, it relies critics analysis using a first set of rules that one can extend with its specific needs.
To install FamixSQL, you’ll need a Moose10 or Moose11 image (see the install Moose page).
In the Moose image, in a playground (
This command will load the FamixSQL project as well as all its Pharo dependencies.
To load a model from an ODBC connection, you first have to create the ODBC connection in your computer. You can find plenty of tutorial on this subject on internet: for Window.
Once you have set up the odbc connection, you will be able to use it through Moose. To do so, again in a playground, perform:
connection := ODBCConnection new.
connection dsn: '<dsn name>'.
connection uid: '<username>'. "Username"
connection pwd: '<password>'. "Password"
Replace value between
This step create a connection object that will be use by the Famix importer. You can now import perform the import by executing in the same playground the following script:
importer := FamixSQLODBCImporter new.
"importer schema: '<my schema>'."
importer model: FamixSQLModel new.
importer source: connection.
model := importer import.
"Install in the Moose system"
model name: 'myDatabase'.
model installWithCache: false.
Import step can take time. Be patient.
Analyse the database
Once you have loaded the model, it is possible to analyze the database. We describe here how to use the preconfigured analysis made using Moose Critics.
First, open the Models Browser.
It shows a list of all the model loading in your Moose environment.
You should see the model
myDatabase loaded in the previous step.
Then, open the Critics Browser.
In the critics browser, you will be able to load the predefined critics rules.
To do so, click on the
Import rules button, then search in the file browser the file
db-critics.ston that should be positioned near the FamixSQL git repository cloned in the first step. By default, it will be located under
<your home directory>/Pharo/images/<your image name>/pharo-local/iceberg/moosetechnology/FamixSQL/critics.
You should see the list of rules in the middle panel of the Critics Browser.
You can now select your model in the Models Browser, and click on the
Propagate button that will send your model to the Critics Browser.
In the Critics Browser, you should see your model entities in the left panel.
Finally, click on the
Run button of the Critics Browser browser.
It will compute the rules and present the list of entities that raised an issue.
By double-cliking on an entity, you will be able to browse the violation using the Moose Inspector
Based on our work, we also built some additional analysis. Some might already be integrated in the full Moose environment, and others are specific to this module. Thus, we list some of our analysis that might help you analyzing your database.
Visualization of the tables with their link
A Roassal3 visualization of the tables and their link is also available in this package and can be opened by executing the following script:
tables := ((model allWithType: FamixSQLTable) first: 10) asMooseSpecializedGroup.
Table without primary key
To retrieve all tables without a primary key using a playground, you can perform the following script:
(model allWithType: FamixSQLTable) select: [ :table | table columns noneSatisfy: #isPrimaryKeyColumn ]
A bit more
Raise an issue
Many thanks to Julien Deplanque who made the first version of this work.