Tuesday, 30 June 2015

How to implement pagination in python Postgres, web.py and jQuery AJAX

We will use a simple worklist app as an example as this is a pet project im working on. You should be able to copy the same patterns into your own web.py project.

This example will make a vertical pagination UX.

Postgres / web.py model

Your sql must have an order by clause otherwise you will get random results. Both offset and limit must not have "=" symbol after it.

model.py
import web
db = web.database(dbn='postgres', db='dev', user='simple_worklist',pw='')

def get_completed_tasks(offset,limit):
    return db.select('wip',where="status='COMPLETE' order by complete_datetime desc offset $offset limit $limit", vars=locals())

Essentially "limit" restricts the result set to X rows. "offset" will skip X many rows before beginning to return rows - http://www.postgresql.org/docs/7.3/static/queries-limit.html

web.py controller

A URL service is needed to serve the database result set.

main.py
import web
import model
urls = (
    '/servicegetcompletedtask(.*)','ServiceGetCompletedTasks'
)
app = web.application(urls, locals())
svcrender = web.template.render('templates')
...
...
class ServiceGetCompletedTasks:
    def GET(self,action):
        i = web.input(offset=0,limit=4)
        outcome = model_tasks.get_completed_tasks(i.offset, i.limit)
        return svcrender.service_get_completed_tasks(outcome)

if __name__ == '__main__':
    app.run()

A separate template render is needed without a base template. Above, this is called svcrender. URL GET params are defined by web.input, offset and limit are defaulted to 0 and 4. We then render the outcome using the svcrender. We test out this service by typing this into our browser http://localhost:8080/servicegetcompletedtask/?offset=2&limit=2

jQuery AJAX / web.py view

Below is the view of our URL service.

We want to return fragmented html from the database result set.

service_get_completed_tasks.html
$def with (tasks)
$for task in tasks:
 <div>
       <h5>$task.name 
           ( 
             $if task.value :
             $task.value 
             $else :
                no 
              value ) </h5>
       <div>$task.content</div>
        <small >Completed on $task.complete_datetime</small>
       
       <br/><br/>
    </div>

We want to use jQuery to AJAX our URL service.

We want javascript to control requesting more results, dynamically updating the DOM, and hiding the "more" button when there are no more results to be returned.

We wrap our code inside the $(document).ready() to make sure the DOM is ready before we manipulate it.

When coding using web.py templates we use $$ to escape the "$" and print the client side "$". Server side "$" is of course, for web.py templating only.

view.html
<div class="text-center">
<h4>Completed</h4>
<div id="completed-tasks"></div>
<div class="btn btn-default btn-sm" id="btn-more">
 View older...
</div>
<script>
$$(document).ready(function(){
 var offset = 0;
 var total = 0;
 function more(){
    $$.ajax({
    type: "GET",
    url: '/servicegetcompletedtask/?limit=2&offset='+offset,
    success: function(response){
     if(response.trim() !=""){
      $$('#completed-tasks').append(response);
      offset=offset+2;
       }else{
      $$('#btn-more').hide();
       }
    }
    });
 }
 more();
 $$('#btn-more').click(function(){
  more();
 });
});
</script>
</div>

Below is an example of an implemented vertical pagination UX.

In conclusion, the brunt of the pagination behaviour is from the database's offset and limit SQL syntax. web.py is used to expose it as a URL GET service, and jQuery is used to consume this service, and dynamically update the DOM to achieve a responsive user interface.

_

How to setup an AWS RDS Postgres database

Aws


Click launch DB instance.
Select postgres.
Enter database connection details, and select the micro free tier option.
Im unsure about the network settings so i left the default settings there.
Select "create new security group". We want one group for app instances and one group for database instances.
Name your database.
Click launch db instance.
It will take some time for your database to be created.

Revise the security group of your database by going to the EC2 section. You will notice that the inbound has an open TCP 5432 port and the ip address has been locked down to your current IP address from your ISP. The outbound traffic has been defaulted to allow all.

Google search "what is my ip address" to find your ISP IP.

You should add another inbound rule to allow your app ec2 instance access to your db rds instance. Do this by clicking on your app instance on your EC2 dashboard.
Find the IP address.
Navigate to security groups again and add the TCP 5432 rule with your app instance IP address.

If you are connecting to your db with the same ISP IP address as you did when you created it, you should be able to connect to it via pgAdmin or some other tool.


_

How to setup an AWS micro Ubuntu, Apache2, Python, web.py stack

Hello, thought i'd blog about my python webserver stack installation attempt. It's quite general but please let me know if there is anything that could be done better.

AWS Setup

On the AWS dashboard click launch new instance. Choose Ubuntu AMI (free tier). Continue to choose free tier options. I'm unsure about the network settings but i just left these as default. Add storage settings. For the security configuration, configure SSH and HTTP inbound to any IP (unless you have a static address you want to lock it down to) Configure HTTP and HTTPS outbound to any. This will be needed later on for installing unix packages from public repos. You can remove this config after configuring your stack if your machine doesn't need the outbound connection any more. You will be prompted to choose key based authentication for logging in. Name your key and download it. Connect to it via -
ssh -i [your key name].pem ubuntu@[your aws instance ip address] 

Ubuntu

Update ubuntu -
sudo apt-get update
sudo apt-get dist-upgrade
Install apache2 -
sudo apt-get install apache2
Type your aws instance ip address in your browser, you should see the default apache webpage. Install python libs -
sudo apt-get install python-pip
sudo pip install web.py
Install mod_wsgi
sudo apt-get install libapache2-mod-wsgi
Verify install -
vi /etc/apache2/mods-enabled/wsgi.load
sudo service apache2 restart
Configure Apache -
sudo cp /etc/apache2/sites-available/000-default.conf /etc/apache2/sites-available/001-www.conf 
sudo vi /etc/apache2/sites-available/001-www.conf
Add below 001-www.conf-
        #DocumentRoot /var/www/[your site]
        WSGIScriptAlias / /var/www/[your site]/main.py/
        Alias /static /var/www/[your site]
        AddType text/html .py
        <Directory /var/www/[your site]/>
                Order deny,allow
                Allow from all
        </Directory>
Enable new settings -
sudo cp /etc/apache2/sites-available/001-www.conf /etc/apache2/sites-enabled/001-www.conf 
 sudo rm /etc/apache2/sites-enabled/000-default.conf
Test with a simple web.py app -
cd /var/www/[your site]
sudo vi main.py
Copy and past below into vi -
import web

urls = (
       '/.*', 'hello',
)
class hello:
   def GET(self):
      return "Hello, world."

application = web.application(urls, globals()).wsgifunc()
Navigate to your aws instance ip address in your browser to test the hello world. If there are any error they will show up in error logs. Tail them here -
tail -fn500 /var/log/apache2/error.log

Related Articles

Below are similar article that i have found on the web that were useful. http://webpy.org/cookbook/mod_wsgi-apache http://www.chrishjorth.com/blog/free-aws-ec2-ubuntu-apache-php-mysql-setup/ http://serverfault.com/questions/232227/python-cgi-on-amazon-aws-ec2-micro-instance-a-how-to _

Apache2 throws 403 Forbidden when changing VirtualHosts

After configuring /private/etc/apache2/httpd.conf to make my webserver point to /Users/e/Documents/dev on my MacOS, i received the below apache access log-
10.0.0.32 - - [26/May/2013:19:35:24 +1000] "GET / HTTP/1.1" 403 213
My virtual host file -
<VirtualHost *:80>
        ServerName myservername
        DocumentRoot "/Users/e/Documents/dev"
        <Directory "/Users/e/Documents/dev">
                Options Indexes FollowSymLinks Includes ExecCGI
                AllowOverride all
                Allow from all
                Require all granted
        </Directory>
</VirtualHost>
I then decided to check the each folder and found that Documents had drwxr-x---+ permissions
I did this by - $ ls -lrt /Users/e/ $ drwxr-x---+ 24 e staff 816 19 May 23:24 Documents
  • You change the permissions by - $ chmod 755 Documents/
  • The new permissions will look like - $ drwxr-xr-x 25 e staff 850 25 Jul 2012 Documents
  • How to install Python and Django with MySQL on MacOS

    The easiest way to do this was with homebrew package manager and pip python package manager. Installing python, django, and mysql does NOT install the mysql-python database backend needed during python runtime. This is something we have to do manually. We therefore use the pip manager to install it.

    This article assumes you have MySQL already installed.

    Install Homebrew - http://mxcl.github.io/homebrew/ , pip and mysql backend

    $ sudo ruby -e "$(curl -fsSL https://raw.github.com/mxcl/homebrew/go)"
    $ sudo brew install python 
    $ export PATH=/usr/local/share/python:$PATH 
    $ sudo easy_install pip
    $ export PATH=$PATH:/usr/local/mysql/bin 
    $ sudo pip install mysql-python 
    

    Check that you can access the python MySql backend

    $ python >>>import MySQLdb

    Designing an 8 function 4 bit ALU

    Hello world, this article was for an old uni assignment in the year 2009. I wouldn't know anything about this anymore!

    1. Circuit Function

    1.1. Brief

    This 8 function 4 bit ALU was created by combining four 1 bit ALUs, each with the following components and functionality-
    • XOR
      • X = A XOR B
    • AND
      • X = A AND B
    • OR
      • X = A OR B
    • Full Adder with Subtraction – Doubles as an Adder and Subtractor
      • X = A + B
      • X = A - B
    • 4 Input Multiplexor – Provides shifting left, right and thru capabilities
      • X = A Shifted left by 1 bit
      • X= A Shifted right by 1 bit
      • X = A
    The above components are located in a Logic Unit as seen in section 2.8. The Logic Unit is part of the 1 bit ALU. The logic unit’s output is selected via an 8 input multiplexor as shown in section 2.9. This allows the selection of a particular function based on 3 bits. The design of the Shifter that is inside the Logic Unit, is more of a passive design as Pin A of the Logic Unit is only coupled to Pin T of the 4 input multiplexor. The brunt of the Shifter functionality lies in the wiring of the 4 ALUs to make a 4 bit ALU as seen in section 1.2 below. The following sections show the functionality of each component of the 8 function 4 bit ALU below.

    1.2. 8 Function 4 bit ALU

    2. Circuit Components

    2.1. Full Adder

    2.1.1. Circuit Diagram

    Full_Adder.dwm Full_Adder.dwt

    2.1.2. Truth Table

    2.2. Full Adder with Subtraction

    2.2.1. Circuit Diagram

    Full_Adder_With_Subtraction.dwm Full_Adder_With_Subtraction.dwt

    2.2.2. Truth Table

    This circuit utilises the Full Adder component shown in section 2.1.1. When S is turned off (set to 0), this circuit becomes the full adder shown in section 2.1.1. To avoid duplication, the truth table below will only show values when S is turned on (set to 1).

    2.3. 2 to 4 Decoder

    2.3.1. Circuit Diagram

    Decoder_2_4.dwm Decoder_2_4.dwt

    2.3.2. Truth Table

    The methodology for creating a 2 to 4 decoder was derived from http://www.cs.uiuc.edu/class/sp08/cs231/lectures/07-Decoders.ppt , accessed 22/04/2008. Although the schematic diagram was not shown in that source, I managed to implement the functionality of turning on and off this circuit via a pin called EN. This aids in the creation of a 3 to 8 decoder shown in section 2.4. The below truth table shows values when EN is turned on (set to 1). If EN is turned off, this circuit spits out no outputs.

    2.4. 3 to 8 Decoder

    2.4.1. Circuit Diagram

    Decoder_3_8.dwm Decoder_3_8.dwt

    2.4.2. Truth Table

    The methodology for creating a 2 to 4 decoder was derived from http://www.cs.uiuc.edu/class/sp08/cs231/lectures/07-Decoders.ppt , accessed 22/04/2008. By having a 2 to 4 decoder that can be switched on and off via pin EN, we can couple 2 of these circuits to make a 3 to 8 decoder. Below is the truth table for this circuit.

    2.5. 4 Input Selector

    2.5.1. Circuit Diagram

    Selector_4.dwm Selector_4.dwt

    2.5.2. Truth Table

    To list all possible inputs for the above circuit, a very large table would have to be produced, one with 256 lines. Therefore I have decided to simplify the table and provide more meaning. The truth table below shows that if A is on and if S1 is on, X will be on too. This pattern continues for each letter input. The alphanumeric input (the selector), passes the value of its corresponding input to X. eg. To pass the value of C, you must turn on S3, that way X will be what ever C is. If the value of C is 0, and S3 is turned on, the value of C will still be passed to X, in which X will be 0.

    2.6. 8 Input Selector

    2.6.1. Circuit Diagram

    Selector_8.dwm Selector_8.dwt

    2.6.2. Truth Table

    To list all possible inputs for the above circuit, a very large table would have to be produced. Therefore I have decided to simplify the table and provide more meaning. This circuit works exactly like the one in section 2.5.1. The only difference with this one is that this selector has 8 Inputs. Like the one in section 2.5.1, the truth table below shows that the Selector (S1- S8) allows the value of the inputs (A- H) to be passed to X.

    2.7. Multiplexor (3 to 8 Decoder & 8 Input Selector)

    2.7.1. Circuit Diagram

    Multiplexor_3_8_Dec_8_Sel.dwm Multiplexor_3_8_Dec_8_Sel.dwt

    2.7.2. Truth Table

    To list all possible inputs for the above circuit, a very large table would have to be produced. Therefore I have decided to simplify the table and provide more meaning. This Multiplexor allows us to input a 3 bit binary to use as a selector of 8 possible inputs (A – H)

    2.8. Logic Unit (Logical AND/OR/XOR, Full Adder with Subtraction, Shifter)

    2.8.1. Circuit Diagram

    Logic_Unit_8_Functions.dwm Logic_Unit_8_Functions.dwt

    2.8.2. Truth Table

    To list all possible inputs for the above circuit, a very large table would have to be produced. Therefore I have decided to simplify the table and provide more meaning. The truth table below was constructed on the basis that there are five components which altogether make up 8 functions. All five components are wired in parallel. The light green sections below show all possible inputs and outputs for each XOR, AND and OR component. The cyan sections show all possible inputs and outputs for the Full Adder in Subtraction mode (Sub set to 1), while the pale blue sections show all possible inputs and outputs for the Full Adder in Addition mode (Sub set 0). The cyan and pale blue sections are a direct reflection of sections 2.1 and 2.2. The purple sections show all possible inputs and outputs for the 4 input multiplexor. When this component is wired into a 4 bit ALU it becomes a functional 4 bit shifter as discussed in sections 1.1 and seen in section 1.2. The 4 input multiplexor shown 2.8.1 has an EN pin wired to a Vcc-, this just mean that power is being sent to that pin (ie. Set EN to 1), resulting in the 2 to 4 decoder inside it to be turned on.

    2.9. 1 Bit ALU (Logic Unit and Multiplexor)

    2.9.1. Circuit Diagram

    ALU_1bit_8_Function.dwm
    ALU_1bit_8_Function.dwt

    2.9.2. Truth Table

    To list all possible inputs for circuit diagram 2.9.1, a very large table would have to be produced. Therefore I have decided to simplify the table and provide more meaning. This circuit utilises the logic unit and passes its value to X via the 8 input multiplexor. The 8 inputs of the multiplexor are selected depending on the value of S1 to S3 pins. See section 2.8.1 for the logic unit’s truth tables. The light green sections below shows all possible inputs and outputs when XOR, AND and OR functions are selected. The cyan sections show all possible inputs and outputs for the Full Adder’s addition function. The pale blue sections show all possible inputs and outputs for the Full Adder’s subtraction function. The purple sections show all possible inputs for when X becomes 1 and some possible inputs for when X become 0. The purple section is just a multiplexor in which it passes the value of A, L or R to X, depending on the value for S1, S2 and S3.

    LCD fix for Panasonic DMC-TZ15 Camera

    One day i was holding my DMC-TZ15 camera when suddenly it slipped through my hands and dropped onto a metal rail. The result was a cracked LCD screen. I was very lucky that the camera was off as this reduced the chances of breaking the lens package.

    The picture to the right shows a cracked screen that is pitch white rendering the camera unusable.

    Remedy

    The solution to my problem was simple, find an LCD part compatible with my DMC-TZ15 and order it from ebay. With my screen having arrived from Hong Kong i can now perform the dismantling process.

    This article shows how to dismantle a Panasonic DMC-TZ15 in order to remove/install a LCD screen. The same procedures could be followed to replace the CMOS/CCD/Lens package of the camera as well.

    Conclusion

    Don't drop your camera!

    All up, the screen cost me 39.76AUD (34.49USD) to order a Sony branded lcd screen from Hong Kong. Installation took me less than 10mins and now i have a usable camera!