3.1.4 : 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. One would like to run a a query like

select top 1000 haloid, np, x,y,z 
  from millimil..mpahalo
 where snapnum=63
  order by np desc

and store the results in a table called massiveHalos. There are two ways to do this. The preferred way is to first create the table. The following is an example statement that will do this.

create table massiveHalos
(
  haloid bigint not null,
  np integer not null,
  x real not null,
  y real not null,
  z real not null
)

Then run the query as follows

insert into massiveHalos
select top 1000 haloid, np, x,y,z 
  from millimil..mpahalo
 where snapnum=63
  order by np desc

Succesive statements like this can be written to insert more rows in this table. Note that one can even concatenate the two statements and submit them in one execution. The slight disadvantage of this method is that one needs to write the create table statement. Instead one can use a shortcut, which is to use

select top 1000 haloid, np, x,y,z
  into massiveHalos 
  from millimil..mpahalo
 where snapnum=63
  order by np desc

The "into ..." will create the table automatically and fill it with the result. This will fail if a table with that name already exists. A disadvantage of this method fo the service is that the statement does not give any information on the result. For the "insert into ..." statement the database returns the number of rows that was inserted. This does not happen for the "select ... into ...". This is probably mainly bothersome for our logging of database use, but for rthat reason we would prefere users to use the first method. It also performs better, as there is no shared tempdb that is used for temporarily storing the result, which will lock it for other users for the duration of the transaction.

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