variables.eventsCacheTable = "ptCalendarEventsCache"; variables.componentName = "calEventsCacheDatabaseDAO"; var retStr = "success"; var createTableResult = StructNew(); var dataQry = QueryNew("tmp"); var insertResults = StructNew(); /* IMPORTANT NOTE: THIS TABLE SHOULD NOT BE CREATED IN ANY COMMONSPOT DATASOURCE!!! For the CommonSpot 9.0 demo site, this table gets created in the demo site custom datasource. For customer installs, a custom datasource is strongly suggested. */ createTableResult = createEventsCacheTable(dropTable=true); // Make sure the table was created before trying to add records if ( StructKeyExists(createTableResult,"status") AND createTableResult.status ) { // Get the generated Events records from the Event Element and the Event datetime properties dataQry = application.ptCalendar.calEventsDAO.getEventsUnion( startDate=arguments.startDate ,endDate=arguments.endDate ); // Add the event records to the newly created DB table insertResults = addEventsDataSet(dataSetQry=dataQry); } else retStr = "failed"; return retStr; var retStr = "false"; var tableResult = dropEventsCacheTable(); if ( StructKeyExists(tableResult,"MSG") ) retStr = tableResult.MSG; return retStr; var retQry = QueryNew("tmp"); var qryStruct = {}; var cacheUnit = 'n'; var cacheExpiresValue = 5; var criteriaKey = 'all'; var errorMsg = ''; if ( NOT IsDate(arguments.startDate) ) arguments.startDate = ''; if ( NOT IsDate(arguments.endDate) ) arguments.endDate = ''; if ( TRIM(arguments.startDate) NEQ '' ) { criteriaKey = getNumericDate(arguments.startDate); if ( TRIM(arguments.endDate) NEQ '' ) { criteriaKey = criteriaKey & "_" & getNumericDate(arguments.endDate); } } try { lock timeout="30" name="ptCal_app_eventsQueryCache" type="exclusive" { if ( NOT structKeyExists(application.ptCalendar, "eventsCache") ) application.ptCalendar.eventsCache = {}; if ( NOT structKeyExists(application.ptCalendar.eventsCache, criteriaKey) OR arguments.rebuildEventsQueryCache ) application.ptCalendar.eventsCache[criteriaKey] = {}; // events query Caching if ( isStruct(application.ptCalendar.eventsCache[criteriaKey]) AND NOT StructIsEmpty(application.ptCalendar.eventsCache[criteriaKey]) AND structKeyExists(application.ptCalendar.eventsCache[criteriaKey], 'query') AND IsQuery(application.ptCalendar.eventsCache[criteriaKey].query) AND structKeyExists(application.ptCalendar.eventsCache[criteriaKey], 'expires') AND dateCompare(now(),application.ptCalendar.eventsCache[criteriaKey].expires) EQ -1) { retQry = application.ptCalendar.eventsCache[criteriaKey].query; } else { // Get Events Query with optional startdate and enddate Criteria // ----------------------------------------------------------------- qryStruct = getEventsQueryDirect(startDate=arguments.startDate,endDate=arguments.endDate,rebuildFieldCache=arguments.rebuildFieldCache); if ( TRIM(qryStruct.error_msg) EQ '' ) { retQry = qryStruct.qry; application.ptCalendar.eventsCache[criteriaKey].query = retQry; application.ptCalendar.eventsCache[criteriaKey].expires = createODBCDateTime(DateAdd(cacheUnit,cacheExpiresValue,now())); application.ptCalendar.eventsCache[criteriaKey].recordCount = retQry.recordCount; } else { errorMsg = "Error building the events database query cache:"; errorMsg = errorMsg & "#chr(10)##qryStruct.error_msg#"; errorMsg = errorMsg & "#chr(10)##Server.CommonSpot.UDF.util.structToText(arguments)#"; errorMsg = errorMsg & "#chr(10)##Server.CommonSpot.UDF.util.structToText(qryStruct)#"; application.ADF.log.addLogEntry(message=errorMsg,useDatePrefix=true,wantAllStacks=false); } } } // END: cflock } catch ( any ex ) { errorMsg = "Error getting the events cache database query:"; errorMsg = errorMsg & "#chr(10)##Server.CommonSpot.UDF.util.structToText(arguments)#"; application.ADF.log.addLogEntry(message=errorMsg,CFCatch=ex,useDatePrefix=true,wantAllStacks=false); /* retStruct.status = false; retStruct.msg = cfcatch.message; retStruct.details = cfcatch.detail; // retStruct.cmd = sqlResult.sql; doErrorLogging("App","getEventsQuery",retStruct); */ } return retQry; // Clear the eventCache DB Query lock timeout="30" name="ptCal_app_eventsQueryCache" type="exclusive" { application.ptCalendar.eventsCache = {}; } var dataQry = QueryNew("tmp"); var retStruct = { 'error_msg': '', 'qry': dataQry, 'status': false, 'recordCount': 0 }; var cacheDataSource = getCacheDataSource(); var tableName = variables.eventsCacheTable; var sqlcmdtext = ""; var keyArray = ArrayNew(1); var i = 1; var eventsFieldStruct = {}; var dateTimefieldStruct = {}; var sqlResult = {}; //var cacheDSNExists = verifyDSNExists(dataSource=cacheDataSource); //var cacheTableExists = verifyCacheTableExists(tableName=tableName); if ( NOT IsDate(arguments.startDate) ) arguments.startDate = ''; if ( NOT IsDate(arguments.endDate) ) arguments.endDate = ''; // New Event Field Struct Caching eventsFieldStruct = application.ptCalendar.calEventDetailsDAO.getEventDetailsFieldStruct(rebuildFieldCache=arguments.rebuildFieldCache); // New DateTime Field Struct Caching dateTimefieldStruct = application.ptCalendar.calEventDateTimeDAO.getDateTimeFieldStruct(rebuildFieldCache=arguments.rebuildFieldCache); // Combine both field structs into one StructAppend(eventsFieldStruct, dateTimefieldStruct, false); // Convert the keys from the field struct into an array so we can count the length if ( NOT StructIsEmpty(eventsFieldStruct) ) keyArray = StructKeyArray(eventsFieldStruct); SELECT #keyArray[i]#, FROM #tableName# WHERE 1 = 1 AND CAST( eventDate AS DATE ) BETWEEN CAST( AS DATE ) AND CAST( AS DATE ) AND CAST( eventDate AS DATE ) >= CAST( AS DATE ) retStruct.qry = dataQry; retStruct.recordCount = dataQry.recordCount; retStruct.status = true; retStruct.cmd = sqlResult.sql; retStruct.error_msg = cfcatch.message; retStruct.status = false; retStruct.msg = retStruct.error_msg; retStruct.details = cfcatch.detail; retStruct.cmd = sqlResult.sql; doErrorLogging("App","databaseCache.getEventsQueryDirect",retStruct); var cacheDataSource = getCacheDataSource(); var qTable = QueryNew("temp"); var errStruct = StructNew(); SELECT #arguments.columnFldName# FROM #arguments.tableName# WHERE #arguments.criteriaFldName# = var qCachedData = getCachedFieldValues(tableName=arguments.tableName,criteriaFldName=arguments.criteriaFldName,criteriaValue=arguments.criteriaValue); if ( qCachedData.RecordCount ) return true; else return false; var tableName = variables.eventsCacheTable; var fldName = "calEventID"; return cachedFieldValueExists(tableName=tableName,criteriaFldName=fldName,criteriaValue=arguments.eventID); var dateTimeStamp = Now(); var newEventsQry = QueryNew("temp"); var insertResults = ArrayNew(1); var eID = ""; var e = 1; // Do we have an eventID if ( LEN(TRIM(arguments.eventID)) ) { // Handle the adding of cache records if arguments.eventID is a list of event IDs. for ( e=1; e LTE ListLen(arguments.eventID); e=e+1) { eID = ListGetAt(arguments.eventID,e); // Generate the LIVE unCached events records newEventsQry = application.ptCalendar.calEventsDAO.getEventsUnion( eventID=eID ); // Do we have generated events if ( newEventsQry.RecordCount ) insertResults[e] = addEventsDataSet(dataSetQry=newEventsQry); } if ( ArrayLen(insertResults) ) { application.ptCalendarCache.eventsCacheUpdateDateTime = dateTimeStamp; // Clear the eventsCache DB Query on Add clearEventsQueryCache(); } } return true; var dateTimeStamp = Now(); // Remove events cache records by EventID var deleteEvents = deleteEventCacheRecord( eventID=arguments.eventID ); // Create new events cache records by EventID var addEvents = addEventCacheRecord( eventID=arguments.eventID ); //Updating the timestamp and data is handled by the other add and delete functions // Clear the eventsCache DB Query on update clearEventsQueryCache(); return true; var dateTimeStamp = Now(); var dataQry = QueryNew("temp"); var newEventsQry = QueryNew("temp"); var deleteResults = ArrayNew(1); var fldName = "calEventID"; // Do we have an eventID if ( LEN(TRIM(arguments.eventID)) ) { // Delete Cached event records by EventID deleteResults = deleteEventsData( uniqueFldName=fldName, uniqueIDlist=TRIM(arguments.eventID) ); // If the Delete was a success if ( StructKeyExists(deleteResults,"MSG") AND deleteResults.MSG EQ "success" ) { application.ptCalendarCache.eventsCacheUpdateDateTime = dateTimeStamp; // Clear the eventsCache DB Query on delete clearEventsQueryCache(); return true; } } return false; var dateTimeStamp = Now(); var dataQry = QueryNew("temp"); var newEventsQry = QueryNew("temp"); var deleteResults = ArrayNew(1); var fldName = "dtHashID"; // Do we have an hashid if ( LEN(TRIM(arguments.hashid)) ) { // Delete Cached event records by EventID deleteResults = deleteEventsData( uniqueFldName=fldName, uniqueIDlist=TRIM(arguments.hashid) ); // If the Delete was a success if ( StructKeyExists(deleteResults,"MSG") AND deleteResults.MSG EQ "success" ) { application.ptCalendarCache.eventsCacheUpdateDateTime = dateTimeStamp; // Clear the eventsCache DB Query clearEventsQueryCache(); return true; } } return false; var tableName = variables.eventsCacheTable; return addDataSet(tableName=tableName,dataSetQry=arguments.dataSetQry); var tableName = variables.eventsCacheTable; var doExistsCheck = true; return deleteDataSetByIDlist(tableName=tableName,uniqueFldName=arguments.uniqueFldName,uniqueIDlist=arguments.uniqueIDlist,doExistsCheck=doExistsCheck); var tableName = variables.eventsCacheTable; var dateTimeStamp = Now(); var retStruct = StructNew(); var retStr = "success"; var eventID = ""; var addCacheEventsResults = StructNew(); var deleteCachedEvents = StructNew(); var cachedEventsExist = false; var calEventIDlist = ""; var fldName = "calEventID"; // 1) Get the Event ID from the data set query and put them in a List if ( StructKeyExists(arguments.dataSetQry,"calEventID") ) { //calEventIDlist = ValueList(arguments.dataSetQry.calEventID); for ( itm=1; itm LTE arguments.dataSetQry.RecordCount; itm=itm+1 ){ eventID = arguments.dataSetQry.calEventID[itm]; if ( ListFindNoCase(calEventIDlist,eventID) EQ 0 ) { calEventIDlist = ListAppend(calEventIDlist,eventID); } } } if ( LEN(TRIM(calEventIDlist)) ) { // 2) Using the calEventID list remove ALL of the events cache // - we can CAN NOT Update since the generated recurrence records uniqueIDs and dtHashID will be different and it would leave orphaned recurrence records deleteCachedEvents = deleteDataSetByIDlist(tableName=tableName,uniqueFldName=fldName,uniqueIDlist=calEventIDlist,doExistsCheck=true); } // 3) Insert New Event Record in to the Events Cache addCacheEventsResults = addDataSet(tableName=tableName,dataSetQry=arguments.dataSetQry); // Build the retStruct retStruct = addCacheEventsResults; retStruct.delete = deleteCacheEvents; application.ptCalendarCache.eventsCacheUpdateDateTime = dateTimeStamp; // Clear the eventsCache DB Query clearEventsQueryCache(); return retStruct; var retStruct = StructNew(); var retStr = "success"; var retArray = ArrayNew(1); var insertResults = StructNew(); var rowData = StructNew(); var itm = 1; // Loop over the records in the dataSet query for ( itm=1; itm LTE arguments.dataSetQry.RecordCount; itm=itm+1 ){ rowData = application.ptCalendar.data.queryRowToStruct(query=arguments.dataSetQry,rowNum=itm); insertResults = addDataRow(tableName=arguments.tableName,keyValueStruct=rowData); // Add the insert results to the retArray ArrayAppend(retArray,insertResults); // Check for BAD inserts and and the status to the return struct if ( StructKeyExists(insertResults,"status") AND insertResults.status EQ false ) retStr = "failed: one or more events cache inserts were not successful."; } // Build the retStruct retStruct.msg = retStr; retStruct.results = retArray; return retStruct; var retStruct = StructNew(); var retStr = "success"; var retArray = ArrayNew(1); var updateResults = StructNew(); var rowData = StructNew(); var rowID = ""; var itm = 1; // Loop over the records in the dataSet query for ( itm=1; itm LTE arguments.dataSetQry.RecordCount; itm=itm+1 ) { rowData = application.ptCalendar.data.queryRowToStruct(query=arguments.dataSetQry,rowNum=itm); if ( LEN(TRIM(arguments.uniqueFldName)) AND StructKeyExists(rowData,arguments.uniqueFldName) ) { rowID = rowData[arguments.uniqueFldName]; updateResults = updateDataRow(tableName=arguments.tableName,uniqueFldName=arguments.uniqueFldName,uniqueValue=rowID,keyValueStruct=rowData); // Add the update results to the retArray ArrayAppend(retArray,updateResults); // Check for BAD inserts and and the status to the return struct if ( StructKeyExists(updateResults,"status") AND updateResults.status EQ false ) retStr = "failed: one or more events cache updates were not successful."; } else { retStr = "failed: a unique Field Name was not provided or it was not part of the provided DataSet"; break; } } // Build the retStruct retStruct.msg = retStr; retStruct.results = retArray; return retStruct; var retStruct = StructNew(); var retStr = "success"; var retArray = ArrayNew(1); var deleteResults = StructNew(); var itm = 1; // Make sure the uniqueFldName was provided if ( LEN(TRIM(arguments.uniqueFldName)) ) { // Loop over the records in the dataSet query for ( itm=1; itm LTE ListLen(arguments.uniqueIDlist); itm=itm+1 ){ deleteResults = deleteDataRow(tableName=arguments.tableName,uniqueFldName=arguments.uniqueFldName,uniqueValue=ListGetAt(arguments.uniqueIDlist,itm),doExistsCheck=arguments.doExistsCheck); // Add the delete results to the retArray ArrayAppend(retArray,deleteResults); // Check for BAD inserts and and the status to the return struct if ( StructKeyExists(deleteResults,"status") AND deleteResults.status EQ false ) retStr = "failed: one or more events cache deletes were not successful."; } } else { retStr = "failed: a unique Field Name was not provided"; } // Build the retStruct retStruct.msg = retStr; retStruct.results = retArray; return retStruct; var cacheDataSource = getCacheDataSource(); var retStruct = StructNew(); var qTable = QueryNew("temp"); //var eventsFieldStruct = application.ptCalendar.calEventDetailsDAO.getEventDetailsFieldStruct(); var key = ""; var i = 1; var v = 1; var keyValue = ""; var keyArray = ArrayNew(1); var sqlcmdtext = ""; var fieldExceptionList = "controlID"; // pageID,formID //var textTypeList = "nvarchar2(4000),longtext,nvarchar(4000)"; // Convert the keys from the field struct into an array so we can count the length if ( NOT StructIsEmpty(arguments.keyValueStruct) ) keyArray = StructKeyArray(arguments.keyValueStruct); INSERT INTO #TRIM(arguments.tableName)# ( #TRIM(key)#, ) VALUES ( , ) retStruct.status = true; retStruct.cmd = sqlcmdtext; // Clear the eventsCache DB Query clearEventsQueryCache(); var cacheDataSource = getCacheDataSource(); var retStruct = StructNew(); var qTable = QueryNew("temp"); var key = ""; var i = 1; var v = 1; var keyValue = ""; var keyArray = ArrayNew(1); var sqlcmdtext = ""; var fieldExceptionList = "controlID"; // pageID,formID // Remove the uniqueFld from the keyValueStruct if it exists if ( StructKeyExists(arguments.keyValueStruct,arguments.uniqueFldName) ) StructDelete(arguments.keyValueStruct,arguments.uniqueFldName); // Convert the keys from the field struct into an array so we can count the length if ( NOT StructIsEmpty(arguments.keyValueStruct) ) keyArray = StructKeyArray(arguments.keyValueStruct); UPDATE #TRIM(arguments.tableName)# SET #TRIM(key)# = , WHERE #TRIM(arguments.uniqueFldName)# = retStruct.status = true; // Clear the eventsCache DB Query clearEventsQueryCache(); var cacheDataSource = getCacheDataSource(); var retStruct = StructNew(); var qTable = QueryNew("temp"); var doDelete = false; DELETE FROM #TRIM(arguments.tableName)# WHERE #TRIM(arguments.uniqueFldName)# = retStruct.status = true; retStruct.msg = "Success: Delete was successful"; // Clear the eventsCache DB Query clearEventsQueryCache(); var cacheDataSource = getCacheDataSource(); var dbType = getCacheDBType(); var cacheDSNExists = verifyDSNExists(dataSource=cacheDataSource); var retStruct = StructNew(); var qCreateEventsTable = QueryNew("temp"); var tableName = variables.eventsCacheTable; var sqlcmdtext = ""; var sqlaltercmdtext = ""; var fieldStruct = StructNew(); var eventsFieldStruct = application.ptCalendar.calEventDetailsDAO.getEventDetailsFieldStruct(overrideDBtype=dbType); var dateTimefieldStruct = application.ptCalendar.calEventDateTimeDAO.getDateTimeFieldStruct(overrideDBtype=dbType); var createStruct = StructNew(); var alterStruct = StructNew(); var key = ""; var keyValue = ""; var createArray = ArrayNew(1); var alterArray = ArrayNew(1); var aKey = ""; var aKeyValue = ""; var i = 1; var a = 1; var runCreateTable = false; var runAlterTable = false; if ( dbtype EQ "MySQL" ) { createStruct = Duplicate(dateTimefieldStruct); alterStruct = Duplicate(eventsFieldStruct); // Convert the keys from the createStruct field struct into an array so we can count the length if ( !StructIsEmpty(createStruct) ) createArray = StructKeyArray(createStruct); // Convert the keys from the alterStruct field struct into an array so we can count the length if ( !StructIsEmpty(alterStruct) ) alterArray = StructKeyArray(alterStruct); } else { // Combine both field structs into one StructAppend(eventsFieldStruct, dateTimefieldStruct, false); createStruct = Duplicate(eventsFieldStruct); // Convert the keys from the field struct into an array so we can count the length if ( !StructIsEmpty(createStruct) ) createArray = StructKeyArray(createStruct); } //WriteDump(var=createStruct,label="createStruct",expand=false); //WriteDump(var=createArray,label="createArray",expand=false); //WriteDump(var=alterStruct,label="alterStruct",expand=false); //WriteDump(var=alterArray,label="alterArray",expand=false); CREATE TABLE #tableName# ( #key# #keyValue#, ) ROW_FORMAT = DYNAMIC;; ALTER TABLE #tableName# ADD #aKey# #aKeyValue#, ; //WriteOutput("arguments.dropTable: ");WriteOutput(arguments.dropTable);WriteOutput("
"); //WriteOutput("runCreateTable: ");WriteOutput(runCreateTable);WriteOutput("
"); //WriteOutput("runAlterTable: ");WriteOutput(runAlterTable);WriteOutput("
"); #sqlcmdtext# #sqlaltercmdtext# retStruct.status = true; retStruct.msg = "Success"; retStruct.details = ""; retStruct.cmd = sqlcmdtext; if ( runAlterTable ) retStruct.cmd = retStruct.cmd & " " & sqlaltercmdtext; // Clear the eventsCache DB Query clearEventsQueryCache(); return dropCacheTable(tableName=variables.eventsCacheTable) var cacheDataSource = getCacheDataSource(); var retStruct = StructNew(); var qDropTable = QueryNew("temp"); var sqlcmdtext = ""; var cacheDSNExists = verifyDSNExists(dataSource=cacheDataSource); var cacheTableExists = verifyCacheTableExists(tableName=TRIM(arguments.tableName)); DROP TABLE #TRIM(arguments.tableName)# #sqlcmdtext# retStruct.status = true; retStruct.msg = "Success"; retStruct.details = ""; retStruct.cmd = sqlcmdtext; // Clear the eventsCache DB Query clearEventsQueryCache(); var cacheDataSource = getCacheDataSource(); var cacheDBtype = getCacheDBType(); var cacheDSNExists = verifyDSNExists(dataSource=cacheDataSource); if ( cacheDSNExists ) return application.ptCalendar.data.verifyTableExists(tableName=arguments.tableName,datasourseName=cacheDataSource,databaseType=cacheDBtype); else return false;