postgresql

CREATE TABLE zgrid (
    id SERIAL PRIMARY KEY,
    "user_id" integer,
    routename VARCHAR(100),
    visibles text,
    invisibles text,
    labels text,
    filter text,
    "updated_at" timestamp,
    "modified_by" integer,
    "created_at" date,
    created_by integer
);

CREATE TABLE zvirtual (
  id serial NOT NULL,
  "table" varchar(225) NOT NULL,
  "is_script_only" SMALLINT DEFAULT '0',
  "fieldname" varchar(225) DEFAULT NULL,
  "field_parent" varchar(225) DEFAULT NULL,
  "field_alias" varchar(225) DEFAULT NULL,
  type SMALLINT  DEFAULT '1',
  "formula_sql" text,
  "formula_node" text,
  javascript text,
  PRIMARY KEY (id),
  UNIQUE ("table",fieldName)
);
COMMENT ON COLUMN zvirtual.table IS 'dropdown_command_SHOW TABLES';
COMMENT ON COLUMN zvirtual.is_script_only  IS 'module_switch';
COMMENT ON COLUMN zvirtual.type  IS 'dropdown_static_1=Text,2=Select,3=Date';


CREATE TABLE public.zfields (
    id SERIAL PRIMARY KEY,
    "table" varchar(100)  NOT NULL,
    tabs text,
    labels text NOT NULL,
    details text,
    "left" text,
    "right" text
);


CREATE TABLE position (
  id serial NOT NULL,
  "companyId" INT  REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE,
  status SMALLINT NOT NULL DEFAULT '1',
  title varchar(255) NOT NULL,
  "createdAt" timestamp,
  "updatedAt" timestamp without time zone NOT NULL,
  "createdBy" INT DEFAULT NULL,
  "updatedBy" INT DEFAULT NULL,
  PRIMARY KEY (id)
);
COMMENT ON COLUMN position.status IS 'dropdown_static_1=Internal,2=External';

CREATE TABLE "user" (
  id serial NOT NULL,
   "companyId" INT  REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE,
  "divisionId" INT  REFERENCES division(id) ON DELETE CASCADE ON UPDATE CASCADE,
  "departmentId" INT  REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE,
  "superior" INT,
  username varchar(255) NOT NULL UNIQUE,
  password varchar(255) NOT NULL,
  language varchar(20) DEFAULT 'lang_en',
  fullname varchar(255) NOT NULL,
  address varchar(255) DEFAULT NULL,
  phone varchar(50) DEFAULT NULL,
  city varchar(100) DEFAULT NULL,
  email varchar(100) NOT NULL,
  image varchar(255) DEFAULT NULL,
  "roleId" INT REFERENCES role(id) ON DELETE CASCADE ON UPDATE CASCADE,
  active SMALLINT DEFAULT '1',
  status SMALLINT  DEFAULT NULL,
  "positionId" INT DEFAULT NULL,
  data text,
  token varchar(100) DEFAULT NULL,
  "createdAt" timestamp,
  "updatedAt" timestamp without time zone NOT NULL,
  "createdBy" INT DEFAULT NULL,
  "updatedBy" INT DEFAULT NULL,
  lastLogin timestamp without time zone,
  "forgotPassword" varchar(100) DEFAULT NULL,
  PRIMARY KEY (id)
);

ALTER TABLE "user" ADD CONSTRAINT user_superior FOREIGN KEY (superior) REFERENCES "user"(id);

COMMENT ON COLUMN "user".active IS 'module_switch_Inactive_Active';
COMMENT ON COLUMN "user".status IS 'dropdown_static_1=Internal,2=External';


CREATE TABLE public.country (
    id SERIAL PRIMARY KEY,
    code varchar(4),
    name varchar(50),
    continent_name varchar(20),
    created_at timestamp,
    updated_at timestamp,
    modified_by integer,
    version character varying
);

CREATE TABLE public.city (
    id SERIAL PRIMARY KEY,
    code varchar(50),
    country_id integer REFERENCES country(id) ON DELETE CASCADE ON UPDATE CASCADE,
     name varchar(50),
     created_at timestamp,
    updated_at timestamp,
    modified_by integer,
    version varchar(20)
);

pg_dump -U postgres -d org_chart_approval > org.backup

pg_restore -U postgres -d pret org.backup

psql -U postgres -d cmqu < org.backup