[GitHub] [shardingsphere] sandynz commented on pull request #17411: openGauss create table SQL append schema name

[GitBox <gi...@apache.org>]

sandynz commented on PR #17411:
URL: https://github.com/apache/shardingsphere/pull/17411#issuecomment-1120134834

   Real test on local:
   ```
   $ gsql --host=host.docker.internal --port=3307 scaling_db root
   Password for user root:
   gsql ((MogDB 2.1.1 build b5f25b20) compiled at 2022-03-21 14:42:30 commit 0 last mr  )
   Non-SSL connection (SSL connection is recommended when requiring high-security)
   Type "help" for help.
   
   MogDB=>\d
   No relations found.
   MogDB=>create schema schema1;
   CREATE SCHEMA
   MogDB=>CREATE TABLE schema1.t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
   
   CREATE TABLE schema1.t_order_item (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, creation_date DATE, PRIMARY KEY (item_id));
   CREATE TABLE
   MogDB=>MogDB=>CREATE TABLE
   MogDB=>insert into schema1.t_order (order_id, user_id, status) values (1,2,'ok'),(2,4,'ok'),(3,6,'ok'),(4,1,'ok'),(5,3,'ok'),(6,5,'ok');
   
   insert into schema1.t_order_item (item_id, order_id, user_id, status) values (1,1,2,'ok'),(2,2,4,'ok'),(3,3,6,'ok'),(4,4,1,'ok'),(5,5,3,'ok'),(6,6,5,'ok');
   INSERT 0 6
   MogDB=>MogDB=>INSERT 0 6
   MogDB=>select * from schema1.t_order;
    order_id | user_id | status
   ----------+---------+--------
           4 |       1 | ok
           2 |       4 | ok
           6 |       5 | ok
           1 |       2 | ok
           5 |       3 | ok
           3 |       6 | ok
   (6 rows)
   
   MogDB=>preview select * from schema1.t_order;
    data_source_name |                                actual_sql
   ------------------+---------------------------------------------------------------------------
    ds_0             | select * from schema1.t_order_0 UNION ALL select * from schema1.t_order_2
    ds_1             | select * from schema1.t_order_1 UNION ALL select * from schema1.t_order_3
   (2 rows)
   
   MogDB=>ADD RESOURCE ds_2 (
       URL="jdbc:opengauss://127.0.0.1:15432/scaling_ds_10",
       USER=mogdb,
       PASSWORD=Enmo@123,
       PROPERTIES("maximumPoolSize"=50,"idleTimeout"="60000")
   ), ds_3 (
       URL="jdbc:opengauss://127.0.0.1:15432/scaling_ds_11",
       USER=mogdb,
       PASSWORD=Enmo@123,
       PROPERTIES("maximumPoolSize"=50,"idleTimeout"="60000")
   ), ds_4 (
       URL="jdbc:opengauss://127.0.0.1:15432/scaling_ds_12",
       USER=mogdb,
       PASSWORD=Enmo@123,
       PROPERTIES("maximumPoolSize"=50,"idleTimeout"="60000"##############)
   );#
   SUCCESS
   MogDB=>ALTER SHARDING TABLE RULE t_order(
   RESOURCES(ds_2,ds_3,ds_4),
   SHARDING_COLUMN=order_id,
   TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=6)),
   KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
   ), t_order_item(
   RESOURCES(ds_2,ds_3,ds_4)#,
   SHARDING_COLUMN=order_id,
   TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=6)),
   KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
   );#########
   SUCCESS
   MogDB=>show scaling list;
                        id                     |        tables        | sharding_total_count | active |     create_time     | stop_time
   --------------------------------------------+----------------------+----------------------+--------+---------------------+-----------
    0130317c30317c3054317c7363616c696e675f6462 | t_order,t_order_item | 2                    | true   | 2022-05-07 11:46:49 |
   (1 row)
   
   MogDB=>show scaling status 0130317c30317c3054317c7363616c696e675f6462;
    item | data_source | status  | active | inventory_finished_percentage | incremental_idle_seconds
   ------+-------------+---------+--------+-------------------------------+--------------------------
    0    | ds_0        | RUNNING | true   | 0                             | 0
    1    | ds_1        | RUNNING | true   | 0                             | 0
   (2 rows)
   
   MogDB=>show scaling status 0130317c30317c3054317c7363616c696e675f6462;
    item | data_source |  status  | active | inventory_finished_percentage | incremental_idle_seconds
   ------+-------------+----------+--------+-------------------------------+--------------------------
    0    | ds_0        | FINISHED | false  | 100                           | 110
    1    | ds_1        | FINISHED | false  | 100                           | 107
   (2 rows)
   
   MogDB=>preview select * from schema1.t_order;
    data_source_name |                                actual_sql
   ------------------+---------------------------------------------------------------------------
    ds_2             | select * from schema1.t_order_0 UNION ALL select * from schema1.t_order_3
    ds_3             | select * from schema1.t_order_1 UNION ALL select * from schema1.t_order_4
    ds_4             | select * from schema1.t_order_2 UNION ALL select * from schema1.t_order_5
   (3 rows)
   
   MogDB=>select * from schema1.t_order;
    order_id | user_id | status
   ----------+---------+--------
           6 |       5 | ok
           3 |       6 | ok
           1 |       2 | ok
           4 |       1 | ok
           2 |       4 | ok
           5 |       3 | ok
   (6 rows)
   ```
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org