diff options
Diffstat (limited to 'module/database')
| -rw-r--r-- | module/database/AccountDatabase.py | 21 | ||||
| -rw-r--r-- | module/database/ConfigDatabase.py | 49 | ||||
| -rw-r--r-- | module/database/DatabaseBackend.py | 517 | ||||
| -rw-r--r-- | module/database/FileDatabase.py | 1193 | ||||
| -rw-r--r-- | module/database/StatisticDatabase.py | 13 | ||||
| -rw-r--r-- | module/database/StorageDatabase.py | 9 | ||||
| -rw-r--r-- | module/database/UserDatabase.py | 167 | ||||
| -rw-r--r-- | module/database/__init__.py | 10 |
8 files changed, 833 insertions, 1146 deletions
diff --git a/module/database/AccountDatabase.py b/module/database/AccountDatabase.py new file mode 100644 index 000000000..1602451fa --- /dev/null +++ b/module/database/AccountDatabase.py @@ -0,0 +1,21 @@ +# -*- coding: utf-8 -*- + +from module.database import queue, async +from module.database import DatabaseBackend + +class AccountMethods: + + @queue + def loadAccounts(db): + db.c.execute('SELECT plugin, loginname, activated, password, options FROM accounts;') + return db.c.fetchall() + + @async + def saveAccounts(db, data): + db.c.executemany('INSERT INTO accounts(plugin, loginname, activated, password, options) VALUES(?,?,?,?,?)', data) + + @async + def removeAccount(db, plugin, loginname): + db.c.execute('DELETE FROM accounts WHERE plugin=? AND loginname=?', (plugin, loginname)) + +DatabaseBackend.registerSub(AccountMethods)
\ No newline at end of file diff --git a/module/database/ConfigDatabase.py b/module/database/ConfigDatabase.py new file mode 100644 index 000000000..198ae0173 --- /dev/null +++ b/module/database/ConfigDatabase.py @@ -0,0 +1,49 @@ +#!/usr/bin/env python +# -*- coding: utf-8 -*- + +from module.database import DatabaseMethods, queue, async + +class ConfigMethods(DatabaseMethods): + + @async + def saveConfig(self, plugin, config, user=None): + if user is None: + self.c.execute('INSERT INTO settings(plugin, config) VALUES(?,?)', (plugin, config)) + else: + self.c.execute('INSERT INTO settings(plugin, config, user) VALUES(?,?,?)', (plugin, config, user)) + + + @queue + def loadConfig(self, plugin, user=None): + if user is None: + self.c.execute('SELECT config FROM settings WHERE plugin=?', (plugin, )) + else: + self.c.execute('SELECT config FROM settings WHERE plugin=? AND user=?', (plugin, user)) + + return self.c.fetchone()[0] + + @async + def deleteConfig(self, plugin, user=None): + if user is None: + self.c.execute('DELETE FROM settings WHERE plugin=?', (plugin, )) + else: + self.c.execute('DELETE FROM settings WHERE plugin=? AND user=?', (plugin, user)) + + @queue + def loadAllConfigs(self): + self.c.execute('SELECT user, plugin, config FROM settings') + configs = {} + for r in self.c: + if r[0] in configs: + configs[r[0]][r[1]] = r[2] + else: + configs[r[0]] = {r[1]: r[2]} + + return configs + + @async + def clearAllConfigs(self): + self.c.execute('DELETE FROM settings') + + +ConfigMethods.register()
\ No newline at end of file diff --git a/module/database/DatabaseBackend.py b/module/database/DatabaseBackend.py index 9530390c3..58e1e74d8 100644 --- a/module/database/DatabaseBackend.py +++ b/module/database/DatabaseBackend.py @@ -1,86 +1,98 @@ #!/usr/bin/env python -""" - This program is free software; you can redistribute it and/or modify - it under the terms of the GNU General Public License as published by - the Free Software Foundation; either version 3 of the License, - or (at your option) any later version. - - This program is distributed in the hope that it will be useful, - but WITHOUT ANY WARRANTY; without even the implied warranty of - MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. - See the GNU General Public License for more details. - - You should have received a copy of the GNU General Public License - along with this program; if not, see <http://www.gnu.org/licenses/>. - - @author: RaNaN - @author: mkaay -""" -from threading import Thread -from threading import Event -from os import remove -from os.path import exists +# -*- coding: utf-8 -*- + +############################################################################### +# Copyright(c) 2008-2012 pyLoad Team +# http://www.pyload.org +# +# This file is part of pyLoad. +# pyLoad is free software: you can redistribute it and/or modify +# it under the terms of the GNU Affero General Public License as +# published by the Free Software Foundation, either version 3 of the +# License, or (at your option) any later version. +# +# Subjected to the terms and conditions in LICENSE +# +# @author: RaNaN, mkaay +############################################################################### + +from threading import Thread, Event from shutil import move from Queue import Queue from traceback import print_exc -from module.utils import chmod +from module.utils.fs import chmod, exists, remove try: from pysqlite2 import dbapi2 as sqlite3 except: import sqlite3 -DB_VERSION = 4 +DB = None +DB_VERSION = 5 + +def set_DB(db): + global DB + DB = db + + +def queue(f): + @staticmethod + def x(*args, **kwargs): + if DB: + return DB.queue(f, *args, **kwargs) + + return x + + +def async(f): + @staticmethod + def x(*args, **kwargs): + if DB: + return DB.async(f, *args, **kwargs) + + return x + + +def inner(f): + @staticmethod + def x(*args, **kwargs): + if DB: + return f(DB, *args, **kwargs) + + return x + + +class DatabaseMethods: + # stubs for autocompletion + core = None + manager = None + conn = None + c = None -class style(): - db = None - - @classmethod - def setDB(cls, db): - cls.db = db - - @classmethod - def inner(cls, f): - @staticmethod - def x(*args, **kwargs): - if cls.db: - return f(cls.db, *args, **kwargs) - return x - - @classmethod - def queue(cls, f): - @staticmethod - def x(*args, **kwargs): - if cls.db: - return cls.db.queue(f, *args, **kwargs) - return x - @classmethod - def async(cls, f): - @staticmethod - def x(*args, **kwargs): - if cls.db: - return cls.db.async(f, *args, **kwargs) - return x + def register(cls): + DatabaseBackend.registerSub(cls) + class DatabaseJob(): def __init__(self, f, *args, **kwargs): self.done = Event() - + self.f = f self.args = args self.kwargs = kwargs - + self.result = None self.exception = False -# import inspect -# self.frame = inspect.currentframe() + # import inspect + # self.frame = inspect.currentframe() def __repr__(self): from os.path import basename + frame = self.frame.f_back output = "" for i in range(5): @@ -104,46 +116,76 @@ class DatabaseJob(): self.exception = e finally: self.done.set() - + def wait(self): self.done.wait() + class DatabaseBackend(Thread): subs = [] + + DB_FILE = "pyload.db" + VERSION_FILE = "db.version" + def __init__(self, core): Thread.__init__(self) self.setDaemon(True) self.core = core + self.manager = None # set later + self.running = Event() self.jobs = Queue() - - self.setuplock = Event() - - style.setDB(self) - + + set_DB(self) + def setup(self): + """ *MUST* be called before db can be used !""" self.start() - self.setuplock.wait() - - def run(self): + self.running.wait() + + def init(self): """main loop, which executes commands""" - convert = self._checkVersion() #returns None or current version - - self.conn = sqlite3.connect("files.db") - chmod("files.db", 0600) - - self.c = self.conn.cursor() #compatibility - - if convert is not None: - self._convertDB(convert) - - self._createTables() - self._migrateUser() + version = self._checkVersion() + + self.conn = sqlite3.connect(self.DB_FILE) + chmod(self.DB_FILE, 0600) + + self.c = self.conn.cursor() + + if version is not None and version < DB_VERSION: + success = self._convertDB(version) + + # delete database + if not success: + self.c.close() + self.conn.close() + + try: + self.manager.core.log.warning(_("File database was deleted due to incompatible version.")) + except: + print "File database was deleted due to incompatible version." + + remove(self.VERSION_FILE) + move(self.DB_FILE, self.DB_FILE + ".backup") + f = open(self.VERSION_FILE, "wb") + f.write(str(DB_VERSION)) + f.close() + + self.conn = sqlite3.connect(self.DB_FILE) + chmod(self.DB_FILE, 0600) + self.c = self.conn.cursor() + + self._createTables() self.conn.commit() - - self.setuplock.set() - + + + def run(self): + try: + self.init() + finally: + self.running.set() + while True: j = self.jobs.get() if j == "quit": @@ -152,201 +194,298 @@ class DatabaseBackend(Thread): break j.processJob() - @style.queue + def shutdown(self): + self.running.clear() + self._shutdown() + + @queue + def _shutdown(self): self.conn.commit() self.jobs.put("quit") def _checkVersion(self): - """ check db version and delete it if needed""" - if not exists("files.version"): - f = open("files.version", "wb") + """ get db version""" + if not exists(self.VERSION_FILE): + f = open(self.VERSION_FILE, "wb") f.write(str(DB_VERSION)) f.close() return - - f = open("files.version", "rb") + + f = open(self.VERSION_FILE, "rb") v = int(f.read().strip()) f.close() - if v < DB_VERSION: - if v < 2: - try: - self.manager.core.log.warning(_("Filedatabase was deleted due to incompatible version.")) - except: - print "Filedatabase was deleted due to incompatible version." - remove("files.version") - move("files.db", "files.backup.db") - f = open("files.version", "wb") - f.write(str(DB_VERSION)) - f.close() - return v - + + return v + def _convertDB(self, v): try: - getattr(self, "_convertV%i" % v)() + return getattr(self, "_convertV%i" % v)() except: - try: - self.core.log.error(_("Filedatabase could NOT be converted.")) - except: - print "Filedatabase could NOT be converted." - + return False + #--convert scripts start - - def _convertV2(self): - self.c.execute('CREATE TABLE IF NOT EXISTS "storage" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "identifier" TEXT NOT NULL, "key" TEXT NOT NULL, "value" TEXT DEFAULT "")') - try: - self.manager.core.log.info(_("Database was converted from v2 to v3.")) - except: - print "Database was converted from v2 to v3." - self._convertV3() - - def _convertV3(self): - self.c.execute('CREATE TABLE IF NOT EXISTS "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT NOT NULL, "email" TEXT DEFAULT "" NOT NULL, "password" TEXT NOT NULL, "role" INTEGER DEFAULT 0 NOT NULL, "permission" INTEGER DEFAULT 0 NOT NULL, "template" TEXT DEFAULT "default" NOT NULL)') - try: - self.manager.core.log.info(_("Database was converted from v3 to v4.")) - except: - print "Database was converted from v3 to v4." - + + def _convertV5(self): + return False + #--convert scripts end - + def _createTables(self): """create tables for database""" - self.c.execute('CREATE TABLE IF NOT EXISTS "packages" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT NOT NULL, "folder" TEXT, "password" TEXT DEFAULT "", "site" TEXT DEFAULT "", "queue" INTEGER DEFAULT 0 NOT NULL, "packageorder" INTEGER DEFAULT 0 NOT NULL)') - self.c.execute('CREATE TABLE IF NOT EXISTS "links" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "url" TEXT NOT NULL, "name" TEXT, "size" INTEGER DEFAULT 0 NOT NULL, "status" INTEGER DEFAULT 3 NOT NULL, "plugin" TEXT DEFAULT "BasePlugin" NOT NULL, "error" TEXT DEFAULT "", "linkorder" INTEGER DEFAULT 0 NOT NULL, "package" INTEGER DEFAULT 0 NOT NULL, FOREIGN KEY(package) REFERENCES packages(id))') - self.c.execute('CREATE INDEX IF NOT EXISTS "pIdIndex" ON links(package)') - self.c.execute('CREATE TABLE IF NOT EXISTS "storage" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "identifier" TEXT NOT NULL, "key" TEXT NOT NULL, "value" TEXT DEFAULT "")') - self.c.execute('CREATE TABLE IF NOT EXISTS "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT NOT NULL, "email" TEXT DEFAULT "" NOT NULL, "password" TEXT NOT NULL, "role" INTEGER DEFAULT 0 NOT NULL, "permission" INTEGER DEFAULT 0 NOT NULL, "template" TEXT DEFAULT "default" NOT NULL)') - - self.c.execute('CREATE VIEW IF NOT EXISTS "pstats" AS \ - SELECT p.id AS id, SUM(l.size) AS sizetotal, COUNT(l.id) AS linkstotal, linksdone, sizedone\ - FROM packages p JOIN links l ON p.id = l.package LEFT OUTER JOIN\ - (SELECT p.id AS id, COUNT(*) AS linksdone, SUM(l.size) AS sizedone \ - FROM packages p JOIN links l ON p.id = l.package AND l.status in (0,4,13) GROUP BY p.id) s ON s.id = p.id \ - GROUP BY p.id') + self.c.execute( + 'CREATE TABLE IF NOT EXISTS "packages" (' + '"pid" INTEGER PRIMARY KEY AUTOINCREMENT, ' + '"name" TEXT NOT NULL, ' + '"folder" TEXT DEFAULT "" NOT NULL, ' + '"site" TEXT DEFAULT "" NOT NULL, ' + '"comment" TEXT DEFAULT "" NOT NULL, ' + '"password" TEXT DEFAULT "" NOT NULL, ' + '"added" INTEGER DEFAULT 0 NOT NULL,' # set by trigger + '"status" INTEGER DEFAULT 0 NOT NULL,' + '"packageorder" INTEGER DEFAULT -1 NOT NULL,' #incremented by trigger + '"root" INTEGER DEFAULT -1 NOT NULL, ' + '"owner" INTEGER NOT NULL, ' + 'FOREIGN KEY(owner) REFERENCES users(uid), ' + 'CHECK (root != pid)' + ')' + ) + + self.c.execute( + 'CREATE TRIGGER IF NOT EXISTS "insert_package" AFTER INSERT ON "packages"' + 'BEGIN ' + 'UPDATE packages SET added = strftime("%s", "now"), ' + 'packageorder = (SELECT max(p.packageorder) + 1 FROM packages p WHERE p.root=new.root) ' + 'WHERE rowid = new.rowid;' + 'END' + ) + + self.c.execute( + 'CREATE TRIGGER IF NOT EXISTS "delete_package" AFTER DELETE ON "packages"' + 'BEGIN ' + 'DELETE FROM files WHERE package = old.pid;' + 'UPDATE packages SET packageorder=packageorder-1 WHERE packageorder > old.packageorder AND root=old.pid;' + 'END' + ) + self.c.execute('CREATE INDEX IF NOT EXISTS "package_index" ON packages(root, owner)') + self.c.execute('CREATE INDEX IF NOT EXISTS "package_owner" ON packages(owner)') + + self.c.execute( + 'CREATE TABLE IF NOT EXISTS "files" (' + '"fid" INTEGER PRIMARY KEY AUTOINCREMENT, ' + '"name" TEXT NOT NULL, ' + '"size" INTEGER DEFAULT 0 NOT NULL, ' + '"status" INTEGER DEFAULT 0 NOT NULL, ' + '"media" INTEGER DEFAULT 1 NOT NULL,' + '"added" INTEGER DEFAULT 0 NOT NULL,' + '"fileorder" INTEGER DEFAULT -1 NOT NULL, ' + '"url" TEXT DEFAULT "" NOT NULL, ' + '"plugin" TEXT DEFAULT "" NOT NULL, ' + '"hash" TEXT DEFAULT "" NOT NULL, ' + '"dlstatus" INTEGER DEFAULT 0 NOT NULL, ' + '"error" TEXT DEFAULT "" NOT NULL, ' + '"package" INTEGER NOT NULL, ' + '"owner" INTEGER NOT NULL, ' + 'FOREIGN KEY(owner) REFERENCES users(uid), ' + 'FOREIGN KEY(package) REFERENCES packages(id)' + ')' + ) + self.c.execute('CREATE INDEX IF NOT EXISTS "file_index" ON files(package, owner)') + self.c.execute('CREATE INDEX IF NOT EXISTS "file_owner" ON files(owner)') + + self.c.execute( + 'CREATE TRIGGER IF NOT EXISTS "insert_file" AFTER INSERT ON "files"' + 'BEGIN ' + 'UPDATE files SET added = strftime("%s", "now"), ' + 'fileorder = (SELECT max(f.fileorder) + 1 FROM files f WHERE f.package=new.package) ' + 'WHERE rowid = new.rowid;' + 'END' + ) + + self.c.execute( + 'CREATE TABLE IF NOT EXISTS "collector" (' + '"owner" INTEGER NOT NULL, ' + '"data" TEXT NOT NULL, ' + 'FOREIGN KEY(owner) REFERENCES users(uid), ' + 'PRIMARY KEY(owner) ON CONFLICT REPLACE' + ') ' + ) + + self.c.execute( + 'CREATE TABLE IF NOT EXISTS "storage" (' + '"identifier" TEXT NOT NULL, ' + '"key" TEXT NOT NULL, ' + '"value" TEXT DEFAULT "", ' + 'PRIMARY KEY (identifier, key) ON CONFLICT REPLACE' + ')' + ) + + self.c.execute( + 'CREATE TABLE IF NOT EXISTS "users" (' + '"uid" INTEGER PRIMARY KEY AUTOINCREMENT, ' + '"name" TEXT NOT NULL UNIQUE, ' + '"email" TEXT DEFAULT "" NOT NULL, ' + '"password" TEXT NOT NULL, ' + '"role" INTEGER DEFAULT 0 NOT NULL, ' + '"permission" INTEGER DEFAULT 0 NOT NULL, ' + '"folder" TEXT DEFAULT "" NOT NULL, ' + '"traffic" INTEGER DEFAULT -1 NOT NULL, ' + '"dllimit" INTEGER DEFAULT -1 NOT NULL, ' + '"dlquota" TEXT DEFAULT "" NOT NULL, ' + '"hddquota" INTEGER DEFAULT -1 NOT NULL, ' + '"template" TEXT DEFAULT "default" NOT NULL, ' + '"user" INTEGER DEFAULT -1 NOT NULL, ' # set by trigger to self + 'FOREIGN KEY(user) REFERENCES users(uid)' + ')' + ) + self.c.execute('CREATE INDEX IF NOT EXISTS "username_index" ON users(name)') + + self.c.execute( + 'CREATE TRIGGER IF NOT EXISTS "insert_user" AFTER INSERT ON "users"' + 'BEGIN ' + 'UPDATE users SET user = new.uid, folder=new.name ' + 'WHERE rowid = new.rowid;' + 'END' + ) + + self.c.execute( + 'CREATE TABLE IF NOT EXISTS "settings" (' + '"plugin" TEXT NOT NULL, ' + '"user" INTEGER DEFAULT -1 NOT NULL, ' + '"config" TEXT NOT NULL, ' + 'FOREIGN KEY(user) REFERENCES users(uid), ' + 'PRIMARY KEY (plugin, user) ON CONFLICT REPLACE' + ')' + ) + + self.c.execute( + 'CREATE TABLE IF NOT EXISTS "accounts" (' + '"plugin" TEXT NOT NULL, ' + '"loginname" TEXT NOT NULL, ' + '"owner", INTEGER NOT NULL, ' + '"activated" INTEGER DEFAULT 1, ' + '"password" TEXT DEFAULT "", ' + '"shared" INTEGER DEFAULT 0, ' + '"options" TEXT DEFAULT "", ' + 'FOREIGN KEY(owner) REFERENCES users(uid), ' + 'PRIMARY KEY (plugin, loginname, owner) ON CONFLICT REPLACE' + ')' + ) + + self.c.execute( + 'CREATE TABLE IF NOT EXISTS "stats" (' + '"user" INTEGER NOT NULL, ' + '"plugin" TEXT NOT NULL, ' + '"time" INTEGER NOT NULL, ' + '"premium" INTEGER DEFAULT 0 NOT NULL, ' + '"amount" INTEGER DEFAULT 0 NOT NULL, ' + 'FOREIGN KEY(user) REFERENCES users(uid), ' + 'PRIMARY KEY(user, plugin, time)' + ')' + ) + self.c.execute('CREATE INDEX IF NOT EXISTS "stats_time" ON stats(time)') #try to lower ids - self.c.execute('SELECT max(id) FROM LINKS') + self.c.execute('SELECT max(fid) FROM files') fid = self.c.fetchone()[0] - if fid: - fid = int(fid) - else: - fid = 0 - self.c.execute('UPDATE SQLITE_SEQUENCE SET seq=? WHERE name=?', (fid, "links")) + fid = int(fid) if fid else 0 + self.c.execute('UPDATE SQLITE_SEQUENCE SET seq=? WHERE name=?', (fid, "files")) - - self.c.execute('SELECT max(id) FROM packages') + self.c.execute('SELECT max(pid) FROM packages') pid = self.c.fetchone()[0] - if pid: - pid = int(pid) - else: - pid = 0 + pid = int(pid) if pid else 0 self.c.execute('UPDATE SQLITE_SEQUENCE SET seq=? WHERE name=?', (pid, "packages")) self.c.execute('VACUUM') - def _migrateUser(self): - if exists("pyload.db"): - try: - self.core.log.info(_("Converting old Django DB")) - except: - print "Converting old Django DB" - conn = sqlite3.connect('pyload.db') - c = conn.cursor() - c.execute("SELECT username, password, email from auth_user WHERE is_superuser") - users = [] - for r in c: - pw = r[1].split("$") - users.append((r[0], pw[1] + pw[2], r[2])) - c.close() - conn.close() - - self.c.executemany("INSERT INTO users(name, password, email) VALUES (?, ?, ?)", users) - move("pyload.db", "pyload.old.db") - def createCursor(self): return self.conn.cursor() - - @style.async + + @async def commit(self): self.conn.commit() - @style.queue + @queue def syncSave(self): self.conn.commit() - - @style.async + + @async def rollback(self): self.conn.rollback() - + def async(self, f, *args, **kwargs): args = (self, ) + args job = DatabaseJob(f, *args, **kwargs) self.jobs.put(job) - + def queue(self, f, *args, **kwargs): args = (self, ) + args job = DatabaseJob(f, *args, **kwargs) self.jobs.put(job) - job.wait() + # only wait when db is running + if self.running.isSet(): job.wait() return job.result - + @classmethod def registerSub(cls, klass): cls.subs.append(klass) - + @classmethod def unregisterSub(cls, klass): cls.subs.remove(klass) - + def __getattr__(self, attr): for sub in DatabaseBackend.subs: if hasattr(sub, attr): return getattr(sub, attr) + raise AttributeError(attr) if __name__ == "__main__": db = DatabaseBackend() db.setup() - + class Test(): - @style.queue + @queue def insert(db): c = db.createCursor() for i in range(1000): c.execute("INSERT INTO storage (identifier, key, value) VALUES (?, ?, ?)", ("foo", i, "bar")) - @style.async + + @async def insert2(db): c = db.createCursor() - for i in range(1000*1000): + for i in range(1000 * 1000): c.execute("INSERT INTO storage (identifier, key, value) VALUES (?, ?, ?)", ("foo", i, "bar")) - - @style.queue + + @queue def select(db): c = db.createCursor() for i in range(10): res = c.execute("SELECT value FROM storage WHERE identifier=? AND key=?", ("foo", i)) print res.fetchone() - - @style.queue + + @queue def error(db): c = db.createCursor() print "a" c.execute("SELECT myerror FROM storage WHERE identifier=? AND key=?", ("foo", i)) print "e" - + db.registerSub(Test) from time import time + start = time() for i in range(100): db.insert() end = time() - print end-start - + print end - start + start = time() db.insert2() end = time() - print end-start - + print end - start + db.error() diff --git a/module/database/FileDatabase.py b/module/database/FileDatabase.py index 7e7efb028..e065b84e2 100644 --- a/module/database/FileDatabase.py +++ b/module/database/FileDatabase.py @@ -1,944 +1,391 @@ #!/usr/bin/env python -""" - This program is free software; you can redistribute it and/or modify - it under the terms of the GNU General Public License as published by - the Free Software Foundation; either version 3 of the License, - or (at your option) any later version. - - This program is distributed in the hope that it will be useful, - but WITHOUT ANY WARRANTY; without even the implied warranty of - MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. - See the GNU General Public License for more details. - - You should have received a copy of the GNU General Public License - along with this program; if not, see <http://www.gnu.org/licenses/>. - - @author: RaNaN - @author: mkaay -""" - - -from threading import RLock -from time import time - -from module.utils import formatSize, lock -from module.PullEvents import InsertEvent, ReloadAllEvent, RemoveEvent, UpdateEvent -from module.PyPackage import PyPackage -from module.PyFile import PyFile -from module.database import style, DatabaseBackend - -try: - from pysqlite2 import dbapi2 as sqlite3 -except: - import sqlite3 - - -class FileHandler: - """Handles all request made to obtain information, - modify status or other request for links or packages""" - - def __init__(self, core): - """Constructor""" - self.core = core - - # translations - self.statusMsg = [_("finished"), _("offline"), _("online"), _("queued"), _("skipped"), _("waiting"), _("temp. offline"), _("starting"), _("failed"), _("aborted"), _("decrypting"), _("custom"), _("downloading"), _("processing"), _("unknown")] - - self.cache = {} #holds instances for files - self.packageCache = {} # same for packages - #@TODO: purge the cache - - self.jobCache = {} - - self.lock = RLock() #@TODO should be a Lock w/o R - #self.lock._Verbose__verbose = True - - self.filecount = -1 # if an invalid value is set get current value from db - self.queuecount = -1 #number of package to be loaded - self.unchanged = False #determines if any changes was made since last call - - self.db = self.core.db - - def change(func): - def new(*args): - args[0].unchanged = False - args[0].filecount = -1 - args[0].queuecount = -1 - args[0].jobCache = {} - return func(*args) - return new - - #---------------------------------------------------------------------- - def save(self): - """saves all data to backend""" - self.db.commit() - - #---------------------------------------------------------------------- - def syncSave(self): - """saves all data to backend and waits until all data are written""" - pyfiles = self.cache.values() - for pyfile in pyfiles: - pyfile.sync() - - pypacks = self.packageCache.values() - for pypack in pypacks: - pypack.sync() - - self.db.syncSave() - - @lock - def getCompleteData(self, queue=1): - """gets a complete data representation""" - - data = self.db.getAllLinks(queue) - packs = self.db.getAllPackages(queue) - - data.update([(x.id, x.toDbDict()[x.id]) for x in self.cache.values()]) - - for x in self.packageCache.itervalues(): - if x.queue != queue or x.id not in packs: continue - packs[x.id].update(x.toDict()[x.id]) - - for key, value in data.iteritems(): - if value["package"] in packs: - packs[value["package"]]["links"][key] = value - - return packs - - @lock - def getInfoData(self, queue=1): - """gets a data representation without links""" - - packs = self.db.getAllPackages(queue) - for x in self.packageCache.itervalues(): - if x.queue != queue or x.id not in packs: continue - packs[x.id].update(x.toDict()[x.id]) - - return packs - - @lock - @change - def addLinks(self, urls, package): - """adds links""" - - self.core.hookManager.dispatchEvent("linksAdded", urls, package) - - data = self.core.pluginManager.parseUrls(urls) - - self.db.addLinks(data, package) - self.core.threadManager.createInfoThread(data, package) - - #@TODO change from reloadAll event to package update event - self.core.pullManager.addEvent(ReloadAllEvent("collector")) - - #---------------------------------------------------------------------- - @lock - @change - def addPackage(self, name, folder, queue=0): - """adds a package, default to link collector""" - lastID = self.db.addPackage(name, folder, queue) - p = self.db.getPackage(lastID) - e = InsertEvent("pack", lastID, p.order, "collector" if not queue else "queue") - self.core.pullManager.addEvent(e) - return lastID - - #---------------------------------------------------------------------- - @lock - @change - def deletePackage(self, id): - """delete package and all contained links""" +# -*- coding: utf-8 -*- + +############################################################################### +# Copyright(c) 2008-2012 pyLoad Team +# http://www.pyload.org +# +# This program is free software: you can redistribute it and/or modify +# it under the terms of the GNU Affero General Public License as +# published by the Free Software Foundation, either version 3 of the +# License, or (at your option) any later version. +# +# Subjected to the terms and conditions in LICENSE +# +# @author: RaNaN +############################################################################### + +from new_collections import OrderedDict + +from module.Api import DownloadInfo, FileInfo, PackageInfo, PackageStats +from module.database import DatabaseMethods, queue, async, inner + +zero_stats = PackageStats(0, 0, 0, 0) + +class FileMethods(DatabaseMethods): + @queue + def filecount(self, user=None): + """returns number of files""" + self.c.execute("SELECT COUNT(*) FROM files") + return self.c.fetchone()[0] - p = self.getPackage(id) - if not p: - if id in self.packageCache: del self.packageCache[id] - return + @queue + def queuecount(self, user=None): + """ number of files in queue not finished yet""" + # status not in NA, finished, skipped + self.c.execute("SELECT COUNT(*) FROM files WHERE dlstatus NOT IN (0,5,6)") + return self.c.fetchone()[0] - oldorder = p.order - queue = p.queue + @queue + def processcount(self, fid, user=None): + """ number of files which have to be processed """ + # status in online, queued, starting, waiting, downloading + self.c.execute("SELECT COUNT(*) FROM files WHERE dlstatus IN (2,3,8,9,10) AND fid != ?", (fid, )) + return self.c.fetchone()[0] - e = RemoveEvent("pack", id, "collector" if not p.queue else "queue") - - pyfiles = self.cache.values() - - for pyfile in pyfiles: - if pyfile.packageid == id: - pyfile.abortDownload() - pyfile.release() + # TODO: think about multiuser side effects on *count methods - self.db.deletePackage(p) - self.core.pullManager.addEvent(e) - self.core.hookManager.dispatchEvent("packageDeleted", id) + @queue + def addLink(self, url, name, plugin, package, owner): + # mark file status initially as missing, dlstatus - queued + self.c.execute('INSERT INTO files(url, name, plugin, status, dlstatus, package, owner) VALUES(?,?,?,1,3,?,?)', + (url, name, plugin, package, owner)) + return self.c.lastrowid - if id in self.packageCache: - del self.packageCache[id] + @async + def addLinks(self, links, package, owner): + """ links is a list of tuples (url, plugin)""" + links = [(x[0], x[0], x[1], package, owner) for x in links] + self.c.executemany('INSERT INTO files(url, name, plugin, status, dlstatus, package, owner) VALUES(?,?,?,1,3,?,?)', + links) + + @queue + def addFile(self, name, size, media, package, owner): + # file status - ok, dl status NA + self.c.execute('INSERT INTO files(name, size, media, package, owner) VALUES(?,?,?,?,?)', + (name, size, media, package, owner)) + return self.c.lastrowid - packs = self.packageCache.values() - for pack in packs: - if pack.queue == queue and pack.order > oldorder: - pack.order -= 1 - pack.notifyChange() + @queue + def addPackage(self, name, folder, root, password, site, comment, status, owner): + self.c.execute( + 'INSERT INTO packages(name, folder, root, password, site, comment, status, owner) VALUES(?,?,?,?,?,?,?,?)' + , (name, folder, root, password, site, comment, status, owner)) + return self.c.lastrowid - #---------------------------------------------------------------------- - @lock - @change - def deleteLink(self, id): - """deletes links""" + @async + def deletePackage(self, pid, owner=None): + # order updated by trigger, as well as links deleted + if owner is None: + self.c.execute('DELETE FROM packages WHERE pid=?', (pid,)) + else: + self.c.execute('DELETE FROM packages WHERE pid=? AND owner=?', (pid, owner)) + + @async + def deleteFile(self, fid, order, package, owner=None): + """ To delete a file order and package of it is needed """ + if owner is None: + self.c.execute('DELETE FROM files WHERE fid=?', (fid,)) + self.c.execute('UPDATE files SET fileorder=fileorder-1 WHERE fileorder > ? AND package=?', + (order, package)) + else: + self.c.execute('DELETE FROM files WHERE fid=? AND owner=?', (fid, owner)) + self.c.execute('UPDATE files SET fileorder=fileorder-1 WHERE fileorder > ? AND package=? AND owner=?', + (order, package, owner)) + + @async + def saveCollector(self, owner, data): + """ simply save the json string to database """ + self.c.execute("INSERT INTO collector(owner, data) VALUES (?,?)", (owner, data)) + + @queue + def retrieveCollector(self, owner): + """ retrieve the saved string """ + self.c.execute('SELECT data FROM collector WHERE owner=?', (owner,)) + r = self.c.fetchone() + if not r: return None + return r[0] - f = self.getFile(id) - if not f: - return None + @async + def deleteCollector(self, owner): + """ drop saved user collector """ + self.c.execute('DELETE FROM collector WHERE owner=?', (owner,)) - pid = f.packageid - e = RemoveEvent("file", id, "collector" if not f.package().queue else "queue") - - oldorder = f.order - - if id in self.core.threadManager.processingIds(): - self.cache[id].abortDownload() - - if id in self.cache: - del self.cache[id] - - self.db.deleteLink(f) - - self.core.pullManager.addEvent(e) - - p = self.getPackage(pid) - if not len(p.getChildren()): - p.delete() - - pyfiles = self.cache.values() - for pyfile in pyfiles: - if pyfile.packageid == pid and pyfile.order > oldorder: - pyfile.order -= 1 - pyfile.notifyChange() - - #---------------------------------------------------------------------- - def releaseLink(self, id): - """removes pyfile from cache""" - if id in self.cache: - del self.cache[id] - - #---------------------------------------------------------------------- - def releasePackage(self, id): - """removes package from cache""" - if id in self.packageCache: - del self.packageCache[id] - - #---------------------------------------------------------------------- - def updateLink(self, pyfile): - """updates link""" - self.db.updateLink(pyfile) - - e = UpdateEvent("file", pyfile.id, "collector" if not pyfile.package().queue else "queue") - self.core.pullManager.addEvent(e) - - #---------------------------------------------------------------------- - def updatePackage(self, pypack): - """updates a package""" - self.db.updatePackage(pypack) - - e = UpdateEvent("pack", pypack.id, "collector" if not pypack.queue else "queue") - self.core.pullManager.addEvent(e) - - #---------------------------------------------------------------------- - def getPackage(self, id): - """return package instance""" - - if id in self.packageCache: - return self.packageCache[id] - else: - return self.db.getPackage(id) + @queue + def getAllFiles(self, package=None, search=None, unfinished=False, owner=None): + """ Return dict with file information - #---------------------------------------------------------------------- - def getPackageData(self, id): - """returns dict with package information""" - pack = self.getPackage(id) + :param package: optional package to filter out + :param search: or search string for file name + :param unfinished: filter by dlstatus not finished + :param owner: only specific owner + """ + qry = ('SELECT fid, name, owner, size, status, media, added, fileorder, ' + 'url, plugin, hash, dlstatus, error, package FROM files WHERE ') - if not pack: - return None + arg = [] - pack = pack.toDict()[id] + if unfinished: + qry += 'dlstatus NOT IN (0, 5, 6) AND ' + if owner is not None: + qry += 'owner=? AND ' + arg.append(owner) - data = self.db.getPackageData(id) + if package is not None: + arg.append(package) + qry += 'package=? AND ' + if search is not None: + search = "%%%s%%" % search.strip("%") + arg.append(search) + qry += "name LIKE ? " - tmplist = [] + # make qry valid + if qry.endswith("WHERE "): qry = qry[:-6] + if qry.endswith("AND "): qry = qry[:-4] - cache = self.cache.values() - for x in cache: - if int(x.toDbDict()[x.id]["package"]) == int(id): - tmplist.append((x.id, x.toDbDict()[x.id])) - data.update(tmplist) + self.c.execute(qry + "ORDER BY package, fileorder", arg) - pack["links"] = data + data = OrderedDict() + for r in self.c: + f = FileInfo(r[0], r[1], r[13], r[2], r[3], r[4], r[5], r[6], r[7]) + if r[11] > 0: # dl status != NA + f.download = DownloadInfo(r[8], r[9], r[10], r[11], self.manager.statusMsg[r[11]], r[12]) - return pack + data[r[0]] = f - #---------------------------------------------------------------------- - def getFileData(self, id): - """returns dict with file information""" - if id in self.cache: - return self.cache[id].toDbDict() + return data - return self.db.getLinkData(id) + @queue + def getAllPackages(self, root=None, owner=None): + """ Return dict with package information - #---------------------------------------------------------------------- - def getFile(self, id): - """returns pyfile instance""" - if id in self.cache: - return self.cache[id] - else: - return self.db.getFile(id) + :param root: optional root to filter + """ + qry = ('SELECT pid, name, folder, root, owner, site, comment, password, added, status, packageorder ' + 'FROM packages%s ORDER BY root, packageorder') - #---------------------------------------------------------------------- - @lock - def getJob(self, occ): - """get suitable job""" - - #@TODO clean mess - #@TODO improve selection of valid jobs - - if occ in self.jobCache: - if self.jobCache[occ]: - id = self.jobCache[occ].pop() - if id == "empty": - pyfile = None - self.jobCache[occ].append("empty") - else: - pyfile = self.getFile(id) + if root is None: + stats = self.getPackageStats(owner=owner) + if owner is None: + self.c.execute(qry % "") else: - jobs = self.db.getJob(occ) - jobs.reverse() - if not jobs: - self.jobCache[occ].append("empty") - pyfile = None - else: - self.jobCache[occ].extend(jobs) - pyfile = self.getFile(self.jobCache[occ].pop()) - + self.c.execute(qry % " WHERE owner=?", (owner,)) else: - self.jobCache = {} #better not caching to much - jobs = self.db.getJob(occ) - jobs.reverse() - self.jobCache[occ] = jobs - - if not jobs: - self.jobCache[occ].append("empty") - pyfile = None + stats = self.getPackageStats(root=root, owner=owner) + if owner is None: + self.c.execute(qry % ' WHERE root=? OR pid=?', (root, root)) else: - pyfile = self.getFile(self.jobCache[occ].pop()) - - #@TODO: maybe the new job has to be approved... - - - #pyfile = self.getFile(self.jobCache[occ].pop()) - return pyfile - - @lock - def getDecryptJob(self): - """return job for decrypting""" - if "decrypt" in self.jobCache: - return None - - plugins = self.core.pluginManager.crypterPlugins.keys() + self.core.pluginManager.containerPlugins.keys() - plugins = str(tuple(plugins)) - - jobs = self.db.getPluginJob(plugins) - if jobs: - return self.getFile(jobs[0]) - else: - self.jobCache["decrypt"] = "empty" - return None - - def getFileCount(self): - """returns number of files""" + self.c.execute(qry % ' WHERE (root=? OR pid=?) AND owner=?', (root, root, owner)) - if self.filecount == -1: - self.filecount = self.db.filecount(1) - - return self.filecount - - def getQueueCount(self, force=False): - """number of files that have to be processed""" - if self.queuecount == -1 or force: - self.queuecount = self.db.queuecount(1) - - return self.queuecount - - def checkAllLinksFinished(self): - """checks if all files are finished and dispatch event""" - - if not self.getQueueCount(True): - self.core.hookManager.dispatchEvent("allDownloadsFinished") - self.core.log.debug("All downloads finished") - return True - - return False - - def checkAllLinksProcessed(self, fid): - """checks if all files was processed and pyload would idle now, needs fid which will be ignored when counting""" - - # reset count so statistic will update (this is called when dl was processed) - self.resetCount() - - if not self.db.processcount(1, fid): - self.core.hookManager.dispatchEvent("allDownloadsProcessed") - self.core.log.debug("All downloads processed") - return True - - return False - - def resetCount(self): - self.queuecount = -1 - - @lock - @change - def restartPackage(self, id): - """restart package""" - pyfiles = self.cache.values() - for pyfile in pyfiles: - if pyfile.packageid == id: - self.restartFile(pyfile.id) - - self.db.restartPackage(id) - - if id in self.packageCache: - self.packageCache[id].setFinished = False - - e = UpdateEvent("pack", id, "collector" if not self.getPackage(id).queue else "queue") - self.core.pullManager.addEvent(e) - - @lock - @change - def restartFile(self, id): - """ restart file""" - if id in self.cache: - self.cache[id].status = 3 - self.cache[id].name = self.cache[id].url - self.cache[id].error = "" - self.cache[id].abortDownload() - - - self.db.restartFile(id) - - e = UpdateEvent("file", id, "collector" if not self.getFile(id).package().queue else "queue") - self.core.pullManager.addEvent(e) - - @lock - @change - def setPackageLocation(self, id, queue): - """push package to queue""" - - p = self.db.getPackage(id) - oldorder = p.order - - e = RemoveEvent("pack", id, "collector" if not p.queue else "queue") - self.core.pullManager.addEvent(e) - - self.db.clearPackageOrder(p) - - p = self.db.getPackage(id) - - p.queue = queue - self.db.updatePackage(p) - - self.db.reorderPackage(p, -1, True) - - packs = self.packageCache.values() - for pack in packs: - if pack.queue != queue and pack.order > oldorder: - pack.order -= 1 - pack.notifyChange() - - self.db.commit() - self.releasePackage(id) - p = self.getPackage(id) - - e = InsertEvent("pack", id, p.order, "collector" if not p.queue else "queue") - self.core.pullManager.addEvent(e) - - @lock - @change - def reorderPackage(self, id, position): - p = self.getPackage(id) - - e = RemoveEvent("pack", id, "collector" if not p.queue else "queue") - self.core.pullManager.addEvent(e) - self.db.reorderPackage(p, position) - - packs = self.packageCache.values() - for pack in packs: - if pack.queue != p.queue or pack.order < 0 or pack == p: continue - if p.order > position: - if pack.order >= position and pack.order < p.order: - pack.order += 1 - pack.notifyChange() - elif p.order < position: - if pack.order <= position and pack.order > p.order: - pack.order -= 1 - pack.notifyChange() - - p.order = position - self.db.commit() - - e = InsertEvent("pack", id, position, "collector" if not p.queue else "queue") - self.core.pullManager.addEvent(e) - - @lock - @change - def reorderFile(self, id, position): - f = self.getFileData(id) - f = f[id] - - e = RemoveEvent("file", id, "collector" if not self.getPackage(f["package"]).queue else "queue") - self.core.pullManager.addEvent(e) - - self.db.reorderLink(f, position) - - pyfiles = self.cache.values() - for pyfile in pyfiles: - if pyfile.packageid != f["package"] or pyfile.order < 0: continue - if f["order"] > position: - if pyfile.order >= position and pyfile.order < f["order"]: - pyfile.order += 1 - pyfile.notifyChange() - elif f["order"] < position: - if pyfile.order <= position and pyfile.order > f["order"]: - pyfile.order -= 1 - pyfile.notifyChange() - - if id in self.cache: - self.cache[id].order = position - - self.db.commit() - - e = InsertEvent("file", id, position, "collector" if not self.getPackage(f["package"]).queue else "queue") - self.core.pullManager.addEvent(e) - - @change - def updateFileInfo(self, data, pid): - """ updates file info (name, size, status, url)""" - ids = self.db.updateLinkInfo(data) - e = UpdateEvent("pack", pid, "collector" if not self.getPackage(pid).queue else "queue") - self.core.pullManager.addEvent(e) - - def checkPackageFinished(self, pyfile): - """ checks if package is finished and calls hookmanager """ - - ids = self.db.getUnfinished(pyfile.packageid) - if not ids or (pyfile.id in ids and len(ids) == 1): - if not pyfile.package().setFinished: - self.core.log.info(_("Package finished: %s") % pyfile.package().name) - self.core.hookManager.packageFinished(pyfile.package()) - pyfile.package().setFinished = True - - - def reCheckPackage(self, pid): - """ recheck links in package """ - data = self.db.getPackageData(pid) - - urls = [] - - for pyfile in data.itervalues(): - if pyfile["status"] not in (0, 12, 13): - urls.append((pyfile["url"], pyfile["plugin"])) - - self.core.threadManager.createInfoThread(urls, pid) - - @lock - @change - def deleteFinishedLinks(self): - """ deletes finished links and packages, return deleted packages """ - - old_packs = self.getInfoData(0) - old_packs.update(self.getInfoData(1)) - - self.db.deleteFinished() - - new_packs = self.db.getAllPackages(0) - new_packs.update(self.db.getAllPackages(1)) - #get new packages only from db - - deleted = [] - for id in old_packs.iterkeys(): - if id not in new_packs: - deleted.append(id) - self.deletePackage(int(id)) - - return deleted - - @lock - @change - def restartFailed(self): - """ restart all failed links """ - self.db.restartFailed() - -class FileMethods(): - @style.queue - def filecount(self, queue): - """returns number of files in queue""" - self.c.execute("SELECT COUNT(*) FROM links as l INNER JOIN packages as p ON l.package=p.id WHERE p.queue=?", (queue, )) - return self.c.fetchone()[0] - - @style.queue - def queuecount(self, queue): - """ number of files in queue not finished yet""" - self.c.execute("SELECT COUNT(*) FROM links as l INNER JOIN packages as p ON l.package=p.id WHERE p.queue=? AND l.status NOT IN (0,4)", (queue, )) - return self.c.fetchone()[0] + data = OrderedDict() + for r in self.c: + data[r[0]] = PackageInfo( + r[0], r[1], r[2], r[3], r[4], r[5], r[6], r[7], r[8], r[9], r[10], stats.get(r[0], zero_stats) + ) - @style.queue - def processcount(self, queue, fid): - """ number of files which have to be proccessed """ - self.c.execute("SELECT COUNT(*) FROM links as l INNER JOIN packages as p ON l.package=p.id WHERE p.queue=? AND l.status IN (2,3,5,7,12) AND l.id != ?", (queue, str(fid))) - return self.c.fetchone()[0] + return data - @style.inner - def _nextPackageOrder(self, queue=0): - self.c.execute('SELECT MAX(packageorder) FROM packages WHERE queue=?', (queue,)) - max = self.c.fetchone()[0] - if max is not None: - return max + 1 - else: - return 0 - - @style.inner - def _nextFileOrder(self, package): - self.c.execute('SELECT MAX(linkorder) FROM links WHERE package=?', (package,)) - max = self.c.fetchone()[0] - if max is not None: - return max + 1 + @inner + def getPackageStats(self, pid=None, root=None, owner=None): + qry = ("SELECT p.pid, SUM(f.size) AS sizetotal, COUNT(f.fid) AS linkstotal, sizedone, linksdone " + "FROM packages p JOIN files f ON p.pid = f.package AND f.dlstatus > 0 %(sub)s LEFT OUTER JOIN " + "(SELECT p.pid AS pid, SUM(f.size) AS sizedone, COUNT(f.fid) AS linksdone " + "FROM packages p JOIN files f ON p.pid = f.package %(sub)s AND f.dlstatus in (5,6) GROUP BY p.pid) s ON s.pid = p.pid " + "GROUP BY p.pid") + + # status in (finished, skipped, processing) + + if root is not None: + self.c.execute(qry % {"sub": "AND (p.root=:root OR p.pid=:root)"}, locals()) + elif pid is not None: + self.c.execute(qry % {"sub": "AND p.pid=:pid"}, locals()) + elif owner is not None: + self.c.execute(qry % {"sub": "AND p.owner=:owner"}, locals()) else: - return 0 - - @style.queue - def addLink(self, url, name, plugin, package): - order = self._nextFileOrder(package) - self.c.execute('INSERT INTO links(url, name, plugin, package, linkorder) VALUES(?,?,?,?,?)', (url, name, plugin, package, order)) - return self.c.lastrowid - - @style.queue - def addLinks(self, links, package): - """ links is a list of tupels (url,plugin)""" - order = self._nextFileOrder(package) - orders = [order + x for x in range(len(links))] - links = [(x[0], x[0], x[1], package, o) for x, o in zip(links, orders)] - self.c.executemany('INSERT INTO links(url, name, plugin, package, linkorder) VALUES(?,?,?,?,?)', links) - - @style.queue - def addPackage(self, name, folder, queue): - order = self._nextPackageOrder(queue) - self.c.execute('INSERT INTO packages(name, folder, queue, packageorder) VALUES(?,?,?,?)', (name, folder, queue, order)) - return self.c.lastrowid - - @style.queue - def deletePackage(self, p): - - self.c.execute('DELETE FROM links WHERE package=?', (str(p.id),)) - self.c.execute('DELETE FROM packages WHERE id=?', (str(p.id),)) - self.c.execute('UPDATE packages SET packageorder=packageorder-1 WHERE packageorder > ? AND queue=?', (p.order, p.queue)) - - @style.queue - def deleteLink(self, f): + self.c.execute(qry % {"sub": ""}) - self.c.execute('DELETE FROM links WHERE id=?', (str(f.id),)) - self.c.execute('UPDATE links SET linkorder=linkorder-1 WHERE linkorder > ? AND package=?', (f.order, str(f.packageid))) - - - @style.queue - def getAllLinks(self, q): - """return information about all links in queue q - - q0 queue - q1 collector - - format: - - { - id: {'name': name, ... 'package': id }, ... - } - - """ - self.c.execute('SELECT l.id,l.url,l.name,l.size,l.status,l.error,l.plugin,l.package,l.linkorder FROM links as l INNER JOIN packages as p ON l.package=p.id WHERE p.queue=? ORDER BY l.linkorder', (q,)) data = {} for r in self.c: - data[r[0]] = { - 'id': r[0], - 'url': r[1], - 'name': r[2], - 'size': r[3], - 'format_size': formatSize(r[3]), - 'status': r[4], - 'statusmsg': self.manager.statusMsg[r[4]], - 'error': r[5], - 'plugin': r[6], - 'package': r[7], - 'order': r[8], - } + data[r[0]] = PackageStats( + r[2] if r[2] else 0, + r[4] if r[4] else 0, + int(r[1]) if r[1] else 0, + int(r[3]) if r[3] else 0, + ) return data - @style.queue - def getAllPackages(self, q): - """return information about packages in queue q - (only useful in get all data) - - q0 queue - q1 collector + @queue + def getStatsForPackage(self, pid): + return self.getPackageStats(pid=pid)[pid] - format: - - { - id: {'name': name ... 'links': {} }, ... - } - """ - self.c.execute('SELECT p.id, p.name, p.folder, p.site, p.password, p.queue, p.packageorder, s.sizetotal, s.sizedone, s.linksdone, s.linkstotal \ - FROM packages p JOIN pstats s ON p.id = s.id \ - WHERE p.queue=? ORDER BY p.packageorder', str(q)) - - data = {} - for r in self.c: - data[r[0]] = { - 'id': r[0], - 'name': r[1], - 'folder': r[2], - 'site': r[3], - 'password': r[4], - 'queue': r[5], - 'order': r[6], - 'sizetotal': int(r[7]), - 'sizedone': r[8] if r[8] else 0, #these can be None - 'linksdone': r[9] if r[9] else 0, - 'linkstotal': r[10], - 'links': {} - } - - return data - - @style.queue - def getLinkData(self, id): - """get link information as dict""" - self.c.execute('SELECT id,url,name,size,status,error,plugin,package,linkorder FROM links WHERE id=?', (str(id), )) - data = {} + @queue + def getFileInfo(self, fid, force=False): + """get data for specific file, when force is true download info will be appended""" + self.c.execute('SELECT fid, name, owner, size, status, media, added, fileorder, ' + 'url, plugin, hash, dlstatus, error, package FROM files ' + 'WHERE fid=?', (fid,)) r = self.c.fetchone() if not r: return None - data[r[0]] = { - 'id': r[0], - 'url': r[1], - 'name': r[2], - 'size': r[3], - 'format_size': formatSize(r[3]), - 'status': r[4], - 'statusmsg': self.manager.statusMsg[r[4]], - 'error': r[5], - 'plugin': r[6], - 'package': r[7], - 'order': r[8], - } - - return data + else: + f = FileInfo(r[0], r[1], r[13], r[2], r[3], r[4], r[5], r[6], r[7]) + if r[11] > 0 or force: + f.download = DownloadInfo(r[8], r[9], r[10], r[11], self.manager.statusMsg[r[11]], r[12]) - @style.queue - def getPackageData(self, id): - """get data about links for a package""" - self.c.execute('SELECT id,url,name,size,status,error,plugin,package,linkorder FROM links WHERE package=? ORDER BY linkorder', (str(id), )) + return f - data = {} - for r in self.c: - data[r[0]] = { - 'id': r[0], - 'url': r[1], - 'name': r[2], - 'size': r[3], - 'format_size': formatSize(r[3]), - 'status': r[4], - 'statusmsg': self.manager.statusMsg[r[4]], - 'error': r[5], - 'plugin': r[6], - 'package': r[7], - 'order': r[8], - } + @queue + def getPackageInfo(self, pid, stats=True): + """get data for a specific package, optionally with package stats""" + if stats: + stats = self.getPackageStats(pid=pid) - return data + self.c.execute('SELECT pid, name, folder, root, owner, site, comment, password, added, status, packageorder ' + 'FROM packages WHERE pid=?', (pid,)) + r = self.c.fetchone() + if not r: + return None + else: + return PackageInfo( + r[0], r[1], r[2], r[3], r[4], r[5], r[6], r[7], r[8], r[9], r[10], stats.get(r[0], zero_stats) if stats else None + ) + + @async + def updateLinkInfo(self, data, owner): + """ data is list of tuples (name, size, status,[ hash,] url)""" + if data and len(data[0]) == 4: + self.c.executemany('UPDATE files SET name=?, size=?, dlstatus=? WHERE url=? AND dlstatus IN (0,1,2,3,14)', + data) + else: + self.c.executemany( + 'UPDATE files SET name=?, size=?, dlstatus=?, hash=? WHERE url=? AND dlstatus IN (0,1,2,3,14)', data) - @style.async - def updateLink(self, f): - self.c.execute('UPDATE links SET url=?,name=?,size=?,status=?,error=?,package=? WHERE id=?', (f.url, f.name, f.size, f.status, f.error, str(f.packageid), str(f.id))) + @async + def updateFile(self, f): + self.c.execute('UPDATE files SET name=?, size=?, status=?,' + 'media=?, url=?, hash=?, dlstatus=?, error=? WHERE fid=?', + (f.name, f.size, f.filestatus, f.media, f.url, + f.hash, f.status, f.error, f.fid)) - @style.queue + @async def updatePackage(self, p): - self.c.execute('UPDATE packages SET name=?,folder=?,site=?,password=?,queue=? WHERE id=?', (p.name, p.folder, p.site, p.password, p.queue, str(p.id))) - - @style.queue - def updateLinkInfo(self, data): - """ data is list of tupels (name, size, status, url) """ - self.c.executemany('UPDATE links SET name=?, size=?, status=? WHERE url=? AND status IN (1,2,3,14)', data) - ids = [] - self.c.execute('SELECT id FROM links WHERE url IN (\'%s\')' % "','".join([x[3] for x in data])) - for r in self.c: - ids.append(int(r[0])) - return ids - - @style.queue - def reorderPackage(self, p, position, noMove=False): - if position == -1: - position = self._nextPackageOrder(p.queue) - if not noMove: - if p.order > position: - self.c.execute('UPDATE packages SET packageorder=packageorder+1 WHERE packageorder >= ? AND packageorder < ? AND queue=? AND packageorder >= 0', (position, p.order, p.queue)) - elif p.order < position: - self.c.execute('UPDATE packages SET packageorder=packageorder-1 WHERE packageorder <= ? AND packageorder > ? AND queue=? AND packageorder >= 0', (position, p.order, p.queue)) - - self.c.execute('UPDATE packages SET packageorder=? WHERE id=?', (position, str(p.id))) - - @style.queue - def reorderLink(self, f, position): - """ reorder link with f as dict for pyfile """ - if f["order"] > position: - self.c.execute('UPDATE links SET linkorder=linkorder+1 WHERE linkorder >= ? AND linkorder < ? AND package=?', (position, f["order"], f["package"])) - elif f["order"] < position: - self.c.execute('UPDATE links SET linkorder=linkorder-1 WHERE linkorder <= ? AND linkorder > ? AND package=?', (position, f["order"], f["package"])) - - self.c.execute('UPDATE links SET linkorder=? WHERE id=?', (position, f["id"])) - - - @style.queue - def clearPackageOrder(self, p): - self.c.execute('UPDATE packages SET packageorder=? WHERE id=?', (-1, str(p.id))) - self.c.execute('UPDATE packages SET packageorder=packageorder-1 WHERE packageorder > ? AND queue=? AND id != ?', (p.order, p.queue, str(p.id))) - - @style.async - def restartFile(self, id): - self.c.execute('UPDATE links SET status=3,error="" WHERE id=?', (str(id),)) - - @style.async - def restartPackage(self, id): - self.c.execute('UPDATE links SET status=3 WHERE package=?', (str(id),)) - - @style.queue - def getPackage(self, id): - """return package instance from id""" - self.c.execute("SELECT name,folder,site,password,queue,packageorder FROM packages WHERE id=?", (str(id), )) + self.c.execute('UPDATE packages SET name=?, folder=?, site=?, comment=?, password=?, status=? WHERE pid=?', + (p.name, p.folder, p.site, p.comment, p.password, p.status, p.pid)) + + # TODO: most modifying methods needs owner argument to avoid checking beforehand + @async + def orderPackage(self, pid, root, oldorder, order): + if oldorder > order: # package moved upwards + self.c.execute( + 'UPDATE packages SET packageorder=packageorder+1 WHERE packageorder >= ? AND packageorder < ? AND root=? AND packageorder >= 0' + , (order, oldorder, root)) + elif oldorder < order: # moved downwards + self.c.execute( + 'UPDATE packages SET packageorder=packageorder-1 WHERE packageorder <= ? AND packageorder > ? AND root=? AND packageorder >= 0' + , (order, oldorder, root)) + + self.c.execute('UPDATE packages SET packageorder=? WHERE pid=?', (order, pid)) + + @async + def orderFiles(self, pid, fids, oldorder, order): + diff = len(fids) + data = [] + + if oldorder > order: # moved upwards + self.c.execute('UPDATE files SET fileorder=fileorder+? WHERE fileorder >= ? AND fileorder < ? AND package=?' + , (diff, order, oldorder, pid)) + data = [(order + i, fid) for i, fid in enumerate(fids)] + elif oldorder < order: + self.c.execute( + 'UPDATE files SET fileorder=fileorder-? WHERE fileorder <= ? AND fileorder >= ? AND package=?' + , (diff, order, oldorder + diff, pid)) + data = [(order - diff + i + 1, fid) for i, fid in enumerate(fids)] + + self.c.executemany('UPDATE files SET fileorder=? WHERE fid=?', data) + + @async + def moveFiles(self, pid, fids, package): + self.c.execute('SELECT max(fileorder) FROM files WHERE package=?', (package,)) r = self.c.fetchone() - if not r: return None - return PyPackage(self.manager, id, * r) + order = (r[0] if r[0] else 0) + 1 + + self.c.execute('UPDATE files SET fileorder=fileorder-? WHERE fileorder > ? AND package=?', + (len(fids), order, pid)) - #---------------------------------------------------------------------- - @style.queue - def getFile(self, id): - """return link instance from id""" - self.c.execute("SELECT url, name, size, status, error, plugin, package, linkorder FROM links WHERE id=?", (str(id), )) + data = [(package, order + i, fid) for i, fid in enumerate(fids)] + self.c.executemany('UPDATE files SET package=?, fileorder=? WHERE fid=?', data) + + @async + def movePackage(self, root, order, pid, dpid): + self.c.execute('SELECT max(packageorder) FROM packages WHERE root=?', (dpid,)) r = self.c.fetchone() - if not r: return None - return PyFile(self.manager, id, * r) + max = (r[0] if r[0] else 0) + 1 + self.c.execute('UPDATE packages SET packageorder=packageorder-1 WHERE packageorder > ? AND root=?', + (order, root)) - @style.queue - def getJob(self, occ): - """return pyfile ids, which are suitable for download and dont use a occupied plugin""" + self.c.execute('UPDATE packages SET root=?, packageorder=? WHERE pid=?', (dpid, max, pid)) - #@TODO improve this hardcoded method - pre = "('DLC', 'LinkList', 'SerienjunkiesOrg', 'CCF', 'RSDF')" #plugins which are processed in collector + @async + def restartFile(self, fid): + # status -> queued + self.c.execute('UPDATE files SET dlstatus=3, error="" WHERE fid=?', (fid,)) - cmd = "(" - for i, item in enumerate(occ): - if i: cmd += ", " - cmd += "'%s'" % item - - cmd += ")" + @async + def restartPackage(self, pid): + # status -> queued + self.c.execute('UPDATE files SET status=3 WHERE package=?', (pid,)) - cmd = "SELECT l.id FROM links as l INNER JOIN packages as p ON l.package=p.id WHERE ((p.queue=1 AND l.plugin NOT IN %s) OR l.plugin IN %s) AND l.status IN (2,3,14) ORDER BY p.packageorder ASC, l.linkorder ASC LIMIT 5" % (cmd, pre) - - self.c.execute(cmd) # very bad! - return [x[0] for x in self.c] + # TODO: multi user approach + @queue + def getJob(self, occ): + """return pyfile ids, which are suitable for download and dont use a occupied plugin""" + cmd = "(%s)" % ", ".join(["'%s'" % x for x in occ]) + #TODO - @style.queue - def getPluginJob(self, plugins): - """returns pyfile ids with suited plugins""" - cmd = "SELECT l.id FROM links as l INNER JOIN packages as p ON l.package=p.id WHERE l.plugin IN %s AND l.status IN (2,3,14) ORDER BY p.packageorder ASC, l.linkorder ASC LIMIT 5" % plugins + # dlstatus in online, queued | package status = ok + cmd = ("SELECT f.fid FROM files as f INNER JOIN packages as p ON f.package=p.pid " + "WHERE f.plugin NOT IN %s AND f.dlstatus IN (2,3) AND p.status=0 " + "ORDER BY p.packageorder ASC, f.fileorder ASC LIMIT 5") % cmd self.c.execute(cmd) # very bad! return [x[0] for x in self.c] - @style.queue + @queue def getUnfinished(self, pid): """return list of max length 3 ids with pyfiles in package not finished or processed""" - - self.c.execute("SELECT id FROM links WHERE package=? AND status NOT IN (0, 4, 13) LIMIT 3", (str(pid),)) - return [r[0] for r in self.c] - @style.queue - def deleteFinished(self): - self.c.execute("DELETE FROM links WHERE status IN (0,4)") - self.c.execute("DELETE FROM packages WHERE NOT EXISTS(SELECT 1 FROM links WHERE packages.id=links.package)") + # status in finished, skipped, processing + self.c.execute("SELECT fid FROM files WHERE package=? AND dlstatus NOT IN (5, 6, 14) LIMIT 3", (pid,)) + return [r[0] for r in self.c] - @style.queue - def restartFailed(self): - self.c.execute("UPDATE links SET status=3,error='' WHERE status IN (6, 8, 9)") + @queue + def restartFailed(self, owner): + # status=queued, where status in failed, aborted, temp offline + self.c.execute("UPDATE files SET dlstatus=3, error='' WHERE dlstatus IN (7, 11, 12)") - @style.queue + @queue def findDuplicates(self, id, folder, filename): """ checks if filename exists with different id and same package """ - self.c.execute("SELECT l.plugin FROM links as l INNER JOIN packages as p ON l.package=p.id AND p.folder=? WHERE l.id!=? AND l.status=0 AND l.name=?", (folder, id, filename)) + # TODO + self.c.execute( + "SELECT l.plugin FROM files f INNER JOIN packages as p ON f.package=p.pid AND p.folder=? WHERE f.fid!=? AND l.status=0 AND l.name=?" + , (folder, id, filename)) return self.c.fetchone() - @style.queue + @queue def purgeLinks(self): - self.c.execute("DELETE FROM links;") - self.c.execute("DELETE FROM packages;") - -DatabaseBackend.registerSub(FileMethods) - -if __name__ == "__main__": - - pypath = "." - _ = lambda x: x - - db = FileHandler(None) - - #p = PyFile(db, 5) - #sleep(0.1) - - a = time() - - #print db.addPackage("package", "folder" , 1) - - pack = db.db.addPackage("package", "folder", 1) - - updates = [] - - - for x in range(0, 200): - x = str(x) - db.db.addLink("http://somehost.com/hoster/file/download?file_id=" + x, x, "BasePlugin", pack) - updates.append(("new name" + x, 0, 3, "http://somehost.com/hoster/file/download?file_id=" + x)) - - - for x in range(0, 100): - updates.append(("unimportant%s" % x, 0, 3, "a really long non existent url%s" % x)) - - db.db.commit() - - b = time() - print "adding 200 links, single sql execs, no commit", b-a - - print db.getCompleteData(1) - - c = time() - - - db.db.updateLinkInfo(updates) - - d = time() - - print "updates", d-c - - print db.getCompleteData(1) - - - e = time() - - print "complete data", e-d + # fstatus = missing + self.c.execute("DELETE FROM files WHERE status == 1") + + @queue + def purgeAll(self): # only used for debugging + self.c.execute("DELETE FROM packages") + self.c.execute("DELETE FROM files") + self.c.execute("DELETE FROM collector") + +FileMethods.register()
\ No newline at end of file diff --git a/module/database/StatisticDatabase.py b/module/database/StatisticDatabase.py new file mode 100644 index 000000000..10619eb5b --- /dev/null +++ b/module/database/StatisticDatabase.py @@ -0,0 +1,13 @@ +#!/usr/bin/env python +# -*- coding: utf-8 -*- + +from module.database import DatabaseMethods, queue, async, inner + +# TODO + +class StatisticMethods(DatabaseMethods): + pass + + + +StatisticMethods.register()
\ No newline at end of file diff --git a/module/database/StorageDatabase.py b/module/database/StorageDatabase.py index 3ed29625f..ffaf51763 100644 --- a/module/database/StorageDatabase.py +++ b/module/database/StorageDatabase.py @@ -16,11 +16,10 @@ @author: mkaay """ -from module.database import style -from module.database import DatabaseBackend +from module.database import DatabaseBackend, queue class StorageMethods(): - @style.queue + @queue def setStorage(db, identifier, key, value): db.c.execute("SELECT id FROM storage WHERE identifier=? AND key=?", (identifier, key)) if db.c.fetchone() is not None: @@ -28,7 +27,7 @@ class StorageMethods(): else: db.c.execute("INSERT INTO storage (identifier, key, value) VALUES (?, ?, ?)", (identifier, key, value)) - @style.queue + @queue def getStorage(db, identifier, key=None): if key is not None: db.c.execute("SELECT value FROM storage WHERE identifier=? AND key=?", (identifier, key)) @@ -42,7 +41,7 @@ class StorageMethods(): d[row[0]] = row[1] return d - @style.queue + @queue def delStorage(db, identifier, key): db.c.execute("DELETE FROM storage WHERE identifier=? AND key=?", (identifier, key)) diff --git a/module/database/UserDatabase.py b/module/database/UserDatabase.py index 0c781057d..0df94e0eb 100644 --- a/module/database/UserDatabase.py +++ b/module/database/UserDatabase.py @@ -1,63 +1,96 @@ # -*- coding: utf-8 -*- -""" - This program is free software; you can redistribute it and/or modify - it under the terms of the GNU General Public License as published by - the Free Software Foundation; either version 3 of the License, - or (at your option) any later version. - This program is distributed in the hope that it will be useful, - but WITHOUT ANY WARRANTY; without even the implied warranty of - MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. - See the GNU General Public License for more details. +############################################################################### +# Copyright(c) 2008-2012 pyLoad Team +# http://www.pyload.org +# +# This file is part of pyLoad. +# pyLoad is free software: you can redistribute it and/or modify +# it under the terms of the GNU Affero General Public License as +# published by the Free Software Foundation, either version 3 of the +# License, or (at your option) any later version. +# +# Subjected to the terms and conditions in LICENSE +# +# @author: RaNaN +############################################################################### - You should have received a copy of the GNU General Public License - along with this program; if not, see <http://www.gnu.org/licenses/>. +from hashlib import sha1 +from string import letters, digits +from random import choice - @author: mkaay -""" +alphnum = letters+digits -from hashlib import sha1 -import random +from module.Api import UserData -from DatabaseBackend import DatabaseBackend -from DatabaseBackend import style +from DatabaseBackend import DatabaseMethods, queue, async -class UserMethods(): - @style.queue - def checkAuth(db, user, password): - c = db.c - c.execute('SELECT id, name, password, role, permission, template, email FROM "users" WHERE name=?', (user, )) - r = c.fetchone() - if not r: - return {} +def random_salt(): + return "".join(choice(alphnum) for x in range(0,5)) - salt = r[2][:5] - pw = r[2][5:] - h = sha1(salt + password) - if h.hexdigest() == pw: - return {"id": r[0], "name": r[1], "role": r[3], - "permission": r[4], "template": r[5], "email": r[6]} - else: - return {} +class UserMethods(DatabaseMethods): - @style.queue - def addUser(db, user, password): - salt = reduce(lambda x, y: x + y, [str(random.randint(0, 9)) for i in range(0, 5)]) + @queue + def addUser(self, user, password): + salt = random_salt() h = sha1(salt + password) password = salt + h.hexdigest() - c = db.c - c.execute('SELECT name FROM users WHERE name=?', (user, )) - if c.fetchone() is not None: - c.execute('UPDATE users SET password=? WHERE name=?', (password, user)) + self.c.execute('SELECT name FROM users WHERE name=?', (user, )) + if self.c.fetchone() is not None: + self.c.execute('UPDATE users SET password=? WHERE name=?', (password, user)) else: - c.execute('INSERT INTO users (name, password) VALUES (?, ?)', (user, password)) + self.c.execute('INSERT INTO users (name, password) VALUES (?, ?)', (user, password)) + + @queue + def getUserData(self, name=None, uid=None): + qry = ('SELECT uid, name, email, role, permission, folder, traffic, dllimit, dlquota, ' + 'hddquota, user, template FROM "users" WHERE ') + + if name is not None: + self.c.execute(qry + "name=?", (name,)) + r = self.c.fetchone() + if r: + return UserData(*r) + + elif uid is not None: + self.c.execute(qry + "uid=?", (uid,)) + r = self.c.fetchone() + if r: + return UserData(*r) + + return None + + @queue + def getAllUserData(self): + self.c.execute('SELECT uid, name, email, role, permission, folder, traffic, dllimit, dlquota, ' + 'hddquota, user, template FROM "users"') + user = {} + for r in self.c: + user[r[0]] = UserData(*r) + + return user - @style.queue - def changePassword(db, user, oldpw, newpw): - db.c.execute('SELECT id, name, password FROM users WHERE name=?', (user, )) - r = db.c.fetchone() + @queue + def checkAuth(self, user, password): + self.c.execute('SELECT uid, name, email, role, permission, folder, traffic, dllimit, dlquota, ' + 'hddquota, user, template, password FROM "users" WHERE name=?', (user, )) + r = self.c.fetchone() + if not r: + return None + salt = r[-1][:5] + pw = r[-1][5:] + h = sha1(salt + password) + if h.hexdigest() == pw: + return UserData(*r[:-1]) + else: + return None + + @queue #TODO + def changePassword(self, user, oldpw, newpw): + self.c.execute('SELECT rowid, name, password FROM users WHERE name=?', (user, )) + r = self.c.fetchone() if not r: return False @@ -65,44 +98,28 @@ class UserMethods(): pw = r[2][5:] h = sha1(salt + oldpw) if h.hexdigest() == pw: - salt = reduce(lambda x, y: x + y, [str(random.randint(0, 9)) for i in range(0, 5)]) + salt = random_salt() h = sha1(salt + newpw) password = salt + h.hexdigest() - db.c.execute("UPDATE users SET password=? WHERE name=?", (password, user)) + self.c.execute("UPDATE users SET password=? WHERE name=?", (password, user)) return True return False + @async + def setPermission(self, user, perms): + self.c.execute("UPDATE users SET permission=? WHERE name=?", (perms, user)) - @style.async - def setPermission(db, user, perms): - db.c.execute("UPDATE users SET permission=? WHERE name=?", (perms, user)) - - @style.async - def setRole(db, user, role): - db.c.execute("UPDATE users SET role=? WHERE name=?", (role, user)) + @async + def setRole(self, user, role): + self.c.execute("UPDATE users SET role=? WHERE name=?", (role, user)) + # TODO update methods - @style.queue - def listUsers(db): - db.c.execute('SELECT name FROM users') - users = [] - for row in db.c: - users.append(row[0]) - return users - - @style.queue - def getAllUserData(db): - db.c.execute("SELECT name, permission, role, template, email FROM users") - user = {} - for r in db.c: - user[r[0]] = {"permission": r[1], "role": r[2], "template": r[3], "email": r[4]} - - return user - - @style.queue - def removeUser(db, user): - db.c.execute('DELETE FROM users WHERE name=?', (user, )) + @async + def removeUser(self, uid=None): + # deletes user and all associated accounts + self.c.execute('DELETE FROM users WHERE user=?', (uid, )) -DatabaseBackend.registerSub(UserMethods) +UserMethods.register() diff --git a/module/database/__init__.py b/module/database/__init__.py index 545789c0c..d3f97fb53 100644 --- a/module/database/__init__.py +++ b/module/database/__init__.py @@ -1,6 +1,8 @@ -from DatabaseBackend import DatabaseBackend -from DatabaseBackend import style +from DatabaseBackend import DatabaseMethods, DatabaseBackend, queue, async, inner -from FileDatabase import FileHandler +from FileDatabase import FileMethods from UserDatabase import UserMethods -from StorageDatabase import StorageMethods
\ No newline at end of file +from StorageDatabase import StorageMethods +from AccountDatabase import AccountMethods +from ConfigDatabase import ConfigMethods +from StatisticDatabase import StatisticMethods
\ No newline at end of file |