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.
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 *
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.
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.
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)
To delete a given table from the user's MyDB use the following statement:
drop table mympahalo
Similar statements work for views and indexes:
drop view mymmhalo
drop index ix_mympahalo_haloid