database

basic concepts

structured data

transaction based

indexed by default

scriptable

seamless vfs-integration

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)

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:

• Who took a certain class? - or took classes from a certain lecturer ?
• Which students have pending classes or failed to finish them ?
• Which classes take place at room r02 ? (Like in real life, there is more than one way to check that...)
• Why our best student (p0010) failed at one specific class ? (Anyway, we think he has a great future in philosophy ;-)
• ... have fun clicking through the various entries and forgive me my dark sense of humour ...
  ...and please have some mercy with poor Bobby Tables (Maybe we'll recommend him back to [ ComputerSience101 ] ?)

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 :

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:

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:

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 :

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

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.

• dbClass
• id
• _dbLinks_
• _lastChanged_
• _empty_
• _count_
• _range_
• cId
• partition
• dateTime

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