CLI to export/import any database to/from SQLite#2496
Conversation
Require PHP 5.5+ FreshRSS#2495
|
|
|
Huh, that's very interesting. Also just to switch servers it might be easier to handle than MySQL export → MySQL import. This done/exit routine looks like a good use of goto to me. It's just using it to go backward (like a while/for loop) that's confusing really. |
|
Tested with success using a MySQL database of 340k articles on an Atom server with 2GB RAM, resulting in a 675MB SQLite file 😃 |
|
To import from SQLite, which would only work properly if the destination DB is empty, it would be nice to provide a command to clear the destination database. But I do not want to make it too easy to avoid user mistakes. Any suggestion? Maybe as a distinct command? ./cli/db-truncate.php --user alice
./cli/import-sqlite-for-user.php --user alice--filename /tmp/alice.sqlite |
|
Perhaps just a sufficiently dangerous sounding flag like |
To ease working with two DBs at the same time
Needs some testing
|
I have made a bit of refactoring to make it easier to work with more than one user / more than one database at a time (fewer static objects). |
More uniform logic for the 3 databases. Fix wrong DROP TABLE for SQLite.
|
I was not so happy of the special case for SQLite. I found the problem with PDO (which gets buggy if we delete the initial .sqlite database) and now all three databases should have the same behaviour. |
|
|
|
Ok, NOW I think it is ready for testing :-) |
|
Now also tested with MariaDB :-) |
|
This is very cool! I tried with my personal database: a 70Mo SQLite file (pretty small compared to yours ^^) to pgsql and it worked just fine :) I'll take a look to the code later |
| ./cli/user-info.php -h --user username | ||
| # -h is to use a human-readable format | ||
| # --user can be a username, or '*' to loop on all users | ||
| # Returns: 1) a * iff the user is admin, 2) the name of the user, |
There was a problem hiding this comment.
| # Returns: 1) a * iff the user is admin, 2) the name of the user, | |
| # Returns: 1) a * if the user is admin, 2) the name of the user, |
There was a problem hiding this comment.
https://en.wikipedia.org/wiki/Iff but I can reformulate :-)
| VALUES(:url, 1, :name, :website, :description, 86400);' | ||
| ); | ||
|
|
||
| define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS `entrytag`, `tag`, `entrytmp`, `entry`, `feed`, `category`'); |
There was a problem hiding this comment.
I'm not sure why you changed that…
Also it implies that we maintain two versions of the same code in UserDAO.::deleteUser which is more difficult to maintain and to understand.
There was a problem hiding this comment.
Because SQLite does not support dropping multiple tables at once. This code never worked.
But in my new PR, I have made all these calls uniform for the 3 databases.
app/Models/CategoryDAO.php
Outdated
| } | ||
| } | ||
|
|
||
| public function select() { |
There was a problem hiding this comment.
[naming, opinion] When I read select, I expect the method to take an id and to return only one result. I think list or all is more understandable. I think you chose select to be close to the SQL syntax but I'm not sure the result is obvious in a development context. My comment applies to all the *DAO->select methods of course.
| } else { | ||
| return true; | ||
| } | ||
| } |
There was a problem hiding this comment.
This was a big part and I have to admit I was very sceptical when I saw the size of the method and the different calls to goto. In the end, it is pretty straightforward to read so I give you some insights but feel free to not consider them :)
- I would rather create two distinct method
import_from_sqlite($filename, $clearFirst = false)andexport_to_sqlite($filename)to don't have to manipulate a$modeparameter and its relatedswitches - it implies a bit of factoring so I would have created
importmethods for each model (for instanceimport_category($daoFrom, $daoTo)) which would returnfalseif an error occurs - your
gotos are nothing more than function calls but with a hidden$errorparameter (goto done;vsreturn done($error);). While I understand the fun of using a pretty old notation next to theyield, I'm not sure that we gain in readability.
There was a problem hiding this comment.
I have removed the goto (there has been so much bashing during one generation, that other readers might have the same reaction), but before it dies:
- In this case, it is indeed a bit like calling a function, but better, because more lightweight (function calls are more expensive), better scoped, and with the code located at a more natural place in the flow.
- In a language that supports nested functions (e.g. C#, JavaScript, Pascal), this is what I would have used, but PHP does not support them, so now there is one more function with a scoping that is too large...
Co-Authored-By: Marien Fressinaud <[email protected]>
|
Tested again after the changes. |
|
Tested again, it still works fine except that I just realize that, for some reason, the feed visibility value ( |
|
Fix in progress! |
|
@marienfressinaud Should be fixed in 2a9bfdc |
|
Ok :-) Merging, with more testing in the follow-up PR |
3 participants
Require PHP 5.5+ #2495
If we move to PHP 5.5+, this is a PR to celebrate that with a new feature 😃
I wanted to have this ability to move from one system to another for a good while, including changing database type, since our current export/import features were not quite good enough, by not taking all entries. This is a full database copy to/from SQLite. This allows e.g. MySQL → SQLite → PostgreSQL.
It takes advantage of
yieldwithout which such a massive data stream would be quite less elegant to write (and I consider using it to our big SELECT as well to avoid big memory copies like we have at the moment).I found amusing to accompany a powerful newish operator with one of the oldest powerful operators (goto) but I can refactor if you are allergic to that part 😆