Module: QDA::Backend::MySQL

Defined in:
lib/weft/backend/mysql.rb,
lib/weft/backend/marshal.rb

Instance Method Summary collapse

Instance Method Details

#category_search_by_words(*words) ⇒ Object 



101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
 
# File 'lib/weft/backend/mysql.rb', line 101

def category_search_by_words(*words)

		words = words.collect { | word | @dbh.quote(word) }

		cats = []

		@dbh.select_all("SELECT category.catid, category.catname, " +

			"SUM(category_ridx.score) AS score " +

			"FROM category_ridx, category " +

			"WHERE category_ridx.catid =  category.catid " + 

			"AND category_ridx.word IN ( ? ) " +

			"GROUP BY category_ridx.catid", 

			words.join(',') ) do | r |



 cat = Category.new(r['catname'])

 cat.dbid = r['catid']	  

 cats.push(cat)

		end

		cats

end

#connect(args) ⇒ Object

load up the database connection



8
9
10
 
# File 'lib/weft/backend/mysql.rb', line 8

def connect(dbh)

		@dbh = dbh

end

#doc_search_by_category(category) ⇒ Object

decide what level to code at? this returns a weighted search result set



51
52
53
54
55
56
57
58
 
# File 'lib/weft/backend/mysql.rb', line 51

def doc_search_by_category(category)

		# SELECT SUM(SQRT(idx_cat.score * idx_doc.score)), idx_doc.catid

		# 	FROM reverse_index AS idx_doc, reverse_index AS idx_cat

		# 	WHERE idx_cat.catid = 1

		# 	AND idx_doc.catid != idx_cat.catid

		# 	AND idx_doc.word = idx_cat.word

		# 	GROUP BY idx_doc.catid;

end

#get_all_categories ⇒ Object

fetches all the categories in a tree structure, starting from the right



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
 
# File 'lib/weft/backend/mysql.rb', line 120

def get_all_categories()

		parents = [ { 'cat' => Category.new('root'), 'r' => 10 } ]

		l = 0 # avoid reinit

		r = 0 # avoid reinit

		cat = nil

		

		query = "SELECT * FROM categories ORDER BY l"

		@dbh.select_all(query) do | row |

 l = row['l'].to_i

 r = row['r'].to_i

 cat = Category.new(row['catname'])

 cat.dbid = row['catid'].to_i

 # if a leaf

 if l + 1 == r

parents[-1]['cat'].add_child(cat)

 else

parents.push( { 'cat' => cat, 'r' => r } )

 end

 

 until parents.length == 1 || r <= parents[-1]['r']

parents.pop.fetch('cat').append_to(parents[-1]['cat'])

 end

		end

		

		# clean up outstanding items to be added

		while parents.length > 1

 parents.pop.fetch('cat').append_to(parents[-1]['cat'])

		end

		

		return parents[0]['cat']

end

#get_all_docs ⇒ Object 



13
14
15
16
17
18
 
# File 'lib/weft/backend/mysql.rb', line 13

def get_all_docs()

		@dbh.select_all("SELECT * FROM docs") do | row | 

 doc = Document.new(row['doctitle'])

 doc.dbid = row['docid']

		end

end

#get_category(catid) ⇒ Object 



81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/weft/backend/mysql.rb', line 81

def get_category(catid)

		r = @dbh.select_one("SELECT * FROM categories WHERE catid = ?", 

				catid)

		return nil unless r

		# raise "No category found matching id '#{cat}'" unless r

		category = Category.new(r['catname'])





		# not found

		category.dbid = catid



		@dbh.select_all("SELECT codes.docid, codes.start, codes.offset

                         FROM codes

                         WHERE catid = ? ", category.dbid) do | row |

 category.code(row['docid'], row['start'].to_i, row['offset'].to_i)



		end

		category

end

#get_doc(ident) ⇒ Object

fetch the document identified by the string ident



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
 
# File 'lib/weft/backend/mysql.rb', line 20

def get_doc(ident)

		doc = nil

		if ident =~ /^\d+/

 r = @dbh.select_one("SELECT doctitle FROM docs WHERE docid = ?", 

				  ident)

 unless r

raise "No document found matching id '#{ident}'"

 end

 doc = Document.new(r[0])

 doc.dbid = ident

		else

 r = @dbh.select_one("SELECT docid FROM docs WHERE doctitle = ?", ident)

 unless r

raise "No document found matching title '#{ident}'"

 end

 doc = Document.new(ident)

 doc.dbid = r[0]

		end	  



		text = ''

		@dbh.select_all("SELECT * FROM chunks 

      		 WHERE docid = ?

	     ORDER BY docseq", doc.dbid) do | row |

 doc.append(row['chunk'], row['type'])



		end

		doc

end

#save ⇒ Object

WARN - below here is older code imported from a different project NOT YET TESTED FOR COMPATIBILITY



192
193
194
195
196
197
198
199
200
201
 
# File 'lib/weft/backend/mysql.rb', line 192

def save()

		sql_install()

		@docs.values.each do | doc |

 sql_store_document(doc)

		end

		i = 0

		counter = proc { i += 1 }

		

		sql_store_node(@root_node, counter)

end

#save_category(cat) ⇒ Object 



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
 
# File 'lib/weft/backend/mysql.rb', line 152

def save_category(cat)

		if cat.dbid

 @dbh.do("DELETE FROM codes WHERE catid = ?", cat.dbid)

 @dbh.do("UPDATE categories SET catname = ? WHERE catid = ?",

	  cat.name, cat.dbid)

		else

 l = nil

 if cat.parent

row = @dbh.select_one("SELECT r FROM categories

                          WHERE catid = ? ", cat.parent.dbid)

l = row['r'].to_i

 else

row = @dbh.select_one("SELECT MAX(r) r FROM categories")

l = row['r'].to_i + 1

 end

 puts "got l = #{l}"



 @dbh.do("INSERT INTO categories VALUES(NULL, ?, '', ?, ?)", 

	  cat.name, l, l + 1)

 r = @dbh.select_one("SELECT LAST_INSERT_ID()")

 cat.dbid = r[0]

 @dbh.do("UPDATE categories SET l = l + 2

            WHERE l > ? ", l)

 @dbh.do("UPDATE categories SET r = r + 2

            WHERE r >= ? 

            AND catid != ? ", l, cat.dbid) 

		end



		cat.vectors.each do | docid, vecs |

 vecs.each do | vec |

@dbh.do("INSERT INTO codes VALUES(?, ?, ?, ?)", 

		cat.dbid, docid, vec.start, vec.length )

 end

		end

		

		self

end

#save_document(doc) ⇒ Object 



60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'lib/weft/backend/mysql.rb', line 60

def save_document(doc)

		if doc.dbid

 @dbh.do("UPDATE docs SET doctitle = ? WHERE docid = ?", 

	  doc.title, doc.dbid)

		else

 @dbh.do("INSERT INTO docs VALUES(NULL, ?)", @title)

 r = @dbh.select_one("SELECT LAST_INSERT_ID()")

 doc.dbid = r[0]

 doc.fragments.each_with_index do | frag, i |

@dbh.do("INSERT INTO chunks VALUES(?, 0, ?, ?)", 

		doc.dbid, frag.text, i)

doc.dbid = r[0]

 end

		end

		self

end

#sql_install ⇒ Object 



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
 
# File 'lib/weft/backend/mysql.rb', line 203

def sql_install()

		@dbh.do("DROP TABLE IF EXISTS docs")

		

		# docid is just nudist's internal document keying system

		@dbh.do("CREATE TABLE docs (

docid varchar(8) primary key,

doctitle varchar(255),

docmemo text,

external CHAR(1) )")

		

		@dbh.do("DROP TABLE IF EXISTS fragments")

		@dbh.do("CREATE TABLE fragments (

docid VARCHAR(8) REFERENCES doc(docid),

seq INT,

fragment text)")



		@dbh.do("DROP TABLE IF EXISTS nodes")

		@dbh.do("CREATE TABLE nodes (

nodeid VARCHAR(32) PRIMARY KEY,

nodepath VARCHAR(255),

nodeuid VARCHAR(255),

nodename VARCHAR(32),

nodememo TEXT,

l INT,

r INT )")

		

		@dbh.do("DROP TABLE IF EXISTS vectors")

		@dbh.do("CREATE TABLE vectors (

nodeid VARCHAR(32) REFERENCES nodes(nodeid),

docid VARCHAR(8) REFERENCES doc(docid),

start INT REFERENCES DOCFRAGMENT(seq),

length INT)")

end

#sql_store_document(doc) ⇒ Object 



256
257
258
259
260
261
262
263
264
 
# File 'lib/weft/backend/mysql.rb', line 256

def sql_store_document(doc)

		@dbh.do("INSERT INTO docs VALUES (?, ?, ?, ?)",

	doc.doc_id, doc.title, doc.memo, doc.external?)

		

		doc.fragments.each_with_index do | frag, i |

 @dbh.do("INSERT INTO fragments VALUES(?, ?, ?)",

	  doc.doc_id, i, frag)

		end

end

#sql_store_node(node, counter) ⇒ Object 



237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
 
# File 'lib/weft/backend/mysql.rb', line 237

def sql_store_node(node, counter)

		left = counter.call()

		node.children.each do | child |

 sql_store_node(child, counter)

		end

		right = counter.call()

		

		@dbh.do("INSERT INTO nodes VALUES(?, ?, ?, ?, ?, ?, ?)",

	node.uniq_id, node.path, node.child_id, node.title,

	node.memo, left, right)

		node.codes.each do | doc, vectors |

 vectors.each do | vector |

@dbh.do("INSERT INTO vectors VALUES (?, ?, ?, ?)",

		node.uniq_id, vector.doc.doc_id, 

		vector.start, vector.length)

 end

		end

end