An Enhancement of Schema Comparison Function

Details

Description

The current schema comparison mechanism only represents a simple text comparison between two DB schemas, and also its performance isn't efficient enough.

It's necessary to improve this function to provide an efficient and functional schema comparison scheme to user, which could leads them to more effectively understand the difference between DBs.

The enhanced Schema Comparison Function contains:

1. Display table info comparison between DBs (in General/Detailed View)
2. Display table schema comparison between DBs
3. Provide table schema alter script (bidirectional: source->target, target->source)
4. Display entire DB schema comparison
5. Provide entire DB schema alter script (bidirectional: source->target, target->source)

Attached figures represent an example of two xe DB schemas comparison by using the updated mechanism.

  1. Alter_Script.PNG
    37 kB
    30/Oct/12 5:08 AM
  2. Detailed_View.PNG
    264 kB
    30/Oct/12 5:08 AM
  3. Entire_Compare.PNG
    91 kB
    30/Oct/12 5:08 AM
  4. General_View.PNG
    222 kB
    30/Oct/12 5:08 AM
  5. view.PNG
    124 kB
    13/Nov/12 12:27 AM

Issue Links

relates to

New Feature - A new feature of the product, which has yet to be developed. TOOLS-2611 Compare data with HA standby and active databases

  • Major - Major loss of function.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed can be reopened.

Task - A task that needs to be done. TOOLS-2308 [DEV][CM][CMT]Database schema/data comparison

  • Minor - Minor loss of function, or other problem where easy workaround is present.
  • In Progress - This issue is being actively worked on at the moment by the assignee.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
董志玲(Ella) added a comment -

I think it's better if it has a new compare function which only compare user tables(do not include system tables).Because users may be not care about the system table, they only care about the tables they use, if the cubrid version is different with each other,they must cost time to compare system tables, but system tables sometimes means nothing to users. And this compare function can coexist with entire comparision Function.

Show
董志玲(Ella) added a comment - I think it's better if it has a new compare function which only compare user tables(do not include system tables).Because users may be not care about the system table, they only care about the tables they use, if the cubrid version is different with each other,they must cost time to compare system tables, but system tables sometimes means nothing to users. And this compare function can coexist with entire comparision Function.
Hide
Permalink
高艳铭 added a comment -

Regression on CM8.4.3.3005

When there are view, the view schema isn't right.
1 In one database

CREATE TABLE "actions_users"(
"action_id" smallint,
"user_id" integer,
"page_id" integer,
"action_type_id" integer,
"action_when" datetime,
"username" character varying(4096),
"nickname" character varying(4096)
);

2 In another database.
CREATE TABLE "actions"(
"action_id" smallint,
"user_id" integer,
"page_id" integer,
"action_type_id" integer,
"source_type_id" integer,
"action_when" datetime,
"action_count" smallint,
"action_weight" smallint
);

CREATE TABLE "users"(
"user_id" integer,
"user_name" character varying(4096),
"user_email" character varying(4096),
"user_nickname" character varying(4096),
"user_firstname" character varying(4096),
"user_lastname" character varying(4096)
);

CREATE OR REPLACE VIEW "actions_users"
(
"action_id" SMALLINT,
"user_id" INTEGER,
"page_id" INTEGER,
"action_type_id" INTEGER,
"action_when" DATETIME,
"username" STRING,
"nickname" STRING
)
AS
SELECT
[actions].[action_id],
[actions].[user_id],
[actions].[page_id],
[actions].[action_type_id],
[actions].[action_when],
[users].[user_name],
[users].[user_nickname]
FROM
[actions] [actions]
LEFT OUTER JOIN
[users] [users]
ON
(
(
[actions].[user_id] = [users].[user_id]
)
)
ORDER BY 5 ;

3 Do compare schema for the database. The result is as in attachment.

Show
高艳铭 added a comment - Regression on CM8.4.3.3005 When there are view, the view schema isn't right. 1 In one database CREATE TABLE "actions_users"( "action_id" smallint, "user_id" integer, "page_id" integer, "action_type_id" integer, "action_when" datetime, "username" character varying(4096), "nickname" character varying(4096) ); 2 In another database. CREATE TABLE "actions"( "action_id" smallint, "user_id" integer, "page_id" integer, "action_type_id" integer, "source_type_id" integer, "action_when" datetime, "action_count" smallint, "action_weight" smallint ); CREATE TABLE "users"( "user_id" integer, "user_name" character varying(4096), "user_email" character varying(4096), "user_nickname" character varying(4096), "user_firstname" character varying(4096), "user_lastname" character varying(4096) ); CREATE OR REPLACE VIEW "actions_users" ( "action_id" SMALLINT, "user_id" INTEGER, "page_id" INTEGER, "action_type_id" INTEGER, "action_when" DATETIME, "username" STRING, "nickname" STRING ) AS SELECT [actions] . [action_id] , [actions] . [user_id] , [actions] . [page_id] , [actions] . [action_type_id] , [actions] . [action_when] , [users] . [user_name] , [users] . [user_nickname] FROM [actions] [actions] LEFT OUTER JOIN [users] [users] ON ( ( [actions] . [user_id] = [users] . [user_id] ) ) ORDER BY 5 ; 3 Do compare schema for the database. The result is as in attachment.
Hide
Permalink
Ray Yin added a comment -

Thanks for the report and suggestion, the function has been updated with the following changes:

1) Remove system tables from schema text comparison
2) Fix the issues about if there is an view whose name is identical with a table existed in the compared database

Show
Ray Yin added a comment - Thanks for the report and suggestion, the function has been updated with the following changes: 1) Remove system tables from schema text comparison 2) Fix the issues about if there is an view whose name is identical with a table existed in the compared database
Hide
Permalink
Ray Yin added a comment -

Please let me know if there is any problem or suggestion, thanks

Show
Ray Yin added a comment - Please let me know if there is any problem or suggestion, thanks
Hide
Permalink
高艳铭 added a comment -

Fixed in CM8.4.3.3009

Show
高艳铭 added a comment - Fixed in CM8.4.3.3009
Hide
Permalink
高艳铭 added a comment -

Fixed in CQB8.4.3.3009

Show
高艳铭 added a comment - Fixed in CQB8.4.3.3009

People

  • Assignee:
    高艳铭
    Reporter:
    Ray Yin
Vote (0)
Watch (3)

Dates

  • Created:
    Updated:
    Resolved: