create tables for database
(self)
| 210 | #--convert scripts end |
| 211 | |
| 212 | def _createTables(self): |
| 213 | """create tables for database""" |
| 214 | |
| 215 | 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)') |
| 216 | 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))') |
| 217 | self.c.execute('CREATE INDEX IF NOT EXISTS "pIdIndex" ON links(package)') |
| 218 | 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 "")') |
| 219 | 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)') |
| 220 | |
| 221 | self.c.execute('CREATE VIEW IF NOT EXISTS "pstats" AS \ |
| 222 | SELECT p.id AS id, SUM(l.size) AS sizetotal, COUNT(l.id) AS linkstotal, linksdone, sizedone\ |
| 223 | FROM packages p JOIN links l ON p.id = l.package LEFT OUTER JOIN\ |
| 224 | (SELECT p.id AS id, COUNT(*) AS linksdone, SUM(l.size) AS sizedone \ |
| 225 | 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 \ |
| 226 | GROUP BY p.id') |
| 227 | |
| 228 | #try to lower ids |
| 229 | self.c.execute('SELECT max(id) FROM LINKS') |
| 230 | fid = self.c.fetchone()[0] |
| 231 | if fid: |
| 232 | fid = int(fid) |
| 233 | else: |
| 234 | fid = 0 |
| 235 | self.c.execute('UPDATE SQLITE_SEQUENCE SET seq=? WHERE name=?', (fid, "links")) |
| 236 | |
| 237 | |
| 238 | self.c.execute('SELECT max(id) FROM packages') |
| 239 | pid = self.c.fetchone()[0] |
| 240 | if pid: |
| 241 | pid = int(pid) |
| 242 | else: |
| 243 | pid = 0 |
| 244 | self.c.execute('UPDATE SQLITE_SEQUENCE SET seq=? WHERE name=?', (pid, "packages")) |
| 245 | |
| 246 | self.c.execute('VACUUM') |
| 247 | |
| 248 | |
| 249 | def _migrateUser(self): |