DB Schema

Discussion in 'General' started by jnsc, Oct 19, 2006.

  1. jnsc

    jnsc rotaredoM Moderator


    I was trying to figure out how the DB tables where connected together, and it would be much easier with something like an Database Schema. Does something like this exist?

  2. falko

    falko Super Moderator ISPConfig Developer

  3. jnsc

    jnsc rotaredoM Moderator

    Thank's for the link, I am going to test it.
  4. jnsc

    jnsc rotaredoM Moderator

    Because there is no Schema you will have to help me :)

    I want to know wich is the language of the admin user for a Website. So, is this request correct ? (it works but I am not sure if it's 100% OK)

    SELECT language
    FROM `isp_isp_web` W, isp_isp_kunde K, isp_dep D, sys_user S
    WHERE D.child_doc_id = W.doc_id
    AND D.child_doctype_id = W.doctype_id
    AND W.doc_id =7
    AND W.doctype_id =1013
    AND K.doc_id = D.parent_doc_id
    AND K.doctype_id = D.parent_doctype_id
    AND K.webadmin_userid = S.doc_id

    I took doc_id 7 and doctype_id 1013 as example. The part I'am realy not sure about is the last line. Is webadmin_userid of table isp_isp_kunde directly linked with doc_id of table sys_usr or do I have to use another table? And if yes which one?
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    The webadmin userid is not linked to the sys_user table. webadmin_userid is linked to the column doc_id of the table isp_isp_user.

    You can get the language for a website (or better the of the client who ons this website) like this:

    SELECT language FROM isp_nodes, sys_user WHERE isp_nodes.doctype_id = 1013 and isp_nodes.doc_id = {ID} and sys_user.doc_id = isp_nodes.userid

    You must replace {ID} with the doc_id of the website from which you want to get the language. If the resulting language field is empty, then the correct language for this website is global language preset found in the variable $go_info["server"]["lang"]

    The trick i used above is that the *_nodes table contains the permission settings for every record, so I'am able to get the userid from this table and join it with the sys_user table to get the language.
  6. jnsc

    jnsc rotaredoM Moderator


    I am going to have a look at it.

  7. jnsc

    jnsc rotaredoM Moderator

    Hi, and if I want all the co-domains of a domain is this correct?

    SELECT domain_host, domain_domain
    FROM isp_isp_web, isp_dep, isp_isp_domain
    WHERE isp_isp_web.doctype_id = isp_dep.parent_doctype_id
    AND isp_isp_web.doc_id = isp_dep.parent_doc_id
    AND isp_isp_domain.doctype_id = isp_dep.child_doctype_id
    AND isp_isp_domain.doc_id = isp_dep.child_doc_id
    AND isp_isp_web.doctype_id = 1013
    AND isp_isp_web.doc_id = {ID}
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    Yes, this looks fine. You can remove this part of the query:

    as the field isp_isp_web.doctype_id contains always the value 1013.
  9. jnsc

    jnsc rotaredoM Moderator

    Ok, thank's

Share This Page