Sqlite Compatibility

[matthewblott]

Hi,

I've been using the wp-sqlite-db Sqlite plugin which is regularly maintained. It works pretty well with the main WordPress plugin but the obvious caveat being this doesn't apply to plugins.

I started using WP2Static recently and I'd really like to get it working with Sqlite.

A brief look revealed the following files generate platform specific SQL code:

• Addons
• Controller
• CoreOptions
• CrawlCache
• CrawlQueue
• DeployCache
• JobQueue
• WsLog

I've been programming for years but I'm not a WordPress or PHP developer, unfortunately WordPress is ubiquitous and I've had to get acquainted. I would be happy to take this on. Is there a way I can do this as an add-on? Obviously I want to avoid forking the project if possible. Any help appreciated.

Matt :-)

[leonstafford]

Hi @matthewblott, thanks for the interest and that's super exciting, I'd love the extra portability and environments we could support by being SQLite friendly!

My replies here have been a bit slow of late and still way behind on pushing a new release out, may be a little while 'til I can help you properly with this.

Do you get any errors output when trying to use WP2Static with SQLite that you can add here?

Perhaps you can check the SQL statements within those files and try to fake the variables in them and run directly in SQLite to better pinpoint the failures?

Once we have a better idea of what's not working, we can start to work on overcoming it.

[leonstafford]

In terms of doing it as an add-on, it's certainly possible (in the case that we're unable to just make all existing statements compatible with SQLite. We do some table creation early in the WP boot process, so adding filters to those could allow an addon to modify those if needed.

[matthewblott]

@leonstafford tbh it seemed fairly easy to spot when I looked earlier. The files I listed use SQL commands that won't run with Sqlite. Things like SHOW TABLES, SHOW COLUMNS and TRUNCATE TABLE. I don't think it would be hard to hack some code and get it working for my needs. But I wanted something a bit more maintainable and possibly useful to others, hence the reason for reaching out.

[leonstafford]

OK, cool. So these 2 WP functions should be your friend:

• https://developer.wordpress.org/reference/functions/apply_filters/ this one could go into a PR for WP2Static
• https://developer.wordpress.org/reference/functions/add_filter/ - this one into your addon (see other WP2Static addons in github, probably still under https://github.com/leonstafford before we move them to /WP2Static)

If you don't want to start by creating a whole addon plugin, you can test out the add_filter call in your theme's functions.php file and ensure it runs earlier in the boot process than when WP2Static calls your new apply_filters() on the SQL query strings.

[leonstafford]

(That's ignoring any implications of changing those values to the rest of the PHP in WP2Static which may be expecting certain values back, in case the results of the queries change. You could modify further, like "if this filter returns false, skip this whole block of code or do something differently)

[matthewblott]

@leonstafford Thanks, I'll have a look 🙂

[matthewblott]

Okay, I've played around with this quite a bit. Knowing neither PHP or WordPress I missed obvious things and it's been a learning process.

TL;DR

You can use the SQLite plugin currently as is (with caveats, see below).

Longer Version

With a standard setup once you activate the plugin you can successfully create a static site when you click the 'Generate Static Site' button on the admin page. Logging however will not work because the log table isn't created. This is due to an error with the SQL conversion process in the SQLite plugin. I have submitted a bug report here as I believe this is where the problem should be fixed.

Workaround

You can easily create the log table by running the following:

CREATE TABLE wp_wp2static_log (
  id integer NOT NULL,
  time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  log TEXT NOT NULL,
  PRIMARY KEY  (id AUTOINCREMENT)
)

If you run the above SQL you will be able to view the log table. However it won't write to the screen while creating the static site because of another issue. To fix this find the SQL on line 92 of the WSLog.php file:

SELECT CONCAT_WS(': ', time, log)

And change it to the following:

SELECT time || ': ' || log

The plugin will work as expected.

Additional Issue

I always have a VPN running on my machine (seems prudent) and this was causing the process to fail. It took me a long time to figure this out! It was only when I switched this off for the site I was working on that everything worked (something to bear in mind).

Final Thoughts

There is now discussion about making SQLite a core part of WordPress which you may wish to follow.