Stumbling on Subtle Errors

As a budding programmer - and perhaps not the most logical one - I have probably done more debugging than the average person. However, it has allowed me to notice small details which some of the better programmers may not notice (because they are wiser and never make those weird mistakes). Below, I will expand on my numerous problems, and hope that maybe it will be useful to some new intern with a crazy mindset like me.

Most of my work was related to the SGN database. Thus, I needed to use postgreSQL and DBIx::Class (a Perl module which allows the user to access the database through Perl code instead of SQL). Here we go!

---

1. The query does not end until you add a semicolon, no matter how many times you press enter.

The database does not necessarily work like the terminal, even though you're still technically typing in the terminal. In the terminal, the query ends the moment you press enter, even if you get an error message. However, in the database, you have to type a semicolon first, or it will simply assume that your query is ongoing.

For example, when creating a table, you can split your query into several lines:

CREATE TABLE Persons
(
p_id int,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
);

Or you can put it all on one line and let the text wrap:

CREATE TABLE Persons (p_id int, lastname varchar(255), firstname varchar(255), address varchar(255), city varchar(255));

The downfall of entering a query on several lines is that you cannot go back to fix your mistakes. (Well, you could try to open an editor and fix it there, but that's a pain.) You are better off typing it all on one line, adding a semicolon at the end, and then hitting enter. But if you find yourself stuck in a query, type Ctrl + C to get out of it.

---

2. In the database, put strings in single quotes.

It doesn't like double quoted strings, so just don't do it.

---

3. In DBIx::Class, find() returns a row object, and search() returns a resultset.

The find() method will give you only one row of information. Use it when you know that there should be only one result returned. You can easily access information by typing the column name. For instance:

my $pet = $schema->resultset('Animals')->find({name => 'Jojo'});
my $type_of_animal = $pet->type;

The search() method returns several rows of information. Thus, I usually try to store this result in an array, because I find it easier to access. However, storing it in a scalar and using ->next() to access the next row of info is fine, too. Here is an example:

my @pet = $schema->resultset('Animals')->search({name => 'Jojo'});
my $type_of_animal = $pet[0]->type;

(You may need to type $pet->get_column('type'); This is especially when there is more than one result after doing the search.)

---

4. When typing in the resultset and search_related arguments, use the module names, not the database names.

Try to follow this example:

1. Let's say we create two tables called “dog” and “cat” in the database (all lowercase letters).
2. Then we created perl modules called Dog.pm and Cat.pm.
3. In Dog.pm, let's say some programmer decided to write:
__PACKAGE__->has_many(“cats”, “Schema::Cat”, {“foreign.id” => “self.id”})
4. In Cat.pm, he wrote:
__PACKAGE__->has_many(“dogs”, “Schema::Dog”, {foreign.id” => “self.id”});

Here's how you would use this info:
my $pet = $schema->resultset('Dog')->search_related('cats', {id => 3});
my $pet = $schema->resultset('Cat')->search_related('dogs', {id => 25});

Notice that for the resultset(), you use the module name. For search_related(), you use the table name found in that module, not the table name found in the database (like I instinctively wanted to use when I first started).

---

5. Some helpful database, SQL and DBIx::Class syntax which is not as easily found online:

Going through the database:

\dn – shows the schemas in the database

\dt – shows the tables in the current schema

\d table_name – shows the columns and other info for that specific table

SET search_path TO schema_name – use this to go to a different schema

**********************************************************
Some SQL:

For searching for parts of a string:

SELECT * FROM table_name WHERE column_name ilike '%string%';


Another way to join tables - use USING instead of ON (as shown in http://www.w3schools.com/), but make sure to include parentheses around the column name:

SELECT * FROM table_name JOIN table2_name USING (column_name_in_common);

**********************************************************
...And some DBIx::Class:

Searching for an item in a list of items – don't just type in the array – you need to use 'in':

my @num = qw/1 3 4 7 9/;
my @search = $schema->resultset('Animals')->search({animal_id = {'in' => [@num]}});

**********************************************************
Let me note that anything you type in DBIx::Class can be written as SQL. For instance, the example above can also be written as:

SELECT * FROM animals WHERE animal_id IN (1, 3, 4, 7, 9);

However, when writing this stuff in a Perl script, DBIx::Class is easier. If you used SQL in Perl, you would have to prepare the query, execute, and fetch the results into variables. However, with DBIx::Class, this is done just by creating resultset objects. Also, DBIx::Class has an auto-loader, which means that you never have to worry about keeping your classes in sync with the database schemas. Doesn't that sound nice?

---

And there you have it. Hope this information is at least somewhat helpful to you all!

P.S. If anyone says DBIC, it's just a short acronym for DBIx::Class. ;)

---

Helpful Sources:

SQL: http://www.w3schools.com/sql/default.asp
DBIx::Class: http://search.cpan.org/~jrobinson/DBIx-Class-Tutorial-0.0001/