🧾About the code (Python)

© Alexander Sychev, Sergei Sychev, “TRIZ-RI Group”

Now consider the possibilities which the programmer gains using this data structure.

Clear example

Supposing we need to make a selection (get). Now we can write universal operations in this style:

searches = []
names = ["list all required parameters here"]

for name in names:
    searches.append(get(set_id, name, value)) # "Gene" of the data structure

For example, the following code will find all people, all agents, all documents and all goods:

searches = []
names = ["people", "agents", "documents", "goods"] # Look here

for name in names:
    searches.append(get(None, name, None)) # Using None as a placeholder

Where None acts as a pointer to a void because the variables set_id and value are not required in the request.

Note: In order to simplify and unify the code, we will try to use this rule regularly: structure

setID, name, value

will always be used in the corresponding function, but when a particular variable is not needed, we will pass the "pointer to void" instead).

So, if any new entity appears (for example, "Deals"), and we need to expand the query to receive "all the deals" as well, we do not need to write any additional features, we only write corresponding entries in the table Values and Sets of our database (if such entities did not exist before) and specify the "deals" in the "get" function:

searches = []
names = ["people", "agents", "documents", "goods", "deals"] # only added "deals"

for index, name in enumerate(names):
    searches.append(get(None, name, None)) 

You may agree that if data structure was different, we would need to do something like:

get_all_people()
get_all_agents()
get_all_documents()
get_all_goods()
get_all_deals()

And that is pretty bad.

But let's move on. Let's go back to our contradiction: the attributes should be different and should not be different. But let us no longer consider it at the level of the data structure, but at the level of the program code as well.

First let's select "people as people", and display the universal attributes for each of them, such as name, gender, phone, email, weight, height, eye color, etc.

Of course, we do not write function for each attribute:

people = get_people()
for person in people:
    print(get_people_name(person)) 
    print(get_surname(person))
    print(get_phone(person))
    print(get_email(person))
    print(get_weight(person))
    print(get_height(person))
    print(get_eye_colour(person))

Let's make our request in the same way we did before:

people = get(Set_id=None, Name="surname", Value=None)
# assuming that the attribute "name" is for people only

attributes = ["name", "surname", "sex", "phone", "email", "weight", "height", "eye_colour"]
    
for person in people:
    for attribute in attributes:
        print(get(person, attribute, None))

Now let's choose a different context and select "people as agents". Let us be interested in other attributes. No eye color, height or weight, but, for example, details of the transaction made with us and related products and documents in addition to the contact itself.

We do not write new functions, but only change the parameters in the marked line:

people = get(Set_id=None, Name="surname", Value=None)

attributes = ["people_name", "surname", "contracts", "goods", "documents" 
             ...list of all required parameters] # look here

for person in people:
    for attribute in attributes:
        print(get(person, attribute, None))

It is clear that if we constantly require a different "set" of settings (people in different contexts: "people", "our employees", "agents", "as employees of the suppliers", "as employees of the Customers company", "as lovers" etc.), we will always use just one function:

people = get(Set_id=None, Name="surname", Value=None)
attributes = ["list all required parameters here"]

for person in people:
    for attribute in attributes:
        print(get(person, attribute, None))

We got a function close to the ideal, which does not indicate any entity explicitly (hence, after any reorganisation of data, the code does not change at all), but, nevertheless, it responds properly depending on the parameters passed.

Now let's standardise a requirement to the parameters of the "ideal function". Let it always require only 3 parameters: "Id of set of attributes", "name of attribute", "value of attribute", which represent the "copy" ("gene") of our universal data structure described above (Set_id, Name, Value):

def any_function(set_id, Name, value):
# or just:
def any(set_id, "name", value):
# Any function implementation goes here
    pass

where:

• Set_id - Id of set of attributes

• Name - name of attribute

• Value - value of attribute

Another methodological remark: apparently, code and data are not stored together. As you see, abstraction conducted fairly seriously (see for yourself), and normalization is so strong that even reached its dialectical negation, but there is no encapsulation in OOP sense at all.

We do not mix the data and code that works with it, hence we don't create any objects, neither in general sense nor in OOP sense. The code is universal so it works with any type of data, and any type of data also does not have its "personal code". We do not make "smart objects" we make "dumb data sets" and "dumb functions" separately from each other. The difference with the OOP is obvious.

---

Continue the optimization

Let us continue the optimization

Any good manual on database design mentions the CRUD, and that all work with databases is reduced to four operations: "Create", "Read", "Update", "Delete".

Let's implement this set of tasks here, considering the idealization of code described above. We shall implement the "dialog" between the program and database via the universal interface ("the kernel"). This interface should be done in such way that the program should not know anything about the databases structure (in a way it should be done by all canons) and the database as well should not care what program is using it. Here we will only list key "kernel functions" (it is an article after all), and if we receive numerous requests to publish the entire kernel, we will publish it separately.

Key kernel functions are: "Get", "Add", "Delete".

Read:

def get(Set_id, Name, Value):

Create & Update:

def add(Set_id, Name, Value):

Delete:

def delete(Set_id, Name, Value):

Let us examine them in detail, as they are carrying out the mission of relieving the programmer from a necessity to know the structure of a database and even necessity to know the SQL language. Firstly, because, considering what was said above, the entire SQL language is reduced to very primitive lines, and secondly, "kernel functions" are creating these lines, and the kernel itself sends retrieved data where it is required.

Function "Get"

def Get(Set_id, Name, Value):
# Gets a record from the database, depending on the parameters passed.

1) def Get(Set_id=None, Name, Value): 
# Returns identifiers of sets ("Set_id"), 
# which have the attribute with name "Name" with value "Value".

2) def Get(Set_id=None, Name, Value=None):
# Returns the identifiers of sets ("Set_id"), 
# which have a attribute with name "Name".

3) def Get(Set_id, Name, Value=None):
# Returns the value of the attribute with name "Name" and set identifier "Set_id".

4) def Get(Set_id, Name=None, Value):
# Returns the name of attribute with set identifier "Set_id" and value "Value".

5) def Get(Set_id, Name=None, Value=None):
# Returns all attribute of set with identifier "Set_id".

As stated above, the Get function is also responsible for the automatic creation of SQL queries

Supposing we want to get a attribute of set with the identifier Set_id = 1.

We should fill in the functions parameters Get ( Set_id, Name, Value) as follows:

def get(Set_id=1, Name, Value=None):

and following SQL-queries will be created and executed:

SELECT VALUE_ID FROM SETS WHERE SET_ID = 1 AND NAME_ID = 2;
SELECT VAL FROM VALUES_STRING WHERE VALUE_ID = 3;

If we change the input as follows:

def get(Set_id=1, Name=None, Value=None):

will create and execute following SQL-queries:

SELECT NAME_ID,VALUE_ID FROM SETS WHERE SET_ID = 1;
SELECT VAL FROM VALUES_STRING WHERE VALUE_ID = 1;
SELECT VAL FROM VALUES_STRING WHERE VALUE_ID = 2;
SELECT VAL FROM VALUES_STRING WHERE VALUE_ID = 3;
SELECT VAL FROM VALUES_STRING WHERE VALUE_ID = 4;
SELECT VAL FROM VALUES_INT WHERE VALUE_ID = 6;

Or as follows:

def get(Set_id=1, Name=None, Value=23):

will create and execute following SQL-queries:

SELECT VALUE_ID FROM VALUES_INT WHERE VAL = 23;
SELECT NAME_ID FROM SETS WHERE SET_ID = 1 AND VALUE_ID = 6;

Or as follows:

def get(Set_id=None, Name=email, Value="[email protected]"):

will create and execute following SQL-queries:

SELECT VALUE_ID FROM VALUES_STRING WHERE VAL = '[email protected]';
SELECT SET_ID FROM SETS WHERE NAME_ID = 3 AND VALUE_ID = 9;

Or as follows:

def get(Set_id=None, Name=login, Value=None):

will create and execute following SQL-queries:

SELECT SET_ID FROM SETS WHERE NAME_ID = 0;

Or as follows:

def get(Set_id=None, Name=None, Value=32):

will create and execute following SQL-queries:

SELECT VALUE_ID FROM VALUES_INT WHERE VAL = 32;
SELECT SET_ID FROM SETS WHERE VALUE_ID = 10;

Function "Add"

Now let's take a closer look at the "Add" function:

def Add(Set_id, Name, Value):
# Adds record to the database (or updates it), depending on the parameters passed.

1) def Add(Set_id=None, Name, Value):
# Creates a new set with attribute which has name Name and value Value 
# and adds it to the database.

For example,

def add(Set_id=None, Name=login, Value="admin3"):

will create and execute following SQL-queries:

SELECT VALUE_ID FROM VALUES_STRING WHERE VAL = 'admin3';
UPDATE OR INSERT INTO VALUES_STRING (VALUE_ID, VAL) VALUES (1, 'admin3')... 
                                                                ...matching (VALUE_ID);
INSERT INTO SETS (SET_ID, NAME_ID, VALUE_ID) VALUES (1, 0, 1);

2) def Add(Set_id, Name, Value) 
# Adds attribute with name Name and value Value to the set with the ID Set_id,
# or updates it, if such attribute has already been set.

For example,

def add(Set_id=None, Name=password, Value="123456"):

will create and execute following SQL-queries:

SELECT VALUE_ID FROM VALUES_STRING WHERE VAL = '777';
UPDATE OR INSERT INTO VALUES_STRING (VALUE_ID, VAL) VALUES (12, '777')... 
                                                                ...matching (VALUE_ID);
SELECT VALUE_ID FROM SETS WHERE SET_ID = 1 AND NAME_ID = 1;
SELECT SET_ID FROM SETS WHERE VALUE_ID = 2;
UPDATE OR INSERT INTO SETS (SET_ID, NAME_ID, VALUE_ID) VALUES (1, 1, 12)... 
                                                         ...matching (SET_ID, NAME_ID);

Such variation is also possible:

SELECT VALUE_ID FROM VALUES_STRING WHERE VAL = 'admin6';
UPDATE OR INSERT INTO VALUES_STRING (VALUE_ID, VAL) VALUES (11, 'admin6')... 
                                                                ...matching (VALUE_ID);
SELECT VALUE_ID FROM SETS WHERE SET_ID = 1 AND NAME_ID = 0;
SELECT SET_ID FROM SETS WHERE VALUE_ID = 1;
DELETE FROM VALUES_STRING WHERE VALUE_ID = 1;
UPDATE OR INSERT INTO SETS (SET_ID, NAME_ID, VALUE_ID) VALUES (1, 0, 11)... 
                                                         ...matching (SET_ID, NAME_ID);

Here the function ADD "cleans up" the database, ie removes the value that no one uses anymore.

You can pass multiple names and values at the input of the ADD function:

vals = ["admin", "123456", "Peter", "[email protected]", 32]
obj_id = add(None, names, vals)

Then it creates an entire set with specified attributes:

SELECT VALUE_ID FROM VALUES_STRING WHERE VAL = 'admin';
UPDATE OR INSERT INTO VALUES_STRING (VALUE_ID, VAL) VALUES (7, 'admin')... 
                                                                ...matching (VALUE_ID);
INSERT INTO SETS (SET_ID, NAME_ID, VALUE_ID) VALUES (2, 0, 7);

SELECT VALUE_ID FROM VALUES_STRING WHERE VAL = '123456';
INSERT INTO SETS (SET_ID, NAME_ID, VALUE_ID) VALUES (2, 1, 2);

SELECT VALUE_ID FROM VALUES_STRING WHERE VAL = 'Петр';
UPDATE OR INSERT INTO VALUES_STRING (VALUE_ID, VAL) VALUES (8, 'Peter')...
                                                                ...matching (VALUE_ID);
INSERT INTO SETS (SET_ID, NAME_ID, VALUE_ID) VALUES (2, 2, 8);

SELECT VALUE_ID FROM VALUES_STRING WHERE VAL = '[email protected]';
UPDATE OR INSERT INTO VALUES_STRING (VALUE_ID, VAL) VALUES (9, '[email protected]')... 
                                                                 ...matching (VALUE_ID;
INSERT INTO SETS (SET_ID, NAME_ID, VALUE_ID) VALUES (2, 3, 9);
 
SELECT VALUE_ID FROM VALUES_INT WHERE VAL = 32;
UPDATE OR INSERT INTO VALUES_INT (VALUE_ID, VAL) VALUES (10, 32) matching (VALUE_ID);
INSERT INTO SETS (SET_ID, NAME_ID, VALUE_ID) VALUES (2, 5, 10);

Function "Delete"

Let's take a closer look at the "Delete" function:

1) def delete(Set_id, Name):
# Removes the attribute with the name Name from the set with the identifier Set_id.

def delete(Set_id=1, login):

will create and execute following SQL-queries:

SELECT NAME_ID,VALUE_ID FROM SETS WHERE SET_ID = 1 AND NAME_ID = 0;
SELECT SET_ID FROM SETS WHERE VALUE_ID = 11;
DELETE FROM VALUES_STRING WHERE VALUE_ID = 11; (Here the Delete function "cleans up" 
the database, ie removes the value that no one uses anymore.)
DELETE FROM SETS WHERE SET_ID = 1 AND NAME_ID = 0;

def delete(Set_id, Name=None): # Removes the set with the corresponding identifier

def delete(Set_id=1, Name=None):

will create and execute following SQL-queries:

SELECT NAME_ID,VALUE_ID FROM SETS WHERE SET_ID = 1;
SELECT SET_ID FROM SETS WHERE VALUE_ID = 1;
DELETE FROM VALUES_STRING WHERE VALUE_ID = 1;
DELETE FROM SETS WHERE SET_ID = 1 AND NAME_ID = 0;
 
SELECT SET_ID FROM SETS WHERE VALUE_ID = 2;
DELETE FROM SETS WHERE SET_ID = 1 AND NAME_ID = 1;
 
SELECT SET_ID FROM SETS WHERE VALUE_ID = 3;
DELETE FROM VALUES_STRING WHERE VALUE_ID = 3;
DELETE FROM SETS WHERE SET_ID = 1 AND NAME_ID = 2;
 
SELECT SET_ID FROM SETS WHERE VALUE_ID = 4;
DELETE FROM VALUES_STRING WHERE VALUE_ID = 4;
DELETE FROM SETS WHERE SET_ID = 1 AND NAME_ID = 3;
 
SELECT SET_ID FROM SETS WHERE VALUE_ID = 6;
DELETE FROM VALUES_INT WHERE VALUE_ID = 6;
DELETE FROM SETS WHERE SET_ID = 1 AND NAME_ID = 5;

And we'll talk about performance in the next chapter.