English

In this article, we will show you how to write a Java plug-in allowing you to import table and column descriptions into a DB-MAIN schema. The descriptions are stored in a CSV file in the following format :

"Name table","name column","description"

The description may contain some « , » or line breaks. If it contains some « " », they must be doubled.
For example, if you want to add the following comment to the table « BOOK » :

A book is any written "piece of work"
in the literary, scientific or technical domain.

The CSV file will be as follows :

BOOK,,"A book is any written ""piece of work""
in the literary, scientific or technical domain."

The result in DB-MAIN will be as follows :

entity type DB-Main

Creation of the project

The previous article from our blog (My first Java plug-in for DB-MAIN) described how to create a project in Eclipse. For this new project, it is necessary to create a class named « ImportDescription.java ». The full source code of the class « ImportDescription » can be found at the following address.

This project uses two .jar files. As in the first project, we need jidbm.jar (located in the bin directory of the installation directory of DB-MAIN) in order to access the repository of DB-MAIN. We also need opencsv-3.9.jar (which can be downloaded at the following url).

Reading the CSV file

« runDBM » is the method called by DB-MAIN. It opens the console (DBMConsole) to display the messages, looks for the current schema (lib.getCurrentDBMSchema()) and requests the CSV file path.

public class ImportDescription {
	private DBMSchema sch;

	/**
	 * Method called by DB-MAIN to import a CSV file to update the semantic description
	 * of the table and column of the current schema.
	 * 
	 * @throws IOException
	 */
	public static void runDBM () throws IOException{
		// Open the DB-MAIN console
		new DBMConsole();
		System.out.println("start");
		// Create the DBMLibrary instance
		DBMLibrary lib = new DBMLibrary();
		// Get the current schema
		DBMSchema sch = lib.getCurrentDBMSchema();
		if(sch == null){
			System.out.println("select a schema ");
			return;
		}
		// get the name of the CSV file
		JFileChooser fileChooser = new JFileChooser();
		fileChooser.setDialogTitle("DBD extractor - Select log file");
		FileNameExtensionFilter filter = new FileNameExtensionFilter(
				"csv (*.csv)", "csv");
		fileChooser.setFileFilter(filter);
		int res = fileChooser.showOpenDialog(null);

		if (res != JFileChooser.APPROVE_OPTION){
			return;
		}
		String filename = fileChooser.getSelectedFile().getPath();
		
		// read the csv file
		ImportDescription importDesc = new ImportDescription(sch);
		importDesc.readFile(filename);
		
		System.out.println("end");
	}

	public ImportDescription(DBMSchema sch) {
		this.sch = sch;
	}
}

The method « readFile() » reads the file. To read the CSV file, we use the library « opencsv » (http://opencsv.sourceforge.net/). The constructor « CSVReader(new FileReader(csvFile)) » takes as argument the CSV file. Each line is read in a string table (String[]). The first element (nextLine[0]) is the name of the table, the second one (nextLine[1]) is the name of the column and the third one (nextLine[2]) is the description.

/**
	 * Read a csv file ('csvFile') and update the description of the tables
 * and the columns of the schema
	 * The format of the csv file is "table name;column name;description"
	 * @param csvFile the path of the csv file
	 */
	private void readFile(String csvFile) {
		CSVReader reader;
		try {
			reader = new CSVReader(new FileReader(csvFile));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return;
		}
		// nextLine[] is an array of values from the line
		String [] nextLine;
		try {
			while ((nextLine = reader.readNext()) != null) {
				// the name of the table
				String tableName = nextLine[0];
				// the name of the column
				String columnName = nextLine[1];
				// the description
				String description = nextLine[2];
				updateDescription(tableName, columnName, description);
			}
			reader.close();
		} catch (IOException e) {
			e.printStackTrace();
			return;
		}   
	}
}

Finding the table/column to modify

The method « findDataObject() » searches for the table or column to update. As can be seen in the following diagram, the concept of table (DBMEntityType) and that of column (DBMAttribute) inherit from the same class, namely DBMDataObject. The method
« findDataObject() » will return a DBMDataObject that will either be a DBMEntityType if the column name is empty, or a DBMAttribute if the column name exists.

The method « findEntityType() » finds an entity type (table) of a schema based on its name. If the name of the column is not empty, we use the method « findAttribute() » to retrieve the attribute belonging to the entity type.

schema

/**
	 * If 'columnName' is empty, return the table (DBMEntityType) named 'tableName'
	 * Otherwise, return the column (DBMSimpleAttributre) named 'tableName.columnName'
	 * @param tableName Name of a table
	 * @param columnName Name of a column of 'table'. Can be empty
	 * @return The table or the column named 'tableName.columnName'
	 */
	private DBMDataObject findDataObject(String tableName, String columnName) {
		// find the table named 'tableName'
		DBMEntityType table = sch.findEntityType(tableName);
		if(table == null){
			System.out.println("table (" + tableName + " not found");
			return null;
		}
		if(columnName.isEmpty()){
			return table;
		} else {
			// find the column of 'table' named 'columnName'
			DBMAttribute column = table.findAttribute(columnName);
			if(column == null){
				System.out.println("column (" + columnName + " not found");
				return null;
			}
			return column;
		}
	}

Editing the description

Each object of the repositery of DB-MAIN has fields (for example name, type, length, semanticDescription). Each field has a
« getter » (for example, getName()) and a « setter » (for example, setName()). Most objects have two descriptions, semantic and technical. In our example, we will update the semantic description.
« updateDescription » calls « findDataObject » to retrieve the object and update its description. For the semantic description, the method « setSemanticDescription() » is used.

/**
	 * if 'columnName' is empty, update the description of the table 'tableName'.
	 * Otherwise update the description of the column 'tableName.columnName' 
	 * @param tableName Name of a table
	 * @param columnName Name of a column of 'table'. Can be empty
	 * @param description A description
	 */
	private void updateDescription(String tableName, String columnName, String description) {
		DBMDataObject dataObject = findDataObject(tableName, columnName);
		if(dataObject != null){
			dataObject.setSemanticDescription(description);
		}
	}

Conclusion

You have just created a Java plug-in for DB-MAIN which updates the descriptions of the entity types and attributes of the current schema. To help you further develop plug-ins, you can consult the reference manual of the JIDBM (« JIDBM-reference-manual.pdf ») and its Javadoc (« JIDBM-javadoc ») distributed with DB-MAIN (either from the [Start/DB-MAIN…] menu under Windows, either from the « doc » directory of the installation directory of DB-MAIN under Linux).

If you have any questions about this article or, more generally, about DB-MAIN, get in touch with us via our contact form or via our forum.

Topics: 

Writer(s): 

Jean Henrard