Remote site aliases for Drush (Proposal and implementation)

Cool.

I think path aliases might be a useful addition; this is something you can't do easily with .ssh/config. It might be complicated to allow arbitrary sets of file aliases, though; if someone made a path alias called "misc", then dev:misc would be ambiguous. Aliases could take priority over folders of the same name in the drupal root; I think that would be okay.

The path alias could be everything up to the first "/" in the path. There are two ways to implement it:

1. $options['site-aliases']['dev'] = array(
       'db_url' => 'pgsql://username:password@dbhost.com:port/databasename',
       'root' => '/path/to/remote/drupal/root',
       'fqdn' => 'mydevsite.localdomain.com',
       'files' => 'sites/default/files',
       'data' => '/my/arbitrary/path',
     );

2. $options['site-aliases']['dev'] = array(
       'db_url' => 'pgsql://username:password@dbhost.com:port/databasename',
       'root' => '/path/to/remote/drupal/root',
       'fqdn' => 'mydevsite.localdomain.com',
       'path-aliases' => array(
         'files' => 'sites/default/files',
         'data' => '/my/arbitrary/path',    )
     );

Do you have a preference? I think I'm partial to #2 (extra nesting over namespace collisions). A third option would be to have a common path-aliases section, but I discount that because 'files' might exist in different locations on different servers.

Do you want me to rename anything? I'm partial to slightly verbose names, but I could change 'root' to 'r' and 'fqdn' to 'l' for better alignment with $options of the same purpose.

You caught me on that "else" formatting; that's how I format my code. I was trying to follow the Drupal convention, but missed one. I'll fix it and submit a new patch with help and path aliases sometime in the next day or two.

- Greg

Implemented as described above.

The way I implemented it, relative paths are always relative to the Drupal root. This makes sense most of the time, but is inconsistent with standard shell command-line usage. What do you think? We could require the use of "root" as an alias (and make the Drupal root just another path alias...), but that would be a bit awkward, since the common usage will be to copy things inside the Drupal root. I have to admit, I was a little ambivalent about the handling of relative paths...

For my part, when I ssh / rsync to a remote system frequently I set up a public/private key pair, sometimes with a password and sometimes without. I use a script ("pushkey") to set it up automatically. "ssh-agent" helps out a lot if you have a script that does a lot of ssh / rsync commands (wrap it in ssh-agent, and you only need to enter your password once). I could also add ssh username and password to the site-alias settings for those who prefer to do it that way.

I'll submit another version with ssh username and password, plus any changes you want for relative paths; just let me know your opinion.

Thanks,

- Greg

I was shying away from requiring 'root' as a path alias because I figured that there were no path aliases most of the time for the local machine, which was a common case; however, it would be easy enough to make an implicit path aliases structure containing just 'root' and 'files' for local paths. So then you would have:

drush sync root stage:root

... if you want to sync the Drupal root of the local installation with the root of 'stage'. An then this:

drush sync ./ stage:files

... would copy everything in the current directory to the 'files' directory on 'stage'.

Now I think that's better. If you agree, I will re-implement it like that.

- Greg

I've updated the code, and I think it works like it should now.

I use 'dt()' to substitute the path aliases, so aliases are now "!files" instead of "files", and they can appear in multiple places in the path if you wish (e.g. "dev:{\!alias1,\!alias2,\!alias3}"). Relative paths in path aliases always start from the Drupal root. Relative paths in the command-line args are relative to the Drupal root when a machine alias is specified, and they are relative to the current working directory if there is no machine alias. "drush sync" tries to determine if a machine is local by checking to see if it is "localhost", or if it has the same name as a folder in "sites" (so it can be wrong if you don't set things up just-so).

"ssh-user" and "ssh-password" settings are now supported. There are two ways to pass the user: one is via 'rsync -e "ssh -l user" machine:/path .' and the other is via 'rsync user@machine:/path .'. The former does not seem to have any facility for specifying the password on the command line, so I only support the later.

I have tested this only in simulated mode--it's good enough for review, but should be run through its paces before commit. I'll do that if you have no further change requests.

- Greg

Very cool. Is pushdb/sync open source / sharable or otherwise available for inspection and porting? (I didn't find it via Google.)

The patch on this particular branch started out as a simple way to keep db_url configuration settings for remote sites out of settings.php and in a drush-specific location. drush sync was supported "because it was there" and because it made sense for the settings to be symmetric for db and rsync.

My thought on configuration was to make a tool (drush command? script? web service?) that would dump a site alias record for the current Drupal installation; if you could hit these remotely, you could easily build a configuration table for whatever set of remote Drupal sites you needed to address. Extra intelligence, be it home-grown or borrowed from existing scripts, is always welcome.

My long-term goal is to build a three-way-merge tool for syncing dev / staging / production servers. I have a working prototype, but it is young and needs work. My thought was to build strong db-independent accessors into drush, and layer the merge function on top of that (either within drush or outside drush, as appropriate). Being able to borrow / steal code from pushdb/sync would be a helpful boon, as the kinds of things that you describe above have crossed my mind from time to time.

Thank you for your feedback,

- Greg

All of the scripts look great, thanks; I'll peruse those over time. I'm setting the status of this incident to "needs work" until I have time to test the mods, probably early next week; then I'll set it back to "needs review" to indicate it's tested. Everything looks good in simulated mode, so I expect I won't need to make any changes or bug fixes. We'll see!

Thanks,

- Greg

I agree; that looks like it would work well. My first thought is that I'd like to see the first four parameters simplified down to two, a-la sql load:

sql sync from to --cache --filename

Here, "from" and "to" are either a site name in the "sites" folder or a site alias. In either case, you can figure out where the machine is and what the db_url is. There are two problems / limitations with this simplification, though.

1. The 'port' of the remote machine might be different depending on whether you are using the site alias with sql load (tunneled) or sql sync (executed locally)
2. It might be inconvenient if you were forced to make a site alias to talk to a remote system

I think that the first problem could be overcome by adding another parameter to the site alias that specified what the db_url port mapped to on the remote system; sql sync could substitute that value when doing a remote operation.

The second problem could be overcome in the same way that other drush sql commands get another optional parameter, e.g.

drush_get_option($prefix . '_database')

For example, it might work like this: presume that "dev" is an actual folder in "sites" or a site alias, and "r" is a fake name that is neither in the sites folder nor in the site aliases. Then we would have:

drush sql sync dev r --r_db --r_db_user --r_db_password --r_ssh_target --cache --filename

sql sync would see that "r" did not exist, but it wouldn't care if it could get the parameters it needed from --r_db, etc. Clearly that's a lot of parameters; I don't think you'd ever want to actually call sql sync like that unless you were calling it from another script.

I caught a bad cold that's kept me down for a few days, so I still have a bit more testing to do on the existing code. In any event, if you think the above proposal is good, I'll work on sql sync when I'm feeling a bit better.

Okay, I agree with everything adrian says, and I will certainly look into using drush_backend_invoke instead of ssh to invoke the remote drush command. I don't think I'm bold enough to jump in and add file-passing as described above, so for the time being I suggest we use rsync to transfer the sql dump. It might be a wise idea to use rsync in any case; if we at some later date employed Kathleen's technique to insure that the sql dump was made in a stable order, rsync might have a big advantage over a homegrown data pipe (being that it is optimized for that sort of thing).

I also think it is a grand idea to be able to easily collect site aliases in your drushrc.php file, although 'drush sql conf' does not contain quite enough information to do that. Another similar command could be added later if desired, but even with that facility, there is still some value in having "exploded" parameters, not for by-hand usage, but for instances where "drush sql sync" is being called by some other script that has all of the connection info it needs (but no site alias).

If it is generally agreed that exploded parameters are a good thing, then I propose the following.

Use case 1: use site aliases

drush sql sync from to

Use case 2: use exploded parameters

drush sql sync --from_db --from_db_user --to_db ... etc.

You can also mix these two forms, in which case sql sync determines whether the argument is for "from" or "to" based on whether there are --from or --to style optional parameters. drush sql sync can determine whether the "from" and "to" args are passed via isset, as missing arguments are always passed as null.

The only shortcoming of this solution is that drush does not support optional parameters; you get a warning every time an argument is omitted. This could be rectified by enhancing drush_parse_command to fill in the extra missing args with explicit 'null's if the command had a (new) indicator that the arguments are optional.

The other option I see is to just abandon the long-form parameters and require site aliases, just like 'sql load'.

Some quick notes. I looked at a couple of things and did a couple of experiments at lunch.

I'm not opposed to the recommended syntax changes to drush, but they are beyond the scope of what I'm prepared to discuss on this thread (or tackle as a minor contributor). Integrating site aliases with drush_backend_invoke probably wouldn't be too hard, though.

1. It seems that the http on --uri is optional (drush --uri=othersite.com status works for me)
2. --uri and -l seem to be equivalent
3. It would be easy to make _drush_bootstrap_site_validate accept a site alias in addition to a named folder with a settings.php file
4. It would be easy enough to make drush_dispatch call drush_backend_invoke if a remote site alias was used
5. This would break places where drush parsed settings.php, but that seems to be mostly limited to environment.inc

Even with this addition, I still favor my proposed syntax above for sql load, sql sync and sync; in other words, for commands that operate on two drush instances at once, I prefer drush sql load from to to drush -l from load to. I think these changes would be low-impact (compatible with existing drush usage) and easy to implement.

Agreed. I'm working on issue #1 using ssh / rsync and should have a patch ready shortly.

Work in progress.

The difficulty in 'sql sync' is not in the actual execution of the operation, but in getting the many parameters to the function in a flexible and usable way. For this reason I took a fairly methodical approach to the implementation and started with parameter management first. As a result, I ended up implementing issue #2 first (Adrian' syntax for remote commands); as a bonus, it also works with drush_backend_invoke, so you can do things like:

drush alias status
drush user@host/path/to/drupal#site.com sql query 'select * from users;'

... or whatever.

I've been over this code pretty well and feel good about it. The implementation of 'sql sync', on the other hand, is still green. I only recommend running it in simulation mode (I have done no more than that). There currently is no provision for doing the rsync without the sql dump, and there is no provision for the "cache" flag. If you are using "Adrian syntax" with 'sql sync', you must specify the db_url with command-line arguments (--source-db-url and --target-db-url). I was going to add a cache flag to 'drush sql dump' to take care of that, but since 'sql sync' is not using drush on the remote machine, I am at a moment a bit unsure how to do the 'cache' test. (bash commands before the sql dump in the ssh command?) I think I have "_" where I mean "-" in some of the command line arguments. Documentation is needed.

I cannot work on this Thursday, but I'll probably have time on Friday. Next week is JavaOne, so I am a bit concerned about "any day now" given my pending short-term lack of availability. I have taken this code pretty far... I'll check here before doing any further work. I recommend playing it by ear until the end of the day Friday vis-a-vis the Drush 2.0 schedule, but if anyone feels the urge to throw in some code or suggestions before then, I'm all ears.

- Greg

Agreed; I will do what I can on Friday, and bring this to completion after JavaOne.

This version of 'sql sync' appears to work fine under light testing. There is still at least one bug; the script uses 'user:pass@machine' with rsync, but rsync does not support this syntax. 'drush sync' correctly uses the rsync password file option.

I invented a new 'alias' context so that the options for the site aliases could be inserted with a lower priority than the command-line options. Before they were being inserted into the 'process' context, which meant that default options from the alias record in drushrc.php could not be overridden with an explicit option on the command line. Now they can.

I added a '!dump' path alias that is used as the sql dump file location if available. This can be overridden by --source-dump and --target-dump options on the command line. If nothing is specified, a temporary file is created in /tmp instead. The default behavior could be more elegant; suggestions on how it should work are welcome.

More documentation and more testing are still needed, but it's getting close.

- Greg

By coincidence, I was just logging in to post an update. I had some critical customer issues come up that have kept me busy the past couple of weeks, but I have done a bit more testing and have included an updated patch with this post.

This version has an sql sync that works well locally, but I have not done remote testing yet. Site specifications are working well, locally and remotely, and I have added an "alias print" command that produces output suitable for dumping into a drushrc file. I have also contemplated "alias list" (show all aliases and site folders); further suggestions are welcome.

I will continue to work on this as time permits, debugging remote syncs and adding documentation. Please let me know if you have any comments; now, before I write the docs, would be a good time to make any adjustments to syntax that might be desired.

Thanks,

- Greg

Update: not forgotten, still fixing bugs and finishing implementation. Haven't had too much time to work on this, but have been making progress. Hope to finish it up soon.

- Greg

This basically works, but there are still a few TODOs and probably still a few bugs. The code is commented; see help for sync, sql sync, alias print and alias list.

Not tested:

mysql (but probably works)
d7 (especially database target specifications)

Features implemented vs requests from various people (see documentation for actual syntax / features):

Owen (14 May) sql sync will fix up d6 paths, but will not create 'secure' databases (I would call these "stripped"). Paths are fixed up in the dump file directly; stripping email addresses should be done at the database level (not supported in current sql sync).

Kathleen (Moshe's 15 May post): mysql will produce sorted output from an sql dump, but postgres will not; this makes committing to svn/cvs in a cross-db way a little difficult, so this is not supported at this time.

Moshe (19 May): sql sync supports the cache flag; you need to specify the path to the sql dump, but you can put this into the site alias (avoids putting it on the command line every time). If the dump file is not specified, then a temporary file is used -- and deleted when the sync is finished.

Adrian (20 May 18:03): Backend invoke is used to execute remote commands such as "drush status". Backend invoke is not used by sql sync, though. 'drush alias print' will produce an alias record you can put in an rc file, but drush will not write these directly. It's easy enough to use "drush alias print > $HOME/.drushrc.php".

Adrian (20 May 18:32) Site specifications as described by Adrian are implemented and work. it is also possible to define other variables (e.g. db_url) by including them in the URI query syntax (e.g. username@server.com:/path.to/drupal?db_url=xyz#site.com).

Adrian (30 May) I've done my best to make the drush code that is not affected by these changes work the same way that it did prior to my modifications; this includes the changes I made to backend.inc, which were for the most part just factoring / parameter changes that should not affect other callers. Please do review, check and adopt carefully; there may be bugs. I'll fix bugs that are reported.

Factoring question: I followed the "drush_x_functionname" convention where it was followed in the existing code -- which is to say that I did not follow this convention in code I added to environment.inc, which did not follow this convention. There is also alias code spread across sql, core, environment, etc., and no new include/alias.inc file or anything like that (command/alias/alias.drush.inc only contains code for alias print and alias list, and is not used by other functions that use aliases). If you'd like me to move or refactor any of this stuff, I'd be happy to do so; I wasn't sure what would be best in some cases. My main goal was to minimize changes in code paths I was not directly affecting.

- Greg

Here is commands/alias/alias.drush.inc; I didn't put it in the patch file this time. This command file requires the patch above (of course), but the main patch does not require this command file; it is optional but useful.

- Greg

Minor bug fix to make remote commands work without spurious messages and errors.

Sounds good, Adrian. The changes I made to backend.inc are independent to the rest of my changes; you might want to patch just backend.inc and test it with your released system to confirm there are no problems. Here is a patch for just that one file.

Thanks,

- Greg

Important note: The "--fix-paths" feature in the patch in #38 and #41 is flawed. This feature tries to fix up paths embedded in a D6 database dump. Unfortunately, paths often occur inside encoded strings like this:

s:34:"sites/mydrupal.org/files/imported/"

In order to fix up "sites/mydrupal.org" to "sites/dev.mydrupal.org" (for example), it is also necessary to fix up the string length included in the encoded string.

Unless I come up with a really good idea, i'll probably just have to back this feature out. There are a few workarounds that can be used in place of this feature:

1) What I usually do with D6 is make my site "dev.mydrupal.org" located at a different drupal root, and store it in "sites/mydrupal.org" just like the live site. This works due to the way Drupal searches for sites folders; the site dev.mydrupal.org will look for "sites/mydrupal.org" if it does not find "sites/dev.mydrupal.org" when looking for settings.php. The other advantage of storing the dev site at a different drupal root is that you can upgrade the dev site and test it out without impacting the live site.

2) Put the dev site and the live site on different machines -- perhaps even put the dev site in a virtual machine. Then both can have the same drupal root and the same sites folder, and you don't need to fix anything up.

3) Use D7, which, as mentioned in #13 above, does not embed the site url in the database.

- Greg

I agree that this shouldn't be rushed in; let's give Owen and Adrian some more time to review it, and if you give me another week or two, I'll test mysql and D7. I expect the former will work without changes, but I think I'll need to make some slight adjustments for D7 database handling.

@moshe: Thanks for the comments; here are my answers (no comment == I agree and will change as suggested).

mode vs. extra: I have no strong opinion; I wasn't really sure how "extra" worked elsewhere in Drush. I saw some references, but didn't see how it was supposed to function. For extra options other than "mode", see what I did in drush sync; it's a little verbose in the implementation, but it allows you to specify any rsync option right on the command line, which has turned out to be very handy. I consider these to be "extra" args, ergo I named "mode" something else -- but I'll name it however you want.

--exclude-sites: Sometimes I have a multi-site installation, and I want to push out just one site somewhere (e.g. maybe take one site from a live server and pull it into a Virtual Machine for testing). If you have your 'files' inside of your Drupal root (maybe not best practice), then rsync'ing the files for all of the sites you don't want takes a lot more time and disk space than necessary. (Real disk space is perhaps free, but keeping a virtual disk small is still nice.) I find this option very handy.

Optional arguments: These are very handy, as they allow "drush alias print " instead of "drush alias print --what=". They are also useful in sql sync if you want to specify one of the targets with a site alias, and the other with --args, as you suggested in #15 (well, you only used --args; I wanted to merge #15 with site aliases, so that either form could be used). I'll document optional args. Basically this flag just causes drush to fill in 'null' for missing arg values so that you don't get a PHP error on the dispatch. The command that supports optional args then has to check for null to see if the arg was provided or not.

sql load vs sql sync: These commands both do about the same thing, and they work almost exactly the same way when run on local databases. They use substantially different mechanisms for remote database access, though. I used sql load a lot when I was in development of sql sync, but now that I've been using sql sync for a while, I personally have no use for sql load. I'd be happy to remove sql load if you want; if so, sql sync could be named either sql sync or sql load, at your option. The only downside to this is you might tweak out people who are using sql load to grab remote databases -- but it might be a net benefit to them, as they'd probably enjoy a performance increase once they switched over to sql sync. Your choice on which way to go; I was keeping sql load around to be conservative.

"seems like site spec should be a global option, no?": I strongly prefer it the way I implemented it. I don't have time to evangelize it right now, but will later if you need convincing. Since I'm short on time, I'll just point to comment #21 above for now (although I moved the order of the spec vs. the command).

I'll post another patch with the suggested modifications as soon as I have time to roll them in and test. I'm also going to roll out --fix-paths; I can't do it with sed, as sed is no good at math, and I can't do it with awk, as awk can't do math on regex matches either. The only option I see is to do stream-processing on the dump file with a php function (or do operations on the database as Owen does); that's another patch for another time.

- Greg

Latest patch. Tested with mysql; works now. Needed to make some bug fixes to --create-db for mysql, and some fixes were needed for situations where the primary Drupal site used a different database than the site being acted on by drush sql * (for people running both postgres and mysql).

Fixed typos identified by moshe. Updated documentation. Removed most calls to drush_log (still wrap call to backend_inc in drush.php with log statements, deliberately).

To do:

Test on D7
Yank out --fix-paths (or buckle down and fix it)
Make --table work more like --skip-tables

- Greg

I noticed that "drush updatecode" takes a variable arguments array. It is implemented like this:

function drush_pm_updatecode() {
...
// Get specific requests
$command = drush_get_command();
$requests = $command['arguments'];
...

I could use this pattern and remove the 'optional-arguments' flag from command.inc if you thought that was better. Then, drush sql sync would take no parameters, and $source and $destination would be loaded from $command['arguments'][0] and $command['arguments'][1], respectively.

- Greg

I just installed and tried Drupal 7 with the patches above. While the basic operations work without modification, there are some caevets.

* D7 uses an array to specify db parameters rather than a string. While it works just fine to use a D6-style db url to point to a D7 database, I intend to change the site alias structure to allow either a db-url string or a D7 db array. I may rename the field from 'db-url' to 'db'.

* There is no way to specify D7 slave database specifications in an alias record. However, since it is possible to make an alias record that points directly at a slave db rather than a main site db, and since it is possible to specify the db-url with a command-line parameter to sql sync, I do not have any specific idea to improve database targeting for D7; it's probably fine as it is.

* One slave-database support feature I am considering is single-argument sql syncing, as in "drush sql sync mydrupal.org". This form would sync from the main db to all of the slave databases defined in settings.php. This could be useful when first setting up slave databases or for testing the feature before replication is set up in the database itself.

* drush alias print does not print D7-style db records; I plan to fix this too.

I'll post a new set of patches when I have time to put them together (probably within a week); let me know if you have any D7-related comments or suggestions.

- Greg

I started implementing the items above yesterday, and found that it was most natural to put the entire 'databases' structure into the alias record. drush will create a 'databases' record with 'default' / 'default' from a db-url if necessary. Contrary to what I said above, I went ahead and supported the database and target flags to select any database in an alas record. It is still an option to make separate alias records for every database, if desired. This is still a work-in-progress, but I should have an updated patch ready shortly.

A question on documentation, though. It is difficult to fully document the changes in this patch within the contect of the drush help system. I think what is needed is a rewrite of the documentation at http://drupal.org/node/477684. I could rewrite this in some external location (where?) and promote it to the main documentation page once drush 3.0 is marked stable. Sound good?

- Greg

Here is the latest patch, which includes all of the code changes mentioned above. To whit:

• D7 support
  • --source-database, --source-target, etc. supported
  • Single-parameter 'drush sql sync' will copy default database to all slave databases
  • D7-style 'database' structures may be used in alias records. If db-url is used instead, it is converted to a database structure for uniformity.
• 'optional-arguments' feature removed
• Unification of skip-tables, structure-tables and tables, all of which can be specified either via a key or list (--skip-tables-key is key of element in tables list, -skip-tables-list is a list of tables on the command line). All tables reference the same table in options, but 'skip-tables' and 'structure-tables' are still supported for backwards compatability.
• The previous implementation of the D6 database repair has been removed in favour of a mechanism that allows the caller to specify a clean-up script via the --db-repair-script option. The --simple-repair option will provide a very simple 'sed' script that works in instances where the root is the same and the uri does not appear in serialised data structures.

The documentation still needs a little touching up. I'll enhance the drush help structures in the near future, but will wait to modify the online drush documentation page until after this patch is committed (to avoid confusion that may be caused if trying to follow documentation on the code at drush HEAD).

alias.drush_.txt should be renamed to alias.drush.inc and placed in "commands/alias" (the --new-file option to cvs diff isn't working as it should for me).

@adrian: No changes to backend.inc; you can still do an isolated test with the patch from #43 before testing the whole patch if you wish.

- Greg

I'll patch against head again later today.

Okay, here is the latest, re-patched against head as of right now. This also includes moshe's fix to the drush sql dump bug that I introduced that broke skip tables when sql dump was called directly, rather than from sql sync (sorry).

There are also a couple of changes that I added.

1. --exclude-conf is now the default for drush sync; you must use --include-conf to copy settings.php, robots.txt and .htaccess files. With site aliases, it became really easy and convenient to just type "drush sync live dev"; after I overwrote my settings.php file by accident a few times, I got annoyed and changed the behaviour. I am generally reluctant to make changes that would break existing functionality, but in this case, the original drush sync added so little on top of rsync that I am assuming that not many people are using it right now. Please let me know if you think this change is too high impact.

2. Previously, drush sql query returned results in psql's default format. I modified the default flags so that it would instead return a table in the same format that mysql does, so that scripts can call drush sql query and process results the same way regardless of the database used. These args to psql were provided as default values to 'extra', so if you provide your own extra, it will replace the default values completely (no need to worry about 'dueling command-line arg preferences').

3. I unified the way that skip tables, structure tables, and tables (list only the tables you want to include) work; it is now possible to either specify a 'key' (lookup to a setting in some .drushrc.php file) or as a direct list. It still works the way it used to, but it also will look in a common table for all three table modifiers, so if you have a table list including the various user tables (users, roles, permissions, etc.), you can either skip them or include them w/out having to maintain both lists in two or three separate lists.

I was thinking about taking the "!" out of path aliases, so that the root would be 'root' instead of '!root', but I didn't do that yet. Let me know if you have an opinion on this; I don't think I'll want to make the change after commit.

The latest 'alias.drush.inc' is also attached; put this in "commands/alias" if you want "drush alias list" and "drush alias print" (very useful, and I recommend taking it, but it is optional).

- Greg

I have no objection to renaming to site alias. Do you want "site alias list" and "site alias print", or something else?

Regarding the db_url parameter in site aliases, I would kind of like to keep it there as an option, as that allows your feature request from #24 above (drush sql sync to remote servers without drush) possible. For my part, I do not put in the db_url parameter by hand; I use "drush alias print >> $HOME/.drushrc.php" and then edit the result. I do admit that under this scheme, all of your site aliases will become stale if you ever change the db_url of your drupal site, so I am amenable to the idea that the db_url parameter could be optional, and would be filled in if needed via a call to the remote drush to look it up if it is not hand-coded. At the end of the day, though, I leave it up to you and Adrian to decide if you want to require drush on the remote site or allow the local copy of db_url in the site alias. I'll write code to fetch db_url remotely in either case.

- Greg

drush sync + site aliases can do your named section pushes (site, files, common, all, but not required). Prefix fixup would be a helpful addition to drush sql sync. I don't use that feature myself, so it is a lower priority for me than the other kinds of fixups discussed above. At the moment I am carefully configuring my sites so they do not need fixup (see above); what I'm working on right now is a higher-level set of commands (a separate patch) to detect database differences and facilitate three-way merges (see some of the references above).

Thank you for the link to your scripts; I think the code here might start moving a bit for a while as Adrian and Moshe submit their comments. If you were interested, perhaps you could make a prefix-fixing patch after this thread is committed.

- Greg

@adrian,

I know you're probably still catching up post DrupalCon, but please review this for me when you get a chance.

Regarding your comments that Moshe forwarded in #61, I looked into implementing your suggestions, but found backend.inc to be lacking in two important ways.

1. 'drush' is addressed via the full path to drush.php, which defaults to $GLOBALS['argv'][0]. Your suggestion was to provide a path to 'drush', defaulting to just 'drush'. I actually like your suggestion better, but to do so would require a modification to backend.inc -- and of course we'd have to be sensitive to existing code that was calling it with a path to drush.php and not just accepting the default value.

2. backend.inc nicely packages up a JSON-encoded data structure to return to caller, where it is nicely unpackaged again; however, the command output is always returned as a simple string. This is fine for most D6 implementations, where $db_url is a string, but it leaves much to be desired in D7, where $databases is a nested array. (Even D6 has some support for multiple databases, but this may be vestigial; I've never tried to use it, anyway.) I could always package up a JSON-encoded string and write it to stdout; the result would then be a JSON-encoded string embedded inside a JSON-encoded string. If I closed my eyes and tried not to visualize it, that might be okay, but I wanted something cleaner.

I solved these two problems with two enhancements to backend.inc.

1. The path to 'drush' can now be either a path to a drush script, or a path to drush.php itself. If the provided path ends in ".php", then backend.inc will use 'php' to execute it. If it does not end in ".php", it is presumed to be a shell script and is executed directly. When the target machine is local, the default path to drush is $GLOBALS['argv'][0] (which must always be correct on the local machine), and on remote machines the default is just 'drush' (which will be correct whenever 'drush' is in the path, which seems like the most likely choice given no explicit information about the remote machine).

2. I added two convenience routines, like so:

function drush_backend_set_result($value) {
  if (drush_get_context('DRUSH_BACKEND')) {
    drush_set_context('BACKEND_RESULT', $value);
  }
}

function drush_backend_get_result() {
  return drush_get_context('BACKEND_RESULT');
}

Any function can call 'drush_backend_set_result', and the object passed in will be stored in the BACKEND_RESULT context if drush was called with the --backend flag. drush_backend_output then uses drush_backend_get_result like so:

  $result_object = drush_backend_get_result();
  if (isset($result_object)) {
    $data['object'] = $result_object;
  }

This can be used as in the following example:

function drush_sql_conf() {
  $db_spec = _drush_sql_get_db_spec();
  drush_backend_set_result($db_spec);
  drush_print_r($db_spec);
}

In other words, stdout is the human-readable output for the command, and the 'object' entry in the returned array is the machine-readable php object that is equivalent to the human-readable output. drush_backend_set_result would of course be optional, and would be used only by drush commands designed to provide information to a remote drush instance.

The full patch containing my modifications to backend.inc are attached. Please let me know if you think these extensions are acceptable; if they are, I will go ahead and use them to extend the functionality of site aliases to fetch db settings from the remote drush instance.

Thanks,

- Greg

Yes, I do know that it is technically possible to put a JSON-encoded string inside of the results in backend.inc; my objection is merely one of style and complexity. backend.inc currently takes all of the output from stdout and puts it into the result variable. To put a JSON-encoded string into the output, you would either need to surround it in start / end marks (as backend.inc already does for its result string) and strip it off on the other end, or just put it in 'naked' and hope for the best (which would work fine unless there was stray output on stdout, in which case it breaks). Putting in a naked JSON string seemed to fragile, and duplicating the output wrapping and unwrapping seemed inelegant. In either case, you would need a new version of "sql conf" that output JSON-encoded output, or a separate arg to that controlled the output style. Overloading --backend to control both (that is, make "sql conf" output JSON when --backend is specified) interferes with code that really does want the human-readable version returned remotely. Overall, I think I like it the way I did it better, but I could re-implement as a wrapped JSON-encoded string; there's just so much more duplicated infrastructure that way.

I re-rolled the backend.inc patch and the entire patch as well, but I want to test them before posting.

Okay, here we go. Since I was in the middle of implementing the db spec fetch, re-rolling and testing the patch turned into finishing the implementation; it now works pretty well. If you do not specify 'db-url' or 'databases' in an alias, it will be fetched via backend_invoke (remote aliases) or read from settings.php (local aliases).

I also renamed a couple of functions

• 'site alias', aliased to 'sa' (formerly was 'alias print')
• 'list sites', aliased to 'ls' (formerly was 'alias list')

Also, you can now do the following:
drush sa `drush ls`
This quick little construct will print out a site alias record for every alias and every site in the 'sites' folder.

drush-HEAD is the whole patch, naturally. The backend.inc patch is just for Adrian, for isolation testing; it is included in drush-HEAD. The sitealias text file should be renamed to sitealias.drush.inc and placed in "commands/sitealias".

- Greg

Thank you for the thorough review. I'm away for the weekend, but I will be able to quickly take care of these issues sometime early next week.

- Greg

Yes, you can run any command remotely with this mod (e.g. drush dev status will run drush status on the remote machine specified by the "dev" alias. Actually, "dev" does not have to be a remote machine; it can be another site on a multi-site install. (Aside: this effectively makes the "-l" optional, per Adrian's comments in #18 and #19.) The code to process the alias does not happen right there in drush_main, though; just like the other portions of the drush bootstrap, it happens elsewhere as the drush state is initialized.

The site alias is processed in the function _drush_process_site_alias(), which is called from _drush_bootstrap_drush() in environment.inc. This is one of the functions you already requested more comments for; I will provide them once I have a chance to sit down and address the issues raised since the last time I rolled the patch.

- Greg

The task is longer, and my available time scarcer than I supposed; I'm still working on comments and factoring. In the meantime, here is the new sitealias command. With no parameters, it lists the known sites. With --long, it lists them as site specifications. With --full, it prints full alias records. Unlike the old 'alias print' command, sa with no parameters lists all known sites.

For factoring, I was considering making a new 'sync.drush.inc' file for 'sync' and 'db sync' (renamed from 'sql sync') to live. Let me know if you'd like to see that happen, or if you'd prefer that I keep 'sync' and 'sql sync' as they are.

This one might be ready to go; please give it a try and let me know if you find any blocking issues. I still want to polish the site alias command, and I never did get rid of the SQL command echoing during sql sync, but "the perfect is the enemy of the good". I will continue to refine this code as time goes on.

IMPORTANT NOTE to those who have tried this code before: If you have any existing site aliases, please rename and !drush entries in your path aliases to !drush-script. I renamed this alias so that I could use !drush as the alias to the folder that drush lives in. If !drush is not defined, it defaults to the same path that drush is installed to on the current machine. Therefore, without doing much of anything, you can update drush remotely via:

drush sync \!drush stage:\!drush

I renamed sync to rsync and aliased it back to sync again. This prevented Drush's "magic dispatcher" from running both sync and sql sync when I factored them into separate files. There are probably other solutions that would have worked as well.

See my comment on ob_flush in the patch. I found the source of this, but was reluctant to change the file in case there was some side effect. The warning is innocuous.

Owen made some good points, but I didn't have time to address them. I agree with Moshe's comments. I didn't even take the time to remove the repair script. It would be okay if that disappeared; I'm not sure that I'm going to use it long-term. Short-term it is vaguely useful to me. The comments on these features should be shorter. Actually, I just noticed that I broke this feature. I switched to strstr as suggested, but this should have been str_replace (fixed in drush sync, but not here -- no time to remove this now).

I just backed out the feature that let you put an alias in the -l / -uri parameter.

All other issues commented above, I believe, are fixed.

- Greg

Minor update:

1. re-rolled against latest commits
2. sql sync now quieter on import
3. used string_replace in prepare script

Also, some answers to a couple of questions that I missed above:

By default, it seems like the mysqldump on the remote site tries to save to the temp dir of the local site.

Yes, this was by design. If the temp dir of the local site is not in sync with the temp dir of the remote site, then you can set the dump file location in the site alias to avoid using the temp file feature. As a side benefit, this also allows you to use the cache feature, which is not available when using temp files. I could ask the remote machine to create the temp file, but that would require one more call to ssh (and one more password entry, if you don't have your credentials set up yet). I felt it wasn't really worth the effort to put this code in, considering the overhead.

the term 'slave databases' confused me here. Perhaps 'destination databases'?

A d7 'slave database' is a mirror of the main (default) database of the named database set. Usually, the slaves are set up to mirror at the database level, but if you haven't configured your database to do this yet, this drush feature will sync up all of the slave databases to the main by dumping the main and copying them over all of the slaves. This feature isn't super-critical, IMO.

Some new functions in this patch feel more like API functions that belong in drush.inc or a new sitealias.inc. If you disagree, then perhaps lets break sync out into its own file like updatecode.inc

So, I split out the larger functions into sitealias.inc, and the two commands went into rsync.inc and sync.sql.inc. The problem with my factoring is that it requires sql/sync.sql.inc to make a cross-sectional include to core/rsync.inc, which is inelegant (if not prohibited by policy). Moving the drush_core_call_rsync function to includes/drush.inc would take most of the contents of the rsync file with it. Thoughts?

Bail if user specified unrecognized option? Catches typos? Just a thought.

The reason I have individual options here instead of just using "extra" is that it allows site aliases to specify default options to rsync that can also be overridden on the command line. In order to detect unrecognized options, I would also have to be able to filter our drush global options, which means that the sync function would break every time more of these were added. I didn't think that was ideal.

Overall, though, I implemented these as I thought would probably be best. It would be nice to commit this so that my differences and conflict lists could be smaller. Please let me know if there's anything in these items, or anything else you need prior to doing that.

Thanks,

- Greg

Thanks!

1. I'll remove the repair scripts.

2. Doh. I was about to say 'works on my machine', but does indeed seem to be broken. I'll do a clean checkout and fix.

4. I leave this to you. I have no objections, but it will break people who use sql load. You might want to leave it in for a while to give people a chance to migrate to sql sync.

5. I will remove the slave db feature.

6. I promised to document this on drupal.org once the code was committed. Since it now is, I'll move this to my active to-do list.

7. Will do.

8. What sort of validation do you want to see? Potential solutions seem tedious; requiring an appropriate $PATH on the remote machine seems okay to me. I could put in the full path to the commands, but there is some chance that they may be installed in different locations on different machines. 'sed' should always be at '/bin/sed', but then again, 'bin' should always be on $PATH, unless $PATH is empty. How would you like to see this handled? Call each remote command via its full path? Hard code the path to common values, or assume that remote and local machines are about the same and use 'which sed' to find the path to sed?

- Greg

Update: #1, #2 and #5 done in this patch. (Still owe you the other half of #1: a reply to Owen's issues.)

1. You are right; you cannot define site aliases in site config files. An earlier version of site aliases attempted to initialize aliases at every stage of the bootstrap. However, I changed it to the way it is now because aliases that change the drupal root after we bootstrap the drupal root would require us to run through the bootstrap process again. From a code standpoint, this is very awkward due to the way the drush bootstrap works today. Aliases in site config files that do not change the drupal root work, though; I could perhaps put in a secondary alias check after we bootstrap sites, and require that the drupal root not change if the current bootstrap phase is > DRUSH_BOOTSTRAP_ROOT. Then you could put in aliases to remote machines, or other sites in the same sites folder in site config files. (I sometimes have multiple drupal roots for isolation testing, but I imagine that for most people, multiple roots is probably rare.)
2. You rarely need to specify !drush-script; if $PATH is correct, !drush-script defaults to 'drush', and if '!drush' is defined, then !drush-script defaults to '!drush/drush', which should always be correct. I could check to see if !drush-script is defined, and initialize '!drush' from 'dirname(!drush-script)' if it was not explicitly defined. The rest of the code's behavior would not have to change, and you wouldn't have to define both unless you had an exotic setup.
3. What if I added a flag '--temp' that indicated that drush sql sync should use a temporary file? Then we could fail unless we had !dump or --temp. This 'nag feature' could get more people to initialize !dump. (Alternately, I could print a nag warning message instead of failing).

Regarding #2, how about if I change the code as I suggested in #88, and then either (a) change the documentation to emphasize that you do not need to specify both path aliases, or (b) remove the documentation for !drush. (b) sort of obscures the drush sync !drush trick, though...

Edit: Question removed; I thought of a better way to do it.

Edit: Do not apply this patch; it is superseded by #94 below.

Here is a patch for your consideration. Site aliases defined in site config files work, but only if they have the same drupal root as the site the config file appears in. This limitation is caused by the fact the it is not possible to bootstrap the drupal root twice.

Edit: This limitation is difficult to overcome because the function "conf_path()" is pulled in by the drupal root bootstrap; if you don't bootstrap the drupal root, then you can't load additional drush settings from the drupal site folder, so once you have bootstrapped to the site, you cannot switch to a new drupal root.

This patch is a little rough; I can clean it up a bit if you want to keep it, but I want to know what your thoughts are first.

- Greg

Edit: Ignore this; the solution in #94 is better. Keeping this here for reference, though.

Here is an alternate idea on how to solve the site alias problem.

• Move the 'locate drupal root' operation to the drush bootstrap phase (put off the rest of the drupal root bootstrap operations until later)
• Find all of the drushrc.php files anywhere under the drupal root
• Include each one, and array_merge the site aliases from $options into the site aliases in the drush contect
• unset $options at the end

The correct site drushrc.php file would still be found and included during the site bootstrap.

The advantage of this solution is that all aliases would work cleanly (e.g. you can set the drupal root in a site configuration file).

The disadvantage of this solution is that it would introduce a new limitation to site configuration files, as you would now have problems if you defined functions or introduced other side effects besides setting $options in one of these files. While I expect this to be rare, any drush user doing this today would be hurt by this change.

Please let me know which way you think it better: #92, #93, leaving it unfixed, or perhaps some other technique I haven't thought of yet.

- Greg

P.S. I also considered loading site aliases from a new, different configuration file stored in each site folder, following the techniques above (call this "#93b"). This is, perhaps, counterintuitive, as people will expect to put their aliases in drushrc.php. But maybe we do -both- #92 and #93b, with the warning message in #92 providing the name of an alternate file that the alias can be placed in...

Wait, I've got it.

The code will proceed as in #92. If the alias changes the drupal root, then I will set the 'remote-host' option to 'localhost', forcing the command down the remote-execution code path in drush.php. I will special-case 'remote-host' == 'localhost' just before calling drush backend invoke, and substitute a local exec instead. If the drupal root does not change, then I'll re-bootstrap the drush site as #92 currently does.

That should work cleanly. Give me some time to code that up; I'll post it here and set the status of this incident to 'needs-review' when it's ready.

- Greg

Here is the patch for #94. It only differs from the patch in #92 by a few lines, but it clears up all of the open issues from above nicely and works well.

Please review; I recommend committing if you find no issues with it.

- Greg

Finally replying to Owen's comments in #72...

1. Per Moshe, the repair scripts have been backed out as you suggested. I think this is for the best.
2. If you take a look at the code that fetches the db_url from a remote machine (see drush_sitealias_get_record in includes/sitealias.inc), you will see the mechanism where we can fetch a php object from a remote drush instance using drush backend invoke. The invoked command uses drush_backend_set_result to pass the php object result back (see drush_sql_conf for an example), and the caller obtains the object from $values['object'], where $values is the result from backend invoke. It would be easy to use this same mechanism to load settings from a remote machine. I would put this facility in the Drush bootstrap routine _drush_bootstrap_drush just before the call to drush_sitealias_check_arg. You would need a new drush command to call on the remote machine that would return either the value of the $options variable from one config file, or perhaps the entire $cache object from context.inc. It wouldn't take too much for the receiver to then integrate either $cache or $options, as appropriate. I think I'd go with $options, as there might be too much in the whole $cache. It would probably only take a dozen or two lines to implement. However, I think that Moshe's suggestion -- to just check a drushrc.php into svn -- is better. Another option would be to get yourself started via "drush --remote-host=somewhere.com sa --full >> $HOME/.drushrc.php", and hand-edit from there. Also, as previously mentioned, the code will already fetch the db spec from the target machine if it is not included in the alias record, and this much is probably sufficient for most purposes (automatically sync with database name / password changes). So, in summary, I don't think I will pursue this route (fetching remote options every call), but it wouldn't be hard to implement if someone wanted to do it--in which case I'd be happy enough to help.
3. I agree with Moshe on the issue of database fixup. For my part, I'd like to add that if you have a security concern about the contents of your database, it would be better to have a web service or cron interface that would clone the secure database to a work database on the same machine (perhaps via drush sql sync) and then fix up the records via the SQL interfaces. You could then allow developers to drush sql sync directly from the work database, or (perhaps more securely) sql dump the work database and only give access to the dump for import. This can be remotely imported via the --no-dump option of drush sql sync as Moshe previously suggested.
4. Regarding telling rsync to skip large files, if it were me, I think I would want to place the large files in a particular path inside of the files section, and use the built-in rsync facility to skip files at a given path. I realise that this isn't necessarily realistic for existing file hierarchies, or in instances where users or schemas dictate where the large files are stored, but that would be the ideal situation. If this feature was widely desired, my preference would be to see it enhanced inside of rsync, but I have no insight into the rsync code complexity or the feasibility of making a contribution to that project. Because drush sync will pass through most rsync options, you could always continue to use your existing script above, but replace the call to rsync with a call to drush sync, and replace the ssh / find call with a remote drush call to a new drush command that executed the 'find' command for you. That would only be a little work, and you could then use your drush aliases to address your remote machines. Offhand, I don't think this should be an integral feature of drush sync, though.

Thank you, Owen and Moshe and Adrian for all of your feedback on this drush feature. The open issues are starting to wind down and I'm starting to make new incidents for some of the minor items due to the length of this thread. All in all, it's nearly "done", and the result is clearly much better than it would have been without all of your great assistance. Thank you so much for your help.

- Greg

Here is a new patch that addresses issues 1 - 3 in #99, above. It also fixes a bug with site aliases in site config files that change the drupal root; I'm not sure if it never worked and I just didn't notice, or if I broke it with some other change, but it definitely works here.

If I come up with a good idea for the 'omit large files' request, I'll submit a new issue.

Moshe,

In the instance you mention, the 'localhost' setting will never be shown to the local machine's network; it will be special-cased in backend invoke, and converted into a direct call to drush rather than an ssh-wrapped call to drush. The change you requested above, to also respect 127.0.0.1 in backend invoke, will only come into play should someone call drush with "--remote-host=127.0.0.1". There's no motivation to do that by hand, but I figured it might help if someone had a script that called drush with --remote-host set to one of its parameters, which might be 127.0.0.1 if that's what you habitually use on your local machine.

- Greg

There's always a better way...

This patch is an improved version of the previous patch. I realised that I was checking for localhost in the wrong part of backend invoke, so I was not correctly testing against localhost in all parts of the function. This is now corrected, and I also factored out the localhost test to a function in environment.inc (moved from rsync.inc).

This fix unfortunately exposed an existing bug already discussed in #586466: Drush.php is no longer directly executable (can interfere with backend invoke). I put in my proposed fix for that problem in another patch that I attached to that incident; that fix is not included here. These two patches do not conflict; the patch attached to this issue will work on your system if drush_find_php is working on your system.