To be written

UTGB has a built-in support of database access for SQLite, PostgreSQL, MySQL, etc. In order to use such DBMSs, you need to add some configurations in !config/track-config.xml file. Here is an example of the database configuration. One of the database tags below shows an example using SQLite database, whose file is placed at !db/mydb.sqlite. The address path is relative to the project folder. The next database tag is an example using PostgreSQL database served at localhost:5432 whose database name is 'addressbook'. Each database configuration is associated with an database ID, 'mydb' or 'pgdb'. In the web action code, you can refer these IDs to access these databases.

<config version="1.0">
  <group>org.utgenome.track</group>
  <projectName>myapp</projectName>
  <package>myapp</package>
  <import actionPackage="org.utgenome.gwt.utgb.server.app" alias="utgb-core" />
 
  <!-- settings for using SQLite database in the local hard disk -->
  <database id="mydb">
    <connection dbms="sqlite">
      <address>db/mydb.sqlite</address>
    </connection>
  </database> 

  <!-- settings for using local PostgreSQL database server at port 5432 -->
  <database id="pgdb">
    <connection dbms="postgres">
      <address>localhost:5432/addressbook</address> 
      <user>postgres</user>
      <pass>(password)</pass>
    </connection>
  </database>
</config>

Note that for a licensing reason, we cannot include MySQL's JDBC library, which is licensed under the GPL (Gnu Public License), in the UTGB package, since the UTGB is licensed under the Apache License version 2.0. The main difference between these licenses is that GPL forces every program using GPL-licensed codes also must be licensed under the GPL, while the Apache license is applied in file basis, so your codes generated by UTGB or just using UTGB libraries have no need to be licensed under the Apache license. If you see no problem in applying the GPL license to your source codes, you can use MySQL databases in the UTGB by setting dbms=mysql in the connection tag in the configuration file. In this case, download the MySQL's JDBC library from the web, and add it to your classpath.

After you have done the database configuration, you can test your settings using utgb dbinfo command, which will display the schema information of the specified databases.

myapp> utgb dbinfo            
[DBInfo]        database ID: mydb

[DBInfo]        table: gene
[DBInfo]         column: id (integer)
[DBInfo]         column: target (string)
[DBInfo]         column: start (integer)
[DBInfo]         column: end (integer)
[DBInfo]         column: strand (string)
...

To actually see the database content, you can use utgb query command, which receives a database ID and a SQL query, then performs the database query:

myapp> utgb query mydb "select * from gene"

0|chrX|70518318|70518344|+|+_1(1,0,0,0)|
1|chr12|51949963|51949989|-|-_1(0,1,0,0)|
2|chr6|48444655|48444681|-|-_1(0,0,0,1)|
3|chr13|109111185|109111211|-|-_1(0,0,0,1)|
4|chr5|133731340|133731366|-|-_3(0,0,0,1)|
5|chr20|61835474|61835500|-|-_1(1,0,0,0)|
....

To be written