Simple bash shell script for running batch MySQL jobs
The other day I needed to run a simple mysql job to backup and delete some database records on a live server. Being a live server, it is important to make sure you aren’t asking the database to take on jobs that could potentially lock it up. Better to run a batch job. Running a batch is simple. You can call it right from the mysql console with:
source [path_to]/[the_batch_script].sql
But what if there are millions of records that need deleting? Bash shell script to the rescue.
Here is the idea of the SQL job that needed to get run a few times:
START TRANSACTION;
/* Find what you want to delete and put a LIMIT on your batch size */
CREATE TEMPORARY TABLE records_to_delete_temp SELECT id from `records` where ..... limit 1000;
/* Creating backup table to archive spam orders */
CREATE TABLE IF NOT EXISTS `records_backup` LIKE `records`;
INSERT INTO `records_backup` SELECT * from `records` where id in (select id from `records_to_delete_temp`);
/* Delete Dependents - If your records have foreign key dependencies, delete them first */
DELETE FROM `dependent_1` where record_id in (select id from `records_to_delete_temp`);
DELETE FROM `dependent_2` where record_id in (select id …
shell mysql
How to pick a Ruby gem
RubyGems are one of the big benefits of developing in the Ruby environment as they can provide you with a powerful set of building blocks that were created by some great developers. Earlier in my Ruby career I used to think of RubyGems as a quick way to get some “free” code into my applications and I would spend a tremendous amount of time trying to see what kind of apps I could concoct by stacking gem on top of gem. In practice this turned out to be foolish because rather than gaining a stack of “free” code what I was instead doing was “paying” for each gem by having to learn how each of these gems worked and what kind of assumptions and gotchas they were bringing into my apps. I changed my ideas about gems and now I opt by default to avoid adding gems to my projects, but when I do decide that a piece of functionality might be better served through a gem, I make sure to put potential candidates through a rigorous vetting process.
When looking for a gem the question I keep in mind is, “Does adding this gem to my project benefit me more than just writing these features by hand?” I measure the gem up against some criteria and if the …
ruby rails
Don't Sleep on Rails 3 SQL Injection Vulnerabilities
SQL injection is a problem that every web developer needs to be aware of when accepting parameters that will during the life of the request be converted into SQL statements. Rails historically has done what it can to mitigate this risk for the developer by providing vehicles for sanitizing parameter inputs at the points when they are being converted for use inside of a SQL statement, however with Rails 3 there are numerous ways to execute a SQL statement against the database and some of these methods are safer than others.
Consider two cases where valid Rails code is vulnerable to SQL injection:
#user inputed parameters
params[:query] = "'robert'; DROP TABLE students; ##"
#CASE 1 - find_by_sql
User.find_by_sql("SELECT * FROM users WHERE (name = '#{params[:query]}'") ##(BAD BAD BAD)
#generated SQL
SELECT `users`.* FROM `users` WHERE (email = 'Robert'); DROP TABLE STUDENTS; ##') ##(THIS STATEMENT WILL DROP TABLE STUDENTS)
The example above shows how find_by_sql can allow parameters submitted by a user to be directly entered into a SQL statement and how an attacker might use the vulnerability to wreak havoc. These types of find_by_sql …
ruby rails
Feature Isolation, an Overview
Yesterday, Brian Buchalter blogged about a recent presentation I did for End Point Rails developers.
While the blog article did a great job of capturing some of the nitty gritty detail from the presentation, I’d like to just followup with a quick overview statement about Feature Isolation. I’ve also made my slides available for anyone who is interested.
Feature Isolation is what I’m calling a development strategy for adding new features to existing applications. In Rails, I’m utilizing cucumber, a tool for transforming use-case language into ruby code, to specify the requirements and then execute them outside of the Rails environment and away from the complexity of the rest of the application.
Using stubbing and a minimal mock of ActiveRecord (FastModel) I can then begin to design my feature from a more object oriented approach than is typical in Rails development. I can bring in any models, new or existing, that I will need and stub out the interface to the database. Likewise, I can design my classes and their public interface. Getting all my tests to pass from a high level without actually developing the behavior itself allows me to make design decisions …
ruby rails testing
Feature Isolation with Mike Farmer
My brilliant co-worker Mike Farmer gave a presentation today talking about a development strategy he calls “Feature Isolation.” It involves developing new features on the fringe of your application, isolating it from the complexity of existing code. This allows greater focus on ensuring that your feature is well designed from an object-oriented perspective and that you don’t build more than you need.
In order to truly isolate the feature, Mike put together some cucumber tools to allow you to run cucumber without Rails and to create what he calls a “FastModel”. The models are fast for two reasons. First, you don’t need to load ActiveRecord to get functionality like specifying field names, specifying relationships, or emulating saving records. Second, it let’s you to sketch out a design for your class while the cost of change is very very low.
An Example: Product Variants
Here’s an example of a tight little feature and step set for showing shoppers a comparison of product variants.
Feature: As a shopper, I want to compare the variants of a product
Background:
Given there is a product named "Product A"
And it has some …
rails testing
Piggybak: The Roadmap
Over the last couple of weeks, a few of us at End Point have had some discussion about the future direction of Piggybak. Piggybak is an open source mountable ecommerce framework written in Ruby on Rails supported and developed by End Point. It introduces core ecommerce functionality into a Rails application, but is intended to allow the mounted Rails application to maintain control over some architecture elements.
Pros of Piggybak
Until now, the advantage of Piggybak is that it’s a fairly lightweight approach. It leverages the power of RailsAdmin rather than creating it’s own admin. It allows the mounted Rails application to make decisions on what types of items are sellable and how these items are found (i.e. product finding methods, SSL configuration). Piggybak also has streamlined integration of ActiveMerchant, which immediately provides support of over 40 popular payment gateways. Piggybak has a cookie-based cart and an AJAX-driven one-page checkout.
Cons of Piggybak Approach
Because Piggybak has a lightweight approach, the major disadvantage is that it cannot compete with existing ecommerce frameworks as an out of the box solution with a full ecommerce feature set. …
ecommerce piggybak ruby rails
The Real Cost of Data Roundtrip
Sometimes you need to perform some heavy database operations. I don’t know why very often programmers are afraid of using databases for that. They usually have some fancy ORM which performs all the operations, and the only way to change the data is to make some SELECT * from a table, create a bunch of unneeded objects, change one field, convert those changed objects into queries and send that to the database.
Have you ever thought about the cost of the roundtrip of data? The cost of getting all the data from database just to send changed data into the database? Why do that if there would be much faster way of achieving the same results?
Imagine that you have quite a heavy operation. Let’s make something which normally databases cannot do, some more complicated operation. Many programmers just don’t know that there is any other way than writing this in the application code. Let’s change all the HTML entities into real characters.
The HTML entities are a way of writing many different characters in HTML. This way you can write for instance the Euro currency sign “€” in HTML even if you don’t have it on your keyboard. You just have to write € …
database optimization postgres
Setting user ownership of nginx and Passenger processes
Do this now on all your production Rails app servers:
ps ux | grep Rails
The first column in the results of that command show which user runs your Rails and Passenger processes. If this is a privileged user (sudoer, or worse yet password-less sudoer), then this article is for you.
Assumptions Check
There are several different strategies for modifying which user your Rails app runs as. By default the owner of config/environment.rb is the user which Passenger will run your application as. For some, simply changing the ownership of this file is sufficient, but in some cases, we may want to force Passenger to always use a particular user.
This article assumes you are running nginx compiled with Passenger support and that you have configured an unprivileged user named rails-app. This configuration has been tested with nginx version 0.7.67 and Passenger version 2.2.15. (Dated I know, but now that you can’t find the docs for these old versions, this article is extra helpful.)
Modifying nginx.conf
The changes required in nginx are very straight forward.
# Added in the main, top-level section
user rails-app;
# Added in the appropriate http section among your other Passenger related …
hosting rails security