How to Download and install plugin database remotely

I have the following problem. I got a plugin that is huge due to all the data needed. The data is all the countries and cities in the world and also a maxmind.mmdb database.

1 – Issue

Currently I got everything packed in the plugin which makes a 20mb plugin and that creates a problem for users with limited upload size which force them to unzip and manually upload by FTP the whole plugin.

2 – Issue

To load all the cities I got 6 csv files that I load upon activation using the following code:

if ($wpdb->get_var( "SHOW TABLES LIKE '{$city_table_name}'") != $city_table_name) {
    dbDelta( $city_table );

    for ( $i = 1; $i <= 6; $i ++ ) {
        $csv_file  = dirname( __FILE__ ) . '/data/cities' . $i . '.csv';
        $load_data = "LOAD DATA LOCAL INFILE '{$csv_file}' INTO TABLE `{$wpdb->base_prefix}geot_cities` CHARACTER SET UTF8 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\' LINES TERMINATED BY '\\n' ( `country_code` , `city`);";
        $wpdb->query( $load_data );
    }
}

This works on almost every hosting but it fails in some of them like for example WPEngine.

Posible solution

So I was thinking that maybe to fix both issues at once I could upload all the data to my amazon instance and once the plugin is active the user will download and install all data by simple clicking a button.

Questions

  • Is this something viable or doable using WordPress functions or I will face other hosting incompatibilities. Anyone have experiences using something like this?
  • In case this is doable what would be the best way to store the data ? Because instead of loading the csv I will have to replace that for some kind of insert data query in order to make it more compatible.

I hope someone could throw some light at this. Thanks!

Solutions Collecting From Web of "How to Download and install plugin database remotely"

To directly answer your questions:

Your proposed solution is indeed viable, just make sure your CDN solution (Amazon S3 for example) is configured to accept both secure (http) and insecure (https) connections.

As far as your next question, CSV is by nature a great way to store a representation of a table. Another alternative (without seeing an example set of data that you are working with) is XML. That might be a good fit as well because it allows one-to-many relationships.

JSON might also fit well to ease the transition between text and a database.

That being said though, the hands-down best solution is to build an API service yourself and allow your plugin to communicate with it.

I can see two options:

  • You create an API with the city info and make the plugin use the API
  • You create separate add-ons for your plugin, like one for each CSV

Some ideas:

  1. ENCLOSED BY ‘\”‘

It is not a must have in this case. Without ” chars you can reduce your .csv file sizes. This might help alone.

  1. Using SQLite can be another option. If zip codes are numbers only it may save a smaller db file than a .csv file but I haven’t tested this. It does worth a try. And you can use that as a database. WP doesn’t support it by default so you have to write or use other functions.
  2. Use HTTP API functions! These functions are also used by the plugin manager. So if it works there you can also use them here.

Storing data: my best bet (if LOAD DATA is not allowed) is to store zipped JSON or INSERT statements on your Amazon instance.

You can also check disabled functions on WP Engine here.