🧾About the code (PHP)

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 = array();
$names = array (list all required parameters here);
foreach($names as $index => $name){
      $searches[$index] = 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 = array();
$names = array($people,$agents,$documents,$goods); // look here
foreach($names as $index => $name){
       $searches[$index] = get(null,$name,null); // and look here
}

Where null is a pointer to a void, because the variables $set and $value are not used 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 = array();
$names = array($people,$agents,$documents,$goods,$deals); // only added "deals"
foreach($names as $index => $name){
      $searches[$index] = get(null,$name,null);
}

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

getAllpeople(); 
getAllAgents(); 
getAllDocuments(); 
getAllGoods(); 
getAllDeals();

And that is pretty bad.

But let's move on. Let's go back to our contradiction: the properties 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 properties for each of them, such as name, gender, phone, email, weight, height, eye color, etc.

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

$people = getpeople(); 
foreach($people as $person){ 
 echo get_people_name(); 
 echo get_surname(); 
 echo get_phone(); 
 echo get_email(); 
 echo get_weight(); 
 echo get_height(); 
 echo get_eye_colour(); 
}

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

$people = get(null,$surname,null);
//assuming that the property "name" is for people only
$properties = array($people_name, $surname, $sex, $phone, $email, $weight, $height, 
                    $eye_colour); // look here
foreach($people as $person){
     foreach($properties as $property){
          echo get($person,$property,null);
     }
}

Now let's choose a different context and select "people as agents". Let us be interested in other properties. 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(null,$surname,null); 
$properties = array ($people_name, $surname, $contracts, $goods, $documents
                     ...list of all required parameters); // look here
foreach($people as $person){
     foreach($properties as $property){
          echo get($person,$property,null);
     }
}

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(null,$surname,null); 
$properties = array (list all required parameters here);
foreach($people as $person){
     foreach($properties as $property){
          echo get($person,$property,null);
     }
}

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

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

any_function($Set_id, $Name, $Value) or just:
any($Set_id, $Name, $Value)

where:

Set_id - Id of set of properties
Name - name of property
Value - value of property

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:

get($SetId,$Name,$Value)

Create & Update: add ($SetId,$Name,$Value)

add($SetId,$Name,$Value)

Delete:

delete($SetId,$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"

Get ($Set_id, $Name, $Value) 
//Gets a record from the database, depending on the parameters passed:

1) Get (null, $name, $value) 
// Returns identifiers of sets (Set_id), 
// which have the property with name Name with value Value.

2) Get (null, $name, null) 
// Returns the identifiers of sets (Set_id), 
// which have a property with name Name.

3) Get ($Set_id, $name, null) 
// Returns the value of the property with name Name and set identifier Set_id.

4) Get ($Set_id, null, $value) 
// Returns the name of property with set identifier Set_id and value Value.

5) Get ($Set_id, null, null) 
// Returns all properties 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 property of set with the identifier Set_id = 1.

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

get (1, $name, null)

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:

get (1, null, null)

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:

get (1, null, 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:

get (null, $email, "[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:

get (null, $login, null)

will create and execute following SQL-queries:

SELECT SET_ID FROM SETS WHERE NAME_ID = 0;

Or as follows:

get (null,null,32)s

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:

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

1) Add (null, $Name, $Value) 
// Creates a new set with property which has name Name and value Value 
// and adds it to the database.

For example,

add (null, $login, "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) Add ( $Set_id, $Name, $Value) 
// Adds property with name Name and value Value to the set with the ID Set_id,
// or updates it, if such property has already been set.

For example,

add (null, $password,"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  = array("admin","123456","Peter","[email protected]",32)
$obj_id = add(null,$names ,$vals);

Then it creates an entire set with specified properties:

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) Delete ($Set_id, $Name) 
// Removes the property with the name Name from the set with the identifier Set_id.

delete (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;

Delete ($Set_id, null)// Removes the set with the corresponding identifier

delete (1,null)

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.

PreviousAbout the code (Python)NextAbout the performance

Last updated 1 year ago