One place for hosting & domains

      Manage

      How To Manage and Use MySQL Database Triggers on Ubuntu 18.04


      The author selected the the Apache Software Foundation to receive a donation as part of the Write for DOnations program.

      Introduction

      In MySQL a trigger is a user-defined SQL command that is invoked automatically during an INSERT, DELETE, or UPDATE operation. The trigger code is associated with a table and is destroyed once a table is dropped. You can specify a trigger action time and set whether it will be activated before or after the defined database event.

      Triggers have several advantages. For instance, you can use them to generate the value of a derived column during an INSERT statement. Another use case is enforcing referential integrity where you can use a trigger to save a record to multiple related tables. Other benefits include logging user actions to audit tables as well as live-copying data across different database schemas for redundancy purposes to prevent a single point of failure.

      You can also use triggers to keep validation rules at the database level. This helps in sharing the data source across multiple applications without breaking the business logic. This greatly reduces round-trips to the database server, which therefore improves the response time of your applications. Since the database server executes triggers, they can take advantage of improved server resources such as RAM and CPU.

      In this tutorial, you’ll create, use, and delete different types of triggers on your MySQL database.

      Prerequisites

      Before you begin, make sure you have the following:

      Step 1 — Creating a Sample Database

      In this step, you’ll create a sample customer database with multiple tables for demonstrating how MySQL triggers work.

      To understand more about MySQL queries read our Introduction to Queries in MySQL.

      First, log in to your MySQL server as root:

      Enter your MySQL root password when prompted and hit ENTER to continue. When you see the mysql> prompt, run the following command to create a test_db database:

      Output

      Query OK, 1 row affected (0.00 sec)

      Next, switch to the test_db with:

      Output

      Database changed

      You’ll start by creating a customers table. This table will hold the customers’ records including the customer_id, customer_name, and level. There will be two customer levels: BASIC and VIP.

      • Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;

      Output

      Query OK, 0 rows affected (0.01 sec)

      Now, add a few records to the customers table. To do this, run the following commands one by one:

      • Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
      • Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
      • Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

      You’ll see the following output after running each of the INSERT commands:

      Output

      Query OK, 1 row affected (0.01 sec)

      To make sure that the sample records were inserted successfully, run the SELECT command:

      Output

      +-------------+---------------+-------+ | customer_id | customer_name | level | +-------------+---------------+-------+ | 1 | JOHN DOE | BASIC | | 2 | MARY ROE | BASIC | | 3 | JOHN DOE | VIP | +-------------+---------------+-------+ 3 rows in set (0.00 sec)

      You’ll also create another table for holding related information about the customers account. The table will have a customer_id and status_notes fields.

      Run the following command:

      • Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

      Next, you’ll create a sales table. This table will hold sales data related to the different customers through the customer_id column:

      • Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

      Output

      Query OK, 0 rows affected (0.01 sec)

      You’ll add sample data to the sales data in the coming steps while testing the triggers. Next, create an audit_log table to log updates made to the sales table when you implement the AFTER UPDATE trigger in Step 5:

      • Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;

      Output

      Query OK, 0 rows affected (0.02 sec)

      With the test_db database and the four tables in place, you’ll now move on to work with the different MySQL triggers in your database.

      Step 2 — Creating a Before Insert Trigger

      In this step, you’ll examine the syntax of a MySQL trigger before applying this logic to create a BEFORE INSERT trigger that validates the sales_amount field when data is inserted into the sales table.

      The general syntax for creating a MySQL trigger is shown in the following example:

      DELIMITER //
      CREATE TRIGGER [TRIGGER_NAME]
      [TRIGGER TIME] [TRIGGER EVENT]
      ON [TABLE]
      FOR EACH ROW
      [TRIGGER BODY]//
      DELIMITER ;
      

      The structure of the trigger includes:

      DELIMITER //: The default MySQL delimiter is ;—it’s necessary to change it to something else in order for MySQL to treat the following lines as one command until it hits your custom delimiter. In this example, the delimiter is changed to // and then the ; delimiter is redefined at the end.

      [TRIGGER_NAME]: A trigger must have a name and this is where you include the value.

      [TRIGGER TIME]: A trigger can be invoked during different timings. MySQL allows you to define if the trigger will initiate before or after a database operation.

      [TRIGGER EVENT]: Triggers are only invoked by INSERT, UPDATE, and DELETE operations. You can use any value here depending on what you want to achieve.

      [TABLE]: Any trigger that you create on your MySQL database must be associated with a table.

      FOR EACH ROW: This statement tells MySQL to execute the trigger code for every row that the trigger affects.

      [TRIGGER BODY]: The code that is executed when the trigger is invoked is called a trigger body. This can be a single SQL statement or multiple commands. Note that if you are executing multiple SQL statements on the trigger body, you must wrap them between a BEGIN...END block.

      Note: When creating the trigger body, you can use the OLD and NEW keywords to access the old and new column values entered during an INSERT, UPDATE, and DELETE operation. In a DELETE trigger, only the OLD keyword can be used (which you’ll use in Step 4).

      Now you’ll create your first BEFORE INSERT trigger. This trigger will be associated with the sales table and it will be invoked before a record is inserted to validate the sales_amount. The function of the trigger is to check if the sales_amount being inserted to the sales table is greater than 10000 and raise an error if this evaluates to true.

      Make sure you’re logged in to the MySQL server. Then, enter the following MySQL commands one by one:

      • DELIMITER //
      • CREATE TRIGGER validate_sales_amount
      • BEFORE INSERT
      • ON sales
      • FOR EACH ROW
      • IF NEW.sales_amount>10000 THEN
      • SIGNAL SQLSTATE '45000'
      • SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
      • END IF//
      • DELIMITER ;

      You’re using the IF...THEN...END IF statement to evaluate if the amount being supplied during the INSERT statement is within your range. The trigger is able to extract the new sales_amount value being supplied by using the NEW keyword.

      To raise a generic error message, you use the following lines to inform the user about the error:

      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
      

      Next, insert a record with a sales_amount of 11000 to the sales table to check if the trigger will stop the operation:

      • Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');

      Output

      ERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

      This error shows that the trigger code is working as expected.

      Now try a new record with a value of 7500 to check if the command will be successful:

      • Insert into sales(sales_id, customer_id, sales_amount) values('1','1','7500');

      Since the value is within the recommended range, you’ll see the following output:

      Output

      Query OK, 1 row affected (0.01 sec)

      To confirm that the data was inserted run the following command:

      The output confirms that the data is in the table:

      Output

      +----------+-------------+--------------+ | sales_id | customer_id | sales_amount | +----------+-------------+--------------+ | 1 | 1 | 7500 | +----------+-------------+--------------+ 1 row in set (0.00 sec)

      In this step you’ve tested triggers to validate data before insertion into a database.

      Next, you’ll work with the AFTER INSERT trigger to save related information into different tables.

      Step 3 — Creating an After Insert Trigger

      AFTER INSERT triggers are executed when records are successfully inserted into a table. This functionality can be used to run other business-related logics automatically. For instance, in a bank application, an AFTER INSERT trigger can close a loan account when a customer finishes paying off the loan. The trigger can monitor all payments inserted to a transaction table and close the loan automatically once the loan balance is zero.

      In this step, you’ll work with your customer_status table by using an AFTER INSERT trigger to enter related customer records.

      To create the AFTER INSERT trigger, enter the following commands:

      • DELIMITER //
      • CREATE TRIGGER customer_status_records
      • AFTER INSERT
      • ON customers
      • FOR EACH ROW
      • Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
      • DELIMITER ;

      Output

      Query OK, 0 rows affected (0.00 sec)

      Here you instruct MySQL to save another record to the customer_status table once a new customer record is inserted to the customers table.

      Now, insert a new record in the customers table to confirm your trigger code will be invoked:

      • Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');

      Output

      Query OK, 1 row affected (0.01 sec)

      Since the record was inserted successfully, check that a new status record was inserted into the customer_status table:

      • Select * from customer_status;

      Output

      +-------------+-----------------------------+ | customer_id | status_notes | +-------------+-----------------------------+ | 4 | ACCOUNT OPENED SUCCESSFULLY | +-------------+-----------------------------+ 1 row in set (0.00 sec)

      The output confirms that the trigger ran successfully.

      The AFTER INSERT trigger is useful in monitoring the lifecycle of a customer. In a production environment, customers’ accounts may undergo different stages such as account opening, suspension, and closing.

      In the following steps you’ll work with UPDATE triggers.

      Step 4 — Creating a Before Update Trigger

      A BEFORE UPDATE trigger is similar to the BEFORE INSERT trigger—the difference is when they are invoked. You can use the BEFORE UPDATE trigger to check a business logic before a record is updated. To test this, you’ll use the customers table in which you’ve inserted some data already.

      You have two levels for your customers in the database. In this example, once a customer account is upgraded to the VIP level, the account can not be downgraded to the BASIC level. To enforce such a rule, you will create a BEFORE UPDATE trigger that will execute before the UPDATE statement as shown following. If a database user tries to downgrade a customer to the BASIC level from the VIP level, a user-defined exception will be triggered.

      Enter the following SQL commands one by one to create the BEFORE UPDATE trigger:

      • DELIMITER //
      • CREATE TRIGGER validate_customer_level
      • BEFORE UPDATE
      • ON customers
      • FOR EACH ROW
      • IF OLD.level='VIP' THEN
      • SIGNAL SQLSTATE '45000'
      • SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';
      • END IF //
      • DELIMITER ;

      You use the OLD keyword to capture the level that the user is supplying when running the UPDATE command. Again, you use the IF...THEN...END IF statement to signal a generic error statement to the user.

      Next, run the following SQL command that tries to downgrade a customer account associated with the customer_id of 3:

      • Update customers set level='BASIC' where customer_id='3';

      You’ll see the following output providing the SET MESSAGE_TEXT:

      Output

      ERROR 1644 (45000): A VIP customer can not be downgraded.

      If you run the same command to a BASIC level customer, and try to upgrade the account to the VIP level, the command will execute successfully:

      • Update customers set level='VIP' where customer_id='1';

      Output

      Rows matched: 1 Changed: 1 Warnings: 0

      You’ve used the BEFORE UPDATE trigger to enforce a business rule. Now you’ll move on to use an AFTER UPDATE trigger for audit logging.

      Step 5 — Creating an After Update Trigger

      An AFTER UPDATE trigger is invoked once a database record is updated successfully. This behavior makes the trigger suitable for audit logging. In a multi-user environment, the administrator may want to view a history of users updating records in a particular table for audit purposes.

      You’ll create a trigger that logs the update activity of the sales table. Our audit_log table will contain information about the MySQL users updating the sales table, the date of the update, and the new and old sales_amount values.

      To create the trigger, run the following SQL commands:

      • DELIMITER //
      • CREATE TRIGGER log_sales_updates
      • AFTER UPDATE
      • ON sales
      • FOR EACH ROW
      • Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
      • DELIMITER ;

      You insert a new record to the audit_log table. You use the NEW keyword to retrieve the value of the sales_id and the new sales_amount. Also, you use the OLD keyword to retrieve the previous sales_amount since you want to log both amounts for audit purposes.

      The command SELECT USER() retrieves the current user performing the operation and the NOW() statement retrieves the value of the current date and time from the MySQL server.

      Now if a user tries to update the value of any record in the sales table, the log_sales_updates trigger will insert a new record to the audit_log table.

      Let’s create a new sales record with a random sales_id of 5 and try to update it. First, insert the sales record with:

      • Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');

      Output

      Query OK, 1 row affected (0.00 sec)

      Next, update the record:

      • Update sales set sales_amount='9000' where sales_id='5';

      You’ll see the following output:

      Output

      Rows matched: 1 Changed: 1 Warnings: 0

      Now run the following command to verify if the AFTER UPDATE trigger was able to register a new record into the audit_log table:

      The trigger logged the update. Your output shows the previous sales_amount and new amount registered with the user that updated the records:

      Output

      +--------+----------+-----------------+------------+----------------+---------------------+ | log_id | sales_id | previous_amount | new_amount | updated_by | updated_on | +--------+----------+-----------------+------------+----------------+---------------------+ | 1 | 5 | 8000 | 9000 | root@localhost | 2019-11-07 09:28:36 | +--------+----------+-----------------+------------+----------------+---------------------+ 1 row in set (0.00 sec)

      You also have the date and time the update was performed, which are valuable for audit purposes.

      Next you’ll use the DELETE trigger to enforce referencing integrity at the database level.

      Step 6 — Creating a Before Delete Trigger

      BEFORE DELETE triggers invoke before a DELETE statement executes on a table. These kinds of triggers are normally used to enforce referential integrity on different related tables. For example, each record on the sales table relates to a customer_id from the customers table. If a database user deleted a record from the customers table that has a related record in the sales table, you would have no way of knowing the customer associated with that record.

      To avoid this, you can create a BEFORE DELETE trigger to enforce your logic. Run the following SQL commands one by one:

      • DELIMITER //
      • CREATE TRIGGER validate_related_records
      • BEFORE DELETE
      • ON customers
      • FOR EACH ROW
      • IF OLD.customer_id in (select customer_id from sales) THEN
      • SIGNAL SQLSTATE '45000'
      • SET MESSAGE_TEXT = 'The customer has a related sales record.';
      • END IF//
      • DELIMITER ;

      Now, try to delete a customer that has a related sales record:

      • Delete from customers where customer_id='2';

      As a result you’ll receive the following output:

      Output

      ERROR 1644 (45000): The customer has a related sales record.

      The BEFORE DELETE trigger can prevent accidental deletion of related information in a database.

      However, in some situations, you may want to delete all the records associated with a particular record from the different related tables. In this situation you would use the AFTER DELETE trigger, which you’ll test in the next step.

      Step 7 — Creating an After Delete Trigger

      AFTER DELETE triggers are activated once a record has been deleted successfully. An example of how you can use an AFTER DELETE trigger is a situation in which the discount level a particular customer receives is determined by the number of sales made during a defined period. If some of the customer’s records are deleted from the sales table, the customer discount level would need to be downgraded.

      Another use of the AFTER DELETE trigger is deleting related information from another table once a record from a base table is deleted. For instance, you’ll set a trigger that deletes the customer record if the sales records with the related customer_id are deleted from the sales table. Run the following command to create your trigger:

      • DELIMITER //
      • CREATE TRIGGER delete_related_info
      • AFTER DELETE
      • ON sales
      • FOR EACH ROW
      • Delete from customers where customer_id=OLD.customer_id;//
      • DELIMITER ;

      Next, run the following to delete all sales records associated with a customer_id of 2:

      • Delete from sales where customer_id='2';

      Output

      Query OK, 1 row affected (0.00 sec)

      Now check if there are records for the customer from the sales table:

      • Select * from customers where customer_id='2';

      You will receive an Empty Set output since the customer record associated with the customer_id of 2 was deleted by the trigger:

      Output

      Empty set (0.00 sec)

      You’ve now used each of the different forms of triggers to perform specific functions. Next you will see how you can remove a trigger from the database if you no longer need it.

      Step 8 — Deleting Triggers

      Similarly to any other database object, you can delete triggers using the DROP command. The following is the syntax for deleting a trigger:

      Drop trigger [TRIGGER NAME];
      

      For instance, to delete the last AFTER DELETE trigger that you created, run the following command:

      • Drop trigger delete_related_info;

      Output

      Query OK, 0 rows affected (0.00 sec)

      The need to delete triggers arises when you want to recreate its structure. In such a case, you can drop the trigger and redefine a new one with the different trigger commands.

      Conclusion

      In this tutorial you’ve created, used, and deleted the different kinds of triggers from a MySQL database. Using an example customer-related database you’ve implemented triggers for different use cases such as data validation, business-logic application, audit logging, and enforcing referential integrity.

      For further information on using your MySQL database, check out the following:



      Source link

      How To Manage Sorted Sets in Redis


      Introduction

      Redis is an open-source, in-memory key-value data store. In Redis, sorted sets are a data type similar to sets in that both are non repeating groups of strings. The difference is that each member of a sorted set is associated with a score, allowing them to be sorted from the smallest score to the largest. As with sets, every member of a sorted set must be unique, though multiple members can share the same score.

      This tutorial explains how to create sorted sets, retrieve and remove their members, and create new sorted sets from existing ones.

      How To Use This Guide

      This guide is written as a cheat sheet with self-contained examples. We encourage you to jump to any section that is relevant to the task you’re trying to complete.

      The commands shown in this guide were tested on an Ubuntu 18.04 server running Redis version 4.0.9. To set up a similar environment, you can follow Step 1 of our guide on How To Install and Secure Redis on Ubuntu 18.04. We will demonstrate how these commands behave by running them with redis-cli, the Redis command line interface. Note that if you’re using a different Redis interface — Redli, for example — the exact output of certain commands may differ.

      Alternatively, you could provision a managed Redis database instance to test these commands, but note that depending on the level of control allowed by your database provider, some commands in this guide may not work as described. To provision a DigitalOcean Managed Database, follow our Managed Databases product documentation. Then, you must either install Redli or set up a TLS tunnel in order to connect to the Managed Database over TLS.

      Creating Sorted Sets and Adding Members

      To create a sorted set, use the zadd command. zadd accepts as arguments the name of the key that will hold the sorted set, followed by the score of the member you’re adding and the value of the member itself. The following command will create a sorted set key named faveGuitarists with one member, "Joe Pass", that has a score of 1:

      • zadd faveGuitarists 1 "Joe Pass"

      zadd will return an integer that indicates how many members were added to the sorted set if it was created successfully.

      Output

      (integer) 1

      You can add more than one member to a sorted set with zadd. Note that their scores don’t need to be sequential, there can be gaps between scores, and multiple members held in the same sorted set can share the same score:

      • zadd faveGuitarists 4 "Stephen Malkmus" 2 "Rosetta Tharpe" 3 "Bola Sete" 3 "Doug Martsch" 8 "Elizabeth Cotten" 12 "Nancy Wilson" 4 "Memphis Minnie" 12 "Michael Houser"

      Output

      (integer) 8

      zadd can accept the following options, which you must enter after the key name and before the first member score:

      • NX or XX: These options have opposite effects, so you can only include one of them in any zadd operation:
        • NX: Tells zadd not to update existing members. With this option, zadd will only add new elements.
        • XX: Tells zadd to only update existing elements. With this option, zadd will never add new members.
      • CH: Normally, zadd only returns the number of new elements added to the sorted set. With this option included, though, zadd will return the number changed elements. This includes newly added members and members whose scores were changed.
      • INCR: This causes the command to increment the member’s score value. If the member doesn’t yet exist, the command will add it to the sorted set with the increment as its score, as if its original score was 0. With INCR included, the zadd will return the member’s new score if it’s successful. Note that you can only include one score/member pair at a time when using this option.

      Instead of passing the INCR option to zadd, you can instead use the zincrby command which behaves the exact same way. Instead of giving the sorted set member the value indicated by the score value like zadd, it increments that member’s score up by that value. For example, the following command increments the score of the member "Stephen Malkmus", which was originally 4, up by 5 to 9.

      • zincrby faveGuitarists 5 "Stephen Malkmus"

      Output

      "9"

      As is the case with the zadd command’s INCR option, if the specified member doesn’t exist then zincrby will create it with the increment value as its score.

      Retrieving Members from Sorted Sets

      The most fundamental way to retrieve the members held within a sorted set is to use the zrange command. This command accepts as arguments the name of the key whose members you want to retrieve and a range of members held within it. The range is defined by two numbers that represent zero-based indexes, meaning that 0 represents the first member in the sorted set (or, the member with the lowest score), 1 represents the next, and so on.

      The following example will return the first four members from the faveGuitarists sorted set created in the previous section:

      • zrange faveGuitarists 0 3

      Output

      1) "Joe Pass" 2) "Rosetta Tharpe" 3) "Bola Sete" 4) "Doug Martsch"

      Note that if the sorted set you pass to zrange has two or more elements that share the same score, it will sort those elements in lexicographical, or alphabetical, order.

      The start and stop indexes can also be negative numbers, with -1 representing the last member, -2 representing the second to last, and so on:

      • zrange faveGuitarists -5 -2

      Output

      1) "Memphis Minnie" 2) "Elizabeth Cotten" 3) "Stephen Malkmus" 4) "Michael Houser"

      zrange can accept the WITHSCORES argument which, when included, will also return the members’ scores:

      • zrange faveGuitarists 5 6 WITHSCORES

      Output

      1) "Elizabeth Cotten" 2) "8" 3) "Stephen Malkmus" 4) "9"

      zrange can only return a range of members in ascending numerical order. To reverse this and return a range in descending order, you must use the zrevrange command. Think of this command as temporarily reversing the order of the given sorted set before returning the members that fall within the specified range. So with zrevrange, 0 will represent the last member held in the key, 1 will represent the second to last, and so on:

      • zrevrange faveGuitarists 0 5

      Output

      1) "Nancy Wilson" 2) "Michael Houser" 3) "Stephen Malkmus" 4) "Elizabeth Cotten" 5) "Memphis Minnie" 6) "Doug Martsch"

      zrevrange can also accept the WITHSCORES option.

      You can return a range of members based on their scores with the zrangebyscore command. In the following example, the command will return any member held in the faveGuitarists key with a score of 2, 3, or 4:

      • zrangebyscore faveGuitarists 2 4

      Output

      1) "Rosetta Tharpe" 2) "Bola Sete" 3) "Doug Martsch" 4) "Memphis Minnie"

      The range is inclusive in this example, meaning that it will return members with scores of 2 or 4. You can exclude either end of the range by preceding it with an open parenthesis ((). The following example will return every member with a score greater than or equal to 2, but less than 4:

      • zrangebyscore faveGuitarists 2 (4

      Output

      1) "Rosetta Tharpe" 2) "Bola Sete" 3) "Doug Martsch"

      As with zrange, zrangebyscore can accept the WITHSCORES argument. It also accepts the LIMIT option, which you can use to retrieve only a selection of elements from the zrangebyscore output. This option accepts an offset, which marks the first member in the range that the command will return, and a count, which defines how many members the command will return in total. For example, the following command will look at the first six members of the faveGuitarists sorted set but will only return 3 members from it, starting from the second member in the range, represented by 1:

      • zrangebyscore faveGuitarists 0 5 LIMIT 1 3

      Output

      1) "Rosetta Tharpe" 2) "Bola Sete" 3) "Doug Martsch"

      The zrevrangebyscore command returns a reversed range of members based on their scores. The following command returns every member of the set with a score between 10 and 6:

      • zrevrangebyscore faveGuitarists 10 6

      Output

      1) "Stephen Malkmus" 2) "Elizabeth Cotten"

      As with zrangebyscore, zrevrangebyscore can accept both the WITHSCORES and LIMIT options. Additionally, you can exclude either end of the range by preceding it with an open parenthesis.

      There may be times when all the members in a sorted set have the same score. In such a case, you can force redis to return a range of elements sorted lexicographically, or in alphabetical order, with the zrangebylex command. To try out this command, run the following zadd command to create a sorted set where each member has the same score:

      • zadd SomervilleSquares 0 Davis 0 Inman 0 Union 0 porter 0 magoun 0 ball 0 assembly

      zrangebylex must be followed by the name of a key, a start interval, and a stop interval. The start and stop intervals must begin with an open parenthesis (() or an open bracket ([), like this:

      • zrangebylex SomervilleSquares [a [z

      Output

      1) "assembly" 2) "ball" 3) "magoun" 4) "porter"

      Notice that this example returned only four of the eight members in the set, even though the command sought a range from a to z. This is because Redis values are case-sensitive, so the members that begin with uppercase letters were excluded from its output. To return those, you could run the following:

      • zrangebylex SomervilleSquares [A [z

      Output

      1) "Davis" 2) "Inman" 3) "Union" 4) "assembly" 5) "ball" 6) "magoun" 7) "porter"

      zrangebylex also accepts the special characters -, which represents negative infinity, and +, which represents positive infinity. Thus, the following command syntax will also return every member of the sorted set:

      • zrangebylex SomervilleSquares - +

      Note that zrangebylex cannot return sorted set members in reverse lexicographical (ascending alphabetical) order. To do that, use zrevrangebylex:

      • zrevrangebylex SomervilleSquares + -

      Output

      1) "porter" 2) "magoun" 3) "ball" 4) "assembly" 5) "Union" 6) "Inman" 7) "Davis"

      Because it’s intended for use with sorted sets where every member has the same score, zrangebylex does not accept the WITHSCORES option. It does, however, accept the LIMIT option.

      Retrieving Information about Sorted Sets

      To find out how many members are in a given sorted set (or, in other words, to determine its cardinality), use the zcard command. The following example shows how many members are held in the faveGuitarists key from the first section of this guide:

      Output

      (integer) 9

      zcount can tell you how many elements are held within a given sorted set that fall within a range of scores. The first number following the key is the start of the range and the second one is the end of the range:

      • zcount faveGuitarists 3 8

      Output

      (integer) 4

      zscore outputs the score of a specified member of a sorted set:

      • zscore faveGuitarists "Bola Sete"

      Output

      "3"

      If either the specified member or key don’t exist, zscore will return (nil).

      zrank is similar to zscore, but instead of returning the given member’s score, it instead returns its rank. In Redis, a rank is a zero-based index of the members of a sorted set, ordered by their score. For example, "Joe Pass" has a score of 1, but because that is the lowest score of any member in the key, it has a rank of 0:

      • zrank faveGuitarists "Joe Pass"

      Output

      (integer) 0

      There’s another Redis command called zrevrank which performs the same function as zrank, but instead reverses the ranks of the members in the set. In the following example, the member "Joe Pass" has the lowest score, and consequently has the highest reversed rank:

      • zrevrank faveGuitarists "Joe Pass"

      Output

      (integer) 8

      The only relation between a member’s score and their rank is where their score stands in relation to those of other members. If there is a score gap between two sequential members, that won’t be reflected in their rank. Note that if two members have the same score, the one that comes first alphabetically will have the lower rank.

      Like zscore, zrank and zrevrank will return (nil) if the key or member doesn’t exist.

      zlexcount can tell you how many members are held in a sorted set between a lexicographical range. The following example uses the SomervilleSquares sorted set from the previous section:

      • zlexcount SomervilleSquares [M [t

      Output

      (integer) 5

      This command follows the same syntax as the zrangebylex command, so refer to the previous section for details on how to define a string range.

      Removing Members from Sorted Sets

      The zrem command can remove one or more members from a sorted set:

      • zrem faveGuitarists "Doug Martsch" "Bola Sete"

      zrem will return an integer indicating how many members it removed from the sorted set:

      Output

      (integer) 2

      There are three Redis commands that allow you to remove members of a sorted set based on a range. For example, if each member in a sorted set has the same score, you can remove members based on a lexicographical range with zremrangebylex. This command uses the same syntax as zrangebylex. The following example will remove every member that begins with a capital letter from the SomervilleSquares key created in the previous section:

      • zremrangebylex SomervilleSquares [A [Z

      zremrangebylex will output an integer indicating how many members it removed:

      Output

      (integer) 3

      You can also remove members based on a range of scores with the zremrangebyscore command, which uses the same syntax as the zrangebyscore command. The following example will remove every member held in faveGuitarists with a score of 4, 5, or 6:

      • zremrangebyscore faveGuitarists 4 6

      Output

      (integer) 1

      You can remove members from a set based on a range of ranks with the zremrangebyrank command, which uses the same syntax as zrangebyrank. The following command will remove the three members of the sorted set with the lowest rankings, which are defined by a range of zero-based indexes:

      • zremrangebyrank faveGuitarists 0 2

      Output

      (integer) 3

      Note that numbers passed to remrangebyrank can also be negative, with -1 representing the highest rank, -2 the next highest, and so on.

      Creating New Sorted Sets from Existing Ones

      Redis includes two commands that allow you to compare members of multiple sorted sets and create new ones based on those comparisons: zinterstore and zunionstore. To experiment with these commands, run the following zadd commands to create some example sorted sets.

      • zadd NewKids 1 "Jonathan" 2 "Jordan" 3 "Joey" 4 "Donnie" 5 "Danny"
      • zadd Nsync 1 "Justin" 2 "Chris" 3 "Joey" 4 "Lance" 5 "JC"

      zinterstore finds the members shared by two or more sorted sets — their intersection — and produces a new sorted set containing only those members. This command must include, in order, the name of a destination key where the intersecting members will be stored as a sorted set, the number of keys being passed to zinterstore, and the names of the keys you want to analyze:

      • zinterstore BoyBands 2 NewKids Nsync

      zinterstore will return an integer showing the number of elements stored to the destination sorted set. Because NewKids and Nsync only share one member, "Joey", the command will return 1:

      Output

      (integer) 1

      Be aware that if the destination key already exists, zinterstore will overwrite its contents.

      zunionstore will create a new sorted set holding every member of the keys passed to it. This command uses the same syntax as zinterstore, and requires the name of a destination key, the number of keys being passed to the command, and the names of the keys:

      • zunionstore SuperGroup 2 NewKids Nsync

      Like zinterstore, zunionstore will return an integer showing the number of elements stored in the destination key. Even though both of the original sorted sets held five members, because sorted sets can’t have repeating members and each key has one member named "Joey", the resulting integer will be 9:

      Output

      (integer) 9

      Like zinterstore, zunionstore will overwrite the contents of the destination key if it already exists.

      To give you more control over member scores when creating new sorted sets with zinterstore and zunionstore, both of these commands accept the WEIGHTS and AGGREGATE options.

      The WEIGHTS option is followed by one number for every sorted set included in the command which weight, or multiply, the scores of each member. The first number after the WEIGHTS option weights the scores of the first key passed to the command, the second number weights the second key, and so on.

      The following example creates a new sorted set holding the intersecting keys from the NewKids and Nsync sorted sets. It weights the scores in the NewKids key by a factor of three, and weights those in the Nsync key by a factor of seven:

      • zinterstore BoyBandsWeighted 2 NewKids Nsync WEIGHTS 3 7

      If the WEIGHTS option isn’t included, the weighting defaults to 1 for both zinterstore and zunionstore.

      AGGREGATE accepts three sub-options. The first of these, SUM, implements zinterstore and zunionstore’s default behavior by adding the scores of matching members in the combined sets.

      If you run a zinterstore or zunionstore operation on two sorted sets that share one member, but this member has a different score in each set, you can force the operation to assign the lower of the two scores in the new set with the MIN suboption.

      • zinterstore BoyBandsWeightedMin 2 NewKids Nsync WEIGHTS 3 7 AGGREGATE MIN

      Because the two sorted sets only have one matching member with the same score (3), this command will create a new set with a member that has the lower of the two weighted scores:

      • zscore BoyBandsWeightedMin "Joey"

      Output

      "9"

      Likewise, AGGREGATE can force zinterstore or zunionstore to assign the higher of the two scores with the MAX option:

      • zinterstore BoyBandsWeightedMax 2 NewKids Nsync WEIGHTS 3 7 AGGREGATE MAX

      This command creates a new set with on one member, "Joey", that has the higher of the two weighted scores:

      • zscore BoyBandsWeightedMax "Joey"

      Output

      "21"

      It can be helpful to think of WEIGHTS as a way to temporarily manipulate members’ scores before they’re analyzed. Likewise, it’s helpful to think of the AGGREGATE option as a way to decide how to control members’ scores before they’re added to their new sets.

      Conclusion

      This guide details a number of commands used to create and manage sorted sets in Redis. If there are other related commands, arguments, or procedures you’d like to see outlined in this guide, please ask or make suggestions in the comments below.

      For more information on Redis commands, see our tutorial series on How to Manage a Redis Database.



      Source link

      How To Manage Objects with Lifecycle Policies


      Updated by Linode

      Contributed by
      Linode

      Note

      While deleting a few objects in an Object Storage bucket might not take that long, when the objects number in the thousands or even millions the time required to complete the delete operations can easily become unmanageable. When deleting a substantial amount of objects, it’s best to use lifecycle policies. These policies can be represented in XML; here’s an (incomplete) snippet of an action that will delete objects after 1 day:

      1
      2
      3
      
      <Expiration>
          <Days>1</Days>
      </Expiration>

      A lifecycle policy is applied to a bucket. Policies are sets of rules that govern the management of objects after they have aged for a certain amount of time. For instance, you can create a lifecycle policy that deletes objects every thirty days, or once a week. This is useful for cases where the data in a bucket becomes outdated, such as when collecting activity logs.

      In This Guide

      This guide will first describe when policies are enforced and will then explain how to create and delete lifecycle policies with two tools:

      • s3cmd command line interface (CLI): In addition to deleting objects, more complicated policies can be managed with s3cmd, including deleting old versions of objects that have been retained, and failed multipart uploads.

      • Cyberduck desktop application (GUI): Cyberduck does not feature as many policy options, but they can be managed through a point-and-click interface.

      Before You Begin

      • Familiarize yourself with Linode Object Storage by reading the How to Use Object Storage guide.
      • For demonstration purposes, you can create an Object Storage bucket with a few objects that you will later delete.

      When Policies are Enforced

      Lifecycle policies are triggered starting at midnight of the Object Storage cluster’s local time. This means that if you set a lifecycle policy of one day, the objects will be deleted the midnight after they become 24 hours old.

      For example, if an object is created at 5PM on January 1, it will reach 24 hours in age at 5PM on January 2. The policy will then be enforced on the object at 12AM on January 3.

      Note

      There is a chance that a lifecycle policy will not delete all of the files in a bucket the first time the lifecycle policy is triggered. This is especially true for buckets with upwards of a million objects. In cases like these, most of the objects are deleted, and any remaining objects are typically deleted during the next iteration of the lifecycle policy’s rules.

      Create and Delete Lifecycle Policies

      s3cmd

      s3cmd allows users to set and manage lifecycle policies from the command line. In this section, you will find instructions on how to create and manage lifecycle policies to delete objects, previous versions of objects, and failed multipart uploads using s3cmd.

      Note

      If you don’t have s3cmd set up on your computer, visit the Install and Configure s3cmd section of the How to Use Linode Object Storage guide.

      Creating a Lifecycle Policy File

      In S3-compatible Object Storage, a lifecycle policy is represented by an XML file. You can use your preferred text editor to create this XML file. Consider the following lifecycle policy file:

      lifecycle_policy.xml
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      
      <LifecycleConfiguration>
          <Rule>
              <ID>delete-all-objects</ID>
              <Prefix></Prefix>
              <Status>Enabled</Status>
              <Expiration>
                  <Days>1</Days>
              </Expiration>
          </Rule>
      </LifecycleConfiguration>

      The above lifecycle policy deletes all objects in the bucket after one day. Each lifecycle policy file needs a LifecycleConfiguration block and a nested Rule block. The Rule block must contain Prefix and Status, and at least one action, like the Expiration block. It’s also a good idea to include an ID block:

      BlockDescription
      IDDefines a name for the lifecycle policy rule. If your lifecycle policy contains multiple rules, then the ID for each should be unique. If one is not specified in your policy file, then a random alphanumeric ID will be assigned to your policy when the policy is applied to a bucket.
      PrefixThis string is used to select objects for deletion with the same matching prefix. For example, objects that begin with error_report- could be targeted for deletion by providing this prefix. This Prefix can be empty if you want a rule to apply to all files in a bucket.
      StatusA string value describing the status of the lifecycle policy. To enable the policy, set this value to Enabled. To disable the policy set the value to Disabled.
      ExpirationContains the Days block. The Days block is the number of days before this rule will be enforced. In the above example, the Days is set to 1, meaning that the objects in the bucket will be deleted after one day.

      Additional Actions

      Other actions can also be specified in a rule:

      • NoncurrentVersionExpiration block, and its child, NoncurrentDays. These are used to control the lifecycle of objects with multiple older versions, and should only be used with buckets that have bucket versioning enabled. Using this option will delete objects that are not the newest, most current version. Below is an example of how to use NoncurrentVersionExpiration:

        lifecycle_policy_noncurrent_versions.xml
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        
        <LifecycleConfiguration>
            <Rule>
                <ID>delete-prior-versions</ID>
                <Prefix></Prefix>
                <Status>Enabled</Status>
                <NoncurrentVersionExpiration>
                    <NoncurrentDays>1</NoncurrentDays>
                </NoncurrentVersionExpiration>
            </Rule>
        </LifecycleConfiguration>
      • AbortIncompleteMultipartUpload, and its child, DaysAfterInitiation. These work similarly to NoncurrentVersionExpiration, but instead of deleting previous versions of objects, they will delete failed multipart uploads. The following will delete failed multipart uploads three days after they were initiated:

        lifecycle_policy_multipart_upload.xml
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        
        <LifecycleConfiguration>
            <Rule>
                <ID>delete-incomplete-multipart-uploads</ID>
                <Prefix></Prefix>
                <Status>Enabled</Status>
                <AbortIncompleteMultipartUpload>
                    <DaysAfterInitiation>3</DaysAfterInitiation>
                </AbortIncompleteMultipartUpload>
            </Rule>
        </LifecycleConfiguration>



        About multipart uploads

        Objects that are part of failed multipart uploads (the mechanism by which large files are uploaded) stay within Object Storage buckets, counting towards your total Object Storage costs. s3cmd will automatically initiate a multipart upload when a file is larger than 15MB. Lifecycle policies are a great way to clear out stale multipart uploads.

      Multiple Actions in One Rule

      More than one action can be specified in a single rule. For example, you may want to both expire the current version of an object after a set number of days and also remove old versions of it after another period of time. The following policy will delete the current version of an object after 10 days and remove any noncurrent versions of an object 3 days after they are demoted from the current version:

      lifecycle_policy_multipart_upload.xml
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      12
      13
      
      <LifecycleConfiguration>
          <Rule>
              <ID>delete-prior-versions</ID>
              <Prefix></Prefix>
              <Status>Enabled</Status>
              <Expiration>
                  <Days>10</Days>
              </Expiration>
              <NoncurrentVersionExpiration>
                  <NoncurrentDays>3</NoncurrentDays>
              </NoncurrentVersionExpiration>
          </Rule>
      </LifecycleConfiguration>

      Note

      As a reminder, if a versioned object is deleted, only the current version of the object will be deleted and all older versions will be preserved in the bucket. For this reason, the above rule has the effect of deleting any objects if they are not updated within 10 days, and then removing the remaining object versions after 3 days.

      Multiple Rules

      A lifecycle policy file can only contain one LifecycleConfiguration block, but the LifecycleConfiguration block can contain more than one Rule. For instance, if you had a bucket that contained both error and general output logs, you could set a lifecycle policy that saves error logs for a week but deletes standard logs at the end of every day:

      lifecycle_policy_error_and_standard_logs.xml
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      
      <LifecycleConfiguration>
          <Rule>
              <ID>delete-error-logs</ID>
              <Prefix>error</Prefix>
              <Status>Enabled</Status>
              <Expiration>
                  <Days>7</Days>
              </Expiration>
          </Rule>
          <Rule>
              <ID>delete-standard-logs</ID>
              <Prefix>logs</Prefix>
              <Status>Enabled</Status>
              <Expiration>
                  <Days>1</Days>
              </Expiration>
          </Rule>
      </LifecycleConfiguration>

      Uploading the Lifecycle Policy to a Bucket

      In order to apply a lifecycle policy to a bucket with s3cmd, you need to upload the lifecycle file to the bucket. This operation is not a normal PUT operation. Instead, the command to use is setlifecycle, followed by the name of the lifecycle policy file, and the name of bucket:

      s3cmd setlifecycle lifecycle_policy.xml s3://lifecycle-policy-example
      

      You should see output like the following:

      s3://lifecycle-policy-example/: Lifecycle Policy updated
      

      Once the lifecycle policy has been uploaded, objects will be deleted according to the policy set in place.

      Viewing a Bucket’s Lifecycle Policy

      To view a lifecycle policy after it has been uploaded to a bucket, use the getlifecycle command and provide the bucket name:

      s3cmd getlifecycle s3://lifecycle-policy-example
      

      You should see the contents of the XML file that was uploaded:

      <?xml version="1.0" ?>
      <LifecycleConfiguration xmlns="http://s3.amazonaws.com/doc/2006-03-01/">
        <Rule>
          <ID>delete-all</ID>
          <Prefix/>
          <Status>Enabled</Status>
          <Expiration>
            <Days>1</Days>
          </Expiration>
        </Rule>
      </LifecycleConfiguration>
      

      Deleting a Lifecycle Policy

      To delete a lifecycle policy that you’ve uploaded, effectively disabling it, use the dellifecycle command and provide the bucket name:

      s3cmd dellifecycle s3://lifecycle-policy-example
      

      You’ll see a confirmation that the lifecycle policy was deleted:

      s3://lifecycle-example/: Lifecycle Policy deleted
      

      Cyberduck

      Cyberduck allows less control over lifecycle polices than the s3cmd CLI. In particular, Cyberduck does not allow you to set a lifecycle policy that removes outdated versions of objects stored in buckets where versioning is enabled, nor does it allow you to delete multipart uploads. Cyberduck also limits the length of a lifecycle policy to commonly used time spans. Below you will learn how to set a lifecycle policy using Cyberduck.

      Note

      Enable a Lifecycle Policy

      1. Right click or control + click on the bucket for which you would like to set a lifecycle policy. This will bring up the bucket info menu.

      2. Click on the S3 tab to open the S3 bucket settings.

        Open the Cyberduck bucket settings menu.

      3. Click on the checkbox labeled Delete files and select a time interval from the drop-down menu below it.

        Click on the "S3" tab and then check the box labeled "Delete files."

      This will enable the lifecycle policy and the objects within the bucket will be deleted after the designated time.

      Disable a Lifecycle Policy

      To disable a lifecycle policy, uncheck the box entitled Delete Files that you checked in the previous section.

      Find answers, ask questions, and help others.

      This guide is published under a CC BY-ND 4.0 license.



      Source link