Caché Data Access Modes

Since I have joined InterSystems a while ago, I thought a Caché post might be appropriate, especially since it illustrates quite nicely the various data access modes offered by the platform.
Below is a routine called TestModes.mac, invoked by a >do ^TestModes. The SQLCompile macro is necessary because the SQL code references objects which do not exist compile time, before CreateTable is run (InsertData). The multi-dimensional sparse arrays created as SQL tables (by CreateTable) are accessed in multi-value mode by ReadMV and deleted by DropMV (which also uses the DeleteExtent method of the data objects to perform a ‘table truncation’ on the data objects).
Another nice feature is the extraction of the list items from the arrays (table data rows are stored as array items, whereby each field is stored in a string list created by $LISTBUILD) back to strings by the $LISTTOSTRING function.
Some legacy features are visible (the need to declare SQLCODE as a public variable and make it NEW to restrict it to the current scope). I don’t think that this kind of mix would make sense in an application, where it would make sense to use one access metaphor and only one for a given data item; but this is still a valuable example which ties the data architecture(s) of Caché together nicely.

TestModes
; shows the different data access types
; SQL, MV, class
#SQLCompile Mode = DEFERRED

DO CreateTable
DO InsertData(1000)
DO ReadMV
DO DropMV
WRITE $$ReadData()
QUIT
;
CreateTable()
{
&sql( DROP TABLE items)
&sql( CREATE TABLE items(id INT, data VARCHAR(50)))
QUIT
}

InsertData(upto)[SQLCODE]
{
#DIM x as %String
#DIM i as %Integer

NEW SQLCODE

FOR i = 1:1:upto
{
SET x = i _ " DataItem"
&sql( INSERT INTO SQLUser.items(id, data) VALUES( :i, :x))
IF (SQLCODE '= 0 )
{
WRITE "SQL error:", $ZERROR, !
}
}
QUIT
}

ReadMV()
{
#DIM ky AS %String
#DIM extr AS %String

WRITE "Navigating the data", !

SET ky = $ORDER(^User.itemsD(""))
WHILE( ky '= "")
{
; WRITE ky, ^User.itemsD(ky), !
SET ky = $ORDER(^User.itemsD(ky))
IF ( ky '= "" )
{
SET extr = $LISTTOSTRING(^User.itemsD(ky))
WRITE ?5, extr, !
}
}
QUIT
}

DropMV()
{
DO ##class(User.items).%DeleteExtent()
KILL ^User.itemsD
QUIT
}

ReadData() [SQLCODE]
{
#Dim result AS %String
NEW SQLCODE

&sql(SELECT COUNT(*) INTO :result FROM SQLUser.items )
; the table is still there, but empty

IF (SQLCODE '= 0) SET result = "Error " _ $ZERROR
Q result

}

Tableau Public

My first attempt!

MySQL and SQL Server

...actually, how to make the two talk.

Create a Linked Server in SQL Server (using ODBC):

To query (from SQL Server):

select top 5 * from mysqlanalytics...analytics;

select * from mysqlanalytics...session;

Stored procedures aren't available, but you can get around this by writing a view in MySQL to return the data from the stored procedure (and perhaps use a table to get any parameters the stored procedure might need).

mysql> create view ViewSession as
    -> select GetSession() as CurrentSession;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from viewsession;
+----------------+
 CurrentSession
+----------------+
              1
+----------------+

And ViewSession is now available in SQL Server:

select * from mysqlanalytics...ViewSession

Google Analytics API

Very basic GA API project (very poorly hosted too!)

Adobe Forms

Adobe Forms review. A test run of XForms, later.

EMC xProc Designer

Yet one more visual designer.

CMS everywhere

This is old news, but it is interesting to see how ASP.MVC follows the same principles as, for example, Django or Zope; it contains the building blocks for a CMS, and supersedes the ASP.NET paradigm.

That being said, there are conceptual differences - Zope is middleware-centric (the database is customized for it), Django is database-centric (you start with the db definition, even if it is done at the ORM level: you start with the classes in Python which then get mapped to relational tables) and ASP.MVC is purely database centric (using LINQ to SQL - the classes are generated from the relational tables).

Finally you have something like eZ Publish where objects exist somewhere between PHP and the CMS, and are stored in a bit bucket (relational database, but with no relational features per se, so they might be better off using Mongo or Cache for speed).

Other than that, same features - URL mapping, templates, etc.

Next, I'll delve into ZODB, it would be interesting to see how much is actually stored there and how much in /var.

---

Speaking of ASP.MVC, here are a couple of links on SEO, relevant to other CMS as well (especially to migrations!):

• SEO features of ASP.MVC
• SEO Toolkit
• URL routing in ASP.MVC

Python classes

Old-style:

class OldClass:

    def method(self):

        ….

Characterized by:

P = OlcClass()

p.__class__ à 'OldClass'

type(p) à 'instance'

 

>>> class Test:

    def __init__(self):

        print 'Test initialized'

    def meth(self):

        self.member = 1

        print 'Test.member = ' + str(self.member)

 

>>> class TestKid(Test):

    "This is derived from Kid, meth is overriden and so is member"

    def __init__(self):

        print 'Kid initialized'

    def meth(self):

        self.member = 2

Test.meth(self)

print 'Kid.member = ' + str(self.member)

 

Above is shown how to override a method, call its parent implementation; the member attribute is shared between the parent and child classes and hence calling a function in parent which references it will modify it in the child as well. The parent constructor (or any other overridden function) is not called by default.

New-style:

>>> class Test(object):

Type(p) would return 'Test'. Unifies types and classes.

It has classmethods and staticmethods.

Also (for both old and new):

P = Test() ; calling a class object yields a class instance

p.__dict__ à {'member':1}

p.__dict__['member'] = 1 ; same as p.member

 

You can use properties (almost .NET-style) to access class attributes with new classes.

More.

Images in SQL Server

A simple .NET class to dump image data into/from SQL Server. I'd like to explore a potential alternative to this using FILESTREAM.

In T-SQL:

update version set [file] = BulkColumn from
openrowset(bulk 'e:\....jpg', single_blob) as [file]
where ...;

---

In Python/MySQL, this is done like this: (the image column, image_data, is defined as BLOB in MySQL)

>>> import MySQLdb
>>> connection = MySQLdb.connect('','root','','RTest')
>>> blob = open('d:\\pic1.jpg', 'rb').read()
>>> sql = 'INSERT INTO rtest.mm_image(image_data, mm_person_id_mm_person) VALUES(%s, 1)'
>>> args = (blob,)
>>> cursor = connection.cursor()
>>> cursor.execute(sql, args)
>>> connection.commit()

blob is a string type.

CMSRDBMSWT..?

There seems to be precious little information on data management in the context of CMS (Content Management Systems). To me, this would seem very important - data admin, modeling, etc, all should be hugely important, and yet some of the CMS I come across are little more than bit buckets with a data dictionary. While web-based CMS have certainly driven the development of NoSQL (due to performance reasons, given the underoptimal use of the RDBMS by the CMS), certainly some thought has gone into the data layer of the CMS... where is it at?

---

Speaking of CMS: Here's Plone, which is running on a NoSQL incidentally.

Python ORM

A first shot at ORMing in Python.

The second, improved shot. Inheritance/polymorphism in weakly-typed languages such as Python is a bit hard to grasp at first. Anyway, this seems quite cool.

Class diagram: (I am not an expert @ UML)




Existing solutions:
- for PHP
- for Python

Collaboration tools

Great info:

BLOBing in Mongo

SQL Server, Oracle, Cache, etc, all have binary streams, and offer various ways of storing binary data (such as images) directly into the database. I was curious to see how this would work with Mongo, and here is the result:

import pymongo
import urllib2
import wx
import sys
from pymongo import Connection

class Image:

    def __init__(self):
        self.connection = pymongo.Connection()
        self.database = self.connection.newStore
        self.collection = self.database.newColl
        self.imageName = "Uninitialized"
        self.imageData = ""

    def loadImage(self, imageUrl, imageTitle = "Undefined"):
        try:
            ptrImg = urllib2.Request(imageUrl)
            ptrImgReq = urllib2.urlopen(ptrImg)
            imageFeed = ptrImgReq.read()
            self.imageData = pymongo.binary.Binary(imageFeed, pymongo.binary.BINARY_SUBTYPE)
            self.imageName = imageTitle
            ptrImgReq.close()
       
        except:
            self.imageName = "Error " + str(sys.exc_info())
            self.imageData = None

    def persistImage(self):
        if self.imageData == None:
            print 'No data to persist'
        else:
            print 'Persisting ' + self.imageName
            self.collection.insert({"name":self.imageName, "data":self.imageData})
            self.imageData = None
   
    def renderImage(self, parm = None):
        if parm == None:
            self.imageData = self.collection.find_one({"name":self.imageName})
        else:
            self.imageName = parm
            self.imageData = self.collection.find_one({"name":self.imageName})
   
        ptrApp = wx.PySimpleApp()
        fout = file('d:/tmp.jpg', 'wb')
        fout.write(self.imageData["data"])
        fout.flush()
        fout.close()
        wximg = wx.Image('d:/tmp.jpg',wx.BITMAP_TYPE_JPEG)
        wxbmp = wximg.ConvertToBitmap()
        ptrFrame = wx.Frame(None, -1, "Show JPEG demo")
        ptrFrame.SetSize(wxbmp.GetSize())
        wx.StaticBitmap(ptrFrame, -1, wxbmp, (0,0))
        ptrFrame.Show(True)
        ptrApp.MainLoop()


img = Image()
img.loadImage('http://i208.photobucket.com/albums/bb82/julianzzkj/Acapulco/e614.jpg', 'Acapulco at night')
img.persistImage()
img.renderImage('Acapulco at night')

I have had some problems with installing PIL, so this is certainly not optimal (I have to use wx for image rendering instead, and I have not found a way of feeding a JPG datastream to an image constructor, hence the ugly recourse to a temporary file). However, the idea was to test how the database can store an image, which seems to work quite well, despite taking a few seconds to load a 300kb file.

A findOne query returns:

> db.newColl.findOne()
{
        "_id" : ObjectId("4be82f74c7ccc11908000000"),
        "data" : BinData type: 2 len: 345971,
        "name" : "Acapulco at night"
}
>

Thanks are due for some of the wx code.

Some thoughts on NoSQL

So I have been playing with various database systems, many pertaining to the NoSQL category. Here are some thoughts:

• Mongo is cool. I definitely like it. However, it differs from Cache in one important way: JSON objects are native to JavaScript. To everything else, they are just a text format (that Python can understand easily, true) not necessarily any more efficient than XML. Cache objects are more or less portable across languages and the impedance mismatch between the consumer and the database is definitely much less significant than in the case of Mongo;
• Mongo is fast, and easy enough to understand for perhaps a dozen or two 'collections'. I am not sure how well it would support (or perform) with a 3000-table schema, which is not at all unlikely in an enterprise application. While the proliferation of tables is a perverse effect of relational normalization, the fact is that the relational model is easy to understand. Complex text representations of object hierarchies, which Mogo really allows for, might quickly spin out of control (assuming that the schema is kept under control by restricting access to the database through the front end, and object collections to not degenerate to the point of being simple bit buckets);
• so Mongo might be best appropriate in an environment with a few deep entities with loose connections: e.g. 12-25 'tables' with million+ rows, especially for client apps that can read JSON (or derivatives: such as Python's collection objects) more or less natively.

More on this:

• VoltDB @ InformationWeek
• and @ RWW

Twitter Python Mongo

...or how many buzzwords can you get in one title. Here is a shortish piece of code that pulls data from Twitter and inserts it into Mongo. Other than the shortness of the code (given what it accomplishes!), what is remarkable is the ease of use of the data that is passed around, with a minimum amount of marshalling: Twitter can return data in JSON which is the native Mongo format and Python can use with a minimum of tweaking (mostly to reduce the response from Twitter).

import urllib
import json
import string
from pymongo import Connection

def runQuery(query, pp, pages):
 ret = []
 for pg in range(1, pages+1):
  print 'page...' + str(pg)
  p = urllib.urlopen('http://search.twitter.com/search.json?q=' + query + '&rpp=' + str(pp) + '&page=' + str(pg))
  s = json.load(p)
  dic = json.dumps(s)
  dic = string.replace(dic, 'null', '"none"')
  dx = eval(dic)
  listOfResults = dx['results']
  for result in listOfResults:
   ret.append( { 'id':result['id'], 'from_user':result['from_user'], 'created_at':result['created_at'], 'text': result['text'] } )
  completeRet = {"results": ret}
 return completeRet
  
c = Connection()
d = c.twitterdb
coll = d.postbucket
res = runQuery('Iran', 100, 15)
ptrData = res.get('results')
for item in ptrData:
 coll.save(item)

A Twitter Python web service

Taking the code from the previous post: here is a Python web service that reads the Twitter feed for a given query and returns a subset of the results in JSON:

import urllib
import json
import string
import SimpleXMLRPCServer
from SimpleXMLRPCServer import SimpleXMLRPCServer
from SimpleXMLRPCServer import SimpleXMLRPCRequestHandler

def runQuery(query, pp, pages):
 p = urllib.urlopen('http://search.twitter.com/search.json?q=' + query + '&rpp=' + str(pp) + '&page=' + str(pages))
 s = json.load(p)
 dic = json.dumps(s)
 dic = string.replace(dic, 'null', '"none"')
 dx = eval(dic)
 listOfResults = dx['results']
 ret = []
 for result in listOfResults:
  ret.append( { 'id':result['id'], 'from_user':result['from_user'], 'created_at':result['created_at'], 'text': result['text'] } )
 completeRet = {"results": json.dumps(str(ret))}
 return str(completeRet)
  
class RequestHandler(SimpleXMLRPCRequestHandler):
 rpc_paths=('/RPC2')
 
server=SimpleXMLRPCServer(("localhost", 8000), requestHandler=RequestHandler)
server.register_introspection_functions()
server.register_function(runQuery, 'qry')
server.serve_forever()

More potential uses of this (including Google Apps, Mongo, or Processing) later. And here is how to use it (from Python):

>>> import xmlrpclib
>>> s = xmlrpclib.ServerProxy('http://localhost:8000')
>>> print s.qry('Bumrungrad', 10, 1)

Where the first numeric parameter is the number of records per page and the second, the number of page (max 100/15).

Twitter API

A bit of topical coding.... getting tweets regarding the situation in Bangkok:

>>> import urllib
>>> from xml.dom import minidom
>>> p=urllib.urlopen('http://search.twitter.com/search.atom?q=Bangkok')
>>> xml=minidom.parse(p)
>>> p.close()
>>> nodes=xml.getElementsByTagName('title')
>>> for node in nodes:
 print node.firstChild.NodeValue

It's the first time I try the Twitter API, and it seems simple enough!

Mongo and Cache

Some similarities:

• the system-generated row id: (_id for Mongo)
• object references, and a kind of relationship definition in Mongo:

> x = {name:'Lab test'}
{ "name" : "Lab test" }
> db.second.save(x)
> pat = {name:'Amornrakot', test:[new  DBRef('second', x._id)]}
{
        "name" : "Amornrakot",
        "test" : [
                {
                        "$ref" : "second",
                        "$id" : ObjectId("4bd6d7c64e660000000f665a")
                }
        ]
}
> pat.test[0].fetch()
{ "_id" : ObjectId("4bd6d7c64e660000000f665a"), "name" : "Lab test" }

The similarities aren't surprising perhaps; it is the differences that trouble me (in this case, Mongo's looseness - lack of structure); although SQLite was the first one to go down that path, by not enforcing strict data typing, and now Mongo doesn't even enforce schemas. A discussion on Mongo database design principles here.

For now I have a couple of other questions:

• is there a reporting tool that binds to JSON/Mongo natively?
• how do you update an existing JSON entry? just one tuple, not the entire record; some notes:
• var p = db.coll.findOne();
• p.member (notation supported, p is an object already and there is no need to eval() it; originally, say p{member:"y"} ) = "x" and now p is disconnected from the collection, but db.coll.save(p) does update it in place

What is cool is that you can save JS objects (declared using the JS object notation):

function pobj(param){this.p1=param;}

var newObj = new pobj("test");

db.coll.save(newObj);

db.coll.find(); returns { "_id" : ObjectId("4bd722a6eb29000000007ac4"), "p1" : "test" }. You can even 'serialize' objects' methods, and then call the method for the objects deserialized using findOne. All of this might be JS-specific candy, I am curious how this ports over to other language drivers.

So you can view Mongo as a (JS) object-oriented database, with nothing in the way of SQL facilities though; a tuple serialization mechanism; a key-value pair list; a 'document'/hierarchical database using JSON as the document format (as opposed to xDB's XML), all of which are correct.

Another question: when you have an embedded object, var ptrUser = {name : "Mr Iwata", address : { city : "Tokyo }}, how do you search by the inner object properties? db.coll.find({address:{ city : "criteria" }} does not seem to work.RTfM

Also, if you store objects with different structures in one collection, they can be inspected:

from pymongo import Connection
c = Connection()
d = c.clinical
coll = d.physician
for item in coll.find():
 itmkeys = []
 print item.get("_id")
 for ky in item.iterkeys():
  itmkeys.append(ky)
 print itmkeys

Lots of interesting info at the Wikipedia JSON page.

Data visualization

Some resources:

• Tableau Software
• Smart Data Collective
• ITO World
• Protovis

Very basic Google Chart

• create the URL
• you can then pull it in Python:
  

>>> import urllib
>>> p=urlopen('http://chart.apis.google.com/chart?chs=250x100&chd=t:60,40,90,20&cht=p3')
>>> data = p.read()
>>> f = file('d:\\file.png', 'wb')
>>> f.write(data)
>>> f.close()

It's quite easy to build the URL based on the data in a Googledoc spreadsheet: (code modified from Google's own documentation)

try:
  from xml.etree import ElementTree
except ImportError: 
  from elementtree import ElementTree
import gdata.spreadsheet.service
import gdata.service
import atom.service
import gdata.spreadsheet
import atom
import string

def main():
 gd_client = gdata.spreadsheet.service.SpreadsheetsService()
 gd_client.email = '______________@gmail.com'
 gd_client.password = '________'
 gd_client.source = 'SpreadSheet data source'
 gd_client.ProgrammaticLogin()

 print 'List of spreadsheets'
 feed = gd_client.GetSpreadsheetsFeed()
 PrintFeed(feed)

 key = feed.entry[string.atoi('0')].id.text.rsplit('/', 1)[1]

 print 'Worksheets for spreadsheet 0'
 feed = gd_client.GetWorksheetsFeed(key)
 PrintFeed(feed)

 key_w = feed.entry[string.atoi('0')].id.text.rsplit('/', 1)[1]

 print 'Contents of worksheet'
 feed = gd_client.GetListFeed(key, key_w)
 PrintFeed(feed)

 return

def PrintFeed(feed):
 for i, entry in enumerate(feed.entry):
     if isinstance(feed, gdata.spreadsheet.SpreadsheetsCellsFeed):
  print 'Cells Feed: %s %s\n' % (entry.title.text, entry.content.text)
     elif isinstance(feed, gdata.spreadsheet.SpreadsheetsListFeed):
  print 'List Feed: %s %s %s' % (i, entry.title.text, entry.content.text)
  print ' Contents:'
  for key in entry.custom:
      print '  %s: %s' % (key, entry.custom[key].text)
      print '\n',
     else:
  print 'Other Feed: %s. %s\n' % (i, entry.title.text)


if __name__ == "__main__":
    main()