public Status createUsersTable()
{
string query = "create table users_tbl(" +
"user_id int primary key identity (1,1)," +
"user_first_name text ," +
"user_last_name text , " +
"user_email text ," +
"user_passsword text," +
"user_birth_date date," +
"user_gender int ," +
"user_profile_photo text," +
"user_posts_count int," +
"user_photos_count int," +
"user_connection_count int," +
"user_reputation int ," +
"user_current_location geography," +
"user_city text," +
"user_country text ," +
"user_job text , " +
"user_location_swich bit, " +
"account_is_private bit," +
"user_is_online bit ," +
"user_is_active bit," +
"notification_count int," +
"unread_messages_count int," +
"connection_request_count int)";
return(ConnectionInit.excuteQuery(query));
}
public Status createTriggerOnSenddingMessage()
{
string query = "create trigger OnSenddingMessage " +
"on chat_messages_tbl " +
"after insert " +
"as " +
"declare @chat_id int,@message_sender_id int,@message_id int " +
"set @message_id =(select message_id from inserted) " +
"set @message_sender_id =(select message_sender_id from chat_messages_tbl where message_id=@message_id) " +
"set @chat_id =(select chat_id from chat_messages_tbl where message_id=@message_id) " +
"update chats_tbl set chat_messages_count=chat_messages_count+1 " +
"where chat_id=@chat_id " +
"" +
"if(@message_sender_id = (select chat_sender_id from chats_tbl where chat_id=@chat_id)) " +
"begin " +
"update chats_tbl set receiver_not_read=receiver_not_read+1 " +
"where chat_id=@chat_id " +
"end " +
"" +
"else " +
"begin " +
"update chats_tbl set sender_not_read=sender_not_read+1 where chat_id=@chat_id " +
"end " +
"" +
"update chats_tbl set chat_removed_sender=0 " +
", chat_removed_receiver=0 " +
", chat_last_message_datetime=(select chat_messages_tbl.message_datetime from chat_messages_tbl where message_id=@message_id) " +
",chat_last_message_user_id=@message_sender_id ,chat_last_message =(select message_text from chat_messages_tbl where message_id=@message_id) " +
"where chat_id=@chat_id ";
return(ConnectionInit.excuteQuery(query));
}
public Status createLikesTable()
{
string query = "create table likes_tbl(" +
"like_id int primary key identity(1,1), " +
"user_id int references users_tbl(user_id), " +
"post_id int references posts_tbl(post_id)," +
"like_datetime datetime)";
return(ConnectionInit.excuteQuery(query));
}
public Status createNotificationTable()
{
string query = "create table notification_tbl(notification_id int primary key identity(1,1)," +
"user_id_sent_notification int references users_tbl(user_id)," +
"user_id_received_notification int references users_tbl(user_id)," +
"notification_type int references notifiaction_type_tbl(type_id)," +
"notification_datetime datetime)";
return(ConnectionInit.excuteQuery(query));
}
public Status createCommentsTable()
{
string query = "create table comments_tbl(" +
"comment_id int primary key identity(1,1)," +
"post_id int references posts_tbl(post_id)," +
"user_id int references users_tbl(user_id)," +
"comment_text NTEXT," +
"comment_date_time datetime)";
return(ConnectionInit.excuteQuery(query));
}
public static Status update_user_bio(String userid, String bio)
{
String query = "update users_tbl set user_bio=@bio where user_id=@userid";
SqlCommand command = new SqlCommand();
//parameters
command.Parameters.AddWithValue("@bio", bio);
command.Parameters.AddWithValue("@userid", userid);
return(ConnectionInit.go_to_insertingToDB(query, command));
}
public static Status update_fist_last_name(String userid, String firstname, String lastname)
{
String query = "update users_tbl set user_first_name=@first_name,user_last_name=@last_name where user_id=@userid";
SqlCommand command = new SqlCommand();
//parameters
command.Parameters.AddWithValue("@first_name", firstname);
command.Parameters.AddWithValue("@last_name", lastname);
command.Parameters.AddWithValue("@userid", userid);
return(ConnectionInit.go_to_insertingToDB(query, command));
}
public Status createLikesTrigger()
{
string query = "create trigger OnLike " +
"on likes_tbl " +
"after insert " +
"as " +
"declare @post_id int " +
"set @post_id=(select post_id from inserted) " +
"update posts_tbl set post_likes_count=post_likes_count+1 where post_id=@post_id";
return(ConnectionInit.excuteQuery(query));
}
public Status createMessagesTable()
{
string query = "create table chat_messages_tbl(" +
"message_id int primary key identity(1,1)," +
"chat_id int references chats_tbl(chat_id)," +
"message_sender_id int references users_tbl(user_id)," +
"message_text NTEXT," +
"message_datetime datetime)";
return(ConnectionInit.excuteQuery(query));
}
public Status createTriggerForAddingComment()
{
string query = "create trigger triggerAddComment " +
"on comments_tbl " +
"after insert " +
"as " +
"declare @post_id int " +
"set @post_id=(select post_id from inserted) " +
"update posts_tbl set post_comments_count=post_comments_count+1 where post_id=@post_id ";
return(ConnectionInit.excuteQuery(query));
}
public Status createRemoveDisLikeTrigger()
{
string query = "create trigger OnRemoveDisLike " +
"on dis_likes_tbl " +
"after delete " +
"as " +
"declare @post_id int " +
"set @post_id=(select post_id from deleted) " +
"update posts_tbl set post_dislike_count=post_dislike_count-1 where post_id=@post_id";
return(ConnectionInit.excuteQuery(query));
}
public Status createFriendsTable()
{
string query = "create table friends_tbl(" +
"request_id int primary key identity(1,1)," +
"sender_id int references users_tbl(user_id)," +
"receiver_id int references users_tbl(user_id)," +
"date_time_sent datetime ," +
"date_time_accepted datetime," +
"is_accepted bit)";
return(ConnectionInit.excuteQuery(query));
}
public Status createChatsTable()
{
string query = "create table chats_tbl(" +
"chat_id int primary key identity(1,1)," +
"chat_sender_id int references users_tbl(user_id)," +
"chat_receiver_id int references users_tbl(user_id)," +
"chat_datetime_created datetime ," +
"chat_messages_count int ," +
"chat_last_message_user_id int references users_tbl(user_id)," +
"chat_last_message NTEXT, " +
"chat_last_message_datetime datetime ," +
"chat_removed_sender bit," +
"chat_removed_receiver bit," +
"sender_not_read int ," +
"receiver_not_read int)";
return(ConnectionInit.excuteQuery(query));
}
public Status createPostsTable()
{
string query = "create table posts_tbl(" +
"post_id int primary key identity(1,1)," +
"user_id int references users_tbl(user_id)," +
"post_text NTEXT," +
"post_image_path text ," +
"post_date_time datetime," +
"post_type int, " +
"post_comments_count int ," +
"post_likes_count int ," +
"post_dislike_count int ," +
"post_reports_count int ," +
"post_share_count int ," +
"post_is_shared bit," +
"original_post_id int references posts_tbl(post_id) )";
return(ConnectionInit.excuteQuery(query));
}
public Status createNotificationTypeTable()
{
string query = "create table notifiaction_type_tbl(type_id int primary key identity (1,1),type_name text)";
return(ConnectionInit.excuteQuery(query));
}
