Database: Stored Procedures, Triggers, Indexing Experiments

Homework content – stored procedures and triggers, indexing experiments

1. Stored procedure and trigger experiment

1. Please implement at least one stored procedure for each of the following operations on the database platform you choose according to your application scenario:

1) Single or multi-table query 2) Data insertion 3) Data deletion 4) Data modification

2. Call the implemented background stored procedure in the front-end program (C/S or B/S mode) through ODBC, OLEDB, JDBC or any other way.

3. In your case scenario, design and implement a trigger triggered by data insertion, data update, and data deletion (either pre-trigger or post-trigger), and test the execution effect of the trigger.

2. Index experiment

1) Combining with homework #3, write a simple data query for a table in your database (the query statement should include a single query condition involving the comparison of non-main attribute equivalence, set the non-main attribute as A, and combine the specific attributes with the business Background) and data insertion statement, the program should be able to record the time-consuming of each data read and write operation in the form of a file on the terminal or server.

2) No index test: Execute the query (the query condition does not include the main code, and there is no index established for attribute A), record the query time under different data scales,

3) Indexed test: Create an index for attribute A, use the same query as in 2), and record the query time under different data scales.

4) Analyze the experimental data, make charts, compare the change of query time with the increase of data volume under the condition of index and no index, and analyze the reasons leading to the experimental results.

• Stored procedure and trigger experiment

1. Please implement at least one stored procedure for each of the following operations on the database platform you choose according to your application scenario:

1) Single table or multi-table query

Query the data in the client table whose client_id is [init, termient]

CREATE DEFINER=`root`@`localhost` PROCEDURE `select_pro`(in init int, in termint int)
BEGIN
    SELECT * FROM client WHERE client_id>=init AND client_id<=termient;
END 

1. Data insertion

Insert data into the bank table, and check the primary key constraint before inserting. The bank_id of the data to be inserted already exists, return this data in the existing table, and return the message ‘The ID has exsited already’. The bank_id of the data to be inserted does not exist, insert new data, and return ‘Successfully insert’ message.

 CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_pro`(IN id INT, IN bank_name VARCHAR(255), IN bank_balance DOUBLE, OUT result VARCHAR(64))
BEGIN
    IF EXISTS(SELECT * FROM bank WHERE bank_id=id) THEN
        SET result='The ID has exsited already';
        SELECT * FROM bank WHERE bank_id=id;
    ELSE
        INSERT INTO bank VALUES(id, bank_name, bank_balance);
        SET result='Successfully insert';
    END IF;
END 

1. Data deletion

Delete the data in the bank table. If the data exists, delete it and return the ‘Successfully delete’ message. If the message does not exist, return ‘No this data’ message.

CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_pro`(IN id INT, OUT result VARCHAR(64))
BEGIN
    IF EXISTS(SELECT * FROM bank WHERE bank_id=id) THEN
        DELETE FROM administrator WHERE adm_id=id;
        SET result='Successfully delete';
    ELSE
        SET result='No this data';
    END IF;
END 

1. Data modification

Specify bank_id to modify the corresponding bank_balance data in the bank table. If the data exists, update the data and return the ‘Successfully update’ message. If the data does not exist, return ‘No this data’ message.

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_pro`(IN id INT, IN balance DOUBLE, OUT result VARCHAR(64))
BEGIN
    IF EXISTS(SELECT * from bank where bank_id=id) THEN
        UPDATE bank SET bank_balance=balance WHERE bank_id=id;
        SET result='Successfully update';
    ELSE
        SET result='No this data';
    END IF;
END 

2. Call the implemented background stored procedure in the front-end program (C/S or B/S mode) through ODBC, OLEDB, JDBC or any other way.

Use ODBC to implement the front-end implementation of the `update_pro` stored procedure for the above data modification through the python language.

import web
import pyodbc
  
class update_web:
    '''''
    open the update web
    '''
    def GET(self):
        return render. update()
      
class result_web:
    '''''
    open the result web
    '''
    def GET(self):
        return render.result(result)
  
class return:
    '''''
     receive message from web and return to homepage
    '''
    def POST(self):
        raise web.seeother('/')
      
class update():
    def POST(self):
        i = web.input() # Receive information sent by web
        try:
            cnxn =pyodbc.connect('DRIVER={' + driver + '};' +
                        f'SERVER={server};DATABASE={database};UID={username};PWD={password}') # connect to database ODBC
            cursor = cnxn. cursor()
            global result
            result = ''
            params = (i.bank_id, i.bank_balance, result)
            cursor.execute("{CALL update_pro(?,?,?)}", params)
            rows = cursor. fetchall()
            result = rows[0][0].decode()
            print(result)
            cnxn. close()
            raise web.seeother('/result')
        except:
            pass
  
  
if __name__ == "__main__":
    urls = (
    '/', 'update_web',
    '/update', 'update',
    '/result', 'result_web',
    '/returnback', 'returnback'
    )
      
    # Database ODBC connection information
    driver = 'MySQL ODBC 8.0 Unicode Driver' # pyodbc.drivers() View available drivers, select the driver that has configured the database before
    server = 'localhost'
    database = 'bank'
    username = 'root'
    password = '********'
       
    render = web.template.render('./template')
    app = web. application(urls, globals())
    app. run() 

3. In your case scenario, design and implement a trigger triggered by data insertion, data update, and data deletion (either pre-trigger or post-trigger), and test the execution effect of the trigger.

data insertion trigger

-- Newly inserted employee points are always 0
CREATE DEFINER=`root`@`localhost` TRIGGER insert_adm
BEFORE INSERT
on administrator
FOR EACH ROW
IF new. score<>0 THEN
    SET new. score=0;
END IF

-- When a new user deposits for the first time, the balance in the bank table will also increase, that is, the total funds of the bank will increase accordingly
CREATE TRIGGER insert_basic
AFTER INSERT
on basic_service
FOR EACH ROW
UPDATE bank SET bank_balance=bank_balance + new.balance where bank_id=new.bank_id; 

data update trigger

-- When the user deposits and withdraws money, the balance in the bank table also changes accordingly
CREATE TRIGGER update_basic
AFTER UPDATE
on basic_service
FOR EACH ROW
UPDATE bank SET bank_balance=bank_balance-old.balance + new.balance where bank_id=old.bank_id; 

Data Delete Trigger

-- When the user withdraws all deposits, the balance in the bank table also decreases
CREATE TRIGGER delete_basic
AFTER DELETE
on basic_service
FOR EACH ROW
UPDATE bank SET bank_balance=bank_balance-old.balance where bank_id=old.bank_id; 

• Index experiment

1) Combining with homework #3, write a simple data query for a table in your database (the query statement should include a single query condition involving the comparison of non-main attribute equivalence, set the non-main attribute as A, and combine the specific attributes with the business Background) and data insertion statement, the program should be able to record the time-consuming of each data read and write operation in the form of a file on the terminal or server.

2) No index test: Execute the query (the query condition does not include the main code, and there is no index established for attribute A), record the query time under different data scales,

3) Indexed test: Create an index for attribute A, use the same query as in 2), and record the query time under different data scales.

4) Analyze the experimental data, make charts, compare the change of query time with the increase of data volume under the condition of index and no index, and analyze the reasons leading to the experimental results.

import numpy as np
from random import randint
import pickle
import pymysql
import time
from tqdm import tqdm, trange
  
def insert_data(job_num_i, data_num_i,f): # Data insertion, set the number of categories and the total number of data in the index column
    start_time = time.perf_counter()
    sql = "INSERT INTO administrator VALUES(%s,'L','hycyzufs','2023-5-23',13022896926,990,%s)"
    sql_list = []
    for i in trange(data_num_i, desc=f'inserting-{job_num_i}-{data_num_i}', leave=False):
        a = randint(1, job_num_i)
        sql_list. append((i + 1, a))
    cursor.executemany(sql, sql_list)
    conn.commit()
    insert_time = time.perf_counter() - start_time
    f.write(f'Insert_time: {insert_time}\\
')
          
def delete_all(): # delete all data
    sql = 'DELETE FROM administrator where adm_id>0'
    cursor. execute(sql)
    conn.commit()
      
def index_on(): # open index
    sql = 'CREATE INDEX index_job ON administrator (job_id)'
    cursor. execute(sql)
    conn.commit()
      
def index_out(): # close index
    sql = 'DROP INDEX index_job ON administrator'
    cursor. execute(sql)
    conn.commit()
      
def test(test_num_i, data_num_i, sql, f): # Test search time, set the number of tests, and calculate the average search time
    all_time = []
    for _ in trange(test_num_i, desc=f'testing-{test_num_i}', leave=False):
        start_time = time.perf_counter()
        cursor. execute(sql)
        results = cursor. fetchall()
        pickle.dump(list(results), open('outcome', 'wb'))
        end_time = time.perf_counter()
        use_time = end_time - start_time
        all_time.append(use_time)
    avg_time = np. mean(all_time)
    f.write(f'{str(data_num_i)}: {str(avg_time)}\\
')
  
if __name__ == '__main__':
    job_num = [3, 5, 10] # list of index column categories
    data_num = [100, 1000, 10000, 100000, 1000000] # generate a list of data numbers
    test_num = 10 # list of test times, average after several tests
  
    conn = pymysql.connect(user='root', password='********', database='bank')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
      
    f = open('results.txt', 'w + ')
    sql = 'select * from administrator where job_id=1'
    job_num_i = job_num[0]
    data_num_i = data_num[0]
    for job_num_i in tqdm(job_num, desc=f'job_num-{job_num_i}'):
        f.write(f'-------job_num{job_num_i}---------\\
')
        for data_num_i in tqdm(data_num, desc=f'data_num-{data_num_i}'):
            delete_all()
            insert_data(job_num_i, data_num_i, f)
            test(test_num, data_num_i, sql, f)
            try:
                index_on()
                time. sleep(3)
            except:
                pass
            test(test_num, data_num_i, sql, f)
            try:
                index_out()
                time. sleep(3)
            except:
                pass
        f.write('\\
')
             
    delete_all()
    f. close()
    cursor. close()
    conn. close()

Test Results:

data_num job_num	100	1,000	10,000	100,000	1,000,000
no index 3	0.002488	0.008003	0.065155	0.792094	8.438734
have index 3	0.001907	0.007637	0.058433	0.077227	0.094179
no index 5	0.153701	0.029841	0.049829	0.934420	5.202662
have index 5	0.001961	0.004566	0.027477	0.0697084	0.563507
no index 10	0.329588	0.031040	0.039202	0.291128	2.984093
have index 10	0.001789	0.005060	0.003347	0.068878	0.086979

From the perspective of data volume, when there is less data, the query time with or without index is very fast, and with index is slightly faster than without index, but the gap between the two is not large. As the amount of data increases, the query time with the index will gradually show its advantages. In this experiment, the biggest difference can be achieved that the time spent with the index is 1/180 of the time spent without the index.

From the perspective of the number of categories in the index column, under different categories in this experiment, it is faster to have an index than to have no index. However, as the number of categories increases, the query time with indexes gradually increases, and the advantages decrease.

For the complete code, see: (1 message) Database: Stored Procedures, Triggers, Indexing Experimental Code Resources – CSDN Library