2056 lines
99 KiB
Text
2056 lines
99 KiB
Text
//***************************************************************************//
|
|
// Megjegyzés? //
|
|
//***************************************************************************//
|
|
template ConvertDictItemTypeToTranslationTable( dictItemType )
|
|
|
|
/*============================================================================*/
|
|
/* [FormatSQLHeader("Table : " ConvertDictItemTypeToTranslationTableName([dictItemType]) )]*/
|
|
/*============================================================================*/
|
|
create table [ConvertDictItemTypeToTranslationTableName([dictItemType])](
|
|
[GenerateDictItemTypeTranslationTableColumnDefinitions(ConvertDictItemTypeToTranslationTableName([dictItemType]))]
|
|
)
|
|
[GetCommandSeparator()]
|
|
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// Megjegyzés? //
|
|
//***************************************************************************//
|
|
template ConvertDictItemTypeToDropTranslationTable(dictItemType)
|
|
drop table [ConvertDictItemTypeToTranslationTableName([dictItemType])] [GetDropTableOptions()]
|
|
[GetCommandSeparator()]
|
|
|
|
end template
|
|
|
|
// NINCS HASZNLAVA
|
|
//***************************************************************************//
|
|
// Megjegyzés? //
|
|
//***************************************************************************//
|
|
proc GenerateDictItemTypeTranslationTableColumnDefinitions(tablename)
|
|
local columns = "";
|
|
local ident = " ";
|
|
|
|
// id oszlop definicioja
|
|
[columns] = Generate_ID_Column([ident], "true");
|
|
// language
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("LANGUAGE");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertAttributeInfoToSQlType("String","","", ""));
|
|
[columns] = [columns] " " FormatSQLColumnModifier("NULL");
|
|
// value
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("VALUE");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertAttributeInfoToSQlType("String","200","", ""));
|
|
[columns] = [columns] " " FormatSQLColumnModifier("NULL");
|
|
// serial
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("SERIAL");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[columns] = [columns] " " FormatSQLColumnModifier("NULL");
|
|
// lastchanged
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("LASTCHANGED");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertUMLTypeToSQLType("DateTime"));
|
|
[columns] = [columns] " " GetDefaultDateTimeString();
|
|
[columns] = [columns] " " FormatSQLColumnModifier("NULL");
|
|
// created
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("CREATED");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertUMLTypeToSQLType("DateTime"));
|
|
[columns] = [columns] " " GetDefaultDateTimeString();
|
|
[columns] = [columns] " " FormatSQLColumnModifier("NULL");
|
|
// creator
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("CREATOR");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[columns] = [columns] " " FormatSQLColumnModifier("NULL");
|
|
// modifier
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("MODIFIER");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[columns] = [columns] " " FormatSQLColumnModifier("NULL");
|
|
//FK to DictItemTypeTable
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("TYPEVALUEID");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[columns] = [columns] " " FormatSQLColumnModifier("NULL");
|
|
|
|
// primary key constraint
|
|
[columns] = [columns] ",\n" [ident] "CONSTRAINT " ConvertNameToPrimaryKeyConstraintName([tablename]) " PRIMARY KEY (ID, " FormatColumnName("INTEZMENYID") ", " FormatColumnName("TANEVID") ")";
|
|
return [columns];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// Megjegyzés? //
|
|
//***************************************************************************//
|
|
template ConvertDictItemTypeToTable( dictItemType )
|
|
|
|
/*============================================================================*/
|
|
/* [FormatSQLHeader("Table : " ConvertDictItemTypeToItemTableName([dictItemType]) )]*/
|
|
/*============================================================================*/
|
|
create table [ConvertDictItemTypeToItemTableName([dictItemType])](
|
|
[GenerateDictItemTypeTableColumnDefinitions(ConvertDictItemTypeToItemTableName([dictItemType]))]
|
|
)
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott dictItemtype alapján genrál SQL drop table scriptet//
|
|
//***************************************************************************//
|
|
template ConvertDictItemTypeToDropTable(dictItemType)
|
|
drop table [ConvertDictItemTypeToItemTableName([dictItemType])] [GetDropTableOptions()]
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
// NINCS HASZNLAVA
|
|
//***************************************************************************//
|
|
// Megjegyzés? //
|
|
//***************************************************************************//
|
|
proc GenerateDictItemTypeTableColumnDefinitions(tablename)
|
|
local columns = "";
|
|
local ident = " ";
|
|
|
|
// id oszlop definicioja
|
|
[columns] = Generate_ID_Column([ident], "true");
|
|
// name
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("NAME");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertAttributeInfoToSQlType("String","200","", ""));
|
|
[columns] = [columns] " " FormatSQLColumnModifier("null");
|
|
// value
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("VALUE");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertAttributeInfoToSQlType("Integer", "", "", ""));
|
|
[columns] = [columns] " " FormatSQLColumnModifier("not null");
|
|
// serial
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("SERIAL");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertAttributeInfoToSQlType("Integer", "", "", ""));
|
|
[columns] = [columns] " " FormatSQLColumnModifier("null");
|
|
// lastchanged
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("LASTCHANGED");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertUMLTypeToSQLType("DateTime"));
|
|
[columns] = [columns] " " GetDefaultDateTimeString();
|
|
[columns] = [columns] " " FormatSQLColumnModifier("null");
|
|
// created
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("CREATED");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertUMLTypeToSQLType("DateTime"));
|
|
[columns] = [columns] " " GetDefaultDateTimeString();
|
|
[columns] = [columns] " " FormatSQLColumnModifier("null");
|
|
// creator
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("CREATOR");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[columns] = [columns] " " FormatSQLColumnModifier("null");
|
|
// modifier
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("MODIFIER");
|
|
[columns] = [columns] " " FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[columns] = [columns] " " FormatSQLColumnModifier("null");
|
|
// primary key constraint
|
|
[columns] = [columns] ",\n" [ident] "CONSTRAINT " ConvertNameToPrimaryKeyConstraintName([tablename]) " PRIMARY KEY (ID, C_ALINTEZMENYID, C_ALTANEVID)";
|
|
return [columns];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott asszociáció információkból legenerálja az //
|
|
// asszociációhoz tartozó duo lista előállításához szükséges select where //
|
|
// részét //
|
|
//***************************************************************************//
|
|
proc GenerateDuoSelectWhereClause(MClass as CurrentClass, MAssociationEnd as StartRole, MAssociation as CurrentAssoc, MAssociationEnd as EndRole, MClass as PartnerClass, MClass as AssocClass )
|
|
local res = "";
|
|
[res] = [res] ConvertNameToSQLTableScript([AssocClass.name]);
|
|
[res] = [res] ".";
|
|
[res] = [res] ConvertNameToSQLColumnName(ConvertRoleToName([StartRole]));
|
|
[res] = [res] " = ";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([StartRole]));
|
|
[res] = [res] " AND ";
|
|
|
|
[res] = [res] ConvertNameToSQLTableScript([AssocClass.name]);
|
|
[res] = [res] ".";
|
|
[res] = [res] ConvertNameToSQLColumnName(ConvertRoleToName([EndRole]));
|
|
[res] = [res] " = ";
|
|
[res] = [res] ConvertNameToSQLTableScript([PartnerClass.name]);
|
|
[res] = [res] ".ID";
|
|
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott asszociáció információkból legenerálja az //
|
|
// asszociációhoz tartozó duo lista előállításához szükséges select select //
|
|
// részét //
|
|
//***************************************************************************//
|
|
proc GenerateDuoSelectSelectFromClause(MClass as CurrentClass, MAssociationEnd as StartRole, MAssociation as CurrentAssoc, MAssociationEnd as EndRole, MClass as PartnerClass, MClass as AssocClass )
|
|
local res;// = "SELECT ";
|
|
[res] = [res] GenerateRecursiveColumnListForEntity([AssocClass]);
|
|
[res] = [res] ", ";
|
|
[res] = [res] GenerateRecursiveColumnListForEntity([PartnerClass]);
|
|
|
|
[res] = [res] " from ";
|
|
[res] = [res] ConvertNameToSQLTableScript([AssocClass.name]);
|
|
[res] = [res] ", ";
|
|
[res] = [res] ConvertNameToSQLTableScript([PartnerClass.name]);
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott reláció információk alapján generálja a select //
|
|
//utasítás where feltételét, ami leírja a reláció menti összekapcsolást //
|
|
//***************************************************************************//
|
|
proc GenerateNoAssocSwitchPartnerSelectWhereClause(MClass as CurrentClass, MAssociationEnd as StartRole, MAssociation as CurrentAssoc, MAssociationEnd as EndRole, MClass as PartnerClass )
|
|
local res = "";
|
|
// első kulcs
|
|
[res] = [res] ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole]));
|
|
[res] = [res] ".";
|
|
[res] = [res] ConvertNameToSQLColumnName(ConvertRoleToName([StartRole]));
|
|
[res] = [res] " = ";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([StartRole]));
|
|
[res] = [res] " AND ";
|
|
|
|
[res] = [res] ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole]));
|
|
[res] = [res] ".";
|
|
[res] = [res] ConvertNameToSQLColumnName(ConvertRoleToName([EndRole]));
|
|
[res] = [res] " = ";
|
|
[res] = [res] ConvertNameToSQLTableScript([PartnerClass.name]);
|
|
[res] = [res] ".ID";
|
|
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// Megjegyzés? //
|
|
//***************************************************************************//
|
|
proc GenerateNoAssocSwitchPartnerSelectSelectFromClause(MClass as CurrentClass, MAssociationEnd as StartRole, MAssociation as CurrentAssoc, MAssociationEnd as EndRole, MClass as PartnerClass )
|
|
return GenerateSelectClauseForEntitySelect([PartnerClass]) ", " ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole]));
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott reláció információk alapján generálja a select //
|
|
// utasítás where feltételét, ami leírja a reláció menti összekapcsolást //
|
|
//***************************************************************************//
|
|
proc GenerateNoAssocPartnerSelectWhereClause(MClass as CurrentClass, MAssociationEnd as StartRole, MAssociation as CurrentAssoc, MAssociationEnd as EndRole, MClass as PartnerClass )
|
|
local res = "";
|
|
[res] = [res] ConvertNameToSQLTableScript([PartnerClass.name]);
|
|
[res] = [res] ".";
|
|
[res] = [res] ConvertNameToSQLColumnName(ConvertRoleToName([StartRole]));
|
|
[res] = [res] " = ";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([StartRole]));
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// Visszaadja a megadott attribútum alapértelmezett (kezdeti) értékét az SQL //
|
|
// nyelv szintaktikájának megfelelően //
|
|
//***************************************************************************//
|
|
proc GetSQLDefaultValueOfAttribute(MAttribute)
|
|
local result;
|
|
|
|
if ([MAttribute.type] == "DictionaryItem")
|
|
[result] = GetTaggedValueOfAttribute([MAttribute], "dictdefault");
|
|
|
|
|
|
|
|
|
|
else
|
|
[result] = [MAttribute.defaultValue];
|
|
|
|
if ([result] != "")
|
|
if (ToLower([MAttribute.type]) == "boolean")
|
|
if (ToLower([result]) == "false")
|
|
[result] = "'F'";
|
|
else
|
|
[result] = "'T'";
|
|
end if
|
|
end if
|
|
if (ToLower([MAttribute.type]) == "string" || ToLower([MAttribute.type]) == "guid")
|
|
if (ToLower([result]) == "newid")
|
|
[result] = "NEWID()";
|
|
else
|
|
[result] = "'" [result] "'";
|
|
end if
|
|
end if
|
|
if (ToLower([MAttribute.type]) == "char")
|
|
[result] = "'" [result] "'";
|
|
end if
|
|
if (ToLower([MAttribute.type]) == "datetime")
|
|
if (ToLower([result]) == "datetime.now")
|
|
switch ( [SQL_DBType])
|
|
case "ORACLE" :
|
|
[result] = "SYSDATE";
|
|
break;
|
|
case "MSSQL" :
|
|
[result] = "GETDATE()";
|
|
break;
|
|
end switch
|
|
else
|
|
[result] = "";
|
|
end if
|
|
end if
|
|
end if
|
|
end if
|
|
return [result];
|
|
end proc
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként megkapott osztály explicit definiált attributumaból //
|
|
// generál SQL table script oszlop definiciókat //
|
|
//***************************************************************************//
|
|
proc Generate_Columns_From_Explicite_Defined_Attributes(MClass, ident)
|
|
local res = "";
|
|
local defaultertek = "";
|
|
local computed = "";
|
|
loop (MClass->MAttribute)
|
|
[res] = [res] ",\n" [ident];
|
|
[computed] = GetTaggedValueOfAttribute([MAttribute],"computed");
|
|
[res] = [res] FormatColumnName(ConvertNameToSQLColumnName([MAttribute.name]));
|
|
[res] = [res] " ";
|
|
|
|
// Ha computed column
|
|
if ([computed] != "")
|
|
[res] = [res] "AS (" [computed] ")";
|
|
if (GetTaggedValueOfAttribute([MAttribute],"persisted") == "true")
|
|
[res] = [res] " PERSISTED";
|
|
end if
|
|
else //minden más esetben
|
|
[res] = [res] FormatSQLColumnType(ConvertAttributeToSQlType([MAttribute]));
|
|
if ( ( [MAttribute.defaultValue] != "" ))
|
|
[defaultertek] = GetSQLDefaultValueOfAttribute([MAttribute]);
|
|
if ([defaultertek] != "")
|
|
[res] = [res] " DEFAULT " [defaultertek];
|
|
end if
|
|
end if
|
|
[res] = [res] " ";
|
|
[res] = [res] FormatSQLColumnModifier(GetColumnModifyerOfAttribute([MAttribute]));
|
|
end if
|
|
end loop
|
|
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// Formázott ID oszlop definíciót generál //
|
|
//***************************************************************************//
|
|
proc Generate_ID_Column(ident, isMasterEntity)
|
|
local res = "";
|
|
[res] = [res] [ident];
|
|
[res] = [res] FormatColumnName("ID");
|
|
[res] = [res] " ";
|
|
[res] = [res] FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[res] = [res] " NOT NULL";
|
|
if([isMasterEntity] == "true")
|
|
[res] = [res] " IDENTITY(1,1)";
|
|
end if
|
|
return [res];
|
|
end proc
|
|
|
|
//***************************************************************************//
|
|
// Formázott oszlop definicíókat generál a paraméterként kapott osztályból. //
|
|
// figyelembe veszi az osszályhoz tartozó asszociációkat, amelyeknek nincs //
|
|
// asszociációs osztálya, valamit az ehhez az osztályhoz (mint asszociációs //
|
|
// osztályhoz) rendelt asszociációkat. //
|
|
//***************************************************************************//
|
|
proc GenerateColumnDeffinitionsAssociation(MClass)
|
|
local columns = "";
|
|
local ident = " ";
|
|
|
|
// Asszociacios osztaly nelkuli relaciok
|
|
local HasAC = "";
|
|
loop (MClass->Role as StartRole ->MAssociation as CurentAssoc ->MAssociationEnd as EndRole -> MClass as PartnerClass where (([StartRole.id]!=[EndRole.id]) and GetStereoType([PartnerClass]) == "Entity"))
|
|
[HasAC] = HasAssociationClass([CurentAssoc]);
|
|
// csak ha nincs a relaciohoz asszociacios osztaly rendelve
|
|
// akkor kell ennel az osztalynal felvenni az oszlopokat.
|
|
if ([HasAC] == "")
|
|
if (
|
|
//0..1-1
|
|
([StartRole.multiplicity]=="0..1" and [EndRole.multiplicity]=="1") or
|
|
//1..*-1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="1") or
|
|
//*-1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="1")
|
|
)
|
|
[columns] = [columns] ",\n" [ident];
|
|
[columns] = [columns] FormatColumnName( ConvertNameToSQLColumnName(ConvertRoleToName([EndRole])) );
|
|
[columns] = [columns] " ";
|
|
[columns] = [columns] FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[columns] = [columns] " ";
|
|
[columns] = [columns] FormatSQLColumnModifier("NOT NULL");
|
|
|
|
end if
|
|
if (
|
|
//1..*-0..1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="0..1") or
|
|
//*-0..1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="0..1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="0..1")
|
|
)
|
|
[columns] = [columns] ",\n" [ident];
|
|
[columns] = [columns] FormatColumnName( ConvertNameToSQLColumnName(ConvertRoleToName([EndRole])) );
|
|
[columns] = [columns] " ";
|
|
[columns] = [columns] FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[columns] = [columns] " ";
|
|
[columns] = [columns] FormatSQLColumnModifier("NULL");
|
|
end if
|
|
end if
|
|
end loop
|
|
|
|
//Asszociacios osztaly relaciok
|
|
loop (MClass -> MAssociation as CurrentAssoc-> MAssociationEnd as StartRole -> MClass as StartClass where (GetStereoType([StartClass])=="Entity") )
|
|
loop (CurrentAssoc-> MAssociationEnd as EndRole -> MClass as EndClass where([StartRole.id]<[EndRole.id] and GetStereoType([EndClass])=="Entity" ))
|
|
[columns] = [columns] ",\n" [ident];
|
|
[columns] = [columns] FormatColumnName( ConvertNameToSQLColumnName(ConvertRoleToName([StartRole])) );
|
|
[columns] = [columns] " ";
|
|
[columns] = [columns] FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[columns] = [columns] " ";
|
|
[columns] = [columns] FormatSQLColumnModifier("NOT NULL");
|
|
[columns] = [columns] ",\n" [ident];
|
|
[columns] = [columns] FormatColumnName( ConvertNameToSQLColumnName(ConvertRoleToName([EndRole])) );
|
|
[columns] = [columns] " ";
|
|
[columns] = [columns] FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[columns] = [columns] " ";
|
|
[columns] = [columns] FormatSQLColumnModifier("NOT NULL");
|
|
end loop
|
|
end loop
|
|
return [columns];
|
|
end proc
|
|
|
|
//***************************************************************************//
|
|
// Formázott oszlop definicíókat generál a paraméterként kapott osztályból. //
|
|
// figyelembe veszi az osztály attribútumait, az osszályhoz tartozó //
|
|
// asszociációkat, amelyeknek nincs asszociációs osztálya, valamit az ehhez //
|
|
// az osztályhoz (mint asszociációs osztályhoz) rendelt asszociációkat. //
|
|
//***************************************************************************//
|
|
proc GenerateColumnDeffinitions(MClass)
|
|
local columns = "";
|
|
local ident = " ";
|
|
local isDkt = "";
|
|
local isDictionaryAltipus = "false";
|
|
local inheritance = "";
|
|
|
|
// id oszlop definicioja
|
|
local isMasterEntity = "false";
|
|
if( IsMasterEntity([MClass]) == "true" )
|
|
[isMasterEntity] = "true";
|
|
end if
|
|
|
|
[isDkt] = GetTaggedValue([MClass], "dkt");
|
|
|
|
//az öröklodés típusa
|
|
[inheritance] = GetInheritance([MClass]);
|
|
|
|
if (IsProtectedDictionaryItem([MClass]) == "True")
|
|
[isDictionaryAltipus] = "true";
|
|
end if
|
|
|
|
|
|
if ([inheritance] == "tpc" && [isDictionaryAltipus] == "false")
|
|
return [columns];
|
|
end if
|
|
//Ha TCP az öröklodes modelje, akkor az ös osztály oszlopat is generáljuk
|
|
if ([inheritance] == "tpc" && [isDictionaryAltipus] == "true")
|
|
[columns] = [columns] Generate_ID_Column([ident], "true");
|
|
loop(MClass->SuperClass AS MasterEntity)
|
|
[columns] = [columns] Generate_Columns_From_Explicite_Defined_Attributes([MasterEntity], [ident]);
|
|
[columns] = [columns] GenerateColumnDeffinitionsAssociation([MasterEntity]);
|
|
end loop
|
|
|
|
|
|
else
|
|
[columns] = [columns] Generate_ID_Column([ident], [isMasterEntity]);
|
|
|
|
end if
|
|
|
|
|
|
// explicite definialt oszlopok
|
|
[columns] = [columns] Generate_Columns_From_Explicite_Defined_Attributes([MClass], [ident]);
|
|
[columns] = [columns] GenerateColumnDeffinitionsAssociation([MClass]);
|
|
|
|
|
|
// serial az kozvetlenul az entityBase-bol szarmazo
|
|
// osztalyok szamara
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("TOROLT") " " FormatSQLColumnType(ConvertAttributeInfoToSQlType("Boolean", "", "", "")) " " FormatSQLColumnModifier("NOT NULL") " DEFAULT 'F'";
|
|
//if (IsMasterEntity([MClass]) == "true" )
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("SERIAL") " " FormatSQLColumnType(ConvertAttributeInfoToSQlType("Integer", "", "", "")) " DEFAULT 0 " FormatSQLColumnModifier("NOT NULL");
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("LASTCHANGED") " " FormatSQLColumnType(ConvertAttributeInfoToSQlType("DateTime", "", "", "")) " " GetDefaultDateTimeString() " " FormatSQLColumnModifier("NOT NULL");
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("CREATED") " " FormatSQLColumnType(ConvertAttributeInfoToSQlType("DateTime", "", "", "")) " " GetDefaultDateTimeString() " " FormatSQLColumnModifier("NOT NULL");
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("MODIFIER") " " FormatSQLColumnType(ConvertUMLTypeToSQLType("ID")) " " FormatSQLColumnModifier("NULL");
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("CREATOR") " " FormatSQLColumnType(ConvertUMLTypeToSQLType("ID")) " " FormatSQLColumnModifier("NULL");
|
|
if ([isDkt] == "true")
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("DELETER") " " FormatSQLColumnType(ConvertUMLTypeToSQLType("ID")) " " FormatSQLColumnModifier("NULL");
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("DELETED") " " FormatSQLColumnType(ConvertAttributeInfoToSQlType("DateTime", "", "", "")) " " FormatSQLColumnModifier("NULL");;
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("STATUSID") " " FormatSQLColumnType(ConvertAttributeInfoToSQlType("Integer", "", "", "")) " DEFAULT 0 " FormatSQLColumnModifier("NOT NULL");
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("UNIQUEID") " " FormatSQLColumnType(ConvertAttributeInfoToSQlType("Guid", "", "", "")) " DEFAULT NEWID() " FormatSQLColumnModifier("NOT NULL");
|
|
[columns] = [columns] ",\n" [ident] FormatColumnName("ACLCODE") " NVARCHAR(1000) " FormatSQLColumnModifier("NULL");;
|
|
end if
|
|
|
|
//end if
|
|
|
|
// primary key constraint generalas
|
|
if(GetMasterEntityNameOf([MClass]) == "DictionaryItemBase" && [MClass.name] != "DictionaryItemBase") // szótár elem leszármaztatott oldalán kell intézmény, tanév is
|
|
[columns] = [columns] ",\n" [ident] "CONSTRAINT " ConvertNameToPrimaryKeyConstraintName([MClass.name]) " PRIMARY KEY (ID, C_ALINTEZMENYID, C_ALTANEVID) " ;
|
|
else
|
|
if([MClass.name] != "DictionaryItemBase" && !([inheritance] == "tpc" && [isDictionaryAltipus] == "true")) // mindenre
|
|
[columns] = [columns] ",\n" [ident] "CONSTRAINT " ConvertNameToPrimaryKeyConstraintName([MClass.name]) " PRIMARY KEY (ID) ";
|
|
else // csak DictionaryItemBase
|
|
[columns] = [columns] ",\n" [ident] "CONSTRAINT " ConvertNameToPrimaryKeyConstraintName([MClass.name]) " PRIMARY KEY (ID, C_INTEZMENYID, C_TANEVID)";
|
|
end if
|
|
end if
|
|
|
|
return [columns];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott asszociációs végpontok alapján formázott //
|
|
// kapcsolótábla oszlop definíciókat generál. //
|
|
//***************************************************************************//
|
|
proc GenerateSwitchTableColumnsDefinitions(MAssociationEnd as StartRole, MAssociationEnd as EndRole, MClass as StartClass, MClass as EndClass)
|
|
local res = "";
|
|
local ident = " ";
|
|
|
|
[res] = [res] [ident];
|
|
[res] = [res] FormatColumnName(ConvertNameToSQLColumnName(ConvertRoleToName([StartRole])));
|
|
[res] = [res] " ";
|
|
[res] = [res] FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[res] = [res] " ";
|
|
[res] = [res] FormatSQLColumnModifier("NOT NULL");
|
|
|
|
[res] = [res] ",\n" [ident];
|
|
[res] = [res] FormatColumnName(ConvertNameToSQLColumnName(ConvertRoleToName([EndRole])));
|
|
[res] = [res] " ";
|
|
[res] = [res] FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[res] = [res] " ";
|
|
[res] = [res] FormatSQLColumnModifier("NOT NULL");
|
|
|
|
if(IsEntityDictionaryItem([StartClass]) == "True" && IsEntityDictionaryItem([EndClass]) == "True")
|
|
[res] = [res] ",\n" [ident] "C_INTEZMENYID";
|
|
[res] = [res] " ";
|
|
[res] = [res] FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[res] = [res] " ";
|
|
[res] = [res] FormatSQLColumnModifier("NOT NULL");
|
|
|
|
[res] = [res] ",\n" [ident] "C_TANEVID";
|
|
[res] = [res] " ";
|
|
[res] = [res] FormatSQLColumnType(ConvertUMLTypeToSQLType("ID"));
|
|
[res] = [res] " ";
|
|
[res] = [res] FormatSQLColumnModifier("NOT NULL");
|
|
end if
|
|
|
|
// primary key constraint generalas
|
|
[res] = [res] ",\n" [ident] "CONSTRAINT ";
|
|
[res] = [res] ConvertNameToPrimaryKeyConstraintName(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole]));
|
|
[res] = [res] " PRIMARY KEY (";
|
|
[res] = [res] ConvertNameToSQLColumnName(ConvertRoleToName([StartRole]));
|
|
[res] = [res] ",";
|
|
[res] = [res] ConvertNameToSQLColumnName(ConvertRoleToName([EndRole]));
|
|
if(IsEntityDictionaryItem([StartClass]) == "True" && IsEntityDictionaryItem([EndClass]) == "True")
|
|
[res] = [res] ", C_INTEZMENYID, C_TANEVID";
|
|
end if
|
|
|
|
[res] = [res] ")";
|
|
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján alter table állításokat generál //
|
|
// Figyelembe veszi az öröklődést, az asszociációs osztály nélküli //
|
|
// asszociációkat, valamint azon asszociációkat, amelyeknek ez asz osztály //
|
|
// az asszociációs osztálya. //
|
|
//***************************************************************************//
|
|
proc GenerateAlterDefinitionsForClass(MClass)
|
|
local res = "";
|
|
local ident = " ";
|
|
local fkname = "";
|
|
local ukname = "";
|
|
//os-re mutato foreign key
|
|
if (IsMasterEntity([MClass]) == "false" AND GetInheritance([MClass]) != "tpc")
|
|
loop (MClass -> SuperClass as super -> MInheritance)
|
|
[fkname] = ConvertNameToForeignKeyName([MClass.name], "Id_AlintezmenyId_AltanevId", [super.name]);
|
|
out = AddForeignKeyConstraint(GetSchemaName([MClass]), GetSchemaName([super]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToForeignKeyConstraint([MClass.name], ""), "ID", ConvertNameToSQLTableScript([super.name]), [fkname], "false", "false" );
|
|
|
|
end loop
|
|
end if
|
|
|
|
// Asszociacios osztaly nelkuli relaciok
|
|
local HasAC = "";
|
|
loop (MClass->Role as StartRole ->MAssociation as CurentAssoc ->MAssociationEnd as EndRole -> MClass as PartnerClass where (([StartRole.id]!=[EndRole.id]) and GetStereoType([PartnerClass]) == "Entity"))
|
|
[HasAC] = HasAssociationClass([CurentAssoc]);
|
|
// csak ha nincs a relaciohoz asszociacios osztaly rendelve
|
|
// akkor kell ennel az osztalynal felvenni az oszlopokat.
|
|
|
|
if ([HasAC] == "")
|
|
|
|
if (
|
|
//0..1-1
|
|
([StartRole.multiplicity]=="0..1" and [EndRole.multiplicity]=="1") or
|
|
//1..*-1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="1") or
|
|
//*-1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="1") or
|
|
//1..*-0..1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="0..1") or
|
|
//*-0..1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="0..1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="0..1")
|
|
)
|
|
//tablename,pkcname,localkey, foreigntablename
|
|
|
|
local endRoleName = "";
|
|
[endRoleName] = DefineRoleName([EndRole]);
|
|
[fkname] = ConvertNameToForeignKeyName([MClass.name], ConvertRoleToName([EndRole]), [PartnerClass.name]);
|
|
out = AddForeignKeyConstraint(GetSchemaName([MClass]), GetSchemaName([PartnerClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToForeignKeyConstraint([MClass.name], ""), ConvertNameToSQLColumnName(ConvertRoleToName([EndRole])),ConvertNameToSQLTableScript([PartnerClass.name]), [fkname],"false", "false" );
|
|
end if
|
|
|
|
if (
|
|
//0..1-1
|
|
([StartRole.multiplicity]=="0..1" and [EndRole.multiplicity]=="1")
|
|
)
|
|
//tablename,pkcname, uniquekeylist)
|
|
[ukname] = ConvertNameToUniqueIndexName([MClass.name], ConvertRoleToName([EndRole]));
|
|
out = AddUniqueKeyConstraint( ConvertNameToSQLTableScript([MClass.name]),[ukname],ConvertNameToSQLColumnName(ConvertRoleToName([EndRole])));
|
|
end if
|
|
end if
|
|
end loop
|
|
//Asszociacios osztaly relaciok
|
|
loop (MClass -> MAssociation as CurrentAssoc-> MAssociationEnd as StartRole -> MClass as StartClass where (GetStereoType([StartClass])=="Entity") )
|
|
loop (CurrentAssoc-> MAssociationEnd as EndRole -> MClass as EndClass where([StartRole.id]<[EndRole.id] and GetStereoType([EndClass])=="Entity" ))
|
|
// foreign kulcsok
|
|
local endclass_dictionary = "false";
|
|
local extendedcolumns = "";
|
|
if(IsEntityDictionaryItem([EndClass]) == "True")
|
|
[endclass_dictionary] = "true";
|
|
[extendedcolumns] = ", C_INTEZMENYID, C_TANEVID";
|
|
end if
|
|
local startclass_dictionary = "false";
|
|
if(IsEntityDictionaryItem([StartClass]) == "True")
|
|
[startclass_dictionary] = "true";
|
|
[extendedcolumns] = ", C_INTEZMENYID, C_TANEVID";
|
|
end if
|
|
[fkname] = ConvertNameToForeignKeyName([MClass.name], ConvertRoleToName([StartRole]), [StartClass.name]);
|
|
out = AddForeignKeyConstraint(GetSchemaName([MClass]), GetSchemaName([StartClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToForeignKeyConstraint([CurrentAssoc.name], ""), ConvertNameToSQLColumnName(ConvertRoleToName([StartRole])) [extendedcolumns], ConvertNameToSQLTableScript([StartClass.name]), [fkname],[startclass_dictionary], "false" );
|
|
|
|
[fkname] = ConvertNameToForeignKeyName([MClass.name], ConvertRoleToName([EndRole]), [EndClass.name]);
|
|
out = AddForeignKeyConstraint(GetSchemaName([MClass]), GetSchemaName([EndClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToForeignKeyConstraint([CurrentAssoc.name], ""), ConvertNameToSQLColumnName(ConvertRoleToName([EndRole])) [extendedcolumns], ConvertNameToSQLTableScript([EndClass.name]), [fkname],[endclass_dictionary], "false" );
|
|
// composite unique key constraint
|
|
// out = AddUniqueKeyConstraint( ConvertNameToSQLTableScript([MClass.name]), ConvertNameToUniqueKeyConstraint([StartRole.id]"_"[CurrentAssoc.id]"_"[EndRole.id]), ConvertNameToSQLColumnName(ConvertRoleToName([StartRole])) "," ConvertNameToSQLColumnName(ConvertRoleToName([EndRole])) );
|
|
end loop
|
|
end loop
|
|
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján alter table állításokat generál //
|
|
// Figyelembe veszi az öröklődést, az asszociációs osztály nélküli //
|
|
// asszociációkat, valamint azon asszociációkat, amelyeknek ez asz osztály //
|
|
// az asszociációs osztálya. //
|
|
//***************************************************************************//
|
|
proc GenerateAlterDropDefinitionsForClass(MClass)
|
|
local res = "";
|
|
local ident = " ";
|
|
|
|
//os-re mutato foreign key
|
|
if (IsMasterEntity([MClass]) == "false")
|
|
loop (MClass -> SuperClass as super -> MInheritance)
|
|
out = DropForeignKeyConstraint(GetPackage([MClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToForeignKeyConstraint( ConvertNameToSQLTableScript([super.id]), [MInheritance.id]) );
|
|
end loop
|
|
end if
|
|
|
|
// Asszociacios osztaly nelkuli relaciok
|
|
local HasAC = "";
|
|
loop (MClass->Role as StartRole ->MAssociation as CurentAssoc ->MAssociationEnd as EndRole -> MClass as PartnerClass where (([StartRole.id]!=[EndRole.id]) and GetStereoType([PartnerClass]) == "Entity"))
|
|
[HasAC] = HasAssociationClass([CurentAssoc]);
|
|
// csak ha nincs a relaciohoz asszociacios osztaly rendelve
|
|
// akkor kell ennel az osztalynal felvenni az oszlopokat.
|
|
if ([HasAC] == "")
|
|
|
|
if (
|
|
//0..1-1
|
|
([StartRole.multiplicity]=="0..1" and [EndRole.multiplicity]=="1") or
|
|
//1..*-1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="1") or
|
|
//*-1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="1") or
|
|
//1..*-0..1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="0..1") or
|
|
//*-0..1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="0..1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="0..1")
|
|
)
|
|
//tablename,pkcname,localkey, foreigntablename
|
|
out = DropForeignKeyConstraint(GetPackage([MClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToForeignKeyConstraint( [EndRole.id], [CurentAssoc.id] ) );
|
|
end if
|
|
end if
|
|
end loop
|
|
//Asszociacios osztaly relaciok
|
|
loop (MClass -> MAssociation as CurrentAssoc-> MAssociationEnd as StartRole -> MClass as StartClass where (GetStereoType([StartClass])=="Entity") )
|
|
loop (CurrentAssoc-> MAssociationEnd as EndRole -> MClass as EndClass where([StartRole.id]<[EndRole.id] and GetStereoType([EndClass])=="Entity" ))
|
|
// foreign kulcsok
|
|
out = DropForeignKeyConstraint(GetPackage([MClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToForeignKeyConstraint( [StartRole.id], [CurrentAssoc.id]) );
|
|
out = DropForeignKeyConstraint(GetPackage([MClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToForeignKeyConstraint( [EndRole.id], [CurrentAssoc.id]) );
|
|
end loop
|
|
end loop
|
|
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján indexeket generál //
|
|
// Figyelembe veszi az öröklődést, az asszociációs osztály nélküli //
|
|
// asszociációkat, valamint azon asszociációkat, amelyeknek ez asz osztály //
|
|
// az asszociációs osztálya. //
|
|
//***************************************************************************//
|
|
proc GenerateTableIndexesForClass(MClass)
|
|
local res = "";
|
|
local ident = " ";
|
|
local HasAC = "";
|
|
|
|
local fieldName = "";
|
|
local functionName = "";
|
|
local complexIndex = "";
|
|
local attributeNames = "";
|
|
|
|
loop ( MClass -> MAttribute as ATTRIBUTE Where toLower(GetTaggedValueOfAttribute([ATTRIBUTE], "index")) == "1")
|
|
[functionName] = GetTaggedValueOfAttribute([ATTRIBUTE], "index_function");
|
|
if ([functionName] == "")
|
|
[fieldName] = ConvertNameToSQLColumnName([ATTRIBUTE.name]);
|
|
else
|
|
[fieldName] = [functionName] "(" ConvertNameToSQLColumnName([ATTRIBUTE.name]) ")";
|
|
end if
|
|
out = CreateIndex(GetPackage([MClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToIndexName([MClass.name], [ATTRIBUTE.name]), [fieldName] );
|
|
end loop
|
|
|
|
loop ( MClass -> MAttribute as ATTRIBUTE Where toLower(GetTaggedValueOfAttribute([ATTRIBUTE], "index1")) == "1" )
|
|
|
|
[functionName] = GetTaggedValueOfAttribute([ATTRIBUTE], "index_function");
|
|
if ([functionName] == "")
|
|
[fieldName] = ConvertNameToSQLColumnName([ATTRIBUTE.name]);
|
|
else
|
|
[fieldName] = [functionName] "(" ConvertNameToSQLColumnName([ATTRIBUTE.name]) ")";
|
|
end if
|
|
|
|
[complexIndex] = [fieldName];
|
|
[attributeNames] = [ATTRIBUTE.name];
|
|
|
|
loop ( MClass -> MAttribute as ATTRIBUTE2 Where toLower(GetTaggedValueOfAttribute([ATTRIBUTE2], "index1")) == "2" )
|
|
[functionName] = GetTaggedValueOfAttribute([ATTRIBUTE2], "index_function");
|
|
if ([functionName] == "")
|
|
[fieldName] = ConvertNameToSQLColumnName([ATTRIBUTE2.name]);
|
|
else
|
|
[fieldName] = [functionName] "(" ConvertNameToSQLColumnName([ATTRIBUTE2.name]) ")";
|
|
end if
|
|
|
|
[complexIndex] = [complexIndex] "," [fieldName];
|
|
[attributeNames] = [attributeNames] "_"[ATTRIBUTE2.name];
|
|
|
|
end loop
|
|
loop ( MClass -> MAttribute as ATTRIBUTE2 Where toLower(GetTaggedValueOfAttribute([ATTRIBUTE2], "index1")) == "3" )
|
|
[functionName] = GetTaggedValueOfAttribute([ATTRIBUTE2], "index_function");
|
|
if ([functionName] == "")
|
|
[fieldName] = ConvertNameToSQLColumnName([ATTRIBUTE2.name]);
|
|
else
|
|
[fieldName] = [functionName] "(" ConvertNameToSQLColumnName([ATTRIBUTE2.name]) ")";
|
|
end if
|
|
|
|
[complexIndex] = [complexIndex] "," [fieldName];
|
|
[attributeNames] = [attributeNames] "_"[ATTRIBUTE2.name];
|
|
end loop
|
|
out = CreateIndex(GetPackage([MClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToIndexName([MClass.name], "") toUpper([attributeNames]), [complexIndex] );
|
|
end loop
|
|
loop ( MClass -> MAttribute as ATTRIBUTE Where toLower(GetTaggedValueOfAttribute([ATTRIBUTE], "index2")) == "1" )
|
|
|
|
[functionName] = GetTaggedValueOfAttribute([ATTRIBUTE], "index_function");
|
|
if ([functionName] == "")
|
|
[fieldName] = ConvertNameToSQLColumnName([ATTRIBUTE.name]);
|
|
else
|
|
[fieldName] = [functionName] "(" ConvertNameToSQLColumnName([ATTRIBUTE.name]) ")";
|
|
end if
|
|
|
|
[complexIndex] = [fieldName];
|
|
|
|
loop ( MClass -> MAttribute as ATTRIBUTE2 Where toLower(GetTaggedValueOfAttribute([ATTRIBUTE2], "index2")) == "2" )
|
|
[functionName] = GetTaggedValueOfAttribute([ATTRIBUTE2], "index_function");
|
|
if ([functionName] == "")
|
|
[fieldName] = ConvertNameToSQLColumnName([ATTRIBUTE2.name]);
|
|
else
|
|
[fieldName] = [functionName] "(" ConvertNameToSQLColumnName([ATTRIBUTE2.name]) ")";
|
|
end if
|
|
|
|
[complexIndex] = [complexIndex] "," [fieldName];
|
|
end loop
|
|
loop ( MClass -> MAttribute as ATTRIBUTE2 Where toLower(GetTaggedValueOfAttribute([ATTRIBUTE2], "index2")) == "3" )
|
|
[functionName] = GetTaggedValueOfAttribute([ATTRIBUTE2], "index_function");
|
|
if ([functionName] == "")
|
|
[fieldName] = ConvertNameToSQLColumnName([ATTRIBUTE2.name]);
|
|
else
|
|
[fieldName] = [functionName] "(" ConvertNameToSQLColumnName([ATTRIBUTE2.name]) ")";
|
|
end if
|
|
|
|
[complexIndex] = [complexIndex] "," [fieldName];
|
|
end loop
|
|
out = CreateIndex(GetPackage([MClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToIndexName([MClass.name], [MClass.id] "_2"), [complexIndex] );
|
|
end loop
|
|
|
|
loop (MClass->Role as StartRole ->MAssociation as CurentAssoc ->MAssociationEnd as EndRole -> MClass as PartnerClass where (([StartRole.id]!=[EndRole.id]) and GetStereoType([PartnerClass]) == "Entity"))
|
|
[HasAC] = HasAssociationClass([CurentAssoc]);
|
|
// csak ha nincs a relaciohoz asszociacios osztaly rendelve
|
|
// akkor kell ennel az osztalynal felvenni az oszlopokat.
|
|
if ([HasAC] == "")
|
|
|
|
if (
|
|
//0..1-1
|
|
([StartRole.multiplicity]=="0..1" and [EndRole.multiplicity]=="1") or
|
|
//1..*-1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="1") or
|
|
//*-1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="1") or
|
|
//1..*-0..1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="0..1") or
|
|
//*-0..1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="0..1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="0..1")
|
|
)
|
|
//tablename,indexname,localkey
|
|
out = CreateIndex(GetPackage([MClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToIndexName([MClass.name], ConvertRoleToName([EndRole])), ConvertNameToSQLColumnName(ConvertRoleToName([EndRole])));
|
|
end if
|
|
end if
|
|
end loop
|
|
//Asszociacios osztaly relaciok
|
|
loop (MClass -> MAssociation as CurrentAssoc-> MAssociationEnd as StartRole -> MClass as StartClass where (GetStereoType([StartClass])=="Entity") )
|
|
loop (CurrentAssoc-> MAssociationEnd as EndRole -> MClass as EndClass where([StartRole.id]<[EndRole.id] and GetStereoType([EndClass])=="Entity" ))
|
|
// foreign kulcsokra index
|
|
out = CreateIndex(GetPackage([MClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToIndexName([MClass.name], ConvertRoleToName([StartRole])), ConvertNameToSQLColumnName(ConvertRoleToName([StartRole])) );
|
|
out = CreateIndex(GetPackage([MClass]), ConvertNameToSQLTableScript([MClass.name]), ConvertNameToIndexName([MClass.name], ConvertRoleToName([EndRole])), ConvertNameToSQLColumnName(ConvertRoleToName([EndRole])) );
|
|
end loop
|
|
end loop
|
|
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján index dropokat generál //
|
|
// Figyelembe veszi az öröklődést, az asszociációs osztály nélküli //
|
|
// asszociációkat, valamint azon asszociációkat, amelyeknek ez asz osztály //
|
|
// az asszociációs osztálya. //
|
|
//***************************************************************************//
|
|
proc GenerateDropTableIndexesForClass(MClass)
|
|
local res = "";
|
|
local ident = " ";
|
|
|
|
// Asszociacios osztaly nelkuli relaciok
|
|
local HasAC = "";
|
|
loop (MClass->Role as StartRole ->MAssociation as CurentAssoc ->MAssociationEnd as EndRole -> MClass as PartnerClass where (([StartRole.id]!=[EndRole.id]) and GetStereoType([PartnerClass]) == "Entity"))
|
|
[HasAC] = HasAssociationClass([CurentAssoc]);
|
|
// csak ha nincs a relaciohoz asszociacios osztaly rendelve
|
|
// akkor kell ennel az osztalynal felvenni az oszlopokat.
|
|
if ([HasAC] == "")
|
|
|
|
if (
|
|
//0..1-1
|
|
([StartRole.multiplicity]=="0..1" and [EndRole.multiplicity]=="1") or
|
|
//1..*-1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="1") or
|
|
//*-1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="1") or
|
|
//1..*-0..1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="0..1") or
|
|
//*-0..1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="0..1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="0..1")
|
|
)
|
|
|
|
switch( [SQL_DBType] )
|
|
//ORACLE Type
|
|
case "ORACLE" :
|
|
out = DropIndex( ConvertNameToIndexName([MClass.name], [EndRole.id]"_"[CurentAssoc.id] ));
|
|
break;
|
|
|
|
//MSSQL Type
|
|
case "MSSQL" :
|
|
out = DropIndexMSSQL( "A" ConvertNameToSQLTableScript([MClass.name]),ConvertNameToIndexName([MClass.name], [EndRole.id]"_"[CurentAssoc.id] ));
|
|
break;
|
|
end switch
|
|
end if
|
|
end if
|
|
end loop
|
|
//Asszociacios osztaly relaciok
|
|
loop (MClass -> MAssociation as CurrentAssoc-> MAssociationEnd as StartRole -> MClass as StartClass where (GetStereoType([StartClass])=="Entity") )
|
|
loop (CurrentAssoc-> MAssociationEnd as EndRole -> MClass as EndClass where([StartRole.id]<[EndRole.id] and GetStereoType([EndClass])=="Entity" ))
|
|
// foreign kulcsok indexeinek törlése
|
|
|
|
switch( [SQL_DBType] )
|
|
//ORACLE Type
|
|
case "ORACLE" :
|
|
out = DropIndex( ConvertNameToIndexName([MClass.name], [StartRole.id]"_"[CurrentAssoc.id]));
|
|
out = DropIndex( ConvertNameToIndexName([MClass.name], [EndRole.id]"_"[CurrentAssoc.id]));
|
|
break;
|
|
|
|
//MSSQL Type
|
|
case "MSSQL" :
|
|
out = DropIndexMSSQL( ConvertNameToSQLTableScript([MClass.name]), ConvertNameToIndexName([MClass.name], [StartRole.id]"_"[CurrentAssoc.id]));
|
|
out = DropIndexMSSQL( ConvertNameToSQLTableScript([MClass.name]), ConvertNameToIndexName([MClass.name], [EndRole.id]"_"[CurrentAssoc.id]));
|
|
break;
|
|
end switch
|
|
end loop
|
|
end loop
|
|
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály attributumain végiglépegetve //
|
|
// a taggedvalue-kon végig lépkedve check constrainteket generál //
|
|
//***************************************************************************//
|
|
proc GenerateAlterCheckDefinitionsForClass(MClass as Entity)
|
|
|
|
local minLen = "";
|
|
local maxLan = "";
|
|
local minVal = "";
|
|
local maxVal = "";
|
|
local LineLen = "";
|
|
|
|
loop ( Entity -> MAttribute Where (([MAttribute.type] != "DictionaryItem" ) and ([MAttribute.type] != "DateTime") and ([MAttribute.type] != "Binary" )))
|
|
if ( loopCount ( MAttribute -> TaggedValue ) != 0 )
|
|
loop ( MAttribute -> TaggedValue Where (([TaggedValue.tag] == "minlength") or ([TaggedValue.tag] == "maxlength") or ([TaggedValue.tag] == "minvalue") or ([TaggedValue.tag] == "maxvalue")))// or ([TaggedValue.tag] == "value")))
|
|
|
|
switch ( [TaggedValue.tag] )
|
|
case "minlength" :
|
|
[minLen] = [TaggedValue.value];
|
|
break;
|
|
case "maxlength" :
|
|
[maxLen] = [TaggedValue.value];
|
|
break;
|
|
case "minvalue" :
|
|
[minVal] = [TaggedValue.value];
|
|
break;
|
|
case "maxvalue" :
|
|
[maxVal] = [TaggedValue.value];
|
|
break;
|
|
end switch
|
|
end loop
|
|
|
|
//template AddCheckConstraint(tablename, pkcname, condition)
|
|
if (( [minLen] != "") and ([maxLen] != "") )
|
|
out = AddCheckConstraint(GetPackage([Entity]), ConvertNameToSQLTableScript([Entity.name]), ConvertNameToCheckConstraintName([Entity.id] "_" [MAttribute.id] "_1"), GetFunctionName( "LENGTH" ) "(" ConvertNameToSQLColumnName([MAttribute.name]) ") between " [minLen] " and " [maxLen] );
|
|
end if
|
|
|
|
if (( [minLen] != "") and ([maxLen] == "") )
|
|
out = AddCheckConstraint(GetPackage([Entity]), ConvertNameToSQLTableScript([Entity.name]), ConvertNameToCheckConstraintName([Entity.id] "_" [MAttribute.id] "_2"), GetFunctionName( "LENGTH" ) "(" ConvertNameToSQLColumnName([MAttribute.name]) ") >= " [minLen] );
|
|
end if
|
|
|
|
if (( [minLen] == "") and ([maxLen] != "") )
|
|
out = AddCheckConstraint(GetPackage([Entity]), ConvertNameToSQLTableScript([Entity.name]), ConvertNameToCheckConstraintName([Entity.id] "_" [MAttribute.id] "_3"), GetFunctionName( "LENGTH" ) "(" ConvertNameToSQLColumnName([MAttribute.name]) ") <= " [maxLen] );
|
|
end if
|
|
|
|
if (( [minVal] != "") and ([maxVal] != "") )
|
|
out = AddCheckConstraint( GetPackage([Entity]), ConvertNameToSQLTableScript([Entity.name]), ConvertNameToCheckConstraintName([Entity.id] "_" [MAttribute.id] "_4"), ConvertNameToSQLColumnName([MAttribute.name]) " between " [minVal] " and " [maxVal] );
|
|
end if
|
|
|
|
if (( [minVal] != "") and ([maxVal] == "") )
|
|
out = AddCheckConstraint(GetPackage([Entity]), ConvertNameToSQLTableScript([Entity.name]), ConvertNameToCheckConstraintName([Entity.id] "_" [MAttribute.id]"_5"), ConvertNameToSQLColumnName([MAttribute.name]) " >= " [minVal] );
|
|
end if
|
|
|
|
if (( [minVal] == "") and ([maxVal] != "") )
|
|
out = AddCheckConstraint(GetPackage([Entity]), ConvertNameToSQLTableScript([Entity.name]), ConvertNameToCheckConstraintName([Entity.id] "_" [MAttribute.id] "_6"), ConvertNameToSQLColumnName([MAttribute.name]) " <= " [maxVal] );
|
|
end if
|
|
|
|
[minLen] = "";
|
|
[maxLen] = "";
|
|
[minVal] = "";
|
|
[maxVal] = "";
|
|
|
|
end if
|
|
end loop
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály attributumain végiglépegetve //
|
|
// unique constrainteket generál //
|
|
//***************************************************************************//
|
|
proc GenerateAlterUniqueDefinitionsForClass(MClass as Entity)
|
|
|
|
local uniqueIds = "";
|
|
local uniqueFields = "";
|
|
local needUKC = "false";
|
|
local ukname = "";
|
|
local uknamecols = "";
|
|
loop ( Entity -> MAttribute as ATTRIBUTE Where toLower(GetTaggedValueOfAttribute([ATTRIBUTE], "Unique")) == "true")
|
|
//tablename,pkcname, uniquekeylist)
|
|
[ukname] = ConvertNameToUniqueIndexName([Entity.name], [ATTRIBUTE.name]);
|
|
out = AddUniqueKeyConstraint( ConvertNameToSQLTableScript([Entity.name]), [ukname], ConvertNameToSQLColumnName([ATTRIBUTE.name]) );
|
|
end loop
|
|
|
|
loop ( Entity -> MAttribute as ATTRIBUTE Where toLower(GetTaggedValueOfAttribute([ATTRIBUTE], "Unique1")) == "true")
|
|
|
|
//tablename,pkcname, uniquekeylist)
|
|
|
|
if ( [uniqueIds] != "" )
|
|
[uniqueIds] = [uniqueIds] "_";
|
|
end if
|
|
|
|
[uniqueIds] = [uniqueIds] [ATTRIBUTE.name];
|
|
|
|
if ( [uniqueFields] != "" )
|
|
[uniqueFields] = [uniqueFields] ", ";
|
|
end if
|
|
|
|
if ( [uknamecols] != "" )
|
|
[uknamecols] = [uknamecols] "_";
|
|
end if
|
|
|
|
[uniqueFields] = [uniqueFields] ConvertNameToSQLColumnName([ATTRIBUTE.name]);
|
|
[uknamecols] = [uknamecols] [ATTRIBUTE.name];
|
|
|
|
if ( [uniqueFields] != "" )
|
|
[needUKC] = "true";
|
|
else
|
|
[needUKC] = "false";
|
|
end if
|
|
end loop
|
|
|
|
if ( [needUKC] == "true" )
|
|
[ukname] = ConvertNameToUniqueIndexName([Entity.name], [uknamecols]);
|
|
out = AddUniqueKeyConstraint( ConvertNameToSQLTableScript([Entity.name]), [ukname], [uniqueFields]);
|
|
[uniqueIds] = "";
|
|
[uniqueFields] = "";
|
|
[uknamecols] = "";
|
|
[needUKC] = "false";
|
|
end if
|
|
|
|
loop ( Entity -> MAttribute as ATTRIBUTE Where toLower(GetTaggedValueOfAttribute([ATTRIBUTE], "Unique2")) == "true")
|
|
//tablename,pkcname, uniquekeylist)
|
|
|
|
if ( [uniqueIds] != "" )
|
|
[uniqueIds] = [uniqueIds] "_";
|
|
end if
|
|
|
|
[uniqueIds] = [uniqueIds] [ATTRIBUTE.name];
|
|
|
|
if ( [uniqueFields] != "" )
|
|
[uniqueFields] = [uniqueFields] ", ";
|
|
end if
|
|
|
|
if ( [uknamecols] != "" )
|
|
[uknamecols] = [uknamecols] "_";
|
|
end if
|
|
|
|
[uniqueFields] = [uniqueFields] ConvertNameToSQLColumnName([ATTRIBUTE.name]);
|
|
[uknamecols] = [uknamecols] [ATTRIBUTE.name];
|
|
|
|
if ( [uniqueFields] != "" )
|
|
[needUKC] = "true";
|
|
else
|
|
[needUKC] = "false";
|
|
end if
|
|
end loop
|
|
|
|
if ( [needUKC] == "true" )
|
|
[ukname] = ConvertNameToUniqueIndexName([Entity.name], [uknamecols]);
|
|
out = AddUniqueKeyConstraint( ConvertNameToSQLTableScript([Entity.name]), [ukname], [uniqueFields]);
|
|
[uniqueIds] = "";
|
|
[uniqueFields] = "";
|
|
[uknamecols] = "";
|
|
[needUKC] = "false";
|
|
end if
|
|
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott asszociáció alapján alter table állításokat generál//
|
|
// kapcsolótáblához. //
|
|
//***************************************************************************//
|
|
proc GenerateAlterDefinitonsForSwitchTable(MClass as StartClass, MAssociationEnd as StartRole, MClass as EndClass, MAssociationEnd as EndRole, MAssociation as CurrentAssoc)
|
|
// foreign key -definitions
|
|
local endclass_dictionary = "false";
|
|
local extendedcolumns = "";
|
|
if(IsEntityDictionaryItem([EndClass]) == "True")
|
|
[endclass_dictionary] = "true";
|
|
[extendedcolumns] = ", C_INTEZMENYID, C_TANEVID";
|
|
end if
|
|
local startclass_dictionary = "false";
|
|
if(IsEntityDictionaryItem([StartClass]) == "True")
|
|
[startclass_dictionary] = "true";
|
|
[extendedcolumns] = ", C_INTEZMENYID, C_TANEVID";
|
|
end if
|
|
[fkname] = ConvertNameToForeignKeyName(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole]), ConvertRoleToName([StartRole]), [StartClass.name]);
|
|
out = AddForeignKeyConstraint(GetSchemaName([StartClass]), GetSchemaName([StartClass]),ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole])), ConvertNameToForeignKeyConstraint([CurrentAssoc.name], ""), ConvertNameToSQLColumnName(ConvertRoleToName([StartRole])) [extendedcolumns], ConvertNameToSQLTableScript([StartClass.name]), [fkname],[startclass_dictionary], "false" );
|
|
|
|
[fkname] = ConvertNameToForeignKeyName(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole]), ConvertRoleToName([EndRole]), [EndClass.name]);
|
|
out = AddForeignKeyConstraint(GetSchemaName([StartClass]), GetSchemaName([EndClass]),ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole])), ConvertNameToForeignKeyConstraint([CurrentAssoc.name], ""), ConvertNameToSQLColumnName(ConvertRoleToName([EndRole])) [extendedcolumns], ConvertNameToSQLTableScript([EndClass.name]), [fkname],[endclass_dictionary], "false" );
|
|
|
|
//0..1-0..1 single unique constraints
|
|
if ([StartRole.multiplicity]=="0..1" and [EndRole.multiplicity]=="0..1")
|
|
//tablename,pkcname, uniquekeylist
|
|
[ukname] = ConvertNameToUniqueIndexName([StartClass.name], ConvertRoleToName([StartRole]));
|
|
out = AddUniqueKeyConstraint( ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole])), [ukname], ConvertNameToSQLColumnName(ConvertRoleToName([StartRole])) );
|
|
[ukname] = ConvertNameToUniqueIndexName([StartClass.name], ConvertRoleToName([EndRole]));
|
|
out = AddUniqueKeyConstraint( ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole])), [ukname], ConvertNameToSQLColumnName(ConvertRoleToName([EndRole])) );
|
|
end if
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott asszociáció alapján alter table drop constraint //
|
|
// statement-eket generál kapcsolótáblához. //
|
|
//***************************************************************************//
|
|
proc GenerateAlterDropDefinitonsForSwitchTable(MClass as StartClass, MAssociationEnd as StartRole, MClass as EndClass, MAssociationEnd as EndRole, MAssociation as CurrentAssoc)
|
|
// foreign key -definitions
|
|
out = DropForeignKeyConstraint(GetSchemaName([StartClass]), ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole])), ConvertNameToForeignKeyConstraint([StartRole.id], [CurrentAssoc.id]) );
|
|
out = DropForeignKeyConstraint(GetSchemaName([StartClass]), ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole])), ConvertNameToForeignKeyConstraint([EndRole.id], [CurrentAssoc.id]) );
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján genrálja az oszlopok value //
|
|
// definiciótt, amelyek felhasználhatóak az insert SQL utasításban //
|
|
//***************************************************************************//
|
|
proc GenerateInsertValuesForEntity(MClass)
|
|
local res = "";
|
|
// id column
|
|
if (IsMasterEntity([MClass]) == "true" )
|
|
[res] = "SEQ_ID_GENERATOR.NEXTVAL";
|
|
else
|
|
[res] = ConvertNameToSQLParamName("ID");
|
|
end if
|
|
// explicite attributes
|
|
loop (MClass->MAttribute)
|
|
[res] = [res] ",";
|
|
[res] = [res] ConvertNameToSQLParamName([MAttribute.name]);
|
|
end loop
|
|
// Asszociacios osztaly nelkuli relaciok oszlopai
|
|
local HasAC = "";
|
|
loop (MClass->Role as StartRole ->MAssociation as CurentAssoc ->MAssociationEnd as EndRole -> MClass as PartnerClass where (([StartRole.id]!=[EndRole.id]) and GetStereoType([PartnerClass]) == "Entity"))
|
|
[HasAC] = HasAssociationClass([CurentAssoc]);
|
|
// csak ha nincs a relaciohoz asszociacios osztaly rendelve
|
|
// akkor kell ennel az osztalynal felvenni az oszlopokat.
|
|
if ([HasAC] == "")
|
|
if (
|
|
//0..1-1
|
|
([StartRole.multiplicity]=="0..1" and [EndRole.multiplicity]=="1") or
|
|
//1..*-1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="1") or
|
|
//*-1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="1")
|
|
)
|
|
[res] = [res] ",";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([EndRole]));
|
|
|
|
end if
|
|
if (
|
|
//1..*-0..1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="0..1") or
|
|
//*-0..1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="0..1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="0..1")
|
|
)
|
|
[res] = [res] ",";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([EndRole]));
|
|
end if
|
|
end if
|
|
end loop
|
|
|
|
//Asszociacios osztaly relaciok
|
|
loop (MClass -> MAssociation as CurrentAssoc-> MAssociationEnd as StartRole -> MClass as StartClass where (GetStereoType([StartClass])=="Entity") )
|
|
loop (CurrentAssoc-> MAssociationEnd as EndRole -> MClass as EndClass where([StartRole.id]<[EndRole.id] and GetStereoType([EndClass])=="Entity" ))
|
|
[res] = [res] ",";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([StartRole]));
|
|
[res] = [res] ",";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([EndRole]));
|
|
end loop
|
|
end loop
|
|
|
|
// serial az kozvetlenul az entityBase-bol szarmazo
|
|
// osztalyok szamara
|
|
if (IsMasterEntity([MClass]) == "true" )
|
|
[res] = [res] ",1";
|
|
end if
|
|
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterént kapott asszociációs végpontok alapján genenrál oszlopnév //
|
|
// definíciókat, amelyek felhasználhatóak az SQL insert uatsításban //
|
|
//***************************************************************************//
|
|
proc GenerateInsertColumnsForSwitchTable(MAssociationEnd as StartRole, MAssociationEnd as EndRole)
|
|
local res = "";
|
|
[res] = [res] ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole])) "." ConvertNameToSQLColumnName(ConvertRoleToName([StartRole]));
|
|
[res] = [res] ",";
|
|
[res] = [res] ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole])) "." ConvertNameToSQLColumnName(ConvertRoleToName([EndRole]));
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott asszociációs végpontok alapján generál oszlop value//
|
|
// definícióka, amelyek felhasználhatóak az SQL insert utasításban. //
|
|
// Kapcsoló táblákról van szó. //
|
|
//***************************************************************************//
|
|
proc GenerateInsertValuesForSwitchTable(MAssociationEnd as StartRole, MAssociationEnd as EndRole)
|
|
local res = "";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([StartRole]));
|
|
[res] = [res] ",";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([EndRole]));
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján generál SQL update utasítás részt, //
|
|
// where feltétel rész nélkül. //
|
|
//***************************************************************************//
|
|
proc GenerateUpdateColumsForEntity(MClass)
|
|
local res = "";
|
|
// id oszlopot nem bantjuk
|
|
|
|
// explicite attributes
|
|
local delimitter = " ";
|
|
loop (MClass->MAttribute)
|
|
[res] = [res] [delimitter];
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) "." ConvertNameToSQLColumnName([MAttribute.name]);
|
|
[res] = [res] " = ";
|
|
[res] = [res] ConvertNameToSQLParamName([MAttribute.name]);
|
|
[delimitter] = ", ";
|
|
end loop
|
|
// Asszociacios osztaly nelkuli relaciok oszlopai
|
|
local HasAC = "";
|
|
|
|
loop (MClass->Role as StartRole ->MAssociation as CurentAssoc ->MAssociationEnd as EndRole -> MClass as PartnerClass where (([StartRole.id]!=[EndRole.id]) and GetStereoType([PartnerClass]) == "Entity"))
|
|
[HasAC] = HasAssociationClass([CurentAssoc]);
|
|
// csak ha nincs a relaciohoz asszociacios osztaly rendelve
|
|
// akkor kell ennel az osztalynal felvenni az oszlopokat.
|
|
if ([HasAC] == "")
|
|
if (
|
|
//0..1-1
|
|
([StartRole.multiplicity]=="0..1" and [EndRole.multiplicity]=="1") or
|
|
//1..*-1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="1") or
|
|
//*-1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="1")
|
|
)
|
|
[res] = [res] [delimitter];
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) "." ConvertNameToSQLColumnName(ConvertRoleToName([EndRole]));
|
|
[res] = [res] " = ";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([EndRole]));
|
|
[delimitter] = ", ";
|
|
end if
|
|
if (
|
|
//1..*-0..1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="0..1") or
|
|
//*-0..1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="0..1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="0..1")
|
|
)
|
|
[res] = [res] [delimitter];
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) "." ConvertNameToSQLColumnName(ConvertRoleToName([EndRole]));
|
|
[res] = [res] " = ";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([EndRole]));
|
|
[delimitter] = ", ";
|
|
end if
|
|
end if
|
|
end loop
|
|
|
|
//Asszociacios osztaly relaciok
|
|
loop (MClass -> MAssociation as CurrentAssoc-> MAssociationEnd as StartRole -> MClass as StartClass where (GetStereoType([StartClass])=="Entity") )
|
|
loop (CurrentAssoc-> MAssociationEnd as EndRole -> MClass as EndClass where([StartRole.id]<[EndRole.id] and GetStereoType([EndClass])=="Entity" ))
|
|
[res] = [res] [delimitter];
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) "." ConvertNameToSQLColumnName(ConvertRoleToName([StartRole]));
|
|
[res] = [res] " = ";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([StartRole]));
|
|
[delimitter] = ", ";
|
|
[res] = [res] [delimitter];
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) "." ConvertNameToSQLColumnName(ConvertRoleToName([EndRole]));
|
|
[res] = [res] " = ";
|
|
[res] = [res] ConvertNameToSQLParamName(ConvertRoleToName([EndRole]));
|
|
end loop
|
|
end loop
|
|
|
|
// serial az kozvetlenul az entityBase-bol szarmazo
|
|
// osztalyok szamara
|
|
if (IsMasterEntity([MClass]) == "true" )
|
|
[res] = [res] [delimitter];
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) ".SERIAL = :pSERIAL + 1";
|
|
end if
|
|
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján generál where feltételt SQL update //
|
|
// utasításhoz //
|
|
//***************************************************************************//
|
|
proc GenerateUpdateWhereForEntity(MClass)
|
|
local res = "";
|
|
[res] = "ID=" ConvertNameToSQLParamName("ID");
|
|
|
|
if (IsMasterEntity([MClass]) == "true" )
|
|
[res] = [res] " and SERIAL=" ConvertNameToSQLParamName("SERIAL");
|
|
end if
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján generál ős osztályok tábláinak //
|
|
// listáját, amely az SQL Select utasításban használatos. //
|
|
//***************************************************************************//
|
|
proc GenerateSuperTableList(MClass)
|
|
local res = "";
|
|
[res] = ConvertNameToSQLTableScript([MClass.name]);
|
|
|
|
if (IsMasterEntity([MClass]) == "true" )
|
|
return [res];
|
|
else
|
|
loop (MClass->SuperClass)
|
|
return [res] ", " GenerateSuperTableList([SuperClass]);
|
|
end loop
|
|
end if
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján generál where feltételt a select //
|
|
// SQL utasításhoz. Ez tartlamazza az osztály ősosztályainak összekapcsolását//
|
|
//***************************************************************************//
|
|
proc GenerateSuperTableWhereList(MClass)
|
|
local res = "";
|
|
local tail = "";
|
|
if (IsMasterEntity([MClass]) == "false" )
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) ".ID = ";
|
|
loop (MClass->SuperClass)
|
|
[res]=[res] ConvertNameToSQLTableScript([SuperClass.name]) ".ID";
|
|
[tail] = GenerateSuperTableWhereList([SuperClass]);
|
|
if ([tail] != "")
|
|
[res] = [res] " AND " [tail];
|
|
end if
|
|
end loop
|
|
end if
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján generál oszlop listát, de nem //
|
|
// tartalmazza az ID-t //
|
|
//***************************************************************************//
|
|
proc GenerateNotBasicColumnListForEntity(MClass)
|
|
local res = "";
|
|
// explicite definiált változók
|
|
loop (MClass->MAttribute)
|
|
// szeparátor
|
|
if ( [res] != "" )
|
|
[res] = [res] ", ";
|
|
end if
|
|
// maga az oszlopnév
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) "." ConvertNameToSQLColumnName([MAttribute.name]);
|
|
end loop
|
|
|
|
// Asszociacios osztaly nelkuli relaciok eredményezte oszlopok
|
|
local HasAC = "";
|
|
loop (MClass->Role as StartRole ->MAssociation as CurentAssoc ->MAssociationEnd as EndRole -> MClass as PartnerClass where (([StartRole.id]!=[EndRole.id]) and GetStereoType([PartnerClass]) == "Entity"))
|
|
[HasAC] = HasAssociationClass([CurentAssoc]);
|
|
// csak ha nincs a relaciohoz asszociacios osztaly rendelve
|
|
// akkor kell ennel az osztalynal felvenni az oszlopokat.
|
|
if ([HasAC] == "")
|
|
if (
|
|
//0..1-1
|
|
([StartRole.multiplicity]=="0..1" and [EndRole.multiplicity]=="1") or
|
|
//1..*-1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="1") or
|
|
//*-1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="1")
|
|
)
|
|
// szeparátor
|
|
if ( [res] != "" )
|
|
[res] = [res] ", ";
|
|
end if
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) "." ConvertNameToSQLColumnName(ConvertRoleToName([EndRole]));
|
|
end if
|
|
if (
|
|
//1..*-0..1
|
|
([StartRole.multiplicity]=="1..*" and [EndRole.multiplicity]=="0..1") or
|
|
//*-0..1
|
|
([StartRole.multiplicity]=="*" and [EndRole.multiplicity]=="0..1") or
|
|
([StartRole.multiplicity]=="0..*" and [EndRole.multiplicity]=="0..1")
|
|
)
|
|
// szeparátor
|
|
if ( [res] != "" )
|
|
[res] = [res] ", ";
|
|
end if
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) "." ConvertNameToSQLColumnName(ConvertRoleToName([EndRole]));
|
|
end if
|
|
end if
|
|
end loop
|
|
|
|
//Asszociacios osztaly létéből fakadó oszlopok
|
|
loop (MClass -> MAssociation as CurrentAssoc-> MAssociationEnd as StartRole -> MClass as StartClass where (GetStereoType([StartClass])=="Entity") )
|
|
loop (CurrentAssoc-> MAssociationEnd as EndRole -> MClass as EndClass where([StartRole.id]<[EndRole.id] and GetStereoType([EndClass])=="Entity" ))
|
|
// szeparátor
|
|
if ( [res] != "" )
|
|
[res] = [res] ", ";
|
|
end if
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) "." ConvertNameToSQLColumnName(ConvertRoleToName([StartRole]));
|
|
[res] = [res] ", ";
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) "." ConvertNameToSQLColumnName(ConvertRoleToName([EndRole]));
|
|
end loop
|
|
end loop
|
|
// Seria szám, ha az osztály master entity
|
|
if (IsMasterEntity([MClass]) == "true" )
|
|
if ( [res] != "" )
|
|
[res] = [res] ", ";
|
|
end if
|
|
[res] = [res] ConvertNameToSQLTableScript([MClass.name]) ".SERIAL";
|
|
end if
|
|
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján generál oszloplistát, amely //
|
|
// az adott szintű enitás minden oszlopát tartalmazza. //
|
|
//***************************************************************************//
|
|
proc GenerateFullColumnListForEntity(MClass)
|
|
local res = ConvertNameToSQLTableScript([MClass.name]) ".ID";
|
|
|
|
local notBasicColumnList = GenerateNotBasicColumnListForEntity([MClass]);
|
|
if ([res]!="" and [notBasicColumnList]!= "")
|
|
[res] = [res] ", " [notBasicColumnList];
|
|
else
|
|
// az egyik úgy is üres nem kell elválasztani őket
|
|
[res] = [res] [notBasicColumnList];
|
|
end if
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján generál oszloplistát, amely //
|
|
// tartalmazza az entitás őseinek az oszlopait is. Csak a master entiás id-ja//
|
|
// lesz benne a listában. //
|
|
//***************************************************************************//
|
|
proc GenerateRecursiveColumnListForEntity(MClass)
|
|
// visszatérési változó
|
|
local res = "";
|
|
|
|
// Aktuális osztály oszlopainak a genrálása
|
|
|
|
// id oszlop, ha az entitás master entity
|
|
if (IsMasterEntity([MClass]) == "true")
|
|
[res] = GenerateFullColumnListForEntity([MClass]);
|
|
else
|
|
[res] = GenerateNotBasicColumnListForEntity([MClass]);
|
|
end if
|
|
|
|
// Aktuális osztály oszlopainak a genrálása befejeződött
|
|
|
|
// rekurzió indítása.
|
|
|
|
if (IsMasterEntity([MClass]) == "true" )
|
|
return [res];
|
|
else
|
|
local tail = "";
|
|
loop (MClass->SuperClass)
|
|
[tail] = GenerateRecursiveColumnListForEntity([SuperClass]);
|
|
end loop
|
|
|
|
if ( [res] != "" and [tail] != "")
|
|
return [res] ", " [tail];
|
|
else
|
|
return [res] [tail];
|
|
end if
|
|
end if
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján generálja le az SQL select utasítás//
|
|
// Select és From részét //
|
|
//***************************************************************************//
|
|
proc GenerateSelectClauseForEntitySelect(MClass)
|
|
local res = "SELECT ";
|
|
[res] = [res] GenerateRecursiveColumnListForEntity([MClass]);
|
|
[res] = [res] " FROM " GenerateSuperTableList([MClass]);
|
|
return [res];
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján generálja le az SQL select utasítás//
|
|
// Where magját, beleértve az ID parameterezest is. //
|
|
//***************************************************************************//
|
|
proc GenerateWhereClauseForEntitySelect(MClass)
|
|
local res = ConvertNameToSQLTableScript([MClass.name]) ".ID = " ConvertNameToSQLParamName("ID");
|
|
local supertablelist = GenerateSuperTableWhereList([MClass]);
|
|
if ([supertablelist] == "")
|
|
return [res];
|
|
else
|
|
return [res] " AND " [supertablelist];
|
|
end if
|
|
end proc
|
|
|
|
|
|
//***************************************************************************//
|
|
//***************************************************************************//
|
|
// Entity ből származó osztályok SQL manipulációs scriptjei //
|
|
//***************************************************************************//
|
|
//***************************************************************************//
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján generál SQL create table scriptet //
|
|
//***************************************************************************//
|
|
template ConvertClassToTable( MClass )
|
|
[if (!(IsMasterEntity([MClass]) == "true" && GetInheritance([MClass]) == "tpc"))]
|
|
/*============================================================================*/
|
|
/* [FormatSQLHeader("Table : " ConvertNameToSQLTableScript([MClass.name]) )] */
|
|
/*============================================================================*/
|
|
/* Package: [GetPackage([MClass])] */
|
|
|
|
[if ( GetPackage([MClass]) == [ProjectName]) ]
|
|
CREATE TABLE \[dbo\].[ConvertNameToSQLTableScript([MClass.name])] (
|
|
[else]
|
|
CREATE TABLE \[[GetPackage([MClass])]\].[ConvertNameToSQLTableScript([MClass.name])] (
|
|
[end if]
|
|
[GenerateColumnDeffinitions([MClass])]
|
|
)
|
|
[GetCommandSeparator()]
|
|
[end if]
|
|
end template
|
|
|
|
//***************************************************************************//
|
|
// Létrehoz egy sémát egy package alapján. //
|
|
//***************************************************************************//
|
|
proc CreateExtendedSchema(schema_name)
|
|
local SchemaSql = "";
|
|
|
|
[SchemaSql] = [SchemaSql] "IF NOT EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = '" [schema_name] "' )\n";
|
|
[SchemaSql] = [SchemaSql] "BEGIN\n";
|
|
[SchemaSql] = [SchemaSql] " --create [schema_name] schema\n";
|
|
[SchemaSql] = [SchemaSql] " EXECUTE sp_executesql N'CREATE SCHEMA [" [schema_name] "] AUTHORIZATION [dbo] '\n\n";
|
|
[SchemaSql] = [SchemaSql] "END\n\n";
|
|
|
|
return [SchemaSql];
|
|
|
|
end proc
|
|
|
|
//***************************************************************************//
|
|
// Létrehoz egy schema-t. //
|
|
//***************************************************************************//
|
|
proc CreateSchema()
|
|
local SchemaSql = "";
|
|
|
|
[SchemaSql] = "If not Exists (select 1 from master.dbo.syslogins where name = @SchemaUser and dbname = 'master')\n";
|
|
[SchemaSql] = [SchemaSql] "BEGIN\n";
|
|
[SchemaSql] = [SchemaSql] " -- create user login\n";
|
|
[SchemaSql] = [SchemaSql] " SET @Sql = N'CREATE LOGIN ['+@SchemaUser+'] WITH PASSWORD=N''Porcica1.'', DEFAULT_LANGUAGE= British '\n";
|
|
[SchemaSql] = [SchemaSql] " EXECUTE sp_executesql @Sql\n";
|
|
[SchemaSql] = [SchemaSql] " END\n\n";
|
|
|
|
[SchemaSql] = [SchemaSql] "If not Exists (SELECT 1 FROM sys.sysusers WHERE name= @SchemaUser)\n";
|
|
[SchemaSql] = [SchemaSql] "BEGIN\n";
|
|
[SchemaSql] = [SchemaSql] "--create user in Kreta database\n";
|
|
[SchemaSql] = [SchemaSql] "SET @Sql = N'CREATE USER ['+@SchemaUser+'] FOR LOGIN ['+@SchemaUser+'] WITH DEFAULT_SCHEMA=['+@Schema+'] '\n";
|
|
[SchemaSql] = [SchemaSql] "EXECUTE sp_executesql @Sql\n\n";
|
|
|
|
[SchemaSql] = [SchemaSql] "--create schema\n";
|
|
[SchemaSql] = [SchemaSql] "SET @Sql = N'CREATE SCHEMA ['+@Schema+'] AUTHORIZATION [dbo] '\n";
|
|
[SchemaSql] = [SchemaSql] "EXECUTE sp_executesql @Sql\n\n";
|
|
|
|
[SchemaSql] = [SchemaSql] "--apply permissions to schemas\n";
|
|
[SchemaSql] = [SchemaSql] "SET @Sql = N'GRANT SELECT ON SCHEMA::['+@Schema+'] TO ['+@SchemaUser+']\n";
|
|
[SchemaSql] = [SchemaSql] "GRANT EXECUTE ON SCHEMA::['+@Schema+'] TO ['+@SchemaUser+']\n";
|
|
[SchemaSql] = [SchemaSql] "GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO ['+@SchemaUser+']\n";
|
|
|
|
[SchemaSql] = [SchemaSql] "GRANT DELETE ON SCHEMA::['+@Schema+'] TO ['+@SchemaUser+']\n";
|
|
[SchemaSql] = [SchemaSql] "GRANT UPDATE ON SCHEMA::['+@Schema+'] TO ['+@SchemaUser+']\n";
|
|
[SchemaSql] = [SchemaSql] "GRANT INSERT ON SCHEMA::['+@Schema+'] TO ['+@SchemaUser+']'\n\n";
|
|
[SchemaSql] = [SchemaSql] "EXECUTE sp_executesql @Sql\n\n";
|
|
|
|
[SchemaSql] = [SchemaSql] "--Allow user to connect to database\n";
|
|
[SchemaSql] = [SchemaSql] "SET @Sql = N'GRANT CONNECT TO ['+@SchemaUser+']'\n";
|
|
[SchemaSql] = [SchemaSql] "EXECUTE sp_executesql @Sql\n\n";
|
|
[SchemaSql] = [SchemaSql] "END\n\n";
|
|
|
|
[SchemaSql] = [SchemaSql] "SET @Sql = ''\n";
|
|
[SchemaSql] = [SchemaSql] "SELECT @sql += 'DROP VIEW [' + TABLE_SCHEMA + '].[' + TABLE_NAME +']' + char(13) + char(10)\n";
|
|
[SchemaSql] = [SchemaSql] "FROM INFORMATION_SCHEMA.TABLES v\n";
|
|
[SchemaSql] = [SchemaSql] "WHERE NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE REPLACE(v.TABLE_NAME, '_OSSZES', '') = t.TABLE_NAME AND t.TABLE_SCHEMA = 'dbo' AND t.TABLE_TYPE = 'BASE TABLE')\n";
|
|
[SchemaSql] = [SchemaSql] " AND v.TABLE_TYPE = 'VIEW'\n";
|
|
[SchemaSql] = [SchemaSql] " AND v.TABLE_SCHEMA = @Schema\n";
|
|
[SchemaSql] = [SchemaSql] "EXEC sp_executesql @Sql\n\n";
|
|
|
|
|
|
return [SchemaSql];
|
|
|
|
end proc
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján generál SQL create view scriptet //
|
|
//***************************************************************************//
|
|
template ConvertClassToView( MClass )
|
|
|
|
/*============================================================================*/
|
|
/* [FormatSQLHeader("View : " ConvertNameToSQLTableScript([MClass.name]) )] */
|
|
/*============================================================================*/
|
|
/* Package: [GetPackage([MClass])] */
|
|
IF OBJECT_ID('[ConvertSchema()].[ConvertNameToSQLTableScript([MClass.name])]', 'V') IS NOT NULL BEGIN
|
|
SET @Sql = N'EXEC sp_refreshview ''[ConvertSchema()].[ConvertNameToSQLTableScript([MClass.name])]'''
|
|
EXEC sp_executesql @Sql
|
|
|
|
IF OBJECT_ID('[ConvertSchema()].[ConvertNameToSQLTableScript([MClass.name])]_OSSZES', 'V') IS NOT NULL BEGIN
|
|
SET @Sql = N'EXEC sp_refreshview ''[ConvertSchema()].[ConvertNameToSQLTableScript([MClass.name])]_OSSZES'''
|
|
EXEC sp_executesql @Sql
|
|
END
|
|
END
|
|
ELSE BEGIN
|
|
/* Ha nem létezik a view, akkor létrehozzuk */
|
|
IF (SELECT COUNT(1)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME = [ConvertTableName([MClass.name])] AND TABLE_SCHEMA = [ConvertDBOSchema()] AND COLUMN_NAME = [ConvertQuoteTanevId([MClass])]
|
|
) > 0
|
|
BEGIN
|
|
SET @AktivTanev = N' [ConvertClassTanevId([MClass])] = '+ CAST(@TanevId as nvarchar) +' AND '
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @AktivTanev = N''
|
|
END
|
|
|
|
IF (SELECT COUNT(1)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME = [ConvertTableName([MClass.name])] AND TABLE_SCHEMA = [ConvertDBOSchema()] AND COLUMN_NAME = [ConvertIntezmenyId([MClass])]
|
|
) > 0
|
|
BEGIN
|
|
/* [ConvertNameToSQLTableScript([MClass.name])] */
|
|
SET @Sql = N'CREATE VIEW [ConvertSchema()].[ConvertNameToSQLTableScript([MClass.name])]
|
|
AS
|
|
SELECT *
|
|
FROM dbo.[ConvertNameToSQLTableScript([MClass.name])]
|
|
[if( IsMasterEntity([MClass]) == "true" )]
|
|
WHERE '+@AktivTanev+' ([ConvertTorolt()]) AND C_INTEZMENYID = '+CAST(@IntezmenyId as nvarchar)
|
|
[else]
|
|
WHERE '+@AktivTanev+' ([ConvertTorolt()]) AND C_ALINTEZMENYID = '+CAST(@IntezmenyId as nvarchar)
|
|
[end if]
|
|
EXECUTE sp_executesql @Sql
|
|
|
|
/* [ConvertNameToSQLTableScript([MClass.name])]_OSSZES */
|
|
SET @Sql = N'CREATE VIEW [ConvertSchema()].[ConvertNameToSQLTableScript([MClass.name])]_OSSZES
|
|
AS
|
|
SELECT *
|
|
FROM dbo.[ConvertNameToSQLTableScript([MClass.name])]
|
|
[if( IsMasterEntity([MClass]) == "true" )]
|
|
WHERE C_INTEZMENYID = '+CAST(@IntezmenyId as nvarchar)
|
|
[else]
|
|
WHERE C_ALINTEZMENYID = '+CAST(@IntezmenyId as nvarchar)
|
|
[end if]
|
|
EXECUTE sp_executesql @Sql
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
/* [ConvertNameToSQLTableScript([MClass.name])] */
|
|
SET @Sql = N'CREATE VIEW [ConvertSchema()].[ConvertNameToSQLTableScript([MClass.name])]
|
|
AS
|
|
SELECT *
|
|
FROM dbo.[ConvertNameToSQLTableScript([MClass.name])]
|
|
WHERE '+@AktivTanev+' ([ConvertTorolt()])'
|
|
EXECUTE sp_executesql @Sql
|
|
|
|
/* [ConvertNameToSQLTableScript([MClass.name])]_OSSZES */
|
|
SET @Sql = N'CREATE VIEW [ConvertSchema()].[ConvertNameToSQLTableScript([MClass.name])]_OSSZES
|
|
AS
|
|
SELECT *
|
|
FROM dbo.[ConvertNameToSQLTableScript([MClass.name])]'
|
|
EXECUTE sp_executesql @Sql
|
|
END
|
|
END
|
|
|
|
end template
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott kapcsolótábla alapján generál SQL create view scriptet //
|
|
//***************************************************************************//
|
|
template ConvertAssociatedClassToView( TableName )
|
|
|
|
/*============================================================================*/
|
|
/* [FormatSQLHeader("View : " [TableName])] */
|
|
/*============================================================================*/
|
|
IF OBJECT_ID('[ConvertSchema()].[TableName]', 'V') IS NOT NULL BEGIN
|
|
SET @Sql = N'EXEC sp_refreshview ''[ConvertSchema()].[TableName]'''
|
|
EXEC sp_executesql @Sql
|
|
|
|
IF OBJECT_ID('[ConvertSchema()].[TableName]_OSSZES', 'V') IS NOT NULL BEGIN
|
|
SET @Sql = N'EXEC sp_refreshview ''[ConvertSchema()].[TableName]_OSSZES'''
|
|
EXEC sp_executesql @Sql
|
|
END
|
|
END
|
|
ELSE BEGIN
|
|
/* Ha nem létezik a view, akkor létrehozzuk */
|
|
IF (SELECT COUNT(1)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME = [ConvertAssociatedTableName([TableName])] AND TABLE_SCHEMA = [ConvertDBOSchema()] AND COLUMN_NAME IN ( [ConvertDefaultIntezmenyId()], [ConvertTanevId()] )
|
|
) > 0
|
|
BEGIN
|
|
SET @AktivTanev = N' C_TANEVID = '+ CAST(@TanevId as nvarchar) +' AND '
|
|
|
|
/* [TableName] */
|
|
SET @Sql = N'CREATE VIEW [ConvertSchema()].[TableName]
|
|
AS
|
|
SELECT *
|
|
FROM dbo.[TableName]
|
|
WHERE '+@AktivTanev+' C_INTEZMENYID = '+CAST(@IntezmenyId as nvarchar)
|
|
EXECUTE sp_executesql @Sql
|
|
|
|
/* [TableName]_OSSZES */
|
|
SET @Sql = N'CREATE VIEW [ConvertSchema()].[TableName]_OSSZES
|
|
AS
|
|
SELECT *
|
|
FROM dbo.[TableName]
|
|
WHERE C_INTEZMENYID = '+CAST(@IntezmenyId as nvarchar)
|
|
EXECUTE sp_executesql @Sql
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
/* [TableName] */
|
|
SET @Sql = N'CREATE VIEW [ConvertSchema()].[TableName]
|
|
AS
|
|
SELECT *
|
|
FROM dbo.[TableName]'
|
|
EXECUTE sp_executesql @Sql
|
|
END
|
|
END
|
|
|
|
end template
|
|
|
|
proc ConvertAssociatedTableName(tablename)
|
|
return "'" [tablename] "'";
|
|
end proc
|
|
|
|
proc ConvertDBOSchema()
|
|
return "'dbo'";
|
|
end proc
|
|
|
|
proc ConvertSchema()
|
|
return "['+@Schema+']";
|
|
end proc
|
|
|
|
proc ConvertSchemaName()
|
|
return "''+@Schema+''";
|
|
end proc
|
|
|
|
proc ConvertTableName(classname)
|
|
return "N'" ConvertNameToSQLTableScript([classname]) "'";
|
|
end proc
|
|
|
|
proc ConvertTorolt()
|
|
return "TOROLT = ''F''";
|
|
end proc
|
|
|
|
proc ConvertIntezmenyId( MClass )
|
|
if( IsMasterEntity([MClass]) == "true" )
|
|
return "'C_INTEZMENYID'";
|
|
else
|
|
return "'C_ALINTEZMENYID'";
|
|
end if
|
|
end proc
|
|
|
|
proc ConvertDefaultIntezmenyId()
|
|
return "'C_INTEZMENYID'";
|
|
end proc
|
|
|
|
proc ConvertTanevId()
|
|
return "'C_TANEVID'";
|
|
end proc
|
|
|
|
proc ConvertClassTanevId( MClass )
|
|
if( IsMasterEntity([MClass]) == "true" )
|
|
return "C_TANEVID";
|
|
else
|
|
return "C_ALTANEVID";
|
|
end if
|
|
end proc
|
|
|
|
proc ConvertQuoteTanevId( MClass )
|
|
if( IsMasterEntity([MClass]) == "true" )
|
|
return "'C_TANEVID'";
|
|
else
|
|
return "'C_ALTANEVID'";
|
|
end if
|
|
end proc
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján generál SQL create table PK //
|
|
// scriptet //
|
|
//***************************************************************************//
|
|
template AddPKToClassTable( MClass )
|
|
|
|
/*============================================================================*/
|
|
/* [FormatSQLHeader("Table PK : " ConvertNameToSQLTableScript([MClass.name]) )] */
|
|
/*============================================================================*/
|
|
[if(IsEntityDictionaryItem([MClass]) == "True")] // szótár elem leszármaztatott oldalán kell intézmény, tanév is
|
|
ALTER TABLE \[[GetSchemaName([MClass])]\].[ConvertNameToSQLTableScript([MClass.name])] ADD CONSTRAINT [ConvertNameToPrimaryKeyConstraintName([MClass.name])] PRIMARY KEY (ID, C_ALINTEZMENYID, C_ALTANEVID)
|
|
[end if]
|
|
[if(ConvertNameToPrimaryKeyConstraintName([MClass.id]) == "T_DICTIONARYITEMBASE")]
|
|
ALTER TABLE \[[GetSchemaName([MClass])]\].[ConvertNameToSQLTableScript([MClass.name])] ADD CONSTRAINT [ConvertNameToPrimaryKeyConstraintName([MClass.name])] PRIMARY KEY (ID, C_INTEZMENYID, C_TANEVID)
|
|
[end if]
|
|
[if(IsEntityDictionaryItem([MClass]) == "False" && ConvertNameToPrimaryKeyConstraintName([MClass.id]) != "T_DICTIONARYITEMBASE")]
|
|
ALTER TABLE \[[GetSchemaName([MClass])]\].[ConvertNameToSQLTableScript([MClass.name])] ADD CONSTRAINT [ConvertNameToPrimaryKeyConstraintName([MClass.name])] PRIMARY KEY (ID)
|
|
[end if]
|
|
|
|
[GetCommandSeparator()]
|
|
|
|
end template
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján genrál SQL drop table scriptet //
|
|
//***************************************************************************//
|
|
template ConvertClassToDropTable (MClass)
|
|
drop table [ConvertNameToSQLTableScript([MClass.name])] [GetDropTableOptions()]
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott asszociációs végpontok alapján generál SQL insert //
|
|
// insert utasítást. //
|
|
//***************************************************************************//
|
|
template GenerateInsertCommandForEntity(MClass)
|
|
INSERT INTO [ConvertNameToSQLTableScript([MClass.name])] ( [GenerateFullColumnListForEntity([MClass])] ) VALUES ( [GenerateInsertValuesForEntity([MClass])] )
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján, generál SQL update utasítást //
|
|
//***************************************************************************//
|
|
template GenerateUpdateCommandForEntity(MClass)
|
|
UPDATE [ConvertNameToSQLTableScript([MClass.name])] SET [GenerateUpdateColumsForEntity([MClass])] WHERE [GenerateUpdateWhereForEntity([MClass])]
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
//***************************************************************************//
|
|
// A megfellő multiplicitással rendelkező asszociációkból keletkező kapcsoló //
|
|
// táblák SQL manipulációs scriptjei //
|
|
//***************************************************************************//
|
|
//***************************************************************************//
|
|
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott asszociációs végpontok alapján generál SQL create //
|
|
// table scriptet //
|
|
//***************************************************************************//
|
|
template ConvertAssociationToSwitchTable(MAssociationEnd as StartRole, MAssociationEnd as EndRole, MClass as StartClass, MClass as EndClass)
|
|
/*============================================================================*/
|
|
/* [FormatSQLHeader("Table : " ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName ([StartRole],[EndRole]) ))]*/
|
|
/*============================================================================*/
|
|
/* StartPackage: [GetPackage([StartClass])] EndPackage: [GetPackage([EndClass])] */
|
|
create table \[[GetSchemaName([StartClass])]\].[ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole]))](
|
|
|
|
[GenerateSwitchTableColumnsDefinitions([StartRole],[EndRole], [StartClass], [EndClass])]
|
|
)
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott asszociációs végpontok alapján generált SQL create //
|
|
// table scripthez tartozóan generál PK constraintet //
|
|
//***************************************************************************//
|
|
template AddPKConstraintToSwitchTable(schemaname, MAssociationEnd as StartRole, MAssociationEnd as EndRole)
|
|
|
|
/*============================================================================*/
|
|
/* [FormatSQLHeader("Table : " ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName ([StartRole],[EndRole]) ))]*/
|
|
/*============================================================================*/
|
|
[if ([schemaname] == [ProjectName])]
|
|
ALTER TABLE [ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole]))] ADD CONSTRAINT [ConvertNameToPrimaryKeyConstraintName(ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole])))] PRIMARY KEY ([ConvertNameToSQLColumnName(ConvertRoleToName([StartRole]))], [ConvertNameToSQLColumnName(ConvertRoleToName([EndRole]))])
|
|
[else]
|
|
ALTER TABLE \[[schemaname]\].[ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole]))] ADD CONSTRAINT [ConvertNameToPrimaryKeyConstraintName(ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole])))] PRIMARY KEY ([ConvertNameToSQLColumnName(ConvertRoleToName([StartRole]))], [ConvertNameToSQLColumnName(ConvertRoleToName([EndRole]))])
|
|
[end if]
|
|
[GetCommandSeparator()]
|
|
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott asszociációs végpontok alapján generál SQL drop //
|
|
// table scriptet //
|
|
//***************************************************************************//
|
|
template ConvertAssociationToDropSwitchTable(MAssociationEnd as StartRole, MAssociationEnd as EndRole)
|
|
drop table [ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName ([StartRole],[EndRole]))] [GetDropTableOptions()]
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott asszociációs végpontot alapján generál SQL insert //
|
|
// utasítást. //
|
|
//***************************************************************************//
|
|
template GenerateInsertCommandForSwitchTable(MAssociationEnd as StartRole, MAssociationEnd as EndRole)
|
|
INSERT INTO [ConvertNameToSQLTableScript(ConvertAssociationRolesToSwitchTableName([StartRole],[EndRole]))] ( [GenerateInsertColumnsForSwitchTable([StartRole],[EndRole])] ) VALUES ( [GenerateInsertValuesForSwitchTable([StartRole],[EndRole])] )
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
//***************************************************************************//
|
|
// Altertable scriptek a generált táblákhoz //
|
|
//***************************************************************************//
|
|
//***************************************************************************//
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján genrál SQL alter table scriptet, //
|
|
// amely foreign key constraintet ad a táblához. //
|
|
//***************************************************************************//
|
|
template AddForeignKeyConstraint(schemaname, foreignschemaname,tablename,pkcname,localkey, foreigntablename, fkname, dictionaryitem, tpc)
|
|
[if ([foreigntablename] == "T_DICTIONARYITEMBASE" && [tablename] != "T_DICTIONARYITEMBASENYELV")]
|
|
ALTER TABLE \[[schemaname]\].[tablename]
|
|
ADD CONSTRAINT [fkname]
|
|
FOREIGN KEY ([localkey], C_ALINTEZMENYID, C_ALTANEVID)
|
|
REFERENCES \[[foreignschemaname]\].[foreigntablename] (ID, C_INTEZMENYID, C_TANEVID)
|
|
[end if]
|
|
|
|
[// azok a táblák, amik közvetlenül a T_DICTIONARYITEMBASE-hez kapcsolódnak, de NEM abból generalizált táblák]
|
|
[if ([foreigntablename] == "T_DICTIONARYITEMBASE" && [tablename] == "T_DICTIONARYITEMBASENYELV")]
|
|
ALTER TABLE \[[schemaname]\].[tablename]
|
|
ADD CONSTRAINT [fkname]
|
|
FOREIGN KEY ([localkey], C_INTEZMENYID, C_TANEVID)
|
|
REFERENCES \[[foreignschemaname]\].[foreigntablename] (ID, C_INTEZMENYID, C_TANEVID)
|
|
[end if]
|
|
[if ([dictionaryitem] == "true" && [tpc] == "false")]
|
|
ALTER TABLE \[[schemaname]\].[tablename]
|
|
ADD CONSTRAINT [fkname]
|
|
FOREIGN KEY ([localkey])
|
|
REFERENCES \[[foreignschemaname]\].[foreigntablename] (ID, C_ALINTEZMENYID, C_ALTANEVID)
|
|
[end if]
|
|
[if ([dictionaryitem] == "true" && [tpc] == "true")]
|
|
ALTER TABLE \[[schemaname]\].[tablename]
|
|
ADD CONSTRAINT [fkname]
|
|
FOREIGN KEY ([localkey])
|
|
REFERENCES \[[foreignschemaname]\].[foreigntablename] (ID, C_INTEZMENYID, C_TANEVID)
|
|
[end if]
|
|
[if ([foreigntablename] != "T_DICTIONARYITEMBASE" && [dictionaryitem] == "false")]
|
|
ALTER TABLE \[[schemaname]\].[tablename]
|
|
ADD CONSTRAINT [fkname]
|
|
FOREIGN KEY ([localkey])
|
|
REFERENCES \[[foreignschemaname]\].[foreigntablename] (ID)
|
|
[end if]
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján genrál SQL alter table scriptet, //
|
|
// amely unique key constraintet ad a táblához. //
|
|
//***************************************************************************//
|
|
template AddUniqueKeyConstraint(tablename,ukname, uniquekeylist)
|
|
IF (SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'[tablename]' AND COLUMN_NAME = 'C_INTEZMENYID' ) > 0 BEGIN
|
|
CREATE UNIQUE INDEX [ukname]_IntezmenyId_TanevId ON [tablename] ([uniquekeylist], C_INTEZMENYID, C_TANEVID) WHERE TOROLT = 'F'
|
|
END
|
|
ELSE IF (SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'[tablename]' AND COLUMN_NAME = 'C_ALINTEZMENYID' ) > 0 BEGIN
|
|
CREATE UNIQUE INDEX [ukname]_AlintezmenyId_AltanevId ON [tablename] ([uniquekeylist], C_ALINTEZMENYID, C_ALTANEVID) WHERE TOROLT = 'F'
|
|
END
|
|
ELSE BEGIN
|
|
CREATE UNIQUE INDEX [ukname] ON [tablename] ([uniquekeylist]) WHERE TOROLT = 'F'
|
|
END
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott foreign key constraint hez generál //
|
|
// SQL alter table drop constraint szkriptet. //
|
|
//***************************************************************************//
|
|
template DropForeignKeyConstraint(schemaname,tablename, pkcname)
|
|
[if ([schemaname] == [ProjectName])]
|
|
alter table [tablename] drop constraint [pkcname]
|
|
[else]
|
|
alter table \[[schemaname]\].[tablename] drop constraint [pkcname]
|
|
[end if]
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott osztály alapján genrál SQL alter table scriptet, //
|
|
// amely unique key constraintet ad a táblához. //
|
|
//***************************************************************************//
|
|
template DropUniqueKeyConstraint(schemaname,tablename,pkcname)
|
|
[if ([schemaname] == [ProjectName])]
|
|
alter table [tablename] drop constraint [pkcname]
|
|
[else]
|
|
alter table \[[schemaname]\].[tablename] drop constraint [pkcname]
|
|
[end if]
|
|
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott foreign key constraint hez generál //
|
|
// SQL alter table drop constraint szkriptet. //
|
|
//***************************************************************************//
|
|
template AddCheckConstraint(schemaname, tablename, pkcname, condition)
|
|
[if ([schemaname] == [ProjectName])]
|
|
alter table [tablename] add constraint [pkcname] check ([condition])
|
|
[else]
|
|
alter table \[[schemaname]\].[tablename] add constraint [pkcname] check ([condition])
|
|
[end if]
|
|
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott táblához generál adott nevű és adott //
|
|
// mezőre vonatkozó index létrehozó szkriptet //
|
|
//***************************************************************************//
|
|
template CreateIndex(schemaname, tablename, indexname, fieldlist)
|
|
[if ([schemaname] == [ProjectName])]
|
|
CREATE INDEX [indexname] ON [tablename] ([fieldlist])
|
|
[else]
|
|
CREATE INDEX [indexname] ON \[[schemaname]\].[tablename] ([fieldlist])
|
|
[end if]
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
template CreateIndexRename(tablename, indexname, indexnameold)
|
|
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = '[indexnameold]') AND NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = '[indexname]') BEGIN
|
|
EXEC sp_rename N'[tablename].[indexnameold]', N'[indexname]', N'INDEX'
|
|
END
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként kapott táblához generál adott nevű és adott //
|
|
// mezőre vonatkozó index törlő szkriptet //
|
|
//***************************************************************************//
|
|
template DropIndex(indexname)
|
|
drop index [indexname]
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|
|
|
|
//***************************************************************************//
|
|
// A paraméterként indexhez generál //
|
|
// index törlő SQL szerverhez szkriptet //
|
|
//***************************************************************************//
|
|
template DropIndexMSSQL(tablename, indexname)
|
|
drop index [tablename].[indexname]
|
|
[GetCommandSeparator()]
|
|
end template
|
|
|