Revision 7b67b39a
Added by dsorber almost 12 years ago
| software/bookmark_library/lib/bookmark.py | ||
|---|---|---|
|
import datetime
|
||
|
import re
|
||
|
from urllib.error import HTTPError, URLError
|
||
|
from urllib.request import urlopen
|
||
|
|
||
|
import dateutil.parser
|
||
|
import sqlite3
|
||
|
|
||
|
from tag import Tag
|
||
|
|
||
|
|
||
|
DB_PATH = 'bookmarks.db'
|
||
|
|
||
|
SQL_SELECT_BOOKMARK = "SELECT * FROM bookmarks WHERE id=?"
|
||
|
SQL_INSERT_BOOKMARK = "INSERT INTO bookmarks VALUES (?,?,?,?,?,?,?,?,?)"
|
||
|
SQL_UPDATE_BOOKMARK = "UPDATE bookmarks SET {:s} WHERE id=?"
|
||
|
SQL_SELECT_CAT_TAGS = "SELECT tag_id FROM category_tags WHERE bookmark_id=?"
|
||
|
SQL_SELECT_DESC_TAGS = "SELECT tag_id FROM description_tags WHERE bookmark_id=?"
|
||
|
|
||
|
SQL_DELETE_CAT_TAGS = "DELETE FROM category_tags WHERE bookmark_id=?"
|
||
|
SQL_DELETE_DESC_TAGS = "DELETE FROM description_tags WHERE bookmark_id=?"
|
||
|
|
||
|
SQL_INSERT_CAT_TAGS = "INSERT INTO category_tags VALUES (?, ?)"
|
||
|
SQL_INSERT_DESC_TAGS = "INSERT INTO description_tags VALUES (?, ?)"
|
||
|
|
||
|
HTTP_OKAY = 200
|
||
|
|
||
|
class BookmarkNotFoundError(Exception):
|
||
|
|
||
|
def __init__(self, value):
|
||
|
self.value = value
|
||
|
|
||
|
def __str__(self, value):
|
||
|
return repr(self.value)
|
||
|
|
||
|
|
||
|
class DBConnectionError(Exception):
|
||
|
|
||
|
def __init__(self, value):
|
||
|
self.value = value
|
||
|
|
||
|
def __str__(self, value):
|
||
|
return repr(self.value)
|
||
|
|
||
|
|
||
|
def _format_date(date):
|
||
|
if date is not None:
|
||
|
return date.isoformat()
|
||
|
else:
|
||
|
return date
|
||
|
|
||
|
|
||
|
class Bookmark(object):
|
||
|
|
||
|
def __init__(self, db_conn, id=None, url=None):
|
||
|
|
||
|
# Make sure the DB connection is valid before doing anything else
|
||
|
if not db_conn:
|
||
|
raise DBConnectionError('Invalid database connection object!')
|
||
|
|
||
|
# Setup internal parameters
|
||
|
self._db_conn = db_conn
|
||
|
self._content = None
|
||
|
|
||
|
# Setup defaut object data attributes
|
||
|
self.id = id
|
||
|
self.url = url
|
||
|
self.title = ''
|
||
|
self.description = ''
|
||
|
self.times_visited = 0
|
||
|
self.last_visited = None
|
||
|
self.last_reachable = None
|
||
|
self.date_added = None
|
||
|
self.deleted = False
|
||
|
self.category_tags = set()
|
||
|
self.description_tags = set()
|
||
|
|
||
|
# Retrieve an existing bookmark if an ID is passed in
|
||
|
if self.id is not None:
|
||
|
db = self._db_conn.cursor()
|
||
|
db.execute(SQL_SELECT_BOOKMARK, (self.id,))
|
||
|
row = db.fetchone()
|
||
|
|
||
|
# Check to see if a record was found
|
||
|
if not row:
|
||
|
raise BookmarkNotFoundError(self.id)
|
||
|
|
||
|
# Assign data attributes
|
||
|
self.url = row[1]
|
||
|
self.title = row[2]
|
||
|
self.description = row[3]
|
||
|
self.times_visited = int(row[4])
|
||
|
if row[5]:
|
||
|
self.last_visited = dateutil.parser.parse(row[5])
|
||
|
if row[6]:
|
||
|
self.last_reachable = dateutil.parser.parse(row[6])
|
||
|
self.date_added = dateutil.parser.parse(row[7])
|
||
|
if int(row[8]) == 0:
|
||
|
self.deleted = False
|
||
|
else:
|
||
|
self.deleted = True
|
||
|
|
||
|
# Grab category tags
|
||
|
for row in db.execute(SQL_SELECT_CAT_TAGS, (self.id,)):
|
||
|
self.category_tags.add(Tag(int(row[0])))
|
||
|
|
||
|
# Grab description tags
|
||
|
for row in db.execute(SQL_SELECT_DESC_TAGS, (self.id,)):
|
||
|
self.description_tags.add(Tag(int(row[0])))
|
||
|
|
||
|
# Store hash of tag sets for dirty comparison
|
||
|
self._cat_tags_hash = hash(frozenset(self.category_tags))
|
||
|
self._desc_tags_hash = hash(frozenset(self.description_tags))
|
||
|
|
||
|
# This dict MUST appear *after* the data attributes, it is used to
|
||
|
# record which data attributes are dirty
|
||
|
self._dirty = {'url': False,
|
||
|
'title': False,
|
||
|
'description': False,
|
||
|
'times_visited': False,
|
||
|
'last_visited': False,
|
||
|
'last_reachable': False,
|
||
|
'deleted': False}
|
||
|
|
||
|
def __del__(self):
|
||
|
""" Automatically save any changes to a record before the object is
|
||
|
destroyed.
|
||
|
"""
|
||
|
self.save()
|
||
|
|
||
|
def save(self):
|
||
|
""" Save any changes to a bookmark record if it is dirty. """
|
||
|
# Check if the record is dirty
|
||
|
if self.record_dirty:
|
||
|
if self.id:
|
||
|
# Update existing bookmark record
|
||
|
self._db_update()
|
||
|
else:
|
||
|
# Insert new bookmark record
|
||
|
self._db_insert()
|
||
|
|
||
|
def __setattr__(self, name, value):
|
||
|
""" Customize setting data attributes so we can tell which ones
|
||
|
are dirty.
|
||
|
"""
|
||
|
# The date_added attribute is immutable once it has been set
|
||
|
if hasattr(self, 'date_added') and name == 'date_added':
|
||
|
if getattr(self, name) is not None:
|
||
|
return
|
||
|
|
||
|
# Check if the attr is a data attribute then check if the value has
|
||
|
# changed. If so, mark the attribute as dirty
|
||
|
if hasattr(self, '_dirty'):
|
||
|
if name in self._dirty and getattr(self, name) != value:
|
||
|
self._dirty[name] = True
|
||
|
|
||
|
# Set the attribute value using the super class's __setattr__ to avoid
|
||
|
# infinite recursion (recursion joke here)
|
||
|
super(Bookmark, self).__setattr__(name, value)
|
||
|
|
||
|
def _db_insert(self):
|
||
|
""" Insert a new bookmark record into the database. """
|
||
|
self.date_added = datetime.datetime.now()
|
||
|
|
||
|
db = self._db_conn.cursor()
|
||
|
values = (None, self.url, self.title, self.description,
|
||
|
self.times_visited, _format_date(self.last_visited),
|
||
|
_format_date(self.last_reachable),
|
||
|
_format_date(self.date_added),
|
||
|
self.deleted and '1' or '0')
|
||
|
db.execute(SQL_INSERT_BOOKMARK, values)
|
||
|
|
||
|
# Set bookmark ID after insert
|
||
|
self.id = db.lastrowid
|
||
|
|
||
|
# Insert any category tags
|
||
|
for tag in self.category_tags:
|
||
|
tag.save()
|
||
|
db.execute(SQL_INSERT_CAT_TAGS, (tag.id, self.id))
|
||
|
|
||
|
# Insert any description tags
|
||
|
for tag in self.description_tags:
|
||
|
tag.save()
|
||
|
db.execute(SQL_INSERT_DESC_TAGS, (tag.id, self.id))
|
||
|
|
||
|
self._db_conn.commit()
|
||
|
|
||
|
# Reset dirty flags
|
||
|
for key in self._dirty.keys():
|
||
|
self._dirty[key] = False
|
||
|
|
||
|
def _db_update(self):
|
||
|
""" Update an exisitng bookmark record in the database. """
|
||
|
values = []
|
||
|
sets = []
|
||
|
|
||
|
# Find dirty data attributes for updating
|
||
|
for key in self._dirty.keys():
|
||
|
if self._dirty[key] == True:
|
||
|
sets.append('{:s}=?'.format(key))
|
||
|
if key in ['last_reachable', 'last_visited']:
|
||
|
# Datetime objects need to be formated before update
|
||
|
values.append(_format_date(getattr(self, key)))
|
||
|
else:
|
||
|
values.append(getattr(self, key))
|
||
|
|
||
|
db = self._db_conn.cursor()
|
||
|
if values and sets:
|
||
|
# Add the record id as the last value
|
||
|
values.append(self.id)
|
||
|
|
||
|
# Build full SQL statement then execute it
|
||
|
sql = SQL_UPDATE_BOOKMARK.format(','.join(sets))
|
||
|
db.execute(sql, values)
|
||
|
|
||
|
# Reset dirty flags
|
||
|
for key in self._dirty.keys():
|
||
|
self._dirty[key] = False
|
||
|
|
||
|
# Inner function FTW
|
||
|
def update_tag_lists(delete_sql, insert_sql, tags):
|
||
|
db.execute(delete_sql, (self.id,))
|
||
|
tag_updates = []
|
||
|
for tag in tags:
|
||
|
tag.save()
|
||
|
tag_updates.append((tag.id, self.id))
|
||
|
db.executemany(insert_sql, tag_updates)
|
||
|
|
||
|
# Update tag lists if they are dirty
|
||
|
if self.category_tags_dirty:
|
||
|
update_tag_lists(SQL_DELETE_CAT_TAGS, SQL_INSERT_CAT_TAGS,
|
||
|
self.category_tags)
|
||
|
if self.description_tags_dirty:
|
||
|
update_tag_lists(SQL_DELETE_DESC_TAGS, SQL_INSERT_DESC_TAGS,
|
||
|
self.description_tags)
|
||
|
|
||
|
self._db_conn.commit()
|
||
|
|
||
|
@property
|
||
|
def category_tags_dirty(self):
|
||
|
return self._cat_tags_hash != hash(frozenset(self.category_tags))
|
||
|
|
||
|
@property
|
||
|
def description_tags_dirty(self):
|
||
|
return self._desc_tags_hash != hash(frozenset(self.description_tags))
|
||
|
|
||
|
@property
|
||
|
def record_dirty(self):
|
||
|
""" Check if any data attributes are dirty, which indicates the record
|
||
|
is dirty.
|
||
|
"""
|
||
|
dirty = [val for key, val in self._dirty.items()]
|
||
|
dirty.append(self.category_tags_dirty)
|
||
|
dirty.append(self.description_tags_dirty)
|
||
|
return any(dirty)
|
||
|
|
||
|
def _get_content(self):
|
||
|
""" Read the page and get its raw page HTML content. """
|
||
|
if self.url:
|
||
|
try:
|
||
|
response = urlopen(self.url)
|
||
|
if response.getcode() == HTTP_OKAY:
|
||
|
content = response.read()
|
||
|
try:
|
||
|
self._content = content.decode('utf-8')
|
||
|
except UnicodeDecodeError:
|
||
|
self._content = content.decode('ISO-8859-1')
|
||
|
except (HTTPError, URLError):
|
||
|
return ''
|
||
|
|
||
|
def suggest_title(self):
|
||
|
""" Return the contents of the page's <title> tag as a title suggestion
|
||
|
"""
|
||
|
if self.url and not self._content:
|
||
|
self._get_content()
|
||
|
|
||
|
try:
|
||
|
title = re.search('<title>(.+)</title>', self._content).group(1)
|
||
|
except (IndexError, AttributeError, TypeError):
|
||
|
title = None
|
||
|
return title
|
||
|
|
||
|
def is_reachable(self):
|
||
|
""" Check if the page is reachable. """
|
||
|
if self.url:
|
||
|
try:
|
||
|
response = urlopen(self.url)
|
||
|
if response.getcode() == HTTP_OKAY:
|
||
|
self.last_reachable = datetime.datetime.now()
|
||
|
return True
|
||
|
except (HTTPError, URLError):
|
||
|
return False
|
||
|
return False
|
||
|
|
||
|
def visit(self):
|
||
|
""" Increment the visit count and update the last visited datestamp """
|
||
|
self.times_visited += 1
|
||
|
self.last_visited = datetime.datetime.now()
|
||
| software/bookmark_library/lib/tag.py | ||
|---|---|---|
|
import sqlite3
|
||
|
|
||
|
SQL_SELECT_TAG_BY_ID = "SELECT * FROM tags WHERE id=?"
|
||
|
SQL_SELECT_TAG_BY_VALUE = "SELECT * FROM tags WHERE value=?"
|
||
|
SQL_INSERT_TAG = "INSERT INTO tags VALUES (?,?)"
|
||
|
|
||
|
|
||
|
class Tag(object):
|
||
|
|
||
|
def __init__(self, db_conn, id=None, value=None):
|
||
|
|
||
|
# Make sure the DB connection is valid before doing anything else
|
||
|
if not db_conn:
|
||
|
raise Exception
|
||
|
#~ raise DBConnectionError('Invalid database connection object!')
|
||
|
|
||
|
self._db_conn = db_conn
|
||
|
|
||
|
# Data attributes
|
||
|
self.id = id
|
||
|
self.value = value
|
||
|
|
||
|
if self.id is not None:
|
||
|
# If an ID was passed in then the tag's value must match the ID
|
||
|
db = self._db_conn.cursor()
|
||
|
db.execute(SQL_SELECT_TAG_BY_ID, (self.id,))
|
||
|
row = db.fetchone()
|
||
|
if not row:
|
||
|
raise Exception
|
||
|
self.value = row[1]
|
||
|
|
||
|
elif self.value:
|
||
|
# If a value was passed in, check to see if it already exists in
|
||
|
# the DB; if so set the ID accordingly
|
||
|
db = self._db_conn.cursor()
|
||
|
db.execute(SQL_SELECT_TAG_BY_VALUE, (self.value,))
|
||
|
row = db.fetchone()
|
||
|
if row:
|
||
|
self.id = int(row[0])
|
||
|
|
||
|
def __str__(self, value):
|
||
|
return repr(self.value)
|
||
|
|
||
|
def save(self):
|
||
|
# Only insert a new tag if it does not already exist in the database
|
||
|
if self.id is None:
|
||
|
db = self._db_conn.cursor()
|
||
|
values = (None, self.value)
|
||
|
db.execute(SQL_INSERT_TAG, values)
|
||
|
|
||
|
# Now set Tag object's ID
|
||
|
self.id = db.lastrowid
|
||
|
|
||
|
self._db_conn.commit()
|
||
|
|
||
|
|
||
|
|
||
| software/bookmark_library/setup.py | ||
|---|---|---|
|
from setuptools import setup, find_packages
|
||
|
|
||
|
setup(
|
||
|
name = 'bmklib',
|
||
|
version = '0.1',
|
||
|
packages = find_packages(),
|
||
|
install_requires = ['dateutils>=0.6.6'],
|
||
|
)
|
||
| software/bookmark_library/tests/script | ||
|---|---|---|
|
import sqlite3
|
||
|
conn = sqlite3.connect('bookmarks.db')
|
||
|
from bookmark import Bookmark
|
||
|
d = Bookmark(conn)
|
||
|
d.url = 'http://www.google.com'
|
||
|
d.title = 'Google'
|
||
|
d.record_dirty
|
||
| software/bookmark_library/utils/bookmark_converter.py | ||
|---|---|---|
|
import re
|
||
|
import sys
|
||
|
|
||
|
def main():
|
||
|
""" The purpose of this script is to convert the output of a bookmarks
|
||
|
export from Google Chrome into something more useful for the bookmark
|
||
|
import script.
|
||
|
"""
|
||
|
if len(sys.argv) < 2:
|
||
|
print('ERROR: not enough arguments provided')
|
||
|
return -1
|
||
|
|
||
|
file_contents = open(sys.argv[1]).read()
|
||
|
matches = re.finditer('<A HREF=\"(.+?)\".+ADD_DATE=\"(.+?)\"',
|
||
|
file_contents)
|
||
|
|
||
|
for match in matches:
|
||
|
print('{:s} {:s}'.format(match.group(1), match.group(2)))
|
||
|
|
||
|
if __name__ == '__main__':
|
||
|
main()
|
||
| software/bookmark_library/utils/bookmark_importer.py | ||
|---|---|---|
|
import datetime
|
||
|
import os.path
|
||
|
import sys
|
||
|
|
||
|
import sqlite3
|
||
|
|
||
|
from bookmark import Bookmark
|
||
|
from tag import Tag
|
||
|
|
||
|
def usage():
|
||
|
print('\n+++ Bookmark Importer Script +++')
|
||
|
print('USAGE: python3 bookmark_importer.py <database path> <input file> '
|
||
|
'[<tag> ...]')
|
||
|
|
||
|
def main():
|
||
|
""" Import bookmarks into the database from a bookmarks export.
|
||
|
|
||
|
NOTE: If using a Google Chrome export with "date added" information, you
|
||
|
need to comment out the date_added field protections in bookmark.py.
|
||
|
|
||
|
"""
|
||
|
|
||
|
# Input error checking
|
||
|
if len(sys.argv) < 3:
|
||
|
print('ERROR: not enough arguments specified')
|
||
|
usage()
|
||
|
return -1
|
||
|
|
||
|
if not os.path.isfile(sys.argv[1]):
|
||
|
print('ERROR: unable to open database "{:s}"'.format(sys.argv[1]))
|
||
|
return -1
|
||
|
|
||
|
try:
|
||
|
input_file = open(sys.argv[2], 'r')
|
||
|
except FileNotFoundError:
|
||
|
print('ERROR: unable to open file "{:s}"'.format(sys.argv[2]))
|
||
|
return -1
|
||
|
|
||
|
# Count the number of lines in the file for output
|
||
|
num_lines = sum(1 for line in open(sys.argv[2]) if line.strip())
|
||
|
|
||
|
# Open a connection to the database
|
||
|
conn = sqlite3.connect('bookmarks.db')
|
||
|
|
||
|
# Build list of category tags if needed
|
||
|
cat_tags = set()
|
||
|
if len(sys.argv) > 3:
|
||
|
for tag in sys.argv[3:len(sys.argv)]:
|
||
|
cat_tags.add(Tag(conn, value=tag))
|
||
|
|
||
|
# Iterate over the input file and create new
|
||
|
bmark_counter = 1
|
||
|
for line in input_file:
|
||
|
if line:
|
||
|
print('Adding bookmark {:d} of {:d}'.format(bmark_counter, num_lines))
|
||
|
|
||
|
# Parse input line and create new bookmark
|
||
|
splits = line.strip().split()
|
||
|
bookmark = Bookmark(conn)
|
||
|
bookmark.url = splits[0]
|
||
|
bookmark.title = bookmark.suggest_title()
|
||
|
bookmark.is_reachable()
|
||
|
bookmark.category_tags = bookmark.category_tags.union(cat_tags)
|
||
|
|
||
|
# If the date added timestamp (unix epoch format) is present, set
|
||
|
# it.
|
||
|
if len(splits) == 2:
|
||
|
added = datetime.datetime.fromtimestamp(int(splits[1]))
|
||
|
bookmark.date_added = added
|
||
|
|
||
|
bookmark.save()
|
||
|
bmark_counter += 1
|
||
|
|
||
|
|
||
|
if __name__ == '__main__':
|
||
|
main()
|
||
| software/bookmark_library/web/bmklib.wsgi | ||
|---|---|---|
|
import cgi
|
||
|
import datetime
|
||
|
import os
|
||
|
import sqlite3
|
||
|
from genshi.template import TemplateLoader
|
||
|
from genshi.template.base import Context
|
||
|
|
||
|
BASE_PATH = '/home/dsorber/bfcs'
|
||
|
DB_PATH = os.path.join(BASE_PATH, 'beer_temps.db')
|
||
|
TEMPLATE_PATH = os.path.join(BASE_PATH, 'templates')
|
||
|
|
||
|
# It accepts two arguments:
|
||
|
# environ points to a dictionary containing CGI like environment variables
|
||
|
# which is filled by the server for each received request from the client
|
||
|
# start_response is a callback function supplied by the server
|
||
|
# which will be used to send the HTTP status and headers to the server
|
||
|
def application(environ, start_response):
|
||
|
loader = TemplateLoader(TEMPLATE_PATH, auto_reload=True)
|
||
|
if not environ['PATH_INFO'] or environ['PATH_INFO'] == '/':
|
||
|
# Load the index template
|
||
|
template = loader.load('index.html')
|
||
|
# Create the response args
|
||
|
response_args = {'title': 'Beer Fermentation Control System'}
|
||
|
# Open up the connection to the database, execute the query,
|
||
|
# grab the results, then close the DB connection.
|
||
|
db_conn = sqlite3.connect(DB_PATH)
|
||
|
db = db_conn.cursor()
|
||
|
db.execute("select strftime('%m/%d/%Y', date(ts, 'unixepoch')), "
|
||
|
" count(ts), avg(temp1), max(temp1), min(temp1),"
|
||
|
" avg(temp2), max(temp2), min(temp2), avg(temp3),"
|
||
|
" max(temp3), min(temp3), avg(temp4), max(temp4),"
|
||
|
" min(temp4)"
|
||
|
"from temp_data "
|
||
|
"group by date(ts, 'unixepoch')")
|
||
|
response_args['results'] = db.fetchall()
|
||
|
db_conn.close()
|
||
|
# Generate the stream and then render it to HTML
|
||
|
stream = template.generate(**response_args)
|
||
|
response_body = stream.render('html', doctype='html', encoding='utf-8')
|
||
|
# Set the appropriate response headers
|
||
|
response_headers = [('Content-Type', 'text/html; charset=utf-8'),
|
||
|
('Content-Length', str(len(response_body)))]
|
||
|
elif environ['PATH_INFO'].startswith('/day/') and environ['PATH_INFO'].endswith('/all'):
|
||
|
# Load the all template
|
||
|
template = loader.load('all.html')
|
||
|
# Parse the date out of the URL (TODO: validate all this)
|
||
|
day = environ['PATH_INFO'][5:7]
|
||
|
month = environ['PATH_INFO'][7:9]
|
||
|
year = environ['PATH_INFO'][9:13]
|
||
|
date = '%s/%s/%s' % (month, day, year)
|
||
|
# Create the response args
|
||
|
response_args = {'title': 'Sensor Readings for %s' % date}
|
||
|
# Open up the connection to the database, execute the query
|
||
|
# (limit to today and the past 5 minutes),
|
||
|
# grab the results, then close the DB connection.
|
||
|
db_conn = sqlite3.connect(DB_PATH)
|
||
|
db = db_conn.cursor()
|
||
|
db.execute("select strftime('%%H:%%M:%%S', ts, 'unixepoch'), "
|
||
|
" temp1, temp2, temp3, temp4, relay_status "
|
||
|
"from temp_data "
|
||
|
"where date(ts, 'unixepoch') is date('%s-%s-%s')"
|
||
|
"order by ts desc" % (year, month, day))
|
||
|
response_args['results'] = db.fetchall()
|
||
|
db_conn.close()
|
||
|
# Generate the stream and then render it to HTML
|
||
|
stream = template.generate(**response_args)
|
||
|
response_body = stream.render('html', doctype='html', encoding='utf-8')
|
||
|
# Set the appropriate response headers
|
||
|
response_headers = [('Content-Type', 'text/html; charset=utf-8'),
|
||
|
('Content-Length', str(len(response_body)))]
|
||
|
elif environ['PATH_INFO'].startswith('/day/'):
|
||
|
# Load the day template
|
||
|
template = loader.load('day.html')
|
||
|
|
||
|
# Parse the date out of the URL (TODO: validate all this)
|
||
|
day = environ['PATH_INFO'][5:7]
|
||
|
month = environ['PATH_INFO'][7:9]
|
||
|
year = environ['PATH_INFO'][9:13]
|
||
|
date = '%s/%s/%s' % (month, day, year)
|
||
|
# Create the response args
|
||
|
response_args = {'title': 'Sensor Readings for %s' % date,
|
||
|
'url_date': environ['PATH_INFO'][5:13]}
|
||
|
# Open up the connection to the database, execute the query
|
||
|
# (limit to today and the past 5 minutes),
|
||
|
# grab the results, then close the DB connection.
|
||
|
db_conn = sqlite3.connect(DB_PATH)
|
||
|
db = db_conn.cursor()
|
||
|
db.execute("select strftime('%%H:%%M:%%S', ts, 'unixepoch'), "
|
||
|
" temp1, temp2, temp3, temp4, relay_status "
|
||
|
"from temp_data "
|
||
|
"where date(ts, 'unixepoch') is date('%s-%s-%s')"
|
||
|
"order by ts desc limit 300" % (year, month, day))
|
||
|
response_args['results'] = db.fetchall()
|
||
|
# Chart query
|
||
|
#
|
||
|
db.execute("select strftime('%%H:%%M:%%S', ts, 'unixepoch'), ts, temp1, "
|
||
|
" temp2, temp3, temp4 from ( "
|
||
|
"select ts, temp1, temp2, temp3, temp4 "
|
||
|
"from temp_data "
|
||
|
"where date(ts, 'unixepoch') is date('%s-%s-%s')"
|
||
|
"order by ts desc limit 60) order by ts"
|
||
|
% (year, month, day))
|
||
|
chart_results = db.fetchall()
|
||
|
# Process chart query results
|
||
|
sensor1_data = []
|
||
|
sensor2_data = []
|
||
|
sensor3_data = []
|
||
|
sensor4_data = []
|
||
|
for row in chart_results:
|
||
|
sensor1_data.append([int(row[1]) * 1000, row[2]])
|
||
|
sensor2_data.append([int(row[1]) * 1000, row[3]])
|
||
|
sensor3_data.append([int(row[1]) * 1000, row[4]])
|
||
|
sensor4_data.append([int(row[1]) * 1000, row[5]])
|
||
|
response_args['sensor1_data'] = str(sensor1_data)
|
||
|
response_args['sensor2_data'] = str(sensor2_data)
|
||
|
response_args['sensor3_data'] = str(sensor3_data)
|
||
|
response_args['sensor4_data'] = str(sensor4_data)
|
||
|
# This is a bit of hack to get around a javascript issue in
|
||
|
# the template
|
||
|
# response_args['chart_results'] = chart_results[:-1]
|
||
|
# response_args['chart_last_row'] = chart_results[-1]
|
||
|
db_conn.close()
|
||
|
# Generate the stream and then render it to HTML
|
||
|
stream = template.generate(**response_args)
|
||
|
response_body = stream.render('html', doctype='html', encoding='utf-8')
|
||
|
# Set the appropriate response headers
|
||
|
response_headers = [('Content-Type', 'text/html; charset=utf-8'),
|
||
|
('Content-Length', str(len(response_body)))]
|
||
|
elif environ['PATH_INFO'] == ('/chart_update'):
|
||
|
# Update the self updating chart
|
||
|
response_body = ''
|
||
|
if environ['QUERY_STRING']:
|
||
|
# Parse the query string to get the last timestamp from the client
|
||
|
get_args = cgi.parse_qs(environ['QUERY_STRING'])
|
||
|
last_ts = int(get_args['last_ts'][0])
|
||
|
date_from_ts = datetime.datetime.fromtimestamp(last_ts)
|
||
|
# Reconstruct portions of the date from the ts
|
||
|
year = date_from_ts.year
|
||
|
month = '%02d' % date_from_ts.month
|
||
|
day = '%02d' % date_from_ts.day
|
||
|
|
||
|
# Grab any entries from the supplied date that are greater than
|
||
|
# the last timestamp sent from the client javascript
|
||
|
db_conn = sqlite3.connect(DB_PATH)
|
||
|
db = db_conn.cursor()
|
||
|
db.execute("select strftime('%%H:%%M:%%S', ts, 'unixepoch'), ts, "
|
||
|
" temp1, temp2, temp3, temp4, relay_status "
|
||
|
"from temp_data "
|
||
|
"where date(ts, 'unixepoch') is date('%s-%s-%s') and "
|
||
|
" ts > %s "
|
||
|
"order by ts asc limit 60" % (year, month, day, last_ts))
|
||
|
db_rows = db.fetchall()
|
||
|
db_conn.close()
|
||
|
# Iterate over the rows fetched by the query and pretty them up
|
||
|
# in preparation of being sent back to the client javascript for
|
||
|
# display
|
||
|
result_rows = []
|
||
|
for row in db_rows:
|
||
|
result_rows.append('%s %s %s %s %s %s %s' %
|
||
|
(row[0], (int(row[1]) * 1000),
|
||
|
row[2], row[3], row[4], row[5],
|
||
|
int(row[6]) and 'on' or 'off'))
|
||
|
response_body = ','.join(result_rows)
|
||
|
response_body = response_body.encode('utf-8')
|
||
|
response_headers = [('Content-Type', 'text/plain; charset=utf-8'),
|
||
|
('Content-Length', str(len(response_body)))]
|
||
|
else:
|
||
|
# User supplied an invalid URL
|
||
|
response_body = 'Invalid URL "%s"' % environ['PATH_INFO']
|
||
|
response_headers = [('Content-Type', 'text/plain'),
|
||
|
('Content-Length', str(len(response_body)))]
|
||
|
# HTTP response code and message
|
||
|
status = '200 OK'
|
||
|
# Send the status and response headers to the server
|
||
|
start_response(status, response_headers)
|
||
|
# Return the response body. (Notice it is wrapped in a list although it
|
||
|
# could be any iterable.)
|
||
|
return [response_body]
|
||
I'm still learning how to use git...