473 lines
14 KiB
PL/PgSQL
473 lines
14 KiB
PL/PgSQL
--
|
|
-- PostgreSQL database dump
|
|
--
|
|
|
|
\restrict o9C3USTSL3jJ8pVlC2zBtApXvhMeLlwZwN0SqSM7Q8Md3uBEd4kJskn9cygKcpX
|
|
|
|
-- Dumped from database version 18.3
|
|
-- Dumped by pg_dump version 18.3
|
|
|
|
-- Started on 2026-03-17 21:46:48
|
|
|
|
SET statement_timeout = 0;
|
|
SET lock_timeout = 0;
|
|
SET idle_in_transaction_session_timeout = 0;
|
|
SET transaction_timeout = 0;
|
|
SET client_encoding = 'UTF8';
|
|
SET standard_conforming_strings = on;
|
|
SELECT pg_catalog.set_config('search_path', '', false);
|
|
SET check_function_bodies = false;
|
|
SET xmloption = content;
|
|
SET client_min_messages = warning;
|
|
SET row_security = off;
|
|
|
|
--
|
|
-- TOC entry 227 (class 1255 OID 24737)
|
|
-- Name: get_available_courses(); Type: FUNCTION; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE FUNCTION public.get_available_courses() RETURNS TABLE(courseid integer, name character varying, startdate date, enddate date, trainingmode character varying, rate numeric)
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
c.courseid,
|
|
c.name,
|
|
c.startdate,
|
|
c.enddate,
|
|
c.trainingmode,
|
|
c.rate
|
|
FROM course c;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.get_available_courses() OWNER TO postgres;
|
|
|
|
SET default_tablespace = '';
|
|
|
|
SET default_table_access_method = heap;
|
|
|
|
--
|
|
-- TOC entry 222 (class 1259 OID 24639)
|
|
-- Name: course; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.course (
|
|
courseid integer NOT NULL,
|
|
name character varying(150) NOT NULL,
|
|
startdate date NOT NULL,
|
|
enddate date NOT NULL,
|
|
trainingmode character varying(20) NOT NULL,
|
|
rate numeric(10,2) NOT NULL,
|
|
noteid integer,
|
|
tutorid integer,
|
|
createddate timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
createdby character varying(100) NOT NULL,
|
|
lastupdatedby character varying(100),
|
|
lastupdateddate timestamp without time zone
|
|
);
|
|
|
|
|
|
ALTER TABLE public.course OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 221 (class 1259 OID 24638)
|
|
-- Name: course_courseid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.course_courseid_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.course_courseid_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 4954 (class 0 OID 0)
|
|
-- Dependencies: 221
|
|
-- Name: course_courseid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.course_courseid_seq OWNED BY public.course.courseid;
|
|
|
|
|
|
--
|
|
-- TOC entry 220 (class 1259 OID 24624)
|
|
-- Name: entitynotes; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.entitynotes (
|
|
noteid integer NOT NULL,
|
|
entitytype character varying(50) NOT NULL,
|
|
notes text NOT NULL,
|
|
createddate timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
createdby character varying(100) NOT NULL,
|
|
inactivateddate timestamp without time zone,
|
|
inactivatedby character varying(100)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.entitynotes OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 219 (class 1259 OID 24623)
|
|
-- Name: entitynotes_noteid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.entitynotes_noteid_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.entitynotes_noteid_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 4955 (class 0 OID 0)
|
|
-- Dependencies: 219
|
|
-- Name: entitynotes_noteid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.entitynotes_noteid_seq OWNED BY public.entitynotes.noteid;
|
|
|
|
|
|
--
|
|
-- TOC entry 224 (class 1259 OID 24660)
|
|
-- Name: student; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.student (
|
|
studentid integer NOT NULL,
|
|
name character varying(150) NOT NULL,
|
|
emailaddress character varying(150) NOT NULL,
|
|
phoneno character varying(15) NOT NULL,
|
|
studentstatus character varying(20) NOT NULL,
|
|
noteid integer,
|
|
createddate timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
createdby character varying(100) NOT NULL,
|
|
lastupdatedby character varying(100),
|
|
lastupdateddate timestamp without time zone,
|
|
password character varying(255)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.student OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 223 (class 1259 OID 24659)
|
|
-- Name: student_studentid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.student_studentid_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.student_studentid_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 4956 (class 0 OID 0)
|
|
-- Dependencies: 223
|
|
-- Name: student_studentid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.student_studentid_seq OWNED BY public.student.studentid;
|
|
|
|
|
|
--
|
|
-- TOC entry 226 (class 1259 OID 24684)
|
|
-- Name: studentcourse; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.studentcourse (
|
|
studentcourseid integer NOT NULL,
|
|
studentid integer NOT NULL,
|
|
courseid integer NOT NULL,
|
|
paymentstatus character varying(20) NOT NULL,
|
|
paiddate date,
|
|
noteid integer,
|
|
createddate timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
createdby character varying(100) NOT NULL,
|
|
lastupdatedby character varying(100),
|
|
lastupdateddate timestamp without time zone
|
|
);
|
|
|
|
|
|
ALTER TABLE public.studentcourse OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 225 (class 1259 OID 24683)
|
|
-- Name: studentcourse_studentcourseid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.studentcourse_studentcourseid_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.studentcourse_studentcourseid_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- TOC entry 4957 (class 0 OID 0)
|
|
-- Dependencies: 225
|
|
-- Name: studentcourse_studentcourseid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.studentcourse_studentcourseid_seq OWNED BY public.studentcourse.studentcourseid;
|
|
|
|
|
|
--
|
|
-- TOC entry 4773 (class 2604 OID 24642)
|
|
-- Name: course courseid; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.course ALTER COLUMN courseid SET DEFAULT nextval('public.course_courseid_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- TOC entry 4771 (class 2604 OID 24627)
|
|
-- Name: entitynotes noteid; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.entitynotes ALTER COLUMN noteid SET DEFAULT nextval('public.entitynotes_noteid_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- TOC entry 4775 (class 2604 OID 24663)
|
|
-- Name: student studentid; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.student ALTER COLUMN studentid SET DEFAULT nextval('public.student_studentid_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- TOC entry 4777 (class 2604 OID 24687)
|
|
-- Name: studentcourse studentcourseid; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.studentcourse ALTER COLUMN studentcourseid SET DEFAULT nextval('public.studentcourse_studentcourseid_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- TOC entry 4944 (class 0 OID 24639)
|
|
-- Dependencies: 222
|
|
-- Data for Name: course; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.course (courseid, name, startdate, enddate, trainingmode, rate, noteid, tutorid, createddate, createdby, lastupdatedby, lastupdateddate) FROM stdin;
|
|
1 Angular Development 2026-04-01 2026-05-15 Online 12000.00 1 \N 2026-03-13 01:46:48.445682 Admin \N \N
|
|
2 Java Full Stack 2026-04-10 2026-06-10 Hybrid 18000.00 2 \N 2026-03-13 01:46:48.445682 Admin \N \N
|
|
3 Python for Data Science 2026-04-20 2026-06-01 Online 15000.00 3 \N 2026-03-13 01:46:48.445682 Admin \N \N
|
|
4 Android App Development 2026-05-01 2026-06-20 In-person 17000.00 4 \N 2026-03-13 01:46:48.445682 Admin \N \N
|
|
\.
|
|
|
|
|
|
--
|
|
-- TOC entry 4942 (class 0 OID 24624)
|
|
-- Dependencies: 220
|
|
-- Data for Name: entitynotes; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.entitynotes (noteid, entitytype, notes, createddate, createdby, inactivateddate, inactivatedby) FROM stdin;
|
|
1 Student Interested in Angular and Java courses 2026-03-13 00:52:54.927 System \N \N
|
|
2 Course Angular course for frontend development 2026-03-13 01:44:49.098183 Admin \N \N
|
|
3 Course Java full stack training program 2026-03-13 01:44:49.098183 Admin \N \N
|
|
4 Course Python data science training 2026-03-13 01:44:49.098183 Admin \N \N
|
|
5 Course Android mobile app development course 2026-03-13 01:44:49.098183 Admin \N \N
|
|
6 StudentCourse Student selected courses 2026-03-13 12:56:42.295188 System \N \N
|
|
7 StudentCourse Student selected courses 2026-03-13 13:23:17.52 System \N \N
|
|
8 Student Interested in Java and Python 2026-03-14 17:08:32.144 System \N \N
|
|
9 StudentCourse Student selected courses 2026-03-14 17:39:53.096 System \N \N
|
|
10 StudentCourse Student selected courses 2026-03-14 18:54:00.499 System \N \N
|
|
11 StudentCourse Student selected courses 2026-03-14 19:42:48.048 System \N \N
|
|
12 Student Interested in Android development 2026-03-14 20:30:22.797 System \N \N
|
|
13 StudentCourse Student selected courses 2026-03-14 20:38:53.812 System \N \N
|
|
14 Student Interested in angular 2026-03-14 21:58:12.739 System \N \N
|
|
15 StudentCourse Student selected courses 2026-03-14 22:02:27.457 System \N \N
|
|
\.
|
|
|
|
|
|
--
|
|
-- TOC entry 4946 (class 0 OID 24660)
|
|
-- Dependencies: 224
|
|
-- Data for Name: student; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.student (studentid, name, emailaddress, phoneno, studentstatus, noteid, createddate, createdby, lastupdatedby, lastupdateddate, password) FROM stdin;
|
|
1 Nithiya 2003nithi@gmail.com 9655449914 Active 1 2026-03-13 00:52:56.453 System System 2026-03-13 01:14:37.326 $2b$10$vl098H2s9vp8Nlvo6C9hbeOfbzHhwiK3aMcaQg86okcI3FnNOzbUO
|
|
2 Yuvan 2008yuvanyuvi@gmail.com 9791355044 Active 8 2026-03-14 17:08:32.854 System System 2026-03-14 17:10:19.652 $2b$10$aOpNjenv4WYR9CbjdDlDDekj8fBlanwaEkQonDjnhpdsmkCDEZM3S
|
|
3 Sanjay 2003sanjaysan@gmail.com 7358863770 Active 12 2026-03-14 20:30:24.223 System System 2026-03-14 20:31:18.382 $2b$10$XMSAl3ZqwAByp0dHiK.u9eAXNi3c31hlZIeJ/pkeDPNYjFQ/7BFvG
|
|
4 Anitha anitha@gmail.com 9791355033 Active 14 2026-03-14 21:58:13.601 System System 2026-03-14 21:59:06.81 $2b$10$SJeXP5TnTaGCvObT/o40O.qO1VBj6RturXvR9ybSL2t1jZFzSQF5i
|
|
\.
|
|
|
|
|
|
--
|
|
-- TOC entry 4948 (class 0 OID 24684)
|
|
-- Dependencies: 226
|
|
-- Data for Name: studentcourse; Type: TABLE DATA; Schema: public; Owner: postgres
|
|
--
|
|
|
|
COPY public.studentcourse (studentcourseid, studentid, courseid, paymentstatus, paiddate, noteid, createddate, createdby, lastupdatedby, lastupdateddate) FROM stdin;
|
|
1 1 1 Paid 2026-03-14 7 2026-03-13 13:23:18.423 System System 2026-03-14 00:02:49.530382
|
|
2 1 2 Paid 2026-03-14 7 2026-03-13 13:23:19.112 System System 2026-03-14 00:02:49.530382
|
|
3 1 3 Paid 2026-03-14 7 2026-03-13 13:23:19.196 System System 2026-03-14 00:02:49.530382
|
|
5 2 1 Paid 2026-03-14 11 2026-03-14 19:42:49.537 System System 2026-03-14 19:44:08.845971
|
|
6 2 2 Paid 2026-03-14 11 2026-03-14 19:42:50.44 System System 2026-03-14 19:44:08.845971
|
|
7 3 4 Paid 2026-03-14 13 2026-03-14 20:38:53.969 System System 2026-03-14 20:40:27.583084
|
|
8 4 2 Paid 2026-03-14 15 2026-03-14 22:02:27.917 System System 2026-03-14 22:03:52.024094
|
|
9 4 4 Paid 2026-03-14 15 2026-03-14 22:02:27.967 System System 2026-03-14 22:03:52.024094
|
|
\.
|
|
|
|
|
|
--
|
|
-- TOC entry 4958 (class 0 OID 0)
|
|
-- Dependencies: 221
|
|
-- Name: course_courseid_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.course_courseid_seq', 4, true);
|
|
|
|
|
|
--
|
|
-- TOC entry 4959 (class 0 OID 0)
|
|
-- Dependencies: 219
|
|
-- Name: entitynotes_noteid_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.entitynotes_noteid_seq', 15, true);
|
|
|
|
|
|
--
|
|
-- TOC entry 4960 (class 0 OID 0)
|
|
-- Dependencies: 223
|
|
-- Name: student_studentid_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.student_studentid_seq', 4, true);
|
|
|
|
|
|
--
|
|
-- TOC entry 4961 (class 0 OID 0)
|
|
-- Dependencies: 225
|
|
-- Name: studentcourse_studentcourseid_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
|
--
|
|
|
|
SELECT pg_catalog.setval('public.studentcourse_studentcourseid_seq', 9, true);
|
|
|
|
|
|
--
|
|
-- TOC entry 4782 (class 2606 OID 24653)
|
|
-- Name: course course_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.course
|
|
ADD CONSTRAINT course_pkey PRIMARY KEY (courseid);
|
|
|
|
|
|
--
|
|
-- TOC entry 4780 (class 2606 OID 24637)
|
|
-- Name: entitynotes entitynotes_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.entitynotes
|
|
ADD CONSTRAINT entitynotes_pkey PRIMARY KEY (noteid);
|
|
|
|
|
|
--
|
|
-- TOC entry 4784 (class 2606 OID 24677)
|
|
-- Name: student student_emailaddress_key; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.student
|
|
ADD CONSTRAINT student_emailaddress_key UNIQUE (emailaddress);
|
|
|
|
|
|
--
|
|
-- TOC entry 4786 (class 2606 OID 24675)
|
|
-- Name: student student_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.student
|
|
ADD CONSTRAINT student_pkey PRIMARY KEY (studentid);
|
|
|
|
|
|
--
|
|
-- TOC entry 4788 (class 2606 OID 24696)
|
|
-- Name: studentcourse studentcourse_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.studentcourse
|
|
ADD CONSTRAINT studentcourse_pkey PRIMARY KEY (studentcourseid);
|
|
|
|
|
|
--
|
|
-- TOC entry 4789 (class 2606 OID 24654)
|
|
-- Name: course fk_course_note; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.course
|
|
ADD CONSTRAINT fk_course_note FOREIGN KEY (noteid) REFERENCES public.entitynotes(noteid);
|
|
|
|
|
|
--
|
|
-- TOC entry 4790 (class 2606 OID 24678)
|
|
-- Name: student fk_student_note; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.student
|
|
ADD CONSTRAINT fk_student_note FOREIGN KEY (noteid) REFERENCES public.entitynotes(noteid);
|
|
|
|
|
|
--
|
|
-- TOC entry 4791 (class 2606 OID 24702)
|
|
-- Name: studentcourse fk_studentcourse_course; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.studentcourse
|
|
ADD CONSTRAINT fk_studentcourse_course FOREIGN KEY (courseid) REFERENCES public.course(courseid);
|
|
|
|
|
|
--
|
|
-- TOC entry 4792 (class 2606 OID 24707)
|
|
-- Name: studentcourse fk_studentcourse_note; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.studentcourse
|
|
ADD CONSTRAINT fk_studentcourse_note FOREIGN KEY (noteid) REFERENCES public.entitynotes(noteid);
|
|
|
|
|
|
--
|
|
-- TOC entry 4793 (class 2606 OID 24697)
|
|
-- Name: studentcourse fk_studentcourse_student; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.studentcourse
|
|
ADD CONSTRAINT fk_studentcourse_student FOREIGN KEY (studentid) REFERENCES public.student(studentid);
|
|
|
|
|
|
-- Completed on 2026-03-17 21:46:49
|
|
|
|
--
|
|
-- PostgreSQL database dump complete
|
|
--
|
|
|
|
\unrestrict o9C3USTSL3jJ8pVlC2zBtApXvhMeLlwZwN0SqSM7Q8Md3uBEd4kJskn9cygKcpX
|
|
|