File: dataModel.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 (257 lines) | stat: -rw-r--r-- 13,945 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
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
# -*- coding: UTF-8 -*-
"""
The string constants to handle the data model
Copyright © 2008, 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"

"""
from .tags import *
MCACHE_BASE_FIELDS = [
  'cache_hash','repo','lang','kitab','version', 'releaseMajor', 'releaseMinor', 'type',
  'author', 'year', 'originalAuthor', 'originalYear', 'originalKitab', 'originalVersion',
  'classification', 'keywords'
]
MCACHE_FIELDS = MCACHE_BASE_FIELDS + ['uri', 'mtime', 'flags']

SQL_MCACHE_SET = 'INSERT OR REPLACE INTO meta (rowid, %s) VALUES (1, %s)' % \
                 (', '.join(MCACHE_BASE_FIELDS),
                 ', '.join([":"+i for i in MCACHE_BASE_FIELDS]))
SQL_MCACHE_ADD = 'INSERT OR REPLACE INTO meta (%s) VALUES (%s)' % \
                 (', '.join(MCACHE_FIELDS),
                 ', '.join([":"+i for i in MCACHE_FIELDS]))
SQL_MCACHE_DROP = 'DELETE FROM meta WHERE uri=?'

MCACHE_BASE = """\
CREATE TABLE "meta" (
	"cache_hash" TEXT,
	"repo" TEXT,
	"lang" TEXT,
	"kitab" TEXT,
	"version" TEXT,
	"releaseMajor" INTEGER,
	"releaseMinor" INTEGER,
	"type" INTEGER,
	"author" TEXT,
	"year" INTEGER,
	"originalAuthor" TEXT,
	"originalYear" INTEGER,
	"originalKitab" TEXT,
	"originalVersion" TEXT,
	"classification" TEXT,
	"keywords" TEXT
);"""

SQL_MCACHE_DATA_MODEL = MCACHE_BASE[:MCACHE_BASE.find('\n)')] + \
""",\n\
	"uri" TEXT UNIQUE,
	"mtime" FLOAT,
	"flags" INTEGER DEFAULT 0
);

CREATE INDEX MetaURIIndex on meta (uri);
CREATE INDEX MetaRepoIndex on meta (repo);
CREATE INDEX MetaLangIndex on meta (lang);
CREATE INDEX MetaKitabIndex on meta (kitab);
CREATE INDEX MetaKitabTypeIndex on meta (type);
CREATE INDEX MetaKitabVersionIndex on meta (repo,kitab,version);
CREATE INDEX MetaAuthorIndex on meta (author);
CREATE INDEX MetaYearIndex on meta (year);
CREATE INDEX MetaOriginalAuthorIndex on meta (originalAuthor);
CREATE INDEX MetaOriginalYearIndex on meta (originalYear);
CREATE INDEX MetaClassificationIndex on meta (classification);
CREATE INDEX MetaFlagsIndex on meta (flags);

CREATE TABLE "directories" (
	"abspath" TEXT,
	"mtime" FLOAT
);

"""
SQL_MCACHE_GET = """SELECT rowid,* FROM meta"""
SQL_MCACHE_GET_BY_KITAB = """SELECT rowid,* FROM meta ORDER BY kitab"""
SQL_MCACHE_GET_UNINDEXED = """SELECT rowid,* FROM meta WHERE flags=0"""
SQL_MCACHE_GET_DIRTY_INDEX = """SELECT rowid,* FROM meta WHERE flags=1"""
SQL_MCACHE_GET_INDEXED = """SELECT rowid,* FROM meta WHERE flags=2"""
SQL_MCACHE_SET_INDEXED = """UPDATE OR IGNORE meta SET flags=? WHERE uri=?"""
SQL_MCACHE_SET_ALL_INDEXED = """UPDATE OR IGNORE meta SET flags=? WHERE flags>0"""

SQL_DATA_MODEL = """\
%s

CREATE TABLE "nodes" (
	"idNum" INTEGER PRIMARY KEY NOT NULL,
	"content" TEXT,
	"parent" INTEGER,
	"globalOrder" INTEGER,
	"depth" INTEGER NOT NULL
);

CREATE TABLE "tags" (
	"idNum" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	"name" VARCHAR NOT NULL,
	"flags" INTEGER NOT NULL,
	"comment" VARCHAR,
	"parent" INTEGER,
	"relation" INTEGER
);

CREATE TABLE "nodesTags" (
	"tagIdNum" INTEGER NOT NULL,
	"nodeIdNum" INTEGER NOT NULL,
	"param" VARCHAR,
	PRIMARY KEY ("tagIdNum", "nodeIdNum")
);

CREATE INDEX NodesParentIndex on nodes (parent);
CREATE INDEX NodesNodesGlobalOrderIndex on nodes (globalOrder);
CREATE INDEX NodesDepthIndex on nodes (depth);

CREATE INDEX NodesTagTagIdNumIndex on nodesTags(tagIdNum);
CREATE INDEX NodesTagNodeIdNumIndex on nodesTags(nodeIdNum);
CREATE INDEX NodesTagParamIndex on nodesTags(param);
CREATE INDEX TagsName on tags (name);

""" % MCACHE_BASE

#################################################
# arguments to make the built-in tags
STD_TAGS_ARGS = ( \
  # (name, comment, flags, parent, relation)
  ("header", "an anchor that marks header in TOC.", TAG_FLAGS_FLOW_BLOCK | TAG_FLAGS_HEADER),
  ("request.fix.head", "a tag that marks an error in content.", 0),
  ("request.fix.footnote", "a tag that marks an error in content footnotes.", 0),
  ("textbody", "a tag that marks a typical text.",0),
  ("quran.tafseer.ref", 'a reference to some Ayat in tafseer (in the form of "Sura-Aya-number").', 0),
  ("embed.section.ref", 'a reference to some section in another kitab to embed (in the form of "kitabName-version/section").', 0),
  ("embed.original.section", 'a reference to some section in the original kitab to embed. (used in commentary books)', 0),
  # the following index-tags marks the header
  ("hadith.authenticity", "marks the authenticity of the hadith, param values are Sahih, Hasan, weak, fabricated", TAG_FLAGS_IX_TAG),
  # new index field for rawi
  ("hadith.ruwah.rawi", "marks a rawi", TAG_FLAGS_IX_FIELD),
  # the following index-tags marks the rawi field
  ("hadith.ruwah.authenticity", "marks the authenticity of the rawi, param values are thiqah, ...,kathoob", TAG_FLAGS_IX_TAG),
  ("hadith.ruwah.tabaqa", "marks the tabaqa of the rawi, param values are sahabi,tabii,...", TAG_FLAGS_IX_TAG)
)
STD_TAGS_HASH = dict([(i[0],i) for i in STD_TAGS_ARGS])
# ENUMs
WITH_NONE = 0
WITH_CONTENT = 1
WITH_TAGS = 2
WITH_CONTENT_AND_TAGS = 3
#################################################
# SQL statements for manipulating the dataModel
SQL_GET_ALL_TAGS = """SELECT name,flags,comment,parent,relation FROM tags"""
SQL_GET_NODE_CONTENT = """SELECT content from nodes WHERE idNum=? LIMIT 1"""

SQL_GET_NODE_TAGS = """SELECT tags.name,nodesTags.param FROM nodesTags LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodesTags.nodeIdNum=?"""

# FIXME: all sql that uses SQL_NODE_ARGS should be revised to check the shift after adding globalOrder

SQL_NODE_ARGS = "nodes.idNum, nodes.parent, nodes.depth, nodes.globalOrder"
SQL_NODE_COLS = (SQL_NODE_ARGS, SQL_NODE_ARGS+", nodes.content", 
  SQL_NODE_ARGS+", tags.name, nodesTags.param, tags.flags",
  SQL_NODE_ARGS+", nodes.content"+", tags.name, nodesTags.param, tags.flags")

SQL_GET_CHILD_NODES = ( \
  """SELECT %s FROM nodes WHERE parent=? ORDER BY globalOrder""" % SQL_NODE_ARGS,
  """SELECT %s FROM nodes WHERE parent=? ORDER BY globalOrder""" % SQL_NODE_COLS[WITH_CONTENT],
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.parent=? ORDER BY nodes.globalOrder""" % SQL_NODE_COLS[WITH_TAGS],
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.parent=? ORDER BY nodes.globalOrder""" % SQL_NODE_COLS[WITH_CONTENT_AND_TAGS]
)

SQL_TAG = """INSERT OR REPLACE INTO nodesTags (tagIdNum,nodeIdNum,param) SELECT tags.IdNum,?,? FROM tags WHERE tags.name = ? LIMIT 1"""
SQL_CLEAR_TAGS_ON_NODE = """DELETE FROM nodesTags WHERE tags.name = ?"""

SQL_GET_NODE_BY_IDNUM = ( \
  """SELECT %s FROM nodes WHERE idNum=? ORDER BY globalOrder""" % SQL_NODE_ARGS,
  """SELECT %s FROM nodes WHERE idNum=? ORDER BY globalOrder""" % SQL_NODE_COLS[1],
)

# node slices
SQL_GET_NODES_SLICE = ( \
  """SELECT %s FROM nodes WHERE globalOrder>? AND globalOrder<? ORDER BY globalOrder""" % SQL_NODE_ARGS,
  """SELECT %s FROM nodes WHERE globalOrder>? AND globalOrder<? ORDER BY globalOrder""" % SQL_NODE_COLS[WITH_CONTENT],
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.globalOrder>? AND nodes.globalOrder<? ORDER BY nodes.globalOrder""" % SQL_NODE_COLS[WITH_TAGS],
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.globalOrder>? AND nodes.globalOrder<? ORDER BY nodes.globalOrder""" % SQL_NODE_COLS[WITH_CONTENT_AND_TAGS]
)
SQL_GET_UNBOUNDED_NODES_SLICE = (
  """SELECT %s FROM nodes WHERE globalOrder>? ORDER BY globalOrder""" % SQL_NODE_ARGS,
  """SELECT %s FROM nodes WHERE globalOrder>? ORDER BY globalOrder""" % SQL_NODE_COLS[WITH_CONTENT],
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.globalOrder>? ORDER BY nodes.globalOrder""" % SQL_NODE_COLS[WITH_TAGS],
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.globalOrder>? ORDER BY nodes.globalOrder""" % SQL_NODE_COLS[WITH_CONTENT_AND_TAGS]
)

# tagged children node
SQL_GET_TAGGED_CHILD_NODES = ( \
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.parent=? AND tags.name=? ORDER BY nodes.globalOrder""" % SQL_NODE_ARGS,
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.parent=? AND tags.name=? ORDER BY nodes.globalOrder""" % SQL_NODE_COLS[1]
)
# tagged node slices
SQL_GET_TAGGED_NODES_SLICE = ( \
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE tags.name=? AND nodes.globalOrder>? AND nodes.globalOrder<? ORDER BY nodes.globalOrder""" % SQL_NODE_ARGS,
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE tags.name=? AND nodes.globalOrder>? AND nodes.globalOrder<? ORDER BY nodes.globalOrder""" % SQL_NODE_COLS[1]
)
SQL_GET_UNBOUNDED_TAGGED_NODES_SLICE = ( \
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE tags.name=? AND nodes.globalOrder>? ORDER BY nodes.globalOrder""" % SQL_NODE_ARGS,
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE tags.name=? AND nodes.globalOrder>? ORDER BY nodes.globalOrder""" % SQL_NODE_COLS[1])

# get tagged node slices by param value
SQL_GET_NODES_BY_TAG_VALUE = ( \
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE tags.name=? AND nodesTags.param=? ORDER BY nodes.globalOrder""" % SQL_NODE_ARGS,
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE tags.name=? AND nodesTags.param=? ORDER BY nodes.globalOrder""" % SQL_NODE_COLS[1])

# get prev/next tagged node
SQL_GET_PREV_TAGGED_NODE = ( \
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.globalOrder<? and tags.name=? ORDER BY nodes.globalOrder DESC LIMIT 1""" % SQL_NODE_ARGS,
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.globalOrder<? and tags.name=? ORDER BY nodes.globalOrder DESC LIMIT 1""" % SQL_NODE_COLS[1])
SQL_GET_NEXT_TAGGED_NODE = ( \
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.globalOrder>? and tags.name=? ORDER BY nodes.globalOrder LIMIT 1""" % SQL_NODE_ARGS,
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.globalOrder>? and tags.name=? ORDER BY nodes.globalOrder LIMIT 1""" % SQL_NODE_COLS[1])

# get tagged child nodes
SQL_GET_TAGGED_CHILD_NODES = ( \
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.parent=? and tags.name=? ORDER BY nodes.globalOrder""" % SQL_NODE_ARGS,
  """SELECT %s FROM nodes LEFT OUTER JOIN nodesTags ON nodes.idNum = nodesTags.nodeIdNum LEFT OUTER JOIN tags on nodesTags.tagIdNum=tags.idNum WHERE nodes.parent=? and tags.name=? ORDER BY nodes.globalOrder""" % SQL_NODE_COLS[1])


SQL_GET_GLOBAL_ORDER = """SELECT globalOrder,depth FROM nodes WHERE idNum=? LIMIT 1"""
SQL_GET_DESC_UPPER_BOUND = """SELECT globalOrder FROM nodes WHERE globalOrder>? AND depth<=? ORDER BY globalOrder LIMIT 1"""
SQL_GET_SIBLING_GLOBAL_ORDER = """SELECT globalOrder FROM nodes WHERE parent=? and globalOrder>? ORDER BY globalOrder LIMIT 1"""
SQL_GET_LAST_GLOBAL_ORDER = """SELECT globalOrder FROM nodes ORDER BY globalOrder DESC LIMIT 1"""
SQL_DROP_DESC_NODES = ["""DELETE FROM nodes WHERE globalOrder>? AND globalOrder<?""",
  """DELETE FROM nodes WHERE globalOrder>=? AND globalOrder<?"""]
SQL_DROP_TAIL_NODES = ["""DELETE FROM nodes WHERE globalOrder>?""",
  """DELETE FROM nodes WHERE globalOrder>=?"""]
SQL_APPEND_NODE = ["""INSERT INTO nodes (content,parent,globalOrder,depth) VALUES (?,?,?,?)""",
"""INSERT INTO tmp_nodes (content,parent,globalOrder,depth) VALUES (?,?,?,?)"""]
# SQL tags commands
SQL_ADD_TAG = "INSERT OR REPLACE INTO tags (name, comment, flags, parent,relation) VALUES (?,?,?,-1,-1)"

# modified:
#  SQL_GET_NODE_BY_IDNUM
#  SQL_GET_CHILD_NODES
#  SQL_GET_NODES_SLICE
#  SQL_GET_UNBOUNDED_NODES_SLICE
#  SQL_GET_TAGGED_CHILD_NODES
#  SQL_GET_TAGGED_NODES_SLICE
#  SQL_GET_UNBOUNDED_TAGGED_NODES_SLICE
# removed:
#  SQL_GET_CHILD_NODES_AND_TAGS
#  SQL_GET_NODES_SLICE_AND_TAGS
#  SQL_GET_UNBOUNDED_NODES_SLICE_AND_TAGS
# TODO:
#  make SQL_GET_NODE_BY_IDNUM capable of pre-loading tags (is this really needed??)