User Tools

Site Tools


en-211-database


Data Base Definitions


# ========================================================================================
#
#   FOLLOWZUP PROJECT
#   MySQL Tables Definition
#
# ========================================================================================
#
#   Copyright (C) 2016 Followzup.com
#
#   This program is free software: you can redistribute it and/or modify it under
#   the terms of the GNU General Public License as published by the Free Software
#   Foundation, either version 3 of the License, or any later version.
#
#   This program is distributed in the hope that it will be useful, but WITHOUT
#   ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
#   FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
#
#   You should have received a copy of the GNU General Public License
#   along with this program.  If not, see <http://www.gnu.org/licenses/>
#
# ========================================================================================

    CREATE TABLE users (

        iduser          char(12)      collate utf8_general_ci not null default '',
        email           varchar(256)  collate utf8_general_ci not null default '',
        pass            char(64)      collate utf8_general_ci not null default '',
        daterescue      timestamp     not null default 0,
        dateincl        timestamp     not null default 0,
        datetry         timestamp     not null default 0,
        name            varchar(80)   collate utf8_general_ci not null default '',
        regstatus       char(1)       collate utf8_general_ci not null default '',

        /* regstatus:   (n)new, (a)active, (d)deleted */

        PRIMARY KEY (iduser) )
        ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

    ALTER TABLE users ADD UNIQUE INDEX (email);

# ========================================================================================

    CREATE TABLE channels (

        idchannel       char(12)      collate utf8_general_ci not null default '',
        iduser          char(12)      collate utf8_general_ci not null default '',
        tag             varchar(128)  collate utf8_general_ci not null default '',
        briefing        varchar(256)  collate utf8_general_ci not null default '',
        welcome         varchar(256)  collate utf8_general_ci not null default '',
        dateincl        timestamp     not null default 0,
        datetransf      timestamp     not null default 0,
        newiduser       char(12)      collate utf8_general_ci not null default '',
        channelseq      int           unsigned not null default 0,
        channeltype     char(1)       collate utf8_general_ci not null default '',
        privcode        char(8)       collate utf8_general_ci not null default '',
        idkey           char(12)      collate utf8_general_ci not null default '',
        md5icon         char(32)      collate utf8_general_ci not null default '',
        welcomeurl      varchar(256)  collate utf8_general_ci not null default '',
        responseurl     varchar(256)  collate utf8_general_ci not null default '',
        channelicon     text          collate utf8_general_ci,
        regstatus       char(1)       collate utf8_general_ci not null default '',

        /* channeltype: (r)private, (u)public
           regstatus:   (a)active,  (s)suspended, (d)deleted */

        PRIMARY KEY (idchannel) )
        ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

    ALTER TABLE channels ADD UNIQUE INDEX (tag);
    ALTER TABLE channels ADD UNIQUE INDEX (iduser, idchannel);

# ========================================================================================

    CREATE TABLE pkeys (

        idkey           char(12)      collate utf8_general_ci not null default '',
        idchannel       char(12)      collate utf8_general_ci not null default '',
        dateincl        timestamp     not null default 0,

        pkpub           varchar(1024) collate utf8_general_ci not null default '',
        pkpri           varchar(3072) collate utf8_general_ci not null default '',
        pkmod           varchar(512)  collate utf8_general_ci not null default '',
        pkpux           varchar(512)  collate utf8_general_ci not null default '',
        pkprx           varchar(512)  collate utf8_general_ci not null default '',
        pkpr1           varchar(512)  collate utf8_general_ci not null default '',
        pkpr2           varchar(512)  collate utf8_general_ci not null default '',
        pkdmp           varchar(512)  collate utf8_general_ci not null default '',
        pkdmq           varchar(512)  collate utf8_general_ci not null default '',
        pkiqm           varchar(512)  collate utf8_general_ci not null default '',

        PRIMARY KEY (idkey) )
        ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

# ========================================================================================

    CREATE TABLE devices (

        iddevice        char(12)      collate utf8_general_ci not null default '',
        idinterface     char(12)      collate utf8_general_ci not null default '',
        iduser          char(12)      collate utf8_general_ci not null default '',
        devicetag       varchar(64)   collate utf8_general_ci not null default '',
        dateincl        timestamp     not null default 0,
        lastact         timestamp     not null default 0,
        deviceseq       int           unsigned not null default 0,
        regstatus       char(1)       collate utf8_general_ci not null default '',

        pkpub           varchar(1024) collate utf8_general_ci not null default '',
        pkpri           varchar(3072) collate utf8_general_ci not null default '',
        pkmod           varchar(512)  collate utf8_general_ci not null default '',
        pkpux           varchar(512)  collate utf8_general_ci not null default '',
        pkprx           varchar(512)  collate utf8_general_ci not null default '',
        pkpr1           varchar(512)  collate utf8_general_ci not null default '',
        pkpr2           varchar(512)  collate utf8_general_ci not null default '',
        pkdmp           varchar(512)  collate utf8_general_ci not null default '',
        pkdmq           varchar(512)  collate utf8_general_ci not null default '',
        pkiqm           varchar(512)  collate utf8_general_ci not null default '',

        /* regstatus:   (a)active, (d)deleted */

        PRIMARY KEY (iddevice) )
        ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

    ALTER TABLE devices ADD UNIQUE INDEX (iduser, iddevice);
    ALTER TABLE devices ADD UNIQUE INDEX (iduser, devicetag);
    ALTER TABLE devices ADD INDEX (idinterface, iduser);

# ========================================================================================

    CREATE TABLE interfaces (

        idinterface     char(12)      collate utf8_general_ci not null default '',
        iduser          char(12)      collate utf8_general_ci not null default '',
        stamp           char(128)     collate utf8_general_ci not null default '',
        dateincl        timestamp     not null default 0,
        regstatus       char(1)       collate utf8_general_ci not null default '',

        /* regstatus:   (a)active, (d)deleted */

        PRIMARY KEY (idinterface) )
        ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

# ========================================================================================

    CREATE TABLE subscriptions (

        iduser          char(12)      collate utf8_general_ci not null default '',
        idchannel       char(12)      collate utf8_general_ci not null default '',
        dateincl        timestamp     not null default 0,
        datetry         timestamp     not null default 0,
        subscode        int           unsigned not null default 0,
        regstatus       char(1)       collate utf8_general_ci not null default '',

        /* regstatus:   (a)active, (d)deleted */

        PRIMARY KEY (iduser, idchannel) )
        ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

    ALTER TABLE subscriptions ADD UNIQUE INDEX (idchannel, iduser);

# ========================================================================================

    CREATE TABLE messages (

        idmessage       bigint        unsigned not null auto_increment,
        iduser          char(12)      collate utf8_general_ci not null default '',
        idchannel       char(12)      collate utf8_general_ci not null default '',
        mediamd5        char(32)      collate utf8_general_ci not null default '',
        dateincl        timestamp     not null default 0,
        dateterm        timestamp     not null default 0,
        hours           smallint      unsigned not null default 0,
        regstatus       char(1)       collate utf8_general_ci not null default '',

        /* regstatus:   (p)pending, (s)sent, (c)canceled-by-channel, (d)deleted-by-user */

    PRIMARY KEY (idmessage) )
    ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

    ALTER TABLE messages ADD INDEX (iduser, idchannel);

# ========================================================================================

    CREATE TABLE medias (

        idchannel       char(12)      collate utf8_general_ci not null default '',
        mediamd5        char(32)      collate utf8_general_ci not null default '',
        mediatext       text          collate utf8_general_ci,
        mediaurl        varchar(256)  collate utf8_general_ci not null default '',

        PRIMARY KEY (idchannel, mediamd5) )
        ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

# ========================================================================================


en-211-database.txt ยท Last modified: 2017/06/19 17:44 by admin

Page Tools