This repository contains the files to run tests against an existing PostgreSQL database.
For an example of an output of the reportGen.py file, click here
The reportGen.py file utilizes three queries to generate reports for the following three questions:
- What are the most popular three articles of all time?
topArticles.sql - Who are the most popular article authors of all time?
topAuthors.sql - On which days did more than 1% of requests lead to errors?
totalErrors.sql
NOTE: Before running the reportGen.py file, there are some required views. NOTE: Modify the reportGen.py file with your user and password.
The python script expects the following views to exist within the database:
CREATEVIEWvw_httpStatusErrorASSELECTcount(*), status, DATE(TIME), SUBSTRING(status, 1, 3) as httpStatus FROM log WHERE status ='404 NOT FOUND'GROUP BY status, DATE(TIME) ORDER BYDATE(TIME)CREATEVIEWvw_httpStatusOKASSELECTcount(*), status, DATE(TIME), SUBSTRING(status, 1, 3) as httpStatus FROM log WHERE status ='200 OK'GROUP BY status, DATE(TIME) ORDER BYDATE(TIME)CREATEVIEWvw_topArticlesASSELECT DISTINCTcount(*), A.title, FORMAT('"%s" - %s views', A.title, count(*)) AS POPULAR FROM log l INNER JOIN articles A ONSUBSTRING(l.path, 10, 100) =A.SLUGGROUP BYA.titleORDER BYcount(*) descLIMIT3CREATEVIEWvw_topAuthorsASSELECT DISTINCTcount(*), AU.name, FORMAT('%s - %s views', AU.name, count(*)) AS POPULAR FROM log l INNER JOIN articles A ONSUBSTRING(l.path, 10, 100) =A.SLUGINNER JOIN Authors AU ONAU.ID=A.authorGROUP BYAU.nameORDER BYcount(*) desc