View
Repeats

Use find_in_batches to process many records without tearing down the server

Occasionally you need to do something directly on the server – like having all records recalculate something that cannot be done in a migration because it takes a long time.

Let's say you do something like this:

Project.all.each(&:recalculate_statistics!)

Even though you may have been successful with this on your development machine or the staging server, keep in mind that production machines often hold a lot more records. Using all may just work, even with lots of records, but when you iterate over such records and fetch associati…

Repeats

IIFEs in Coffeescript

In JavaScript we often use Immediately Invoked Function Expessions (or IIFEs) to prevent local variables from bleeding into an outside scope:

(function() {
  var foo = "value"; // foo is scoped to this IIFE
})();

In Coffeescript an IIFE looks like this:

(->
  foo = "value" # foo is scoped to this IIFE
)()

There is also a shorthand syntax with do:

do ->
  foo = "value" # foo is scoped to this IIFE

You can also use do with arguments t…

Repeats

Testing ActiveRecord callbacks with RSpec

Our preferred way of testing ActiveRecord is to simply create/update/destroy the record and then check if the expected behavior has happened.

We used to bend over backwards to avoid touching the database for this. For this we used a lot of stubbing and tricks like it_should_run_callbacks.

Today we would rather make a few database queries than have a fragile test full of stubs.

Example

Let's say your User model creates a first Project on cr…

Geordi 1.9 released

New features:

geordi delete_dumps [directory]

Recursively search for files ending in *.dump and offer to delete those. When no argument is given, two default directories are searched for dump files: the current working directory and ~/dumps (for dumps created with geordi).

geordi drop_databases

Delete local MySQL/MariaDB and Postgres databases that are not whitelisted.

Authentication is handled via PAM for Postgres and MariaDB, via .my.cnf with fallback to mysql -p for MySQL. Different connection methods can be chosen via …

Store MySQL passwords for development

On your local system that only hosts non-critical development data and only you have access to, you can store MySQL's root password in your home directory so you can use mysql commands without prompt for passwords, i.e. when doing batch processing.

Of course, this has security implications. The password must be stored in plain text, so this method is out of the question if there's any confidential data in your databases. Assuming diligent screen-locking, an encrypted hard disk and correct permissions set on your credential file, it shoul…

Repeats

FileIO: Writing strings as Carrierwave uploads

When you have string contents (e.g. a generated binary stream, or data from a remote source) that you want to store as a file using Carrierwave, here is a simple solution.

While you could write your string to a file and pass that file to Carrierwave, why even bother? You already have your string (or stream).
However, a plain StringIO object will not work for Carrierwave's ActiveRecord integration:

>> Attachment.create!(file: StringIO.new(contents))
TypeError: no implicit conversion of nil into String

This is because Carrierwave ex…

PostgreSQL: How to show table sizes

When you have a large PG database, you may want to find out which tables are consuming the most disk space.
You can easily check this using the following SQL statement from the PostgreSQL wiki.

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_tot...
External content

User-defined Order in SQL

The attached article explains options you have to store the order of items in a database table.

The simplest solution of course is to use a position column. However the author explores some alternatives where you don't need to update multiple rows when you move a single item.

Repeats

Don't sum up columns with + in a SQL query if NULL-values can be present.

Don't sum up columns with + in a sql-query if NULL-Values can be present.

MySQL and PostgreSQL cannot sum up NULL values with the + value. The sum value will be NULL.

MySQL:

mysql> select 1 + 2 + 3;
+-----------+
| 1 + 2 + 3 |
+-----------+
|         6 |
+-----------+
1 row in set (0,00 sec)

mysql> select 1 + NULL + 3;
+--------------+
| 1 + NULL + 3 |
+--------------+
|         NULL |
+--------------+
1 row in set (0,00 sec)

Postgres:

```

test_database=# select 1 + 2 + 3;

?column?

    6 (1 row)

t…

Running Rails 2 apps with modern MariaDB SQL server

You might have some trouble running a Rails LTS 2 app with MySQL 5.7.

If you don't want to hack Mysql 5.6 into your modern Ubuntu or use the MySQL sandbox, you might want to try MariaDB 10.x.

MariaDB 10.x should work with both old and new Rails applications.

Switch to MariaDB

Remove MySQL:

```
sudo apt r…

Repeats

Common mistakes when storing file uploads with Rails

1. Saving files to a directory that is not shared between deploys or servers

If you save your uploads to a made up directory like "RAILS_ROOT/uploads", this directory goes away after every deploy (since every release gets a new). Also this directory is not shared between multiple application servers, so your uploads are randomly saved to one local filesystem or another. Fixing this afterwards is a lot of fun.

Only two folders are, by default, shared between our application servers and deployments: "RAILS_ROOT/storage" and `"RAILS…

Repeats

MySQL: Disable query cache for database profiling

If you want to see how long your database queries actually take, you need to disable MySQL's query cache. This can be done globally by logging into a database console, run

SET GLOBAL query_cache_type=OFF;

and restart your rails server.

You can also disable the cache on a per query basis by saying

SELECT SQL_NO_CACHE * FROM ...

You also probably want to disable Rails internal (per-request) cache. For this, wrap your code with a call to ActiveRecord::Base.uncached. For example, as an around_filter:

def disable_cache  ...
Repeats

Don't compare datetimes with date ranges in MySQL

When selecting records in a date range, take care not to do it like this:

start_date = Date.parse('2007-05-01')
end_date = Date.parse('2007-05-31')
LogItem.where(:created_at => start_date .. end_date)

The problem is that created_at is a datetime (or Time in Ruby), while start_date and end_date are simple dates. In order to make sense of your query, MySQL will cast your dates to datetimes where the time component is 00:00:00. Because of this the query above will lose records created from 2007-05-31 00:00:01 t…

Repeats

How DECIMAL columns deal with numbers exceeding their precision or scale

When storing floating-point numbers such as prices or totals in an SQL database, always use a DECIMAL column. Never use FLOAT or kittens will die.

DECIMAL columns are parametrized with a precision and a scale. These parameters describe which numbers can be stored in that column. E.g. a decimal with a precision of 5 and a scale of 2 can store numbers from -999.99 to 999.99, but not 1000 or 1.234.

This card explains what various databases do when you try to store a number in a DECIMAL field, and that number exceeds that colum…

Auto-destruct in 24 days

Unpoly 0.54.0 released

There were a number of Unpoly releases addressing issues that we encountered in our current development projects:

0.54.0

Passive updates

  • [up-hungry] elements will now also be updated when the server responds with an error code. This helps when [up-hungry] is used to display error messages.

Forms

  • When a form is submitted you can now consistently refer to that form element as ampersand (&) in CSS selectors ([like in Sass](…
Repeats

Understanding SQL compatibility modes in MySQL and MariaDB

MySQL and MariaDB have an SQL mode setting which changes how MySQL behaves.

The SQL mode value is comprised of multiple flags like "STRICT_TRANS_TABLES, NO_ZERO_IN_DATE". Each flag activates or disables a particular behavior.

The default SQL mode varies widly between versions of MySQL and MySQL. In general, more recent versions of MySQL and MariaDB have stricter settings than older versions, and MySQL has stricter settings than the more liberal MariaDB.

If your app explodes af…

Repeats

Don't assign time values to date attributes

Do not pass times to date attributes. Always convert times to dates when your application uses time zones.

Background

A time-zoned Time attribute on a Rails record is converted to UTC using to_s(:db) to be stored, and converted back into the correct time zone when the record is loaded from the database. So when you are not on UTC, time objects will be converted as follows.

>> Time.current
=> Fri, 15 Mar 2013 11:56:03 CET +01:00
>> Time.current.to_s(:db)
=> "2013-03-15 10:56:03" # This is now UTC

Problem

That will…

Auto-destruct in 20 days

Spreewald 1.11.0 console step starts irb in execution context

You can now e.g. run steps within the irb console.

Example:

You want to debug why your fill in step is not working. Add the console step before this step.

When I select "Rejection" from "Decision"
Then console
Then I fill in "Comment" with "Some comment"

Run the test, open the vnc with geordi and try to reproduce the error step by step:

```
# This is new: You can now access step, page, with_scope and others.

irb> step('Then I fill in "Comment" with "Some comment"')
irb> fill_in("Comment", :with => "Some comment") …
`…

View