/// <summary>
/// Read a single comment from the index
/// </summary>
/// <param name="client">Engine client to make the request</param>
/// <param name="commentid">id of the comment to retrieve</param>
/// <returns>a Comment object (or null if the comment was not found)</returns>
private Comment ReadComment(EngineClient client, string commentid, string docid)
{
// Get the current comment
string sql = $"SELECT * FROM {indexname} WHERE id={Str.SqlValue(commentid)} AND docid={Str.SqlValue(docid)} LIMIT 1";
Cursor cursor = client.ExecCursor(sql);
if (cursor != null && !client.HasError())
{
Comment comment = null;
if (!cursor.End())
{
comment = new Comment(cursor, Method.Session.UserId);
}
else
{
SetError(404, $"This comment could not be found");
}
cursor.Dispose();
return(comment);
}
else
{
throw new Exception("Cursor is null! " + client.GetError());
}
}
/// <summary>
/// Get the number of comments for a given document or multiple documents
/// at once if a list of ids is provided
/// </summary>
/// <param name="client"></param>
/// <param name="docid"></param>
private void Count(EngineClient client, string docid)
{
ListStr docids = Method.JsonRequest.ValueListStr("docids");
if (docids != null && docids.Count > 0)
{
var sep = dynamicSeparator("=", docids.ToStr(' ')); // Since document ids potentially contains ';', we have to look for a custom separator dynamically
var sql = $"SELECT DISTRIBUTION('docid,SEPARATOR=\"{sep}\"') AS ids FROM {indexname} WHERE {Sql.In("docid", docids)} LIMIT 1";
Cursor cursor = client.ExecCursor(sql);
if (cursor != null)
{
// Write JSON
var counts = Json.NewObject();
var pairs = cursor.GetAttribute("ids").Split(new [] { sep }, StringSplitOptions.None);
foreach (var pair in pairs)
{
int i = pair.LastIndexOf(';');
counts.Set(pair.Substring(0, i), int.Parse(pair.Substring(i + 1)));
}
JsonResponse.Set("counts", counts);
}
else
{
throw new Exception("Cursor is null!");
}
}
else
{
var sql = $"SELECT id FROM {indexname} WHERE docid={Str.SqlValue(docid)} ORDER BY modified LIMIT 1";
Cursor cursor = client.ExecCursor(sql);
if (cursor != null)
{
// Write JSON
JsonResponse.Set("count", cursor.TotalRowCount);
}
else
{
throw new Exception("Cursor is null!");
}
}
}
public void GetPages(List <string> pages, Dictionary <string, string> url2id, Dictionary <string, int> url2idx)
{
CCIndex index = CC.Current.Indexes[indexname];
EngineClient client = null;
try
{
client = EngineClientsPool.FromPool(index);
String sqlquery = Str.And("select id,", column_id, " from ", indexname, " where collection = '", collection, "' and ", column_id, "<> ''");
Sys.Log("processing query for ", sqlquery);
Cursor cursor = client.ExecCursor(sqlquery);
if (cursor != null)
{
Sys.Log("Number of rows: ", cursor.CursorRowCount);
int duplicates = 0;
for (int i = 0; i < cursor.CursorRowCount; i++)
{
//Sys.Log("doc " + i);
string docid = cursor.GetColumn("id");
string pagerankid = cursor.GetColumn(column_id);
if (!url2id.ContainsKey(pagerankid)) // Duplicates id are possible...
{
pages.Add(pagerankid);
url2id[pagerankid] = docid;
url2idx[pagerankid] = i - duplicates;
}
else
{
duplicates++;
}
//Sys.Log("Added doc " + doc.Id);
cursor.MoveNext();
}
}
else
{
Sys.Log("No doc");
}
}
finally
{
EngineClientsPool.ToPool(client);
}
}
public void GetLinks(Dictionary <string, int> url2idx, ArrayList matrix)
{
CCIndex index = CC.Current.Indexes[indexname];
EngineClient client = null;
try
{
client = EngineClientsPool.FromPool(index);
String sqlquery = Str.And("select ", column_id, ",", column_links, " from ", indexname, " where collection = '", collection, "' and ", column_id, "<> ''");
Sys.Log("processing query for ", sqlquery);
Cursor cursor = client.ExecCursor(sqlquery);
if (cursor != null)
{
Sys.Log("Number of rows: ", cursor.CursorRowCount);
for (int i = 0; i < cursor.CursorRowCount; i++)
{
string pagerankid = cursor.GetColumn(column_id);
string links = cursor.GetColumn(column_links);
//Sys.Log("doc ", i, " ", pagerankid, " ", url2idx[pagerankid]);
List <int> doc = matrix[url2idx[pagerankid]] as List <int>;
if (links != null && Str.NEQ(links, ""))
{
foreach (string link in links.Split(';'))
{
if (url2idx.ContainsKey(link))
{
doc.Add(url2idx[link]);
}
}
}
//Sys.Log("Added doc " + url2idx[pagerankid]);
cursor.MoveNext();
}
}
else
{
Sys.Log("No doc");
}
}
finally
{
EngineClientsPool.ToPool(client);
}
}
/// <summary>
/// Retrieve the raw list of comments attached to a document
/// </summary>
/// <param name="client">Engine client to make the request</param>
/// <param name="docid">id of the document to retrieve</param>
/// <returns>A dictionary mapping comment ids to comment objects</returns>
private Dictionary <string, Comment> ReadComments(EngineClient client, string docid)
{
var comments = new Dictionary <string, Comment>();
var sql = $"SELECT * FROM {indexname} WHERE docid={Str.SqlValue(docid)} ORDER BY modified LIMIT 10000"; // Hard limit of 10000 comments for extreme cases
Cursor cursor = client.ExecCursor(sql);
if (cursor != null)
{
while (!cursor.End())
{
Comment comment = new Comment(cursor, Method.Session.UserId);
comments.Add(comment.commentid, comment);
cursor.MoveNext();
}
}
else
{
throw new Exception("Cursor is null!");
}
return(comments);
}
private bool ExecCursor()
{
if (_client == null)
{
return(false);
}
Stopwatch swClientCursorExecute = new Stopwatch();
swClientCursorExecute.Start();
_cursor = _client.ExecCursor(sql);
swClientCursorExecute.Stop();
execCursorTimer = swClientCursorExecute.ElapsedMilliseconds;
AddToTotalTime(execCursorTimer);
swClientCursorExecute.Reset();
Sys.Log2(50, $"{{{threadId}}} Thread group [{threadGroupName}][{iteration}] ExecCursor [{Sys.TimerGetText(execCursorTimer)}]");
if (_cursor == null)
{
return(false);
}
return(true);
}
public override Return OnExecute()
{
string indexes;
HashSet <string> hsMD5 = new HashSet <string>();
int emptyLinesCount = 0;
int duplicatesLinesCount = 0;
int writtenLinesCount = 0;
int rowProcessed = 0;
ListStr lHeaders = new ListStr();
EngineClient _client = null;
Sinequa.Engine.Client.Cursor _cursor = null;
//do not use StreamWriter in a using statement. In simulate mode the file is not created so this will trigger an exception.
StreamWriter sw = null;
try
{
_client = EngineClientsPool.FromPoolByIndexList(conf.listIndexes, out indexes, conf.engine, false);
}
catch (Exception ex)
{
Sys.LogError("Cannot get Engine Client from pool for indexes [" + conf.listIndexes + "]");
Sys.LogError(ex);
return(Return.Error);
}
Sys.Log("Using Engine client [" + _client.Name + "]");
try
{
Sys.Log("Execute query [" + conf.GetSQL() + "]");
_cursor = _client.ExecCursor(conf.GetSQL());
if (_cursor == null)
{
Sys.LogError("Cannot get cursor for query [" + conf.GetSQL() + "]");
return(Return.Error);
}
DocExportIndexToCsv doc = new DocExportIndexToCsv(this, _cursor, conf.dColumnColumnAlias);
var context = new IDocContext {
Doc = doc
};
Sys.Log("Query processingtime [" + _cursor.GetAttribute("processingtime") + "]");
Sys.Log("Query row count [" + _cursor.TotalRowCount + "]");
int globalTimer = Sys.TimerStart();
if (!conf.simulate)
{
sw = new StreamWriter(conf.destinationFilePath, false, Encoding.UTF8);
}
int localTimer = Sys.TimerStart();
while (!_cursor.End())
{
rowProcessed++;
if (rowProcessed % logStatsEveryNRows == 0)
{
Sys.Log("----------------------------------------------------");
Sys.Log("Number of rows processed [" + rowProcessed + "] ");
Sys.Log("Number of lines exported [" + writtenLinesCount + "] ");
Sys.Log("Number of empty lines removed [" + emptyLinesCount + "] ");
Sys.Log("Number of duplicated lines removed [" + duplicatesLinesCount + "] ");
Sys.Log("Processing [" + logStatsEveryNRows + "] rows in [", Sys.TimerGetText(Sys.TickDuration(localTimer)), "]");
localTimer = Sys.TimerStart();
}
ListStr l = new ListStr();
bool isEmpty = true;
for (int i = 0; i < _cursor.ColumnCount; i++)
{
if (conf.addHeaders && rowProcessed == 1) //headers
{
string header = _cursor.GetColumnName(i);
if (conf.useDblQuote)
{
header = "\"" + header + "\"";
}
lHeaders.Add(header);
}
string colValue = Str.Empty;
//cursor column match column mapping column name ?
if (conf.lColumnMapping.Exists(x => Str.EQNC(x.columnName, _cursor.GetColumnName(i))))
{
//get all matching column mapping for current column
List <ColumnMapping> lColumnMapping = conf.lColumnMapping.FindAll(x => Str.EQNC(x.columnName, _cursor.GetColumnName(i)));
foreach (ColumnMapping columnMapping in lColumnMapping)
{
if (columnMapping.slectionQuery.IsSelected(context, doc)) //match selection query ? if so, apply value pattern
{
Sys.Log2(40, "Column [" + columnMapping.columnName + "] match selection query [" + columnMapping.slectionQuery.Sql + "]");
colValue = IDocHelper.GetValue(context, doc, columnMapping.valueExpression);
Sys.Log2(40, "Column [" + columnMapping.columnName + "] use value pattern [" + columnMapping.valueExpression + "] = [" + colValue + "]");
break; //stop mapping when selection query match
}
else
{
Sys.Log2(40, "Column [" + columnMapping.columnName + "] don't match selection query [" + columnMapping.slectionQuery.Sql + "]");
continue; //go to next expression
}
}
}
//no column mapping, get value from cursor
else
{
colValue = _cursor.GetColumn(i);
}
if (!Str.IsEmpty(colValue))
{
isEmpty = false;
}
if (conf.useReplaceSeparator)
{
colValue = colValue.Replace(conf.separator, conf.replaceSeparator); //replace separator in values
}
if (conf.useDblQuote)
{
colValue = "\"" + colValue + "\""; //use double quote
}
l.Add(colValue);
}
string line = l.ToStr(conf.separator);
if (conf.removeDuplicates) //remove duplicates
{
string MD5 = Str.Md5(line);
if (!hsMD5.Add(MD5))
{
duplicatesLinesCount++;
_cursor.MoveNext();
continue;
}
}
if (conf.removeEmptyLines && isEmpty) //remove empty lines
{
emptyLinesCount++;
_cursor.MoveNext();
continue;
}
writtenLinesCount++;
if (conf.simulate) //simulate, add headers and line into logs
{
if (conf.addHeaders && rowProcessed == 1)
{
Sys.Log(GetHeaders(lHeaders)); // write headers
}
Sys.Log(line); //write line
if (writtenLinesCount >= conf.simulateCount)
{
break;
}
}
else
{
if (conf.addHeaders && rowProcessed == 1)
{
sw.WriteLine(GetHeaders(lHeaders)); // write headers
}
sw.WriteLine(line); //write line
}
_cursor.MoveNext();
}
if (sw != null)
{
sw.Close(); //dispose stream writer
}
Sys.Log("----------------------------------------------------");
if (conf.removeEmptyLines)
{
Sys.Log("Number of empty lines removed [" + emptyLinesCount + "]");
}
if (conf.removeDuplicates)
{
Sys.Log("Number of duplicated lines removed [" + duplicatesLinesCount + "]");
}
Sys.Log("[" + writtenLinesCount + "] lines exported into file [" + conf.destinationFilePath + "]");
Sys.Log("Processing [" + rowProcessed + "] rows in [", Sys.TimerGetText(Sys.TickDuration(globalTimer)), "]");
}
catch (Exception ex)
{
Sys.LogError("Select index Cursor error : ", ex);
try
{
if (_client != null)
{
EngineClientsPool.ToPool(_client);
}
}
catch (Exception exe)
{
Sys.LogError("EngineClientsPool ToPool : ", exe);
Sys.Log(exe.StackTrace);
}
}
finally
{
try
{
if (_cursor != null)
{
_cursor.Close();
}
}
catch (Exception ex)
{
Sys.LogError("Close cursor error : ", ex);
Sys.Log(ex.StackTrace);
}
EngineClientsPool.ToPool(_client);
}
return(base.OnExecute());
}