vcs.maemo.org Git - fmms/blob - src/dbhandler.py

   1 #!/usr/bin/env python
   2 # -*- coding: utf-8 -*-
   3 """ database handler for fMMS
   4
   5 @author: Nick Leppänen Larsson <frals@frals.se>
   6 @license: GNU GPL
   7 """
   8 import sqlite3
   9 import os
  10
  11 from gnome import gnomevfs
  12
  13 import fmms_config as fMMSconf
  14
  15
  16 # TODO: constants.py?
  17 MSG_DIRECTION_IN = 0
  18 MSG_DIRECTION_OUT = 1
  19 MSG_UNREAD = 0
  20 MSG_READ = 1
  21
  22
  23 class DatabaseHandler:
  24
  25         def __init__(self):
  26                 self.config = fMMSconf.fMMS_config()
  27                 self.pushdir = self.config.get_pushdir()
  28                 self.mmsdir = self.config.get_mmsdir()
  29                 self.outdir = self.config.get_outdir()
  30                 self.db = self.config.get_db_path()
  31                 self.conn = sqlite3.connect(self.db)
  32                 self.conn.row_factory = sqlite3.Row
  33                 try:
  34                         c = self.conn.cursor()
  35                         c.execute("SELECT * FROM revision")
  36                         for row in c:
  37                                 if row['version'] != 1:
  38                                         self.create_database_layout()
  39                 except:
  40                         self.create_database_layout()
  41
  42
  43         def create_database_layout(self):
  44                 c = self.conn
  45                 c.execute("""CREATE TABLE "revision" ("version" INT);""")
  46                 c.execute("""INSERT INTO "revision" ("version") VALUES ('1');""")
  47                 # database layout
  48                 c.execute("""CREATE TABLE "push"(
  49                           "idpush" INTEGER PRIMARY KEY NOT NULL,
  50                           "transactionid" TEXT NOT NULL,
  51                           "content_location" TEXT NULL,
  52                           "msg_time" TIMESTAMP,
  53                           "msg_type" TEXT NOT NULL,
  54                           "file" TEXT
  55                         );""")
  56                 c.execute("""CREATE TABLE "contacts"(
  57                           "idcontacts" INTEGER PRIMARY KEY NOT NULL,
  58                           "number" INTEGER NOT NULL,
  59                           "abook_uid" INTEGER DEFAULT NULL
  60                         );""")
  61                 c.execute("""CREATE TABLE "mms"(
  62                           "id" INTEGER PRIMARY KEY NOT NULL,
  63                           "pushid" INTEGER DEFAULT NULL,
  64                           "transactionid" INTEGER DEFAULT NULL,
  65                           "msg_time" TIMESTAMP DEFAULT NULL,
  66                           "read" INTEGER DEFAULT NULL,
  67                           "direction" INTEGER DEFAULT NULL,
  68                           "size" INT DEFAULT NULL,
  69                           "contact" INTEGER DEFAULT NULL,
  70                           "file" TEXT DEFAULT NULL,
  71                           CONSTRAINT "pushid"
  72                             FOREIGN KEY("pushid")
  73                             REFERENCES "push"("idpush"),
  74                           CONSTRAINT "contact"
  75                             FOREIGN KEY("contact")
  76                             REFERENCES "contacts"("idcontacts")
  77                         );""")
  78                 c.execute("""CREATE INDEX "mms.pushid" ON "mms"("pushid");""")
  79                 c.execute("""CREATE INDEX "mms.contact" ON "mms"("contact");""")
  80                 c.execute("""CREATE TABLE "mms_headers"(
  81                           "idmms_headers" INTEGER PRIMARY KEY NOT NULL,
  82                           "mms_id" INTEGER DEFAULT NULL,
  83                           "header" TEXT DEFAULT NULL,
  84                           "value" TEXT DEFAULT NULL,
  85                           CONSTRAINT "mms_id"
  86                             FOREIGN KEY("mms_id")
  87                             REFERENCES "mms"("id")
  88                         );""")
  89                 c.execute("""CREATE INDEX "mms_headers.mms_id" ON "mms_headers"("mms_id");""")
  90                 c.execute("""CREATE TABLE "attachments"(
  91                           "idattachments" INTEGER PRIMARY KEY NOT NULL,
  92                           "mmsidattach" INTEGER DEFAULT NULL,
  93                           "file" TEXT DEFAULT NULL,
  94                           "hidden" INTEGER DEFAULT NULL,
  95                           CONSTRAINT "mmsidattach"
  96                             FOREIGN KEY("mmsidattach")
  97                             REFERENCES "mms"("id")
  98                         );""")
  99                 c.execute("""CREATE INDEX "attachments.mmsidattach" ON "attachments"("mmsidattach");""")
 100                 c.execute("""CREATE TABLE "push_headers"(
 101                           "idpush_headers" INTEGER PRIMARY KEY NOT NULL,
 102                           "push_id" INTEGER DEFAULT NULL,
 103                           "header" TEXT DEFAULT NULL,
 104                           "value" TEXT DEFAULT NULL,
 105                           CONSTRAINT "push_id"
 106                             FOREIGN KEY("push_id")
 107                             REFERENCES "push"("idpush")
 108                         );""")
 109                 c.execute("""CREATE INDEX "push_headers.push_id" ON "push_headers"("push_id");""")
 110                 self.conn.commit()
 111
 112
 113         def get_push_list(self, types=None):
 114                 """ gets all push messages from the db and returns as a list
 115                 containing a dict for each separate push """
 116                 c = self.conn.cursor()
 117                 retlist = []
 118                 # TODO: better where clause
 119                 c.execute("select * from push where msg_type != 'm-notifyresp-ind' order by msg_time DESC")
 120                 pushlist = c.fetchall()
 121                 for line in pushlist:
 122                         result = {}
 123                         result['PUSHID'] = line['idpush']
 124                         result['Transaction-Id'] = line['transactionid']
 125                         result['Content-Location'] = line['content_location']
 126                         result['Time'] = line['msg_time']
 127                         result['Message-Type'] = line['msg_type']
 128                         c.execute("select * from push_headers WHERE push_id = ?", (line['idpush'],))
 129                         for line2 in c:
 130                                 result[line2['header']] = line2['value']
 131
 132                         retlist.append(result)
 133
 134                 return retlist
 135
 136         def insert_push_message(self, pushlist):
 137                 """ Inserts a push message (from a list)
 138                 Returns the id of the inserted row
 139
 140                 """
 141                 c = self.conn.cursor()
 142                 conn = self.conn
 143                 try:
 144                         transid = pushlist['Transaction-Id']
 145                         del pushlist['Transaction-Id']
 146                         contentloc = pushlist['Content-Location']
 147                         del pushlist['Content-Location']
 148                         msgtype = pushlist['Message-Type']
 149                         del pushlist['Message-Type']
 150                 except:
 151                         print "No transid/contentloc/message-type, bailing out!"
 152                         raise
 153                 fpath = self.pushdir + transid
 154                 vals = (transid, contentloc, msgtype, fpath)
 155                 c.execute("insert into push (transactionid, content_location, msg_time, msg_type, file) VALUES (?, ?, datetime('now'), ?, ?)", vals)
 156                 pushid = c.lastrowid
 157                 conn.commit()
 158                 print "inserted row as:", pushid
 159
 160                 for line in pushlist:
 161                         vals = (pushid, line, str(pushlist[line]))
 162                         c.execute("insert into push_headers (push_id, header, value) VALUES (?, ?, ?)", vals)
 163                         conn.commit()
 164
 165                 return pushid
 166
 167         def insert_push_send(self, pushlist):
 168                 """ Inserts a push message (from a list)
 169                 Returns the id of the inserted row
 170
 171                 """
 172                 c = self.conn.cursor()
 173                 conn = self.conn
 174                 try:
 175                         transid = pushlist['Transaction-Id']
 176                         del pushlist['Transaction-Id']
 177                         msgtype = pushlist['Message-Type']
 178                         del pushlist['Message-Type']
 179                 except:
 180                         print "No transid/message-type, bailing out!"
 181                         raise
 182                 fpath = self.outdir + transid
 183                 vals = (transid, 0, msgtype, fpath)
 184                 c.execute("insert into push (transactionid, content_location, msg_time, msg_type, file) VALUES (?, ?, datetime('now'), ?, ?)", vals)
 185                 pushid = c.lastrowid
 186                 conn.commit()
 187                 print "inserted row as:", pushid
 188
 189                 for line in pushlist:
 190                         vals = (pushid, line, str(pushlist[line]))
 191                         c.execute("insert into push_headers (push_id, header, value) VALUES (?, ?, ?)", vals)
 192                         conn.commit()
 193
 194                 return pushid
 195
 196         def link_push_mms(self, pushid, mmsid):
 197                 c = self.conn.cursor()
 198                 c.execute("update mms set pushid = ? where id = ?", (pushid, mmsid))
 199                 self.conn.commit()
 200
 201
 202         def get_push_message(self, transid):
 203                 """ retrieves a push message from the db and returns it as a dict """
 204                 c = self.conn.cursor()
 205                 retlist = {}
 206                 vals = (transid,)
 207                 c.execute("select * from push WHERE transactionid = ? LIMIT 1;", vals)
 208
 209                 for line in c:
 210                         pushid = line['idpush']
 211                         retlist['Transaction-Id'] = line['transactionid']
 212                         retlist['Content-Location'] = line['content_location']
 213                         retlist['Message-Type'] = line['msg_type']
 214                         retlist['Time'] = line['msg_time']
 215                         retlist['File'] = line['file']
 216                         retlist['PUSHID'] = pushid
 217
 218                 try:
 219                         c.execute("select * from push_headers WHERE push_id = ?;", (pushid, ))
 220                 except Exception, e:
 221                         raise
 222
 223                 for line in c:
 224                         hdr = line['header']
 225                         val = line['value']
 226                         retlist[hdr] = val
 227
 228                 return retlist
 229
 230
 231         def is_mms_downloaded(self, transid):
 232                 c = self.conn.cursor()
 233                 vals = (transid,)
 234                 isread = None
 235                 c.execute("select * from mms where `transactionid` = ?;", vals)
 236                 for line in c:
 237                         isread = line['id']
 238                 if isread != None:
 239                         return True
 240                 else:
 241                         return False
 242
 243
 244         def is_message_read(self, transactionid):
 245                 c = self.conn.cursor()
 246                 vals = (transactionid,)
 247                 isread = None
 248                 c.execute("select read from mms where `transactionid` = ?;", vals)
 249                 for line in c:
 250                         isread = line['read']
 251                 if isread == 1:
 252                         return True
 253                 else:
 254                         return False
 255
 256         def insert_mms_message(self, pushid, message, direction=MSG_DIRECTION_IN):
 257                 """Takes a MMSMessage object as input, and optionally a MSG_DIRECTION_*
 258                 Returns the newly inserted rows id.
 259
 260                 """
 261                 #print direction
 262                 mmslist = message.headers
 263                 attachments = message.attachments
 264                 #mmslist = message
 265                 c = self.conn.cursor()
 266                 conn = self.conn
 267                 try:
 268                         transid = mmslist['Transaction-Id']
 269                         del mmslist['Transaction-Id']
 270                         if direction == MSG_DIRECTION_OUT:
 271                                 basedir = self.outdir + transid
 272                         else:
 273                                 basedir = self.mmsdir + transid
 274
 275                         fpath = basedir + "/message"
 276                         size = os.path.getsize(fpath)
 277                 except:
 278                         print "No transid/message-type, bailing out!"
 279                         raise
 280                 try:
 281                         time = mmslist['Date']
 282                         del mmslist['Date']
 283                 except:
 284                         time = "datetime('now')"
 285                 isread = MSG_UNREAD
 286                 contact = 0
 287                 vals = (pushid, transid, time, isread, direction, size, contact, fpath)
 288                 c.execute("insert into mms (pushid, transactionid, msg_time, read, direction, size, contact, file) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", vals)
 289                 mmsid = c.lastrowid
 290                 conn.commit()
 291                 print "inserted row as:", mmsid
 292
 293                 # insert all headers
 294                 for line in mmslist:
 295                         vals = (mmsid, line, str(mmslist[line]))
 296                         c.execute("insert into mms_headers (mms_id, header, value) VALUES (?, ?, ?)", vals)
 297                         conn.commit()
 298                 attachpaths = basedir + "/"
 299                 #print attachpaths
 300                 # insert the attachments
 301                 for line in attachments:
 302                         print line
 303                         filetype = gnomevfs.get_mime_type(attachpaths + line)
 304                         (fname, ext) = os.path.splitext(line)
 305                         hidden = 0
 306                         # These files should be "hidden" from the user
 307                         if ext.startswith(".smil") or filetype == "application/smil":
 308                                 hidden = 1
 309                         vals = (mmsid, line, hidden)
 310                         c.execute("insert into attachments (mmsidattach, file, hidden) VALUES (?, ?, ?)", vals)
 311                         conn.commit()
 312                         #print "inserted", vals
 313
 314                 return mmsid
 315
 316         def get_mms_attachments(self, transactionid, allFiles=False):
 317                 c = self.conn.cursor()
 318                 mmsid = self.get_mmsid_from_transactionid(transactionid)
 319                 if mmsid != None:
 320                         if allFiles == True:
 321                                 c.execute("select * from attachments where mmsidattach == ?", (mmsid,))
 322                         else:
 323                                 c.execute("select * from attachments where mmsidattach == ? and hidden == 0", (mmsid,))
 324                         filelist = []
 325                         for line in c:
 326                                 filelist.append(line['file'])
 327
 328                         return filelist
 329
 330
 331         def get_mms_headers(self, transactionid):
 332                 c = self.conn.cursor()
 333                 mmsid = self.get_mmsid_from_transactionid(transactionid)
 334                 retlist = {}
 335
 336                 c.execute("select * from mms WHERE id = ? LIMIT 1;", (mmsid,))
 337
 338                 for line in c:
 339                         retlist['Transaction-Id'] = line['transactionid']
 340                         retlist['Time'] = line['msg_time']
 341
 342                 if mmsid != None:
 343                         c.execute("select * from mms_headers WHERE mms_id = ?;", (mmsid, ))
 344                         for line in c:
 345                                 hdr = line['header']
 346                                 val = line['value']
 347                                 retlist[hdr] = val
 348                 return retlist
 349
 350         def get_mmsid_from_transactionid(self, transactionid):
 351                 c = self.conn.cursor()
 352                 c.execute("select * from mms where transactionid == ?", (transactionid, ))
 353                 res = c.fetchone()
 354                 try:
 355                         mmsid = res['id']
 356                         return mmsid
 357                 except:
 358                         return None
 359
 360         def get_direction_mms(self, transid):
 361                 c = self.conn.cursor()
 362                 c.execute("select direction from mms where transactionid = ?", (transid, ))
 363                 res = c.fetchone()
 364                 try:
 365                         direction = res['direction']
 366                         return direction
 367                 except:
 368                         return None
 369
 370
 371         def get_pushid_from_transactionid(self, transactionid):
 372                 c = self.conn.cursor()
 373                 c.execute("select * from push where transactionid == ?", (transactionid, ))
 374                 res = c.fetchone()
 375                 try:
 376                         mmsid = res['idpush']
 377                         return mmsid
 378                 except:
 379                         return None
 380
 381         def delete_mms_message(self, transactionid):
 382                 c = self.conn.cursor()
 383                 mmsid = self.get_mmsid_from_transactionid(transactionid)
 384                 if mmsid != None:
 385                         c.execute("delete from mms where id == ?", (mmsid,))
 386                         c.execute("delete from attachments where mmsidattach == ?", (mmsid,))
 387                         c.execute("delete from mms_headers where mms_id == ?", (mmsid,))
 388                         self.conn.commit()
 389
 390         def delete_push_message(self, transactionid):
 391                 c = self.conn.cursor()
 392                 pushid = self.get_pushid_from_transactionid(transactionid)
 393                 if pushid != None:
 394                         c.execute("delete from push where idpush == ?", (pushid,))
 395                         c.execute("delete from push_headers where push_id == ?", (pushid,))
 396                         self.conn.commit()
 397
 398
 399 if __name__ == '__main__':
 400         db = DatabaseHandler()
 401         c = db.conn.cursor()
 402         #print db.get_push_list()
 403         #print db.get_mms_headers("1tid46730354431_2zzhez")