Обсуждение: Request into several DBMS simultaneously on DDL and DML
(1)Let's allow to __connect into several DBMS__ at once, last of them becomes DBMS 'by default'. User assign any nickname to each database at connection, except nickname 'all' and 'local', e.g. connect ra=database.remote.com username=Smith password=pwds nickname=db1; connectra=db.distination.com username=Tomson password=pwdt nickname=db2; connect ra=database.remote.com username=Johnsonpassword=pwdj nickname=db3; And user can change DBMS by default, e.g. local db2; Each time, when __name of datatype, table, view, sequence, index, trigger, function, procedure, user, role__ is used, it's implied, that object is in database by default, e.g. create type NAME ... alter table NAME ...drop sequence NAME ... create index NAME ... replace trigger NAME ... drop function NAME ... insert into NAME ... select ... from NAME ... create user NAME ... create role NAME ... grant NAMEto NAME (2)Let's allow to specify __location of object by prefix__, which is before name through colon. Prefix can be (2.1) nickname of concrete database, e.g. select ... from db1:NAME ... insert into db3:NAME ... (2.2) predicate "all:" (which designates all databases), "local:" (which designate database by default), e.g. insert into all:NAME ... select ... from local:NAME ... select ... where fld > any (select ... from all:NAME ... (2.3) marker - word, meaning all databases consecutively (__any two markers never simultaneously mean the same database__). Sign "%" is put before marker, e.g. insert into %db1:NAME select * from %db2:NAME; (3) As result, __new type of system information__ appears: field, refering to other field by foreign key, has additional bit except own value - bit specifies, whether record, to which it refers, is in the same or in other database. It's necessary to not signalize about break of foreign key, if remote database will not connected in next time. (4) 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. Document is stated on http://sql40.chat.ru/site/sql40/en/author/mc_eng.htm Related ideas are on: http://sql40.chat.ru/site/sql40/en/author/mc2_eng.htm Dmitry Turin SQL4 (4.2.0) http://sql40.chat.ru HTML6 (6.4.0) http://html60.chat.ru Unicode2 (2.0.1) http://unicode2.chat.ru Computer2 (2.0.3) http://computer20.chat.ru
Kitter Georgiy wrote: > (1)Let's allow to __connect into several DBMS__ at once, > last of them becomes DBMS 'by default'. ... > It's necessary to not signalize about break of foreign key, > if remote database will not connected in next time. But that's the vital bit. Without being able to extend relational integrity checks across database boundaries you're not really getting something that's general-use. Without that it's not really worth extending syntax - dblink() offers a solution to the limited cases available. If you manage to come up with an efficient multi-master replication system though, please let us know :-) -- Richard Huxton Archonet Ltd
Good day, Richard. DT> As result, __new type of system information__ appears: As result, new type of system information appears - field, refering to other field by foreign key, indeed contain two values: (1) usual value, assinged into field by user select FieldFK, ... insert into ... values (FieldFK, ...) (2) 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: (1) under login of user, which start procedure create procedure ... (2) under login of author of procedure create sticky procedure ... Each database stores all nicknames, known for its users, in system table 'pg_nicknames' for execution of stored procedures and timers (some of nicknames can name database, in which this enumeration of nicknames is). IdUser al an username password IdDb nickname datatime 101 database.com Smith pwds 31 db1 101 123.123.123.123 33 db2 105 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'=nullor '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 thisrecord 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 'pg_nicknames' (it is used for replication of tables 'pg_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 'pg_nicknames' (next command 'default' will use table 'pg_nicknames' of new database, i.e. of 'db2'): connect db2; Command 'connect' affects to table 'pg_nicknames' of database, in which user is: it (1) updates field 'an' by value, got from DNS-server (field 'an' itself exists for event of breakage of DNS-server) (2) adds new records (in which field 'password'=null), when user executes it into external database, which yet not registered in 'pg_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 http://sql40.chat.ru/site/sql40/en/author/ddl_eng.htm#department ) connect ra="data.storage.com" username="Johnson" password="pwdj"nickname="db4"; update pg_nicknames set password=pwdj where ra="data.storage.com" username="Johnson" nickname="db4"; IdUser al an username password IdDb nickname datatime 101 data.storage.com 234.234.234.234 Johnson pwdj 38 db4 Summary of all ideas is in http://sql40.chat.ru/site/sql40/en/author/mc_eng.htm Dmitry Turin SQL4 (4.3.0) http://sql40.chat.ru HTML6 (6.4.0) http://html60.chat.ru Unicode2 (2.0.1) http://unicode2.chat.ru Computer2 (2.0.3) http://computer20.chat.ru
DT> As result, __new type of system information__ appears: DT> field, refering to other field by foreign key, DT> has additional bit except own value - DT> bit specifies, whether record, to which it refers, DT> is in the same or in other database. DT> It's necessary to not signalize about break of foreign key, DT> if remote database will not connected in next time. As result, new type of system information appears - field, refering to other field by foreign key, indeed contain two values: (1) usual value, assinged into field by user select FieldFK, ... insert into ... values (FieldFK, ...) (2) 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: (1) under login of user, which start procedure create procedure ... (2) under login of author of procedure create sticky procedure ... 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). IdUser al an username password IdDb nickname datatime 101 database.com Smith pwds 31 db1 101 123.123.123.123 33 db2 105 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'=nullor '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 thisrecord 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 db2; Command 'connect' affects to table 'sys-nicknames' of database, in which user is: it (1) updates field 'an' by value, got from DNS-server (field 'an' itself exists for event of breakage of DNS-server) (2) 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 http://sql40.chat.ru/site/sql40/en/author/ddl_eng.htm#department ) 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"; IdUser al an username password IdDb nickname datatime 101 data.storage.com 234.234.234.234 Johnson pwdj 38 db4 Summary of all ideas is in http://sql40.chat.ru/site/sql40/en/author/mc_eng.htm Dmitry Turin HTML6 (6.4.0) http://html60.chat.ru SQL4 (4.2.0) http://sql40.chat.ru Unicode2 (2.0.1) http://unicode2.chat.ru Computer2 (2.0.3) http://computer20.chat.ru