Hook in sqlite install before initial WP setup

[mhsdef]

trafficstars

I reckon you've thought of this, but, I'm blocked in testing because I think I'm forced through a traditional WP install.

I want to:

1. Copy fresh WP install to filesystem.
2. Copy the plugin to plugins.
3. Somehow, activate, so it sets everything up nicely.

It looks like I have to:

1. Copy fresh WP install to filesystem.
2. Copy the plugin to plugins.
3. Setup wp-config (or do web interface) with a MySQL DB somewhere.
4. Activate the plugin.

Am I missing anything? Too early to the party -- that flow is coming?

[csears]

I ran into the same problem.

I've been looking at the code in the plugin's activation function. I'm wondering if there's some way to perform the same steps manually to get the sqlite support configured before initializing WP for the first time.

[jeff-cannapress]

So from my experience making docker images using the wp-sqlite-db dropin, the process would look something like this:

1. Copy a fresh WP install to the filesystem
2. copy the plugin to WP_CONTENT_DIR.'plugins/sqlite-database-integration'
3. copy WP_CONTENT_DIR.'plugins/sqlite-database-integration/db.copy' to WP_CONTENT_DIR.'/db.php'
4. update the replace parameters {SQLITE_IMPLEMENTATION_FOLDER_PATH} and {SQLITE_PLUGIN} per the contents of activate.php
5. manually install a wp_config.php as the setup-config.php script will explode
6. continue through the setup process.

for the wp-sqlite-db based docker image I use, I include a custom version of setup-config.php

[patmood]

@jeff-cannapress do you have an example of a dockerfile that does this?

[csears]

@patmood Here's the contents of the Dockerfile I used to get it working...

FROM wordpress:latest
ADD src/wp-content/db.php /var/www/html/wp-content/
ADD src/wp-content/plugins/sqlite-database-integration /var/www/html/wp-content/plugins/sqlite-database-integration
RUN chown -R www-data:www-data .
ENV WORDPRESS_DB_HOST=localhost
ENV WORDPRESS_DB_USER=not-used
ENV WORDPRESS_DB_PASSWORD=not-used
ENV WORDPRESS_DB_NAME=not-used

Note that I had to manually make the changes @jeff-cannapress mentioned to db.php before copying it into the image. The environment variables are needed so the setup doesn't prompt you for MySQL connection info, but otherwise are not used.

If the authors of the patch feel like things are stable enough and folks are interested in testing it out via Docker, I'd be happy to publish the image.

[jeff-cannapress]

@patmood I dont have one using the currently under-development plugin. However here is the version I use for local development based on the wp-sqlite-db plugin: https://github.com/jeff-cannapress/wp_sqlite_docker

This integrates adminer for database access via a shared volume in docker-compose. If all you're looking for is to spin up a site without a mysql instance: https://github.com/jeff-cannapress/wp_sqlite_docker/blob/main/.docker/wordpress/Dockerfile is the docker file

[mdbraber]

For those interested, this is a Docker file I've used to build a Docker container containing Wordpress, Nginx and SQL database integration. Mostly based on https://github.com/TrafeX/docker-wordpress/blob/master/Dockerfile and this thread / links (Wordpress 6.1.1, plugin in 1.2.2). See https://github.com/TrafeX/docker-wordpress for the other referenced files.

FROM alpine:3.16
LABEL Maintainer="Maarten den Braber <[email protected]>" \
      Description="Lightweight WordPress container with Nginx 1.22 & PHP-FPM 8.0 & SQLite support based on Alpine Linux."

# Install packages
RUN apk --no-cache add \
  php8 \
  php8-fpm \
  php8-pdo \
  php8-pdo_sqlite \
  php8-sqlite3 \
  php8-json \
  php8-openssl \
  php8-curl \
  php8-zlib \
  php8-xml \
  php8-phar \
  php8-intl \
  php8-dom \
  php8-xmlreader \
  php8-xmlwriter \
  php8-exif \
  php8-fileinfo \
  php8-sodium \
  php8-gd \
  php8-simplexml \
  php8-ctype \
  php8-mbstring \
  php8-zip \
  php8-opcache \
  php8-iconv \
  php8-pecl-imagick \
  nginx \
  supervisor \
  curl \
  bash \
  less

# Configure nginx
COPY config/nginx.conf /etc/nginx/nginx.conf

# Configure PHP-FPM
COPY config/fpm-pool.conf /etc/php8/php-fpm.d/zzz_custom.conf
COPY config/php.ini /etc/php8/conf.d/zzz_custom.ini

# Configure supervisord
COPY config/supervisord.conf /etc/supervisor/conf.d/supervisord.conf

# wp-content volume
VOLUME /var/www/wp-content
WORKDIR /var/www/wp-content
RUN chown -R nobody.nobody /var/www

# WordPress
ENV WORDPRESS_VERSION 6.1.1
ENV WORDPRESS_SHA1 80f0f829645dec07c68bcfe0a0a1e1d563992fcb
ENV SQLITE_INTEGRATION_VERSION 2.1.1

ENV WORDPRESS_DB_HOST=localhost
ENV WORDPRESS_DB_USER=not-used
ENV WORDPRESS_DB_PASSWORD=not-used
ENV WORDPRESS_DB_NAME=not-used

RUN mkdir -p /usr/src

# Upstream tarballs include ./wordpress/ so this gives us /usr/src/wordpress
RUN curl -o wordpress.tar.gz -SL https://wordpress.org/wordpress-${WORDPRESS_VERSION}.tar.gz \
        && echo "$WORDPRESS_SHA1 *wordpress.tar.gz" | sha1sum -c - \
        && tar -xzf wordpress.tar.gz -C /usr/src/ \
        && rm wordpress.tar.gz \
        && chown -R nobody.nobody /usr/src/wordpress

# Add sqlite-database-integration
RUN curl -L -o sqlite-database-integration.tar.gz https://github.com/WordPress/sqlite-database-integration/archive/refs/tags/v${SQLITE_INTEGRATION_VERSION}.tar.gz \
    && mkdir /usr/src/wordpress/wp-content/plugins/sqlite-database-integration \
    && tar -xzf sqlite-database-integration.tar.gz -C /usr/src/wordpress/wp-content/plugins/sqlite-database-integration --strip-components=1 \
    && cp /usr/src/wordpress/wp-content/plugins/sqlite-database-integration/db.copy /usr/src/wordpress/wp-content/db.php \
    && rm sqlite-database-integration.tar.gz \
    && sed -i 's#{SQLITE_IMPLEMENTATION_FOLDER_PATH}#/usr/src/wordpress/wp-content/plugins/sqlite-database-integration#' /usr/src/wordpress/wp-content/db.php \
    && sed -i 's#{SQLITE_PLUGIN}#sqlite-database-integration/load.php#' /usr/src/wordpress/wp-content/db.php \
    && chown -R nobody.nobody /usr/src/wordpress/wp-content/plugins

RUN mkdir /usr/src/wordpress/wp-content/database && touch /usr/src/wordpress/wp-content/database/.ht.sqlite && chown -R nobody.nobody /usr/src/wordpress/wp-content/database && chmod -R 777 /usr/src/wordpress/wp-content/database

# Setup wp-config
COPY wp-config.php /usr/src/wordpress
RUN chown nobody.nobody /usr/src/wordpress/wp-config.php && chmod 640 /usr/src/wordpress/wp-config.php

# Add WP CLI
RUN curl -o /usr/local/bin/wp https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar \
    && chmod +x /usr/local/bin/wp

# Append WP secrets
COPY wp-secrets.php /usr/src/wordpress
RUN chown nobody.nobody /usr/src/wordpress/wp-secrets.php && chmod 640 /usr/src/wordpress/wp-secrets.php

# Entrypoint to copy wp-content
COPY entrypoint.sh /entrypoint.sh
ENTRYPOINT [ "/entrypoint.sh" ]

EXPOSE 80

CMD ["/usr/bin/supervisord", "-c", "/etc/supervisor/conf.d/supervisord.conf"]

HEALTHCHECK --timeout=10s CMD curl --silent --fail http://127.0.0.1/wp-login.php

[mdbraber]

For those interested: here's a Github repository for getting Wordpress with SQLite setup in Docker without any need for MySQL https://github.com/mdbraber/docker-wordpress-sqlite

[aristath]

As long as SQLite is in the form of a plugin, there is no straight-forward way to setup a new site without using MySQL - unless you manually go through the steps in the original post.

I do keep a branch of WP with SQLite integration if you would like to test that though... You can find it in https://github.com/WordPress/wordpress-develop/pull/3220 and it basically integrates the classes and functions from this plugin, as well as add some changes to the install screen to make it easy to actually install a new WP site using SQLite 👍

[mdbraber]

@aristath TBH with the Dockerfile like above it does work quite OK installing / running it without MySQL, so that's great! I'm more concerned whether or not SQLite can indeed run without errors with popular plugins like Yoast: https://github.com/WordPress/sqlite-database-integration/issues/39

[aristath]

The SQLite project got neglected for a couple of months because of the upcoming WP 6.3 release, we needed to focus on that. I'll try to get back on the horse and see what we can do for these things 👍

[ZachWatkins]

The SQLite project got neglected for a couple of months because of the upcoming WP 6.3 release, we needed to focus on that. I'll try to get back on the horse and see what we can do for these things 👍

I just discovered this plugin and am excited to hear it's being worked on! I'm using this plugin on Windows with wp-phpunit for removing dependence on Docker for my test environment. Link: https://github.com/ZachWatkins/wordpress-theme/blob/main/test/phpunit/bootstrap.php

Implementation

1. Add this to composer.json:

"repositories": [
    {
        "url": "https://github.com/wordpress/sqlite-database-integration.git",
        "type": "git"
    }
],
"scripts": {
    "test": [
        "@putenv WP_SQLITE_MODE=enabled",
        "./vendor/bin/phpunit"
    ],
}

2. Install wp-phpunit/wp-phpunit, yoast/phpunit-polyfills, roots/wordpress, and wordpress/sqlite-database-integration as dev dependencies using Composer.
3. Add this to a phpunit test bootstrap.php file before it loads WordPress. This lets me enable or disable SQLite mode using an environment variable:

// Activate or deactivate WordPress SQLite Database Integration plugin.
if ( 'enabled' === getenv( 'WP_SQLITE_MODE' ) ) {

    if ( ! is_dir( dirname( __DIR__, 2 ) . '/wordpress/wp-content/' ) ) {
        mkdir( dirname( __DIR__, 2 ) . '/wordpress/wp-content/' );
    }

    file_put_contents(
        dirname( __DIR__, 2 ) . '/wordpress/wp-content/db.php',
        str_replace(
            array(
                '{SQLITE_IMPLEMENTATION_FOLDER_PATH}',
                '{SQLITE_PLUGIN}',
            ),
            array(
                dirname( __DIR__, 2 ) . '/vendor/wordpress/sqlite-database-integration',
                'sqlite-database-integration/load.php',
            ),
            file_get_contents( dirname( __DIR__, 2 ) . '/vendor/wordpress/sqlite-database-integration/db.copy' )
        )
    );

    /**
     * Activate plugin.
     */
    tests_add_filter(
        'muplugins_loaded',
        function () {
            require dirname( __DIR__, 2 ) . '/vendor/wordpress/sqlite-database-integration/load.php';
        }
    );

} else {

    // Undo changes if necessary.
    if ( file_exists( dirname( __DIR__, 2 ) . '/wordpress/wp-content/db.php' ) ) {
        unlink( dirname( __DIR__, 2 ) . '/wordpress/wp-content/db.php' );
        $remaining_files = array_diff(
            scandir( dirname( __DIR__, 2 ) . '/wordpress/wp-content/' ),
            array( '..', '.' )
        );

        if ( empty( $remaining_files ) ) {
            rmdir( dirname( __DIR__, 2 ) . '/wordpress/wp-content/' );
        }
    }

}

[locol-media-dev]

We want people to use WordPress on the Raspberry Pi, so wanted an easy process to use this plugin without needing the MySQL server.

Thank you @mdbraber for the docker file. I used it as a guide to create a more generic installation process. I think SQLite as an alternative holds much potential!

[mati75]

@locol-media-dev

sudo mkdir database sudo touch database/.ht.sqlite sudo chmod -R 777 database

chmod 777 for database file is a tragic security failure

[locol-media-dev]

chmod 777 for database file is a tragic security failure

Fair, I will spend the weekend sorting out the right permissions and revise.

[locol-media-dev]

Played a bit more with the plugin. I understand the SQLite requirements that is causing people to do 777 on the database folder and file. However I have changed the blog post to mimic what the plugin does,

sudo chown -r www-data:www-data database
sudo chmod -R 644 database
sudo chmod 766 database
sudo echo 'DENY FROM ALL' | sudo tee database/.htaccess > /dev/null

Happy to improve further but probably need guidance from the team.

[ZachWatkins]

I've resumed working on a solution which can initialize this plugin and run <?php wp_install(); ?> to provide a default site without asking a user to go through the site setup UI. The NPM package @wordpress/env provides a Dockerized WP installation with a preset site and I want that DX without Docker.

Would you accept a pull request if I found a way to make the plugin easier to provide this functionality natively?

If it turns out that the replacement wp_install() function this plugin provides does throw an error in the default PHP web server without a MySQL server being available, then I could add a conditional statement that checks for MySQL availability without throwing a database exception that halts the request.

Use Case

If someone has no experience with WordPress, I want them to have a plugin template repository they can install and run in a few CLI commands:

.bin/install-php-and-composer.sh
composer install
.bin/prepare-wordpress-directory.sh
php -S localhost:8080 -t wordpress

Issue

It's difficult to determine when to run the wp_install() function from another plugin while avoiding running core WordPress functions that attempt to access the MySQL database, thus resulting in a database access error.

Non-PHP Solution

Running sqlite3 from the command line to seed the database: sqlite3 wordpress/wp-content/database/.ht.sqlite < demosite.sql.

[luizbills]

I ran in this issue too and decided to create this little script: https://github.com/luizbills/create-wordpress-sqlite

My goal was to be able to quickly install a WordPress on Termux (just out of curiosity).

[swissspidy]

I'm surprised that no none has brought up WP-CLI for this. Wouldn't it be cool if you could just do something like wp core download && wp core install --with-sqlite?