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 cacheDataSource = getCacheDataSource(); var qTable = QueryNew("temp"); var tableName = variables.eventsCacheTable; var sqlcmdtext = ""; var eventsFieldStruct = application.ptCalendar.calEventDetailsDAO.getEventDetailsFieldStruct(); var dateTimefieldStruct = application.ptCalendar.calEventDateTimeDAO.getDateTimeFieldStruct(); var keyArray = ArrayNew(1); var i = 1; // 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# #sqlcmdtext# 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; } 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 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; 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; 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 = "pageID,controlID,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 ( , ) 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 = "pageID,controlID,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)# = var cacheDataSource = getCacheDataSource(); var retStruct = StructNew(); var qTable = QueryNew("temp"); var doDelete = false; DELETE FROM #TRIM(arguments.tableName)# WHERE #TRIM(arguments.uniqueFldName)# = 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# 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# 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;