Hello ~ I'm Milo!

I'm building an open source interface testing platform from 0 to 1, and I'm also writing a complete set of corresponding tutorials. I hope you can support me.

Welcome to my official account test development pit, get the latest article tutorial!

review

In the previous section, we briefly introduced the next APScheduler. In this section, we will write relevant contents of the test plan.

Design test schedule

In fact, test plan can also be called test set. It is a set of use cases. And has corresponding characteristics:

• Timed execution

• Notification method after execution

• What is the passing rate lower than? Send mail / nail and other notices

• priority

• How many case s are covered

• case failure, retry interval, etc

  What may be strange here is that the test plan + test set are coupled together.

  Based on the above ideas, we can design the test plan table. Refer to the notes for the meaning of specific fields.

  (the design of the data table is not necessarily perfect, and it will be expanded according to business requirements in the future)

from sqlalchemy import Column, String, TEXT, UniqueConstraint, BOOLEAN, SMALLINT, INT
from sqlalchemy.dialects.mysql import TINYTEXT

from app.models.basic import PityBase


class PityTestPlan(PityBase):
    project_id = Column(INT, nullable=False)
    # Test plan execution environment; multiple choices are allowed
    env = Column(String(64), nullable=False)
    # Test plan name
    name = Column(String(32), nullable=False)
    # Test plan priority
    priority = Column(String(3), nullable=False)
    # cron expressions 
    cron = Column(String(12), nullable=False)
    # Use case list
    case_list = Column(TEXT, nullable=False)
    # Parallel / serial (sequential execution or not)
    ordered = Column(BOOLEAN, default=False)
    # If the pass rate is lower than this number, the notification will be sent automatically
    pass_rate = Column(SMALLINT, default=80)
    # Inform the user that there is only mailbox at present. The mobile phone number field may be improved in the subsequent user table for notification
    receiver = Column(TEXT)
    # Notification method 0: email 1: nailing 2: enterprise wechat 3: Flying book supports multiple choices
    msg_type = Column(TINYTEXT)
    # Retry interval for single case failure: 2 minutes by default
    retry_minutes = Column(SMALLINT, default=2)
    # Is the test plan in progress
    state = Column(SMALLINT, default=0, comment="0: Not started 1: In operation")

    __table_args__ = (
        UniqueConstraint('project_id', 'name', 'deleted_at'),
    )

    __tablename__ = "pity_test_plan"

    def __init__(self, project_id, env, case_list, name, priority, cron, ordered, pass_rate, receiver, msg_type,
                 retry_minutes, user, state=0, id=None):
        super().__init__(user, id)
        self.env = ",".join(map(str, env))
        self.case_list = ",".join(map(str, case_list))
        self.name = name
        self.project_id = project_id
        self.priority = priority
        self.ordered = ordered
        self.cron = cron
        self.pass_rate = pass_rate
        self.receiver = ",".join(map(str, receiver))
        self.msg_type = ",".join(map(str, msg_type))
        self.retry_minutes = retry_minutes
        self.state = state

It is worth noting here that the FORM data we defined include environment list env, receiver, and use case list case_list is an array. We need a wave of conversion.

Write CRUD method

• Extract asynchronous paging method and where method

Add a pagination method in the DatabaseHelper class, accept page and size, session and sql parameters, read the total number of sql matches first, if it is 0, return directly, otherwise obtain the data of the corresponding page through offset and limit.

The where method is used to improve our usual multi condition query, similar to this:

• Write new test plan method

You can see that after the transformation, we only need to call the where method and do not need to write if name= '': such a statement.

Because we do not allow test plans with the same name in the same project, the condition is that the project id+name cannot be repeated.

• Preparation of addition, modification and deletion methods

from sqlalchemy import select

from app.models import async_session, DatabaseHelper
from app.models.schema.test_plan import PityTestPlanForm
from app.models.test_plan import PityTestPlan
from app.utils.logger import Log


class PityTestPlanDao(object):
    log = Log("PityTestPlanDao")

    @staticmethod
    async def list_test_plan(page: int, size: int, project_id: int = None, name: str = ''):
        try:
            async with async_session() as session:
                conditions = [PityTestPlan.deleted_at == 0]
                DatabaseHelper.where(project_id, PityTestPlan.project_id == project_id, conditions) \
                    .where(name, PityTestPlan.name.like(f"%{name}%"), conditions)
                sql = select(PityTestPlan).where(conditions)
                result, total = await DatabaseHelper.pagination(page, size, session, sql)
                return result, total
        except Exception as e:
            PityTestPlanDao.log.error(f"Failed to get test plan: {str(e)}")
            raise Exception(f"Failed to get test plan: {str(e)}")

    @staticmethod
    async def insert_test_plan(plan: PityTestPlanForm, user: int):
        try:
            async with async_session() as session:
                async with session.begin():
                    query = await session.execute(select(PityTestPlan).where(PityTestPlan.project_id == plan.project_id,
                                                                             PityTestPlan.name == plan.name,
                                                                             PityTestPlan.deleted_at == 0))
                    if query.scalars().first() is not None:
                        raise Exception("Test plan already exists")
                    plan = PityTestPlan(**plan.dict(), user=user)
                    await session.add(plan)
        except Exception as e:
            PityTestPlanDao.log.error(f"Failed to add test plan: {str(e)}")
            raise Exception(f"Add failed: {str(e)}")

    @staticmethod
    async def update_test_plan(plan: PityTestPlanForm, user: int):
        try:
            async with async_session() as session:
                async with session.begin():
                    query = await session.execute(
                        select(PityTestPlan).where(PityTestPlan.id == plan.id, PityTestPlan.deleted_at == 0))
                    data = query.scalars().first()
                    if data is None:
                        raise Exception("Test plan does not exist")
                    DatabaseHelper.update_model(data, plan, user)
        except Exception as e:
            PityTestPlanDao.log.error(f"Failed to edit test plan: {str(e)}")
            raise Exception(f"Edit failed: {str(e)}")

    @staticmethod
    async def delete_test_plan(id: int, user: int):
        try:
            async with async_session() as session:
                async with session.begin():
                    query = await session.execute(
                        select(PityTestPlan).where(PityTestPlan.id == plan.id, PityTestPlan.deleted_at == 0))
                    data = query.scalars().first()
                    if data is None:
                        raise Exception("Test plan does not exist")
                    DatabaseHelper.delete_model(data, user)
        except Exception as e:
            PityTestPlanDao.log.error(f"Failed to delete test plan: {str(e)}")
            raise Exception(f"Deletion failed: {str(e)}")
            
    @staticmethod
    async def query_test_plan(id: int) -> PityTestPlan:
        try:
            async with async_session() as session:
                sql = select(PityTestPlan).where(PityTestPlan.deleted_at == 0, PityTestPlan.id == id)
                data = await session.execute(sql)
                return data.scalars().first()
        except Exception as e:
            PityTestPlanDao.log.error(f"Failed to get test plan: {str(e)}")
            raise Exception(f"Failed to get test plan: {str(e)}")

The basic idea is almost the same, old CRUD! I won't say much here. Those who don't understand the async content of sqlalchemy can go to the official website to see the demo.

This query method is used to query test plan data for scheduled tasks. Due to soft deletion, you often forget to bring deleted_at==0.

Write relevant interfaces (APP / routes / testcase / testplan. Py)

from fastapi import Depends

from app.dao.test_case.TestPlan import PityTestPlanDao
from app.handler.fatcory import PityResponse
from app.models.schema.test_plan import PityTestPlanForm
from app.routers import Permission
from app.routers.testcase.testcase import router
from config import Config


@router.get("/plan/list")
async def list_test_plan(page: int, size: int, project_id: int = None, name: str = "", user_info=Depends(Permission())):
    try:
        data, total = await PityTestPlanDao.list_test_plan(page, size, project_id, name)
        return PityResponse.success_with_size(PityResponse.model_to_list(data), total=total)
    except Exception as e:
        return PityResponse.failed(str(e))


@router.get("/plan/insert")
async def insert_test_plan(form: PityTestPlanForm, user_info=Depends(Permission(Config.MANAGER))):
    try:
        await PityTestPlanDao.insert_test_plan(form, user_info['id'])
        return PityResponse.success()
    except Exception as e:
        return PityResponse.failed(str(e))


@router.get("/plan/update")
async def update_test_plan(form: PityTestPlanForm, user_info=Depends(Permission(Config.MANAGER))):
    try:
        await PityTestPlanDao.update_test_plan(form, user_info['id'])
        return PityResponse.success()
    except Exception as e:
        return PityResponse.failed(str(e))


@router.get("/plan/delete")
async def delete_test_plan(id: int, user_info=Depends(Permission(Config.MANAGER))):
    try:
        await PityTestPlanDao.delete_test_plan(id, user_info['id'])
        return PityResponse.success()
    except Exception as e:
        return PityResponse.failed(str(e))

Here, we give the MANAGER the authority to add, delete and modify the test plan, but it's better to give it to the corresponding project MANAGER, but that will be a little more complicated. Let's be lazy for the time being and maybe improve it.

Today's content is shared here. The next section will introduce how to combine the test plan with the APScheduler, and then write the front page of the test plan to complete the scheduled task function.