Class: Ink::Database

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

Overview

Database class

Config

Currently there are two types of databases supported, MySQL and SQLite3. Either way, you need to specify them in a config-file that is located inside the web project folder.

Sample config for MySQL:

config = {
  :production        => true,
  :db_type           => "mysql",
  :db_user           => "yourusername",
  :db_pass           => "yourpassword",
  :db_database       => "yourdatabase",
  :db_server         => "localhost",
}

Sample config for SQLite3:

config = {
  :production        => true,
  :db_type           => "sqlite3",
  :db_server         => "/full/path/to/database.sqlite",
}

Adapters

Generally before using the database, a fitting adapter needs to be loaded which will host the necessary methods to query the database.

The SqlAdapter is an abstract class shipped with webink that is inherited in Sqlite3Adapter and MysqlAdapter. Any custom adapters can be easily built in a similar manner. More information can be found in the doc of SqlAdapter

This means, all database does essentially, is being an interface to all methods offered by the adapters. Method documentation is copied over to the SqlAdapter for convenience.

Usage

Create an Ink::Database instance with the self.create class method. Now it can be accessed via the public class variable ‘database’. Once that is done, can use it to execute various SQL statements to gather data.

Ink::Database.database.query "SELECT * FROM x;"

This is the most basic query, it returns an Array of results, and each element contains a Hash of column_name => column_entry.

Ink::Database.database.query("SELECT * FROM x;", Array){ |itm, k, v|
  itm.push v
}

The query method has a second parameter “type” which defaults to Hash. This is the class instance created in the resultset array (i.e. query returns [ type, type, type,…]). A block allows you to assign the k (column name) and v (value) to itm (a type.new taken from the second parameter) however you like.

The following methods are convenience methods to access data for models. As example a model Apple and its n:1 relation to Tree are used. Please note that both class and database table name can be used to call the find and related methods. The table name for Apple would be “apple”; for MyApple would be “my_apple”.

Ink::Database.database.find "apple", "WHERE id < 10 GROUP BY color"
=> self.query("SELECT * FROM apple WHERE id < 10 GROUP BY color;")

This is different from the query method, because it returns an Array of Objects, created by the information stored in the database. So this find() will return you a set of Apple-instances.

Ink::Database.database.find_union "apple", 5, "tree", "AND tree_id>1"

find_union allows you to retrieve data through a many_many reference. When you define a many_many relationship, a helper-table is created inside the database, which is apple_tree, in this case. This statement will fetch an Array of all Trees, that connect to the Apple with primary key 5. Notice that the relationship database apple_tree is put together by the alphabetically first, and then second classname. The last quotes allow additional query informations to be passed along (like group by)

Ink::Database.database.find_references Tree, 1, Apple, "AND tree_id>1"

find_references is similar to find_union, only that it handles all other relationships. This statement above requires one Tree to have many Apples, so it will return an Array of Apples, all those that belong to the Tree with primary key 1

Ink::Database.database.find_reference Apple, 5, Tree, ""

find_reference is essentially equal to find_references, yet it returns one result of a Tree or nil. This is used when Apple-Tree is a many_one or one_one relationship. It saves the need for the result Array from find_references.

Please close the dbinstance once you are done. This is automatically inserted in the init.rb of a project.

Convenience methods

Database.format_date(Time.now)

This will return a date in the form of 2012-11-20 10:00:02 and takes a Time instance.

Constant Summary collapse

@@database = 
nil

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(config) ⇒ Database

Private Constructor

Uses the config parameter to create a database connection, and will throw an error, if that is not possible.

param config:

Hash of config parameters



123
124
125
126
127
128
129
130
131
 
# File 'lib/webink/database.rb', line 123

def initialize(config)
  klass = Ink.const_get("#{config[:db_type].capitalize}Adapter")
  if klass.is_a?(Class)
    @db_class = klass
    @db = klass.new(config)
  else
    raise ArgumentError.new("Database undefined.")
  end
end

Class Method Details

.create(config) ⇒ Object

Class method

Instanciates a new Database if none is found

param config:

Hash of config parameters



137
138
139
 
# File 'lib/webink/database.rb', line 137

def self.create(config)
  @@database = new(config) if not @@database
end

.database ⇒ Object

Class method

Returns the Database instance or raises a Runtime Error

returns:

Database instance



152
153
154
155
156
157
158
 
# File 'lib/webink/database.rb', line 152

def self.database
  if @@database
    @@database
  else
    raise RuntimeError.new("No Database found. Create one first")
  end
end

.drop ⇒ Object

Class method

Removes an instanciated Database



144
145
146
 
# File 'lib/webink/database.rb', line 144

def self.drop
  @@database = nil if @@database
end

.format_date(date) ⇒ Object

Class method

Formats a Time object according to the SQL TimeDate standard

param date:

Time object

returns:

Formatted string



338
339
340
 
# File 'lib/webink/database.rb', line 338

def self.format_date(date)
  @db_class.format_date(date)
end

.transform_from_sql(value) ⇒ Object

Class method

Transform a value from sql to objects. This means nils, integer, floats and strings are imported correctly.

param value:

String

returns:

Object



187
188
189
 
# File 'lib/webink/database.rb', line 187

def self.transform_from_sql(value)
  @db_class.transform_from_sql(value)
end

.transform_to_sql(value) ⇒ Object

Class method

Transform a value to sql representative values. This means quotes are escaped, nils are transformed and everything else is quoted.

param value:

Object

returns:

transformed String



176
177
178
 
# File 'lib/webink/database.rb', line 176

def self.transform_to_sql(value)
  @db_class.transform_to_sql(value)
end

Instance Method Details

#close ⇒ Object

Instance method

Closes the database connection, there is no way to reopen without creating a new Ink::Database instance



205
206
207
208
 
# File 'lib/webink/database.rb', line 205

def close
  @db.close
  self.class.drop
end

#create_all_links(instance, link, type, value) ⇒ Object

Instance method

Attempt to create links of instance to the data inside value. link is the class of the related data, and type refers to the relationship type of the two. When one tries to insert an array for a x_one relationship, the last entry will be set.

param instance:

Instance of a class that refers to an existing database entry

param link:

the related class (not a String, but class reference)

param type:

relationship type

param value:

relationship data that was set, either a primary key value, or an instance, or an array of both



314
315
316
 
# File 'lib/webink/database.rb', line 314

def create_all_links(instance, link, type, value)
  @db.create_all_links(instance, link, type, value)
end

#create_link(instance, link, type, fk) ⇒ Object

Instance method

Creates a link between instance and a link with primary fk. The relationship between the two is defined by type. one_one relationships are placing an additional call to delete_all_links that will remove conflicts.

param instance:

Instance of a class that refers to an existing database entry

param link:

the related class (not a String, but class reference)

param type:

relationship type

param value:

primary key of the relationship, that is to be created



329
330
331
 
# File 'lib/webink/database.rb', line 329

def create_link(instance, link, type, fk)
  @db.create_link(instance, link, type, fk)
end

#delete_all_links(instance, link, type) ⇒ Object

Instance method

This method attempts to remove all existing relationship data of instance with link of type: type. For one_one relationships this works only one way, requiring a second call later on before setting a new value.

param instance:

Instance of a class that refers to an existing database entry

param link:

the related class (not a String, but class reference)

param type:

relationship type



298
299
300
 
# File 'lib/webink/database.rb', line 298

def delete_all_links(instance, link, type)
  @db.delete_all_links(instance, link, type)
end

#find(class_name, params = "") ⇒ Object

Instance method

Retrieve class instances, that are loaded with the database result set.

param class_name:

Defines the class name or class which should be queried

param params:

Additional SQL syntax like WHERE conditions (optional)

returns:

Array of class_name instances from the SQL result set



244
245
246
 
# File 'lib/webink/database.rb', line 244

def find(class_name, params="")
  @db.find(class_name, params)
end

#find_reference(class1, class1_id, class2, params = "") ⇒ Object

Instance method

Retrieve one class2 instance, that is related to the class1 instance with primary key class1_id. Only relevant for one_one and one_many relationships

param class1:

Reference classname or class

param class1_id:

Primary key value of the reference classname

param class2:

Match classname or class

param params:

Additional SQL syntax like GROUP BY (optional)

returns:

single class2 instance from the SQL result set or nil



285
286
287
 
# File 'lib/webink/database.rb', line 285

def find_reference(class1, class1_id, class2, params="")
  @db.find_reference(class1, class1_id, class2, params)
end

#find_references(class1, class1_id, class2, params = "") ⇒ Object

Instance method

Retrieve class2 instances, that are related to the class1 instance with primary key class1_id. Not relevant for many_many relationships

param class1:

Reference classname or class

param class1_id:

Primary key value of the reference classname

param class2:

Match classname or class

param params:

Additional SQL syntax like GROUP BY (optional)

returns:

Array of class2 instances from the SQL result set



271
272
273
 
# File 'lib/webink/database.rb', line 271

def find_references(class1, class1_id, class2, params="")
  @db.find_references(class1, class1_id, class2, params)
end

#find_union(class1, class1_id, class2, params = "") ⇒ Object

Instance method

Retrieve class2 instances, that are related to the class1 instance with primary key class1_id. This is done via an additional relationship table. Only relevant for many_many relationships.

param class1:

Reference classname or class

param class1_id:

Primary key value of the reference classname

param class2:

Match classname or class

param params:

Additional SQL syntax like GROUP BY (optional)

returns:

Array of class2 instances from the SQL result set



258
259
260
 
# File 'lib/webink/database.rb', line 258

def find_union(class1, class1_id, class2, params="")
  @db.find_union(class1, class1_id, class2, params)
end

#last_inserted_pk(class_name) ⇒ Object

Instance method

Attempts to fetch the last inserted primary key

param class_name:

Defines the __table__ name or class

returns:

primary key or nil



215
216
217
 
# File 'lib/webink/database.rb', line 215

def last_inserted_pk(class_name)
  @db.last_inserted_pk(class_name)
end

#primary_key_autoincrement(pk = "id") ⇒ Object

Instance method

Creates the SQL syntax for the chosen database type to define a primary key, autoincrementing field

returns:

SQL syntax for a primary key field



224
225
226
 
# File 'lib/webink/database.rb', line 224

def primary_key_autoincrement(pk="id")
  @db.primary_key_autoincrement(pk)
end

#query(query, type = Hash) ⇒ Object

Instance method

Send an SQL query string to the database and retrieve a result set

param query:

SQL query string

returns:

Array of Hashes of column_name => column_entry



197
198
199
 
# File 'lib/webink/database.rb', line 197

def query(query, type=Hash)
  @db.query(query, type)
end

#remove(class_name, params = "") ⇒ Object

Instance method

Delete something from the database.

param class_name:

Defines the class name or class

param params:

Additional SQL syntax like WHERE conditions (optional)



233
234
235
 
# File 'lib/webink/database.rb', line 233

def remove(class_name, params="")
  @db.remove(class_name, params)
end

#tables ⇒ Object

Instance method

This will retrieve all tables nested into the connected database.

returns:

Array of tables



165
166
167
 
# File 'lib/webink/database.rb', line 165

def tables
  @db.tables
end