563 lines
16 KiB
C#
563 lines
16 KiB
C#
using Commentbase;
|
|
using Npgsql;
|
|
using System.Collections.Generic;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace Database
|
|
{
|
|
public class ImplementsPostgres : Abstracts
|
|
{
|
|
private NpgsqlConnection GetConnect()
|
|
{
|
|
return new NpgsqlConnection("Host=192.168.56.102;Username=postgres;Password=postgres;Database=postgres");
|
|
}
|
|
public override void CreateAlbum(Album album)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("INSERT INTO Album (Title, Description) " +
|
|
"VALUES (@Title, @Description)", conn);
|
|
cmd.Parameters.AddWithValue("@Title", album.Title);
|
|
cmd.Parameters.AddWithValue("@Description", album.Description);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void UpdateAlbum(Album album)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"UPDATE Album SET Title = @Title, Description = @Description WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@Title", album.Title);
|
|
cmd.Parameters.AddWithValue("@Description", album.Description);
|
|
cmd.Parameters.AddWithValue("@Id", album.Id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void DeleteAlbum(int id)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM Album WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@Id", id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override Album GetAlbum(int id)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM Album WHERE id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Album
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Title = reader.GetString(1),
|
|
Description = reader.GetString(2),
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override Album GetAlbum(string title)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM Album WHERE Title = '{title}'", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Album
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Title = reader.GetString(1),
|
|
Description = reader.GetString(2),
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override List<Album> GetAlbums()
|
|
{
|
|
List<Album> albums = new List<Album>();
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM Album", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
albums.Add(new Album
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Title = reader.GetString(1),
|
|
Description = reader.GetString(2),
|
|
});
|
|
}
|
|
return albums;
|
|
}
|
|
|
|
public override void DeleteAllAlbums()
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM Album", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void CreateLocation(Location location)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("INSERT INTO Location (Name, Shortname) " +
|
|
"VALUES (@Name, @Shortname)", conn);
|
|
cmd.Parameters.AddWithValue("@Name", location.Name);
|
|
cmd.Parameters.AddWithValue("@Shortname", location.ShortName);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void UpdateLocation(Location location)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("UPDATE Location SET Name = @Name, ShortName = @Shortname WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@Name", location.Name);
|
|
cmd.Parameters.AddWithValue("@Shortname", location.ShortName);
|
|
cmd.Parameters.AddWithValue("@Id", location.Id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void DeleteLocation(int id)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM Location WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@Id", id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override Location GetLocation(int id)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM Location WHERE id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Location
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
ShortName = reader.GetString(2),
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override Location GetLocation(string name)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM Location WHERE Name = '{name}'", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Location
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
ShortName = reader.GetString(2),
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override List<Location> GetLocations()
|
|
{
|
|
List<Location> locations = new List<Location>();
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM Location", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
locations.Add(new Location
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
ShortName = reader.GetString(2),
|
|
});
|
|
}
|
|
return locations;
|
|
}
|
|
|
|
public override void DeleteAllLocations()
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM Location", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void CreatePhoto(Photo photo)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("INSERT INTO Photo (Title, Description, Privacy, UploadDate, ImagePath, AlbumId, LocationId, AuthorId) " +
|
|
"VALUES (@Title, @Description, @Privacy, @UploadDate, @ImagePath, @Album, @Location, @Author)", conn);
|
|
cmd.Parameters.AddWithValue("@Title", photo.Title);
|
|
cmd.Parameters.AddWithValue("@Description", photo.Description);
|
|
cmd.Parameters.AddWithValue("@Privacy", photo.Privacy);
|
|
cmd.Parameters.AddWithValue("@UploadDate", photo.UploadDate);
|
|
cmd.Parameters.AddWithValue("@ImagePath", photo.ImagePath);
|
|
cmd.Parameters.AddWithValue("@Album", photo.AlbumId);
|
|
cmd.Parameters.AddWithValue("@Location", photo.LocationId);
|
|
cmd.Parameters.AddWithValue("@Author", photo.AuthorId);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void UpdatePhoto(Photo photo)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("UPDATE Photo SET Title = @Title, Description = @Description, Privacy = @Privacy, UploadDate = @UploadDate, ImagePath = @ImagePath, AlbumId = @Album, LocationId = @Location, AuthorId = @Author WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@Title", photo.Title);
|
|
cmd.Parameters.AddWithValue("@Description", photo.Description);
|
|
cmd.Parameters.AddWithValue("@Privacy", photo.Privacy);
|
|
cmd.Parameters.AddWithValue("@UploadDate", photo.UploadDate);
|
|
cmd.Parameters.AddWithValue("@ImagePath", photo.ImagePath);
|
|
cmd.Parameters.AddWithValue("@Album", photo.AlbumId);
|
|
cmd.Parameters.AddWithValue("@Location", photo.LocationId);
|
|
cmd.Parameters.AddWithValue("@Author", photo.AuthorId);
|
|
cmd.Parameters.AddWithValue("@Id", photo.Id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void DeletePhoto(int id)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM Photo WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@Id", id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override Photo GetPhoto(int id)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM Photo WHERE id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Photo
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Title = reader.GetString(4),
|
|
Description = reader.GetString(5),
|
|
Privacy = reader.GetString(6),
|
|
UploadDate = reader.GetDateTime(7),
|
|
ImagePath = reader.GetString(8),
|
|
AlbumId = reader.GetInt32(1),
|
|
LocationId = reader.GetInt32(2),
|
|
AuthorId = reader.GetInt32(3)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override Photo GetPhoto(int AlbumId, int LocationId, int AuthorId)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM Photo WHERE (AlbumId = {AlbumId} AND LocationId = {LocationId} AND AuthorId = {AuthorId})", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Photo
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Title = reader.GetString(4),
|
|
Description = reader.GetString(5),
|
|
Privacy = reader.GetString(6),
|
|
UploadDate = reader.GetDateTime(7),
|
|
ImagePath = reader.GetString(8),
|
|
AlbumId = reader.GetInt32(1),
|
|
LocationId = reader.GetInt32(2),
|
|
AuthorId = reader.GetInt32(3)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override List<Photo> GetPhotos()
|
|
{
|
|
List<Photo> photos = new List<Photo>();
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM Photo", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
photos.Add(new Photo
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Title = reader.GetString(4),
|
|
Description = reader.GetString(5),
|
|
Privacy = reader.GetString(6),
|
|
UploadDate = reader.GetDateTime(7),
|
|
ImagePath = reader.GetString(8),
|
|
AlbumId = reader.GetInt32(1),
|
|
LocationId = reader.GetInt32(2),
|
|
AuthorId = reader.GetInt32(3)
|
|
});
|
|
}
|
|
return photos;
|
|
}
|
|
|
|
public override void DeleteAllPhotos()
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM Photo", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void CreateAuthor(Author author)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("INSERT INTO Author (Name, PhoneNum, Email) " +
|
|
"VALUES (@Name, @PhoneNum, @Email)", conn);
|
|
cmd.Parameters.AddWithValue("@Name", author.Name);
|
|
cmd.Parameters.AddWithValue("@PhoneNum", author.PhoneNum);
|
|
cmd.Parameters.AddWithValue("@Email", author.Email);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void UpdateAuthor(Author author)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("UPDATE Author SET Name = @Name, PhoneNum = @PhoneNum, Email = @Email WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@Name", author.Name);
|
|
cmd.Parameters.AddWithValue("@PhoneNum", author.PhoneNum);
|
|
cmd.Parameters.AddWithValue("@Email", author.Email);
|
|
cmd.Parameters.AddWithValue("@Id", author.Id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void DeleteAuthor(int id)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM Author WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@Id", id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override Author GetAuthor(int id)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM Author WHERE id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Author
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
PhoneNum = reader.GetString(2),
|
|
Email = reader.GetString(3)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override Author GetAuthor(string name)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM Author WHERE Name = '{name}'", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Author
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
PhoneNum = reader.GetString(2),
|
|
Email = reader.GetString(3)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override List<Author> GetAuthors()
|
|
{
|
|
List<Author> authors = new List<Author>();
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM Author", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
authors.Add(new Author
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
PhoneNum = reader.GetString(2),
|
|
Email = reader.GetString(3)
|
|
});
|
|
}
|
|
return authors;
|
|
}
|
|
|
|
public override void DeleteAllAuthors()
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM Author", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void CreateComment(Comment comment)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("INSERT INTO Comment (PostDate, Content, Photold) " +
|
|
"VALUES (@PostDate, @Content, @Photo)", conn);
|
|
cmd.Parameters.AddWithValue("@PostDate", comment.PostDate);
|
|
cmd.Parameters.AddWithValue("@Content", comment.Content);
|
|
cmd.Parameters.AddWithValue("@Photo", comment.PhotoId);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void UpdateComment(Comment comment)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("UPDATE Comment SET PostDate = @PostDate, Content = @Content, Photold = @Photo WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@PostDate", comment.PostDate);
|
|
cmd.Parameters.AddWithValue("@Content", comment.Content);
|
|
cmd.Parameters.AddWithValue("@Photo", comment.PhotoId);
|
|
cmd.Parameters.AddWithValue("@Id", comment.Id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void DeleteComment(int id)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM Comment WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@Id", id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override Comment GetComment(int id)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM Comment WHERE id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Comment
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
PostDate = reader.GetDateTime(2),
|
|
Content = reader.GetString(3),
|
|
PhotoId = reader.GetInt32(1)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override Comment GetCommentPhoto(int PhotoId)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM Comment WHERE Photold = {PhotoId}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Comment
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
PostDate = reader.GetDateTime(2),
|
|
Content = reader.GetString(3),
|
|
PhotoId = reader.GetInt32(1)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override List<Comment> GetComments()
|
|
{
|
|
List<Comment> comments = new List<Comment>();
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM Comment", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
comments.Add(new Comment
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
PostDate = reader.GetDateTime(2),
|
|
Content = reader.GetString(3),
|
|
PhotoId = reader.GetInt32(1)
|
|
});
|
|
}
|
|
return comments;
|
|
}
|
|
|
|
public override void DeleteAllComments()
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM Comment", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
private Sequence GetSequence(string nameInPg, string nameInApp)
|
|
{
|
|
using var conn = GetConnect();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT sequencename, last_value " +
|
|
"FROM pg_sequences " +
|
|
"WHERE sequencename = '" + nameInPg + "_id_seq'", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Sequence
|
|
{
|
|
Id = nameInApp,
|
|
Count = reader.GetInt32(1)
|
|
};
|
|
}
|
|
else
|
|
return new Sequence
|
|
{
|
|
Id = nameInApp,
|
|
Count = 0
|
|
};
|
|
}
|
|
|
|
public List<Sequence> GetSequences()
|
|
{
|
|
return new List<Sequence>{
|
|
GetSequence("album", "Album"),
|
|
GetSequence("location", "Location"),
|
|
GetSequence("author", "Author"),
|
|
GetSequence("photo", "Photo"),
|
|
GetSequence("comment", "Comment")
|
|
};
|
|
}
|
|
}
|
|
}
|