UML Class Diagrams from PostgreSQL. Part One
Content
Procedure umlpr_TablesScipt_FromArray — Unloading from a list of tables
Procedure umlpr_TablesScipt_ByWhereOption —Unloading by condition
Function umlfn_Table_fromArray — PlantUML script from list of tables
Function umlfn_Table_AllGroupScript — PlantUML script by condition
Function umlfn_Table_Script — PlantUML script of table with attributes
Function umlfn_Attribute_String — PlantUML table attribute script
Function umlfn_Table_LinksFromArray — PlantUML script of links
Assignment of functions
The functions described in the previous articles returned information about database objects (relations (tables), table attributes, constraints, indexes, etc.) in the form of lists. These lists can be included in a document describing the project database in the form of tables. But the list form is not very visual for documenting the relationships between tables. Another matter is ER diagrams or UML class diagrams, such as the one shown in Fig. 1. Usually, such diagrams are created using special graphical tools, such as, for example, ERwin Data Modeler[*1] Microsoft Visio[*2] the disadvantages of which include the lack or complexity of connection with an existing database. An alternative to graphic editors has become the technology of creating diagrams from their text descriptions, which are created in the PlantUML language. This technology involves creating UML in two steps – first, a description or PlantUML script is created, which is then loaded into the environment, which converts it into a diagram.
The functions described in this document are intended for creating PlantUML scripts from PostgreSQL system catalog data. The basic format of the created PlantUML scripts corresponds to the requirements of the plantuml plugin for the TRAC project management system[*3] . The additional functions described can create scripts of the classic form without the additional wrapper required for TRAC.
General structure of functions
The main function that creates PlantUML scripts is umlfn_Table_fromArray. It converts an array of table names (relations) into a PlantUML script for subsequent display in the form of a UML class diagram.
Additionally, PlantUML scripts can be created using the umlfn_Table_AllGroupScript function, which, based on a given condition, generates an array of tables from the pg_class catalog and calls the main umlfn_Table_fromArray function.
Fig. 2 shows the diagram of the interaction of functions in the process of creating PlantUML scripts. In it, the main functions have the prefix umlfn_ or umltf. The remaining functions are auxiliary, designed to access and process data from system catalogs.
Table 1 contains a list and brief description of the main functions.
Table 2 contains a list and brief purpose of only those helper functions that are not described in the following “Functions for Documenting PostgreSQL Databases” articles:
Basically, these functions were created for the convenience of converting system catalog data to the PlantUML script format.
The generated PlantUML scripts can be exported to a text file.
To unload PlantUML scripts, stored procedures are used that call the main or additional functions, and their result is saved in a text file using the COPY TO operator. The interaction diagram of the main functions and procedures is shown in Fig. 3.
Table 3 contains a list and brief purpose of the main procedures.
Procedure umlpr_TablesScipt_FromArray
Procedure umlpr_TablesScipt_FromArray unloads into a text file a PlantUML script created from an array with table names by the function umlfn_Table_fromArray.
The procedure takes an array of table names as its main parameters (a_TableNameArray), the path to the folder to which the PlantUML script should be downloaded (a_OutputFolder) and the name of the scheme within which they are located tables (a_SchemaName). Additional parameters are: “Indicator of the need to include an attribute comment” (a_NeedAttrDesc) and “Format of the returned PlantUML script: 'Trac' or 'PlantTest'» (a_Mode).
The name of the file being downloaded has one of the following two types:
plantuml_YYYY-MM-DD;
plantuml_YYYY-MM-DD_classic
Files with the second type of name are created if the a_Mode parameter has the value 'PlantTest'. The template 'YYYY-MM-DD' in the name is replaced by the file creation date, where 'YYYY' is the year, 'MM' is the ordinal number of the month in the year, 'DD' is the day in the month.
Hidden text
COPY (SELECT unnest FROM unnest(ARRAY[umlfn_Table_FromArray(a_SchemaName::VARCHAR, a_TableNameArray, FALSE,a_Mode)]))
TO ''' || a_OutputFile||'plantuml_'||TO_CHAR(current_date, 'YYYY-MM-DD')||
CASE a_Mode WHEN 'PlantTest' THEN '_classic' ELSE '' END||'.txt''';
The figure containing the COPY operator unloading a PlantUML script into a text file is a basic diagram of how the result of executing the umlfn_Table_fromArray function. However, due to the peculiarities of the COPY operator, changes have to be made to the PlantUML script format. The fact is that umlfn_Table_fromArray returns a text variable consisting of substrings not enclosed in quotation marks, while for normal execution of the COPY operator, on the contrary, each substring must be enclosed in quotation marks. In this case, after converting the original format into an array of strings, the strings are unloaded into the output file without quotation marks.
Hidden text
v_PlantUML_Script:= umlfn_Table_FromArray(a_SchemaName::VARCHAR, a_TableNameArray, FALSE,a_Mode);
v_PlantUML_Script:= ''''||REPLACE(v_PlantUML_Script,CHR(13)||CHR(10),''', ''')||'''';
COPY (SELECT unnest FROM unnest(ARRAY[v_PlantUML_Script]))
TO ''' || a_OutputFile||'plantuml_'||TO_CHAR(current_date, 'YYYY-MM-DD')||
CASE a_Mode WHEN 'PlantTest' THEN '_classic' ELSE '' END||'.txt''';
The new version of the figure takes into account the need to pre-enclose all substrings of the PlantUML script in quotation marks. This transformation is performed in the second line.
Fig. 4 shows a variant of a folder with files unloaded by the umlpr_TablesScipt_FromArray procedure. The contents of the files are described in detail in the section “Function umlfn_Table_fromArray…”
Procedure umlpr_TablesScipt_ByWhereOption
Procedure umlpr_TablesScipt_ByWhereOption unloads a PlantUML script into a text file, created from an array with the names of tables found according to the condition specified by the parameter. The PlantUML script itself is created by the function umlfn_Table_fromArray.
As main parameters it accepts the table search condition (a_WHEREoption), which does not contain the WHERE keyword, the path to the folder to which the PlantUML script should be downloaded (a_OutputFolder) and the name of the scheme within which they are located tables (a_SchemaName). Additional parameters are: “Indicator of the need to include an attribute comment” (a_NeedAttrDesc) and “Format of the returned PlantUML script: 'Trac' or 'PlantTest'» (a_Mode).
The procedure execution is reduced to obtaining a list (array) of functions according to a given condition using the function admfn_Table_TableNameArrayfollowed by a procedure call umlfn_Table_fromArray.
Hidden text
SELECT umlpr_TablesScipt_FromArray (admfn_Table_TableNameArray(a_SchemaName,a_WHEREoption), a_OutputFolder, a_SchemaName,v_Mode);When describing the table selection condition, you should take into account the names of the aliases of the catalogs that are used in the function. Thus, for the pg_class catalog, the alias “tbl” is used, and for the pg_namespace catalog, “nsp”.
Function umlfn_Table_fromArray
The umlfn_Table_fromArray function returns a PlantUML script created from an array of table names.
The function takes as its main parameters an array of table names (a_TableNameArray) and the name of the scheme within which they are created (a_SchemaName). Additional parameters are: “Indicator of the need to include an attribute comment” (a_NeedAttrDesc) and “Format of the returned PlantUML script: 'Trac' or 'PlantTest'» (a_Mode).
Hidden text
SELECT INTO v_TableGroupScript, v_TableGroupLegendScript
STRING_AGG (umlfn_Table_Script(a_SchemaName,tbl.relname,FALSE,cnfn_Table_LowerName(a_TableNameArray),'') AS v_TableGroupScript
'''legend center'''||CHR(13)||CHR(10)||'''<b><i><u>ТАБЛИЦЫ</u></i></b>'''|| CHR(13)||CHR(10)||''' '''|| CHR(13)||CHR(10)||
STRING_AGG (quote_literal('<b>'||UPPER(ftbl.relname)||'</b>'||'- '||COALESCE(dsc.description,''))|| CHR(13)||CHR(10),'')||
'''endlegend'''|| CHR(13)||CHR(10) AS v_TableGroupLegendScript
FROM pg_class tbl
INNER JOIN pg_namespace nsp ON tbl.relnamespace = nsp.oid
LEFT OUTER JOIN pg_Description dsc ON tbl.oid=dsc.objoid
AND dsc.objsubid=0
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) AND tbl.relkind='r'
AND tbl.relname !~* E'(raster_|spatial_|geometry_|geography_)'
AND ARRAY[LOWER(tbl.relname)::TEXT] <@ cnfn_Array_LowerCase(a_TableNameArray)::TEXT[]
The main data function extracts from the catalog record pg_classwhich contains, in addition to records about tables, also records about sequences, views, materialized views, and composite types. Therefore, the relkind= condition is used to select tables.'r'.
Additionally, the function accesses directory data. pg_namespace And pg_description. The first one contains the names of the database schemas, and the second one contains comments on all the database objects.
It is important to pay attention to the condition objsubid=0 here. It defines a comment for the table, since the value of the field objoid is the same for both the table and its attributes. The comment for a table attribute is contained in the entry in which objsubid matches the number of this attribute.
In addition, the selection condition excludes PostgreSQL extension tables (EXTENSION) postgisand all elements of the original array of function names are converted to lower case using the helper function cnfn_Array_LowerCase.
Using the SELECT operator, two parts of the PlantUML script are grouped from the source tables: v_TableLinksScript—a list of tables with their attributes, v_TableGroupLegendScript—the legend of the previous list, i.e. the names of the tables with comments on them.
Hidden text
v_TableLinksScript:=umlfn_Table_LinksFromArray(a_SchemaName, a_TableNameArray);
v_TableGroupScript:=v_TableGroupScript ||COALESCE(v_TableLinksScript,'') ||v_TableGroupLegendScript||v_TableGroupScriptSuffix;
To describe the relationships between the given tables, the function is called umlfn_Table_LinksFromArraywhich analyzes the foreign keys of the given tables, establishes the detected relationships, and also indicates the nature of these relationships: “*-” – many to one, “-*“— one to many, “1-1» — one to one. Table 4 contains a description of the symbols for linking tables from PlantUML language manuals.
The last operator connects all parts of the PlantUML script in the required sequence. At the same time, a sign identifying the PlantUML script is added to the beginning of the line, and a PlantUML script sign is added to the end of the resulting line.
Table 5 allows us to present the result of executing the umlfn_Table_fromArray function, as well as the composition of its parts.
Hidden text
Table 5 shows a PlantUML script as a set of substrings arranged from top to bottom. At the same time, in the body of stored functions and procedures, these substrings form the value of one text (TEXT) variable, in which they are separated by line break characters (CHR(13) — “Carriage Return” and CHR(10) — “Line Feed”). Sometimes, the text of a PlantUML script must be converted into an array of substrings. This explains why each individual substring is enclosed in single quotes.
v_TableGroupScript:=ARRAY_TO_STRING(cnfn_Array_QuotesWrapperWithout(STRING_TO_ARRAY(v_TableGroupScript, CHR(13)||CHR(10))), CHR(13)||CHR(10));
But at the exit umlfn_Table_fromArray Single quotes are not needed. Therefore, the text is temporarily converted to an array, with the leading and trailing quotes removed from each element. The array is then converted back to a string, placed in a test variable.
Usually the function returns a PlantUML script in the format of the plantuml plugin for the TRAC project management system, but if the additional parameter a_Mode does it matter 'PlantTest'“, then the script wrapper is limited to “@startuml…@enduml“. Where @startuml — script start operator, @enduml—endings.
The source code of the function can be viewed here.
Function umlfn_Table_AllGroupScript
Function umlfn_Table_AllGroupScriptt returns a PlantUML script created from tables found according to the condition specified by the parameter.
The function takes the table search condition as its main parameters (a_WHEREoption), which does not contain the WHERE keyword, and the name of the schema within which the search will be conducted (a_SchemaName). Additional parameters are: “Indicator of the need to include an attribute comment” (a_NeedAttrDesc) and “Format of the returned PlantUML script:'Trac' or 'PlantTest'» (a_Mode).
In the body of the function, the admfn_Table_TableNameArray function is called to convert the selection condition into an array of table names, and this array is then passed as a parameter to the umlfn_Table_fromArray function, which directly creates the PlantUML script.
Hidden text
v_TableGroupScript:=umlfn_Table_FromArray(a_SchemaName, admfn_Table_TableNameArray(a_SchemaName,a_WHEREoption),a_NeedAttrDesc,v_Mode);When describing the condition for selecting tables, you should take into account the names of the aliases of the catalogs that are used in the function. So for the catalog pg_class an alias is used «tbl»and for the catalog pg_namespace — “nsp”.
Table 6 shows an example of the result returned by the function with a valid value for the a_WHEREoption parameter, in a_Mode=”PlantTest” mode.
Table 6. Result of executing the function umlfn_Table_AllGroupScript('public','tbl.relname ~* E''^Street''', FALSE, 'PlantTest')
The source code of the function can be viewed here.
Function umlfn_Table_Script
The umlfn_Table_fromArray function returns a part of the PlantUML script by the name of one table with its attributes.
The function takes the table name as its main parameters (a_TableName) and the name of the scheme within which they are created (a_SchemaName). Additional parameters are: “Indicator of the need to include an attribute comment” (a_NeedAttrDesc) and “Array of tables allowed for searching foreign keys of tables” (a_AllowedTableName).
Hidden text
SELECT '''object '|| a_SchemaName ||'.'||a_TableName||'{'''|| CHR(13)||CHR(10)||
STRING_AGG (umlfn_Attribute_String(oattr.oid, oattr.attnum,a_NeedAttrDesc,a_AllowedTableName),'')||
'''}'''|| CHR(13)||CHR(10)
FROM (SELECT tbl.oid, attr.attnum
FROM pg_attribute attr
WHERE attr.attrelid=( a_SchemaName ||'.'|| a_TableName)::regclass AND attr.attnum>0
ORDER BY tbl.relname,attr.attnum) oattr;
The main data function extracts from the catalog record pg_attributewhich contains records with data about the attributes of tables, views, materialized views, composite types, and even functions.
Explicit access to directory data pg_namespace And pg_class missing due to use of pseudonym Regclasswhich converts the qualified name of a table into its system ID (OID).
It is important to pay attention to the condition attnum>0 here. It determines the presence of the attribute type in the catalog pg_type data types.
Using the SELECT operator, a part of the PlantUML script is grouped from the source attributes: v_TableScript — a table with a list of its attributes. The description of each attribute is performed by the function umlfn_Attribute_String.
Table 7 allows us to present the result of executing the umlfn_Table_fromArray function, as well as the composition of its parts.
The source code of the function can be viewed here.
Function umlfn_Attribute_String
Function umlfn_Attribute_String returns a part of PlantUML script for one attribute by its name.
The function takes a system identifier (OID) tables (a_TableOID) and the ordinal number of the attribute in this table (a_AttributeNo). Additional parameters are: “Indicator of the need to include an attribute comment” (a_NeedAttrDesc) and “Array of tables allowed for searching foreign keys of tables” (a_AllowedTableName). In this case, the value of the last parameter (a_AllowedTableName) is not used. As they say, it was created with the expectation of future improvement of the function.
The main data of the function is obtained as a result of executing another function. admtf_Attribute_Featureswhich is passed the values of the parameters a_TableOID and a_AttributeNo. A detailed description of this function can be found in document “Functions for Documenting Databases”.
Hidden text
SELECT ass.AttString||STRING_AGG(ass.FKString,'')
FROM (SELECT TRIM(af.rsa_AttributeName)||':'||
' '||CASE WHEN af.rsa_Description IS NULL OR NOT a_NeedAttrDesc THEN '' ELSE '(<i>'||af.rsa_Description||'</i>)' END||
' '||af.rsa_TypeName||
' '||CASE WHEN af.rsa_isNotNULL THEN 'NOT' ELSE '' END || 'NULL' || ''||
CASE WHEN af.rsa_isAttributePK THEN '(PK'||TRIM(COALESCE(TO_CHAR(af.rsa_ColumnPKNo,'999'),''))||')'
ELSE '' END AS AttString,
'(FK'||TRIM(COALESCE(TO_CHAR(af.rsa_ColumnFKNo[af.fkInx],'999'),''))||' '||COALESCE(af.rsa_FKTableName[af.fkInx],'')||
'('||COALESCE(af.rsa_FKTableColumnName[af.fkInx],'')||')'||')' FKString
FROM (SELECT iaf.rsa_AttributeName,iaf.rsa_Description,iaf.rsa_TypeName,iaf.rsa_isNotNULL,iaf.rsa_isAttributePK,iaf.rsa_ColumnPKNo,
iaf.rsa_isAttributeFK,iaf.rsa_ColumnFKNo,iaf.rsa_FKTableName,iaf.rsa_FKTableColumnName,
GENERATE_SUBSCRIPTSs(iaf.rsa_ColumnFKNo, 1) fkInx
FROM admtf_Attribute_Features(a_TableOID , a_AttributeNo) iaf) af
ORDER BY fkInx) ass
GROUP BY ass AttString;
Some of the cumbersomeness of the given SELECT statement is caused by the fact that the admtf_Attribute_Features function, as Table 8 shows, returns the characteristics of foreign keys as arrays. This necessitated the use of the GENERATE_SUBSCRIPTS function and subsequent grouping of the foreign key data. Although, perhaps, such grouping should be abandoned in the future.
In addition, when forming a string with an attribute description, the value of the a_NeedAttrDesc parameter is taken into account, which indicates whether to add a comment to the attribute name or not. The remaining attribute data is simply connected in the order and format specified in the PlantUML language requirements.
Table 9 contains an example of executing the umlfn_Attribute_String function, the peculiarity of which is that the parameter a_NeedAttrDesc='TRUE'and the parameter a_TableOID specified using an alias Regclasswhich is more reliable than using an explicit value OID tables, since the latter can be changed during the operation of the database.
The source code of the function can be viewed here.
Function umlfn_Table_LinksFromArray
Function umlfn_Table_LinksFromArray returns a part of the PlantUML script with a list of links created from an array with table names. The function takes as its main parameters an array of table names (a_TableNameArray) and the name of the scheme within which they are created (a_SchemaName).
The main data function extracts from the catalog records pg_constraint. Catalog pg_constraint given that contype='f' contains records with characteristics of foreign keys of tables that link records from the catalog pg_classbelonging to the type relkind='r'. To convert the name of the foreign key to the characteristics corresponding to the table relationship ('N:1','1:1') the function is used admfn_Table_LinkSign.
Additionally, the function accesses the data in the pg_namespace directory, which contains the names of the database schemas.
Hidden text
SELECT STRING_AGG(''''||LOWER(nsp.nspname)||'.'||UPPER(tbl.relname)||' '||
CASE LEFT(ls.*,1) WHEN 'N' THEN '*' ELSE '' END || '--' || CASE RIGHT(ls.*,1) WHEN 'N' THEN '*' ELSE '' END||
' '||LOWER(nsp.nspname)||'.'||UPPER(Ftbl.relname)||''''||CHR(13)||CHR(10),'')
FROM pg_class tbl
INNER JOIN pg_namespace nsp ON tbl.relnamespace = nsp.oid
INNER JOIN pg_constraint lcon ON lcon.conrelid=tbl.oid
INNER JOIN pg_class ftbl ON lcon.confrelid=ftbl.oid
INNER JOIN pg_namespace fnsp ON ftbl.relnamespace=fnsp.oid,
LATERAL admfn_Table_LinkSign(nsp.nspname,tbl.relname:: VARCHAR(256),lcon.conname:: VARCHAR(256)) ls
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) AND tbl.relkind='r'
LOWER(fnsp.nspname)=LOWER(a_SchemaName) AND ftbl.relkind='r'
AND lcon.contype="f"
AND ARRAY[tbl.relname::VARCHAR(256)] <@ admfn_Table_LowerName(a_TableNameArray)
AND ARRAY[ftbl.relname::VARCHAR(256)] <@ admfn_Table_LowerName(a_TableNameArray);
As can be seen from the above statement, the link characteristics returned by the admfn_Table_LinkSign function are transformed as follows:
The tables being linked are simply joined in the order specified in the requirements for PlantUML language.
Table 10 contains an example of the function execution. umlfn_Table_LinksFromArray.
The source code of the function can be viewed here.
APPLICATION
Creating the umlpr_TablesScipt_FromArray procedure
Comments on the source code of the procedure can be found here.
Hidden text
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS umlpr_TablesScipt_FromArray(a_TableNameArray VARCHAR(256)[],a_OutputFolder TEXT,a_Schema VARCHAR(256),
a_Mode VARCHAR(20));
/****************************************************************************************************************/
/* Создание файла с описанием таблиц из исходного массива в формате плагина (plantuml) */
/****************************************************************************************************************/
CREATE OR REPLACE FUNCTION umlpr_TablesScipt_FromArray
(a_TableNameArray VARCHAR(256)[], /* Исходный массив таблиц */
a_OutputFolder TEXT, /* Папка создаваемого файла*/
a_Schema VARCHAR(256) DEFAULT 'public',/*Схема исходных таблиц*/
a_Mode VARCHAR(20) default 'Trac' /* Формат возвращаемой строки:*/
/* 'Trac' -для средства управления проектам; */
/*'PlantTest' - для тестирования на сайте*/
/* https://www.planttext.com/ */
)
RETURNS INTEGER
AS
$BODY$
DECLARE
c_Schema CONSTANT name:='public';
c_NeedAttrDesc CONSTANT BOOLEAN:=FALSE; /* Признак необходимости включения комментария аттрибута */
c_TracMode CONSTANT VARCHAR(50):='Trac'; /*для средства управления проектам*/
c_PlantTestMode CONSTANT VARCHAR(50):='PlantTest'; /*для тестирования на сайте "https://www.planttext.com/" */
v_Schema VARCHAR(256); /*Схема исходных таблиц*/
v_Mode VARCHAR(20); /* Формат возвращаемой строки: 'Trac' -для средства управления проектам;*/
/* 'PlantTest' - для тестирования на сайте https://www.planttext.com/ */
v_Return_Error Integer := 0; /* Код возврата*/
v_OutputFile TEXT; /* Путь и название создаваемого файла*/
v_COPY_Statement TEXT; /* Оператор копирования скрипта в файл*/
v_PlantUML_Script TEXT; /* Сгенерированный скрипт*/
v_TableNameArray VARCHAR(256)[];/* Исходный массив таблиц */
BEGIN
v_Mode:=COALESCE(a_Mode,c_TracMode);
v_Schema:=COALESCE(a_Schema,c_Schema);
v_TableNameArray:=a_TableNameArray;
v_OutputFile:=TRIM(a_OutputFolder)||'plantuml_'||current_date||CASE v_Mode WHEN c_PlantTestMode THEN '_classic' ELSE '' END||'.txt';
v_PlantUML_Script:= umlfn_Table_FromArray(v_Schema::VARCHAR,v_TableNameArray,c_NeedAttrDesc,v_Mode);
v_PlantUML_Script:=''''||REPLACE(v_PlantUML_Script,CHR(13)||CHR(10),''', ''')||'''';
v_COPY_Statement="COPY (SELECT unnest FROM unnest(ARRAY["|| v_PlantUML_Script || '])) TO ''' || v_OutputFile||'''';
BEGIN
EXECUTE v_COPY_Statement;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'ОШИБКА ПУТИ к папке создаваемого файла. Проверьте правильность значений параметров.
ПАРАМЕТРЫ создания: ********************************************************************
Путь - "%"
Массив таблиц - "%"
Схема - "%"
***********************************************************',
v_OutputFile,v_TableNameArray,v_Schema;
RETURN -2;
END;
RETURN v_Return_Error;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION umlpr_TablesScipt_FromArray(a_TableNameArray VARCHAR(256)[],a_OutputFolder TEXT,a_Schema VARCHAR(256),a_Mode VARCHAR(20)) IS
'Создание файла с описанием таблиц из исходного массива в формате плагина (plantuml)';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT umlpr_TablesScipt_FromArray('{Street,StreetType,StreetTypeSynonym,STREETSYNONYM}'::VARCHAR(256)[],
'D:\plantuml\Scripts\','public','PlantTest' ); SELECT umlpr_TablesScipt_FromArray('{Street,StreetType,StreetTypeSynonym,STREETSYNONYM}'::VARCHAR(256)[],
'D:\plantuml\Scripts\','public');
Creating the umlpr_TablesScipt_ByWhereOption procedure
Comments on the source code of the procedure can be found here.
Hidden text
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS umlpr_TablesScipt_ByWhereOption(a_WHEREoption VARCHAR(1000),a_OutputFolder TEXT,a_Schema VARCHAR(256),
a_Mode VARCHAR(20));
/********************************************************************************************************************/
/* Создание файла с описанием таблиц по условию выбора таблиц из системного каталога в формате плагина (plantuml) */
/********************************************************************************************************************/
CREATE OR REPLACE FUNCTION umlpr_TablesScipt_ByWhereOption
(a_WHEREoption VARCHAR(1000), /* условие запроса на выбор таблиц из системного каталога */
a_OutputFolder TEXT, /* Папка создаваемого файла*/
a_Schema VARCHAR(256) DEFAULT 'public',/*Схема исходных таблиц*/
a_Mode VARCHAR(20) default 'Trac' /* Формат возвращаемой строки: */
/* 'Trac' -для средства управления проектам;*/
/*'PlantTest' - для тестирования на сайте */
/*https://www.planttext.com/ */
)
RETURNS INTEGER
AS
$BODY$
DECLARE
v_TableNameArray VARCHAR(256)[]; /* Массив выбранных таблиц */
v_Return_Error Integer := 0; /* Код возврата */
BEGIN
v_TableNameArray:=admfn_Table_TableNameArray(a_Schema,a_WHEREoption);
v_Return_Error:=umlpr_TablesScipt_FromArray(v_TableNameArray,a_OutputFolder,a_Schema,a_Mode);
RETURN v_Return_Error;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION umlpr_TablesScipt_ByWhereOption(a_WHEREoption VARCHAR(1000),a_OutputFolder TEXT,a_Schema VARCHAR(256),a_Mode VARCHAR(20)) IS 'Создание файла с описанием таблиц по условию выбора таблиц из системного каталога в формате плагина (plantuml) ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
/*SELECT umlpr_TablesScipt_ByWhereOption('tbl.relname LIKE ''id_districts'' OR tbl.relname LIKE ''id_nps'''::VARCHAR(1000),
'D:\Projects\Enisey GIS\DB\PLpgSQL\Common\DBAdministrate\plantuml\NONScripts\' );
SELECT umlpr_TablesScipt_ByWhereOption('tbl.relname LIKE ''id_districts'' OR tbl.relname LIKE ''id_nps'''::VARCHAR(1000),
'D:\Projects\Enisey GIS\DB\PLpgSQL\Common\DBAdministrate\plantuml\Scripts\' ,'public','Trac');
SELECT umlpr_TablesScipt_ByWhereOption('tbl.relname LIKE ''id_districts'' OR tbl.relname LIKE ''id_nps'''::VARCHAR(1000),
'D:\Projects\Enisey GIS\DB\PLpgSQL\Common\DBAdministrate\plantuml\Scripts\','public','PlantTest');
*/
SELECT umlpr_TablesScipt_ByWhereOption('tbl.relname ~* E''^street''',
'D:\Projects\Enisey GIS\DB\PLpgSQL\Common\DBAdministrate\plantuml\Scripts\','public','PlantTest');
SELECT umlpr_TablesScipt_ByWhereOption('tbl.relname ~* E''^street''',
'D:\Projects\Enisey GIS\DB\PLpgSQL\Common\DBAdministrate\plantuml\Scripts\','public','PlantTest');
Creating the umlfn_Table_fromArray function
Comments on the source code of the function can be found here.
Hidden text
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS umlfn_Table_FromArray (a_SchemaName VARCHAR(100),a_TableNameArray VARCHAR(256)[],a_NeedAttrDesc BOOLEAN,a_Mode VARCHAR(20));
/********************************************************************************************************/
/* Функция возвращает строку с описанием таблиц из исходного массива в формате плагина (plantuml) */
/* к Trac — средству управления проектами и отсслеживания ошибок в программном обеспечении. */
/*------------------------------------------------------------------------------------------------------*/
/* ПРИМЕР: umlfn_Table_FromArray('public','{kmed__adres1,kmed_adres,kmed_adreskladr,kmed_adreskladr_temp,kmed_peoplebyidadreshome,kmed_radres,kmed_sadres,kmed_tadres}'); */
/*------------------------------------------------------------------------------------------------------*/
/* {{{ */
/* #!plantuml */
/* @startuml */
/* object public.KMED__ADRES1{ */
/* id_adres1 : (<i>Уникальный код записи в оглавлении </i>) integer NOT NULL (PK1) */
/* idadres : (<i>Уникальный код записи адресного элемента (Код адреса) в kmed_ADRES</i>) integer NULL */
/* idparent : (<i (...) */
/* */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION umlfn_Table_FromArray
(a_SchemaName VARCHAR(100) default 'public', /* название схемы базы данных */
a_TableNameArray VARCHAR(256)[] default '{}', /* Исходный массив таблиц */
a_NeedAttrDesc BOOLEAN default false, /* Признак необходимости включения комментария аттрибута */
a_Mode VARCHAR(20) default 'Trac' /* Формат возвращаемого PlantUML-скрипта: 'Trac' -для средства*/
/* управления проектом; 'PlantTest' - для тестирования на сайте */
/* https://www.planttext.com/ */
)
RETURNS TEXT AS
$BODY$
DECLARE
c_SchemaName CONSTANT VARCHAR(256):='public'; /* название схемы базы данных */
c_TableKind CONSTANT CHAR:='r';
c_ForeignKeyKind CONSTANT CHAR:='f';
c_TracMode CONSTANT VARCHAR(50):='Trac'; /*для средства управления проектам*/
c_PlantTestMode CONSTANT VARCHAR(50):='PlantTest'; /*для тестирования на сайте "https://www.planttext.com/" */
c_TracTableGroupScriptPrefix CONSTANT VARCHAR(50):='''{{{'''||CHR(13)||CHR(10)||'''#!plantuml'''||CHR(13)||CHR(10)||'''@startuml'''||CHR(13)||CHR(10);
/* Начало скрипта описания группы таблиц в режиме 'Trac'*/
c_TracTableGroupScriptSuffix CONSTANT VARCHAR(50):='''@enduml'''||CHR(13)||CHR(10)||'''}}}'''||CHR(13)||CHR(10)||''' '''||CHR(13)||CHR(10)||'''----'''||CHR(13)||CHR(10);
/* Конец скрипта описания группы таблиц в режиме 'Trac'*/
c_PlantTestTableGroupScriptPrefix CONSTANT VARCHAR(50):='''@startuml'''||CHR(13)||CHR(10);
/* Начало скрипта описания группы таблиц в режиме 'PlantTest'*/
c_PlantTestTableGroupScriptSuffix CONSTANT VARCHAR(50):='''@enduml''';
/* Конец скрипта описания группы таблиц в режиме 'PlantTest'*/
c_TableGroupLegendPrefix CONSTANT VARCHAR(100):='''legend center'''||CHR(13)||CHR(10)||'''<b><i><u>ТАБЛИЦЫ</u></i></b>'''||CHR(13)||CHR(10)||''' '''||CHR(13)||CHR(10);
/* Начало раздела комментарии к таблицам группы */
c_TableGroupLegendSuffix CONSTANT VARCHAR(100):='''endlegend'''||CHR(13)||CHR(10);
/* Конец раздела комментарии к таблицам группы */
c_TableNameLegendPrefix CONSTANT VARCHAR(10):='<b>'; /* Начало наименования таблицы в комментариях*/
c_TableNameLegendSuffix CONSTANT VARCHAR(10):='</b>'; /* Окончание наименования таблицы в комментариях*/
c_TableDescriptionLegendPrefix CONSTANT VARCHAR(10):='- '; /* Начало описания таблицы в комментариях*/
c_TableDescriptionLegendSuffix CONSTANT VARCHAR(10):=CHR(13)||CHR(10); /* Окончание описания таблицы в комментариях*/
c_TableNameDelimiter CONSTANT VARCHAR(5):='.'; /* Символ-разделитель имени таблицы */
c_Blank CONSTANT VARCHAR(5):=' '; /* Символ пробела */
v_Mode VARCHAR(20); /* Формат возвращаемой строки: 'Trac' -для средства управления*/
/* проектом; 'PlantTest' - для тестирования на сайте */
/* https://www.planttext.com/ */
v_SchemaName VARCHAR(256); /* название схемы базы данных */
v_TableGroupScriptPrefix VARCHAR(50); /* Начало скрипта описания группы таблиц */
v_TableGroupScriptSuffix VARCHAR(50); /* Конец скрипта описания группы таблиц*/
v_TableGroupScript TEXT; /* Формируемый скрипт*/
v_TableLinksScript TEXT; /* Формируемый раздел связей таблиц */
v_TableGroupLegendScript TEXT; /* Формируемый раздел комментириев к таблицам группы*/
v_TableCount INTEGER; /* Счетчик таблиц группы*/
v_TableNameArray TEXT[]; /* Массив таблиц */
v_AloneTableName VARCHAR(256); /* Название одной из таблиц группы*/
v_TableDescription TEXT; /* Описание одной из таблиц группы*/
v_TablesRecord RECORD; /* Запись с данными таблицы */
v_TableOID OID; /* ИД таблицы */
v_ProcessedTableNameArray TEXT[]; /* Массив обработанных таблиц */
v_TableNameArrayRemainder TEXT[]; /* Массив необработанных таблиц */
v_Return_Error INTEGER := 0; /* Код возврата*/
--******************************************************************************************************
BEGIN
v_Mode:=COALESCE(a_Mode,c_TracMode);
v_TableGroupScriptPrefix:=CASE v_Mode WHEN c_PlantTestMode THEN c_PlantTestTableGroupScriptPrefix ELSE c_TracTableGroupScriptPrefix END;
v_TableGroupScriptSuffix:=CASE v_Mode WHEN c_PlantTestMode THEN c_PlantTestTableGroupScriptSuffix ELSE c_TracTableGroupScriptSuffix END;
v_SchemaName:=COALESCE(a_SchemaName,c_SchemaName);
v_TableNameArray:=a_TableNameArray;
v_TableNameArray:=cnfn_Array_LowerCase(a_TableNameArray);
v_TableNameArray:=string_to_array(REPLACE(REPLACE(array_to_string(v_TableNameArray,','),'''',''),'"',''),',');
v_TableGroupScript:='';
v_TableGroupLegendScript:='';
IF COALESCE(v_TableNameArray,'{}')<>'{}' THEN
v_TableCount:=0;
v_TableGroupScript:=v_TableGroupScriptPrefix;
v_TableGroupLegendScript:=c_TableGroupLegendPrefix;
FOR v_TablesRecord IN SELECT ftbl.oid AS TableOID,ftbl.relname:: VARCHAR(256)
FROM pg_class ftbl
INNER JOIN pg_namespace fnsp ON ftbl.relnamespace = fnsp.oid
LEFT OUTER JOIN pg_Description fdsc ON ftbl.oid=fdsc.objoid
AND fdsc.objsubid=0
WHERE LOWER(fnsp.nspname)=LOWER(v_SchemaName) AND ftbl.relkind=c_TableKind
AND ftbl.relname !~ '(raster_|spatial_|geometry_|geography_)'
AND ARRAY[LOWER(ftbl.relname)::TEXT] <@ v_TableNameArray
ORDER BY fnsp.nspname,ftbl.relname
LOOP
v_AloneTableName:=LOWER(v_TablesRecord.relname);
v_AloneTableName:=v_TablesRecord.relname;
v_TableCount:=v_TableCount+1;
v_TableGroupScript:=v_TableGroupScript||
umlfn_Table_Script(a_SchemaName,v_AloneTableName,a_NeedAttrDesc,v_TableNameArray);
SELECT INTO v_TableDescription rs_TableDescription FROM admtf_Table_Features (a_SchemaName,v_AloneTableName);
v_TableGroupLegendScript:=v_TableGroupLegendScript||
quote_literal(c_TableNameLegendPrefix||UPPER(v_AloneTableName)||c_TableNameLegendSuffix||
c_TableDescriptionLegendPrefix||COALESCE(v_TableDescription,''))||c_TableDescriptionLegendSuffix;
v_ProcessedTableNameArray:=array_append(v_ProcessedTableNameArray, v_AloneTableName::TEXT);
END LOOP;
v_TableGroupLegendScript:=v_TableGroupLegendScript||c_TableGroupLegendSuffix ;
v_TableLinksScript:=cnfn_Array_LowerCase(a_SchemaName,v_TableNameArray);
v_TableGroupScript:=v_TableGroupScript ||COALESCE(v_TableLinksScript,'') ||v_TableGroupLegendScript||v_TableGroupScriptSuffix; END IF;
RETURN v_TableGroupScript;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION umlfn_Table_FromArray(a_SchemaName VARCHAR(100),a_TableNameArray VARCHAR(256)[],a_NeedAttrDesc BOOLEAN,a_Mode VARCHAR(20)) IS 'Возвращает строку с описанием таблиц массива в формате плагина (plantuml) к Trac';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT umlfn_Table_FromArray('public'::VARCHAR,'{ Street,StreetType, STREETSYNONYM }'::VARCHAR(256)[]);
Creating the umlfn_Table_AllGroupScript function
Comments on the source code of the function can be found here.
Hidden text
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS umlfn_Table_AllGroupScript (a_SchemaName VARCHAR(100),a_WHEREoption VARCHAR(1000),a_NeedAttrDesc BOOLEAN,a_Mode VARCHAR(20));
/********************************************************************************************************/
/* Функция возвращает строку с описанием группы таблиц в формате плагина (plantuml) к Trac — средству */
/* управления проектами и отслеживания ошибок в программном обеспечении. */
/*------------------------------------------------------------------------------------------------------*/
/* ПРИМЕР: umlfn_Table_AllGroupScript('public','tbl.relname ~* E''^Street''',FALSE,'Trac'); */
/*------------------------------------------------------------------------------------------------------*/
/* {{{ */
/* #!plantuml */
/* @startuml */
/* object public.STREET{ */
/* wcrccode : smallint NOT NULL (PK1) */
/* localityid : integer NOT NULL (PK2) */
/* streetid : smallint NOT NULL (PK3) */
/* streettypeacrm : character(8) NULL (FK1 streettype(streettypeacrm)) */
/* streetname : character varying(150) NOT NULL */
/* streettsvector : tsvector NULL */
/* streettslitevector : tsvector NULL */
/* } */
/* object public.STREETSYNONYM{ */
/* wcrccode : smallint NOT NULL (PK1)(FK1 street(wcrccode)) */
/* localityid : integer NOT NULL (PK2)(FK2 street(localityid)) */
/* streetid : smallint NOT NULL (PK3)(FK3 street(streetid)) */
/* synonymid : smallint NOT NULL (PK4) */
/* streettypeacrm : character(8) NULL (FK1 streettype(streettypeacrm)) */
/* streetsynonymname : character varying(200) NULL */
/* } */
/* object public.STREETTYPE{ */
/* streettypeacrm : character(8) NOT NULL (PK1) */
/* streettypename : character varying(30) NULL */
/* } */
/* object public.STREETTYPESYNONYM{ */
/* streettypeacrm : character(8) NOT NULL (PK1)(FK1 streettype(streettypeacrm)) */
/* synonymid : smallint NOT NULL (PK2) */
/* streettypesynonymacrm : character varying(15) NULL */
/* } */
/* public.STREETSYNONYM *-- public.STREET */
/* public.STREETSYNONYM *-- public.STREETTYPE */
/* public.STREET *-- public.STREETTYPE */
/* public.STREETTYPESYNONYM *-- public.STREETTYPE */
/* legend center */
/* <b><i><u>ТАБЛИЦЫ</u></i></b> */
/* */
/* <b>STREET</b>- Список улиц в населенных пунктах */
/* <b>STREETSYNONYM</b>- Список синонимов назвачний улиц в населенных пунктах */
/* <b>STREETTYPE</b>- Справочник типов улиц */
/* <b>STREETTYPESYNONYM</b>- Справочник синонимов типов улиц */
/* endlegend */
/* @enduml */
/* }}} */
/* */
/* ---- */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION umlfn_Table_AllGroupScript
(a_SchemaName VARCHAR(100) default 'public', /* название схемы базы данных */
a_WHEREoption VARCHAR(1000) default NULL, /* условие запроса на выбор таблиц из системного каталога */
a_NeedAttrDesc BOOLEAN default false, /* Признак необходимости включения комментария аттрибута */
a_Mode VARCHAR(20) default 'Trac' /* Формат возвращаемой строки: 'Trac' -для средства управления проектам; 'PlantTest' - для тестирования на сайте https://www.planttext.com/ */
)
RETURNS TEXT AS
$BODY$
DECLARE
c_TracMode CONSTANT VARCHAR(50):='Trac'; /*для средства управления проектам*/
c_PlantTestMode CONSTANT VARCHAR(50):='PlantTest'; /*для тестирования на сайте "https://www.planttext.com/ */
v_Mode VARCHAR(20); /* Формат возвращаемой строки: 'Trac' -для средства управления проектам; */
/* 'PlantTest' - для тестирования на сайте https://www.planttext.com/ */
v_TableNameArray VARCHAR(256)[]; /* Массив выбранных таблиц */
v_TableGroupScript TEXT; /* Формируемый скрипт*/
v_Return_Error Integer := 0; /* Код возврата*/
--******************************************************************************************************
BEGIN
v_Mode:=COALESCE(a_Mode,c_TracMode);
v_TableGroupScript:='';
v_TableNameArray:=admfn_Table_TableNameArray(a_SchemaName,a_WHEREoption);
IF COALESCE(v_TableNameArray,'{}')<>'{}' THEN
v_TableGroupScript:=umlfn_Table_FromArray(a_SchemaName,v_TableNameArray,a_NeedAttrDesc,v_Mode);
END IF;
RETURN v_TableGroupScript;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION umlfn_Table_AllGroupScript(a_SchemaName VARCHAR(100),a_WHEREoption VARCHAR(1000),a_NeedAttrDesc BOOLEAN,a_Mode VARCHAR(20)) IS 'Возвращает строку строку с описанием группы таблиц в формате плагина (plantuml) к Trac';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT umlfn_Table_AllGroupScript('public','tbl.relname ~* E''^Street''');
SELECT umlfn_Table_AllGroupScript('public','tbl.relname ~* E''^Street''',FALSE,'PlantTest');
Creating the umlfn_Table_Script function
Comments on the source code of the function can be found here.
Hidden text
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS umlfn_Table_Script (a_SchemaName name,a_TableName name,a_NeedAttrDesc BOOLEAN,a_AllowedTableName VARCHAR(256)[]);
/********************************************************************************************************/
/* Функция возвращает строку с описанием таблицы в формате плагина (plantuml) к Trac — средству */
/* управления проектами и отслеживания ошибок в программном обеспечении. */
/*------------------------------------------------------------------------------------------------------*/
/* ПРИМЕР: SELECT umlfn_Table_Script('public','Street',TRUE); */
/*------------------------------------------------------------------------------------------------------*/
/* 'object public.STREET{' */
/* 'wcrccode : (<i>Код страны</i>) smallint NOT NULL (PK1)(FK1 locality(wcrccode))' */
/* 'localityid : (<i>ИД населенного пункта</i>) integer NOT NULL (PK2)(FK2 locality(localityid))' */
/* 'streetid : (<i>ИД улицы населенного пункта</i>) smallint NOT NULL (PK3)' */
/* 'streettypeacrm : (<i>Акроним типа улицы</i>) character(8) NULL (FK1 streettype(streettypeacrm))' */
/* 'streetname : (<i>Наименование улицы населенного пункта</i>) character varying(150) NOT NULL' */
/* 'streettsvector : (<i>Расширенные характеристики улицы в населенном пункте, преобразованные в вектор полнотекстового поиска</i>) tsvector NULL ' */
/* 'streettslitevector : (<i>Характеристики улицы в населенном пункте, преобразованные в вектор полнотекстового поиска</i>) tsvector NULL ' */
/* '}' */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION umlfn_Table_Script
(a_SchemaName NAME default 'public', /* название схемы базы данных */
a_TableName NAME default NULL, /* Название таблицы */
a_NeedAttrDesc BOOLEAN default false, /* Признак необходимости включения комментария аттрибута */
a_AllowedTableName VARCHAR(256)[] default '{}' /* Массив допустимых для поиска внешних ключей таблиц */
)
RETURNS TEXT AS
$BODY$
DECLARE
c_TableScriptPrefix CONSTANT VARCHAR(10):='''object '; /* Начало скрипта описания таблицы*/
c_AttributeListPrefix CONSTANT VARCHAR(5):='{'''||CHR(13)||CHR(10); /* Начало списка атрибутов */
c_AttributeListSuffix CONSTANT VARCHAR(5):='''}'''||CHR(13)||CHR(10); /* Конец списка атрибутов */
c_TableScriptSuffix CONSTANT VARCHAR(5):=' '||CHR(13)||CHR(10); /* Конец скрипта описания таблицы*/
c_TableNameDelimiter CONSTANT VARCHAR(5):='.'; /* Символ-разделитель имени таблицы */
c_Blank CONSTANT VARCHAR(5):=' '; /* Символ пробела */
v_AttributeScript TEXT; /* */
v_TableScript TEXT; /* */
v_Schema name;
v_TableName name;
v_TableOID OID;
v_AttributeName name;
v_AttributeNum SMALLINT;
v_AttributeCount INTEGER; /* Счетчик цикла*/
v_Return_Error Integer := 0;
cursor_Attributes CURSOR FOR SELECT tbl.oid, attr.attnum
FROM pg_attribute attr
INNER JOIN pg_class tbl ON tbl.oid = attr.attrelid
INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableName) AND attr.attnum>0
ORDER BY tbl.relname,attr.attnum;
--******************************************************************************************************
BEGIN
v_TableScript:='';
OPEN cursor_Attributes;
v_AttributeCount:=0;
v_TableScript:=c_TableScriptPrefix || LOWER(a_SchemaName) || c_TableNameDelimiter ||
CASE WHEN LEFT(TRIM(a_TableName),1) =UPPER(LEFT(TRIM(a_TableName),1))
AND SUBSTRING(TRIM(a_TableName) FROM 2) =LOWER(SUBSTRING(TRIM(a_TableName) FROM 2))
THEN UPPER(a_TableName)
WHEN a_TableName ~ E'[A-ZА-ЯЁ]' THEN '"'||a_TableName||'"'
ELSE UPPER(a_TableName) END ||c_AttributeListPrefix;
FETCH FIRST FROM cursor_Attributes INTO v_TableOID,v_AttributeNum;
WHILE FOUND
LOOP
v_AttributeCount:=v_AttributeCount+1;
v_TableScript:=v_TableScript||COALESCE(umlfn_Attribute_String(v_TableOID,v_AttributeNum,a_NeedAttrDesc,a_AllowedTableName),'');
FETCH NEXT FROM cursor_Attributes INTO v_TableOID,v_AttributeNum;
END LOOP;
CLOSE cursor_Attributes;
v_TableScript:=v_TableScript ||c_AttributeListSuffix;
RETURN v_TableScript;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION umlfn_Table_Script(a_SchemaName name,a_TableName name,a_NeedAttrDesc BOOLEAN,a_AllowedTableName VARCHAR(256)[]) IS 'Возвращает строку с описанием таблицы в формате плагина (plantuml) к Trac';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT umlfn_Table_Script('public'::VARCHAR,'Street');
Creating the umlfn_Attribute_String function
Comments on the source code of the function can be found here.
Hidden text
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS umlfn_Attribute_String (a_TableOID OID,a_AttributeNo SMALLINT,a_NeedAttrDesc BOOLEAN,a_AllowedTableName VARCHAR(256)[]);
/***************************************************************************************************************/
/* Функция возвращает строку с описанием атрибута таблицы в формате плагина (plantuml) к Trac — средству */
/* управления проектами и отслеживания ошибок в программном обеспечении. */
/*-------------------------------------------------------------------------------------------------------------*/
/* ПРИМЕРЫ: */
/* ИД_Пользователь : double (PK) */
/* ИД_Группа : double (FK1) */
/* ИД_Должность : double (FK2) */
/* Системное_Имя_Пользователя : varchar2(255) (AK) */
/* ФИО : varchar2(255) */
/* Телефоны : varchar2(255) */
/* Почта : varchar2(100) */
/* Адрес : varchar2(255) */
/* Дата_Рождения : date */
/****************************************************************************************************************/
CREATE OR REPLACE FUNCTION umlfn_Attribute_String
(a_TableOID OID, /* ИД таблицы */
a_AttributeNo SMALLINT, /* Порядковый номер атрибута в таблице*/
a_NeedAttrDesc BOOLEAN default false, /* Признак необходимости включения комментария аттрибута */
a_AllowedTableName VARCHAR(256)[] default '{}' /* Массив допустимых для поиска внешних ключей таблиц */
)
RETURNS TEXT AS
$BODY$
DECLARE
c_PrimaryKeyPrefix CONSTANT VARCHAR(5):='(PK'; /* Начало опции об участии аттрибута в первичном ключе*/
c_PrimaryKeySuffix CONSTANT VARCHAR(5):=')'; /* Окончание опции об участии аттрибута в первичном ключе*/
c_ForeignKeyPrefix CONSTANT VARCHAR(5):='(FK'; /* Начало опции об участии аттрибута во внешнем ключе*/
c_ForeignKeySuffix CONSTANT VARCHAR(5):=')'; /* Окончание опции об участии аттрибута во внешнем ключе*/
c_DescriptionPrefix CONSTANT VARCHAR(5):='(<i>'; /* Начало комментария к атрибуту*/
c_DescriptionSuffix CONSTANT VARCHAR(5):='</i>)'; /* Окончание комментария к атрибуту*/
c_EndOfAttributeString CONSTANT VARCHAR(5):=CHR(13)||CHR(10); /* Символы окончания строки описания атрибута */
c_NULL CONSTANT VARCHAR(5):='NULL'; /* Строка NULL */
c_NOT CONSTANT VARCHAR(5):='NOT'; /* Строка NOT */
c_Blank CONSTANT VARCHAR(5):=' '; /* Символ пробела */
c_AttributeDelimiter CONSTANT VARCHAR(5):=' : '; /* Символ - разделитель названия атрибута и его типа */
c_ColumnFKList_NDims CONSTANT INTEGER:=1; /* Размерность массива атрибутов внешнего ключа*/
v_NeedAttrDesc BOOLEAN; /* Признак необходимости включения комментария аттрибута */
v_AllowedTableName VARCHAR(256)[]; /* Массив допустимых для поиска внешних ключей таблиц */
v_Attribute_String TEXT; /* Строка описания атрибута*/
v_AttributeName name; /* Название атрибута*/
v_UserTypeName VARCHAR(256); /* Пользовательский тип атрибута (название домена)*/
v_TypeName VARCHAR(256); /* Тип атрибута*/
v_isNotNULL BOOLEAN; /* Признак недопустимости значения NULL*/
v_isAttributePK BOOLEAN; /* Признак вхождения атрибута в первичный ключ */
v_ColumnPKNo SMALLINT; /* Порядковый номер аттрибута в первичном ключе*/
v_Description TEXT; /* Комментари к аттрибуту */
v_isAttributeFK BOOLEAN; /* Признак вхождения атрибута во внешний ключ */
v_ColumnFKNoARRAY SMALLINT[]; /* Массив порядковых номеров атрибута во всех внешних ключах */
v_FKTableNameARRAY name[]; /* Массив названий таблиц, на которые сслылаются внешние ключи */
v_FKTableColumnNameARRAY name[]; /* Массив названий атрибутов таблицы, на которую сслылается внешний ключи */
v_ColumnFKNo SMALLINT; /* Порядковый номер атрибута текщем внешнем ключе */
v_FKTableName name; /* Название таблицы, на которую сслылается текщий внешний ключ */
v_FKTableColumnName name; /* Название атрибута таблицы, на которую сслылается внешний ключ */
v_ColumnFKIndx INTEGER; /* Текущий индекс масссива атрибутов внешнего ключа*/
v_ColumnFKLBound INTEGER; /* Нижний индекс масссива атрибутов внешнего ключа*/
v_ColumnFKUBound INTEGER; /* Верхний индекс масссива атрибутов внешнего ключа*/
v_Return_Error Integer := 0; /* Код возврата*/
--******************************************************************************************************
BEGIN
v_Attribute_String:='';
v_AllowedTableName:=COALESCE(a_AllowedTableName,'{}');
v_AllowedTableName:=cnfn_Array_LowerCase(v_AllowedTableName);
v_NeedAttrDesc:=COALESCE(a_NeedAttrDesc,false);
SELECT INTO v_AttributeName,v_TypeName,v_isNotNULL,v_isAttributePK,v_ColumnPKNo,v_Description,v_isAttributeFK,v_ColumnFKNoARRAY,
v_FKTableNameARRAY,v_FKTableColumnNameARRAY
rsa_AttributeName,rsa_TypeName,rsa_isNotNULL,rsa_isAttributePK,rsa_ColumnPKNo,rsa_Description,
rsa_isAttributeFK,rsa_ColumnFKNo,
rsa_FKTableName,rsa_FKTableColumnName
FROM admtf_Attribute_Features(a_TableOID,a_AttributeNo);
v_Attribute_String:=TRIM(v_AttributeName)||c_AttributeDelimiter||
CASE WHEN v_Description IS NULL OR NOT v_NeedAttrDesc THEN ''
ELSE c_Blank||c_DescriptionPrefix||v_Description||c_DescriptionSuffix|| c_Blank END ||
v_TypeName||
c_Blank||CASE WHEN v_isNotNULL THEN c_NOT||c_Blank ELSE '' END || c_NULL || c_Blank ||
CASE WHEN v_isAttributePK
THEN c_PrimaryKeyPrefix||TRIM(COALESCE(TO_CHAR(v_ColumnPKNo,'999'),''))||
c_PrimaryKeySuffix
ELSE '' END;
IF v_isAttributeFK THEN
v_ColumnFKLBound:=array_lower(v_ColumnFKNoARRAY,c_ColumnFKList_NDims);
v_ColumnFKUBound:=array_upper(v_ColumnFKNoARRAY,c_ColumnFKList_NDims);
v_ColumnFKIndx:=v_ColumnFKLBound;
WHILE v_ColumnFKIndx<=v_ColumnFKUBound
LOOP
v_ColumnFKNo:=v_ColumnFKNoARRAY[v_ColumnFKIndx];
v_FKTableName:=v_FKTableNameARRAY[v_ColumnFKIndx];
v_FKTableColumnName:=v_FKTableColumnNameARRAY[v_ColumnFKIndx];
IF COALESCE(a_AllowedTableName,'{}')='{}' OR ARRAY[v_FKTableName] <@ a_AllowedTableName::NAME[] THEN
v_Attribute_String:=v_Attribute_String||c_ForeignKeyPrefix||
TRIM(COALESCE(TO_CHAR(v_ColumnFKNo,'999'),''))||
' '||COALESCE(v_FKTableName,'')||'('||COALESCE(v_FKTableColumnName,'')||')'||
c_ForeignKeySuffix;
END IF;
v_ColumnFKIndx:=v_ColumnFKIndx+1;
END LOOP;
END IF;
v_Attribute_String:=quote_literal(v_Attribute_String)||c_EndOfAttributeString;
RETURN v_Attribute_String;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION umlfn_Attribute_String(a_TableOID OID,a_AttributeNo SMALLINT,a_NeedAttrDesc BOOLEAN,a_AllowedTableName VARCHAR(256)[]) IS 'Возвращает строку с описанием атрибута таблицы в формате плагина (plantuml) к Trac';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM umlfn_Attribute_String('public.Street'::REGCLASS,2::SMALLINT,TRUE);
SELECT * FROM generate_series (1,6) inx, LATERAL umlfn_Attribute_String('public.Street'::REGCLASS,inx::SMALLINT,FALSE) ORDER BY inx;
Creating the umlfn_Table_LinksFromArray function
Comments on the source code of the function can be found here.
Hidden text
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS umlfn_Table_LinksFromArray (a_SchemaName VARCHAR(100),a_TableNameArray VARCHAR(256)[]);
/************************************************************************************************************/
/* Функция возвращает строку с описанием связей таблиц из исходного массива в формате плагина (plantuml) */
/* к Trac — средству управления проектами и отсслеживания ошибок в программном обеспечении. */
/*----------------------------------------------------------------------------------------------------------*/
/* ПРИМЕР: umlfn_Table_LinksFromArray('public','{Street,StreetType,StreetSynonym,StreetTypeSynonym}'); */
/*----------------------------------------------------------------------------------------------------------*/
/* 'public.STREETSYNONYM *-- public.STREET' */
/* 'public.STREETSYNONYM *-- public.STREETTYPE' */
/* 'public.STREET *-- public.STREETTYPE' */
/* 'public.STREETTYPESYNONYM *-- public.STREETTYPE' */
/************************************************************************************************************/
CREATE OR REPLACE FUNCTION umlfn_Table_LinksFromArray
(a_SchemaName VARCHAR(100) default 'public', /* название схемы базы данных */
a_TableNameArray VARCHAR(256)[] default '{}' /* Исходный массив таблиц */
)
RETURNS TEXT AS
$BODY$
DECLARE
c_SchemaName CONSTANT VARCHAR(256):='public'; /* название схемы базы данных */
c_TableKind CONSTANT CHAR:='r';
c_ForeignKeyKind CONSTANT CHAR:='f';
c_ManySign CONSTANT CHAR:='N';
c_OneSign CONSTANT CHAR:='1';
c_PlantManySign CONSTANT VARCHAR:='*';
c_PlantOneSign CONSTANT VARCHAR:='';
c_PlantLinkMark CONSTANT VARCHAR:='--';
c_TableLinksSuffix CONSTANT VARCHAR(10):=CHR(13)||CHR(10); /* Окончание описания таблицы в комментариях*/
c_TableNameDelimiter CONSTANT VARCHAR(5):='.'; /* Символ-разделитель имени таблицы */
c_Blank CONSTANT VARCHAR(5):=' '; /* Символ пробела */
v_SchemaName VARCHAR(256); /* название схемы базы данных */
v_TableLinksScript TEXT; /* Формируемый скрипт*/
v_TableCount INTEGER; /* Чсетчик таблиц группы*/
v_TableNameArray TEXT[]; /* Массив таблиц */
v_TableName VARCHAR(256); /* Название одной из таблиц группы*/
v_FTableName VARCHAR(256); /* Название одной из таблиц группы*/
v_TableDescription TEXT; /* Описание одной из таблиц группы*/
v_TablesRecord RECORD; /* Запись с данными таблицы */
v_TableOID OID; /* ИД таблицы */
v_ForeignKeyName VARCHAR(256); /* Название внешнего ключа*/
v_ProcessedTableNameArray TEXT[]; /* Массив обработанных таблиц */
v_TableNameArrayRemainder TEXT[]; /* Массив необработанных таблиц */
v_LinkSign VARCHAR(5); /* Признак связи по отношения к таклице*/
v_LinkMarkLeftChar VARCHAR; /* Левый символ признака связи*/
v_LinkMarkRightChar VARCHAR; /* Правый символ признака связи*/
v_PlantLinkSign VARCHAR(5);
v_Return_Error Integer := 0;
--******************************************************************************************************
BEGIN
v_SchemaName:=COALESCE(a_SchemaName,c_SchemaName);
v_TableNameArray:=cnfn_Array_LowerCase(a_TableNameArray);
v_TableLinksScript:='';
IF COALESCE(v_TableNameArray,'{}')<>'{}' THEN
v_TableCount:=0;
FOR v_TablesRecord IN SELECT tbl.oid AS TableOID,tbl.relname:: VARCHAR(256),ftbl.oid AS FTableOID,
Ftbl.relname:: VARCHAR(256) AS Frelname,
lcon.oid AS ForeignKeyOID,lcon.conname:: VARCHAR(256) AS ForeignKeyName
FROM pg_class tbl
INNER JOIN pg_namespace nsp ON tbl.relnamespace = nsp.oid
LEFT OUTER JOIN pg_Description dsc ON tbl.oid=dsc.objoid
AND dsc.objsubid=0
INNER JOIN pg_constraint lcon ON lcon.conrelid=tbl.oid
INNER JOIN pg_namespace lnspc ON lcon.connamespace = lnspc.oid
INNER JOIN pg_class ftbl ON lcon.confrelid=ftbl.oid
INNER JOIN pg_namespace fnsp ON ftbl.relnamespace=fnsp.oid
WHERE LOWER(fnsp.nspname)=LOWER(v_SchemaName) AND ftbl.relkind=c_TableKind
AND lcon.contype =c_ForeignKeyKind AND LOWER(lnspc.nspname)=LOWER(v_SchemaName)
AND ftbl.relname !~* E'(raster_|spatial_|geometry_|geography_)'
AND ARRAY[tbl.relname::TEXT] <@ v_TableNameArray
AND ARRAY[ftbl.relname::TEXT] <@ v_TableNameArray
ORDER BY COUNT(*) OVER(PARTITION BY nsp.oid,tbl.oid) DESC, nsp.nspname,tbl.relname
LOOP
v_TableName:=v_TablesRecord.relname;
v_FTableName:=v_TablesRecord.Frelname;
v_ForeignKeyName:=v_TablesRecord.ForeignKeyName;
v_TableCount:=v_TableCount+1;
v_LinkSign:=admfn_Table_LinkSign(a_SchemaName,v_TableName,v_ForeignKeyName);
v_LinkMarkLeftChar:=LEFT(v_LinkSign,1);
v_LinkMarkRightChar:=RIGHT(v_LinkSign,1);
v_PlantLinkSign:=CASE v_LinkMarkLeftChar WHEN c_ManySign THEN c_PlantManySign ELSE c_PlantOneSign END ||
c_PlantLinkMark || CASE v_LinkMarkRightChar WHEN c_ManySign THEN c_PlantManySign
ELSE c_PlantOneSign END;
v_TableLinksScript:=v_TableLinksScript||''''||LOWER(v_SchemaName)||c_TableNameDelimiter||UPPER(v_TableName)||c_Blank||
v_PlantLinkSign||c_Blank||LOWER(v_SchemaName)||c_TableNameDelimiter||UPPER(v_FTableName)||''''||
c_TableLinksSuffix;
v_ProcessedTableNameArray:=array_append(v_ProcessedTableNameArray, v_TableName::TEXT);
END LOOP;
END IF;
RETURN v_TableLinksScript;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION umlfn_Table_LinksFromArray(a_SchemaName VARCHAR(100),a_TableNameArray VARCHAR(256)[]) IS 'Возвращает строку строку с описанием таблиц массива в формате плагина (plantuml) к Trac';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT umlfn_Table_LinksFromArray('public','{Street,StreetType,StreetSynonym,StreetTypeSynonym}');
APPENDIX 2. Additional materials
Step-by-step instructions for viewing class diagrams
Hidden text
You can see what a class diagram described by a PlantUML script looks like on the PlantText UML editor website.
Step 1. Follow the link https://www.planttext.com/ to the editor page, as shown in Fig. 5. Place the cursor on the title of the “File Manager” tab. In the menu that opens, select “Import / Export”.
Step 2. In the opened “Import/Export” window, in the “Import” line, left-click on the “Browse” button, as shown.
The standard file selection window for your operating system will open. In Windows 10, it will look something like the one shown in Fig. 7.
Select a pre-prepared script and click the “Open” button.
Here, the script ending with “PlantTest” is selected, since it is created in a format ready for displaying a class diagram in the PlantText UML editor.
There will be no trouble if you select a script without such an ending, because as shown below, the script format for TRAC contains additional lines at the beginning and end of the script, which can be easily removed.
Step 3. In the opened “Import/Export” window, in the “Import” line, left-click on the “Browse” button, as shown.
As can be seen from Fig. 8, the class diagram can be converted to the following formats: PNG, SVG [*5] TXT.
How to edit PlantUML script for TRAC to see class diagram
PlantText UML editor works with scripts built according to the rules of the PlantUML language, according to which the main text of the script should be enclosed between the operators @startuml and @enduml. At the same time, for embedding in the plugin (plantuml) TRAC, an additional shell is created around the classic text.
In order for PlantText UML editor not to perceive the script as erroneous, the lines above the operator should be removed from its text @startuml and below the operator @endumlThese lines are highlighted in Fig. 10.
After deletion, the screen will look like the one shown in Fig. 8.
Step-by-step instructions for creating a TRAC card
Hidden text
It is assumed here that the “TRAC Project Management System” is already installed. For those interested in information on how to install this system, we suggest going to the page on the official website called “Trac Installation Guide”.
Login to the TRAC system installed on the company's server is performed via a browser. The URL format of the installed TRAC may look like this: https://trac.company.ru/. Where instead of “company” the domain of the company in which the TRAC system is installed is indicated. Therefore, as shown in Fig. 11, the first step is logging into the TRAC system.
After logging in, a project page opens, displaying a list of the company's projects. Since the login is for educational purposes, the second step is to select a “sandbox” project, designed to explore TRAC's capabilities through experimentation. See Fig. 12.
Clicking on the project name with the left mouse button causes a list of project cards to appear on the screen, as well as buttons for calling various operations on the cards. In this case, as shown in Fig. 13, we create a new card by clicking on the button with the corresponding name.
In the window that opens, enter the name of the card in the “Brief Description” field to create a new card, and insert the text of the PlantUML script into the main window. See Fig. 14.
Note that the script being inserted must be surrounded by TRAC wrapper operators. In Fig. 15, these operators are highlighted in red. After clicking one of the Preview or Create Card buttons, the class diagram described by the script will be displayed on the screen.
The goal has been achieved – the script is included in the TRAC system card, which displays the class diagram.
