oracle移植_将Oracle移植到YugabyteDB

2022-05-12 00:00:00 创建数据库删除脚本约束

oracle移植

I presented an Introduction to SQL webinar. In preparation for this, I needed a dataset. Because YugabyteDB is compatible with PostgreSQL, it seemed obvious to try out the PostgreSQL Tutorialsite. However, I have never liked the “film database” example. I prefer the classic example with customers, orders, and products. So instead I checked out the sister site the Oracle Tutorial. Let us look at what it takes to port this database to YugabyteDB.

我介绍了SQL网络研讨会简介。为此，我需要一个数据集。由于YugabyteDB与PostgreSQL兼容，因此尝试PostgreSQL教程站点似乎很明显。但是，我从不喜欢“电影数据库”示例。我更喜欢客户，订单和产品的经典示例。因此，相反，我签出了姐妹站点Oracle Tutorial 。让我们看看将数据库移植到YugabyteDB所需的资源。

Oracle教程样本数据库 (The Oracle Tutorial Sample Database)

The Oracle Tutorial sample database contains a dozen tables each with a handful to a few hundred records. It maps pretty clearly to the kind of data that a company with some customers and products might manage.

Oracle Tutorial示例数据库包含十二个表，每个表都有少数几百个记录。它非常清楚地映射到拥有一些客户和产品的公司可能管理的数据类型。

(Credit: The Oracle Tutorial)

(来源：Oracle教程)

The download consists of a few scripts. These create the users, the schema, and load the data. They are not that different off from what you might generate with the Oracle get_ddl PL/SQL tool. The zip file contains:

下载内容包含一些脚本。这些创建用户，模式并加载数据。它们与您使用Oracle get_ddl PL / SQL工具生成的内容没有太大不同。压缩文件包含：

ot_create_user.sql — creates an initial user called “ot” and grants privileges to the user
ot_create_user.sql —创建一个名为“ ot”的初始用户并向该用户授予特权
ot_schema.sql — creates all of the tables and constraints
ot_schema.sql —创建所有表和约束
ot_drop.sql — drops all of the tables
ot_drop.sql —删除所有表
ot_data.sql — disables the constraints, inserts all of the data, enables the constraints
ot_data.sql —禁用约束，插入所有数据，启用约束

在YugabyteDB中运行SQL (Running SQL in YugabyteDB)

If you want to follow along first install YugabyteDB by following the instructions on the download site. Pay extra care to make sure you have enough file handles.

如果要遵循此步骤，请先按照下载站点上的说明安装YugabyteDB。请格外小心，以确保您有足够的文件句柄。

From the home directory of an existing YugabyteDB installation type “./bin/ysqlsh.” If this is a local install you should be immediately taken to a shell. This is basically the same as the postgresql psql command. You can execute SQL statements here. There are also built in commands that are executed with the backslash ‘\’ character.

在现有YugabyteDB安装目录的主目录中，输入“ ./bin/ysqlsh”。如果这是本地安装，则应立即将其带入Shell。这基本上与postgresql psql命令相同。您可以在此处执行SQL语句。还内置有用反斜杠“ \”字符执行的命令。

\? – displays help
\? –显示帮助
\dt – lists the tables (similar to select table_name from user_tables)
\dt –列出表(类似于从user_tables中选择table_name)
\q – exits the shell
\q –退出外壳

更改Oracle教程脚本 (Making changes to the Oracle Tutorial Scripts)

While both Oracle and YugabyteDB are relational databases and both support SQL, like any other two relational databases there are some minor differences that require consideration. For the purpose of the tutorial here are the main issues:

尽管Oracle和YugabyteDB都是关系数据库，并且都支持SQL，但与其他两个关系数据库一样，都需要考虑一些细微的差异。就本教程而言，这里是主要问题：

Users and roles
用户和角色
Schema references
模式参考
Data *
资料类型
YugabyteDB does not support enabling or disabling constraints
YugabyteDB不支持启用或禁用约束
YugabyteDB does not support “generated” identity fields
YugabyteDB不支持“生成的”身份字段

通过SQL Lines Web工具运行所有脚本 (Running all of the scripts through the SQL Lines web tool)

Because YugabyteDB supports PostgreSQL’s dialect and data*, the Oracle scripts have to be converted. This could be done by hand, but that would be really boring. Instead, there is a free-to-use web tool at http://www.sqlines.com/online. You literally cut and paste each script into it, select “oracle” as the source format, and “postgresql” as the output format and click convert. Copy the output (on the right) into the original file and save over the original. This fixes all of the data*.

由于YugabyteDB支持PostgreSQL方言和数据类型，因此必须转换Oracle脚本。可以手工完成，但这确实很无聊。而是在http://www.sqlines.com/online上提供了免费的网络工具。您可以将每个脚本直接剪切并粘贴到其中，选择“ oracle”作为源格式，选择“ postgresql”作为输出格式，然后单击“转换”。将输出(在右侧)复制到原始文件中，然后保存在原始文件上。这将修复所有数据类型。

更改ot_create_user.sql脚本 (Changing the ot_create_user.sql script)

The original script has comments, the word “privileges” spelled with a “d” multiple times and two lines:

原始脚本带有注释，“特权”一词多次拼写为“ d”，并有两行：

CREATE USER OT IDENTIFIED BY yourpassword;GRANT CONNECT, RESOURCE, DBA TO OT;

The YugabyteDB version looks like this:

YugabyteDB版本如下所示：

-- create new userCREATE USER OT WITH PASSWORD 'yugabyte';-- create databaseCREATE DATABASE OT WITH OWNER=OT;-- grant privilegesGRANT ALL ON DATABASE OT TO OT;

However, for convenience sake you may also add to the top of the file:

但是，为方便起见，您也可以添加到文件的顶部：

DROP DATABASE OT;DROP USER OT;

This is mainly for convenience’s sake while creating/destroying the database during testing.

这主要是为了方便起见，在测试期间创建/销毁数据库。

更改ot_schema.sql (Changing ot_schema.sql)

Most of the work is in this file. It is also useful to divide it into two files: One to create schema and table elements and another to create constraints.

大多数工作在此文件中。将其分为两个文件也很有用：一个创建架构和表元素，另一个创建约束。

Copy ot_schema.sql to ot_create_constraints.sql
将ot_schema.sql复制到ot_create_constraints.sql
Add:
加：

CREATE SCHEMA OT;SET search_path TO OT,public;CREATE SEQUENCE OT.regions_seq;

This creates the “ot” schema and changes the user’s search path to look at the “OT” schema first before the default “public” schema. This has to be done because all of the insert statements in the “ot_data.sql” script directly reference the schema name i.e. “insert into OT.countries” but in other places, the table is referenced without the schema name.

这将创建“ ot”模式，并更改用户的搜索路径，以在默认的“ public”模式之前先查看“ OT”模式。之所以必须这样做，是因为“ ot_data.sql”脚本中的所有插入语句都直接引用架构名称，即“插入OT.countries”，但是在其他地方，所引用的表没有架构名称。

Next, change every create table statement to explicitly reference the schema. For example:

接下来，更改每个create table语句以显式引用该架构。例如：

CREATE TABLE regions

becomes

变成

CREATE TABLE OT.regions

Next, remove every “GENERATED BY DEFAULT AS IDENTITY” line. For example:

接下来，删除每条“默认生成的身份”行。例如：

“GENERATED BY DEFAULT AS IDENTITY START WITH 320” — just delete these lines. I will blog about sequences some other time, but my colleague Bryn has explained in another blog why database sequences are not good in a distributed database and some alternatives. While they are not needed for the example database to work for our purposes, if you were doing something more production-ready you would want to replace sequences with UUIDs, Bryn’s blog explains this in exquisite detail.

“默认身份由320开头” —只需删除这些行即可。我将在其他时间撰写有关序列的博客，但是我的同事Bryn在另一个博客中解释了为什么在分布式数据库和某些替代方案中数据库序列不好。虽然示例数据库不需要它们就可以达到我们的目的，但是如果您要进行更多生产准备工作，则希望用UUID替换序列，Bryn的博客对此进行了详细说明。

Finally, remove all of the lines around foreign key constraints except for the primary keys. While YugabyteDB does support the constraint clause, it does not support “enable” or “disable.” Instead it is better to create them after a data load and delete them before a data load. In order to do that without duplicating the creation, it makes sense to delete them here and create them with “alter table” statements instead. Delete all of the foreign key constraint clauses except for the primary key clauses.

后，除去外键约束周围的所有行(主键除外)。尽管YugabyteDB确实支持约束子句，但它不支持“启用”或“禁用”。相反，好是在数据加载后创建它们，并在数据加载之前删除它们。为了做到这一点而又不重复创建，可以在此处删除它们，并使用“ alter table”语句创建它们。删除除主键子句以外的所有外键约束子句。

For example, in the OT.orders definition, remove:

例如，在OT.orders定义中，删除：

CONSTRAINT fk_orders_customers       FOREIGN KEY( customer_id )      REFERENCES customers( customer_id )      ON DELETE CASCADE,    CONSTRAINT fk_orders_employees       FOREIGN KEY( salesman_id )      REFERENCES employees( employee_id )       ON DELETE SET NULL

And the comma in the line above this clause. The orders table definition should be simplified to:

以及此子句上方一行的逗号。订单表的定义应简化为：

CREATE TABLE OT.orders  (    order_id DOUBLE PRECISION PRIMARY KEY,    customer_id INT NOT NULL, -- fk    status      VARCHAR( 20 ) NOT NULL ,    salesman_id INT         , -- fk    order_date  TIMESTAMP(0) NOT NULL  );

However, leave in all of the primary key constraint clauses. For example, order_items should remain:

但是，保留所有主键约束子句。例如，order_items应该保留：

CREATE TABLE OT.order_items  (    order_id   BIGINT                                , -- fk    item_id    BIGINT                                ,    product_id BIGINT NOT NULL                       , -- fk    quantity   DECIMAL( 8, 2 ) NOT NULL                        ,    unit_price DECIMAL( 8, 2 ) NOT NULL                        ,    CONSTRAINT pk_order_items      PRIMARY KEY( order_id, item_id )  );

And the inventories table should remain:

库存表应该保留：

CREATE TABLE OT.inventories  (    product_id   BIGINT        , -- fk    warehouse_id BIGINT        , -- fk    quantity     INT NOT NULL,    CONSTRAINT pk_inventories      PRIMARY KEY( product_id, warehouse_id )  );

Those are all the changes required for the schema script.

这些都是架构脚本所需的全部更改。

ot_create_constraints.sql (ot_create_constraints.sql)

In the beginning we made a copy of the “ot_schema.sql” script. Leave the “SET search_path TO OT,public;” at the top but erase everything except the create table statements. Each create_table statement should be changed to ALTER TABLE … ADD. The field definitions should be deleted and only the constraints should remain. For example the “order_items” constraint definition should be:

首先，我们复制了“ ot_schema.sql”脚本。离开“ SET search_path TO OT,public; ”，但删除除create table语句以外的所有内容。每个create_table语句应更改为ALTER TABLE…ADD。字段定义应删除，仅保留约束。例如，“ order_items”约束定义应为：

ALTER TABLE OT.order_items ADD    CONSTRAINT fk_order_items_products      FOREIGN KEY( product_id )      REFERENCES products( product_id )      ON DELETE CASCADE;ALTER TABLE OT.order_items ADD    CONSTRAINT fk_order_items_orders      FOREIGN KEY( order_id )      REFERENCES orders( order_id )      ON DELETE CASCADE  ;

Copy this to another file called ot_drop_constraints.sql.

将此复制到另一个文件ot_drop_constraints.sql。

ot_drop_constraints.sql (ot_drop_constraints.sql)

Change all of the “ALTER TABLE ADD” statements to “DROP” and add one per constraint. The entire output should look like this:

将所有“ ALTER TABLE ADD”语句更改为“ DROP”，并为每个约束添加一个。整个输出应如下所示：

ALTER TABLE countries DROP CONSTRAINT fk_countries_regions;ALTER TABLE locations DROP CONSTRAINT fk_locations_countries;ALTER TABLE warehouses DROP CONSTRAINT fk_warehouses_locations;ALTER TABLE employees DROP CONSTRAINT fk_employees_manager;ALTER TABLE products DROP CONSTRAINT fk_products_categories;ALTER TABLE contacts DROP CONSTRAINT fk_contacts_customers;ALTER TABLE orders DROP CONSTRAINT fk_orders_customers;ALTER TABLE orders DROP CONSTRAINT fk_orders_employees;ALTER TABLE order_items DROP CONSTRAINT fk_order_items_products;ALTER TABLE order_items DROP CONSTRAINT fk_order_items_orders;ALTER TABLE inventories DROP CONSTRAINT fk_inventories_products;ALTER TABLE inventories DROP CONSTRAINT fk_inventories_warehouses;

ot_data.sql (ot_data.sql)

Remove all of the ALTER TABLE statements from the top and bottom of the file. We have externalized this into the ot_create_constraints.sql and ot_drop_constraints.sql scripts.

从文件的顶部和底部删除所有的ALTER TABLE语句。我们已将其外部化为ot_create_constraints.sql和ot_drop_constraints.sql脚本。

在YugabyteDB上运行Oracle教程脚本 (Running the Oracle Tutorial Scripts on YugabyteDB)

The scripts should now be run in this sequence:

现在应按以下顺序运行脚本：

ot_create_user.sql
ot_create_user.sql
ot_schema.sql
ot_schema.sql
ot_data.sql
ot_data.sql
ot_create_constraint.sql
ot_create_constraint.sql

The “drop_constraint.sql” is not technically needed unless running another bulk load, but it is nice to have.

除非运行另一个批量加载，否则从技术上讲，不需要“ drop_constraint.sql”，但是很高兴。

返回ysqlsh (Back to ysqlsh)

Run the YugabyteDB shell:

运行YugabyteDB shell：

./bin/ysqlsh

Run these steps:

运行以下步骤：

\i ot_create_user.sql\c ot ot

You are now connected to database “ot” as user “ot”.

现在，您以用户“ ot”连接到数据库“ ot”。

\i ot_schema.sql\i data.sql\i ../ot_create_constraint.sql

测试出来 (Testing it out)

That is it. The Oracle Tutorial sample database has been successfully loaded into YugabyteDB.

这就对了。 Oracle Tutorial示例数据库已成功加载到YugabyteDB中。

\dt tells you what tables are here:

\ dt告诉您这里有什么表：

ot=> \dtList of relations Schema |        Name        | Type  | Owner --------+--------------------+-------+------- ot     | contacts           | table | ot ot     | countries          | table | ot ot     | customers          | table | ot ot     | employees          | table | ot ot     | inventories        | table | ot ot     | locations          | table | ot ot     | order_items        | table | ot ot     | orders             | table | ot ot     | product_categories | table | ot ot     | products           | table | ot ot     | regions            | table | ot ot     | warehouses         | table | ot(12 rows)

Describe a table:

描述一个表：

\d orders                               Table "ot.orders"   Column    |              Type              | Collation | Nullable | Default -------------+--------------------------------+-----------+----------+--------- order_id    | double precision               |           | not null |  customer_id | integer                        |           | not null |  status      | character varying(20)          |           | not null |  salesman_id | integer                        |           |          |  order_date  | timestamp() without time zone |           | not null | Indexes:    "orders_pkey" PRIMARY KEY, lsm (order_id HASH)Foreign-key constraints:    "fk_orders_customers" FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE    "fk_orders_employees" FOREIGN KEY (salesman_id) REFERENCES employees(employee_id) ON DELETE SET NULLReferenced by:    TABLE "order_items" CONSTRAINT "fk_order_items_orders" FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE

You can get row counts to make sure the data got inserted:

您可以获取行数以确保已插入数据：

select count(*) from customers; count -------   319(1 row)

And even run a more complicated query to get totals per order:

甚至运行更复杂的查询以获取每个订单的总数：

select order_id, sum(total) from (select o.order_id as order_id, o.customer_id, o.order_date, i.item_id as item_id, i.quantity * i.unit_price as total from orders o inner join order_items i on o.order_id=i.order_id) as x group by order_id, item_id; order_id |     sum     ----------+-------------       28 | 166798.6100      102 |  75899.3100       94 |   4232.5400       46 | 166074.3500       23 |  54167.2600       72 | 130993.7700       34 |  31638.8700       88 | 213601.6600       24 |  18605.0000        1 |  89609.1300       36 |  30914.5500       95 |  37921.7100       13 |  20253.8000       27 |  25080.0000       83 |  55631.2400       67 |  85186.7600       43 | 240799.1400       27 | 279407.0400      105 |  62369.2300       18 |  78278.9700       22 |  53577.1100        5 |  70846.3600       98 |  41598.9600

下一步是什么？ (What’s next?)

If you want to learn more SQL:

如果您想了解更多SQL：

If you are new to SQL then I would check out the previously recorded webinar.
如果您不熟悉SQL，那么我将签出以前录制的网络研讨会。
Check out the Yugabyte Docs and the Northwind sample database, which is very similar to the Oracle Tutorial sample database
查看Yugabyte Docs和Northwind示例数据库，它与Oracle Tutorial示例数据库非常相似。
…but if you are not a command-line junkie, consider one of the great graphical tools available for Yugabyte including pgAdmin
…但是，如果您不是命令行迷，请考虑使用适用于Yugabyte的出色图形工具之一，其中包括pgAdmin

If you want to learn more about SQL or porting Oracle or Learning SQL:

如果要了解有关SQL的更多信息或移植Oracle或学习SQL：

Try other paths in the Oracle Tutorial
试用Oracle教程中的其他路径
Try the PostgreSQL Tutorial
尝试PostgreSQL教程
Read more about sequences and the concurrency issues caused by sequences as well as the alternative (UUIDs)
阅读有关序列和由序列以及替代方案(UUID) 引起的并发问题的更多信息

相关文章