// returns the id of the new list
public static int?CreateNewList(IDbConnection dbConnection, string userToken, string listName = "New List")
{
string listId;
int userId;
User user = UserFactory.LoadSingleByToken(userToken);
string checkedListName = FactoryUtils.CheckInput(listName, 0, 30, @"^[a-zA-Z0-9!'_\-\.\s]*$");
// if listName is bad don't create
if (checkedListName == null)
{
return(null);
}
// if user doesn't exist don't create
if (user != null)
{
userId = user.Id;
}
else
{
return(null);
}
dbConnection.Insert("lists", new KeyValuePair <string, object>[] {
Pairing.Of("name", checkedListName),
Pairing.Of("owner", userId)
}).Execute();
listId = dbConnection.Take("lists").OrderBy("id", "desc").Limit(1).Execute()[0][0];
return(Int32.TryParse(listId, out int id) ? id : (int?)null);
}
public int Create(IDbConnection db, string table, string col, object val)
{
db.Insert(table, new KeyValuePair <string, object>[] {
Pairing.Of(col, val)
}).Execute();
return(Int32.TryParse(db.Take("lists").OrderBy("id", "desc").Limit(1).Execute()[0][0], out int id) ? id : 0);
}
public static void UpdateListName(IDbConnection dbConnection, int id, string listName)
{
string checkedListName = FactoryUtils.CheckInput(listName, 0, 30, @"^[a-zA-Z0-9!'_\-\.\s]*$");
if (checkedListName != null)
{
dbConnection.Update("lists", Pairing.Of("name", checkedListName)).Where(Pairing.Of("id", id)).Execute();
}
}
public static void UpdateFirstName(IDbConnection dbConnection, string userToken, string name)
{
if (CheckFirstname(name) && !string.IsNullOrWhiteSpace(userToken))
{
dbConnection
.Update("users", Pairing.Of("name", name))
.Where(Pairing.Of("userToken", userToken))
.Execute();
}
}
public static void DeleteUser(IDbConnection dbConnection, string userToken)
{
if (!string.IsNullOrWhiteSpace(userToken))
{
dbConnection
.Delete("users")
.Where(Pairing.Of("userToken", userToken))
.Execute();
}
}
// returns the id of the new item
public static string CreateNewItem(IDbConnection dbConnection, int bookId, int listId)
{
// TODO: Add checking to make sure the book and list are also in the database
dbConnection.Insert("booklist", new KeyValuePair <string, object>[] {
Pairing.Of("book", bookId),
Pairing.Of("list", listId)
}).Execute();
return(dbConnection.Take("booklist").OrderBy("id", "desc").Limit(1).Execute()[0][0]);
}
// Starting place
public PostgreSQLConnection DropTable(string table)
{
var result = Take("information_schema.tables").Where(Pairing.Of("table_name", $"{table}")).Execute();
// Table is in the database so we can delete it
if (result[0].Count > 0)
{
SQL = $"drop table {table}";
IsQuery = false;
}
return(this);
}
public static void UpdateBook(IDbConnection dbConnection, int id, string title, string author)
{
string checkedBookTitle = FactoryUtils.CheckInput(title, 0, 120, @"^[a-zA-Z0-9!.:;""'?\s]*$");
string checkedBookAuthor = FactoryUtils.CheckInput(author, 0, 30, @"^[a-zA-Z\s]*$");
// If title or author don't pass the CheckInput test then don't update the book
if (checkedBookTitle == null || checkedBookAuthor == null)
{
return;
}
dbConnection.Update("books", Pairing.Of("title", $"{checkedBookTitle}")).Execute();
dbConnection.Update("books", Pairing.Of("author", $"{checkedBookAuthor}")).Execute();
}
// Starting place
public PostgreSQLConnection CreateTable(string table, params KeyValuePair <string, object>[] columnTypes)
{
var result = Take("information_schema.tables").Where(Pairing.Of("table_name", $"{table}")).Execute();
if (result[0].Count == 0)
{
SQL = $"create table {table}();" +
$" alter table {table} add column id bigserial primary key;";
foreach (var columnType in columnTypes)
{
SQL = SQL + $" alter table {table} add column {columnType.Key} {columnType.Value.ToString()};";
}
}
return(this);
}
// returns the id of the new book
// if the book can't be created returns null
public static int?CreateNewBook(IDbConnection dbConnection, string title, string author)
{
string bookId;
string checkedBookTitle = FactoryUtils.CheckInput(title, 0, 120, @"^[a-zA-Z0-9!.:;""'?\s]*$");
string checkedBookAuthor = FactoryUtils.CheckInput(author, 0, 30, @"^[a-zA-Z\s]*$");
// If title or author don't pass the CheckInput test then don't create the book
if (checkedBookTitle == null || checkedBookAuthor == null)
{
return(null);
}
dbConnection.Insert("books", new KeyValuePair <string, object>[] {
Pairing.Of("title", $"{checkedBookTitle}"),
Pairing.Of("author", $"{checkedBookAuthor}")
}).Execute();
bookId = dbConnection.Take("books").OrderBy("id", "desc").Limit(1).Execute()[0][0];
return(Int32.TryParse(bookId, out int id) ? id : (int?)null);
}
// returns the userToken of the new user
// if the username is already taken, returns null
public static string CreateNewUser(IDbConnection dbConnection, string name, string username, string password)
{
if (LoadSingle(username) != null)
{
return(null);
}
if (!CheckUsername(username) || !CheckPassword(password) || !CheckFirstname(name))
{
return(null);
}
string userToken = GenerateUserToken();
var hashedPwd = HashPassword(password);
dbConnection.Insert("users", new KeyValuePair <string, object>[] {
Pairing.Of("name", $"{name}"),
Pairing.Of("username", $"{username.ToLower()}"),
Pairing.Of("password", $"{hashedPwd}"),
Pairing.Of("usertoken", $"{userToken}")
}).Execute();
return(userToken);
}
public static void DeleteList(IDbConnection dbConnection, int id)
{
dbConnection.Delete("booklist").Where(Pairing.Of("list", id)).Execute();
dbConnection.Delete("lists").Where(Pairing.Of("id", id)).Execute();
}
