A Baffling Encounter with a Tech Poseur

Social Media has never held all that much attraction for me. At some point, the novelty of interacting with people online turns into the drudgery of interacting with people, period. It was mid 2022 when this random encounter happened on the Clubhouse app, with some dude from Canada.

I introduced myself as a software developer, which seemed to signal to this fella that I needed to be challenged. He asked what I knew about Kubernetes, and when I honestly answered "not a lot", he then openly questioned how I could call myself a software developer and followed up by asking what I knew about SQL.

Again, seeing no reason to even attempt to impress a total stranger online, I said I wouldn't call myself an expert and just used as much as I needed to. This was totally subjective, of course. I know how to perform CRUD operations and database joins, and normalize database structures even if I've long forgotten the formal definition of BCNF. To some people, that would absolutely count as expertise, though I feel obligated to hold myself to higher standards.

In response, he started questioning my dedication to my craft, and this made me suspect that I wasn't speaking to someone who actually worked in tech... because genuine tech workers, even if they lacked humility, would probably have the experience to avoid simplistic (and frankly not terribly clever) snap judgments like these.

Why that was completely stupid

A couple reasons. First of all, Kubernetes isn't what I would call a mainstream tech thing. It's more of a DevOps thing, which still makes it a software tech discipline, just not first on everyone's to-do list.

A DevOps engineer would totally learn it.

A back-end engineer? Almost definitely.

A full-stack developer? Maybe.

A Data Analyst, Front-end engineer or Network Manager? Doubtful.

So acting like a techie is somehow deficient for not knowing much about it, tells me more about the person making that statement, than the one being judged. This would be as absurd as me mocking back-end engineers for not knowing HTML and CSS. Because, really, why would they? What next? You aren't a real musician if you can't play the zither? You're not a real doctor if you can't extract a tooth?

Real doctors can pull teeth!

Now SQL would be a more mainstream tool than Kubernetes. Regardless of which layer of the stack a tech worker is operating on currently, they would almost be guaranteed to have been taught SQL in school. And even then, plenty of tech workers don't deal with SQL professionally. Even I only really use raw SQL when I'm performing joins in database consoles - for actual software development, it's too much of a security risk and the accepted tools abstract away SQL for library functions.

Also, can I just mention that using Kubernetes and SQL together as a flex, is just kind of odd? Not pineapple-on-pizza weird. Maybe spinach-on-pizza weird.

Genuine Questions...

My industry isn't a circus, so why are we attracting these clowns? We were known as nerds in school. Why are people now so anxious to be be associated with us?

Why these clowns?

Oh, well. It's now 2026. With all the tech layoffs going on, perhaps my industry would have lost enough mystique to no longer produce these groupies.

Also, why does every tech poseur I encounter, feel the need to namedrop Kubernetes? Does it even sound that impressive?

I'm dead serious. Every other guy who tried to impress me with his tech cred, has cited Kubernetes. One even took the pains to explain Kubernetes was a upscaler in cloud infrastructure, as if we couldn't just read that up on its Wikipedia page. I'm not even that offended by the posturing. It's more the low-effort posturing, like I was some noob who can't tell when someone's full of hot air.

There are three situations where Kubernetes might be brought up casually as a subject.

One; DevOps engineers dissecting a scaling problem. Two; backend engineers deep in infra discussion. Three; tech performer using tech buzzwords as a big stick to hammer home the illusion of being technically experienced.

As far as I could tell, this did not fall under the first two categories. The irony is, in his eagerness to perform tech experience, he ended up demonstrating something that few actual software engineers would exhibit - ignorance of tech stacks.

So... what is it about Kubernetes specifically? This stumps me.

Finally

Of course, I don't mind being told I'm not much of a tech. I already don't consider myself exceptional. Although, the judgement might have stung more coming from someone who obviously knew his shit. As it was, it left me somewhat bemused. As a generalist, I didn't feel personally disrespected. But someone who actually spent the sweat and sleepless nights mastering Kubernetes and working with it, might feel quite differently.

It's been four years. Why am I even talking about this now? In truth, I wanted to talk about it way back, but more interesting stuff kept coming up. Plus, I am one hell of a procrastinator. It's a slow news week, so there you go.

How's your Java and JavaScript?
T___T

The Case of the Awkward Tech Interviewee

It's been a while since I attended a job interview. As an applicant, anyway.

Recently, I was on the other side of the interview, reviewing candidates for a tech position within my company. The interviewee in question was a nervous-looking Malaysian. There wasn't anything that really stood out, except for his awkwardness. Which, in itself, was a problem. You see, for tech positions, sometimes one can get away with not being able to communicate that well. The stereotype of socially-inept but brilliant techies exists for a reason.

Brilliant but
awkward?

However, this particular position was for a leadership role. And lack of communication skills just wasn't going to fly. I could tell that my co-interviewer had already written our applicant off and was about to call it in, but I persisted. We'd already scheduled and allotted the time; I figured that I might as well develop the muscle memory I needed for being an interviewer.

Besides, it could have been just nerves. Some people have told me I can be intimidating, a statement I consider laughable. Please, look at this face. Nothing about me is remotely intimidating.

Either way, I decided to give him a question he could answer. I asked him to explain what he would do to combat SQL Injection. His answer, predictably, was "Stored Procedures". My follow-up, probably just as predictably, was why Stored Procedures? What do Stored Procedures do that normal SQL queries don't?

He stammered. Hemmed. Hawed.

After a few minutes, I put him out of his misery. I told him to Google the term "paramterized queries". And then told him that in future, if anyone ever asked him a question like that again, he should refer to that, rather than simply say "Stored Procedures". It would be a better, more complete, more correct answer. And more importantly, it would be an answer that instilled confidence in the asker of the question, confidence that the answerer knew his shit.

After we were thanked him for his time and bade him goodbye, my co-interviewer turned to me and asked me why I'd spent so much time on this guy. Did I see some potential that he'd missed?

I replied, simply, that we obviously weren't going to give him the job... so I might as well give him something.

People commented on my perceived generosity, or at least, on me "paying it forward". I don't think either is true. This was decidedly not about making the tech industry a better place, or even giving back.

Back then...

Upon further reflection, something similar happened to me in the days when I was the applicant. There were times when I sensed I had failed the interview, and when the interviewer asked if I had any questions, I ventured out of the box.

I asked for advice. And this paid off in spades. They proceeded to tell me more or less where I'd gone wrong. We all knew I wouldn't make it past this round, but they at least thought I deserved a fighting chance... somewhere else. They wanted me to succeed, again, somewhere else. I guess it helped that His Teochewness has such a winning personality.

Seeking answers.

I think that helping candidates along is a very natural instinct on the part of any tech interviewer who takes their job seriously. Of course, it helps if the applicant doesn't piss them off. One may object on the grounds that this helps applicants cheat on their next tech interview. That's only true if they get asked the exact same question next interview. Also, let's be real, answers are all over the internet.

And from a cynical point of view, even if it's true that you're enabling inferior candidates to "cheat", they'd likely be "cheating" at the interviews of your competitors. Nobody really needs me to explain this one now, do they?

It's probably also true that techies generally like to look out for other techies. Some little tribal instinct there. Us geeks against the laypersons.

To conclude...

Interviews need not be an adversarial exercise. Your interviewers want you to succeed, or at least not turn out to be a total waste of time. Selfishly, because they already invested the time and energy into prepping for this interview, and conducting it. From that point of view, they get nothing if you turn out to be a dud.

Therefore, they don't need to love you. They just need to not severely dislike you. And the rest takes care of itself.

Without question,
T___T

Web Tutorial: Ruby On Rails Xmas Poll (Part 4/4)

We'll, of course, want to find a way to view the poll results. To do that, let's first go back to Oracle APEX and create an endpoint for this. Remember the template we created in the first part of this web tutorial? It was called /poll/:id/results/.

Then we create a GET handler for this.

And this is the Pl/SQL code. Do bear in mind that here, averages and medians only make sense because the answers in the poll are numerical. In any case, tables are joined and columns are grouped to get these averages and medians. Before using the AVG() and MEDIAN() functions, we must first run TO_NUMBER() on the data, to convert them to numerical values.

SELECT
pq.TITLE,
AVG(TO_NUMBER(pr.RESULT)) AS AVG_RESULT,
MEDIAN(TO_NUMBER(pr.RESULT)) AS MEDIAN_RESULT
FROM POLL_QUESTIONS pq LEFT JOIN POLL_RESULTS pr
ON pr.QUESTION_SERIAL_NO = pq.SERIAL_NO AND pr.POLL_ID = :id AND pq.POLL_ID = :id
GROUP BY pq.TITLE
ORDER BY AVG_RESULT DESC, MEDIAN_RESULT DESC

The results should be something like this.

{
 "items": [
  {
   "title": "IT CAME UPON THE MIDNIGHT CLEAR",
   "avg_result": 4,
   "median_result": 5
  },
  {
   "title": "JINGLE BELL ROCK",
   "avg_result": 4,
   "median_result": 4.5
  },
  {
   "title": "SILENT NIGHT",
   "avg_result": 3.5,
   "median_result": 3
  },
  {
   "title": "THE FIRST NOEL",
   "avg_result": 3.16666666666666666666666666666666666667,
   "median_result": 3
  },
  {
   "title": "JINGLE BELLS",
   "avg_result": 3,
   "median_result": 3
  },
  {
   "title": "HARK THE HERALD ANGELS SING",
   "avg_result": 2.83333333333333333333333333333333333333,
   "median_result": 3
  },
  {
   "title": "DECK THE HALLS",
   "avg_result": 2.66666666666666666666666666666666666667,
   "median_result": 3
  },
  {
   "title": "JOY TO THE WORLD",
   "avg_result": 2.54545454545454545454545454545454545455,
   "median_result": 3
  },
  {
   "title": "RUDOLPH THE RED-NOSED REINDEER",
   "avg_result": 2.5,
   "median_result": 2.5
  },
  {
   "title": "I SAW MOMMY KISSING SANTA CLAUS",
   "avg_result": 2.27272727272727272727272727272727272727,
   "median_result": 2
  }
 ],
 "hasMore": false,
 "limit": 25,
 "offset": 0,
 "count": 10,
 "links": [
  {
   "rel": "self",
   "href": "https://oracleapex.com/ords/teochewthunder/polls/poll/1/results/"
  },
  {
   "rel": "describedby",
   "href": "https://oracleapex.com/ords/teochewthunder/metadata-catalog/polls/poll/1/results/"
  },
  {
   "rel": "first",
   "href": "https://oracleapex.com/ords/teochewthunder/polls/poll/1/results/"
  }
 ]
}

Back to the Rails server! In the CLI, run this command to create the controller result.

rails generate controller result

Once we've done that, it's time to modify the generated file. Again, we need httparty. We'll reuse the environment variable ORDS_API_URL but append "/results" to it.

app/controllers/result_controller.rb

require "httparty"

class ResultController < ApplicationController
    ORDS_API_URL = ENV["ORDS_API_URL"] + "/results"

    def index

    end
end

Of course, the next step is to mirror what we did for the index action of the root page, and use HTTParty's get() method to call the URL endpoint, then have an If block to handle the result.

app/controllers/result_controller.rb

require "httparty"

class ResultController < ApplicationController
    ORDS_API_URL = ENV["ORDS_API_URL"] + "/results"

    def index
        response = HTTParty.get(
            ORDS_API_URL
        )

        if response.code == 200

        else
            flash.now[:alert] = "Error fetching data."
            @api_data = {}
        end
    end
end

If successful, we return the result to the view by binding it to api_data.

app/controllers/result_controller.rb

require "httparty"

class ResultController < ApplicationController
    ORDS_API_URL = ENV["ORDS_API_URL"] + "/results"

    def index
        response = HTTParty.get(
            ORDS_API_URL
        )

        if response.code == 200
            @api_data = response.parsed_response
        else
            flash.now[:alert] = "Error fetching data."
            @api_data = {}
        end
    end
end

This is the view. We have a header, a div styled using the CSS class pollresult, and a table.

app/views/result/index.html.erb

<h1>Poll Results</h1>
<div class="pollresult">
  <table>
    <tr>
      <td width="300px"><b>Carol</b></td><td width="150px" style="text-align: right;"><b>Average Rating</b></td>
      <td width="150px" style="text-align: right;"><b>Median Rating</b></td>
    </tr>
  </table>
</div>

Refer to the sample JSON result I showed you earlier. We'll use the title, avg_result and median_result properties here.

app/views/result/index.html.erb

<h1>Poll Results</h1>
<div class="pollresult">
  <table>
    <tr>
      <td width="300px"><b>Carol</b></td><td width="150px" style="text-align: right;"><b>Average Rating</b></td>
      <td width="150px" style="text-align: right;"><b>Median Rating</b></td>
    </tr>

    <% @api_data["items"].each do |result| %>
      <tr>
        <td><%= "#{result['title']}" %></td>
        <td style="text-align: right;"><%= "#{result['avg_result']}" %></td>
        <td style="text-align: right;"><%= "#{result['median_result']}" %></td>
      </tr>
    <% end %>
  </table>
</div>

For the CSS, if you're a lazy bastard like me, you might just want to piggyback off this existing class, or create a new one with a custom design.

app/assets/stylesheets/application.css

.pollform, .pollresult
{
  width: 600px;
  padding: 10px;
  border-radius: 10px;
  border: 3px solid rgb(200, 0, 0);
  background-color: rgb(0, 200, 0);
  margin: 5% auto 0 auto;
}

Do you see the problem? The average is way too long and needs to be truncated.

We use sprintf(), passing in "%.1f" to truncate the text to one decimal place.

app/views/result/index.html.erb

<h1>Poll Results</h1>
<div class="pollresult">
  <table>
    <tr>
      <td width="300px"><b>Carol</b></td><td width="150px" style="text-align: right;"><b>Average Rating</b></td>
      <td width="150px" style="text-align: right;"><b>Median Rating</b></td>
    </tr>

    <% @api_data["items"].each do |result| %>
      <tr>
        <td><%= "#{result['title']}" %></td>
        <td style="text-align: right;"><%= "#{sprintf('%.1f', result['avg_result'])}" %></td>
        <td style="text-align: right;"><%= "#{sprintf('%.1f', result['median_result'])}" %></td>
      </tr>
    <% end %>
  </table>
</div>

There you go!

For a final touch, add a nav link to each of these pages.

app/views/poll/index.html.erb

<h1><%= @api_data["items"][0]["name"] %></h1>
<h2><%= link_to "View Results", result_page_path %></h2>
<div class="pollform">

app/views/result/index.html.erb

<h1>Poll Results</h1>
<h2><%= link_to "View Poll", root_path %></h2>
<div class="pollresult">

Then style the h2 tag.

app/assets/stylesheets/application.css

h1
{
  text-align: center;
  color: rgba(255, 255, 255, 0.5);
}


h2
{
  text-align: center;
  font-size: 0.8em;
}


.pollform, .pollresult
{
  width: 600px;
  padding: 10px;
  border-radius: 10px;
  border: 3px solid rgb(200, 0, 0);
  background-color: rgb(0, 200, 0);
  margin: 5% auto 0 auto;
}

Unit Tests

It's time to write a few unit tests! Bear in mind that these are just samples. There are probably better tests one can write. This is the default test that is generated when we create a controller.

test/controllers/poll_controller_test.rb

require "test_helper"

class PollControllerTest < ActionDispatch::IntegrationTest
    test "should get index" do
        get poll_page_url
        assert_response :success
        assert_not_nil assigns(:api_data)
    end
end

And more. Here, we test submitting the form by sending a sample payload, and checking a couple of expected behaviors. We check if there's a redirect to the form page, and that there's a flash notice.

test/controllers/poll_controller_test.rb

require "test_helper"

class PollControllerTest < ActionDispatch::IntegrationTest
    test "should get index" do
        get poll_page_url
        assert_response :success
        assert_not_nil assigns(:api_data)
    end

    test "should submit results" do
        post submit_poll_form_url, params: { answers: { "1" => "3", "2" => "5", "3" => "2"} }
        assert_response :redirect
        assert_redirected_to root_path
        assert_not_nil flash[:notice]
    end
end

The result controller test should look like this as well.

test/controllers/result_controller_test.rb

require "test_helper"

class ResultControllerTest < ActionDispatch::IntegrationTest
    test "should get index" do
        get result_page_url
        assert_response :success
        assert_not_nil assigns(:api_data)
    end
end

When you run these tests, that's what you should get.

Conclusion

Looks like it's the day after Christmas.

This concludes my first Ruby On Rails web tutorial in years. I like to think it's better than my last couple efforts. That's because despite being an old fart, I'm still growing and adapting, and so should you. Enjoy your holidays!

Poll-ite season's greetings!
T___T

Web Tutorial: Ruby On Rails Xmas Poll (Part 1/4)

Merry Christmas season to all!

It's been years since I got my hands dirty with Ruby On Rails, and seeing as I got time, why not now? Ruby, despite me never having had an opportunity to use it professionally, is probably one of my favorite programming languages that I ever picked up for no goddamn practical reason. And, well, this Christmas season, I wanna go back to my guilty pleasure.

Today, we will be creating a form in Ruby On Rails, to submit answers to a poll. This will be a simple Christmas Carol Poll, where the user rates all presented Chsristmas Carols on a scale of 1 to 5.

Normally, a Ruby On Rails project would have its own built-in server capabilities, but I wanted to see how Oracle APEX worked with Rails. Pretty darn well, actually. Thus, what I did was first log onto my Oracle APEX workspace and create some new tables.

The first, obviously, would be a POLLS table. This is the PL/SQL statement for the table, though you can use the UI to create it. It basically is just an auto-increment integer column, ID, and a string, NAME.

CREATE TABLE "POLLS" (
    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
    "NAME" VARCHAR2(100 CHAR) NOT NULL ENABLE,
    CONSTRAINT "POLLS_PK" PRIMARY KEY ("ID") USING INDEX ENABLE
);

Far easier to just copy this to clipboard, go to SQL > SQL Command and paste into the box, then run it.

There, you have the POLLS table. Ignore the other tables; they're remnants of my last Oracle APEX project.

I'm just going to enter the name of the poll here, manually.

The next table is POLL_QUESTIONS. Here's the SQL. This one is just a bit more complicated. We don't have an ID field here; instead uniqueness is determined by the combination of POLL_ID (which is a foreign key to the POLLS table) and NO. NO is an integer that denotes the serial number of that question within a poll. TITLE is a string which displays the question, and ANSWERS is a comma-separated string of possible values.

CREATE TABLE "POLL_QUESTIONS" (
    "NO" NUMBER(2,0) NOT NULL ENABLE,
    "TITLE" VARCHAR2(100 CHAR),
    "POLL_ID" NUMBER NOT NULL ENABLE,
    "ANSWERS" VARCHAR2(200),
    CONSTRAINT "POLL_QUESTIONS_PK" PRIMARY KEY ("NO", "POLL_ID") USING INDEX ENABLE
);

The easy thing to do would be to run SQL Command with this query.

Again, I'm going to do some manual data entry here. Notice that POLL_ID is always 1, because we only have the one entry in POLLS. For the column ANSWERS, it is a JSON-encoded array of the values 1 to 5.

Finally, we have POLL_RESULTS. ID is a primary key just like in POLLS. Each row will have two foreign keys - POLL_ID and QUESTION_NO which reference POLL_QUESTIONS. RESULT will store the answer for the specific question referenced by the last two fields. POLL_ID and QUESTION_NO.

CREATE TABLE "POLL_RESULTS" (
    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
    "POLL_ID" NUMBER NOT NULL ENABLE,
    "QUESTION_NO" NUMBER NOT NULL ENABLE,
    "RESULT" VARCHAR2(100 CHAR) NOT NULL ENABLE,
    CONSTRAINT "POLL_RESULTS_PK" PRIMARY KEY ("ID") USING INDEX ENABLE
);

We can create the table the easy way here. We will not be populating this table manually. No, the entire point is to populate this table by means of submission through the Ruby On Rails form.

Time to determine API endpoints!

We've populated the POLLS and POLL_QUESTIONS table because we want to use this data to populate the form. In order for that to happen, we'll need to provide a means of retrieving that data. And that will be REST API endpoints. For now, we will do just a GET endpoint.

Go to SQL Workshop, then go to RESTful Services. Create a new Module and let's call it "polls". The endpoint here will be "/polls/".

Congrats! You have a Module! Now you need to add Templates to this Module. Click on that button, Create Template, near the bottom right side of the screen.

Create two Templates. The first one is poll/:id, and the second is poll/:id/results/.

Go to the first Template you created, poll/:id. You'll need to add a GET handler to it. To do that, click on the Create Handler button somewhere near the bottom right area of the screen. We haven't forgotten about the second Template, but we'll get to it later.

This is the SQL code that will be in that box. It's a simple join between POLLS and POLL_QUESTIONS.

SELECT p.NAME, pq.SERIAL_NO, pq.TITLE FROM POLLS p
LEFT JOIN POLL_QUESTIONS pq ON pq.POLL_ID = p.ID
WHERE p.ID = :id
ORDER BY pq.SERIAL_NO

This is a good place to stop. We've set up some stuff on Oracle APEX that will be used in the next part of this web tutorial.

Next

The Ruby On Rails setup.

Idempotence in CRUD operations

Hello, readers. Today we're going to talk about idempotence.

The concept of idempotence is paramount in database security. By saying that, of course, I feel compelled to explain what idempotence is. That term tends to come up a lot with regard to data transactions. The official definition is as follows, with regard to data transactions.

Idempotency is the property of an operation where performing it multiple times produces the same result as performing it once.

What that means is that if you apply an operation over and over, and it makes no difference to the database after the first time, that operation is idempotent.

With regard to CRUD (CREATE, READ, UPDATE, DELETE) operations, it's important to understand which ones are idempotent, and which ones run the risk of undesirable results if accidentally executed multiple times. 

Please note that code examples are in MySQL.

CREATE

Idempotence: No (except in special circumstances)
CREATE operations are not idempotent by default. If you run a CREATE operation multiple times, you are going to get multiple created rows.

INSERT INTO categories (name, description) VALUES ("Cat A", "test")

Running this three times will result in these three rows being added.

id	name	description
1	Cat A	test
2	Cat A	test
3	Cat A	test

What if the design of the table was structured this way? What if name was supposed to be unique?

CREATE table categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) UNIQUE,
  description TEXT
)

Being unique.

In that case, that particular CREATE operation would be idempotent because the database simply wouldn't allow subsequent CREATE operations with the same values in the name column.

id	name	description
1	Cat A	test

Also, if you defined an UPSERT operation instead of a CREATE, the record would only get inserted the first time. Subsequent times, the record would already exist, and therefore it would get updated... with the same values. Thus, making it idempotent. There is an exception to this exception, but that will be discussed in the UPDATE operation further down.

INSERT INTO categories (name, description)
VALUES ("Cat A", "test")
ON DUPLICATE KEY UPDATE
description = VALUES(description);

READ

Idempotence: Yes
This is the most straightforward operation in the sense that it is always idempotent. The first READ request makes no change to the database. Subsequent READ requests also make no change.

SELECT * FROM categories WHERE name = "Cat A"

READ operations are not
about changing data.

No exceptions to the rule, no nothing. This is as open-and-shut a case of idempotence as you're ever going to get in the world of computer science.

UPDATE

Idempotence: Yes (except in special circumstances)
In most cases, running an UPDATE operation on a specific record multiple times, has no effect beyond the first time. It will end up updating that record to the same values. In the query below, row id 1's name and description values would just keep being updated to "Cat A" and "test", which is basically no change.

UPDATE categories
name = "Cat A",
description = "test"
WHERE id = 1

Thus, this is idempotent. Unless...

What if the table had an Audit Field that tracked when the record was updated?

CREATE table categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  description TEXT,
  updated DATETIME
)

And what if the UPDATE operation was like this? Then the updated field would be a different value every time this operation was run, and therefore it would no longer be idempotent!

Time being tracked.

UPDATE categories
name = "Cat A",
description = "test"
updated = now()
WHERE id = 1

Also, what if the UPDATE operation wasn't on a specific row? This query updates all rows where the name field fits a particular condition. Kind of like dropping a bomb on an entire country instead of surgically taking out one terrorist. (Boy, this got dark)

UPDATE categories
name = "Cat A",
description = "test"
WHERE name like "%Cat%"

What if, just after the UPDATE operation above was run, a row was coincidentally inserted?

INSERT INTO categories (name, description) VALUES ("Cat B", "test")

Then we'd have two rows like this...

id	name	description
1	Cat A	test
2	Cat B	test

...but if the UPDATE operation was run again, this would be the result!

id	name	description
1	Cat A	test
2	Cat A	test

So, to conclude, in principle, the UPDATE operation is idempotent. But only if the values updated are absolute, and only if a very specific record is being updated.

DELETE

Idempotence: Yes (except in special circumstances)
In principle, DELETE operations are idempotent. After all, you can only delete a record successfully, once. After that, it no longer exists, so rerunning the same DELETE operation, achieves nothing. To employ a grim analogy, you can't kill someone twice.

You can only kill
someone once.

DELETE FROM categories WHERE id = 1

However, the concerns that exist for the UPDATE operation also exist for the DELETE operation. What if the DELETE operation wasn't on a specific row? This deletes all rows where the name field fits a certain filter.

DELETE FROM categories WHERE name LIKE "%CAT%"

And if, after the first DELETE operation, a record was added like so, rerunning the above DELETE operation would remove this row!

INSERT INTO categories (name, description) VALUES ("Cat B", "test")

In conclusion

A lot of what I've outlined is context. In simple cases, the answer of idempotence are similarly straightforward. But this is software development, where things are rarely that simple.

Wishing you a categorically good day,
T___T

The year I finally achieved that JLPT certification

Some say "better late than never". I got a huge dose of that this year as I obtained my Japanese Language Proficiency Test (JLPT) N5 Certificate.

Why was this overdue? Well, you see, because I actually began on this journey at the tender age of 15. I was a Polytechnic student at the time, and this was a supplementary module. At first, things went well. Having received education in Chinese, learning the basic sounds of Japanese was a breeze. Learning the Hiragana and Katakana writing systems wasn't much of a stretch as well; in fact it was significantly easier than reading and writing Chinese characters. Until we got to Kanji, which essentially was Chinese characters.

Ugh, more Chinese.

At which point I lost all interest.

Also, at this time, I had begun an obsession with writing code, and building stuff. Japanese was a pretty language, but it just didn't fit into my world the same way C++, SQL or even QBasic, did.

And the rest, as they say, is history. Even after graduation, not only did I neglect whatever little Japanese I had learned, the next few decades were a disastrous collection of bad career choices, even worse lifestyle choices, and sobering life lessons.

COVID-19 and Clubhouse

Fast-forward to 2021. COVID-19 had the world in its loathsome grip and I, like many others, sought human interaction online. That was when I discovered the Clubhouse app. Through it, I encountered numerous communities, not all of which spoke English, Mandarin, or even Cantonese.

A few of these were Japanese speakers. And some were even offering Japanese lessons. I dove right in, relearning all the basics I had forgotten. Many times, I would simply listen in, try to make sense of the conversation, and Google words that came up. Then I started engaging, hesitantly, in the conversations. I even participated in Mandarin-Japanese exchanges!

Little cultural exchanges.

It was slow going. I wrote an app to facilitate relearning Hiragana and Katakana. I practiced writing every day, religiously. But there was this feeling that I could do more.

That was when someone suggested that I take the JLPT. And from that day forth, I took things up a notch.

Training in earnest

My next step was to install Duolingo on my phone. For the next few years, I faithfully partook of the exercises daily. At the same time, I went onto YouTube to search for JLPT Listening exercises. I didn't really have a sense of how far I had progressed; I just knew I needed to keep going. I've never been brilliant; but what I am good at is being consistent.

One year later, I downloaded the Migii app, and paid the subscription fees for accessing sample JLPT exam questions. With Duolingo, the returns were diminishing. I needed to train a different set of linguistic muscles - the kind used for passing the exam. Duolingo had brought me to a certain point where I could read the Japanese exam questions without too much difficulty... now I had to practice answering them.

The next milestone was hit another year later when, while watching a Japanese TV show, with English subtitles, I noticed something odd. Almost constantly, the subtitles would read "It's OK". Even though "It's OK" was a perfectly reasonable thing to say in the context of the current point of the story, it was a pretty poor translation.

Phrase	Literal Meaning	English Translation
daijobu	It's OK.	"It's OK."
dou itashimashite	You're welcome.	"It's OK."
shinpai suna	Don't worry.	"It's OK."
anshin shite	Relax.	"It's OK."
ki ni shinai de	Pay it no mind.	"It's OK."

At this point, I found myself thinking - wow, these translators are so goddamn lazy! They just use "It's OK" for everything!

And my next thought was - hey, if I know this much, shouldn't I be taking the JLPT already?!

The JLPT Test Voucher

That was when I registered for the JLPT on the Japanese Cultural Society of Singapore website. and paid a hundred Singapore dollars for the privilege. A month later, the JLPT test voucher arrived in the mail. The train was in motion; there was no going back.

Meanwhile, I continued training religiously. With Listening practice, especially. Online chatter had it that the recordings would be played only once, so I trained myself similarly, by not rewinding and replaying.

The JLPT N5 Exam

It was July when the day arrived. I head for the Singapore Management Institute where the exam was held, and stood in line with what looked like mostly Burmese folks. Interesting.

The invigilator who took charge of the exam room I had been assigned to, seemed to be Japanese going by the way she spoke English.

Shading answers with a pencil.

The proceedings were charmingly old-school. We were given question and answer sheets. It was all multiple-choice, and the correct answer had to be shaded with a pencil for feeding into a scanning machine. Despite my numerous certificates, I hadn't done this since... 2015? This was because my last couple certifications were earned from doing the coursework and presentations, rather than standardized tests.

The toughest moment in the exam came from the Choukai, which was the Listening portion. Despite my best efforts, my concentration slipped at various points. It was with considerable relief that I handed in my question and answer sheets, and headed off.

In August, I logged on to the JLPT portal to check my results. I had passed, and my results were more than decent. I actually scored higher on Listening than other sections!

The real value of all this

Last September, I got my actual physical certification through the mail. It was a foregone conclusion by that time, but I still felt that little thrill of pride. Job well done, bro, I told my reflection. Not such a big deal in the larger picture, but we've got to celebrate our wins even if they're small. Especially if said win took thirty-three friggin' years to achieve.

My results.

The JLPT N5 Certification isn't going to change my life. As far as professional cred goes, it's barely a blip. Achieving the JLPT N5 Certification probably puts me on par with the average Japanese toddler where the language is concerned. As for the value of understanding multiple languages? That's not much of a flex. This is Southeast Asia; just about everyone and their dog is multilingual.

No; at my age, the act of learning is arguably more important than what's being learned. It helps stave off dementia.

The real value is knowing and affirming, that with sufficient motivation and putting in the time and effort, I can learn pretty much whatever I choose to learn. And that is powerful stuff. In an age where things are constantly and rapidly evolving, the ability to learn shit has become vital - not just in the tech industry, but for life itself.

In hindsight, I should have realized this. How did I learn ASP? PHP? Ruby? VueJS? D3? All by picking up a book, watching videos, visiting websites, and constant practice. Most of the time, it really is that simple. The methods vary, but at the end of the day, it's about the willingness to put in the hours.

The linguistic journey continues!

I do want to see if I can achieve the next rung, the JLPT N4 Certification. Again, I'm not really sure why. Just for the hell of it, I guess. It's not like I plan to visit Japan. It's not like I realistically have anything to prove to anyone.

On the other hand, Korean does seem pretty interesting.

Decisions, decisions...

Ganbarimashoo!
T___T

What's the best programming language for beginners?

What should be a programmer's first language?

Often, this is not a deliberate choice, more a matter of circumstance. Perhaps a programmer started on QBasic as a kid. Perhaps, like me, the programmer picked it up in school and was made to learn C++ or Java. I would even venture to say very few programmers start out saying I'm going to begin my programming journey with the best, most modern language out there! To begin with, how would they know what constitutes the "best" programming language, or what even is a good programming language? You don't know what you don't know, after all.

Let's learn!

Also, how suitable a programming language is for your purposes also really depends on what your purpose is. Do you want to crunch data? Write games? Build web portals?

So, obviously, before we can sort out what the "best" programming language is, perhaps it is more useful to figure out what a programmer's first programming language should be. After all, this choice opens different doors.

For that, we should probably look at a few factors. I can only draw upon languages I've coded in before. as examples, obviously.

Syntax

For this, we look at simplicity of writing code in that language. How intuitive the syntax is, and how few extra keystrokes needed to get something done.

For this, my obvious picks would be Python or Ruby. Less of the semi-colons and curly brackets. Anyone picking either Python or Ruby as a first language, I suspect, would be less likely to give up out of frustration.

Code syntax.

C# and Java is probably among the worst for this. Too many namespaces. Overly verbose syntax. I haven't had many projects that needed to be done in either of these languages, and for that I'm truly grateful.

But the one that takes the proverbial cake, in terms of being cumbersome, would be PHP. The function calls structures are wildly inconsistent, and in addition to semi-colons and curly brackets, it also requires a dollar sign preceding every variable name.

As far as syntax goes, the group of programming languages with visually similar styles - PHP, Java, JavaScript, C and its variants - is such that if you pick one up, the learning curve for the others, at least for the simple stuff, isn't so great. Thus, even if PHP is hideously cumbersome in parts, it benefits from being visually similar to other languages mentioned.

Transferable skills

This measures underlying principles or skills that one will pick up while programming in a certain language, that will be useful when transitioning to another stack. It could be something as simple as similar syntax (as mentioned earlier) or strong data typing.

Or it could be something so ubiquitous that no matter what stack you're in, you're almost certainly going to encounter it. A great example of this would be SQL. No matter what kind of programming you go into, the chances of you needing to access a database at some point, are pretty good. I wouldn't go so far as to say that this makes SQL the number one choice for a programmer's first language, but it makes a great case for being a programmer's second, at least.

Data processing.

For a similar reason, if one wanted to go into web development, it's almost impossible not to have to deal with HTML, CSS and JavaScript, but learning JavaScript as a first language just for that, is questionable. As a second language, definitely.

Strongly-typed, class-based languages such as Java and C# would be good choices as a first programming language for learning concepts such as Object-Oriented Programming. (PHP and JavaScript kind of implement OOP as well, but in a way that's a little odd). Python would be the choice if one wanted to have a strong foundation in data structures. However, Python's lack of strong typing might work against it as a first language.

Ease of setup

One of the most daunting tasks of learning a language is setting up the environment for it. Java requires the JRE (Java Runtime Environment). PHP requires an Apache server. Python and Ruby, well, you get the idea.

For this, the most fuss-free option has to be JavaScript, hands down.

All major browsers
run JavaScript.

JavaScript runs on all major browsers. You don't need to install anything you probably don't already have. If you're reading this, unless you're reading it on your phone, you have a desktop browser which you can run JavaScript in. It's almost zero setup. Just write your code, and run it in the browser.

For a Windows environment, VBScript is also almost zero effort. All you need to do is write your script, save it with extension "*.vbs" and you can run it by double-clicking it! Unfortunately, I'm not sure how useful knowing VBScript is.

In essence, in the cases of both JavaScript and VBScript, the environment has already been set up for the programmer.

Popular support

No developer plies their trade without consulting a reference of some sort. Textbooks are a valid source, but for the most updated material, we turn to the internet. Portals and forums provide most of these, with each language having large communities of programmers ready to provide support.

Learn by reading, or
by community.

Which programming language has the largest, or best, communities?

That's hard to say. All communities have their fair share of toxic losers who waste little time being condescending and acting like gatekeepers. At the same time, each community also boasts genuine people. I'm not going to waste time detailing how large each programming language's community is relative to others. Suffice to say, they're all (OK, mostly) large enough to be useful.

Generally, the longer they have been around, the larger they tend to be.

Conclusion

Whatever the choice turns out to be, ultimately, there will be benefits to that choice. That is because no matter what language someone chooses for their first programming experience, the fact remains is that they are still engaging in the activity of programming. It's a start. Some starts are better than others, sure, but it's a step. And hopefully the first of many.

Mind your (first) language!
T___T

Functions that handle NULL values in databases

Not every value in a database has a well-defined value. Sometimes there is no value, or a NULL.

Empty values!

In these cases, you may need to handle these values, especially if there are calculations involved. Take the following table, TABLE_SALES, for example. There are missing values in the DISCOUNT column.

TABLE_SALES

DATETIME	ITEM	QTY	SUBTOTAL	DISCOUNT
2023-10-10 12:13:10	STRAWBERRY WAFFLE	2	20	0
2023-10-10 12:44:54	STRAWBERRY WAFFLE	1	10	0
2023-10-11 15:03:09	CHOCO DELIGHT	1	25	-2.5
2023-10-11 18:22:42	ORANGE SLICES	5	30
2023-10-12 10:56:01	STRAWBERRY WAFFLE	4	40	-3

Now let's say we tried this query.

SELECT DISCOUNT FROM TABLE_SALES

This does not present a problem.

DISCOUNT

0

0

-2.5

-3

But what if we wanted to use it as part of a calculation? You would have situations where we tried to add NULL values to the value of SUBTOTAL.

SELECT (SUBTOTAL + DISCOUNT) AS NETT FROM TABLE_SALES

Functions to handle NULL values

There are functions to handle these cases. They are named differently in different database systems. In SQLServer, it's IFNULL(). In MySQL, it's ISNULL(). In Oracle, it's NVL().

In all of these cases, two arguments are passed in. The first is the value that could be NULL. The second is the value to substitute it with if the value is NULL. Thus, for Oracle, it would be...

SELECT (SUBTOTAL + NVL(DISCOUNT, 0)) AS NETT FROM TABLE_SALES

This is nice and neat, but we can do better. The problem here is portability. If you had to move your data from Oracle to MySQL, for example, you would have to change all instances of NVL() to ISNULL().

The COALESCE() function

COALESCE() is a function that exists in all of the databases mentioned above. How does it work?

Well, you slip in any number of arguments to the COALSECE() function call, and the function will return the first non-NULL value. Thus...

COALSECE(NULL, NULL, 0.5, 1, NULL, 8)

.... will return this.

0.5

So if we did this...

SELECT (SUBTOTAL + COALESCE(DISCOUNT, 0)) AS NETT FROM TABLE_SALES

...it would return this. And you would be able to use that same function anywhere!

NETT

20

10

23.5

30

37

Finally...

Handling NULL values is important. Whether you choose to handle them at the data entry level (not allowing NULL values in a column) or in a calculation (using the COALESCE() function), at some point you have to handle them. I hope this helped!

NULL and forever,
T___T

Web Tutorial: The Self-affirmations WordPress Plugin (Part 1/4)

Last September, I walked you through setting up a Mailing List Registration Form in Oracle APEX. This month is a follow up. We are going to use the database created then, to drive what we're making today. So if you didn't pay attention the last time, tough luck, chum! We'll be continuing on our work from the last round.

We want to set up a script in WordPress to send out those emails on a scheduled basis, to the users in the database. For that, first of all, we have to set up REST endpoints for the database in Oracle APEX.
Log in to your workspace and go straight to SQL Workshop. There should be a range of big buttons. We want the one that says RESTful Services.

Over here you should see this screen, with no Modules yet. Click the Create Module button.

This should come up. Make up a name for the Module, and an endpoint name. Click Save.

Now you should see this. Click on the Module name.

Now you will see the Module. You'll want to create Templates. The button is on the lower right, in a nice aqua color.

Here we add the template. For this, we use the name "readytoreceive". This is where we get a list of all users that have not received an email in the last n number of days, determined by the value of DAYS.

Now it's there, but this is just a Template. We'll need to create a Handler for it.

In here, set the Method to "GET". Then click Create Handler.

Now you'll see this Handler for "GET" has been created. Click on it...

...and you're in this interface. Set Source Type to "Collection Query". And input the SQL below.

This is the SQL. We basically say that if the user's LAST_SENT value is less than the value of today's date minus DAYS minus 1, it's added to the list. Why minus 1? Well, DAYS could be 1, and if user's LAST_SENT value was yesterday, it would not get entered into the list. Thus, we do a minus 1 to take care of that case.

SELECT EMAIL, FIRST_NAME, LAST_NAME, DOB, GENDER FROM MAILING_LIST WHERE LAST_SENT < TO_DATE(CURRENT_DATE - (DAYS - 1))

So that's your first endpoint!

The next endpoint is called "terms". We set it up the same way, first creating a Template. Unlike the last time, we have ":email" in the URI Template setting. This is the parameter which we will use later.

Then we create a Handler for it by clicking Create Handler. For this, everything is the same as previously, except for the SQL.

The SQL is as follows. We want to get everything from the MAILING_LIST_TERMS table where the EMAIL field matches the value of email.

SELECT TYPE, TERM FROM MAILING_LIST_TERMS WHERE EMAIL = :email

We need to define the parameter, email, for the query. In the Parameters section right at the bottom, click the Add Row button and fill in the details as shown. This defines the parameter for the query as a string.

Done with the second endpoint!

The third one also uses a parameter, but it will update the database. Create a Template for it, like this.

Then create a Handler. This time, the Source Type is "PL/SQL".

This is the SQL. It sets the value of LAST_SENT for the MAILING_LIST table where the EMAIL value matches email, to today's date.

UPDATE MAILING_LIST SET LAST_SENT = CURRENT_DATE  WHERE EMAIL = :email

Of course, we will need to set up the parameter, email.

Time to test!

In your browser, test this URL for readytoreceive. You should get this.

Now test terms using an email address in the database. You should have this list, assuming you've entered data for it.

Test setreceived with the email. There's no visual indication, but if you check the database, the LAST_SENT column for that email should be today's date. Now if you test setreceived again, this email should not appear in the list!

In this part of the web tutorial, we set up REST endpoints to get and set data. Subsequently, we will use these endpoints for the script.

Next

The WordPress plugin, setting up testing.