Posts Tagged ‘MySQL’

Configuring Persistence for Lift Web Applications

Sunday, May 29th, 2011

Generating a basic Lift web application using Maven (see Using Maven and Eclipse to generate Scala Lift Web Applications) creates a project that by default uses a H2 database. The generated application provides three obvious options for configuring persistence. The starting point is Boot.scala (located in src/main/scala/bootstrap/liftweb/Boot.scala).

In examples below we look at:

  • how H2 is specified as the default database in Boot.scala
  • using a properties file to specify a different database (e.g. MySQL)
  • using JNDI to specify a third database (e.g. PostgreSQL).

The Default Database

The code fragment below from the generated Boot.scala file determines that:

  • if there is no JNDI entry for the database and
  • there are no JDBC entries specified in the properties file
  • then connect to a H2 database using JDBC driver class “org.h2.Driver” and JDBC url “jdbc:h2:lift_proto.db; AUTO_SERVER=TRUE”.
class Boot {
def boot {
  if (!DB.jndiJdbcConnAvailable_?) {
    val vendor =
       new StandardDBVendor(Props.get("db.driver") openOr "org.h2.Driver",
	   Props.get("db.url") openOr
	  "jdbc:h2:lift_proto.db;AUTO_SERVER=TRUE",
	   Props.get("db.user"), Props.get("db.password"))

    LiftRules.unloadHooks.append(vendor.closeAllConnections_! _)

    DB.defineConnectionManager(DefaultConnectionIdentifier, vendor)
  }
 ...
}

Since the property file is not automatically generated with JDBC properties the H2 database becomes the default.

Updating the Maven POM File

When configuring for a database other than H2 the Maven POM file needs to be updated so that a jar file containing the appropriate JDBC driver is available at runtime. The following adds the JDBC drivers for both MySQL and Postgres.

<project ... >
   ...
  <dependencies>
    <dependency>
     ...
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <version>1.2.138</version>
      <scope>runtime</scope>
    </dependency>
    <!--  Added for MySQL datasource -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.15</version>
      <scope>runtime</scope>
    </dependency>
    <!--  Added for PostgreSQL datasource -->
    <dependency>
    	<groupId>postgresql</groupId>
    	<artifactId>postgresql</artifactId>
    	<version>9.0-801.jdbc4</version>
      <scope>runtime</scope>
    </dependency>
     ...
  </dependencies>
   ...
<project>

Configuring JDBC with a Properties File

Adding the property file “default.props” to the project at

  • src/main/resources/props/default.props

allows a different database to be configured by setting JDBC properties with names matching those expected in Boot.scala.

The example default.props below configures a MySQL database.

# Properties in this file will be read when running in dev mode
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/liftbasic
db.user=mysql_username
db.password=mysql_password

Using JNDI to Configure a Datasource

A JNDI configured database can be enabled by adding one additional line specifying the JNDI name of the datasource, for example:

DefaultConnectionIdentifier.jndiName = "jdbc/liftbasic"

The original generated code becomes.

def boot {
  ...
  DefaultConnectionIdentifier.jndiName = "jdbc/liftbasic"

  if (!DB.jndiJdbcConnAvailable_?) {
    val vendor =
       new StandardDBVendor(Props.get("db.driver") openOr "org.h2.Driver",
	   Props.get("db.url") openOr
	  "jdbc:h2:lift_proto.db;AUTO_SERVER=TRUE",
	   Props.get("db.user"), Props.get("db.password"))

    LiftRules.unloadHooks.append(vendor.closeAllConnections_! _)

    DB.defineConnectionManager(DefaultConnectionIdentifier, vendor)
  }
 ...
}

A more configurable option is to replace the code just added with the following which checks to see if the JNDI datasource has been set by the “jndi.name” property in the “default.props” property file. If not “jdbc/liftbasic” is used.

DefaultConnectionIdentifier.jndiName = Props.get("jndi.name") openOr "jdbc/liftbasic"

A resource-ref element is also added to the web.xml file (i.e. src/main/webapp/WEB-INF/web.xml) to allow the container to manage the connection to the database.


<web-app>
    ...
   <resource-ref>
      	<description>Database Connection</description>
      	<res-ref-name>jdbc/liftbasic</res-ref-name>
      	<res-type>javax.sql.DataSource</res-type>
      	<res-auth>Container</res-auth>
   </resource-ref>
    ...
</web-app>

Container Specific JNDI Settings

Each server platform has its own specific way of configuring the JNDI settings that map the JNDI name read by Lift to a specific database. Below are examples for Tomcat, Jetty and the Cloudbees platform.

Tomcat

Tomcat manages JNDI settings via the Context element. The following adds a Context element to the Tomcat server.xml file ( i.e. TOMCAT_HOME/conf/server.xml),  mapping the JNDI name to a PostgreSQL database.

<Server>
  <Service>
    <Engine>
      <Host>
         ...
		<Context path="/liftbasic" docBase="liftbasic" reloadable="true" crossContext="true">
			<Resource name="jdbc/liftbasic"
			auth="Container"
			description="DB Connection"
			type="javax.sql.DataSource"
			driverClassName="org.postgresql.Driver"
			url="jdbc:postgresql://127.0.0.1:5432/postgres"
			username="postgres_username"
			password="postgres_password"
			maxActive="4"
			maxIdle="2" maxWait="-1"/>
		</Context>
        ...
      </Host>
    </Engine>
  </Service>
</Server>

Jetty

There are a number of options for configuring JNDI for Jetty. One option is to add a jetty-env.xml file to the WEB-INF directory to configure JNDI resources specifically for that webapp. The example jetty-env.xml file below configures a MySQL JNDI datasource for Jetty.

<?xml version="1.0"?>
<!DOCTYPE Configure PUBLIC "-//Mort Bay Consulting//DTD Configure//EN" "http://jetty.mortbay.org/configure.dtd">
<Configure class="org.mortbay.jetty.webapp.WebAppContext">

	<New id="liftbasic" class="org.mortbay.jetty.plus.naming.Resource">
	    <Arg></Arg>
	    <Arg>jdbc/liftbasic</Arg>
	    <Arg>
	     <New class="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
	                 <Set name="Url">jdbc:mysql://localhost:3306/liftbasic</Set>
	                 <Set name="User">mysql_username</Set>
	                 <Set name="Password">mysql_password</Set>
	     </New>
	    </Arg>
	   </New>
</Configure>

CloudBees

CloudBees specific configuration is placed in the cloudbees-web.xml file in the WEB-INF directory of the deployed WAR file (i.e. src/main/webapp/WEB-INF/cloudbees-web.xml).  The example below maps a CloudBees Managed MySQL datasource to the JNDI name read by Lift.

<?xml version="1.0"?>
<cloudbees-web-app xmlns="http://www.cloudbees.com/xml/webapp/1">
    <appid>lift</appid>
    <context-param>
        <param-name>application.environment</param-name>
        <param-value>prod</param-value>
    </context-param>
	<resource name="jdbc/liftbasic" auth="Container" type="javax.sql.DataSource">
	 <param name="username" value="cloudbees_mysql_username" />
	 <param name="password" value="cloudbees_mysql_password" />
	 <param name="url" value="jdbc:cloudbees://liftbasic" />
	</resource>
</cloudbees-web-app>

Setting up MySql on Mac OSX for Jena SDB

Friday, October 17th, 2008

Awhile ago I installed MySQL 5.0.37 on my MacBook Pro using the default mysql settings.

Recently I installed Jena SDB 1.1, following the instructions on the wiki.

As part of the install I created a mysql database, specifying utf8, e.g.

mysql> create database sdb-index character set utf8 ;

and set up a store description (named sdb-index.ttl) based on the SDB example, changing it to use mysql and the “layout2/index” layout.

The create command worked fine
SDBROOT > bin/sdbconfig –sdb=sdb-index.ttl –create

but when I ran the testsuite
SDBROOT > bin/sdbtest –sdb=sdb-index.ttl testing/manifest-sdb.ttl

I got the following error in the Unicode-5 test.

Checking out the SDB notes for Mysql it seemed likely that the problem was related to the msyql default character set.

To see what was currently set I ran the “show variables” command below

mysql> show variables like ‘character%’;
+————————–+————————————————————+
| Variable_name | Value |
+————————–+————————————————————+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.0.37-osx10.4-i686/share/mysql/charsets/ |
+————————–+————————————————————+

The SDB notes for Mysql recommends setting default-character-set=utf8. The Mysql documentation seemed to favour setting character-set-server=utf8 and collation-server=utf8_general_ci.

To make the changes I needed to create a config file with the changed settings that mysql reads on startup.

To do this I copied the example config file for small installations to /etc/my.cnf.

cp /usr/local/mysql-5.0.37-osx10.4-i686/support-files/my-small.cnf /etc/my.cnf

In the [mysqld] section of my.cnf I added the lines:
# utf8
init-connect=’SET NAMES utf8′
character-set-server=utf8
collation-server=utf8_general_ci

After restarting mysql the “show variables” command showed the following utf8 updates.
mysql> show variables like ‘character%’;
+————————–+————————————————————+
| Variable_name | Value |
+————————–+————————————————————+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.0.37-osx10.4-i686/share/mysql/charsets/ |
+————————–+————————————————————+

When I tried it again the SDB testsuite ran without errors.

SDBROOT > bin/sdbtest –sdb=sdb-index.ttl testing/manifest-sdb.ttl

MySQL Commands

Friday, January 25th, 2008

MySQL commands I use but can never remember:

  • Logon> mysql –user=userame –password=password
  • Create a database> create database richard_projects character set utf8;
  • Explain> explain select * from projects
  • Alter Table> alter table PROJECTS add primary key (ID);
  • Alter Table> alter table USER modify password varchar(255);
  • Add Foreign Key> alter table`USER_ROLE` add foreign key(`USER_ID`) references `USER`(`ID`) on delete cascade on update cascade;
  • Create Index> create index USER_PASSWORD on USER(PASSWORD);
  • Create Unique Index> create unique index CATEGORY_NAME on CATEGORY(NAME);
  • Insert> insert into CATEGORY VALUES (6, ‘Food’,'Generic Food Category’);
  • Update> update CATEGORY set NAME=’Other’ ,DESCR =’Other’ where NAME=’Food’;
  • Delete> delete from CATEGORY where NAME=’Food’;

User Management

Add a user with both remote and local access:

  • Grant>GRANT ALL PRIVILEGES ON *.* TO ‘richard’@'localhost’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;
  • Grant>GRANT ALL PRIVILEGES ON *.* TO ‘richard’@'%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

References

MySQL 5 Reference Manual Online

W3Schools SQL Quick References