/*
* Method to return n URLs from DB.
*/
public static IEnumerable <URL> getURLSFromDB(int n, bool initialLoad)
{
IEnumerable <URL> myUrlEnumerable = null;
String dbConfig = new MyConfigurationHelper().getDBConnectionConfig();
using (IDbConnection db = DBConnectionHelper.getConnection(dbConfig)){//get connection
if (db != null)
{
if (!initialLoad)
{
//if not initial load, we need to get new urls in status INITIAL
myUrlEnumerable =
db.Query <URL>("Select Id, Url, Urltype, Property from URL where status = @status limit @k",
new { status = URL.URLStatus.INITIAL, k = n });
}
else
{
//if initial load, we need to get URLs in RUNNING status as well as they were not parseds last time
myUrlEnumerable =
db.Query <URL>("Select Id, Url, Urltype, Property from URL where status = ANY(@status) limit @k",
new { status = new [] { (int)URL.URLStatus.INITIAL, (int)URL.URLStatus.RUNNING }, k = n });
}
}
}
return(myUrlEnumerable);
}
/*
* This method updates the status of url passed to it to DONE.
*/
public static void markURLDone(URL url)
{
String dbConfig = new MyConfigurationHelper().getDBConnectionConfig();
using (IDbConnection db = DBConnectionHelper.getConnection(dbConfig)){
if (db != null)
{
db.Execute("update url set status = @status where id=@id", new { status = (int)URL.URLStatus.DONE, id = url.id });
}
}
}
/*
* This method simply merges whatever data is passed to it into DB
*/
public static void updateURLs(Queue <URL> myUrlQueue)
{
String dbConfig = new MyConfigurationHelper().getDBConnectionConfig();
using (IDbConnection db = DBConnectionHelper.getConnection(dbConfig)){
if (db != null)
{
db.BulkMerge(myUrlQueue);
}
}
}
/*
* Method to insert parsed properties into DB
*/
public static void insertParsedProperties(PropertyData propData)
{
if (propData == null)
{
return;
}
List <PropertyType> propertyTypeList = propData.urlList;
if (propertyTypeList != null && propertyTypeList.Count > 0)
{
String dbConfig = new MyConfigurationHelper().getDBConnectionConfig();
using (IDbConnection db = DBConnectionHelper.getConnection(dbConfig)){ //get connection
db.BulkMerge(propertyTypeList) //insert the list of property types
.ThenForEach(x => x.properties
.ForEach(y => y.propertytype = x.id)) //set property type id for properties
.ThenBulkMerge(x => x.properties) //insert properties
.ThenForEach(x => x.url.property = x.id) //set property id for urls
.ThenBulkMerge(x => x.url); //insert urls
}
}
}
public static IDbConnection getConnection(String location)
{
if (location.SequenceEqual("aws"))
{
String secretString = AwsSecretManager.Get("PostgresSecret");
JObject secret = JObject.Parse(secretString);
String host = secret.GetValue("host").ToObject(typeof(String)).ToString();
String username = secret.GetValue("username").ToObject(typeof(String)).ToString();
String password = secret.GetValue("password").ToObject(typeof(String)).ToString();
String dbName = secret.GetValue("dbname").ToObject(typeof(String)).ToString();
String connectionString = new StringBuilder()
.Append("Host=").Append(host)
.Append(";Username="******";Password="******";Database=").Append(dbName).ToString();
return(new NpgsqlConnection(connectionString));
}
else
{
MyConfigurationHelper myConfigurationManager = new MyConfigurationHelper();
return(new SqlConnection(myConfigurationManager.getDBConnectionString()));
}
}
public static IDbConnection getConnection()
{
MyConfigurationHelper myConfigurationManager = new MyConfigurationHelper();
return(new NpgsqlConnection(myConfigurationManager.getDBConnectionString()));
}
