Creation of a Roman Calendar Database: Part 2

Warning: This is a Technical Blog Posting… It is going to get pretty technical. Also please See Part 1 before going farther.

Time to finish this database. First off, I have re-run the script from part 1 for 500 year segments, zipped up the files are just to large to upload into PhpMyAdmin, and I really just don’t want to mess with modifying the settings to allow larger uploads right now. So I am just going to cut the numbers in half and just do more multiple downloads to stay Y3K compliant (yes, the Y3K thing is a joke).  I have also added a “-o” switch to the script because things just look nicer without the optional memorials, don’t worry we’ll bring them in before the end of this tutorial. The new script actually looks like this:

  #!/bin/bash 
        for i in `seq 2014 2514`;
        do
        ./romcal $i -L -a -e >> output.txt
        done  
  #!/bin/bash 
        for i in `seq 2014 2514`;
        do
        ./romcal $i -L -a -e -o >> output-0-500.txt
        done  

  #!/bin/bash 
        for i in `seq 2514 3014`;
        do
        ./romcal $i -L -a -e >> output-2.txt
        done  
  #!/bin/bash 
        for i in `seq 2514 3014`;
        do
        ./romcal $i -L -a -e -o >> output-0-500-2.txt
        done

Ok now if we look at the structure of the file we will see it is sort of semicolon deliminated: Here are the first 7 rows of output-0-500.txt:

Wed Jan  1, 2014:SOLEMNITY:White  :Solemnity of Mary, Mother of God
Thu Jan  2, 2014:Memorial :White  :Basil the Great and Gregory Nazianzen, Bb & Dd
Fri Jan  3, 2014:Weekday  :White  :Friday before Epiphany
Sat Jan  4, 2014:Memorial :White  :Elizabeth Ann Seton
Sun Jan  5, 2014:Sunday   :White  :Second Sunday of Christmas
Mon Jan  6, 2014:SOLEMNITY:White  :Epiphany of the Lord
Tue Jan  7, 2014:Weekday  :White  :Tuesday after Epiphany

Looking at the file format I would honestly say that format is fixed width. Fixed width is the bane of DBA’s everywhere, we always seem to have to use it and sometimes we are doing the craziest things to export and import them.  I think the file can be imported rather simply with some basic CSV fomating trickery, which won’t be too hard… or so I thought. Working with the output-0-500 file I started by separating the 3 character day of the week from the date, by inserting a “:” in the space. Then I did a simple find and replace to turn all semicolons into ‘,’ bulk loaded the file into a ruff table in mySQL from my desktop. After I was done I loaded the output.txt into a second table as well.

Using a simple join I created an alternate name field in the firs table and brought the data into just one table, joining off the date.

Then things went south. I added a field to store a unix timecode and tryied to translate the date field into timecode. I guess my host is a 32 bit server because when I hit January 19th 2038 the strtotime() function in Php prompty started to return “0”. Now I know why fathers website only goes to 2037 he probably ran into a similar problem with the romcal function on a 32 bit computer. Someday I will just have to do all the data in the future on a 64 bit LAMP server.

From here I deleted all data after Jan 19th 2038, just to save time overhead. I then added an sname, 8 character field. This will be for when I need a short name for the feast on the calendar. Doing a couple of group by select queries I got all the Sundays, actually looked at each Sunday name and created an 8 character or less name for them and then using a spreadsheet, find and replace in gedit, I created a buch of simple update scripts to run in myphpadmin. Afterwords I repeated the processes for Solemnities…. Did you get all that? What did I say, way to technical for a blog.

Here is an idea of waht the Data looks like clean and shiney.  screen4-romcal

Let me share the fruits of my labor with you. Here is a SQL file that will create and populate the table into a mySQL database.

After the insanity of this liturgical calendar I am looking forward to getting back to PHP and finishing the second to last section of this first part of the project.

3 responses

  1. Hi. I’m searching code to calculate the liturgical day of current date and the biblical texts for this day. I want show too the current week. You can see an example on left footer of my web site: deiverbum.org

    Thanks.

    • Fr. Cedano,
      It gets mighty complex to add the lectionary to the mix. I will probably tackle it down the road. Right now I would imagine that it would be easier to simply download resources to calculate year by year the readings, maybe sit down and work through the next 3 years (ABC) with overlapping feasts etc to get it to work. Here in the USA I would simply go here: http://www.usccb.org/about/divine-worship/liturgical-calendar/index.cfm and get the next years PDF, convert to Word and reorganize the data into a table and put up a query on the webpage based off the servers date. Another option may be to load the readings into a Google Calendar. It might be cool to start a project that would have a google calendar that spent time figuring out all the dates going in the future and then publically shared the calendar.

Leave a Reply