Creating extendable Web Application with Pyramid, PCA and Jinja2

Pyramid is a great framework for developing web applications that also supports the development of “Extensible” and “Pluggable” ones based on certain rules. However, there is no much documentation on how to create such type of applications.

CKAN is an excellent example of a web application that can be extended or modified using plugins. It relies on PyUtilib Component Architecture (PCA) to declare a series of interfaces and extension points that then are used by plugins to hook in. It also implements a series of Jinja2 extension (notably CKAN_EXTENDS) that allows easily template inheritance between CKAN and connected plugins.

CKAN however is developed using Pylons which is now superseded by Pyramid and coupled to its complexity makes it difficult to grasp how to apply such extensibility to Pyramid web applications.

I took the time to look into the CKAN code, the PCA documentation and Jinja2 and I came with an example that applies a modified version of CKAN extensible system to a simple Pyramid application. It can be used as a starter to develop more complex extendable web applications. The example (pcaexample) is based on a “pyramid-cookiecutter-alchemy” Cookiecutter. The code also includes an example of a plugin that is based on a “pyramid-cookiecutter-starter”. It uses Pyramid 1.8.3 with PyUtilib 5.4.1.

You can get the code here.

Cheers,

Carlos

Making one Qt5 application to connect to both MySQL server and embedded

QT5 usually builds with a MySQL driver that connect to a server. The documentation also provides information on how to achieve an embedded connection however, there is no much information on how to have a Qt application using both the embedded and server connection. The example below show how to achieve it:

MySQL Dual Connection (Embedded / Server) with QT5

Cheers,

QLands

Working out a table insertion/deletion order from a MySQL Schema

Sometimes for scripting purposes is good to produce a table insertion/deletion order for a MySQL schema. For InnoDB schemas this is important to satisfy constraints without the need of disabling foreign key checks.

Working out a table insertion/deletion order in MySQL is no so straight forward because MySQL does not store anywhere a position or index in which a table was created. The order must be collected from the relationships stored in the information schema.

The following snippet in C++ with Qt generates an insertion order.

#include <QStringList>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QVariant>

QStringList tables; //List of tables
QSqlQuery references; //Query to references

void log(QString message)
{
    QString temp;
    temp = message + "\n";
    printf(temp.toLocal8Bit().data());
}

//Get the minimum index of parent of a table
int getMaxParentIndex(QString table)
{
    int res;
    res = -1;
    references.first();
    while (references.isValid())
    {
        if (references.value(0).toString() == table)
        {
            if (tables.indexOf(references.value(1).toString()) >= res)
                res = tables.indexOf(references.value(1).toString());
        }
        references.next();
    }
    return res;
}

//Fillup the QStringList of tables in insertion order
int getTableOrder(QSqlDatabase db)
{
    QSqlQuery qtables(db);
    QString sql;
    //Get all the tables that are parents
    sql = "SELECT DISTINCT REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE";
    sql = sql + " WHERE table_schema = '" + db.databaseName() + "' AND";
    sql = sql + " REFERENCED_TABLE_NAME is not null";

    if (qtables.exec(sql))
    {
        while (qtables.next())
        {
            tables.append(qtables.value(0).toString());
        }
    }
    else
        return 1;

    //Get only neccesary references to workout the order in parent tables that are also parents
    sql = "SELECT table_name,REFERENCED_TABLE_NAME FROM";
    sql = sql + " information_schema.KEY_COLUMN_USAGE WHERE";
    sql = sql + " table_schema = '" + db.databaseName() + "' AND";
    sql = sql + " REFERENCED_TABLE_NAME is not null AND";
    sql = sql + " table_name IN (SELECT DISTINCT REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE";
    sql = sql + " WHERE table_schema = '" + db.databaseName() + "' AND";
    sql = sql + " REFERENCED_TABLE_NAME is not null)";
    sql = sql + " group by table_name,REFERENCED_TABLE_NAME";
    references = QSqlQuery(db);
    references.exec(sql);

    //Because a parent table can also be a child we need to organize the list so childs are after parents
    int pos;
    int parentIndex;
    QString table;
    for (pos = 0; pos <= tables.count()-1; pos++)
    {
        //Get the maximum index if the parent of this table
        parentIndex = getMaxParentIndex(tables[pos]);
        if (pos < parentIndex) //If the position of this table is before the max parent index
        {
            table = tables[pos]; //Get the table name
            tables.removeAt(pos); //Remove the table from the list
            tables.insert(parentIndex+1,table); //Insert it back in the position after the last parent
            pos = 0; //Back to beginning of the process
        }

    }

    //Append the tables that have no childs
    sql = "SELECT DISTINCT information_schema.tables.table_name FROM information_schema.tables WHERE";
    sql = sql + " information_schema.tables.table_schema = '" + db.databaseName() + "' AND";
    sql = sql + " table_type = 'BASE TABLE' AND";
    sql = sql + " information_schema.tables.table_name NOT IN";
    sql = sql + " (SELECT DISTINCT REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE";
    sql = sql + " WHERE table_schema = '" + db.databaseName() + "' AND";
    sql = sql + " REFERENCED_TABLE_NAME is not null)";
    if (qtables.exec(sql))
    {
        while (qtables.next())
        {
            tables.append(qtables.value(0).toString());
        }
    }
    //Display the list
    for (pos = 0; pos <= tables.count()-1; pos++)
    {
        log(tables[pos]);
    }

    return 0;
}

int main(int argc, char *argv[])
{
    {
        QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
        db.setHostName("localhost");
        db.setPort(3306);
        db.setDatabaseName("MySchema");
        db.setUserName("MyUser");
        db.setPassword("MyPass");
        if (db.open())
        {
            getTableOrder(db);
        }
        else
        {
            log("Cannot connect to database");
            log(db.lastError().databaseText());
            return 1;
        }
    }

    return 0;
}

Exporting Formhub data to JSON files

Few weeks ago we installed FormHub in one of our servers at ILRI. Formhub is an online system for aggregating ODK data coming from mobile devices. The data is stored in a Mongo Database. Although Formhub provides exporting features to CSV and Excel it does not provide exporting to more interoperable formats like JSON.

I created a small Python program that connects to a Formhub database and extracts survey data into a directory using the Survey ID as filter. Each data submission is exported as a JSON file using the UUID as file name.

You can get it from here

The long-awaited post and ODK Viewer

I have been programming software for the past 18 years, had a web-server for the past 10, it is until now that I started a blog about programming and it took me ages to decide on my first post. Quite ridiculous!

Nevertheless I think I can share one of the latest tools I have been working on.

ODK Viewer is a tool for Android devices to visualize ODK (Open Data Kit) data in tabular form. It allows loading, visualizing and editing ODK XML data.

You can grab the code and the APK @ https://github.com/ilri/odkviewer