Add sqlite3 table creation

3 files changed, 53 insertions, 2 deletions

diff --git a/.gitignore b/.gitignore
index 68504eb..acab611 100644
--- a/.gitignore
+++ b/.gitignore
@@ -130,3 +130,6 @@ dmypy.json
 
 # Don't commit secrets!
 config.py
+
+# don't commit data
+data/
diff --git a/poller/__init__.py b/poller/__init__.py
index 86af046..2d52545 100644
--- a/poller/__init__.py
+++ b/poller/__init__.py
@@ -1,10 +1,57 @@
 """ A small flask Hello World """
 
 import os
-import subprocess
+import threading
+import sqlite3
+import atexit
 
 from flask import Flask, jsonify
 
+POOL_TIME = 5 * 60 # Seconds
+DASHBOARD_URL = 'https://rit.edu/ready/dashboard'
+db_lock = threading.Lock()
+
+if not os.path.exists('./data'):
+    os.mkdir('./data')
+
+def interrupt():
+    global ping_thread
+    ping_thread.cancel()
+
+def create_tables():
+    with db_lock:
+        db_conn = sqlite3.connect('data/data.sqlite3')
+        c = db_conn.cursor()
+        sql = f'CREATE TABLE IF NOT EXISTS `alertlevel` (time DATETIME PRIMARY KEY NOT NULL, color CHAR(50) NOT NULL);'
+        c.execute(sql)
+        sql = f'CREATE TABLE IF NOT EXISTS `total` (time DATETIME PRIMARY KEY NOT NULL, total_students INT NOT NULL, total_staff INT NOT NULL);'
+        c.execute(sql)
+        sql = f'CREATE TABLE IF NOT EXISTS `new` (time DATETIME PRIMARY KEY NOT NULL, new_students INT NOT NULL, new_staff INT NOT NULL);'
+        c.execute(sql)
+        sql = f'CREATE TABLE IF NOT EXISTS `quarantine` (time DATETIME PRIMARY KEY NOT NULL, quarantine_on_campus INT NOT NULL, quarantine_off_campus INT NOT NULL);'
+        c.execute(sql)
+        sql = f'CREATE TABLE IF NOT EXISTS `isolation` (time DATETIME PRIMARY KEY NOT NULL, isolation_on_campus INT NOT NULL, isolation_off_campus INT NOT NULL);'
+        c.execute(sql)
+        sql = f'CREATE TABLE IF NOT EXISTS `isolation` (time DATETIME PRIMARY KEY NOT NULL, isolation_on_campus INT NOT NULL, isolation_off_campus INT NOT NULL);'
+        c.execute(sql)
+        sql = f'CREATE TABLE IF NOT EXISTS `beds` (time DATETIME PRIMARY KEY NOT NULL, beds_available INT NOT NULL);'
+        c.execute(sql)
+        sql = f'CREATE TABLE IF NOT EXISTS `tests` (time DATETIME PRIMARY KEY NOT NULL, tests_administered INT NOT NULL);'
+        c.execute(sql)
+        db_conn.commit()
+        db_conn.close()
+
+def ping_sites():
+    global ping_thread
+    ping_thread = threading.Timer(POOL_TIME, ping_sites, ())
+    ping_thread.start()
+    create_tables()
+    
+ping_sites()
+# When you kill Flask (SIGTERM), clear the trigger for the next thread
+atexit.register(interrupt)
+
+
 APP = Flask(__name__)
 
 # Load file based configuration overrides if present
diff --git a/requirements.txt b/requirements.txt
index 4437399..8b69505 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -2,4 +2,5 @@ Flask==1.1.2
 gunicorn==20.0.4
 pylint==2.6.0
 pylint-quotes==0.2.1
-requests==2.24.0
\ No newline at end of file
+requests==2.24.0
+beautifulsoup4==4.9.3
\ No newline at end of file