orm2sql

Convert object relational mapping class to query

Since R2023b

collapse all in page

    Syntax

    query = orm2sql(conn,classInfo)

    Description

    query = orm2sql(conn,classInfo) returns the SQL CREATE statements 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.

    example

    Examples

    collapse all

    Open Live Script

    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

    collapse all

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

    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

    collapse all

    SQL CREATE statements representing the class, returned as a string array.

    Version History

    Introduced in R2023b

    expand all

    See Also

    | | |