1 / 33

Kako razvijate PL/SQL pakete? File based PL/SQL development

Kako razvijate PL/SQL pakete? File based PL/SQL development. Mitja Golouh mitja.golouh@hermes.si SIOUG 2006, 11.10.2006. Agenda. Schema deployment Scripts and schema versioning PL/SQL development cycle Creating database schema from scripts Sqlmake - automated deploy tool.

ulric-padilla
Download Presentation

Kako razvijate PL/SQL pakete? File based PL/SQL development

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Kako razvijate PL/SQL pakete?File based PL/SQL development Mitja Golouh mitja.golouh@hermes.si SIOUG 2006, 11.10.2006

  2. Agenda • Schema deployment • Scripts and schema versioning • PL/SQL development cycle • Creating database schema from scripts • Sqlmake - automated deploy tool

  3. What is database schema? • The word schema comes from the Greek word "σχήμα" (skhēma), which means shape or more generally plan. • A database schema is a collection of logical structures of data, or schema objects. • A schema is owned by a database user and has the same name as that user.

  4. Schema and application Client New release of application Application .ear, .msi Application server .net, J2EE, Oracle Forms Application server .net, J2EE, Oracle Forms .fmx SQL*Plus scripts Database database schema Database database schema manually

  5. Deploying schema • What to deploy Schema objects (physical attributes!) Non schema objects Data Privileeges • Deployment type Empty schema Upgrade existing schema

  6. How to deploy? • Non repeatable Hm, no need, I am developing in production Generate from Designer into database Use tools to compare and synchronize two schemas • Repeatable Scripts

  7. Deployment problems • Script is not working Package has errors when we compile in target environment Index already exists Update table has failed • App - schema synchronization Database schema version is not in sync with application release • Deployment gap is finalized manually

  8. The gap • Differences between What is in target schema and What we think is in target schema • Source of differences Loose deployment organization and control Manual interaction during deployment 3rd party interaction inbetween

  9. Scripts • Different types of database objects Create (tables, indexes, ...) Create or Replace (PL/SQL packages, views, ...) Data (insert, “import”) Upgrade (alter, update, drop, ...) • How to group objects Single big install script One object per script Somewhere in between

  10. Creating scripts • Generate scripts Data modeling tools like Oracle Designer Compare two schemas for differencies • PL/SQL IDE tools Oracle SQL Developer (Raptor) Toad PL/SQL Developer • Manually

  11. Upgrade scripts • Upgrade script is ... New objects (data and privileges) Modified objects (and data) Droped objects (data and privileges) • Usually created By hand Using compare schemas By developer responsible for modification

  12. Testing scripts • How to setup test environment? Upgrade existing test schema Set up in empty schema (import or create all objects and test data from scripts) • Tests (and also deployments) can fail – how to rollback DDL changes? CREATE SCHEMA but no ALTER SCHEMA RESTORE POINT + FLASHBACK Set up a new test environment Manual “rollback”

  13. UPGRADE SCRIPT Schema v3 UPGRADE SCRIPT Schema v2 Schema v1 Data v3 Data v2 Data v1 Deploying upgrade scripts • Different approaches to upgrade scripts • Single big upgrade script • Small atomical upgrade scripts • Use prepared and tested upgrade scripts • Who does the deployment? • Know what was deployed!

  14. The challange Customer 1 Release 1 Customer 2 Release 2 Customer 2 Release 1 Base Release 1 Base Release 2 Customer 3 Release 2

  15. Schema versioning • Can we version database schema? Google keywords: schema evolution, schema versioning • Source Configuration Management (SCM) Clear Case, Subversion, Perforce, CVS Multiple developers Multiple target environments (branches) Change request tracking History tracking

  16. How to use SCM • Data modeling tool Version generated scripts One big file or many small files • PL/SQL development Integrate into development cycle One package = one file • Data Version insert scripts

  17. Oracle Designer SCM • Introduced in version 6i • It looked promising but … • Statement of direction Oracle SCM has been placed into maintenance mode. This means that Oracle will continue to fix bugs but there will be no new features added to the product. • Not very user friendly PL/SQL development

  18. 3rd party SCM • Inbuilt functionality with PL/SQL development tools TOAD (Team coding, Project Manager) PL/SQL Developer (Project, VCS plugin) SQL Developer (sorry, not there yet) • Versioning software works with file system

  19. Versioning PL/SQL code • Frequent changes • No need for upgrade scripts due to CREATE or REPLACE syntax • Can deploy previous version • File based development and versioning supported by development tools

  20. PL/SQL development cycle • Instead of Open from database Edit Compile • Use source files Open from filesystem Edit Compile Save Checkin to SCM

  21. File based development • Top wish on new SQL Developer (Raptor) functionality survey File based PL/SQL support Added file browsing support • Every database object is scripted in a file • Privileeges are scripted in a file • Data is scripted in a file Test data System data List of values

  22. File system

  23. SPOC • Change of philosophy • Do you trust your file system? Drop your development or test schema Recreate it from scripts See if anyone complains • Single Point Of Control= File system SPOCK

  24. Creating schema from scripts • Deploying 10, 100 or 1000 scripts is not easy • Alternatives Hand made install scripts Automated deploy tool like Designer Repository install

  25. Hand made scripts • Good example found in utPLSQL install scripts • Use SQL*Plus commands @, @@ Substitution variables • Hard to maintain for large number of files in changing environment

  26. Automated tool • Goal = Synchronize source files and database schema schema name =

  27. Sqlmake • Automated tool for database schema deployment • Predecessor used in live projects • Currently in development Ported to .net Adding new features

  28. Sqlmake benefits • No need to manually create install scripts • Easy setup of new schema from release xy For a new developer For testing purposes For bug hunting • Easy upgrade of PL/SQL code (all database objects with replace syntax) • Controlled deployment of upgrade scripts (all database objects without replace syntax, data)

  29. Sqlmake internals • Some obstacles found and solved Database object dependencies Performance (reinstall only modified files) Keep schema version information Upgrade scripts • To do Nant integration PL/SQL development tool integration

  30. Summary • SCM can add value to database schema development • Automated tools like Sqlmake are helpful in a file based development • Take control of your development and deployment • Deployment organization is very important for good testing practices

  31. Questions ? Mitja Golouh mitja.golouh@hermes.si

  32. Usefull keywords • Versioning tools CVS Subversion • File compare tools WinMerge • Extract DDL from export files DDL Wizard (Databee)

More Related