List of articles Terminology Choose language

Request into several databases simultaneously on DDL and DML

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

nickname of concrete database (any word, except 'all' and 'default').
One of databases is database, in which user "is" - usernames and passwords of other databases will be taken from it for execution of stored procedures, and also it will be used, if nickname isn't specified.
```
select  ...  from db1:NAME ...
insert  into      db2:NAME ...
```

predicate

"all:" is all databases
"default:" is database, in which user "is" (not silent, but forced specification of this database)

insert  into          all:NAME ...
select  ...  from default:NAME ...
select  ...  where fld > any (select ... from all:NAME ...

marker - word, meaning all databases consecutively (any two markers never simultaneously mean the same database). Sign "%" is put before marker.

insert  into %db1:NAME select * from %db2:NAME;

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

usual value, assinged into field by user

select                   FieldFK, ...
insert  into ... values (FieldFK, ...)

system value, which contain identifier of external database (if record, to which field refers, is in external database) and to which it's possible to refer, if to write "#sys" after name of field, containing usual value:
```
select                   FieldFK#sys, ...
insert  into ... values (FieldFK#sys, ...)
```

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:

under login of user, which start procedure
```
create procedure ...
```
under login of author of procedure
```
create sticky procedure ...
```

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
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

'IdUser' is unique identifier of user in this database
'al' (address literal) is DNS-address
'an' (address numeric) is IP-address
- if 'al'≠null, then it is used
- if field 'al'=null, then 'an' is used
'username' is name of user in external database
'password' is password of user in external database. And:
- SQL-command or stored procedure, refering to external database, asks username for external database in program-terminal (if 'username'=null for this 'IdUser' and for this nickname) and password (if 'password'=null)
- timer, refering to external database, will not begin execution, if 'username'=null or 'password'=null, nothing question will be
'IdDb' is identifier of external database, unique inside this database (if 'IdDb'=null, then it is database, in which this record itself is)
'nickname' is nickname of external database (several different 'IdUser' can have identical 'nickname' with identical or different 'al' and 'an', but one 'IdUser' cann't has two identical 'nickname')
'datatime' is data of last updating of this record of table 'sys-nicknames' (it is used for replication of tables 'sys-nicknames')

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

updates field 'an' by value, got from DNS-server (field 'an' itself exists for event of breakage of DNS-server)

adds new records (in which field 'password'=null), when user executes it into external database, which yet not registered in 'sys-nicknames' with new 'ra' and 'username' (value of field 'password' is set by command 'update', rights of access are given to user on each record separately, including to each record of system table)

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

Сайт управляется системой uCoz