Hi,
sorry if this is not the best forum to post my question, but I did checked all and was unsure in which one to post this type of wondering
This is something I been wondering for years and decided now to ask the community since per my real professional experience (big banks and insurances) maybe been unlucky but never saw a full integrated source control organization/products/etc to simple have both production deliverables vs all those non production deliverables we create like .sql test scripts and/or excels mapping etc..
The few organizations I been working - complex IT environments - for some years each, they only care to source control (doesn't matter GIT, SVN, etc.. as the focus of my post is to request ideas from the community in:
"how about all the other important non-production stateful objects and/or implementation helper scripts like test scripts, excels mappings, even doc specs, and/or even quickstart how to setup development environment to make life easy for a new developer to jump in and learn best practices".
Example, I just completed 1 year long project with 4 productions releases over time of a complex MDM project (night batch that loads four different schemes in 3 different DBs but similar entities into a new new of tables), in the end production deliverable s were just 2 packages 1 called by the night batch job to reload the routing tables and 2nd package contains search procedure called anywhere from the java).
But.. I got some 50 auxiliary files (like devtest .sql scripts, 2 excel mappings containing all specs into dev readable low level way etc).
For example, all load procedures in the data load package exist as external .sql scripts (1 to 1) during development, as I'm using only pure SQL insert/selects/dml logging transformations.. as any developer can just select and execute the statement rollback/commit etc.. and simulate the data load step by step when debugging.
Enabling debug in sql developer its ok in other type of packages having more complex PL/SQL, and there one can use breakpoints etc.. but which is not the case of my data_load package. (I always prefer a SQL only way to process data -much faster and a developer able to simply select and execute F9).
Anyway.. so the development of my data load package goes like:
1. develop/test each data_load procedure .sql script
2. finally copy the .sql inside the package.
Please try focus your suggestions and ideally real life experience in just the main question/wondering im having that is where/how/to maintain the non-production files.. So the structure I defined is:
dev_ROOT folder
quickstart <-- contains 1 excel explaining a new developer how to setup SQL developer, source code origin and explain the dev/test process (avoiding whats common in house which is nothing exists except production code...)
mappings folder <-- contains 2 excels TECH_INFO.xlsx and the MAPPINGS.xlsx (dest vs 4 db source field by field mapping, data type, etl rules etc..)
devtest_scripts folder <-- a bunch of 20 .sql scripts that help find data patterns in the 4 source dbs specific for 7 business rules Y/N flags we pre-calculating)
SOURCE <--- all the .sql procedures scripts - 1 .sql per package procedure
DELIVERY_NOV
the 4th final production delivery folder that only contains in THE SAME STRUCTURE AS THE BITBUCKET/GIT SERVER BUT ONLY THE SPECIFIC FILES OF THE PROJECT (BLUES). SINCE IN THE GIT ROOT SERVER IT CONTAINS THOUSANDS OF FILES, AS EVERYTHING THAT EXIST IN THE DATABASE. Example the PACKAGES folder I only have the 2 only project package but obviously the GIT_ROOT/oracle_technologies/database/PACKAGES/ contains hundreds of packages.
1 .sh to compile both data_load and the search packages
git_root
delivery
2017
11-ent.rel.
12-ent.rel.
2018
1-ent.rel.
JIRA_ID folder
runin.sh (only compiles 2 packages)
2-ent.rel.
stateful
packages
data_load package
search package
procedures
views
types
(etc..)
We just moved to GIT and the way we delivery oracle code is by spliting scripts into 2 folders IMPLEMENTATION and STATEFUL:
git_root
IMPLEMENTATION <-- shell script that compiles/execute ddl/dml/sqlplus/etc scripts
2017
11-ent.rel.
12-ent.rel.
2018
1-ent.rel.
JIRA_ID folder
runin.sh (only compiles 2 packages)
2-ent.rel.
STATEFUL <-- all oracle objects in production
packages
data_load package
search package
(..)
procedures
(..)
views
(..)
types
(..)
(etc..)
Sorry for the lengthy post.. I wanted to clear define the context.. and also being i been frustating in past 5 years in which every single shop i work I never saw any solution for the "where to keep the non-production files" problem.
The problem I'm facing is and that's why I would like to ear your real life experience and suggestions (given its a complex IT environment) is the supposedly people responsible to proper define a full solution don't do and/or provider a proper solution (they say "doesn't matter where you/we keep all non-production files", "put somewhere like in our wiki.. or create a sub-folder inside git_root>delivery>2018>1-ent.rel>JIRA-ID>this_folder_contains_all_non_production_stuff
Both above 2 "mad" ideas don't make sense.. 1st spreading in some disorganized wiki (which is what we have) is not solution in my opinion.. you can put docs there but there's .sql scripts and pure development scripts that developer needs easy way to pull when setting up a new fresh dev/environment to be able to work on this process for example. 2nd creating sub-folder (that DBA ignore when implementing the JIRA-ID in production) would make sense for localized aux files.. but again, in my case i have some 50 auxiliary scripts that constitute the full dev/test universe for this complex process (that in the end is just.. 2 packages).
Look forward for constructive suggestions.
FYI: we use SQL Developer and we just moved to use GIT.
Kind Regards,
Emanuel O.
Oracle Developer Sr.