Allow export function to use system default newline character

Weel Young reporter

marked as minor

2016-07-01T07:43:42+00:00

Christopher Kramer

But the \r\n should display and import fine on Mac and Linux, shouldn't it? I would say this is on purpose. Consider the normal user running phpLiteAdmin on his shared webhosting, which is running Linux, exporting the file. He downloads it to his windows system and displays it there. If we were using PHP_EOL, Windows programs like Notepad would show everything in one line.

I think in case of export, we should use line breaks that are compatible with most platforms, as exported files are likely to be moved across platforms.

2016-07-01T08:16:55+00:00

Weel Young reporter

that's a valid point. However if the user opens the exported file on Mac/Linux, it will show 'CR' character. In this case, shall we add an option to let user choose what kind of EOL they want in the exported file?

2016-07-01T08:21:45+00:00

Christopher Kramer

Okay, I have not seen this CR on Linux text editors before, but of course it depends on your text editor. Good text editors for windows also don't have any problem with linux/mac style linebreaks...

I was thinking whether the OS of the client (browser) might be significant or the OS of the server. As phpliteadmin at the server side should not care about the type of linebreaks when importing, I would say the OS of the client might be more significant.

I guess an option might make sense. And I think I would preselect the OS of the client.

2016-07-01T08:35:08+00:00

Christopher Kramer

I just looked up what phpMyAdmin does, as the have kind of the same problem:

https://github.com/phpmyadmin/phpmyadmin/blob/master/export.php#L317

// Defines the default <CR><LF> format.
// For SQL always use \n as MySQL wants this on all platforms.
if ($what == 'sql') {
    $crlf = "\n";
} else {
    $crlf = PMA\libraries\Util::whichCrlf();
}

https://github.com/phpmyadmin/phpmyadmin/blob/master/libraries/Util.php#L1057

    public static function whichCrlf()
    {
        // The 'PMA_USR_OS' constant is defined in "libraries/Config.php"
        // Win case
        if (PMA_USR_OS == 'Win') {
            $the_crlf = "\r\n";
        } else {
            // Others
            $the_crlf = "\n";
        }
        return $the_crlf;
    }

https://github.com/phpmyadmin/phpmyadmin/blob/master/libraries/Config.php#L152

    private function _setClientPlatform($user_agent)
    {
        if (mb_strstr($user_agent, 'Win')) {
            $this->set('PMA_USR_OS', 'Win');
        } elseif (mb_strstr($user_agent, 'Mac')) {
            $this->set('PMA_USR_OS', 'Mac');
        } elseif (mb_strstr($user_agent, 'Linux')) {
            $this->set('PMA_USR_OS', 'Linux');
        } elseif (mb_strstr($user_agent, 'Unix')) {
            $this->set('PMA_USR_OS', 'Unix');
        } elseif (mb_strstr($user_agent, 'OS/2')) {
            $this->set('PMA_USR_OS', 'OS/2');
        } else {
            $this->set('PMA_USR_OS', 'Other');
        }
    }

So they are using the newline format of the client based on the user agent, except for MySQL, as they say MySQL does not like windows style newlines on import.

Maybe we should also check the sqlite3 executable on different platforms, what it accepts.

2016-07-01T08:42:18+00:00

Weel Young reporter

right. 'user-agent' is a good indicator of what newline character to be used. Then in this case we do not need an extra option. Not sure how to check sqlite3 executable? At least on Mac there is no problem to import table with \n

2016-07-01T08:48:52+00:00

Christopher Kramer

I just successfully imported sql dumps with \r\n and \n using the Sqlite3 executables (using its .read command) both on Windows (SQLite 3.13.0) and Linux (SQLite 3.11.0). I did not try importing csvs, even though the SQLIte3 executable can import csvs, it only supports very simple csvs (e.g. without quotes), so it is not compatible with csvs exported by phpLiteAdmin with default settings anyway. And I think using csvs is the wrong way to transfer from sqlite to sqlite anyway.

So we don't need to take special care about the SQLite3 executable. Let's just use the CRLF format that is used by the user's client OS.