Документ взят из кэша поисковой машины. Адрес оригинального документа : http://www.naic.edu/~palfa/doc/mysql_intro.txt Дата изменения: Thu Apr 7 17:54:28 2005 Дата индексирования: Sun Apr 10 04:11:20 2016 Кодировка: Поисковые слова: п п п п п п п п п п п п п п п п

#############################################################################
# A quick introduction to what's going on with MySQL and the PALFA database #
#############################################################################

by: Jason Hessels (hessels@physics.mcgill.ca)
last updated: August 5th, 2004

(updated August 10th, 2004 by Beth Reid)

1) The database server is located on 'fusion00.naic.edu'.

To access the database:

a. Login to Arecibo (i.e. 'remote2.naic.edu')
b. Login to 'fusion00.naic.edu'
c. Type 'mysql -u palfa -p'
d. You will be asked for a password: enter 'alfa_305m'
e. You should now be at the 'mysql>' prompt
f. To list the databases on this server type 'show databases;'
g. We are using the 'PALFA' database. To select this, type 'use PALFA;'
h. To list the tables in the database, type 'show tables;'. A description of each table follows.

2) Tables in the PALFA database:

To print the fields contained in each of these tables, follow the steps in 1) for connecting to the
database and then type 'describe **tablename**;' where **tablename** is one of the following:

atnfshort: This contains the pulsars in the ATNF catalog, with a reduced number of parameters for each pulsar.

candidates: This table will contain the candidates we identify after full processing of the data.

drives: This will contain information on where our different portable drives are and where they're going.

longerproc: This contains the results of the high-resolution preliminary processing on the ASP.

observations: Contains information about every single scan done by the PALFA project ID p1944.

pointings: Contains the list of pointings that are being observed.

prelimproc: This contains the results of the low-resolution "real-time" processing on the ASP.

results: Contains the status of the full (highest resolution) processing of the data.

3) Querying a table:

The whole point of the database is to organize information about the survey in a way that makes it easy to access.
The following describes some simple queries one can do to extract information from the database. You need to follow
1) to login to the PALFA database before you can run these queries. Note that MySQL syntax is case insensitive, so
it doesn't matter if you type 'SELECT', 'select', or 'SeLeCt'. However, if the name of a table is 'observations',
typing 'OBSERVATIONS' or 'Observations' will return an error. Also, don't forget to include a semicolon (';') at the
end of your commands.

a. Say you want to know how many scans we have made so far (including observations of test pulsars and scans that were
aborted before they were done):

mysql> SELECT COUNT(*) FROM observations;

b. Now say you want to know how many scans with pointing_ID beginning with 'G' (i.e. true survey pointings, not test
observations of a known pulsar or confirmation observations) and integration time greater than 60s have been completed:

mysql> SELECT COUNT(*) FROM observations WHERE pointing_ID LIKE 'G%' AND int_time > 60;

If you want to print all the rows that satisfy these conditions (warning: there may be a lot of them!), then use '*'
instead of 'COUNT(*)'. Notice that we've used the wildcard '%' in the statement 'LIKE 'G%''. MySQL allows you to
use regular expressions, which I think you'll agree is pretty swanky!

c. Say you want to know what are the 10 most recent observations (starting with the most recent), along with a subset of
the fields associated with this observation:

mysql> SELECT obs_ID,pointing_ID,date,int_time,ra0,decl0 FROM observations ORDER BY date DESC LIMIT 10;

If you wanted to pick the first ten observations from the survey, you'd use 'ASC' instead of 'DESC'.

d. Say you want to know what pointing positions exist in a certain area of the sky:

mysql> SELECT l,b FROM pointings WHERE l BETWEEN 50.0 AND 51.0 AND b BETWEEN -1 AND 1;

e. Say you're observing and at the same time checking the output of the realtime search code being run on ASP.
You find a 1.438s candidate and you want to check that it isn't already a known pulsar. You can do this
using the atnfshort table in the PALFA database:

mysql> SELECT psrname,ra,decl FROM atnfshort WHERE period BETWEEN 1.4 AND 1.5;

Hopefully your new candidate does not appear in this short list!

f. For each observation, there is a unique 'obs_ID'. This is simply the MJD of the observation underscore the scan number
on that day (e.g. 53222_0013). If you want to see all the candidates from a particular obs_ID and beam:

mysql> SELECT * FROM prelimproc WHERE obs_ID = '53222_0013' AND beam_num = '0';

g. Now let's get a bit fancier and query two tables at the same time. Hang on to your hat...

What if in our last query, we want also want to know the RA and DEC of the pointings:

mysql> SELECT prelimproc.*, ra0, decl0 FROM prelimproc, observations WHERE prelimproc.obs_ID = '53222_0013' AND beam_num = '0'
AND prelimproc.obs_ID = observations.obs_ID;

10. Here's one that took me forever to figure out (thanks to Fernando):

We want to query the database to find out how many results every beam had
adding the 'having count(*) > 300' statement simply limits the ones you print
out.

select prelimproc.obs_ID, beam_num, count(2) from prelimproc, observations
where prelimproc.obs_ID = observations.obs_ID and observations.pointing_ID
like "G%" and (prelimproc.period < 118 or prelimproc.period > 122) group by
obs_ID, beam_num having count(*) > 300;


So... if you run through these examples you should get a basic idea of how to extract information from the tables. I will try
to add more examples soon. For further information, check out the MySQL documentation online at
'http://dev.mysql.com/doc/mysql/en/index.html' (warning: there's a lot of information there that won't be useful for the average
user). Also check out mysql_python_scripts.txt in this same directory to find out what scripts we've been writing to get data into
and out of the database.