The UI for List-O-Rama is built using html and javascript, with Bootstrap for styling. The data is retrieved using http GET ajax requests (to php files, see below), and changes are done via http POSTs.
JQuery and JQuery Tools (for the search overlays) are used. The code can easily be perused.
List-O-Rama uses MySQL to hold the data. There are three tables used to hold List-O-Rama lists: List Type, List, and List Item. There are also tables for User, Owner and Owner/User - currently the userId and ownerId are equivalent. (At some point the system may support having multiple users connected to one Owner record). Each table has a php file which handles the CRUD operations on that table. This document explains the high-level details of how these database objects are maintained.
The php file for table maintenance has the suffix 'maint', and it supports the basic actions of Add, Update, Delete, and Read using SQL against the MySQL database. Data is passed in JSON format. Add, Update and Delete can be called as http POSTs, while the Read action should be called as an http GET. The input is a JSON object with an action code ('A', 'U', 'D' or 'R') and the object data (all fields need to be sent for Add and Update, even if only one field value is being changed, while for Delete and Read just the action and the id need to be sent). The output is JSON which contains four separate objects: the prime key 'id' of the row, a 'status' boolean indicating success or failure, a 'message', and the full 'object' (except in the case of Delete, where no object is retured).
A validation or integrity error will have status false and the message will contain the error.
In case of successful delete, no object is sent back.
Security checks are performed to ensure that changes are only done by the owner, and reads
cannot be done on another owner's non-public list items.
Note that maintenance of the createDate and updateDate fields is handled behind-the-scenes.
The A(dd), U(pdate), and D(elete) actions can be called as http POSTs, passing in a JSON object in this form:
| {"action":actionCode, | 'A', 'U', 'D' or 'R' |
| "itemId":itemId, | the prime key, system assigned |
| "itemDesc":desc, | a string |
| "ownerListId":ownerListId, | prime key of owning list |
| "longDesc":longDesc, | a string, length 250 |
| "refItemId":refItemId, | prime key of another list's item |
| "refListId":refListId, | prime key of another list |
| "url":url, | string, |
| "rank":rank, | number, used on ranked lists |
| "date1Dt":date1, | date, used on dated lists |
| "date2Dt":date2, | date, used on dated lists |
| "status":status}; | string 4, not yet used |
In addition to the field values shown above, the List Item object returned on a Read includes these fields from the owning List: ownerId, publicSw, listTypeId.
Sample input with data for a dated List Item:
| {"action":"U", |
| "itemId":"17", |
| "itemDesc":"Call the dentist", |
| "ownerListId":"63", |
| "longDesc":"Move appointment", |
| "refItemId":"0", |
| "refListId":"0", |
| "url":"", |
| "rank":"0", |
| "date1Dt":"2013-06-30", |
| "date2Dt":"0000-00-00", |
| "status":""}; |
Sample output with data for a dated List Item (after successful update):
| {"id":"17", |
| "status":true, |
| "message":"Update successful", |
| "object": [{"item":[ {itemId":"17", "itemDesc":"Call the dentist", "ownerListId":"63", "longDesc":"Move appointment", "refItemId":"0", "refListId":"0", "url":"", "rank":"0", "date1Dt":"2013-06-30", "date2Dt":"0000-00-00", "status":"" "createDt":"2013-04-18", "updateDt":"2013-06-24"}]}]}; |
Sample output with data with a validation error:
| {"id":"17", |
| "status":false, |
| "message":"List Item error: A URL value is not allowed", |
The A(dd), U(pdate), and D(elete) actions can be called as http POSTs, passing in a JSON object in this form:
| {"action":actionCode, | 'A', 'U', 'D' or 'R' |
| "listId":listId, | the prime key, system assigned |
| "listName":name, | char 60, descriptive name |
| "listTypeId":listTypeId, | fk to List Type |
| "sourceListId":sourceListId, | fk to another List, to rank its items |
| "ownerId":ownerId, | fk to Owner of the List |
| "date1label":date1label, | string, UI label |
| "date2label":date2label, | string, UI label |
| "reminderSw":reminderSw, | 0 or 1 boolean for reminder emails |
| "daysPrior":daysPrior, | number of days prior for emails |
| "publicSw":publicSw, | 0 or 1 boolean for public list |
| "status":status}; | string 4, not yet used |
In addition to the field values shown above, the List object returned on a Read includes these fields from the List Type: rankedSw, useDatesSw, useUrlSw, useListsSw, useItemsSw and the owner's name.
| {"action":actionCode, | 'A', 'U', 'D' or 'R' |
| "listTypeId":listTypeId, | the prime key, system assigned |
| "listTypeName":name | string |
| "listTypeDesc":description | string |
| "ownerId":ownerId | FK to Owner, required |
| "rankedSw":rankedSw | boolean; if true, Items require a rank |
| "publicSw":publicSw | boolean; if true, all Users can use List Type |
| "useDatesSw":useDatesSw | boolean; if true, Items require date1 |
| "useUrlSw":useUrlSw | boolean; if true, Items require url |
| "useItemsSw":useItemsSw | boolean; if true, Lists require a sourceList and items require a refItem |
| "useListsSw":useListsSw} | boolean; if true, Items require a refList |
The query functionality is supported by php code that executes the appropriate SQL, and hands back JSON output with all the row data. Input for these queries is also JSON, passed in an http GET call.
Returns Lists owned by the logged in user. Format for input JSON:
| {"ownerId":ownerId, | prime key of Owner as required search criteria |
| "listTypeId":listTypeId}; | prime key of List Type as optional search criteria |
Sample output (when access denied):
| {"status":false, |
| "message":"User cannot access Lists for this Owner"} |
Sample output for successful query:
| {"status":true, |
| "lists":[{"listId":"8","listName":"Best websites!","listTypeName":"Ranked URL List","publicSw":"1","createDt":"2013-04-02","updateDt":"2013-04-29"}, {"listId":"34","listName":"Birthday List","listTypeName":"Dated List","publicSw":"1","createDt":"2013-05-12","updateDt":"2013-05-24"}]} |
Returns lists marked as 'public' - but only works if a user is logged in. Format for input JSON:
| {"numRows":maxNumRows, | number of rows to return |
| "listName":listName, | string, optional search criteria |
| "ownerId":ownerId, | prime key of Owner as optional search criteria |
| "listTypeId":listTypeId, | prime key of List Type as optional search criteria |
| "listNameMore":listNameMore} | string used in 'get more' feature, optional |
Since there could be a large number of Public Lists, this query supports the ability to 'get more' records by taking the last row's list name and using it as search criteria for the next query.
Returns lists marked as 'public' - can be called without a logged in user
No input needed - simply returns JSON with each List's listId and listName
Sample JSON output:
[{"listId":"8","listName":"Best websites!"},
{"listId":"1","listName":"Celebrated Movies"},
{"listId":"30","listName":"Countries - Americas"}]