/**
* \param count Number of users to generate
* \param database DB to use in query.
* \return Generated SQL query.
* \brief Generates the number of given users using random pieces of data, for random looking users (User, Address, UserToTag)
*/
public static string GenerateUsers(int count, string database)
{
string result = "USE " + database + ";\n";
int startAddressID = GetAutoIncrementID(database, "Address");
int startUserID = GetAutoIncrementID(database, "User");
if (startAddressID < 0)
{
startAddressID = 1;
}
if (startUserID < 0)
{
startUserID = 1;
}
// Container for different tables / insert statement groups
List <List <string> > addresses = CustomGenerator.GetAddresses(count);
List <List <string> > users = CustomGenerator.GetUsers(count, startAddressID);
List <string> categoryIDs = Database.GetRows(count, Database.DB_MAIN, "Category", "ID", Database.FORMAT_NUMBER);
List <List <string> > userTags = CustomGenerator.GetTags(count, 4, categoryIDs);
// Format lists into query statements
result += PrepareOutput(count, "Address", addresses, new bool[] { true, true, false, true });
result += PrepareOutput(count, "User", users, new bool[] { false, true, true, true, true, true });
result += PrepareOutputTags("UserToTag", userTags, startUserID);
//result += CompactQueries("Address", addresses.ToArray(), new bool[] { true, true, false, true });
//result += CompactQueries("User", users.ToArray(), new bool[] { false, true, true, true, true, true });
//result += PrepareOutputTags("UserToTag", userTags, startUserID);
return(result);
}
/**
* \param database The database to use.
* \return List<string> The table list.
* \brief Returns a list of tables in the given database. If the db is set to retrieve 'Custom' tables, it will do so.
*/
public static List <string> GetSourceTables(string database)
{
List <string> tables = new List <string>();
if (database == DB_CUSTOM)
{
tables = CustomGenerator.GetMethods();
}
else if (database != DB_NONE && database != DB_NULL && database != DB_EMPTY)
{
tables = GetTables(database);
}
return(tables);
}
/**
* \param count Number of events to generate
* \param database DB to insert into.
* \return string The SQL query.
* \brief Generates the number of requested events, randomly selecting data where needed to create random data (Product, Event, ProductToTag, ProductToEvent, SellerToEvent).
*/
public static string GenerateEvents(int count, string database)
{
string result = "USE " + database + ";\n";
int startProductID = GetAutoIncrementID(database, "Product");
int startEventID = GetAutoIncrementID(database, "Event");
if (startProductID < 0)
{
startProductID = 1;
}
if (startEventID < 0)
{
startEventID = 1;
}
// Container for different tables / insert statement groups
// List of users (sellers)
List <string> sellerIDs = Database.GetRows(count, Database.DB_MAIN, "User", "ID", Database.FORMAT_NUMBER);
// Product
List <List <string> > products = CustomGenerator.GetProducts(count, sellerIDs, startEventID, startProductID);
List <string> categoryIDsEvent = Database.GetRows(count, Database.DB_MAIN, "Category", "ID", Database.FORMAT_NUMBER);
// Loop through and create a categoryID for each product set
// (one item for each product, needed so that events have the same product types, ex. (1, 1, 5, 3, 3, 3, 2, 2))
List <string> categoryIDsProduct = new List <string>();
List <string> currentBiddingIDs = new List <string>();
int j = 0;
string prevEventID = "";
for (int i = 0; i < products[0].Count(); i++)
{
string currEventID = products[5][i];
if (i != 0 && prevEventID != currEventID)
{
j++;
}
// Track first product in each event
if (prevEventID != currEventID)
{
currentBiddingIDs.Add((i + startProductID).ToString());
}
categoryIDsProduct.Add(categoryIDsEvent[j]);
prevEventID = currEventID;
}
List <List <string> > productTags = CustomGenerator.GetTags(products[0].Count(), 1, categoryIDsProduct);
List <string> productsFirstTagID = new List <string>();
for (int i = 0; i < productTags.Count(); i++)
{
productsFirstTagID.Add(productTags[i][0]);
}
// Event
List <List <string> > events = CustomGenerator.GetEvents(count, currentBiddingIDs, categoryIDsEvent);
// Update products to have the correct images
List <string> productImages = CustomGenerator.GetRowsByFilterID(products[0].Count(), productsFirstTagID, DB_SAMPLES, "ProductImages", "Value", "TagID", "Tag");
products.Insert(3, productImages);
// Set the event image to the first product image
List <string> eventImages = new List <string>();
prevEventID = "";
for (int i = 0; i < products[0].Count(); i++)
{
string currEventID = products[6][i];
// Track first product in each event
if (prevEventID != currEventID)
{
eventImages.Add(products[3][i]);
}
prevEventID = currEventID;
}
events.Insert(4, eventImages);
// Product to Events
List <List <string> > productsToEvents = new List <List <string> >();
List <string> eventIDs = products[products.Count() - 2];
List <string> productIDs = products[products.Count() - 1];
productsToEvents.Add(eventIDs);
productsToEvents.Add(productIDs);
// Seller to Events
List <List <string> > sellerToEvents = new List <List <string> >();
List <string> sellerToEventEventIDs = new List <string>();
for (int i = 0; i < count; i++)
{
string eventIDSingle = (i + startEventID).ToString();
sellerToEventEventIDs.Add(eventIDSingle);
}
sellerToEvents.Add(sellerIDs);
sellerToEvents.Add(sellerToEventEventIDs);
// Remove temporary data holders (productId, eventId)
products.RemoveAt(products.Count() - 1);
products.RemoveAt(products.Count() - 1);
// Format lists into query statements
int fullCount = products[0].Count();
result += PrepareOutput(fullCount, "Product", products, new bool[] { true, false, true, true, false, false });
result += PrepareOutput(count, "Event", events, new bool[] { true, true, true, true, true, false, false });
//result += PrepareOutputTags("ProductToTag", productTags, startProductID);
//result += PrepareOutput(fullCount, "ProductToEvent", productsToEvents, new bool[] { false, false });
//result += PrepareOutput(count, "SellerToEvent", sellerToEvents, new bool[] { false, false });
//result += CompactQueries("Product", products.ToArray(), new bool[] { true, false, true, true, false, false });
//result += CompactQueries("Event", events.ToArray(), new bool[] { true, true, true, true, true, false });
result += PrepareOutputTags("ProductToTag", productTags, startProductID);
result += CompactQueries("ProductToEvent", productsToEvents.ToArray(), new bool[] { false, false });
result += CompactQueries("SellerToEvent", sellerToEvents.ToArray(), new bool[] { false, false });
return(result);
}
/**
* \param dgv DataGridView to modify
* \param count The number of loops required to run
* \param database The DB to use.
* \param table The table to insert into.
* \return string The insert statement
* \brief Generate an insert statement based on the data in the provided DGV.
*/
public static string GenerateQuery(DataGridView dgv, int count, string database, string table)
{
// Sample Output: "USE liveart_db; INSERT INTO Person VALUES ('Bailey', 'Mills', 'A1A1A1', 0);"
bool valid = true;
// Prepare data from datagridview
List <List <string> > items = new List <List <string> >();
List <Summary> data = new List <Summary>();
// Loop through all columns
for (int i = 0; i < dgv.Columns.Count; i++)
{
string sDatabase = "";
string sTable = "";
string sColumn = "";
string sFormat = "";
// Pull data from each row of the current column
var _database = dgv.Rows[0].Cells[i].Value;
var _table = dgv.Rows[1].Cells[i].Value;
var _column = dgv.Rows[2].Cells[i].Value;
var _format = dgv.Rows[3].Cells[i].Value;
// Conver to string if possible
if (_database != null)
{
sDatabase = _database.ToString();
}
if (_table != null)
{
sTable = _table.ToString();
}
if (_column != null)
{
sColumn = _column.ToString();
}
if (_format != null)
{
sFormat = _format.ToString();
}
data.Add(new Summary(sDatabase, sTable, sColumn, sFormat));
}
// Loop through the columns of the dgv and query the db for sample data
foreach (Summary col in data)
{
List <string> currResults = new List <string>();
if (col.valid)
{
if (col.customQuery)
{
CustomGenerator cg = new CustomGenerator();
MethodInfo method = cg.GetType().GetMethod(col.table);
currResults = (List <string>)method.Invoke(cg, new object[] { count });
}
else if (!col.runQuery)
{
if (col.includeValue)
{
for (int i = 0; i < count; i++)
{
currResults.Add(col.column);
}
}
}
else
{
currResults = GetRows(count, col.database, col.table, col.column, col.format);
}
// Add the current item to the list of items
items.Add(currResults);
}
else
{
valid = false;
break;
}
}
return(PrepareOutputReverse(count, database, table, items, valid));
}
