[ /database.html ]

database

basic concepts

structured data
transaction based
indexed by default
scriptable
seamless vfs-integration
tl;dr

... jump right over to the [ fileViewer ] and browse the test-database ...
... or skip to the more technical descriptions of [ structure ] and [ data types ] ...

explanation by example

Let's say - we founded a philosophers school and need a database for administration.

philosophy101.rooms.db

As philosphy isn't that popular, our school is small - we have 3 course rooms.
( Click to open/close)

So, lets see what we've got so far...

Our rooms.db has a nice and easy structure, having fields for :

ids
  • with an indication of its id-format [r00]

  • room names
  • with an indication of its <dataType> (a String variant)
  • and that those names need to be unique #

  • room descr
  • as <string>
  • the field is marked as acceptEmptyString ", wich means:
    • it is required to have a string value
    • this field will accept an empty String

  • room's capacity
  • as required <int>

  • room availability
  • as a optional * <bool>
  • with a default value of true

  • On opening those fields - (we'll go with rAvailable) -
    we can inspect the assigned Values - true and false.

    Again opening those dbValues, we find the assigned database Ids for each value.
    - 2 rooms seem to be available, one doesn't.

    Now - if You open id - You will find the database entries, sorted by their ids.

    Lets inspect the non-available room r03...

    We ignore those "underscory-thingies" for a moment...
    The dbEntry is a nicely formatted JS/JSON object.
    - We get clear information of the dbClass and id of the entry,
    - Each value can be accessed by its fieldName.
    - The structure of the JS/JSON object matches the db structure from above.

    Of course, those data were validated against that structure & its definitions before storage.
    Note: as [r03] has no description data, but rDescr is set to acceptEmptyString:true, it is valid anyway.
    Also, when You open rDescr - You will find a dbValue called _empty_, which contains the dbId of [r03].


    Now, to those extra fields :
    They are not part of the dbStructure - (but from the vfs) and are automatically added to each dbEntry.

    _dbLinks_
    what's linking to "here"
    (seems to be a class c007 taking place at this room)
    _lastChanged_
    (the rootuser changed this room on 07.07.2024)

    So, how do we place queries?

    Well, You already did, by clicking through the examples above...

    As the entire [ rooms.db ] is fully indexed and seamlessly integrated into the virtual filesystem,
    You can browse through its structure and subsets.
    On adding/updating new dbEntries, those dbValues from above rAvailable/true, rAvailable/false and their contained indices/idArrs are constantly updated.

    Although the above examples are pretty-printed as HTML and inserted into this page,
    they are purely path/URL based: [/!/db/philosophy101.rooms.db/rAvailable/true].

    You may also trigger them to respond with JS/JSON responses (native or decorated).
    [/!/db/philosophy101.rooms.db/rCapacity].
    If You like, try the following in Your Browser console (F12 or whatever applies to Your browser)
    or in the SessionObj [ console ] : [You may need to Login (as PublicUser?) first]

    vfs.fRead("!/db/philosophy101.rooms.db/rAvailable");
    vfs.fRead("!/db/philosophy101.rooms.db/rAvailable/true");
    vfs.fRead("!/db/philosophy101.rooms.db/rCapacity");
    vfs.fRead("!/db/philosophy101.rooms.db/rCapacity/25");

    They respond with an JS object, containing an values or ids array
    (No async/callback here, just for demonstration...)

    The main idea behind the [indexed-by-default] & [scriptable] approach is the following:
    - The majority of database queries need only a few - most of them only a single argument.
    - Those subsets are readily available at any time directly from the vfs or via URL.

    - We deal with ids/values arrays as long as possible !
    - They are uniform and easily sortable/comparable/combinable...
    - These pre-filtered idArrs may be stored into a variable for later reuse.

    - We can implement complex query logics by splitting them into parts and conditional function blocks.
    - We can skip parts or return early with a meaningful description if we did so.

    - As the last step - we do the lookup and convert those idArrs into their corresponding dbEntries,
       (with the possibility to get them in a specific format or fill their data into a prepared form)

    Lets do a practical example:

    For a new class, we need an available room for 24 students.
    For the sake of clarity - we'll do it "by hand".
    Anyway, the db interface will come with predefined functions for those cases...

    Query with 2 Arguments [ rCapacity ≥ 24, rAvailable : true ]
    Step 1 - Get rooms with the specified size:
    Get room sizes:
    vfs.fRead("!/db/philosophy101.rooms.db/rCapacity") values: [ 20, 28, 25 ]
    We keep only values ≥ 24 values: [ 28, 25 ]
    Get the room ids:
    vfs.fRead("!/db/philosophy101.rooms.db/rCapacity/28") ids: [ r02 ]
    vfs.fRead("!/db/philosophy101.rooms.db/rCapacity/25") ids: [ r03 ]
    Join those room ids: ids: [ r02, r03 ]
    Step 2 - Get available rooms:
    vfs.fRead("!/db/philosophy101.rooms.db/rAvailable/true") ids: [ r01, r02 ]
    Combination Step :
    We keep only values present in both part results
    intersection ([r02, r03], [r01, r02]) ids: [ r02 ]
    Get the final result :
    vfs.fRead("!/db/philosophy101.rooms.db/id/r02") r02

    philosophy101.classes.db

    The [ classes.db ] has a few new field and data types :

    cDescr
  • as a dbFieldArr, accepting/containing an Array of Strings
    (see dbEntry c003 or any other, how they look as pure data)
  • the field is marked as optional *
  • the field is marked as noIndex !

  • cStatus
  • the field is a dbObj, grouping further fields:
    • cActive as a required <bool> field
    • inActiveReason as an optional <string> field
    - all of them belonging together and considered as one entity
  • (see one of the dbEntries how they look as pure data)
    room & lecturer
  • both representing a link to another dbClass
  • dataType is <id> with an indication which dbClass is referenced
  • expecting a valid and existing dbId from the referenced dbClass
  • noIndex:
    Remember rDescr from the [ rooms.db ] ? Those lengthy cut-off Texts ?
    You won't really query for them - So - there is no point in holding indices for that field...
    - the values are descriptive informations only
    - it makes no sense to query those informations

    In [ classes.db ] we did the right thing and declared cDescr as noIndex right away.
    (... as we should have been done with rDescr - but then I wouldn't have an expamle for demonstration)
    - therefore - no indices for cDescr -> no dbValues

    There are other use-cases for noIndex :
    - sensitive, probably user-related informations, which shouldn't be searchable.
    SELECT * FROM USERS WHERE password=@password ? anyone ? Not really !!

    dbFieldArr :
    dbFieldArrs are regular database fields as all the others You've seen so far.
    Except one thing: They do not represent a single value, but an Array of values - all of the same type.

    So - for cDescr - there is not one lengthy descriptions string...
    Instead, an Array of strings is stored in the dbEntry (see dbEntry c003)

    As long as there are no minimum or maximum requirements, the values Array for the dbFieldArr
    can contain one, multiple, or no values at all: [ "descr"] OR [ "descr1", "descr2" ] OR [] ...
    - if the dbFieldArr is declared optional required:false - it will also accept no Array at all.
    - if the dbFiledArr is declared required:true - it expects at least one value in the valueArr.
    It is also possble to declare a dbFieldArr as :
    - required:2 - expecting minimum 2 values.
    - required:[0,2] - expecting maximum 2 values in the valArr.
    - required:[1,5] - expecting at least 1 and maximum 5 values in the valArr.
    - required:[2,2] - expecting exactly 2 values in the valArr.

    dbObj :
    dbObj is a pure structural element of the database, allowing us to group things together.
    - all of them belong together and are considered as one entity

    Technically, the dbObj for cStatus isn't strictly neccesarry in [ classes.db].
    As the db structure is short, the contained fields could also have been placed directly as top-level fields.
    But in the real world - where things tend to grow bigger and more complex - it's a good thing to have a way of grouping them together - in the same way you would do in any object oriented language.

    Note: There is no further logic between cActive and inActiveReason here - the database itself
    wouldn't prevent You from setting { cStatus:{ cActive:true, inActivReason:"because I can!"}, ...}
    (But that's a story for another time...)

    Anyway, this database classes are intended for Your demonstration- AND for my test-cases...
    and especially the [ structured-data ] approach needs some explanations and also some testing.

    philosophy101.personell.db

    Again, [ personell.db ] comes with a few new field and data types :

    pMail
  • as a dbFieldArr containing unique #<emails>
  • acceptEmptyStrings, minimum one value required "[1+]
    (see one of the dbEntries how they look as pure data)

  • roles
  • as an optional <select> dbFieldArr, meaning :
  • one can be student, lecturer, none or both.
    (We follow best philosophical traditions ...
    - as lecturer You still remain someone other's student)
  • default is set to student, so a person will never be none.

  • classes
  • as a dbObjArr containing :
    • clId - attended class by ID
    • clRole - as simple, required <select> (a person can't be both or none)
    • clCompleted - as a <triBool>
      • undefined - course assigned, but not completed yet
      • false - failed to finish the course
      • true - class finished successfully
  • all three (sub)-fields belong together as one entity
  • as classes is not only a dbObj, but a dbObjArr, it will
    contain multiple instances of those entities.
    (see one of the dbEntries how they look as pure data)

  • You may have noticed,
    • that [ classes.db/lecturer ] references forward to [ personell.db ]
    • and [ personell.db/classes/clId ] references back to [ classes.db ]...

    Isn't that considered bad practice? - Is it working? - And why?
    Well, Yes... and Yes..! (traditionally, it is considered bad practice & yes, it works!)
    Due to the [ transaction-based ] nature of the database's implementation it works,
    but also comes with some precautions, pros and cons (most of them at the same time...)

    Consider the following :
    Each creation/update of dbEntries to any dbClass of [philosophy101] is stored in
    one single transactionLog in chronological order.
    This gives us a few key advantages : (and things to keep in mind)
    • each transaction is atomic, traceable (including versioning) and reproducible.
    • on startup, those transactions are reproduced in the exact same order as they originally were sent.
    • we can rewind to any given state or time (or trace the previous versions of a specific entry).
    • if - at a later point - we extend the structure of a dbClass with additional fields (optional or defaultValue)
      - this won't invalidate older entries (or at least will give us a descriptive warning)
    Back to the intertwined (forward and back) references :
    In order to make the above example working, we need to follow a few steps:
    1. create the person [p0001] - class not assigned yet !
    2. create the class [c001] - the lecturer [p0001] can be assigned - {... lecturer:"p001", ...}
    3. update the person [p0001] - assign the class - {... classes:[ { clId:"c001", clRole:"lecturer" ...}, ...], ...}
    This all comes in a natural order:
    You can't create a class, whose lecturer doesn't exist yet,
    and You can't assign to that person a class, that doesn't exist...
    Therefore it needs to be broken down to atomic steps.

    In a real-world application I wouldn't recommend overusing this feature,
    an often there are way more elegant solutions to this, but again:
    - Your demo database is my test case(s)...

    final notices

    Now, as our introductory course [ database101 ] is over and
    our database for [ philosophy101 ] is complete, You may like to browse a bit around...

    Maybe You'll try to find out:

    To see them all in one place, You may hit over to the [ fileViewer ]


    structure

    dbClass

    In a classical, relational (SQL) database, this would be a TABLE.
    It holds the dbId, dbFields/dbFielsArrs, dbObjs/dbObjArrs and dbCategories
    building the entire structure.

    On creation/update of dbEntries, the input data is validated to be complete, conform to this structure, its inner data to be valid according to field definitions and dataTypes.

    definition :

    dbName
    (required) - String
    as a convention, ends with ".db"
    baseFolder
    (required) - nodeId
    iNodeId of the parentNode, the dbClass is attached to
    (set automatically - in most cases fId of !/db/)
    uId
    (optional) - SysId(user)
    specifies the user, the dbClass belongs to
    this setting will be inherited by all child elements
    (default is the root uId)
    gId
    (optional)- SysId(group)
    specifies the group, the dbClass belongs to
    this setting will be inherited by all child elements
    (default is the sudo gId)
    editorGroup
    (optional) - SysId(group)
    - if set, specifies the group, which can create/update
       dbEntries to the dbClass
    - this option makes sense, if You want to give a large group
       reading acces (through gId) and restrict
       writing acces for the db to a small group of specific users.

    queries :

    vfs.ls("!/db/dbClassName/")
    - get a listing of all top-level fields of the dbClass.

    vfs.fRead("!/db/dbClassName/")
    - get an JS/JSON Object, containing all Attributes of the dbClass
    - including dbClass.members - an Array of all top-level field names of the dbClass.

    TODO : - beforeValidation() & afterValidation() hook-in functions.

    TODO : - option hasLogin:true + basic verify(login)/setPwd/resetPwd functionality
    (see also dataType password)

    dbId

    Attached to the dbClass, it holds the [ dbEntries ] and validates/creates new database ids.

    To each created dbEntry a uniqe Id will be assigned from here.

    definition :

    dbParentNode
    (required) - nodeId (set automatically)
    iNodeId of the parentNode, (the dbClass)
    prefix
    (required) - String
    one or more charactes as id prefix
    padding
    (required) - Int
    length of id after the prefix

    Given { prefix:'p', padding:4 } the generated ids will be p0000, p0001...
    The entire dbId will be handled as a String, prefixes do not need to be unique.
    (use them as You see fit for Your purposes)

    queries :

    vfs.ls("!/db/dbClassName/id/")
    - get a listing of all stored dbEntries of the dbClass by Id.

    vfs.fRead("!/db/dbClassName/id/")
    - get an JS/JSON Object, containing all Attributes of the dbId
    - including dbId.ids an Array of all stored Ids

    vfs.fRead("!/db/dbClassName/id/dbId")
    - get the specified dbEntry (as JS/JSON Object).
    Note : specialFields are excluded - they are part of the dbEntry, but not part of the structure !

    dbField

    Attached to dbClass, dbObj or dbObjArr, it validates and holds [ dbValues ].

    Each stored dbEntry will contain a single value, validated by the dataType of the Field.
    e.g: [customers.db] { ... phone:<string>, ... }

    On creation/update of dbEntries, a corresponding dbValue member will updated/created/removed from/to the dbField, holding all dbIds having the same value.

    definition :

    dbParentNode
    (required) - nodeId (dbClass,dbObj,dbObjArr - set automatically)
    fName
    (required) - String - fieldName
    fType
    (required) - valid [ dataType ] String
    required
    (required) - true/false - (no default value - must be present!)
    acceptEmptyString
    (optional) - true/false - (false by default)
    - use with required:true
    - detailed description : below
    (applies to some String-based fields)

    default
    (optional) - default Value
    - use with required:false
    - MUST validate (minor exceptions - see int,float)

    unique
    (optional) - true/false
    if set, the input value / dbValue must be unused yet!
    - detailed description : below
    (forced on dataType name, default for email)
    (does not apply to non-required/empty string)

    noIndex
    (optional) - true/false
    if set, no dbValues will be created for the Field,
    and the Field cannot be queried.
    - used for sensitive data - or Fields where indexing is pointless.
    (forced on dataTypes password, json)
    (not on dataTypes bool, triBool, select)

    [additional]
    required & non-required options, depending on [ fType ]

    Note : combinations of required-default-acceptEmptyString-unique
    - some are rejected (unique:true & defaultValue), (required:true & defaultValue)
    - some have no effect (required:false & acceptEmptyString),
    - some are forced or set as default (depending on dataType),
    - others can produce unexpected side effects !!

    _empty_

    Most dbFields can have a special dbValue _empty_
    _empty_ is a regular dbValue, having a predefined name.
    Entries with no Value or an empty String will be assigned to _empty_
    and also can be queried from here.
    _empty_ will be present if :
    - required:false and NO default
    - required:true and acceptEmptyString:true

    acceptEmptyString :
    Differentiates between empty input Strings "" and
    input values being undefined/null (or not present at all).

    If required:false

  • no effect - both assigned to _empty_
  • If acceptEmptyString:false AND required:true

  • empty input Strings "" will be rejected !
  • input value undefined will be rejected !
  • If acceptEmptyString:true AND required:true

  • empty input Strings "" will be accepted and assigned to _empty_
  • input value undefined will be rejected !
  • Can be assigned to dataTypes: [ <string>, <email>, <path>, <select> <dateTime> ]

    This option effectively says: "I don't care if its empty, but - Give me a String!"
    [ "Bring us a Shrubbery!" - even a small one - but don't You dare saying "it!" or "Nu!(ll)" ]
    Again: forgive me my dark sense of humour - yet the Monty Python analogy nails it...

    required:true && acceptEmptyString:true <= vs => required:false && default:""

    both variants will ensure, that the validated dbEntry has at least
    an empty String assigned for that field.

    required:false && default:"" will accept input data, where
    the input data is "",undefined,null or not present at all.
    - it is more "forgiving" on creation
    - on dbUpdates:
    - - all cases are handled as no Data -> keep old value
    - - the field can NOT be reset !
    (Remember those annoying situations,
    where you have to input a space in the form to get old values deleted?)


    required:true && acceptEmptyString:true
    - is stricter on creation: it requires the sender/client to be explicit !
    - on dbUpdates:
    - - undefined means: no changes, keep old value
    - - "" means: reset to empty, discard the old value

    unique :

    When set, no two dbEntries can have the same value for that field -
    in consequence each dbVal maps to exactly ONE dbId/dbEntry !
    (forced on dataType name, default for email)

    The only exception: If required:false there ARE multiple entries
    having NO value - all of them assigned to _empty_.

    Combination of unique:true and a defaultValue will be rejected !

    queries :

    vfs.ls("!/db/dbClassName/dbFieldName/")
    - get a listing of all stored dbValues of the dbField.

    vfs.fRead("!/db/dbClassName/dbFieldName/")
    - get an JS/JSON Object, containing all Attributes of the dbField
    - including dbFieldArr.members an Array of all stored dbValue Names (as String).
    - including dbFieldArr.values an Array of all stored values (as <dataType>).

    vfs.fRead("!/db/dbClassName/dbFieldName/dbValue")
    - get an JS/JSON Object, containing all Attributes of the dbValue
    - including dbFieldArr.ids an Array of all dbIds/dbEntries, having the specified dbValue.

    vfs.fRead("!/db/dbClassName/dbFieldName/_empty_")
    - get an JS/JSON Object, containing all Attributes of the dbValue
    - including dbFieldArr.ids an Array of all dbIds/dbEntries, having no data / empty String.
    e.g: get all dbIds from [customers.db] having no phone number.

    TODO : db: dbField._range_ - an additional dbValue/methodsCollection,
    where dbIds can be queried, having a value within a specified range.
    (applies to numeric based vals or logical comparable/sortable vals)
    e.g: get all dbIds from [shopItems.db] between 0.0 and 15.00 €.
    e.g: get all dbIds from [orders.db] placed between last week and today.
    e.g: get all dbIds from [orders.db] with zipCode between "74000" and "78999".
    Note : already working with helper function :
       vals = fRead(dbFielName)->sort()->splice();
    forEach(val){ idArr.merge( fRead(dbFieldName/val) ); }

    dbFieldArr

    Attached to dbClass, dbObj or dbObjArr, it validates and holds [ dbValues ].

    Each stored dbEntry will contain a valArr of one or multiple values,
    validated by the dataType of the Field.
    e.g: [customers.db] { ... phone:[ <string>, <string>, ... ] ... } the phone array hold one or multiple phone numbers for a customer.
    On creation/update of dbEntries, a corresponding dbValue member will updated/created/removed from/to the dbFieldArr, holding all dbIds having the same values. (for each item in the valArr)
    In a classical (SQL) database You may have a fixed set of fields in the TABLE
    or would have a separate TABLE to create Array-like relations.

    definition :

    dbParentNode
    (required) - nodeId (dbClass,dbObj,dbObjArr - set automatically)
    fName
    (required) - String - fieldArrName
    fType
    (required) - valid [ dataType ] String
    required
    (required) - true/false/<Int>/[<Int>,<Int>] - (must be present!)
    if required:true expects at least one value in the valArr
    if required is numeric, expects valArr to be at least Int long.
    if required is false or 0, valArr may be empty or not present at all.
    if required:[min,max] it expects the length of the vallArr to fit.
    acceptEmptyString
    (optional) - true/false - (false by default)
    - use with required:true,required:>0
    - detailed description : see dbField
    (applies to some String-based fields)

    default
    (optional) - default Value
    - use with required:false,required:0
    - MUST validate (minor exceptions - see int,float)

    unique
    (optional) - true/false
    if set, the input value / dbValue must be unused yet!
    - detailed description : see dbField
    (default for email)
    (does not apply to non-required/empty string)

    noIndex
    (optional) - true/false
    if set, no dbValues will be created for the Field,
    and the Field cannot be queried.
    used for sensitive data - or FieldArrs where indexing is pointless.
    (not on dataTypes bool, triBool, select)
    [additional]
    required & non-required options, depending on [ fType ]

    Note : combinations of required-default-acceptEmptyString-unique
    - some are rejected (unique:true & defaultValue), (required:true & defaultValue)
    - some have no effect (required:false & acceptEmptyString),
    - some are forced or set as default (depending on dataType),
    - others can produce unexpected side effects !!

    _empty_ & _count_

    dbFieldArrs CAN have a special dbValue _empty_
    Entries, providing a value Array containing an empty String (if allowed)
    will be assigned to _empty_ and can be queried from there.

    In contrast - Entries, providing an empty Array (or no valArr at all)
    will be assigned to _count_.0
    _count_ is an Attribute of the dbFieldArr, where dbIds/dbEntries can be queried,
    having a valArr of a specific lenght.

    acceptEmptyString :
    unique :
    See detailed descriptions of dbField

    queries :

    vfs.ls("!/db/dbClassName/dbFieldArrName/")
    - get a listing of all stored dbValues of the dbFieldArr.

    vfs.fRead("!/db/dbClassName/dbFieldArrName/")
    - get an JS/JSON Object, containing all Attributes of the dbFieldArr
    - including dbFieldArr.members an Array of all stored dbValue Names (as String).
    - including dbFieldArr.values an Array of all stored values (as <dataType>).

    vfs.fRead("!/db/dbClassName/dbFieldArrName/dbValue")
    vfs.fRead("!/db/dbClassName/dbFieldArrName/_empty_")
    - get an JS/JSON Object, containing all Attributes of the dbValue
    - including dbFieldArr.ids an Array of all dbIds/dbEntries, having the specified dbValue.
    - including dbFieldArr._count_ an Attribute,
       where dbIds can be queried, having a valArr of a specific lenght.
       e.g: get all dbIds from [customers.db] having a specific number of phone numbers (0 or 3)

    TODO : db: dbFieldArr._range_
    - dbFieldArr Attribute, where dbIds can be queried, having a value within a specified range.
    (applies to numeric based vals or logical comparable/sortable vals)

    dbObj

    dbObjs are a structural element, attached to the dbClass, or another dbObj/dbObjArr.

    Each stored dbEntry will contain a single object containig further values.
    e.g: [customers.db] { ... address:{ street:<string>, nr:<int>, zipCode:<string>, city:<string> }, ... }
    the adress object holds a defined set of fields, which all belong together as one entity.


    On creation/update of dbEntries, the presence of the object itself and its child elements
    will be validated depending on definitions.
    In a classical relational (SQL) database all of the above obj fields would be "flattened out" in the main TABLE.
    Alternatively (and most likely) there would be a separate TABLE for the addresses, and the main TABLE would relate to IDs of that table or vice versa.

    definition :

    dbParentNode
    (required) - nodeId (dbClass,dbObj,dbObjArr - set automatically)
    oName
    (required) - String - objectName
    required
    (required) - true/false - must be present!
    default
    (optional) - default Value (if required:false)

    queries :

    vfs.ls("!/db/dbClassName/dbObjName/")
    - get a listing of all child elems of the dbObj (dbFields, dbObjs, dbObjArrs).

    vfs.fRead("!/db/dbClassName/dbObjName/")
    - get an JS/JSON Object, containing all Attributes of the dbObj
    - including dbObj.members an Array of all child Names.

    TODO : db: dbObj._empty_ if required:false and no default
    - Attribute, where dbEntries can be queried, which have no data for the dbObj.
    (means: the entire obj is not present in the data - implementation in progress)
    e.g: get all dbIds from [customers.db] having no shippingAddress.

    dbObjArr

    dbObjArrs are a structural element, attached to the dbClass, or another dbObj/dbObjArr.

    Each stored dbEntry will contain an Array of one or multiple objects containig further values.
    e.g: [suppliers.db] { ... contacts:[ { departement:<string>, person:<string>, phone:<string>, email:<string> }, ... ], ... }
    A supplier may have multipe contacts (order, accounting, shipping departments), all of them following the same structure. Each contacts object itself holds a defined set of fields, which belong together as one entity.


    On creation/update of dbEntries, the presence of the objArr itself and its child elements
    will be validated depending on definitions.
    In a classical relational (SQL) database there would be a separate TABLE for the contacts, and the main TABLE would relate to IDs of that table or vice versa. Alternatively those fields may be "flattened out" in the main TABLE.

    definition :

    dbParentNode
    (required) - nodeId (dbClass,dbObj,dbObjArr - set automatically)
    oName
    (required) - String - objectName
    required
    (required) - true/false/<Int>/[<Int>,<Int>] - (must be present!)
    if required:true expects at least one value in the objArr
    if required is numeric, expects objArr to be at least Int long.
    if required is false or 0, objArr may be empty or not present at all.
    if required:[min,max] it expects the length of the objArr to fit.
    default
    (optional) - default Value (if required:false)
    _count_

    dbObjArrs have a special Attribute _count_,
    where dbIds/dbEntries can be queried, having an objArr of a specific lenght.

    queries :

    vfs.ls("!/db/dbClassName/dbObjArrName/")
    - get a listing of all child elems of the dbObjArr (dbFields, dbObjs, dbObjArrs).

    vfs.fRead("!/db/dbClassName/dbObjArrName/")
    - get an JS/JSON Object, containing all Attributes of the dbObjArr
    - including dbObjArr.members an Array of all child Names.
    - including dbObjArr._count_ an Attribute,
       where dbEntries having an objArr of a specific lenght can be queried.
       e.g: get all dbIds from [suppliers.db] having only 1 contact.

    dbCategory

    dbCategories are a structural element, attached directly to the dbClass or a parent dbCategory.
    dbCategories provide an effective filtering mechanism for dbEntries/dbIds in a folder-tree-like structure.

    Each stored dbEntry will have an Array-like structure containing one or multiple category pathes.
    e.g: [shopItems.db] { ... category:[ "accessories", "pointing devices", "cordless", "bluetooth" ], ... }
    for assignig/filtering bluetooth mice to/from the shopItem categories.


    On creation/update of dbEntries, the category data will be validated depending on definitions and sub-categories, the dbId of the dbEntry will be assigned to the endpoint of the category path(es).

    definition :

    dbParentNode
    (required) - nodeId (dbClass,dbCategory - set automatically)
    cName
    (required) - String - categoryName
    required
    (required) - true/false - must be present!
    - on the top-level dbCategory: works as expected.
    - on the sub-levels: required:false (required ATM - see below).
    default
    (optional) - default Value (if required:false)
    - on the top-level dbCategory only!
    (needs implementation)

    queries :

    vfs.ls("!/db/dbClassName/dbCategoryName/")
    - get a listing of all sub-categories.

    vfs.fRead("!/db/dbClassName/dbCategoryName/")
    - get an JS/JSON Object, containing all Attributes of the dbCategory
    - including dbCategory.members an Array of all child Names.
    - including dbCategory.ids an Array of all dbIds/dbEntries, assigned to the dbCategory.

    implementation :

    Status: working
    - dbCategory currently filters "endpoint-only" !! (by design)
    - TODO : filterPartially or endPointsOnly:false allows a filter mechanism
        which narrows down the results as the dbCategory tree is travelled. (favourable for ar shopItem list)
    - TODO : dbCategoryName/_empty_ on top-level dbCategory (if required:false)
    - TODO : required:true on sub-level dbCategories:
        if set to true, the sub-level is declared as "transitional" - which means this can't be an endpoint in the dbEntry

    dbValue

    dbValues are an indexing element, attached to a dbField or dbFieldArr.
    They represent an index for a specific value of a database field,
    holding the dbIds of all entries, having that specific value.
    e.g: !/db/shopItems.db/available/true contains the Ids of all available shopItems.

    Note : the Name of the dbValue is a String representation of the real values.
    - depending on <dataType> or special cases the dbValue Name may differ...
    (see _empty_ (below) & dataTypes string,path )

    definition : none (created automatically)

    On creation of new dbEntries:
    the parent dbField/dbFieldArr will check its existing dbValues
    for a match - if not existing, it creates a new dbVal -
    and assign the dbId of that dbEntry to the dbValue.

    On updating dbEntries:
    the parent dbField/dbFieldArr will
    - remove the dbId from the "old" dbVal
    - assign it to the "new" dbVal

    Once a dbValue has no more dbId entries, it will be removed from the parent field
    (except dataTypes bool|triBool|select - see there)

    queries :

    vfs.fRead("!/db/dbClassName/dbFieldName/dbValue")
    - get an JS/JSON Object, containing all Attributes of the dbValue
    - including dbFieldArr.ids an Array of all dbIds/dbEntries, having the specified dbValue.
    e.g: get all dbIds from [customers.db] having a specific zip-code)

    vfs.fRead("!/db/dbClassName/dbFieldName/_empty_")
    - get an JS/JSON Object, containing all Attributes of the dbValue
    - including dbFieldArr.ids an Array of all dbIds/dbEntries, having no data / empty String.
    e.g: get all dbIds from [customers.db] having no phone number assigned. (phone is a dbField)

    _empty_ , _count_, _range_

    _empty_ is present if:
    - required:false and NO default
    - required:true and acceptEmptyString:true

    Note : _empty_ is a regular dbValue (except its predefined naming)
    _count_ and _range_ are Attributes of the parent dbField/dbFieldArr

    db: dbFieldArr._count_, dbObjArr._count_
    - dbFieldArr/dbObjArr Attribute, where dbIds can be queried,
       having a valArr/objArr of a specific lenght.
    e.g: get all dbIds from [customers.db] having a specific number of phone numbers (0 or 3) (if phone is a dbFieldArr)

    TODO : db: dbField._range_
    - dbValue/methodsCollection, where dbIds can be queried, having a value within a specified range.
    (applies to numeric based vals or logical comparable/sortable vals)
    e.g: get all dbIds from [shopItems.db] between 0.0 and 15.00 €.
    e.g: get all dbIds from [orders.db] placed between last week and today.
    e.g: get all dbIds from [orders.db] with zipCode between "74000" and "78999".
    Note : already working with helper function :
       vals = fRead(dbFielName)->sort()->splice();
    forEach(val){ idArr.merge( fRead(dbFieldName/val) ); }

    data processing :

    First, retrieving one or more Arrays of Ids from specific dbField/dbValue queries.
    (All from the same dbClass...)

    Second, those result Arrays can be combined/filtered (join/merge/diff/exclude/...)
    (Also resultArrs from dbCategory queries - These steps depend on the query logic of Your application)
    ... to get the final Set of Ids. idArr
    (Note : up until this point we dealt with Arrays of dbId's only !)

    Finally, to get the "real" dbEntries -
    let result = {};
    for (let i=0; i<idArr.length; i++) {
        let dbId = idArr[i];
        result[i] = vfs.fRead("!/db/dbClassName/id/dbId");
    };
    or
    let resultArr = []
    ... resultArr.push( fRead(...) )


    See also [ example ]

    dbEntry

    dbEntries are attached to the dbId and accessible by their dbId as:
    vfs.fRead("!/db/dbClassName/id/dbId").

    Inside Interpreter/ScriptEngine, a returned dbEntry will be a native JS-Value.
    On output, (stored/returned/printed) as JS/JSON string.

    On creation, input data are validated against the struct, definitions and dataTypes of their dbClass.
    Updating existing dbEntries accepts partial input data ("only the changing parts"),
    on revalidation those input data will be filled in from the existing data.
    (still some edge cases on "reset" to undefined or to empty ObjArrs)

    dbEntries are traceable and versioned (no user interface for versioning yet)
    - see also [ special Fields ]

    queries :

    vfs.fRead("!/db/dbClassName/id/dbId")
    - get the specified dbEntry (as JS/JSON Object).
    Note : specialFields are excluded - they are part of the dbEntry, but not part of the structure !

    dbAttribute

    A dbAttribute is not a real element of the database structure.
    It is the response, the database elements return from vfs.fRead() or the db interface.

    Although the returned Attributes (as JS/JSON objects) may differ
    depending on what element type You queried, they all follow a common schema :
    They contain : name, dbClass, nodeType & the options set for that element.
    Additionally, they may contain a members, values or ids Array.


    structural db elements : dbClass, dbField, dbFieldArr, dbObj, dbObjArr

    vfs.fRead("!/db/dbClassName")
    vfs.fRead("!/db/dbClassName/dbFieldName/")
    vfs.fRead("!/db/dbClassName/dbFieldArrName/")
    vfs.fRead("!/db/dbClassName/dbObjName/")
    vfs.fRead("!/db/dbClassName/dbObjArrName/")

    e.g. - our test database - vfs.fRead("!/db/philosophy101.personell.db/classes/clCompleted"); will return:

    { name:clCompleted, dbClass:philosophy101.personell.db, nodeType:dbField, required:false, unique:false, noIndex:false, dataType:triBool, members: , values: , }

    As clCompleted is a dbField it has its dataType, members & values. (also true for dbFieldArrs).
    In Contrast - dbClasses, dbObjs & dbObjArrs will have only a members Array,
    but no dataType or values.
    dbFieldArrs & dbObjArrs will also contain the _count_ attribute.


    indexing db elements : dbValue

    When querying dbValues, vfs.fRead("!/db/dbClassName/dbFieldName/dbValue")
    the dbAttributes are are a bit shorter and they will contain the Array of referenced ids.

    e.g. - our test database - vfs.fRead("!/db/philosophy101.personell.db/classes/clCompleted/true"); will return:

    { name:true, dbClass:philosophy101.personell.db, nodeType:dbValue, value:true, immutable:true, ids: , }

    dbCategories :

    vfs.fRead("!/db/dbClassName/dbCategoryName/")
    Due to the filtering nature of dbCategories, they are a mix of structural AND indexing element.
    They will have members & ids Arrays in their dbAttributes.

    dbIds :

    vfs.fRead("!/db/dbClassName/id/")
    dbIds are a special indexing (and at the same time structural) element.
    They will have a ids Array in their dbAttributes.


    data types

    bool, triBool

    Boolean Values

    bool handles true and falsy values (false,undefined) as two states.
    triBool handles true, false and undefined as three different states.
    e.g: [customers.db] { ... newsletterSubscribed:<triBool>, ... }
    - true: customer wants the newsletter
    - false: customer doesn't want to be bothered
    - undefined : customer didn't made a decision yet

    additional definition : (none)

    It forces the following option : noIndex:false

    On db startup, the dbValues true false (and undefined) will be created
    and immutably added to the dbField/dbFieldArr.
    - therefore queries to a bool/triBool field may return an empty idArr !

    For non-required bool - default:false will be set internally to chatch all falsy values.

    Note : there will never be a dbVal _empty_ for bool, triBool fields !

    TODO : Due to JSON conversion (stringify)
    - the values Array of the Attributes Object may contain null instead of undefined for triBools.

    int, float

    Numeric Values

    additional definition :

    padding
    (optional) - float only ! - Int
    - input values will be truncated to the given padding.
    minVal
    (optional) - Int/Float (according to dataType)
    - minimum for input values (inclusive)
    maxVal
    (optional) - Int/Float (according to dataType)
    - maximum for input values (inclusive)

    minVal can be set alone (e.g to ensure only positive numbers)
    maxVal requires minVal to be set !! (also, maxVal >= minVal is checked on startup)

    default is not checked on startup to be inside the min/maxVal range !! (by design)

    Given the following scenario:
    - fType:int
    - required:false
    - minVal:0
    - default:-1

    On validation - input values is:
    - ≥ 0 => valid
    - < 0 => invalid !!
    - undefined => switch to default => -1

    This effectively means: Valid inputs will be accepted - Invalid input values will be rejected,
    but as the field is declared as non-required - null/undefined will be accepted and mapped to -1.

    This way, entries with no value can be queried via dbVal -1
    (use this variant, if you need the field to contain an int in any case and cannot accept undefined/null values)

    Note : depending on options, dbVals _empty_ AND 0 / 0.0 may be present.

    string

    String Values

    Note : Strings are expected to be UTF-8 !!

    additional definition :

    acceptEmptyString
    (optional) - true/false - (false by default)
    - use with required:true
    - see detailed description at dbField

    maxLen
    (optional) - Int - (unlimited by default)
    - maximum length for input values (inclusive)

    strict
    (optional) - true/false/undefined - (undefined by default)
    - see validation below
    validation :
    Unsanitized input Strings may contain :
  • control characters, Tabs, NewLines
  • characters reserved for pathes/URLs ( : / # & % ? )
  • characters vulnerable for json injection ( : " ' ` )

  • Approaches :
  • strict reject everything
  • loose permanently replace some (with an equivalent utf-variant), reject the rest.
  • permissive accept input value as is, but replace invalid characters for dbValue names / vfs.
        - this means : the queryable name of the dbValue is different from the real data in the dbEntry !

  • In General :
    While SQL is vulnerable for query injections, NAME='Robert'); DROP TABLE Students;--'

    - this database MAY be vulnerable for JSON injections... name:'Robert', injectedVal:'blub'
    (good thing is: inputs may pass/'survive' a first parsing, but will be discarded or rejected at (re)validation)

    - this database IS vulnerable for path/URL injections...
    (good thing is: path-injected string inputs will fail at vfs.treeWalk gracefully)
    Generic strings can be validated in three variants:
    strict:true
  • reject ALL invalid characters

  • strict:undefined
    (default)
  • reject <control> characters, Tabs, NewLines
  • normalize quotation to all-single-quoted
  • replace [ / \ # & % ? : ] => utf-variants

  • strict:false
  • reject <control> characters
  • normalize quotation to all-single-quoted
  • accepted / dbVal name replacement : Tab => (U+FE4D)
  • accepted / dbVal name replacement : NewLine => (U+2016)
  • accepted / dbVal name replacement : [ / \ # & % ? : ] => ...
  • will set noIndex:true (Can be overridden)

  • For the specialized String-based dataTypes below, it is easier to catch input errors
    as they are strict by default and the exceptions are predictable.

    Considerations :
    Although strict:undefined as default is a fair compromise -
    setting strict to true at first and loosen when needed, is the safest approach.

    strict:undefined effectively means : "This is pure display text - modify if needed"...
    { address:"Gartenstrasse 17/1", ...} => { address:"Gartenstrasse 17⧸1", ...}
    [ Who is #1 / Top 1% ? ] => [ Who is ﹟1 ⧸ Top 1﹪ ﹖ ]

    strict:false effectively means : "The input strings needs to be stored exactly as given - even
    if querying will need an extra step from the client side for conversion ..."

    Free-Text, Comments, Descriptions
    Lengthy user input may be marked as noIndex anyway (no need to be vfs/path/URL-compliant or queryable)
    or (better) written/uploaded to a text file and referenced by path/URL.
    (For images & binary data You'd do it in any case)

    Additional, specialized dataTypes will follow as they are needed:
    - dataType <URL> - remove "https://",port & query/fragment parts, replace "/" (for dbVal names)

    TODO : multiLine, URLencode(d), beforeValidation()

    Personal Note :
    - sanitizing string inputs is always pita, no matter which language - and sometimes requires compromises...
    - not happy with the replacements, as it needs an extra step & helper function for the conversions...
    - on the other hand - it works and gives the required safety for json-integrity and vfs-integration...
    - replacements list is short, predictable (and reversible)
    - normalize quotation to all-single-quoted eliminates the vast majority of json-injection-trickery
    - the db-interface can/will handle the conversions by itself...

    id

    String-based Value

    id is a string-based dataType referencing a dbId/dbEntry of another dbClass.

    additional definition :

    idLink
    (required) - String - Name of the referenced dbClass

    On validation, the input is validated against the referenced dbClass
    to be valid and an existing dbId/dbEntry.

    On startup, the idLink is checked to be valid and present (!!order in db-definition!!)

    Input Validation: strict
    - max length: 255
    - invalid: <control> characters, Tab, NewLine
    - invalid: [ / \ # & % ? : " ' ` ]

    name

    String-based Value

    name is a special string dataType.

    It forces the following options : required:true,
    acceptEmptyString:false,
    unique:true
    and rejects a default value.

    additional definition :

    maxLen
    (optional) - Int - (default 255)

    dataType name is mainly intended for unique naming of dbEntries. (top-level dbFields)
    - not allowed as dbFieldArr !!

    Note : do not use for person names (they may collide) !!

    Input Validation: strict
    - max length: 255 / maxLen(if set)
    - invalid: <control> characters, Tab, NewLine
    - invalid: [ / \ # & % ? : " ' ` ]

    Note : there will never be a dbVal _empty_ for name fields !

    email

    String-based Value

    email is a special string dataType.

    It sets the following option as default : (Can be overridden) unique:true

    additional definition :

    acceptEmptyString
    (optional) - true/false - (false by default)
    - use with required:true
    - see detailed description at dbField

    Input strings are regex-validated to be a valid email string:
    /^\w[\w-\.]*\w@\w[\w-\.]*\w\.\w{2,}/
    - user (accepting "-" & ".")
    - single "@" char
    - domain (accepting "-" & ".")
    - at least one "." char in the domain name
    - TLD at least 2 chars wide

    Input Validation: strict
    - max length: 255
    - invalid: <control> characters, Tab, NewLine
    - invalid: [ / \ # & % ? : " ' ` ]

    Note : setting a defaultValue requires to reset unique:false !

    password

    String-based Value

    password is a special string dataType.
    The values are stored as sha- or md5-encrypted strings.

    It forces the following options : (for obvious reasons ...) noIndex:true

    Note : preferrably name as "passw"*, *"pwd", *"Pwd" or "uPwd" ! (LogHandler replacements)

    additional definition :

    pwdFormat
    (required) - "sha" / "md5"
    default
    (optional) - initPwd
    dataType password is mainly intended for (non-sytem)user/customer dbs.
  • not allowed as dbFieldArr !!
  • usage as top-level dbField recommended
  • initPwd is allowed as plain string. (evaluted to determine, if the pwd was reset or not initialized yet)
  • "sha" uses the SHA-256 algorithm
  • "md5" is not collision-resistant and no longer considered safe for passwords!

  • Can be used to store MD5 checksums for files etc.
    ( Although the dataType name may be misleading - the functionality is the same...)
    Anyway - for that purpose, md5 is still "safe enough" to be included here.

    Input Validation: strict
    - max length: 255
    - invalid: <control> characters, Tab, NewLine
    - invalid: [ / \ # & % ? : " ' ` ]

    path

    String-based Value

    path is a string-based dataType representing pathes.
    The path delimiter "/" will be replaced by and utf-variant for the dbVal name
    to avoid conflicts with vfs- & URL-handling.
    Nevertheless input strings can be passed and are stored in the dbEntries as "/".

    It sets the following option as default : (Can be overridden) noIndex:true

    additional definition :

    basePath
    (optional) - String
    - basePath MUST be absolute !
    - on validation, input pathes MUST match the basePath
    maxLen
    (optional) - Int - (default 255)
    acceptEmptyString
    (optional) - true/false - (false by default)
    - use with required:true
    - see detailed description at dbField

    If acceptEmptyString:true - check against basePath is skipped for ""...
    Unless basePath is set, pathes may be relative...

    TODO : option validateVfs true/false - check input path to be present in the vfs

    TODO : option validatePerm R/W/M/A/X - check input path to be accessible (by Mode)

    Input Validation: strict (except / replacement)
    - max length: 255 / maxLen(if set)
    - invalid: <control> characters, Tab, NewLine
    - invalid: [ \ # & % ? : " ' ` ]
    - accepted / dbVal name replacement : / => (U+29F8)

    queries :

    vfs.ls("!/db/dbClassName/dbFieldName/") - listing of stored dbValues.
    vfs.fRead("!/db/dbClassName/dbFieldName/") - JS/JSON Array of stored dbValue Names.
    both queries will return their values with the / => (U+29F8) replacements !

    Using those returned values to query:
    vfs.fRead("!/db/dbClassName/dbFieldName/"+dbValName) - will work as expected.

    If You have the path to be queried as a variable: pathVar = "/path/to/file.txt";
    vfs.fRead("!/db/dbClassName/dbFieldName/"+pathVar) will evaluate to something like
    vfs.fRead("!/db/dbClassName/dbFieldName//path/to/file.txt")
    - which obviously won't work, as it is an invalid path...
    Therefore you need to do the conversion first let dbValName = pathVar.replace(/\//g, "\u29F8");
    and do the vfs.fRead with the dbValName.

    The db-interface will check and do those conversions automatically...
    db.class(dbClassName).query([dbFieldName, dbValName])
    db.class(dbClassName).query([dbFieldName, pathVar])
    db.class(dbClassName).query([dbFieldName, "/path/to/file.txt"])

    select

    String-based Value

    select is a string-based dataType which allows input values from a predefined set of values.
    Can be assigned to a dbField for single values or to dbFieldArr for multiSelect.

    It forces the following option : noIndex:false

    additional definition :

    selection
    (required) - Array of Strings
    - no duplicates allowed !
    default
    (optional) - MUST match to one of the selection values !
    acceptEmptyString
    (optional) - true/false - (false by default)
    - use with required:true
    - see detailed description at dbField

    On db startup, all values from the selection are validated (strict)
    and their corresponding dbValues will be created,
    and immutably added to the dbField/dbFieldArr.
    - therefore queries to a select field may return an empty idArr !

    No empty String in selection ! (And no default:"")
    EITHER set required:false OR required:true, acceptEmptyString:true.
    If one of them is set, an additional _empty_ dbValue will be added
    and the select field will accept those values.

    Selection & Input Validation: strict
    - max length: 255
    - invalid: <control> characters, Tab, NewLine
    - invalid: [ / \ # & % ? : " ' ` ]

    TODO : option selection as Object of Key/Value <string>/<number> pairs for numeric values

    dateTime

    String-based Value

    dateTime is a string-based dataType accepting any parsable input string format.

    Inside Interpreter/ScriptEngine, a returned dbEntry will contain a native JS-Date.
    On output, dateTimes are stored/returned/printed as ISO dateTime strings.

    additional definition :

    acceptEmptyString
    (optional) - true/false - (false by default)
    - use with required:true
    - see detailed description at dbField

    Input Validation: strict (except ":" )
    - max length: 255
    - invalid: <control> characters, Tab, NewLine
    - invalid: [ / \ # & % ? " ' ` ]
    - accepted: : (part of ISO dateTime string)

    json

    String-based Value

    json is a string-based dataType accepting any parsable JS/JSON input.
    - they MUST be self-contained (no references outside its own scope !),
    - TODO : - they MAY reference values inside the current dbEntry's (unvalidated) scope ?,
    - they MAY contain function declarations OR be a self-executing function returning JS values,
    - they MAY contain Interpreter/ScriptEngine-specific values (literal SysIds).

    Inside Interpreter/ScriptEngine, a returned dbEntry will contain a native JS-Value.
    On output, (stored/returned/printed) as JS/JSON string.

    It forces the following options: noIndex:true,
    unique:false
    - not allowed as dbFieldArr !!

    additional definition : (none)

    dataType json is NOT intended for user-to-user dbClasses !

    Note : do NOT use, without double checking !!

    Can be mis-used to spread unwanted code !
    When the dbEntry containing <json> fields comes from one user's (text)input,
    and returned to another user's browser or sessionObj, it may be executed there...

    dataType json is intended for storage of settings or options in administrative dbClasses,
    maintained by a system user with elevated privileges, and where individual dbEntries are returned
    only to those users, the dbEntry is assigned to. (by uId or role, depending on application logic.)

    If any of those settings or options follow a predictable structure, are predefined/indexable:
    - split and use dbObj/dbObjArrs and dbField/dbFieldArrs (even if all of them are optional)

    Usage : [ Used for the sessionObj's sessStorage ]
    - Keys in sessStorage are user-defined and not predictable.
    - due to the sessStorage.export function, the data inside are pre-validated and self-contained.
    - sessStorage -> dbEntry is an automated process with no user interaction.
    - the stored dbEntry is returned only to the assigned user/sessObj.


    special fields

    There is a small set of reserved fieldNames with specialized meanings.
    They cannot be chosen in database definitions and are automatically set/updated
    while creating/updating dbEntries.

    Those fields are present in each returned dbEntry and some dbValue queries.
    They are NOT present in the vfs, are not indexed and cannot be queried actively.

    dbClass

    The dbClass to which the current dbEntry (or set of dbIds) belongs to.

    _dbLinks_

    An Array of all dbIds (long version) which reference to the current dbEntry.

    _lastChanged_

    Contains a userId (SysId), userName and a dateTime of the current version of the dbEntry.

    reserved field names

    Reserved field names cannot be chosen as fName, oName or cName in db definitions.


    © Siggi Gross - June 2024 - mail@siggi-gross.de
    [ Legal Notice | Impressum & Datenschutz ]

    Loading Application ...