Where is the calendar data located in the database for custom querying

I was wondering where the data that populates the CM1 calendar is located in the database. The RSS feed isn’t going to meet my needs so I need to write a custom query to pull data from the calendar table(s).

Hi Matt,

To perform a query similar to what the calendar widget does, on a very high level (the full SQL query would be fairly complex), you would need to query the PERC_PAGE_METADATA table in your DTS database (not your Percussion application database, but the Derby database that lives on your web server), and pull in all items whose “TYPE” column equals “page”. And then you would query your PERC_PAGE_METADATA_PROPERTIES table and find all items whose “STRINGVALUE” column matches the name of your calendar.

Once you have this, you would compare results and return only the items whose “PAGEPATHHASH” and “ENTRY_ID” match from each table. The remaining items would contain the meta-information relating to all of the pages that are assigned to that particular calendar (page title, link path, etc.).

I would be curious to know exactly what your goals are here, as there might be a simpler solution.

Nathaniel,

I’ve looked through my MS SQL database and do not see any tables starting with PERC_ . Any ideas?

Hey Matt,

As I mentioned, all of this will be contained within your DTS Derby database, not your App database.

Nathaniel,

So I’ve been trying to work with querying the DTS database but I’m completely lost on it. I’m hoping I can explain what I have going on and you can possibly shoot me in the right direction.

Currently we have 5 calendars. All the calendars reside in different area’s. Example:
/root_url/calendar (main calendar)
/root_url/academics/college_1/calendar
/root_url/academics/college_2/calendar
/root_url/academics/college_3/calendar
/root_url/academics/college_4/calendar

They are all use the same template pages. (I was going to use a custom widget but all fields are required right now and not all fields on our calendar events are required.)

I’m needing to show on our homepage the 6 calendar items closest to the current date.

I’m not sure if I’m just not seeing what I need to do but I would appreciate any insight.

Hey Matt,

Have you successfully connected to your DTS database yet to see what’s in there? If you’re set on going down this route, it would make your life much easier to connect your DTS Tomcat instance to a SQL Server database (instead of the default Derby database), as discussed in this thread:

https://community.percussion.com/t/how-to-configure-dts-to-use-ms-sql-server-as-its-repository/1081

This way you will be able to query the DTS database as you typically would using ASPX.

Nathaniel,

I’ve been successful in querying the two database tables that you mentioned in a previous post with no problems. The only thing I’m not seeing is the dates for the calendar event. Do you happen to know what table that is stored in?

Hey Matt,

Sure. Once you’ve identified the ENTRY_ID of the page you’re looking for in PERC_PAGE_METADATA_PROPERTIES, query that table WHERE name = ‘perc:start_date’ AND ENTRY_ID = ‘Entry ID of page’. This will return the date for when the event is scheduled to begin. There will be multiple rows in this table for the each ENTRY_ID, and each row (identified by the “name” column) will contain additional useful metadata information about the page.

Thank Nathaniel. I’ve gotten two select statement written now. Now it’s time to try to combine them into one. Let the fun being.

I’ve written this code so far but it’s not returning my start_date or end_date. Any ideas?

SELECT PERC\_PAGE\_METADATA.linktext, PERC\_PAGE\_METADATA.pagepath,  
MAX(CASE WHEN PERC\_PAGE\_METADATA\_PROPERTIES.name = 'perc:start\_date' THEN PERC\_PAGE\_METADATA\_PROPERTIES.datevalue END) AS StartDate,  
MAX(CASE WHEN PERC\_PAGE\_METADATA\_PROPERTIES.name = 'perc:end\_date' THEN PERC\_PAGE\_METADATA\_PROPERTIES.datevalue END) AS EndDate   
FROM PERC\_PAGE\_METADATA LEFT OUTER JOIN   
PERC\_PAGE\_METADATA\_PROPERTIES ON PERC\_PAGE\_METADATA.pagepathHash = PERC\_PAGE\_METADATA\_PROPERTIES.ENTRY\_ID   
WHERE (PERC\_PAGE\_METADATA\_PROPERTIES.stringvalue = 'university-calendar')   
GROUP BY PERC\_PAGE\_METADATA.linktext, PERC\_PAGE\_METADATA.pagepath   
 

Hey Matt,

Just curious, have you made any progress with this one? I haven’t had time to look at your SQL query yet; was it returning other data but not the date fields, or was nothing (or an error) coming back?

I get data for everything but the start and end dates. I’ve even simplified the code to just pull results that have perc:end_date and perc:start_date and nothing else and there is data in the db with those values so I know something should be returning.

Matt,

Again I haven’t had any time to dive into this deeply, but I did notice that in your WHERE clause you are requiring that the row returned has a “stringvalue” equal to “university-calendar”. With the way the tables are being joined, this is filtering out the two rows identified with the “name” of “perc:start_date” and “perc:end_date”, which contain that date values you’re looking for.

For example, if you select everything that matches your query parameters, you’ll see that the rows returned have a NULL value for the “datevalue” column:

SELECT \* FROM PERC\_PAGE\_METADATA LEFT OUTER JOIN 
 PERC\_PAGE\_METADATA\_PROPERTIES ON PERC\_PAGE\_METADATA.pagepathHash = PERC\_PAGE\_METADATA\_PROPERTIES.ENTRY\_ID 
 WHERE (PERC\_PAGE\_METADATA\_PROPERTIES.stringvalue = 'university-calendar') 
 GROUP BY PERC\_PAGE\_METADATA.linktext, PERC\_PAGE\_METADATA.pagepath

Whereas if you expand your WHERE clause to also look in for these two additional rows, you’ll then see the datevalues:

SELECT \* FROM PERC\_PAGE\_METADATA RIGHT OUTER JOIN 
 PERC\_PAGE\_METADATA\_PROPERTIES ON PERC\_PAGE\_METADATA.pagepathHash = PERC\_PAGE\_METADATA\_PROPERTIES.ENTRY\_ID 
 WHERE PERC\_PAGE\_METADATA\_PROPERTIES.name = 'perc:start\_date' 
 OR PERC\_PAGE\_METADATA\_PROPERTIES.name = 'perc:end\_date' 
 OR PERC\_PAGE\_METADATA\_PROPERTIES.stringvalue = 'university-calendar'

Of course, this isn’t how you want your query to be structured as it brings in redundant and irrelevant content in three separate rows; I’m just sharing my findings with you. Hopefully this will steer you in the right direction.

Matt,

Scratch that, I may have inadvertently solved your issue while breaking it down just now. I just looked over your query again and realized this might do the trick for you:

SELECT PERC\_PAGE\_METADATA.linktext, PERC\_PAGE\_METADATA.pagepath, 
 MAX(CASE WHEN PERC\_PAGE\_METADATA\_PROPERTIES.name = 'perc:start\_date' THEN PERC\_PAGE\_METADATA\_PROPERTIES.datevalue END) AS StartDate, 
 MAX(CASE WHEN PERC\_PAGE\_METADATA\_PROPERTIES.name = 'perc:end\_date' THEN PERC\_PAGE\_METADATA\_PROPERTIES.datevalue END) AS EndDate 
 FROM PERC\_PAGE\_METADATA LEFT OUTER JOIN 
 PERC\_PAGE\_METADATA\_PROPERTIES ON PERC\_PAGE\_METADATA.pagepathHash = PERC\_PAGE\_METADATA\_PROPERTIES.ENTRY\_ID 
 WHERE PERC\_PAGE\_METADATA\_PROPERTIES.name = 'perc:start\_date' 
 OR PERC\_PAGE\_METADATA\_PROPERTIES.name = 'perc:end\_date' 
 OR PERC\_PAGE\_METADATA\_PROPERTIES.stringvalue = 'university-calendar' 
 GROUP BY PERC\_PAGE\_METADATA.linktext, PERC\_PAGE\_METADATA.pagepath

Nathaniel,

That’s pulling back results with start & end dates but it’s showing items that are not part of our university-calendar.

Good catch. I tested this on a DB that only had one calendar, so I got a bit of a false positive. I guess we’re not quite there after all. If you’re still unsure how to proceed, I recommend posting this on a forum such as stackoverflow, as you’ll be reaching a much wider audience with greater SQL Server experience: http://stackoverflow.com/questions/ta…

Of course, when you do get this SQL code ironed out, please come back and share it with us here!

Hey Nathaniel,

Here is the code I ended up using and it returns exactly what I was need.

select linktext, pagepath, (select dateValue from PERC\_PAGE\_METADATA\_PROPERTIES where ENTRY\_ID=p.ENTRY\_ID and name='perc:start\_date') as StartDate, (select dateValue from PERC\_PAGE\_METADATA\_PROPERTIES where ENTRY\_ID=p.ENTRY\_ID and name='perc:end\_date') as EndDate   
from PERC\_PAGE\_METADATA d,PERC\_PAGE\_METADATA\_PROPERTIES p   
where d.pagepathHash=p.ENTRY\_ID and stringvalue='university-calendar'   

Very nice! Thanks for following-up.