Thor CLI KJV Bible Search Engine – part 2

In part 1 I showed how I created a simple CLI (command line interface) Bible Search Engine. The problem is that one must always reference the path to the Bible text files. So I will create a Thor task which will eliminate this dependence on the user giving a path (or having to change to the text file directory). First, I create a directory called bbl (which means Bible) inside my _thor directory (which is just a collection of task directories) and then copy the source files there. I’ll create a bbl.rb file which holds my Ruby code creating a Thor task.
[code]
_thor/bbl$ ls
bbl.rb books

_thor/bbl$ more bbl.rb
class Bbl < Thor

def self.source_root
# the permanent location of the text files on my system
‘~/projects/_thor/bbl/books’
end

end
Now we can install the Thor task so the system always knows about it:
[code]
_thor/bbl$ thor install bbl.rb
Please specify a name for bbl.rb in the system repository [bbl.rb]: bbl
Storing thor file in your system repository
[/code]

[code]
_thor/bbl$ thor installed
Modules Namespaces
——- ———-
bbl bbl

bbl

thor bbl:find [PHRASE] # find a phase given as list of words on param line
[/code]

Now we can search through the Bible from anywhere on the system. Here is an example from the home directory:
[code]
author@his-laptop:~$ cd ~
author@his-laptop:~$ thor bbl:find "thou art a priest"
psalms 110:004 The LORD hath sworn, and will not repent, Thou art a priest for ever after the order of Melchizedek.
hebrews 005:006 As he saith also in another place, Thou art a priest for ever after the order of Melchisedec.
hebrews 007:017 For he testifieth, Thou art a priest for ever after the order of Melchisedec.
hebrews 007:021 (For those priests were made without an oath; but this with an oath by him that said unto him, The Lord sware and will not repent, Thou art a priest for ever after the order of Melchisedec:)
[/code]

Another advantage of running output through the Thor library is that we can perform post-processing to clean up the output (notice the use of Thor’s shell.set_color method):

We get a cleaner presentation:

[code]
_thor/bbl$ thor bbl:find "Thou art a priest"
[/code]

psalms 110:004 The LORD hath sworn, and will not repent, << Thou art a priest >> for ever after the order of Melchizedek.
hebrews 005:006 As he saith also in another place, << Thou art a priest >> for ever after the order of Melchisedec.
hebrews 007:017 For he testifieth, << Thou art a priest >> for ever after the order of Melchisedec.
hebrews 007:021 (For those priests were made without an oath; but this with an oath by him that said unto him, The Lord sware and will not repent, << Thou art a priest >> for ever after the order of Melchisedec:)

fun with sqlite3

We already have sqlite3 installed. So creating a new database is easy:

[code]
..mypath/db$ sqlite3 test.sqlite3
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
[/code]

We add some tables:
[code]
sqlite> create table categories (name varchar(10), id smallint);
sqlite> create table products(name varchar(20), id smallint, category_id smallint);
[/code]

List our new tables:
[code]
sqlite> .schema
CREATE TABLE categories (name varchar(10), id smallint);
CREATE TABLE products(name varchar(20), id smallint, category_id smallint);
sqlite>
[/code]

Now insert some data:
[code]
sqlite> insert into categories values (‘catholicism’, 1);
sqlite> insert into categories values (‘baptist’, 2);
sqlite> insert into categories values (‘chino orthodox’, 3);
sqlite> insert into categories values (‘redneck’, 4);
sqlite> insert into products values (‘bible’, 1, 2);
sqlite> insert into products values (‘candle’, 2, 1);
sqlite> insert into products values (‘gun’, 3, 4);
sqlite> insert into products values (‘chopsticks’, 4, 3);
[/code]

List content of tables:
[code]
sqlite> select * from categories;
catholicism|1
baptist|2
chino orthodox|3
redneck|4
sqlite>
[/code]
[code]
sqlite> select * from products;
bible|1|2
candle|2|1
gun|3|4
chopsticks|4|3
sqlite>
[/code]

Okay. In this simple design there is a one-to-many relationship between tables. A category has many products. A category is just a category. It has no foreign keys. The products tables has a foreign key called “category_id.”

Now we can join the data:

[code]
sqlite> select p.name, c.name from products p inner join categories c on p.category_id = c.id;
bible|baptist
candle|catholicism
gun|redneck
chopsticks|chino orthodox
sqlite>
[/code]

Why did I use the “inner join” construct? Honestly, I don’t even think about this. I always think first in terms of inner join. If I need more complicated logic then perhaps I will use an “left join” construct.

[code]
sqlite> select p.name, c.name from products p left join categories c on p.category_id = c.id;
bible|baptist
candle|catholicism
gun|redneck
chopsticks|chino orthodox
[/code]

Hey! The results are the same! Why? Only because of the state of the data.

Let us change the data:
[code]
sqlite> update products set category_id = null where id = 3;
sqlite> select * from products;
bible|1|2
candle|2|1
gun|3|
chopsticks|4|3
sqlite>
[/code]

Now what category does the “gun” item belong to? We don’t know. Maybe it belongs to the Baptists or the Catholics?

Run the query again with both queries:

[code]
sqlite> select p.name, c.name from products p inner join categories c on p.category_id = c.id;
bible|baptist
candle|catholicism
chopsticks|chino orthodox
sqlite>
[/code]
[code]
sqlite> select p.name, c.name from products p left join categories c on p.category_id = c.id;
bible|baptist
candle|catholicism
gun|
chopsticks|chino orthodox
sqlite>
[/code]

Wow. The first query only shows products that have valid categories. The second query allows the invalid categories. So it returns the intersection and layers it on top of the invalid data.

Now we can ask which products need a category:
[code]
sqlite> select p.name, c.name from products p left join categories c on p.category_id = c.id where p.category_id is null;
gun|
sqlite>
[/code]
Ooooh! Someone in the Church Bookstore forgot to update the category of the gun item!

renaming batches of files in linux

I often need to rename many files at one time. Usually I just want to add an extension to the end of the name. The best way I have found is with the rename command. I have a few .rb files which I want to rename as .rb.erb files.

[code]
-rwxr-xr-x 1 philip philip 218 2010-10-22 17:19 message_broadcaster.rb
-rwxr-xr-x 1 philip philip 90 2010-10-22 17:19 message_group_criteria.rb
-rwxr-xr-x 1 philip philip 2180 2010-10-22 17:19 message.rb
-rwxr-xr-x 1 philip philip 193 2010-10-22 17:19 message_recipient_group.rb
-rwxr-xr-x 1 philip philip 134 2010-10-22 17:19 message_recipient.rb
-rwxr-xr-x 1 philip philip 427 2010-10-22 17:19 message_template_owner.rb
-rwxr-xr-x 1 philip philip 661 2010-10-22 17:19 message_template.rb
[/code]

Running the rename command with a -n will show you files that will be changed without actually running the command. Let’s take a look at potential changes:
[code]rename -v -n ‘s/^([a-z_A-Z]+\.rb)$/$1.erb/’ *.rb[/code]
[code]
message_broadcaster.rb renamed as message_broadcaster.rb.erb
message_group_criteria.rb renamed as message_group_criteria.rb.erb
message.rb renamed as message.rb.erb
message_recipient_group.rb renamed as message_recipient_group.rb.erb
message_recipient.rb renamed as message_recipient.rb.erb
message_template_owner.rb renamed as message_template_owner.rb.erb
message_template.rb renamed as message_template.rb.erb
[/code]

Now we just take the -n off of the command and rerun it. Now we can relist the files and see the changes:
[code]ls -al[/code]
[code]
-rwxr-xr-x 1 philip philip 218 2010-10-22 17:19 message_broadcaster.rb.erb
-rwxr-xr-x 1 philip philip 90 2010-10-22 17:19 message_group_criteria.rb.erb
-rwxr-xr-x 1 philip philip 2180 2010-10-22 17:19 message.rb.erb
-rwxr-xr-x 1 philip philip 193 2010-10-22 17:19 message_recipient_group.rb.erb
-rwxr-xr-x 1 philip philip 134 2010-10-22 17:19 message_recipient.rb.erb
-rwxr-xr-x 1 philip philip 427 2010-10-22 17:19 message_template_owner.rb.erb
-rwxr-xr-x 1 philip philip 661 2010-10-22 17:19 message_template.rb.erb
[/code]

Easy.

Symfony introspecting ERD generated SQL or Exporting ERD to YML directly?

We want to export Entity Relational Diagram (ERD) from MySQL Workbench to Symfony format in YAML or which is typically the same we want to end up building our Object Oriented Database Model.

Here is the ERD:

There are two approaches:

1. From ERD to YML conversion:

The plugin below is 2 years old now but it works well so far (2010):
http://trac.symfony-project.org/wiki/SymfonyYamlMyqlWorkbenchPlugin
The other problem I found with this old plugin is that is geared towards propel and not doctrine. It generated the yml from my ERD diagram which is what I wanted but for propel. It has a drawback, besides being old, it does not support something related to
i18. The path to save a file when generating the yml is relative to the user’s ~ directory. So DISCARDED.

Previous approach is different than this one here
http://github.com/johmue/mysql-workbench-schema-exporter
in that the latter is php code to generate the yml from the mwb file.
The same plugin has also a lua format that can be run from the Workbench program from the catalog plugin menu. However, the drawback of this method is that it is still experimental and not ready for production. It is better than the menu plugin in which is free to use php class code and that makes it neat to streamline into a work flow for symfony. I had to first fork the repository on github as it was read only. Then clone my copy of the repository with:
[code]
git clone git@github.com:cordoval/mysql-workbench-schema-exporter.git
[/code]
After that I run some tests and the only thing I needed to do is to:
[code]
php doctrine1.yaml.php > output.yml
pico output.yml (remove the html table tags that has at the beginning and at the end)
[/code]
The code seemed to be of pretty good quality.

Starting with the yml generated thus far, the commands that we need to issue to build our Object Oriented Database Model is:

[code]
symfony doctrine:build-model
[/code]

or

[code]
symfony doctrine:build-all
[/code]

2. From ERD to SQL and then from SQL to YML or direct Object Oriented Database Model conversion:

Workbench has a export function which generates what they call a SQL Forward Engineering Create Script for building database tables. Once we have generate this SQL script it seems that we can use symfony doctrine:build-schema using this SQL script. So starting from the ERD we can generate the SQL required to build this same Object Oriented Database Model. Symfony will introspect the database for us and do the job.

So after we have run the sql code into phpmyadmin we thereby create the database.
Once we have the database and no yml schema on symfony’s side we run the following command to get the yml file (config/doctrine/schema.yml):
[code]
./symfony doctrine:build-schema
>> doctrine generating yaml schema from database
>> doctrine Generate YAML schema successfully from database
[/code]

Now let’s turn to comparing the results between the plugin results and the symfony generate results from the SQL script.

This is a portion generated by introspection:
[code]
Affiliate:
connection: doctrine
tableName: Affiliate
columns:
id:
type: integer(4)
fixed: false
unsigned: false
primary: true
autoincrement: false
url:
type: string(45)
fixed: false
unsigned: false
primary: false
notnull: false
autoincrement: false
email:
type: string(45)
fixed: false
unsigned: false
primary: false
notnull: false
autoincrement: false
token:
type: string(45)
fixed: false
unsigned: false
primary: false
notnull: false
autoincrement: false
is_active:
type: string(45)
fixed: false
unsigned: false
primary: false
notnull: false
autoincrement: false
created_at:
type: string(45)
fixed: false
unsigned: false
primary: false
notnull: false
autoincrement: false
relations:
CategoryHas_Affiliate:
local: id
foreign: affiliate_id
type: many
[/code]

And this is a portion generated directly from the ERD:
[code]
Affiliate:
columns:
id:
type: integer(4)
primary: true
notnull: true
url:
type: string(45)
email:
type: string(45)
token:
type: string(45)
is_active:
type: string(45)
created_at:
type: string(45)
options:
charset: utf8
type: InnoDB
[/code]

And this is what the corresponding portion of yml by hand would look like:

[code]
JobeetAffiliate:
actAs: { Timestampable: ~ }
columns:
url: { type: string(255), notnull: true }
email: { type: string(255), notnull: true, unique: true }
token: { type: string(255), notnull: true }
is_active: { type: boolean, notnull: true, default: 0 }
relations:
JobeetCategories:
class: JobeetCategory
refClass: JobeetCategoryAffiliate
local: affiliate_id
foreign: category_id
foreignAlias: JobeetAffiliates
[/code]

My conclusion is to create the database from an ERD and then create the yml file.
Reason is that some of the relations are missing from the script generated as you can see by comparing the outputs above.