preface

The project kakaka is currently working on is a saas system. After the development of new features, users need to add corresponding permissions for their roles, and then all users need to add corresponding permissions for the whole system.

Because of the flaws of the previous system, you can now only use scripts to handle these tasks, so the next step will show you how to write safe and reliable PHP scripts, and how to design this feature in advance, step over a hole and bury it

How to write a secure and reliable PHP script

1-1 Set a proper memory

PHP uses memory_limit to set memory for each process, running scripts with 256MB or 512MB of memory.

You can set the memory to prevent the script execution from occupying too much memory and resulting in system crash.

Write ini_set(‘memory_limit’, ‘512M’) at the beginning of the file; Can.

1-2 Can receive command line parameters

The argument to the command line in PHP is $argv, subscript 0 is the file name, and subscript 1 is the argument you pass in.

Simply create an index. PHP file and print $argv

Execute the index.php file PHP index. PHP kaka and print out the same array as described above. The first value is the file name of the execution, and the second argument is the argument that is carried to the script.

In laravel, the command line carries parameters. As you can see, this parameter was only known when the RabbitMQ routing mode encountered problems, and can now be used directly when writing scripts.

1-3 execute in a while loop

$id = ! empty($argv[1]) ? $argv[1] : 0; while(true){ $sql = "select * from user id > $id order by id asc limit 10000"; $res = execute $SQL statement; if(empty($res)){ break; } foreach ($res as $k = > $v) {/ / hold the id of each value $id = $v [' id ']; $checkDataSql = "Check whether data already exists "; // Add data if(! $checkSql){ $sql = "insert into user ..." ; $res = "add "; Echo $res."\n"; } // unset($res); } echo 'ok';Copy the code

Explain the code briefly

  • Accepts the command parameter. This parameter is used to prevent the script execution from hanging. You can enter the parameter to continue the execution.
  • Use while to perform the loop logic
  • Obtain the data for which permissions need to be added, and query 10000 rows at a time.
  • The exit condition is that the data for which you want to add permissions cannot be queried.
  • Loop through 10,000 rows of data per query.
  • Loop through the queried data ID and assign it to the ID in the while to prevent the script from hanging and knowing which data to continue from.
  • Perform a check to determine whether the data already has permissions to be added. If it does not exist, add it again.
  • The last and most important step is to return the added primary key ID to the terminal, and the script hangs with this ID as an argument to continue execution.
  • Delete or 10000 rows of variables to prevent memory overflow.
  • The last and most important step of data processing is to return a sign that the script has been executed.

The example cited here is to add, if your data volume can be large under the circumstances of batch add, modify.

This script is not perfect yet, if you have better ideas, let’s see you in the comments section.

Two, how to design in advance similar to this situation

1-1 Disadvantages of using scripts to brush data

  • New employees are not familiar with the business, and it is more workload to restore data after data brushing errors
  • The initial user base is small and it will take a long time for each script to be executed
  • When the number of users is large, the deep paging query of MySQL becomes very slow, which may affect normal users
  • New users will register while you are swiping data, which is very easy to cause database deadlock.

Why do deadlocks occur?

Let’s say your current data is id, authority_info, name

,5,5 insert into t values (1), (5,5,5), (10,10,10), (15,15,15), (20,20,20), (25,25,25);Copy the code

Update role set authority_info=authority_info+1 where ID = 7

If this statement is equivalent query, it will degenerate into gap lock, and the lock range is (5,10). If a user executes the statement insert into role (8,8,8) at this time, it is a pity that the execution is unsuccessful.

Update (5,10) adds a gap lock to the data in this range, and all data in this range cannot be added without releasing the lock.

Of course, this can only happen if there is no primary key index, because the equivalent query for the primary key index degrades to a row lock.

In short, using scripts to brush data is a bad idea. Let’s see how to handle this situation.

1-2 Application scenarios

After each company is registered, it has several roles by default, such as supermanagement, sub-management, finance, administration, and human resources. All permissions are bound to the corresponding roles and a single record is used.

Since you need to add a CUI function module, you need to add the corresponding permissions of this module, which are added for all roles of all tenants by default.

The permission of each tenant’s role is stored in the role table. All permission information is stored in a JSON string, and an update time is maintained.

So let’s start with obtaining permissions. The main system maintains a separate permission table to see what the process looks like.

Plan 1-2 is implemented

So how to optimize this design to ensure that the later stage will not use scripts to brush data, but let the user trigger.

The role table structure data is as follows

The system tables

When the system needs to set permissions to add new functions, the permission name is stored as JSON in auth_info of the system table, and a data is added each time the permission is updated.

After login, the user obtains the update time of the role table, checks the system table, obtains the data that is later than the role time, merges the queried permission into the role table, and updates the update time of this record.

In addition, the newly added permissions are maintained in the application initialization process. Newly registered users need to add all permissions to the application, which needs to be maintained according to their own systems.

1-4 Disadvantages of the scheme

This solution eliminates the need to execute scripts each time to add new permissions. However, users must compare their own permission time with the system permission time each time they log in to obtain permissions.

Virtually can carry on a query more, this also is the plan that kaka can think of at present, if you have better plan can discuss.

Third, summary

This article focuses on the actual business to discuss how to write scripts, how to make up for design defects in the later stage, in the script must use positive sequence (to prevent new users to register) I believe we all know.

The solution is not optimal in my opinion, if you have a better idea, you can expand it in the comments section.

Insist on learning, insist on writing, insist on sharing is the belief that Kaka has been upholding since he started his career. May the article in the big Internet can give you a little help, I am kaka, see you next time.