Skip to content

Instantly share code, notes, and snippets.

@joshcangit
Last active May 21, 2023 09:08
Show Gist options
  • Save joshcangit/ad28f82baf1c9c2fab22dd9e8f39f799 to your computer and use it in GitHub Desktop.
Save joshcangit/ad28f82baf1c9c2fab22dd9e8f39f799 to your computer and use it in GitHub Desktop.
Import .sql database files from a folder into Adminer.
<?php
/**
* Import SQL files from a directory
*
* @author joshcangit, https://github.com/joshcangit
* @author Roy-Orbison, https://github.com/Roy-Orbison
*/
class AdminerImportFromFolder {
protected $dir;
/**
* @param string $dir optional directory to read from, other than Adminer's current working dir.
*/
function __construct($dir = '') {
$dir = (string) $dir;
if ($dir != '') {
$dir = rtrim($dir, DIRECTORY_SEPARATOR) . DIRECTORY_SEPARATOR;
}
$this->dir = $dir;
}
protected function _readFiles($gz = false) {
$mapped = array();
$glob = "$this->dir*.[Ss][Qq][Ll]";
if ($gz) {
$suffix = '.gz'; # lowercase only because of core
$glob .= $suffix;
$suffix_cut = -3;
}
if ($files = glob($glob)) {
$from = strlen($this->dir);
foreach ($files as $file) {
if ($from) {
$file = substr($file, $from); # do not expose server paths in output
}
if ($gz) {
$mapped[substr($file, 0, $suffix_cut)] = $file;
}
else {
$mapped[$file] = $file;
}
}
}
return $mapped;
}
function importServerPath() {
static $posted = null;
$files = $this->_readFiles();
if (extension_loaded('zlib')) {
$files += $this->_readFiles(true); # core prioritises files without .gz
}
if (count($files) > 1) {
ksort($files);
}
if ($posted !== null || !isset($_POST['webfile'])) {
# use existing translation strings
echo "<fieldset><legend>" . lang('From server') . "</legend><div>";
echo lang('Webserver file %s', '<select name="webfilename">' . optionlist(array('' => lang('Select')) + $files, $posted, true) . '</select>');
echo ' <input type="submit" name="webfile" value="' . lang('Run file') . '">';
echo "</div></fieldset>\n";
$posted = null;
return false; # skip core UI
}
if (
empty($_POST['webfilename'])
|| !is_string($_POST['webfilename'])
|| !array_key_exists($_POST['webfilename'], $files)
) {
$posted = '';
return 'SELECTED_FILE_DOES_NOT_EXIST'; # can't return empty string because of core file_exists() check
}
$posted = $_POST['webfilename'];
return $this->dir . $posted;
}
}
@joshcangit
Copy link
Author

joshcangit commented Jan 5, 2019

Introduction

I know there would be an error if I uploaded a file for importing that is too large.
I like that I can import .sql database files from a folder like how it is in phpMyAdmin which avoids the upload file size limit problem.
In phpMyAdmin, I can change this setting in config.inc.php. e.g. The folder is called upload.

/**
 * Directories for saving/loading files from server
 */
$cfg['UploadDir'] = 'upload';

After that, I made a folder called upload in the phpmyadmin folder and store my .sql files there for easy and convenient importing with no file size limit.

This plugin aims to do just that on Adminer. I find the way it is done on Adminer to be not as convenient. Needing to make a file in the same place as adminer.php called adminer.sql and copy-pasting all the .sql files into that 1 file. I like it in separate files.

Usage

Make a folder within the web root directory to store .sql files for importing.
e.g. The folder in this example is called upload.

- plugins
	- folder-import.php
	- ...
	- plugin.php
- upload
        - ....sql
        - ....sql
        - ...
- adminer.php
- index.php

Load the plugin specifying the relative path of the folder.
Instructions referenced from https://www.adminer.org/en/plugins/#use

<?php
function adminer_object() {
    // required to run any plugin
    include_once "./plugins/plugin.php";

    // autoloader
    foreach (glob("plugins/*.php") as $filename) {

        include_once "./$filename";
    }

    $plugins = array(
        // specify enabled plugins here
        new AdminerImportFromFolder("upload"), // This is where and how to use the plugin.
    );

    return new AdminerPlugin($plugins);
}

// include original Adminer or Adminer Editor
include "./adminer.php";
?>

How it looked before update

On selecting a file. In this case, it is a file called new_database.sql made to test this out.
import-new-database
After clicking Run file, the page will look pretty messed up.
page-looks-messed-up
Even so, the new database works just fine.
new-database

How it looks now

import-database

@joshcangit
Copy link
Author

Suggestions for improvements are welcome.

@KZeni
Copy link

KZeni commented Apr 4, 2019

Please provide a default value for the parameter in the __construct function to prevent fatal PHP 7.2 error. Using function __construct($folder = null) instead should have it work as it did before while giving newer PHP a default/fallback value since it's always expecting one.

@joshcangit
Copy link
Author

joshcangit commented Nov 11, 2019

This should now be able to access .sql files in the current directory.
I assumed it should require a folder but I was wrong.

@joshcangit
Copy link
Author

I wonder if anyone knows how to fix that visual glitch.
🤔

@D-ominik
Copy link

D-ominik commented Sep 1, 2021

Still not perfect, but a little bit better...

<?php
/**
 * Import databases from a folder
 *
 * @author joshcangit, http://github.com/joshcangit/
 */
class AdminerImportFromFolder {
    private $folder;

    /**
    * $folder - directory containing .sql files
    */
    function __construct($folder = null){
        $this->folder = $folder;
    }

    function importServerPath() {
        ?>
        <fieldset>
          <legend>From folder</legend>
          <div>
            <select name="file">
                <option value=""></option>
                <?php
                if ($this->folder)
                    $folder = $this->folder."/";
                else
                    $folder = $this->folder;
                // load all .sql files
                foreach (glob($folder."*.sql") as $path) {
                    $file = preg_replace("~(?:.+\/)?(.+\.sql)~", "$1", $path);
                    ?> <option value="<?php echo $path; if ($_POST['file'] == $path) return $_POST['file']; ?>"><?php echo $file; ?></option>
                    <?php
                }
                ?>
            </select>
        </fieldset>
        <?php
    }
}

@Roy-Orbison
Copy link

@joshcangit
Copy link
Author

Fixed.

Thank you very much! 🙏

In that case, would it be alright to use your code?
The Adminer developer has linked to my gist so I'm thinking of updating my code with yours.

@Roy-Orbison
Copy link

May as well. I emailed Jakub, but I think he's a very busy guy. Better to have one linked solution on the official plugins page.

There's a way to merge gists cleanly. (See my note at the bottom.)

@joshcangit
Copy link
Author

May as well. I emailed Jakub, but I think he's a very busy guy. Better to have one linked solution on the official plugins page.

There's a way to merge gists cleanly. (See my note at the bottom.)

Thought about that for a while. I decided to just copy everthing but keep the filename and class name the same.

@Roy-Orbison
Copy link

👍

@Roy-Orbison
Copy link

Drop that close tag though. You don't want the possibility of breaking someone's Adminer install over a style preference.

@joshcangit
Copy link
Author

Drop that close tag though. You don't want the possibility of breaking someone's Adminer install over a style preference.

Now I realize the closing tag wasn't necessary.

@nausaltech
Copy link

how to increase adminer import file limit

@joshcangit
Copy link
Author

how to increase adminer import file limit

🤔

You will need to edit the upload_max_filesize in the php.ini configuration file if you use the Browse... button to actually upload a sql file larger than 2MB.

Thanks to @Roy-Orbison, this plugin replaces the adminer.sql[.gz] with a drop-down menu to select a sql file found in a folder.
I really didn't like how without this plugin, all the databases would need to be in a single file to be imported.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment