CN111752546A - Excel-based database object design management platform, system and method - Google Patents
Excel-based database object design management platform, system and method Download PDFInfo
- Publication number
- CN111752546A CN111752546A CN202010612714.3A CN202010612714A CN111752546A CN 111752546 A CN111752546 A CN 111752546A CN 202010612714 A CN202010612714 A CN 202010612714A CN 111752546 A CN111752546 A CN 111752546A
- Authority
- CN
- China
- Prior art keywords
- database
- excel
- constraint
- information
- index
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/20—Software design
- G06F8/24—Object-oriented
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- General Engineering & Computer Science (AREA)
- Software Systems (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
- Stored Programmes (AREA)
Abstract
The invention discloses a database object design management platform, a system and a method based on Excel. The database object design management platform comprises: the system comprises a database reading module, a design module, an Excel reading module, a comparison module, an Sql generating module and an execution module. The invention integrates the design, display and management of the database object structure, is convenient and visual, manages Excel-based documents, and can realize various functions of automatic synchronization, batch modification, multiplexing and the like.
Description
Technical Field
The invention belongs to the technical field of databases, and particularly relates to a database object design management platform, system and method based on Excel.
Background
The database technology is one of the commonly used technologies in current software development, is used for performing operations such as addition, query, update, deletion and the like on a large amount of data, and is widely applied to various projects. In using a database, the design and management of objects in the database is a very important part of it.
However, design software of a common database, such as powerdigner and the like, can design database objects in the manner of an ER diagram and the like, and can intuitively show association relations between the database objects, such as the association between tables, but the design process of the objects is complex, for example, jump involving different interfaces; in addition, because the main interface is separated from the design interface, the main interface displays the design of the whole database, so that all information related to a single object cannot be comprehensively displayed on a single interface, for example, the self-increment attribute of a column cannot be displayed on the main interface, and whether the self-increment attribute of the column can be checked only by entering the design part; constraints, indexes, etc. objects associated with columns of the table, their association with the columns may not be displayed in the main interface. And thus are not intuitive in presenting table-centric database design information.
In addition, the data flow of such design software is unidirectional, that is, they can convert the design into Sql script finally to generate the database, or can import the database into the design model, but often cannot be modified or deleted by comparing the difference between the design of the current database and the database in use, so as to achieve the purpose of synchronizing the design to a certain established database.
Database management software such as SQL SERVER MANAGEMENT STUDIO can realize the design and management of a database, but the software can not integrally and intuitively display all information related to a single table; the database management software can integrate design and management, but usually aims at a single database object and cannot modify in batches; in addition, a state of the current database design cannot be saved in the form of text and the like, that is, a version of the database design cannot be saved for subsequent reuse.
In summary, there are some limitations in the management and design of current databases: the method has the problems that the browsing is inconvenient due to the separation of the design and the display of the database object structure, the two-way interaction of data between the design and the database cannot be carried out, the batch operation cannot be realized on the database management through the design, the design of the database cannot be stored and reused in a certain form, and the like.
Disclosure of Invention
Aiming at least one defect or improvement requirement in the prior art, the invention provides a database object design management platform, a system and a method based on Excel, which integrate the design, display and management of a database object structure into a whole, are convenient and intuitive, manage Excel-based documents, and can realize multiple functions of automatic synchronization, batch modification, multiplexing and the like.
To achieve the above object, according to a first aspect of the present invention, there is provided an Excel-based database object design management platform, including:
the database reading module is used for reading the current object structure information of the database and mapping the current object structure information of the database into an example facing a corresponding class in the object;
the design module is used for predefining an Excel database object design specification and designing database object structure information in the Excel according to the database object design specification in the Excel;
the Excel reading module is used for reading the database object structure information designed in the Excel platform according to the database object design specification in the Excel;
the comparison module is used for comparing the read current object structure information of the database with the object structure information designed by the Excel platform to create an operation object set;
the Sql generating module is used for generating a Sql execution statement set according to the created operation object set;
and the execution module is used for executing the generated Sql execution statement set and modifying the database.
Preferably, the database reading module includes: the table structure reading module is used for reading the current table information of the database and mapping the table information into a table object in an object-oriented mode; the index reading module is used for reading the current index information of the database and mapping the current index information into an index object in an object-oriented mode; the constraint reading module is used for reading the current constraint information of the database and mapping the current constraint information into a constraint object in an object-oriented way;
the database object design specification in the Excel comprises design specifications of tables, constraints and indexes;
the Excel reading module is used for reading table information, index information and constraint information contained in Excel from an Excel platform and mapping the table information, the index information and the constraint information into object-oriented table objects, index objects and constraint objects;
the alignment module comprises: the table structure comparison module is used for comparing the read current table object of the database with the table object in the Excel to generate an operation object for adding, deleting or modifying the table; the index comparison module is used for comparing the read current index object of the database with the index object in the Excel to generate an operation object for adding, deleting or modifying the index; and the constraint comparison module is used for comparing the read current constraint object of the database with the constraint object in the Excel to generate an operation object for adding, deleting or modifying the constraint.
Preferably, the constraint objects include primary key constraints, foreign key constraints, unique constraints, default constraints, check constraints, and non-null constraints.
Preferably, the comparison module is further configured to rank the plurality of operation objects in the operation object set.
Preferably, the database object design management platform based on Excel further includes a Sql buffer pool for caching the generated Sql execution statements, and the execution module reads and executes the generated Sql execution statements from the Sql buffer pool.
According to a second aspect of the present invention, there is provided an Excel-based database object design management system, comprising: an Excel platform, a database and any one of the Excel-based database object design management platforms.
According to a third aspect of the present invention, there is provided a database object design management method based on Excel, including the steps of:
predefining an Excel database object design specification, and designing database object information in Excel according to the Excel database object design specification;
reading current object structure information in a database, and mapping the object structure information in the database into an instance facing a corresponding class in an object;
reading data structure information contained in an Excel platform according to a database object design specification in the Excel, and converting a database object in the Excel into an instance of a corresponding class in an object-oriented object;
comparing the read current object structure information of the database with the object structure information designed in the Excel platform to create an operation object set;
the Sql generating module is used for generating a Sql execution statement set according to the created operation object set;
and the execution module is used for executing the generated Sql execution statement set and modifying the database.
In general, compared with the prior art, the invention has the following beneficial effects:
(1) the method integrates the design, display and management of the database object structure, is convenient and visual, is based on Excel documents, and can achieve multiple functions of automatic synchronization, batch modification, multiplexing and the like.
(2) The design and display of the database object structure are realized through Excel, and all information related to a single object can be comprehensively displayed on a single interface.
(3) And performing data interaction between the design and the database, and comparing the difference between the design of the current database and the database in use to modify or delete the design so as to achieve the aim of synchronizing the design to a certain established database.
(4) When the object structure or definition is modified in Excel, the system can generate corresponding Sql statements for all modifications, and execute in batch to complete the modification.
(5) One state of the current database design can be stored in an Excel table form, namely one version of the database design is stored and reserved for subsequent multiplexing.
(6) The design and management of tables, constraints, indexes, all of which are contained in the design of tables, are fused together.
Drawings
FIG. 1 is a functional diagram of an Excel-based database object design management system according to an embodiment of the present invention;
FIG. 2 is an architecture diagram of an Excel-based database object design management system according to an embodiment of the present invention;
FIG. 3 is a database object design specification according to an embodiment of the present invention;
FIG. 4 is a state diagram for reading database object design information in an Excel platform according to an embodiment of the present invention;
FIG. 5 is a class diagram of an object abstracted from a database, in accordance with an embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention. In addition, the technical features involved in the embodiments of the present invention described below may be combined with each other as long as they do not conflict with each other.
As shown in fig. 1, a database object design management system based on Excel according to an embodiment of the present invention includes a database platform, an Excel platform, and a management platform. The system can design and manage objects in the database. Objects that the system may design and manage may include: table, constraint, index. The system fuses the design and management of tables, constraints, indexes, all of which are contained in the design of tables. The design of the system for the database platform is based on an Excel platform, the management is based on a management platform, and the management platform is connected with the Excel platform through an Excel reading module and is connected with the database platform through a database reading module.
As shown in fig. 2, the database object design management platform based on Excel according to the embodiment of the present invention includes a database reading module (module 1 in fig. 2), an Excel reading module (module 2 in fig. 2), a comparison module (module 3 in fig. 2), an Sql generating module (module 4 in fig. 2), an execution module (module 5 in fig. 2), and a design module (module 6 in fig. 2). And the design module is used for predefining an Excel database object design specification and designing the database object structure information in the Excel according to the database object design specification in the Excel. The database reading module is used for reading the table, constraint and index structure information in the database and mapping the table, constraint and index structure information into table objects, constraint objects and index objects in the object-oriented system; the Excel reading module is used for reading table, constraint and index information in the Excel platform and mapping the table, constraint and index information into a table object, a constraint object and an index object in an object-oriented manner; the comparison module is used for comparing the read database object with the corresponding object of the Excel platform to create an operation object set; the Sql generating module is used for generating Sql execution statements according to the created operation object set; and the execution module is used for executing the generated Sql execution statement and modifying the database. Taking SQLSERVER2008 as a database platform as an example, the working principle of the database object design management platform based on Excel is specifically explained.
The database object design specification in Excel is shown in FIG. 3, wherein a table is taken as a center, a design and storage frame of table, constraint and index in Excel is provided, and the design and storage frame is divided into fields, types, description, main keys, foreign keys, unique keys, self-increment, permission to be blank, default values, ranges, indexes, index types and remarks, wherein the description and remarks are used for reminding a designer of the designer.
The design method in Excel is as follows:
the design information for marking a certain table object starts with "(ta: table name)".
The first column of row data is the column name.
The row data second column is the column data type.
The fourth column of the row data is primary key information, if the primary key in the table only contains one column, the 'yes' is directly filled in the primary key column of the column, and if the primary key in the table contains multiple columns, the same primary key name is filled in the primary key column of the primary key containing column, and the primary key name is set arbitrarily.
The fifth column of the row data is the foreign key information, and if a certain column in a certain table is referred by a certain column in the table, the name of a reference table (referred column name) is directly filled in the foreign key column.
The sixth column of row data is unique key information, if the unique key includes a single column, "yes" is filled directly in the column, and if the unique key includes a plurality of columns, the same unique key name is filled in the unique key column including the column.
Column number seven of row data is a self-increment message, "yes" indicating that the column is self-incrementing and defaults to non-self-incrementing.
The eighth column of row data is non-null information, and "yes" indicates nullable, and is non-null by default.
The ninth column of line data is default information, and the default can be directly filled in.
The tenth column of row data is inspection information where the inspection constraints support the usual data ranges and enumerations. "(" represents an unachievable minimum value, "[" represents an acquirable minimum value, ")" represents an unachievable maximum value; "]" indicates the maximum value obtainable; "{ }" denotes an enumerated type, enumerated values are used, and "separated".
The eleventh column of line data is index information, and if an index is created on a certain column alone, "yes" is filled in the index field of the column, and if an index is created on a plurality of columns, the same index name is filled in the columns.
The line data is twelfth column of index type, 1 denotes a clustered index, 2 denotes a non-clustered index, and defaults to a non-clustered index.
The same class objects in the database are abstracted into classes as shown in fig. 5. Mainly includes DATABASE, TABLE COLUMN, CONSTRAINT (CONSTRAINT, the CONSTRAINT is parent class, and the following is subclass of six different CONSTRAINTs), INDEX.
The database class comprises a library name, an address, a port, a login name and a password;
the column class comprises column names and column types, and can be empty or self-increment;
the table class comprises a table name, a constraint dictionary, a column dictionary and an index dictionary;
the index class comprises an index name and a column dictionary;
the constraint class comprises a constraint name and a column dictionary;
checking that the constraints further comprise rules;
the default constraints also include default values;
non-null constraints also include can be null.
Preferably, the database reading module is implemented as follows:
the database reading module comprises: a table structure reading module (module 11 in fig. 2) for reading the current table information in the database and mapping the table information into a table object; an index reading module (module 12 in fig. 2) for reading the current index information of the database and mapping the current index information into an index object; and a constraint reading module (a module 13 in fig. 2) for reading the current constraint information of the database and mapping the constraint information to a constraint object.
And the table structure reading module reads the table structure information in the current database from the SQLSERVER database data dictionary and caches the table structure information.
The structure information of the table can be obtained by connecting sys.tabes and sys.column, can obtain the table name, the column type, the column self-increment and the column empty, can be specifically obtained by the Sql statement and is mapped into the table object.
The index reading module reads index information in the database, and can acquire an index name, an index belonging table, a column contained in the index and whether the index is a cluster index by connecting SYS.TABLES, SYS.COLUMNS and SYS.INDEXES, specifically can acquire the index through an Sql statement and map the index into an index object.
The constraint reading module reads constraint information in the SQLSERVER database, wherein the constraint information comprises a main key constraint, an external key constraint, a default constraint, a non-empty constraint, a check constraint and a unique constraint.
By associating INFORMATION _ scope _ key _ COLUMN _ USAGE, INFORMATION _ scope _ table _ configuration, the primary key INFORMATION, including the primary key name, the table to which the primary key belongs, and the primary key containing COLUMN, can be obtained, and can be specifically obtained through the Sql statement and mapped into the primary key object.
Foreign KEY INFORMATION including a foreign KEY name, a reference table name, a COLUMN of a reference table, a referenced table name, a COLUMN of a referenced table can be acquired by associating sys.
The related information of the default constraint can be obtained by associating SYS.DEFAULT _ CONSTRATRANTS and SYS.TABLES, and comprises a default constraint name, a default constraint list name, a default constraint column name and a default constraint value, and the related information of the default constraint can be obtained by Sql statements and is mapped into a default constraint object.
The check constraint related information including a check constraint name, a table name to which the check constraint belongs, a check constraint column name and a rule can be obtained by associating sys.
The related INFORMATION of the unique constraint, including the unique constraint name, the table to which the unique constraint belongs and the COLUMN included in the unique constraint, can be obtained by associating INFORMATION _ parameter, configuration _ COLUMN _ USAGE and INFORMATION _ parameter.
Preferably, the Excel reading module is implemented as follows:
preferably, the design of the current database object is obtained from Excel according to the specification shown in fig. 3, and the information of the object is obtained by reading Excel line by using OLEDB.
As shown in fig. 4, during the process of reading Excel row by row, the following five states are respectively switched.
Reading non-null data, and obtaining non-null Excel data through OLEDB.
② reading the object name, wherein the object name is contained in "()" and expressed as ": "separate, as in FIG. 3 the object name is TA _ TEST.
Reading an object information structure, wherein the object information structure is a specification of a storage table, a column, a constraint and an index in Excel, and comprises fields, types, descriptions, a main key, an external key, a unique key, self-increment, permission of null, a default value, a range, an index type and remarks.
And fourthly, reading the object information, wherein the object information is the specific data stored in the object information structure.
Reading empty, namely ending state, and finishing object information reading.
As shown in fig. 4, the specific reading process may be implemented as follows.
Reading the data of the worksheet in the Excel line by line, directly entering a state 5 in the figure 4 when the read line data are empty, continuing to read the data if the data exist in the current Excel worksheet, and ending the Excel reading if the data do not exist in the Excel worksheet. And reading the line data, and entering the state 1 in the figure 4 when the read line data is not empty. The object names in the row data are extracted by regular expression packets after entering state 1, the regular expression being "· (TA: (. If the table name is empty, the state 5 in fig. 4 is entered, and if not, the state 2 is entered. Reading the line data, if the line data is not empty, and the first column in the line data is 'field', the second column is 'type', the current structure information of the table name is not empty, entering a state 3, otherwise entering a state 5, reading the line data after entering the state 3, entering a state 4 if the line data is not empty, reading the information of the current object when the read line data is not empty, including the field name, the field type and the like, until the read line data is empty, and entering the state 5.
Reading the table information at state 4 in fig. 4 is shown in fig. 3, and also includes three types of table object, index object and constraint object, specifically, includes field, type, primary key, foreign key, unique key, self-increment, null, default value, range, index and index type, and puts the table into the cache.
Preferably, the alignment module is implemented as follows:
comparing the object cached from the database with the object read from the Excel cache, wherein the comparison result can generate an operation class object, and the operation class object comprises the following components: firstly, operating targeted objects, namely table objects, constraint objects, index objects and column objects; object type (possibly table type, column type, constraint type, index type); operation type (addition, deletion, modification).
Preferably, the alignment module comprises: the table structure comparison module is used for comparing the read current table object of the database with the table object in the Excel to generate an operation object for adding, deleting or modifying the table; the index comparison module is used for comparing the read current index object of the database with the index object in the Excel to generate an operation object for adding, deleting or modifying the index; and the constraint comparison module is used for comparing the read current constraint object of the database with the constraint object in the Excel to generate an operation object for adding, deleting or modifying the constraint.
The table alignment is as follows:
if a table exists in the Excel design and the table does not exist in the database (the table name is used as judgment), the operation of creating the added table is needed; if a table exists in the database and the table does not exist in Excel, an operation of deleting the table needs to be created.
And comparing whether the table structures of the same table in the database and the Excel are the same or not. If a certain column exists in a certain table of Excel and the corresponding table does not exist in the database (column name judgment), an operation of adding the column needs to be created, if the certain column exists in the database and the corresponding table does not exist in the Excel, an operation of deleting the column needs to be created, and if the certain column exists in the Excel and the attributes (whether empty or not and type) of the corresponding column in the database are different, an operation of modifying the column needs to be created.
The alignment of the indices is as follows:
if an index exists in the Excel and the index does not exist in the database (the table name is used as judgment), an operation of creating the added table is needed; if an index exists in the database and the index does not exist in Excel, an operation of deleting the table needs to be created. And if the columns contained in the indexes in the Excel are different from the columns contained in the indexes in the database, deleting the indexes in the database by taking the indexes in the Excel as a standard, and recreating the indexes.
The alignment of constraints is as follows:
constraints can be divided into: primary key constraints, foreign key constraints, unique constraints, default constraints, check constraints.
Because only one primary key is constrained in the table, whether a primary key is included in Excel and a database on a certain table is compared, if the primary key is included in Excel but not in the database, the primary key needs to be created, if the primary key is included in the database but not in Excel, the primary key needs to be deleted, if the primary key and the primary key are both included, whether columns included in the primary key are the same needs to be compared, and if the columns are different, the primary key in Excel needs to be modified (modification comprises deletion and creation) as a criterion.
A single table can have a plurality of foreign keys, wherein the foreign key comparison is mainly based on a reference table in the foreign key and related columns in the table, and if the foreign key exists in Excel, the reference table and related columns in the foreign key do not exist in all foreign keys in the database, the foreign key is created. If a foreign key exists in Excel, the reference table of the foreign key is the same as the reference table of a foreign key in the table in the database but contains a different column, the foreign key needs to be modified (deleted and created), and if a foreign key exists in the database and the reference table contained in the foreign key does not exist in all foreign keys of the table in Excel, the deletion operation is performed.
Unique constraints can be divided into column-level constraints (constraints contain only a single column) and table-level constraints. And if the unique column-level constraint exists in the Excel and the unique column-level constraint does not exist in the same column in the database, creating an adding constraint operation, and if the unique column-level constraint exists in the database and the unique column-level constraint does not exist in the Excel, creating a deleting constraint operation. And if the Excel has a table level unique constraint and the database does not have the table level unique constraint (judged by using the constraint name), creating an adding constraint operation, and if the database has the table level unique constraint and the Excel does not have the same-name table level constraint, creating a deleting constraint operation. And if the table-level unique constraint exists in the Excel and the table-level unique constraint with the same name exists in the database but the tables and the columns depended by the table-level unique constraint and the table-level unique constraint are not identical, creating a modified unique constraint operation (the modified operation encapsulates the deletion operation and the addition operation).
The default constraint is for a certain column of the table. If a certain column of a certain table in the Excel has default constraints and the same column of the same table of the database does not have default constraints, creating an operation of adding the default constraints; if a default constraint exists on a column of a table in the database and no default constraint exists on the same column of the same table in Excel, an operation of deleting the default constraint is created. If the default constraint exists on the same column of the same table in Excel and the database, but the default values are different, a modified default constraint (modification encapsulates deletion and addition) operation is created.
The check constraint is for a column of the table. If the check constraint exists on a certain column of a certain table in the Excel and the check constraint does not exist on the same column of the same table in the database, establishing an operation of adding the check constraint; if there is a check constraint on a column of a table in the database and there is no check constraint on the same column of the same table in Excel, an operation is created to delete the check constraint. If the check constraint exists on the same column of the same table in Excel and the database, but the definition of the check constraint is different, a modified check constraint (modification encapsulates deletion and addition) operation is created. Commonly used examination constraints include: numerical range, enumeration. Since the definitions checked in SQLSERVER2008 are all stored in the form of check statements, and the check constraints in Excel are stored in the form of sets, the check sets in Excel are converted into check statements according to the rules in SQLSERVER 2008. The rules for enumeration are to generate the check statements in reverse order of the input set and in OR connection enumeration. The range type is then the positive order of the input ranges AND is connected as an AND.
The set of operation class objects that will be generated after the comparison, the operation objects include database objects (tables, constraints, indexes) and database object actions (creation, deletion, modification).
Preferably, the comparing module is further configured to rank the plurality of operation objects in the operation object set. Because of the dependency relationship between the operations (e.g., creating a primary key presupposes that a table exists, creating a foreign key presupposes that a reference table and a referred table exist, and the referred table is created before the reference table). The operation set requires a numbering ordering. The rules for ordering are as follows:
the table creation operation is performed first.
And if the table has a foreign key, the sequence of the referred table is arranged before the reference table.
And the other operation sequences are unchanged.
And the Sql generation module is used for converting the operation class generated by the comparison module into a corresponding DDL statement according to the grammar specification of the database, and the parameters in the DDL statement can be acquired through the action in the operation class object and the structure information of the operation class object.
NET executes the generated Sql statement one by one.
Preferably, the database object design management platform based on Excel further includes a Sql buffer pool for caching the generated Sql execution statements, and the execution module reads and executes the generated Sql execution statements from the Sql buffer pool.
The database object design management method based on Excel in the embodiment of the invention comprises the following steps:
predefining an Excel database object design specification, and designing database object information in Excel according to the Excel database object design specification;
reading the current object structure information of the database, and mapping the current object structure information into an object oriented to the object;
reading database object structure information contained in an Excel platform, and mapping the database object structure information into an object oriented to the object;
comparing the read current object structure information of the database with the object structure information designed in the Excel platform to create an operation object set;
the Sql generating module is used for generating a Sql execution statement set according to the created operation object set;
and the execution module is used for executing the generated Sql execution statement set and modifying the database.
Preferably, the mapping of the data objects into the database includes the steps of: reading the current table information of the database, and mapping the table information into a table object; reading the current index information of the database, and mapping the current index information into an index object; reading the current constraint information of the database, and mapping to a constraint object;
the data object mapped to the Excel platform specifically includes: reading table information, index information and constraint information in Excel from an Excel platform, and mapping the table information, the index information and the constraint information into a table object, an index object and a constraint object in an object-oriented mode;
the comparison comprises the following steps: comparing the read current table object of the database with the table object in the Excel platform to generate an operation object for adding, deleting or modifying the table; comparing the read current index object of the database with the index object in the Excel platform to generate an operation object for adding, deleting or modifying the index; and comparing the read current constraint object of the database with the constraint object in the Excel platform to generate an operation object for adding, deleting or modifying the constraint.
Preferably, the constraint objects include primary key constraints, foreign key constraints, unique constraints, default constraints, check constraints, and non-null constraints.
Preferably, the alignment further comprises the steps of: and sorting the plurality of operation objects in the operation object set.
The implementation principle and technical effect of the Excel-based database object design management method are similar to those of the management platform, and are not described herein again.
It must be noted that in any of the above embodiments, the methods are not necessarily executed in order of sequence number, and as long as it cannot be assumed from the execution logic that they are necessarily executed in a certain order, it means that they can be executed in any other possible order.
It will be understood by those skilled in the art that the foregoing is only a preferred embodiment of the present invention, and is not intended to limit the invention, and that any modification, equivalent replacement, or improvement made within the spirit and principle of the present invention should be included in the scope of the present invention.
Claims (10)
1. An Excel-based database object design management platform, comprising:
the database reading module is used for reading the current object structure information of the database and mapping the current object structure information of the database into an example facing a corresponding class in the object;
the design module is used for predefining an Excel database object design specification and designing database object structure information in the Excel according to the database object design specification in the Excel;
the Excel reading module is used for reading the database object structure information designed in the Excel platform according to the database object design specification in the Excel;
the comparison module is used for comparing the read current object structure information of the database with the object structure information designed by the Excel platform to create an operation object set;
the Sql generating module is used for generating a Sql execution statement set according to the created operation object set;
and the execution module is used for executing the generated Sql execution statement set and modifying the database.
2. The Excel-based database object design management platform according to claim 1, wherein the database reading module comprises: the table structure reading module is used for reading the current table information of the database and mapping the table information into a table object in an object-oriented mode; the index reading module is used for reading the current index information of the database and mapping the current index information into an index object in an object-oriented mode; the constraint reading module is used for reading the current constraint information of the database and mapping the current constraint information into a constraint object in an object-oriented way;
the database object design specification in the Excel comprises design specifications of tables, constraints and indexes;
the Excel reading module is used for reading table information, index information and constraint information contained in Excel from an Excel platform and mapping the table information, the index information and the constraint information into object-oriented table objects, index objects and constraint objects;
the alignment module comprises: the table structure comparison module is used for comparing the read current table object of the database with the table object in the Excel to generate an operation object for adding, deleting or modifying the table; the index comparison module is used for comparing the read current index object of the database with the index object in the Excel to generate an operation object for adding, deleting or modifying the index; and the constraint comparison module is used for comparing the read current constraint object of the database with the constraint object in the Excel to generate an operation object for adding, deleting or modifying the constraint.
3. The Excel-based database object design management platform in accordance with claim 2, wherein the constraint objects comprise primary key constraints, foreign key constraints, unique constraints, default constraints, check constraints and non-null constraints.
4. The Excel-based database object design management platform according to claim 1, 2 or 3, wherein the comparison module is further configured to order all the operation objects in the operation object set.
5. The Excel-based database object design management platform according to claim 1, 2 or 3, further comprising a Sql buffer pool for caching generated Sql execution statements, wherein the execution module reads and executes the generated Sql execution statements from the Sql buffer pool.
6. An Excel-based database object design management system, comprising: excel platform, database and Excel-based database object design management platform according to anyone of claims 1 to 5.
7. A database object design management method based on Excel is characterized by comprising the following steps:
predefining an Excel database object design specification, and designing database object information in Excel according to the Excel database object design specification;
reading current object structure information in a database, and mapping the object structure information in the database into an instance facing a corresponding class in an object;
reading data structure information contained in an Excel platform according to a database object design specification in the Excel, and converting a database object in the Excel into an instance of a corresponding class in an object-oriented object;
comparing the read current object structure information of the database with the object structure information designed in the Excel platform to create an operation object set;
the Sql generating module is used for generating a Sql execution statement set according to the created operation object set;
and the execution module is used for executing the generated Sql execution statement set and modifying the database.
8. The Excel-based database object design management method according to claim 7, wherein said mapping into classes in object-oriented comprises the steps of: reading the current table information of the database, and mapping the table information into a table object in an object-oriented mode; reading the current index information of the database, and mapping the current index information into an index object in an object-oriented mode; reading the current constraint information of the database, and mapping the constraint information into a constraint object in an object-oriented mode;
the data object mapped into the Excel platform specifically comprises: reading table information, index information and constraint information contained in Excel from an Excel platform, and mapping the table information, the index information and the constraint information into a table object, an index object and a constraint object in an object-oriented mode;
the alignment comprises the steps of: comparing the read current table object of the database with the table object in the Excel platform to generate an operation object for adding, deleting or modifying the table; comparing the read current index object of the database with the index object in the Excel platform to generate an operation object for adding, deleting or modifying the index; and comparing the read current constraint object of the database with the constraint object in the Excel platform to generate an operation object for adding, deleting or modifying the constraint.
9. The method for managing Excel-based database object design according to claim 8, wherein the constraint objects include primary key constraints, foreign key constraints, unique constraints, default constraints, check constraints, and non-null constraints.
10. The Excel-based database object design management method according to claim 7, 8 or 9, wherein the comparison further comprises the steps of: and sorting the plurality of operation objects in the operation object set.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010612714.3A CN111752546A (en) | 2020-06-30 | 2020-06-30 | Excel-based database object design management platform, system and method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010612714.3A CN111752546A (en) | 2020-06-30 | 2020-06-30 | Excel-based database object design management platform, system and method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN111752546A true CN111752546A (en) | 2020-10-09 |
Family
ID=72678345
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010612714.3A Pending CN111752546A (en) | 2020-06-30 | 2020-06-30 | Excel-based database object design management platform, system and method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111752546A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113779955A (en) * | 2021-09-15 | 2021-12-10 | 中国农业银行股份有限公司 | Generation method and device of difference script and storage medium |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107357942A (en) * | 2017-09-04 | 2017-11-17 | 山东达创网络科技股份有限公司 | A kind of source code auxiliary Core Generator and its generation method |
| CN108037916A (en) * | 2017-11-29 | 2018-05-15 | 福州市智捷信息科技有限公司 | A kind of graphical service modeling method based on web |
| CN109299332A (en) * | 2018-11-02 | 2019-02-01 | 芜湖智久机器人有限公司 | A kind of method, apparatus and storage medium by class and Database Mapping |
| CN111291226A (en) * | 2020-02-20 | 2020-06-16 | 山东爱城市网信息技术有限公司 | System and method for auditing design document table and database table |
-
2020
- 2020-06-30 CN CN202010612714.3A patent/CN111752546A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107357942A (en) * | 2017-09-04 | 2017-11-17 | 山东达创网络科技股份有限公司 | A kind of source code auxiliary Core Generator and its generation method |
| CN108037916A (en) * | 2017-11-29 | 2018-05-15 | 福州市智捷信息科技有限公司 | A kind of graphical service modeling method based on web |
| CN109299332A (en) * | 2018-11-02 | 2019-02-01 | 芜湖智久机器人有限公司 | A kind of method, apparatus and storage medium by class and Database Mapping |
| CN111291226A (en) * | 2020-02-20 | 2020-06-16 | 山东爱城市网信息技术有限公司 | System and method for auditing design document table and database table |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113779955A (en) * | 2021-09-15 | 2021-12-10 | 中国农业银行股份有限公司 | Generation method and device of difference script and storage medium |
| CN113779955B (en) * | 2021-09-15 | 2023-11-03 | 中国农业银行股份有限公司 | Method, device and storage medium for generating difference script |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11907247B2 (en) | Metadata hub for metadata models of database objects | |
| US6374252B1 (en) | Modeling of object-oriented database structures, translation to relational database structures, and dynamic searches thereon | |
| US5937409A (en) | Integrating relational databases in an object oriented environment | |
| US7096231B2 (en) | Export engine which builds relational database directly from object model | |
| CN107038222B (en) | Database cache implementation method and system | |
| US20040260715A1 (en) | Object mapping across multiple different data stores | |
| US8874619B2 (en) | Method and apparatus for defining common entity relationships | |
| US20080301168A1 (en) | Generating database schemas for relational and markup language data from a conceptual model | |
| US20060200438A1 (en) | System and method for retrieving data from a relational database management system | |
| CN111708804B (en) | Data processing method, device, equipment and medium | |
| KR20060045622A (en) | Extraction, transformation and loading designer module of a computerized financial system | |
| US9495475B2 (en) | Method of representing an XML schema definition and data within a relational database management system using a reusable custom-defined nestable compound data type | |
| CN103559189B (en) | Electric analog training resource management system and method based on Metadata integration model | |
| JP2006244498A (en) | Data model for object relational data | |
| CN109840256A (en) | A kind of inquiry implementation method based on Business Entity | |
| US11341142B2 (en) | Framework and metadata artefacts for updating data artefacts | |
| CN105447051A (en) | Database operation method and device | |
| JP5090481B2 (en) | Data modeling method, apparatus and program | |
| CN103246704A (en) | Mapping method for describing entity and relational data based on conventional data structure | |
| CN111241065B (en) | Database adaptation development and operation method supporting domestic database | |
| CN113342325A (en) | Visual modeling method, system, electronic device and storage medium | |
| US11561976B1 (en) | System and method for facilitating metadata identification and import | |
| CN111752546A (en) | Excel-based database object design management platform, system and method | |
| US11080332B1 (en) | Flexible indexing for graph databases | |
| WO2023151239A1 (en) | Micro-service creation method and related device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |