Disable Foreign Key Check Under MySQL

Mysql::Error: #23000Cannot add or update a child row: a foreign key constraint fails
(`depot_development/line_items`, 
CONSTRAINT `fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
ON DELETE CASCADE): INSERT INTO line_items (`quantity`, `product_id`, `id`,
`unit_price`) VALUES (1, 1, 1, 29.95)

OR

ActiveRecord::StatementInvalid: Mysql::Error: 
Cannot delete or update a parent row: a foreign key constraint fails: 
DELETE FROM line_items WHERE id = 1

If you've ever gotten the above error message from unit testing with Rails, this is what you gotta do.

Turn off constraints when fixtures are loaded to run unit tests by adding the following to the end of test_helper.rb:

class Fixtures
  def delete_existing_fixtures_with_fk_checks_disabled
    @connection.update "SET FOREIGN_KEY_CHECKS = 0" 
    delete_existing_fixtures_without_fk_checks_disabled
    @connection.update "SET FOREIGN_KEY_CHECKS = 1" 
  end

  def insert_fixtures_with_fk_checks_disabled
    @connection.update "SET FOREIGN_KEY_CHECKS = 0" 
    insert_fixtures_without_fk_checks_disabled
    @connection.update "SET FOREIGN_KEY_CHECKS = 1" 
  end

  alias_method_chain :delete_existing_fixtures, :fk_checks_disabled unless method_defined?(:delete_existing_fixtures_without_fk_checks_disabled)
  alias_method_chain :insert_fixtures, :fk_checks_disabled unless method_defined?(:insert_fixtures_without_fk_checks_disabled)
end

Read more about it here.

Testing Stage

Seems like I've pretty much finished my coding. Perhaps a little bit of tweaking now and there.

Will begin to write up testing reports and fix any bugs that arises. Also commenting all of my code as I go along.

Marko and I will be integrating our code together tomorrow as he needs my user function and also my stylesheet.

8 days to go...

Gruff Graph Customisation

I've just managed to get my graph customisation up and running. You can now graph three different types of graph: pie, stackedbar and sidestackedbar.



The variables that you can customise are the width of the image in pixels, (automatically sets it to 4:3 ratio), graphing by project or job, data breakdown and specified flow.



Not all of these customisations are available to all graphs but the javascript control handles what the user can select. Different graphs work differently.

The way a pie graph handles data is by accepting a number, attached with the data identifier.

# Example of adding data to a pie graph.
g.data("Data Identifier", 50)

The way a sidestackedbar/stackedbar graph handles data is by accepting an array of values, attached with the data identifier.

# Example of adding data to a sidestackedbar/stackedbar graph.
g.data("Data Identifier", [1, 2, 5])

Of course there are so many other customisations that can be implemented but I don't think I have time to implement them. This will do for now.

For Gruff API reference, click here. Gruff samples can also be seen here.

Syntax Highlighter

I've just installed Google's Syntax Highlighter. This javascript allows you to highlight codes without formatting it manually.

To use, simply do

<pre name="code" class="language">
...some code here...
</pre>

A wiki of usage can be found here.

NOTE: One important thing to watch out for is opening triangular bracket <. It must be replaced with an HTML equivalent of < in all cases. Failure to do won't break the page, but might break the source code displayed.

Here's a list of supported languages and their aliases:

Language	Aliases
C++	cpp, c, c++
C#	c#, c-sharp, csharp
CSS	css
Delphi	delphi, pascal
Java	java
Java Script	js, jscript, javascript
PHP	php
Python	py, python
Ruby	rb, ruby, rails, ror
Sql	sql
VB	vb, vb.net
XML/HTML	xml, html, xhtml, xslt

Converting binary data to image in HTML

Let's say you were making an Ajax request for an image, and the response is a base64 encoded string which you want to use to create an image in HTML. A way of doing so is to use "data:image/png;base64," at the start of your image source, and append the encoded string.

For example,

<img='data:image/<image format>;base64,<encoded string>' />

This will correctly render the encoded string into an image that can be displayed on the browser.

Using Gruff to create graphs, the Gruff::Base has a method called to_blob which draws the graph and returns the graph as a rendered binary. Simply return a base64 encoded version of it to the Ajax call to be processed.

# Controller
g = Gruff::Pie.new(300)
.
.
.

render :text => encode64(g.to_blob)

Creating hover menus and closing them with Javascript + Prototype

First of all I created an empty div that will be used to contain my menu contents. For example,

<div id="accesslist" class="accessmenu">Hello there!</div>

With CSS,

div.accessmenu {
    position: absolute;
    background: #F5F5F5;
    border: 1px solid #888;
    margin: 0;
    padding: 3px;
    visibility: hidden;
}

Now it's up to you how you want to position the menu, but this is how I did it with Javascript.

$('accesslist').style.left = Position.positionedOffset(el)[0];
$('accesslist').style.top = Position.positionedOffset(el)[1] + el.offsetHeight;
$('accesslist').style.zIndex = 10;
$('accesslist').style.visibility = 'visible';

The code above will place the div directly below element 'el'. Now the problem is how do you close the menu when you click away from it? A simple solution can be found using prototype's Event class.

Event.observe(document.body, 'click', function(e) {
    if (!Element.descendantOf(Event.element(e), $('accesslist')))
        $('accesslist').style.visibility = 'hidden';
});

What this code does is that it observes 'click' events that occur on the document.body. The third function parameter checks if the event click happened on my menu element's descendants (i.e. down the tree hierarchy). If not, then close the menu.

Simple.

Foreign Key in MySQL Error #1452 and #1216

This has caused me 3-4 hours of time wasting and it is so bloody simple! I can't believe after googling for so many hours that I could not find something that could solve this problem for me.

If you've came across an error #1452 when trying to add a foreign key and you have existing entries in the table, you need to make sure there are valid values in all entries in the foreign key column before creating a foreign key.

So what I did was do a zero fill on the foreign key column. Sweet I thought. This should fix it. After trying to create the foreign key again, I get an error #1216. Turns out that the values in the foreign key column must be valid (i.e. correspond to a valid id to referenced table) and the zero fill obviously didn't help because I dont have any id with 0.

Once you've entered valid values in, the foreign key creation should work! Make sure that the column in the referenced table is a primary key or an index and foreign key column is an index before proceeding.

Progress Update

Have been working on budgeting functionalities this past couple of weeks. Seems like I've managed to get these things working though it may require further testing.

1. Transactions - Add/Edit/Delete (admin, member, guest)
2. Jobs - Add/Edit/Delete (admin) [job description might still need some work]
3. Projects - Add (all) [need to do edit and delete]
4. Members - Add/Delete (admin) [need to do permission editing]

All this is done using javascript and ajax. A lot of fiddling around but its getting there.

Tax function

Functionality for the tax function is complete. Graphing and reporting is coming to completion:

ImageMagick / RMagick / Gruff - Installation

Installing RMagick is known to be a complex procedure, especially with an InstantRails environment. The first step is to download the RMagick-win32 package from the following location:

http://rubyforge.org/frs/download.php/24863/RMagick-1.14.1_IM-6.3.0-7-Q8-2.zip

Very nice...

The standard procedure, found in the file called README.html in the above archive, should generally be followed. However, the following modifications will need to be inserted for the installation to work with the InstantRails environment and to obtain the functionality required for FoR:

--------------------------------------
set HTTP_PROXY=http://proxy:port #(check for proxy and port in firefox)

gem update --system -y

gem install rmagick --local

gem install gruff -y


add to environment.rb -> require 'rubygems'

#also add "require 'RMagick'" to necessary controllers

#also add "require 'gruff'" in the controllers which are going to create gruff graphs
--------------------------------------

The following links may also be of use during installation:

http://docs.rubygems.org/read/chapter/3#page70
http://nubyonrails.com/pages/gruff
http://geoffreygrosenbach.com/projects/show/5

Let's play with Sortables

Ok, this seriously took me such a long time to figure out. I'm trying to create sortable menu customisation, like the one you see in your personalised Google homepage. The tutorial I followed was the one provided by scrtip.aculo.us.

First of all, you have to create a block container. In this case I used a div element. Its child div elements are the ones that I want to move around, and sort according to the user's preference.

For example,

<div id="container">
<div id="left" style="float:left;width:50%;background:red;">Left Div</div>
<div id="right" style="float:right;width:50%;background:green;">Right Div</div>
</div>

Left Div

Right Div

This displays the two child divs next to each other, like a table format but without using table tags. Using Rails helper,

<%= sortable_element "content_container", :tag => "DIV", :constraint => false %>

This specifies that the sortable list is the container which has lists in it. By default, the li tag is treated as the list, that is why you have to specify :tag => "DIV". The :constraint => false all allows free movement when dragging (default is vertical).

After doing so, you'd think that it should all work perfectly. Not really to be exact. This bug caused me lots of trouble to figure out as I had no idea why it wasn't working. You would be able to drag the elements, but it just would not sort.

Fiddling around for some time, I came across what the problem was. When the right div is sorted to the left, the css tells it to float right meaning the css is preventing the sorting from happening. In other words, when it is in the left position, it wants to float right. Obviously this is incorrect.

So I changed the right div to float left, which will still keep the alignment correct. This solved the problem pretty much. However, you are only able to sort the elements from right to left, and not the other way around. I still can't figured out why and how to fix this.

Try it out here,

Left Div

Right Div

[edit date="18/09/07"]

Hmm, a bloody weird discovery. Left to right actually works! It isn't as responsive as right to left though. Left to right is harder to sort, but if you position it kinda properly, it works.

Weird.

[/edit]

Rendering Global Partials

Just a quick note to point out. If you want to render a partial thats available globally (by default its local), all you have to do is create a folder in ..\app\views.

Assuming we created a folder called shared and a partial called _menu.rhtml, you can then access it by doing,
render :partial => "shared/menu"

Simple.

Dealing with JavaScript Object Notation (JSON)

Ruby on Rails provides an easy way to render JavaScript Object Notation (JSON) without actually having to deal with itty gritty details of forming the object literal.

# Controller
render :json => { :name => "Danny", :title => "Mr" }.to_json

produces

{ name: 'Danny', title: 'Mr' }

Once you have done so, you can create an object in javascript by running

var person = eval("(" + request.responseText) + ")");

You can then access the attributes by going person.name and person.title which returns "Danny" and "Mr", as expected.

Thesis Update!

It's been a busy past few weeks, getting familiar with the prototype and scriptaculous javascript framework but it has paid off. Been looking into user interface design where there can be more interaction with the user.

What I've been trying to do is to try and simulate a 'desk' environment, where you would normally use your hands to move things around depending on what you want to do. What that means is to make things draggable, collapsible, expandable etc.

As you can see below, I've added drop down menus for menu selection. I've also made the right hand navigation bar draggable, as well as the main content in the middle. Can't really see it from a picture I guess. The main content and navigation bars are also expandable and collapsible.
I've also rethought the design of performing actions that does not require a lot of data loading. In other words, utilizing AJAX to perform actions without really leaving the page which means no refresh.

Created a hovering div that disables the content below, and displays the content above it depending on what type of action it is. I used scriptaculous' effects (Fade and Switchoff) to display and close the hover div.

I've only tested this on a Firefox environment as the browser has great plugin tools (Firebug) which makes debugging JavaScript, HTML and CSS a walk in the park.





Over the next couple of weeks I plan to add drag & drop features which will allow more user interaction by using the mouse which in return, simulates using a hand to do stuff. Hopefully there will be minimal user input where it isn't necessary.

Hopefully I'll have the chance to post up how I did all these things soon.

JavaScript Validation

I've spent some time writing a validation framework for checking user text field inputs on the client-side.

The parameters it takes are:
function(valField, type, options) where valField and type are required. valField is the element to validate, and type is a choice of ["required", "exists", "update", "password"].
Options is optional and is an object. In the options object, it takes these attributes:

infoField: element - the element to display messages to the user,
repeatField: element - the element which is used to repeat (like repeat password or email),
required: boolean - specifies whether input is required (defaults to true),
ajax: boolean - specifies whether Ajax is used to communicate to the server (defaults to false),
url: string - url for Ajax call (only works if ajax: true),
column: string - the column in the table (only works if ajax: true),
idkey: string - the id parameter (only works if ajax: true)

Examples of different types of usage:

• new Validator(this, 'exists', { infoField: $('project_name_span'), ajax: true, idkey: $('user_id').value , column: 'name', url: '/project/check_exists' });
• new Validator(this, 'required', { infoField: $('project_name_span') });
• new Validator(this, 'update', { infoField: $('user_lname_span'), ajax: true, column: 'lname', idkey: $('user_id').value, url: '/user/ajax_update' });
• new Validator(this, 'password', { infoField: $('user_pwd_span') });
  

Updated Database Design

As promised.

Table Definition

users

Column Name	Data Type	Constraints
id	integer	Primary Key
handle	varchar(10)	Unique Key
fname	varchar(15)	NOT NULL
lname	varchar(15)	NOT NULL
password_hash	varchar(128)	NOT NULL
password_salt	varchar(8)	NOT NULL
email	varchar(45)	NOT NULL

handle – name of the account

fname – first name of user

lname – last name of user

password_hash – SHA512 digest of user’s plaintext password

password_salt – 8 bit randomly generated salt

email – email of user

members

Column Name	Data Type	Constraints
id	integer	Primary Key
project_id	integer	Foreign Key
user_id	integer	Foreign Key
permission	varchar(10)	NOT NULL, Default “guest”

id – primary key

project_id – project the member belongs to

user_id – user that has access to the project

permission – the permission level (admin, member, guest)

projects

Column Name	Data Type	Constraints
id	integer	Primary Key
name	varchar(30)	Primary Key
description	varchar(100)	NOT NULL, Default “”

id – primary key

name – name of the project

description – description of the project

jobs

Column Name	Data Type	Constraints
id	integer	Primary Key
project_id	integer	Foreign Key
name	varchar(30)	NOT NULL
description	varchar(100)	NOT NULL, Default “”

id – primary key

project_id – name of the project it belongs to

name – name of the job

description – description of the job

etransactions

Column Name	Data Type	Constraints
id	integer	Primary Key
date	date	NOT NULL, Default Timestamp
job_id	integer	Foreign Key
counterparty	varchar(30)	NOT NULL
category	varchar(30)	NOT NULL, Default “Other”
flow	varchar(10)	NOT NULL
created_by	integer	Foreign Key
description	varchar(100)	Default “”
amount	decimal(9,2)	NOT NULL, Unsigned

id – primary key

date – date of the transaction

job_id – the job which the transaction belongs to

counterparty – the transaction made to

category – the category of the transaction

flow – inflow or outflow

created_by – the user who created the transaction

description – description of the transaction

amount – the amount of the transaction

Bug #2: Problem with updating user object

The bug was found in the model side as I was trying to update the user object by doing:

@user = User.update(params[:id], {
  :fname => params[:user][:fname],
  :lname => params[:user][:lname],
  :email => params[:user][:email] })

Whenever I tried to update, I'd get this error:

ArgumentError in UserController#update wrong number of arguments (2 for 4)

Could not figure out what was wrong with it from googling. So once again I decided to hop onto my trusty real-time help solution: irc. I got suggested to try this method of updating:

@user = User.find(params[:id])
@user.update_attributes({
:fname => params[:user][:fname],
:lname => params[:user][:lname],
:email => params[:user][:email]}) if @user != nil

This seemed to work perfectly. I still haven't figured out what was the problem with the other one. Perhaps its because I didn't provide the other two parameters that's in the table definition: password_hash and password_salt. I'm not sure.

Bug #1: AJAX checking in certain actions

So I came across a bug as I was testing my newly written code to check whether handles and usernames have already been registered in the database. This worked fine when I want to sign up. However, when I want to update a user's profile, it would not let me update my profile with my existing email because the email already exists in the database. Pretty obvious that there's a bug there.

So when I did my AJAX checking, I had to determine what type of action I was doing (i.e. signing up, updating...). I had a parameter called action which would be sent in the query string, telling the controller what kind of action I am doing. The problem is, whenever I did that, my code for some reason could not recognise what kind of action I'm performing. When I printed out params[:action], it gave me the controller's method name.

Out of frustration, I hopped onto irc to get some real-time help. Turns out that action was a reserved name used for routing purposes, which determines which method of the controller you're calling to. D'oh! Obviously I should've known this as my code is scattered with :action => "something".

So I changed the parameter name to actiontype and it worked perfectly!

Form validation with AJAX

Finally managed to utilise javascript and ajax to do client-side form checking. I am using javascript instead of rjs because I feel like I have more control over what I'm doing and also I'm more familiar with the javascript syntax. Since prototype framework is included as part of the javascript defaults in RoR, I will be using that framework too.

Its got some pretty handy things like accessing DOM elements using $('element id'), so you don't have to deal with document.getElementId etc. The framework also provides a cool Ajax object which handles all the nasty readystates that you will come across if you use the conventional way of doing Ajax.

Here is a snippet of the client-side code:

new Ajax.Request("/user/check_exists",
{
parameters: params + "=" + textField.value,
onLoading: showMsg(infoField, "<img src="../../images/ajax/ajax-loader.gif" border="0" /> Checking if available..."),
onSuccess: function(request) {
if (request.responseText)
{
showMsg(infoField, request.responseText);
textField.className = "fail";
textField.focus();
}
else
{
showMsg(infoField, "");
textField.className = "success";
}
}
});

Here is a snippet of the server-side code:

def check_exists
if params[:handle]
@handle_exists = User.find(:first, :conditions => [ "handle = ?", params[:handle] ])
if @handle_exists == nil
render :text => ""
else
render :text => "Username is taken. Please choose another."
end
elsif params[:email]
@email_exists = User.find(:first, :conditions => [ "email = ?", params[:email] ])
if @email_exists == nil
render :text => ""
else
render :text => "This email is registered. Please choose another."
end
end
end

Sorry about the indentations if they don't come out correctly. For some reason this current blogger layout strips any unnecessary whitespaces.

Here are some screen shots of what I've done today. You will notice I used a little CSS to make things look nicer.



[edit date="23-08-07"]

I decided to move the business logic into the model and leave the transaction logic in the controller. More elegant and scalable I reckon.

In the model:

def self.attribute_exists?(attribute, value)
@attribute_exists = User.find(:first, :conditions => [ attribute + " = ?", value ])
if @attribute_exists != nil
return @attribute_exists
else
return nil
end

In the controller:

def check_exists
if params[:actiontype] == "new"
if params[:handle]
@user = User.attribute_exists?("handle", params[:handle])
if @user == nil
render :text => ""
else
render :text => "Username is taken. Please choose another."
end
elsif params[:email]
@user = User.attribute_exists?("email", params[:email])
if @user == nil
render :text => ""
else
render :text => "This email is registered. Please choose another."
end
end
elsif params[:actiontype] = "edit"
@cur_user = User.find(params[:id])

if params[:handle]
@user = User.attribute_exists?("handle", params[:handle])
if @user == nil || @user == @cur_user
render :text => ""
else
render :text => "Username is taken. Please choose another."
end
elsif params[:email]
@user = User.attribute_exists?("email", params[:email])
if @user == nil || @user == @cur_user
render :text => ""
else
render :text => "This email is registered. Please choose another."
end
end
end
end

A general rule of thumb is that code that requires communication with the database should be placed in the model side instead of the controller. The controller's job is to handle the user's request, retrieve data from the model and provide a response if necessary. The view's job is to handle presentation layer (i.e. HTML & CSS) for viewing purposes (thus called the View).

[/edit]

Ruby on Rails and J2EE: Is there room for both?

An interesting article about the comparison of Ruby on Rails and J2EE. Both web technologies use the MVC (Model, View, Controller) framework.

You can read about it here.

authentication feature

Please enable a forgot password feature - probably generating a temporary password to be emailed to user.

auto-complete textfield

We have all seen the famous Google Suggestions which was one of the driving factors to the rapid growth of AJAX applications, using real-time auto-completion without refreshing your browser.

Ruby on Rails makes this easy by using script.aculo.us javascript framework. A simple ajax auto-complete field only consists of two lines (with reference to the MVC framework):

#View
<%= text_field_with_auto_complete :model, :attribute %>

#Controller
auto_complete_for :model, :attribute

Viola! You've got yourself a working auto-complete textfield.

authentication

Currently working on FoR authentication by following this tutorial. Most likely be using SHA1 for hashing the password to be stored/compared with the password on database. Something to note that SHA1's digest is 40 characters long.

I have significantly modified the database table design with respect to the budgeting function. Will post an update on it later.

[edit date="17-08-07"]

Have got the authentication working now. The authentication checking is done in application.rb where all controllers have access to. Controllers that need protection simply need to add:
>> before_filter :authenticate

You can also define which methods within the controller you want to exclude protection by adding this parameter to before_filter:
>> before_filter: authenticate, :except => [ :method1, :method2]

The code for the authentication is:

def authenticate
 unless @session[:user]
   @session[:return_to] = @request.request_uri
   @session[:intended_action] = action_name
   @session[:intended_controller ] = controller_name
   flash[:notice] = 'Login required.';
   redirect_to :action => "login",
                        :controller => "user_admin"
   return false
 end
end

I have a controller called user_admin which handles all the user administrations (login, logout, sign up, etc.). The actual authentication logic is inside the User model. I also did a bit of research and found out that SHA-0 and SHA-1 can be broken through collision attack. Therefore, I decided to use SHA-512 (512 bit output) to hash the password and salted with a randomly generated 32-bit salt.

The actual password and salt creation:

def password=(pass)
   salt = [Array.new(6){rand(256).chr}.join].pack("m").chomp
   self.password_salt, self.password_hash = salt, Digest::SHA512.hexdigest(pass + salt)
end

So when you create a new user, to store the password_hash and password_salt, simply do:
>> @user.password = password

where password is the user's plaintext password.

[/edit]

configuring environments

Just finished installing and configuring

• Instant Rails
• MySQL Tools
• Notepad++
• FileZilla
• Smart Draw

on both machines (2A, 2B) in the EIE 417 labs.

It seems like Instant Rails works well in Windows and that will be chosen platform to work on. Runs Mongrel behind Apache because apparently Apache is hard to configure and everything is done for you in Instant Rails.

Chose Notepad++ as the ruby editor as well because Aptana doesn't have colour for ruby code (or at least I can't figure out how to do configure it).

FileZilla will be used to transfer files for backup onto our diesel account.

Smart Draw will be used to draw software diagrams.

Installing Ruby on Rails

Just downloaded Ruby 1.8.5-24 and RubyGems 0.9.4 (Ruby package manager). Installation was pretty easy as I followed the instructions found here. I also installed the recommended editor Aptana RadRails.

Now just gotta figure out how it all works together...

Preliminary Database Design

Just spent a couple of hours with Marko doing a preliminary design of our database. Will look further into it and try to normalise it later on. Marko is meeting up with Rodica tomorrow to discuss about our computer/server arrangements. Most likely we'll be meeting up on Thursday to begin installation and configurations.

Database Design

User

Column Name	Data Type	Constraints
handle	varchar(10)	Primary Key
fname	varchar(15)	NOT NULL
lname	varchar(15)	NOT NULL
password	varchar(x)	NOT NULL
dob	date	NOT NULL

Transaction

Column Name	Data Type	Constraints
recipient	varchar(10)	Foreign Key
amount	money	NOT NULL, >= 0
receivedate	date	NOT NULL
source	varchar(20)	NOT NULL
revenuetype	varchar(30)	NOT NULL
paymenttype	varchar(10)	NOT NULL
transacttype	varchar(10)	NOT NULL

Loan

Column Name	Data Type	Constraints
debtor	varchar(10)	Foreign Key
principal	money	NOT NULL, > 0
interest	real	NOT NULL, > 0
lender	varchar(30)	NOT NULL
payfrequency	integer	NOT NULL, > 0

Tax

Column Name	Data Type	Constraints
recipient	varchar(10)	Foreign Key
income	money	NOT NULL
taxwitheld	money	NOT NULL
deductions	money	NOT NULL
carryforwardlosses	money	NOT NULL
taxableincome	money	NOT NULL
capitalgains	money	NOT NULL

Portfolio

Column Name	Data Type	Constraints
owner	varchar(10)	Foreign Key
tickersymbol	varchar(5)	NOT NULL
quantity	integer	NOT NULL
totalvalue	money	NOT NULL
company	varchar(30)	NOT NULL
securitytype	varchar(20)	NOT NULL