Cascade Delete

    +

    Goal: This example illustrates how to leverage the Eventing Service to perform a cascade delete operation. When a user is deleted, Couchbase Functions provide a reliable method to delete all the associated documents related to the deleted user.

    Implementation: Create a JavaScript Function that contains an OnDelete handler. The handler listens to mutations or data-changes within a specified, users source bucket. When a user within the source bucket gets deleted, the handler executes a routine to remove the deleted user. When the delete operation is complete, all associated documents of the delete user also get removed.

    Preparations:

    For this example, three buckets 'users', 'transactions', and 'metadata', are required (note the metadata bucket for Eventing can be shared with other Eventing functions). Make all three buckets with minimum size of 100MB.

    For steps to create buckets, see Create a Bucket.

    The 'metadata' bucket is for the sole use of the Eventing system, do not add, modify, or delete documents from this bucket. In addition do not drop or flush or delete the bucket while you have any deployed Eventing functions.

    Procedure:

    1. From the Couchbase Web Console > Eventing page, click ADD FUNCTION, to add a new Function. The ADD FUNCTION dialog appears.

    2. In the ADD FUNCTION dialog, for individual Function elements provide the below information:

      • For the Source Bucket drop-down, select users.

      • For the Metadata Bucket drop-down, select metadata.

      • Enter delete_orphaned_txns as the name of the Function you are creating in the Function Name text-box.

      • [Optional Step] Enter text Delete Orphaned Transactions from the `transactions’ bucket when user_id is greater than or equal to 100, in the Description text-box.

      • For the Settings option, use the default values.

      • For the Bindings option, add one binding.

        • For the binding, select "bucket alias", specify src_user as the "alias name" of the bucket, and select users as the associated bucket, and select "read only". Note we will not be using this binding as we have no need of it because we will use N1QL.

      • After configuring your settings your screen should look like:

        cascadedel 01 settings
    3. After providing all the required information in the ADD FUNCTION dialog, click Next: Add Code. The delete_orphaned_txns dialog appears.

      • The delete_orphaned_txns dialog initially contains a placeholder code block. You will substitute your actual delete_orphaned_txns code in this block.

        cascadedel 02 editor with default
      • Copy the following Function, and paste it in the placeholder code block of delete_orphaned_txns dialog.

        function OnUpdate(doc, meta) {
            // debug only shows out mutations, we could delete this entire OnUpdate() function
            log('OnUpdate NOOP id: ' + meta.id + ' document:',doc);
        }
        function OnDelete(meta) {
            // Ignore all keys not matching "user_#", allows other types in the source bucket
            if ((meta.id).startsWith("user_") == false) return;
            // implement a contrived filter, keep all user transactions where the user_id > 100
            var id = meta.id;
            var numeric_id = parseInt(id.substring(5));
            if(!isNaN(numeric_id) && numeric_id >= 100) {
               try  {
                    DELETE FROM `transactions` WHERE user_id = $numeric_id;
                    log('OnDelete: removed orphaned transactions for:', id);
               } catch(e) {
                   log('OnDelete: Exception:', e)
               }
            } else {
                log('OnDelete: user_id < 100, kept orphaned transactions for:', id);
            }
        }

        After pasting, the screen appears as displayed below:

        cascadedel 03 editor with code
      • Click Save.

      • To return to the Eventing screen, click the '< back to Eventing' link (below the editor) or click Eventing tab.

    4. The OnDelete handler above is triggered for user delete transaction. The handler checks if the user_id is greater than or equal to 100 (the contrived filter never deletes the orphaned transactions form any user with an id of < 100). When this condition is fulfilled, then an N1QL query is triggered to delete all user related information. The handler also logs if the orphaned transactions were removed or kept to Function specific application log file.

    5. From the Eventing screen, click Deploy.

      • In the Confirm Deploy Function dialog, select Everything from the Feed boundary option.

      • Click Deploy Function.

    6. The Eventing function is deployed and starts running within a few seconds. From this point, the defined Function is executed on all existing documents and on subsequent mutations.

    7. From the Couchbase Web Console > Query page we will seed some data :

      • First create some actual users (5 total)

        INSERT INTO `users` (KEY,VALUE)
            VALUES ( "user_50",  { "user_id":  50, "name": "jeff shoemaker", "age": "77"} ),
            VALUES ( "user_100", { "user_id": 100, "name": "john doe",       "age": "30"} ),
            VALUES ( "user_101", { "user_id": 101, "name": "frank smith",    "age": "20"} ),
            VALUES ( "user_102", { "user_id": 102, "name": "jenny jones",    "age": "47"} ),
            VALUES ( "user_103", { "user_id": 103, "name": "jerry springer", "age": "28"} );
      • Next create some transactions (9 total) one or more for each of our users SELECT count() FROM transactions; SELECT count() FROM users;

        INSERT INTO `transactions` (KEY,VALUE)
            VALUES ( "txid_999",  { "user_id":  50, "item": "vitamins", "price": 2.99} ),
            VALUES ( "txid_1000", { "user_id": 100, "item": "milk", "price": 3.50} ),
            VALUES ( "txid_1001", { "user_id": 100, "item": "cheese", "price": 2.50} ),
            VALUES ( "txid_1002", { "user_id": 100, "item": "beer", "price": 7.89} ),
            VALUES ( "txid_1003", { "user_id": 100, "item": "pizza", "price": 12.53} ),
            VALUES ( "txid_1004", { "user_id": 101, "item": "lettuce", "price": 1.30} ),
            VALUES ( "txid_1005", { "user_id": 101, "item": "salad dressing", "price": 4.15} ),
            VALUES ( "txid_1006", { "user_id": 102, "item": "chicken", "price": 4.32} ),
            VALUES ( "txid_1007", { "user_id": 103, "item": "steak", "price": 6.53} );
      • Next create some indices so that we can perform some N1QL queries.

        CREATE PRIMARY INDEX `def_primary` ON `users`;
        CREATE PRIMARY INDEX `transactions` ON `transactions`;
      • Before deleting a user to test our Eventing Function we will use the NIQL Query Editor to inspect our data we just made in the bucket in users we should have five (5) users.

        SELECT * FROM `users` ORDER BY user_id;

        After executing the above N1QL statement you should see a table of 'user' data as follows:

        cascadedel 04 qryusers
      • Next use the NIQL Query Editor to inspect our data we just made in the bucket in users we should have nine (9) transactions.

        SELECT * FROM `transactions` ORDER BY user_id;

        After executing the above N1QL statement you should see a table of 'transaction' data as follows:

        cascadedel 05 qrytrans
      • Now NIQL Query Editor print out our user count and our transaction count (copy-and-paste both statements together, e.g. a compound query). This verifies that we have five (5) users and nine (9) transactions.

        SELECT count(*) FROM `users`; SELECT count(*) FROM `transactions`;
    8. Access the Couchbase Web Console > Buckets page and click the Documents link of the users bucket.

      • You should see five user records.

        cascadedel 06 usersdocs
      • Click on the "trash can" icon to delete the document with the id user_100

      • Click "Continue" in the warning dialog to verify you want to delete the document

      • The user_100 should no longer be listed

        cascadedel 07 del 100 usersdocs
    9. Access the Couchbase Web Console > Query and rerun your compound query as above.

      • You will now have four (4) users and five (5) transactions.

        SELECT count(*) FROM `users`; SELECT count(*) FROM `transactions`;

        After executing the above N1QL statement you should see a table showing counts as follows:

        cascadedel 08 reminingcounts
    10. Access the Couchbase Web Console > Eventing and click the Log link of the deployed delete_orphaned_txns Eventing function.

      • This Function Log dialog lists log statements in reverse order (newest items first).

      • The most recent or top line should be similar to the following:

        2020-01-17T11:33:38.457-08:00 [INFO] "OnDelete: removed orphaned transactions for:" "user_100"
      • The next four lines came from the OnUpdate handler of the delete_orphaned_txns Eventing function and show when you created your users. If you eliminate the function OnUpdate(doc, meta) {…​} handler in your Eventing function you would not get the below messages.

        2020-01-17T11:28:36.556-08:00 [INFO] "OnUpdate NOOP id: user_102 document:" {"age":"47","name":"jenny jones","user_id":102}
        2020-01-17T11:28:36.547-08:00 [INFO] "OnUpdate NOOP id: user_100 document:" {"age":"30","name":"john doe","user_id":100}
        2020-01-17T11:28:36.503-08:00 [INFO] "OnUpdate NOOP id: user_50 document:" {"age":"77","name":"jeff shoemaker","user_id":50}
        2020-01-17T11:28:36.503-08:00 [INFO] "OnUpdate NOOP id: user_103 document:" {"age":"28","name":"jerry springer","user_id":103}
        2020-01-17T11:28:36.499-08:00 [INFO] "OnUpdate NOOP id: user_101 document:" {"age":"20","name":"frank smith","user_id":101}
    11. Access the Couchbase Web Console > Query and run the following N1QL statements

      • Verify counts in both bucket 'users' and bucket 'transactions'

        SELECT count(*) FROM `users`; SELECT count(*) FROM `transactions`;
      • Delete all Users in bucket 'users'

        DELETE FROM `users`;
      • Verify counts again both bucket 'users' and bucket 'transactions' at this point you should only have one (1) transaction

        SELECT count(*) FROM `users`; SELECT count(*) FROM `transactions`;
      • Look at the one (1) remaining transaction it should be related to user_id 50

        SELECT * FROM `transactions`;

        After executing the above N1QL statement you should see only one 'transaction' item follows:

        cascadedel 09 kepttrans
    12. Access the Couchbase Web Console > Eventing and click the Log link of the deployed delete_orphaned_txns Eventing function.

      • The most recent four lines should be similar to the statements:

        Function Log - delete_orphaned_txns
        2020-01-17T11:43:51.485-08:00 [INFO] "OnDelete: user_id < 100, kept orphaned transactions for:" "user_50"
        2020-01-17T11:43:51.485-08:00 [INFO] "OnDelete: removed orphaned transactions for:" "user_103"
        2020-01-17T11:43:51.485-08:00 [INFO] "OnDelete: removed orphaned transactions for:" "user_102"
        2020-01-17T11:43:51.485-08:00 [INFO] "OnDelete: removed orphaned transactions for:" "user_101"
      • Note, the transaction(s) associated with user_id were kept as per our business logic.

    13. Cleanup, go to the Eventing portion of the UI and undeploy the Function delete_orphaned_txns, this will remove the 1024 documents from the 'metadata' bucket (in the Bucket view of the UI). Remember you may only delete the 'metadata' bucket if there are no deployed Eventing functions.