Remote database support added

[erikreagan]

Hey there

Lovely work on Greeny. It's helping us out on a project with Assets where we need absolute paths and relative paths no longer work.

I encountered an issue which required some tweaking to Greeny though. Perhaps you can take a look and merge our changes into the add-on if you are good with them.

We have multiple developers on a project and each developer has their own local working copy of the site. We often connect to the same remote database so we can all see the working Channels, Entries etc. This caused an issue with Greeny because it would get triggered by each developer and continually overwrote path settings in the same database.

I added 1 column to the exp_greeny table which serves as a "key" for each developer. All that is required is that each developer set the $config['greeny_env'] setting locally and Greeny will respect that and not continually overwrite the same remote db upload directory paths. We achieve this by ignoring each developers local config override file from the repository meaning it's never deployed to our other environments.

Let me know what you think - or if you have any questions. Thanks again for the add-on!

Regards, Erik

[bliswebagency]

Hi Erik,

Sounds like a great idea - I've merged your changes and will update the release on Devot:ee a little later on today. Thanks for contributing to the project!

Out of interest, how are you finding the process of working with a remote database on EE. We've tried this in the past and always found it a little too slow.

Sam

[erikreagan]

Hey Sam

Glad you liked the tweaks. Thanks for pulling them in.

The speed of using a remote database presents far less of a problem than having to merge/migrate changes from 3+ developers into a single dev environment during the development process. So while it is admittedly slower than a local db, it's very much worth it.

It really depends on the server though. Ours isn't bad at all with remote db connections but I know some are no fun at all.

Erik

[bliswebagency]

Ok, you've rebuilt be enthusiasm to look further into remote DBs again. Managing the EE Database within a team when working on a dev environment has always been a real sore point for us. We've been getting around it using Navicat's syncing tools but that can be hairy at the best of times! Might just need to shop around a little more for a hosting company who can offer a more acceptable speed from our local dev connections...!

[erikreagan]

We use Navicat for certain transfers as well. I can't imagine how much you use it with multiple developers working on a new project.

I'm really hoping a better workflow comes into play down the road. I've been running ideas around the EE sex community. I've also chatted with some EllisLab devs. I'm hoping to chat a bit at EECI with Les and Derek. We'll see how that goes.

I've also started experimenting with a new approach to automating things a bit. It's a feat though.

Let me know how the search for a host goes for your DB needs. We certainly prefer it over the alternatives.

Best, Erik

[bliswebagency]

All I'll say about our approach with Navicat is that it requires a pretty good understanding of what's going on under the hood of the EE DB structure and a bit of coordination regarding who does what. It's not a perfect system by any means... unique IDs can cause issues if not managed well but it's the serialised settings associated with exp_sites and some other modules which can knock us for six. However, knowing the caveats does help us avoid many problems now... you just need to be a real gEEk to attempt this idea.

Obviously we've known that this approach wasn't really tenable as a long term solution which was why I thought I'd bail you up regarding how you've managed to make the remote db work for your scenario. We tested this approach on a few of the hosting companies we use only to find it far too slow. After spending the last hour looking further into remote hosting options, I'm now strongly leaning towards biting the bullet and setting up our own locally hosted server. Fast ping times, complete control and a dedicated machine all seem to be pushing me in this direction. Maintaining it however isn't something I look forward to. Perhaps a necessary evil for the payoff.

Any way, thanks for letting me pick your brain. Hopefully this conversation becomes part of something that leads to a better solution for the whole EE community in the future.

[erikreagan]

I would instantly setup a local dev server with remote access based on IP address if we had a more centralized team. Since I'm the only dev who is actually in our office though we stick to a 3rd party host.

I definitely understand the need to know the ins and outs of the EE DB. I think that's important anyways when you start working in multiple environments, regardless of how you keep data flowing upstream and downstream.

In my experimenting I ended up with some documents regarding content flow and table structure. Maybe one day it will turn into a released add-on :)

http://twitpic.com/5kdv17/full - early screenshot of the sql file generator based on schema and changes since last deployment http://twitpic.com/5kdk8b/full - early screenshot of a spreadsheet with our db notes regarding migration possibilities

[bliswebagency]

That spreadsheet is gold! It would be great if EllisLabs provided that sort of documentation; I'm sure it would lead to some further innovations in this area.

As far as using a remote DB goes; I setup a QnD server on the weekend and initially found it sluggish (as always) until I switched on SQL caching that brought the whole thing to life. I'm assuming you would have had to do the same thing - have you found any consequences to this?

[erikreagan]

We actually don't have SQL caching enabled. We disable all forms of caching during development.

Our front-end pages load in an average of 1-2 seconds and our CP pages load in about 5-7 seconds.

[bliswebagency]

That makes sense. With things actively being changed by other developers in the background, you can't afford to pull from the cache.

It does makes you think though; some form of "clever caching" could prove to be a solution to this remote db speed problem. It would have to involve logging each new change to the db and reflecting this in some way to all the other users also using that db. Then, if designed well, EE would pull only the changed data from the db and retrieve the rest from cache.

It's probably something EllisLabs would need to take on at a core level but it would be interesting to see if something like this has been implemented in other systems.