Project databases

A Telosys project can define one or more databases.

Each database can be used to create a new model based on its database schema (tables, relations, etc).

Project databases are defined in the file "TelosysTools/databases.yaml".

This YAML file can be edited with any editor or IDE.

Commands for databases definition

• ldb -> List databases defined in the current project

• edb -> Edit "databases.yaml" file with the editor defined in Telosys-CLI configuration

• cdb -> Check database configuration (try to connect and to get meta-data)

YAML file structure

The file contains a list of database configurations (n items in "databases:" entry)

For each database entry:

• Database identification:

  • id: (string) a unique identifier for the database

  • name: (string) a name describing the database

  • type: (string) the database type examples: "PostgreSQL", "MySQL", "Oracle", etc

• JDBC connection:

  • driver: (string) the JDBC driver to use to connect to the database (driver Java class name) examples: "org.postgresql.Driver", "org.mariadb.jdbc.Driver"

  • url: (string) the JDBC URL for connection examples: "jdbc:postgresql://{HOST}:{PORT}/{DATABASE}"

  • user: (string) the database user name

  • password: (string) the database user password

• What tables to retrieve - first level filter:

  • catalog: (string) the database catalog to use or '!' for none

  • schema: (string) the database schema to use or '!' for none

  • tableNamePattern: (string) pattern used as table name filter examples: "%", "ABC%", ...

  • tableTypes: (string) types to retrieve, separated by blanks examples: "TABLE" (only tables), "VIEW" (only views), "TABLE VIEW" (tables and views)

• What tables to retrieve - second level filter (applied on the result of the first level filter)

  • tableNameExclude: (string) regular expression used to exclude tables

  • tableNameInclude: (string) regular expression used to include tables

• Options for Telosys model creation ("boolean" type for all):

  • linksManyToOne: (default = true) create "ManyToOne" links in model entities

  • linksOneToMany: (default = false) create "OneToMany" links in model entities

  • linksManyToMany: ( default = false) create "ManyToMany" links in model entities

  • dbComment: (default = true) create @DbComment(...) annotation (entity & attribute level)

  • dbCatalog: (default = true) create @DbCatalog(...) annotation (entity level)

  • dbSchema: (default = true) create @DbSchema(...) annotation (entity level)

  • dbTable: (default = true) create @DbTable(...) annotation (entity level)

  • dbView: (default = true) create @DbView(...) annotation (entity level)

  • dbName: (default = true) create @DbName(...) annotation (attribute level)

  • dbType: (default = true) create @DbType(...) annotation (attribute level)

  • dbDefaultValue: (default = true) create @DbDefaultValue(...) annotation (attribute level)

Setting up the JDBC driver library

After defining the database in "databases.yaml" you also need to set up the Java library containing the JDBC driver.

The Java library is the ".jar" file (sometimes ".zip") provided by the database supplier.

JDBC library files examples: - postgresql-42.2.26.jre7.jar - mariadb-java-client-3.1.0.jar

Put the ".jar" file in "TelosysTools/lib" directory so that Telosys can use it.

Check database configuration

Once a database has been defined in "databases.yaml", it is recommended to test the database configuration using the "cdb" command.

• Check if it is possible to connect to the database cdb <dbid>

• Check tables retrieved -> -t cdb <dbid>-t

• Check columns retrieved -> -c cdb <dbid>-c

• Check primary keys retrieved -> -pk cdb <dbid>-pk

• Check foreign keys retrieved -> -fk cdb <dbid>-fk

• Get database shemas -> -s cdb <dbid>-s

• Get database catalogs -> -cat cdb <dbid>-cat

When all checks are OK, you are ready to create a model from the database.

Create a new model from a database

To create a new model from one of the databases defined in "databases.yaml" you just have to use the "nm" (New Model) command.

See "Model creation"