summaryrefslogtreecommitdiffstats
path: root/pyload/database
diff options
context:
space:
mode:
Diffstat (limited to 'pyload/database')
-rw-r--r--pyload/database/AccountDatabase.py25
-rw-r--r--pyload/database/ConfigDatabase.py56
-rw-r--r--pyload/database/DatabaseBackend.py492
-rw-r--r--pyload/database/FileDatabase.py448
-rw-r--r--pyload/database/StatisticDatabase.py13
-rw-r--r--pyload/database/StorageDatabase.py48
-rw-r--r--pyload/database/UserDatabase.py125
-rw-r--r--pyload/database/__init__.py8
8 files changed, 1215 insertions, 0 deletions
diff --git a/pyload/database/AccountDatabase.py b/pyload/database/AccountDatabase.py
new file mode 100644
index 000000000..eaa1a3203
--- /dev/null
+++ b/pyload/database/AccountDatabase.py
@@ -0,0 +1,25 @@
+# -*- coding: utf-8 -*-
+
+from pyload.database import queue, async
+from pyload.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):
+ # TODO: owner, shared
+
+ 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/pyload/database/ConfigDatabase.py b/pyload/database/ConfigDatabase.py
new file mode 100644
index 000000000..0c0dd72dd
--- /dev/null
+++ b/pyload/database/ConfigDatabase.py
@@ -0,0 +1,56 @@
+#!/usr/bin/env python
+# -*- coding: utf-8 -*-
+
+from pyload.database import DatabaseMethods, queue, async
+
+class ConfigMethods(DatabaseMethods):
+
+ @async
+ def saveConfig(self, plugin, config, user=None):
+ if user is None: user = -1
+ self.c.execute('INSERT INTO settings(plugin, config, user) VALUES(?,?,?)', (plugin, config, user))
+
+
+ @queue
+ def loadConfig(self, plugin, user=None):
+ if user is None: user = -1
+ self.c.execute('SELECT config FROM settings WHERE plugin=? AND user=?', (plugin, user))
+
+ r = self.c.fetchone()
+ return r[0] if r else ""
+
+ @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
+
+ @queue
+ def loadConfigsForUser(self, user=None):
+ if user is None: user = -1
+ self.c.execute('SELECT plugin, config FROM settings WHERE user=?', (user,))
+ configs = {}
+ for r in self.c:
+ configs[r[0]] = r[1]
+
+ return configs
+
+ @async
+ def clearAllConfigs(self):
+ self.c.execute('DELETE FROM settings')
+
+
+ConfigMethods.register() \ No newline at end of file
diff --git a/pyload/database/DatabaseBackend.py b/pyload/database/DatabaseBackend.py
new file mode 100644
index 000000000..99a406d9c
--- /dev/null
+++ b/pyload/database/DatabaseBackend.py
@@ -0,0 +1,492 @@
+#!/usr/bin/env python
+# -*- 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 pyload.utils.fs import chmod, exists, remove
+
+try:
+ from pysqlite2 import dbapi2 as sqlite3
+except:
+ import sqlite3
+
+DB = None
+DB_VERSION = 6
+
+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
+
+ @classmethod
+ 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()
+
+ def __repr__(self):
+ from os.path import basename
+
+ frame = self.frame.f_back
+ output = ""
+ for i in range(5):
+ output += "\t%s:%s, %s\n" % (basename(frame.f_code.co_filename), frame.f_lineno, frame.f_code.co_name)
+ frame = frame.f_back
+ del frame
+ del self.frame
+
+ return "DataBase Job %s:%s\n%sResult: %s" % (self.f.__name__, self.args[1:], output, self.result)
+
+ def processJob(self):
+ try:
+ self.result = self.f(*self.args, **self.kwargs)
+ except Exception, e:
+ print_exc()
+ try:
+ print "Database Error @", self.f.__name__, self.args[1:], self.kwargs, e
+ except:
+ pass
+
+ 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()
+
+ set_DB(self)
+
+ def setup(self):
+ """ *MUST* be called before db can be used !"""
+ self.start()
+ self.running.wait()
+
+ def init(self):
+ """main loop, which executes commands"""
+
+ 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(_("Database was deleted due to incompatible version."))
+ except:
+ print "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()
+
+
+ def run(self):
+ try:
+ self.init()
+ finally:
+ self.running.set()
+
+ while True:
+ j = self.jobs.get()
+ if j == "quit":
+ self.c.close()
+ self.conn.commit()
+ self.conn.close()
+ self.closing.set()
+ break
+ j.processJob()
+
+
+ def shutdown(self):
+ self.running.clear()
+ self.closing = Event()
+ self.jobs.put("quit")
+ self.closing.wait(1)
+
+ def _checkVersion(self):
+ """ 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(self.VERSION_FILE, "rb")
+ v = int(f.read().strip())
+ f.close()
+
+ return v
+
+ def _convertDB(self, v):
+ try:
+ return getattr(self, "_convertV%i" % v)()
+ except:
+ return False
+
+ #--convert scripts start
+
+ def _convertV6(self):
+ return False
+
+ #--convert scripts end
+
+ def _createTables(self):
+ """create tables for database"""
+
+ 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,'
+ '"tags" TEXT DEFAULT "" NOT NULL,'
+ '"shared" 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 DEFAULT -1, '
+ '"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(fid) FROM files')
+ fid = self.c.fetchone()[0]
+ fid = int(fid) if fid else 0
+ self.c.execute('UPDATE SQLITE_SEQUENCE SET seq=? WHERE name=?', (fid, "files"))
+
+ self.c.execute('SELECT max(pid) FROM packages')
+ pid = self.c.fetchone()[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 createCursor(self):
+ return self.conn.cursor()
+
+ @async
+ def commit(self):
+ self.conn.commit()
+
+ @queue
+ def syncSave(self):
+ self.conn.commit()
+
+ @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)
+ # 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():
+ @queue
+ def insert(db):
+ c = db.createCursor()
+ for i in range(1000):
+ c.execute("INSERT INTO storage (identifier, key, value) VALUES (?, ?, ?)", ("foo", i, "bar"))
+
+ @async
+ def insert2(db):
+ c = db.createCursor()
+ for i in range(1000 * 1000):
+ c.execute("INSERT INTO storage (identifier, key, value) VALUES (?, ?, ?)", ("foo", i, "bar"))
+
+ @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()
+
+ @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
+
+ start = time()
+ db.insert2()
+ end = time()
+ print end - start
+
+ db.error()
+
diff --git a/pyload/database/FileDatabase.py b/pyload/database/FileDatabase.py
new file mode 100644
index 000000000..7b39cfa47
--- /dev/null
+++ b/pyload/database/FileDatabase.py
@@ -0,0 +1,448 @@
+#!/usr/bin/env python
+# -*- 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 pyload.Api import DownloadInfo, FileInfo, PackageInfo, PackageStats, DownloadState as DS, state_string
+from pyload.database import DatabaseMethods, queue, async, inner
+
+zero_stats = PackageStats(0, 0, 0, 0)
+
+
+class FileMethods(DatabaseMethods):
+
+ @queue
+ def filecount(self):
+ """returns number of files, currently only used for debugging"""
+ self.c.execute("SELECT COUNT(*) FROM files")
+ return self.c.fetchone()[0]
+
+ @queue
+ def downloadstats(self, user=None):
+ """ number of downloads and size """
+ if user is None:
+ self.c.execute("SELECT COUNT(*), SUM(f.size) FROM files f WHERE dlstatus != 0")
+ else:
+ self.c.execute(
+ "SELECT COUNT(*), SUM(f.size) FROM files f, packages p WHERE f.package = p.pid AND dlstatus != 0",
+ user)
+
+ r = self.c.fetchone()
+ # sum is None when no elements are added
+ return (r[0], r[1] if r[1] is not None else 0) if r else (0, 0)
+
+ @queue
+ def queuestats(self, user=None):
+ """ number and size of files in queue not finished yet"""
+ # status not in NA, finished, skipped
+ if user is None:
+ self.c.execute("SELECT COUNT(*), SUM(f.size) FROM files f WHERE dlstatus NOT IN (0,5,6)")
+ else:
+ self.c.execute(
+ "SELECT COUNT(*), SUM(f.size) FROM files f, package p WHERE f.package = p.pid AND p.owner=? AND dlstatus NOT IN (0,5,6)",
+ user)
+
+ r = self.c.fetchone()
+ return (r[0], r[1] if r[1] is not None else 0) if r else (0, 0)
+
+
+ # TODO: multi user?
+ @queue
+ def processcount(self, fid=-1, user=None):
+ """ number of files which have to be processed """
+ # status in online, queued, starting, waiting, downloading
+ self.c.execute("SELECT COUNT(*), SUM(size) FROM files WHERE dlstatus IN (2,3,8,9,10) AND fid != ?", (fid, ))
+ return self.c.fetchone()[0]
+
+ @queue
+ def processstats(self, user=None):
+ if user is None:
+ self.c.execute("SELECT COUNT(*), SUM(size) FROM files WHERE dlstatus IN (2,3,8,9,10)")
+ else:
+ self.c.execute(
+ "SELECT COUNT(*), SUM(f.size) FROM files f, packages p WHERE f.package = p.pid AND dlstatus IN (2,3,8,9,10)",
+ user)
+ r = self.c.fetchone()
+ return (r[0], r[1] if r[1] is not None else 0) if r else (0, 0)
+
+ @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
+
+ @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
+
+ @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
+
+ @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]
+
+ @async
+ def deleteCollector(self, owner):
+ """ drop saved user collector """
+ self.c.execute('DELETE FROM collector WHERE owner=?', (owner,))
+
+ @queue
+ def getAllFiles(self, package=None, search=None, state=None, owner=None):
+ """ Return dict with file information
+
+ :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 ')
+
+ arg = []
+
+ if state is not None and state != DS.All:
+ qry += 'dlstatus IN (%s) AND ' % state_string(state)
+ if owner is not None:
+ qry += 'owner=? AND '
+ arg.append(owner)
+
+ 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 ? "
+
+ # make qry valid
+ if qry.endswith("WHERE "): qry = qry[:-6]
+ if qry.endswith("AND "): qry = qry[:-4]
+
+ self.c.execute(qry + "ORDER BY package, fileorder", arg)
+
+ 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])
+
+ data[r[0]] = f
+
+ return data
+
+ @queue
+ def getMatchingFilenames(self, pattern, owner=None):
+ """ Return matching file names for pattern, useful for search suggestions """
+ qry = 'SELECT name FROM files WHERE name LIKE ?'
+ args = ["%%%s%%" % pattern.strip("%")]
+ if owner:
+ qry += " AND owner=?"
+ args.append(owner)
+
+ self.c.execute(qry, args)
+ return [r[0] for r in self.c]
+
+ @queue
+ def getAllPackages(self, root=None, owner=None, tags=None):
+ """ Return dict with package information
+
+ :param root: optional root to filter
+ :param owner: optional user id
+ :param tags: optional tag list
+ """
+ qry = (
+ 'SELECT pid, name, folder, root, owner, site, comment, password, added, tags, status, shared, packageorder '
+ 'FROM packages%s ORDER BY root, packageorder')
+
+ if root is None:
+ stats = self.getPackageStats(owner=owner)
+ if owner is None:
+ self.c.execute(qry % "")
+ else:
+ self.c.execute(qry % " WHERE owner=?", (owner,))
+ else:
+ stats = self.getPackageStats(root=root, owner=owner)
+ if owner is None:
+ self.c.execute(qry % ' WHERE root=? OR pid=?', (root, root))
+ else:
+ self.c.execute(qry % ' WHERE (root=? OR pid=?) AND owner=?', (root, root, owner))
+
+ 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].split(","), r[10], r[11], r[12],
+ stats.get(r[0], zero_stats)
+ )
+
+ return data
+
+ @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:
+ self.c.execute(qry % {"sub": ""})
+
+ data = {}
+ for r in self.c:
+ 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
+
+ @queue
+ def getStatsForPackage(self, pid):
+ return self.getPackageStats(pid=pid)[pid]
+
+ @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
+ 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])
+
+ return f
+
+ @queue
+ def getPackageInfo(self, pid, stats=True):
+ """get data for a specific package, optionally with package stats"""
+ if stats:
+ stats = self.getPackageStats(pid=pid)
+
+ self.c.execute(
+ 'SELECT pid, name, folder, root, owner, site, comment, password, added, tags, status, shared, 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].split(","), r[10], r[11], r[12],
+ stats.get(r[0], zero_stats) if stats else None
+ )
+
+ # TODO: does this need owner?
+ @async
+ def updateLinkInfo(self, data):
+ """ data is list of tuples (name, size, status,[ hash,] url)"""
+ # status in (NA, Offline, Online, Queued, TempOffline)
+ 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,11)',
+ data)
+ else:
+ self.c.executemany(
+ 'UPDATE files SET name=?, size=?, dlstatus=?, hash=? WHERE url=? AND dlstatus IN (0,1,2,3,11)', data)
+
+ @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))
+
+ @async
+ def updatePackage(self, p):
+ self.c.execute(
+ 'UPDATE packages SET name=?, folder=?, site=?, comment=?, password=?, tags=?, status=?, shared=? WHERE pid=?',
+ (p.name, p.folder, p.site, p.comment, p.password, ",".join(p.tags), p.status, p.shared, 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()
+ 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))
+
+ 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()
+ max = (r[0] if r[0] else 0) + 1
+
+ self.c.execute('UPDATE packages SET packageorder=packageorder-1 WHERE packageorder > ? AND root=?',
+ (order, root))
+
+ self.c.execute('UPDATE packages SET root=?, packageorder=? WHERE pid=?', (dpid, max, pid))
+
+ @async
+ def restartFile(self, fid):
+ # status -> queued
+ self.c.execute('UPDATE files SET dlstatus=3, error="" WHERE fid=?', (fid,))
+
+ @async
+ def restartPackage(self, pid):
+ # status -> queued
+ self.c.execute('UPDATE files SET status=3 WHERE package=?', (pid,))
+
+
+ # TODO: multi user approach
+ @queue
+ def getJob(self, occ):
+ """return pyfile ids, which are suitable for download and don't use a occupied plugin"""
+ cmd = "(%s)" % ", ".join(["'%s'" % x for x in occ])
+ #TODO
+
+ # 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)
+
+ return [x[0] for x in self.c]
+
+ @queue
+ def getUnfinished(self, pid):
+ """return list of max length 3 ids with pyfiles in package not finished or processed"""
+
+ # 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]
+
+ @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)")
+
+ @queue
+ def findDuplicates(self, id, folder, filename):
+ """ checks if filename exists with different id and same package, dlstatus = finished """
+ # TODO: also check root of package
+ self.c.execute(
+ "SELECT f.plugin FROM files f INNER JOIN packages as p ON f.package=p.pid AND p.folder=? WHERE f.fid!=? AND f.dlstatus=5 AND f.name=?"
+ , (folder, id, filename))
+ return self.c.fetchone()
+
+ @queue
+ def purgeLinks(self):
+ # 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/pyload/database/StatisticDatabase.py b/pyload/database/StatisticDatabase.py
new file mode 100644
index 000000000..d5f9658f2
--- /dev/null
+++ b/pyload/database/StatisticDatabase.py
@@ -0,0 +1,13 @@
+#!/usr/bin/env python
+# -*- coding: utf-8 -*-
+
+from pyload.database import DatabaseMethods, queue, async, inner
+
+# TODO
+
+class StatisticMethods(DatabaseMethods):
+ pass
+
+
+
+StatisticMethods.register() \ No newline at end of file
diff --git a/pyload/database/StorageDatabase.py b/pyload/database/StorageDatabase.py
new file mode 100644
index 000000000..2d4c8a9c7
--- /dev/null
+++ b/pyload/database/StorageDatabase.py
@@ -0,0 +1,48 @@
+# -*- 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.
+
+ 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: mkaay
+"""
+
+from pyload.database import DatabaseBackend, queue
+
+class StorageMethods():
+ @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:
+ db.c.execute("UPDATE storage SET value=? WHERE identifier=? AND key=?", (value, identifier, key))
+ else:
+ db.c.execute("INSERT INTO storage (identifier, key, value) VALUES (?, ?, ?)", (identifier, key, value))
+
+ @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))
+ row = db.c.fetchone()
+ if row is not None:
+ return row[0]
+ else:
+ db.c.execute("SELECT key, value FROM storage WHERE identifier=?", (identifier, ))
+ d = {}
+ for row in db.c:
+ d[row[0]] = row[1]
+ return d
+
+ @queue
+ def delStorage(db, identifier, key):
+ db.c.execute("DELETE FROM storage WHERE identifier=? AND key=?", (identifier, key))
+
+DatabaseBackend.registerSub(StorageMethods)
diff --git a/pyload/database/UserDatabase.py b/pyload/database/UserDatabase.py
new file mode 100644
index 000000000..3dd06c912
--- /dev/null
+++ b/pyload/database/UserDatabase.py
@@ -0,0 +1,125 @@
+# -*- 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
+###############################################################################
+
+from hashlib import sha1
+from string import letters, digits
+from random import choice
+
+alphnum = letters+digits
+
+from pyload.Api import UserData
+
+from DatabaseBackend import DatabaseMethods, queue, async
+
+def random_salt():
+ return "".join(choice(alphnum) for x in range(0,5))
+
+class UserMethods(DatabaseMethods):
+
+ @queue
+ def addUser(self, user, password):
+ salt = random_salt()
+ h = sha1(salt + password)
+ password = salt + h.hexdigest()
+
+ 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:
+ 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
+
+
+ @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
+
+ salt = r[2][:5]
+ pw = r[2][5:]
+ h = sha1(salt + oldpw)
+ if h.hexdigest() == pw:
+ salt = random_salt()
+ h = sha1(salt + newpw)
+ password = salt + h.hexdigest()
+
+ 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))
+
+ @async
+ def setRole(self, user, role):
+ self.c.execute("UPDATE users SET role=? WHERE name=?", (role, user))
+
+ # TODO update methods
+
+ @async
+ def removeUser(self, uid=None):
+ # deletes user and all associated accounts
+ self.c.execute('DELETE FROM users WHERE user=?', (uid, ))
+
+UserMethods.register()
diff --git a/pyload/database/__init__.py b/pyload/database/__init__.py
new file mode 100644
index 000000000..d3f97fb53
--- /dev/null
+++ b/pyload/database/__init__.py
@@ -0,0 +1,8 @@
+from DatabaseBackend import DatabaseMethods, DatabaseBackend, queue, async, inner
+
+from FileDatabase import FileMethods
+from UserDatabase import UserMethods
+from StorageDatabase import StorageMethods
+from AccountDatabase import AccountMethods
+from ConfigDatabase import ConfigMethods
+from StatisticDatabase import StatisticMethods \ No newline at end of file
generated by cgit v1.2.3 (git 2.25.1) at 2025-10-31 03:12:34 +0100