refactoring data base parte 2
TRANSCRIPT
![Page 1: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/1.jpg)
Refactoring Databases2
Por: Ismael Soares
![Page 2: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/2.jpg)
Agenda
• Objetivo• Dicas e considerações• As categorias de refactoring• Exemplos práticos
![Page 3: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/3.jpg)
Objetivo
Apresentar estratégias de Refactoring Databases e alguns exemplos práticos de cada categoria.
![Page 4: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/4.jpg)
Nenhuma arquitetura deve ser tão rígida a ponto de ser inalterável.
Dicas e considerações
![Page 5: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/5.jpg)
Não se iluda! Pequenas melhorias sempre irão acontecer.
Dicas e considerações
![Page 6: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/6.jpg)
Dicas e considerações
Alterações menores são mais fáceis de aplicar.Implementar uma mudança grande em partes pequenas.Identificar as refactorings de forma individual.
Controlar as alterações fará muita diferença.Criar uma tabela de configuração do banco de dados.Atualização ou sincronização em lote.Escolher um período de depreciação/transição suficiente.Encapsular o acesso ao banco de dados para não duplicar SQLs.
![Page 7: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/7.jpg)
As categorias de Refactoring
1. Estrutural
2. Qualidade dos dados
3. Integridade referencial
4. Arquitetura
5. Métodos
6. Transformação sem refactoring
![Page 8: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/8.jpg)
Estrutural
Drop ColumnDrop TableDrop View Introduce Calculated Column Introduce Surrogate KeyMerge ColumnsMerge TablesMove ColumnRename ColumnRename TableRename ViewReplace Large Object (LOB) With TableReplace ColumnReplace One-to-Many With Associative TableReplace Surrogate Key With Natural KeySplit ColumnSplit Table
![Page 9: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/9.jpg)
Estrutural – problemas comuns
Selects em viewsDefinições de TriggersDefinições de Procedures
Tabelas (foreign key)
![Page 10: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/10.jpg)
Estrutural – problemas comuns
Período de transição
Triggers circulares
![Page 11: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/11.jpg)
CREATE OR REPLACE TRIGGER SynchronizeColunaTabelaToTabela2 BEFORE INSERT OR UPDATE OR DELETE ON TABELA REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE BEGIN IF DELETING THEN delete from tabela2 from where id=:new.id; END IF; IF INSERTING THEN jaTemRegisto number := (select distinct 1 from tabela2 where id=:new.id); IF NOT(jaTemRegistro = 1) THEN insert into tabela2(id, coluna) values(:new.id, :new.coluna); END IF; END IF; IF UPDATING THEN IF NOT(:NEW.coluna = :OLD.coluna) THEN IF (:NEW.coluna IS NOT NULl) THEN update tabela2 set coluna = :new.coluna where id= :new.id; END IF; END IF; END IF; END;
Estrutural – problemas comuns
![Page 12: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/12.jpg)
Estrutural – Renomear coluna
![Page 13: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/13.jpg)
Estrutural – Rename Column
![Page 14: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/14.jpg)
Estrutural – Introduce Calculated Column
1. Determine a estratégia de sincronização.2. Determine a seleção que fará parte do calculo.3. Adicione a coluna.4. Implemente a estratégia.
![Page 15: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/15.jpg)
Estrutural – Introduce Calculated Column
![Page 16: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/16.jpg)
Estrutural – Merge Column
![Page 17: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/17.jpg)
Estrutural – Merge Column
![Page 18: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/18.jpg)
Estrutural – Merge Table
![Page 19: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/19.jpg)
Estrutural – Merge Table
![Page 20: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/20.jpg)
Estrutural – Merge Table
![Page 21: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/21.jpg)
Estrutural – Move column
![Page 22: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/22.jpg)
Estrutural – Move column
![Page 23: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/23.jpg)
Qualidade de Dados
Add lookup Table (*)Apply Standard Codes (*)Apply Standard Type (*)Consolidate Key Strategy Drop Column ConstraintDrop Default ValueDrop Non-NullableIntroduce Column Constraint (*)Introduce Common Format (*)Introduce Default ValueMake Column Non-NullableMove Data Replace Type Code With Property Flags
![Page 24: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/24.jpg)
Qualidade de Dados - problemas comuns
1. Constraints fixas quebradas (check constraint).
2. Views com condições fixas na cláusula where.
3. Procedures que usam variavéis fixas para fazer cálculos.
4. Update dos dados (concorrência).
![Page 25: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/25.jpg)
Qualidade de Dados – Add Lookup table
![Page 26: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/26.jpg)
Qualidade de Dados – Apply Standard Codes
![Page 27: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/27.jpg)
Qualidade de Dados – Apply Standard Type
![Page 28: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/28.jpg)
Qualidade de Dados – Introduce Column Constraint
![Page 29: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/29.jpg)
Qualidade de Dados – Introduce Column Format
UPDATE Customer SET PhoneNumber = REPLACE(PhoneNumber,'-',''); UPDATE Customer SET PhoneNumber = REPLACE(PhoneNumber,' ',''); UPDATE Customer SET PhoneNumber = REPLACE(PhoneNumber,'(',''); UPDATE Customer SET PhoneNumber = REPLACE(PhoneNumber,')',''); UPDATE Customer SET PhoneNumber = REPLACE(PhoneNumber,'+1',''); UPDATE Customer SET PhoneNumber = REPLACE(PhoneNumber,'.','');
![Page 30: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/30.jpg)
Integridade referencial
Add Foreign Key Constraint (*)Add Trigger For Calculated Column (*)Drop Foreign Key ConstraintIntroduce Cascading Delete (*)Introduce Hard DeleteIntroduce Soft DeleteIntroduce Trigger For History
![Page 31: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/31.jpg)
Integridade referencial - Add Foreign Key Constraint
Checked immediately
Checked commit
![Page 32: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/32.jpg)
Integridade referencial - Add Trigger For Calculated Column
![Page 33: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/33.jpg)
Integridade referencial - Add Trigger For Calculate Column
![Page 34: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/34.jpg)
Integridade referencial - Introduce Cascading Delete
Cuidado com:DeadlockAccidental mass deletionDuplicated functionality
![Page 35: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/35.jpg)
Integridade referencial - Introduce Cascading Delete
![Page 36: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/36.jpg)
Arquitetura
Add CRUD Methods (*)Add Mirror Table (redundância)Add Read MethodEncapsulate Table With ViewIntroduce Calculation Method (*) Introduce IndexIntroduce Read-Only Table (*)Migrate Method From DatabaseMigrate Method To DatabaseReplace Method(s) With ViewReplace View With Method(s)Use Official Data Source
![Page 37: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/37.jpg)
Arquitetura- Add CRUD Methods
![Page 38: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/38.jpg)
Arquitetura- Introduce Calculation Method
![Page 39: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/39.jpg)
Arquitetura- Introduce Read-Only Table
![Page 40: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/40.jpg)
Arquitetura- Introduce Read-Only Table
Uma trigger para cada tabela
![Page 41: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/41.jpg)
Transformações
Insert DataIntroduce New ColumnIntroduce New TableIntroduce ViewUpdate Data
![Page 42: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/42.jpg)
Bibliografia
Ambler, Scott W., Pramod J. Sadalage (2006). Refactoring Databases: Evolutionary Databases Design. New York: Addison Wesley Professional. http://www.ambysoft.com/books/refactoringDatabases.html Ambler, Scott W., Pramod J. Sadalage (2006). Refactoring Databases: The Process.http://www.simple-talk.com/sql/database-administration/refactoring-databases-the-process/ Ambler, Scott W. (2007). Presentation Databases Refactoring.http://www.infoq.com/presentations/ambler-database-refactoring Ambler, S. W. (2003). Agile Databases Techniques: Effective Strategies for the Agile Software Developer. New York: John Wiley & Sons. www.ambysoft.com/agileDatabasesTechniques.html
Sato, Danilo e Ferreira, João Eduardo (2007). Banco de Dados Ágeis e Refatoração. Curso de Verão 2007 - IME/USP. http://ccsl.ime.usp.br/agilcoop/files/4-BDs-Ageis.pdf
![Page 43: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/43.jpg)
Perguntas
![Page 44: Refactoring Data base parte 2](https://reader033.vdocuments.us/reader033/viewer/2022060115/55799af1d8b42ae72b8b529b/html5/thumbnails/44.jpg)
Agradecimentos
Obrigado