SQL to anything: installation

s2x converts SQL functions calls to a variety of formats (JSON, XML, csv, Excel) and returns result sets over HTTP.

s2x is distributed as source and runs as a FastCGI process connected to a web server. s2x needs PostgreSQL, Apache and a number of other mainstream components.

Download the latest version: s2x-1.3.

Summary (Ubuntu):

$ sudo apt-get install g++ make apache2 libfcgi-dev libapache2-mod-fcgid libpq-dev libarchive-dev
$ sudo a2enmod rewrite
$ wget http://s2x.co/z/s2x-1.3.tar.gz
$ tar -xzf s2x-1.3.tar.gz
$ cd s2x-1.3
$ ./configure
$ make
$ sudo make install

Prerequisites

s2x should compile and run on UNIX systems with the following prerequisites:

  1. PostgreSQL libpq 8.1+: You obviously need a database server (PostgreSQL 8.1+)
  2. Apache 2.2+
  3. A recent C++ compiler with support for C++11 (e.g. GNU g++ 4.8); also libstdc++ and make
  4. libarchive: a popular multi-format archive and compression library
  5. Perl 5.10+: the automated installation uses a Perl script. You don't need it if you will build/install manually.
  6. Apache mod_rewrite: s2x calls by file name extension use mod_rewrite. You don't need it if you will only be making calls to endpoint.

s2x uses FastCGI to connect to the web. The following third party packages are also included in the installation. You can install them from your OS distribution (if available), or the s2x installer can install them from the package.

Ubuntu

Tested on Ubuntu 14.04, 13.10 and 12.04 (without SELinux). Ubuntu 10.04 is not supported.

$ sudo apt-get install g++ make apache2 libfcgi-dev libapache2-mod-fcgid libpq-dev libarchive-dev
$ sudo a2enmod rewrite

RedHat/CentOS/Fedora

Should work on all RedHat variants.

# yum install gcc-c++ perl make httpd postgresql-devel httpd-devel libarchive-devel policycoreutils policycoreutils-python
# yum install gcc-c++ perl make httpd postgresql-devel httpd-devel libarchive-devel

Source

  • Download s2x version 1.3
  • $ sha256sum -b s2x-1.3.tar.gz
    145bc6e1fbab921ae21cb49aa75175f6508e025fd1ac3c7290ede7b6ac3d8be5 *s2x-1.3.tar.gz
    

    Database setup

    s2x needs PostgreSQL 8.1+

    You need a database role (e.g. s2x) that can connect to your database server from the installation machine. The role needs to have execute privileges on functions you want exposed through s2x.

    GRANT EXECUTE ON FUNCTION listProductNames() TO s2x;
    

    Ideally, you should REVOKE all other privileges from this role.

    Automated installation

    $ wget http://s2x.co/z/s2x-1.3.tar.gz
    $ tar -xzf s2x-1.3.tar.gz
    $ cd s2x-1.3
    $ ./configure
    $ make
    $ sudo make install
    

    The automated installer is a perl script. Use the manual installation if you don't want perl on your system.

    At the first run, the installer will ask you to:

    1. Select a site for the installation (e.g. example.com:80) from your Apache configuration (if your Apache installation has more than one site).
    2. Provide database connection information (host, database, user, password)
    3. Choose if you want to install mod_fcgid from the package (if it hasn't been detected on your system)

    Apache may stop working if you install mod_fcgid and don't update your SELinux policies.

    SELinux configuration may be an involved process if you choose to do it manually.

    The installer will detect and prompt you for missing prerequisites.

    After a successful installation, you should see:

    curl http://example.com:80/json?hello
    {"hello":{"error":"ERROR:  function hello() does not exist"}}

    If your database connection parameters are wrong you can expect:

    {"hello":{"error":"cannot connect to database server"}}

    You should be able to access all SQL functions visible through the account specified during the installation. Remember to provide the required parameters on the URL:

    curl http://example.com:80/json?YourFunction

    If mod_rewrite is installed and enabled in your installation, you should see:

    curl http://example.com:80/hello.json
    {"hello":{"error":"ERROR:  function hello() does not exist"}}

    If mod_rewrite is not active, you will most likely get a 404: Not Found error in response to hello.json.

    If you haven't messed a lot with the installation, you can automatically uninstall s2x:

    sudo make uninstall

    Manual installation

    s2x comes with a generic Makefile (and its backup Makefile.manual) that should theoretically work on a system with all the prerequisites.

    You need to know your way around:

    At a minimum:

    1. You should make sure that the Makefile target for your s2x binaries is set correctly.
    2. Unless you are using some more sophisticated configuration, you will need to create mod_fcgid configuration directives in the Apache configuration files for the s2x binaries (endpoints):
    TARGET_DIRECTORY= /var/www/*your-site*/
    
    <Location /xml>
        SetHandler fcgid-script
        Options +ExecCGI
    </Location>
    

    Database connection parameters are passed as InitialEnv command line options for the FcgidCmdOptions directive.

    FcgidCmdOptions /var/www/*your-site*/xml \
        InitialEnv host=*your-host* \
        InitialEnv user=*your-user* \
        InitialEnv password=*your-password*
    

    To support call by extension, you need mod_rewrite and the following directives:

    RewriteEngine on
    RewriteRule ^/(.*)\.(json|xml|csv|xlsx)$ /$2?$1&%{QUERY_STRING} [PT]
    

    When you are ready:

    $ make
    $ sudo make install