iA Writer as a CMS: Publishing directly from iA Writer to MySQL using PHP

iA Writer as a CMS: Publishing directly from iA Writer to MySQL using PHP

Published by Patrick Griffith on Oct 11, 2017

Holy crap I'm so happy right now. I love writing. But up until a few hours ago I hated the process. I hated the process so badly that sometimes I skipped writing to avoid it.

But I just eliminated the process!!!!! (note that I'm not a fan of exclamation points, so let that be proof of how excited I am)

That process has always looked something like this:

  1. Write (the part I enjoy) in Bear Writer. Or Ulysses or whatever.
  2. Copy/paste into WordPress.
  3. Find a small change that I want to make, and then make that change in Bear.
  4. Copy/paste into WordPress again.
  5. Repeat steps 3-4 a few dozen times.
  6. Accidentally make a change directly in WordPress instead of Bear, then struggle to remember which is more up-to-date.
  7. Read through the entirety of both posts to figure out which is more recent.
  8. Repeat steps 3-7 for all of time.

And that was all back when I used WordPress. Now that I use a custom CMS it's even worse.

I just want to freaking write. Why does that have to involve so many non-writing steps? Fortunately, as of today, it no longer does for me.

Note that I switched from Bear Writer to iA Writer to accomplish this. Bear is prettier, but it stores its data in a proprietary format that I don't know how to access. iA, on the other hand, stores its data in raw text files. Nice! Also, Bear is super buggy about exporting to HTML. I'm still using Bear for notes, because it's the best app in the world for that, but I'm far happier with iA for writing my blog posts.

Drumroll...

I've hacked together a solution that automatically syncs my MySQL database with my iA files whenever I make a change in iA. Never again do I have to logon to the CMS on my website. Because as of today iA is my CMS.

This is going to save me more time than I can begin to explain. How many times do you want to change a single word or fix a single typo on a post of yours? That happens to me all the time, and each of those times was a hassle. Not anymore.

The Post

You might be skeptical at this point. A post is more than just a text blob, after all. A post also has a title, a description, a status, some timestamps, a slug, an image, etc. So how the hell do I manage all of that without logging on to my CMS?

Settings.

At the top of each file that I want to be published, right below the H1, I include the following:

[settings]
	slug=
	title=
	long_title=
	status=
	description=
	image_url=
	published_time=
	modified_time=
	challenge_id=
	challenge_order=
[/settings]

Some of those things, like challenge_order and challenge_id, are unique to my site. But that doesn't matter. What matters is that all of those settings are fields in my database. You can change them to whatever fields exist in your database. Fields left blank are treated as NULL.

Here is the top of this post as a real-world example:

iA Writer CMS Settings

If I don't want to export an article? I leave the settings out altogether. If I want to publish an article but have it be invisible? I set "status" to either "draft" or "hidden" depending on which I want.

The Code

My blog is built on Laravel, not WordPress. The following is the exact code (except the routes and some other things are different for security reasons) that I'm using, which was made for my Laravel setup, not your WordPress setup.

I'm not going to go over the exacts of how to port this to WP. I assume that if you're going to install this strategy then you're a developer, and if you're a developer then this should be pretty easy to port.

I'm using this package to convert iA's Markdown to HTML. It doesn't support strikethroughs at this time, but that's not a deal-breaker for me. If you're not using Laravel you can use this package instead.

That package supports real-time conversion, but I'm choosing to convert to HTML on import rather than on display for performance reasons.

Local PHP

I created a folder with two files. One called last_updated.txt which contains nothing but a timestamp. The other is ia_to_mysql.php which contains:

<?php

    //Config
    define('PATH_TO_LOCAL_SCRIPT', "/Users/patrickgriffith/Projects/scripts/ia_to_mysql/");
    define('PATH_TO_WRITING', "/Users/patrickgriffith/Library/Mobile Documents/27N4MQEA55~pro~writer/Documents/Pat On Purpose");
    define('SERVER_DOMAIN', "patonpurpose.com");
    define('PATH_TO_LIVE_SCRIPT', '/articles/publish');
    define('SERVER_URL', "http://patonpurpose:8888"); //"https://patonpurpose.com"
    define('VERIFICATION', "VERY_LONG_RANDOM_STRING");


    //Function to get contents between two strings.
    //Taken from https://stackoverflow.com/questions/5696412/get-substring-between-two-strings-php
    function get_string_between($string, $start, $end){
        $string = ' ' . $string;
        $ini = strpos($string, $start);
        if ($ini == 0) return '';
        $ini += strlen($start);
        $len = strpos($string, $end, $ini) - $ini;
        return substr($string, $ini, $len);
    }

    //Function to cURL my article to my live server
    function post_to_live_site($file_contents){

        //Add a long random string that will serve to keep buttholes from submitting on my behalf
        $data = array("content" => $file_contents, "verification" => VERIFICATION);
        $data_string = json_encode($data);

        //Set up cURL variables
        $ch = curl_init(SERVER_URL.PATH_TO_LIVE_SCRIPT);
        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
        curl_setopt($ch, CURLOPT_POSTFIELDS, $data_string);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_HTTPHEADER, array(
            'Content-Type: application/json',
            'Content-Length: ' . strlen($data_string))            
        );

        //Run it
        $result = curl_exec($ch);
    }    

    //Make sure I can make a connection to my site.
    if(checkdnsrr(SERVER_DOMAIN)){

        //Read file containing the last updated timestamp
        $last_updated_file = PATH_TO_LOCAL_SCRIPT.'last_updated.txt';
        $handle = fopen($last_updated_file, 'r');
        $last_updated_time = fread($handle,filesize($last_updated_file));

        // Construct the iterator
        $iterator = new RecursiveDirectoryIterator(PATH_TO_WRITING);

        // Loop through files, including files in subdirectories
        foreach(new RecursiveIteratorIterator($iterator) as $file) {
            
            //Not only does this make sure the file is a .txt, but it also excludes directories
            if ($file->getExtension() == 'txt') {

                //Only do something if the file was modified more recently than this script was successfullly executed (with an internet connection)
                if($file->getMTime() > $last_updated_time){

                    //Get the contents of the file
                    $handle = fopen($file->getPathname(), 'r');
                    $file_contents = fread($handle,filesize($file->getPathname()));

                    //Make sure the contents contain settings and that those settings contain a slug, title, status, and published_time. These are required fields in my database.

                    if($settings = get_string_between($file_contents, '[settings]', '[/settings]')){
                        
                        if(strpos($settings, 'slug=') !== false
                            && strpos($settings, 'title=') !== false
                            && strpos($settings, 'status=') !== false
                            && strpos($settings, 'published_time=') !== false
                            && strpos($settings, 'modified_time=') !== false
                            && strpos($settings, 'challenge_id=') !== false
                        ){                  
                            //That's all that this script needs to handle. The rest can be done on the live server. Send it away!          
                            post_to_live_site($file_contents);
                        }

                    }

                }

            }
        }

        //Overwrite the last updated timestamp and then close the file
        $handle = fopen($last_updated_file, 'w');
        fwrite($handle, time());
        fclose($handle);        

    }

?>

I'm not going to go through all of that code because it's commented decently. But what the code does is:

  1. Check to make sure that I can access my server.
  2. Loop through all .txt files in my iA folder to find ones that have been updated since the last time this code was run successfully.
  3. If the file has the necessary settings, submit it to my live server for processing.

iA stores files in iCloud which stores a copy on your computer for offline access. To find your iA directory cd into ~/Library/Mobile\ Documents/ and then look for a folder ending in ~pro~writer.

Live PHP

Again, my blog is built on Laravel. So for me personally I added Route::post('/articles/publish', '[email protected]'); to routes/web.php.

And then I added the following to app/Http/Middleware/VerifyCsrfToken.php in order to allow the POST to be submitted:

protected $except = [
    'articles/publish'
];

The above two things are Laravel-specific. But that's not the point. What matters is what I put at the bottom of my app/Http/Controllers/ArticleController.php file that you can put wherever makes sense for your application.

public function publish(Request $request){
    
    //Make sure that both fields exist and that the verification string matches
    if($request->has('content') && $request->has('verification') && $request->get('verification') == 'VERY_LONG_RANDOM_STRING'){
        
        $file_contents = $request->get('content');

        //Recheck to make sure settings are included
        if($settings = $this->get_string_between($file_contents, '[settings]', '[/settings]')){        

            //Iterate through the lines of the settings and store values in an array.
            $fields = array();
            foreach(preg_split("/((\r?\n)|(\r\n?))/", $settings) as $setting){
                
                //Get rid of whitespace
                $setting = trim($setting);
                
                //Make sure it's an actual setting, not just whitespace
                //Save setting to fields array
                if(strlen($setting) > 0){
                    $setting_parts = explode("=",$setting);
                    $fields[$setting_parts[0]] = (strlen($setting_parts[1]) > 0 ? $setting_parts[1] : NULL);
                }
            } 

            //Make sure that all of the mandatory fields have values
            if(isset($fields['slug']) &&
                isset($fields['title']) &&
                isset($fields['status']) &&
                isset($fields['published_time']) &&
                isset($fields['modified_time'])                   
            ){                    

                //If this article already exists, update it. If not, create it.
                //Slug is NOT a unique field in my DB. It is only unique to the challenge to which it belongs, which could be no challenge (NULL)
                if($article = Article::where('slug',$fields['slug'])->where('challenge_id',$fields['challenge_id'])->first()){
                    //Found. Do nothing.
                } 
                else{    
                    //Not found. Create it.                    
                    $article = new Article();
                }

                //Set the fields that were passed through in [settings]
                foreach($fields as $key => $value){
                    $article->{$key} = $value;
                }

                //Strip everything from [/settings] and above from the content
                //Add a limit of 2 elements so [/settings] somewhere else in the post doesn't get cut off.
                $content = explode("[/settings]",$file_contents,2);

                //And then update the content
                $article->content = Markdown::convertToHtml($content[1]);                    

                $article->save();
                
            }              

        }
        
    }
}


private function get_string_between($string, $start, $end){
    $string = ' ' . $string;
    $ini = strpos($string, $start);
    if ($ini == 0) return '';
    $ini += strlen($start);
    $len = strpos($string, $end, $ini) - $ini;
    return substr($string, $ini, $len);
}

Again, I'll summarize what that does.

  1. Check a secret verification string to make sure that the submission is coming from me.
  2. Parse the settings and store them as individual values.
  3. Convert the content from Markdown to HTML.
  4. Check to see if this article already exists.
  5. Either save an existing article or create a new article, depending on the previous, with the settings and content provided.

Local Cron Job

*/5 * * * * /usr/bin/php ~/Projects/scripts/ia_to_mysql/ia_to_mysql.php

That checks for updates every five minutes.

Were you expecting more steps? Too bad. That’s it.

As developers we praise the concept of DRY (don't repeat yourself) so we only have to change things in one location when we want to make a modification. Being able to apply that to writing is magical.