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.
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.