OiDbImplementationsNotes < Jmmc/Software

This page is listing technical discussions and notes on the !OiDB project. See the definition of the OiDB project in the document from the [[OiDbReunion26June2013][2013-06-26 meeeting]] or in the [[OiDbSpecs][wiki page]].


%TOC%


---+ Notes

---++ Collection

Define a collection as a way for a submitter (dataPI) submitting data to group entries together.

---++ Push/Pull for database insertion

Methods for adding data :

   * submit files/collection: the user make HTTP PUT request to an URL on the server for his OIFits files or collection of OIFits files. The portal displays a Web form for manually importing new data.

   * download files/collection: the application is in charge of automatically downlaading the files from the server specified by the user. TODO: define format: XML files with resources to analyze ? parsing HTML file listing ? The portal let the user specify the address of his server and the access conditions.


---++ Data update

Files/collections may be modified by the owner from time to time. The application should regularly download and reprocess the distant repository to stay up to date.

Ask the user for reimporting the data (user resend data)? Automatic import (identify modifications on files: checksum)?

Is it possible to use the name of the file as observation ID?


---++ Data display

To retrieve external data :

   * turn the portal into a proxy: at user request, the portal check the repository, download the file and return it to the user. The application knows for sure that the user has download the file.

<img src="%ATTACHURLPATH%/external_data_proxy.png"/>

   * the user perform a request on the application that return the URL for the requested resource. The user then make an HTTP GET request on this URL to download the document. The application can log the request (the user may have downloaded the file but not guaranteed).

<img src="%ATTACHURLPATH%/external_data_direct.png"/>

   * make a redirection (similar to Google search results): slightly different from the previous proposition, the user performs a request on the portal that issue a redirect (URL forwarding, HTTP 303) to the external repository. Do all clients and libs support 303 redirections ?


---++ Private data

Some data may not be downloadable until a given amount of time. Still should let people get in contact with the dataPI.


---++ Changes to the current database schema

   * sort by collection.

   * link data and dataPI.

   * add fields for descriptions and comments on data (file, collection target).

   * make link between observation in the database and scientific publication (bibliographic reference)

   * keep versions from a same source.

---++ Data model

This information is now in a [[OiDbColumns][dedicated topic]]

---++ OIFITS

Define/extract metadata from submitted OIFits files.

   * manage datum with flag=true (OI_VIS, OI_VIS2, OI_T3).

   * take into account repeated wavelength (more than one OI_WAVELENGTH in a single OIFits with the same wavelength value).

   * measurements from more than one instrument in the same file (OI_WAVELENGTH tables with different INSNAME while observing the same target).


---++ Publishing the the VO
See : 
   * http://wiki.ivoa.net/twiki/bin/view/IVOA/PublishingInTheVONew

---++ Web portal examples

|*Web Site*|*MultiCriterii*|*ADQL*|*TAP/ObsCore*|*Samp*|*Description/comments*|
| http://portal-nvo.noao.edu/adql/query | X | X | | | |
| http://voparis-srv.obspm.fr/portal/vo.php | X | | ? | X | |
| http://saada.u-strasbg.fr/taphandle/ |  | X | | X | |
| http://labs.adsabs.harvard.edu/adsabs/ | X | NC | NC | NC | Not for observation data ;) |
| http://polarbase.irap.omp.eu/ | X | | X | |  |
| http://herschel.esac.esa.int/Science_Archive.shtml |  | | | | javaWebStart interface  see Johan screenshots on the mailinglist : 05/02/2014 14:54 |
| http://telbib.eso.org/?boolany=or&boolaut=or&boolti=or&yearto=2014&boolins=or&telescope[]=%22ALMA%22&booltel=or&search=Search | X | ? | ? | ? | base de données de publi qui renvoie vers l'archive alma |
| http://almascience.eso.org/aq/ | X | ? | ? | ? | Archive Eso. simple d'utilisation recuperation de tgz sur pages web+ script shell |
| http://giraffe-archive.obspm.fr/search.html | X | | | coté VO bien développé A voir!! |


---+ Infrastructure documentation

---++ Linux packages (as root)
   * Add gavo debian sources (http://soft.g-vo.org/repo)
<verbatim>
cat > /etc/apt/sources.list.d/gavo.list << EOF 
# oidb requirements
# http://docs.g-vo.org/DaCHS/install.html#debian-systems
deb http://vo.ari.uni-heidelberg.de/debian stable main
deb-src http://vo.ari.uni-heidelberg.de/debian stable main
EOF 
wget -qO - http://docs.g-vo.org/archive-key.asc | apt-key add -
apt-get update

</verbatim>

   * apt-get install -y postgresql postgresql-9.1-pgsphere libpostgresql-jdbc-java openjdk-7-jdk apache2 vim indent sudo subversion ant

---++ Accounts (as root)
   * create the =existdb= user account with bash account (can be done during install process)
 
---+++ Manual software package (as existdb user)
   * wget http://optimate.dl.sourceforge.net/project/exist/Stable/2.2/eXist-db-setup-2.2.jar
   * mkdir exist-db ; cd exist-db/
   * java -jar ~/eXist-db-setup-2.2.jar -console

   * [[%ATTACHURL%/ExistDbInstallationSample.txt][ExistDbInstallationSample.txt]]: another doc exist for a quick install procedure sample for existdb

<verbatim>
existdb@oidb:~/exist-db$ java -jar ~/eXist-db-setup-2.2.jar -console
Select target path [/home/existdb/exist-db] 

press 1 to continue, 2 to quit, 3 to redisplay
1
Set Data Directory
Please select a directory where eXist-db will keep its data files. On Windows, this should be outside the 'Program Files' directory. Please make sure eXist can write to the directory it is installed in.
Data dir:  [webapp/WEB-INF/data] 
/home/existdb/data
press 1 to continue, 2 to quit, 3 to redisplay
1
Set Admin Password and Configure Memory
Enter password:  [] 
XXXX
Enter password:  [XXXX] 
XXXX
------------------------------------------

Maximum memory in mb: [1024] 
2048
Cache memory in mb: [128] 
512
press 1 to continue, 2 to quit, 3 to redisplay
1
[ Starting to unpack ]
...
--- Initialization complete. Shutdown embedded database instance ---
[ Console installation done ]
</verbatim>

   * open dahsboard/package-manager and remove useless applications (demo/client...)

   * install exist-db wrapper (root)
<verbatim>
   cd /etc/init.d
   ln -s ~existdb/exist-db/tools/wrapper/bin/exist.sh .
   update-rc.d exist.sh defaults
</verbatim>

   * set memory options for wrapper:
<verbatim>
root@oidb:/home/existdb/exist-db# grep 2048 /home/existdb/exist-db/tools/wrapper/bin/../conf/wrapper.conf
wrapper.java.initmemory=2048
wrapper.java.maxmemory=2048
</verbatim>

   * Tell jetty's servlets to use the frontend server name -> set forwarded property of the SelectChannelConnector to true
<verbatim>
<New class="org.eclipse.jetty.server.nio.SelectChannelConnector">
   ...
   <Set name="forwarded">true</Set>
   ...
</verbatim>


---++ Apache setup
   * create the virtual host file for oidb:
<verbatim>
<VirtualHost *:80>

	ServerName                   oidb.ujf-grenoble.fr

	DocumentRoot                 /var/www/
	AllowEncodedSlashes          on

	# see doc on https://wiki.eclipse.org/Jetty/Howto/Configure_mod_proxy
	#            http://httpd.apache.org/docs/2.2/mod/mod_proxy.html
	#            http://exist-db.org/exist/apps/doc/production_web_proxying.xml#D2.2.3.5
        #            https://wiki.eclipse.org/Jetty/Tutorial/Apache#Configuring_mod_proxy
        #            https://wiki.eclipse.org/Jetty/Tutorial/Apache#Proxying_SSL_on_Apache_to_HTTP_on_Jetty 

	ProxyRequests                off

# does not yet work on next interpolate (requires mod_rewrite ?)	ProxyPassInterpolateEnv On

	# ProxyStatus On

	# do not redirect server-status
	ProxyPass                    /server-status !

# uncomment to allow direct access on exide, dashboard and every exist applications (still reachable over 8080)      
#	ProxyPass                    /exist/ http://localhost:8080/exist/ nocanon
#	ProxyPassReverse             /exist/ http://${SERVER_NAME}/exist/ interpolate

	# register tap servlet as /tap
	# and map /exist/tap to the taplib servlet because async results contain ${SERVER_NAME}:80/exist/tap using getContextPath in the java code 
	ProxyPass                    /tap/ http://localhost:8080/exist/tap/ nocanon
	ProxyPass                    /exist/tap/ http://localhost:8080/exist/tap/ nocanon
#	ProxyPassReverse             /tap/ http://${SERVER_NAME}:80/exist/tap/ interpolate
	ProxyPassReverse             /tap/ http://oidb.ujf-grenoble.fr:80/exist/tap/
	
        # leave access to uploaded files
	ProxyPass                    /exist/apps/oidb-data/oifits/ http://localhost:8080/exist/apps/oidb-data/oifits/ nocanon

#	ProxyPass                    /exist/apps/oidb/ http://localhost:8080/exist/apps/oidb/ nocanon
	ProxyPass                    / http://localhost:8080/exist/apps/oidb/ nocanon
#	ProxyPassReverse             / http://${SERVER_NAME}/exist/apps/oidb/ interpolate
	ProxyPassReverse             / http://oidb.ujf-grenoble.fr/exist/apps/oidb/

#	ProxyPassReverseCookieDomain localhost ${SERVER_NAME} interpolate
	ProxyPassReverseCookieDomain localhost oidb.ujf-grenoble.fr
	ProxyPassReverseCookiePath   /exist /

	ErrorLog                     ${APACHE_LOG_DIR}/oidb-error.log
	LogFormat "%h %l %u %t \"%r\" %>s %b" common
	CustomLog 		     ${APACHE_LOG_DIR}/oidb-access.log common

</VirtualHost>


</verbatim>

   * copy the same lines (but log files) in the /etc/apache2/sites-available/default-ssl

   * a2enmod ssl
   * a2ensite oidb 
   * a2ensite default-ssl 
   * a2dissite 000-default 
   * a2enmod proxy proxy_http deflate

   * Set !MaxRequestsPerChild to 10000 in /etc/apache2/apache2.conf

   * apache2ctl -S

---+++ Creation / installation of a certificate
   * Get your signed certificate : https://dsi.ujf-grenoble.fr/catalogue-services/securite/obtenir-certificat-serveur-tcs
   * and install it in default-ssl:
<verbatim>
/etc/apache2/sites-enabled/default-ssl:	SSLCertificateFile /etc/ssl/certs/cert-26285-oidb.ujf-grenoble.fr.pem
/etc/apache2/sites-enabled/default-ssl:	SSLCertificateKeyFile /etc/ssl/private/oidb.ujf-grenoble.fr.key
/etc/apache2/sites-enabled/default-ssl:	SSLCertificateChainFile /etc/ssl/certs/chain-26285-oidb.ujf-grenoble.fr.pem
</verbatim>



---++ !PostgreSQL configuration (as postgres)

   * Create database and database user
<verbatim>
#    create PostgreSQL user
createuser --no-createdb --no-createrole --no-superuser --encrypted oidb

#    create PostgreSQL database
createdb --owner=oidb --encoding=UTF-8 oidb

#    init pgsphere on new database
psql -d oidb -f //usr/share/postgresql/9.1/contrib/pg_sphere.sql

# set pass database permissions
# add as second rules in pg_hba.conf the following lines:
# (/etc/postgresql/9.1/main/pg_hba.conf) 'local' can't be use with JDBC
host   oidb             oidb             127.0.0.1/32           trust

#    restart postgres
service postgres restart
</verbatim>

---++ Retrieve setup data (as existdb)
   * cd ~
   * read -s -p "enter password" p
   * wget --no-check-certificate --user=mella --password=$p https://svn.jmmc.fr/jmmc-web/trunk/exist/oidb/conf/oidb.sql

   * Create table
<verbatim>
psql -U oidb -d oidb -f oidb.sql
</verbatim>


---++ Sources repository
Main oidb sources are located under the next repository:
   * https://svn.jmmc.fr/jmmc-sw/oiTools/trunk/
      * jmal : JMMC Astronomical library (ALX HMS / DMS conversion)
      * oitools : OIFits reader / writer library
      * oiexplorer-existdb : oitools wrapper (in future OIExplorer wrapper ...)

   * https://svn.jmmc.fr/jmmc-web/trunk/exist/
      * jmmc-realm : An eXist-db realm for authentication with the JMMC user database. 
It creates a new eXist-db account with the registration information from the JMMC service. 
Accounts are created on-demand. 
The user password is never stored in eXist-db, user identification is still performed by the underlying JMMC user database. 
All authenticated users are added to a 'jmmc' group. Users with specific credentials are also added to groups named after the credential. 

      * jmmc-resource : shared XQuery functions + JMMC libraries
      * oidb : web application module
      * oidb-data : data layer definition (collection, comments, logs, uploaded oifits files)


---++ eXist-db Setup
   * Activate java binding (used by jmal / oitools) : set xquery/@enable-java-binding to yes in file exist-db/conf.xml (existdb user)
   * Restrict guest access to exide : set @guest to no in existdb:///db/apps/exide/configuration.xml


---++ OiDB Build

Requires: ant, mvn, eXist-db 2.2

=First time: install exist-db libraries (exist.jar and xmldb.jar) into the maven local repository=
see oiexplorer-existdb/install-existdb-artifacts.sh


---+++ jmmc-realm module
   * mvn install
   * get the jmmc-realm package in exist/jmmc-realm/target/exist-security-jmmc.jar

---+++ jmmc-resource
   * build the jmcs and jmal modules (mvn install) (out of scope)
   * build the oitools module (mvn install)
   * build the oiexplorer-existdb (requires oitools)
      * mvn install
   * build the jmmc-resource (requires jmal oitools oiexplorer-existdb)
      * mvn install (only gathers previously built modules)
      * ant
   * get the jmmc-resource package in exist/jmmc-resources/build/jmmc-resources-x.y.xar

---+++ oidb-data
   * build the oidb-data (use svn to get aspro configuration)
      * ant
   * get the oidb-data package in exist/oidb-data/build/oidb-data-x.y.xar

---+++ oidb
   * build the oidb module
      * create the file : config.properties
<pre>
jndi-name = jdbc/oidb
sql-table = oidb
tap-baseurl=http://oidb.ujf-grenoble.fr/exist/tap
</pre>
   * ant -Dconfig.properties=config.properties
   * get the oidb package in exist/oidb/build/oidb-x.y.xar

---+++ oidb-tap
   * build the oidb-tap
      * mvn install
   * get the oidb-tap package in exist/oidb-tap/target/oidb-tap-TRUNK.jar



---+++ distribution package
TODO: gather all items (jar, xar, ...) into a single zip file ...

   * jmmc-realm : jmmc-realm/target/exist-security-jmmc.jar
   * jmmc-resource : jmmc-resources/build/jmmc-resources-x.y.xar
   * oidb-data : oidb-data/build/oidb-data-x.y.xar
   * oidb : oidb/build/oidb-x.y.xar
   * oidb-tap : /oidb-tap/target/oidb-tap-TRUNK.jar and /oidb-tap/target/runtime-libs/tap-x.y.jar


---++ OiDB Setup

---+++ jmmc-realm module
   Install jmmc-realm module to enable JMMC's authentication :
      * copy the JAR file exist-security-jmmc.jar in /home/existdb/exist-db/lib/extensions/
      * edit the configuration for the Security Manager from eXide =/db/system/security/config.xml= and add:
<verbatim>
     <realm id="JMMC">
       <url>https://jmmc.obs.ujf-grenoble.fr/account/manage.php</url>
     </realm>
</verbatim>
      * restart exist-db
      * check it's working:
<verbatim>
grep JMMC /home/existdb/exist-db/webapp/WEB-INF/logs/exist.log
</verbatim>

---+++ oidb-tap
Note: for the time being, log location (!OiDBServiceConnection.java), JDBC url and user identification (!OiDBDBConnection.java) are hard-coded in oidb-tap source code.

   * prepare directory for taplib logs (root user)
<verbatim>
mkdir -p /var/log/oidb/taplib
chmod -R 775 /var/log/oidb
chown -R existdb /var/log/oidb
</verbatim>
   * install postgres driver to eXist-db lib directory (first time only)
<verbatim>
cp /usr/share/java/postgresql-jdbc4.jar ~existdb/exist-db/lib/user/
</verbatim>
   * install oidb-tap files to eXist-db lib directory
<verbatim>
cp taplib*.jar oidb-tap*.jar ~existdb/exist-db/lib/user/
</verbatim>
   * Jetty setup
    edit ~existdb/exist-db/webapp/WEB-INF/web.xml
<verbatim>
    <servlet>
        <servlet-name>OiDBTAPServlet</servlet-name>
        <servlet-class>fr.jmmc.oidb.tap.OiDBTAPServlet</servlet-class>
    </servlet>
</verbatim>
   * exist-db setup
   add to ~existdb/exist-db/webapp/WEB-INF/controller-config.xml
<verbatim>
    <forward pattern="/tap" servlet="OiDBTAPServlet"/>
</verbatim>
   * restart exist-db service (root)
   * test:
<verbatim>
wget http://localhost:8080/exist/tap/availability
wget 'http://localhost:8080/exist/tap/sync?MAXREC=1&QUERY=SELECT * FROM oidb&FORMAT=votable&REQUEST=doQuery&LANG=ADQL'
</verbatim>


---+++ exist-db packages
Use the package manager (admin):
   * =JMMC Commons module= : install jmmc-resources-x.y.xar (may require on dev machines the http proxy : -Dhttp.proxyHost=... -Dhttp.proxyPort=...)
   * =oidb-data= : install oidb-data-x.y.xar (only if not yet present!) =TODO: how to handle migration / data model lifecycle ?=
   * =OIData portal= : install oidb-x.y.xar

That's all folks ! 


---+++ Links

   * OIDB portal : http://oidb.ujf-grenoble.fr/index.html
   * exist-db admin : http://oidb.ujf-grenoble.fr:8080/
   * tap service : http://oidb.ujf-grenoble.fr:8080/exist/tap/capabilities

<verbatim>
http://oidb.ujf-grenoble.fr:8080/exist/tap/sync?QUERY=SELECT%20COUNT%28*%29%20FROM%20%28SELECT%20ALL%20%20*%20FROM%20oidb%20AS%20t%20WHERE%20%28%20t.calib_level=0%20%29%20%20%29%20AS%20e&FORMAT=votable&REQUEST=doQuery&LANG=ADQL
</verbatim>


---+++ TODO
---++++ Backup

   * Store db content on local files : 
      * SQL backup
      * existbd backup
      * oidata backup
   * Store files on an external medium


---++++ Migration / Maintenance
   * describe procedure
      * provide readonly mode
      * provide a common message displayed on every pages
      * ...

<!-- dead doc -->
<!--
---++++!! Dependance avec pgsphere (legacy)
   * Installing !PostgreSQL 8.4.x on !CentOS 5.5
<verbatim>
# http://heatware.net/linux-unix/how-install-postgresql-8-4-centos-5/

#    Download the PostgreSQL 8.4 repository RPM
wget http://yum.pgrpms.org/reporpms/8.4/pgdg-centos-8.4-2.noarch.rpm
#    Add the repository
rpm -ivh pgdg-centos-8.4-2.noarch.rpm
#    Use yum to install PostgresSQL
yum install -y postgresql postgresql-server
#    Initialize the database
service postgresql initdb
#    Start the database
service postgresql restart
#    Automatically start database on boot
chkconfig postgresql on
</verbatim>

   * Installing PGSphere
<verbatim>
# install PGSphere
yum install pgsphere
</verbatim>

   * Install !PostgreSQL JDBC driver
<verbatim>
# install postgresql jdbc driver
yum install postgresql-jdbc
</verbatim>

---++++!! !PostgreSQL configuration

   * Create database and database user
<verbatim>
#    create PostgreSQL user
sudo -u postgres createuser --no-createdb --no-createrole --no-superuser --password --encrypted oidb

#    create PostgreSQL database
sudo -u postgres createdb --owner=oidb --encoding=UTF-8 oidb

#    init pgsphere on new database
sudo -u postgres psql -d oidb -f /usr/share/pgsphere/pg_sphere.sql
</verbatim>

# set pass

   * Setup database permissions
<verbatim>
#    add to the top of /var/lib/pgsql/data/pg_hba.conf the following lines:
local   oidb        postgres                          ident
local   oidb        all                               md5

#    restart postgres
service postgres restart
</verbatim>

   * Create table
<verbatim>
psql -U oidb -d oidb -W -f oidb.postgres.sql
</verbatim>


   * [[%ATTACHURL%/oidb.postgres.sql][oidb.postgres.sql]]: !PostgreSQL table creation script
-->
Attachments
Topic attachments
I	Attachment	History	Action	Size	Date	Who	Comment
txt	ExistDbInstallationSample.txt	r1	manage	7.4 K	2015-09-24 - 06:44	GuillaumeMella	quick install procedure sample for existdb
sql	oidb.postgres.sql	r3 r2 r1	manage	2.1 K	2014-11-18 - 10:39	PatrickBernaud	PostgreSQL table creation script
This topic: Jmmc/Software > WebHome > OiDb > OiDbImplementationsNotes
Topic revision: r53 - 2016-11-23 - GuillaumeMella