Tabilet Tutorial

Database Tables

poll_admin

CREATE TABLE polls_admin (
admin_id int NOT NULL auto_increment,
login varchar(32) NOT NULL,
passwd char(32) NOT NULL,
PRIMARY KEY (admin_id),
UNIQUE KEY login (login)
);

poll_polls

CREATE TABLE polls_poll (
poll_id int NOT NULL auto_increment,
question varchar(200) NOT NULL,
pub_date timestamp,
PRIMARY KEY  (`poll_id`)
);

poll_choice

CREATE TABLE polls_choice (
choice_id int NOT NULL auto_increment,
poll_id int NOT NULL,
choice varchar(200) NOT NULL,
votes int NOT NULL default '0',
PRIMARY KEY  (choice_id),
KEY poll_id (poll_id)
);

This tutorial is similar to the Poll Tutorial in the Python-based Django framework. It lets people view public polls and vote in them; plus, it creates an admin backend that lets you add, change and delete polls.

Pre-requests

You need to have a Tabilet account. If not, you can apply one here. After you get an account, login to your administration center and create a database containing three tables listed on the right-hand side of this page. We assume that the databased name and user are dbname, dbuser, dbpass.

Step 1: Run Helper

Helper is a wizard program that helps you generate the first set of JSON service files.

Enable Helper in section xxxx.
Run it at the URL: http://your_web_site/cgi-bin/helper/a/e/main
Login, and you are ready to lunch your first project.

Step 2: Create a Project

For each web site or web service, you create one and should only one project. Input project properties as follows.

Name your project (alphanumeric only), choose myproj.
Name your controller script: /myproj.let, which must be a full webpath with the extention .let.
Name your debugging log: /home/eightran/public_html/chinajilin/myproj.log, which must be a full file path.
Modify the JSON object in System Paramters:
- "db" is the array of the database source, use ["dbname", "dbuser", "dbpass"] that you set up earlier in the client administration center;
- "template" is the full directory path containing Velocity template files which display Views, choose "/home/eightran/public_html/chinajilin/tmpls" (you don't need actually making the templates at this step);
- "components" is the array of all active components. In this Tutorial, we will build three components: ["Admin", "Poll", "Choice"];
- "pubrole" is the name of public role, choose "p".
- You may leave all the other pairs as-is.
Content Tags is an array describing which content types you want to View. By default, there are two tags: "json" and "html". For "json", returned View is always JSON data so there is no need for templates. For "html", you need to build Velocity templates for each role in directory "template". For administrative roles, we will generate them automatically.
Role can be public, which is for everybody; or non-public, which needs authentication. By default, there are public role "p" and administrative role "admin". For each non-public role, define the following JSON object:
- "idname": unique id name for role members, most likely from database's role table. Choose "admin_id" here.
- "sql": this SQL will let you verify the login and return a list of authenticated account parameters. Choose "select admin_id from admin where login=? and passwd=?" as in the poll_admin table.
- "attributes": express the above returned property in a valid JSON array; here it is ["admin_id"]. Note that the first element in the array is always the value of "idname".
- "credential_1" and "credential_2": on the role login page, they are names for the user-and-password input pair. Choose "admin" and "adminpass".
- You may leave all the properties as-is.

Step 3: Create Component

A project can have multiple components. A component is usually associated with a database table and describes properties and methods of the table. Internally, component consists of both Model and Controller.

By default, component always inheritates 5 object methods:

insert - insert new row to the table;
topics - list multiple rows;
edit - list one row by an id (which is usually what happens when clcking on webpage's Edit link);
update - update a row;
delete - delete a row.

In language CRUD: those 5 actions are C=insert, R=topics+edit, U=update, and D=delete. In language RESTful: POST=insert, GET=topics+edit, UPDATE=update, and DELETE=delete.

After finishing Step 2, click on COMPONENT on the top bar and you will land on the page to create new component.

Create component Admin; put the whole "CREATE TABLE polls_admin .. " in the text area Table. Assign no action to public role p. Leave all the other columns as-in.
Create component Poll; put the whole "CREATE TABLE polls_poll .. " in the text area Table. Assign edit and topics public role p. Leave all the other columns as-in.
Create component Choice; put the whole "CREATE TABLE polls_poll .. " in the text area Table. Assign topics and update public role p. Leave all the other columns as-in.

Step 4: Deploy Project

Go back to the dashboard, and click on Deploy to automatically generate the JSON web service and administrative web site. If you have ever made changes to a component, the Last Deployed time stamp would be turned in brown.

Step 5: Work on Administative Site

Before log into the administative management backend, make sure you add at least one row to table poll_admin via phpAdmin in the client administration center. The administrative site is at:

Manage poll's administrator accounts: http://your_web_site/myproj.let/admin/html/admin
Manage polls: http://your_web_site/myproj.let/admin/html/poll
Manage poll choices: http://your_web_site/myproj.let/admin/html/choice?poll_id=1
Logout: http://your_web_site/myproj.let/admin/logout

The first time you access any of the URLs, you are prompted to login. Login using the account you added in the poll_admin. Now do the following tasks.

Add a new administrator on the account management page: choose hello for LOGIN and world for PASSWD, and click Add. Go back and refresh, you will see the new account is listed as administrator.
Try to logout and to access the account management page again using the hello/world pair.
Add a new poll at the poll management URL. Choose Do you like IPhone4? and then submit. Go back and refresh the page. You will see the new poll is listed.
Assume the first poll gets an auto increment ID as 1, go to the choice management page using "poll_id=1" query string in the URL. Click on Create New; choose 1, hate, 0 for POOL_ID, CHOICE and VOTES. Go back, refrsh the page, and Create New again. Choose 1, love, 0 as the second set of choices.
Done. You are ready to poll the answers on your web site or via web service.

Step 6: Work on Public Site

You have assigned permission topics and edit to components Poll and Choice. You can access different JSON data as follows:

List of poll topics: http://your_web_site/myproj.let/p/json/poll
Get details of the poll_id=1 entry: http://your_web_site/myproj.let/p/json/poll?action=edit&poll_id=1
List of choices: http://your_web_site/myproj.let/p/json/choice?poll_id=1
Get details of the choice_id=1 entry: http://your_web_site/myproj.let/p/json/choice?action=edit&poll_id=1&choice_id=1

Step 7: How about Vote?

To allow visitors to vote, you need to create a customized method in Choice, since vote can not fit to any of the 5 existing inherited methods. A vote is basically the SQL execution: UPDATE polls_choice SET votes=votes+1 WHERE choice_id=? with choice_id=1 to hate iPhone and choice_id=2 to love it.

Go back to Dashboard in Helper on Step 4. Follow Myproj/edit, COMPONENT, and then to edit component Choice.
Under Customized, you can add all customized SQL executions as a JSON object. The JSON has three keys: selects for all SELECT-type SQLs, dos for UPDATE or DELETE types, and procedures for stored procedures. Here we should use dos. The value of dos is array of multiple cutomized SQL DO objects. There are three keys for each DO:
1. name, give the DO a name.
2. sql, the SQL statement.
3. pars, list of all query parameters in the above SQL statement.
As a summary, you should add this JSON to Customized:
```
{
   "dos" : [
      {
         "sql" : "UPDATE polls_choice SET votes=votes+1 WHERE choice_id=?",
         "pars" : [ "choice_id" ],
         "name" : "vote"
      }
   ]
}
```
Finally, you should add this pair to the Actions object:
```
{
...
   "vote" : {
      "groups" : [ "p" ]
   },
...
}
```
which informs the system that there is a new method named vote for public visitors to use.
Accessing this URL will add a vote to the poll: http://your_web_site/myproj.let/p/json/choice?action=vote&poll_id=1&choice_id=? with choice_id 1 or 2.

Step 8: How about JOIN?

This step is optional. It is about how to override the existing topics method in Choice.

You may have noticed that in the returned data of Choice topics, you got only poll_id, instead of question for this poll_id. The reason is that topics acts only on single table. You can use a customized topics to override the inherited one.

Add this pair to the Customized object:

   "selects" : [
      {
         "sql" : "SELECT c.choice_id, c.poll_id, c.choice, p.question
                  FROM polls_choice c 
                  INNER JOIN polls_poll p ON c.poll_id=p.poll_id
                  WHERE c.poll_id=?",
         "pars" : [ "poll_id" ],
         "name" : "topics"
      }
   ],

and you will get question in the return of topics. If you want it also in edit, change the above to

   "selects" : [
      {
         "sql" : "SELECT c.choice_id, c.choice, c.votes, p.question 
                  FROM polls_choice c 
                  INNER JOIN polls_poll p ON c.poll_id=p.poll_id
                  WHERE c.poll_id=?",
         "pars" : [ "poll_id" ],
         "name" : "topics"
      },
      {
         "sql" : "SELECT c.choice, c.votes, p.question
                  FROM polls_choice c
                  INNER JOIN polls_poll p ON c.poll_id=p.poll_id
                  WHERE c.poll_id=? AND c.choice_id=?",
         "pars" : [ "poll_id", "choice_id" ],
         "name" : "topics"
      }
   ],

Step 9: Work on Public HTML Pages

You may want to poll the choices on a normal web page too. Just make another set of View templates for the content tag html.

Under directory template (see Step 2), create another sub directory p in parallel to the existing admin
Under p, mkdir two sub directories poll and choice. Copy files topics.html and edit.html from admin/poll to poll; copy those from admin/choice to choice.
Create a file vote.html in p/choice for the vote page. You may simply put the word "done" in the file.
You definitely should re-design all the public html pages so they look nice and professional.

Thanks! Then end.

tabilettutorial

HHHHH How to Build Your First Tabilet Project.