db.Db query examples

Here we provide some SQL query examples using the db.Db class. More examples, related to DB management and record insertion are provided in the class wiki page.

Basic connection

To create and object and connect

D = db.Db

Check that the connection information in the object is correct. If not edit it. Note that you can create a Passwords.yml configuration file. For more details see PasswordsManager, and Configuration wiki pages.

For example you can edit the properties using e.g.,

D.User = {"LASTDB_Root","default"};  % will read User/Pass from a configuration file

or set the user and password explicitly:

D.User = "last_user"; % set it to a specific user name 
D.Password ="<the password of the last_user>"; set the password

Next, connect to the DB:

D.Conn;

when finished, disconnect using:

D.disconnect

Optionally, you can use a specific DB:

D.useDB('last');

Optionally you can see a list of DBs/tables/columns and more meta data information:

D.showCurrentDB
D.showTables
D.describeTable('visit_images')
[ColNames, ColTypes]=D.getColumns('visit_images')

You are now ready to work with the DB and run queries

Query examples

The examples provided here are using the following tables:

• last.proc_src - Sources detected in individual LAST images.
• last.visit_images - List of all visits sub images (24 sub images per visit).

Get the first few lines of a table

T=D.query("SELECT top 5 * FROM last.proc_src;");

Note that if you remove the "top 5" from the expression then all lines will be returned. Be careful, the output maybe very large, then do not do it, unless you know what you are doing.

Query by some columns

Searching by value of columns depends on column types (i.e., string or numerical) which you can get using the getColumns method.

Some strings search examples:

% exact search for fieldID 1000:
T=D.query("SELECT * FROM last.visit_images WHERE fieldid LIKE '1000'");

% fieldID that of the format 1000.*:
T=D.query("SELECT * FROM last.visit_images WHERE fieldid LIKE '1000%'");

% Using the position of substring command:
T=D.query("SELECT * FROM last.visit_images WHERE POSITION(fieldid, '1000')>0");

Numeric searches - count the number of images with FWHM<2 arcsec:

T=D.query("SELECT COUNT(*) FROM last.visit_images WHERE fwhm<2");

Compound searches:

T=D.query("SELECT * FROM last.visit_images WHERE fieldid LIKE '1200%' AND fwhm<3 AND limmag>20");
T=D.query("SELECT * FROM last.visit_images WHERE fieldid LIKE '1200%' AND fwhm<3 AND limmag>20 AND camnum=1 AND cropid=10");