 -- =============================================
-- Schema Creation
-- =============================================
CREATE SCHEMA IF NOT EXISTS app;
SET search_path TO app;
-- =============================================
-- Extensions
-- =============================================
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
-- =============================================
-- ENUM Types
-- =============================================
CREATE TYPE tenant_state AS ENUM ('ACTIVE', 'INACTIVE', 'SUSPENDED', 'DELETED');
CREATE TYPE common_state AS ENUM ('ACTIVE', 'INACTIVE', 'ARCHIVED', 'DELETED');
CREATE TYPE build_state AS ENUM ('PROCESSING', 'READY', 'FAILED', 'DELETED');
CREATE TYPE user_state AS ENUM ('ACTIVE', 'INACTIVE', 'PENDING', 'DELETED');
CREATE TYPE user_role AS ENUM ('ADMIN', 'MANAGER', 'MEMBER', 'VIEWER');
CREATE TYPE creation_source AS ENUM ('SYSTEM', 'USER');
CREATE TYPE attribute_type AS ENUM ('STRING', 'NUMBER', 'BOOLEAN', 'DATE');
CREATE TYPE attribute_visibility AS ENUM ('TENANT', 'APPLICATION');
CREATE TYPE filter_visibility AS ENUM ('TENANT', 'USER');
CREATE TYPE api_token_state AS ENUM ('ACTIVE', 'REVOKED', 'EXPIRED');
CREATE TYPE api_token_access AS ENUM ('READ', 'FULL');
CREATE TYPE group_type AS ENUM ('PUBLIC', 'EXTERNAL', 'INTERNAL');
CREATE TYPE tester_source AS ENUM ('INVITATION', 'SELF_JOIN', 'IMPORT');
CREATE TYPE tester_state AS ENUM ('INVITED', 'ACTIVE', 'INACTIVE', 'BLOCKED');
CREATE TYPE login_state AS ENUM ('ACTIVE', 'EXPIRED');
CREATE TYPE magic_link_type AS ENUM ('SIGNUP', 'LOGIN');
-- =============================================
-- Utility Functions
-- =============================================
-- Timestamp management function
CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS TRIGGER AS $$ BEGIN BEGIN IF TG_OP = 'INSERT' THEN NEW.created_at = CURRENT_TIMESTAMP;
NEW.updated_at = CURRENT_TIMESTAMP;
ELSIF TG_OP = 'UPDATE' THEN -- Prevent created_at from being modified
NEW.created_at = OLD.created_at;
NEW.updated_at = CURRENT_TIMESTAMP;
END IF;
EXCEPTION
WHEN undefined_column THEN -- Do nothing if the created_at or updated_at column does not exist
RETURN NEW;
END;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- State-based deletion function
CREATE OR REPLACE FUNCTION trigger_state_deletion() RETURNS TRIGGER AS $$ BEGIN BEGIN -- Only proceed if the state column is being changed to 'DELETED'
  IF NEW.state = 'DELETED'
  AND (
    OLD.state IS NULL
    OR OLD.state != 'DELETED'
  ) THEN NEW.deleted_at = CURRENT_TIMESTAMP;
-- If state is changing from 'DELETED' to something else, clear deleted_at
ELSIF OLD.state = 'DELETED'
AND NEW.state != 'DELETED' THEN NEW.deleted_at = NULL;
END IF;
EXCEPTION
WHEN undefined_column THEN -- Do nothing if the state column does not exist
RETURN NEW;
END;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Function to validate email format
CREATE OR REPLACE FUNCTION app.is_valid_email(email TEXT) RETURNS BOOLEAN AS $$ BEGIN RETURN email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
END;
$$ LANGUAGE plpgsql;
-- =============================================
-- Tables
-- =============================================
-- Tenant table
CREATE TABLE IF NOT EXISTS app.tenant (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL CHECK (length(trim(name)) > 0),
  description TEXT,
  state tenant_state NOT NULL DEFAULT 'ACTIVE',
  contact_email VARCHAR(255) NOT NULL CHECK (app.is_valid_email(contact_email::TEXT)),
  web_site VARCHAR(255),
  created_by UUID,
  updated_by UUID,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ DEFAULT NULL
);
-- User table
CREATE TABLE IF NOT EXISTS app.user (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL CHECK (app.is_valid_email(email::TEXT)),
  state user_state NOT NULL DEFAULT 'PENDING',
  role user_role NOT NULL DEFAULT 'VIEWER',
  configurations JSONB NOT NULL DEFAULT '{"MarketingNotifications": false, "BuildNotifications": false}',
  tenant_id UUID REFERENCES app.tenant(id),
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  profile_url VARCHAR(255),
  created_by UUID,
  updated_by UUID,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  CONSTRAINT chk_user_tenant_state CHECK (
    (
      state != 'ACTIVE'
      OR (
        state = 'ACTIVE'
        AND tenant_id IS NOT NULL
      )
    )
  )
);
-- Platform table
CREATE TABLE IF NOT EXISTS app.platform (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL CHECK (length(trim(name)) > 0),
  description TEXT,
  state common_state NOT NULL DEFAULT 'ACTIVE',
  creation_source creation_source NOT NULL DEFAULT 'SYSTEM',
  tenant_id UUID,
  created_by UUID,
  updated_by UUID,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ DEFAULT NULL
);
-- Application table
CREATE TABLE IF NOT EXISTS app.application (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL CHECK (length(trim(name)) > 0),
  description TEXT,
  state common_state NOT NULL DEFAULT 'ACTIVE',
  icon_url VARCHAR(255),
  tenant_id UUID NOT NULL REFERENCES app.tenant(id),
  created_by UUID NOT NULL REFERENCES app.user(id),
  updated_by UUID NOT NULL REFERENCES app.user(id),
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  CONSTRAINT unique_app_per_tenant UNIQUE (tenant_id, name, deleted_at)
);
-- Application Platform associative table
CREATE TABLE IF NOT EXISTS app.application_platform (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  application_id UUID NOT NULL REFERENCES app.application(id),
  platform_id UUID NOT NULL REFERENCES app.platform(id),
  state common_state NOT NULL DEFAULT 'ACTIVE',
  created_by UUID NOT NULL REFERENCES app.user(id),
  updated_by UUID NOT NULL REFERENCES app.user(id),
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  CONSTRAINT unique_application_platform UNIQUE (application_id, platform_id, deleted_at)
);
-- Build table
CREATE TABLE IF NOT EXISTS app.build (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  number NUMERIC NOT NULL DEFAULT 1,
  age NUMERIC NOT NULL DEFAULT 30,
  state build_state NOT NULL DEFAULT 'PROCESSING',
  file_url VARCHAR(255) NOT NULL CHECK (length(trim(file_url)) > 0),
  application_id UUID NOT NULL REFERENCES app.application(id),
  platform_id UUID NOT NULL REFERENCES app.platform(id),
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  CONSTRAINT chk_build_number CHECK (number > 0),
  CONSTRAINT chk_build_age CHECK (age > 0) -- in days
);
-- Attribute table
CREATE TABLE IF NOT EXISTS app.attribute (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL CHECK (length(trim(name)) > 0),
  description TEXT,
  type attribute_type NOT NULL,
  visibility attribute_visibility NOT NULL DEFAULT 'APPLICATION',
  state common_state NOT NULL DEFAULT 'ACTIVE',
  creation_source creation_source NOT NULL DEFAULT 'SYSTEM',
  application_id UUID,
  -- if creation_source == user and visibility is APPLICATION, this must refer to an application
  tenant_id UUID,
  -- if creation_source == user and visibility is TENANT, this must refer to a tenant
  created_by UUID,
  -- if creation_source == user must refer to existing user.id 
  updated_by UUID,
  -- if creation_source == user must refer to existing user.id
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  CONSTRAINT chk_application_id_switch_visibility CHECK (
    (
      visibility = 'APPLICATION'
      AND application_id IS NOT NULL
    )
    OR (
      visibility != 'APPLICATION'
      AND application_id IS NULL
    )
  )
);
-- Build Attribute associative table
CREATE TABLE IF NOT EXISTS app.build_attribute (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  creation_source creation_source NOT NULL DEFAULT 'SYSTEM',
  value TEXT NOT NULL,
  build_id UUID NOT NULL REFERENCES app.build(id),
  attribute_id UUID NOT NULL REFERENCES app.attribute(id),
  created_by UUID,
  updated_by UUID,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  CONSTRAINT unique_build_attribute UNIQUE (attribute_id, build_id),
  CONSTRAINT chk_attribute_created_by_system CHECK (
    (
      creation_source = 'SYSTEM'
      AND created_by IS NULL
      AND updated_by IS NULL
    )
    OR (
      creation_source != 'SYSTEM'
      AND created_by IS NOT NULL
      AND updated_by IS NOT NULL
    )
  )
);
-- Attribute Platform associative table
CREATE TABLE IF NOT EXISTS app.attribute_platform (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  creation_source creation_source NOT NULL DEFAULT 'SYSTEM',
  attribute_id UUID NOT NULL REFERENCES app.attribute(id),
  platform_id UUID NOT NULL REFERENCES app.platform(id),
  created_by UUID,
  updated_by UUID,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  CONSTRAINT unique_attribute_platform UNIQUE (attribute_id, platform_id)
);
-- Filter table
CREATE TABLE IF NOT EXISTS app.filter (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL CHECK (length(trim(name)) > 0),
  description TEXT NOT NULL,
  definition JSONB NOT NULL,
  state common_state NOT NULL DEFAULT 'ACTIVE',
  visibility filter_visibility NOT NULL DEFAULT 'USER',
  tenant_id UUID NOT NULL REFERENCES app.tenant(id),
  application_id UUID NOT NULL REFERENCES app.application(id),
  created_by UUID NOT NULL REFERENCES app.user(id),
  updated_by UUID NOT NULL REFERENCES app.user(id),
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ DEFAULT NULL
);
-- Group table
CREATE TABLE IF NOT EXISTS app.group (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL CHECK (length(trim(name)) > 0),
  description TEXT,
  type group_type NOT NULL DEFAULT 'INTERNAL',
  state common_state NOT NULL DEFAULT 'ACTIVE',
  public_link VARCHAR(255),
  -- link to join the group
  tenant_id UUID NOT NULL REFERENCES app.tenant(id),
  created_by UUID NOT NULL REFERENCES app.user(id),
  updated_by UUID NOT NULL REFERENCES app.user(id),
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ DEFAULT NULL
);
-- Group Build associative table
CREATE TABLE IF NOT EXISTS app.group_build (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id UUID NOT NULL REFERENCES app.group(id),
  build_id UUID NOT NULL REFERENCES app.build(id),
  created_by UUID NOT NULL REFERENCES app.user(id),
  updated_by UUID NOT NULL REFERENCES app.user(id),
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  CONSTRAINT unique_build_group UNIQUE (group_id, build_id)
);
-- Tester table
CREATE TABLE IF NOT EXISTS app.tester (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL CHECK (length(trim(email)) > 0),
  source tester_source NOT NULL DEFAULT 'INVITATION',
  state tester_state NOT NULL DEFAULT 'INVITED',
  group_id UUID NOT NULL REFERENCES app.group(id),
  tenant_id UUID NOT NULL REFERENCES app.tenant(id),
  created_by UUID NOT NULL REFERENCES app.user(id),
  updated_by UUID NOT NULL REFERENCES app.user(id),
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ DEFAULT NULL
);
-- Download table
CREATE TABLE IF NOT EXISTS app.download (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  metadata JSONB,
  tenant_id UUID NOT NULL REFERENCES app.tenant(id),
  tester_id UUID NOT NULL REFERENCES app.tester(id),
  build_id UUID NOT NULL REFERENCES app.build(id),
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);
-- API Token table
CREATE TABLE IF NOT EXISTS app.api_token (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  token VARCHAR(255) NOT NULL CHECK (length(trim(token)) > 0),
  state api_token_state NOT NULL DEFAULT 'ACTIVE',
  access api_token_access NOT NULL DEFAULT 'READ',
  created_by UUID NOT NULL REFERENCES app.user(id),
  updated_by UUID NOT NULL REFERENCES app.user(id),
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);
-- Login table
CREATE TABLE IF NOT EXISTS app.login (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES app.user(id),
  state login_state NOT NULL DEFAULT 'ACTIVE',
  tenant_id UUID NOT NULL REFERENCES app.tenant(id),
  created_by UUID NOT NULL REFERENCES app.user(id),
  updated_by UUID NOT NULL REFERENCES app.user(id),
  last_login_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);
-- Magic Link table
CREATE TABLE IF NOT EXISTS app.magic_link (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  age INTEGER NOT NULL CHECK (age >= 0),
  -- Age in seconds
  type magic_link_type NOT NULL,
  user_email VARCHAR(255) NOT NULL CHECK (app.is_valid_email(user_email::TEXT)),
  validation_code VARCHAR(255) NOT NULL CHECK (length(trim(validation_code)) > 0),
  created_at TIMESTAMPTZ NOT NULL
);
-- =============================================
-- Indexes & constraints
-- =============================================
-- Tenant indexes
CREATE INDEX IF NOT EXISTS idx_tenant_state_name ON app.tenant(state, lower(name))
WHERE state != 'DELETED'
  AND deleted_at IS NULL;
-- User indexes
CREATE INDEX IF NOT EXISTS idx_user_tenant_state ON app.user(tenant_id, state)
WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_user_email_state ON app.user(lower(email), state)
WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_user_tenant_email ON app.user(tenant_id, email, state)
WHERE deleted_at IS NULL;
-- Platform indexes
CREATE INDEX IF NOT EXISTS idx_platform_name_state ON app.platform(lower(name), state)
WHERE deleted_at IS NULL;
-- Application indexes
CREATE INDEX IF NOT EXISTS idx_application_tenant_state ON app.application(tenant_id, state)
WHERE deleted_at IS NULL;
-- Build indexes
CREATE INDEX IF NOT EXISTS idx_build_application_state ON app.build(application_id, state)
WHERE deleted_at IS NULL;
-- Attribute indexes
CREATE INDEX IF NOT EXISTS idx_attribute_name_type ON app.attribute(lower(name), type);
-- Group indexes
CREATE INDEX IF NOT EXISTS idx_group_name_type ON app.group(lower(name), type);
-- Tester indexes
CREATE INDEX IF NOT EXISTS idx_tester_email_state ON app.tester(lower(email), state);
-- Download indexes
CREATE INDEX IF NOT EXISTS idx_download_metadata ON app.download USING gin(metadata);
CREATE INDEX idx_download_tester_build ON app.download(tester_id, build_id);
-- Login indexes
CREATE INDEX IF NOT EXISTS idx_login_user_state ON app.login(user_id, state);
-- Magic link indexes
CREATE INDEX IF NOT EXISTS idx_magic_link_email_state ON app.magic_link(lower(user_email));
-- =============================================
-- Triggers
-- =============================================
-- Timestamp triggers
CREATE TRIGGER set_timestamp_tenant BEFORE
INSERT
  OR
UPDATE ON app.tenant FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_user BEFORE
INSERT
  OR
UPDATE ON app.user FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_platform BEFORE
INSERT
  OR
UPDATE ON app.platform FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_application BEFORE
INSERT
  OR
UPDATE ON app.application FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_application_platform BEFORE
INSERT
  OR
UPDATE ON app.application_platform FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_build BEFORE
INSERT
  OR
UPDATE ON app.build FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_attribute BEFORE
INSERT
  OR
UPDATE ON app.attribute FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_filter BEFORE
INSERT
  OR
UPDATE ON app.filter FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_group BEFORE
INSERT
  OR
UPDATE ON app.group FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_attribute_platform BEFORE
INSERT
  OR
UPDATE ON app.attribute_platform FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_group_build BEFORE
INSERT
  OR
UPDATE ON app.group_build FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_tester BEFORE
INSERT
  OR
UPDATE ON app.tester FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_download BEFORE
INSERT
  OR
UPDATE ON app.download FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_api_token BEFORE
INSERT
  OR
UPDATE ON app.api_token FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
CREATE TRIGGER set_timestamp_login BEFORE
INSERT
  OR
UPDATE ON app.login FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
-- Timestamp trigger for magic_link
CREATE TRIGGER set_timestamp_magic_link BEFORE
INSERT ON app.magic_link FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();
-- State-based deletion triggers (only for tables with state column)
CREATE TRIGGER state_deletion_tenant BEFORE
UPDATE ON app.tenant FOR EACH ROW EXECUTE FUNCTION trigger_state_deletion();
CREATE TRIGGER state_deletion_user BEFORE
UPDATE ON app.user FOR EACH ROW EXECUTE FUNCTION trigger_state_deletion();
CREATE TRIGGER state_deletion_platform BEFORE
UPDATE ON app.platform FOR EACH ROW EXECUTE FUNCTION trigger_state_deletion();
CREATE TRIGGER state_deletion_application BEFORE
UPDATE ON app.application FOR EACH ROW EXECUTE FUNCTION trigger_state_deletion();
CREATE TRIGGER state_deletion_application_platform BEFORE
UPDATE ON app.application_platform FOR EACH ROW EXECUTE FUNCTION trigger_state_deletion();
CREATE TRIGGER state_deletion_build BEFORE
UPDATE ON app.build FOR EACH ROW EXECUTE FUNCTION trigger_state_deletion();
CREATE TRIGGER state_deletion_attribute BEFORE
UPDATE ON app.attribute FOR EACH ROW EXECUTE FUNCTION trigger_state_deletion();
CREATE TRIGGER state_deletion_filter BEFORE
UPDATE ON app.filter FOR EACH ROW EXECUTE FUNCTION trigger_state_deletion();
CREATE TRIGGER state_deletion_group BEFORE
UPDATE ON app.group FOR EACH ROW EXECUTE FUNCTION trigger_state_deletion();
CREATE TRIGGER state_deletion_tester BEFORE
UPDATE ON app.tester FOR EACH ROW EXECUTE FUNCTION trigger_state_deletion();

created 8 months ago

PostgreSQL online editor

Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.

About PostgreSQL

PostgreSQL is a open source relational database system and is also knows as Postgres.

Key Features:

Postgres is not only free and open-source but also it is highly extensible.
Custom Data types and funtions from various programming languaues can be introduced and the good part is compiling entire database is not required.
ACID(Atomicity, Consistency, Isolation, Durability) compliant.
First DBMS which implemented Multi-version concurrency control (MVCC) feature.
It's the default database server for MacOS.
It supports all major operating systems like Linux, Windows, OpenBSD,FreeBSD etc.

Syntax help

1. CREATE

CREATE command is used to create a table, schema or an index.

Syntax:

         CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

2. ALTER

ALTER command is used to add, modify or delete columns or constraints from the database table.

Syntax

ALTER TABLE Table_name ADD column_name datatype;

3. TRUNCATE:

TRUNCATE command is used to delete the data present in the table but this will not delete the table.

Syntax

TRUNCATE table table_name;

4. DROP

DROP command is used to delete the table along with its data.

Syntax

DROP TABLE table_name;

5. RENAME

RENAME command is used to rename the table name.

Syntax

ALTER TABLE table_name1 RENAME to new_table_name1;

6. INSERT

INSERT Statement is used to insert new records into the database table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

7. SELECT

Select statement is used to select data from database tables.

Syntax:

SELECT column1, column2, ...
FROM table_name;

8. UPDATE

UPDATE statement is used to modify the existing values of records present in the database table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

9. DELETE

DELETE statement is used to delete the existing records present in the database table.

Syntax

DELETE FROM table_name where condition;