构建 Oracle 数据库；良好的目录布局
我正在寻找有关如何在我的项目目录中最好地组织新的 Oracle 模式和依赖文件的建议 - 使用序列、触发器、DDL 等。我已经使用一个名为 schema.sql 的整体文件一段时间了，但是我想知道是否有最佳实践？类似...
database/
tables/
person.sql
group.sql
sequences/
person.sequence
group.sequence
triggers/
new_person.trigger
Penny 的想法或我可能错过的 URL！
谢谢你！
I'm looking for advice on how to best organize a new Oracle schema and dependent files in my project directory - with the sequences, triggers, DDL, etc. I've been using one monolothic file called schema.sql for some time, but I'm wondering if there's a best practice? Something like...
database/
tables/
person.sql
group.sql
sequences/
person.sequence
group.sequence
triggers/
new_person.trigger
Penny for your thoughts or a URL that I may have missed!
Thank you!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
按对象类型存储 DDL 是一种合理的方法——任何东西都可能比单一的 SQL 脚本更容易导航。但就我个人而言，我更愿意按功能组织 DDL。例如，如果您正在构建会计系统，则可能有一系列用于管理应付账款的对象、一组单独的用于管理应收账款的对象以及一些用于管理总账科目的核心对象。这将导致随着
系统变得更加复杂，随着时间的推移，层次结构自然会变得更深。这种方法会更自然地反映非数据库代码在源代码管理中的存储方式。您不会在目录结构中拥有单个 Java 类目录，例如
您会将实现相同类型业务逻辑的类组织在一起，并与实现不同业务逻辑位的类分开。
Storing DDL by object type is a reasonable approach-- anything is likely to be easier to navigate than a monolithic SQL script. Personally, though, I'd much rather have DDL organized by function. If you're building an accounting system, for example, you probably have a series of objects to manage accounts payable and a separate set of objects to manage accounts receivable along with some core objects for managing the general ledger accounts. That would lead to something along the lines of
As the system gets more complex, that hierarchy would naturally get deeper over time. This sort of approach would more naturally mirror the way non-database code is stored in source control. You wouldn't have a single directory of Java classes in a directory structure like
You would organize the classes that implement the same sorts of business logic together and separate from the classes that implement different bits of business logic.
需要考虑的一项是那些可以充当“仅限最新”脚本的 SQL。其中包括创建或替换过程/函数/触发器等。您运行最新版本，并且不必担心数据库中以前可能存在的内容。
另一方面，您可以在表中以 CREATE TABLE 开始，然后随着模式的更改而进行多个 ALTER TABLE。如果您正在进行升级，您可能需要应用多个 ALTER TABLE 脚本（最好按顺序）。
我反对“功能分组”，除非界限非常明显。您可能不希望这样的情况：一个组中有一个 USERS 表，另一个组中有一个 USER_AUTHORITIES，第三个组中有一个 AUTHORITY 组。
如果您确实有适当的分离，那么它们可能位于不同的模式中，并且您确实希望保持模式不同（因为您可以在不同的模式中拥有相同的对象名称）。
One item to consider is those SQLs which can act as 'latest only' scripts. These include CREATE OR REPLACE PROCEDURE/FUNCTION/TRIGGER etc. You run the latest version and you are not worried about what may have previously existed in the database.
On the other hand you have tables where you may start off with a CREATE TABLE followed by several ALTER TABLEs as changes to the schema evolve. And if you are doing an upgrade you may want to apply several of the ALTER TABLE scripts (preferably in order).
I'd argue against a 'functional grouping' unless it is really obvious where the lines are drawn. You probably don't want to be in a position where you have a USERS table in one group and a USER_AUTHORITIES in another and an AUTHORITY group in a third.
If you do have decent separation, then they are probably in separate schemas and you do want to keep schemas distinct (since you can have the same object names in different schemas).
按对象类型划分的安排以及在数据库目录下添加“模式”目录对我来说效果很好。
我使用过具有额外按功能划分层的源代码控制系统 - 如果有许多对象，如果您尝试将源代码控制文件与您在数据库中看到的对象交叉引用，它会添加额外的搜索GUI 导航器通常按类型对对象进行分组。人们并不总是清楚应该如何以这种方式对对象进行分类。
考虑添加一个“grants”目录，用于该架构向其他架构或角色授予的授权，每个受授权者有一个文件。如果您有“基于规则”的授权，例如“APPLICATION_USER 角色始终在所有模式 X 的表上获得 SELECT”，则编写一个 PL/SQL 匿名块来执行此操作。 （在通过某种临时方法将授权落实到位后，您可能会想对授权进行逆向工程，但是当新表或视图添加到应用程序时很容易错过某些内容）。
对所有脚本的分隔符进行标准化，如果您开始通过构建实用程序（例如 Ant）进行部署，您的生活将会变得更加轻松。使用“/”（相对于“;”）适用于 SQL 语句以及 PL/SQL 匿名块。
The division-by-object-type arrangement, with the addition of a "schema" directory below the database directory works well for me.
I've worked with source control systems that have the additional division-by-function layer - if there are many objects it adds additional searching if you're trying to cross-reference the source control file with the object that you see in a database GUI navigator that generally groups objects by type. It's also not always clear how an object should be classified this way.
Consider adding a "grants" directory for the grants made by that schema to other schemas or roles, with one file per grantee. If you have "rule-based" grants such as "the APPLICATION_USER role always gets SELECT on all of schema X's tables", then write a PL/SQL anonymous block to perform this action. (You might be tempted to reverse-engineer the grants after they get put in place by some ad-hoc method, but it's easy to miss something when new tables or views are added to the application).
Standardize on a delimiter for all scripts and you'll make your life easier if you start deploying through a build utility such as Ant. Using "/" (vs. ";") works for both SQL statements as well as PL/SQL anonymous blocks.
在我们的项目中，我们使用某种组合方法：我们的程序核心作为根目录，其他功能位于子文件夹中：
等等。
在所有这些文件夹中，我们都有 DDL 和 DML 脚本，几乎所有脚本都可以运行多次，例如所有包都定义为
创建或替换...，所有数据插入脚本检查数据是否已存在等等。这使我们有机会使用几乎所有脚本，而不必担心我们可能会崩溃。显然这种情况不能应用于
create table和类似的语句。对于这些脚本，我们手动编写了小型 bash 脚本，该脚本提取指定的文件并运行它们，不会因特定的 ORA 错误而失败，例如：ORA-00955：名称已被现有对象使用。此外，所有文件都混合在目录中，但扩展名不同：
.seq表示序列，.tbl表示表，.pkg表示包接口，.bdy用于包体，.trg用于触发器等等...此外，我们还有一个命名约定，表示所有文件的前缀：我们可以将
cl_oper.tbl表与cl_oper.seq和cl_oper.trg序列和触发器以及cl_oper_processing.pkg一起使用与cl_oper_processing.bdy以及上述对象的逻辑。通过文件管理器中的这种命名约定，可以很容易地看到与我们项目的某些逻辑单元相关的所有文件（尽管按对象类型对目录进行分组并没有提供这一点）。希望这些信息对您有所帮助。如果您有任何疑问，请留言。
In our projects we use somewhat combined approach: we have a core of our program as a root and other functionalities in subfolders:
etc.
In all these folders we have both DDL and DML scripts almost all of them can be run more that once, e.g. all packages are defined as
create or replace..., all data insertion scripts check whether data already exists and so on. This gives us the opportunity to rus almost all scripts without thinking that we can crash something.Obviously this scenario can't be applied for
create tableand similar statements. For these scripts we have manually written small bash script that extracts specified files and runs them not failing on particular ORA errors, like:ORA-00955: name is already used by an existing object.Also all files are mixed in the directories but differ with extensions:
.seqgoes for sequence,.tblgoes for table,.pkggoes for package interface,.bdygoes for package body,.trggoes for trigger an so on...Also we have a naming convention denoting prefixes for all of our files: we can have
cl_oper.tbltable withcl_oper.seqandcl_oper.trgsequence and triggers andcl_oper_processing.pkgtogether withcl_oper_processing.bdywith logic for mentioned objects. With this naming convention in file managers it's very easy to see all the files connected with some unit of logic for our project (whilst the grouping in directories by object types does not provide this).Hope this information helps you somehow. Please leave comments if you have any questions.