/// <summary>
/// 根据表名获取与表相关的约束（主外键关系）
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
private IEnumerable <DBRelation> GetTableRelation()
{
List <DBRelation> tableRelas = new List <DBRelation>();
const string sqlstr = @"SELECT ConstrName=OBJECT_NAME(b.constid)
,ForeignTableId=b.fkeyid
,ForeignTableName=object_name(b.fkeyid)
,ForeignKey=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
,PrimaryTableId=b.rkeyid
,PrimaryTableName=object_name(b.rkeyid)
,PrimaryKey=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
,IsUpdateCascade=ObjectProperty(a.id,'CnstIsUpdateCascade')
,IsDeleteCascade=ObjectProperty(a.id,'CnstIsDeleteCascade')
FROM sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'" ;
var tb = _helper.ExecDataTable(sqlstr);
for (int i = 0; i < tb.Rows.Count; i++)
{
DBRelation tableRela = new DBRelation
{
Id = i + 1,
ConstrName = CommOp.ToStr(tb.Rows[i]["ConstrName"]),//约束名
PrimaryTable = CommOp.ToStr(tb.Rows[i]["PrimaryTableName"]),
ForeignTable = CommOp.ToStr(tb.Rows[i]["ForeignTableName"]),
PrimaryKey = CommOp.ToStr(tb.Rows[i]["PrimaryKey"]),
ForeignKey = CommOp.ToStr(tb.Rows[i]["ForeignKey"]),
IsUpdateCascade = CommOp.ToStr(tb.Rows[i]["IsUpdateCascade"]) == "1",
IsDeleteCascade = CommOp.ToStr(tb.Rows[i]["IsDeleteCascade"]) == "1",
};
tableRelas.Add(tableRela);
}
return(tableRelas);
}
private void generateFieldList()
{
// add fields from primary type
fieldComboBox.Items.Clear();
foreach (DBField currField in DBField.GetFieldList(Table))
{
if (currField.Filterable && currField.AllowDynamicFiltering)
{
ComboFieldWrapper wrapper = new ComboFieldWrapper(currField);
wrapperLookup[currField] = wrapper;
fieldComboBox.Items.Add(wrapper);
}
}
// add fields from secondary types
foreach (DBRelation currRelation in DBRelation.GetRelations(Table))
{
if (!currRelation.Filterable)
{
continue;
}
foreach (DBField currField in DBField.GetFieldList(currRelation.SecondaryType))
{
if (currField.Filterable && currField.AllowDynamicFiltering)
{
ComboFieldWrapper wrapper = new ComboFieldWrapper(currField, currRelation);
wrapperLookup[currField] = wrapper;
fieldComboBox.Items.Add(wrapper);
}
}
}
fieldComboBox.SelectedIndex = 0;
}
// initialize all values of the given object. essentially makes the object a new refernece
// and a new row will be created if commited
public void Clear()
{
id = null;
commitNeeded = true;
RetrievalInProcess = true;
ReadOnlyCollection <DBField> fieldList = DBField.GetFieldList(this.GetType());
foreach (DBField currField in fieldList)
{
object defaultVal = currField.Default;
currField.SetValue(this, defaultVal);
// if this is a dynamic (internally changable) object, setup a listener
if (defaultVal != null && defaultVal.GetType() == typeof(IDynamic))
{
((IDynamic)defaultVal).Changed += new ChangedEventHandler(commitNeededEventHandler);
}
}
ReadOnlyCollection <DBRelation> relationList = DBRelation.GetRelations(this.GetType());
foreach (DBRelation currRelation in relationList)
{
try {
currRelation.GetRelationList(this).Changed += new ChangedEventHandler(commitNeededEventHandler);
}
catch (NullReferenceException) {
throw new System.InvalidOperationException("RelationLists must be initialized in the get{} property method.");
}
}
RetrievalInProcess = false;
}
private void addDynamicNode(DBField field, DBRelation relation, bool forceRoot)
{
if (_menu == null)
{
return;
}
SuspendLayout();
// build internal node object
DBNode <T> newNode = new DBNode <T>();
newNode.Name = field.FriendlyName;
newNode.BasicFilteringField = field;
newNode.BasicFilteringRelation = relation;
newNode.DynamicNode = true;
newNode.DBManager = DBManager;
count++;
// build treenode object (for the UI)
asyncInput = newNode;
forceNewNodeRoot = forceRoot;
ProgressPopup p = new ProgressPopup(new WorkerDelegate(createTreeNodeAsyncWorker));
p.Owner = FindForm();
p.Text = "Building Menu Tree";
p.WorkComplete += new WorkCompleteDelegate(addDynamicNode_TreeNodeCreated);
p.ShowDialogDelayed(300);
}
public static void VerifyFilterMenu()
{
DBMenu <DBTrackInfo> menu = mvCentralCore.Settings.FilterMenu;
if (menu.RootNodes.Count == 0)
{
int position = 1;
DBNode <DBTrackInfo> unwatchedNode = new DBNode <DBTrackInfo>();
unwatchedNode.Name = "${UnwatchedmvCentral}";
unwatchedNode.DynamicNode = false;
unwatchedNode.Filter = new DBFilter <DBTrackInfo>();
DBCriteria <DBTrackInfo> criteria = new DBCriteria <DBTrackInfo>();
criteria.Field = DBField.GetFieldByDBName(typeof(DBUserMusicVideoSettings), "watched");
criteria.Relation = DBRelation.GetRelation(typeof(DBTrackInfo), typeof(DBUserMusicVideoSettings), "");
criteria.Operator = DBCriteria <DBTrackInfo> .OperatorEnum.EQUAL;
criteria.Value = "0";
unwatchedNode.Filter.Criteria.Add(criteria);
unwatchedNode.SortPosition = position++;
unwatchedNode.DBManager = mvCentralCore.DatabaseManager;
menu.RootNodes.Add(unwatchedNode);
DBNode <DBTrackInfo> genreNode = new DBNode <DBTrackInfo>();
genreNode.DynamicNode = true;
genreNode.BasicFilteringField = DBField.GetFieldByDBName(typeof(DBTrackInfo), "genres");
genreNode.Name = "${Genre}";
genreNode.DBManager = mvCentralCore.DatabaseManager;
genreNode.SortPosition = position++;
menu.RootNodes.Add(genreNode);
DBNode <DBTrackInfo> dateNode = new DBNode <DBTrackInfo>();
dateNode.DynamicNode = true;
dateNode.BasicFilteringField = DBField.GetFieldByDBName(typeof(DBTrackInfo), "date_added");
dateNode.Name = "${DateAdded}";
dateNode.DBManager = mvCentralCore.DatabaseManager;
dateNode.SortPosition = position++;
menu.RootNodes.Add(dateNode);
menu.Commit();
}
foreach (DBNode <DBTrackInfo> currNode in menu.RootNodes)
{
currNode.UpdateDynamicNode();
currNode.Commit();
}
}
private void populateFieldCombo()
{
fieldChanging = true;
// add fields from primary type
fieldComboBox.Items.Clear();
foreach (DBField currField in DBField.GetFieldList(typeof(T)))
{
if (currField.Filterable)
{
ComboFieldWrapper wrapper = new ComboFieldWrapper(currField);
wrapperLookup[currField] = wrapper;
fieldComboBox.Items.Add(wrapper);
}
}
// add fields from secondary types
foreach (DBRelation currRelation in DBRelation.GetRelations(typeof(T)))
{
if (!currRelation.Filterable)
{
continue;
}
foreach (DBField currField in DBField.GetFieldList(currRelation.SecondaryType))
{
if (currField.Filterable)
{
ComboFieldWrapper wrapper = new ComboFieldWrapper(currField, currRelation);
wrapperLookup[currField] = wrapper;
fieldComboBox.Items.Add(wrapper);
}
}
}
if (Criteria != null && Criteria.Field != null && wrapperLookup.ContainsKey(Criteria.Field))
{
fieldComboBox.SelectedItem = wrapperLookup[Criteria.Field];
}
else
{
fieldComboBox.SelectedIndex = 0;
}
fieldChanging = false;
}
// create the dictionary to map variables to the correct values for this movie
public static Dictionary <string, string> getVariableMapping(DatabaseTable obj)
{
// add fields from primary object
Dictionary <string, string> replacementStrings = new Dictionary <string, string>();
foreach (DBField currField in DBField.GetFieldList(obj.GetType()))
{
if (currField.Filterable && currField.GetValue(obj) != null)
{
replacementStrings[currField.FieldName] = currField.GetValue(obj).ToString().Trim();
}
}
// add fields from secondary types
foreach (DBRelation currRelation in DBRelation.GetRelations(obj.GetType()))
{
if (!currRelation.Filterable)
{
continue;
}
if (currRelation.GetRelationList(obj).Count > 0)
{
DatabaseTable subObj = (DatabaseTable)currRelation.GetRelationList(obj)[0];
foreach (DBField currField in DBField.GetFieldList(currRelation.SecondaryType))
{
if (currField.Filterable && currField.GetValue(subObj) != null)
{
replacementStrings[currField.FieldName] = currField.GetValue(subObj).ToString().Trim();
}
}
}
}
return(replacementStrings);
}
public static void VerifyCategoryMenu()
{
DBMenu <DBTrackInfo> menu = mvCentralCore.Settings.CategoriesMenu;
if (menu.RootNodes.Count == 0)
{
int position = 1;
DBNode <DBTrackInfo> allNode = new DBNode <DBTrackInfo>();
allNode.Name = "${AllmvCentral}";
allNode.DynamicNode = false;
allNode.Filter = new DBFilter <DBTrackInfo>();
allNode.SortPosition = position++;
allNode.DBManager = mvCentralCore.DatabaseManager;
menu.RootNodes.Add(allNode);
DBNode <DBTrackInfo> unwatchedNode = new DBNode <DBTrackInfo>();
unwatchedNode.Name = "${UnwatchedMusicVideo}";
unwatchedNode.DynamicNode = false;
unwatchedNode.Filter = new DBFilter <DBTrackInfo>();
DBCriteria <DBTrackInfo> criteria = new DBCriteria <DBTrackInfo>();
criteria.Field = DBField.GetFieldByDBName(typeof(DBUserMusicVideoSettings), "watched");
criteria.Relation = DBRelation.GetRelation(typeof(DBTrackInfo), typeof(DBUserMusicVideoSettings), "");
criteria.Operator = DBCriteria <DBTrackInfo> .OperatorEnum.EQUAL;
criteria.Value = "0";
unwatchedNode.Filter.Criteria.Add(criteria);
unwatchedNode.SortPosition = position++;
unwatchedNode.DBManager = mvCentralCore.DatabaseManager;
menu.RootNodes.Add(unwatchedNode);
DBNode <DBTrackInfo> recentNode = new DBNode <DBTrackInfo>();
recentNode.Name = "${RecentlyAddedmvCentral}";
recentNode.DynamicNode = false;
recentNode.Filter = new DBFilter <DBTrackInfo>();
recentNode.SortPosition = position++;
recentNode.DBManager = mvCentralCore.DatabaseManager;
DBCriteria <DBTrackInfo> recentCriteria = new DBCriteria <DBTrackInfo>();
recentCriteria.Field = DBField.GetFieldByDBName(typeof(DBTrackInfo), "date_added");
recentCriteria.Operator = DBCriteria <DBTrackInfo> .OperatorEnum.GREATER_THAN;
recentCriteria.Value = "-30d";
recentNode.Filter.Criteria.Add(recentCriteria);
DBMusicVideoNodeSettings additionalSettings = new DBMusicVideoNodeSettings();
additionalSettings.UseDefaultSorting = false;
// additionalSettings.SortField = SortingFields.DateAdded;
// additionalSettings.SortDirection = SortingDirections.Descending;
recentNode.AdditionalSettings = additionalSettings;
menu.RootNodes.Add(recentNode);
DBNode <DBTrackInfo> genreNode = new DBNode <DBTrackInfo>();
genreNode.DynamicNode = true;
genreNode.BasicFilteringField = DBField.GetFieldByDBName(typeof(DBTrackInfo), "genres");
genreNode.Name = "${Genres}";
genreNode.SortPosition = position++;
genreNode.DBManager = mvCentralCore.DatabaseManager;
menu.RootNodes.Add(genreNode);
menu.Commit();
}
foreach (DBNode <DBTrackInfo> currNode in menu.RootNodes)
{
currNode.UpdateDynamicNode();
currNode.Commit();
}
}
public ComboFieldWrapper(DBField field, DBRelation relation)
{
_field = field;
_relation = relation;
}
