Talk to Coyote Why Should I? Who is Coyote? Why Linux? More Stories

How Coyote Saved the Day

Once a very worried man came to see Coyote. "I have hired Rat to host my websites. He has built a number for me and hosts them in his office, on his server." "Why does he not use a cloud hosting company?" asked Coyote. "I do not know," said the worried man. "One of the sites is an eLearning site. Thousands of my customers use this site to get certifications on the hundreds of different courses we offer. These certifications are required for some of them to do their jobs and pass their accreditations. There are trade organizations involved. Even the FDA." "OK," said Coyote, "What is the problem?"

"Rat is angry at me," said the man, "we are in litigation. He has taken all of my websites down. It even turned out that he owns my web domains. He bought them on my behalf and now he has stolen them." "Oh dear," said Coyote.

"The big problem," the man continued, "is that he has stolen all the data in the eLearning site. Thousands of student records, the records of which courses they passed, how well they scored on their exams, which learning materials they accessed, it is ALL GONE!" "Uh oh," said Coyote, "I'm not sure there is anything to be done, if Rat cannot be made to give the data back."

"Well it's not COMPLETELY gone," said the man, "I will show you what I have."

The man showed Coyote a folder with hundreds of Excel spread sheets in it. "These are reports we exported of our student database, showing the grades students got in each class. I had some of my employees start doing them when Rat started getting angry." The man opened another folder with dozens of spreadsheets. "These are the courses themselves," he said, "including the exams. We made them in Excel and then put them into the software." There were more and more documents with course descriptions, student test results, student account information. "We exported everything we could," said the man, "we didn't know how to do it, other than by making these Excel reports." "OK," said Coyote, "I will see what I can do, but this is not a small project."

It turned out to be a very big project, and it wasn't easy. First, Coyote transformed all of the spreadsheets into CSV files (that stands for Comma Seperated Values). Then Coyote deployed the database that the eLearning software used (it was a MySQL database) and started to look through it and read the documentation.

The documentation was very poor and the database was highly abstracted, and also highly redundant (for example, Coyote found a value that needed to be stored in four different tables in order for the application to work properly). One was meant to interact with the database through an API that would abstract the SQL (that stands for Structured Query Language, which is a programming language for talking to databases) away, so that programmers wouldn't have to deal with it and could just interact with Objects (Like "Student" or "Course"). But the API couldn't really handle a project this complicated (or if it could, that wasn't very well documented). So Coyote spent several weeks going through the database piece by piece, learning what the different parts of it were for and experementing with the application to see what happened.

The man got more and more anxious. His customers kept calling him, because they needed the certificates that proved they had passed their courses. It was a big problem.

Meanwhile, Coyote had been writing scripts in bash. He had to use tools like Awk and Sed and Regex to parse this massive amount of data. When the scripts wrote a record into the database, they then needed to be able to find that record to get it's identifier (called a Primary Key) so that they could write the correct references into all of these highly abstracted tables. And Coyote kept finding new parts of the database. There were tables called things like "Student_Course_Grade_Quiz_Question_Answer" which needed to store the Answer a Student had given on a Question which was part of a Quiz so the application could add them all up and calculate the student's Grade. The script had to make sure that the grades calculated by this process matched the Grade from the spreadsheets the man had given to Coyote at the beginning, and if they didn't, it had to give Coyote an error message so he could figure out what was wrong. There were many problems like this that needed to be solved.

Then one day, Coyote called the man. "Imports are working," he said, "I have rebuilt the database."

The man was amazed and relieved. He actually hadn't expected Coyote to be able to do it (Hope for the best, prepare for the worst). But Coyote is persistent and clever.

Back to Stories