orm2sql

Convert object relational mapping class to query

Since R2023b

Syntax

query = orm2sql(conn,classInfo)

Description

query = orm2sql(conn,classInfo) returns the SQL CREATE statement that corresponds to the class definition in classInfo based on the database connection object conn. You can use the orm2sql function to check whether the attributes set in the class definition have the intended effect.

Examples

Display Database Column Information for Mappable Class

This example depends on the Product class that maps to a database table. This class contains several properties that map to the database, as well as some methods that alter those properties.

classdef (TableName = "products") Product < database.orm.mixin.Mappable

    properties(PrimaryKey,ColumnName = "ProductNumber")
        ID int32
    end
    
    properties
        Name string
        Description string
        Quantity int32
    end

    properties(ColumnName = "UnitCost")
        CostPerItem double
    end

    properties(ColumnName = "Manufacturer")
        Supplier string
    end

    properties(ColumnType = "date")
        InventoryDate datetime
    end

    methods
        function obj = Product(id,name,description,supplier,cost,quantity,inventoryDate)
            if nargin ~= 0
                inputElements = numel(id);
                if numel(name) ~= inputElements || ...
                        numel(description) ~= inputElements || ...
                        numel(supplier) ~= inputElements || ...
                        numel(cost) ~= inputElements || ...
                        numel(quantity) ~= inputElements || ...
                        numel(inventoryDate) ~= inputElements
                    error('All inputs must have the same number of elements')
                end
    
                %Preallocate by creating the last object first
                obj(inputElements).ID = id(inputElements);
                obj(inputElements).Name = name(inputElements);
                obj(inputElements).Description = description(inputElements);
                obj(inputElements).Supplier = supplier(inputElements);
                obj(inputElements).CostPerItem = cost(inputElements);
                obj(inputElements).Quantity = quantity(inputElements);
                obj(inputElements).InventoryDate = inventoryDate(inputElements);

                for n = 1:inputElements-1
                    %Fill in the rest of the objects
                    obj(n).ID = id(n);
                    obj(n).Name = name(n);
                    obj(n).Description = description(n);
                    obj(n).Supplier = supplier(n);
                    obj(n).CostPerItem = cost(n);
                    obj(n).Quantity = quantity(n);
                    obj(n).InventoryDate = inventoryDate(n);
                end
            end
        end
        function obj = adjustPrice(obj,amount)
            arguments
                obj (1,1) Product
                amount (1,1) {mustBeNumeric}
            end
           obj.CostPerItem = obj.CostPerItem + amount; 
        end

        function obj = shipProduct(obj,amount)
            arguments
                obj (1,1) Product
                amount (1,1) {mustBePositive,mustBeInteger}
            end

            obj.Quantity = obj.Quantity - amount;
        end

        function obj = recieveProduct(obj,amount)
            arguments
                obj (1,1) Product
                amount (1,1) {mustBePositive,mustBeInteger}
            end

            obj.Quantity = obj.Quantity + amount;
            obj.InventoryDate = datetime('today');
        end

    end

end

First, create an sqlite database file that does not require a connection to a live database.

filename = "orm_demo.db";
if exist(filename,"file")
    conn = sqlite(filename);
else
    conn = sqlite(filename,"create");
end

Use the orm2sql function to display the database column information based on the class defined in Product.m.

orm2sql(conn,"Product")

ans = 
    "CREATE TABLE products
     (ProductNumber double, 
     Name text, 
     Description text, 
     Quantity double, 
     UnitCost double, 
     Manufacturer text, 
     InventoryDate date, 
     PRIMARY KEY (ProductNumber))"

close(conn)

Input Arguments

conn — Database connection

connection object

Database connection, specified as a connection object created by any of the following:

• database

• mysql

• postgresql

• sqlite

classInfo — Mapped class

character vector | string scalar | scalar matlab.metadata.Class object

Mapped class, specified as a character vector, string scalar, or scalar matlab.metadata.Class object containing the name or meta-information that identifies the class to generate the query from.

Output Arguments

query — SQL CREATE statement

string scalar

SQL CREATE statement representing the class, returned as a string scalar.

Version History

Introduced in R2023b