public static Permissions createPermissions(User user, DatabaseConnection db) { PreparedStatement preStmtUser = db.Prepare("SELECT action.name, content_id, allow FROM user_account_can_do_action, action WHERE action.id = user_account_can_do_action.action_id AND user_account_id = "+user.id); PreparedStatement preStmtGroup = db.Prepare("SELECT action.name, content_id, allow FROM action, user_group_can_do_action, (SELECT user_group_id FROM user_account_in_user_group WHERE user_account_id = " + user.id + ") userGroups WHERE action.id = user_group_can_do_action.action_id AND user_group_can_do_action.user_group_id = userGroups.user_group_id"); List<RestService.Entities.Action> actions = new List<RestService.Entities.Action>(); Console.WriteLine(preStmtUser.GetCmd().CommandText); SqlDataReader reader = db.Query(new Dictionary<string,string>(),preStmtUser); while (reader.Read()) { int contentId = int.Parse(reader.GetString(reader.GetOrdinal("content_id"))); string actionName = reader.GetString(reader.GetOrdinal("name")); bool allowed = reader.GetBoolean(reader.GetOrdinal("allow")); actions.Add(new Entities.Action(contentId,actionName,null, true)); } reader = db.Query(new Dictionary<string,string>(), preStmtGroup); while (reader.Read()) { int contentId = reader.GetInt32(reader.GetOrdinal("content_id")); string actionName = reader.GetString(reader.GetOrdinal("name")); bool allowed = reader.GetBoolean(reader.GetOrdinal("allow")); actions.Add(new Entities.Action(contentId, actionName, null, true)); } return new Permissions(actions.ToArray(), user); }
public static void insertMediaFile(DatabaseConnection db, Stream file, int id, Permissions per) { PreparedStatement stmt1 = db.Prepare("SELECT * FROM media WHERE id = " + id.ToString()); SqlDataReader reader = db.Query(null, stmt1); Media mediaMeta = null; while (reader.Read()) { int mediaId = reader.GetInt32(reader.GetOrdinal("id")); int mediaCategory = reader.GetInt32(reader.GetOrdinal("media_category_id")); int user = reader.GetInt32(reader.GetOrdinal("user_account_id")); string mediaFileLocation = reader.GetString(reader.GetOrdinal("file_location")); string title = reader.GetString(reader.GetOrdinal("title")); string description = reader.GetString(reader.GetOrdinal("description")); int mediaLength = reader.GetInt32(reader.GetOrdinal("minutes")); string format = reader.GetString(reader.GetOrdinal("format")); mediaMeta = new Media(mediaId, mediaCategory, user, mediaFileLocation, title, description, mediaLength, format); } reader.Close(); string fileLocation = @"C:\RentItServices\Rentit26\MediaFiles\" + mediaMeta.id.ToString(); System.IO.Directory.CreateDirectory(fileLocation); string fileDir = fileLocation + @"\" + mediaMeta.title + "." + mediaMeta.format; FileStream writer = new FileStream(fileDir, FileMode.Create, FileAccess.Write); byte[] bytes = new Byte[4096]; int bytesRead = 0; while ((bytesRead = file.Read(bytes, 0, bytes.Length)) != 0) { writer.Write(bytes, 0, bytesRead); } file.Close(); writer.Close(); string fileStream = "http://rentit.itu.dk/RentIt26/MediaFiles/" + mediaMeta.id.ToString() + "/" + mediaMeta.title + "." + mediaMeta.format; PreparedStatement stmt2 = db.Prepare("UPDATE media SET file_location = '"+fileStream+"' WHERE id = "+mediaMeta.id.ToString()); db.Command(null, stmt2); }
private static string GetUserPassword(string email) { DatabaseConnection dbConnect = new DatabaseConnection("SMU"); string query = "SELECT password FROM user_account WHERE email=/'" + email + "/'"; PreparedStatement prepStat = dbConnect.Prepare(query); SqlDataReader data = dbConnect.Query(null, prepStat); string userPassword = data.GetString(0); data.Close(); dbConnect.CloseConnection(); return userPassword; }
private static string GetSecretKey(string clientKey) { DatabaseConnection dbConnect = new DatabaseConnection("SMU"); string query = "SELECT * FROM secret_key WHERE clientKey=/'" + clientKey + "/'"; PreparedStatement prepStat = dbConnect.Prepare(query); SqlDataReader data = dbConnect.Query(null, prepStat); string secretKey; if (data.Read()) { secretKey = data.GetString(1); } else { data.Close(); dbConnect.CloseConnection(); throw new Exception("No such clientKey exists"); } data.Close(); dbConnect.CloseConnection(); return secretKey; }
private static User GetUser(string email, string password) { DatabaseConnection dbConnect = new DatabaseConnection("SMU"); string query = @"SELECT * FROM user_account WHERE email='" + email + "' AND password_hash='" + password + "'"; PreparedStatement prepStat = dbConnect.Prepare(query); SqlDataReader reader = dbConnect.Query(null, prepStat); User user = null; while (reader.Read()) { int id = reader.GetInt32(reader.GetOrdinal("id")); string userEmail = reader.GetString(reader.GetOrdinal("email")); string userPassword = reader.GetString(reader.GetOrdinal("password_hash")); //TODO userdata has to be fetched witht he rast of the data user = new User(id, userEmail, userPassword, null); } reader.Close(); dbConnect.CloseConnection(); return user; }