Swagg::Blogg

sql

I actually normally wouldn't enjoy this but thankfully this Mojo::Pg wrapper makes it easy. I honestly don't have any experience with the original DBD::Pg as I'm still very new to the database world so I hope this doesn't read too much like I'm lost in the sauce. First I took a look at my current tables using the psql tool:

post_text=> \d
                   List of relations
 Schema |         Name          |   Type   |   Owner
--------+-----------------------+----------+-----------
 public | mojo_migrations       | table    | post_text
 public | replies               | table    | post_text
 public | replies_reply_id_seq  | sequence | post_text
 public | threads               | table    | post_text
 public | threads_thread_id_seq | sequence | post_text
(5 rows)

post_text=> \d replies;
                                            Table "public.replies"
     Column     |           Type           | Collation | Nullable |                  Default
----------------+--------------------------+-----------+----------+-------------------------------------------
 reply_id       | integer                  |           | not null | nextval('replies_reply_id_seq'::regclass)
 thread_id      | integer                  |           |          |
 reply_date     | timestamp with time zone |           | not null | now()
 reply_author   | character varying(64)    |           |          |
 reply_body     | character varying(4096)  |           |          |
 hidden_status  | boolean                  |           | not null |
 flagged_status | boolean                  |           | not null |
Indexes:
    "replies_pkey" PRIMARY KEY, btree (reply_id)
Foreign-key constraints:
    "replies_thread_id_fkey" FOREIGN KEY (thread_id) REFERENCES threads(thread_id)

These long lines are ugly so here's a pastebin link. Essentially I need to s/reply_/remark_/g; including that sequence, index and foreign-key constraint:

-- This file is migrations/5/up.sql btw

 ALTER TABLE replies
RENAME TO remarks;

 ALTER TABLE remarks
RENAME reply_id
    TO remark_id;

 ALTER TABLE remarks
RENAME reply_date
    TO remark_date;

 ALTER TABLE remarks
RENAME reply_author
   TO remark_author;

 ALTER TABLE remarks
RENAME reply_body
    TO remark_body;

 ALTER TABLE remarks
RENAME CONSTRAINT replies_thread_id_fkey
    TO remarks_thread_id_fkey;

 ALTER INDEX replies_pkey
RENAME TO remarks_pkey;

 ALTER SEQUENCE replies_reply_id_seq
RENAME TO remarks_remark_id_seq;

I'm also going to do the exact opposite for our rolling-back pleasure:

-- This one is migrations/5/down.sql

 ALTER TABLE remarks
RENAME TO replies;

 ALTER TABLE replies
RENAME remark_id
    TO reply_id;

 ALTER TABLE replies
RENAME remark_date
    TO reply_date;

 ALTER TABLE replies
RENAME remark_author
    TO reply_author;

 ALTER TABLE replies
RENAME remark_body
    TO reply_body;

 ALTER TABLE replies
RENAME CONSTRAINT remarks_thread_id_fkey
    TO replies_thread_id_fkey;

 ALTER INDEX remarks_pkey
RENAME TO replies_pkey;

 ALTER SEQUENCE remarks_remark_id_seq
RENAME TO replies_reply_id_seq;

Idk why but I always find this step feels weird to me because it feels like I'm undoing the undo lol. But we will need it later so let's absolutely include it. Now I'm gunna see if it works...

daniel@netburst:~/git/PostText$ ./PostText.pl eval 'app->pg->migrations->from_dir("migrations")->migrate(5);'
daniel@netburst:~/git/PostText$ echo $?
0

No news is good news I guess. Let's whip out psql again and see how it looks:

post_text=> \d
                   List of relations
 Schema |         Name          |   Type   |   Owner
--------+-----------------------+----------+-----------
 public | mojo_migrations       | table    | post_text
 public | remarks               | table    | post_text
 public | remarks_remark_id_seq | sequence | post_text
 public | threads               | table    | post_text
 public | threads_thread_id_seq | sequence | post_text
(5 rows)

post_text=> \d remarks;
                                            Table "public.remarks"
     Column     |           Type           | Collation | Nullable |                  Default
----------------+--------------------------+-----------+----------+--------------------------------------------
 remark_id      | integer                  |           | not null | nextval('remarks_remark_id_seq'::regclass)
 thread_id      | integer                  |           |          |
 remark_date    | timestamp with time zone |           | not null | now()
 remark_author  | character varying(64)    |           |          |
 remark_body    | character varying(4096)  |           |          |
 hidden_status  | boolean                  |           | not null |
 flagged_status | boolean                  |           | not null |
Indexes:
    "remarks_pkey" PRIMARY KEY, btree (remark_id)
Foreign-key constraints:
    "remarks_thread_id_fkey" FOREIGN KEY (thread_id) REFERENCES threads(thread_id)

Aaaaaand the pastebin. I think we're in good shape. I'm going to migrate back for now as I still need to make some changes in the controller logic to use the new Remark model instead of my old Reply model.

daniel@netburst:~/git/PostText$ ./PostText.pl eval 'app->pg->migrations->from_dir("migrations")->migrate(4);'
daniel@netburst:~/git/PostText$ echo $?
0

I keep the migration hard-coded in the method call in the app itself just to save myself from accidentally migrating to the latest before it's ready:

# From PostText.pl
app->pg->migrations->from_dir('migrations')->migrate(4);

I know there's some reason I started doing that... I accidentally something but now I can't remember. Gunna just stick with the cargo cult and leave it be.

Next I gotta work on the aforementioned controller logic. And then moar tests.

#database #mojolicious #perl #sql #webdev

So I used to read data into an array of arrays like so:

sub get_threads($self) {
    $self->pg->db->query(<<~'END_SQL')->arrays()
        SELECT thread_id,
               TO_CHAR(thread_date, 'Dy Mon DD HH:MI:SS AM TZ YYYY'),
               thread_author,
               thread_title,
               thread_body
          FROM threads
         WHERE NOT hidden_status
         ORDER BY thread_date DESC;
       END_SQL
}

Then I'd plop that data into templates like so:

<% for my $thread (@$threads) { =%>
  <article class="thread">
    <h3 class="title"><%= @$thread[3] %></h3>
    <h4 class="date"><%= @$thread[1] %></h4>
    <h5 class="author"><%= @$thread[2] %></h5>
    <p class="body"><%= @$thread[0] %></p>
  </article>
<% } =%>

This is already pretty cool but I kept losing track of what index number went to which data field. Then I saw that there is a hashes() method in Mojo::Pg::Results and thought... What if we used 100% of the brain? (Edit: Formatting turned out bad for this one)

sub get_threads($self) {
    $self->pg->db->query(<<~'END_SQL')->hashes()
        SELECT thread_id                                             AS id,
               TO_CHAR(thread_date, 'Dy Mon DD HH:MI:SS AM TZ YYYY') AS date,
               thread_author                                         AS author,
               thread_title                                          AS title,
               thread_body                                           AS body
          FROM threads
         WHERE NOT hidden_status
         ORDER BY thread_date DESC;
       END_SQL
}

By using SQL to assign an alias to the column names my templates now look much cleaner:

<% for my $thread (@$threads) { =%>
  <article class="thread">
    <h3 class="title"><%= %$thread{'title'} %></h3>
    <h4 class="date"><%= %$thread{'date'} %></h4>
    <h5 class="author"><%= %$thread{'author'} %></h5>
    <p class="body"><%= %$thread{'body'} %></p>
  </article>
<% } =%>

Readability becomes more and more important as my memory gets worse and worse... I need to learn POD so if I'm a boi of my word there may be a blog post on that in the future. Knowing me... Years in the future.

#perl #mojolicious #sql