Markhof, Ingolf <ingolf.markhof@xxxxxxxxxxxxxx> writes: > Tom, all, > > when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL client) marks views that refer to the table using the old column name as erroneous. So, I can easily identify these cases. And of course I, as a user, I am acting in my context, i.e. my schema. So it is perfectly clear what table I am referring to. > > Please note: I am not developing any PL/SQL code. I don't have big development projects. I have more the role of an data analyst. I just create rather complex SQL queries which, from time to time, may need to be adopted to some new requirements. Or peers want to (re-) use (part of) my SQL queries. There is not really much versioning required. > > What I understood so far is: I can use e.g. DBeaver to interact with PostgreSQL, to develop my SQL code. But I finally need to copy the SQL code into e.g. Github. Which is a manual process. I'd mark the SQL code in the DBeaver editor window and copy&paste it into some file in e.g. GitHub. Using Github, I'd get version control and other enhanced collaboration features which I don't really need. At the price that code transfer from SQL (DBeaver) to the code repository and vice versa is complete manually?! This doesn't really look like an enhancement. > > Most likely, there are more professional ways to do that. I'd be glad to get advice. > > What I would like to have is something that would automatically update the SQL code in the software repository when I run a CREATE OR REPLACE VIEW. > Even with Oracle, I found it works much better to keep all your DDL/DML in files stored within the OS file system. This approach also works fine with tools like DBeaver, pgAdmin, etc as all of these files also support working with files. The main benefits I find with this approach are - 1. Adding version control is easy. Doesn't matter if it is git, hg, bzr, svn or rcs - any version control system works fine. It doesn't have to be a cloud service like github, though some sort of centralised repository can be useful for managing things like backups and sharing code across a team (I've used gitlab hosted locally t great success). Most editors also have built-in support for common version control systems, so the additional overhead associated with using a version control system is very little. 2. Having all your code in version control makes tracking changes trivial. This is often really useful in tracking down problems/bugs caused by a change and other diagnostics. More than once, I have found I've gone down a bad path of changes and want to restore a previous version. Assuming you use your version control system appropriately, this becomes trivial. If your code is only in the db, once you make changes, the old code is gone and cannot easily be restored. 3. Having all the DDL/DML in files makes data migration very simple. I will typically have a development environment where I develop my DDL/DM which is separate from the production environment. This can be very important even in data analysis and data mining type applications as it allows you to develop complex and possibly resource hungry DML in an environment where mistakes won't impact production systems. It also means you can have a dev environment which is populated with specific data sets which have been defined to help in the development process e.g. perhaps smaller, so tests run faster or perhaps ensuring all possible data permutations are included etc. If all your DDL/DML are in files, seting up a new environment is as simple as writing a basic script and using psql (pg) or sqlplus (oracle) to load the DDL/DML. In simpler environments, you can even use a naming scheme for the files which sets the order - loading the data then becomes as easy as 'psql *', avoiding the need to write scripts (even though writing the scripts is typically trivial). 4. Having all your DDL/DML in files allows you to use the many powerful text manipulation tools which exist on most platforms. While such tools are not often required, when they are, it can be a real bonus. Being able to use tools like sed, awk, perl, etc have saved my bacon more than once. Over the years, I have also built up a very useful library of techniques, templates etc. Being able to quickly and easily access this library is very useful. 5. While the built in editors in tools like DBeaver and pgAdmin are OK, I find they are rarely as good as my preferred editor and I often get frustrated at having to know/learn the editors of different tools. Having everything based on files means I can use my preferred editor, which has support for things like completion, familiar syntax highlighting and key bindings, templates etc. These days, many editors even have built-in support for popular databases like pgsql and oracle, so you can load the code and run it without having to leave your editor. Having the code in the database can be useful. I've used this in oracle to provide enhanced debugging support when developing stored procedures and packages and enhanced reporting in unit testing. However, you really don't want this to be the definitive master for your source code. Your source code benefits from being in a location which makes backup, restoration and migration easy/fast, where changes can be tracked, analysed and rolled back, where re-factoring can use advanced tools and can work across projects, not just on a single script, where code sharing is easy and where people can use their preferred tool rather than being forced to use something which understands the database. -- Tim Cross
