After I managed to display a single report last week I have improved/changed a great part of the code. Robert reviewed my querying script, and he pointed out some mistakes, that I fixed on tuesday. To sum up the changes in the querying script:
- The script now has a test mode, where a different query file is loaded. These queries have limits (” | head 10000 …”), so they run quickly and we can easily check if something is wrong.
- The querying script also has error handling now
- Now there is a commit every time we store some results in the database
- If something goes wrong there is a rollback
- Filtering of “private” key-value pairs in Splunks output is now done in the querying script. Filtering on perl’s side just didn’t make sense
- The JSON data stored in the db doesn’t have a top-level array any more. It is an interesting security risk, that probably wouldn’t affect us, but better safe than sorry. Of course the other part of the code had to be changed as well.
Many things changed on the perl side too. Dates in the db are now parsed as DateTime. There still seems to be a bit of an error when displaying them though. That will be fixed soon.
In the second part of the week I worked on looking up mbids, and turning them into entity names. This is also done in the python script. The script takes the result of a report, finds mbids in it, then makes an array from them, and does a querry like this:
select x.gid, x_n.name from %s x, %s x_n where x.gid in %s and x.name = x_n.id;
The SQL query looks for mbids in the array, with “in %s”. This is done for every table that holds entity data (artist, release, recording, etc.). After mbid-name pairs are collected, we put the names back to the JSON. Then the JSON is finally stored in the database.
After that I started working on the display a bit. I changed the format of the table stuff are displayed in, and I added categories. I changed the schema of the table, and added a ‘category’ column. The category of a report is read from the YAML file. Each category is displayed under a <h1> header now, but I plan on adding tabs.
Next week I will consult with Robert, and do more work on display. I need to create charts, and find a better way to display other report types. (We shouldn’t display reports that return a single number in a table)
The code should be pushed to github now.