List of articles   Terminology   Choose language


Referencing into several databases in one request


Prefix

One request can address to several databases simultaneously, if location of object is specified by prefix, which is before name of object through colon. Prefix can be

As result, new type of system information appears - field, refering to other field by foreign key, indeed contain two values:

And as result we can give possibility to refer to external database by foreign key at creating or altering table, or not give (deprive this possibility) - it is doing by creating (adding) or not creating (deleting) of field, name of which is finished by "#sys":
create/alter table ... (
  ...
  FieldFK      num3,
  FieldFK#sys  num2,
  ...
)
Also as result, it's possible to create stored procedures, which, being started by not author, will executed in external database:

System table of nicknames

Each database stores all nicknames, known for its users, in system table 'sys-nicknames' for execution of stored procedures and timers (some of nicknames can name database, in which this enumeration of nicknames is).

sys-nicknames
IdUser al an username password IdDb nickname datatime
101 database.remote.com   Smith pwds 31 db1  
101   123.123.123.123     33 db2  
105 data.storage.com 234.234.234.234 Tomson   34 db1  

It's possible to change database, in which user "is", by command 'connect', in which username, password and address of database are not specified - username, password and address of database will be taken from table 'sys-nicknames' (next command 'default' will use table 'sys-nicknames' of new database, i.e. of 'db2'):

connect nickname="db2";

Command 'connect' affects to table 'sys-nicknames' of database, in which user is: it

connect
  ra="data.storage.com" username="Johnson" password="pwdj" nickname="db4";
update sys-nicknames set password=pwdj where
  ra="data.storage.com" username="Johnson"                 nickname="db4";
sys-nicknames
IdUser al an username password IdDb nickname datatime
101 data.storage.com 234.234.234.234 Johnson pwdj 38 db4  

Replication

After all said, replication of databases by branded programs loss all sense - always it's possible to make it by extension of SQL, described above.



Dmitry Turin



List of articles   Terminology   Choose language