Skip to content

EZcast database

Jump to bottom Edit New page
detobel36 edited this page Jul 20, 2017 · 3 revisions

Database

Database is automatically created when EZAdmin is installed. You can find this specific code here: ezadmin/web_install.php

Entity relational schema

Relationnal database

Manually create database

Normally you don't must to create the database manually. However, you could find the script to create database here bellow:

#------------------------------------------------------------
#        Script MySQL.
#------------------------------------------------------------


#------------------------------------------------------------
# Table: classrooms
#------------------------------------------------------------

CREATE TABLE classrooms(
        room_ID   Varchar (20) NOT NULL ,
        name      Varchar (255) ,
        IP        Varchar (100) NOT NULL ,
        IP_remote Varchar (100) ,
        enabled   TinyINT NOT NULL ,
        PRIMARY KEY (room_ID )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: courses
#------------------------------------------------------------

CREATE TABLE courses(
        course_code  Varchar (50) NOT NULL ,
        course_name  Varchar (255) ,
        shortname    Varchar (100) ,
        in_recorders TinyINT NOT NULL ,
        has_albums   Int NOT NULL ,
        date_created Date NOT NULL ,
        origin       Varchar (255) NOT NULL ,
        PRIMARY KEY (course_code )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: admin_logs
#------------------------------------------------------------

CREATE TABLE admin_logs(
        ID      int (11) Auto_increment  NOT NULL ,
        time   Datetime NOT NULL ,
        table  Varchar (100) NOT NULL ,
        message Varchar (255) ,
        author  Varchar (20) NOT NULL ,
        PRIMARY KEY (ID )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: users
#------------------------------------------------------------

CREATE TABLE users(
        user_ID         Varchar (50) NOT NULL ,
        surname         Varchar (255) ,
        forename        Varchar (255) ,
        passwd          Varchar (255) NOT NULL ,
        recorder_passwd Varchar (255) ,
        permissions     Int NOT NULL ,
        origin          Varchar (255) NOT NULL ,
        PRIMARY KEY (user_ID )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: users_courses
#------------------------------------------------------------

CREATE TABLE users_courses(
        ID          int (11) Auto_increment  NOT NULL ,
        course_code Varchar (50) NOT NULL ,
        user_ID     Varchar (50) NOT NULL ,
        origin      Varchar (255) NOT NULL ,
        PRIMARY KEY (ID )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: threads
#------------------------------------------------------------

CREATE TABLE threads(
        id             int (11) Auto_increment  NOT NULL ,
        authorId       Varchar (50) NOT NULL ,
        authorFullName Varchar (255) NOT NULL ,
        title          Varchar (140) NOT NULL ,
        message        Varchar (8192) NOT NULL ,
        timecode       Int NOT NULL ,
        creationDate   Datetime NOT NULL ,
        studientOnly   Char NOT NULL ,
        albumName      Varchar (255) NOT NULL ,
        assetName      Varchar (255) NOT NULL ,
        assetTitle     Varchar (255) NOT NULL ,
        lastEditDate   Datetime NOT NULL ,
        lastEditAuthor Varchar (255) NOT NULL ,
        nbComments     Int NOT NULL ,
        deleted        Char NOT NULL ,
        PRIMARY KEY (id )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: comments
#------------------------------------------------------------

CREATE TABLE comments(
        id             int (11) Auto_increment  NOT NULL ,
        authorId       Varchar (50) NOT NULL ,
        authorFullName Varchar (255) NOT NULL ,
        message        Varchar (8192) NOT NULL ,
        creationDate   Datetime NOT NULL ,
        thread         BigInt NOT NULL ,
        parent         BigInt ,
        nbChilds       Int NOT NULL ,
        lastEditDate   Datetime NOT NULL ,
        approval       Char NOT NULL ,
        score          Int NOT NULL ,
        upvoteScore    Int NOT NULL ,
        downvoteScore  Int NOT NULL ,
        deleted        Char NOT NULL
        PRIMARY KEY (id )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: votes
#------------------------------------------------------------

CREATE TABLE votes(
        login    Varchar (50) NOT NULL ,
        comment BigInt NOT NULL ,
        voteType TinyINT NOT NULL ,
        PRIMARY KEY (login , comment)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: messages
#------------------------------------------------------------

CREATE TABLE messages(
        id             int (11) Auto_increment  NOT NULL ,
        authorId       Varchar (50) NOT NULL ,
        authorFullName Varchar (255) NOT NULL ,
        message        Varchar (8192) NOT NULL ,
        timecode       Int NOT NULL ,
        creationDate   Datetime NOT NULL ,
        albumName      Varchar (255) NOT NULL ,
        assetName      Varchar (255) NOT NULL ,
        PRIMARY KEY (id )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: event_status
#------------------------------------------------------------

CREATE TABLE event_status(
        asset       Varchar (50) NOT NULL ,
        status      Enum ("auto_success","auto_success_errors","auto_success_warnings","auto_failure",
                          "auto_ignore","manual_ok","manual_partial_ok","manual_failure","manual_ignore") NOT NULL ,
        author      Varchar (50) NOT NULL ,
        status_time Datetime ,
        description Text ,
        PRIMARY KEY (asset )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: event_last_indexes
#------------------------------------------------------------

CREATE TABLE event_last_indexes(
        source Varchar (20) NOT NULL ,
        id     Integer NOT NULL ,
        PRIMARY KEY (source )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: event_asset_parent
#------------------------------------------------------------

CREATE TABLE event_asset_parent(
        asset        Varchar (50) NOT NULL ,
        parent_asset Varchar (50) NOT NULL ,
        PRIMARY KEY (asset )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: asset_infos
#------------------------------------------------------------

CREATE TABLE asset_infos(
        asset        Varchar (50) NOT NULL ,
        start_time   Datetime NOT NULL ,
        end_time     Datetime ,
        classroom_id Varchar (50) NOT NULL ,
        course       Varchar (50) NOT NULL ,
        author       Varchar (50) NOT NULL ,
        cam_slide    Enum ("cam","slide","camslide") NOT NULL ,
        PRIMARY KEY (asset )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: streams
#------------------------------------------------------------

CREATE TABLE streams(
        id          int (11) Auto_increment  NOT NULL ,
        cours_id    Varchar (50) NOT NULL ,
        asset       Varchar (50) NOT NULL ,
        module_type Varchar (15) NOT NULL ,
        classroom   Varchar (50) NOT NULL ,
        record_type Varchar (10) NOT NULL ,
        netid       Varchar (50) NOT NULL ,
        stream_name Varchar (255) NOT NULL ,
        token       Varchar (50) NOT NULL ,
        ip          Varchar (50) NOT NULL ,
        status      Varchar (50) NOT NULL ,
        quality     Varchar (10) NOT NULL ,
        protocol    Varchar (10) NOT NULL ,
        server      Varchar (50) ,
        port        Int ,
        PRIMARY KEY (id )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: events
#------------------------------------------------------------

CREATE TABLE events(
        id                 int (11) Auto_increment  NOT NULL ,
        asset              Varchar (50) NOT NULL ,
        origin             Enum ("ezmanager","ezadmin","ezrecorder","ezrenderer","other") NOT NULL ,
        classroom_id       Varchar (50) ,
        classroom_event_id Int ,
        event_time         Datetime NOT NULL ,
        type_id            Int NOT NULL ,
        context            Varchar (30) NOT NULL ,
        loglevel           TinyINT NOT NULL ,
        message            Text NOT NULL ,
        PRIMARY KEY (id ) ,
        INDEX (asset ,event_time )
)ENGINE=InnoDB;

ALTER TABLE comments ADD CONSTRAINT FK_comments_thread FOREIGN KEY (thread) REFERENCES threads(id);
ALTER TABLE comments ADD CONSTRAINT FK_comments_parent FOREIGN KEY (parent) REFERENCES comments(id);
ALTER TABLE votes ADD CONSTRAINT FK_votes_comment FOREIGN KEY (comment) REFERENCES comments(comment);

Note: This script have been generate with JMerise and is not exactly the same as the one in the code.

Add a custom footer
Clone this wiki locally