BUGFIX : added non-commited module

git-svn-id: file:///svnroot/wifihood/trunk/wifiscanner@38 c51dfc6a-5949-4919-9c8e-f207a149c383

diff --git a/wifimap/db.py b/wifimap/db.py
new file mode 100644 (file)
index 0000000..42edfc3
--- /dev/null
+++ b/wifimap/db.py
@@ -0,0 +1,91 @@
+
+import sqlite3
+
+import config
+
+create_db = """
+    CREATE TABLE ap (
+        mac TEXT PRIMARY KEY UNIQUE NOT NULL,
+        rss INTEGER NOT NULL,
+        last_seen TIMESTAMP NOT NULL,
+        n INTEGER DEFAULT 0,
+        lat REAL DEFAULT 0,
+        lon REAL DEFAULT 0,
+        alt REAL DEFAULT 0,
+        lat2 REAL DEFAULT 0,
+        lon2 REAL DEFAULT 0,
+        alt2 REAL DEFAULT 0
+        );
+"""
+
+class database :
+
+    def __init__ ( self , dbname=None ) :
+        self.setfilename( dbname )
+        self.db = None
+
+    def setfilename ( self , dbname ) :
+        self.dbname = dbname
+
+    def open ( self ) :
+
+        if self.db :
+            self.close()
+
+        self.db = sqlite3.connect( self.dbname )
+
+        rc = self.db.execute( "SELECT * FROM sqlite_master" )
+        tables = map( lambda x : "%s-%s" % ( x[0] , x[1] ) , rc.fetchall() )
+
+        if not tables :
+            self.db.executescript( create_db )
+
+    def close ( self ) :
+        self.db.close()
+        self.db = None
+
+    def is_open ( self ) :
+        if not self.db :
+            return False 
+        return True 
+
+    def __get ( self , query ) :
+        rc = self.db.execute( query )
+        return rc.fetchone()
+
+    def add ( self , mac , rss , timestamp , gps=None ) :
+  
+        query = "INSERT INTO ap ( mac , rss , last_seen ) VALUES ( '%s' , %d , %f )" % ( mac , rss , timestamp )
+        rc = self.db.execute( query )
+        if rc.rowcount :
+            if gps :
+                lat , lon , alt = gps
+                query = "UPDATE ap SET n=1 , lat=%f , lat2=%f , lon=%f , lon2=%f , alt=%f , alt2=%f WHERE mac='%s'" % ( lat , lat*lat , lon , lon*lon , alt , alt*alt , mac )
+                rc = self.db.execute( query )
+            self.db.commit()
+
+    def update ( self , mac , rss , timestamp , gps=None ) :
+
+        query = "UPDATE ap set rss=%d , last_seen=%f" % ( rss , timestamp )
+        if gps :
+            lat , lon = gps
+            query = " , n=n+1 , lat=lat+%f , lat2=lat2+%f , lon=lon+%f , lon2=lon2+%f , alt=alt+%f , alt2=alt2+%f" % ( lat , lat*lat , lon , lon*lon , alt , alt*alt )
+        query += " WHERE mac='%s'" % mac
+        rc = self.db.execute( query )
+        if rc.rowcount :
+            self.db.commit()
+
+    def delete ( self , mac ) :
+        rc = self.db.execute( "DELETE FROM ap WHERE mac='%s'" % mac )
+        if rc.rowcount :
+            self.db.commit()
+
+    def get ( self , mac ) :
+        query = "SELECT rss FROM ap WHERE mac='%s'" % mac
+        return self.__get( query )
+
+    def nrows ( self ) :
+        query = "SELECT COUNT(*) FROM ap"
+        values = self.__get( query )
+        return int( values[0] )
+