class Profile():
def __init__(self):
self.con = Connector()
def createTable(self):
self.con.create("""
CREATE TABLE IF NOT EXISTS Profile(
user_id INT,
firstname VARCHAR(50) NOT NULL ,
lastname VARCHAR(50) ,
gender VARCHAR(1) ,
avatar VARCHAR(200) NOT NULL ,
PRIMARY KEY (user_id),
UNIQUE (firstname, lastname, avatar),
FOREIGN KEY (user_id) REFERENCES User (user_id)
);
""")
def createTriggers(self):
pass
def getAllVideosOfUser(self, user_id):
return self.con.query(
"""
SELECT * FROM Video WHERE user_id = %d;
""", user_id)
def createProfile(self, data, user_id):
res = self.con.query(
"""
INSERT INTO Profile(user_id,firstname,lastname,gender,avatar) VALUES (%s,%s,%s,%s,%s)
""", user_id, data['firstname'], data['lastname'], data['gender'],
"Please change it")
return res
class Playlist():
def __init__(self):
self.con = Connector()
def createTriggers(self):
self.con.create("""
Drop TRIGGER IF EXISTS playlistDeleteTrigger;
DELIMITER //
CREATE TRIGGER playlistDeleteTrigger
BEFORE DELETE ON Playlist
FOR EACH ROW
BEGIN
DELETE FROM Pl_Vid WHERE OLD.playlist_id = playlist_id;
END;//
DELIMITER ;
""")
def createTable(self):
self.con.create("""
CREATE TABLE IF NOT EXISTS Playlist(
playlist_id INT AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
channel_id INT,
PRIMARY KEY(playlist_id),
FOREIGN KEY(channel_id) REFERENCES Channel (channel_id)
);
""")
class Channel():
def __init__(self):
self.con = Connector()
def createTriggers(self):
self.con.create("""
Drop TRIGGER IF EXISTS channelDeleteTrigger;
DELIMITER //
CREATE TRIGGER channelDeleteTrigger
BEFORE DELETE ON Channel
FOR EACH ROW
BEGIN
DELETE FROM Playlist WHERE OLD.channel_id = channel_id;
DELETE FROM Subscription WHERE OLD.channel_id = channel_id;
END//
DELIMITER ;
""")
def createTable(self):
self.con.create("""
CREATE TABLE IF NOT EXISTS Channel(
channel_id INTEGER AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
description VARCHAR(1000) NOT NULL,
PRIMARY KEY(channel_id),
user_id INT,
FOREIGN KEY(user_id) REFERENCES User(user_id)
);
""")
class Relationships():
def __init__(self):
#initiates the database Connector
self.con = Connector()
def create_UsersProfile(self):
self.con.create("""
CREATE VIEW User_Profile AS
SELECT * FROM User NATURAL JOIN Profile;
""")
def create_Vid_Cat(self):
self.con.create("""
CREATE TABLE IF NOT EXISTS Vid_Cat(
video_id INT,
cat_id INT,
FOREIGN KEY(video_id) REFERENCES Video(video_id),
FOREIGN KEY(cat_id) REFERENCES Category(cat_id)
);
""")
def create_Subscription(self):
self.con.create("""
CREATE TABLE IF NOT EXISTS Subscription(
user_id INT,
channel_id INT,
FOREIGN KEY(user_id) REFERENCES User(user_id),
FOREIGN KEY(channel_id) REFERENCES Channel(channel_id)
);
""")
def create_Like(self):
self.con.create("""
CREATE TABLE IF NOT EXISTS `Like`(
video_id INT,
user_id INT,
FOREIGN KEY(video_id) REFERENCES Video(video_id),
FOREIGN KEY(user_id) REFERENCES User(user_id)
);
""")
def create_Pl_Vid(self):
self.con.create("""
CREATE TABLE IF NOT EXISTS Pl_Vid(
video_id INT,
playlist_id INT,
UNIQUE(playlist_id,video_id),
FOREIGN KEY(video_id) REFERENCES Video(video_id),
FOREIGN KEY(playlist_id) REFERENCES Playlist(playlist_id)
);
""")
class Comment():
def __init__(self):
self.con = Connector()
def createTriggers(self):
pass
def createTable(self):
self.con.query("""
CREATE TABLE IF NOT EXISTS Comment(
comment_id INTEGER NOT NULL AUTO_INCREMENT KEY ,
text VARCHAR(2000),
timestamp TIMESTAMP NOT NULL,
video_id INT,
user_id INT,
FOREIGN KEY(video_id) REFERENCES Video(video_id),
FOREIGN KEY(user_id) REFERENCES User(user_id)
);
""")
class Tag():
def __init__(self):
self.con = Connector()
def createTable(self):
self.con.create("""
CREATE TABLE IF NOT EXISTS Tag(
video_id INT,
tag VARCHAR(200) NOT NULL,
PRIMARY KEY (video_id,tag),
FOREIGN KEY (video_id) REFERENCES Video(video_id)
);
""")
def createTriggers(self):
pass
def create_Procs(self):
self.con.create("""
CREATE PROCEDURE insertTag( IN tag VARCHAR(100), IN video_id INT )
BEGIN
INSERT INTO Tag(video_id, tag) VALUES(video_id, tag) ;
END
""")
def insertTags(self, tags, video_id):
tags = tags.split(',')
for tag in tags:
self.con.callProc('insertTag', tag, video_id)
class Category():
def __init__(self):
self.con = Connector()
def createTable(self):
self.con.create("""
CREATE TABLE IF NOT EXISTS Category(
cat_id INTEGER AUTO_INCREMENT,
text VARCHAR(200) NOT NULL,
PRIMARY KEY(cat_id)
);
""")
def trimVidDesc(self,s):
return (' '.join(s.split(' ')[:35])) + "..."
def createTriggers(self):
pass
def insert(self, data):
self.con.modify("""
INSERT INTO Category(text)
VALUES(%s);
""",data["text"])
def getall(self):
return self.con.query("SELECT * FROM Category;")
def get_CatVideos(self,catid):
res = self.con.query("""
SELECT Video.*,Category.text FROM Video,Category,Vid_Cat
WHERE Video.video_id=Vid_Cat.Video_id
AND Vid_Cat. cat_id=Category.cat_id AND Category.cat_id=%s
;""",catid)
for i in range(len(res)) :
res[i]['descr'] = self.trimVidDesc(res[i]['descr'])
return res
def __init__(self):
self.con = Connector()
class User():
def __init__(self):
self.con = Connector()
def createTable(self):
self.con.create("""
CREATE TABLE IF NOT EXISTS User (
user_id INTEGER AUTO_INCREMENT ,
username VARCHAR(50) NOT NULL UNIQUE ,
email VARCHAR(50) NOT NULL UNIQUE ,
password VARCHAR(200) NOT NULL ,
PRIMARY KEY (user_id),
UNIQUE (username, email, password)
);
""")
def createTriggers(self):
pass
# returns user_id for a user data
def getUserId(self, data):
res = self.con.query(
"""
SELECT * FROM `User` WHERE email = %s
""", data['email'])
return res[0]['user_id']
#creates entry in USers and PRofile table
def createUser(self, data):
prf = Profile()
#Creates Entry in users table
res = self.insertUser(data)
if res == None:
# get's the user_id
user_id = self.getUserId(data)
#Creates Entry in profile table
res1 = prf.createProfile(data, user_id)
return res1
else:
return res
def signInUser(self, data):
res = self.con.query(
"""
SELECT * FROM `User` WHERE email = %s and password = %s
""", data['email'], data['password'])
if (len(res) == 1):
return True
else:
return False
def insertUser(self, data):
res = self.con.modify(
"""
INSERT INTO User(email,username,password) VALUES (%s,%s,%s)
""", data['email'], data['username'], data['password'])
# returns None if there's no error
if res == None:
return None
else:
return res
# -*- coding: utf-8 -*-
from __future__ import unicode_literals
from django.shortcuts import render, redirect
from django.views import View
from django.http import JsonResponse
from ORM.user import User
from ORM.dbconnect import Connector
import datetime
import hashlib
from ORM.sessions import SessionsManager
# Create your views here.
con = Connector()
class SignUp(View):
def get(self, request):
return render(request, 'Auth/signup.html')
def post(self, request):
user = User()
data = {
'firstname':
request.POST['firstname'].strip(),
'lastname':
request.POST['lastname'].strip(),
'password':
str(
hashlib.md5(
request.POST['password'].strip().encode()).hexdigest()),
def __init__(self):
#initiates the database Connector
self.con = Connector()
class Video():
def __init__(self):
#initiates the database Connector
self.con = Connector()
def createTable(self):
self.con.create("""
CREATE TABLE IF NOT EXISTS Video (
video_id INTEGER AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
descr TEXT(1000),
url VARCHAR(200) NOT NULL,
user_id INT NOT NULL,
PRIMARY KEY (video_id),
UNIQUE (user_id, url, title),
FOREIGN KEY (user_id) REFERENCES User (user_id)
);
""")
def createTriggers(self):
self.con.create("""
Drop TRIGGER IF EXISTS videoDeleteTrigger;
DELIMITER //
CREATE TRIGGER videoDeleteTrigger
BEFORE DELETE ON Video
FOR EACH ROW
BEGIN
DELETE FROM Tag WHERE OLD.video_id = video_id;
DELETE FROM Vid_Cat WHERE OLD.video_id = video_id;
DELETE FROM Pl_Vid WHERE OLD.video_id = video_id;
DELETE FROM Comment WHERE OLD.video_id = video_id;
DELETE FROM `Like` WHERE OLD.video_id = video_id;
END;//
DELIMITER ;
""")
def insert(self, data):
self.con.modify(
"""
INSERT INTO Video(title, descr, url, user_id) VALUES (%s,%s,%s,%s)
""", data['title'], data['descr'], data['url'],
int(data['user_id']))
def update(self, data):
self.con.modify(
"""
UPDATE Video SET title ="%s",descr ="%s",url ="%s",user_id ="%d"
WHERE video_id = %d;
""", data['video_id'], data['title'], data['descr'], data['url'],
data['user_id'])
def remove(self, data):
self.con.modify(
"""
DELETE FROM Video WHERE video_id = %d
""", data['video_id'])
def getallvideos(self):
return self.con.query("SELECT * FROM Video;")
def get_vid_id(self, data):
return self.con.query("SELECT video_id FROM Video WHERE url=%s",
data['url'])
def get_like(self, video_id, uid):
res = self.con.query(
"SELECT * FROM `Like` WHERE video_id = %s AND user_id=%s",
int(video_id), int(uid))
if (len(res) > 0):
return True
else:
return False