Joomla Stack Exchange is a question and answer site for Joomla! administrators, users, developers and designers. It only takes a minute to sign up.
Sign up to join this community
I work in a development / staging / production environment and I've run into some challenges promoting code from one environment to another, especially when upgrades to Joomla and other third party software are required.
Is there a way to find out which sql statements have been run against the database since a certain date so that I can apply them in different environments, without having to upgrade each environment?
Simple answer is no.
Extended answer is mysql can log queries, however it has limits, and if there is even one difference between the environments it would lead to those queries not functioning. Even with that the only way to do it is though the server itself, since as far as I am aware there is no special logging added to JDatabase (Also if a component does not use JDatabase then the control lowers even more).
With the above you can see in a perfect world, there is a way, however if you move from dev to live, you currently had a live site that was doing queries on its own and it is "different" then the current dev/staging version. So if a user signed up on the live site, and you upgraded Joomla in dev, then the update to the users table would add the need to manually re add that user (there could also be changes to the table structure). Now imagine its not one user, but 10, or even 100, and the process cannot be automated.
Joomla has some nice updating features, allowing a version for version mysql update on the tables, this ensures the tables keep the structure the queries need, however it does not track any inserts or updates on the tables, so if you update in a dev environment, the best way to update in production is to just follow the same steps and update in your production as well.
In the future it may be possible for Joomla to track database changes in its core, allowing you to sync a production version to a development version, however the amount of work to build something like that is crazy, and would have an impact on performance. Right now I do not believe there is, or should be any plans for it.
Databases tends to be a big problem with all software development. Keeping things synced as a major pain for us too especially with staging and production sites.
One thing I have started to look at lately but not yet implemented is:
Another 'poor mans way' is to export the DB schemas (if you're concerned for schemas, data is probably a different animal) and then run a diff on the schemas for each database. Should reveal the differences between pretty easily. Then you just have data consolidation to do. With that I would tend to say, ideally you should only need to worry about data on your production environment and keep it synced with the staging environment. Then you can sandbox and play around in development as needed.
