Kako razvijate PL/SQL pakete? File based PL/SQL development Mitja Golouh [email protected]...

33
Kako razvijate PL/SQL pakete? File based PL/SQL development Mitja Golouh [email protected] SIOUG 2006, 11.10.2006

Transcript of Kako razvijate PL/SQL pakete? File based PL/SQL development Mitja Golouh [email protected]...

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

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

Mitja [email protected]

SIOUG 2006, 11.10.2006

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

2

• Schema deployment

• Scripts and schema versioning

• PL/SQL development cycle

• Creating database schema from scripts

• Sqlmake - automated deploy tool

Agenda

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

3

• 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.

What is database schema?

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

4

Schema and application

Client

Application server.net, J2EE, Oracle Forms

Databasedatabase schema

Application

Application server.net, J2EE, Oracle Forms

Databasedatabase schema

New release of application

.ear, .msi

SQL*Plus scripts

.fmx

manually

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

5

Deploying schema

• What to deploySchema objects (physical attributes!)

Non schema objects

Data

Privileeges

• Deployment typeEmpty schema

Upgrade existing schema

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

6

How to deploy?

• Non repeatableHm, no need, I am developing in production

Generate from Designer into database

Use tools to compare and synchronize two schemas

• RepeatableScripts

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

7

• Script is not workingPackage has errors when we compile in target

environment

Index already exists

Update table has failed

• App - schema synchronizationDatabase schema version is not in sync with

application release

• Deployment gap is finalized manually

Deployment problems

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

8

The gap

• Differences betweenWhat is in target schema and

What we think is in target schema

• Source of differencesLoose deployment organization and control

Manual interaction during deployment

3rd party interaction in between

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

9

Scripts

• Different types of database objectsCreate (tables, indexes, ...)

Create or Replace (PL/SQL packages, views, ...)

Data (insert, “import”)

Upgrade (alter, update, drop, ...)

• How to group objectsSingle big install script

One object per script

Somewhere in between

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

10

Creating scripts

• Generate scriptsData modeling tools like Oracle Designer

Compare two schemas for differencies

• PL/SQL IDE tools Oracle SQL Developer (Raptor)

Toad

PL/SQL Developer

• Manually

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

11

Upgrade scripts

• Upgrade script is ...New objects (data and privileges)

Modified objects (and data)

Droped objects (data and privileges)

• Usually createdBy hand

Using compare schemas

By developer responsible for modification

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

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”

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

13

Deploying upgrade scripts

Schema v1

Schema v2

Schema v3

Data v1

Data v2

Data v3

UPGRADESCRIPT

UPGRADESCRIPT

• 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!

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

14

BaseRelease 2

Customer 1Release 1

Customer 2Release 1

Customer 3Release 2

Customer 2Release 2Base

Release 1

The challange

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

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

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

16

How to use SCM

• Data modeling toolVersion generated scripts

One big file or many small files

• PL/SQL developmentIntegrate into development cycle

One package = one file

• Data Version insert scripts

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

17

• Introduced in version 6i

• It looked promising but …

• Statement of directionOracle 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

Oracle Designer SCM

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

18

• 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

3rd party SCM

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

19

• 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

Versioning PL/SQL code

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

20

• Use source filesOpen from filesystem

Edit

Compile

Save

Checkin to SCM

• Instead ofOpen from database

Edit

Compile

PL/SQL development cycle

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

21

• 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 dataSystem dataList of values

File based development

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

22

schema_name

tab (.tab, .con, .ind)

plsql

data

schema_name

kernel

data_entry

reports

File system

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

23

• 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

SPOC

SPOCK

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

24

• Deploying 10, 100 or 1000 scripts is not easy

• AlternativesHand made install scripts

Automated deploy tool like Designer Repository install

Creating schema from scripts

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

25

• Good example found in utPLSQL install scripts

• Use SQL*Plus commands@, @@

Substitution variables

• Hard to maintain for large number of files

in changing environment

Hand made scripts

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

26

• Goal = Synchronize source files and database schema

schema_name

tab (.tab, .con, .ind)

plsql

data

=

Automated tool

schemaname

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

27

Sqlmake

• Automated tool for database schema deployment

• Predecessor used in live projects

• Currently in developmentPorted to .net

Adding new features

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

28

• 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)

Sqlmake benefits

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

29

• Some obstacles found and solvedDatabase object dependencies

Performance (reinstall only modified files)

Keep schema version information

Upgrade scripts

• To doNant integration

PL/SQL development tool integration

Sqlmake internals

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

30

• 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

Summary

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

31

?Mitja Golouh

[email protected]

Questions

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

32

Usefull keywords

• Versioning toolsCVS

Subversion

• File compare toolsWinMerge

• Extract DDL from export filesDDL Wizard (Databee)

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

©2004 HERMES SoftLab. All rights reserved. Logotype is a registered trademark of HERMES SoftLab d.d. All other names/logos may be registered trademarks of their respective owners. ©2004 HERMES SoftLab. All rights reserved. Logotype is a registered trademark of HERMES SoftLab d.d. All other names/logos may be registered trademarks of their respective owners.