APIs as a Service

Over the last couple of months I've been investing a good amount of time into research and planning for my upcoming push into self-employment. Some common concerns involve questions like "How much money needs to be earned every month?" and "What are the expected monthly operational costs?", but the biggest question has always been "What can I offer that people actually want?". A couple of projects have been outlined on this site in the past, however it seems that one of the most common solutions that I am providing to clients has been absent from consideration: APIs.

Modern C++

When I sit and think about it, I build an awful lot of APIs. Here are some of the more interesting solutions that I've built at the day job or for clients in the last two years:

  • library management
  • pedagogy and digital textbooks
  • online skills training
  • Proprietary CMS ⇢ Salesforce sync middleware
  • Proprietary LMS ⇢ Moodle sync middleware
  • Weather station data collection (for IoT devices distributed across forests in Japan and Thailand)
  • ISBN book catalogue (with an emphasis on textbooks and other learning materials)
  • Email ⇢ Evernote sync middleware
  • restaurant inventory management
  • and many tiny, single-function resources

This seems to be where I'm investing a great deal of my time, building tools that interact with systems, but are generally not seen1. Is this not the area that I should be looking into developing and expanding?

One of the reasons I make these APIs is because the reasonably-priced options that already exist in the world have glaring issues with consistency, or completeness, or are simply far too complicated to be used within the imposed request limitations. As for the unreasonably-priced APIs … I don't get to play with those very often as they're generally cost prohibitive, even for larger corporations.

There are two APIs that I've been thinking about building to solve some of the data organization problems around podcasts and RSS feeds, which are very similar in many respects. Thinking through the sorts of tools that people might actually want, this would make more sense than creating yet another MySQL client.

This is something I need to seriously consider this weekend.

  1. I do occasionally build things that have a web interface, but these UIs are more functional than beautiful. Fortunately there is always ThemeForest to help meet a design deadline for under $20.

A Missing Piece

Something has been bothering me with the structure of the 10Centuries data model recently and I've been unable to identify exactly what the problem was until I was on the train home from work yesterday when, in a flash of insight, things became clear. The underlying problem stems from a core limitation in 10Centuries with regards to accounts and how they're used in the content publishing mechanism: every account is treated as an identity, and every identity is essentially an account. If a person wants to publish posts on one website as "Tom" and on another website as "Jerry", they can easily use the same account. The problem is that when someone looks at the blog post via the RESTful API, they'll see that Tom and Jerry are both the same account but with a different author name.

This is silly, and it's something I've also blindly dealt with over the years writing the occasional post under an alias on various sites all the while using this very account. To make the matter even more blindingly obvious, publishing a social post alongside a blog post or podcast would come from the origin account, not an alias. So what exactly was the point of allowing aliases in the first place?

But this line of thinking is how the missing piece fell into place …

Missing Piece.jpg

What 10Centuries could really use are "personas". A single account could have many personas, but a persona would belong to only one account. This would allow a person to have what appears to be multiple accounts on here that are all available through a single sign-in. But then comes the obvious question: why would anybody want to do this?

With the dwindling activity on 10C — across all functions, not just social — I've been looking at ways to potentially resolve the "dead timeline" problem that people can face when they look at one of the social apps or sites. A lot of people have micro.blog accounts, and that project appears to be "open" enough that it's possible to build the necessary interfaces to play nicely with the network. Making 10C an "easy" way for people to use their micro.blog account seems like a natural fit. People would see posts from that network in their timeline, and they'd also see locally-created posts as well. The system would be smart enough to know where to send the response, posts too long for a single 280-character object on micro.blog could be presented as a partial with a link back to the full message in a proper stream view on a person's personal 10C site. Of course, people who do not wish to use micro.blog wouldn't have to. They could continue to use the system however they choose and everything would continue to work as expected. As working with micro.blog would require the full adoption of microformats, 10C sites would also — finally — adapt to play nice in the IndieWeb space.

Why the multiple personas, though? Separation of identities, really. Not everyone may want their micro.blog account tied to their main 10C account, and not everyone would have just one account to link to. It makes sense to allow multiples for personal, professional, creative, and silly accounts to exist all within one account, while appearing as multiple personas with varying degrees of data visibility via the API.

This is more or less a logical progression so far … right? I certainly hope so.

But wait. IndieWeb is an alternative to the "corporate web" and its silos. While 10C is most certainly not a corporate entity, it is still very much a silo. So this means in order to make this idea ideologically complete, I need to share the source code for 10C with the world.

Okay. I can do that. Version 5 is still very much slated to be the 100% open version of 10C that anyone and everyone can download, install, use — and abuse — to their heart's content, ideally finding issues and contributing back to the community whenever possible. This just means that Version 5 needs to have files to share.

Another Ambitious Goal?

As I explained recently, the last little bit at the day job has been an absolute slog. I'm not excited to go to work and solve problems there anymore. I'm dreading the inbox with every glance at the little mail icon. I'm just not much into the corporate tools right now. What I'd like to do is focus a bit on making something that I can be proud of, even for a little while. A basic version of 10C v5 might just pull me out of the doldrums and get the brain firing on an extra cylinder or two.

But this means I need to put a bit more thought into the data model and how everything will be structured. With 10Cv4 I started playing with the idea of "channels" and how every object was a self-contained entity that resided in a channel that could be accessed via a site or a pipe. While this model did solve some interesting problems, there's still room for improvement. The same can be said about the account structures, which still use the terrible term user and will be changed as soon as is feasible. It's already been decided that accounts can have personas, and accounts can own channels and be granted permissions to them. But what else can be improved about accounts and the model? Are the ToDo and Notes entities as effective as they could be? How about the Photo entities? There is a lot to think about, but not all at once.

What I'm going to aim to do is release a proof-of-concept of a Version 5 implementation in the coming weeks. It will not be too ambitious at first, instead being a simple little tool that will contain an API, a web presentation layer, files, and the start of a self-hosted platform with a few ideas I've been toying around with for 10Cv4 that would probably make more sense to ship with a more modern API. If the project is something people see potential in, then maybe there will be a few people contributing to the project and building something much better than a single person could hope to accomplish. I won't hold my hopes out, though.

There's a lot that I still don't understand about microformats and the IndieWeb project, and there are things in both projects that break my brain as I try to parse the logic behind decisions without knowing the context. That said, it's one of the few ways forward for people who truly care about owning their data and for the 10Centuries platform that I've invested so much time and effort into building.

Let's see if this concept of personas is the missing piece that will make 10C a little more interesting.

Renaming and Rejuvenating

Over the last few years the name of the software powering the 10Centuries service was, for lack of a better name, 10Centuries. While this works fine for services that operate in isolation, it can make for a bit of confusion when talking about something. The next version of the software will be doing something I've wanted to offer for years with 10C and allowing people to host their own instance of the service. In order to keep things simple, I've decided to rename the software starting with the next version simply to v5.

For people who have been using or watching the development of the current site, the first question will undoubtedly be something like "what about all the half-written features in the existing software?" A valid question, too. I plan on completing a few more items for the current platform before putting more attention into v5. Photos and ToDos have some more updates coming, as does the long overdue Comments API, which is functional but has not yet been implemented across the various site themes. With these three key areas complete, it will then become possible to focus on the next version of the software while also seeing whether the newer updates are remotely popular.

One difference with how v5 will be developed compared to other projects I've worked on involves documentation. This is an area that I've been historically weak at, often writing a few pages only after being rightfully pestered by people who are showing an interest in the system. With v5 I plan on writing and updating the documentation with each and every commit. This will hopefully keep everything better aligned and up to date. More than this, the documentation will be part of the software package. This will make it possible for people to read about the tool while offline. A small thing, perhaps, but completely doable given the files will all be plaintext with standard Markdown formatting.

What's Going In v5?

v5 will have a number of features that I've not seen anywhere else. People will naturally be able to publish blog posts, podcasts, social streams, and other basic things that many publishing tools can already do, but it will also allow for automatic backups across servers. This can be to other servers that a person operates, servers operated by people they trust, or to the main 10Centuries service itself. By doing this, a person can safeguard their data from being lost forever if a server goes offline. The feature will be an opt-in service, of course, and encryption will be used at every level to ensure only authorized access to the data.

This distribution mechanism is also how the social feeds will be managed. Rather than have a social client that subscribes to different feeds like an RSS mechanism, the posts will be read into the same server pools, and distributed this way. People will still have the option to selectively subscribe to accounts on different services, of course. The pool would simply allow for a global timeline to exist.

Some other nice features that will go into v5 include:

  • IndieWeb support out of the box
  • JSONFeed support out of the box
  • Archive.org support out of the box
  • Podcast download stats
  • Simpler website templates
  • Bi-directional sync with other social networks
  • and quite a bit more


Why not? I've looked around at a number of the open options for a lot of the core and bonus features I'd like to see in a package like v5 and found some great work hidden behind incredibly complex configurations. v5 is going to me my attempt to simplify this stuff as much as possible. Software is still too complicated in 2017. I think we can do better.

Development of v5 will happen in the open via GitHub, and people are encouraged to participate if they choose. While it will be impossible to appease everybody, it shouldn't be improbably to satisfy many.

Can I Not Have Drafts When Networks Change?

I thought that I could change networks because, seriously, who cares? I wonder if this is not actually the case …

Now here we go. This is working the way I would like to see. What is the best way to have the content page load properly, though? Perhaps the best option would be to have the post guid appear in the URL, which is then used to load the content. That said, I don't have a _getPostByGUID() function in the Content class.

The Simple Things Get Lost ...

When LinkedIn was hacked a few weeks back, I responded like many others and changed the password for that site. No two websites use the same password, and none of them can be realistically memorized without entering them several dozen times, but this didn't stop me from looking over some of the other passwords that are employed and making sure they're up to snuff. All in all, 3 passwords were changed that day and I went to sleep feeling better that all of the unintelligible strings that I use to secure various accounts across the web cannot be broken without a long-running brute force attack1. But there was something I was forgetting …

A few days ago I noticed that I wasn't receiving any messages from any of my websites. Dematigo Software, and this blog were completely silent aside from the occasional comment. While this might be due to the lack of compelling content and side-work, it's unnatural for the spam messages to also stop completely. That's when I remembered something: I hadn't updated the Noteworthy API with the new GMail password.


After a quick update, comments were once again coming through from the main sites. It's funny how some of the simplest things can completely slip our mind from time to time.

Yet Another Update

Today is a pretty big day for NoteWorthy as updates to the API, front-end parser, and a new theme have all been rolled out. Listing all the things that have changed between today's versions and the last would take quite a bit of time but one of the more important items is the introduction of a MySQL component. In previous posts on the subject I said that having a database component was superfluous1, but something new is afoot.

Yet Another aaS …

Over the last few weeks I've been thinking a great deal about the future of NoteWorthy and how it is going to work in the future. The software is broken into two parts; an API and a presentation layer. These two system work together to show the appropriate information when a person visits the site, and there is a great deal of flexibility with regards to how a site presents information.

Here's how it works:

  1. a person visits the site
  2. the web presenter determines what content the visitor would like to see based on the URL and a query string
  3. the presenter checks the cache for the requested information
  4. if the cached version is too old, it's discarded
  5. if the cached version doesn't exist, it's requested from the API
  6. the data is parsed and saved locally, then presented to the visitor

On a good day, all of this happens in less than a second. On a bad day, it can take as long as 1.4 seconds2. Depending on how quickly the NoteWorthy API can respond to requests for data, caching may not even be necessary for less-trafficked sites. Because of this, the option to cache data can be enabled or disabled on an object-level basis. Want to cache everything but Tweets? No problem. Want only the archive elements cached, with pages, posts, and everything else coming in live? Again, no problem. Who am I to tell people how to run their web site?

This does raise an interesting feature, though. If the API is improved for speed and can serve up a sites data darn near instantaneously, would people want to have the whole system installed on their server?

In an ideal situation, the NoteWorthy API would run on a completely separate server from the presentation layer. A person could have three or more heavy-traffic sites calling data from a single (or clustered) API server and presenting data that way. These servers wouldn't even need to be in the same area. More to the point: NoteWorthy could act as a Content-as-a-Service system, allowing people to host their own site on any server they choose, but the database and back-end complexity are completely offloaded. Not only would this make a Celeron-based desktop PC from 2002 a viable web server, but it would simplify content delivery to such an extent that WordPress would look ridiculously over-complicated by comparison3.

What can be stored in the NoteWorthy system? Whatever you want. Blog posts. Pages. Images. Movies. Tweets. Facebook statuses. EverNote records. DropBox files. There really is no limit. The problem, of course, would be the consignment of your data to a 3rd party.

Would anyone use this type of service and pay for it, though? That's the ultimate question that needs to be answered. High-availability databases, APIs, and CDNs aren't cheap. NoteWorthy could potentially change that.

Enterprise APIs

At the tail end of last year Anant Jhingran wrote a quick little article with six predictions for APIs in 2012 and beyond. The predictions he made are ones that the API development community has been pushing hard to make a reality for the last few years and includes a bit of a leap of faith that settings standards in the way we build and deploy APIs will become more important in the future. As someone who regularly analyzes huge sets of data to make useful information for people1, there were a couple of items that I found of particular interest.

The first was the hope that we might have more data APIs like Siri and WolframAlpha in the near future, enabling people to exchange data between applications and services much easier. The people who regularly work with importing and exporting of large data sets have probably been looking forward to this for years and, although tools have certainly gotten better since the early days of connecting Microsoft Excel to a SQL database to import a massive set of data to build pivot tables from, we can still do better. A lot of people have accused me of jumping from Windows to Mac because I'm a fanboy, but I honestly couldn't care less what platform I'm using to get work done. Windows. OS X. iOS. Android. Ubuntu. These things are just tools that enable us to accomplish tasks. Some are better at some types of work than others. But it's the data we work with that is the most valuable component to any task.

It's highly unlikely that we'll see a plethora of single companies creating their own API from scratch2. Instead we will undoubtedly see APIs adhere to standards such as OData which will allow organizations to easily share APIs with customers and vendors without needing to include massive How To manuals full of stale information.

Which brings me to the second part of the article that I am hoping to see: Enterprise APIs.

I've worked for several large organizations where an API that applications, customers, vendors, and internal power users would have solved a lot of problems. Heck, you can look at my resume and see that each and every company save two could have used an API that played well with others. That said, enterprises are slow and resistant to change. Hopefully this will start to change in the near future. It may not seem likely, but I'm quite confident it will catch on as organizations try to satisfy their customers and vendors.

What's in it for a big company to build an API that others can connect to? Analytics, of course. With more people accessing the API for data, companies will get to see what data customers regularly access. This allows the organization to (potentially) make better decisions regarding the future direction of the company and how to better service the people who contribute to the bottom line.

At the end of the day, building an API means setting a standard. A standard that we have to support and maintain for years. The product will naturally evolve, but the core elements will remain active for a long, long time. Almost every project that I've worked on over the last two years has had an API element to it, including Noteworthy3. Hopefully more organizations will follow this trend going forward in order to open the doors to greater data transparency, and greater task accuracy.

Where Are the Google Reader Alternatives?

Several months back there was a lot of shouting online after Google updated their Reader application to use the Google+ design scheme. People were angry that they could no longer share posts with other online networks besides Google+, and a lot of people1 were not happy with the layout as there was way too much wasted space and the website was no longer mobile-friendly. All of the complaints that were made last November still stand today but, oddly enough, there have been no new web-based RSS readers or even an API that people could use as an alternative to Google's popular syndication service. Why is this? Are angry tech people who take to blogs, Twitter, and other digital soap boxes all talk and no action?

It certainly seems this way.

Back in November I built an extension into a PHP-based API project I had been working on at the time2 that would replicate many of the core features that people have come to rely on with the Google Reader service. All in all the work required less than a week's worth of spare time and has worked well enough for the most part. Creating an RSS reader API can be tricky, particularly when it comes to the problem of dealing with duplicate posts with different content ids (I'm looking squarely at you, .NET Blogger), but this is by no means an impossible project. If anything, building something that doesn't rely on Google Reader can be more of an advantage than disadvantage.

Which raises an interesting question: with RSS having been declared dead numerous times by numerous people, and with the ever-dwindling user-base that enjoys all of the benefits RSS has to offer, what sort of tool can make people not only want to use Really Simple Syndication again, but pay for an application that makes the experience so much more enjoyable?

The question of profitability in this area is something that I've been considering time and again as I would love nothing more than to offer a simple and easy-to-use system that allows people to get their daily fix of feeds without pestering them with ads and all the other distractions that makes Google Reader seem more and more like an AOL property than the brain child of a technical genius in Santa Clara. Some software tools, such as Flipboard, have innovated like crazy in this field and offered people a more interactive experience with a limited number of feeds but, being the minimalist geek I am, I'd like to see something different.

So with all the existing RSS readers out there, what could a new one possibly offer that would add any value to be worth a 230円 or higher sticker price? The ability to comment on posts directly from the application? The ability to read things in "offline mode"? The ability to share posts on pretty much any social network at the touch of a button? The ability to view RSS feeds through an interesting and interactive magazine layout? All of these things are already being done by different applications. What will make a new one any better?

And this has been the crux of the problem I've faced while slowly designing a new smartphone/tablet RSS reader and refining the mostly complete API. With so many options out there, how does one even start to get their foot in the door?

Creating a Simple REST API With VB, PHP and MySQL

Visual Studio & MySQLThe internet is an interesting place.  A few weeks ago I had posted an article showing people how to make Crystal Reports play nice with SQL Server Compact Edition.  Since then, I've had a few people contact me with questions about how to use SQL Server CE as well as how to make Crystal Reports access other data sources.  One question seemed to come up more often than any other, though:  How do I get VB to access a MySQL Database hosted somewhere on the internet without granting a user direct access to that database?

It's a pretty good question, and the solution is one that I've had to employ several times over the last few years.

Disclaimer: As with any other solution you would find on the internet, this is not a "one size fits all" answer.  The code that I am going to show has been overly simplified and should be used as a starting point to your solution.  That said, if you have any questions about the code, I'll do my best to answer them.

How It Works

The idea behind this solution is rather simple.  Pass some information to a PHP file on a server using a WebRequest in VB, and build a DataTable dynamically using the XML data returned from the web.  There are no security checks built into any of this code, but that can be added later.

So, with all that stuff said, let's take a look at the PHP code that we'll be using.

<?php $rptID = $_POST['reportid'];

$toDate = $_POST['todate'];//Connect to the database

$db = mysql_connect("localhost","your_username","your_password");

mysql_select_db("your_database");//Query the Database

$res = mysql_query("SELECT rpt.id, rpt.LastName, rpt.FirstName, rpt.Whatever WHERE id = " . $rptID );// Start the XML Document

echo "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>";

echo "<Report>";

//If information exists, write it out as XML

If($rs = mysql_fetch_row($res)) {

do {

echo "<Detail>";

echo "<id>".$rs[0]."</Code>";

echo "<LastName>".$rs[1]."</LastName>";

echo "<FirstName>".$rs[2]."</FirstName>";

echo "<Whatever>".$rs[3]."</Whatever>";

echo "</Detail>";

} while($rs = mysql_fetch_row($res));



echo "</Report>";


As you can see, this code is as plain as it gets.  We have two variables, a MySQL Query, and the result set is returned in XML format.  Of course you'll probably want to change the MySQL query to something that'll work on your own database….

The VB Code

The next thing we'll look at is the code in VB.net.  The example here is using VS2005 with the .NET 2.0 Framework.  It's nothing fancy, and it should be forward compatible right up to the 4.0 Framework due out with VS2010.

The first thing we'll do is create a super simple class that will access the PHP file.

Imports System

Imports System.Net

Imports System.Text

Imports System.XmlPublic Class clsAPI#Region " Class Variables "

Private xURL As String ' URL to Call

Private xPostData As Object ' Post Data to send to API

Private xResp As String ' Response from PHP API

#End Region#Region " Properties "

Friend Property URL() As String

'Target URL


Return xURL

End Get

Set(ByVal Value As String)

xURL = NoNull(Value)

End Set

End PropertyFriend Property PostData() As Object


Return xPostData

End Get

Set(ByVal Value As Object)

xPostData = Value

End Set

End PropertyFriend ReadOnly Property XMLResponse() As String


Return xResp

End Get

End Property

#End Region#Region " Main API Code "

Public Sub Connect()

'Create a new WebRequest object with the specified URL

Dim rq As WebRequest = WebRequest.Create(URL)'Set Method and Header information

With rq

.Method = "POST"

.ContentType = "application/x-www-form-urlencoded"

End With

'Send the data to the php page

SendData(rq, PostData)

'Assign the response from the php page to the variable for XMLResponse

xResp = GetXMLResponse(rq)

End Sub

Private Sub SendData(ByRef req As WebRequest, ByVal data As String)

'Store the post data as a byte array for passing to the page

Dim b As Byte() = Encoding.ASCII.GetBytes(data)

req.ContentLength = b.Length

Dim sr As Stream = req.GetRequestStream()

sr.Write(b, 0, b.Length)


End Sub

Private Function GetXMLResponse(ByRef req As WebRequest) As String

Dim rs As WebResponse = req.GetResponse()

Dim sr As Stream = rs.GetResponseStream()

'Create StreamReader to Convert stream to text

Dim xr As StreamReader = New StreamReader(sr)

'Return the response as text

Return xr.ReadToEnd()

End Function

#End Region

End Class

There's not a whole heck of a lot to this class, so feel free to add all the properties, methods, and everything else that should be in here with a proper application.

The Other Functions…

From here, we need to add two simple functions to a form (or anywhere else) to access the class.

Friend Function doAPIQuery(ByVal tProcName As String, ByVal tPostQuery As String, ByVal ResultName As String) As DataTable

'Function Queries the Online Database and Returns a DataTable

Dim ds As New DataSet("ds")Try

Dim wbg As New clsAPI

With wbg

.PostData = tPostQuery

.URL = "http://www.whateveryoursiteis.com/" & tProcName

.Connect()'Construct the Columns from the XML Nodes

Dim dt As New DataTable

ds = CreateDataSetFromXML(.XMLResponse, ResultName)'Create a StringReader and Pass the XML Response through it

Dim sr As New StringReader(.XMLResponse)'Read in the XMLString to populate the table

ds.ReadXml(sr, XmlReadMode.Auto)

End WithCatch ex As Exception

MsgBox(ex.Message, MsgBoxStyle.Exclamation, "doAPIQuery Error")

End Try

'Return the DataTable

Return ds.Tables(0)

End Function

Private Function CreateDataSetFromXML(ByVal XMLData As String, ByVal ResultName As String) As DataSet

'Function Reads an XML Field and Constructs DataTable Columns based on the results

Dim xmlDoc As New XmlDocument

Dim xmlNodes As XmlNodeList

Dim xNode As XmlNode

Dim baseDataNodes As XmlNodeList

Dim ds As New DataSet("ds")




xmlNodes = xmlDoc.GetElementsByTagName(ResultName)

For Each xNode In xmlNodes

baseDataNodes = xNode.ChildNodes

'Create a Column for Each Node in the XML Result

For Each basedatanode As XmlNode In baseDataNodes

'If the Menu Item hasn't been added yet, do so now

If ds.Tables(0).Columns.IndexOf(NoNull(basedatanode.Name)) < 0 Then _

ds.Tables(0).Columns.Add(NoNull(basedatanode.Name), Type.GetType("System.String"))



Catch ex As Exception

MsgBox(ex.Message, MsgBoxStyle.Exclamation, "CreateDataSetFromXML Error")

End Try

'Return the DataTable

Return ds

End Function

And that's that. To have data returned from the PHP file that we created earlier we would need only to create a DataTable and populate it like so:

Dim dt as new DataTable("Result")

dt = doAPIQuery("whatever.php", "reportid=99&todate=2009-05-24", "Result")

The doAPIQuery function sends and receives data through the API class, and the CreateDataSetFromXML function reads that XML response and converts it into a DataTable that can be used anywhere in the application. Heck, the way this code is presented, there would be little problem adapting this if you wanted to create 100 different PHP files to do different functions and return different result sets.

Panel & GearsIs this the best way to have a VB-based application communicate with an online MySQL database?  Like everything else in the world, it depends.  Some would argue that this method is the lazy man's way out of doing the job right, while others would argue this method would be sufficient for small custom applications built for a small business.  At the end of the day, it's up to you to decide what the best solution is while designing an application.

That said, if you believe you have a better way of getting VB and MySQL to communicate without granting direct access to the database server over the web, I'd love to hear it.