I wrote a lot of SQL scripts today, mostly focused on joins. A few things I learned:
- You have to use double quotes around names for things (databases, tables, columns) within SQL, but single quotes around strings. Mnemonic: “[S]ingle quote for [S]trings, [D]ouble quote for things in the [D]atabase.” The command line doesn’t care about quotes, so you don’t need to be as specific in psql commands.
- A new convention for naming join tables: use semantic names, e.g., “loans” for a join table between books and borrowers or “amounts” for ingredients and recipes (assuming you’re specifying things like “1 cup of flour” in that table). This is a change from how I’ve previously done things, where I’ve used the combined names of the two tables, in alphabetical order, separated by an underscore (“books_borrowers” or “ingredients_recipes”).
- Objects in a database are not just tables. Objects can also be sequences or indices (and maybe other things I don’t know yet).
- VARCHAR is part of SQL standard; TEXT is not. But Postgres gives us TEXT, which is “efficient/optimized”—I think this means efficient in terms of being easier to write when you’re coding, but I’m not sure.
\iin psql reads a script file into buffer and sends it to database server. Rails migrations also do this: they generate SQL commands based on code in migration.
psql offers basic logic capabilitiesYou can use bash loops to batch execute psql scripts (thanks for the correction, Jeff!):
for i in scripts/cookbook/*; do psql -f $i sql-crud; done
UNIONin SQL will joins select statements together.
- A foreign key reference is a constraint: limits what can happen; disallows certain actions.
- SQL doesn’t execute in order: either the whole statement is valid & executes, or nothing executes. This lets you define things (like aliases) after you use them. The parser parses the entire statement & figures out the details for you.
Not relevant to SQL necessarily, but cool: typing
cal into the command line will give you a monthly calendar.
We also talked about how to implement many-to-many relationships in Rails. Scaffolding or creating migrations will set up *part* of the relationship, but you still have to edit your models to specify
has_many through relationships. You also have to add
inverse_of in a join table, telling the join table to be the inverse of itself. This sparked significant confusion in the class, and I’m still not clear on what this is, how it works, and why/where it’s needed.
We continued working with data relationships in Rails.
“Serving Custom JSON From Your Rails API With ActiveModel::Serializers” made serializers “click” for me, particularly with respect to using data relationships (rather than just listing all attributes out) to leverage serializers for other models. Super cool!
We also talked briefly about protecting resources in our Rails API by having our controllers inherit from the ProtectedController class, rather than the ApplicationController. Not clear whether this is a standard feature in Rails, or something that GA built for us.
Behavior-Driven Development (BDD)
This unit was one of my favorites so far: behavior-driven development using RSpec. The approach we took was:
- Write a user story/define a user behavior.
- Write a feature test that targets this behavior.
- Run the feature test. Watch it fail.
- Write a unit test.
- Run the unit test. Watch it fail.
- Write code to satisfy the unit test.
- Run the unit test. Watch it pass.
- Go back to steps 3-7 and repeat until your feature test passes.
- Commit your code.
We kept rolling with BDD today. We talked about four-phase testing: (not all four steps happen for each test)
- setup (a lot of this happens in
before(:each); also parsing JSON, etc.)
- act/exercise (actually execute the code the test is acting upon, e.g.,
- assert (
- teardown (
My feelings about TDD/BDD can be described as:
I understand why we learned about Rails before we learned about RSpec, but I’m sad that I got a head start on my second project and set up all of my resources and THEN learned about automated testing. I’m hoping to be able to use BDD/TDD for another project, but in the meantime, I’m trying to go back and write automated tests for the code I’ve already written. More on this when I write up my project (soon, I hope!).
A few more things about testing and RSpec and Rails:
- Rspec uses TEST (not DEVELOPMENT) environment (test database, not dev database).
- Code within feature tests will by nature replicate code within unit tests. Feature tests are “black box” tests; they “don’t exist within Rails.” Unit tests (controllers, models, routing) exist within Rails and have access to things. Feature tests are like curl requests. Feature tests spin up a server—this takes a long time/is expensive. (This was a quick explanation to a question I asked about why we’re replicating so many lines of code between our feature tests and unit tests—why can’t we just call a unit test we’ve already defined from within a feature test? I need to come back to this; I still don’t fully understand the separation/redundancy here.)
- All hashes that come through Rails are called “hashes with indifferent access” and will work with symbols or strings.
JSON.parsereturns a Ruby hash, meaning we can’t use symbols to access attributes.
- Use more specific, less semantic tests in unit tests (and more semantic, “friendly” language in feature tests). Example:
.to be_successfulin feature test vs
.to eq(200)in unit test.
Thus began project 2. I’m planning to write this up in a separate post, so that wraps things up for this week!
2 thoughts on “General Assembly WDI, Week 6”
Hi Rebekah! Pleased to find your blog. Just wanted to say that `for i in scripts/cookbook/*; do psql -f $i sql-crud; done` isn’t anything related to `psql` but rather the flow control provided by `bash`, which is utilizing the command in a loop.
Thanks! Corrected above.