File: userDb.py

package info (click to toggle)
thawab 4.1-2
  • links: PTS, VCS
  • area: non-free
  • in suites: bullseye, sid
  • size: 1,912 kB
  • sloc: python: 4,041; makefile: 134; sh: 33
file content (177 lines) | stat: -rw-r--r-- 6,460 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
# -*- coding: UTF-8 -*-
"""
The meta handling classes of thawab
Copyright © 2008-2010, Muayyad Alsadi <alsadi@ojuba.org>

        Released under terms of Waqf Public License.
        This program is free software; you can redistribute it and/or modify
        it under the terms of the latest version Waqf Public License as
        published by Ojuba.org.

        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.

        The Latest version of the license can be found on
        "http://waqf.ojuba.org/license"

"""
import sys, os, os.path, re, sqlite3, time, threading

#################################################

USER_DB_SCHEMA = """\
CREATE TABLE "starred" (
    "kitab" TEXT PRIMARY KEY,
    "time" FLOAT
);

CREATE INDEX StarredTimeIndex on starred(time);

CREATE TABLE "bookmarks" (
    "kitab" TEXT,
    "version" TEXT,
    "globalOrder" INTEGER,
    "nodeIdNum" INTEGER,
    "nodeId" TEXT,
    "title" TEXT,
    "time" FLOAT,
    PRIMARY KEY ("kitab", "version", nodeId)
);

CREATE INDEX BookmarksKitabIndex on bookmarks(kitab);
CREATE INDEX BookmarksNodeIdNumIndex on bookmarks(nodeIdNum);
CREATE INDEX BookmarksGlobalOrderIndex on bookmarks(globalOrder);
CREATE INDEX BookmarksTimeIndex on bookmarks(time);

CREATE TABLE "comments" (
    "kitab" TEXT,
    "version" TEXT,
    "globalOrder" INTEGER,
    "nodeIdNum" INTEGER,
    "nodeId" TEXT,
    "title" TEXT,
    "comment" TEXT,
    "time" FLOAT,
    PRIMARY KEY ("kitab", "version", nodeId)
);

CREATE INDEX CommentsKitabIndex on comments(kitab);
CREATE INDEX CommentsNodeIdNumIndex on comments(nodeIdNum);
CREATE INDEX CommentsGlobalOrderIndex on comments(globalOrder);
CREATE INDEX CommentsTimeIndex on comments(time);

"""
SQL_GET_ALL_STARRED = """SELECT kitab FROM starred ORDER BY time"""
SQL_GET_STARRED_TIME = """SELECT time FROM starred WHERE kitab=?"""
SQL_SET_STARRED = 'INSERT OR REPLACE INTO starred (kitab, time) VALUES (?, ?)'
SQL_UNSET_STARRED = 'DELETE OR IGNORE FROM starred WHERE kitab=?'

# NOTE: globalOrder is used to get the right book order
# NOTE: nodeIdNum is used for consistancy checking and optimization

SQL_GET_ALL_BOOKMARKS = """SELECT * FROM bookmarks ORDER BY kitab"""
SQL_GET_BOOKMARKED_KUTUB = """SELECT DISTINCT kitab FROM bookmarks ORDER BY kitab"""
SQL_GET_KITAB_BOOKMARKS = """SELECT * FROM bookmarks WHERE kitab=? ORDER BY time"""
SQL_ADD_BOOKMARK = 'INSERT OR REPLACE INTO bookmarks (kitab, version, globalOrder, nodeIdNum, nodeId, title, time) VALUES (?,?,?,?,?,?,?)'

SQL_GET_ALL_COMMENTS = """SELECT * FROM comments ORDER BY kitab"""
SQL_GET_COMMENTED_KUTUB = """SELECT DISTINCT kitab FROM comments ORDER BY kitab"""
SQL_GET_KITAB_COMMENTS = """SELECT * FROM comments WHERE kitab=? ORDER BY time"""
SQL_ADD_COMMENT = 'INSERT OR REPLACE INTO comments (kitab, version, globalOrder, nodeIdNum, nodeId, title, comment, time) VALUES (?,?,?,?,?,?,?,?)'

#################################

class UserDb(object):
    """a class holding metadata cache"""
    def __init__(self, th, user_db):
        self.th = th
        self.db_fn = user_db
        if not os.path.exists(self.db_fn):
            create_new = True
        else:
            create_new = False
        self._cn = {}
        cn = self._getConnection()
        if create_new:
            cn.executescript(USER_DB_SCHEMA)
            cn.commit()

    def _getConnection(self):
        n = threading.current_thread().name
        if n in self._cn:
            r = self._cn[n]
        else:
            r = sqlite3.connect(self.db_fn)
            r.row_factory = sqlite3.Row
            self._cn[n] = r
        return r

    def getStarredTime(self, kitab):
        """
            return None if not starred, can be used to check if starred
        """
        r = self._getConnection().execute(SQL_GET_STARRED_TIME, (kitab,)).fetchone()
        if not r:
            return None
        return r['time']

    def getStarredList(self):
        r = self._getConnection().execute(SQL_GET_ALL_STARRED).fetchall()
        return [i['kitab'] for i in r]

    def starKitab(self, kitab):
        self._getConnection().execute(SQL_SET_STARRED , (kitab, float(time.time())))

    def unstarKitab(self, kitab):
        self._getConnection().execute(SQL_UNSET_STARRED, (kitab,))

    def starKitab(self, kitab):
        self._getConnection().execute(SQL_SET_STARRED , (kitab, float(time.time())))

    def getAllBookmarks(self):
        r = self._getConnection().execute(SQL_GET_ALL_BOOKMARKS).fetchall()
        return [dict(i) for i in r]

    def getBookmarkedKutub(self):
        r = self._getConnection().execute(SQL_GET_BOOKMARKED_KUTUB).fetchall()
        return [i['kitab'] for i in r]

    def getKitabBookmarks(self, kitab):
        r = self._getConnection().execute(SQL_GET_KITAB_BOOKMARKS, (kitab, )).fetchall()
        return [dict(i) for i in r]

    def addBookmark(self, kitab, version, globalOrder, nodeIdNum, nodeId, title):
        self._getConnection().execute(SQL_ADD_BOOKMARKS,
                                      (kitab,
                                       version,
                                       globalOrder,
                                       nodeIdNum,
                                       nodeId,
                                       title,
                                       float(time.time()) ))

    def getAllComments(self):
        r = self._getConnection().execute(SQL_GET_ALL_COMMENTS).fetchall()
        return [dict(i) for i in r]

    def getCommentedKutub(self):
        r = self._getConnection().execute(SQL_GET_COMMENTED_KUTUB).fetchall()
        return [i['kitab'] for i in r]

    def getKitabComments(self, kitab):
        r = self._getConnection().execute(SQL_GET_KITAB_COMMENTS, (kitab, )).fetchall()
        return [dict(i) for i in r]

    def addComment(self, kitab, version, globalOrder, nodeIdNum, nodeId, title, comment):
        self._getConnection().execute(SQL_ADD_COMMENT,
                                      (kitab,
                                       version,
                                       globalOrder,
                                       nodeIdNum,
                                       nodeId,
                                       title,
                                       comment,
                                       float(time.time()) ))