PLEASE READ: how to add new API and write query

[superliuxz]

Once GH-23 is merged, every1 should be able to add new APIs to the backend, when you want to have a new plot.

Here is how to do it:

• To add an endpoint:
  • in app.py add the handler function with this decorator @app.route('<YOUR API HERE>', methods=['GET']). I don't think for this assignment we need any other requests than GET, and we won't need query parameter (i.e. the handler function shouldn't take any args).
  • In side the handler function, write the query, and call execute_query. You can print it but make sure that your handler function returns something.
  • For example:

    @app.route('/my-endpoint', methods=['GET'])
    def my_ep_handler():
    query = '''select * from table'''
    result = execute_query(query)
    print(result)
    return {'status': 200}

  • once you are happy, delete the print, and return the query result according the API specification. Remember to call jsonify on the query result.

If your query is running fast, great. Create the PR. However if you are doing some slightly complicated query, you might encounter some performance issue. Here is how to debug them.

• Prepend

  EXPLAIN QUERY PLAN

  to your SQL query. I.e.:

  EXPLAIN QUERY PLAN
  SELECT COUNT(*), Rating FROM ratings GROUP BY Rating

  and execute it (I recommend using https://sqlitebrowser.org/).

• Look at the output. Try to understand them (by googling).

  • For example, if you see (you will be likely to see this one, TEMP B-TREE, if your query is slow):

  id	parent	notused	detail
  6	0	0	SCAN TABLE ratings
  8	0	0	USE TEMP B-TREE FOR GROUP BY

  this means that the group by key, Rating, is not indexed, hence the database is creating a temporary B-tree to store the index, then use the temp index to group by.

  • The solution? Creating an index on Rating:

    CREATE INDEX ratings_Rating_index ON ratings(Rating)

  • Now rerun the EXPLAIN QUERY PLAN SQL, you will be seeing:

  id	parent	notused	detail
  6	0	0	SCAN TABLE ratings USING COVERING INDEX ratings_Rating_index

  This means the database is using the existing index ratings_Rating_index to execute the query, and your query runtime should be faster now.