[8853] | 1 | #!/usr/local/bin/python
|
---|
| 2 | """Update the database from the txt/csv file.
|
---|
| 3 |
|
---|
| 4 | First run "__/bin/db.py create", then run this script without arguments: it
|
---|
| 5 | should find the points.txt and points.db files.
|
---|
| 6 |
|
---|
| 7 | """
|
---|
| 8 | import csv
|
---|
| 9 | import os
|
---|
| 10 | import sqlite3
|
---|
| 11 | import stat
|
---|
| 12 | import sys
|
---|
| 13 | from datetime import datetime
|
---|
| 14 |
|
---|
| 15 | import aspen
|
---|
| 16 | aspen.configure()
|
---|
| 17 |
|
---|
| 18 |
|
---|
| 19 | __all__ = ['clear', 'count', 'delete', 'sync']
|
---|
| 20 |
|
---|
| 21 |
|
---|
| 22 | RAWPATH = os.path.join(aspen.paths.__, 'var', 'points.txt')
|
---|
| 23 | DBPATH = os.path.join(aspen.paths.__, 'var', 'points.db')
|
---|
| 24 |
|
---|
| 25 |
|
---|
| 26 | def _create():
|
---|
| 27 | cur = CONN.cursor()
|
---|
| 28 | cur.execute("""
|
---|
| 29 |
|
---|
| 30 | CREATE TABLE IF NOT EXISTS points(
|
---|
| 31 |
|
---|
| 32 | uid TEXT UNIQUE PRIMARY KEY ,
|
---|
| 33 | lat REAL ,
|
---|
| 34 | lng REAL ,
|
---|
| 35 |
|
---|
| 36 | modtime TIMESTAMP ,
|
---|
| 37 | seentime TIMESTAMP
|
---|
| 38 |
|
---|
| 39 | );
|
---|
| 40 |
|
---|
| 41 | """)
|
---|
| 42 |
|
---|
| 43 |
|
---|
| 44 | def clear():
|
---|
| 45 | cur = CONN.cursor()
|
---|
| 46 | cur.execute("DELETE FROM points")
|
---|
| 47 |
|
---|
| 48 |
|
---|
| 49 | def count():
|
---|
| 50 | cur = CONN.cursor()
|
---|
| 51 | cur.execute("SELECT COUNT(uid) FROM points")
|
---|
| 52 | print cur.fetchone()[0]
|
---|
| 53 |
|
---|
| 54 |
|
---|
| 55 | def delete():
|
---|
| 56 | os.remove(DBPATH)
|
---|
| 57 |
|
---|
| 58 |
|
---|
| 59 | def sync():
|
---|
| 60 | """Synchronize points.db with points.txt.
|
---|
| 61 | """
|
---|
| 62 |
|
---|
| 63 | sys.stdout.write('syncing'); sys.stdout.flush()
|
---|
| 64 |
|
---|
| 65 | cur = CONN.cursor()
|
---|
| 66 | modtime = datetime.fromtimestamp(os.stat(RAWPATH)[stat.ST_MTIME])
|
---|
| 67 |
|
---|
| 68 | for point in csv.reader(open(RAWPATH, 'r')):
|
---|
| 69 |
|
---|
| 70 | # Parse and validate values.
|
---|
| 71 | # ==========================
|
---|
| 72 |
|
---|
| 73 | uid, lat, lng = point
|
---|
| 74 | try:
|
---|
| 75 | lat = float(lat)
|
---|
| 76 | lng = float(lng)
|
---|
| 77 | except ValueError:
|
---|
| 78 | print "bad line:", point
|
---|
| 79 |
|
---|
| 80 |
|
---|
| 81 | # Select any existing record for this point.
|
---|
| 82 | # ==========================================
|
---|
| 83 | # After this, 'point' will either be None or a sqlite3.Row.
|
---|
| 84 |
|
---|
| 85 | result = cur.execute("SELECT * FROM points WHERE uid = ?", (uid,))
|
---|
| 86 | result = result.fetchall()
|
---|
| 87 | numresults = len(result) if (result is not None) else 0
|
---|
| 88 | if numresults not in (0, 1):
|
---|
| 89 | msg = "%d result[s]; wanted 0 or 1" % numresults
|
---|
| 90 | print >> sys.stderr, "bad record: <%s> [%s]" % (uid, msg)
|
---|
| 91 | point = result[0] if (numresults == 1) else None
|
---|
| 92 |
|
---|
| 93 |
|
---|
| 94 | # Insert the point if we don't have it.
|
---|
| 95 | # =====================================
|
---|
| 96 |
|
---|
| 97 | if point is None:
|
---|
| 98 | sys.stdout.write('O'); sys.stdout.flush()
|
---|
| 99 | cur.execute("""
|
---|
| 100 |
|
---|
| 101 | INSERT INTO points
|
---|
| 102 | (uid, lat, lng, modtime, seentime)
|
---|
| 103 | VALUES ( ?, ?, ?, ?, ?)
|
---|
| 104 |
|
---|
| 105 | """, (uid, lat, lng, modtime, modtime))
|
---|
| 106 |
|
---|
| 107 |
|
---|
| 108 | # Update the point if it has changed.
|
---|
| 109 | # ===================================
|
---|
| 110 |
|
---|
| 111 | elif (point['lat'], point['lng']) != (lat, lng):
|
---|
| 112 | sys.stdout.write('o'); sys.stdout.flush()
|
---|
| 113 | #print (point['lat'], point['lng']), '!=', (lat, lng)
|
---|
| 114 |
|
---|
| 115 | cur.execute("""
|
---|
| 116 |
|
---|
| 117 | UPDATE points
|
---|
| 118 | SET lat = ?
|
---|
| 119 | , lng = ?
|
---|
| 120 | , modtime = ?
|
---|
| 121 | , seentime = ?
|
---|
| 122 | WHERE uid = ?
|
---|
| 123 |
|
---|
| 124 | """, (lat, lng, modtime, modtime, uid))
|
---|
| 125 |
|
---|
| 126 |
|
---|
| 127 | # If it hasn't changed, at least mark it as seen.
|
---|
| 128 | # ===============================================
|
---|
| 129 | # Otherwise we will delete it presently.
|
---|
| 130 |
|
---|
| 131 | else:
|
---|
| 132 | sys.stdout.write('.'); sys.stdout.flush()
|
---|
| 133 | cur.execute( "UPDATE points SET seentime = ? WHERE uid = ?"
|
---|
| 134 | , (modtime, uid)
|
---|
| 135 | )
|
---|
| 136 |
|
---|
| 137 |
|
---|
| 138 | # Now delete rows that weren't in the latest txt file.
|
---|
| 139 | # ====================================================
|
---|
| 140 |
|
---|
| 141 | cur.execute("DELETE FROM points WHERE seentime != ?", (modtime,))
|
---|
| 142 |
|
---|
| 143 | print 'done'
|
---|
| 144 |
|
---|
| 145 |
|
---|
| 146 | if __name__ == '__main__':
|
---|
| 147 |
|
---|
| 148 | try:
|
---|
| 149 | subc = sys.argv[1]
|
---|
| 150 | except IndexError:
|
---|
| 151 | subc = 'sync' # default
|
---|
| 152 |
|
---|
| 153 | if subc not in __all__:
|
---|
| 154 | raise SystemExit("I wonder, what does '%s' mean?" % subc)
|
---|
| 155 |
|
---|
| 156 |
|
---|
| 157 | # Connect and execute
|
---|
| 158 | # ===================
|
---|
| 159 | # The connect() call will create the database if it doesn't exist. If it was
|
---|
| 160 | # created (i.e., it didn't exist before connect()), we also need to create
|
---|
| 161 | # the initial table. Since _create() only creates the table if it doesn't
|
---|
| 162 | # exist, and the little extra db hit doesn't affect performance here, we
|
---|
| 163 | # just call _create() every time.
|
---|
| 164 |
|
---|
| 165 | need_table = os.path.isfile(DBPATH)
|
---|
| 166 | CONN = sqlite3.connect(DBPATH)
|
---|
| 167 | CONN.row_factory = sqlite3.Row # gives us key access
|
---|
| 168 | if subc != 'delete':
|
---|
| 169 | _create() # sets up our table if needed
|
---|
| 170 | func = globals()[subc]
|
---|
| 171 | func()
|
---|
| 172 | CONN.commit()
|
---|
| 173 | CONN.close()
|
---|
| 174 |
|
---|