I am thinking of marketing a script I have created to upload changes automatically. It uses public/private key authentication between a local computer and a host server to get in through SSH and upload changes to a MySQL database on the host for a given CMS system (i.e. WordPress, Concrete5, TextPattern, etc.).
Do you develop locally using your own copy of Apache and MySQL before uploading changes stored in MySQL to the web host database?
Usually, yes.
But the local data may not be the same as on the live server as most “development” involves changes to the markup or logic code, not changes to the db structure. If changes to the db structure are needed, I definitely trynthem out locally first!
If you deveop on public hosting then you’d need a password protected area to develop in so that the general public can’t see the broken scripts that you are still testing.
Much easier is to develop on your own system first and then upload once you know it works/
Thanks for your input TheOriginalH (don’t know what else to call you :)). If I may ask…when you upload changes to the DB do you do it manually? As in opening up a local copy of phpMyAdmin (or other such program), dumping the DB contents as SQL, opening up phpMyAdmin on the host server, and then run the SQL there? How do you upload the changes to the DB?
Are you using Windows (WAMP) or Linux (LAMP) for local development or perhaps Dreamweaver or otherwise? What is your local development process of choice? I suppose some of these questions might make for another poll but any additional input you might care to share would be appreciated as I am trying to determine the viability of marketing an automatic upload script and what the target market of potential buyers might be.
Carlos
I completely agree with you Stephen. I guess I am quite clueless though as to how many people actually do that as opposed to using a more hodgepodge approach to their web development. I don’t know how many people actually have the werewithal to set up their own Apache, MySQL, PHP stacks (either under Windows or Linux).
I personally imitate my hosting setup under Linux as much as I can and develop locally (under Linux) until and if I want to upload changes. I was getting sick and tired of uploading changes manually so after figuring out how to get public/private key authentication working on my host (not all shared hosting services offer that) I then took the time to figure out how to write a PHP script to upload changes automatically for me.
One needs public/private key authentication for this to avoid having the script execution interrupted by the need to login manually to a command shell (using SSH) on the host server.
Before I figured out how to use public/private key between me and the host server I had two scripts that did the work with me having to login in to the shell between each script’s execution (the one script dumping the local DB to SQL and uploading that through FTP to the host and the other script being run at the host server, once I was logged in through SSH, to import that uploaded SQL into the host DB).
Carlos
I try to develop locally for a variety of reasons. It’s faster (I don’t have to wait for uploads) and LOTS safer because I don’t have to worry about anything that I do touching the production side until it’s completely ready to be there.
Most stuff I work on is local first, then I deploy to a test area on the production server (to catch any issues that might arise because of the differences between my local system and the server), then I go live.
Regarding SQL, if there are changes to the structure itself, and the site is busy, I will take the site offline for a couple of minutes while the changes are made, if they are small. This is assuming that there is user generated content (be that shopping, posts, whatever). Changes are made using either the MySQL command line interface, or phpmyadmin. As far as the data goes, unless it is all controlled by the site (simple cms with no user input), there are few occasions where I would repopulate with local data.
My current dev environment is mamp. I use a mac with Lion, Apache, php and MySql installed, BBEdit for coding, yummyFTP for file transfer. Having said that, I have developed on Win and Linux over the years, and only recently stopped using the Linux box as a staging server (hippy environmentalist in me).
I work freelance and have had the good fortune to work in a fair few full time studios. I would say that there is probably a 50:50 split on average as far as environment goes between Win and Mac, Linux dev is out there, but less frequent.
So, my dev process currently (and I don’t claim for a second that it is the most efficient method) is:
Develop site on local machine (if major amends are being made, a zipped, dated backup is made). Test on local machine, when happy, upload to live environment. Previously, as I said, I did have a Linux box set up in much the same way as server as well, but have found that largely unnecessary recently as sites have been relatively simple.
For one project recently, the entire process, including collaborative changes and upload was managed with Git, which is probably a far more sensible way of working, but there is obviously a learning curve, and unless you are working with developers familiar with it, can be a headache.
I have yet to use subversion, but it is certainly a common approach in some studios and something I must get my head around soon.
I have an Ubuntu server running in a VirtualBox on my computer. I use that as development/ test server. Doing it this way means it’s portable(ish), if I mess something up with the server config I can easily reinstall the machine again without affecting the actual computer itself. Plus it keeps a bunch of software I don’t need for day-to-day stuff (listening to music, watching videos, playing games) off my machine.
I mean, who needs MongoDB or nginx to watch Fringe? 
Interesting follow up input from you TheOriginalH! Thanks very much.
I never heard of MAMP and did not even know that the usual MySQL, Apache, and PHP could run under a Mac.
And I’ve never heard of anyone using GIT to upload changes to a live hosted site. That kind of makes sense but I have just never really delved into that. I’ll have to investigate that as it does sound interesting.
One other point you bring up that I suppose I should think about more is the need to avoid conflicts between say a user making changes through phpMyAdmin at the hosted site and my script, for example, being run from my end and either overwriting those changes or causing a conflict of some sort networking wise.
Up to know my clients have been the kind that simply don’t make changes for themselves and I’ve had cart blanche to do what I want when I want but that certainly is not always the case and it behooves me to take that into account in comtemplating the marketing of my automated upload script.
Another very valuable thing to know that you alluded to TheOriginalH is that any automated script to upload DB changes automatically is of limited value after a site goes live in that one would not want to repopulate the site with local changes at the risk of overwritting any changes made by the client through the web CMS interface.
Thanks again for the great input TheOriginalH! Much appreciated.
Carlos
Anyone using windows, linux, or Mac can do it easily simply by installing either the XAMPP package for their platform or alternatively WAMP, LAMP, or MAMP (all of which are free downloads).
It will not give an identical platform to what is running on the server but it will allow most of the testing to be done locally.
It’s been my experience with clients Stephen that most all of them can’t do it. I mean I suppose they could if they spent hours and hours figuring out how but there is more to installing these things than just running a WAMP, LAMP, or MAMP with respect to setting up a named domain testing environment locally (though these packages of course make it much easier than it would be otherwise).
Web developers can for sure Stephen. I am just saying installing a local development environment is not as easy as these packages might lead one to believe it is.
I’ve had clients in the past who didn’t even know how to start up the Windows Notepad program!
Carlos
My Linux box has LAMP, as well as ColdFusion 9. I develop everything locally, then upload it to my host. Even though it’s not an identical platform, I’ve never had anything work locally and fail on the host unless it was a CF tag that the host had disallowed.
I have a follow up question for those of you following this thread…I wish I could put a poll within a poll but since I can’t…I’ll just ask my question…
Since all of you develop locally before uploading changes (that seems to the unanimous way of developing) would you consider it worthwhile to run a script that would automate completely the uploading of MySQL changes to the web host? Where you could just run the script and instantly have your changes uploaded to the host without you having to do anything else?
Needless to say such a script would only apply to CMS systems using the MySQL database to store content (i.e. WordPress, Concrete5, Drupal, etc.)
I refer only to MySQL changes. The script could be enhanced to allow for more than just DB changes but for now I just want to know about DB changes only.
If such a script would be useful to you…would you pay for such a script? Why or why not? If you would pay for such…how much would you be willing to pay?
Any further input would be appreciated as I have created such a script and am doing some market research to determine whether it is a viable thing to market and if so what a good price point might be for it.
Thanks.
Carlos
Whenever I am developing something that requires database changes the first thing I do is to write a script that automates the changes to the database. This makes it possible to test the database update itself locally first and to then be configdent of oit working correctly once the new version is implemented. At the same time as uploading all of the other changes to the live hosting I upload and run that script and then delete it from the live hosting.
This is even more essential when the changes relate to a script that I sell as then anyone upgrading to the new version of the script will also need to run th the database update script. I do it for all applications though whether they are being sold for use on multiple sites or just used on one site. It makes things much easier if it were to become necessary to restore the database from prior to the update to be able to reapply the update.
For you then Stephen…buying such a script would not be worthwhile (since you write your own).
The script I wrote does not get uploaded to the host server. Ever. It basically dumps the local MySQL database to a text SQL file, uploads that file to the host server through an SSH shell (requires that public/private key authentication not only be supported by your web host but be set up), then continues to run once connected through the shell to import the SQL file that was uploaded into the appropriate MySQL database on the server.
Without setting up public/private key authentication through SSH such a script would not fully automate the process of uploading changes to the DB since one would have to manually sign in to the SSH shell using a username and password before the process of uploading changes could continue.
The only thing that gets put on the sever is the SQL text file which is full of MySQL commands to recreate the database.
I could easily add an additional command to the script that will delete the file once it is used but have tended to leave it up on the server as an extra backup of my database.
Carlos