updates for database + api

3 files changed, 100 insertions, 60 deletions

diff --git a/module/database/DatabaseBackend.py b/module/database/DatabaseBackend.py
index ec39e3fd9..2c494e520 100644
--- a/module/database/DatabaseBackend.py
+++ b/module/database/DatabaseBackend.py
@@ -5,15 +5,15 @@
 #   Copyright(c) 2008-2012 pyLoad Team
 #   http://www.pyload.org
 #
-#   This program is free software: you can redistribute it and/or modify
+#   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
-#   @author: mkaay
+#   @author: RaNaN, mkaay
 ###############################################################################
 
 from threading import Thread, Event
@@ -268,7 +268,6 @@ class DatabaseBackend(Thread):
             '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)')
 
@@ -292,7 +291,6 @@ class DatabaseBackend(Thread):
             '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)')
 
@@ -309,7 +307,7 @@ class DatabaseBackend(Thread):
             'CREATE TABLE IF NOT EXISTS "collector" ('
             '"owner" INTEGER NOT NULL, '
             '"data" TEXT NOT NULL, '
-            'FOREIGN KEY(owner) REFERENCES users(uid)'
+            'FOREIGN KEY(owner) REFERENCES users(uid), '
             'PRIMARY KEY(owner) ON CONFLICT REPLACE'
             ') '
         )
@@ -326,7 +324,7 @@ class DatabaseBackend(Thread):
         self.c.execute(
             'CREATE TABLE IF NOT EXISTS "users" ('
             '"uid" INTEGER PRIMARY KEY AUTOINCREMENT, '
-            '"name" TEXT NOT NULL, '
+            '"name" TEXT NOT NULL UNIQUE, '
             '"email" TEXT DEFAULT "" NOT NULL, '
             '"password" TEXT NOT NULL, '
             '"role" INTEGER DEFAULT 0 NOT NULL, '
@@ -334,12 +332,13 @@ class DatabaseBackend(Thread):
             '"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(
@@ -369,11 +368,24 @@ class DatabaseBackend(Thread):
             '"password" TEXT DEFAULT "", '
             '"shared" INTEGER DEFAULT 0, '
             '"options" TEXT DEFAULT "", '
-            'FOREIGN KEY(owner) REFERENCES users(uid)'
+            '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]
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/UserDatabase.py b/module/database/UserDatabase.py
index 6bfb02bbd..bed4e94a9 100644
--- a/module/database/UserDatabase.py
+++ b/module/database/UserDatabase.py
@@ -1,42 +1,28 @@
 # -*- 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
-"""
+###############################################################################
+#   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
 import random
 
+from module.Api import UserData
+
 from DatabaseBackend import DatabaseMethods, queue, async
 
 class UserMethods(DatabaseMethods):
-    @queue
-    def checkAuth(self, user, password):
-        self.c.execute('SELECT rowid, name, password, role, permission, template, email FROM "users" WHERE name=?', (user, ))
-        r = self.c.fetchone()
-        if not r:
-            return {}
-
-        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 {}
 
     @queue
     def addUser(self, user, password):
@@ -50,8 +36,53 @@ class UserMethods(DatabaseMethods):
         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()
@@ -71,7 +102,6 @@ class UserMethods(DatabaseMethods):
 
         return False
 
-
     @async
     def setPermission(self, user, perms):
         self.c.execute("UPDATE users SET permission=? WHERE name=?", (perms, user))
@@ -80,26 +110,11 @@ class UserMethods(DatabaseMethods):
     def setRole(self, user, role):
         self.c.execute("UPDATE users SET role=? WHERE name=?", (role, user))
 
+    # TODO update methods
 
-    @queue
-    def listUsers(self):
-        self.c.execute('SELECT name FROM users')
-        users = []
-        for row in self.c:
-            users.append(row[0])
-        return users
-
-    @queue
-    def getAllUserData(self):
-        self.c.execute("SELECT name, permission, role, template, email FROM users")
-        user = {}
-        for r in self.c:
-            user[r[0]] = {"permission": r[1], "role": r[2], "template": r[3], "email": r[4]}
-
-        return user
-
-    @queue
-    def removeUser(self, user):
-        self.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, ))
 
 UserMethods.register()