<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Day 1</title>
</head>
<body>
<h1>Day 1: Spider Database Website</h1>
<img border="0" src="screenshot-small.png" width="521" height="511"></p>
<h2>In The Beginning...</h2>
<p><i>"In the beginning of creation, when Marc made the website, the website
was without form and void, with darkness over the face of the HTML, and a mighty
script that swept over the surface of the IDE. Marc said, 'Let there be a
database spider UI', and there was a database spider UI; and Marc saw that the
UI was good, and he separated primary keys from foreign keys. He called
the foreign keys parent/child relationships, and the darkness Ruby on Rails.
So evening came, and morning came, the first day."</i></p>
<p>I've been wanting to create a database navigator, what a friend of mine
termed a "Spider UI", for quite some time now. I originally wrote an
implementation in WinForms for an Oracle database but that never went as far as
I wanted it to go. Currently, I'm working a lot with Ruby on Rails and
also encountered a couple layers called <a href="http://slim-lang.com/">Slim</a>
("a lightweight templating engine") and <a href="http://sass-lang.com/">Sass</a>
("Syntactically Awesome Style Sheets") in another project. Wanting to
learn more about how Slim and Sass work together, I came up with this project
idea. In its full glory, I'm wanting to add all sorts of interesting
features, such as custom layout for editing, but first, I wanted to get some
basic functionality in place first. </p>
<p>Internally, the website must support:</p>
<ul>
<li>Connect to SQL Server (yes, Ruby on Rails can connect to SQL Server.)</li>
<li>Use a separate database for session and other metadata store, leaving
the database we're connecting to for navigation untouched.</li>
<li>Rather than physical Models for each table, implement a dynamic Model. </li>
<li>Dynamic discovery of user tables and foreign key relationships by inspecting
the database schema rather than relying on the Rails application schema (which
would be otherwise generated from concrete Models backed by physical tables.)</li>
</ul>
<p>The user interface must support:</p>
<ul>
<li>Selecting a table from a list</li>
<li>Viewing the data for that table</li>
<li>Selecting parent and child tables to navigate to</li>
<li>Selecting records to qualify navigation and display of parent/child
records</li>
<li>Pagination</li>
<li>Breadcrumb trail of parent/child navigation</li>
</ul>
<p>What I'm leaving for "Day 2" are several metadata features:</p>
<ul>
<li>Replacing foreign key ID's with "display field" lookups.</li>
<li>Describing the display fields in a lookup table to use when resolving
foreign keys.</li>
<li>Specifying fields that need not be displayed.</li>
<li>Aliasing field names</li>
<li>Aliasing table names</li>
</ul>
<p>"Day 3" will consist of supporting:</p>
<ul>
<li>type-based automated generation of UI's to edit table data.</li>
<li>custom record editing templates.</li>
</ul>
<p>"Day 4" will consist of:</p>
<ul>
<li>views (described in metadata as opposed to database-side views), as
creating views with full schema information is required.</li>
<li>creating SQL statements to support transactions on views</li>
</ul>
<p>What comes after that will probably involve the support of custom processing
of data during transactions (both code and PL/SQL calls) using Ruby as well as
server-side triggers on transactions. We'll see!</p>
<h3>If Your New To Ruby on Rails</h3>
<p>Code Project is primarily (at the time of this writing!) a site for all
things Microsoft, so if this is the first time you're encountering Ruby on
Rails, I'd recommend reading through some of my other articles on the Ruby
language and Ruby on Rails:</p>
<ul>
<li>
<a href="http://www.codeproject.com/Articles/551579/Csharp-and-Ruby-Classes">
C# and Ruby Classes</a></li>
<li>
<a href="http://www.codeproject.com/Articles/491362/Comparing-Ruby-and-Csharp-Performance">
Comparing Ruby and C# Performance</a></li>
<li>
<a href="http://www.codeproject.com/Articles/668478/Function-Composition-Function-Chaining-Currying-an">
Function Composition, Function Chaining, Currying, and Partial Application
in F# and Ruby</a></li>
<li>
<a href="http://www.codeproject.com/Articles/575551/User-Authentication-in-Ruby-on-Rails">
User Authentication in Ruby on Rails</a></li>
<li>
<a href="http://www.codeproject.com/Articles/664754/Where-In-The-World-Are-My-Facebook-Friends">
Where in the World are my Facebook Friends?</a></li>
</ul>
<p>as these are more tutorial based for developing Ruby on Rails (RoR)
applications. This article assumes that you already have some familiarity
with the project structure of a RoR application.</p>
<h3>AdventureWorks2008</h3>
<p>This article uses Microsoft's example database, Adventure Works, to
demonstrate Ruby on Rails connectivity to SQL Server as well as example dataset
for all the screenshots in this article.</p>
<h3>About the Ruby Code</h3>
<p>You'll notice that I tend to write very short Ruby functions - this is
intended to promote the clarity of higher-level functions, which can otherwise
detract from the purpose of the function when embedding idiomatic Ruby and
arcane operations. I also like to explicitly state what the return value
of a function is, even when it's unnecessary. This promotes further
clarity to someone who is unfamiliar with the application code.</p>
<h3>About the Slim "Code"</h3>
<h3>About the Sass "Code"</h3>
<h2>Gems and What They Are Used For</h2>
<p>Gems are Ruby on Rails' plug-in mechanism for adding functionality from the
vast amount of free and open source components that people have contributed to
over the years. Besides the Rails gems, the ones I'm taking advantage of
are:</p>
<pre>gem 'tiny_tds'
gem 'activerecord-sqlserver-adapter'
gem 'sqlite3'
gem 'slim'
gem 'thin'
gem 'sass'
gem 'will_paginate'</pre>
<h3>TinyTDS</h3>
<p>The gem <a href="https://github.com/rails-sqlserver/tiny_tds">tiny_tds</a>
necessary for connecting to SQL Server. TinyTDS requires a SQL Server
Authentication login, as opposed to a Windows Authentication login. I have
<a href="http://marcclifton.wordpress.com/2013/10/25/connecting-to-sql-server-express-from-ruby/">
a short blog entry here</a> on connecting to SQL Server Express from Ruby which
covers configuring SQL Server Express and testing the TinyTDS connectivity.
In this application, TinyTDS is used to acquire the schema information from SQL
Server -- see the section "<a href="#Schema_Class">Schema Class</a>" below.</p>
<h3>activerecord-sqlserver-adapter</h3>
<p><a href="https://github.com/rails-sqlserver/activerecord-sqlserver-adapter">
This gem</a> uses the "dblib" connection mode which in turn is dependent upon
TinyTDS. What this gem enables you to do is to work with the Rails
<a href="http://api.rubyonrails.org/classes/ActiveRecord/Base.html">ActiveRecord</a>
API for all transactions with the database. It's important that we use
ActiveRecord for table queries because the pagination system relies on our Model
classes being derived from ActiveRecord::Base. In future articles, we'll
also be relying, in part, on ActiveRecord for other transactions on the table
records.</p>
<p>Rails expects the connection information to be specified in the config\database.yml
file. Here we set up our development connection, specifying the sqlserver
adapter which the above gem provides us, along with the connection information
required by TinyTDS.</p>
<pre>development:
adapter: sqlserver
database: AdventureWorks2008
dataserver: localhost\SQLEXPRESS
username: ruby
password: rubyist1</pre>
<h3>Sqlite3</h3>
<p>One of the requirements of this application is to not change the schema of
the database that we're "spidering." Also, there's a lot of session
information that is being preserved -- too much to place is session cookies on
the client. We're using Sqlite3 for storing session information
independent of our SQL Server database and
<a href="https://github.com/sparklemotion/sqlite3-ruby">this gem</a> provides
the connectivity. To see how this is done, read the
section on "<a href="#Storing_Session_Information_in_a_Separate_Database">Storing
Session Information in a Separate Database.</a>"</p>
<h3>Slim</h3>
<p><a href="http://rubygems.org/gems/slim">This gem</a> eliminates the angle brackets and ending tags of the HTML script.
Here's a simple example of the lighter-weight syntax:</p>
<pre>doctype
html
head
title Database Spider
= stylesheet_link_tag "application", media: "all"
= javascript_include_tag "application"
= csrf_meta_tags
body
= yield</pre>
<p>Notice how indentation is used to determine where the closing tags should go
in the generated HTML.</p>
<h3>Sass</h3>
<p><a href="http://rubygems.org/gems/sass">This gem</a> is also "lightens" the description of CSS and works very well in
conjunction with Slim. You'll see Slim/Sass used such that the structure
of the Slim and Sass markup are identical. Here's a short example taken
from the user table list markup:</p>
<h4>The Slim Markup</h4>
<pre>.table_list_region
p Tables:
.table_list
ul
- @tables.each do |table_name|
li
= link_to(table_viewer_path(table_name: table_name)) do
t #{table_name}</pre>
<p>Notice how Slim knows how to work with both HTML and Ruby script in the
markup!</p>
<h4>The Sass Markup</h4>
<pre>.table_list_region
width: 240px
height: 700px
float: left
p
text-align: left
margin-bottom: 2px
.table_list
text-align: left
border: 1px solid
border-radius: 3px
width: 100%
height: 100%
line-height: 1.5em
ul
width: 190px
height: 99%
overflow: auto
margin-top: 2px
li
list-style-type: none
margin-left: -30px
a:visited
color: #000000
a:link
color: #000000
text-decoration: none
a:hover
color: #0000FF</pre>
<p>You can see how Sass markup can follow the same structure as the Slim markup.
The problem with this approach is that it obfuscates re-use of CSS. For
example, I might want to re-use the <code>table_list</code> styling but since it's a child of <code>table_list_region</code>, I can't
(at least not without a <code>table_list_region</code> container).
However, I can certainly outdent the styling:</p>
<pre>.table_list_region
width: 240px
height: 700px
float: left
p
text-align: left
margin-bottom: 2px
.table_list
text-align: left
... etc ...</pre>
<p>without affecting the structure of the Slim markup. Now <ocde>table_list</ocde> is
accessible inside the container as well as reusable by other containers. Unfortunately, I
have worked on at least one major open source project in which the developers
chose not to promote styling re-use, resulting in complex Sass structures in
which one has to follow the hiearchy of the Slim <i>exactly</i> to locate the
styling that I wanted to change. Don't do this unless your styling is
truly specific to its container context!</p>
<h3>Will_paginate</h3>
<p><a href="https://github.com/mislav/will_paginate/wiki">This</a> is an amazing
gem that paginates your data and provides a variety of pre-existing styling and
also the ability to customize the styling of the pagination bar. And of
course, one of the advantages of using pagination is that for tables with large
numbers of records, only the records for that page are returned from the
database, greatly improving usability.</p>
<h2>The Code</h2>
<p>I'm not going to go into every detail of the code, but I will point out some
of the more interesting features.</p>
<h3><a name="Schema_Class">Schema Class</a></h3>
<p>This class (schema.rb) encapsulates the static functions we need for
connecting to SQL Server directly and obtaining schema information and thus
relies on the TinyTDS gem for the direct SQL connection. The
main workhorse is this function:</p>
<pre>def self.execute(sql)
client = create_db_client
result = client.execute(sql)
records = result.each(as: :array, symbolize_keys: true)
array = convert_to_array_of_hashes(result.fields, records)
array
end</pre>
<p>We rely on these helper methods (helpers\my_utils.rb) to create a client
connection:</p>
<pre># create a client connection.
def create_db_client
config = get_current_database_config
config_as_symbol = symbolize_hash_key(config)
client = TinyTds::Client.new(config_as_symbol)
client
end
# Returns the current database config has a dictionary of string => string
def get_current_database_config
Rails.configuration.database_configuration[Rails.env]
end
# Given a dictionary of string => string, returns :symbol => string
# Example: config_as_symbol = symbolize_hash_key(config)
def symbolize_hash_key(hash)
hash.each_with_object({}){|(k,v), h| h[k.to_sym] = v}
end</pre>
<p>Once the records are returned from TinyTDS, I want to package them into an
array of hashes (field => value) so that there's a consistent representation of
the resulting data. This requires a couple post-processing functions:</p>
<pre># Convert the array of records from the TinyTDS query into an array of hashes, where
# the keys are the field names.
def self.convert_to_array_of_hashes(fields, records)
array = []
records.each { |record|
dict = hash_from_key_value_arrays(fields, record)
array << dict
}
array
end
# Given two arrays of equal length, 'keys' and 'values', returns a hash of key => value
def hash_from_key_value_arrays(keys, values)
Hash[keys.zip values]
end</pre>
<h3>Schema Queries</h3>
<p>We can now define the three functions that we need for our Spider UI:</p>
<ol>
<li>get_user_tables</li>
<li>get_parent_table_info_for</li>
<li>get_child_table_info_for</li>
</ol>
<p>In the last two functions, we replace the string "[table]" with the table
name before executing the query:</p>
<pre> sql.sub!('[table]', table_name.partition('.')[2])</pre>
<p>What we're not dealing with at the moment is that the table name being passed
in is fully qualified (it includes also the schema name) but the query doesn't
pay attention to the schema name, hence we need to extract just the table name
from the parameter value. This is one of those "TODO" items for a later
date.</p>
<p>These functions rely on our storing the actual queries in a "queries.yml"
file, which is a hierarchical file similar to XML in concept but very different
in implementation. In this file, we store our schema queries:</p>
<pre>Schema:
user_tables: "
select
s.name + '.' + o.name as table_name
from
sys.objects o
left join sys.schemas s on s.schema_id = o.schema_id
where
type_desc = 'USER_TABLE'"
get_parents: "
SELECT
f.parent_object_id as ChildObjectID,
SCHEMA_NAME(f.schema_id) SchemaName,
OBJECT_NAME(f.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName,
SCHEMA_NAME(ref.schema_id) ReferencedSchemaName,
OBJECT_NAME(f.referenced_object_id) ReferencedTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferencedColumnName
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.tables t ON t.object_id = fc.referenced_object_id
INNER JOIN sys.tables ref ON ref.object_id = f.referenced_object_id
WHERE
OBJECT_NAME (f.parent_object_id) = '[table]'"
get_children: "
SELECT
f.parent_object_id as ChildObjectID,
SCHEMA_NAME(f.schema_id) SchemaName,
OBJECT_NAME(f.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName,
SCHEMA_NAME(ref.schema_id) ReferencedSchemaName,
OBJECT_NAME(f.referenced_object_id) ReferencedTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferencedColumnName
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON fc.constraint_object_id = f.OBJECT_ID
INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id
INNER JOIN sys.tables ref ON ref.object_id = f.referenced_object_id
WHERE
OBJECT_NAME (f.referenced_object_id) = '[table]'"</pre>
<p>and we again use a small helper function to retrieve the text:</p>
<pre># Gets the specified query from the config.yml file
# Example: sql = get_query("Schema", "user_tables")
# TODO: cache keys
def get_query(key1, key2)
sql = YAML.load_file(File.expand_path('config/queries.yml'))
sql[key1][key2]
end</pre>
<p>Here's another "TODO" item for later: eventually this structure will be "refactored" to include the database
context, because of course querying the schema of a database is very database
specific!</p>
<h4>Get User Tables</h4>
<p>This function simply returns a collection of user table names:</p>
<pre># Returns an array of strings containing the user tables in the database to which we're connecting.
def self.get_user_tables
client = create_db_client
sql = get_query("Schema", "user_tables")
result = client.execute(sql)
records = result.each(as: :array, symbolize_keys: true)
names = get_column(records, 0)
names
end</pre>
<h4>Get Parent Table Info</h4>
<p>This function returns the foreign key associations of the current table,
resulting in an array of all parent tables along with schema information
describing the columns of both child and parent tables that describe the foreign
key relationship:</p>
<pre># Returns an array of hashes (column name => value) of the parent table schemas of the specified table.
def self.get_parent_table_info_for(table_name)
sql = get_query("Schema", "get_parents")
sql.sub!('[table]', table_name.partition('.')[2])
execute(sql)
end</pre>
<h4>Get Child Table Info</h4>
<p>This function returns the child tables and their foreign key column
relationships to the specified parent table. This query has a similar
structure as what is returned in the previous function, courtesy of how the SQL
is formatted (see earlier.)</p>
<pre># Returns an array of hashes (column name => value) of the child table schemas of the specified table.
def self.get_child_table_info_for(table_name)
sql = get_query("Schema", "get_children")
sql.sub!('[table]', table_name.partition('.')[2])
execute(sql)
end</pre>
<h2><a name="Storing_Session_Information_in_a_Separate_Database">Storing Session
Information in a Separate Database</a></h2>
<p>We need to tell Rails in initializers\session_store.rb
that we want to use a database rather than cookies for storing session
information:</p>
<pre>DatabaseSpider::Application.config.session_store :active_record_store</pre>
<p>However, we want to tell Rails not to use the SQL Server database, so we also
need:</p>
<pre>ActiveRecord::SessionStore::Session.establish_connection(:sessions)</pre>
<p>and finally, we need to put the session definition in the config\database.yml
file:</p>
<pre># use sqlite3 as the DB for storing session information.
sessions:
adapter: sqlite3
database: db/session.sqlite3
pool: 5
timeout: 5000</pre>
<h2>Dynamic Active Records</h2>
<p>Another requirement is to avoid creating a concrete Model class for every
physical table in the database. To accomplish this, we derive a class from
ActiveRecord::Base but specify the table name to which it is associated:</p>
<pre>class DynamicTable < ActiveRecord::Base
# Returns an array of records for the specified table.
def set_table_data(table_name)
DynamicTable::table_name = table_name
end
# Returns the field names given at least one record.
def get_record_fields(records)
fields = []
fields = records[0].attributes.keys if records.count > 0
fields
end
end</pre>
<p>This enables us to interact with the table just as we would with any other
ActiveRecord instance.</p>
<p>The function get_record_fields is used to return the field names for the
record - we arbitrarily pick the first record for the field list, assuming any
records exist at all. This is a bit problematic because even if there are
no records returned, we'd like the table to at least display the field names.
So one of the "TODO" items is to use a TinyTDS query with "where 1=0", which,
though it returns zero rows, will populate the field names for us.</p>
<h2>TableViewerController, the Index Function</h2>
<p>The <code>index</code> function of the <code>TableViewerController</code> is the main workhorse of the
website:</p>
<pre>def index
initialize_attributes
update_model_page_numbers
if self.table_name
restore_page_number_on_nav # restores the page number when navigating back along the breadcrumbs
self.last_page_num = self.model_page_nums[self.table_name+'_page'] # preserve the page number so selected navigation records are selected from the correct page.
@data_table = load_DDO(self.table_name, self.last_page_num, self.qualifier, MAIN_TABLE_ROWS)
add_hidden_index_values(@data_table)
load_navigators(self.table_name)
@parent_dataset = load_fk_tables(@parent_tables)
@child_dataset = load_fk_tables(@child_tables)
# Update the parent tab index based on the existence and value of the selected_parent_table_index parameter
update_parent_child_tab_indices
end
end</pre>
<p>Various attributes are initialized, of most important are:</p>
<ul>
<li>the list of user tables.</li>
<li>a DynamicTable instance.</li>
<li>the navigation breadcrumb trail is restored from the session.</li>
</ul>
<h3>Session Variables</h3>
<p>There are several session variables defined in the controller:</p>
<pre>attr_session_accessor :table_name # the selected user table
attr_session_accessor :qualifier # the qualifier currently being used to filter the selected user table
attr_session_accessor :breadcrumbs # the breadcrumb trail
attr_session_accessor :last_page_num # the last page number of the user table
attr_session_accessor :force_page_num # if set, forces the pagination to a different page, used with breadcrumbs
attr_session_accessor :model_page_nums # dictionary of page numbers for all the models being displayed.</pre>
<p>which help preserve the state of the page between posts.</p>
<h3>Custom Attribute Accessor</h3>
<p>I get tired of having to write code like this:</p>
<pre>@qualifier = session[:qualifier]
session[:table_name] = @table_name</pre>
<p>so I wrote a custom attribute accessor that simplifies getting and setting
session values:</p>
<pre># Adds an "attr_session_accessor" declarator that, in addition to setting/getting the value
# to the attribute, it also gets/sets the value from the session.
# Usage inside the class defining the attribute: self.foobar = 1
# Note that "self." must prefix the usage of the attribute.
class Class
def attr_session_accessor(*args)
args.each do |arg|
self.class_eval("def #{arg}; @#{arg}=session['#{arg}']; end")
self.class_eval("def #{arg}=(val); @#{arg}=val; session['#{arg}']=val; end")
end
end
end</pre>
<p>For the getter, this code initializes the specified attribute from the
session name, and is equivalent to writing (for the variable <code>qualifier</code>):</p>
<pre>@qualifier = session[:qualifier]</pre>
<p>The setter initializes both the attribute and the session key with the
specified value, and is the equivalent of writing (for the variable <code>table_name</code>):</p>
<pre>@table_name = val
session[:table_name] = @table_name</pre>
<p>Thus, we can code like this (this is the handler for clicking on a
breadcrumb):</p>
<pre># Navigate back to the selected table in the nav history and pop the stack to that point.
# Use the qualifier that was specified when navigating to this table.
# Restore the page number the user was previously on for this table.
def nav_back
stack_idx = params[:index].to_i
breadcrumb = self.breadcrumbs[stack_idx] # get the current breadcrumb
self.table_name = breadcrumb.table_name # we want to go back to this table and its qualifier
self.qualifier = breadcrumb.qualifier
self.breadcrumbs = self.breadcrumbs[0..stack_idx] # remove all the other items on the stack
self.force_page_num = breadcrumb.page_num
redirect_to table_viewer_path+"/index"
end</pre>
<p>I suppose it would be more readable if I named these variables with some
prefix, perhaps "sess_" to make it clear to the reader that we're accessing
session data, however, an more useful "TODO" would be to put together a <code>Session</code>
class that has all the session attributes that the controller referenecs.
Internally, this class could still use my custom attribute accessor, but it
would also allow intellisense to work within the IDE, improving the programmer's
experience and providing clarity as to what is going on behind the scenes.</p>
<h3>Byte Encoding</h3>
<p>One of the issues I encountered was with SQL Server fields that were using
byte encoding. This gave Rails fits and required a helper method, applied
to each field value:</p>
<pre># Fixes encoding to UTF-8 for certain field types that cause problems.
# http://stackoverflow.com/questions/13003287/encodingundefinedconversionerror
helper_method :fix_encoding
def fix_encoding(value)
value.to_s.encode('UTF-8', {:invalid => :replace, :undef => :replace, :replace => '?'})
end</pre>
<h3>Hiding Fields</h3>
<p>Certain fields that are found in the Adventure Works database can simply
always be hidden. Also, the paginator adds a column that it uses
internally for keeping track of what page the user is viewing, and finally, I
add a column that the checkbox on each row uses to identify the selected row.
None of these need to be displayed, therefore we have a couple helper methods:</p>
<pre># Return true if the field can be displayed.
# All sorts of interesting things can be done here:
# Hide primary keys, ModifiedDate, rowguid, etc.
helper_method :display_field?
def display_field?(table_name, field_name)
# '__rn' is something that will_paginate adds.
# '__idx' is my hidden column for creating a single column unique ID to identify selected rows, since
# we can't guarantee single-field PK's and we need some way to identify a row uniquely other than the actual data.
return false if ['__rn', '__idx', 'rowguid', 'ModifiedDate'].include?(field_name)
true
end
# Returns only the visible fields
helper_method :get_visible_fields
def get_visible_fields(data_table)
data_table.fields.keep_if {|f| display_field?(data_table.table_name, f)}
end</pre>
<h2>The User Interface</h2>
<p>The user interface consists of six areas:</p>
<ol>
<li>The list of user tables in the database</li>
<li>The selected table's data</li>
<li>The parent tables of the selected table</li>
<li>The child tables of the selected table</li>
<li>Navigation options</li>
<li>List of navigations (breadcrumbs)</li>
</ol>
<p>I decided to put each of these sections into their own render block, thus the
resulting index.html.slim file is simply:</p>
<pre>=form_for @table_viewer do |f|
= render 'breadcrumbs'
= render 'user_tables'
- # The selected table data
- if !@table_name.nil?
= render 'parent_tables'
= render 'selected_table'
= render 'navigation', f: f
= render 'child_tables'
javascript:
select_fk_tab('#parent_tab', '#parent_tab_content', #{@parent_tab_index}, #{@parent_tables.count})
select_fk_tab('#child_tab', '#child_tab_content', #{@child_tab_index}, #{@child_tables.count})</pre>
<h3>Breadcrumbs</h3>
<p>
<img border="0" src="../../DatabaseSpider/article/breadcrumbs.png" width="375" height="62"></p>
<p>The breadcrumbs is a clickable list of tables that the user has already
navigated to using the "navigate to parent" and/or "navigate to child" options.
When a breadcrumb is selected, any selection that was made to qualify the data
for that table is restored as well as the table's data.</p>
<h4>The Slim Markup</h4>
<pre>- # The navigation breadcrumbs
.navigation_history
p = "Nav History:"
br
- if @breadcrumbs
- @breadcrumbs.each_with_index do |breadcrumb, index|
- table_name = breadcrumb.table_name
= link_to content_tag(:span, "#{table_name.partition('.')[2]}"), navigate_back_path(index: index), {:class => "button"}, :onclick => 'this.blur();'</pre>
<h3>User Tables</h3>
<p>
<img border="0" src="../../DatabaseSpider/article/user_tables.png" width="249" height="404"></p>
<p>This is simply an alphabetical list of user tables.</p>
<h4>The Slim Markup</h4>
<pre>- # The list of all tables.
.table_list_region
p Tables:
.table_list
ul
- @tables.each do |table_name|
li
= link_to(table_viewer_path(table_name: table_name)) do
t #{table_name}</pre>
<h3>Selected Table Data</h3>
<p>
<img border="0" src="../../DatabaseSpider/article/selected_table.png" width="613" height="189"></p>
<p>The selected user table is displayed with a checkbox on each row so that the
user can select a specific row or rows and navigate to a child or parent table
whose resulting data is displayed in the same box but qualified by the
selection. If no selection is made, then all the parent or child data is
displayed. At the bottom of the table is the paginator (see the parent and
child table data for visual examples.)</p>
<h4>The Slim Markup</h4>
<pre>- # The selected user table data.
.table_data_region
p #{@data_table.table_name}
= render "table_data", data_table: @data_table
.digg_pagination
= will_paginate @data_table.records, param_name: @data_table.table_name+"_page"</pre>
<p>Note that this code (and the code used for child and table navigation) all
re-use another render called "table_data".</p>
<h3>_table_data.html.slim</h3>
<p>This is file used in common for rendering the table data in the selected
table, parent tables and child tables:</p>
<pre>- # render for table data. The paginator is separate because it requires additional parameters that are specific
- # to the data being paginated.
.table_data
- visible_fields = get_visible_fields(data_table)
table
- # Display header
tr
- # Dummy header column for checkbox
th
- # Header of field names
- visible_fields.each do |field_name|
th = field_name
- # Display records
- data_table.records.each do |record|
tr class=cycle('row0', 'row1')
td
- # __idx is added by the controller to uniquely identify a record.
= check_box_tag 'selected_records[]', record["__idx"]
- visible_fields.each_with_index do |field_name, field_index|
- # Extends the last column out to the right edge of the table.
- if field_index == visible_fields.length - 1
td.last = fix_encoding(record[field_name])
- else
td = fix_encoding(record[field_name])</pre>
<p>The interesting part of this markup is figuring out when the last column is
being rendered and to change the styling slightly so that the row color extends
to the right edge of the table box:</p>
<rep>td<br>
border-left: 1px solid #d0d0d0<br>
white-space: nowrap<br>
padding-left: 5px<br>
padding-right: 5px<br>
td.last
// the last column fills any remaining space<br>
width: 100%<p>Also notice the snazzy cycle function of Ruby:</p>
<pre>tr class=cycle('row0', 'row1')</pre>
<p>which cycles through the class styling strings for the rows, achieving the
alternating colors:</p>
<p>tr.row0
// alternating row colors<br>
background-color: #ffffff<br>
tr.row1<br>
background-color: #ddffdd<br>
</p>
<p> </p>
<p>Javascript</p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p>
</body>
</html>
Submit an article or tip