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 ) :
54 return "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 )
56 def add ( self , mac , rss , timestamp , gps=None ) :
58 query = "INSERT INTO ap ( mac , rss , last_seen ) VALUES ( '%s' , %d , %f )" % ( mac , rss , timestamp )
59 rc = self.db.execute( query )
63 query = "UPDATE ap SET n=1 , %s WHERE mac='%s'" % ( self.__extend_query(gps) , mac )
64 rc = self.db.execute( query )
67 def update ( self , mac , rss , timestamp , gps=None ) :
69 query = "UPDATE ap set rss=%d , last_seen=%f" % ( rss , timestamp )
72 query += " , n=n+1 , %s" % self.__extend_query(gps)
73 query += " WHERE mac='%s'" % mac
74 rc = self.db.execute( query )
78 def delete ( self , mac ) :
79 rc = self.db.execute( "DELETE FROM ap WHERE mac='%s'" % mac )
83 def get ( self , mac ) :
84 query = "SELECT rss FROM ap WHERE mac='%s'" % mac
85 return self.__get( query )
88 query = "SELECT COUNT(*) FROM ap"
89 values = self.__get( query )
90 return int( values[0] )