Sore about the source code of database objects

Imagine this situation: a development team is working on a program. In this case, the source code of the application is not stored anywhere. Each programmer, using a special decompiler, unloads the necessary code from the binary, works with it, and then reassembles it and gives it to colleagues for further development.
Do you think this is a normal situation? I think no.
But for some reason, this approach is often used when developing database applications.

Objects are created and changed "gain" directly in the database. Most specialized IDEs provide a lot of “convenient” tools for this - finding the desired object using the navigation tree, modifying it with a few clicks, etc. At the same time, few people think about the source code - and when building the version, utilities are often used that, based on the code of the current database and the production base code, generate a differential script (it still remains a mystery to me, because alter (tables for example) can form in a variety of ways, it all depends on the particular case, on the logic of change, etc.).
It turns out that the source code of the database, as it were, does not exist, it is considered a kind of machine code that is difficult for human to perceive and is completely at the mercy of visual development tools. The programmer does not work with the functionality of his DBMS, but with the functionality of a specific IDE, a kind of beautiful and (as it seems the first time) convenient intermediate layer. At the same time, you have to study the IDE, and not the DBMS itself.
It’s worth remembering that after using an actual written code (verified, formatted by corporate standards, formatted with comments) to create a database object, this code will not be saved anywhere, you will lose it, and in return you will receive what the machine collects for you ( IDE, special utilities or libraries), based on a database data dictionary. At the same time, the history of changing objects is lost (it becomes impossible to find out / remember who, when and why created / deleted / modified this or that object), conflicts can arise during joint development and much more.

You can read about this in the PLSQL Standards Developed for the PLSQL Starter Framework in the Source Code Control chapter:

Remember to never modify the PL / SQL stored in the database. Work from the source code file instead. Yes, modifying the compiled code inside the database is technically feasible, but a really bad idea. It is comparable in some ways to modifying Java bytecode or C object files .

and in the chapter “Data Models and DDL”:

Most DDL is still created by hand. Make it clean, commented and readable, just like source code .

Finally, I will give the code for creating a simple table in Oracle and what it gets from it after unloading from the database with various utilities.

The source code for creating the table: DBMS_METADATA: Tora: TOAD Eclpise plugin: As you can see, the hodgepodge is complete, who cares.
-- This is test_table
CREATE TABLE test_table (
id NUMBER(38), -- PK
value NUMBER(10,2), -- main value
width NUMBER(5,0) -- main width value
);



CREATE TABLE "OBJECTMAN"."TEST_TABLE"
( "ID" NUMBER(38,0),
"VALUE" NUMBER(10,2),
"WIDTH" NUMBER(5,0)
)



CREATE TABLE objectman.test_table
(
id NUMBER(38)
, value NUMBER(10,2)
, width NUMBER(5)
)



create table test_table
(
id number(38),
value number(10,2),
width number(5)
)



Of course, this does not mean that you need to completely abandon modern visual tools and work in a console editor. The most important thing is to work with the source code of your database application, control it yourself, and not give up the machine. The source code of database applications is no worse than Java or C ++ application code and requires the same treatment: formatting, comments, version control, etc.

A few recommendations:
1. All database code (DML, DDL, DCL, TCL, etc.) should be stored in the repository (one object = one file);
2. Based on the first paragraph, it should be possible to collect a "clean" base "from scratch" ready for use, using the sources from the repository (there is no need to make database dumps, cut out unnecessary (test) data, etc.)
3. You need to edit and compile the code of the database object from the file (that is, instead of the navigator for the database object of your IDE, the project file navigator in the repository will become relevant - find the file, open for editing, roll to the database).

Also popular now: