Everything is searchable

Interesting things happening while I wasn't watching. Not only the previously mentioned Data.gov, or the newspapers moving in the same direction, but also ...

• Freebase
• Wolfram Alpha Data

I'm really curious what effect will this have on databases. In theory, with the right authentication in place, everything could be exposed online and EDI would be vastly simplified, from sneakernet to HL7, everything would be replaced by REST calls.

At any rate, Freebase's attempt to organize everything is ambitious/stunning.

Wikipedia's own API, here.

Related: Talis.

Data.gov

US Government's open data initiative. Interesting, have to find out more about what's there. The potential for mashups and visualizations is great... if the data is trusted and current.
Here is the equivalent UK site.
I need to look into this some more, for now a lot of the data seems to be Excel files that can be downloaded. No universal REST/JSON access?

Oracle objects vs Cache objects in .NET

Oracle Objects vs Cache Objects

Interface engines

Unbeknownst to myself, in a previous job I wrote an interface engine. It was a (initially) simple VB application which was reading files from a directory and copying them to another directory, then deleting them from the source. Then more source directories were added. Then, file endings had to determine the destination. Then, file contents (i.e., header or template-based triggers) had to determine which files were copied and which not. Finally, database tables became source/destinations. The thing had grown immensely since the beginning and it was running 24/7, with a 30 second polling interval, and a large number of customers were relying in fact on a rickety PC running a hopelessly spaghetti-coded application with manually-installed DLLs. If only they knew.

I did not know at the time that this was an interface engine. Somewhat restricted to healthcare and financial IT, one wonders why this concept hasn't taken off in mainstream computing more significantly. I.e., in databases, interfaces could provide a strong and pervasive (cross-platform, even) replication/synchronization mechanism.

Or perhaps the concept has been folded into workflow engines and transaction-based orchestration systems; Biztalk or Oracle Fusion can presumably do what interface engines do, and much more – but at a hefty footprint.

Mirthcorp's Java-based interface engine, below, running against a Cache database over JDBC/ODBC.

Message passing:

Channel definition:

A web-based interface engine? Perhaps using technology similar to YQL. Monitoring a queue of some kind and updating your Facebook status? Have to think about this one.

Cache dates

Cache's multiple interfaces (RDMBS, OO) can cause confusion even for simple operations, such as inserting dates. Here is how that is done:

In SQL:

insert into CM.StrmClass(PatientAddress, PatientName, PatientDateOfBirth)

values('5 Soi Rangnam Bangkok, Thailand', 'TG', {d '1990-03-17'})

Rather ugly ODBC formatting for the date field. In Object Script:

Set p.PatientDateOfBirth = $ZDateh("12/21/1977")

By the way, if a %Save fails (does not make the results commit to the database), then there is a problem with the data validation. Running the container from the Terminal should show the result (e.g. W p.%Save())

---

Another interesting feature of Cache, which accounts for its performance: 'Using a unique feature known as “subscript mapping,” you can specify how the data within one or more arrays is mapped to a physical database file. Such mapping is a database administration task and requires no change to class/table definitions or application logic. Moreover, mapping can be done within a specific sparse array; you can map one range of values to one physical location while mapping another to another file, disk drive, or even to another database server. This makes it possible to reconfigure Caché applications (such as for scaling) with little effort.'

New media uselessness

I'm not a new media expert but even I know that…

• Calling an ad a 'welcome screen' is a pointless trickery. I understand you need to pay for content somehow, no need to sugarcoat it
  
• Sometimes, more media is bad: if there is an article that lists the top 10 cities to live, 14 most troubled real estate markets, 10 top moments in British TV history, etc, well, I take that as information. I can just read it, I don't need photos to go with it, or if there are photos, they should be optional. Making me scroll through 10 or 14 screens, each heavy on images but low on info, that will result in me simply leaving an otherwise potentially interesting article
  

So maybe the death of print is largely exaggerated.

Updating Cache from Excel

Excel (2007) is great as a quick database front end tool – reasonably versatile and easy to set up. This ease of use might make many want to be able to update a database from Excel, and that is not so easy.

It has to be done in code. One way would be through linking the Excel file to the database server (there are different mechanisms for doing this, e.g. OPENROWSET in SQL Server or the SQL Gateway in Cache). However, if you're running Office in 64-bit mode you soon discover that there is no 64-bit compliant Excel ODBC driver.

A quick and more natural solution is to embed the database update code in Excel itself; in the case of a Cache backend, there are two ways to write this code:

• Using ADO/ODBC
  

  Dim cm As Command
  

  Dim cn As Connection
  

  Dim cns As String
  

  
  

  cns = "DSN=CacheWeb User"
  

  
  

  Set cn = New Connection
  

  cn.Open cns
  

  
  

  Set cm = New Command
  

  cm.ActiveConnection = cn
  

  cm.CommandType = adCmdText
  

  cm.CommandText = "INSERT INTO Income(UserId, Income, Country) VALUES('@u', @i, '@c')"
  

  cm.Execute
  

  
  

• Using Cache ActiveX
  

A bit more interesting since it gets into some Cache internals:

Dim Factory As CacheObject.Factory

Set Factory = New CacheObject.Factory

If Factory.Connect("cn_iptcp:127.0.0.1[1972]:TESTDBNS") = False Then

MsgBox "Failed to connect"

Exit Function

End If

Dim Income As Object

Set Income = Factory.New("Income")

Income.UserId = "2"

Income.Income = 120

Income.country = "PAK"

Income.sys_Save

Income.sys_Close

Set Income = Nothing

HealthSpace

It seems there is a government-run GoogleHealth-like service in the UK.



I am still miffed by the resistance people show to this type of project. After all, they bank online. Why not keep track of their health info online?

Google App Engine and Python

Quick steps to develop with Google App Engine:

- download the SDK
- this will place a Google App Launcher shortcut on your desktop
- click on it
- File > Create New Application and choose the directory (a subdirectory with the app name will be created there)
- Edit and change the name of the main *.py file you'll be using (say, myapp.py)
- create your myapp.py file and save it in the subdirectory created 2 steps ago
- select the app in the Google App Launcher main window and click Run
- open a browser: http://localhost:808x (this is the default value, check in your application settings as defined in Google App Launcher)
- then you have to deploy it to your applications in your Google profile

More, later, this is just a quick vade mecum.

MySQL errno 150

InnoDb needs an index to exist on the column referenced by a FKey. Interesting, I have to check if this is a requirement of the relational model, I don't remember seeing that before although I seem to recall that this might be one of those auto/hidden indexes created by SQL Server (__________ix#122200_____________, etc).

Interesting SQL

select * from #t1

can also be written as....

select * from #t1 t1 left join #t2 t2
on 1 = 2

While:
select * from #t1 t1 left join #t2 t2
on 1 = 1
... is really a cross join.

This is acceptable:

select * from
(select 1 a, 2 b, 3 c) t1
full outer join
(select 2 a, 4 b, 5 c) t2
on t1.a = t2.a

Perhaps not very useful, but interesting to know nonetheless as they are available. And, I really do like (T-SQL's new) MERGE statement.

Google App Engine

The Python dev environment. A very basic sample app that comes with the SDK - and which I managed to deploy without a lot of headache (and without having read the documentation!).

Python confusion

...at least for a newbie.

• lists: L = ['a', 'b', 'this is another element', 1, [1, 2, 3]]. Can do a L.append(), len(L), L.pop(), etc
  
• tuples: T = 1, 2, 3, 4, 'this is a tuple element'. Or T1 = () for an empty tuple, T2= 'one element tuple', . None of the functions listed above apply.
  
• sets: S = {1, 2, 3, 'set element'}. The items must be unique and set functions are available. S = set(L) converts list to set (removes duplicates in the process).
  

Quick note on ORM

So it would appear that Cache uses the active record ORM. But combined with a unit of work for related objects (swishing, I believe they call it). I really need the time to look at Cache, the Entity Framework, and Python's ORM in more detail. Interesting stuff.

Cloud, AIR, GoogleHealth

My new article is online at developer.com. Wish I would have added a few more images though. What to write about next?

Wither SQL?

More on the rise of non-relational databases. Maybe it is time for me to do another 'strategy' post... something to tie Cache, CouchDb and all the others together.

Under the, err... hood?

Just love it when unsuspecting users are exposed to the gory details of their favorite web sites.

CouchDb

Yet another thing to look at.

More on ORM; optimization?

Thinking about Cache and its 'native' objects, I wonder if when those are persisted to disk, only the data is actually saved, or any functions as well - i.e., p-Code compiled ObjectScript or Cache Basic member functions. Or perhaps even (class-query) SQL. All this code could be optimized for the given state (properties) of the object.

Then the question becomes, where is this data saved - perhaps in some raw extensions of the sparse arrays that hold the object member data.

Another interesting aspect (related to the sparse array storage system) is the kind of optimization, if any, that occurs at the SQL relational engine level. If there is optimization of any kind done at the I/O-sparse array level, this might conflict with the SQL optimization. Interesting stuff.

Which brings into question, is the optimization cottage industry a by product of the relational model? I have always found Oracle's optimization 'strategies' (the thick books dealing with that) somewhat ludicrous and antiquated. In order to do that really well, you need a deep understanding both of data and of sorting algorithms; with so many intervening layers (physical design, I/O patterns), even that understanding is corrupted. So if you can avoid a couple of grievous errors (e.g. multiple joins on non-indexed columns), you will do reasonably well. But then, the DBMS should be able to detect if you're about to make a grievous error (or perhaps the reporting tool, if you use one). So, why a thick book on optimization?

AIR and GoogleHealth

Finally, I completed this project. Write up coming soon - in the meantime, here is the Javascript/AIR code amalgamation. Briefly, this is a client for GoogleHealth written in Adobe AIR/Javascript; it lets you query a GH profile and update it (via the Atom protocol). GH documentation is spotty and occasionally incorrect, so this wasn't as pain-free as it should. Neither is the code production-ready or elegant. It is just a prototype - a working one.

The code requires a (sqlite) database, and of course the HTML forms. However, the most important functionality is encapsulated in the file, so that should be enough for a quick start.

It's all, of course, ensconced in Subversion....