Class: Refi::SpreadsheetDb

Inherits:
Object
  • Object
show all
Defined in:
lib/spreadsheet_db_refi.rb

Overview

stores spreadsheet data in a sqlite database

Constant Summary collapse

@@std_create_tbls_strs = 
{
	metadata: %q( 
		create table metadata(
			nam text, 
			val text, 

			primary key(nam)
		);
	),
	sheet: %q(
		create table sheet(
			id integer, 
			nam text, 
			pos integer,

			primary key(id), 
			unique(nam),
			unique(pos)
		);
	),
	typ: %q(
		create table typ(
			id integer,
			nam text,
			mcv text,

			primary key(id),
			unique(nam)
		);
	),
	cell: %q(
		create table cell( 
			sheet_id integer, 
			row integer, 
			col integer, 
			val text, 
			typ_id integer,

			primary key(sheet_id, row, col), 
			foreign key(sheet_id) references sheet(id),
			foreign key(typ_id) references typ(id)
		);
	)
}.inject({}){|memo,(k,v)| memo[k] = v.strip_a_lot ; memo}
@@std_tbl_create_order = 
[:metadata, :sheet, :typ, :cell]
@@std_stmts_strs = 
{
	metadata_set_nam_val: "insert or replace into metadata(nam,val) values(?,?);",
	metadata_get_all: "select * from metadata;",
	metadata_get_val_by_nam: "select val from metadata where nam=?;",
	metadata_del_val_by_nam: "delete from metadata where nam=?;",
	metadata_wipe: "delete from metadata;",
	sheet_set_id_nam_pos: "insert or replace into sheet(id,nam,pos) values(?,?,?);",
	sheet_get_id_by_nam: "select id from sheet where nam=?;",
	sheet_get_nam_by_id: "select nam from sheet where id=?",
	sheet_get_all: "select * from sheet;",
	typ_set_id_nam_mcv: "insert or replace into typ(id,nam,mcv) values(?,?,?);",
	typ_get_all_by_nam: "select * from typ where nam=?",
	cell_set_sheet_row_col_val_typ: "insert or replace into cell(sheet_id,row,col,val,typ_id) values(?,?,?,?,?);",
	cell_get: "select val,typ_id from cell where sheet_id=? and row=? and col=?;",
	get_min_col: "select min(col) from cell where sheet_id=?;",
	get_max_col: "select max(col) from cell where sheet_id=?;",
	get_min_row: "select min(row) from cell where sheet_id=?;",
	get_max_row: "select max(row) from cell where sheet_id=?;",
	get_row: "select col,val,typ_id from cell where sheet_id=? and row=?;",
	get_col: "select row,val,typ_id from cell where sheet_id=? and col=?;",
	sqlite_get_tbl_nam_by_nam: "select name from sqlite_master where name=?;",
}

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(db_pn = nil) ⇒ SpreadsheetDb

Returns a new instance of SpreadsheetDb.



77
78
79
80
81
82
 
# File 'lib/spreadsheet_db_refi.rb', line 77

def initialize(db_pn=nil)
	@create_tbls_strs = @@std_create_tbls_strs
	@stmts_strs = @@std_stmt_strs
	@tbl_create_order = @@std_tbl_create_order
	init2(db_pn)
end

Instance Attribute Details

#db ⇒ Object

Returns the value of attribute db.



76
77
78
 
# File 'lib/spreadsheet_db_refi.rb', line 76

def db
  @db
end

Instance Method Details

#assert_db_well_formed(p = {}) ⇒ Object

requires good tables(with minimum records) and prepared statements



133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
 
# File 'lib/spreadsheet_db_refi.rb', line 133

def assert_db_well_formed(p={}) # requires good tables(with minimum records) and prepared statements
	exist_prior = p[:exist_prior] || false
	on_err_exit = p[:on_err_exit] || true
	good = true
	@tbl_create_order.each{|sym|
		nam = sym.to_s
		# @prep_stmts is not ready yet
		found_table = @db.execute(@stmts_strs[:sqlite_get_tbl_nam_by_nam],nam)[0] == [nam]
		#found_table = @prep_stmts[:sqlite_get_tbl_name_by_name].execute(sym.to_s).size == 1
		if(!found_table)
			good = false
			# missing table
			$Log.tlog({id: 'err1122',table:sym.to_s})
		end
	}
	if(!good)
		if(exist_prior)
			dperr(120,"existing db not well formed (pn:#{db_pn})",true,on_err_exit)
		else
			dperr(121,"new db not well formed (pn:#{db_pn})",true,on_err_exit)
		end
	end
	return good
end

#create_sheet(sheet_name, pos_ix) ⇒ Object 



174
175
176
177
178
 
# File 'lib/spreadsheet_db_refi.rb', line 174

def create_sheet(sheet_name,pos_ix)
	@prep_stmts[:sheet_set_id_nam_pos].execute(nil,sheet_name,pos_ix)
	new_sheet_id = @prep_stmts[:sheet_get_id_by_nam].execute!(sheet_name).flatten[0]
	return new_sheet_id
end

#create_tables ⇒ Object 



125
126
127
128
129
 
# File 'lib/spreadsheet_db_refi.rb', line 125

def create_tables 
	@tbl_create_order.each{|tbl_nam|
		db.execute(@create_tbls_strs[tbl_nam])
	}
end

#enhance_db_funcs ⇒ Object 



157
158
159
160
161
 
# File 'lib/spreadsheet_db_refi.rb', line 157

def enhance_db_funcs
	$sqlite_enhance_funcs.each{|func_nam,func|
		@db.create_function(func_nam,func[:func_body].arity + 1,func[:return_type], &func[:func_body])
	}
end

#get_metadata ⇒ Object 



167
168
169
170
171
172
173
 
# File 'lib/spreadsheet_db_refi.rb', line 167

def get_metadata
	if @db
		return Hash[ @prep_stmts[:metadata_get_all].execute!() ]
	else
		return nil
	end
end

#get_sheet(sheet_xid) ⇒ Object 



253
254
255
256
257
 
# File 'lib/spreadsheet_db_refi.rb', line 253

def get_sheet(sheet_xid)
	sheet_id = get_sheet_id_from_xid(sheet_xid)
	new_sheet = SsDbSheet.new(self,sheet_id)
	return new_sheet
end

#get_sheet_id_from_xid(sheet_xid) ⇒ Object 



179
180
181
182
183
184
185
 
# File 'lib/spreadsheet_db_refi.rb', line 179

def get_sheet_id_from_xid(sheet_xid)
	if(sheet_xid.is_a?(String))
		return @prep_stmts[:sheet_get_id_by_nam].execute!(sheet_xid).flatten[0]
	elsif(sheet_xid.is_a?(Fixnum))
		return sheet_xid
	end
end

#get_sheet_name_by_id(sheet_id) ⇒ Object 



196
197
198
 
# File 'lib/spreadsheet_db_refi.rb', line 196

def get_sheet_name_by_id(sheet_id)
	return prep_ex(:sheet_get_nam_by_id,[sheet_id])[0]
end

#get_sheet_name_from_xid(sheet_xid) ⇒ Object 



186
187
188
189
190
191
192
 
# File 'lib/spreadsheet_db_refi.rb', line 186

def get_sheet_name_from_xid(sheet_xid)
	if(sheet_xid.is_a?(String))
		return sheet_xid
	elsif(sheet_xid.is_a?(Fixnum))
		return @prep_stmts[:sheet_get_nam_by_id].execute!(sheet_xid).flatten[0]
	end
end

#import_sheet(sheet_id, ss) ⇒ Object 



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
 
# File 'lib/spreadsheet_db_refi.rb', line 202

def import_sheet(sheet_id,ss)
	$Log.tlog({id: 'info1045'})
	$Log.indent
	#dputi("import all rows of sheet into cache")
	#metadata = get_metadata
	# CUR	
	row_a = ss.first_row
	row_e = ss.last_row
	col_a = ss.first_column
	col_e = ss.last_column
	@db.transaction{|dbi|
		row_a.upto(row_e){|row_i|
			col_a.upto(col_e){|col_i|
				#pp ss
				#puts row_i
				#puts col_i
				cell_value = ss.cell(row_i,col_i)
				value = ''
				# TODO use hash?
				if(cell_value == nil)
					# TODO
					#dparn(117,"empty cell (#{@ss_pn}:#{sheet_id}:#{row_i}:#{col_i}) using default value:'#{@empty_cell_default_value}'")
					if(@empty_cell_default_value==nil)
						dparn(777,"no empty cell default value using builtin string of the form 'XXX_empty_cell(...)'")
						value = "XXX_empty_cell(#{@ss_pn}:#{sheet_id}:#{row_i}:#{col_i})"
					else
						value = @empty_cell_default_value
					end
				elsif(cell_value.is_a?(String))
					value = cell_value
				elsif(cell_value.is_a?(Date))
					value = cell_value.iso8601
				elsif(cell_value.is_a?(Float))
					value = cell_value
				elsif(cell_value.is_a?(NilClass))
					dperr(113,"NilClass in cell")
				else
					dpev cell_value.class
					dperr(1375, "#{__FILE__} #{__LINE__}")
				end
				type = 1
				set_cell(sheet_id,row_i,col_i,value,type)
			}
			print 'c'
		}
		#dput
		$Log.undent
	}
	#dpun
	$Log.undent
end

#init2(db_pn = nil) ⇒ Object 



83
84
85
86
87
88
89
90
 
# File 'lib/spreadsheet_db_refi.rb', line 83

def init2(db_pn=nil)
	@is_open = false
	reset
	if(db_pn)
		@db_pn = db_pn
		open
	end
end

#insert_minimum_records ⇒ Object

requires prepared statements



130
131
132
 
# File 'lib/spreadsheet_db_refi.rb', line 130

def insert_minimum_records # requires prepared statements
	@prep_stmts[:typ_set_id_nam_mcv].execute([nil,'std','{}'])
end

#open ⇒ Object 



101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
# File 'lib/spreadsheet_db_refi.rb', line 101

def open
	exist_prior = File.exist?(@db_pn)
	if(exist_prior)
		if(File.zero?(@db_pn))
			File.delete(@db_pn)
			exist_prior = false
		end
	end
	@db = SQLite3::Database.new(@db_pn)
	enhance_db_funcs

	if(exist_prior)
		assert_db_well_formed({exist_prior: exist_prior,on_err_exit: true}) 
		prepare_statements
	else
		@db.transaction{|foo|
			create_tables
			prepare_statements
			insert_minimum_records 
		}
		assert_db_well_formed({exist_prior: exist_prior,on_err_exit: true}) 
	end
	@is_open = true
end

#prep_ex(sym, p_ary = []) ⇒ Object 



193
194
195
 
# File 'lib/spreadsheet_db_refi.rb', line 193

def prep_ex(sym,p_ary=[])
	return @prep_stmts[sym].execute!(p_ary)
end

#prepare_statements ⇒ Object 



162
163
164
165
166
 
# File 'lib/spreadsheet_db_refi.rb', line 162

def prepare_statements
	@stmts_strs.each{|sym,stmt_str|
		@prep_stmts[sym] = @db.prepare(stmt_str)
	}
end

#reset ⇒ Object 



91
92
93
94
95
96
97
98
99
100
 
# File 'lib/spreadsheet_db_refi.rb', line 91

def reset
	if @is_open
		@prep_stmts.each{|sym,prep_stmt| prep_stmt.close }
		@db.close
	end
	@prep_stmts = {}
	@db = nil
	@is_open = false
	@empty_cell_default_value = ''
end

#set_cell(sheet_id, row, col, value, type) ⇒ Object 



199
200
201
 
# File 'lib/spreadsheet_db_refi.rb', line 199

def set_cell(sheet_id,row,col,value,type)
	prep_ex(:cell_set_sheet_row_col_val_typ,[sheet_id,row,col,value,type])
end