///<summary>No error checking. Only called from WikiLists.</summary>
public static void InsertNew(WikiListHeaderWidth newWidth)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), newWidth);
return;
}
Crud.WikiListHeaderWidthCrud.Insert(newWidth);
RefreshCache();
}
///<summary>Also alters the db table for the list itself. Throws exception if number of columns does not match.</summary>
public static void UpdateNamesAndWidths(string listName, List <WikiListHeaderWidth> columnDefs)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), listName, columnDefs);
return;
}
string command = "DESCRIBE wikilist_" + POut.String(listName);
DataTable tableListDescription = Db.GetTable(command);
if (tableListDescription.Rows.Count != columnDefs.Count)
{
throw new ApplicationException("List schema has been altered. Unable to save changes to list.");
}
List <string> listChanges = new List <string>();
//rename Columns with dummy names in case user is renaming a new column with an old name.---------------------------------------------
for (int i = 1; i < tableListDescription.Rows.Count; i++) //start with index 1 to skip PK col
{
DataRow row = tableListDescription.Rows[i];
listChanges.Add($"CHANGE {POut.String(row[0].ToString())} {POut.String(DummyColName+i)} TEXT NOT NULL");
command = $@"UPDATE wikilistheaderwidth SET ColName='{POut.String(DummyColName+i)}'
WHERE ListName='{POut.String(listName)}'
AND ColName='{POut.String(row[0].ToString())}'" ;
Db.NonQ(command);
}
Db.NonQ($"ALTER TABLE wikilist_{POut.String(listName)} {string.Join(",",listChanges)}");
listChanges = new List <string>();
//rename columns names and widths-------------------------------------------------------------------------------------------------------
for (int i = 1; i < tableListDescription.Rows.Count; i++) //start with index 1 to skip PK col
{
WikiListHeaderWidth wCur = columnDefs[i];
listChanges.Add($"CHANGE {POut.String(DummyColName+i)} {POut.String(wCur.ColName)} TEXT NOT NULL");
command = $@"UPDATE wikilistheaderwidth
SET ColName='{POut.String(wCur.ColName)}',ColWidth='{POut.Int(wCur.ColWidth)}',PickList='{POut.String(wCur.PickList)}'
WHERE ListName='{POut.String(listName)}'
AND ColName='{POut.String(DummyColName+i)}'" ;
Db.NonQ(command);
}
Db.NonQ($"ALTER TABLE wikilist_{POut.String(listName)} {string.Join(",",listChanges)}");
//handle width of PK seperately because we do not rename the PK column, ever.
command = $@"UPDATE wikilistheaderwidth
SET ColWidth='{POut.Int(columnDefs[0].ColWidth)}',
PickList='{POut.String(columnDefs[0].PickList)}'
WHERE ListName='{POut.String(listName)}'
AND ColName='{POut.String(columnDefs[0].ColName)}'" ;
Db.NonQ(command);
RefreshCache();
}
public static List <WikiListHeaderWidth> GetFromListHist(WikiListHist wikiListHist)
{
List <WikiListHeaderWidth> retVal = new List <WikiListHeaderWidth>();
string[] arrayHeaders = wikiListHist.ListHeaders.Split(';');
for (int i = 0; i < arrayHeaders.Length; i++)
{
WikiListHeaderWidth hw = new WikiListHeaderWidth();
hw.ListName = wikiListHist.ListName;
hw.ColName = arrayHeaders[i].Split(',')[0];
hw.ColWidth = PIn.Int(arrayHeaders[i].Split(',')[1]);
retVal.Add(hw);
}
return(retVal);
}
/// <summary>Creates empty table with a single column for PK. List name must be formatted correctly before being passed here. Ie. no spaces, all lowercase.</summary>
public static void CreateNewWikiList(string listName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), listName);
return;
}
//listname is checked in the UI for proper format.
string command = "CREATE TABLE wikilist_" + POut.String(listName) + " (" + POut.String(listName) + "Num bigint NOT NULL auto_increment PRIMARY KEY ) DEFAULT CHARSET=utf8";
Db.NonQ(command);
WikiListHeaderWidth headerWidth = new WikiListHeaderWidth();
headerWidth.ColName = listName + "Num";
headerWidth.ColWidth = 100;
headerWidth.ListName = listName;
WikiListHeaderWidths.InsertNew(headerWidth);
}
///<summary>Column is automatically named "Column#" where # is the number of columns+1.</summary>
public static void AddColumn(string listName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), listName);
return;
}
//Find Valid column name-----------------------------------------------------------------------------------------
DataTable columnNames = Db.GetTable("DESCRIBE wikilist_" + POut.String(listName));
string newColumnName = "Column1"; //default in case table has no columns. Should never happen.
for (int i = 0; i < columnNames.Rows.Count + 1; i++) //+1 to guarantee we can find a valid name.
{
newColumnName = "Column" + (1 + i); //ie. Column1, Column2, Column3...
for (int j = 0; j < columnNames.Rows.Count; j++)
{
if (newColumnName == columnNames.Rows[j]["Field"].ToString())
{
newColumnName = "";
break;
}
}
if (newColumnName != "")
{
break; //found a valid name.
}
}
if (newColumnName == "")
{
//should never happen.
throw new ApplicationException("Could not create valid column name.");
}
//Add new column name--------------------------------------------------------------------------------------------
string command = "ALTER TABLE wikilist_" + POut.String(listName) + " ADD COLUMN " + POut.String(newColumnName) + " TEXT NOT NULL";
Db.NonQ(command);
//Add column widths to wikiListHeaderWidth Table-----------------------------------------------------------------
WikiListHeaderWidth headerWidth = new WikiListHeaderWidth();
headerWidth.ColName = newColumnName;
headerWidth.ListName = listName;
headerWidth.ColWidth = 100;
WikiListHeaderWidths.InsertNew(headerWidth);
}
/*///<summary>Returns header widths for list sorted in the same order as the columns appear in the DB. Can be more efficient than using cache.</summary>
* public static List<WikiListHeaderWidth> GetForListNoCache(string listName) {
* if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
* return Meth.GetObject<List<WikiListHeaderWidth>>(MethodBase.GetCurrentMethod(),listName);
* }
* List<WikiListHeaderWidth> retVal = new List<WikiListHeaderWidth>();
* List<WikiListHeaderWidth> tempList = new List<WikiListHeaderWidth>();
* string command="DESCRIBE wikilist_"+POut.String(listName);
* DataTable listDescription=Db.GetTable(command);
* command="SELECT * FROM wikilistheaderwidth WHERE ListName='"+POut.String(listName)+"'";
* tempList=Crud.WikiListHeaderWidthCrud.SelectMany(command);
* for(int i=0;i<listDescription.Rows.Count;i++) {
* for(int j=0;j<tempList.Count;j++) {
* //Add WikiListHeaderWidth from tempList to retVal if it is the next row in listDescription.
* if(listDescription.Rows[i][0].ToString()==tempList[j].ColName) {
* retVal.Add(tempList[j]);
* break;
* }
* }
* //next description row.
* }
* return retVal;
* }*/
///<summary>Returns header widths for list sorted in the same order as the columns appear in the DB. Uses cache.</summary>
public static List <WikiListHeaderWidth> GetForList(string listName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <WikiListHeaderWidth> >(MethodBase.GetCurrentMethod(), listName));
}
List <WikiListHeaderWidth> retVal = new List <WikiListHeaderWidth>();
string command = "DESCRIBE wikilist_" + POut.String(listName);//TODO: Oracle compatible?
DataTable tableDescripts = Db.GetTable(command);
List <WikiListHeaderWidth> listHeaderWidths = GetWhere(x => x.ListName == listName);
for (int i = 0; i < tableDescripts.Rows.Count; i++)
{
WikiListHeaderWidth addWidth = listHeaderWidths.Where(x => x.ColName == tableDescripts.Rows[i][0].ToString()).FirstOrDefault();
if (addWidth != null)
{
retVal.Add(addWidth);
}
}
return(retVal);
}
///<summary>No error checking. Only called from WikiLists.</summary>
public static void InsertNew(WikiListHeaderWidth newWidth) {
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
Meth.GetVoid(MethodBase.GetCurrentMethod(),newWidth);
return;
}
Crud.WikiListHeaderWidthCrud.Insert(newWidth);
RefreshCache();
}
/// <summary>Creates empty table with a single column for PK. List name must be formatted correctly before being passed here. Ie. no spaces, all lowercase.</summary>
public static void CreateNewWikiList(string listName) {
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
Meth.GetVoid(MethodBase.GetCurrentMethod(),listName);
return;
}
//listname is checked in the UI for proper format.
string command = "CREATE TABLE wikilist_"+POut.String(listName)+" ("+POut.String(listName)+"Num bigint NOT NULL auto_increment PRIMARY KEY ) DEFAULT CHARSET=utf8";
Db.NonQ(command);
WikiListHeaderWidth headerWidth = new WikiListHeaderWidth();
headerWidth.ColName=listName+"Num";
headerWidth.ColWidth=100;
headerWidth.ListName=listName;
WikiListHeaderWidths.InsertNew(headerWidth);
}
///<summary>Column is automatically named "Column#" where # is the number of columns+1.</summary>
public static void AddColumn(string listName) {
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
Meth.GetVoid(MethodBase.GetCurrentMethod(),listName);
return;
}
//Find Valid column name-----------------------------------------------------------------------------------------
DataTable columnNames = Db.GetTable("DESCRIBE wikilist_"+POut.String(listName));
string newColumnName="Column1";//default in case table has no columns. Should never happen.
for(int i=0;i<columnNames.Rows.Count+1;i++) {//+1 to guarantee we can find a valid name.
newColumnName="Column"+(1+i);//ie. Column1, Column2, Column3...
for(int j=0;j<columnNames.Rows.Count;j++) {
if(newColumnName==columnNames.Rows[j]["Field"].ToString()) {
newColumnName="";
break;
}
}
if(newColumnName!="") {
break;//found a valid name.
}
}
if(newColumnName=="") {
//should never happen.
throw new ApplicationException("Could not create valid column name.");
}
//Add new column name--------------------------------------------------------------------------------------------
string command = "ALTER TABLE wikilist_"+POut.String(listName)+" ADD COLUMN "+POut.String(newColumnName)+" TEXT NOT NULL";
Db.NonQ(command);
//Add column widths to wikiListHeaderWidth Table-----------------------------------------------------------------
WikiListHeaderWidth headerWidth = new WikiListHeaderWidth();
headerWidth.ColName=newColumnName;
headerWidth.ListName=listName;
headerWidth.ColWidth=100;
WikiListHeaderWidths.InsertNew(headerWidth);
}
public static List<WikiListHeaderWidth> GetFromListHist(WikiListHist wikiListHist) {
List<WikiListHeaderWidth> retVal=new List<WikiListHeaderWidth>();
string[] arrayHeaders=wikiListHist.ListHeaders.Split(';');
for(int i=0;i<arrayHeaders.Length;i++) {
WikiListHeaderWidth hw=new WikiListHeaderWidth();
hw.ListName=wikiListHist.ListName;
hw.ColName=arrayHeaders[i].Split(',')[0];
hw.ColWidth=PIn.Int(arrayHeaders[i].Split(',')[1]);
retVal.Add(hw);
}
return retVal;
}
