Tablelist

Function: TableList Type: API Endpoint (POST)

Purpose:
The function retrieves a paginated list of tables and their additional fields from the customer database based on the provided customer_key. It also includes the count of 'In progress' uploads for each table.

URL:
POST /api/mdm/table_list/

Request Parameters:

• Customer-Key (Header, required):
  A base64 encoded string that contains lscode and firmcode, separated by a pipe (|).

Request Example:

GET /api/mdm/table_list/?page=1&limit=25 HTTP/1.1
Host: yourdomain.com
Content-Type: application/json
Customer_Key: "your customer key"

Response Example:

{
    "total_count": <total_count>,
    "data": [
        {
            "Table Name": "<Table Name>",
            "Description": "<Description>",
            "Active Status": <Active Status>,
            "Error Type": "<Error Type>",
            "Delete Option": "<Delete Option>",
            "In progress Uploads": <In progress Uploads>,
            "Record count": <Record count>
        },
        {
            "Table Name": "<Table Name>",
            "Description": "<Description>",
            "Active Status": <Active Status>,
            "Error Type": "<Error Type>",
            "Delete Option": "<Delete Option>",
            "In progress Uploads": <In progress Uploads>,
            "Record count": <Record count>
        },
    ......
]
}

Steps:

1. Log Creation:
   A unique log ID is generated using the current timestamp for logging purposes.

2. Customer Key Decoding:
   The customer_key is decoded from base64. It should contain lscode and firmcode separated by a pipe (|). If the key is empty, improperly formatted, or cannot be decoded, an error response is returned.

3. Database Connection:
   The decoded lscode and firmcode are used to establish a connection to the customer’s database using the custCursorConnection function. If the connection fails, an error is raised.

4. Query Execution:
   A SQL query is prepared and executed to fetch a paginated list of tables from the [dbo].[LS_Custom_Tables] table. Only active tables ([Active Flag] = 1) are retrieved.
   The query results include table details like Table Name, Delete Option, Error Type, and Description.

5. In Progress Upload Count:
   For each table, a query is made using SQLAlchemy to fetch the count of 'In progress' uploads from the LSMDMFileUploadQueue table based on the table name.

6. Response Construction:
   The retrieved data is formatted into a list of dictionaries containing basic and additional table details. The result is logged and returned as a JSON response.

Error Handling:

• Invalid Customer Key:
  If the customer_key is invalid or incorrectly formatted, an error message is returned.
• Database Errors:
  Any issues during query execution or connection are caught, rolled back, logged, and returned as a 500 error response.

Security Considerations:

• The customer_key is encoded to protect sensitive data like lscode and firmcode.

Error Response Example:

{
    "status": "failed",
    "message": "Invalid Customer Key"
}

{
    "status": "failed",
    "message": {
        "error_type": "OperationalError",
        "message": "Connection failed"
    }
}