public void ExecuteSQLiteNonQuery(SQLiteCommand command, Logfile logfile)
{
//Exectute SQL non-query command using command object
logfile.WriteLine("SQL non-query: ", command.CommandText);
command.ExecuteNonQuery();
}
public IRestResponse ExecutPostRequest(RestRequest request, Logfile logfile)
{
logfile.WriteLine("Goodreads POST request:", client.BuildUri(request).ToString());
var response = client.Execute(request);
return(response);
}
public IRestResponse <T> ExecuteGetRequest <T>(RestRequest request, Logfile logfile)
{
logfile.WriteLine("Goodreads GET request:", client.BuildUri(request).ToString());
request.XmlSerializer = new RestSharp.Serializers.DotNetXmlSerializer();
var response = client.Execute <T>(request);
return(response);
}
public GoodReadsInterface(Logfile logfile)
{
//Set up authentication with client keys and authentication
//Set up OAuth1 with developer api tokens
apiKey = Environment.GetEnvironmentVariable("goodreads_key");
apiSecret = Environment.GetEnvironmentVariable("goodreads_secret");
client.Authenticator = OAuth1Authenticator.ForRequestToken(apiKey, apiSecret);
logfile.WriteLine("Developer key and token loaded");
LogIn(logfile);
Console.WriteLine("Welcome {0}!", userName);
}
public bool LogIn(Logfile logfile)
{
//Log in to GoodReads to authenticate user
//Get OAuth tokens
var oauthRequestToken = new RestRequest("oauth/request_token");
var response = client.Execute(oauthRequestToken);
var qs = HttpUtility.ParseQueryString(response.Content);
var oauthToken = qs["oauth_token"];
var oauthTokenSecret = qs["oauth_token_secret"];
logfile.WriteLine("Goodreads API request:", oauthRequestToken.Body);
//Construct url for user to login to
var oauthAuthorise = new RestRequest("oauth/authorize");
oauthAuthorise.AddParameter("oauth_token", oauthToken);
oauthAuthorise.AddParameter("oauth_token_secret", oauthTokenSecret);
response = client.Execute(oauthAuthorise);
var url = client.BuildUri(oauthAuthorise).ToString();
//Get user to login online
Console.WriteLine("Please log in at: {0}\nPress any key to continue.", url);
Console.ReadLine();
client.Authenticator = OAuth1Authenticator.ForAccessToken(apiKey, apiSecret, oauthToken, oauthTokenSecret);
var oauthAccessToken = new RestRequest("oauth/access_token");
response = client.Execute(oauthAccessToken);
if ((int)response.StatusCode != 200)
{
return(false);
}
qs = HttpUtility.ParseQueryString(response.Content);
var accessToken = qs["oauth_token"];
var accessTokenSecret = qs["oauth_token_secret"];
//Get user information
client.Authenticator = OAuth1Authenticator.ForProtectedResource(apiKey, apiSecret, accessToken, accessTokenSecret);
var authUser = new RestRequest("api/auth_user", DataFormat.Xml);
var authResponse = ExecuteGetRequest <AuthResponse>(authUser, logfile);
userId = authResponse.Data.user.id;
userName = authResponse.Data.user.name;
return(true);
}
public void InitialiseTable(Logfile logfile)
{
//Create empty book SQL table
//Create table if it does not already exist
var command = new SQLiteCommand(connection);
command.CommandText = "SELECT name FROM sqlite_master WHERE name = 'books';";
logfile.WriteLine("SQL command: ", command.CommandText);
var name = command.ExecuteScalar();
if (name == null)
{
ExecuteSQLiteNonQuery("CREATE TABLE books(id INTEGER PRIMARY KEY, title TEXT, author TEXT, language TEXT, date DATE, rating INTEGER, missing_info BIT, goodreads_id TEXT);", logfile);
ExecuteSQLiteNonQuery("CREATE UNIQUE INDEX title_author on books(title, author);", logfile);
}
}
public BookDB(Logfile logfile, string dbPath = "")
{
//Constructor opens DB connection
//Set default path as current directory
if (dbPath == "")
{
string pwd = Directory.GetCurrentDirectory();
dbPath += pwd + "/.book_logger.db";
}
string connectionCommand = "Data Source=" + dbPath + ";";
//Open connection
connection = new SQLiteConnection(connectionCommand);
connection.Open();
logfile.WriteLine("Database connection opened at: ", dbPath);
//Initialise table
InitialiseTable(logfile);
}
public List <Book> ExecuteSQLiteReader(SQLiteCommand command, Logfile logfile)
{
//Execture SQL read command using command object
logfile.WriteLine("SQL reader: ", command.CommandText);
SQLiteDataReader reader = command.ExecuteReader();
List <Book> bookRecords = new List <Book>();
while (reader.Read())
{
Book book = new Book();
book.dbId = reader.GetInt32(0);
book.title = reader.GetString(1);
book.author = reader.GetString(2);
book.language = reader.GetString(3);
book.date = reader.GetString(4);
book.rating = reader.GetInt32(5);
book.missing_info = reader.GetBoolean(6);
bookRecords.Add(book);
}
return(bookRecords);
}
//App to store user information about books they have read
static void Main(string[] args)
{
//Initialise logfile
Logfile logfile = new Logfile();
logfile.WriteLine("Book Logger begun");
//Display welcome message
Console.WriteLine("Welcome to your Book Logger");
//Initialise menus
Menu mainMenu = new Menu(new string[] { "Add Book", "Delete Book", "Search Books", "Show All", "Sync goodreads (down)", "Sync goodreads (up)", "Hard Reset", "Quit" }, new string[] { "add", "delete", "search", "show", "sync down", "sync up", "reset", "quit" });
logfile.WriteLine("Menus initialised");
//Initialise DB
BookDB bookDB = new BookDB(logfile);
//Intialise Good Reads interface
GoodReadsInterface goodReads = new GoodReadsInterface(logfile);
//Run until user quits
bool quit = false;
string option;
do
{
//Main menu
option = mainMenu.GetUserOptionCL();
switch (option)
{
case "add":
{
Book book = new Book();
book.UserInputCL();
bookDB.AddBook(book, "IGNORE", logfile);
Console.WriteLine("\nYou added the book: \n{0}", book);
break;
}
case "search":
{
List <Book> searchResults = bookDB.SearchCL(logfile);
Console.WriteLine("{0} records found", searchResults.Count);
Console.WriteLine("Press enter to display");
for (int i = 0; i < searchResults.Count; ++i)
{
Console.ReadLine();
Console.WriteLine(searchResults[i]);
}
break;
}
case "delete":
{
List <Book> searchResults = bookDB.SearchCL(logfile);
Console.WriteLine("{0} records found", searchResults.Count);
Console.WriteLine("Choose book to delete");
Book bookToDelete = new Book();
bool bookSelected = false;
foreach (Book book in searchResults)
{
Console.WriteLine(book);
Console.WriteLine("Would you like to delete this book?");
string response = Console.ReadLine();
if (response == "y" || response == "Y" || response == "yes" || response == "Yes")
{
bookToDelete = book;
bookSelected = true;
break;
}
}
if (bookSelected)
{
bookDB.DeleteBook(bookToDelete, logfile);
Console.WriteLine("Book deleted.");
}
else
{
Console.WriteLine("No book selected for deletion.");
}
break;
}
case "show":
{
List <Book> searchResults = bookDB.GetAllBooks(logfile);
Console.WriteLine("{0} records found", searchResults.Count);
Console.WriteLine("Press enter to display");
for (int i = 0; i < searchResults.Count; ++i)
{
Console.ReadLine();
Console.WriteLine(searchResults[i]);
}
break;
}
case "sync down":
{
List <Book> goodReadsBooks = goodReads.GetAllBooks(logfile);
foreach (Book book in goodReadsBooks)
{
bookDB.AddBook(book, "IGNORE", logfile);
}
break;
}
case "sync up":
{
List <Book> localBooks = bookDB.GetAllBooks(logfile);
var res = goodReads.Sync(localBooks, logfile);
var synced = res.synced;
var updatedBooks = res.syncBooks;
for (int i = 0; i < synced.Count; ++i)
{
if (synced[i])
{
bookDB.AddBook(updatedBooks[i], "REPLACE", logfile);
}
}
break;
}
case "reset":
{
bookDB.ResetTable(logfile);
break;
}
case "quit":
{
Console.WriteLine("See you next time!");
quit = true;
break;
}
}
} while (!quit);
}
