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