public Author FindOrCreateAuthor(AuthorInfoNode info, IDbTransaction transaction)
{
string commandText;
FbCommand command;
Author author;
if (info == null)
{
throw new ArgumentNullException("info");
}
this.manager.BeginConnect();
try
{
commandText =
@"SELECT autor.* FROM autor
INNER JOIN autor_synonims ON autor_synonims.autorid = autor.autorid
WHERE UPPERCASE(autor_synonims.lastname) = UPPERCASE(@lastname) AND
UPPERCASE(autor_synonims.firstname) = UPPERCASE(@firstname) AND
UPPERCASE(autor_synonims.midname) = UPPERCASE(@midname)";
command = this.connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = commandText;
command.Transaction = transaction as FbTransaction;
command.Parameters.Add("@firstname", FbDbType.VarChar, 40).Value = info.FirstName;
command.Parameters.Add("@midname", FbDbType.VarChar, 40).Value = info.MiddleName;
command.Parameters.Add("@lastname", FbDbType.VarChar, 40).Value = info.LastName;
using (SqlDbCommand dbCommand = new SqlDbCommand(command))
{
author = dbCommand.ExecuteObject<Author>();
}
if (author != null)
{
return author;
}
commandText =
@"SELECT autor.* FROM autor
INNER JOIN autor_synonims ON autor_synonims.autorid = autor.autorid
WHERE UPPERCASE(autor_synonims.lastname) = UPPERCASE(@lastname) AND
UPPERCASE(autor_synonims.firstname) = UPPERCASE(@firstname) AND
((autor_synonims.midname = '' AND CAST(@midname AS VARCHAR(40)) <> '') OR (autor_synonims.midname <> '' AND CAST(@midname AS VARCHAR(40)) = ''))";
command = this.connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = commandText;
command.Transaction = transaction as FbTransaction;
command.Parameters.Add("@firstname", FbDbType.VarChar, 40).Value = info.FirstName;
command.Parameters.Add("@midname", FbDbType.VarChar, 40).Value = info.MiddleName ?? String.Empty;
command.Parameters.Add("@lastname", FbDbType.VarChar, 40).Value = info.LastName;
using (SqlDbCommand dbCommand = new SqlDbCommand(command))
{
author = dbCommand.ExecuteObject<Author>();
}
if (author != null)
{
return author;
}
commandText =
@"SELECT * FROM autor
WHERE UPPERCASE(autor.lastname) = UPPERCASE(@lastname) AND
UPPERCASE(autor.firstname) = UPPERCASE(@firstname) AND
UPPERCASE(autor.midname) = UPPERCASE(@midname) AND
UPPERCASE(autor.nickname) = UPPERCASE(@nickname)";
command = this.connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = commandText;
command.Transaction = transaction as FbTransaction;
command.Parameters.Add("@firstname", FbDbType.VarChar).Value = info.FirstName;
command.Parameters.Add("@midname", FbDbType.VarChar).Value = info.MiddleName;
command.Parameters.Add("@lastname", FbDbType.VarChar).Value = info.LastName;
command.Parameters.Add("@nickname", FbDbType.VarChar).Value = info.NickName;
using (SqlDbCommand dbCommand = new SqlDbCommand(command))
{
author = dbCommand.ExecuteObject<Author>();
}
if (author != null)
{
return author;
}
commandText =
@"SELECT * FROM autor
WHERE UPPERCASE(autor.lastname) = UPPERCASE(@lastname) AND
UPPERCASE(autor.firstname) = UPPERCASE(@firstname) AND
UPPERCASE(autor.midname) = UPPERCASE(@midname)";
command = this.connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = commandText;
command.Transaction = transaction as FbTransaction;
command.Parameters.Add("@firstname", FbDbType.VarChar).Value = info.FirstName;
command.Parameters.Add("@midname", FbDbType.VarChar).Value = info.MiddleName;
command.Parameters.Add("@lastname", FbDbType.VarChar).Value = info.LastName;
using (SqlDbCommand dbCommand = new SqlDbCommand(command))
{
author = dbCommand.ExecuteObject<Author>();
}
if (author != null)
{
return author;
}
commandText =
@"SELECT autor.* FROM autor
WHERE UPPERCASE(autor.lastname) = UPPERCASE(@lastname) AND
UPPERCASE(autor.firstname) = UPPERCASE(@firstname) AND
((autor.midname = '' AND CAST(@midname AS VARCHAR(40)) <> '') OR (autor.midname <> '' AND CAST(@midname AS VARCHAR(40)) = ''))";
command = this.connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = commandText;
command.Transaction = transaction as FbTransaction;
command.Parameters.Add("@firstname", FbDbType.VarChar, 40).Value = info.FirstName;
command.Parameters.Add("@midname", FbDbType.VarChar, 40).Value = info.MiddleName ?? String.Empty;
command.Parameters.Add("@lastname", FbDbType.VarChar, 40).Value = info.LastName;
using (SqlDbCommand dbCommand = new SqlDbCommand(command))
{
author = dbCommand.ExecuteObject<Author>();
}
if (author != null)
{
return author;
}
if (String.IsNullOrEmpty(info.FirstName) && String.IsNullOrEmpty(info.LastName) && !String.IsNullOrEmpty(info.NickName))
{
commandText =
@"SELECT autor.* FROM autor WHERE UPPERCASE(autor.nickname) = UPPERCASE(@nickname)";
command = this.connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = commandText;
command.Transaction = transaction as FbTransaction;
command.Parameters.Add("@nickname", FbDbType.VarChar, 40).Value = info.NickName;
using (SqlDbCommand dbCommand = new SqlDbCommand(command))
{
author = dbCommand.ExecuteObject<Author>();
}
if (author != null)
{
return author;
}
}
author = new Author();
author.FirstName = info.FirstName;
author.MiddleName = info.MiddleName;
author.LastName = info.LastName;
author.NickName = info.NickName;
author.LibraryId = info.Id;
author.EMail = StringUtils.Truncate(StringUtils.Join(", ", info.Email), 125);
author.Homepage = StringUtils.Truncate(StringUtils.Join(", ", info.Homepage), 125);
commandText =
"INSERT INTO AUTOR (AUTORID, FIRSTNAME, MIDNAME, LASTNAME, NICKNAME, EMAIL, HOMEPAGE) " +
"VALUES (GEN_ID(GEN_AUTOR_ID, 1), @firstname, @midname, @lastname, @nickname, @email, @homepage) " +
"RETURNING AUTORID";
using (FbCommand cmd = this.connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = commandText;
cmd.Transaction = transaction as FbTransaction;
cmd.Parameters.Add("@firstname", FbDbType.VarChar, 40).Value = author.FirstName ?? String.Empty;
cmd.Parameters.Add("@midname", FbDbType.VarChar, 40).Value = author.MiddleName ?? String.Empty;
cmd.Parameters.Add("@lastname", FbDbType.VarChar, 40).Value = author.LastName ?? String.Empty;
cmd.Parameters.Add("@nickname", FbDbType.VarChar, 40).Value = author.NickName ?? String.Empty;
cmd.Parameters.Add("@email", FbDbType.VarChar, 125).Value = author.EMail;
cmd.Parameters.Add("@homepage", FbDbType.VarChar, 125).Value = author.Homepage;
cmd.Parameters.Add("@authorid", FbDbType.Integer).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
author.Id = Convert.ToInt32(cmd.Parameters["@authorid"].Value);
}
if (!String.IsNullOrEmpty(author.LibraryId))
{
commandText =
"INSERT INTO AUTHOR_INFO (AUTORID, ID) VALUES (@autorid, @id)";
using (FbCommand cmd = this.connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = commandText;
cmd.Transaction = transaction as FbTransaction;
cmd.Parameters.Add("@autorid", FbDbType.Integer).Value = author.Id;
cmd.Parameters.Add("@id", FbDbType.VarChar, 254).Value = author.LibraryId;
cmd.ExecuteNonQuery();
}
}
List<Author> authors = AuthorList;
authors.Add(author);
}
catch (FbException exp)
{
throw new DatabaseException(exp.Message, exp);
}
finally
{
this.manager.EndConnect();
}
return author;
}
public Author FindOrCreateTranslator(AuthorInfoNode info)
{
return FindOrCreateTranslator(info, null);
}
public List<Author> FindSimularAuthors(AuthorInfoNode info)
{
if (info == null)
{
throw new ArgumentNullException("info");
}
List<Author> result = new List<Author>();
List<Author> searchList = this.AuthorList;
//BKTree<Author> tree = new BKTree<Author>(searchList, null);
//IList<Author> test = tree.RangeSearch(info, (x, y) => StringUtils.DamerauLevenshteinDistance(x.DisplayName, y.DisplayName), 0, 5);
foreach (Author author in searchList)
{
int firstNameDistance = StringUtils.DamerauLevenshteinDistance(info.FirstName ?? String.Empty, author.FirstName ?? String.Empty);
int middleNameDistance = StringUtils.DamerauLevenshteinDistance(info.MiddleName ?? String.Empty, author.MiddleName ?? String.Empty);
int lastNameDistance = StringUtils.DamerauLevenshteinDistance(info.LastName ?? String.Empty, author.LastName ?? String.Empty);
double sourceMetric = (info.FirstName ?? String.Empty).Length * 0.45 + (info.MiddleName ?? String.Empty).Length * 0.1 + (info.LastName ?? String.Empty).Length * 0.45;
double distanceMetric = firstNameDistance * 0.45 + middleNameDistance * 0.1 + lastNameDistance * 0.45;
double metric = (sourceMetric > 0) ? (1 - distanceMetric / sourceMetric) * 100 : 0;
if (metric >= 70.0)
{
result.Add(author);
}
}
return result;
}
public Author FindOrCreateDocumentAuthor(AuthorInfoNode info)
{
return FindOrCreateDocumentAuthor(info, null);
}
