Some techniques of working in Bitrix on SQL and BASH

I keep writing about Bitrix in the context of exchanges, Mysql and the Linux command line.

This article is an introduction to a series of articles on the structure of the Bitrix database, where this topic will be covered in sufficient detail. For a start, solutions of some small but annoying tasks will be presented. As always, knowledge of SQL is necessary.

The article deals with quite private issues that do not arise daily. Of course, you can use these materials for their intended purpose, but this is not the main purpose of the article. I begin to open the “black box” called “Bitrix database structure”, and show that this knowledge can be useful for improving the level of both the system and basic technologies (SQL, linux shell), which, of course, helps to solve new complex, interesting, diverse tasks.
')
The page with which you can make SQL queries to the database, or the mysql web Bitrix client, is located in the Bitrix control panel along the path: “Settings-> Tools-> SQL query”.

Also, of course, you can make requests from the command line of the operating system, for which I can offer you a simple operation to retrieve the login and password directly from the settings of Bitrix:

log=$(grep -i "login" /home/bitrix/www/bitrix/php_interface/dbconn.php | cut -f2 -d'"') pas=$(grep -i "pass" /home/bitrix/www/bitrix/php_interface/dbconn.php | cut -f2 -d'"') mysql -u$log -p$pas $log 

- in this way you can get the login and password for the Mysql Bitrix database from the linux command line on bash. Of course, replace the paths with your own. In the version provided by the Bitrix virtual machine, DOCUMENT_ROOT looks like / home / bitrix / www /.

1. Set up "Favorites" for all users of the Bitrix control panel at once.

When a new site is introduced, the developer does not want to explain to anyone who adds news or edits pages what to click to get into the news or product catalog. It is better to bring the links directly to the start page of the Bitrix control panel, especially since in the Bitrix the “desktop” in the admin panel is intended for this.

If the settings of the so-called "desktop" can be extended to anyone who enters the Beatrix admin area, the user can fill in the "Favorites" column only for himself. This is logical, but usually users neglect this opportunity, so we will do this work for them.

In addition, not everything can be placed on the "Desktop", and you can add anything to the "Favorites", even a link to a third-party resource, for example, to mail or tickets from the technical support service of another system.

The control panel does not allow filling in the “Favorites” block in the bitrix admin panel for all content managers: there is no such button. Therefore, we first add the necessary links in the block "Favorites" for yourself.

Find out your user ID. If you are under the administrator (admin) in the control panel, then your user ID = 1.

Obviously, this data refers to the user, and the first glance is thrown at the tables starting with b_user_, of which there are a dozen in the Bitrix database. However, the table we need does not have such a prefix and is called b_f Favorites.

This table contains the COMMON field, which for all records is 'N'. As it turns out, the function of displaying favorites for all users is present, and the COMMON field in the b_f is well justifies its name.

 update b_favorite set common='Y' where user_id=1; 

We don’t even need to duplicate the administrator’s records for all users (and how would it be with the newly created site administrators? .. Make mysql a trigger? .. Start an event? ...). Now all Bitrix admin users see the same “Favorites” as you. Problem solved.

2. How to change the username and password of the Bitrix user through the database. How to reset the Bitrix admin password.

If you are unable to log in to the Bitrix control panel, but the base is available, you can either set your own password for the user, or make it the same as any other user whose password you know.

The master data of all users is stored in the b_user table. You need to know the user ID in the Bitrix system.

Setting your own Bitrix login and password using Mysql

The password is stored in the database as:

<_>md5(<_><>)

They say they have changed something else, but if you do this, it will work.

 update b_user set login='su',password=concat('12345678',md5('12345678stupidpassword')) where id=8; 

Where
su - new user login
12345678 - salt
stupidpassword - new user password
id = 8 - user id (if admin, then = 1)

Password Bitrix reset, the problem is solved. At the same time, you can change the login.

Copying password from another user

And the second, very simple, way to reset the password in the bitrix.

This time, we just need to transfer the password hash from one user to another. Of course, you need to know the ID of both users in bitrix, in this case, the user with ID = 36, we set the password from the user with ID = 895.

 update b_user b1 inner join b_user b2 on b2.id=36 set b1.password=b2.password where b1.id=895; 

3. Application of setting fields in order editing in Bitrix admin panel for all users.

In a certain second-level domain zone, on some website, in the administrative panel of the control system of Bitrix, there is an online store order editing page, in the header of which product table there is a gear setting button, and this button is sometimes blue, then gray ...

By clicking on this button, you can set the order fields that will be displayed in the table, including the custom properties of the order. But at the same time, it is set only for one user.

To apply these settings to all users of the Bitrix control panel, and not to explain to everyone, "click the little button in the table header with the goods, see the list of properties ... Choose the ones you need ...", set the settings through the base.

These settings are stored in the b_user_option table, which are mapped to a specific user through the user_id field. But there is one feature: if user_id = 0, then the property is global, and applies to all users.

 update b_user_option set user_id=0 where name='table_columns' and category='order_basket_table' and user_id=1 

where user_id = 1 is the user ID under which you set up the product table. If it is admin, then id = 1.

The problem is solved: Now your settings table of products work for all at once.

4. Sort search results in Bitrix on their own algorithm.

If we are talking about site search, the main table for the bitrix search modules is b_search_content.

Interesting fields in the b_search_content table:

custom_rank - the field by which sorting works before all other fields
param1 - information block code
param2 - information block number
module_id - = 'iblock'
item_id - the id of the item or section
- to search in information blocks.

First of all, we are interested in the custom_rank field.

If in the previous cases we used the provided, but unrealized capabilities of Bitrix, then there is a special “tick” for this field in the control panel. But it is exhibited separately for each element, and usually this opportunity is not used.

By the way, this field turns out to be numeric, and we will fill it at our discretion, thereby setting our own rules for sorting search results. By default, for all elements its value is zero.

In my case, when searching for products in the online store catalog implemented on the romza developer's solution, it was necessary to first display those products that had a certain special check mark that was a “property” corresponding to the availability of goods in a certain outlet. All this stuff arrived with incremental loading from 1C, so this SQL query was hung on the exchange termination event from 1C.

 update b_search_content sc inner join b_iblock_element_property elp on elp.iblock_element_id=sc.item_id and elp.iblock_property_id=<_id_> set sc.custom_rank=if(elp.value=<_id__>,<_>,0) where sc.param1='<_>' and sc.param2=<_> 

In the information block setting, “Property values ​​should be stored: in the general table (default)”, and not in a separate table, otherwise the query will look somewhat different.

Now, due to the fact that the request first goes to the b_iblock_element_property table, and not b_iblock_element, which is the main storage for information block blocks, we select only those elements that have the desired property in general, and if it is equal to the desired value, then custom_rank rises otherwise, it is reset to zero.

In my case, the problem is solved, and for you there is full scope for the implementation of its own logic of sorting search results. And if you do not quite know these table names, then in the next article I will reveal their purpose in more detail.

How to quickly clean the cache in Bitrix.

“And on footer.php”: Clearing the cache in the bitrix via the control panel is very slow. In fact, there is only the removal of files in certain directories, it is just done by slow means. Make a special file to clear the cache in the bitrix on the linux command line.

 cd /home/bitrix/ cat <<'EOF' > clearcache.sh #!/bin/bash cd $( dirname $0 ) basedir=$(pwd)/www/bitrix/ for d in cache managed_cache; do find $basedir$d -mindepth 1 -delete done EOF chmod 0775 clearcache.sh 

Now you can run ./clearcache.sh, and the Bitrix cache will clear almost instantly.

This script should be one directory higher than the DOCUMENT_ROOT of your site. Although you can change both its location and code, the point is that we have to delete all files from directories:

cache
managed_cache
stack_cache - there used to be this directory before, but in recent versions I don’t watch it, so now without it.

In fact, if you delete the directories themselves, nothing happens, they will be re-created. But it is more correct to leave them.

In a virtual machine, Bitrix DOCUMENT_ROOT is equal to / home / bitrix / www /, if your site is installed in another directory, adjust the script according to your paths.