[Powerguru-commit] [SCM] powerguru branch, master, updated. 420643130b72417ae7dc5373780d3b57133075b1

[Rob Savoye]

This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "powerguru".

The branch, master has been updated
       via  420643130b72417ae7dc5373780d3b57133075b1 (commit)
       via  7f471c69b89d484ea568eae200682e4f32d3eba6 (commit)
      from  85026303f4b30f43a327a66cf46d1e2f53ab4e80 (commit)

Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.

- Log -----------------------------------------------------------------
http://git.savannah.gnu.org/cgit/powerguru.git/commit/?id=420643130b72417ae7dc5373780d3b57133075b1


commit 420643130b72417ae7dc5373780d3b57133075b1
Author: Rob Savoye <address@hidden>
Date:   Thu Jan 10 14:32:57 2019 -0700

    Script to merge powerguru data from remote postgresql database into a local 
one

diff --git a/python/mergedb.py b/python/mergedb.py
new file mode 100755
index 0000000..181a8fa
--- /dev/null
+++ b/python/mergedb.py
@@ -0,0 +1,171 @@
+#!/usr/bin/python3
+
+# 
+#   Copyright (C) 2019 Free Software Foundation, Inc.
+# 
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; either version 3 of the License, or
+# (at your option) any later version.
+# 
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
+# 
+
+# API documentation at: https://pyownet.readthedocs.io/en/latest/
+
+import epdb
+import logging
+import time
+import psycopg2
+import getopt
+import sys
+from sys import argv
+
+options = dict()
+options['dbname'] = "powerguru"  # hostname of the database
+options['source'] = "pi"  # hostname of the source database server
+options['dest'] = "localhost"  # hostname of the destination server
+options['starttime'] = ""  # hostname of the destination server
+options['endtime'] = ""  # hostname of the destination server
+
+# menu for --help
+def usage(argv):
+    print(argv[0] + ": options: ")
+    print("""
+    \t--help(-h)        Help
+    \t--source(-s)      Source Database server [host]:port]], default '%s'
+    \t--dest(-d)        Destination Database server [host]:port]], default '%s'
+    \t--database(-n)    Database name, default for now '%s'
+    \t--starttime(-t)   Use timestanps after this, default begining of data
+    \t--endtime(-e)     Use timestanps after this, default end of data
+    \t--verbose(-v)     Enable verbosity
+
+    The starting and ending timestamps need to be in a normal timestamp
+    format for now. ie... '2019-01-10 12:18:48'
+    """ % (options['source'], options['dest'], options['dbname'])
+    )
+    quit()
+
+# Check command line arguments
+try:
+    (opts, val) = getopt.getopt(argv[1:], "h,s:,d:,n:,t:,e:,v,",
+           ["help", "source", "dest", "database", "starttime", "endtime", 
"verbose"])
+except getopt.GetoptError as e:
+    logging.error('%r' % e)
+    usage(argv)
+    quit()
+
+# Setup a disk space log filemode. By default, everything
+# gets logged to the disk file
+logging.basicConfig(
+    filename='pgdpy.log',
+    filemode='w',
+    level=logging.DEBUG,
+    format= '[%(asctime)s] {%(filename)s:%(lineno)d} %(levelname)s - 
%(message)s',
+     datefmt='%Y-%m-%d %H:%M:%S'
+)
+
+# Setup console logging, useful for debugging
+# By default, print nothing to the console. There
+# re two versosity levels, the first just informational
+# messages, the second all debugging messages as well.
+root = logging.getLogger()
+ch = logging.StreamHandler(sys.stdout)
+ch.setLevel(logging.CRITICAL)
+formatter = logging.Formatter('%(message)s')
+#formatter = logging.Formatter('{%(filename)s:%(lineno)d} - %(message)s')
+ch.setFormatter(formatter)
+root.addHandler(ch)
+terminator = ch.terminator
+verbosity = logging.CRITICAL
+
+# process command line arguments, will override the defaults
+for (opt, val) in opts:
+    if opt == '--help' or opt == '-h':
+        usage(argv)
+    elif opt == "--source" or opt == '-s':
+        options['source'] = val
+    elif opt == "--dest" or opt == '-d':
+        options['dest'] = val
+    elif opt == "--starttime" or opt == '-t':
+        options['starttime'] = val
+    elif opt == "--endtime" or opt == '-e':
+        options['endtime'] = val
+    elif opt == "--verbose" or opt == '-v':
+        if verbosity == logging.INFO:
+            verbosity = logging.DEBUG
+            formatter = logging.Formatter('{%(filename)s:%(lineno)d} 
%(levelname)s - %(message)s')
+            ch.setFormatter(formatter)
+        if verbosity == logging.CRITICAL:
+            verbosity = logging.INFO
+
+ch.setLevel(verbosity)
+
+#
+# Open the two data base connections, if not localhost
+#
+connect = ""
+if options['source'] != "localhost":
+    connect = "host='" + options['source'] + "'"
+    connect += " dbname='" + options['dbname'] + "'"
+
+logging.debug("Source connect: %r" % connect)
+source = psycopg2.connect(connect)
+if source.closed == 0:
+    source.autocommit = True
+    logging.info("Opened connection to %r on %r" % (options['dbname'], 
options['source']))
+srccursor = source.cursor()
+logging.debug("Opened cursor in %r" % options['dbname'])
+    
+connect = "host='" + options['dest'] + "'"
+connect += " dbname='" + options['dbname'] + "'"
+logging.debug("Dest connect: %r" % connect)
+dest = psycopg2.connect(connect)
+if dest.closed == 0:
+    dest.autocommit = True
+    logging.info("Opened connection to %r on %r" % (options['dbname'], 
options['source']))
+
+    
+# FIXME: for now this is limited to the powerguru database
+destcursor = dest.cursor()
+logging.debug("Opened cursor in %r" % options['dbname'])
+
+start = ""
+if options['starttime'] != "":
+    start = "WHERE timestamp>=%r" % options['starttime']
+else:
+    start = ""
+if options['endtime'] != "" and options['starttime'] != "":
+    end = " AND timestamp<=%r" % options['endtime']
+if options['endtime'] != "" and options['starttime'] == "":
+    end = " WHERE timestamp<=%r" % options['endtime']
+else:
+    end = ""
+# FIXME: Add LIMIT if we need to transfer data by blocks
+query = """SELECT * FROM temperature %s %s ORDER BY timestamp""" % (start, end)
+logging.debug(query)
+srccursor.execute(query)
+temps = dict()
+data = list()
+# Store the returned data
+for id,temperature,temphigh,templow,scale,timestamp in srccursor:
+    temps['id'] = id
+    temps['temperature'] = temperature
+    temps['temphigh'] = temphigh
+    temps['templow'] = templow
+    temps['scale'] = scale
+    temps['timestamp'] = timestamp
+    print("%r" % temps)
+    data.append(temps)
+    #INSERT INTO datas () VALUES 
('$data','$notes','$sortname','$listname','$url')";
+
+    query = """INSERT INTO temperature VALUES (%r, '%r', '%r', '%r', %r, %r) 
ON CONFLICT DO NOTHING""" % (id, temperature, temphigh, templow, scale,
+                             timestamp.strftime("%Y-%m-%d %H:%M:%S"))
+    logging.debug("Dest query: %r" % query)
+    destcursor.execute(query)

http://git.savannah.gnu.org/cgit/powerguru.git/commit/?id=7f471c69b89d484ea568eae200682e4f32d3eba6


commit 7f471c69b89d484ea568eae200682e4f32d3eba6
Author: Rob Savoye <address@hidden>
Date:   Thu Jan 10 14:32:18 2019 -0700

    make timestap field UNIQUE, so it can be used in an INSERT INTO ... ON 
CONFLICT statement

diff --git a/powerguru.sql b/powerguru.sql
index 2cea00d..b6f0fc5 100644
--- a/powerguru.sql
+++ b/powerguru.sql
@@ -78,7 +78,7 @@ CREATE TABLE onewire (
   id varchar(12) NOT NULL default '0',
   alias varchar(12) NOT NULL default '0',
   type  wire_type NOT NULL default 'UNSUPPORTED',
-  "timestamp" timestamp without time zone
+  "timestamp" timestamp without time zone UNIQUE
 );
 
 DROP TABLE IF EXISTS temperature;
@@ -88,7 +88,7 @@ CREATE TABLE temperature (
   temphigh float NOT NULL default '0',
   templow float NOT NULL default '0',
   scale char(1) NOT NULL default 'F',
-  "timestamp" timestamp without time zone
+  "timestamp" timestamp without time zone UNIQUE
 );
 
 DROP TABLE IF EXISTS battery;
@@ -97,7 +97,7 @@ CREATE TABLE battery (
   current float NOT NULL default '0',
   volts float NOT NULL default '0',
   type volt_type NOT NULL default 'DC',
-  "timestamp" timestamp without time zone
+  "timestamp" timestamp without time zone UNIQUE
 );
 
 /*!40101 SET address@hidden */;

-----------------------------------------------------------------------

Summary of changes:
 powerguru.sql     |   6 +-
 python/mergedb.py | 171 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 174 insertions(+), 3 deletions(-)
 create mode 100755 python/mergedb.py


hooks/post-receive
-- 
powerguru