Main Content

orm2sql

Convert object relational mapping class to query

Since R2023b

    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

    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