CN115269590A

CN115269590A - Matrix type user permission index implementation method and system on PostgreSQL and electronic equipment

Info

Publication number: CN115269590A
Application number: CN202210797536.5A
Authority: CN
Inventors: 伍仪强; 邓彬彬
Original assignee: Wuhan Huaxin Technology Co ltd
Current assignee: Wuhan Huaxin Technology Co ltd
Priority date: 2022-07-05
Filing date: 2022-07-05
Publication date: 2022-11-01

Abstract

The invention discloses a matrix type user authority index implementation method and system on PostgreSQL, wherein the method comprises the following steps: establishing a matrix data model according to an organization structure of a system and a relation between users and data, wherein the matrix data model comprises a user authority matrix and a data attribution matrix; respectively extracting the user authority matrix and the data attribution matrix line by line according to a preset extraction rule to obtain a user authority key and a data attribution unit; according to a preset comparison rule, comparing each user authority key with all the data attribution units one by one to obtain each user authority key and a user data list corresponding to the user authority key; forming a GIN index storage structure according to all the user authority secret keys and the corresponding user data lists; adding a matrix data type in PostgreSQL, and constructing an operational character corresponding to the matrix data type; and defining a support function required by the GIN index, and realizing the GIN index based on the operator and the support function.

Description

Matrix type user permission index implementation method and system on PostgreSQL and electronic equipment

Technical Field

The invention relates to a postgreSQL, a postgreSQL operational character and a postgreSQL index, in particular to a matrix type user authority index implementation method and system on the postgreSQL.

Background

The postgreSQL is an open source object relational database system, and when the amount of data stored in a database is larger and more complex, querying some data according to conditions becomes tedious, and the querying speed is reduced.

The above problem can be solved using the index in PostgreSQL. The index is a data structure, data in the database is stored according to a certain data structure algorithm, and a proper index is established to help people to quickly retrieve the data in the database and quickly locate records which possibly meet conditions without traversing all the records.

The index types supported by the PostgreSQL currently include B-tree, hash, giST, SP-GiST, GIN, BRIN and the like. The applicable scenarios for each index type are different. For example, B-tree is used primarily for processing equivalence and range queries on orderable data, and B-tree indices may be considered for data queries when these operators are involved in < < < = > = > operations. The hash index stores the hash VALUE of the indexed field VALUE, and only supports equivalent queries. GIN (Generalized Inverted Index) is an Index structure that stores a set of pairs (keys), and currently supports data types such as arrays and full-text search.

With the continuous development of services, the control of the system on the user authority is more and more refined, for example, in a self-examination service, the user can be divided into students, teachers and managers, wherein the managers are divided into six levels, namely schools, sites, teaching points, professions, grades and classes. It is simple for a student to know whether a student has an operation right on certain data, because the relationship between the student and the data is one-to-one, the student can only operate the data related to the student, and PostgreSQL can quickly find the corresponding data by using = operator and B-tree index. For a teacher, the teacher can manage a plurality of classes, one piece of data can belong to the plurality of classes, the relationship between users and the data is a many-to-many relationship, the authorization of the data can be stored in an array form, and PostgreSQL can quickly search the corresponding data by using & & operator and GIN index.

Both students and teachers can follow PoOperators and indexes provided by stgreSQL are used for completing quick search of data, but for an administrator, the administrator has multiple role identities, different roles also have a hierarchical relationship, for example, a user in the system is a site administrator of 1 school 1 site and a teaching point administrator of 2 school 1 site 1 teaching points, and the matrix representation is that

For such a matrixed user, we want to know which data he has operation right, and there are no proper operators and indexing methods supporting matrixed data types in PostgreSQL to handle the relationship between the user and the data.

Accordingly, the present invention provides a custom operator and an indexing method capable of supporting matrixed data types to solve the above problems.

Disclosure of Invention

In order to overcome the defects in the prior art, the present invention provides a method and a system for implementing matrix type user rights indexing on PostgreSQL, so as to solve at least one technical problem described above.

According to an aspect of the present disclosure, a method for implementing indexing of a matrix user right on PostgreSQL is provided, including: constructing an operational character to realize the conversion of the matrix data, and realizing the storage of the converted matrix data on the basis of GIN indexes;

wherein constructing the operator to effect the conversion of the matrix data further comprises:

establishing a matrix data model according to an organization architecture of a system and a relation between a user and data, wherein the matrix data model comprises a user authority matrix and a data attribution matrix;

respectively extracting the user authority matrix and the data attribution matrix line by line according to a preset extraction rule to obtain a user authority key and a data attribution unit;

according to a preset comparison rule, comparing each user authority key with all the data attribution units one by one to obtain each user authority key and a user data list corresponding to the user authority key;

forming a GIN index storage structure according to all the user authority keys and the corresponding user data lists;

realizing storage of the converted matrix data on the basis of the GIN index, and further comprising the following steps of:

adding a matrix data type in the PostgreSQL, and constructing an operational character corresponding to the matrix data type;

defining five support functions required by the GIN index, and realizing the index of the GIN index storage structure based on the operator and the support functions.

According to the technical scheme, the relation between matrix data is processed by constructing the operational characters, and the matrix data storage is realized by expanding an access method supporting the matrix data type on the basis of GIN indexing, so that the matrix data can be quickly indexed under a GIN framework, and the problem that a matrix user is difficult to determine which data have operation authority is solved.

The matrix type user means that one user corresponds to a plurality of nodes, the plurality of nodes have authority, each node generates a large amount of use data, and the user authority data and the use data generated by the user are expressed by adopting a matrix.

Because each node generates a large amount of data in the system operation process, the storage space requirement is large, and when the authority of a certain matrix type user is queried according to the existing method, the index number is large, the calculation cost is large, and it is difficult to rapidly query which data the user has the operation authority. Therefore, the method extracts and compares system data according to preset logic by constructing the custom operator to obtain a storage structure suitable for the GIN index; and then, under a GIN framework, importing matrix data by adding matrix data types and constructing an operator corresponding to the matrix data types, defining five support functions required by a GIN index, and performing semantic analysis on the imported and stored matrix data so as to realize GIN index storage of the matrix data.

As a further technical scheme, the organization architecture of the system comprises six layers of organization nodes including schools, sites, teaching points, specialties, grades and classes, wherein each row of the user authority matrix corresponds to one authority data of a user, and each column corresponds to one organization node; each row of the data attribution matrix corresponds to attribution of one piece of data, and each column corresponds to one organization node.

For the data attribution matrix, the data may be usage data generated by a student, a teacher or an administrator, or resource data uploaded by a student, a teacher or an administrator, and regardless of the usage data generated in the process or the resource data uploaded by the process, there is a relationship that one piece of data belongs to a plurality of nodes, for example, if a student goes class in two classes, the usage data generated by the student has an attribution relationship with the two class nodes, and belongs to both data of class a and data of class B, so in the data attribution matrix, each row of the matrix is represented as an attribution of one piece of data, which represents that the data is hung under the attribution relationship.

Further, for resource data, such as curriculum resource data uploaded by an administrator, which is shared by multiple schools or sites, is suspended below multiple organization nodes, so that each row of the resource data matrix represents attribution data of one organization node.

Because the organization nodes under the organization architecture are layered more in the existing teaching management system, and an administrator usually integrates the management work of a plurality of organization nodes, a many-to-many relationship of a plurality of administrators to a plurality of organization nodes is formed in the whole system, and the many-to-many relationship is more suitable for being expressed by adopting a matrix structure. Furthermore, the user authority data and the user use data are separated, different matrix structures are constructed, and user authority management is facilitated.

As a further technical scheme, six layers of organization nodes of schools, sites, teaching points, professions, grades and classes are abstractly represented as A, B, C, D, E, F respectively, each row in a user authority matrix and a user data matrix represents AaBbcDdEff, wherein a represents a school number, b represents a site number, c represents a teaching point number, d represents a professional number, e represents a grade number, and f represents a class number; and constructing and forming a matrix data model according to the authority of the user at each organization node and the data generated by the user at each organization node.

By the abstract expression mode, the matrix data are converted into a column of abstract data, and the storage space is reduced. And the abstracted data formats are the same, so that logical operation is conveniently carried out through operators, and the rapid conversion of matrix data is realized.

As a further technical solution, for the user authority matrix, the extraction rule is: if the user has authority at a certain organization node, extracting the corresponding organization node in the matrix as a node number, and extracting a lower node of the organization node as zero; and after extraction is finished, obtaining the authority data of each row of the user authority matrix.

As a further technical solution, for the data attribution matrix, the extraction rule is: if the user generates data at a certain organization node, extracting the corresponding organization node in the matrix as a node number, and extracting a lower node of the organization node as zero; and after extraction is finished, obtaining attribution data of each row of the data attribution matrix.

By extracting the matrix data line by line, one row of each user authority matrix corresponds to one row of authority data, one row of each data attribution matrix corresponds to one row of attribution data, the authority data and the attribution data are in the same format, and the judgment of the user authority of the user on each node can be realized through data comparison, so that the quick query of the user authority is realized with less calculation overhead and better index number.

As a further technical scheme, each row of data of the matrix is defined as the end of zero crossing and is represented by a letter Z, and the extracted authority data and attribution data are subjected to zero crossing processing to obtain a simplified user authority key and a simplified data attribution unit. The extracted authority data and the user use data are further simplified through the technical scheme.

As a further technical scheme, the user authority matrix and the data attribution matrix are respectively extracted according to rows to obtain a user authority key and a data attribution unit which are expressed in rows, the user authority key and the data attribution unit are compared pairwise, once the comparison result is True, the True is returned, and otherwise, false is returned.

Specifically, the comparison rule includes three rules, which are respectively: the first one is False when different is obtained by comparison; secondly, when the user authority secret key is compared with the data attribution unit, if any one of the user authority secret keys meets an ending symbol Z, the comparison is stopped, and a result before the ending is taken as a final result; third, if there are multiple results when a certain user authority key is compared with all data attribution units in a data attribution matrix, the multiple results are in an or relationship.

According to the three comparison rules, the user permission data and the user use data are compared one by one to obtain the permission data corresponding to each user permission data, so that the user use data corresponding to the permission can be directly obtained by inputting the user permission data during indexing.

As a further technical scheme, the GIN index storage structure is [ Key, postinglist ], where Key represents a user authority Key, and Postinglist represents a user data list corresponding to the user authority Key.

According to an aspect of the present disclosure, there is provided an index implementation system of matrixed user permissions on PostgreSQL, including:

the matrix data model building module is used for building a matrix data model according to an organization structure of a system and a relation between users and data, and the matrix data model comprises a user authority matrix and a data attribution matrix;

the data extraction module is used for respectively extracting the user authority matrix and the data attribution matrix line by line according to a preset extraction rule to obtain a user authority key and a data attribution unit;

the data comparison module is used for comparing each user authority secret key with all the data attribution units one by one according to a preset comparison rule to obtain each user authority secret key and a user data list corresponding to the user authority secret key;

the data forming module is used for forming a GIN index storage structure according to all the user authority secret keys and the corresponding user data lists;

the GIN extension module is used for adding a matrix data type in the PostgreSQL and constructing an operational character corresponding to the matrix data type;

and the index module is used for defining five support functions required by the GIN index and realizing the index of the GIN index storage structure based on the operational characters and the support functions.

And storing the constructed GIN index storage pair according to the GIN index to realize the GIN index.

According to an aspect of the present description, there is provided an electronic device comprising a processor and a memory, wherein at least one instruction, at least one program, a set of codes, or a set of instructions is stored in the memory, and the at least one instruction, the at least one program, the set of codes, or the set of instructions is loaded and executed by the processor to implement the method for implementing indexing of matrixed user permissions on PostgreSQL according to any of claims 1 to 8.

Compared with the prior art, the invention has the beneficial effects that:

(1) The invention processes the relation between matrix data by constructing an operational character, and expands an access method supporting matrix data types on the basis of GIN index to realize the storage of the matrix data, thereby realizing the quick index of the matrix data under a GIN frame and solving the problem that a matrix user is difficult to determine which data have operation authority.

(2) The invention adds an operational character on the basis of the original operational character of the PostgreSQL, and completes the short board of the matrix data type which cannot be compared by the PostgreSQL operational character.

(3) According to the invention, the matrix data type is newly added on the basis of the GIN index, so that the data of the matrix data type can be stored according to the GIN index.

(4) The invention can greatly improve the data query speed, optimize the storage space, reduce the index number and reduce the calculation cost by a new index access method.

Drawings

FIG. 1 is a data model abstraction diagram according to an embodiment of the invention.

Fig. 2 is a schematic diagram of data nesting list integration according to an embodiment of the present invention.

FIG. 3 is a flowchart of index expansion according to an embodiment of the present invention.

Detailed Description

The technical solutions of the embodiments of the present invention will be described below clearly and completely with reference to the accompanying drawings, and it is to be understood that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments of the present invention without any inventive step, are within the scope of the present invention.

The invention discloses a matrix type user authority index implementation method on PostgreSQL. The method mainly comprises the implementation of an algorithm inside an operator and the implementation of index.

Operators are necessary for SQL statements to find and process data, and for this purpose, the invention customizes an operator to process the relationship of matrix data type.

The construction of the operator specifically comprises:

1. building a data model

The data model is built according to the organizational structure of the system (school, site, teaching point, specialty, year, class) and the relationship between the user and the data. Data models are the cornerstone of the algorithmic logic inside operators.

According to the business requirements, the organization architecture in the system is divided into six layers, namely schools, sites, teaching points, major, grades and classes. As shown in FIG. 1, the six-level organizational structure is abstracted to the letters A, B, C, D, E, F. Every piece of data in the system is mounted in the six-layer organization structure.

For example, user u2 in fig. 1 is a site administrator of 1 school 2 site and a teaching point administrator of 1 school 1 site 1 teaching point, and u2 is represented by a matrix, that is, u2 is represented by a matrix

The data is mounted in the organizational structure and can be represented as A1B2C0D0E0F0 and A1B1C1D0E0F0, and actually the data A1B2C0D0E0F0 is mounted on the site of school 2 1, and the data is finished by the site, so that the matrix data is defined to be finished by the letter Z when meeting 0, and the user u2 can be finally abstracted into A1B2Z and A1B1C1Z.

In the above manner, all user information data and other data are represented in this form. And the abstracted user authority data is taken as a key (such as A1B 2Z), and the data under the authority are classified together to be taken as a posting list (since we develop based on GIN reverse ordering index extension, GIN is an index structure for storing a set of pairs (key). By this we abstract the data into a (keying) form.

2. Custom operator internal logic algorithm

And implementing an operator internal logic algorithm according to the data model. For example, in the data model

And

two matrix data, we define operators to operate on the relationship between A and B, can be expressed as A-B, and the algorithm is mainly used to process the relationship between the matrix data similar to A and B. The types of relationships processed within the algorithm are greater, less and inclusive.

As shown in fig. 2, taking the user right data A1Z as a key as an example, how to find the nesting List set corresponding to A1Z by comparison is illustrated. The comparison is mainly performed according to three rules, the first rule is that the comparison result is False, for example, A1Z and data d3 (A2B 1Z) when the comparison result is different, and the comparison result is False when A1 is not equal to A2. The second rule is that the comparison is stopped when any one of the two data to be compared meets an end mark Z, the result before the end is used as the final result, such as A1Z and data d4 (A1B 1C 1Z), the first bit A1 is equal to A1 and is True, the second bit meets Z and stops the comparison, the final result is True, and the third rule is that if a plurality of results are compared with one data, the relationship is positive, such as data d1, A1Z and data d1 are compared, the result is T, F, T, T, T, and the final result is True. According to the three rules, I find the corresponding nesting List of A1Z as d1, d2 and d4. These three rules are also the basic basis for the alignment algorithm inside the operator.

An index is a dispersed storage structure created to speed up the retrieval of data lines in a table. The invention extends a matrix indexing method based on GIN inverted sorting index.

As shown in fig. 3, the matrix indexing method based on GIN inverted sorting index mainly includes three steps:

1. adding a new data type: a new data type matrix data type is added. Since the PostgreSQL has no indexing method for supporting the matrix data type, but the GIN index has good expansibility, it is allowed to design an appropriate access method to implement the application of the GIN index when developing the custom data type. Therefore, we extend one data type, the matrix data type, based on the extension method provided by the GIN-index. Implementing input and output functions for the new data type and registering inside the database.

2. Create new types of operators: an operator is created according to the database method, and functions required by the various operators are implemented and registered for the new data type.

3. Defining a GIN access method: here, five methods are mainly used, namely, compare, extractValue, extractQuery, constraint, and componepartial, which define a key value, a relationship between a key value and a key value, an indexed value, a query capable of using an index, and partial matching.

Specifically, the compare method: comparing two key values a and b and then returning an integer value, a negative value indicating a < b, 0 indicating a = b, and a positive value indicating a > b. The function prototype is: int compare (Datum a, datum b).

The extracvalue method: and generating a key value array according to the parameter inputValue, returning a pointer of the key value array, and storing the number of elements in the key value array in another parameter nkeys. The function prototype is Datum extra value (Datum inputValue, int32 nkeys).

The extracquery method: and generating a key value array for query according to the parameter query, and returning a pointer of the key value array. The function prototype is Datum _ ExtractQuery (Datum query, int32 _ nkeys, strategyNumber n, bool _ Ppath, point _ Extra _ data).

The consistency method comprises the following steps: for checking whether the index value satisfies the query. The function prototype is a pool component (pool check [ ], strategyNumber n, datum query, int32 nkeys, point extra _ data [ ], pool × recovery).

The composepartial method: comparing the partially matched query with the index value, wherein the returned value is a negative value which indicates that the query and the index value are not matched, but continuing index scanning; a return value of 0 indicates that the two match; a positive return value indicates that scanning is stopped. Its function prototype is (int composepartial _ key, datum key, strateGyNumber n, point extra _ data)).

The support of the GIN index on the matrix data type is realized through the three steps.

Because each node generates a large amount of data in the system operation process, the storage space requirement is large, and when the authority of a certain matrix type user is queried according to the existing method, the index number is large, the calculation cost is large, and it is difficult to rapidly query which data the user has the operation authority. Therefore, the method extracts and compares system data according to preset logic by constructing a custom operator to obtain a storage structure suitable for GIN index; and then, under a GIN framework, importing matrix data by adding matrix data types and constructing an operator corresponding to the matrix data types, and performing semantic analysis on the imported and stored matrix data by defining five support functions required by a GIN index, so that the GIN index storage of the matrix data is realized.

the matrix data model building module is used for building a matrix data model according to an organization structure of the system and the relation between users and data, wherein the matrix data model comprises a user authority matrix and a data attribution matrix;

the data comparison module is used for comparing each user authority key with all the data attribution units one by one according to a preset comparison rule to obtain each user authority key and a user data list corresponding to the user authority key;

the GIN extension module is used for adding a matrix data type in the PostgreSQL and constructing an operator corresponding to the matrix data type;

In the description herein, references to the description of the terms "one embodiment," "certain embodiments," "an illustrative embodiment," "an example," "a specific example," or "some examples," etc., mean that a particular feature, structure, material, or characteristic described in connection with the embodiment or example is included in at least one embodiment or example of the invention. In this specification, schematic representations of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the particular features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples.

Finally, it should be noted that: the above examples are only intended to illustrate the technical solution of the present invention, but not to limit it; although the present invention has been described in detail with reference to the foregoing embodiments, it should be understood by those of ordinary skill in the art that: the technical solutions described in the foregoing embodiments may still be modified, or some or all of the technical features may be equivalently replaced; and the modifications or the substitutions do not make the essence of the corresponding technical solutions deviate from the technical solutions of the embodiments of the present invention.

Claims

1. The index implementation method of matrix user permission on PostgreSQL is characterized by comprising the steps of constructing an operational character to implement conversion of matrix data and implementing storage of the converted matrix data on the basis of GIN indexes;

establishing a matrix data model according to an organization structure of a system and a relation between users and data, wherein the matrix data model comprises a user authority matrix and a data attribution matrix;

forming a GIN index storage structure according to all the user authority secret keys and the corresponding user data lists;

realizing storage of the converted matrix data on the basis of the GIN index, and further comprising the following steps:

adding a matrix data type in PostgreSQL, and constructing an operational character corresponding to the matrix data type;

and defining five support functions required by the GIN index, and realizing the index of the GIN index storage structure based on the operator and the support functions.

2. The matrix-type user authority index implementation method of claim 1, wherein the organization architecture of the system includes six layers of organization nodes, i.e., school, site, teaching point, professional, year and class, wherein each row of the user authority matrix corresponds to one authority data of the user, and each column corresponds to one organization node; each row of the data attribution matrix corresponds to attribution of one piece of data, and each column corresponds to one organization node.

3. The matrix type index implementation method of user authority on PostgreSQL according to claim 2, wherein six layers of organization nodes of school, site, teaching point, specialty, grade and class are abstractly represented as A, B, C, D, E, F, each row in the user authority matrix and the data attribution matrix represents aabbccddeff, where a represents school number, b represents site number, c represents teaching point number, d represents specialty number, e represents grade number, and f represents class number; and constructing and forming a matrix data model according to the authority of the user at each organization node and the data generated by the user at each organization node.

4. The method for indexing matrix user permissions on PostgreSQL according to claim 3, wherein for the user permissions matrix, the extraction rules are: if the user has the authority at a certain organization node, extracting the corresponding organization node in the matrix as a node number, and extracting a lower node of the organization node as zero; and after extraction is finished, obtaining authority data of each row of the user authority matrix.

5. The method for indexing the matrix user right on PostgreSQL according to claim 3, wherein for the data attribution matrix, the extraction rule is: if the user generates data at a certain organization node, extracting the corresponding organization node in the matrix as a node number, and extracting a lower node of the organization node as zero; and after extraction is finished, obtaining attribution data of each row of the data attribution matrix.

6. The matrix-type user right indexing method on postgreSQL according to claim 4 or 5, wherein each row of data in the matrix is defined as the end of zero-crossing and is represented by the letter Z, and the extracted right data and attribution data are subjected to zero-crossing processing to obtain a simplified user right key and a simplified data attribution unit.

7. The matrix user authority index implementation method on the PostgreSQL according to claim 6, wherein the user authority matrix and the data attribution matrix are extracted in rows, so as to obtain user authority keys and data attribution units expressed in rows, the user authority keys and the data attribution units are compared pairwise, once the comparison result is True, true is returned, otherwise, false is returned.

8. The matrix-type user authority index implementation method on PostgreSQL according to claim 1, wherein the GIN index storage structure is [ Key, postinglist ], where Key represents a user authority Key, and Postinglist represents a user data list corresponding to the user authority Key.

9. Matrix type user authority index implementation system on postgreSQL, its characterized in that includes:

10. An electronic device, comprising a processor and a memory, wherein the memory stores at least one instruction, at least one program, set of codes, or set of instructions, which is loaded and executed by the processor to implement the method for indexing of matrixed user permissions on PostgreSQL according to any of claims 1 to 8.