Flexbol database : an introduction

1
Flexbol database an introduction

• Alexandre BEELEN
• IAS, Orsay, France

• PHP/MySQL
• System management
• Database design
• flexbol scripts
• Time accounting
• an example
• Authorization
• Future

2
Flexbol database PHP/MySQL

• PHP (http//www.php.net)
• Script language to create dynamical HTML pages
• These pages could be classical HTML pages, but
  with PHP code.
• Test of PHP
• Syntax similar to language C with loans in Perl
  and Java
• PHP scripts are interpreted by the server
• The source code is not available from the
  produced page
• Interface with Database Oracle, Sybase, MySQL,
  ODBC
• Works on most UNIX/Linux/BSD/NT platform

3
Flexbol database PHP/MySQL

• MySQL (http//www.mysql.org)
• MySQL is a relational database management system.
  
• ODBC compliant
• SQL Strutured Query Language
• SELECT FROM database WHERE field0 1
• Separate tables instead putting all data in one
  big storeroom
• One database, multiple tables
• Tables are linked ? speed and flexibility
• Possibility to combine data from several tables
  on request
• SELECT table1.field1, table2.field3
• FROM table1, table2
• WHERE table1.field2 table2.field2
• Multi-users, multi-threaded
• Easy interface to/from PHP
• Works on most UNIX/Linux/BSD platform

4
Flexbol database System management

• PHP server
• Web scripts ? module (Apache, Ms IIIS, Netscape,
  iPlan )
• In line scripts ? PHP executable
• Programs with GUI ? PHP-GTK
• phpMyAdmin ? set of tools to manipulate MySQL
  table
• MySQL server
• Server on any supported platform
• Does not need to be on the same server of the
  PHP/Web server
• Web Server
• Apache provide easy interface with PHP MySQL.
• Large variety of platform
• Apache Server with PHP module MySQL currently
  installed on mrt-lx2
• MySQL 3.22.32 / 3.23.49a
• PHP 4.0.0 / 4.1.2
• Apache 1.3.12 / 1.3.23

5
Flexbol database System management
6
Flexbol database Database design

• The most critical point
• All depends on the design of the database
• One table to log the observations
• LOGBOOK.ALL ? logbook
• One table to list the sources
• .CAT ? source
• One table to rules them all
• session

7
Flexbol database LOGBOOK.ALL

• Obs program log example
• 15-FEB-02 PROJECT FLEXBOL OBSERVER lutz
  OPERATOR fernando
• --------------------------------------------------
  --------------------------------------------------
  ----------------------------
• SCANSOURCE BASLAMBDA BETA VEL
  COLNULESFCZUT AZMELVOFF_1OFF_2
  ISSSSTIBEFREQUENCY LINE
• PROCEDURE
  REF_1REF_2
  BEFREQUENCY LINE
• --------------------------------------------------
  --------------------------------------------------
  ----------------------------
• 7864HERO-2 -1132422.3 272901 0L
  -2 0-1.90031 86 45 0 0H 16
  1010250.00000LCONT
• ONOFF
  150 0
  
• 7865HERO-2 -1132422.3 272901 0L
  -2 0-1.90035 87 46 0 0H 16
  1010250.00000LCONT
• ONOFF
  150 0
  
• 7866HERO-2 -1132422.3 272901 0L
  -2 0-1.90039 88 47 0 0H 16
  1010250.00000LCONT
• ONOFF
  150 0
  
• 7867 1308326 1130807.5 323640 0L
  -2 0-1.90042 88 48 0 0H 4
  3010250.00000LCONT
• POINTING
  150 0
  
• 7868 1308326 1130807.5 323640 0L
  -2 1-1.90045 83 53 0 0H 6
  1010250.00000LCONT
• FOCUS
  150 0
  

8
Flexbol database Database design

• The logbook table Image of the LOGBOOK.ALL file

Unique Identification Scan number Source
name Type of observation Correction
factor U.T. Azimuth Elevation Number of
subscan Time for each subscan Date of
observation Observer name Operator name Scan
cancelled ?
9
Flexbol database .CAT file

• The .cat file contains sources list for each
  project
• 150.cat
• INDIVIDUAL SNAM SLAM SBET
• COMMON SBAS -1 ! using J 2000.0 coordinates
• !
• !! QSOs at z 2
  
• !
• ! SOURCE RA DEC
  Z Mb
• !
• SNAM ahso1555 SLAM 00 17 33.3 SBET 15
  55 09 ! 2.110 -24.7
• SNAM 02250033 SLAM 02 25 47.9 SBET 00
  33 29 ! 2.092 -24.5
• SNAM 07076909 SLAM 07 26 17.8 SBET 69
  09 22 ! 2.100 -25.7
• SNAM 09144615 SLAM 09 14 03.6 SBET 46
  15 50 ! 2.180 -26.1
• SNAM 09144610 SLAM 09 14 04.1 SBET 46
  10 45 ! 2.180 -24.7
• SNAM 09243701 SLAM 09 24 20.5 SBET 37
  01 19 ! 2.450 -24.9
• SNAM 09444732 SLAM 09 44 27.2 SBET 47
  32 37 ! 2.103 -26.5

10
Flexbol database Database design

• The sources table List all the sources

Source ID Name of the source Position of the
source Priority within the project Project
number Comments
11
Flexbol database Database design

• The session table List all the projects

Project number PI name Title of the project Short
description Mode of observation Allowed time Time
spent Priority of the project Weather
condition Remark Path to the command file
live example
12
Flexbol database flexbol scripts
session.php
project.php
logbook.php
edit_project.php
edit_source.php
13
Flexbol database flexbol scripts
var.inc
14
Flexbol database time accounting

• Source matching
• 9/10 characters used for extragalactic sources
  in LOGBOOK.ALL
• SUBSTRING(logbook.source,1,9)
  SUBSTRING(sources.name,1,9)
• Overheard time
• var.inc
• observation_desc array( "ON-OFF", "MAP",()
• overhead_time array( 100, 40, ()
• time computation
• refresh.inc
• ptime (issssti)flag(1.overhead_timep
  roc1./100)
• project.php
• timecount t_sstit_iss
• stimetimeiflagi(1.overhead_timepr
  ocfrom1./100) ()
• ptime_nstime ()

15
Flexbol database an example

• list_project.php
• Transitional//EN"
• an example of php
  script
• include "db.inc" // connect to the Db
• year_list array("2001","2002")
• foreach (year_list as key year)
• print "
  year
  \n"
• query"SELECT logbook.scan FROM logbook,
  sources
• WHERE SUBSTRING(logbook.source,1,9)
  SUBSTRING(sources.name,1,9)
• AND sources.project 'id' AND YEAR(DATE) ".(year1)."
• AND YEAR(DATE) year ORDER BY DATE,SCAN"
• result mysql_query(query,db)
• while(list(scan) mysql_fetch_row(result) )
  
• scan_file scan.".fits"
• print "scan\n"

HTML Tag ? ? ? ?
PHP Tag ? ?
Include File ? Array definition ? Foreach Loop
? Print command ? SQL Query ? ? While loop
? Print Command? ?
16
Flexbol database an example

• list_project.php?id150
• Transitional//EN"
• an example of php
  script
• 
  2001
  
• 4937
• 4938
• ()
• 8325
• 8326
• 
  
• 
  2002
  
• 1211
• 1212
• ()
• 6943
• 6944
• 
  

17
Flexbol database Authorization

• Observer
• Need to access all projects informations
• Permission to modify source/project information
• priority
• source list
• Should not have the permission to modify
• the flex_bol scripts
• the database directly (phpMyAdmin)
• Internet
• Restricted access
• Permit to each PI to modify his own project
  information
• Different pages ?

18
Flexbol database Authorization

• MySQL
• Password protection
• User/Password could/must be different of the
  passwd file
• Connection information remains in ONE file
  (db.inc)
• db mysql_pconnect('localhost','flexible','teide
  510')
• mysql_select_db('flexible',db)
• ?
• Password in clear text ? Protect the access of
  this file
• Apache/PHP
• .htaccess protection
• https authentification
• Php session (?)

19
Flexbol database Future

• Security Issue
• Observer
• Internet
• Retrieve the LOGBOOK.ALL directly
• Permissions problems
• LOGBOOK.ALL bug
• Comments database
• Web interface
• Database backup
• Sources identification
• now by name
• by position
• Code optimisation

20
Flexbol database Future

• Printable forms
• PHP can produce pdf files directly
• Generate the project tar file automatically
• Even send email to PI !!
• Statistics
• Easy to do with database
• New database
• Pointing sources
• calibrators
• Graphics pages
• PHP can produce images dynamically
• HTML 3.0/4.0 compatibility
• Browser independent
• Pages Style
• CSS
• XML

• Last but not least !!
• Reduce the data
• Write the article
• Send it directly to AA
• Open Champagne bottle