| -- |
| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| -- |
| |
| -- |
| -- +=================================+ |
| -- | ORACLE SQL schema definition. | |
| -- +=================================+ |
| -- |
| -- NOTE: connect as 'nlpcraft'. |
| -- |
| |
| -- |
| -- Company table. |
| -- |
| CREATE TABLE nc_company ( |
| id NUMBER PRIMARY KEY, |
| name VARCHAR2(64) NOT NULL, |
| website VARCHAR2(256), |
| country VARCHAR2(32), |
| region VARCHAR2(512), |
| city VARCHAR2(512), |
| address VARCHAR2(512), |
| postal_code VARCHAR2(32), |
| auth_token VARCHAR2(64) NOT NULL, |
| auth_token_hash VARCHAR2(64) NOT NULL, |
| created_on DATE DEFAULT sysdate NOT NULL, |
| last_modified_on DATE DEFAULT sysdate NOT NULL |
| ); |
| |
| CREATE UNIQUE INDEX nc_company_idx_1 ON nc_company(name); |
| CREATE UNIQUE INDEX nc_company_idx_2 ON nc_company(auth_token); |
| CREATE UNIQUE INDEX nc_company_idx_3 ON nc_company(auth_token_hash); |
| |
| -- |
| -- Company properties table. |
| -- |
| CREATE TABLE nc_company_property ( |
| id NUMBER PRIMARY KEY, |
| company_id NUMBER NOT NULL, |
| property VARCHAR2(64) NOT NULL, |
| value VARCHAR2(512) NULL, |
| created_on DATE DEFAULT sysdate NOT NULL, |
| last_modified_on DATE DEFAULT sysdate NOT NULL, |
| CONSTRAINT fk_company_id_company_property FOREIGN KEY (company_id) REFERENCES nc_company(id) |
| ); |
| |
| CREATE INDEX nc_company_property_idx1 ON nc_company_property(company_id); |
| |
| -- |
| -- User table. |
| -- |
| CREATE TABLE nc_user ( |
| id NUMBER PRIMARY KEY, |
| company_id NUMBER NOT NULL, |
| ext_id VARCHAR2(64) NULL, -- External user ID. |
| email VARCHAR2(64) NULL, -- Used as username during login. |
| avatar_url VARCHAR2(2083) NULL, -- URL or encoding of avatar for this user, if any. |
| first_name VARCHAR2(64) NULL, |
| last_name VARCHAR2(64) NULL, |
| is_admin NUMBER(1) NOT NULL, -- Whether or not created with admin token. |
| passwd_salt VARCHAR2(64) NULL, |
| created_on DATE DEFAULT sysdate NOT NULL, |
| last_modified_on DATE DEFAULT sysdate NOT NULL, |
| CONSTRAINT fk_company_id_user FOREIGN KEY (company_id) REFERENCES nc_company(id) |
| ); |
| |
| CREATE UNIQUE INDEX nc_user_idx_1 ON nc_user(email); |
| CREATE UNIQUE INDEX nc_user_idx_2 ON nc_user(company_id, ext_id); |
| CREATE INDEX nc_user_idx_3 ON nc_user(company_id); |
| |
| -- |
| -- User properties table. |
| -- |
| CREATE TABLE nc_user_property ( |
| id NUMBER PRIMARY KEY, |
| user_id NUMBER NOT NULL, |
| property VARCHAR2(64) NOT NULL, |
| value VARCHAR2(512) NULL, |
| created_on DATE DEFAULT sysdate NOT NULL, |
| last_modified_on DATE DEFAULT sysdate NOT NULL, |
| CONSTRAINT fk_user_id_user_property FOREIGN KEY (user_id) REFERENCES nc_user(id) |
| ); |
| |
| CREATE INDEX nc_user_property_idx1 ON nc_user_property(user_id); |
| |
| -- |
| -- Pool of password hashes. |
| -- |
| CREATE TABLE passwd_pool ( |
| id NUMBER PRIMARY KEY, |
| passwd_hash VARCHAR2(64) NOT NULL |
| ); |
| |
| -- |
| -- Processing log. |
| -- |
| CREATE TABLE proc_log ( |
| id NUMBER PRIMARY KEY, |
| srv_req_id VARCHAR2(64) NOT NULL, |
| txt VARCHAR2(1024) NULL, |
| user_id NUMBER NULL, |
| model_id VARCHAR2(64) NULL, |
| status VARCHAR2(32) NULL, |
| user_agent VARCHAR2(512) NULL, |
| rmt_address VARCHAR2(256) NULL, |
| sen_data CLOB NULL, |
| -- Ask and result timestamps. |
| recv_tstamp DATE NOT NULL, -- Initial receive timestamp. |
| resp_tstamp DATE NULL, -- Result or error response timestamp. |
| cancel_tstamp DATE NULL, -- Cancel timestamp. |
| -- Result parts. |
| res_type VARCHAR2(32) NULL, |
| res_body_gzip CLOB NULL, -- GZIP-ed result body. |
| intent_id VARCHAR2(256) NULL, |
| error CLOB NULL, |
| -- Probe information for this request. |
| probe_token VARCHAR2(256) NULL, |
| probe_id VARCHAR2(512) NULL, |
| probe_guid VARCHAR2(512) NULL, |
| probe_api_version VARCHAR2(512) NULL, |
| probe_api_date DATE NULL, |
| probe_os_version VARCHAR2(512) NULL, |
| probe_os_name VARCHAR2(512) NULL, |
| probe_os_arch VARCHAR2(512) NULL, |
| probe_start_tstamp DATE NULL, |
| probe_tmz_id VARCHAR2(64) NULL, |
| probe_tmz_abbr VARCHAR2(64) NULL, |
| probe_tmz_name VARCHAR2(64) NULL, |
| probe_user_name VARCHAR2(512) NULL, |
| probe_java_version VARCHAR2(512) NULL, |
| probe_java_vendor VARCHAR2(512) NULL, |
| probe_host_name VARCHAR2(1024) NULL, |
| probe_host_addr VARCHAR2(512) NULL, |
| probe_mac_addr VARCHAR2(512) NULL |
| ); |
| |
| CREATE UNIQUE INDEX proc_log_idx_1 ON proc_log(srv_req_id); |
| |
| -- |
| -- Request processing user feedback. |
| -- |
| CREATE TABLE feedback ( |
| id NUMBER PRIMARY KEY, |
| srv_req_id VARCHAR2(64) NOT NULL, |
| user_id NUMBER NOT NULL, |
| score NUMBER NOT NULL, |
| comment VARCHAR2(1024) NULL, |
| created_on DATE DEFAULT sysdate NOT NULL |
| ); |
| |
| CREATE INDEX feedback_idx_1 ON feedback(srv_req_id); |