kysuc - keep your schema under control
TRANSCRIPT
![Page 1: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/1.jpg)
KYSUC
Keep Your Schema Under Control
04.FEV.2016
Nuno Alexandre Alves
![Page 2: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/2.jpg)
Speaker
• Nuno Alves
• Born at Coimbra / Lives in Leiria
• Degree in Informatics Engineering (ESTG-IPLeiria)
• Some experience with databases
• Nowadays working in Critical Software, SA
nuno.alexandre.alves
![Page 3: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/3.jpg)
Disclaimer
This presentation is:• From my personal experience• My own suggestions
This presentation is NOT to:• Pointing fingers• Enumerate errors• This is better than that• Do not use this
![Page 4: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/4.jpg)
Just another day in “paradise”
• How do you develop your scripts ?
• Do you have a ER model of your Database(s) ?
• Do you have your Database documented ?
• How do you control your scripts versions ?
• Do you have your test data under scripts ?
• How do you test your scripts ?
• How do you deploy your application and database to a specific version ?
![Page 5: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/5.jpg)
Agenda
1. Goals
2. Diagrams (ER models)
3. Scripts
4. Deploy
5. Documentation
![Page 6: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/6.jpg)
Goals for Today
• Updated ER model
• Scripts versioned
• Easy Deploy
• Easy Documentation
• Schema Under Control !
![Page 7: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/7.jpg)
Agenda
1. Goals
2. Diagrams (ER models)
3. Scripts
4. Deploy
5. Documentation
![Page 8: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/8.jpg)
ER Model (1/4)
Start New Project (from requirements)• Build your Conceptual Model• First Physical Model• First Scripts (version 1.0.0)• Print (paper not PDF) your Physical Model and put it
in a Wall next to the team (old-school but it works)
Assumptions:• High Level Architecture designed• RBDMS almost decided (for the first scripts)
![Page 9: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/9.jpg)
ER Model (2/4)
Start New Project (already in PROD) / Maintenance• Ask for actual documentation (get lucky )• DDL of actual production database• Import DDL in your development servers• Reverse Engineering• Build Conceptual Model (from Physical) if
necessary• Script Physical Model with actual application
version (ex: v3.5.2 – it’s your baseline)
![Page 10: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/10.jpg)
ER Model (3/4)
Development / Maintenance
• Keep your database changes updated in your ER Model
• Use Reverse Engineering to do checkpoints (compare)
![Page 11: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/11.jpg)
ER Model (4/4)
Tools
• Use a Case Tool
• PowerDesigner
• Enterprise Architect
• …
![Page 12: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/12.jpg)
Agenda
1. Goals
2. Diagrams (ER models)
3. Scripts
4. Deploy
5. Documentation
![Page 13: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/13.jpg)
Scripts (1/10)
Scripts Development (projects reality)
• Manually
• I don’t have scripts. My ORM tool does it for me !?!
• Database is something to store some data on it. I don’t care.
![Page 14: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/14.jpg)
Scripts (2/10)
Scripts Development (projects reality)
• WTF ?!
• Your medical records relies on a database system
• Your bank account relies on a database system
• Your salary relies on a database system
• …
• Your life relies on too many database systems
• Take your own conclusions
![Page 15: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/15.jpg)
Scripts (3/10)
Then someday• How do I execute the database scripts ? Order By ?• Which development database is updated ?• How do you develop scripts ?
• One script vs multiple scripts ?• Full Scripts vs Partial Scripts ?• It’s your ORM your Database developer ?• Naming Conventions for objects (where is the guide?)
• I have a app log with this error “constraint SYS_098374 violated”. What is that constraint ?
• The column CGTRTHY stores what ?
![Page 16: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/16.jpg)
Scripts (4/10)
Database Scripts Under Control• Why ?
• Is your source code under control ?• Does your source code have versions ?
• Database scripts = source code ?• I think Yes !
• Table definition• Table initial data• Function / Stored Procedure / Package
![Page 17: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/17.jpg)
Scripts (5/10)
Decide your Database Storage Strategy• Check production for rolling projects• Decide according to hosting supplier or client needs
• Tablespaces• Data files• File groups
My Suggestion (minimum)• One Tablespace for Data• One Tablespace for Indexes• One Tablespace for LOBs
![Page 18: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/18.jpg)
Scripts (6/10)
Decide your Database / Schema(s) architecture• One schema, multiple users with diff grants• Multiple schemas, one/multiple users
My Suggestion• Keep it simple• One schema/database, multiple users (grants/views)
• FKs between multiple schemas not supported in all RDBMS• Distributed Transactions (do you really want ?!)• Multiple queries (joins in app server)
• Are you creating your own RDBMS ?!
![Page 19: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/19.jpg)
Scripts (7/10)
Decide your Database Naming Conventions• Easy identify a database object• Name every object in database
• Don’t let default names under RDBMS
![Page 20: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/20.jpg)
Scripts (8/10)
Script like source code• Use SQL formatting (for human readable)• Comment your SQL• Define objects language: PT; EN; FR … (don’t mix)
Script for documentation• Comment every object (mandatory: wait and see)• Keep documentation in database dictionary• Useful for Reverse Engineering• Useful for Automatic Documentation
• COMMENT ON TABLE• COMMENT ON COLUMN TABLE_NAME.COLUMN_NAME
![Page 21: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/21.jpg)
Scripts (9/10)
Tools• Manually (Notepad, VI, … is your friend!) • Liquibase• mybatis• MIGRATEdb• migrate4j• Flyway • …
![Page 22: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/22.jpg)
Scripts (10/10)
Scripts Under Control ?
![Page 23: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/23.jpg)
Flyway (1/6)
Overview• Scripts: SQL, Java• RDBMS Agnostic (JDBC driver)• Inside your source code• Placeholders (variables)• Multiple Execution Modes
• Java API• Maven / Ant• Command line
• Ex: V2_0_0_1_0001__script_name.sql
![Page 24: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/24.jpg)
Flyway (2/6)
Decide your Script Naming Convention• V<JIRA_FIX_VERSION>_1_<4_digits>__<JIRA_KEY>.sql
• <4_digitos>• Execution order
![Page 25: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/25.jpg)
Flyway (3/6)
Put some code in your Script• Add an header to your SQL file
• Add JIRA_KEY or JIRA URL as a comment
• Commit
![Page 26: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/26.jpg)
Flyway (4/6)
Decide your Execution Mode• Command line (java -jar …)• Maven/ANT
• configure inside your IDE or in CLI
• Use Maven profiles is necessary (useful in multiple databases/environments)
![Page 27: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/27.jpg)
Flyway (5/6)
Flyway in action• flyway:info
• Check schema version already installed
• You need flyway:baseline for the first time
![Page 28: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/28.jpg)
Flyway (6/6)
Flyway in action• flyway:migration• Script execution
• With/without target release
![Page 29: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/29.jpg)
Flyway
In Action
![Page 30: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/30.jpg)
Agenda
1. Goals
2. Diagrams (ER models)
3. Scripts
4. Deploy
5. Documentation
![Page 31: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/31.jpg)
Deploy (1/7)
Deploy Database Scripts• Do you test your scripts ? (how many times)• Do you re-create your schema from scratch ? • Do you have automatic deployment for your app ?
• And your database ?• Do you have night builds ?
• They include database too ? and automated tests ? • Do you simulate your client releases in development
environments ?• Do you have confidence in your own scripts ?
![Page 32: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/32.jpg)
Deploy (2/7)
Deploy Database (full / upgrade)• Use your Continuous Integration
• Jenkins
• Team City
• Bamboo
• Other …
![Page 33: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/33.jpg)
Deploy (3/7)
Jenkins + Flyway = easy database deploy• Why Jenkins ?
• Easy to configure• Parametrized builds• Many plugins (CVS, SVN, Maven, JIRA, Email, …)• Scheduler• Live Build Logs / Console output directly in browser• Why not ?!
![Page 34: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/34.jpg)
Deploy (4/7)
Jenkins + Flyway = easy database deploy• Configure a Jenkins Job
• Checkout script from VCS• Put some parameters
• if necessary for customs build• for multiple versions• for multiple databases
• Execute Flyway• via Maven • via custom Shell Script / Windows batch
![Page 35: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/35.jpg)
Jenkins + Flyway
In Action
![Page 36: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/36.jpg)
Deploy (5/7)
Continuous Monitor your database• Oracle Enterprise Manager (Oracle)• pgwatch (PostgreSQL)• db2top (DB2)• SQL Server Management Studio (SQLServer)• MySQL Enterprise Monitor (MySQL)• …• Check administrator views (available in every RDBMS)
![Page 37: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/37.jpg)
Deploy (6/7)
Continuous Monitor your database• Check some random SQL Statements
• CPU• I/O
• Get query plan for these statements• Help development team write better SQL• Create indexes according to business needs
• Monitor sessions• Check Connection Pooling parameters
• Min / Max Pool sizes (warn: multiple Connections Pools)
![Page 38: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/38.jpg)
Deploy (7/7)
Help Development Team to help yourself• Write SQL Statements to log (debug mode)
• Ask for log4jdbc / P6Spy / … (with parameters)• Write query times to log• Analyze top time consuming queries• Analyze top running queries
• Ask for application times vs database times (in log)• Most of times the application consumes more time (yes it’s true)• Try to analyze how many queries are made for a business operation• Check the number of columns retrieved in every query (it’s really necessary 80
columns ?)
![Page 39: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/39.jpg)
Agenda
1. Goals
2. Diagrams (ER models)
3. Scripts
4. Deploy
5. Documentation
![Page 40: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/40.jpg)
Documentation (1/3)
How do you produce your database documentation ?• Manually• I don’t have database documentation• “My client doesn’t need this documentation”
• And your team ?• How do you maintain a project without documentation?• Most (~All) of your production tickets are resolved by SQL
statements !• Client business data is stored in “your” database (really?!)
![Page 41: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/41.jpg)
Documentation (2/3)
Automatic Documentation• Use RDBMS data dictionary
• information_schema.tables, …• user_tables, …• syscat.tables, …• Remember COMMENT ON … from scripts ?
• “Export” to something useful• Word Document/PDF ? • Who reads a 800 pages document ?
![Page 42: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/42.jpg)
Documentation (3/3)
Automatic Documentation• Perl script (reads data dictionary, outputs One HTML file)• Send to your client as a deliverable• Publish in your wiki• Open locally in your browser
• Produce custom CSS: 30m - 1h• Update Documentation: 1 minute
• Remember COMMENT ON
![Page 43: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/43.jpg)
Summary (from initial goals) (1/2)
ER Model• Use a Case Tool• Keep your model updated
Scripts versioned• Use Flyway / Liquibase / Other• Script everything (data tests included)• Review your scripts
• Naming Conventions• Comments
![Page 44: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/44.jpg)
Summary (from initial goals) (2/2)
Easy Deploy• Use Jenkins / Team City / Bamboo / Other• Keep your database under Continuous Integration• Schedule your database builds• Parametrize your database deploy• Monitor your database close• Release Matrix isn’t nice to have (is mandatory)
Easy Documentation• Comment your objects in data dictionary
![Page 45: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/45.jpg)
Main Goal
Updated ER Model + Scripts versioned + Easy Deploy + Easy Documentation
= KYSUC
![Page 46: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/46.jpg)
Extra Credits
Define / ask for backup/restore plan• Test your backups (restore is your friend)• Check backup/restore times• Check database growth
Compare data• Compare your production data with development data:
reference data• Try to equal PKs IDs across environments (for referencial data)
![Page 47: KYSUC - Keep Your Schema Under Control](https://reader033.vdocuments.us/reader033/viewer/2022051503/586fe2211a28ab18428b7a59/html5/thumbnails/47.jpg)
That’s It!
Thank you for your time.