database structure

database definition

the database consists of tables, which contain fields, and relations to other tables.

fields be of these types:

int(1,2,4,big)
boolean
string(length)
blob
timestamp

a primary key is always added implicitly, and named 'id'. all references to other tables have <tablename>+'id' in their name, optionally preceeded by a name clarifying the type of relation. relations can be of these types:

1 to 1 : is-a
n to 1 : enum
1 to n : list
n to n : set

queries can be formulated as follows: ( each querie has a resulttable property, which dictates which table the results should be from )

query ::= field-specification relational-operator value-specification
field-specification ::= [ relation-name "." ... ] { field-name | relation-name }
relational-operator ::= '=' | '<' | '>' | '<=' | '>=' | '==' | 'in'
value-specification ::= quoted-string | number | date | null | '[' value-list ']'

example database layout:

  srcT.idF   = "pk"     [ implicit field ]
  srcT.srcF = "string"
  srcT.enum1IdF = "enum.enumT"
  srcT.enum2IdF = "enum.enumT"
  srcT.list1F = "list.listdstT.back1IdF"
  srcT.list2F = "list.listdstT.back2IdF"
  srcT.set1F = "set.link1T"
  srcT.set2F = "set.link2T"

  enumT.idF = "pk"     [ implicit field ]
  enumT.enF = "string"

  listdstT.idF = "pk"     [ implicit field ]
  listdstT.back1IdF = "enum.srcT"
  listdstT.back2IdF = "enum.srcT"
  listdstT.lsF = "string"

  link1T.srcIdF = "enum.srcT"
  link1T.dstIdF = "enum.dstT"

  link2T.srcIdF = "enum.srcT"
  link2T.dstIdF = "enum.dstT"

  dstT.idF = "pk"     [ implicit field ]
  dstT.setF = "string"

not yet sure how to call the 'enum' field. enum sort of implies a short limited number of values, while actually this may also be something like a userid. maybe I should call it reference. or just plain 'foreigh key' / 'fk'.

several combinations are possible:

srcF=='src1'

    where srcF='src1'

list1F.lsF=='list1'

left join listDstT j1 on j1.back1IdF=j0.idF and j1.lsF='list1'
    where not j1.back1IdF is NULL

enum1IdF.enF=='group1'

left join enumT j1 on j1.idF=j0.enum1IdF and j1.enF='group1'
    where not j1.idF is NULL

enum1IdF.enF!=='group1'

left join enumT j1 on j1.idF=j0.enum1IdF and j1.enF='group1'
    where j1.idF is NULL

set1F.setF=='set1'

left join (link1T j1  join dstT j2 on j1.dstIdF=j2.idF and j2.setF='set1')
       on j1.srcIdF=j0.idF
    where not j1.srcIdF is NULL

set1F.setF==[list] (exact list match)

left join (link1T j1  join dstT j2 on j1.dstIdF=j2.idF and j2.setF in (list) )
         on j1.srcIdF=j0.idF
     left join (link1T j3  joie dstT j4 on j3.dstIdF=j4.idF and j4.setF in (list) )
         on j3.srcIdF=j0.idF
     where not j1.srcIdf is null and j3.srcIdF is null
     group by j0.IdF having count(*) = sizeof(list)

enum1Id.enum2Id.enum3Id.enF='val'

left join (group1T j1 join (group2T j2 join (group3T j3) on j3.idF=j2.enum3IdF and  and j3.enF='val') on j2.idF=j1.enum2IdF) on j1.idF=j0.enum1IdF
    where not j1.idF is NULL

complex example : group.groupname==['Guests'] or group.groupname==['Admins']
( with basetable 'user' - returning userid of users who are in only one group
*** the group by should be put in a subquery which is joined.

table user
   username string
   groups   set.usergroup.group

table group
   groupname string
   users    set.usergroup.user

[implicit table 'usergroup']

    select j0.id from user j0
    left join (
    select j0.userid from usergroup_set j0
      left join group j1
        on j0.groupid=j1.id and j1.groupname in ('Guests')
      left join group j2
        on j0.groupid=j2.id and not j2.groupname in ('Guests')
    where not j1.id is null and j2.id is null
    group by j0.userid having count(*)=1
    ) j1 on j0.id=j1.userid
    left join (
    select j0.userid from usergroup_set j0
      left join group j1
        on j0.groupid=j1.id and j1.groupname in ('Admins')
      left join group j2
        on j0.groupid=j2.id and not j2.groupname in ('Admins')
    where not j1.id is null and j2.id is null
    group by j0.userid having count(*)=1
    ) j2 on j0.id=j2.userid
    where not j1.userid is null or not j2.userid is null