Skip to content

Latest commit

 

History

History
199 lines (147 loc) · 9.55 KB

table-definitions.md

File metadata and controls

199 lines (147 loc) · 9.55 KB

Tables definition

language: English also available in: Spanish

Are defined tables, database views (which correspond to a database VIEW), or query views (which are simply a query that knows the application but didn't generate a VIEW). In the future there will be available to create tables which not have data originated by the database, for example a list of fields would have to be able to be seen in a table.

tableDef

property type default value use
name T table name in database. this name is the table id inside the system
title T name grid title
editable L false permissions
allow PO editable individual permissions object
primaryKey [T] [] PK name field list
foreignKeys [O] [] FK definition list
softForeignKeys [O] [] SFK definition list. It's used to specify FKs (one to one) not defined in database.
constraints [O] [] constraints list (except PK and FK)
sql O deduced SQL syntax for special cases
layout O {} (see Spanish)
vertical L false (see Spanish)
forInsertOnlyMode L false (see Spanish)
filterColumns [O] [] (see Spanish)
registerImports [O] (registerImportsDef) Object list. It is uset to configure how "others" fields are stored when any person imports a file (it works only if you set to true one field with "defaultForOtherFields" (see fieldDef))
sortColumns [O] [] default order
detailTables [O] [] master/detail subgrids based in other tables
list examples element format
foreignKeys {references:'ptable', fields:['atomic_number']}
softForeignKeys {references:'ptable', fields:['atomic_number']}
constraints {constraintType:'unique', fields:['atomic_number','order'], consName:'repeating order in atomic_number'}
filterColumns {column:'atomic_number', operator:'=', value:7}
sortColumns {column:'discovery_date', order:-1}
detailTables {table:'ptable', fields:['atomic_number'], abr:'A', refreshParent:true}
permissions table field allows:
insert x x (see Spanish)
update x x (see Spanish)
delete x (see Spanish)
select x x (see Spanish)
filter x (see Spanish)
import x (see Spanish)
export x (see Spanish)
orientation x (see Spanish)
sql usage
postCreateSqls (see Spanish)
isTable (see Spanish)
insertIfNotUpdate for excel import you can disable insertion of new rows present in excel (pk not in DB) by setting in false this property

fieldDef

property type default value use
name T name in database and field id
visible B true show/hide a field by default
typeName T data type
title T name title in the grid if you don't want to use name property default value
inTable L true determine if field belongs physically to the table and the dump.
sequence [O] (sequenceDef) determine if field will have auto-incremental value.
defaultForOtherFields B false determines if field (must to be defined as "text") is used to save a JSON with other fields when any person imports a file (it works only if you configures "registerImports" (see tableDef))

registerImportsDef

property type default value use
inTable T null table name used to save "other" fields. It's necessary to define if you want to save information (the table must exist, see integrating example)
fieldNames [O] (fieldNamesDef) Object with table fields configuration.

sequenceDef

A json containing the info for the generated sequence

property type default value use
name T null (REQUIRED) sequence name
firstValue Number 1 sequence first number
prefix T null sequence prefix

fieldNamesDef

Each property defines the field name of the table previously setted in "registerImports.inTable" that will be used to store information about "other" fields imported.

property type default value use y restrictions
tableName T 'table_name' Origin table of field (can't be null and must to be defined as text and PK in "registerImports.inTable")
fieldName T 'field' Fieldname (can't be null and must to be defined as text and PK in "registerImports.inTable")
fieldIndex T 'field_index' Field position in file (can't be null and must to be defined as integer in "registerImports.inTable")
originalFileName T null Filename to which belongs the field (can be null and must to be defined as text in "registerImports.inTable")
serverPath T null File path to whitch belongs the field (can be null and must to be defined as text in "registerImports.inTable")
lastUpload T null Timestamp of last import (can be null and must to be defined as timestamp in "registerImports.inTable")

Null fields can be undefined in "registerImports.inTable". Not Null fields are required and must respect restrictions. If you don't define optatives properties (Which can be null), information not will be registered although you defines them in "registerImporst.inTable".

Context

Integrating example:

module.exports = function(context){
    return context.be.tableDefAdapt({
        name:'isotopes',
        title:'stable isotopes',
        allow:{
            insert:context.user.rol==='boss',
            delete:context.user.rol==='boss',
            update:context.user.rol==='boss',
        },
        registerImports:{
            inTable:'other_fields', 
            fieldNames:{
                originalFileName:'original_filename',
                serverPath:'server_filepath',
                lastUpload:'last_upload',
            }
        },
        fields:[
            {name:'atomic_number', title:'A#', typeName:'integer' , width:100, nullable:false,      orderForInsertOnly:'1' },
            {name:'mass_number'              , typeName:'integer' , width:100,                      orderForInsertOnly:'2' },
            {name:'order'                    , typeName:'integer' , width:100,                      orderForInsertOnly:'4' },
            {name:'stable'                   , typeName:'boolean' , width:100,                                             },
            {name:'others'                   , typeName:'text'    , width:700, defaultForOtherFields: true                },
        ],
        filterColumns:[
            {column:'atomic_number', operator:'>', value:context.be.internalData.filterAtomicNumberForIsotopes}
        ],
        primaryKey:['atomic_number','mass_number'],
        constraints:[
            {constraintType:'unique', fields:['atomic_number','order'], consName:'repeating order in atomic_number'}
        ],
        foreignKeys:[
            {references:'ptable', fields:['atomic_number']}
        ]
    },context);
}

//other fields table definition

module.exports = function(context){
    var admin = context.user.rol==='boss';
    return context.be.tableDefAdapt({
        name:'other_fields',
        allow:{
            insert:true,
            update:true,
        },
        title:'information about other fields',
        editable:admin,
        fields:[
            {name:'table_name'          , typeName:'text'       , nullable:false  },
            {name:'field'               , typeName:'text'       , nullable:false  },
            {name:'field_index'         , typeName:'integer'    , nullable:false  },
            {name:'original_filename'   , typeName:'text'                         },
            {name:'server_filepath'     , typeName:'text'                         },
            {name:'last_upload'         , typeName:'timestamp'                    },
        ],
        primaryKey:['table_name', 'field'],
    }, context);
}