Tuesday 19 March 2013

Postgresql Transfer Database to New Tablespace with Python

This is a quick script I wrote in python to generate the sql file needed to transfer one database to another tablespace.



 #!/usr/bin/python

# will generate the sql file needed to alter the tablespace for a database.

import psycopg2
import psycopg2.extras
import sys

#
# Configuration
# 
host = '';            # The host on which the database resides.
user = '';            # The username to access the database.
password = '';            # The password to access the database.
db = '';            # The database to move.
tablespace = '';    # The tablespace to move the database to.
output = ""

#
# Application
#
conn = psycopg2.connect(database=db, user=user, password=password)
conn.set_isolation_level(0)  
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

# Create SQL code to put new tables and indexes in the new tablespace.
output = output + "ALTER DATABASE " + db + " SET default_tablespace = " + tablespace + ";\n"

# Select all tables from the database.
tableQuery = "SELECT * FROM pg_tables where tableowner='" + user + "' ORDER BY tablename"

cur.execute(tableQuery)

db_tables = cur.fetchall()
# print db_tables 
for table in db_tables:
    schemaName = table['schemaname']
    tableName = table['tablename']

    # Create SQL code to move the table to the new tablespace.
    output = output + "ALTER TABLE "+ schemaName +"." + tableName + " SET TABLESPACE " + tablespace + ";\n";
    # print output

    # Select all indexes from the table.
    indexQuery = "SELECT * FROM pg_indexes WHERE schemaname = '" + schemaName + "' AND tablename = '" + tableName + "' ORDER BY indexname"
    print indexQuery
    cur.execute(indexQuery)
    print cur.statusmessage
    db_indexes = cur.fetchall()
    # print db_indexes
    for index in db_indexes:
        print dict(index)
        indexName = index['indexname']

        # Create SQL code to move the index to the new tablespace.
        output = output + "ALTER INDEX " + schemaName + "." + indexName + " SET TABLESPACE " + tablespace+ ";\n";
    


# Write the resulting SQL code to a file.
filename = 'migrate_' + host + '_' + db + '_to_' + tablespace + '.sql'
sqlfile = open('filename, 'w')
sqlfile.write(output)

 


Best of luck.


References:
  1. http://blog.lodeblomme.be/2008/03/15/move-a-postgresql-database-to-a-different-tablespace/
  2. http://www.postgresql.org/docs/9.1/static/sql-createtablespace.html

Saturday 9 March 2013

Rare but powerful Unix/Linux commands

This blog is a list of some of the more powerful but not as frequently used Linux or Unix commands.

Need to copy a large amount of files from one directory to another

find $directory1 -maxdepth 1 -type f -name '*' -exec mv {} $directory2/. \; 

This can also be used with the mv command.
 
 
What to rename a number of file in a directory and preserve their numbering
 
rename "s/scenario/testcase-/" scenario*.xml
 
Files were
 



scenario2797.xml scenario4597.xml scenario6397.xml scenario8197.xml scenario9998.xml
scenario2798.xml scenario4598.xml scenario6398.xml scenario8198.xml scenario9999.xml
 
Now they are 
testcase-2797.xml testcase-4597.xml testcase-6397.xml testcase-8197.xml testcase-9998.xml
testcase-2798.xml testcase-4598.xml testcase-6398.xml testcase-8198.xml testcase-9999.xml 
 
 
for file in `find . -name '*.sql' -print 2>/dev/null`
do
    echo $file
    echo "Dirname:"
    echo ${file##*/}
    filename=${file##*/}
    fileprefix=${filename%.*}
    dirname $file
    echo "fileprefix: $fileprefix"
    psql -d  -U  -f $filename > "results/$fileprefix.result"
done
 

Friday 8 March 2013

SmartBody, first steps


SmartBody is a character animation platform originally developed at the University of Southern California Institute for Creative Technologies. SmartBody provides locomotion, steering, object manipulation, lip syncing, gazing and nonverbal behavior in real time.
SmartBody is written in C++ and can be incorporated into most game and simulation engines. SmartBody is a Behavioral Markup Language (BML) realization engine that transforms BML behavior descriptions into realtime animations. SmartBody runs on Windows, Linux, OSx as well as the iPhone and Android devices.

I want to use this to control the character animations for a video game made in Unity3D.

 The package does say this it can work with
  • Unity
  • Ogre
  • Unreal
  • Panda3D
  • GameBryo
However it supports Unity and Ogre actively. 

Goal: To send BML messages to a control characters in SmartBody via TCP or UDP messages.

Tasks:
  1. Get and Compile SmartBody ( For Windows and Linux )
  2. Familiarize self with SmartBody examples
  3. Learn how to use TCP in Python.
  4. Produce code to control SmartBody character from python code combining what was learned from 2 and 3.


    
    import random

    import select
    import socket
    import sys

    service = socket.socket(socket.AF_INET, socket.SOCK_STREAM)

    # Connect the socket to the port where the server is listening
    server_address = ('localhost', 3850)
    print >> sys.stderr, 'connecting to %s port %s' % server_address
    service.connect(server_address)


    print "|--------------------------------------------|"
    print "|       Starting Locomotion Type Demo        |"
    print "|--------------------------------------------|"

    smartBodyRelitivePath = "../../smartbody/data/"

    # Add asset paths
    scene.addAssetPath('script',smartBodyRelitivePath + 'sbm-common/scripts')
    scene.addAssetPath('mesh', smartBodyRelitivePath + 'mesh')
    scene.addAssetPath('mesh', smartBodyRelitivePath + 'retarget/mesh')
    scene.addAssetPath('motion', smartBodyRelitivePath + 'ChrBrad')
    scene.addAssetPath('motion', smartBodyRelitivePath + 'ChrRachel')
    scene.addAssetPath('motion', smartBodyRelitivePath + 'retarget\motion')
    scene.addAssetPath('motion', smartBodyRelitivePath + 'sbm-common/common-sk')
    scene.loadAssets()


    # Set scene parameters and camera
    print 'Configuring scene parameters and camera'
    scene.setScale(1.0)
    scene.setBoolAttribute('internalAudio', True)
    scene.run('default-viewer.py')
    camera = getCamera()
    camera.setEye(0, 2.87, 11.67)
    camera.setCenter(0, 2.14, 9.81)
    camera.setUpVector(SrVec(0, 1, 0))
    camera.setScale(1)
    camera.setFov(1.0472)
    camera.setFarPlane(100)
    camera.setNearPlane(0.1)
    camera.setAspectRatio(0.966897)
    scene.getPawn('camera').setPosition(SrVec(0, -5, 0))

    # Set joint map for Brad
    print 'Setting up joint map for Brad'
    scene.run('zebra2-map.py')
    zebra2Map = scene.getJointMapManager().getJointMap('zebra2')
    bradSkeleton = scene.getSkeleton('ChrBrad.sk')
    zebra2Map.applySkeleton(bradSkeleton)
    zebra2Map.applyMotionRecurse('ChrBrad')

    # Retarget setup
    scene.run('motion-retarget.py')
    # Animation setup
    scene.run('init-param-animation.py')

    # Set up 3 Brads
    print 'Adding characters into scene'
    posX = -200
    for i in range(1): # Only add one brad
        baseName = 'ChrBrad%s' % i
        brad = scene.createCharacter(baseName, '')
        bradSkeleton = scene.createSkeleton('ChrBrad.sk')
        brad.setSkeleton(bradSkeleton)
        # Set position
        bradPos = SrVec((posX + (i * 200))/100, 0, 0)
        brad.setPosition(bradPos)
        # Set up standard controllers
        brad.createStandardControllers()
        # Set deformable mesh
        brad.setDoubleAttribute('deformableMeshScale', .01)
        brad.setStringAttribute('deformableMesh', 'ChrBrad')
        # Play idle animation
        bml.execBML(baseName, '')
        # Retarget character
        retargetCharacter(baseName, 'ChrBrad.sk', False)

    # Turn on GPU deformable geometry for all
    for name in scene.getCharacterNames():
        scene.command("char %s viewer deformableGPU" % name)

    # Whether character has reached its target
    brad1Reached = True

    # Paths for characters
    brad1Path = [SrVec(-2, 8, 0), SrVec(-2, -8, 0)]

    bradCur = 0
    pathAmt = 2


    class LocomotionDemo(SBScript):
        def update(self, time):
            # Select is a creative way of checking to see if a service has data ready to read,
            # ready to write to or an exceptional condition.
            r, w, e = select.select([service], [], [], 0.0)
            if r: # There is something to read
                data = service.recv(256)
                character = 'ChrBrad0'
                # The last 0 in the target is zero just so the character stays on the plane
                bmlMessage = ''
               
                print "BML Message for " + character + ": " + bmlMessage
                bml.execBML(character, bmlMessage)
           

    # Run the update script
    scene.removeScript('locomotiondemo')
    locomotiondemo = LocomotionDemo()
    scene.addScript('locomotiondemo', locomotiondemo)

Eclipse Find/Replace

This is a quick example on how to use the power of find and replace in eclipse. Some might say that eclipse find and replace is not as powerful as something like vi or sed but it is still rather powerful.



I had a program where I wanted to transform all of all of parameters created using #define statement to command line arguments so that I could write an algorithm to test the effect of different values for these parameters. 

First copy and past all of the define statements to another location of your program ( I wanted to keep the define statements to be used as defaults if no new parameter value is passed.

After you have copied the text should look something like 


#define PED_BRAKING_RATE    0.95f

#define PED_COMFORT_ZONE    1.5f

#define PED_QUERY_RADIUS    10.0f
 
NOTE: Make sure you have Regular expressions check-box checked.

To replace these lines with atribute definitions you can use ability of find and replace to capture strings (). Putting a regular expressions between () will save that string into a variable $1 that can be used in your replace with:. IN this case use "#define ([A-Z|_]*)[ |.|0-9|f]*" (don't include the quotes ") to find one of the above lines. Now this line can be replaced with "float $1;". very cool stuff.

If you want to change all of the character to lowercase you will have to use ctrl+shift+y. Highlight want you want to change then use ctrl+shift+y.

The last thing I did was copy and paste a bunch of lines that assigned the #defined macro to the class varaible

ped_max_speed = PED_MAX_SPEED;

That I had to do slightly by hand because find and replace does not replace with lowercase as far as I know.

I want to change these lines into the piece of code that will be used to check if there was a command line parameter option passed to change this value. If there was update the value. This part was really easy.

I could use this in my find statement:


([a-z|_]*)[a-z| |_|A-Z|=]*;


and this to replace each line with the code I wanted:

else if ((*optionIter).first == "$1")\n\t\t{\n\t\t\t(*optionIter).second >> $1;\n\t\t}


To result in:


        else if ((*optionIter).first == "ped_max_speed")
        {
            (*optionIter).second >> ped_max_speed;

        }

I used this to replace about 50 assignments. Took me about 30 seconds. 

Now all of my #define statement parameters are also command line arguments that can be passed to easily test out different values.

Happy hunting!

References:
  1. Me.

Wednesday 6 March 2013

SmartBody Example:


SmartBody characters are usually controlled via python scripts. These scripts are responsible for loading all of the features need for the character animation and then seting up the character in the environment and last functions to control the actions of the character(s).

This is a common exmaple, there are more advanced features than what is discussed in this post.

The first part of the python script to control a SmartBody character should be calls to add any assests that are need for the character animations that will be used later in the script.


smartBodyRelitivePath = "../../smartbody/data/"

# Add asset paths
scene.addAssetPath('script',smartBodyRelitivePath + 'sbm-common/scripts')
scene.addAssetPath('mesh', smartBodyRelitivePath + 'mesh')
scene.addAssetPath('mesh', smartBodyRelitivePath + 'retarget/mesh')
scene.addAssetPath('motion', smartBodyRelitivePath + 'ChrBrad')
scene.addAssetPath('motion', smartBodyRelitivePath + 'ChrRachel')
scene.addAssetPath('motion', smartBodyRelitivePath + 'retarget\motion')
scene.addAssetPath('motion', smartBodyRelitivePath + 'sbm-common/common-sk')
scene.loadAssets()


The above code first defines the relative path to the directory that contain all of the assest for character animation. These include meshes, skeletal meshes, joint maps, etc. These are usually stored in the data folder in SmartBody. As can bee seen assets for the mesh for a character is added as well as common script for that character and motion data that will be used to create the motion of the character.

The next section of the program Will configure the scene. This is much more OpenGL like and is to define the setting for the camera.


# Set scene parameters and camera
print 'Configuring scene parameters and camera'
scene.setScale(1.0)
scene.setBoolAttribute('internalAudio', True)
scene.run('default-viewer.py')
camera = getCamera()
camera.setEye(0, 2.87, 11.67)
camera.setCenter(0, 2.14, 9.81)
camera.setUpVector(SrVec(0, 1, 0))
camera.setScale(1)
camera.setFov(1.0472)
camera.setFarPlane(100)
camera.setNearPlane(0.1)
camera.setAspectRatio(0.966897)
scene.getPawn('camera').setPosition(SrVec(0, -5, 0))


The next section is a little less obvious. It first runs a script, this script loads a joint map. After a joint map is loaded it can be applied to a character in SmartBody.


# Set joint map for Brad
print 'Setting up joint map for Brad'
scene.run('zebra2-map.py')
zebra2Map = scene.getJointMapManager().getJointMap('zebra2')
bradSkeleton = scene.getSkeleton('ChrBrad.sk')
zebra2Map.applySkeleton(bradSkeleton)
zebra2Map.applyMotionRecurse('ChrBrad')


Next two more scripts are run to setup the motion animation that the character can do and next activates the parametric animation that is used for the characters.


# Retarget setup
scene.run('motion-retarget.py')
# Animation setup
scene.run('init-param-animation.py')


Now everything that needs to be done to be able to use animations is complete The last step that needs to be done so that a character can be used in the scene is described below.


# Set up Brads

print 'Adding characters into scene'
posX = -200
for i in range(1): # Only add one brad
    baseName = 'ChrBrad%s' % i
    brad = scene.createCharacter(baseName, '')
    bradSkeleton = scene.createSkeleton('ChrBrad.sk')
    brad.setSkeleton(bradSkeleton)
    # Set position
    bradPos = SrVec((posX + (i * 200))/100, 0, 0)
    brad.setPosition(bradPos)
    # Set up standard controllers
    brad.createStandardControllers()
    # Set deformable mesh
    brad.setDoubleAttribute('deformableMeshScale', .01)
    brad.setStringAttribute('deformableMesh', 'ChrBrad')
    # Play idle animation
    bml.execBML(baseName, '')
    # Retarget character
    retargetCharacter(baseName, 'ChrBrad.sk', False)



# Turn on GPU deformable geometry for all
for name in scene.getCharacterNames():
    scene.command("char %s viewer deformableGPU" % name)


To add a character to the scene it needs to have a unique name ( will be seen later why ) and must be assigned a skeleton. THe rest of the code sets up the initial conditions for the character Brad: initial position, standard controllers, scale and turn on deformable geometry (by default a characters geometry is not displayed) and the initial posture for brad. A posture is the stance the character will take when no other animations are in effect.


The last part of the script is the part that you must write. After the characters have been initialized properly it is up to you to control the character as you want. For this example I wrote a simple controller that accepts information from a TCP connection so that I can control the character with another program.



class LocomotionDemo(SBScript):
    def update(self, time):
        # Select is a creative way of checking to see if a service has data ready to read,
        # ready to write to or an exceptional condition.
        r, w, e = select.select([service], [], [], 0.0)
        if r: # There is something to read
            data = service.recv(256)
            character = 'ChrBrad0'
            # The last 0 in the target is zero just so the character stays on the plane
            bmlMessage = ''
            
            print "BML Message for " + character + ": " + bmlMessage
            bml.execBML(character, bmlMessage)


To add a controlling script you need to write a class and have a method called update in that class. This update method will be called every frame and you can check the characters and scene for events of interest to further animate your characters. THe code include some notes on some features of Python.

Last the script that you have created need to be given control. This is done by explicitely removing the script name if it already exsist and then creating an object that can be used to reference your update function.

NOTE: this script name is for the class that is created not the name of script file that is be used.



# Run the update script
scene.removeScript('locomotiondemo')
locomotiondemo = LocomotionDemo()
scene.addScript('locomotiondemo', locomotiondemo)




References
  1. http://smartbody.ict.usc.edu/forum
  2. http://smartbody.ict.usc.edu

Sunday 3 March 2013

plpgsql: Creating Functions and saving intermetiate query results


Sometimes SQL queries are difficult or impossible to optimize if you are using pure standard SQL.

I started this because I just wanted to find a way to save the result of a sub-query to use later. In my example I wanted to save the average + the standard deviation of a large set of values for some research. I found that Posgresql supports standard SQL only so you can not save query results. Instead you can use plpgsql with is a procedural langauge that can be used with Postgresql (psql).

These are the two tables being considered for these examples.


CREATE TABLE IF NOT EXISTS test 

(

  test_id integer NOT NULL primary key,

  algorithm_data_id integer NOT NULL references 

  test_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  test_status integer NOT NULL,

  num_agents integer NOT NULL,

  num_obstacles integer NOT NULL

) ;



CREATE TABLE IF NOT EXISTS composite1

    benchmark_id integer NOT NULL references test(test_id) ON DELETE CASCADE,

    collisions integer NOT NULL,    

    time double precision NOT NULL,    

    success integer NOT NULL,    

    num_unique_collisions integer NOT NULL,       

    total_distance_travelled double precision NOT NULL,       

    agent_complete integer NOT NULL,    

    agent_success integer NOT NULL,    

    optimal_path_length double precision NOT NULL,    

);


Basic syntax for a function definition


CREATE OR REPLACE FUNCTION multiply(x real, y real) RETURNS real AS 
CREATE OR REPLACE The replace part is useful for development if testing a function and updating it but also useful if it so happens that there already exist a function with the same name you want to override it to ensure proper functionality.

After the AS comes the function definition. The most non obvious part that I found was the use of '$$'. Think of the $$ as {} from many programming languages that are used to symbolize a body of code, in this case the function body.


CREATE OR REPLACE FUNCTION multiply(x real, y real) RETURNS real AS
$$
BEGIN
    RETURN x * y;
END;
$$ LANGUAGE plpgsql;
 
This is a simple example of a simple definition that can be used to multiply two numbers.
 
Note the uses of ';' they are only at the end of return statements and queries. This will be seen more later.

A more advanced example:


CREATE OR REPLACE FUNCTION getAVG_WRTTotalTicksforScenario(scenarioID INT) RETURNS double precision AS

$BODY$

DECLARE

average double precision;

BEGIN

    

SELECT AVG(total_total_ticks_accumulated/total_number_of_times_executed) INTO STRICT average

    FROM ppr_ai_data pd2, test test, algorithm_data

    WHERE test.algorithm_data_id = algorithm_data.algorithm_data_id and

           -- test.scenario_group = scenario_group and

           algorithm_data.algorithm_data_id = pd2.ppr_ai_id and

            test.scenario_group = scenarioID;

    RETURN average;

END

$BODY$ LANGUAGE plpgsql; 


Any variables that are going to be used should be defined in the declare section.

The return types of functions can be of the basic types for can be of a time defined for a table. For example



CREATE OR REPLACE FUNCTION getAllSignificantTestsForScenario(scenarioID INT, algorithmName TEXT) RETURNS SETOF Test AS

$BODY$

DECLARE

average double precision; 

std_dev double precision;

r test%rowtype;





BEGIN



average := getAVG_WRTTotalTicksforScenario(scenarioID)

        

std_dev :=    getSTDDEV_WRTTotalTicksforScenario(scenarioID)

                

RAISE NOTICE 'Average is %', average;

RAISE NOTICE 'Standard deviation is %', std_dev;



RETURN QUERY     SELECT * from test where test_id IN (

SELECT t.test_id

    FROM test t, ppr_ai_data pd, algorithm al, algorithm_data Adata

    WHERE t.algorithm_data_id = Adata.algorithm_data_id and

            pd.ppr_ai_id = Adata.algorithm_data_id and

            Adata.algorithm_type = al.algorithm_id and

            al.name = algorithmName and

            t.scenario_group = scenarioID and

        pd.total_total_ticks_accumulated / pd.total_number_of_times_executed > (average + std_dev));

                

RETURN;



END


Return can be used a in few ways. In the above case it is used to return the result of an entire query. In the above example the return is typed to that of a row of the table test. A small trick was used to get around having to return a full test row. The use of Raise in this case is just used to inform the user of the values being used in the sub query. Usually RAISE is used to inform the user issues in the function and can be used to throw exception like many other programming languages [4].

References
  1. http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html
  2. http://www.postgresql.org/docs/8.4/static/plpgsql-declarations.html
  3. http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
  4. http://www.postgresql.org/docs/9.1/static/plpgsql-errors-and-messages.html