wiki:OqaFma

Version 5 (modified by joshuadf, 7 years ago) (diff)

--

http://sig.biostr.washington.edu/projects/oqafma/

Testing

StruQL examples at http://fma.biostr.washington.edu:8080/noqafma/query.jsp

telnet fma 4242
Trying 128.95.10.191...
Connected to fma.biostr.washington.edu (128.95.10.191).
Escape character is '^]'.
WHERE X->":NAME"->"Heart", X->":NAME"->bar CREATE foo(bar);
<results>
  <foo>
    <bar>Heart</bar>
  </foo>
</results>
Connection closed by foreign host.

Moving OQAFMA

This is simpler than installing from scratch, but still takes some time. The OQAFMA database rebuild depends on source data from the FME database, so do the FMEInstall first.

Prerequisites:

yum -y install java-1.6.0-sun perl-DBD-Pg postgresql-server
# on source machine, get the pg_hba.conf entries and run
# pg_dumpall --schema-only > oldpgschemas
/etc/init.d/postgresql initdb
/etc/init.d/postgresql start 
chkconfig postgresql on
# load oldpgschemas
# rebuild the db:
runuser oqafma -c /home/oqafma/bin/cron-rebuild-oqafma.sh
# now add the OQAFMA init scripts and log dir
ln -s /home/oqafma/bin/oqafma /etc/init.d/oqafma 
ln -s /home/oqafma/bin/init-gapp-manage /etc/init.d/gapp-manage
ln -s /home/oqafma/bin/lisp-fms-java /etc/init.d/lisp-fms-java
ln -s /home/oqafma/logs/ /var/log/oqafma
chkconfig --add oqafma
chkconfig --add gapp-manage 
chkconfig --add lisp-fms-java

Creating the database

# run as postgres
createuser --no-superuser --no-createrole --no-createdb --pwprompt oqafma_user
# enter pass
createdb --owner oqafma_user testOQAFMA
createdb --owner oqafma_user OQAFMA
# as user oqafma
PQL=/home/oqafma/bin/newq.psql
psql -h localhost --user oqafma_user OQAFMA  < $PQL
# enter pass
psql -h localhost --user oqafma_user testOQAFMA  < $PQL
# enter pass
# next step takes many hours, see below for info
nohup ~/bin/cron-rebuild-oqafma.sh &

There's a script run from cron called build-oqafma-database.sh that imports the latest FMA into PostgreSql and then runs BuildIndexBuilder.jar to build the indices.

In case you've ever wondered what OQAFMA is doing, it creates tables with the transitive closures of each class. I left query logging on once during the testOQAFMA database build. I couldn't resist doing some quick stats on the query log, and the number of lines turned out to be something like this:

5948014 insert-slots lines with "INSERT INTO Slot_..."
1431156 with-select  lines with "SELECT short_value, long_value FROM fm WHERE ..."
   3609 other-sql    mostly Protege setup, DROPs and CREATEs
   1452 logs         non-SQL PostgreSQL messages; several ERRORs about using "DROP ... CASCADE"

Unfortunately there's not a good way to speed up all those INSERTs, and a quick benchmark of the SELECTs with prepared statements were only a 20% performance gain.