Losing motivation

02 Mar 2019

Write software well

28 Feb 2019

Dependency injection is not a virtue

27 Feb 2019

In languages less open than Ruby, hard-coded class references can make testing tough. If your Java code has Date date = new Date();` buried in its guts, how do you set it to a known value you can then compare against in your tests? Well, you don’t. So what you do instead is pass in the date as part of the parameters to your method. You inject the dependency on Date. Yay, testable code!

As has unfortunately happened with a variety of patterns that originate from rigid languages like Java, Dependency Injection has spread and been advocated as a cross-language best practice on trumped up benefits of flexibility and malleability. If your code never knows exactly who it’s talking to, it can talk to anyone! Testing stubs, mocks, and future collaborators. Hogwash.

Primay keys: UUID vs bigint – or both?

22 Feb 2019

Some tables in the project I’m working on have UUIDs as primary keys others are indexed by bigint. To have both wasn’t a conscious decision. I began by choosing UUIDs for some tables because I expected I’d need to expose those ids in the URLs. I intended to use UUIDs throughout the model but I forgot and ended up with some tables being keyed by integers.

I wondered if it was a bad idea to have primary keys of different types, and I came across this post. There’s plenty to take in and I’ll need to read it a few more times to fully understand all that the author is expressing, but my question of whether or not it’s a good idea to mix primary key types is answered by the author in the comments in which he states the rule:

“Normalize first, de-normalize only when needed.”

I don’t yet have a need to de-normalize. It might not matter for me yet – not at the scale I’m working at, at least – but I’m about to implement a model that has a polymorphic association where the foreign key might point to either a UUID or bigint. I think that will be a problem.

And then there’s the question of which I should normalize to. My initial assumption about UUIDs exposed in a URL being more secure might be misplaced. “Using a PK in a URL is generally a bad practice.”

Polymorphic associations

21 Feb 2019

A model can associate with other models by way of a polymorphic association. This can be useful when the associated models are not of the same class but share a common interface, or when a model shares the same type of relationship with multiple other models.

For example, three models Post, Project, and Issue may have many comments.

Instead of creating a different comment model for each of these three models (PostComment, ProjectComment…) it would be much more straightforward to have a single Comment model and establish a relationship to a subject.

create_table :comments do |t|
  t.text :body
  t.references :subject, polymorhic: true

The subject could be a Post, Project, Issue or any other model we wish to attach a comment to.

The polymorphic option, instructs the database to store the associated object’s type. The above migration creates a :subject_type column.

The relationships are established in the models like so:

class Comment < ApplicationRecord
  belongs_to :subject, polymorphic: true
end

class Post < ApplicationRecord
  has_many :comments, as: subject
end

class Project < ApplicationRecord
  has_many :comments, as: subject
end

class Issue < ApplicationRecord
  has_many :comments, as: subject
end

UUID as column type in create_join_table

21 Feb 2019

via BigBinary

Rails 5 has started supporting UUID as a column type for primary key, so create_join_table should also support UUID as a column type instead of only integers.

class CreateJoinTableCustomerProduct < ActiveRecord::Migration[5.0]
  def change
    create_join_table(:customers, :products, column_options: {type: :uuid})
  end
end

Avoid the three-state boolean problem

16 Feb 2019

via thoughtbot

The NOT NULL constraint means that this migration will fail if we have existing users, because Postgres doesn’t know what to set the column values to other than NULL. We get around this by adding a default value:

add_column :users, :admin, :boolean, null: false, default: false

Rails SQL Injection

01 Feb 2019

https://rails-sqli.org

This page lists many query methods and options in ActiveRecord which do not sanitize raw SQL arguments and are not intended to be called with unsafe user input. Careless use of these methods can open up code to SQL Injection exploits. The examples here do not include SQL injection from known CVEs and are not vulnerabilites themselves, only potential misuses of the methods.

SQL Notes

25 Jan 2019

SQL keywords are capatalized. SQL statements are terminated with a ;.

`SELECT`

Select all columns from a table:

SELECT * FROM Authors;

Select a single column from a table:

SELECT LastName FROM Authors;

Retrieve a list of all different values in a column:

SELECT DISTINCT LastName FROM Authors;

`WHERE`

Retrieve row where column matches:

SELECT * FROM Authors WHERE Language = 'English';

Retrieve a row where the column does not match:

SELECT * FROM Authors WHERE NOT Language = 'English';

Multiple conditions where all must match:

SELECT * FROM Authors
WHERE Language = 'English'
AND LastName = 'Hardinge';

Multiple conditions where either must match:

SELECT * FROM Authors
WHERE Language = 'English'
OR LastName = 'Garner';

`ORDER BY`

Order the result:

SELECT * FROM Authors ORDER BY LastName;

Return the result in reverse order:

SELECT * FROM Authors ORDER BY LastName DESC;

Order the result alphabetically by last name then language:

SELECT * FROM Authors ORDER BY LastName, Language;

`INSERT`

Insert a new row into a table:

INSERT INTO Authors (
    LastName,
    Language
)
VALUES (
    'Jansson',
    'Swedish'
);

`NULL`

Select a row with an empty column:

SELECT * FROM Authors WHERE DateOfDeath IS NULL;

Select a row where a specified column is not empty:

SELECT * FROM Authors WHERE DateOfDeath IS NOT Null;

`UPDATE`

Update a column in all rows:

UPDATE Authors SET Published = 'true';

Update only specific columns (I’m stretching the example a bit now):

UPDATE Authors SET Read = 'true' WHERE Published = 'true';

Update multiple columns:

UPDATE Authors
SET Read = 'true',
Published = 'true',
WHERE AuthorId = 23;

`DELETE`

Delete rows:

DELETE FROM Authors WHERE Published = 'false';

Delete all rows:

DELETE FROM Authors;

Functions

Select the smallest value:

SELECT MIN(Titles) FROM Authors;

Select the highest value:

SELECT MAX(Titles) FROM Authors;

Return the count of rows that have Titles set to 3:

SELECT COUNT(*) FROM Authors WHERE Titles = 3;

Calculate an average:

SELECT AVG(Books) FROM Authors;

Sum all the values of a column:

SELECT SUM(Books) FROM Authors;

Select all the records where the given column starts with a given letter:

SELECT Authors WHERE LastName LIKE 'A%';

Select all the records where the given column ends with a letter:

SELECT Authors WHERE LastName LIKE '%s';

Select all the records where a given column contains a given letter:

SELECT Authors WHERE LastName LIKE '%d';

Select all rows where a given column starts and ends with the given letters:

SELECT Authors WHERE LastName LIKE 'A%s';

Select all rows where a given column does not start with the given letter:

SELECT Authors WHERE LastName NOT LIKE `A%`;

Select all rows where the second letter of a column is a given letter:

SELECT Authors WHERE LastName LIKE ' l%';

Select all rows where the first letter of a column begins with one of the given letters:

SELECT Authors WHERE LastName LIKE '[ABC]%';

Select all rows where the first letter of a column falls within an inclusive range:

SELECT Authors WHERE LastName LIKE '[A-F]%';

Select all rows where the first letter of a column does begin with one of:

SELECT Authors WHERE LastName LIKE '[!A-F]%';

`IN`

Select rows where a column matches:

SELECT Authors WHERE LastName IN ('Aldiss', 'Ballard');

Select rows where a country does not match:

SELECT Authors WHERE LastName NOT IN ('Archer', 'Jonker');

Select all rows where column value falls within an numerical range:

SELECT * Authors WHERE Titles BETWEEN 5 AND 10

Select all rows where column value does not fall within a numerical range:

SELECT * Authors WHERE Titles NOT BETWEEN 5 AND 10

Select all rows where the column name falls within an alphabetical range:

SELECT * Authors WHERE LastName BETWEEN 'Cooper' AND 'Masefield';

`ALIAS`

Display column under a different name:

SELECT LastName, Books, Language AS Lang FROM Authors;

Display a table under a different name:

SELECT * FROM Authors AS Writers;

`JOIN`

A JOIN is done ON something.

Join two tables using the AuthorID field:

SELECT *
FROM Publishers
LEFT JOIN Authors
ON Publishers.AuthorID=Authors.AuthorID;

Select intersection:

SELECT *
FROM Publishers
INNER JOIN Authors
ON Publishers.AuthorID = Authors.AuthorID;

Select all of one table plus all that matches in another:

SELECT * 
FROM Publishers
RIGHT JOIN Authors
ON Publishers.AuthorID = Authors.AuthorID;

`GROUP BY`

List the number of authors by language:

SELECT COUNT(AuthorID)
Language
From Authors
GROUP BY Language;

List number of authors by language, ordered by language with most authors first:

SELECT COUNT(AuthorID)
Language
FROM Authors
GROUP BY Language
ORDER BY COUNT(AuthorID) DESC;

SQL

Create a db:

CREATE DATABASE db;

Drop a db:

DROP DATABASE db;

Create a table:

CREATE TABLE Authors (
    AuthorID int,
    LastName varchar(255),
    Language varchar(255)
);

Drop a table:

DROP TABLE Authors;

Delete all rows within a table:

TRUNCATE TABLE Authors;

Add a column:

ALTER TABLE Authors ADD Birthday DATE;

Delete a colum:

ALTER TABLE Authors DROP COLUMN Birthday;

How to Count With ActiveRecord

21 Jan 2019

via https://work.stevegrossi.com

Counting records returned from the database by ActiveRecord is quite a bit more complicated than you’d expect, but that’s the price of Rails’ magic and the invisible efficiency it gives you behind the scenes. ActiveRecord has its own implementation of Ruby’s size, any?, and empty? methods which behave differently than their array counterparts. Understanding how each works will help you write more efficient code when dealing with database records.

In General

When counting the number of results from the database, use size unless you know you’ll be loading all of the results later on, then use length.

When checking if there are any results from the database, use any? or its opposite empty? unless you know you’ll be loading all of the results later on, then use present? or its opposite blank?.

In a nutshell, calling #size could result in an extra call to the db.

LENGTH

As mentioned above, length is a plain Ruby method which functions like count when called on an array. The difference is that ActiveRecord does not define its own length method. So if you call length on an ActiveRecord::Relation object, it will be converted to an array, and will count the array’s objects. This is equivalent to the slower count method above:…

#size behaves differently depending on whether or not the record has been loaded.

In this case, it behaves differently depending on whether or not the relation’s records have been loaded from the database yet.

Older Newer