public override List <idx_table> table_idxs(string table, bool?uniques = null, string index_name = "")
{
List <idx_table> indexes = new List <idx_table>();
foreach (DataRow row in schemaIndexes(table).Rows)
{
string nm = row["index_name"].ToString();
bool unique = bool.Parse(row["unique"].ToString());
bool primary = bool.Parse(row["primary_key"].ToString());
if (index_name != "" && index_name.ToLower() != nm.ToLower() ||
constraintType(table, nm) == "FOREIGN KEY" || !idx_table.filter_unique(uniques, unique, primary))
{
continue;
}
idx_table index = indexes.FirstOrDefault(x => x.name == nm);
if (index == null)
{
indexes.Add(index = new idx_table(table, nm, bool.Parse(row["clustered"].ToString()), unique, primary));
}
index.fields.Add(new idx_field(row["column_name"].ToString(), int.Parse(row["collation"].ToString()) == 1, index.fields.Count));
}
return(indexes);
}
//public override void create_table(XmlNode tableNode, bool createIndexes = true, string nameCreated = "", List<string> flds_null = null) {
// string tableName = nameCreated != "" ? nameCreated : tableNode.Attributes["name"].Value;
// // schema
// if (tableName.IndexOf('.') >= 0) {
// string schemaName = tableName.Substring(0, tableName.IndexOf('.'));
// if (!there_schema(schemaName)) create_schema(schemaName);
// }
// // creo la tabella
// exec("CREATE TABLE " + tableName + "("
// + string.Join(",", tableNode.SelectNodes("cols/col").Cast<XmlNode>().Select(x => schema_field.getFieldSqlServer(x.Attributes["name"].Value
// , x.Attributes["type"].Value, xmlDoc.nodeValue(x, "numprec"), xmlDoc.nodeValue(x, "numscale"), xmlDoc.nodeValue(x, "maxlength")
// , xmlDoc.nodeBool(x, "nullable", false), xmlDoc.nodeValue(x, "default"), xmlDoc.nodeBool(x, "autonumber")))) + ")");
// // creo gli indici
// if (createIndexes) schema_doc.table_indexes(tableNode).ForEach(i => {
// i.TableName = tableName;
// if (nameCreated != "") i.Name = i.Name + "_" + nameCreated.Replace(".", "");
// create_index(i);
// });
//}
public override List <idx_table> table_idxs(string table, bool?uniques = null, string index_name = "")
{
List <idx_table> indexes = new List <idx_table>();
DataTable rows = dt_table("select distinct ind.index_name, ind.index_type, ind.non_unique "
+ " from information_schema.statistics ind "
+ " where ind.table_schema = '" + db_name() + "' and ind.table_name = '" + table + "' "
+ (index_name != "" ? " and ind.index_name = '" + index_name + "'" : ""));
foreach (DataRow row in rows.Rows)
{
// clustered
bool?clustered = false;
// filtro uniques
bool unique = row["non_unique"].ToString() == "0", primary = row["index_name"].ToString() == "PRIMARY";
if (idx_table.filter_unique(uniques, unique, primary))
{
idx_table index = new idx_table(table, row["index_name"].ToString(), clustered.Value, unique, primary);
int i = 0;
index.fields.AddRange(dt_table("SELECT ind.column_name as colname, 0 as is_descending_key "
+ " FROM information_schema.statistics ind "
+ " WHERE ind.table_schema = '" + db_name() + "' and ind.table_name = '" + table + "' and ind.index_name = '" + index.name + "' "
+ " ORDER BY ind.seq_in_index").Rows.Cast <DataRow>()
.Select(dr => new idx_field(dr["colname"].ToString(), !(dr["is_descending_key"].ToString() == "1"), i++)));
indexes.Add(index);
}
}
return(indexes);
}
public override idx_table create_index(idx_table index)
{
exec(!index.primary ? "CREATE " + (index.unique ? "UNIQUE" : "") + " " + (!index.clustered ? "NONCLUSTERED" : "CLUSTERED")
+ " INDEX [" + index.name + "] ON " + index.table_name + " ("
+ string.Join(", ", index.fields.Select(x => "[" + x.name + "]" + (x.ascending ? " ASC" : " DESC"))) + ")"
: "ALTER TABLE " + index.table_name + " ADD " + (index.name.ToUpper() != "PRIMARY" ? " CONSTRAINT " + index.name : "") + " PRIMARY KEY "
+ (!index.clustered ? "NONCLUSTERED" : "CLUSTERED") + " ("
+ string.Join(",", index.fields.Select(x => "[" + x.name + "]")) + ")");
return(index);
}
public override List <idx_table> table_idxs(string table, bool?uniques = null, string index_name = "")
{
List <idx_table> indexes = new List <idx_table>();
if (table.IndexOf(".") < 0)
{
table = "dbo." + table;
}
string sql = "SELECT distinct ind.name, ind.type_desc, ind.is_unique, ind.is_primary_key "
+ " FROM sys.indexes ind "
+ " INNER JOIN sys.tables t ON ind.object_id = t.object_id "
+ (table.IndexOf('.') > 0 ? " WHERE SCHEMA_NAME(T.schema_id) + '.' + T.NAME = '" + table + "' AND IND.NAME IS NOT NULL"
: " WHERE T.NAME = '" + table + "' AND IND.NAME IS NOT NULL") + (index_name != "" ? " AND IND.NAME = '" + index_name + "'" : "");
DataTable rows = dt_table(sql);
foreach (DataRow row in rows.Rows)
{
// clustered
bool?clustered = row["type_desc"].ToString() == "CLUSTERED" ? true
: row["type_desc"].ToString() == "NONCLUSTERED" ? false : (bool?)null;
if (!clustered.HasValue)
{
log.log_warning("il tipo di indice '" + type + "' non viene gestito verrà adottato il tipo 'NONCLUSTERED'!");
clustered = false;
}
// filtro uniques
bool unique = bool.Parse(row["is_unique"].ToString()), primary = bool.Parse(row["is_primary_key"].ToString());
if (idx_table.filter_unique(uniques, unique, primary))
{
idx_table index = new idx_table(table, row["name"].ToString(), clustered.Value, unique, primary);
int i = 0;
index.fields.AddRange(dt_table("SELECT col.name as colname, ic.is_descending_key "
+ " FROM sys.indexes ind "
+ " INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id "
+ " INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id "
+ " INNER JOIN sys.tables t ON ind.object_id = t.object_id "
+ ((table.IndexOf('.') >= 0) ? " WHERE SCHEMA_NAME(T.schema_id) + '.' + t.name = '" + table + "' and ind.name = '" + index.name + "' "
: " WHERE t.name = '" + table + "' and ind.name = '" + index.name + "' ")
+ " ORDER BY t.name, ind.name, ind.index_id, ic.index_column_id ").Rows.Cast <DataRow>()
.Select(dr => new idx_field(dr["colname"].ToString(), !bool.Parse(dr["is_descending_key"].ToString()), i++)));
indexes.Add(index);
}
}
return(indexes);
}
static public bool same_index(idx_table idx, idx_table idx2)
{
if (idx.clustered != idx2.clustered || idx.unique != idx2.unique || idx.primary != idx2.primary)
{
return(false);
}
foreach (idx_field field in idx.fields)
{
if (idx2.exist_field(field.name) == null)
{
return(false);
}
}
return(true);
}
public override idx_table create_index(idx_table index)
{
log.log_info("creazione indice '" + index.name + "'");
//string clusteredSql = "CLUSTERED";
//if (!index.Clustered) clusteredSql = "NONCLUSTERED";
string sql = "";
foreach (idx_field iField in index.fields)
{
sql += (sql != "" ? ", " : "") + "[" + iField.name + "]" + (iField.ascending ? " ASC" : " DESC");
}
exec("CREATE " + (index.unique ? "UNIQUE" : "") + " INDEX [" + index.name + "] ON [" + index.table_name + "] ("
+ sql + ")" + (index.primary ? " WITH PRIMARY" : ""));
return(index);
}
static public XmlNode create_idx_node(XmlDocument doc, idx_table idx)
{
XmlNode idx_node = doc.CreateElement("index");
idx_node.Attributes.Append(doc.CreateAttribute("name")).Value = idx.name;
idx_node.Attributes.Append(doc.CreateAttribute("clustered")).Value = idx.clustered.ToString().ToLower();
idx_node.Attributes.Append(doc.CreateAttribute("unique")).Value = idx.unique.ToString().ToLower();
idx_node.Attributes.Append(doc.CreateAttribute("primary")).Value = idx.primary.ToString().ToLower();
XmlNode fieldsNode = idx_node.AppendChild(doc.CreateElement("fields"));
foreach (idx_field field in idx.fields)
{
XmlNode fieldNode = fieldsNode.AppendChild(doc.CreateElement("field"));
fieldNode.Attributes.Append(doc.CreateAttribute("name")).Value = field.name;
fieldNode.Attributes.Append(doc.CreateAttribute("name-upper")).Value = field.name.ToUpper();
fieldNode.Attributes.Append(doc.CreateAttribute("ascending")).Value = field.ascending.ToString().ToLower();
}
return(idx_node);
}
public virtual idx_table create_index(idx_table index)
{
throw new Exception("il provider " + _dbType.ToString() + " non supporta la funzionalità createIndex");
}
public XmlNode create_idx_node(idx_table idx)
{
return(create_idx_node(_doc.doc, idx));
}
public XmlNode add_idx(string table, idx_table idx)
{
return(xml_node.add_node(xml_node.add_node(table_node(table), "indexes"), create_idx_node(idx)));
}
static public idx_table find_index(List <idx_table> list, idx_table idx)
{
return(list.FirstOrDefault(x => same_index(x, idx)));
}