3.1.1 : MyDB

Here we define various concepts that are important when working with a private database and describe various scenarios how the user might use this in the particular implementation provided by GAVO and the Virgo consortium.

The MyDB concept

With MyDB we indicate a private database assigned to a registered user, which(s)he can update. This in contrast to the majority of databases which are read-only. This concept was first conceived by the SkyServer team of the SDSS collaboration. Their implementation can be found here. CAS-jobs is more sophisticated than the current GAVO implementation and we are working on implementing a version of this on top of the Millennium databases as well. This will be announced on these web pages.

Context

When a user connects to the database server a single database is selected as the default database. This database we refer to as the context. The context database in a particular session is indicated with the word "context" appended in parens to the database name in the left menu on the main query window. SQL queries do not need to append the name of the context database in front of table names. When a user has a MyDB assigned, this database will automatically be the context database. Note that we allow a user write access to multiple database, in a sense providing multiple MyDBs. This is so that users who wish to collaborate can do so. Only one will be the context database. Currently it is not possible to change which database is assigned to be the context database.

select ... into ...

The main use of MyDB will be to store results of SQL queries on the server. The following example SQL shows how this can be done:

select top 100 * 
  into mympahalo 
  from millimil..mpahalo

This will create, in the context database, a table named "mympahalo" which will contain the first 100 rows from the result set of the query. This query returns no result, but if it succeeds will indicate this on the web page or CSV result, depending on the query mode.

Views

Users can decide to create database views in their MyDB. The following example illustrates a possible reason:

create view mymmhalo as
select * from millimil..mpahalo

Here a user may decide that (s)he would rather not always write the "millimil.." prefix when querying the "mpahalo" table in the "millimil" database. Instead this view can be used in queries where otherwise the full term would be used.

Indexes

In certain cases a user may decide to add an index to a table that was created in the MyDB. The following is an example of a statement that would do so.

create index ix_mympahalo_haloid on mympahalo(haloId)

Deleting objects

To delete a given table from the user's MyDB use the following statement:

drop table mympahalo

pre> Similar statements work for views and indexes:

drop view mymmhalo

and

drop index ix_mympahalo_haloid