LexAccess

Using Other Database - MySql

This page provides details of how to integrate and configures LexAccess tool data into a pre-existing MySql database. This page can also be used as reference to use other database servers at users' own risk. It is recommended to use the installation script to install LexAccess tool with the default HyperSonic database. If such option is chosen, users may skip this page.

As an additional option, LexAccess Java provides users the option of using another database, such as MySql, to fit in user's need in their applications. In other words, LexAccess allows users to select other databases through JDBC. To use these database options, users need to set up appropriate variables in the configuration file, create two LexAccess related tables and load them into the database. This page uses MySql as an example to illustrate these steps:

  • Follow installation instructions to install LexAccess with default database
  • Setup database - MySql
  • Create tables and load lexAccess data into database - MySql
  • Configure MySql in LexAccess

Users may choose other databases for LexAccess only if all related tables are created and loaded with data in the database appropriately.

I. Setup MySql

MySql, provided by MySQL AB, is one of the most popular Open Source databases. The LexAccess tool Java uses the following versions of the MySql and its JDBC driver:

Please refer to the MySql web site to download software and follow the documents to install MySql.

If you have used and installed MySql, you may skip the preceding step.

Create a user account for LexAccess:

  • username: lexAccess
  • password: lexAccess

Create a database for LexAccess:

  • database name: lexAccess2023

Script Example:
You might need your DBA to help you on setting up user account and database for lexAccess. However, there is a shell script provided with lexAccess package to ease this step.

  • Edit file $LA_DIR/loadDb/sources/gov/nih/nlm/nls/lexAccess/loadDb/MySql/db.cfg and modify values of
    • DBA_USERNAME
    • DBA_PASSWORD
    • LA_DIR
  • Edit file $LA_DIR/loadDb/bin/1.SetupMySql and modify values of
    • LA_DIR
    • JDBC
  • shell>cd $LA_DIR/loadDb/bin/
  • shell>1.SetupMySql

Up to this point, users should have installed the MySql database, created a database in it called lexAccess2023, and opened an account with lexAccess as both username and password.

II. Create and load tables into MySql

There are 2 major tables to be created and loaded into MySql. Each of the tables below needs to be created and loaded. The directory for these data files is: $LA_DIR/data/tables/

Table NameSource File Records
INFL_VARS$LA_DIR/data/tables/inflVars.data1,658,160
LEX_RECORD$LA_DIR/data/tables/lexicon.data469,992

Please Note that the source codes for loading and checking data into MySql are under $LA_DIR/loadDb/sources/gov/nih/nlm/nls/lexAccess/loadDb/MySql. Both

  • $LA_DIR/loadDb/lib/lexAccessLoadDb.jar and
  • $LA_DIR/lib/lexAccess2023dist.jar
contain all Java classes for these files. Please refer to scripts under $LA_DIR/loadDb/bin/2.LoadDb for how to run these classes.

  • A set of similar Java programs are used to create tables and load data for HSqlDb. These programs can be found under $LA_DIR/loadDb/sources/gov/nih/nlm/nls/lexAccess/loadDb/HsqlDb

    Load data

    Users need to run LoadXXX for both 2 tables. The Java program LoadXXX will:

    • Delete table XXX if the table already exists in the database.
    • Create a new table named XXX in the database.
    • Load data from a flat file into table XXX.
    • Show the total number of inserted records.

    To load these tables, users need to:

    • Set variables (LA_DIR) in the config file ($LA_DIR/loadDb/sources/gov/nih/nlm/nls/lexAccess/loadDb/MySql/db.cfg).
    • Add $LA_DIR/loadDb/lib/lexAccessLoadDb.jar to classpath.
    • Add $JDBC to classpath.
    • cd $LA_DIR/loadDb/sources/gov/nih/nlm/nls/lexAccess/loadDb/MySql/
    • java -classpath $CLASSPATH gov.nih.nlm.nls.lexAccess.loadDb.MySql.LoadXXX

    Check data

    This step is optional. Users may run CheckXXX (similar setup steps as above) to check and perform queries to table XXX to make sure all tables are created and loaded appropriately.

    Script Examples:

    There are two scripts are provided to ease the processes of loading and checking data in the database:

    • Edit file $LA_DIR/loadDb/bin/2.LoadDb and modify values of
      • LA_DIR
      • JDBC
    • shell>cd $LA_DIR/loadDb/bin/
    • shell> 2.LoadDb 2023 
      2
3

    • Edit file $LA_DIR/loadDb/bin/3.TestDb and modify values of
      • LA_DIR
      • JDBC
    • shell>cd $LA_DIR/loadDb/bin/
    • shell> 3.TestDb 
      

    


    At this point, all lexAccess data are loaded into created tables in the database.


    III. Configure MySql in LexAccess

    Location of configuration file:

      

    •  The default configuration file is: $LA_DIR/data/config/lexAccess.properties 

    


    Change the setup in the configuration file

    There are two ways to setup the MySql database, as shown below:


      

    1. Method 1:

        

      •  DB_TYPE=MYSQL

      •  DB_DRIVER=com.mysql.jdbc.Driver

      •  DB_NAME=lexAccess2023

      •  DB_HOST=localhost

      •  DB_USERNAME=lexAccess

      •  DB_PASSWORD=lexAccess

      


      or 


    2. Method 2:

        

      •  DB_TYPE=OTHER

      •  DB_DRIVER=com.mysql.jdbc.Driver

      •  JDBC_URL=jdbc:mysql://localhost/lexAccess2023

      •  DB_USERNAME=lexAccess

      •  DB_PASSWORD=lexAccess

      

    


    Make sure the JDBC driver is included in classpath. For examples, 
edit file $LA_DIR/bin/lexAccess and modify (fill in) the value to JDBC

    Now, the LexAccess Tool is ready to run with MySql database


    IV. Use different port number for MySql in LexAccess

    The default port of MySql is 3306.  In case that different port number (for example, 3307) is used in your MySql database, you will need to add the port number after the database host (localhost:3307).


    •