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.

SetupX - rawdata access

ever had the desire to access the rawdata in SetupX, because the gui doesn't give you easy or access at all to it?

First you need to understand what's happening in the background. The used approache for setupx looks like a mapping from a tree to a table structure. Which is a valid approach and keeps it very flexible in therory, but is not a real pratical solution. Specially since it's a real pain to write queries for.



Get all species:

select distinct value from formobject where question = 'species' and value != ''

Get all organs:

select distinct lower(value) from formobject where question in ( 'organ','organ name','Organs','Organ specification' ) and value != ''

we need to use lower, cause people have a 'strange' way of spelling things


work in progress!

SetupX - bug repository

so the first step of taking an existing project over is to setup a repository to track bugs and changes to make it easier to discover problems by the users.

The new setupx bug repository can now be found here

Hacking SetupX - working on the sql tables instead of hibernate

Currently one of my jobs is to get data out of the setupX system, since the developer left us and nobody has a clue how it actually works.

So far the basic understanding is that the system has one major table and all queries are executed by building sub queries in it.

Advantage of the technique:

it's extremely flexible,
Disadvantage?

It's ridiculous slow and a nightmare to figure out, since you got so many possible sub graphs.

Don't we love reverse engineering an existing software?

So this is just a small collection of statements to get data out.

get list of available questions and attributes


select question from formobject group by question;


get the list of registered species


select value from formobject where question = 'species' group by value;



The final idea is to make a snapshot of the database every night and transform it into a real database structure. Like having several tables and so.

Installing SetupX under ubuntu

a first try to write a connector to getch all kinds of informations, using a hibernate layer.

how to:

• install your plain old Ubuntu server box
• install java 5: apt-get install sun-java5-jdk
• install subversion: apt-get install subversion
• install unzip: apt-get install unzip
  
• install ant: download ant 1.6.5, unzip it and add it to the path
• install jboss: download jboss-4.2.3.GA, unzip it in your directoy of choice, let's use opt
• get setupX:
  

  svn checkout -r421 http://setupx.googlecode.com/svn/trunk/ /opt/setupx

• install mysql: apt-get install mysql-server
• start the mysql server: /etc/init.d/mysql start

let's configure setupX:

• lims/setup/setup.properties - general configuration
  
• lims/setup/resources/system/config.properties - configure where jboss sits
  
• lims/setup/resources/system/system.properties - configure email settings and jboss again. Also specify your data directories

let's start our first install

• go into /opt/jboss/seupx/lims
• execute 'sh firstinstall.sh'

at this point we run into several issues, since we want to install setupx in '/opt/setupx' and not '/usr/share/setupx'.


[java] Util_____________: DEBUG: Using org.xml.sax.parser : org.apache.xerces.parsers.SAXParser
[java] org.setupx.repository.SettingsUnavailableException: java.io.FileNotFoundException: /usr/share/setupx/lims/conf/system/system.properties (No such file or directory)

This is directly related to a bug in the class "org.setupx.repository.Settings" and can be fixed with a simple change in this file to reflect our directory.

This is a nice example why it's considered to be bad practice to hard code file paths.


private static File file = new File("/opt/setupx/lims/conf/system/system.properties");

Since we already created the database with the 'firstinstall.sh' script we now just need to go into setupx/lims/setup and execute:

sh setup.sh

After the installation finished we could now access setupX under:

http://****:8080/xx1/

your setupx username is the set email address and your password the same.

review:

The installation took a lot of effort to get it working and is not yet perfect. I think the first thing which should be done is to write a graphical ant installer which modifies the files for you, to make this process a bit easier.
The main issue is really the absolute file path in the one java class.

Time cost: a bit over an hour and 30 minutes.