namespace GB5Shared.Query.Framework.DBObject { public static class DBObjectQB { public const string GET_DBOBJECT = @" SELECT -- ===================================== -- 🔵 PARENT : DBOBJECT -- ===================================== DO.DBOBJECTID AS DBObjectId, DO.DBOBJECTNAME AS DBObjectName, DO.MODULEID AS ModuleId, M.MODULECODE AS ModuleCode, M.MODULENAME AS ModuleName, DO.SECTION AS DBObjectSection, DO.DBOBJECTTYPE AS DBObjectType, DO.REMARKS AS DBObjectRemarks, DO.SORTORDER AS DBObjectSortOrder, DO.STATUS AS DBObjectStatus, DO.VERSION AS DBObjectVersion, DO.SOURCETYPE AS DBObjectSourceType, DO.CREATEDBYID AS DBObjectCreatedById, DO.CREATEDON AS DBObjectCreatedOn, DO.MODIFIEDBYID AS DBObjectModifiedById, DO.MODIFIEDON AS DBObjectModifiedOn, DO.ISSTANDARDDATAREQUIRED AS DBObjectIsStandardDataRequired, DO.DATASOURCETYPEID AS DataSourceTypeId, DO.DISPLAYNAME AS DBObjectDisplayName, DO.OBJECTNATURE AS DBObjectObjectNature, DO.TABLETYPE AS DBObjectTableType, DO.ISANALYSIS AS DBObjectIsAnalysis, DO.BASEOBJECTID AS BaseObjectId, BO.DBOBJECTNAME AS BaseObjectName, DO.CONDITION AS DBObjectCondition, DO.ISSYSTEM AS DBObjectIsSystem, DO.ISDEVELOPER AS DBObjectIsDeveloper, DO.ISIMPLEMENTATION AS DBObjectIsImplementation, DO.ISADMIN AS DBObjectIsAdmin, DO.ISUSER AS DBObjectIsUser, DO.EXPRESSION AS DBObjectExpression, DO.VARIABLES AS DBObjectVariables, DO.QUERYTYPE AS DBObjectQueryType, DO.ISARCHIVE AS DBObjectIsArchive, DO.REPLAYUNIT AS DBObjectReplayUnit, DO.REPLAYAMOUNT AS DBObjectReplayAmount, DO.PARENTOBJECTID AS ParentObjectId, PO.DBOBJECTNAME AS ParentObjectName, DO.GROUPDBOBJECTID AS GroupDBObjectId, GDO.DBOBJECTNAME AS GroupDBObjectName, DO.DATEKEY AS DBObjectDateKey, DO.DELETEQUERY AS DBObjectDeleteQuery, DO.ISIDENTITYOBJECT AS DBObjectIsIdentityObject, DO.DELETEQUERYPOSTGRE AS DBObjectDeleteQueryPostgre, DO.SOURCEDBOBJECTKIND AS SourceDbObjectKind, DO.SOURCECLIENTDBID AS SourceClientDbId, CBU.USERNAME AS DBObjectCreatedByName, MBU.USERNAME AS DBObjectModifiedByName, -- ===================================== -- 🔴 CHILD : DBOBJECTFIELDS -- ===================================== DF.DBOBJECTFIELDID AS DBObjectFieldsId, -- 🔥 splitOn column DF.DBOBJECTID AS DBObjectId, DF.SLNO AS DBObjectFieldsSlNo, DF.DBFIELDNAME AS DBObjectFieldsName, DF.REMARKS AS DBObjectFieldsRemarks, DF.DBFIELDATATYPE AS DBObjectFieldsDataType, DF.DBFIELDSIZE AS DBObjectFieldsSize, DF.DBPRECISION AS DBObjectFieldsPrecision, DF.DBSCALE AS DBObjectFieldsScale, DF.SECTION AS DBObjectFieldsSection, DF.ISLOGICALKEY AS DBObjectFieldsIsLogicalKey, DF.ISCRITERIA AS DBObjectFieldsIsCriteria, DF.ISPRIMARYKEY AS DBObjectFieldsIsPrimaryKey, DF.DISPLAYNAME AS DBObjectFieldsDisplayName, DF.COLUMNTYPE AS DBObjectFieldsColumnType, DF.FIELDNATURE AS DBObjectFieldsFieldNature, DF.EXPRESSION AS DBObjectFieldsExpression, DF.VARIABLES AS DBObjectFieldsVariables, DF.DIMENSIONID AS DBObjectFieldsDimensionId, DF.DIMENSIONLEVEL AS DBObjectFieldsDimensionLevel, DF.SORTON AS DBObjectFieldsSortOn, DF.AGGREGATIONTYPE AS DBObjectFieldsAggregationType, DF.AGGREGATION AS DBObjectFieldsAggregation, DF.DIMENSIONWISE AS DBObjectFieldsDimensionwise, DF.ISFILTER AS DBObjectFieldsIsFilter, DF.CRITERIAATTRIBUTEID AS DBObjectFieldsCriteriaAttributeId, DF.ISWRITEBACK AS DBObjectFieldsIsWriteback, DF.ISCURRENCY AS DBObjectFieldsIsCurrency, DF.SOURCETYPE AS DBObjectFieldsSourceType, DF.ISANALYSISFIELDS AS DBObjectFieldsIsAnalysisFields FROM DBOBJECT DO LEFT JOIN MMODULE M ON M.MODULEID = DO.MODULEID LEFT JOIN DBOBJECT BO ON BO.DBOBJECTID = DO.BASEOBJECTID LEFT JOIN DBOBJECT PO ON PO.DBOBJECTID = DO.PARENTOBJECTID LEFT JOIN DBOBJECT GDO ON GDO.DBOBJECTID = DO.GROUPDBOBJECTID LEFT JOIN MUSER CBU ON CBU.USERID = DO.CREATEDBYID LEFT JOIN MUSER MBU ON MBU.USERID = DO.MODIFIEDBYID LEFT JOIN DBOBJECTFIELDS DF ON DF.DBOBJECTID = DO.DBOBJECTID WHERE DO.DBOBJECTID = @dbobjectid;"; // Index: UKDBOBJECT_SOURCE_DBOBJECTNAME on (SOURCEDBOBJECTKIND, SOURCECLIENTDBID, DBOBJECTNAME) public const string GET_DBOBJECT_BY_SOURCE_AND_NAME = @" SELECT DO.DBOBJECTID AS DBObjectId, DO.DBOBJECTNAME AS DBObjectName, DO.MODULEID AS ModuleId, DO.SECTION AS DBObjectSection, DO.DBOBJECTTYPE AS DBObjectType, DO.REMARKS AS DBObjectRemarks, DO.SORTORDER AS DBObjectSortOrder, DO.STATUS AS DBObjectStatus, DO.VERSION AS DBObjectVersion, DO.SOURCETYPE AS DBObjectSourceType, DO.CREATEDBYID AS DBObjectCreatedById, DO.CREATEDON AS DBObjectCreatedOn, DO.MODIFIEDBYID AS DBObjectModifiedById, DO.MODIFIEDON AS DBObjectModifiedOn, DO.ISSTANDARDDATAREQUIRED AS DBObjectIsStandardDataRequired, DO.DATASOURCETYPEID AS DataSourceTypeId, DO.DISPLAYNAME AS DBObjectDisplayName, DO.OBJECTNATURE AS DBObjectObjectNature, DO.TABLETYPE AS DBObjectTableType, DO.ISANALYSIS AS DBObjectIsAnalysis, DO.BASEOBJECTID AS BaseObjectId, DO.CONDITION AS DBObjectCondition, DO.ISSYSTEM AS DBObjectIsSystem, DO.ISDEVELOPER AS DBObjectIsDeveloper, DO.ISIMPLEMENTATION AS DBObjectIsImplementation, DO.ISADMIN AS DBObjectIsAdmin, DO.ISUSER AS DBObjectIsUser, DO.EXPRESSION AS DBObjectExpression, DO.VARIABLES AS DBObjectVariables, DO.QUERYTYPE AS DBObjectQueryType, DO.ISARCHIVE AS DBObjectIsArchive, DO.REPLAYUNIT AS DBObjectReplayUnit, DO.REPLAYAMOUNT AS DBObjectReplayAmount, DO.PARENTOBJECTID AS ParentObjectId, DO.GROUPDBOBJECTID AS GroupDBObjectId, DO.DATEKEY AS DBObjectDateKey, DO.DELETEQUERY AS DBObjectDeleteQuery, DO.ISIDENTITYOBJECT AS DBObjectIsIdentityObject, DO.DELETEQUERYPOSTGRE AS DBObjectDeleteQueryPostgre, DO.SOURCEDBOBJECTKIND AS SourceDbObjectKind, DO.SOURCECLIENTDBID AS SourceClientDbId FROM DBOBJECT DO WHERE DO.DBOBJECTNAME = @DBObjectName AND DO.SOURCEDBOBJECTKIND = @SourceDbObjectKind AND ISNULL(DO.SOURCECLIENTDBID, -1) = ISNULL(@SourceClientDbId, -1);"; public const string GET_SELECTLIST_DBOBJECT_FIELDS = @"SELECT DBOBJECTFIELDID AS Id, DBFIELDNAME AS Name from DBOBJECTFIELDS"; public const string SAVE_DBOBJECT = @"INSERT INTO DBOBJECT ( DBOBJECTID, DBOBJECTNAME, MODULEID, SECTION, DBOBJECTTYPE, REMARKS, SORTORDER, STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, ISSTANDARDDATAREQUIRED, DATASOURCETYPEID, DISPLAYNAME, OBJECTNATURE, TABLETYPE, ISANALYSIS, BASEOBJECTID, CONDITION, ISSYSTEM, ISDEVELOPER, ISIMPLEMENTATION, ISADMIN, ISUSER, EXPRESSION, VARIABLES, QUERYTYPE, ISARCHIVE, REPLAYUNIT, REPLAYAMOUNT, PARENTOBJECTID, GROUPDBOBJECTID, DATEKEY, DELETEQUERY, ISIDENTITYOBJECT, DELETEQUERYPOSTGRE, SOURCEDBOBJECTKIND, SOURCECLIENTDBID ) VALUES ( @DBObjectId, @DBObjectName, @ModuleId, @DBObjectSection, @DBObjectType, @DBObjectRemarks, @DBObjectSortOrder, @DBObjectStatus, @DBObjectVersion, @DBObjectSourceType, @DBObjectCreatedById, @DBObjectCreatedOn, @DBObjectModifiedById, @DBObjectModifiedOn, @DBObjectIsStandardDataRequired, @DataSourceTypeId, @DBObjectDisplayName, @DBObjectObjectNature, @DBObjectTableType, @DBObjectIsAnalysis, @BaseObjectId, @DBObjectCondition, @DBObjectIsSystem, @DBObjectIsDeveloper, @DBObjectIsImplementation, @DBObjectIsAdmin, @DBObjectIsUser, @DBObjectExpression, @DBObjectVariables, @DBObjectQueryType, @DBObjectIsArchive, @DBObjectReplayUnit, @DBObjectReplayAmount, @ParentObjectId, @GroupDBObjectId, @DBObjectDateKey, @DBObjectDeleteQuery, @DBObjectIsIdentityObject, @DBObjectDeleteQueryPostgre, @SourceDbObjectKind, @SourceClientDbId ); "; public const string UPDATE_DBOBJECT = @"UPDATE DBOBJECT SET DBOBJECTNAME = @DBObjectName, MODULEID = @ModuleId, SECTION = @DBObjectSection, DBOBJECTTYPE = @DBObjectType, REMARKS = @DBObjectRemarks, SORTORDER = @DBObjectSortOrder, STATUS = @DBObjectStatus, VERSION = @DBObjectVersion, SOURCETYPE = @DBObjectSourceType, MODIFIEDBYID = @DBObjectModifiedById, MODIFIEDON = @DBObjectModifiedOn, ISSTANDARDDATAREQUIRED = @DBObjectIsStandardDataRequired, DATASOURCETYPEID = @DataSourceTypeId, DISPLAYNAME = @DBObjectDisplayName, OBJECTNATURE = @DBObjectObjectNature, TABLETYPE = @DBObjectTableType, ISANALYSIS = @DBObjectIsAnalysis, BASEOBJECTID = @BaseObjectId, CONDITION = @DBObjectCondition, ISSYSTEM = @DBObjectIsSystem, ISDEVELOPER = @DBObjectIsDeveloper, ISIMPLEMENTATION = @DBObjectIsImplementation, ISADMIN = @DBObjectIsAdmin, ISUSER = @DBObjectIsUser, EXPRESSION = @DBObjectExpression, VARIABLES = @DBObjectVariables, QUERYTYPE = @DBObjectQueryType, ISARCHIVE = @DBObjectIsArchive, REPLAYUNIT = @DBObjectReplayUnit, REPLAYAMOUNT = @DBObjectReplayAmount, PARENTOBJECTID = @ParentObjectId, GROUPDBOBJECTID = @GroupDBObjectId, DATEKEY = @DBObjectDateKey, DELETEQUERY = @DBObjectDeleteQuery, ISIDENTITYOBJECT = @DBObjectIsIdentityObject, DELETEQUERYPOSTGRE = @DBObjectDeleteQueryPostgre, SOURCEDBOBJECTKIND = @SourceDbObjectKind, SOURCECLIENTDBID = @SourceClientDbId WHERE DBOBJECTID = @DBObjectId; "; public const string DELETE_DBOBJECT = @"DELETE FROM DBOBJECT WHERE DBOBJECTID = @dbobjectid; "; public const string GET_SELECTLIST_DBOBJECT = @"SELECT DBOBJECTID AS Id, DBOBJECTNAME AS Name FROM DBOBJECT;"; // Index: IX_DBOBJECTFIELDS_DBOBJECTID on DBOBJECTID public const string DELETE_DBOBJECTFIELDS_BY_PARENT = @" DELETE FROM DBOBJECTFIELDS WHERE DBOBJECTID = @DBObjectId"; // DBOBJECTFIELDID is auto-identity — not included in INSERT; DB generates it. // Note: DB column is DBFIELDATATYPE (single 'A'), not DBFIELDDATATYPE — preserved intentionally. public const string SAVE_DBOBJECTFIELDS = @" INSERT INTO DBOBJECTFIELDS ( DBOBJECTID, SLNO, DBFIELDNAME, REMARKS, DBFIELDATATYPE, DBFIELDSIZE, DBPRECISION, DBSCALE, SECTION, ISLOGICALKEY, ISCRITERIA, ISPRIMARYKEY, DISPLAYNAME, COLUMNTYPE, FIELDNATURE, EXPRESSION, VARIABLES, DIMENSIONID, DIMENSIONLEVEL, SORTON, AGGREGATIONTYPE, AGGREGATION, DIMENSIONWISE, ISFILTER, CRITERIAATTRIBUTEID, ISWRITEBACK, ISCURRENCY, SOURCETYPE, ISANALYSISFIELDS ) VALUES ( @DBObjectId, @DBObjectFieldsSlNo, @DBObjectFieldsName, @DBObjectFieldsRemarks, @DBObjectFieldsDataType, @DBObjectFieldsSize, @DBObjectFieldsPrecision, @DBObjectFieldsScale, @DBObjectFieldsSection, @DBObjectFieldsIsLogicalKey, @DBObjectFieldsIsCriteria, @DBObjectFieldsIsPrimaryKey, @DBObjectFieldsDisplayName, @DBObjectFieldsColumnType, @DBObjectFieldsFieldNature, @DBObjectFieldsExpression, @DBObjectFieldsVariables, @DBObjectFieldsDimensionId, @DBObjectFieldsDimensionLevel, @DBObjectFieldsSortOn, @DBObjectFieldsAggregationType, @DBObjectFieldsAggregation, @DBObjectFieldsDimensionwise, @DBObjectFieldsIsFilter, @DBObjectFieldsCriteriaAttributeId, @DBObjectFieldsIsWriteback, @DBObjectFieldsIsCurrency, @DBObjectFieldsSourceType, @DBObjectFieldsIsAnalysisFields )"; } }