// Switch to the database
use myFirstDB

-- Switch to the schema
ALTER SESSION SET CURRENT_SCHEMA = myfirstdb;

// Create a collection
db.createCollection("students")

-- Create a table
CREATE TABLE students (
  name   VARCHAR2(50),
  age    NUMBER,
  course VARCHAR2(20)
);

// List the collections
show collections

-- List the tables
SELECT table_name FROM user_tables;

// Show details of a collection
db.getCollectionInfos({ name: "students" })

-- Show details of a table
DESC students;

// Schema validation
db.createCollection("employees", {
  validator: { $jsonSchema: {
    bsonType: "object",
    required: ["name", "salary"],
    properties: {
      name:   { bsonType: "string" },
      salary: { bsonType: "number", minimum: 0 }
    }
  }}
})

-- NOT NULL and CHECK constraints
CREATE TABLE employees (
  name   VARCHAR2(50) NOT NULL,
  salary NUMBER NOT NULL
    CONSTRAINT chk_salary CHECK (salary >= 0)
);

// This insert fails validation
// (salary is missing)
db.employees.insertOne({ name: "Ravi" })

-- This insert fails
-- (ORA-01400: cannot insert NULL into salary)
INSERT INTO employees (name) VALUES ('Ravi');

// Capped collection (fixed size, oldest
// documents are overwritten, size is in bytes)
db.createCollection("logs", {
  capped: true, size: 1048576, max: 1000
})

-- No direct equivalent in Oracle

// Rename a collection
db.students.renameCollection("learners")

-- Rename a table
ALTER TABLE students RENAME TO learners;

// Change the validation rules
db.runCommand({
  collMod: "employees",
  validator: { $jsonSchema: {
    bsonType: "object",
    required: ["name", "salary", "dept"]
  }},
  validationLevel: "moderate",
  validationAction: "error"
})

-- Add a column and make it mandatory
-- (the MODIFY fails if existing rows have no dept)
ALTER TABLE employees ADD (dept VARCHAR2(20));
ALTER TABLE employees MODIFY (dept NOT NULL);

-- Check new and changed rows only
-- (similar idea to validationLevel moderate)
ALTER TABLE employees ADD CONSTRAINT chk_salary2
  CHECK (salary >= 0) ENABLE NOVALIDATE;

-- Drop a constraint
ALTER TABLE employees DROP CONSTRAINT chk_salary;

// Add a field to all documents
db.learners.updateMany(
  {}, { $set: { status: "active" } }
)

-- Add a column
ALTER TABLE learners
  ADD (status VARCHAR2(10) DEFAULT 'active');

// Rename a field in all documents
db.learners.updateMany(
  {}, { $rename: { course: "department" } }
)

-- Rename a column
ALTER TABLE learners
  RENAME COLUMN course TO department;

// Remove a field from all documents
db.learners.updateMany(
  {}, { $unset: { status: "" } }
)

-- Remove a column
ALTER TABLE learners DROP COLUMN status;

// Single field index
db.learners.createIndex({ name: 1 })

-- Single column index
CREATE INDEX idx_name ON learners (name);

// Compound index
db.learners.createIndex(
  { department: 1, age: -1 }
)

-- Composite index
CREATE INDEX idx_dept_age
  ON learners (department, age DESC);

// Unique index
db.learners.createIndex(
  { email: 1 }, { unique: true }
)

-- Unique index
CREATE UNIQUE INDEX idx_email
  ON learners (email);

// Index with your own name
db.learners.createIndex(
  { age: 1 }, { name: "idx_age" }
)

-- Index with your own name
CREATE INDEX idx_age ON learners (age);

// List all indexes
db.learners.getIndexes()

-- List all indexes
SELECT index_name, column_name
FROM user_ind_columns
WHERE table_name = 'LEARNERS';

// Drop one index
// (default name is field_direction, for example name_1)
db.learners.dropIndex("name_1")

-- Drop one index
DROP INDEX idx_name;

// Drop all indexes except _id
db.learners.dropIndexes()

-- No single command in Oracle,
-- drop the indexes one by one

// Drop a collection
// (returns true when done)
db.learners.drop()

-- Drop a table
-- (PURGE skips the recycle bin)
DROP TABLE learners PURGE;

// Drop the current database
db
db.dropDatabase()

-- Drop a schema with all its objects
-- (closest thing to dropping a database)
DROP USER myfirstdb CASCADE;

// Remove only the documents,
// the collection and indexes stay
db.learners.deleteMany({})

-- Remove only the rows
TRUNCATE TABLE learners;