vvvvget/db.py

import os
import sqlite3

class VVVVIDatabase():

	def __init__(self, path):
		self.path = path
		self.is_valid = os.path.isfile(self.path)
		self.con = sqlite3.connect(self.path)

	def __del__(self):
		self.con.close()

	def create(self):
		cur = self.con.cursor()
		cur.execute("CREATE TABLE series (id INTEGER NOT NULL, name TEXT NOT NULL, season_id INTEGER NOT NULL, type TEXT NOT NULL, PRIMARY KEY (id, season_id));")
		cur.execute("CREATE TABLE episodes (id INTEGER NOT NULL, serie_id INTEGER NOT NULL, season_id INTEGER NOT NULL, cdn_url TEXT NOT NULL, type TEXT NOT NULL, PRIMARY KEY (id, serie_id, season_id, cdn_url, type));")
		self.con.commit()

	def last_serie(self):
		cur = self.con.cursor()
		cur.execute("SELECT id FROM series ORDER BY id DESC LIMIT 1;")
		rows = cur.fetchall()
		last = (rows[0][0] + 1) if len(rows) > 0 else 0
		self.con.commit()
		return last

	def series_id(self):
		cur = self.con.cursor()
		cur.execute("SELECT id, season_id FROM series ORDER BY id, season_id DESC;")
		rows = cur.fetchall()
		self.con.commit()
		# getting series in a useful format for later
		series = {}
		for i, s in rows:
			if series.get(i) is None:
				series[i] = []
			series[i].append(s)
		return series

	def series_episodes(self, serie_id):
		cur = self.con.cursor()
		cur.execute("SELECT id, serie_id, season_id FROM episodes ORDER BY id, serie_id, season_id DESC;")
		rows = cur.fetchall()
		self.con.commit()
		# getting series in a useful format for later
		series = {}
		for i, s in rows:
			if series.get(i) is None:
				series[i] = []
			series[i].append(s)
		return series

	def insert_serie(self, serie):
		cur = self.con.cursor()
		try:
			cur.execute("INSERT INTO series (id, name, season_id, type) VALUES (?, ?, ?, ?);", serie)
			self.con.commit()
			return True
		except sqlite3.IntegrityError:
			# serie gia' presente
			pass
		return False

	def insert_episodes(self, eps):
		cur = self.con.cursor()
		for ep in eps:
			try:
				cur.execute("INSERT INTO episodes (id, serie_id, season_id, type, cdn_url) VALUES (?, ?, ?, ?, ?);", ep)
				self.con.commit()
			except sqlite3.IntegrityError:
				# episodi gia' presenti
				pass