Additional Options for SQL schemas

For SQL Models you can use some additional schema parameters to make the schema more precise.

These will be mostly handed directly to SqlAlchemy or the DB and are used to:

•  Define a concrete subtype for the SQL Database. For example make a clearer definition of STRING as TEXT, UNICODE or ENUM. 
• Set additional Database conmstraints. 

We use the special schema keys "sql" and "sqltype" for that. PythonOnWheels will automatically exclude them from the cerberus schema and hand them to the DB.

This is how a schema might look like:

schema = {         
        'title': {'type': 'string', 'maxlength' : 35 }, 
        'task':  {'type': 'string', "sqltype" : "text" },  
        'likes': { 
            'type': 'integer', 
             "sql" : {          # sql attributes are handed raw to sqlalchemy Column 
                "primary_key"   : False, 
                "default"       : "123", 
                "unique"        : False,  
                "nullable"      : False  
            } 
        } 

Look at the "sqltype" definition of the task field ans the additional "sql" parameter for the "likes" field.

sqltype can be used for type STRING and can be TEXT, UNICODE

If you add an additional allowed constraint like this you can also choose the type ENUM

'text':  {'type': 'string', "allowed" : ["one", "two", "three"],  "sqltype" : "enum" }, 

Using SqlAlchemy Column definitions directly

# You can also go completely raw and use column definitions in sqlalchemy classic style 
    # which offer you all sqlalchemy options. But the above cerberus style is prefered 
    # 
    # title = Column(String(50)) 
    # text = Column(String) 

A complete example

# 
# Model Testmodel 
# 
from sqlalchemy import Column, Integer, String, Boolean, Sequence 
from sqlalchemy import BigInteger, Date, DateTime, Float, Numeric, Unicode, Text 
from testapp.powlib import relation 
from testapp.database.sqldblib import Base  
#@relation.has_many("<plural_other_models>") 
@relation.setup_sql_schema() 
class Testmodel(Base): 
     
    # 
    # cerberus style schema 
    schema = {         
        'title' :   { 'type' : 'string', 'maxlength' : 35 }, 
        'text'  :   { 'type' : 'string' }, 
        "votes" :   { "type" : "integer", "default" : 0 }   
    } 
    # define a custom tablename to link for this model: 
    _tablename =  
     
    # Omit using the pow schema extensions (id, created_at, last_updated) 
    _use_pow_schema_attrs = True 
     
    #  
    # you can also use special sqlalchemy attributes which are handed raw ro sqlalchemy.  
    #       => (see "sql" parameter) 
    # and for type string (default varchar) you can specify the concrete sql type  
    # currently text or unicode =>   (see "sqltype") 
    # like this: 
    # 
    # schema = {         
    #     'title': {'type': 'string', 'maxlength' : 35 }, 
    #     'text':  {'type': 'string', "sqltype" : "text" },  
    #     'likes': { 
    #         'type': 'integer', 
    #          "sql" : {          # sql attributes are handed raw to sqlalchemy Column 
    #             "primary_key"   : False, 
    #             "default"       : "123", 
    #             "unique"        : False,  
    #             "nullable"      : False  
    #         } 
    #     } 
    # } 
    # You can also go completely raw and use column definitions in sqlalchemy classic style 
    # which offer you all sqlalchemy options. But the above cerberus style is prefered 
    # 
    # title = Column(String(50)) 
    # text = Column(String) 
     
    # define class attributes/variables here that should be included in to_dict()  
    # conversion and also handed to the encoders but that are NOT part of the schema. 
    include_attributes=[] 
    # Add sqlalchemy table_args here.  Add "autoload" : True for reflection
    __table_args__ = { "extend_existing": True  } 
    # init 
    def __init__(self, **kwargs): 
        self.setup_instance_values() 
        self.init_on_load(**kwargs) 
    # 
    # your model's methods down here 
    #