SQL database growth exponentionaly

[gribouk]

Dear All,

I am using variable component to emulate classes (structures) to build sort of an objects and procedures approach within HA automations. Maybe it is not a proper way to do that, but I decided to try it this way with Jinja2. In general, It works pretty good - variables and their attributes -> classes and fields, HA scripts == functions, you can impliment pretty complicated logics without need to go to python. But noticed an unexpected fenomena - my SQL DB grows at unprecedented speed after I lauched my "modules" at full load - all objects are created and functional. I assume it is variable writing the data to DB, but I fail to understand which ones of several houndreds I have. I mannualy excluded most of them from the recorder integration, but I do not see any significant difference as far as database growth is concerned. Is there any way to understand what exactly is "eating" my space? To give you an understanding, I estimate it is 20GB/day and I do not have any cameras in this integration! 20 GB/day of some text data! Apart from that I do not see any load on my system - CPU is ~3%, mem ~ 7%...

Would be really thanksfull for any suggestions how to trace this issue.

[gribouk]

UPD. I was not excluding whole domain variable from the recorder integration for the reason that I assumed that it needs it in order to have the data for restore after reboot. But experimentally I discovered, that excluding variable domain from the recorder does not effect the restore and my variables carry the info I need after the reboot even when not written into SQL DB. Excluding variable domain from recorder reduced my DB space consumption from 20GB/day to 50-60 Mb/day which is pretty acceptible now, since I do not plan to keep the data older than 5 years.

The last question I still would like to have an answer to is where does it get the data from after the reboot to restore the variable, if it is excluded from the recorder?

[pdwonline]

In general, It works pretty good - variables and their attributes -> classes and fields, HA scripts == functions, you can impliment pretty complicated logics without need to go to python

This is what I was looking for, but despite the history example, it is not clear to me how to set a new attribute. Since you have some experience, do you mind to share some scripting?

[gribouk]

Not sure what you mean. You have a procedure(variable.set_variable). Usingvthat one you change the value of your attributes... You cannot implement full Object Oriented Programming with classes through that, but you can do full Procedure programming paradigma - data structures + procedures. Scripts are now a procedures like in Pascal. And you pass to these scripts names of you variables and some other params you want to be used by the procedures. So, your script finds your variable by the entity id, does some computation, and writes the result into a variable attribute using variable.set_variable. This way you can store your dynamic data and pass it between different modules of your software... Basically, at the moment without digging into the python I code as if it was a script version of Pascal with Jinja syntax...

[gribouk]

In general, It works pretty good - variables and their attributes -> classes and fields, HA scripts == functions, you can impliment pretty complicated logics without need to go to python

This is what I was looking for, but despite the history example, it is not clear to me how to set a new attribute. Since you have some experience, do you mind to share some scripting?

To provide some context below is a tiny example. This is a part of one of my scripts which serves as a module. Inside this module you may call different procedures - services provided by module. So, you pass variable action to this script, and it knows what procedure you are calling. Then it performes the action according to the parameters provided. This is an example for one of the actions - release_from_group:

      - conditions:
          - condition: template
            value_template: |-

              {{ action == 'release_from_group' }}
        sequence:
          - service: script.general_log
            data_template:
              debugging: 'false'
              message: |-

                {{input_room}}

                {{input_pipe_to_release}}
          - service: variable.set_variable
            data:
              variable: |-

                {% set room = states[input_room] %}

                {{room.object_id}}
              attributes:
                dynamic_data_manager: |-

                  {% set old = state_attr(input_room, 'dynamic_data_manager') %}
                  {% set old_list = old.split(input_pipe_to_release+'$') %}
                  {% if old_list|length > 1 %}   

                    {% set tmp = old_list[0].split('#_group_#') %}

                    {% set group_number = tmp|length - 1 %}

                    {% if (group_number > 0 and 
                          '#' in tmp[tmp|length-1])
                          or
                          group_number == 0
                    %}

                      {% set main_pipe = false %}

                    {% else %}

                      {% set main_pipe = true %}

                    {% endif %}

                    {% if main_pipe %}

                      {% set old_list_before = old_list[0].split('#_group_#') %}                       

                      {% set old_list_after = old_list[1].split('#_group_#') %}

                      {% set init_group_data = old_list_after.pop(0) %}

                      {% set initial_group = input_pipe_to_release + '$' + init_group_data %}

                      {% set remainder = (old_list_before + old_list_after)|join('#_group_#')|regex_replace('#_group_##_group_#','#_group_#') %}

                      {% set remainder = (initial_group + '#' + remainder)|regex_replace('##','') %}

                      {{ remainder }}                                                                                                  

                    {% elif group_number > 0 %}

                      {% set old_list_before = old_list[0].split('#') %}    

                      {% set old_list_after = old_list[1].split('#') %}

                      {% set init_group_data = old_list_after.pop(0) %}

                      {% set initial_group = input_pipe_to_release + '$' + init_group_data %}

                      {% set remainder = (old_list_before + old_list_after)|join('#') %}

                      {% set remainder = (initial_group + '#' + remainder)|regex_replace('##','') %}

                      {{ remainder }}                            

                    {% else %}

                      {{ old }}

                    {% endif %}

                  {% else %}

                    {{ old }}

                  {% endif %}

and in every module I have a services descriptor which looks like that:

  - service: script.general_log
    data_template:
      debugging: 'false'
      message: |-
        managing the descriptor of general scenario object.
        action 

         + add_scenario - input_room = entity_id of the scenario variable for the room
                         input_scenario_name = reference to the scenario name in the speacker variable
                                               attributes
                         input_scenario_mode = single/loop

         + remoove_scenario - input_room 
                             input_scenario_name = same as above or none
                             input_scenario_id = int (0 if none) - deprecated

         + run_scenario - input_room
                         input_presence = in/out/both
                         input_scenario_name
                         input_scenario_id - deprecated
                         input_run_mode = single/loop
                         input_run_duration 

         + stop_scenario -  stops the last instance of the scenario added
                            to the timeline. If there were older instances
                            of the scenario which effect the state of spome
                            light scenes, the procedure needs to be called
                            several times.
                         input_room
                         input_presence = in/out/both
                         input_scenario_name                         

         + add_device - input_room
                       input_scenario_name
                       input_scenario_id - deprecated
                       input_light_scene = variable entity_id 

         + remoove_device - input_room
                           input_scenario_name
                           input_scenario_id - deprecated
                           input_light_scene
                           input_scene_id = int (0 if none)

         + add_device_mode_state - mode state tells what mode of a device to be set and for how long                               

                                  input_room
                                  input_scenario_name
                                  input_scenario_id - deprecated
                                  input_light_scene
                                  input_scene_id - deprecated
                                  input_scene_mode - mode_name in scene descriptor

                                  input_state_duration - any int secs, 0 - means as long as
                                                         scenario

         + add_device_mode_update - this procedure adds an update rule to already added mode of a device (scene)
                                    standard parameters for mode update comprise 6 value. 

                                  input_room         
                                  input_scenario_name
                                  input_scenario_id - deprecated
                                  input_light_scene
                                  input_scene_id - deprecated         

                                  input_scene_mode
                                  input_scene_mode_id - deprecated

                                  input_update_time - int secs, interval used to call mode update
                                                      while the mode is active
                                                      must be less than input_state_duration
                                  input_colors - int/all/'variable_entity_id><mode_name_in_a_dynamic_data_manager'
                                  input_base_color - color_name/none/'variable_entity_id><mode_name_in_a_dynamic_data_manager'
                                  input_color_temp - 153 - 500/rnd/'variable_entity_id><mode_name_in_a_dynamic_data_manager'
                                  input_brightness - 1-100/rnd/'variable_entity_id><mode_name_in_a_dynamic_data_manager'
                                  input_transition - 1-10/rnd/'variable_entity_id><mode_name_in_a_dynamic_data_manager'

         + remoove_last_device_mode_update - input_room         
                                  input_scenario_name
                                  input_scenario_id - deprecated
                                  input_light_scene
                                  input_scene_id - deprecated         

                                  input_scene_mode
                                  input_scene_mode_id - deprecated                                                          

         + remoove_last_device_mode_state - input_room
                                           input_scenario_name
                                           input_scenario_id - deprecated
                                           input_light_scene
                                           input_scene_id - deprecated

         + clear_device_mode_states - input_room
                                     input_scenario_name
                                     input_scenario_id - deprecated
                                     input_light_scene
                                     input_scene_id - deprecated   

         + clear_states - input_room      

         + update_states - input_room    

         + next_time_step - If an update to a projection is needed this procedure is called 

                           input_room      

         + release_from_group - releases a given pipe from a roup

                          input_room
                          input_pipe_to_release      

Thus, if you need to use this module, you look into descriptor and know what variables must be provided to a script and what they mean...

[gribouk]

In general, It works pretty good - variables and their attributes -> classes and fields, HA scripts == functions, you can impliment pretty complicated logics without need to go to python

This is what I was looking for, but despite the history example, it is not clear to me how to set a new attribute. Since you have some experience, do you mind to share some scripting?

One more thing. Pascal did not survive for the reason it did not allow dynamic allocation of objects, which Object Oriented Programming is famous for. To give birth and anihilate objects you need constructor. With hass variables I emulate this process through parcing. The variable itself is 'static' in a way, that you declare it in the config file. But it may contain dynamic arrays of 'virtual' variables that you may gesign yourself runtime. For this you only need to develop a structure and write a parcing procedures for adding/removing elements from that list. To provide an example I poste some of my comments to the fields of one of my objects I use for a pretty advanced light managment software which I've developed, you wont belive - using Jinja only - nobody belives when sees how it works, one never expects Jinja can handle such projects, but it can...

# descriptor - stores the descriptor of all scenarios existing in the system. the data structure is  as follows:
#         scenario_name_1$scenario_mode$
#              
#            light_scene_1_entity_id&last_set_mode_num&
#                   duration_1|mode_name_1|
#                       update_time_1_1!color_num_1!base_color_1!color_temp_1!brightness_1!transition_1
#                       <>
#                       update_time_1_2!color_num_2!....
#                   %
#                   duration_2|mode_name_2....
#               @
#               light_scene_2_entity_id&
#                   mode_name_1%.........
#           #
#           scenario_name_2....

Basically, you have several types of delimiters which spawn non comutative operators you can apply to a strings space. It is only sufficient to write a proper procedure to handle such strings and you can add, remove, change variables with any intrinsic structure - fully emulate constructors in C++, to some extent ofcaurse... Ofcaurse parcing is a slow thing to do compared to python, but it requires no knowledge of API and stuff and you can start your project right away. And modern machines are pretty swift, so you would not notice much of a delay...

[pdwonline]

Thanks for your comments!