Lift - getting access to a database

Since I recently started to play more and more with lift. I discovered that it's not always as easy as you think it is and once you find a solution it was easy indeed.

For example I want to connect to my database server, but have no desire to actually use any form of OR mapping.

So after googleing a bit and woundering how todo this. It's all rather simple.

In your Boot.scala file

def boot {
    // where to search snippet
    LiftRules.addToPackages("code")

    // Build SiteMap
    LiftRules.setSiteMap(sitemap)

    //rest based stuff

    /**
     * connection url for lift
     */
    val vendor = new StandardDBVendor("org.postgresql.Driver","jdbc:postgresql://****/****",Full("username"),Full("password")) with ProtoDBVendor{
      override def maxPoolSize = 100
      override def doNotExpandBeyond = 200
    }
    
    DB.defineConnectionManager(DefaultConnectionIdentifier, vendor)

  }

This basically defines what to connect to and a connection pool with a max of 100 connections.

To actually access this connection and todo something with it, well this is remarkable easy.

          DB.performQuery("select * from data")

SetupX - tuning and the missing indexes

after analyzing a couple of hundred sql statements in setupx I noticed that there is no real use of indexes for some reason. Why there are no indexes escapes my mind, but since we prefer higher query speed I suggest the creation of the following indexes (which are far from perfect)

create index pubdata_relatedExperimentID_index on pubdata(relatedExperimentID)
create index NCBIClassifierID_ncbiID_index on NCBIClassifierID(ncbiID)
create index formobject_question_index on formobject(question)
create index formobject_discriminator_index on formobject(discriminator)
create index formobject_value_index on formobject(value(100))
create index query_querytype_index on query(querytype)
create index query_userid_index on query(userID)
create index datafile_source_index on datafile (source)
create index cache_experiment_id_index on cache (experimentID)
create index user_password_index on user (passwd)
create index user_username_index on user (username)
create index BlockedIP_blockedIp_index on BlockedIP(blockedIP)

this should improve the performance nicely and is currently being applied to our production system.