db.py

   1 #!/usr/bin/python
   2
   3 import MySQLdb
   4 import ConfigParser
   5
   6 def SortName(name):
   7   if database:
   8     c = database.cursor()
   9     c.execute('SELECT metadata.SortStr(%s)', (name))
  10     return c.fetchone()[0]
  11   else:
  12     print "No connection to DB"
  13     exit()
  14
  15 def SortAuthorName(name):
  16   if database:
  17     c = database.cursor()
  18     c.execute('SELECT metadata.SortAuthor(%s)', (name))
  19     return c.fetchone()[0]
  20   else:
  21     print "No connection to DB"
  22     exit()
  23
  24 def GetOrCreateAuthor(name):
  25   if database:
  26     c = database.cursor()
  27     c.execute('SELECT metadata.GetOrCreateAuthor(%s)', (name))
  28     return c.fetchone()[0]
  29   else:
  30     print "No connection to DB"
  31     exit()
  32
  33 def GetOrCreateLang(name):
  34   if database:
  35     c = database.cursor()
  36     c.execute('SELECT metadata.GetOrCreateLang(%s)', (name))
  37     return c.fetchone()[0]
  38   else:
  39     print "No connection to DB"
  40     exit()
  41
  42 def GetOrCreatePublisher(name):
  43   if database:
  44     c = database.cursor()
  45     c.execute('SELECT metadata.GetOrCreatePublisher(%s)', (name))
  46     return c.fetchone()[0]
  47   else:
  48     print "No connection to DB"
  49     exit()
  50
  51 def GetOrCreateSeries(name):
  52   if database:
  53     c = database.cursor()
  54     c.execute('SELECT metadata.GetOrCreateSeries(%s)', (name))
  55     return c.fetchone()[0]
  56   else:
  57     print "No connection to DB"
  58     exit()
  59
  60 def GetOrCreateTag(name):
  61   if database:
  62     c = database.cursor()
  63     c.execute('SELECT metadata.GetOrCreateTag(%s)', (name))
  64     return c.fetchone()[0]
  65   else:
  66     print "No connection to DB"
  67     exit()
  68
  69 def CreateBook(title,pubdate,series_index,isbn):
  70   if database:
  71     c = database.cursor()
  72     c.execute('SELECT metadata.CreateBook(%s,%s,%s,%s)', (title,pubdate,series_index,isbn))
  73     return c.fetchone()[0]
  74   else:
  75     print "No connection to DB"
  76     exit()
  77
  78 def LinkBookToAuthors(book_id,author_ids):
  79   if database:
  80     c = database.cursor()
  81     for author_id in author_ids:
  82       c.execute('INSERT INTO metadata.books_authors_link(book,author) VALUES (%s,%s)', (book_id,author_id))
  83   else:
  84     print "No connection to DB"
  85     exit()
  86
  87 def LinkBookToLangs(book_id,lang_ids):
  88   if database:
  89     c = database.cursor()
  90     io = 0
  91     for lang_id in lang_ids:
  92       io = io + 1
  93       c.execute('INSERT INTO metadata.books_languages_link(book,lang_code,item_order) VALUES (%s,%s,%s)', (book_id,lang_id,io))
  94   else:
  95     print "No connection to DB"
  96     exit()
  97
  98 def LinkBookToPublishers(book_id,pub_id):
  99   if database:
 100     c = database.cursor()
 101     c.execute('INSERT INTO metadata.books_publishers_link(book,publisher) VALUES (%s,%s)', (book_id,pub_id))
 102   else:
 103     print "No connection to DB"
 104     exit()
 105
 106 def LinkBookToSeries(book_id,ser_id):
 107   if database:
 108     c = database.cursor()
 109     c.execute('INSERT INTO metadata.books_series_link(book,series) VALUES (%s,%s)', (book_id,ser_id))
 110   else:
 111     print "No connection to DB"
 112     exit()
 113
 114 def LinkBookToTags(book_id,tag_ids):
 115   if database:
 116     c = database.cursor()
 117     for tag_id in tag_ids:
 118       c.execute('INSERT INTO metadata.books_tags_link(book,tag) VALUES (%s,%s)', (book_id,tag_id))
 119   else:
 120     print "No connection to DB"
 121     exit()
 122
 123 def SetPath(book_id,path,dataname,filesize,cover):
 124   if database:
 125     c = database.cursor()
 126     c.execute('UPDATE metadata.books SET path=%s, has_cover=%s WHERE id=%s', (path,cover,book_id))
 127     c.execute('INSERT INTO metadata.data(book,format,uncompressed_size,name) values (%s,%s,%s,%s)',(book_id,'FB2',filesize,dataname))
 128   else:
 129     print "No connection to DB"
 130     exit()
 131
 132 def StoreComment(book_id,comment):
 133   if database:
 134     c = database.cursor()
 135     c.execute('INSERT INTO metadata.comments(book,text) values (%s,%s)',(book_id,comment))
 136   else:
 137     print "No connection to DB"
 138     exit()
 139
 140 def PathByID(book_id):
 141   if database:
 142     c = database.cursor()
 143     c.execute('SELECT path FROM metadata.books WHERE id=%s',(book_id))
 144     return c.fetchone()[0]
 145   else:
 146     print "No connection to DB"
 147     exit()
 148
 149 def DataByID(book_id,format):
 150   if database:
 151     c = database.cursor()
 152     c.execute('SELECT name FROM metadata.data WHERE book=%s and format=%s',(book_id,format))
 153     return c.fetchone()[0]+'.'+format.lower()
 154   else:
 155     print "No connection to DB"
 156     exit()
 157
 158 def DelBook(book_id):
 159   if database:
 160     c = database.cursor()
 161     c.execute('DELETE FROM metadata.books WHERE id=%s',(book_id))
 162   else:
 163     print "No connection to DB"
 164     exit()
 165
 166 def ChangeBookFormat(book_id,old_format,new_format):
 167   if database:
 168     c = database.cursor()
 169     c.execute('UPDATE metadata.data SET format=%s WHERE book=%s and format=%s',(new_format,book_id,old_format))
 170   else:
 171     print "No connection to DB"
 172     exit()
 173
 174 def TestArchive(name):
 175   if database:
 176     c = database.cursor()
 177     c.execute('SELECT count(*) from metadata.processed_archives WHERE filename=%s',(name))
 178     return c.fetchone()[0]
 179   else:
 180     print "No connection to DB"
 181     exit()
 182
 183 def MarkArchive(name):
 184   if database:
 185     c = database.cursor()
 186     c.execute('insert into metadata.processed_archives(filename) values (%s)',(name))
 187   else:
 188     print "No connection to DB"
 189     exit()
 190
 191 def ListByFormat(format,limit=100):
 192   if database:
 193     c = database.cursor()
 194     c.execute('SELECT DISTINCT book FROM metadata.data WHERE format=%s ORDER BY book LIMIT 0,%s',(format,limit))
 195     return c.fetchall()
 196   else:
 197     print "No connection to DB"
 198     exit()
 199
 200 def ListDups(limit=100):
 201   if database:
 202     c = database.cursor()
 203     c.execute('SELECT b.title,l.author,max(b.id) id FROM metadata.books b,metadata.books_authors_link l where b.id=l.book group by b.title,l.author having count(*)>%s',(limit))
 204     return c.fetchall()
 205   else:
 206     print "No connection to DB"
 207     exit()
 208
 209 def ListByTitleAndAuthor(title,author,id=0):
 210   if database:
 211     c = database.cursor()
 212     c.execute('SELECT b.id FROM metadata.books b,metadata.books_authors_link l where b.id=l.book and b.title=%s and l.author=%s and b.id<>%s',(title,author,id))
 213     return c.fetchall()
 214   else:
 215     print "No connection to DB"
 216     exit()
 217
 218
 219 def Commit():
 220   if database:
 221     database.commit()
 222   else:
 223     print "No connection to DB"
 224     exit()
 225
 226 def Rollback():
 227   if database:
 228     database.rollback()
 229   else:
 230     print "No connection to DB"
 231     exit()
 232
 233
 234 try:
 235
 236   cfg = ConfigParser.RawConfigParser(allow_no_value=True)
 237   cfg.readfp(open('/etc/openlib.conf'))
 238   dbhost = cfg.get("mysql","host")
 239   dbuser = cfg.get("mysql","user")
 240   dbpasswd = cfg.get("mysql","passwd")
 241   file_root = cfg.get("storage","files")
 242   tmp_files = cfg.get("storage","temp")
 243   failed_files = cfg.get("storage","failed")
 244   upload_files = cfg.get("storage","upload")
 245
 246 except:
 247
 248   print "Error reading configuration file"
 249   exit()
 250
 251 try:
 252
 253   database = MySQLdb.connect(host=dbhost,user=dbuser,passwd=dbpasswd,use_unicode=True)
 254   database.set_character_set('utf8')
 255   c = database.cursor()
 256   c.execute('SET NAMES utf8;')
 257
 258 except:
 259
 260   print "Error connecting database"
 261   exit()
 262