Class: Sack::Database::Data

Inherits:
Object
  • Object
show all
Defined in:
lib/sack/database/data.rb

Overview

Data Class: Internal Class presented as Data Access Layer when yielding in Database.open.

Instance Method Summary collapse

Constructor Details

#initialize(db, schema) ⇒ Data

Construct: Builds a Data object around a db instance, set to operate on schema.

Parameters:

  • db (SQLite3::Database) —

    Database instance obtained by opening an SQLite3 file

  • schema (Hash) —

    Schema definition - see README



27
28
29
30
 
# File 'lib/sack/database/data.rb', line 27

def initialize db, schema
	@db = db
	@schema = schema
end

Instance Method Details

#alter_table(name, fields) ⇒ Object

Alter Table: Adds missing fields to an already-existing table (ONLY adds, does not remove or modify existing fields).

Parameters:

  • name (Symbol) —

    Table name

  • fields (Hash) —

    A hash mapping of field names to type definition arrays (from FTYPES)



45
46
47
 
# File 'lib/sack/database/data.rb', line 45

def alter_table name, fields
	fields.each { |fname, ftype| @db.execute "alter table #{Sanitizer.table @schema, name} add #{Sanitizer.field_name fname} #{ftype.respond_to?(:each) ? ftype.collect { |e| FTYPES[Sanitizer.ftype e] }.join(' ') : FTYPES[Sanitizer.ftype ftype]};" rescue nil }
end

#count(table) ⇒ Fixnum

Count: Counts the number of rows for a given table.

Parameters:

  • table (Symbol) —

    Table name

Returns:

  • (Fixnum) —

    The number of rows present in the given table



53
54
55
 
# File 'lib/sack/database/data.rb', line 53

def count table
	@db.execute("select count(*) from #{Sanitizer.table @schema, table};")[0][0]
end

#create(table, fields) ⇒ Object

Create: Inserts fields as a row into a given table.

Parameters:

  • table (Symbol) —

    Table name

  • fields (Hash) —

    Fields to be inserted



94
95
96
 
# File 'lib/sack/database/data.rb', line 94

def create table, fields
	@db.execute Statement.prep("insert into #{Sanitizer.table @schema, table} (#{Generator.fields @schema, table, fields}) values (#{Generator.marks fields});", Generator.values(fields.values))
end

#create_table(name, fields) ⇒ Object

Create Table: Creates a table name defined by the fields hash.

Parameters:

  • name (Symbol) —

    Table name

  • fields (Hash) —

    A hash mapping of field names to type definition arrays (from FTYPES)



36
37
38
39
 
# File 'lib/sack/database/data.rb', line 36

def create_table name, fields
	fq = fields.collect { |fname, ftype| "#{Sanitizer.field_name fname} #{ftype.respond_to?(:each) ? ftype.collect { |e| FTYPES[Sanitizer.ftype e] }.join(' ') : FTYPES[Sanitizer.ftype ftype]}" }.join ', '
	@db.execute "create table #{Sanitizer.table @schema, name} (#{fq});"
end

#delete(table, id) ⇒ Object

Destroy: Removes rows identified by id from a given table.

Parameters:

  • table (Symbol) —

    Table name

  • id (Object) —

    ID of rows to be removed



123
124
125
 
# File 'lib/sack/database/data.rb', line 123

def delete table, id
	@db.execute Statement.prep("delete from #{Sanitizer.table @schema, table} where id = ?;", [id])
end

#exec(q, params = []) ⇒ Object

Execute statement: Generic method to execute any SQL statement.

Parameters:

  • q (String) —

    Statement to be executed

  • params (Array) (defaults to: []) —

    Statement parameters

Returns:

  • (Object) —

    Whatever the statement returned



132
133
134
 
# File 'lib/sack/database/data.rb', line 132

def exec q, params = []
	@db.execute Statement.prep(q, params)
end

#fetch(table, id) ⇒ Array

Fetch: Fetches rows from a given table by id.

Parameters:

  • table (Symbol) —

    Table name

  • id (Object) —

    ID on which to filter

Returns:

  • (Array) —

    An array of Hashes, each representing one row



68
69
70
 
# File 'lib/sack/database/data.rb', line 68

def fetch table, id
	hash_res(table.to_sym, @db.execute(Statement.prep("select * from #{Sanitizer.table @schema, table} where id = ?;", [id])))
end

#fetch_all(table) ⇒ Array

Fetch All: Fetches all rows from a given table.

Parameters:

  • table (Symbol) —

    Table name

Returns:

  • (Array) —

    An array of Hashes, each representing one row



86
87
88
 
# File 'lib/sack/database/data.rb', line 86

def fetch_all table
	hash_res(table.to_sym, @db.execute("select * from #{Sanitizer.table @schema, table};"))
end

#fetch_by(table, field, val) ⇒ Array

Fetch By Field: Fetches rows from a given table where field matches val.

Parameters:

  • table (Symbol) —

    Table name

  • field (Symbol) —

    Field name

  • val (Object) —

    Field value

Returns:

  • (Array) —

    An array of Hashes, each representing one row



78
79
80
 
# File 'lib/sack/database/data.rb', line 78

def fetch_by table, field, val
	hash_res(table.to_sym, @db.execute(Statement.prep("select * from #{Sanitizer.table @schema, table} where #{Sanitizer.field @schema, table, field} = ?;", [val])))
end

#find(table, id) ⇒ Object

Find: Fetches the first matching row from a given table by id.



59
60
61
 
# File 'lib/sack/database/data.rb', line 59

def find table, id
	fetch(table, id).try :first
end

#hash_res(table, x) ⇒ Array

Pull Results into Hash: Converts rows returned by SQLite3 into Hashes matching the provided schema.

Parameters:

  • table (Symbol) —

    Table name

  • x (Array) —

    Results returned by SQLite3

Returns:

  • (Array) —

    An array of Hashes, each representing a single row



141
142
143
144
145
146
 
# File 'lib/sack/database/data.rb', line 141

def hash_res table, x
	x
		.collect { |r| @schema[table].keys.each_with_index.inject([]) { |a, e| a + [e[0], r[e[1]]] } }
		.collect { |r| Hash[*r] }
		.sym_keys rescue nil
end

#save(table, fields) ⇒ Object

Save: Creates or updates fields in a given table, depending on the presence of an id.

Parameters:

  • table (Symbol) —

    Table name

  • fields (Hash) —

    Fields to be inserted / updated



111
112
113
114
115
116
117
 
# File 'lib/sack/database/data.rb', line 111

def save table, fields
	if fields[:id]
		update table, fields.clone.delete(:id), fields
	else
		create table, fields
	end
end

#update(table, id, fields) ⇒ Object

Update: Updates fields in rows identified by id in a given table.

Parameters:

  • table (Symbol) —

    Table name

  • id (Object) —

    ID on which to filter

  • fields (Hash) —

    Fields to be updated



103
104
105
 
# File 'lib/sack/database/data.rb', line 103

def update table, id, fields
	@db.execute Statement.prep("update #{Sanitizer.table @schema, table} set #{Generator.update_marks @schema, table, fields} where id = ?;", [Generator.values(fields.values), id].flatten)
end