From 0adb6bf0fed763b6afdf25da469c32a473c3bdd0 Mon Sep 17 00:00:00 2001 From: Teddy Date: Thu, 29 Aug 2013 11:08:53 +0800 Subject: prepare for further features --- server/piztor/model.py | 61 +++++++++++++++++++++++++++++++++++++++++-------- server/piztor/ptp.rst | 10 +++++++- server/piztor/server.py | 21 +++++++++-------- 3 files changed, 73 insertions(+), 19 deletions(-) diff --git a/server/piztor/model.py b/server/piztor/model.py index 41b9a88..460aa38 100644 --- a/server/piztor/model.py +++ b/server/piztor/model.py @@ -1,4 +1,5 @@ -from sqlalchemy import Column, Integer, String, Float, ForeignKey, Boolean +from sqlalchemy import Table, Column +from sqlalchemy import Integer, String, Float, ForeignKey, Boolean from sqlalchemy.dialects.mysql import BLOB, TINYINT from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship, backref @@ -11,24 +12,46 @@ MAX_USERNAME_SIZE = 20 MAX_PASSWORD_SIZE = 20 _table_typical_settings = { - 'mysql_engine' : 'InnoDB', - 'mysql_charset' : 'utf8', + 'mysql_engine' : 'InnoDB', + 'mysql_charset' : 'utf8', 'mysql_auto_increment' : '1'} class _TableName: # avoid typoes UserModel = 'users' LocationInfo = 'location_info' UserAuth = 'user_auth' + CompanyInfo = 'comp_info' + SectionInfo = 'sec_info' + CompanySub = 'comp_sub' + SectionSub = 'sec_sub' + +comp_sub_assoc = Table(_TableName.CompanySub, Base.metadata, + Column('uid', Integer, ForeignKey(_TableName.UserModel + '.id')), + Column('comp_id', Integer, ForeignKey(_TableName.CompanyInfo + '.id'))) + +sec_sub_assoc = Table(_TableName.SectionSub, Base.metadata, + Column('uid', Integer, ForeignKey(_TableName.UserModel + '.id')), + Column('sec_id', Integer, ForeignKey(_TableName.SectionInfo + '.id'))) class UserModel(Base): __tablename__ = _TableName.UserModel __table_args__ = _table_typical_settings id = Column(Integer, primary_key = True) - sec_id = Column(TINYINT) - comp_id = Column(TINYINT) - username = Column(String(MAX_USERNAME_SIZE), + username = Column(String(MAX_USERNAME_SIZE), unique = True, nullable = False) + sex = Column(Boolean, nullable = False) + sec_no = Column(TINYINT) + comp_id = Column(TINYINT, ForeignKey(_TableName.CompanyInfo + '.id')) + sec_id = Column(TINYINT, ForeignKey(_TableName.SectionInfo + '.id')) + + comp_sub = relationship(_TableName.CompanyInfo, + secondary = comp_sub_assoc, + backref = "subscribers") + sec_sub = relationship(_TableName.SectionInfo, + secondary = sec_sub_assoc, + backref = "subscribers") + location = None auth = None sec = None @@ -37,11 +60,11 @@ class LocationInfo(Base): __tablename__ = _TableName.LocationInfo __table_args__ = _table_typical_settings - uid = Column(Integer, ForeignKey(_TableName.UserModel + '.id'), + uid = Column(Integer, ForeignKey(_TableName.UserModel + '.id'), primary_key = True) lat = Column(Float(precesion = 64), nullable = False) lng = Column(Float(precesion = 64), nullable = False) - user = relationship("UserModel", uselist = False, + user = relationship("UserModel", uselist = False, backref = backref("location", uselist = False, cascade = "all, delete-orphan")) @@ -68,7 +91,7 @@ class UserAuth(Base): salt = Column(BLOB) token = Column(BLOB) - user = relationship("UserModel", uselist = False, + user = relationship("UserModel", uselist = False, backref = backref("auth", uselist = False, cascade = "all, delete-orphan")) @@ -92,3 +115,23 @@ class UserAuth(Base): def get_token(self): return self.token + +class CompanyInfo(Base): + __tablename__ = _TableName.CompanyInfo + __table_args__ = _table_typical_settings + + id = Column(TINYINT, primary_key = True) + marker_lat = Column(Float(precesion = 64), nullable = False) + market_lng = Column(Float(precesion = 64), nullable = False) + + subscribers = None + +class SectionInfo(Base): + __tablename__ = _TableName.SectionInfo + __table_args__ = _table_typical_settings + + id = Column(TINYINT, primary_key = True) + marker_lat = Column(Float(precesion = 64), nullable = False) + market_lng = Column(Float(precesion = 64), nullable = False) + + subscribers = None diff --git a/server/piztor/ptp.rst b/server/piztor/ptp.rst index 1dc4f66..d5a5fde 100644 --- a/server/piztor/ptp.rst +++ b/server/piztor/ptp.rst @@ -1,4 +1,4 @@ -Piztor Transmission Protocol v1.0 +Piztor Transmission Protocol v1.0a ---------------------------------- - General @@ -222,3 +222,11 @@ Piztor Transmission Protocol v1.0 | 0x00 | PUSH_FINGERPRINT | MESSAGE | +------+-------------------+--string--+ + - User Location Update + + :: + + +--1b--+-------32b---------+-------?b-------+ + | 0x01 | PUSH_FINGERPRINT | LOCATION_ENTRY | + +------+-------------------+----------------+ + diff --git a/server/piztor/server.py b/server/piztor/server.py index ab352a0..6901400 100644 --- a/server/piztor/server.py +++ b/server/piztor/server.py @@ -19,6 +19,9 @@ from model import * def get_hex(data): return "".join([hex(ord(c))[2:].zfill(2) for c in data]) +def get_sec_id(comp_id, sec_no): + return comp_id * 256 + sec_no + db_path = "root:helloworld@localhost/piztor" #db_path = "piztor.sqlite" FORMAT = "%(asctime)-15s %(message)s" @@ -299,13 +302,13 @@ class LocationInfoHandler(RequestHandler): username, tail = RequestHandler.trunc_padding(tr_data[32:]) if username is None: raise struct.error - comp_id, sec_id = struct.unpack("!BB", tail) + comp_id, sec_no = struct.unpack("!BB", tail) except struct.error: raise BadReqError("Location request: Malformed request body") logger.info("Trying to request locatin with " \ - "(token = {0}, comp_id = {1}, sec_id = {2})" \ - .format(get_hex(token), comp_id, sec_id)) + "(token = {0}, comp_id = {1}, sec_no = {2})" \ + .format(get_hex(token), comp_id, sec_no)) uauth = RequestHandler.get_uauth(token, username, self.session) # Auth failure @@ -315,13 +318,13 @@ class LocationInfoHandler(RequestHandler): _OptCode.location_info, _StatusCode.failure) - if sec_id == 0xff: # All members in the company + if sec_no == 0xff: # All members in the company ulist = self.session.query(UserModel) \ .filter(UserModel.comp_id == comp_id).all() else: + sec_id = get_sec_id(comp_id, sec_no) ulist = self.session.query(UserModel) \ - .filter(and_(UserModel.comp_id == comp_id, - UserModel.sec_id == sec_id)).all() + .filter(UserModel.sec_id == sec_id).all() reply = struct.pack( "!LBB", self._response_size(len(ulist)), @@ -335,7 +338,7 @@ class LocationInfoHandler(RequestHandler): return reply def pack_gid(user): - return struct.pack("!BB", user.comp_id, user.sec_id) + return struct.pack("!BB", user.comp_id, user.sec_no) def pack_sex(user): return struct.pack("!B", 0x01 if user.sex else 0x00) @@ -529,9 +532,9 @@ class SendTextMessageHandler(RequestHandler): pt = RequestHandler.push_tunnels u = uauth.user + sec_id = get_sec_id(u.comp_id, u.sec_no) ulist = self.session.query(UserModel) \ - .filter(and_(UserModel.comp_id == u.comp_id, - UserModel.sec_id == u.sec_id)).all() + .filter(UserModel.sec_id == sec_id).all() for user in ulist: uid = user.id -- cgit v1.2.3