How can I introduce DB schema to Assistant by using code-interpreter files

API
, ,
1

I’m working on Assistant for XML report generation. This report is basicly SQL query. But I want to explain all my DB that contains hundreds of views and tables. And I thing files is one of the best way to do so. The main problem that assistant do not recognize sql files. And with txt files it doesn’t work good. How can I do this?

2

Welcome @volodymyr.hula

One workaround would be to convert the SQL schema into JSON, which is supported, and use that.

e.g SQL

CREATE TABLE RegularCustomer (
    CustomerID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL UNIQUE,
    PhoneNumber VARCHAR(15),
    StreetAddress VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    PostalCode VARCHAR(10),
    Country VARCHAR(50),
    DateOfBirth DATE,
    Gender CHAR(1),
    RegistrationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    LastPurchaseDate TIMESTAMP,
    TotalPurchases DECIMAL(10, 2) DEFAULT 0.00,
    Notes TEXT
);

-- Adding indexes for faster searches on common queries
CREATE INDEX idx_lastname ON RegularCustomer (LastName);
CREATE INDEX idx_email ON RegularCustomer (Email);
CREATE INDEX idx_city ON RegularCustomer (City);

JSON

{
  "CustomerDB": {
    "RegularCustomer": {
      "type": "object",
      "properties": {
        "CustomerID": {
          "type": "integer",
          "autoIncrement": true,
          "primaryKey": true
        },
        "FirstName": {
          "type": "string",
          "maxLength": 50,
          "notNull": true
        },
        "LastName": {
          "type": "string",
          "maxLength": 50,
          "notNull": true
        },
        "Email": {
          "type": "string",
          "maxLength": 100,
          "notNull": true,
          "unique": true
        },
        "PhoneNumber": {
          "type": "string",
          "maxLength": 15
        },
        "StreetAddress": {
          "type": "string",
          "maxLength": 100
        },
        "City": {
          "type": "string",
          "maxLength": 50
        },
        "State": {
          "type": "string",
          "maxLength": 50
        },
        "PostalCode": {
          "type": "string",
          "maxLength": 10
        },
        "Country": {
          "type": "string",
          "maxLength": 50
        },
        "DateOfBirth": {
          "type": "string",
          "format": "date"
        },
        "Gender": {
          "type": "string",
          "maxLength": 1
        },
        "RegistrationDate": {
          "type": "string",
          "format": "date-time",
          "default": "CURRENT_TIMESTAMP"
        },
        "LastPurchaseDate": {
          "type": "string",
          "format": "date-time"
        },
        "TotalPurchases": {
          "type": "number",
          "default": 0.00
        },
        "Notes": {
          "type": "string"
        }
      },
      "indexes": [
        {
          "name": "idx_lastname",
          "columns": ["LastName"]
        },
        {
          "name": "idx_email",
          "columns": ["Email"]
        },
        {
          "name": "idx_city",
          "columns": ["City"]
        }
      ]
    }
  }
}
2 Likes