6 mac TEXT PRIMARY KEY UNIQUE NOT NULL,
8 last_seen TIMESTAMP NOT NULL,
21 def __init__ ( self , dbname ) :
30 self.db = sqlite3.connect( self.dbname )
32 rc = self.db.execute( "SELECT * FROM sqlite_master" )
33 tables = map( lambda x : "%s-%s" % ( x[0] , x[1] ) , rc.fetchall() )
36 self.db.executescript( create_db )
43 def is_open ( self ) :
48 def __get ( self , query ) :
49 rc = self.db.execute( query )
52 def __extend_query ( self , gps ) :
56 query.append( "lat=lat+%f , lat2=lat2+%f , lon=lon+%f , lon2=lon2+%f" % ( lat , lat*lat , lon , lon*lon ) )
58 query.append( "alt=alt+%f , alt2=alt2+%f" % ( alt , alt*alt ) )
59 return " , ".join( query )
61 def add ( self , mac , rss , timestamp , gps ) :
63 query = "INSERT INTO ap ( mac , rss , last_seen ) VALUES ( '%s' , %d , %f )" % ( mac , rss , timestamp )
64 rc = self.db.execute( query )
66 extra = self.__extend_query(gps)
68 query = "UPDATE ap SET n=1 , %s WHERE mac='%s'" % ( extra , mac )
69 rc = self.db.execute( query )
72 def update ( self , mac , rss , timestamp , gps ) :
74 query = "UPDATE ap set rss=%d , last_seen=%f" % ( rss , timestamp )
75 extra = self.__extend_query(gps)
77 query += " , n=n+1 , %s" % extra
78 query += " WHERE mac='%s'" % mac
79 rc = self.db.execute( query )
83 def delete ( self , mac ) :
84 rc = self.db.execute( "DELETE FROM ap WHERE mac='%s'" % mac )
88 def get ( self , mac ) :
89 query = "SELECT rss FROM ap WHERE mac='%s'" % mac
90 return self.__get( query )
93 query = "SELECT COUNT(*) FROM ap"
94 values = self.__get( query )
95 return int( values[0] )