CHIEFSOFT\ameye
350 lines
10 KiB
SQL
350 lines
10 KiB
SQL
CREATE TABLE password_reset (
|
|
id SERIAL,
|
|
uid uuid DEFAULT uuid_generate_v4(),
|
|
username VARCHAR(35) NOT NULL ,
|
|
status INT DEFAULT 0,
|
|
added timestamp without time zone DEFAULT now(),
|
|
updated timestamp without time zone DEFAULT now()
|
|
);
|
|
ALTER TABLE ONLY password_reset
|
|
ADD CONSTRAINT password_reset_id_key UNIQUE (id);
|
|
|
|
|
|
CREATE TABLE members_pending (
|
|
id SERIAL,
|
|
uid uuid DEFAULT uuid_generate_v4(),
|
|
firstname VARCHAR(35) NOT NULL,
|
|
lastname VARCHAR(35) NOT NULL,
|
|
email VARCHAR(35) NOT NULL ,
|
|
status INT DEFAULT 0,
|
|
added timestamp without time zone DEFAULT now(),
|
|
updated timestamp without time zone DEFAULT now()
|
|
);
|
|
ALTER TABLE ONLY members_pending
|
|
ADD CONSTRAINT members_pending_id_key UNIQUE (id);
|
|
|
|
|
|
|
|
CREATE TABLE members (
|
|
id SERIAL,
|
|
uid uuid DEFAULT uuid_generate_v4(),
|
|
username VARCHAR(25) UNIQUE NOT NULL,
|
|
password VARCHAR(100) NOT NULL,
|
|
loc INET,
|
|
status INT DEFAULT 0,
|
|
added timestamp without time zone DEFAULT now(),
|
|
updated timestamp without time zone DEFAULT now()
|
|
);
|
|
ALTER TABLE ONLY members
|
|
ADD CONSTRAINT members_id_key UNIQUE (id);
|
|
|
|
ALTER TABLE members ADD email VARCHAR(100);
|
|
ALTER TABLE members ADD account_name VARCHAR(100);
|
|
ALTER TABLE members ADD firstname VARCHAR(25);
|
|
ALTER TABLE members ADD lastname VARCHAR(25);
|
|
|
|
-- UPDATE members SET account_name ='This is the account name';
|
|
-- UPDATE members SET firstname ='Firstname';
|
|
-- UPDATE members SET lastname ='Lastname';
|
|
|
|
|
|
CREATE TABLE members_session(
|
|
id SERIAL,
|
|
uid uuid DEFAULT uuid_generate_v4(),
|
|
member_id INT REFERENCES members(id),
|
|
session_id VARCHAR(100) UNIQUE NOT NULL,
|
|
loc INET,
|
|
status INT DEFAULT 0,
|
|
added timestamp without time zone DEFAULT now(),
|
|
updated timestamp without time zone DEFAULT now()
|
|
);
|
|
|
|
ALTER TABLE ONLY members_session
|
|
ADD CONSTRAINT members_session_id_key UNIQUE (id);
|
|
|
|
|
|
|
|
CREATE TABLE products (
|
|
id SERIAL,
|
|
uid uuid DEFAULT uuid_generate_v4(),
|
|
product_id VARCHAR(25) UNIQUE NOT NULL,
|
|
name VARCHAR(100) UNIQUE NOT NULL,
|
|
description VARCHAR(250) NOT NULL,
|
|
status INT DEFAULT 0,
|
|
|
|
added timestamp without time zone DEFAULT now(),
|
|
updated timestamp without time zone DEFAULT now()
|
|
);
|
|
ALTER TABLE ONLY products
|
|
ADD CONSTRAINT products_id_key UNIQUE (id);
|
|
|
|
ALTER TABLE products ADD banner VARCHAR(100);
|
|
|
|
|
|
CREATE TABLE members_products (
|
|
id SERIAL,
|
|
uid uuid DEFAULT uuid_generate_v4(),
|
|
member_id INT REFERENCES members(id),
|
|
product_id VARCHAR(25) REFERENCES products(product_id),
|
|
internal_url VARCHAR(100) UNIQUE NOT NULL,
|
|
external_url VARCHAR(100),
|
|
dns_group VARCHAR(20),
|
|
status INT DEFAULT 0,
|
|
added timestamp without time zone DEFAULT now(),
|
|
updated timestamp without time zone DEFAULT now()
|
|
);
|
|
ALTER TABLE ONLY members_products
|
|
ADD CONSTRAINT members_products_id_key UNIQUE (id);
|
|
|
|
ALTER TABLE members_products
|
|
ADD CONSTRAINT members_products_unique_member_key
|
|
UNIQUE (member_id, product_id);
|
|
|
|
|
|
|
|
INSERT INTO merms_products (name,description,status,product_id, banner) VALUES ('Personal Website','Your personal professional web presence',1,'A000001', 'banner.jpg');
|
|
INSERT INTO merms_products (name,description,status,product_id, banner) VALUES ('Professional Website','Your healthcare practice online presence ',1,'A000002', 'banner.jpg');
|
|
INSERT INTO merms_products (name,description,status,product_id, banner) VALUES ('Personal Blog','Blog to share your health care view',1,'A000003', 'banner.jpg');
|
|
INSERT INTO merms_products (name,description,status,product_id, banner) VALUES ('Professional Blog','Booster your practice with engaging contents',1,'A000004', 'banner.jpg');
|
|
INSERT INTO merms_products (name,description,status,product_id, banner) VALUES ('Practice EMR','Get Open EMR for practice management',1,'A000005', 'banner.jpg');
|
|
|
|
INSERT INTO products (name,description,status,product_id, banner) VALUES ('Personal Website','Your personal professional web presence',1,'A000001', 'banner.jpg');
|
|
INSERT INTO products (name,description,status,product_id, banner) VALUES ('Professional Website','Your healthcare practice online presence ',1,'A000002', 'banner.jpg');
|
|
INSERT INTO products (name,description,status,product_id, banner) VALUES ('Personal Blog','Blog to share your health care view',1,'A000003', 'banner.jpg');
|
|
INSERT INTO products (name,description,status,product_id, banner) VALUES ('Professional Blog','Booster your practice with engaging contents',1,'A000004', 'banner.jpg');
|
|
INSERT INTO products (name,description,status,product_id, banner) VALUES ('Practice EMR','Get Open EMR for practice management',1,'A000005', 'banner.jpg');
|
|
|
|
|
|
|
|
|
|
|
|
|
|
CREATE TABLE provision_plans (
|
|
id SERIAL,
|
|
uid uuid DEFAULT uuid_generate_v4(),
|
|
provision_id INT REFERENCES members_products(id),
|
|
play_file VARCHAR(100)UNIQUE NOT NULL,
|
|
msg VARCHAR(100) ,
|
|
added timestamp without time zone DEFAULT now(),
|
|
updated timestamp without time zone DEFAULT now()
|
|
);
|
|
ALTER TABLE ONLY provision_plans
|
|
ADD CONSTRAINT provision_plans_id_key UNIQUE (id);
|
|
|
|
|
|
|
|
CREATE TABLE members_external_links (
|
|
id SERIAL,
|
|
uid uuid DEFAULT uuid_generate_v4(),
|
|
member_id INT REFERENCES members(id),
|
|
member_uid VARCHAR(100) NOT NULL,
|
|
facebook VARCHAR(125),
|
|
google VARCHAR(125),
|
|
linkedin VARCHAR(125),
|
|
other_web VARCHAR(125),
|
|
twitter VARCHAR(125),
|
|
added timestamp without time zone DEFAULT now()
|
|
);
|
|
ALTER TABLE ONLY members_external_links
|
|
ADD CONSTRAINT members_external_links_id_key UNIQUE (id);
|
|
|
|
|
|
CREATE TABLE provision_actions (
|
|
id SERIAL,
|
|
uid uuid DEFAULT uuid_generate_v4(),
|
|
member_id INT REFERENCES members(id),
|
|
product_uid VARCHAR(100) NOT NULL,
|
|
action VARCHAR(100) NOT NULL,
|
|
added timestamp without time zone DEFAULT now()
|
|
);
|
|
ALTER TABLE ONLY provision_actions
|
|
ADD CONSTRAINT provision_actions_id_key UNIQUE (id);
|
|
|
|
{"id": "3", "action": "Verifying your product..." , "date": "10-10-2010 11:00 AM"},
|
|
{"id": "2", "action": "URL assigned - Progress on the URL " , "date": "10-10-2010 11:30 AM"},
|
|
{"id": "1", "action": "Initiating product creation" , "date": "10-12-2010 11:30 AM"},
|
|
|
|
|
|
|
|
CREATE TABLE members_actions (
|
|
id SERIAL,
|
|
uid uuid DEFAULT uuid_generate_v4(),
|
|
member_id INT REFERENCES members(id),
|
|
member_uid VARCHAR(100) NOT NULL,
|
|
action_label VARCHAR(35) NOT NULL,
|
|
action_name VARCHAR(100) NOT NULL,
|
|
status_description VARCHAR(25) NOT NULL,
|
|
status INT DEFAULT 0,
|
|
added timestamp without time zone DEFAULT now(),
|
|
updated timestamp without time zone DEFAULT now()
|
|
);
|
|
ALTER TABLE ONLY members_actions
|
|
ADD CONSTRAINT members_actions_id_key UNIQUE (id);
|
|
|
|
const insertQuery = 'INSERT INTO ss_subs(members_id, members_uid, action_label,action_name,status_description,status ) VALUES($1, $2, $3, $4, $5, %6)'
|
|
|
|
var Querydata = {
|
|
"members_id": req.query.uid,
|
|
"members_uid": req.query.member_id,
|
|
"action_label": 'processing',
|
|
"action_name": 'processing',
|
|
"status_description": req.query.page
|
|
"status": 0
|
|
};
|
|
await db.query(insertQuery, Querydata, (err, res) => {
|
|
if (err) {
|
|
console.log(err.stack)
|
|
} else {
|
|
console.log(res.rows[0])
|
|
|
|
}
|
|
|
|
|
|
{
|
|
"email": "ameye@chiefsoft.com",
|
|
"firstname": "Olusesan",
|
|
"lastname": "Ameye",
|
|
"isChecked": true
|
|
}
|
|
|
|
|
|
CREATE TABLE ttt (
|
|
id SERIAL,
|
|
uid uuid DEFAULT uuid_generate_v4(),
|
|
username VARCHAR(35) NOT NULL
|
|
);
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- up
|
|
|
|
ALTER TABLE myoldtable ADD COLUMN newcolumn TEXT;
|
|
ALTER TABLE myoldtable ADD CONSTRAINT myoldtable_oldcolumn_newcolumn_key UNIQUE (oldcolumn, newcolumn);
|
|
|
|
---
|
|
|
|
ALTER TABLE myoldtable DROP CONSTRAINT myoldtable_oldcolumn_newcolumn_key;
|
|
ALTER TABLE myoldtable DROP COLUMN newcolumn;
|
|
|
|
-- down
|
|
|
|
merms_panel=# \d products
|
|
Table "public.products"
|
|
Column | Type | Collation | Nullable | Default
|
|
-------------+-----------------------------+-----------+----------+--------------------------------------
|
|
id | integer | | not null | nextval('products_id_seq'::regclass)
|
|
uid | uuid | | | uuid_generate_v4()
|
|
product_id | character varying(25) | | not null |
|
|
name | character varying(100) | | not null |
|
|
description | character varying(250) | | not null |
|
|
status | integer | | | 0
|
|
added | timestamp without time zone | | | now()
|
|
updated | timestamp without time zone | | | now()
|
|
banner | character varying(100) | | |
|
|
Indexes:
|
|
"products_id_key" UNIQUE CONSTRAINT, btree (id)
|
|
"products_name_key" UNIQUE CONSTRAINT, btree (name)
|
|
"products_product_id_key" UNIQUE CONSTRAINT, btree (product_id)
|
|
|
|
|
|
|
|
CREATE TABLE products_actions (
|
|
id SERIAL,
|
|
uid uuid DEFAULT uuid_generate_v4(),
|
|
product_id VARCHAR(25) UNIQUE NOT NULL,
|
|
name VARCHAR(100) UNIQUE NOT NULL,
|
|
description VARCHAR(250) NOT NULL,
|
|
status INT DEFAULT 0,
|
|
|
|
added timestamp without time zone DEFAULT now(),
|
|
updated timestamp without time zone DEFAULT now()
|
|
);
|
|
ALTER TABLE ONLY products_actions
|
|
ADD CONSTRAINT products_actions_id_key UNIQUE (id);
|
|
|
|
|
|
|
|
"name" => "Professional Website",
|
|
"description" => "Your healthcare practice online presence ",
|
|
"status" => 0,
|
|
"product" => 'A000002'
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
INSERT INTO members (username,email,password)
|
|
VALUES
|
|
('testaccount','works@chiefsoft.com',md5('merms.user.panel'));
|
|
|
|
INSERT INTO members (username,email,password)
|
|
VALUES
|
|
('sanyaameye','works@chiefsoft.com',md5('merms.user.panel'));
|
|
|
|
INSERT INTO members (username,email,password)
|
|
VALUES
|
|
('lekanaderibigbe','works@chiefsoft.com',md5('merms.user.panel'));
|
|
|
|
INSERT INTO members (username,email,password)
|
|
VALUES
|
|
('kevkemchiro','works@chiefsoft.com',md5('merms.user.panel'));
|
|
|
|
INSERT INTO members (username,email,password)
|
|
VALUES
|
|
('icarementalhealth','works@chiefsoft.com',md5('merms.user.panel'));
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
su - postgres
|
|
createuser merms_panel
|
|
createdb merms_panel
|
|
|
|
psql template1 postgres
|
|
|
|
alter user merms_panel with encrypted password 'merms_panel';
|
|
grant all privileges on database merms_panel to merms_panel;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|