{"id":618,"date":"2008-10-16T23:37:55","date_gmt":"2008-10-17T06:37:55","guid":{"rendered":"http:\/\/multimedia.cx\/eggs\/sqlite-ultimate-serialization\/"},"modified":"2008-10-16T23:48:46","modified_gmt":"2008-10-17T06:48:46","slug":"sqlite-ultimate-serialization","status":"publish","type":"post","link":"https:\/\/multimedia.cx\/eggs\/sqlite-ultimate-serialization\/","title":{"rendered":"SQLite: The Ultimate Serialization Format?"},"content":{"rendered":"

When I started this FATE journey<\/a>, the easiest approach for getting build results from a build client up to the FATE server was to use the direct MySQL protocol:<\/p>\n


\n\"Direct
\n<\/center><\/p>\n

This is not an accepted way to do things in this day and age. It is more common to funnel the data via HTTP to the server. I resisted this at first because the direct database protocol method was working fine. But it seems that Python’s MySQLdb module is not portable to as many platforms as I would like to see FATE run on. At the same time, 8 months of FATE experience has shown the direct db protocol to be the weakest link of the whole endeavor. If connection is lost during the database operation, the whole script bails out with a Python exception and must be restarted manually. I realize that reading the MySQLdb-python documentation would probably allow me to deal gracefully with such failures and allow the script to continue. Another, more hackish solution would be to put the script in an infinite loop via the shell so that it would restart after a failure.<\/p>\n

Here’s the thing: So what if I could handle the failure gracefully from within the Python script? What do I do then? After the script performs a build\/test cycle, it is holding all the data in memory. So the script would just sit there holding the data until such time that it could access the database again. Meanwhile, more code needing testing is being checked into SVN. But the FATE script can only do one thing at a time.<\/p>\n

So that’s when I got this idea to store the build\/test data in a local database. What database? Would I have to establish a MySQL server on each build machine? Then I remembered hearing about this lighter-weight database called SQLite<\/a>, for which Python has built-in support via its standard library (v2.5+).<\/p>\n

So the idea was born: Store the build\/test data locally and send the data to the main database when convenient. But just how would that work? My first impulse was a multi-process solution:<\/p>\n


\n\"FATE
\n<\/center><\/p>\n

I decided it would be easier to organize everything in a single script. That implies a multi-threaded solution. But how to organize the process? Would the tester thread launch a logging thread? Vice versa? Perhaps a master thread to coordinate between the testing and logging operations?<\/p>\n


\n\"FATE
\n<\/center><\/p>\n

I should note that this thinking was predicated on the empirical data that indicated that the logging phase was painfully slow. Even though I optimized the logging process some months ago by eliminating a bunch of redundant data, it can still take an average of 15-20 seconds to log one build record and its 200 or so test result records. Building and testing does not require the network, while logging does not require the CPU, so parallelize these tasks. Or so I thought.<\/p>\n

Then there is the matter of getting the data to the server. This has always been a hangup for me when thinking about this HTTP transport notion. I envisioned I would have to HTTP-POST the various bits of data comprising a record to the server and hope that the server responded each time or else I would have to start the “transaction” all over again.<\/p>\n


\n\"FATE
\n<\/center><\/p>\n

It might also be possible to encode all the data as separate POST fields somehow and send it all up at once. I worried that as the database grew, it might take an inordinate amount of time for the PHP script to finish its processing. After all, doing the direct protocol approach, the process could easily take more than 20 seconds to log data. The web server enforces hard limits on script processing time.<\/p>\n

Then I had a revelation– SQLite, the missing serialization link.<\/strong><\/p>\n

How to send a build record and its test results? Create a new SQLite database file, store the build record and associated test results in it, and then send that file wholesale to the PHP script, which knows how to pull the data out and stuff it into the MySQL database.<\/p>\n

I modified the main tester script to drop its results into an SQLite database file. Then I prototyped a separate script to pull the records from the database and send them to the server. Then I built the PHP script to receive the data. I was stunned that the entire transaction time (pull data from SQLite db, drop it in temporary file, run an HMAC authentication on the file, Base64- and URL-encode the data, send it via HTTP, decode it, authenticate the file, pull the data out, and dump it into MySQL) took only 2-3 seconds!<\/strong><\/p>\n

This vastly simplified the script since I discarded the multi-thread\/process idea. This is the new model, and the way FATE currently operates:<\/p>\n


\n\"FATE
\n<\/center><\/p>\n

In this model, the build\/test script doesn’t retain any result data in memory– it all goes into the local SQLite db immediately. When a build\/test cycle is finished, it attempts to send the results to the FATE server. If something, anything<\/em>, goes wrong, no big deal, just proceed to the next build\/test cycle. At the end of that cycle, try to log the most recent data, as well as the set that is still hanging around from before.<\/p>\n

After coming up with this use case for SQLite, I realized that this lightweight database could serve perfectly well as the serialization format between 2 languages, such as PHP and Python. Problem: I maintain the FATE test specifications<\/a> via a PHP script. When I update specs, if I want them to be available to the FATE build clients, I have to use a Python script that accesses the MySQL database, fetches the test specs, and serializes the specs using Python’s pickle serialization. Then I have to manually upload that pickled file to the server. It would be nice if PHP would update the test spec file when a test spec is updated, but that requires a common serialization format between PHP and Python. And don’t even talk to me about XML.<\/a><\/p>\n

Hello SQLite<\/a>. Next TODO item is to revise the FATE system so that the test specs are transported via SQLite database rather than Python’s pickling format. Maybe other people can find good uses for the test spec file in such an accessible format.<\/p>\n","protected":false},"excerpt":{"rendered":"

When I started this FATE journey, the easiest approach for getting build results from a build client up to the FATE server was to use the direct MySQL protocol: This is not an accepted way to do things in this day and age. It is more common to funnel the data via HTTP to the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[101],"tags":[],"class_list":["post-618","post","type-post","status-publish","format-standard","hentry","category-fate-server"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/multimedia.cx\/eggs\/wp-json\/wp\/v2\/posts\/618","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/multimedia.cx\/eggs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/multimedia.cx\/eggs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/multimedia.cx\/eggs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/multimedia.cx\/eggs\/wp-json\/wp\/v2\/comments?post=618"}],"version-history":[{"count":0,"href":"https:\/\/multimedia.cx\/eggs\/wp-json\/wp\/v2\/posts\/618\/revisions"}],"wp:attachment":[{"href":"https:\/\/multimedia.cx\/eggs\/wp-json\/wp\/v2\/media?parent=618"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/multimedia.cx\/eggs\/wp-json\/wp\/v2\/categories?post=618"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/multimedia.cx\/eggs\/wp-json\/wp\/v2\/tags?post=618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}