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
);
"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
);
"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
);
"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
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.
No comments:
Post a Comment