Skip to content

All posts by moore - 44. page

Posting to REST APIs from mysql triggers

This was a fun crazy idea that turned out not to be so good in practice. If you have a mysql table you are monitoring for changes, you can use a trigger to do so (as long as you have a semi-modern version of msyql).

Sometimes you might want to notify another service of any change (remote logging service, message queue, etc). For instance, at 8z, when the price of a listing changes, this is an interesting event that other software should be notified of.

The first step is to install the mysql http UDF (all commands below are for centos).

$ sudo yum install curl-devel

$ CPPFLAGS="-I/usr/include/mysql"  ./configure --with-mysql=/etc/my.cnf   --enable-shared

$ make

This gives us a .la file (built with libtool) but luckily there’s a .so file hiding:

sudo cp .libs/mysql-udf-http.so.0.0.0 /usr/lib64/mysql/plugin/mysql-udf-http.so

Then, your remote rest service will probably want JSON, so you’ll want to visit the mysql udf hub. There lives a JSON function. I just grabbed the C file from github and compiled it: gcc -fPIC -shared -o lib_mysqludf_json.so -I/usr/include/mysql/  lib_mysqludf_json.c

After these are compiled you have to copy the .so files to the appropriate directory and then add functions as a privileged user, per usual UDF convention.

Now you can create a trigger that calls these functions.

delimiter $
CREATE TRIGGER upd_check BEFORE UPDATE ON account 
FOR EACH ROW 
  BEGIN 
    IF NEW.amount > 0 THEN 
      set @json = select json_object(account_id,amount) 
      select http_post('http://restservice.example.com/account/post',@json); 
    END IF; 
  END;$ 

delimiter;

Note that I didn’t actually implement this, so the code up above is based on my memory. If you try this and have some corrections, please leave them in the comments and I’ll update this post.

Why didn’t I actually implement this, after doing the better part of a day’s worth of research? The database is not the best place for this kind of logic. Error handling for triggers is weak–if the trigger failed for any reason (like the remote service was down), you would need to build an error logging system, or send an email. Also, if there are a number of updated rows, which all trigger outbound http calls, you might run into performance issues which would be difficult to replicate or analyze, and, most importantly, might impact your database’s ability to act as a database. The three tier architecture exists for a reason.

But, it was fun to investigate, and, as my colleague said, would have been cool if it had worked. If you are still interested, there’s more on this topic here.

Update Jan 2021: The HTTP UDF is now here and a reader pointed out they had trouble with the above instructions, but that a golang UDF implementation worked fine.

APIs MySQL | moore |

Running a brown bag lunch series in your office

Courtesy of smoothfluid
Courtesy of maxually

Brown bag lunches are great opportunities for employees to share their knowledge, learn new skills, and bring a small company together.  By ‘brown bag lunch’, I mean an internal presentation lasting about an hour, made by an employee on an interesting topic of their choice.  The name comes from everyone bringing their lunch to work on that day, rather than eating out.

8z has been doing them for over two years, and here are some lessons.

  • Schedule them monthly, and one mont at a time.  Don’t try to schedule out the whole year.
  • Have presenters spend as little time as possible building a powerpoint.  It’s hard to get away from them as a structural crutch, but they don’t really add a lot of value.
  • Bring in real business situations.  One of the most memorable presentations occurred when presenters analyzed a recorded call during the presenation.
  • Have someone be point and recruit people individually.  Don’t count on volunteers, especially at the beginning.
  • It’s OK to miss a month or two if other stuff is going on.  Hello December.
  • Record them if you can.  All you need is an ipad and a youtube account.
  • Technical presentations (like application architecture) are appreciated by the business folks.
  • Everyone has something to say.
  • You can have people repeat every six months or so.
  • Some people won’t want to speak.
  • Presenting in pairs can work.
  • Make sure the presenter leaves plenty of time for Q&A.  8z budgets an hour for the talk and Q&A.
  • Schedule it so founders/executives attend.  This makes a powerful statement and exposes them to direct ideas.
  • Be prepared to capture changes/feedback from the presentation.
  • The departmental cross pollination is a major benefit.
  • Consider themed potlucks (mexican, breakfast for lunch, etc) instead of brown bag lunches.

How do you spread knowledge within your small company?

8z Business Presentations | moore |

Building an automated postcard mailing system with Lob and Zapier

Courtesy of smoothfluid
Courtesy of smoothfluid

I was looking at automated paper mailing systems recently (and listed what I found), and was especially impressed with Lob, especially the ease of its API.

Among other printing services, Lob will let you mail a postcard with a custom PDF on both sides, or a custom PDF on one side and a text message on the other, anywhere in the USA for $0.94.  (Sorry, not sure about international postcards) The company for which I work sends out tens of thousands of postcards every quarter. The vendor which we use charges them a similar fee (less, but in the same ballpark) but there’s a manual process to deliver the collateral and no API. So an on-demand, one by one post card sending system is very interesting to me.

Note that I haven’t received the Lob postcard which I sent myself, so I can’t speak to quality. Yet.

The Lob API is a bit weird, because the request is form encoded rather than a JSON payload.  It also uses basic auth, but only the username, not the password. But the API seems to have all the pieces you’d need to generate all kinds of postcards–reminder postcards, direct mail postcards, photo postcards, etc.

After testing out the service via the web interface and cURL examples, I thought that it’d be fun to build a Zapier zap. In particular, being able to send a postcard for an entry in a Google spreadsheet seemed like a useful use case. Plus, Zapier is awesome, and I’d wanted to test out their integration environment for myself.

So, I built a Zapier integration for Lob, using the Zapier developer docs in combination with the Lob developer docs. It was actually easy. The most complicated step was translating the Zapier action data, which is a one or two dimensional array of typed data, into the Lob data format, which wanted a couple of text fields and two address arrays. Zapier has a scripting environment that let me modify data from APIs pre and post send, and even had an example about form encoded APIs. Zapier’s JavaScript scripting development environment was full featured, including syntax and error highlighting. It had no real debugging available, but I could use the venerable debug-by-log-statement method fairly easily.

Where could I take this next? Everywhere people use postcards in real life. The postcards depend on PDF files (see a sample), so if you are generating a custom postcard for each interaction things become more complex, but there are a few APIs (based on a 30 second google search, here and here) available for dynamic PDF generation. There are also limits on API call throughput, if I stuck to the Zapier integration–I could send at most 300 postcards a day, unless I managed multiple spreadsheets.

I see reminders of high value events (dentist, house maintenance, etc), contests and marketing as key opportunities for this type of service. And if I had a product where direct mail was a key component, using Lob directly would be worth serious consideration.

Regarding the Zap, I believe I cannot make this Zap available to anyone else. Since I’m not a representative of Lob, I couldn’t commit to maintaining this Zap, and Zapier doesn’t want to have any of their customers depending on an integration that could disappear or be unsupported at any time–a fair position.

If the Zapier or Lob folks want to take this integration and run with it, I’d be happy to share my code–just leave a comment. If anyone else is interested in being able to generate Lob postcards from a Google spreadsheet (or any other compatible API) via Zapier integration, let me know and I’ll see what I can do.

APIs BPA Business Javascript Useful Tools | moore |

Google Spreadsheet Custom Functions With Spreadsheet Based Configuration

The business for which I work, 8z Real Estate, runs on Google spreadsheets–they are everywhere, and are especially powerful when combined with Google Forms. (Except in the tech department–we use more specialized tools like wikis and bug trackers.)

Recently I was cleaning up one of these spreadsheets and making it more efficient. This spreadsheet was an ongoing list of items that should be charged to various real estate agents. There was a fairly clear set of rules (person A should be charged this for item B, but not for item C, and person D should not be charged for items E, but should for item F). The rules were also fairly constant–not a lot of change. While they were clear, there were some intricacies that had tripped up some folks. And since there was real money involved, there was often a lot of time expended to make sure the charges were correct.

For all these reasons, it made sense to automate the charge calculations. Given the data was already in a Google spreadsheet, I decided a custom function was the best way to do this. The custom function could read from a configuration tab in the same spreadsheet with a list of people and items which would represent the charge rules. In this way, if there was a new item, a new row could be added to the configuration spreadsheet without requiring any help from a developer.

I was able to write the functions fairly quickly, using QUnit for Google Apps Script. I can’t recommend using QUnit highly enough–developing in Google Apps Script combines the joys of javascript (with its … intrinsic difficulties) and a remote execution environment that can be tough to debug. So, unit test your Apps Script code!

The initial implementation pulled data from the configuration tab with each custom function call. This naive implementation worked fine up to a couple of hundred rows. However, eventually this caused Exceeded maximum execution time errors. I believe this is because when the spreadsheet was calculating all the charge values, it was accessing the configuration spreadsheet range hundreds of times a second.

My next step was to try to cache the configuration data. I used stringified JSON stored in the cache service. Unfortunately, this caused a different issue: Service invoked too many times in a short time: cacheService rateMax. Try Utilities.sleep(1000) between calls.

Third time is the charm: have the function return multiple values. The configuration data is only read once, as is the list of items and names. These are iterated and the charges for all of them are calculated and returned in a double array to fill entire columns. Using this technique avoided the above issues, but created another one. Adding new rows with new items wouldn’t update the charges columns. Apparently there is additional, ill documented caching of custom function values. Luckily StackOverflow had an explanation: spreadsheets “evaluate your [custom] functions only when a parameter changes.” The way I worked around this was to pass a parameter to the custom function of the number of non blank rows of the spreadsheet: =calculateCosts(counta(A:A)). When a new row is added, the custom function is re-evaluated.

In the end I have written unit tested code that works in the way the business wants to work (in Google Spreadsheets), that runs on someone else’s infrastructure (Google’s), that can be configured by non technical employees and that will increase the accuracy of the charge calculations. Wahoo!

Business Google Apps Javascript | moore |

Zapier is awesome!

APIs are sprouting up everywhere. This is great for developers (and for end users) because it allows all kinds of automation. However, there are times when the investment of writing code to connect two APIs is too much.

Why might writing code be too much?

  • The problem is still fluid and writing code will lock in a solution.
  • There are more pressing business problems to solve.
  • There are no engineering resources available, and/or no money to hire a dev.

If any of these reasons apply to a problem you are facing, consider Zapier (or a competitor like IFTTT). These services are much like Excel macros–they require less software engineering expertise but can leverage some of the power of programming to automate away work. I’m only going to write about Zapier, since that is the solution with which I am familiar.

Zapier runs the connections between each service (called ‘Zaps’) at regular intervals. Zaps are built using a web only interface that leverages the APIs in a manner that, while not completely intuitive, is thoroughly manageable by anyone who can sum up a column in Excel.

Here’s the class of problems for which Zapier is good:

  • Connecting two services for which Zapier has connectors–this list is quite extensive.
  • Syncing needs to happen no more than every 5 minutes (15 for the free account).
  • No processing of the data during the transfer is needed (except possibly omitting some fields)–you are simply moving data from one place to another–no Yahoo! Pipes like transformations are possible in transit.
  • One way sync is OK (though there are workarounds).
  • You don’t need to bulk load initial data via Zapier–you either can disregard initial data or load it in some other fashion.
  • You have a reasonable number of sources and sinks–each linked source and sink will take up one Zap.
  • You have to have valid accounts with each source and sink.

That’s a fair number of limitations, but even so there are a large number of common problems that are solvable by Zapier. Some examples:

  • Syncing a list of contacts from one source to another.
  • Taking a google form submission and adding a user to a mailing list.
  • Moving a row from one Google spreadsheet to another.
  • Taking an email and adding it to a database.
  • Adding a customer who you have just invoiced in QuickBooks to an email list.

These are all types of problems that can be done manually, but if frequency or scale increases, the process can run people ragged.

I want to call special attention to the email processing ability of Zapier. If you have a well-formatted email that you often receive that you want to further process, Zapier can parse it into interesting fields and send that data along to a sink like a Google Spreadsheet. Examples of well-formatted emails include order confirmations, contact us forms, and newsletter subscriptions.

I have found the Zapier support folks very responsive, whether that was troubleshooting an issue with Google docs, finding out how to pronounce the company name, or explaining why having 100 Zaps reading from one Google spreadsheet was a bad idea. Having responsive support staff reassures me, because once Zapier gets embedded into business processes, ripping it out is going to be very painful and a lot of work.

You can also write your own Zaps if you have custom APIs that you’d like to integrate with. I haven’t explored this much–it does seem like a developer centric task.

Zapier is not an all purpose tool nor a total replacement for developers, but it is definitely a great app to have in your toolbox. Take a look and see what little (or big) niggling problem that you haven’t had time to write code for (or, if you can’t write code, what you haven’t been able to get an engineer to write code for) it might solve.

APIs Useful Tools Web Applications | moore |

Observations on a Writing a Custom Report with Java, Quickbooks, Jasper Reports, Google Spreadsheets and Google Drive

Image courtesy of Sean MacEntee: https://flic.kr/p/9P4CwC
Image courtesy of Sean MacEntee

A recently released project is using java and spring to pull data from quickbooks, a mysql database and google spreadsheets, munging the data in various ways, and using jasper reports and jfreechart to generate a good looking report and a CSV of transactions that will give our brokers weekly updates on how they are doing compared to their goals for the year. I then upload it to Google Drive and send an email notifying each realtor that they have a new file.  It’s always nice to release useful software–feels like a new day dawns.

A few observations from this project:

  • The tech was interesting, but it was actually more interesting to see how the needs of tech drove the business to ‘tighten up’ their processes. Whether that was making sure there was one place to look for user type data, or defining exactly what constituted achieving a goal, or making sure that any new realtors who joined created business goals (and committed them to writing), the binary nature of software forced the business (or, more accurately, people in the business) to make decisions. Just another example of business process crystallization. This also meant deferring some software development. Where the business couldn’t answer a question definitively, rather than force them to do so, we chose to defer development.
  • I’m glad that jasper reports makes it so easy to generate PDFs–you basically create an XML file (I was unable to find a spec, but there were plentiful examples) and then put tokens for dynamic content. Then you compile the XML file, give it a map of said tokens and values (which can be text, numbers, dates or images), and then export the object to PDF. Note that I was not using Jasper in a typical way–reporting from large amounts of similar data via a data connection–because I wanted different PDFs for each user. Perhaps there was a way to do this elegantly, but I was just trying to get stuff done. Creating a grid in jasper was interesting, though.
  • JFreechart had a very weird issue where on stage the graph labels were bolded and italicized, but not on production. Since we make every effort to keep these two environments in sync and they were running exactly the same code, this was a mystery. Finally solved it when we discovered java was different (same version, different vendors: openjdk vs sun java). Had been running this way for years. Oops.
  • Interacting with google spreadsheets is great for the business but a pain in the butt for developers. It’s great for our business because it is extremely easy for someone who is not a programmer to create a ‘database’ that is versioned, backed up, almost always accessible and helps them ‘get stuff done’ in a measured way. It’s a pain for developers because it is a ‘database’ and not a database–no referential integrity or data typing. Also, google provides cell based access and row based access, forcing you to choose. And the java libraries are old. Beats excel though–at least it is accessible from a server. We ended up writing a library to wrap Google’s Java SDK to make some common operations easier.
  • Pushing to google drive is interesting–I alluded to this in my last post but you have to be ready for failure. I ended up using a BlockingQueue for this–I throw files (a data structure defining the file, actually) to be uploaded on the queue, then consumers executing in a different thread each take one off, try to upload it, and if it fails, put it back on. I considered using a third party durable queue like IronMQ, but thought it was overkill.
  • Using the Quickbooks SDK, with all the accounting data exposed, lets you build some pretty powerful graphs that are useful to the business. But the docs are jumbled, with a lot of them aimed at developers who are building integrations to sell to Quickbooks users. Support is OK for standard operations, but for things like renewing your token, you have to drop down to the REST API (see my SO question) This article does a good job of outlining the various projects but as a dev you’ll have to ignore certain sets of information–never fun when getting up to speed.
  • We do a lot of backend processing and spring and maven and a custom assembler that generates a tarball when using ‘maven install’ have been great. I also finally figured out how to work with maven to use ‘release:prepare’ and ‘release:perform’ for releasing libraries, as opposed to going my own way, and that has made things much much easier. Learn your tools, folks!
  • I’m once again astounded by the richness of the java library ecosystem. There doesn’t seem to be very much that I can think of doing that doesn’t have at least one, and probably three, java implementations.
8z APIs Business Cloud Computing Java Useful Tools | moore |

Multi threaded push to google drive with Java Executors

I’ve never really written threaded code in java. Sure, I took a java certification class long ago, and reviewed threading and probably wrote some toy code.

But it had always been one of the areas I was aware of but avoided. And it was easy to avoid–after all, I was often writing for a servlet container, and in that case you are supposed to rely on the platform (Tomcat, etc) to provide multi threading magic–you just have to stay in your servlet box. Other times, I was writing glue code where performance wasn’t an issue.

Recently, I was writing integration code that read from google spreadsheets (using the api) and quickbooks online (using the api) and did some calculations on the gathered data and created PDFs (using jfreechart and jasper reports). These PDFs then get pushed to certain folders on google drive.

I had run through some testing in my development and stage environments, and was ready to do a run in production. I started off the run and, after a few minutes, noticed that performance was not very good. About one report every 80 seconds or so.

Now, I’d run this process before I added google drive integration, and it had completed in a normal amount of time. So I suspected the google drive calls were the issue. When pushing the PDFs to google drive in my dev environment, I’d run into some connectivity issues, so as recommended I’d added a exponential backoff to my requests.

This meant that a lot of clock time was spent just waiting for the google services to recover.

So, an easily parallelizable task that spent a lot of time waiting? Seemed like a great place for multiple threads. I googled multithreading in java and found the Executor framework, which made running such tasks in parallel trivial. It even had futures and everything, and had been a standard part of the library since java 1.5.

I set up a thread pool of 15 threads, spent an hour or so rewriting and testing my file push code to fit the framework, and performance is much better. The push to google drive still takes about 20 seconds per report, but that’s a 4 fold increase in speed.

Next steps–use the spring abstraction layer to make the code more maintainable and clear.

Google Apps Java | moore |

What a pleasurable way to learn a language!

This site was recommended to me, and I have to say, it is a fun way to become more familiar with the syntax of a language. There’s the journey aspect:

things are not what they appear to be: nor are they otherwise
your path thus far [...X______________________________________________] 19/280

and the fact that when you see something you want to investigate further, you just write another unit test:

  def test_slicing_arrays
    array = [:peanut, :butter, :and, :jelly]

    assert_equal [:peanut], array[0,1]
    assert_equal [:peanut,:butter], array[0,2]
    assert_equal [:and,:jelly], array[2,2]
    assert_equal [:and,:jelly], array[2,20]
    assert_equal [], array[4,0]
    assert_equal [], array[3,0] # my addition
    assert_equal [], array[4,100]
    assert_equal nil, array[5,0]
  end

Now, running through these koans certainly isn’t going to make me a Ruby expert, but I will have passing familiarity with the language and be ready to use it on my next small project.

Apparently I’ve been living under a rock, because there appear to be koans projects for quite a few languages: java, haskell, erlang (cue whatsapp reference), and even bash. I was, however, unable to find a koans package for assembler.

Dynamic Languages Lifehack Testing Tips | moore |

What’s wrong with this announcement?

Do you see anything wrong with this announcement, which I received as an email attachment, of what looks to be a very interesting discussion of hunger, food and faith?

faithandfoodsummit flyer

There is no URL.

There is no URL!

Folks, if you are having any kind of gathering or event that you’d like to be shared online, please please provide a URL.

Benefits of a URL:

  • Easy to share
  • Can be updated if details change
  • Lives forever, so can be referenced in the future
  • Can hold vastly more information than a flyer

There are many easy ways to create a URL for your event.

  • Facebook
  • EventBrite
  • Google Sites
  • Weebly
  • wordpress.com
  • Google Forms

And I’m sure I’m missing dozens of other options.

Repeat after me: I am having an event, and my event deserves a URL.

Useful Tools | moore |

Build your capital

I was working on a post about how important it is to have a side project, but then read this post by patio11: “Don’t End the Week with Nothing”, which could be more accurately titled “Don’t End the Week with Nothing except your Paycheck”. Not that there is anything wrong with just having a paycheck, but Patrick’s point is that when you work on something you own, rather than something you are paid for, you can (in the right circumstances, with hard work and luck) get accumulating returns.

He did such a good job explaining how to move your career forward as a software developer (a superset of the topic I was covering with my “have a side project” post), that I wanted to call your attention to it. The whole article is worth reading, but here’s my favorite part:

Telling people you can do great work is easy: any idiot can do it, and many idiots do. Having people tell people you do great work is an improvement. It suffers because measuring individual productivity on a team effort is famously difficult, and people often have no particular reason to trust the representations of the people doing the endorsements.

This is one of the reasons I blog, it’s why I have spoken at several user’s groups, it is why I wrote a book, and it is why I have a side project.

Consulting Jobs | moore |