Embedded SQL

A nostalgic visit from the past: Embedded SQL, where you can inject live SQL directly into your C code.

EMBEDDED SQL IN C
Introduction to Pro*C

The second refers to such development using Oracle. Example from the second:

for (;;) {
    printf("Give student id number : ");
    scanf("%d", &id);
    EXEC SQL WHENEVER NOT FOUND GOTO notfound;
    EXEC SQL SELECT studentname INTO :st_name
             FROM   student
             WHERE  studentid = :id;
    printf("Name of student is %s.\n", st_name);
    continue;
notfound:
    printf("No record exists for id %d!\n", id);
}

It’s worth mentioning just to have some central place to search for it later.

Using XML-RPC with Magento

Sure, we could use a cushy SOAP library to communicate with Magento, but maybe you’d want to capitalize on the cacheability of XML-RPC, instead. Sure, we could use an XML-RPC library, but that would be less fun and, as engineers, we like knowing how stuff works. Magento is not for the faint of heart and knowing how to communicate with it at the low-level might be useful at some point.

import os
import json

import xml.etree.ElementTree as ET

import requests

# http://xmlrpc.scripting.com/spec.html

_HOSTNAME = os.environ['MAGENTO_HOSTNAME']
_USERNAME = os.environ['MAGENTO_USERNAME']
_PASSWORD = os.environ['MAGENTO_PASSWORD']

_URL = "http://" + _HOSTNAME + "/api/xmlrpc"

_HEADERS = {
    'Content-Type': 'text/xml',
}

def _pretty_print(results):
    print(json.dumps(
            results, 
            sort_keys=True,
            indent=4, 
            separators=(',', ': ')))

def _send_request(payload):
    r = requests.post(_URL, data=payload, headers=_HEADERS)
    r.raise_for_status()

    root = ET.fromstring(r.text)
    return root

def _send_array(session_id, method_name, args):

    data_parts = []
    for (type_name, value) in args:
        data_parts.append('<value><' + type_name + '>' + str(value) + '</' + type_name + '></value>')

    payload = """\
<?xml version='1.0'?>
<methodCall>
    <methodName>call</methodName>
    <params>
        <param>
            <value><string>""" + session_id + """\
</string></value>
        </param>
        <param>
            <value><string>""" + method_name + """\
</string></value>
        </param>
        <param>
            <value>
                <array>
                    <data>
                        """ + ''.join(data_parts) + """
                    </data>
                </array>
            </value>
        </param>
    </params>
</methodCall>
"""

    return _send_request(payload)

def _send_struct(session_id, method_name, args):
    struct_parts = []

    for (type_name, argument_name, argument_value) in args:
        struct_parts.append("<member><name>" + argument_name + "</name><value><" + type_name + ">" + str(argument_value) + "</" + type_name + "></value></member>")

    payload = """\
<?xml version='1.0'?>
<methodCall>
    <methodName>call</methodName>
    <params>
        <param>
            <value><string>""" + session_id + """\
</string></value>
        </param>
        <param>
            <value><string>""" + method_name + """\
</string></value>
        </param>
        <param>
            <value>
                <struct>
                    """ + ''.join(struct_parts) + """
                </struct>
            </value>
        </param>
    </params>
</methodCall>
"""

    return _send_request(payload)

def _send_login(args):
    param_parts = []
    for (type_name, value) in args:
        param_parts.append('<param><value><' + type_name + '>' + value + '</' + type_name + '></value></param>')

    payload = """\
<?xml version="1.0"?>
<methodCall>
    <methodName>login</methodName>
    <params>""" + ''.join(param_parts) + """\
</params>
</methodCall>
"""

    return _send_request(payload)


class XmlRpcFaultError(Exception):
    pass

def _distill(value_node):
    type_node = value_node[0]
    type_name = type_node.tag

    if type_name == 'nil':
        return None
    elif type_name in ('int', 'i4'):
        return int(type_node.text)
    elif type_name == 'boolean':
        return bool(type_node.text)
    elif type_name == 'double':
        return float(type_node.text)
    elif type_name == 'struct':
        values = {}
        for member_node in type_node:
            key = member_node.find('name').text

            value_node = member_node.find('value')
            value = _distill(value_node)

            values[key] = value

        return values
    elif type_name == 'array':
        flat = []
        for i, child_value_node in enumerate(type_node.findall('data/value')):
            flat.append(_distill(child_value_node))

        return flat
    elif type_name in ('string', 'dateTime.iso8601', 'base64'):
        return type_node.text
    else:
        raise ValueError("Invalid type: [{0}] [{1}]".format(type_name, type_node))

def _parse_response(root):
    if root.find('fault') is not None:
        for e in root.findall('fault/value/struct/member'):
            if e.find('name').text == 'faultString':
                message = e.find('value/string').text
                raise XmlRpcFaultError(message)

        raise ValueError("Malformed fault response")

    value_node = root.find('params/param/value')
    result = _distill(value_node)

    return result

def _main():
    args = [
        ('string', _USERNAME),
        ('string', _PASSWORD),
    ]

    root = _send_login(args)
    session_id = _parse_response(root)

    resource_name = 'catalog_product.info'

    args = [
        ('int', 'productId', '314'),
    ]

    root = _send_struct(session_id, resource_name, args)
    result = _parse_response(root)
    _pretty_print(result)

if __name__ == '__main__':
    _main()

Output:

{
    "apparel_type": "33",
    "categories": [
        "13"
    ],
    "category_ids": [
        "13"
    ],
    "color": "27",
    "country_of_manufacture": null,
    "created_at": "2013-03-05T00:48:15-05:00",
    "custom_design": null,
    "custom_design_from": null,
    "custom_design_to": null,
    "custom_layout_update": null,
    "description": "Two sash, convertible neckline with front ruffle detail. Unhemmed, visisble seams. Hidden side zipper. Unlined. Wool/elastane. Hand wash.",
    "fit": null,
    "gender": "94",
    "gift_message_available": null,
    "gift_wrapping_available": null,
    "gift_wrapping_price": null,
    "group_price": [],
    "has_options": "0",
    "image_label": null,
    "is_recurring": "0",
    "length": "82",
    "meta_description": null,
    "meta_keyword": null,
    "meta_title": null,
    "minimal_price": null,
    "msrp": null,
    "msrp_display_actual_price_type": "4",
    "msrp_enabled": "2",
    "name": "Convertible Dress",
    "news_from_date": "2013-03-01 00:00:00",
    "news_to_date": null,
    "occasion": "29",
    "old_id": null,
    "options_container": "container1",
    "page_layout": "one_column",
    "price": "340.0000",
    "product_id": "314",
    "recurring_profile": null,
    "required_options": "0",
    "set": "13",
    "short_description": "This all day dress has a flattering silhouette and a convertible neckline to suit your mood. Wear tied and tucked in a sailor knot, or reverse it for a high tied feminine bow.",
    "size": "72",
    "sku": "wsd017",
    "sleeve_length": "45",
    "small_image_label": null,
    "special_from_date": null,
    "special_price": null,
    "special_to_date": null,
    "status": "1",
    "tax_class_id": "2",
    "thumbnail_label": null,
    "tier_price": [],
    "type": "simple",
    "type_id": "simple",
    "updated_at": "2014-03-08 08:31:20",
    "url_key": "convertible-dress",
    "url_path": "convertible-dress-418.html",
    "visibility": "1",
    "websites": [
        "1"
    ],
    "weight": "1.0000"
}

You may download the code here.

Converting Infix Expressions to Postfix in Python

A simplified Python algorithm for converting infix expressions to postfix expressions using Dijkstra’s “shunting-yard” algorithm. We omit support for functions and their arguments but support parenthesis as expected. For the purpose of this example, we support simple mathematical expressions.

OP_LPAREN = '('
OP_RPAREN = ')'
OP_MULTIPLY = '*'
OP_DIVIDE = '/'
OP_ADD = '+'
OP_SUBTRACT = '-'

OPERATORS_S = set([
    OP_MULTIPLY, 
    OP_DIVIDE, 
    OP_ADD, 
    OP_SUBTRACT, 
    OP_LPAREN, 
    OP_RPAREN,
])

PRECEDENCE = {
    OP_MULTIPLY: 7,
    OP_DIVIDE: 7,
    OP_ADD: 5,
    OP_SUBTRACT: 5,
    OP_LPAREN: 1,
    OP_RPAREN: 1,
}

LEFT_ASSOCIATIVE_S = set([
    OP_MULTIPLY,
    OP_DIVIDE,
    OP_ADD, 
    OP_SUBTRACT, 
    OP_LPAREN, 
    OP_RPAREN,
])

def _convert(expression_phrase):
    expression_phrase = expression_phrase.replace(' ', '')

    stack = []
    output = []
    for c in expression_phrase:
        if c not in OPERATORS_S:
            # It's an operand.
            output += [c]
        elif c not in (OP_LPAREN, OP_RPAREN):
            # It's an operator. Pop-and-add all recent operators that win over 
            # the current operator via precendence/associativity.

            current_prec = PRECEDENCE[c]
            is_left_assoc = c in LEFT_ASSOCIATIVE_S
            while len(stack):
                top_value = stack[-1]
                top_prec = PRECEDENCE[top_value]

                if is_left_assoc is True and current_prec <= top_prec or \
                   is_left_assoc is False and current_prec < top_prec:
                    stack.pop()
                    output += [top_value]
                else:
                    break

            stack.append(c)

        elif c == OP_LPAREN:
            # It's a left paren.

            stack.append(c)
        else: #if c == OP_RPAREN:
            # It's a right paren. Pop-and-add everything since the last left 
            # paren.

            found = False
            while len(stack):
                top_value = stack.pop()
                if top_value == OP_LPAREN:
                    found = True
                    break

                output += [top_value]

            if found is False:
                raise ValueError("Mismatched parenthesis (1).")

    if stack and stack[-1] in (OP_LPAREN, OP_RPAREN):
        raise ValueError("Mismatched parenthesis (2).")

    # Flush everything left on the stack.
    while stack:
        c = stack.pop()
        output += [c]

    return ' '.join(output)

def _main():
    infix_phrase = 'a * (b * c) / d * (e + f + g) * h - i'
    print(infix_phrase)

    postfix_phrase = _convert(infix_phrase)
    print(postfix_phrase)

if __name__ == '__main__':
    _main()

Output:

a * (b * c) / d * (e + f + g) * h - i
a b c * * d / e f + g + * h * i -

You may download the code here.

SQLite as an Archive (or an Easy, Compressed Filesystem)

The sqlar project was an experiment to test the practicality of a SQLite archive format by the lead SQLite developer. I say “was” because, like sqlite, it seldom needs modification and appears to be stable.

What makes it fun is that the result is not internalized and inaccessible. Rather, it’s an intact SQLite database that you can readily inspect from the SQLite client. Therefore, the compression is per-blob and the purpose of the tool is merely to make it convenient to add records corresponding to files. The reverse should be true as well: if you create a sqlite DB and populate it with zlib-compressed data, you should simply be able to dump it using the sqlar tool.

To test sqlar out, either checkout or download a copy of the source (which embeds SQLite within it). You may download it here: SQLite Archiver.

Extract it and build:

$ mkdir sqlar
$ cd sqlar/
$ tar xzf ../sqlar-src-15adeb2f9a.tar.gz 
$ cd sqlar-src-15adeb2f9a/

$ make
gcc -g -I. -D_FILE_OFFSET_BITS=64 -Wall -Werror -c sqlite3.c -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION sqlite3.c
gcc -g -I. -D_FILE_OFFSET_BITS=64 -Wall -Werror -o sqlar sqlar.c sqlite3.o -lz

Run a test:

$ mkdir test_root
$ echo &quot;test1&quot; &gt; test_root/test1
$ echo &quot;test2&quot; &gt; test_root/test2
$ echo &quot;test3&quot; &gt; test_root/test3

$ ./sqlar -v test_root.sqlar test_root
  added: test_root
  added: test_root/test1
  added: test_root/test3
  added: test_root/test2

$ mkdir output
$ cd output/

$ ../sqlar -x -v ../test_root.sqlar 
test_root
test_root/test1
test_root/test3
test_root/test2

Visually inspect the database file:

$ cd ..

$ sqlite3 test_root.sqlar 
SQLite version 3.8.2 2013-12-06 14:53:30
Enter &quot;.help&quot; for instructions
Enter SQL statements terminated with a &quot;;&quot;

sqlite&gt; .schema
CREATE TABLE sqlar(
  name TEXT PRIMARY KEY,
  mode INT,
  mtime INT,
  sz INT,
  data BLOB
);

sqlite&gt; select * from sqlar;
test_root|16893|1455064403|0|
test_root/test1|33204|1455064393|6|test1

test_root/test3|33204|1455064403|6|test3

test_root/test2|33204|1455064399|6|test2

sqlite&gt; 

Notice that no compression was performed because the files are so trivial. zlib is used for compression every time unless you explicitly turn it off using “-n”.

You can also mount the archive using FUSE:

$ # This requires libfuse-dev to be installed.
$ make sqlarfs
gcc -g -I. -D_FILE_OFFSET_BITS=64 -Wall -Werror -o sqlarfs sqlarfs.c sqlite3.o -lz -lfuse
$ mkdir mount
$ ./sqlarfs test_root.sqlar `pwd`/mount

In another terminal, list the contents:

$ ls -l
total 0
dr-xr-xr-x 1 dustin dustin 0 Feb  9 19:51 test_root

$ cd test_root/

$ ls -l
total 0
-r--r--r-- 1 dustin dustin   6 Feb  9 19:33 test1
-r--r--r-- 1 dustin dustin   6 Feb  9 19:33 test2
-r--r--r-- 1 dustin dustin   6 Feb  9 19:33 test3
-r--r--r-- 1 dustin dustin 576 Feb  9 19:51 test4

$ cat test4 
Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.

This was a later version of our earlier archive where I added a highly compressible text-file (test4).

It’s a fun little tool.

Accessing Sales History in Magento From Python With XML-RPC

Install the python-magento package and simply pass in database table columns with some criteria. I had difficulty figuring out the structure of the filters because there’s so little documentation/examples anywhere online and, where there are, they all differ format/version.

So, here’s an example:

import magento
import json

_USERNAME = "apiuser"
_PASSWORD = "password"
_HOSTNAME = "dev.bugatchi.com"
_PORT = 80

m = magento.MagentoAPI(_HOSTNAME, _PORT, _USERNAME, _PASSWORD)

filters = {
    'created_at': {
        'from': '2013-05-29 12:38:43',
        'to': '2013-05-29 12:55:33',
    },
}

l = m.sales_order_invoice.list(filters)
print(json.dumps(l))

Output:

[
    {
        "created_at": "2013-05-29 12:38:43",
        "grand_total": "447.1400",
        "increment_id": "100000036",
        "invoice_id": "38",
        "order_currency_code": "USD",
        "order_id": "186",
        "state": "2"
    },
    {
        "created_at": "2013-05-29 12:52:44",
        "grand_total": "333.2100",
        "increment_id": "100000037",
        "invoice_id": "39",
        "order_currency_code": "USD",
        "order_id": "185",
        "state": "2"
    },
    {
        "created_at": "2013-05-29 12:55:33",
        "grand_total": "432.2600",
        "increment_id": "100000038",
        "invoice_id": "40",
        "order_currency_code": "USD",
        "order_id": "184",
        "state": "2"
    }
]

Note that debugging is fairly simple since a failure will return the failed query (unless the server is configured not to). So, you can use that to feel out many of the column names and comparison operators.

Efficiently Processing GPX Files in Go

Use gpxreader to process a GPX file of any size without reading the whole thing into memory. This also avoids Go’s issue where the decoder can decode one node at a time, but, when you do that, it implicitly ignores all child nodes (because it automatically seeks to the matching close tag for validation without any ability to disable this behavior).

An excerpt of the test-script from the project:

//...

func (gv *gpxVisitor) GpxOpen(gpx *gpxreader.Gpx) error {
    fmt.Printf("GPX: %s\n", gpx)

    return nil
}

func (gv *gpxVisitor) GpxClose(gpx *gpxreader.Gpx) error {
    return nil
}

func (gv *gpxVisitor) TrackOpen(track *gpxreader.Track) error {
    fmt.Printf("Track: %s\n", track)

    return nil
}

func (gv *gpxVisitor) TrackClose(track *gpxreader.Track) error {
    return nil
}

func (gv *gpxVisitor) TrackSegmentOpen(trackSegment *gpxreader.TrackSegment) error {
    fmt.Printf("Track segment: %s\n", trackSegment)

    return nil
}

func (gv *gpxVisitor) TrackSegmentClose(trackSegment *gpxreader.TrackSegment) error {
    return nil
}

func (gv *gpxVisitor) TrackPointOpen(trackPoint *gpxreader.TrackPoint) error {
    return nil
}

func (gv *gpxVisitor) TrackPointClose(trackPoint *gpxreader.TrackPoint) error {
    fmt.Printf("Point: %s\n", trackPoint)

    return nil
}

//...

func main() {
    var gpxFilepath string

    o := readOptions()

    gpxFilepath = o.GpxFilepath

    f, err := os.Open(gpxFilepath)
    if err != nil {
        panic(err)
    }

    defer f.Close()

    gv := newGpxVisitor()
    gp := gpxreader.NewGpxParser(f, gv)

    err = gp.Parse()
    if err != nil {
        print("Error: %s\n", err.Error())
        os.Exit(1)
    }
}

Output:

$ gpxreadertest -f 20140909.gpx
GPX: GPX
Track: Track
Track segment: TrackSegment
Point: TrackPoint
Point: TrackPoint
Point: TrackPoint