PostgreSQL Conference West 2009 Call for Papers
Posted Wednesday Jun 24th, 2009 12:00pm
by Joshua Drake | Permalink
PostgreSQL Conference West 2009 Call for Papers
June 24th, 2009, the PostgreSQL Conference U.S. team is pleased to announce the West 2009 venue and call for papers. This year the premiere West Coast PostgreSQL Conference will be leaving its roots at Portland State University and moving north to sunny Seattle, Washington.
The event this year is being held at Seattle Central Community College from October 16th through 18th. The move to Seattle opens up a larger metropolitan area for continuing to expose databases users, developers, and administrators to the World's Most Advanced Open Source Database. Following previously successful West Coast conferences, we will be hosting a series of 3-4 hour tutorials, 90 minute mini-tutorials, and 45 minute talks.
This year we will be continuing our trend of covering the entire PostgreSQL ecosystem. We would like to see talks and tutorials on the following topics:
General PostgreSQL:
***
The PostgreSQL Conference U.S. series is an autonomous Educational Project used to educate all comers on the use of The World's Most Advanced Open Source Database. Proceeds from the event are donated directly to United States PostgreSQL; the 501c3
non-profit for PostgreSQL education and advocacy in the United States.
Posted Wednesday Jun 24th, 2009 12:00pm
by Joshua Drake | Permalink
PostgreSQL Conference West 2009 Call for Papers
June 24th, 2009, the PostgreSQL Conference U.S. team is pleased to announce the West 2009 venue and call for papers. This year the premiere West Coast PostgreSQL Conference will be leaving its roots at Portland State University and moving north to sunny Seattle, Washington.
The event this year is being held at Seattle Central Community College from October 16th through 18th. The move to Seattle opens up a larger metropolitan area for continuing to expose databases users, developers, and administrators to the World's Most Advanced Open Source Database. Following previously successful West Coast conferences, we will be hosting a series of 3-4 hour tutorials, 90 minute mini-tutorials, and 45 minute talks.
This year we will be continuing our trend of covering the entire PostgreSQL ecosystem. We would like to see talks and tutorials on the following topics:
General PostgreSQL:
- Administration
- Performance
- High Availability
- Migration
- GIS
- Integration
- Solutions and White Papers
- Python/Django/Pylons/TurboGears/Custom
- Perl5/Catalyst/Bricolage
- Potato
- Ruby/Rails
- Java (PLJava would be great)/Groovy/Grails
- Operating System optimization (Linux/FBSD/Solaris/Windows)
- Solutions and White Papers
Submit your talk (You must be have an account on the site)
***
The PostgreSQL Conference U.S. series is an autonomous Educational Project used to educate all comers on the use of The World's Most Advanced Open Source Database. Proceeds from the event are donated directly to United States PostgreSQL; the 501c3
non-profit for PostgreSQL education and advocacy in the United States.
Pylons, PostgreSQL, and Simpycity in 60 Minutes or Less
Posted Thursday Jun 18th, 2009 10:50am
by Lacey Powers | Permalink
This is a tutorial to show you the basics of creating a small project within Pylons, with Simpycity.
This tutorial assumes that you have already set up a Linux development environment for Pylons.
Preferably on Ubuntu, though these directions should be general enough for any other Linux environment, meaning that you have installed Apache2, Python, mod-wsgi, and PostgreSQL 8.3.
Choose a working directory for your project.
For this example, I am using "project" for my working directory.
Create the working directory, if it doesn't already exist.
Move into the working directory.
Create your project:
This should give you a set of choices.
For templating, choose "genshi".
Include sqlalchemy. "False".
We will be using Simpycity instead.
You should have a directory that looks like this:
cd into the helloworld project.
The base directory should look like this:
This is the basic enviroment for your "Hello World" project.
There are several important files and directories here. To avoid confusion, I will only explain the most important, and most used files within the project.
We need to customize this environment to use Simpycity, a simple and effective ORM based on stored procedures and queries.
First, we edit the "setup.py" file. You can use your preferred text editor to open this file, e.g. Vim, Emacs, Joe, Nano, Gedit, ect. For this example, we will use the basic editor "nano".
There are only a few options that need to be modified in the base setup.py file.
Note the section that looks like this:
This section governs the requirements for your particular project. This currently shows that a version of Pylons of 0.9.7 or greater, and a version of Genshi of 0.4 or greater is required to for this project.
Since we are using Simpycity for our ORM, we need to add a line for Simpycity.
The number 0.2.1 is the current version of Simpycity.
Since new features and bug fixes are added to Simpycity regularly, you should check for updated versions regularly.
To check that the version of Simpycity is up to date, please go
Down in the middle of the page, there is an area marked "Download".
The first item in the download area is the current version of Simpycity.
(Which at the time of writing this tutorial is 0.2.1, so be sure to check.)
All that you need to do to keep Simpycity up to date is copy that version number, and paste over the version number in the install_requires stanza in setup.py.
After that, there is one last thing we need to add to the setup.py file
Near the bottom, before the closing parenthesis, we need to add another snippet of code.
This is the link to the Simpycity repository at Command Prompt.
easy_install will use that URI, combined with the version number (in this case 0.2.1) to locate and install the proper Simpycity .egg file.
Now, we save, and exit from setup.py.
Following the insertion of those lines, we will do one last check.
Sometimes, the newest python packages with easy_install require that they be built on the machine before they are deployed.
This is the case with newer versions of psycopg2. To ensure that all of the build requirements are present, run the following command:
Which should return like this:
If it does not, then running the following command:
Will install the essential libraries for you.
Now, we run the following command:
This will check and process all of the dependencies for your project, including Simpycity. This process will install the dependencies required for your project, so that they may be used within the project.
Now that we have Simpycity installed, we pause to do a bit of database setup.
With sudo become the postgres user.
It will ask you for your password.
Success should look something like this.
Now, we run a PostgreSQL command to create a user.
You are now on the postgresql command line.
While there, run the following commands.
The successful execution of these two commands should look like this:
Exit PostgreSQL with the \q command. This should drop you back to the command line.
If you haven't already, take a moment to modify your pg_hba.conf so you can easily log in.
Near the bottom, there should be a line that looks like *exactly* like this.
The very last part, "ident sameuser" should be changed to md5, so that the line looks like this:
Save and quit.
Now, execute the command:
This will reload the settings that you changed.
Exit the postgres user environment with the exit command.
Now we should be back in the "helloworld" project directory.
Next, as a convenience, we will create a .pgpass file.
cd to your home directory.
When the editor window comes up, add the following line.
Save and quit.
Following that, execute the following command.
This will appropriately set the permissions for your user.
You can check them with the following command.
Now, there is the final test.
Execute the following command.
If everything has been successfully set up, your terminal screen should look
like this:
With the command logging you in without issue, or a password prompt.
While we are here, in PostgreSQL, we should issue the following command:
Again, success should look like this:
And again, we exit with "\q".
Now, we return to the project directory with the following command.
In this directory, we edit development.ini
At line 36, uncomment the line set debug = false, and add the following lines so that the section of the configuration file looks like this:
These are the values that Simpycity requires for the database connections.
Before we go on, a little more explanation is needed, so that you better understand what is going on in later steps.
You may have noticed the "helloworld" directory within the project earlier.
This directory contains all of your project specific code is placed.
If you issue the following commands:
You will note that the directory has the following structure:
The directories here break down as follows:
Now that you have an overview of the directories within a Pylons project, we can start making some changes within this directory and its child directories.
First, we should make an important edit to lib/base.py, related to Simpycity.
The contents of the file will look like this.
We will add the following lines, to fix a troublesome issue before it starts.
For the curious, more detail can be found here at Aurynn Shaw's blog:
For purposes of this tutorial, it should be sufficient to say that this will allow Pylons and mod_wsgi to properly close and clean up connections to the database, so that connections are not left hanging idle in transaction.
So this is very important to include in any project.
We paste this in so that the controller code will look like this:
Save, and exit nano.
Next we edit environment.py
Within this file, at the very end, below the lines:
we add the follwing lines, for Simpycity setup.
And at the top of environment.py, with the rest of the imports, we add the following line.
So that the proper configuration options are read.
It should be noted here, if you need to see the debugging options from Simpycity itself, you should set db_config.debug to True.
Having added the proper configuration parameters to base.py and environment.py, we can start looking at how to serve content from Pylons. There are two basic ways to serve content through Pylons:
The trivially easy case is static content.
Execute the following command.
Paste the following content.
Save and exit.
This creates a basic static html file.
Now, from our current directory, execute the following command
This command starts the Pylons server, and is great for a bit of quick debugging, or if you haven't got Apache installed or configured.
Entering the following URI into your web browser:
You should see a simple page with the words "Hello World!".
Now, stop the server on the command line with <ctrl>-<c>.
We are moving on to the more interesting and complicated case of using a controller to serve content within Pylons.
Execute the following command:
Successful execution of this command should look something like this:
This is an interesting and useful command. As expected, it created a file called hello.py in the helloworld/controllers directory, but it also did a wonderful thing for us, and created a file in helloworld/tests/functional/test_hello.py to contain the specific unit tests for the hello.py controller.
Executing the following command:
Shows us the contents of the hello.py controller, which are very basic.
Exiting from Nano, we can also have a look at test_hello.py by executing a similar command.
The contents of which are:
The paster command has created a basic framework for a unit test of the hello.py controller. We will come back to this later in the document.
For right now, we want to see the controller in action.
But there is a bit more setup that we need to do first.
Execute the following command:
This should bring up a file that looks like this:
This is another relatively spartan, but highly important file. Within this file, the routes for the application are defined. Routes are what translates the URI within the browser into directions in the application, which point data at a specific controller, for manipulation.
This may sound a bit obtuse right now, but at the end of this particular example, it should be clear.
Currently, if you were to restart Pylons, with the aformentioned command....
And attempted to browse to the URI:
You would be presented with a very cheery orange-yellow, and black 404 page.
This is because you haven't specified the route. Without a route, Pylons has no idea what you want. So, to let it know that we want to see what is within the HelloController (located in helloworld/controller/hello.py) we will add the following line to helloworld/config/routes.py :
The first item in map.connect gives the route the name "hello" (this will be handy later). The second part is what maps the part of the URI that you put in the search bar as in:
Now, when we run:
And attempted to browse to the URI:
We will see the words "Hello World".
Note that this is different from our static html entry.
Which says "Hello World!".
And both are available.
Moving to a more generic route....
Will show you a very cheery welcome page, with the same orange-yellow and black color scheme, with links.
This is served from helloworld/public/public.html
Now, that is all fine for an example, but in an actual production application, you don't want someone to see "Welcome To Pylons" when they browse to your root URI.
So, lets fix this.
First, stop the server on the command line with <ctrl>-<c>.
Execute the command:
At the bottom, just before "return map", we will add the following line.
Why do we add this way down there, you might ask? Because the Pylons developers left us with a handy comment at the top of this file.
Taking heed of this advice, and realizing that '/' is the most generic route that you can have, we place this at the very bottom so it is chosen last in the evaluation of routes.
Again, we restart the server, from the ~/projects/hellworld directory.
And browse to:
....We still see the cheery orange-yellow and black welcome page.
(Be sure to shut down the server with <ctrl>-<c>)
Why is that!?!?
Well, there's an interesting thing about Pylons. It will evaluate the static content in helloworld/public first. In this case, it is index.html.
If we poke around a bit, we find the culprit in helloworld/config/middleware.py on line 67.
This line basically says that, look for static content first, and evaluate that over the dynamic application content.
There are two ways to solve this particular issue.
1. Execute the following command:
If you do that, Pylons won't be able to find it, and will default to the route set in routes.py.
In this case, if you restart the server, and browse to the aformentioned URI, you will see Hello World (without the exclamation point).
2. Swap the order of Cascade.
Since the Cascade command defines whether or not you evaluate static content or dynamic content first, swapping the order will mean that dynamic content is evaluated first, and static content second.
So, we execute the following command:
To return the file back to its original state. If we are incorrect, this will show us by displaying that cheery welcome page again.
So, we execute the following command:
Once there, we will copy line 67, and paste it directly below itself. Then we will comment out line 67 (we have the original line for reference), so that the file now looks like this:
With the order of app, and static_app swapped.
In this case, if you restart the server, and browse to the aformentioned URI, you will see Hello World (without the exclamation point).
And index.html is still happily sitting in helloworld/public
Now, even though we're using the HelloController in hello.py, this is still little better than a static application. So, now, we will work on making hello.py dynamic, letting it read and write to and from the database by using Simpycity.
First, we will need a simple set of tables and functions for use in PostgreSQL.
We are currently still in the ~/project/helloworld directory. We are going to move one directory back.
And make a new directory called 'sql'.
Now, we consider the following table and functions, already designed and provided for you to use.
These will be the examples that we use for the rest of the tutorial.
This is a very simple table, that contains ways to say "Hello" in different languages.
Execute the following command, in your ~/project/sql directory.
Paste the above SQL code into that file, save and quit.
Then run the following command.
Success should look like this.
Now that the necessary tables, functions, and data are in place in PostgreSQL, we can move on to making our hello model.
Execute the following command to move back into your helloworld project directory.
The directory you are in should look like this, if you execute the command ls -l :
Recall that earlier, when discussing the structure of the projects, that the "model" directory was where the files related to your ORM are placed.
We are going to create our model for our Hello World project.
Execute the following command:
And paste the following code into it:
This code sets up Simpycity for use in our application.
At the top, are the required imports from Simpycity.
There are two basic ways that Simpycity interacts with the PostgreSQL database beneath it.
Function is how Simpycity maps Python to the stored procedures in the database.
Looking at this line:
The double-quoted portion of this function, "public.add_salutation", is the schema qualified name of the stored procedure we are looking to map to.
The bracketed portion of this function, ['salutation','language'], map the arguments to the function.
For example, when we call this function:
return_status = HelloModel.add_salutation("sawadee ka","Thai")
Will map to "SELECT * FROM public.add_salutation('sawadee ka','Thai')", which will insert the salutation "namaste" and the language "hindi" into the database, and return true, which sets the value of "return_status" to true.
Raw is the Simpycity way of mapping straight SQL to the database.
For example, calling this function:
return_item = HelloModel.get_salutation()
Raw simply executes the provided query, which in this example, is:
Returning a random way to say hello into the variable "return_item".
Now, we need to modify the Hello Controller.
Execute the command:
The contents of which are:
We will add:
and:
To the hello controller, and we will comment out the line "return 'Hello World'", so that our controller now looks like this:
Now, combining the database functions we created, the HelloModel that we created, and this code, we will be able to query the database.
To see the code in action, all we have to do is start the server again.
So we execute the command
And then execute:
And open our browsers to http://localhost:5000/
This should show you a randomly chosen way to say hello.
Pressing the refresh button will show you another, and another.
Now, let's add a bit to it with a Genshi Template.
Press <ctrl>-<c> to stop your server.
Now, we refer back to the directory layout of the project.
The templates directory is where all of our dynamically generated page templates (created by Genshi, as you recall from the very beginning of this tutorial).
So we should execute the following command.
Now, we are going to look at the contents of this directory.
This only contains a blank __init__.py file. This is only because the Genshi templating engine within Pylons will ignore any template directories without an __init__.py in them.
Armed with that knowledge, we will create our own directory.
We will move into it:
and we will make a blank __init__.py file.
We will also make another file.
And within that we will paste the following contents.
Save and quit.
This is a very basic xHTML Genshi template.
Aside from having syntax for dynamically setting variables, Genshi templates can be treated the same as any other xHTML page.
Within this template are dynamically setting the title, a header, a small bit of content.
${c.hello} And ${c.language} are variables within the template, that we will set within our controller.
For more information on the Genshi templates, please consult the documentation, found here
But before we render this page, we need to make a few more modifications.
We move out of this directory, into the model directory.
And open the file hello_model.py. We will add the following content.
This is another variation of the Raw function, in which we are able to substitute variables into the query.
Save and quit.
Lastly, we move to the controller directory, and modify our controller.
We will add the following import:
Which now brings the render_genshi function into our controller, and aliases it to render.
We will also comment out "return salutation"
#return salutation
We will add the following lines:
This should leave the file looking like this:
The c. prefix to the variables are special to Genshi, and let it know what variables it should be rendering.
We are still getting a salutation from the HelloModel.get_salutation() function.
Now, we are taking that result, and using it to get the language for the saluation, using the HelloModel.get_language() function.
We are then setting the c.hello variable to the same value as the salutation variable.
And the c.language variable is set to the return value of the HelloModel.get_language() function.
return render("hello/hello.html") tells Genshi where the file we want to render is in relation to the template directory.
If hello.html was simply in the template directory, the return statement would be: return render("hello.html")
But since it is in the hello directory, we have to represent the rest of the path to the xHTML template.
And for the HelloModel Simpycity functions, we add new parameter. options=dict(fold_output=True)
Since we know these are going to return a single row only, we are collapsing the output of the query, causing it to return a value into the variable that is ready to be used by Python.
Now, to test.
Again, we back out of this directory to the outermost helloworld directory, in order to start the server.
And again, we browse to the page http:://localhost:5000 in our browser.
Which should now look something like this:
Now, displaying dynamic content is only one part of dealing with the database.
We need to be able to manipulate the content.
Deleting and updating content within the database are both crucial parts.
So, we're going to add the remaining parts to make this a well-rounded example.
So we stop the server (<ctrl>-<c>, again), and we move to the helloworld/public directory.
And we copy/paste the following files:
And we paste the following code.
Save and close.
And we paste the following code.
Save and close.
And we paste the following code.
Save and close.
Each of these files are simple, static pages, that submit forms to different actions in the hello controller.
Each of the forms has two fields in which to specify the language and salutation.
In the case of modify_salutation.html, we specify an old and new salutation.
Plus, the ever helpful submit button.
Currently, though, the hello controller only contains the index action. So we will need to modify that.
We navigate to the controllers directory.
And open hello.py for editing
We will add the following functions.
Each of these functions does basically the same thing, with small variations.
They read the request parameters, and check for essential parameters in the request dict.
Then they pull the parameters out, use them to insert, update, or delete fields in the database, and check for success.
If the change is successful, there is a page showing what the change was, and if it was not successful, it gives you a very basic error message.
Both pages redirect you back to the main page that shows the various salutations and languages.
Now, in each of these functions, there are references to rendered pages that are returned. We will go construct those now.
And we will create several files here as well:
And we will paste the following code.
Save and quit.
And we will paste the following code.
Save and quit.
And we will paste the following code.
Save and quit.
We will also add a few links to hello.html
Save and quit.
We will also add the following code to a new file called error.html
And we will paste the following code.
Save and quit.
Finally, we add a couple of lines to hello.html
This allows us to access the static pages that are in helloworld/public.
Now, if you recall from eariler, Pylons needs to know how to point these pages at the appropriate controller and action.
And it does that through the routes defined in routing.py.
So we now go modify that.
And we add the following lines
These routes will, point the pages at the appropriate controllers and actions, as advertised. The additional conditional dict specifies what sort of HTTP method (GET,POST,DELETE, ect) are supported for this URI. Here we are specifiying POST because all of the form methods associated with these URIs are POST.
And this should be the last set of modifications that we need to do to the application.
Now, we test our new code.
Which should put us into the outermost helloworld directory, with development.ini, so that we can start the server.
Now, browsing to
Should show you a page like this:
With the bottom text being HTML links.
Refreshing should take you through various incarnations of "hello" in different languages.
Now, we will add an additional greeting.
Click the link for Add a salutation.
This should take you to another very simple page with a form and a submit button.
Here, we are going to add hello for the Thai language.
Saluation: sawadee ka
Language: Thai
So we copy those words into the appropriate form boxes, and click "Submit".
We should be taken to a page that looks like this.
And if we were to look in the database at this time, we would see the entry has been successfully entered into the database.
The chain of events is as follows.
We can now click to see our newly added salutation and language. (which may take a few tries, since it is randomly selected.)
We can modify a salutation as well.
Click the modify salutation link, which will bring us to the modify salutation page.
We will add capitalization to the Thai greeting that we just added.
Type the following into the form pages:
Old Salutation: sawadee ka
New Salutation: Sawadee Ka
And click submit.
Again, you will be greeted by a page that looks like this.
And if we check the database here as well...
We note that the salutation is now capitalized.
The lifecycle of this event is almost exactly the same as the one noted above, except in two places.
The routing points at the modify_salutation function, and the upd_salutation function is translated into
Finally, if we decide that we no longer want the salutation Sawadee Ka in the database, we can delete with a very similar lifecycle.
Click on the Remove a Salutation Link.
Salutation: Sawadee Ka (note that it has to be capitalized now)
Language: Thai
Checking the database, we note that it is indeed removed.
And as noted above, the lifecycle is the same.
The routing points at the remove_salutation function, and the del_salutation function is translated into
Thus, removing it from the database.
This now completes our small web application.
Combining Simpycity, Pylons and PostgreSQL, we have made an app that displays database content, and creates, updates, and deletes that content, which is the core functionality of any database driven web application.
And this also concludes our PostgreSQL->Simpycity->Pylons tutorial.
Thank you for following along. =)
Posted Thursday Jun 18th, 2009 10:50am
by Lacey Powers | Permalink
This is a tutorial to show you the basics of creating a small project within Pylons, with Simpycity.
This tutorial assumes that you have already set up a Linux development environment for Pylons.
Preferably on Ubuntu, though these directions should be general enough for any other Linux environment, meaning that you have installed Apache2, Python, mod-wsgi, and PostgreSQL 8.3.
Choose a working directory for your project.
For this example, I am using "project" for my working directory.
Create the working directory, if it doesn't already exist.
mkdir project
Move into the working directory.
cd project
Create your project:
paster create -t pylons helloworld
This should give you a set of choices.
Enter template_engine (mako/genshi/jinja2/etc: Template language) ['mako']: genshi
For templating, choose "genshi".
Enter sqlalchemy (True/False: Include SQLAlchemy 0.5 configuration) [False]: False
Include sqlalchemy. "False".
We will be using Simpycity instead.
You should have a directory that looks like this:
lacey@blinky:~/project$ ls -l total 4.0K drwxr-xr-x 5 lacey lacey 4.0K 2009-06-05 08:46 helloworld lacey@blinky:~/project$
cd into the helloworld project.
cd helloworld
The base directory should look like this:
lacey@blinky:~/project/helloworld$ ls -l total 48K -rw-r--r-- 1 lacey lacey 1.6K 2009-06-05 08:46 development.ini drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 docs -rw-r--r-- 1 lacey lacey 9.5K 2009-06-05 08:46 ez_setup.py drwxr-xr-x 9 lacey lacey 4.0K 2009-06-05 08:46 helloworld drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 helloworld.egg-info -rw-r--r-- 1 lacey lacey 125 2009-06-05 08:46 MANIFEST.in -rw-r--r-- 1 lacey lacey 463 2009-06-05 08:46 README.txt -rw-r--r-- 1 lacey lacey 597 2009-06-05 08:46 setup.cfg -rw-r--r-- 1 lacey lacey 959 2009-06-05 08:46 setup.py -rw-r--r-- 1 lacey lacey 509 2009-06-05 08:46 test.ini lacey@blinky:~/project/helloworld$
This is the basic enviroment for your "Hello World" project.
There are several important files and directories here. To avoid confusion, I will only explain the most important, and most used files within the project.
setup.py -- A command and control file for the project.
With setup.py, you can run unit tests, update the packages
associated with the project, and package it into a Python .egg.
test.ini -- The test configuration file for the project.
This file mostly inherits from the development.ini file, but if
you want to have testing specific settings for this project,
you put them here. For now, this will just inherit
from development.ini.
development.ini -- This file contains basic configuration of the project.
This is where you toggle debugging settings, add database
configuration information, and so forth.
We need to customize this environment to use Simpycity, a simple and effective ORM based on stored procedures and queries.
First, we edit the "setup.py" file. You can use your preferred text editor to open this file, e.g. Vim, Emacs, Joe, Nano, Gedit, ect. For this example, we will use the basic editor "nano".
nano setup.py
There are only a few options that need to be modified in the base setup.py file.
Note the section that looks like this:
install_requires=[
"Pylons>=0.9.7",
"Genshi>=0.4",
],
This section governs the requirements for your particular project. This currently shows that a version of Pylons of 0.9.7 or greater, and a version of Genshi of 0.4 or greater is required to for this project.
Since we are using Simpycity for our ORM, we need to add a line for Simpycity.
install_requires=[
"Pylons>=0.9.7",
"Genshi>=0.4",
"Simpycity>=0.2.1"
],
The number 0.2.1 is the current version of Simpycity.
Since new features and bug fixes are added to Simpycity regularly, you should check for updated versions regularly.
To check that the version of Simpycity is up to date, please go
Down in the middle of the page, there is an area marked "Download".
The first item in the download area is the current version of Simpycity.
(Which at the time of writing this tutorial is 0.2.1, so be sure to check.)
The current release version of Simpycity is 0.2.1, which can be downloaded from here
All that you need to do to keep Simpycity up to date is copy that version number, and paste over the version number in the install_requires stanza in setup.py.
After that, there is one last thing we need to add to the setup.py file
Near the bottom, before the closing parenthesis, we need to add another snippet of code.
dependency_links=[
"https://projects.commandprompt.com/public/simpycity/repo/dist/"
]
This is the link to the Simpycity repository at Command Prompt.
easy_install will use that URI, combined with the version number (in this case 0.2.1) to locate and install the proper Simpycity .egg file.
Now, we save, and exit from setup.py.
Following the insertion of those lines, we will do one last check.
Sometimes, the newest python packages with easy_install require that they be built on the machine before they are deployed.
This is the case with newer versions of psycopg2. To ensure that all of the build requirements are present, run the following command:
aptitude search postgresql-server-dev-8.3 python2.5-dev build-essential
Which should return like this:
lacey@blinky:~/project/helloworld$ aptitude search postgresql-server-dev-8.3
python2.5-dev build-essential
i A build-essential - Informational list of build-essential packages
i postgresql-server-dev-8.3 - development files for PostgreSQL 8.3 server-side programming
i python2.5-dev - Header files and a static library for Python (v2.5)
lacey@blinky:~/project/helloworld$
If it does not, then running the following command:
sudo aptitude install postgresql-server-dev-8.3 python2.5-dev build-essential
Will install the essential libraries for you.
Now, we run the following command:
sudo python setup.py develop
This will check and process all of the dependencies for your project, including Simpycity. This process will install the dependencies required for your project, so that they may be used within the project.
Now that we have Simpycity installed, we pause to do a bit of database setup.
With sudo become the postgres user.
sudo su - postgres
It will ask you for your password.
Success should look something like this.
lacey@blinky:~/project/helloworld$ sudo su - postgres [sudo] password for lacey: postgres@blinky:~$
Now, we run a PostgreSQL command to create a user.
postgres@blinky:~$ psql -U postgres
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=#
You are now on the postgresql command line.
While there, run the following commands.
CREATE USER helloworld WITH ENCRYPTED PASSWORD '12345'; CREATE DATABASE helloworld WITH OWNER helloworld;
The successful execution of these two commands should look like this:
postgres=# CREATE USER helloworld WITH ENCRYPTED PASSWORD '12345'; CREATE ROLE postgres=# CREATE DATABASE helloworld WITH OWNER helloworld; CREATE DATABASE postgres=#
Exit PostgreSQL with the \q command. This should drop you back to the command line.
postgres=# \q postgres@blinky:~$
If you haven't already, take a moment to modify your pg_hba.conf so you can easily log in.
nano /etc/postgresql/8.3/main/pg_hba.conf
Near the bottom, there should be a line that looks like *exactly* like this.
local all all ident sameuser
The very last part, "ident sameuser" should be changed to md5, so that the line looks like this:
local all all md5
Save and quit.
Now, execute the command:
/etc/init.d/postgresql-8.3 force-reload
This will reload the settings that you changed.
postgres@blinky:~$ /etc/init.d/postgresql-8.3 force-reload * Reloading PostgreSQL 8.3 database server [ OK ] postgres@blinky:~$
Exit the postgres user environment with the exit command.
postgres@blinky:~$ exit logout lacey@blinky:~/project/helloworld$
Now we should be back in the "helloworld" project directory.
Next, as a convenience, we will create a .pgpass file.
cd to your home directory.
cd ~
nano .pgpass
When the editor window comes up, add the following line.
localhost:5432:*:helloworld:12345
Save and quit.
Following that, execute the following command.
chmod 600 .pgpass
This will appropriately set the permissions for your user.
You can check them with the following command.
lacey@blinky:~$ ls -l .pgpass -rw------- 1 lacey lacey 135 2009-06-05 12:10 .pgpass lacey@blinky:~$
Now, there is the final test.
Execute the following command.
psql -U helloworld
If everything has been successfully set up, your terminal screen should look
like this:
lacey@blinky:~$ psql -U helloworld
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
helloworld=>
With the command logging you in without issue, or a password prompt.
While we are here, in PostgreSQL, we should issue the following command:
CREATE LANGUAGE plpgsql;
Again, success should look like this:
helloworld=> CREATE LANGUAGE plpgsql; CREATE LANGUAGE helloworld=>
And again, we exit with "\q".
helloworld=> \q lacey@blinky:~$
Now, we return to the project directory with the following command.
cd project/helloworld/
In this directory, we edit development.ini
nano development.ini
At line 36, uncomment the line set debug = false, and add the following lines so that the section of the configuration file looks like this:
db.database = helloworld db.user = helloworld db.host = localhost db.port = 5432 db.password = 12345
These are the values that Simpycity requires for the database connections.
database = a database that belongs to the user. user = the user that you are connecting as. host = the server being connected to. localhost for a development machine. port = the port that PostgreSQL expects you to connect on. password = the password for the user.
Before we go on, a little more explanation is needed, so that you better understand what is going on in later steps.
You may have noticed the "helloworld" directory within the project earlier.
lacey@blinky:~/project/helloworld$ ls -l total 48K -rw-r--r-- 1 lacey lacey 1.6K 2009-06-05 08:46 development.ini drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 docs -rw-r--r-- 1 lacey lacey 9.5K 2009-06-05 08:46 ez_setup.py drwxr-xr-x 9 lacey lacey 4.0K 2009-06-05 08:46 helloworld drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 helloworld.egg-info -rw-r--r-- 1 lacey lacey 125 2009-06-05 08:46 MANIFEST.in -rw-r--r-- 1 lacey lacey 463 2009-06-05 08:46 README.txt -rw-r--r-- 1 lacey lacey 597 2009-06-05 08:46 setup.cfg -rw-r--r-- 1 lacey lacey 959 2009-06-05 08:46 setup.py -rw-r--r-- 1 lacey lacey 509 2009-06-05 08:46 test.ini lacey@blinky:~/project/helloworld$
This directory contains all of your project specific code is placed.
If you issue the following commands:
cd helloworld ls -l
You will note that the directory has the following structure:
lacey@blinky:~/project/helloworld/helloworld$ ls -l total 32K drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 config drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 controllers -rw-r--r-- 1 lacey lacey 0 2009-06-05 08:46 __init__.py drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 lib drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 model drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 public drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 templates drwxr-xr-x 3 lacey lacey 4.0K 2009-06-05 08:46 tests -rw-r--r-- 1 lacey lacey 296 2009-06-05 08:46 websetup.py lacey@blinky:~/project/helloworld/helloworld$
The directories here break down as follows:
config -- Configuration files for your application as a whole.
controllers -- This is where most of the application logic goes.
The files here control what you display, and how you display it.
lib -- This is where miscellaneous files that are necessary for your
application, but have no distinct place go.
model -- This is where the files related to the ORM go.
This will be where most of the files related
to the database and Simpycity will live.
public -- This is where static html, docs, css, and such live.
templates -- This is where Genshi will create the dynamic html templates
for the dynamic pages that you will use.
__init__.py -- This is a helper file for packaging your application.
That will be covered later in a different tutorial.
tests -- This is where your unit tests live.
These test controllers and other application functionality.
websetup.py -- This is another helper file for packaging your application.
It too will be covered later in a different tutorial.
Now that you have an overview of the directories within a Pylons project, we can start making some changes within this directory and its child directories.
First, we should make an important edit to lib/base.py, related to Simpycity.
nano lib/base.py
The contents of the file will look like this.
"""The base Controller API
Provides the BaseController class for subclassing.
"""
from pylons.controllers import WSGIController
from pylons.templating import render_genshi as render
class BaseCoOne line ntroller(WSGIController):
def __call__(self, environ, start_response):
"""Invoke the Controller"""
# WSGIController.__call__ dispatches to the Controller method
# the request is routed to. This routing information is
# available in environ['pylons.routes_dict']
return WSGIController.__call__(self, environ, start_response)
We will add the following lines, to fix a troublesome issue before it starts.
For the curious, more detail can be found here at Aurynn Shaw's blog:
For purposes of this tutorial, it should be sufficient to say that this will allow Pylons and mod_wsgi to properly close and clean up connections to the database, so that connections are not left hanging idle in transaction.
So this is very important to include in any project.
import psycopg2
import psycopg2.extensions
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
from simpycity.handle import Manager
m = Manager()
try:
return WSGIController.__call__(self, environ, start_response)
finally:
# Shut down *all* the extant handles.
m.close()
We paste this in so that the controller code will look like this:
One line
"""The base Controller API
Provides the BaseController class for subclassing.
"""
from pylons.controllers import WSGIController
from pylons.templating import render_genshi as render
class BaseController(WSGIController):
def __call__(self, environ, start_response):
"""Invoke the Controller"""
# WSGIController.__call__ dispatches to the Controller method
# the request is routed to. This routing information is
# available in environ['pylons.routes_dict']
#return WSGIController.__call__(self, environ, start_response)
import psycopg2
import psycopg2.extensions
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
from simpycity.handle import Manager
m = Manager()
try:
return WSGIController.__call__(self, environ, start_response)
finally:
# Shut down *all* the extant handles.
m.close()
Save, and exit nano.
Next we edit environment.py
One line nano config/environment.py
Within this file, at the very end, below the lines:
# CONFIGURATION OPTIONS HERE (note: all config options will override # any Pylons config options)
we add the follwing lines, for Simpycity setup.
app_conf = config['app_conf'] db_config.port = app_conf['db.port'] db_config.database= app_conf['db.database'] db_config.host= app_conf['db.host'] db_config.user = app_conf['db.user'] db_config.password = app_conf['db.password'] db_config.debug = False
And at the top of environment.py, with the rest of the imports, we add the following line.
from simpycity import config as db_config
So that the proper configuration options are read.
It should be noted here, if you need to see the debugging options from Simpycity itself, you should set db_config.debug to True.
Having added the proper configuration parameters to base.py and environment.py, we can start looking at how to serve content from Pylons. There are two basic ways to serve content through Pylons:
1. To server static content from the public/ directory. 2. Use a controller, which contains the logic for what to control and how to display it.
The trivially easy case is static content.
Execute the following command.
nano helloworld/public/hello.html
Paste the following content.
<html>
<body>
Hello World!
</body>
</html>
Save and exit.
This creates a basic static html file.
Now, from our current directory, execute the following command
paster serve --reload development.ini
This command starts the Pylons server, and is great for a bit of quick debugging, or if you haven't got Apache installed or configured.
Entering the following URI into your web browser:
http://localhost:5000/hello.html
You should see a simple page with the words "Hello World!".
Now, stop the server on the command line with <ctrl>-<c>.
We are moving on to the more interesting and complicated case of using a controller to serve content within Pylons.
Execute the following command:
paster controller hello
Successful execution of this command should look something like this:
lacey@blinky:~/project/helloworld$ paster controller hello
Creating /home/lacey/project/helloworld/helloworld/controllers/hello.py
Creating /home/lacey/project/helloworld/helloworld/tests/functional/test_hello.py
This is an interesting and useful command. As expected, it created a file called hello.py in the helloworld/controllers directory, but it also did a wonderful thing for us, and created a file in helloworld/tests/functional/test_hello.py to contain the specific unit tests for the hello.py controller.
Executing the following command:
nano helloworld/controller/hello.py
Shows us the contents of the hello.py controller, which are very basic.
import logging
from pylons import request, response, session, tmpl_context as c
from pylons.controllers.util import abort, redirect_to
from helloworld.lib.base import BaseController, render
log = logging.getLogger(__name__)
class HelloController(BaseController):
def index(self):
# Return a rendered template
#return render('/hello.mako')
# or, return a response
return 'Hello World'
Exiting from Nano, we can also have a look at test_hello.py by executing a similar command.
nano helloworld/tests/functional/test_hello.py
The contents of which are:
from helloworld.tests import *
class TestHelloController(TestController):
def test_index(self):
response = self.app.get(url(controller='hello', action='index'))
# Test response...
The paster command has created a basic framework for a unit test of the hello.py controller. We will come back to this later in the document.
For right now, we want to see the controller in action.
But there is a bit more setup that we need to do first.
Execute the following command:
nano helloworld/config/routes.py
This should bring up a file that looks like this:
"""Routes configuration
The more specific and detailed routes should be defined first so they
may take precedent over the more generic routes. For more information
refer to the routes manual at http://routes.groovie.org/docs/
"""
from pylons import config
from routes import Mapper
def make_map():
"""Create, configure and return the routes Mapper"""
map = Mapper(directory=config['pylons.paths']['controllers'],
always_scan=config['debug'])
map.minimization = False
# The ErrorController route (handles 404/500 error pages); it should
# likely stay at the top, ensuring it can always be resolved
map.connect('/error/{action}', controller='error')
map.connect('/error/{action}/{id}', controller='error')
# CUSTOM ROUTES HERE
map.connect('/{controller}/{action}')
map.connect('/{controller}/{action}/{id}')
return map
This is another relatively spartan, but highly important file. Within this file, the routes for the application are defined. Routes are what translates the URI within the browser into directions in the application, which point data at a specific controller, for manipulation.
This may sound a bit obtuse right now, but at the end of this particular example, it should be clear.
Currently, if you were to restart Pylons, with the aformentioned command....
paster serve --reload development.ini
And attempted to browse to the URI:
http://localhost:5000/hello
You would be presented with a very cheery orange-yellow, and black 404 page.
This is because you haven't specified the route. Without a route, Pylons has no idea what you want. So, to let it know that we want to see what is within the HelloController (located in helloworld/controller/hello.py) we will add the following line to helloworld/config/routes.py :
map.connect('hello', '/hello', controller='hello', action='index')
The first item in map.connect gives the route the name "hello" (this will be handy later). The second part is what maps the part of the URI that you put in the search bar as in:
http://localhost:5000/hello
Now, when we run:
paster serve --reload development.ini
And attempted to browse to the URI:
http://localhost:5000/hello
We will see the words "Hello World".
Note that this is different from our static html entry.
http://localhost:5000/hello.html
Which says "Hello World!".
And both are available.
Moving to a more generic route....
http://localhost:5000
Will show you a very cheery welcome page, with the same orange-yellow and black color scheme, with links.
This is served from helloworld/public/public.html
Now, that is all fine for an example, but in an actual production application, you don't want someone to see "Welcome To Pylons" when they browse to your root URI.
So, lets fix this.
First, stop the server on the command line with <ctrl>-<c>.
Execute the command:
nano helloworld/config/routes.py
At the bottom, just before "return map", we will add the following line.
map.connect('root', '/', controller='hello', action='index')
Why do we add this way down there, you might ask? Because the Pylons developers left us with a handy comment at the top of this file.
The more specific and detailed routes should be defined first so they may take precedent over the more generic routes.
Taking heed of this advice, and realizing that '/' is the most generic route that you can have, we place this at the very bottom so it is chosen last in the evaluation of routes.
Again, we restart the server, from the ~/projects/hellworld directory.
paster serve --reload development.ini
And browse to:
http://localhost:5000
....We still see the cheery orange-yellow and black welcome page.
(Be sure to shut down the server with <ctrl>-<c>)
Why is that!?!?
Well, there's an interesting thing about Pylons. It will evaluate the static content in helloworld/public first. In this case, it is index.html.
If we poke around a bit, we find the culprit in helloworld/config/middleware.py on line 67.
app = Cascade([static_app, app])
This line basically says that, look for static content first, and evaluate that over the dynamic application content.
There are two ways to solve this particular issue.
1. Execute the following command:
mv helloworld/public/index.html helloworld/public/index.html.dont_evaluate_me_pylons
If you do that, Pylons won't be able to find it, and will default to the route set in routes.py.
In this case, if you restart the server, and browse to the aformentioned URI, you will see Hello World (without the exclamation point).
2. Swap the order of Cascade.
Since the Cascade command defines whether or not you evaluate static content or dynamic content first, swapping the order will mean that dynamic content is evaluated first, and static content second.
So, we execute the following command:
mv helloworld/public/index.html.dont_evaluate_me_pylons helloworld/public/index.html
To return the file back to its original state. If we are incorrect, this will show us by displaying that cheery welcome page again.
So, we execute the following command:
nano helloworld/config/middleware.py
Once there, we will copy line 67, and paste it directly below itself. Then we will comment out line 67 (we have the original line for reference), so that the file now looks like this:
#app = Cascade([static_app, app]) app = Cascade([app, static_app])
With the order of app, and static_app swapped.
In this case, if you restart the server, and browse to the aformentioned URI, you will see Hello World (without the exclamation point).
And index.html is still happily sitting in helloworld/public
lacey@blinky:~/project/helloworld$ ls -l helloworld/public/index.html -rw-r--r-- 1 lacey lacey 4.6K 2009-06-05 08:46 helloworld/public/index.html
Now, even though we're using the HelloController in hello.py, this is still little better than a static application. So, now, we will work on making hello.py dynamic, letting it read and write to and from the database by using Simpycity.
First, we will need a simple set of tables and functions for use in PostgreSQL.
We are currently still in the ~/project/helloworld directory. We are going to move one directory back.
cd ..
And make a new directory called 'sql'.
mkdir sql
Now, we consider the following table and functions, already designed and provided for you to use.
These will be the examples that we use for the rest of the tutorial.
This is a very simple table, that contains ways to say "Hello" in different languages.
CREATE TABLE hello
(
salutation text not null primary key,
language text not null
);
-- Various formal ways of saying hello or good morning.
INSERT INTO hello (salutation, language) VALUES ('hello','English');
INSERT INTO hello (salutation, language) VALUES ('hola','Spanish');
INSERT INTO hello (salutation, language) VALUES ('bonjour','French');
INSERT INTO hello (salutation, language) VALUES ('merhaba selam','Turkish');
INSERT INTO hello (salutation, language) VALUES ('zdravstvuyte','Russian');
INSERT INTO hello (salutation, language) VALUES ('buon giorno','Italian');
CREATE OR REPLACE FUNCTION add_salutation
(in_salutation text, in_language text)
RETURNS boolean AS $BODY$
DECLARE
BEGIN
INSERT INTO hello (salutation, language)
VALUES (in_salutation, in_language);
RETURN TRUE;
END;
$BODY$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION remove_salutation
(in_salutation text, in_language text)
RETURNS boolean AS $BODY$
DECLARE
BEGIN
DELETE FROM hello
WHERE salutation = in_salutation
AND language = in_language;
RETURN TRUE;
END;
$BODY$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION update_salutation
(in_old_salutation text, in_new_salutation text)
RETURNS boolean AS $BODY$
DECLARE
BEGIN
UPDATE hello
SET salutation = in_new_salutation
WHERE salutation = in_old_salutation;
RETURN TRUE;
END;
$BODY$ LANGUAGE PLPGSQL;
Execute the following command, in your ~/project/sql directory.
nano hello.sql
Paste the above SQL code into that file, save and quit.
Then run the following command.
psql -U helloworld helloworld -f hello.sql
Success should look like this.
lacey@blinky:~/project/sql$ psql -U helloworld helloworld -f hello.sql psql:hello.sql:5: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "hello_pkey" for table "hello" CREATE TABLE INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION lacey@blinky:~/project/sql$
Now that the necessary tables, functions, and data are in place in PostgreSQL, we can move on to making our hello model.
Execute the following command to move back into your helloworld project directory.
cd ../helloworld/helloworld
The directory you are in should look like this, if you execute the command ls -l :
lacey@blinky:~/project/helloworld/helloworld$ ls -l total 36K drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 16:52 config drwxr-xr-x 2 lacey lacey 4.0K 2009-06-06 18:57 controllers -rw-r--r-- 1 lacey lacey 0 2009-06-05 08:46 __init__.py -rw-r--r-- 1 lacey lacey 140 2009-06-05 15:07 __init__.pyc drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 15:26 lib drwxr-xr-x 2 lacey lacey 4.0K 2009-06-06 19:35 model drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 16:40 public drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 templates drwxr-xr-x 3 lacey lacey 4.0K 2009-06-05 08:46 tests -rw-r--r-- 1 lacey lacey 296 2009-06-05 08:46 websetup.py lacey@blinky:~/project/helloworld/helloworld$
Recall that earlier, when discussing the structure of the projects, that the "model" directory was where the files related to your ORM are placed.
We are going to create our model for our Hello World project.
Execute the following command:
nano model/hello_model.py
And paste the following code into it:
from simpycity.core import Function, Raw
from simpycity.model import SimpleModel, Function, Construct
from simpycity.core import Raw
class HelloModel(SimpleModel):
# Getters
get_salutation = Raw("SELECT salutation
FROM public.hello
ORDER BY random() LIMIT 1")
# Setters
add_salutation = Function("public.add_salutation",
['salutation','language'])
upd_salutation = Function("public.update_salutation",
['old_salutation','new_salutation'])
# Deleters
del_salutation = Function("public.remove_salutation",
['salutation','language'])
This code sets up Simpycity for use in our application.
At the top, are the required imports from Simpycity.
There are two basic ways that Simpycity interacts with the PostgreSQL database beneath it.
Function is how Simpycity maps Python to the stored procedures in the database.
Looking at this line:
add_salutation = Function("public.add_salutation",['salutation','language'])
The double-quoted portion of this function, "public.add_salutation", is the schema qualified name of the stored procedure we are looking to map to.
The bracketed portion of this function, ['salutation','language'], map the arguments to the function.
For example, when we call this function:
return_status = HelloModel.add_salutation("sawadee ka","Thai")
Will map to "SELECT * FROM public.add_salutation('sawadee ka','Thai')", which will insert the salutation "namaste" and the language "hindi" into the database, and return true, which sets the value of "return_status" to true.
Raw is the Simpycity way of mapping straight SQL to the database.
get_salutation = Raw("SELECT salutation
FROM public.hello
ORDER BY random() LIMIT 1")
For example, calling this function:
return_item = HelloModel.get_salutation()
Raw simply executes the provided query, which in this example, is:
SELECT salutation FROM public.hello ORDER BY random() LIMIT 1;
Returning a random way to say hello into the variable "return_item".
Now, we need to modify the Hello Controller.
Execute the command:
nano controller/hello.py
The contents of which are:
import logging
from pylons import request, response, session, tmpl_context as c
from pylons.controllers.util import abort, redirect_to
from helloworld.lib.base import BaseController, render
log = logging.getLogger(__name__)
class HelloController(BaseController):
def index(self):
# Return a rendered template
#return render('/hello.mako')
# or, return a response
return 'Hello World'
We will add:
from helloworld.model.hello_model import HelloModel
and:
salutation = HelloModel.get_salutation(options=dict(fold_output=True)) return salutation
To the hello controller, and we will comment out the line "return 'Hello World'", so that our controller now looks like this:
import logging
from pylons import request, response, session, tmpl_context as c
from pylons.controllers.util import abort, redirect_to
from helloworld.lib.base import BaseController, render
from helloworld.model.hello_model import HelloModel
log = logging.getLogger(__name__)
class HelloController(BaseController):
def index(self):
# Return a rendered template
#return render('/hello.mako')
# or, return a response
#return 'Hello World'
salutation = HelloModel.get_salutation(options=dict(fold_output=True))
return salutation
Now, combining the database functions we created, the HelloModel that we created, and this code, we will be able to query the database.
To see the code in action, all we have to do is start the server again.
So we execute the command
cd ..
And then execute:
paster serve --reload development.ini
And open our browsers to http://localhost:5000/
This should show you a randomly chosen way to say hello.
Pressing the refresh button will show you another, and another.
Now, let's add a bit to it with a Genshi Template.
Press <ctrl>-<c> to stop your server.
Now, we refer back to the directory layout of the project.
lacey@blinky:~/project/helloworld/helloworld$ ls -l total 36K drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 16:52 config drwxr-xr-x 2 lacey lacey 4.0K 2009-06-06 18:57 controllers -rw-r--r-- 1 lacey lacey 0 2009-06-05 08:46 __init__.py -rw-r--r-- 1 lacey lacey 140 2009-06-05 15:07 __init__.pyc drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 15:26 lib drwxr-xr-x 2 lacey lacey 4.0K 2009-06-06 19:35 model drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 16:40 public drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 templates drwxr-xr-x 3 lacey lacey 4.0K 2009-06-05 08:46 tests -rw-r--r-- 1 lacey lacey 296 2009-06-05 08:46 websetup.py lacey@blinky:~/project/helloworld/helloworld$
The templates directory is where all of our dynamically generated page templates (created by Genshi, as you recall from the very beginning of this tutorial).
So we should execute the following command.
cd helloworld/templates
Now, we are going to look at the contents of this directory.
lacey@blinky:~/project/helloworld/helloworld/templates$ ls -l total 0 -rw-r--r-- 1 lacey lacey 0 2009-06-05 08:46 __init__.py lacey@blinky:~/project/helloworld/helloworld/templates$
This only contains a blank __init__.py file. This is only because the Genshi templating engine within Pylons will ignore any template directories without an __init__.py in them.
Armed with that knowledge, we will create our own directory.
mkdir hello
We will move into it:
cd hello
and we will make a blank __init__.py file.
touch __init__.py
We will also make another file.
nano hello.html
And within that we will paste the following contents.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>${c.hello}</title>
</head>
<body class="index">
<div id="header">
<h1>${c.hello}</h1>
</div>
<p>That is hello in: ${c.language}</p>
</body>
</html>
Save and quit.
This is a very basic xHTML Genshi template.
Aside from having syntax for dynamically setting variables, Genshi templates can be treated the same as any other xHTML page.
Within this template are dynamically setting the title, a header, a small bit of content.
${c.hello} And ${c.language} are variables within the template, that we will set within our controller.
For more information on the Genshi templates, please consult the documentation, found here
But before we render this page, we need to make a few more modifications.
We move out of this directory, into the model directory.
cd ../../model
And open the file hello_model.py. We will add the following content.
get_language = Raw("SELECT language
FROM public.hello
WHERE salutation = %s", ['salutation']);
This is another variation of the Raw function, in which we are able to substitute variables into the query.
Save and quit.
Lastly, we move to the controller directory, and modify our controller.
cd ../controller
nano hello.py
We will add the following import:
from pylons.templating import render_genshi as render
Which now brings the render_genshi function into our controller, and aliases it to render.
We will also comment out "return salutation"
#return salutation
We will add the following lines:
c.language = HelloModel.get_language(salutation,
options=dict(fold_output=True))
c.hello = salutation
return render("hello/hello.html");
This should leave the file looking like this:
import logging
from pylons import request, response, session, tmpl_context as c
from pylons.controllers.util import abort, redirect_to
from helloworld.lib.base import BaseController, render
from helloworld.model.hello_model import HelloModel
from pylons.templating import render_genshi as render
log = logging.getLogger(__name__)
class HelloController(BaseController):
def index(self):
# Return a rendered template
#return render('/hello.mako')
# or, return a response
#return 'Hello World'
salutation = HelloModel.get_salutation(options=dict(fold_output=True))
c.language = HelloModel.get_language(salutation,
options=dict(fold_output=True))
c.hello = salutation
return render("hello/hello.html");
#return salutation
The c. prefix to the variables are special to Genshi, and let it know what variables it should be rendering.
We are still getting a salutation from the HelloModel.get_salutation() function.
Now, we are taking that result, and using it to get the language for the saluation, using the HelloModel.get_language() function.
We are then setting the c.hello variable to the same value as the salutation variable.
And the c.language variable is set to the return value of the HelloModel.get_language() function.
return render("hello/hello.html") tells Genshi where the file we want to render is in relation to the template directory.
If hello.html was simply in the template directory, the return statement would be: return render("hello.html")
But since it is in the hello directory, we have to represent the rest of the path to the xHTML template.
And for the HelloModel Simpycity functions, we add new parameter. options=dict(fold_output=True)
Since we know these are going to return a single row only, we are collapsing the output of the query, causing it to return a value into the variable that is ready to be used by Python.
Now, to test.
Again, we back out of this directory to the outermost helloworld directory, in order to start the server.
cd ../..
paster serve --reload development.ini
And again, we browse to the page http:://localhost:5000 in our browser.
Which should now look something like this:
bonjour That is hello in: French
Now, displaying dynamic content is only one part of dealing with the database.
We need to be able to manipulate the content.
Deleting and updating content within the database are both crucial parts.
So, we're going to add the remaining parts to make this a well-rounded example.
So we stop the server (<ctrl>-<c>, again), and we move to the helloworld/public directory.
cd helloworld/public
And we copy/paste the following files:
nano add_salutation.html
And we paste the following code.
<html>
<body>
<form action="/hello/add_salutation" method="POST">
<p>
Add Salutation:<br/>
Salutation: <input type="text" name="salutation"><br/>
Language: <input type="text" name="language"><br/>
<input type="submit" value="submit">
</p>
</form>
</body>
</html>
Save and close.
nano modify_salutation.html
And we paste the following code.
<html>
<body>
<form action="/hello/modify_salutation" method="POST">
<p>
Modify Salutation:<br/>
Old Salutation: <input type="text" name="old_salutation"><br/>
New Salutation: <input type="text" name="new_salutation"><br/>
<input type="submit" value="submit">
</p>
</form>
</body>
</html>
Save and close.
nano remove_salutation.html
And we paste the following code.
<html>
<body>
<form action="/hello/remove_salutation" method="POST">
<p>
Remove Salutation:<br/>
Salutation: <input type="text" name="salutation"><br/>
Language: <input type="text" name="language"><br/>
<input type="submit" value="submit">
</p>
</form>
</body>
</html>
Save and close.
Each of these files are simple, static pages, that submit forms to different actions in the hello controller.
Each of the forms has two fields in which to specify the language and salutation.
In the case of modify_salutation.html, we specify an old and new salutation.
Plus, the ever helpful submit button.
Currently, though, the hello controller only contains the index action. So we will need to modify that.
We navigate to the controllers directory.
cd ../controllers
And open hello.py for editing
nano hello.py
We will add the following functions.
def add_salutation(self):
if 'salutation' in request.params:
if 'language' in request.params:
salutation = request.params['salutation']
language = request.params['language']
rs = HelloModel.add_salutation(salutation, language)
return_status = rs.next()[0]
rs.commit()
if return_status == True:
c.language = language
c.hello = salutation
response.status = '200 OK'
return render("hello/added_salutation.html")
else:
c.language = language
c.hello = salutation
return render("hello/error.html")
def remove_salutation(self):
if 'salutation' in request.params:
if 'language' in request.params:
salutation = request.params['salutation']
language = request.params['language']
rs = HelloModel.del_salutation(salutation, language)
return_status = rs.next()[0]
rs.commit()
if return_status == True:
c.hello = salutation
c.language = language
response.status = '200 OK'
return render("hello/removed_salutation.html")
else:
c.hello = salutation
c.language = language
return render("hello/error.html")
def modify_salutation(self):
if 'old_salutation' in request.params:
if 'old_salutation' in request.params:
old_salutation = request.params['old_salutation']
new_salutation = request.params['new_salutation']
rs = HelloModel.upd_salutation(old_salutation, new_salutation)
return_status = rs.next()[0]
rs.commit()
if return_status == True:
c.old = old_salutation
c.new = new_salutation
response.status = '200 OK'
return render("hello/modified_salutation.html")
else:
c.old = old_salutation
c.new = new_salutation
return render("hello/error.html")
Each of these functions does basically the same thing, with small variations.
They read the request parameters, and check for essential parameters in the request dict.
Then they pull the parameters out, use them to insert, update, or delete fields in the database, and check for success.
If the change is successful, there is a page showing what the change was, and if it was not successful, it gives you a very basic error message.
Both pages redirect you back to the main page that shows the various salutations and languages.
Now, in each of these functions, there are references to rendered pages that are returned. We will go construct those now.
cd ../templates/hello
And we will create several files here as well:
nano added_salutation.html
And we will paste the following code.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>SUCCESS</title>
</head>
<body class="index">
<div id="header">
<h1>SUCCESS: Added:</h1>
</div>
<p>${c.hello}</p>
<p>${c.language}</p>
<p/>
<a href="/hello">Return home</a>
</body>
</html>
Save and quit.
nano modified_salutation.html
And we will paste the following code.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>SUCCESS</title>
</head>
<body class="index">
<div id="header">
<h1>SUCCESS: Modified:</h1>
</div>
<p>${c.old}</p>
<p>${c.new}</p>
<p/>
<a href="/hello">Return home</a>
</body>
</html>
Save and quit.
nano removed_salutation.html
And we will paste the following code.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>SUCCESS</title>
</head>
<body class="index">
<div id="header">
<h1>SUCCESS: Removed:</h1>
</div>
<p>${c.hello}</p>
<p>${c.language}</p>
<p/>
<a href="/hello">Return home</a>
</body>
</html>
Save and quit.
We will also add a few links to hello.html
nano hello.html
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>${c.hello}</title>
</head>
<body class="index">
<div id="header">
<h1>${c.hello}</h1>
</div>
<p>That is hello in: ${c.language}</p>
<p/>
<a href="add_salutation.html">Add a salutation</a><br/>
<a href="remove_salutation.html">Remove a salutation</a><br/>
<a href="modify_salutation.html">Modify a salutation</a>
</body>
</html>
Save and quit.
We will also add the following code to a new file called error.html
nano error.html
And we will paste the following code.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>ERROR</title>
</head>
<body class="index">
<div id="header">
<h1>ERROR: Failed to add/modify/delete</h1>
</div>
<p>${c.hello}</p>
<p>${c.language}</p>
<p/>
<a href="/hello">Return home</a>
</body>
</html>
Save and quit.
Finally, we add a couple of lines to hello.html
nano hello.html
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>${c.hello}</title>
</head>
<body class="index">
<div id="header">
<h1>${c.hello}</h1>
</div>
<p>That is hello in: ${c.language}</p>
<p/>
<a href="add_salutation.html">Add a salutation</a><br/>
<a href="remove_salutation.html">Remove a salutation</a><br/>
<a href="modify_salutation.html">Modify a salutation</a>
</body>
</html>
This allows us to access the static pages that are in helloworld/public.
Now, if you recall from eariler, Pylons needs to know how to point these pages at the appropriate controller and action.
And it does that through the routes defined in routing.py.
So we now go modify that.
cd ../../config
nano routing.py
And we add the following lines
map.connect('add_salutation',
'/hello/add_salutation',
controller='hello',
action='add_salutation',
conditions=dict(method=['POST']))
map.connect('remove_salutation',
'/hello/remove_salutation',
controller='hello',
action='remove_salutation',
conditions=dict(method=['POST']))
map.connect('modify_salutation',
'/hello/modify_salutation',
controller='hello',
action='modify_salutation',
conditions=dict(method=['POST']))
These routes will, point the pages at the appropriate controllers and actions, as advertised. The additional conditional dict specifies what sort of HTTP method (GET,POST,DELETE, ect) are supported for this URI. Here we are specifiying POST because all of the form methods associated with these URIs are POST.
And this should be the last set of modifications that we need to do to the application.
Now, we test our new code.
cd ../..
Which should put us into the outermost helloworld directory, with development.ini, so that we can start the server.
paster serve --reload development.ini
Now, browsing to
http://localhost:5000/hello
Should show you a page like this:
hello That is hello in: English Add a salutation Remove a salutation Modify a salutation
With the bottom text being HTML links.
Refreshing should take you through various incarnations of "hello" in different languages.
Now, we will add an additional greeting.
Click the link for Add a salutation.
This should take you to another very simple page with a form and a submit button.
Here, we are going to add hello for the Thai language.
Saluation: sawadee ka
Language: Thai
So we copy those words into the appropriate form boxes, and click "Submit".
We should be taken to a page that looks like this.
SUCCESS: Added: sawadee ka Thai Return home
And if we were to look in the database at this time, we would see the entry has been successfully entered into the database.
lacey@blinky:~/project/sql$ psql -U helloworld
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
helloworld=> SELECT * FROM hello;
salutation | language
---------------+----------
hello | English
hola | Spanish
bonjour | French
merhaba selam | Turkish
zdravstvuyte | Russian
buon giorno | Italian
sawadee ka | Thai
(7 rows)
helloworld=>
The chain of events is as follows.
1. We type the words into the form.
2. We click submit.
3. The words are placed into the http request:
webob._parsed_post_vars: (MultiDict([('salutation', 'sawadee ka'),
('language', 'Thai')]), <FakeCGIBody at
0x354b5d0 viewing MultiDict([('ol...R')])>)
4. We are routed to the add_salutation function in the hello
controller via routing.py
5. The add_salutation function parses the salutation and language
out of the http request.
6. It then hands it off to Simpycity, which translates it into:
SELECT * FROM public.add_salutation(E'sawadee ka',E'Thai');
7. Which then inserts it into the database, returning true.
8. We come back to the add_salutation function, which checks the
return, which is True.
9. We are routed to the success page. Here, we can click to go back
to the /hello page.
We can now click to see our newly added salutation and language. (which may take a few tries, since it is randomly selected.)
sawadee ka That is hello in: Thai Add a salutation Remove a salutation Modify a salutation
We can modify a salutation as well.
Click the modify salutation link, which will bring us to the modify salutation page.
We will add capitalization to the Thai greeting that we just added.
Type the following into the form pages:
Old Salutation: sawadee ka
New Salutation: Sawadee Ka
And click submit.
Again, you will be greeted by a page that looks like this.
SUCCESS: Modified: sawadee ka Sawadee Ka Return home
And if we check the database here as well...
lacey@blinky:~/project/helloworld/helloworld/model$ psql -U helloworld
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
helloworld=> SELECT * FROM hello;
salutation | language
---------------+----------
hello | English
hola | Spanish
bonjour | French
merhaba selam | Turkish
zdravstvuyte | Russian
buon giorno | Italian
Sawadee Ka | Thai
(7 rows)
helloworld=>
We note that the salutation is now capitalized.
The lifecycle of this event is almost exactly the same as the one noted above, except in two places.
The routing points at the modify_salutation function, and the upd_salutation function is translated into
SELECT * FROM public.update_salutation(E'sawadee ka',E'Sawadee Ka')
Finally, if we decide that we no longer want the salutation Sawadee Ka in the database, we can delete with a very similar lifecycle.
Click on the Remove a Salutation Link.
Salutation: Sawadee Ka (note that it has to be capitalized now)
Language: Thai
SUCCESS: Removed: Sawadee Ka Thai
Checking the database, we note that it is indeed removed.
lacey@blinky:~/project/helloworld/helloworld/model$ psql -U helloworld
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
helloworld=> SELECT * FROM hello;
salutation | language
---------------+----------
hello | English
hola | Spanish
bonjour | French
merhaba selam | Turkish
zdravstvuyte | Russian
buon giorno | Italian
(6 rows)
helloworld=>
And as noted above, the lifecycle is the same.
The routing points at the remove_salutation function, and the del_salutation function is translated into
SELECT * FROM public.remove_salutation(E'Sawadee Ka',E'Thai')
Thus, removing it from the database.
This now completes our small web application.
Combining Simpycity, Pylons and PostgreSQL, we have made an app that displays database content, and creates, updates, and deletes that content, which is the core functionality of any database driven web application.
And this also concludes our PostgreSQL->Simpycity->Pylons tutorial.
Thank you for following along. =)
The shortest path between two points
Posted Friday Jun 12th, 2009 10:52am
by Joshua Drake | Permalink
Recently I was doing some benchmarking on one of our machines. The benchmarking wasn't going so well due to bad batteries on the RAID controller. I had instructed one of our System Administrators to take care of the problem.
Long story short, the Administrator went down a very long trail to an obvious solution. The trail was well mapped, thought out and precise. It however missed some important points. When I caught on to the long trail she was taking I asked, "What is the shortest path between two points?". She replied, "On a plane or sphere?". That is when I knew we were in trouble.
Now most people would have just said, "Huh?". Luckily I have been blessed with at least a modicum of technical/mathematical knowledge and a general experience with Geeks for 18 years. Let's review.
The System Administrators trail was:
Using the System Administrators path, the solution to the problem cost at least 1500.00-2000.00. Using my path, the cost is 550.00. My path is a single trip to the collocation facility with cards drop shipped, an hour to replace and upgrade BIOS. Same resolution, ~ 37% of the cost.
But... But... what if that doesn't fix the problem?
Then you know you have bad cards and it will still cost less to replace them that to perform multiple trips to the colo.
Again it isn't that the System Administrators path was incorrect. In fact I would bet a lot of businesses would think it was the absolutely correct direction to take. I would rather just write off the cards and replace them. We can make far more money from the System Administrator if they are not focusing on long and winding roads to the same destination that can be reached by not taking a left at the fork.
Posted Friday Jun 12th, 2009 10:52am
by Joshua Drake | Permalink
Recently I was doing some benchmarking on one of our machines. The benchmarking wasn't going so well due to bad batteries on the RAID controller. I had instructed one of our System Administrators to take care of the problem.
Long story short, the Administrator went down a very long trail to an obvious solution. The trail was well mapped, thought out and precise. It however missed some important points. When I caught on to the long trail she was taking I asked, "What is the shortest path between two points?". She replied, "On a plane or sphere?". That is when I knew we were in trouble.
Now most people would have just said, "Huh?". Luckily I have been blessed with at least a modicum of technical/mathematical knowledge and a general experience with Geeks for 18 years. Let's review.
The System Administrators trail was:
- Visit Colo to check cards physically after receiving BIOS message
- Record all information about cards including BIOS versions
- Research possible cause of batteries not charging, find that some versions of the BIOS can do that. Thus causing yet another trip to the colo for installation.
- Go back to colo to update BIOS revisions to see if that resolves the problem
- If that doesn't solve the problem, research new batteries for order
- If that doesn't solve the problem, research new batteries for order
- System reports batteries are bad
- Known fact: Hardware was bought used
- Buy new batteries
- Upgrade BIOS during new battery installation
Using the System Administrators path, the solution to the problem cost at least 1500.00-2000.00. Using my path, the cost is 550.00. My path is a single trip to the collocation facility with cards drop shipped, an hour to replace and upgrade BIOS. Same resolution, ~ 37% of the cost.
But... But... what if that doesn't fix the problem?
Then you know you have bad cards and it will still cost less to replace them that to perform multiple trips to the colo.
Again it isn't that the System Administrators path was incorrect. In fact I would bet a lot of businesses would think it was the absolutely correct direction to take. I would rather just write off the cards and replace them. We can make far more money from the System Administrator if they are not focusing on long and winding roads to the same destination that can be reached by not taking a left at the fork.
Categories: Business
Hardware problem solved, when you really need some cache.
Posted Friday May 22nd, 2009 09:38am
by Joshua Drake | Permalink
As reported in my last blog, Stefan was having much greater success with his pgbench results than I. In reviewing why, we found a problem with the hardware. What I like about this problem is that the results in the previous blog post become more interesting. As a reminder I was running 16 connections over 4 different users at 1M transactions. Below is the results from a single user from that batch:
Over 16 connections we were getting ~ 400 TPS. I verified that this was consistent by running a second test with a single user and 4 connections. The results:
So, what is it that causes a machine with plenty of resources to perform in such a consistently slow manner?. You can only write data as fast and the spindles turn. That is why they invented cache. The results should look very similar to Stefan's once we replace the battery cache.
Posted Friday May 22nd, 2009 09:38am
by Joshua Drake | Permalink
As reported in my last blog, Stefan was having much greater success with his pgbench results than I. In reviewing why, we found a problem with the hardware. What I like about this problem is that the results in the previous blog post become more interesting. As a reminder I was running 16 connections over 4 different users at 1M transactions. Below is the results from a single user from that batch:
pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 101.024360 (including connections establishing) tps = 101.024392 (excluding connections establishing)
Over 16 connections we were getting ~ 400 TPS. I verified that this was consistent by running a second test with a single user and 4 connections. The results:
pghost: localhost pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 404.021738 (including connections establishing) tps = 404.022316 (excluding connections establishing)
So, what is it that causes a machine with plenty of resources to perform in such a consistently slow manner?. You can only write data as fast and the spindles turn. That is why they invented cache. The results should look very similar to Stefan's once we replace the battery cache.
Categories: OpenSource, PostgreSQL
Thanks Stefan
Posted Thursday May 21st, 2009 02:13pm
by Joshua Drake | Permalink
So while doing the benchmarking of the various parameters, Stefan pointed out the my numbers were ridiculously low. I wasn't really paying attention because I was looking at differences between parameters but then he posted me an example of a single thread pgbench using my same parameters. His machine is a dual core connected to 10 spindles on a NetAPP. In theory my machine should be faster. It is not. His configuration, like mine was all defaults.
Stefan's numbers.
So now I am trying to figure out what is up with my hardware.
Posted Thursday May 21st, 2009 02:13pm
by Joshua Drake | Permalink
So while doing the benchmarking of the various parameters, Stefan pointed out the my numbers were ridiculously low. I wasn't really paying attention because I was looking at differences between parameters but then he posted me an example of a single thread pgbench using my same parameters. His machine is a dual core connected to 10 spindles on a NetAPP. In theory my machine should be faster. It is not. His configuration, like mine was all defaults.
Stefan's numbers.
scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 1681.615766 (including connections establishing) tps = 1681.622950 (excluding connections establishing)
So now I am trying to figure out what is up with my hardware.
8.3.7 TPS and checkpoint segments
Posted Thursday May 21st, 2009 09:09am
by Joshua Drake | Permalink
Continuing my postgresql.conf changes I ran a new test yesterday with checkpoint_segments set to 300. As a reminder the original results and specs of the machine being used in the test are here. The results of the new test below:
O.k. so same test, same hardware, 10x more checkpoint_segments gives us ~8 percent improvement.
Posted Thursday May 21st, 2009 09:09am
by Joshua Drake | Permalink
Continuing my postgresql.conf changes I ran a new test yesterday with checkpoint_segments set to 300. As a reminder the original results and specs of the machine being used in the test are here. The results of the new test below:
pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 101.024360 (including connections establishing) tps = 101.024392 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 100.796885 (including connections establishing) tps = 100.796924 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 100.801501 (including connections establishing) tps = 100.801534 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 100.852900 (including connections establishing) tps = 100.852944 (excluding connections establishing)
O.k. so same test, same hardware, 10x more checkpoint_segments gives us ~8 percent improvement.
Default TPS performance of 8.3.7
Posted Wednesday May 20th, 2009 10:58am
by Joshua Drake | Permalink
I recently purchased some used hardware for some performance testing of PostgreSQL. I didn't want to interrupt the great work that Mark Wong was doing with the PostgreSQL Performance Lab. The testing I am doing is a bit different than Mark's. Where Mark is testing various filesystem performance via PostgreSQL using DBT2 and FIO and wanted to go up a level.
I am testing using the PostgreSQL tool pgbench which is available in contrib. I am also testing in a basically default environment as a way to see how changing different parameters of the postgresql.conf changes overall performance.
Hardware Configuration
Operating System Configuration
Outside of the minor operating system changes the system remained in default. The only postgresql.conf parameter I changed was to set checkpoint_segments to 30.
pgbench configuration
I used four pgbench instances within a single database with four schemas. Each schema was assigned to its own pgbench user 01-04. The pgbench command used was:
Results
Each pgbench process executed 4M transactions with an average of ~ 24TPS per client. That is pretty miserable. However remember this is defaults and the defaults will make you checkpoint quite a bit (every 20 seconds or so) with the above stress test. Stay tuned for results as we change specific parameters to see the effect each one has.
Posted Wednesday May 20th, 2009 10:58am
by Joshua Drake | Permalink
I recently purchased some used hardware for some performance testing of PostgreSQL. I didn't want to interrupt the great work that Mark Wong was doing with the PostgreSQL Performance Lab. The testing I am doing is a bit different than Mark's. Where Mark is testing various filesystem performance via PostgreSQL using DBT2 and FIO and wanted to go up a level.
I am testing using the PostgreSQL tool pgbench which is available in contrib. I am also testing in a basically default environment as a way to see how changing different parameters of the postgresql.conf changes overall performance.
Hardware Configuration
Newisys Quad Opteron 846
32GB of memory
(2) MSA 30s (RAID 10, 14 drives each)
(2) HP 6402 Controllers (one for each MSA)
Operating System Configuration
Ubuntu Hardy LTS x86_64
/array1 , ext3, data=writeback
elevator=deadline
Outside of the minor operating system changes the system remained in default. The only postgresql.conf parameter I changed was to set checkpoint_segments to 30.
pgbench configuration
I used four pgbench instances within a single database with four schemas. Each schema was assigned to its own pgbench user 01-04. The pgbench command used was:
/array1/jd/pgsql/bin/pgbench -U bench01 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench01& /array1/jd/pgsql/bin/pgbench -U bench02 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench02& /array1/jd/pgsql/bin/pgbench -U bench03 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench03& /array1/jd/pgsql/bin/pgbench -U bench04 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench04&
Results
pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 92.279931 (including connections establishing) tps = 92.279960 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 91.674708 (including connections establishing) tps = 91.674739 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 91.583754 (including connections establishing) tps = 91.583782 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 91.616330 (including connections establishing) tps = 91.616355 (excluding connections establishing)
Each pgbench process executed 4M transactions with an average of ~ 24TPS per client. That is pretty miserable. However remember this is defaults and the defaults will make you checkpoint quite a bit (every 20 seconds or so) with the above stress test. Stay tuned for results as we change specific parameters to see the effect each one has.
Surprising events from a top 20 list
Posted Monday May 11th, 2009 10:15am
by Joshua Drake | Permalink
In a completely unscientific review of community popularity I was reviewing the active members of various channels on #freenode. For those that don't know, Freenode is the defacto destination for the Open Source community on IRC. This is where you will find official community support channels for such open source luminaries as #gentoo, #ubuntu, #mysql and of course #postgresql.Read more...
Posted Monday May 11th, 2009 10:15am
by Joshua Drake | Permalink
In a completely unscientific review of community popularity I was reviewing the active members of various channels on #freenode. For those that don't know, Freenode is the defacto destination for the Open Source community on IRC. This is where you will find official community support channels for such open source luminaries as #gentoo, #ubuntu, #mysql and of course #postgresql.Read more...
Long Running Request Handlers and Python Garbage Collection
Posted Thursday May 7th, 2009 09:56am
by Aurynn Shaw | Permalink
While working on a Simpycity + Pylons environment the other day, I noticed that my app was leaking Postgres connection handles. This is not behaviour you ever want to see, especially in software as vital as Simpycity.
Investigation and testing demonstrated pretty conclusively here, and here that the Python garbage collector was *not* immediately cleaning up dead/unreferenced objects.
Specifically, in the test case below,
when executed in the Python interactive interpreter, a dangling (unassigned) ResultSet object in remained in memory. Even though no references to the object exist, and it should be cleaned up, it will *not* be cleaned up until another garbage collection event is triggered. Calling locals(), globals() or by forcibly executing the garbage collection via import gc; gc.collect() will clean up the stray ResultSet as expected.
Model-based objects exhibit the same behaviour.
As the Postgres handles are being created (by default) READ COMMITTED, they are left in the state " in transaction". This part can be handled via calling .commit() on all handles, but, still prone to the leaking/cleanup issue.
At its core, the leaking issue stems from design choices behind Simpycity; specifically, abstracting away the connection management logic leaves us in the position where automatic management cannot depend purely on the Python garbage collector for handle cleanup.
This lifecycle issue affects Simpycity under Pylons, as well. By relying on the Python garbage collection, a request's Postgres connections fall out of scope, but are not reaped until *later*, when the mod_wsgi subprocess accepts and handles a new request. Once we realized how this happens in the interactive interpreter, we saw that in an environment such as mod_wsgi or mod_python, with multiple active subprocesses, lurking/hanging sessions will inevitably occur.
When Lacey and I studied SQLAlchemy, it appeared vulnerable to the same behaviour; Reading over their pooling logic, as well as examining a Pylons app configured to use SQLAlchemy in the Paster template showed they are *explicitly* cleaning up all DB connections after every Pylons request.
This was implemented using a
block in the core Pylons controller, as well as the global initialization of a db connection during Pylons setup (see model/meta.py, model/__init__.py, lib/base.py in a default Pylons project setup as an example).
As Pylons + SQLAlchemy is not our default environment, this was initially missed.
Discussion with James Bennett (ubernostrum, irc.freenode.net) shows that the Django ORM has a similar issue. Django resolves this with an asynchronous message-passing system, which, at the end of a Request cycle, notifies the ORM backend that the opened connections should be terminated.
Therefore:
* Reliance on Python's garbage-collection cycle is fickle at best, especially in long-running, asynchronous processes such as Pylons.
* Based on the SQLAlchemy mechanisms within Pylons, specifically the end-of-request session cleanup, it seems reasonable to implement a session manager for Simpycity. This would exist to only to handle long-running scenarios where automatic garbage collection would not be able to consistently run.
The connection manager would need to do little more than maintain a list of all active/open handles, and offer a .cleanup() call to free all handles at the end of the request. Coupled with the explicit try-finally cleanup pattern that SQLAlchemy uses within the Pylons WSGIController, a connection manager would succinctly solve the problem.
* This would maintain correct transactional integrity, because implicit commits are never issued to the PostgreSQL backend.
* The leaking handle issues would vanish, because we know that the only handles used within Simpycity will be tracked within the connection management object, and correctly cleaned up after each request.
* In short-run applications, this is largely a non-issue, as the exiting of the interpreter closes all extant handles automatically.
As this is a subtle issue, we'll be providing an upstream patch for Pylons, to provide a Paster template specifically to set up a connection management environment for Simpycity. This will also be documented on the Simpycity wiki, discussing how to set up a connection manager and use it for long-running programs.
Posted Thursday May 7th, 2009 09:56am
by Aurynn Shaw | Permalink
While working on a Simpycity + Pylons environment the other day, I noticed that my app was leaking Postgres connection handles. This is not behaviour you ever want to see, especially in software as vital as Simpycity.
Investigation and testing demonstrated pretty conclusively here, and here that the Python garbage collector was *not* immediately cleaning up dead/unreferenced objects.
Specifically, in the test case below,
def foo():
r = Raw("SELECT count(*) as cnt, usename, current_query FROM
pg_catalog.pg_stat_activity GROUP BY usename,current_query ORDER BY cnt
DESC")
return r()
foo()
when executed in the Python interactive interpreter, a dangling (unassigned) ResultSet object in remained in memory. Even though no references to the object exist, and it should be cleaned up, it will *not* be cleaned up until another garbage collection event is triggered. Calling locals(), globals() or by forcibly executing the garbage collection via import gc; gc.collect() will clean up the stray ResultSet as expected.
Model-based objects exhibit the same behaviour.
As the Postgres handles are being created (by default) READ COMMITTED, they are left in the state "
At its core, the leaking issue stems from design choices behind Simpycity; specifically, abstracting away the connection management logic leaves us in the position where automatic management cannot depend purely on the Python garbage collector for handle cleanup.
This lifecycle issue affects Simpycity under Pylons, as well. By relying on the Python garbage collection, a request's Postgres connections fall out of scope, but are not reaped until *later*, when the mod_wsgi subprocess accepts and handles a new request. Once we realized how this happens in the interactive interpreter, we saw that in an environment such as mod_wsgi or mod_python, with multiple active subprocesses, lurking/hanging sessions will inevitably occur.
When Lacey and I studied SQLAlchemy, it appeared vulnerable to the same behaviour; Reading over their pooling logic, as well as examining a Pylons app configured to use SQLAlchemy in the Paster template showed they are *explicitly* cleaning up all DB connections after every Pylons request.
This was implemented using a
try:
...
finally:
...
block in the core Pylons controller, as well as the global initialization of a db connection during Pylons setup (see model/meta.py, model/__init__.py, lib/base.py in a default Pylons project setup as an example).
As Pylons + SQLAlchemy is not our default environment, this was initially missed.
Discussion with James Bennett (ubernostrum, irc.freenode.net) shows that the Django ORM has a similar issue. Django resolves this with an asynchronous message-passing system, which, at the end of a Request cycle, notifies the ORM backend that the opened connections should be terminated.
Therefore:
* Reliance on Python's garbage-collection cycle is fickle at best, especially in long-running, asynchronous processes such as Pylons.
* Based on the SQLAlchemy mechanisms within Pylons, specifically the end-of-request session cleanup, it seems reasonable to implement a session manager for Simpycity. This would exist to only to handle long-running scenarios where automatic garbage collection would not be able to consistently run.
The connection manager would need to do little more than maintain a list of all active/open handles, and offer a .cleanup() call to free all handles at the end of the request. Coupled with the explicit try-finally cleanup pattern that SQLAlchemy uses within the Pylons WSGIController, a connection manager would succinctly solve the problem.
* This would maintain correct transactional integrity, because implicit commits are never issued to the PostgreSQL backend.
* The leaking handle issues would vanish, because we know that the only handles used within Simpycity will be tracked within the connection management object, and correctly cleaned up after each request.
* In short-run applications, this is largely a non-issue, as the exiting of the interpreter closes all extant handles automatically.
As this is a subtle issue, we'll be providing an upstream patch for Pylons, to provide a Paster template specifically to set up a connection management environment for Simpycity. This will also be documented on the Simpycity wiki, discussing how to set up a connection manager and use it for long-running programs.
My turn on Oracle purchasing Sun.
Posted Friday Apr 24th, 2009 08:44am
by Joshua Drake | Permalink
I feel like I am coming late to this topic. All the pundits have already had there say and the blogosphere has been rampant. I have been talking with a lot of MySQL folks lately, encouraging them to at least test PostgreSQL as an alternative. MySQL folks are nervous. They don't like the opportunity Oracle brings to the table. This morning I was asked quite bluntly, "From your perspective what is the future of MySQL?".
Read more...
Posted Friday Apr 24th, 2009 08:44am
by Joshua Drake | Permalink
I feel like I am coming late to this topic. All the pundits have already had there say and the blogosphere has been rampant. I have been talking with a lot of MySQL folks lately, encouraging them to at least test PostgreSQL as an alternative. MySQL folks are nervous. They don't like the opportunity Oracle brings to the table. This morning I was asked quite bluntly, "From your perspective what is the future of MySQL?".
Read more...
Copyright © 2000-2009 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.