public void CreateMirrorTable()
{
Debug.Assert(Table != null);
StringBuilder sql = new StringBuilder();
sql.AppendFormat("create table {0} (", Table.DBTableName);
if (Table.DocIdReplaceField == null)
{
sql.Append("DocId Int NOT NULL Primary Key,");
}
int i = 0;
for (i = 0; i < Table.Fields.Count; i++)
{
Data.Field field = Table.Fields[i];
string fieldSql = GetFieldLine(field);
if (fieldSql != null)
{
sql.Append(fieldSql);
if (Table.DocIdReplaceField != null)
{
if (field.Name.Equals(Table.DocIdReplaceField, StringComparison.CurrentCultureIgnoreCase))
{
sql.Append(" Primary Key");
}
}
if (i < Table.Fields.Count - 1)
{
sql.Append(",");
}
}
}
sql.Append(")");
using (SQLiteDataProvider sqlData = new SQLiteDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExecuteNonQuery(sql.ToString());
if (!string.IsNullOrEmpty(Table.SQLForCreate))
{
sqlData.ExecuteNonQuery(Table.SQLForCreate);
}
}
}
public void Create()
{
Debug.Assert(Table != null);
StringBuilder sql = new StringBuilder();
sql.AppendFormat("create table {0} (", Table.DBTableName);
sql.Append("DocId Int NOT NULL Primary Key,");
int i = 0;
for (i = 0; i < Table.Fields.Count; i++)
{
Data.Field field = Table.Fields[i];
string fieldSql = GetFieldLine(field);
if (fieldSql != null)
{
sql.Append(fieldSql);
if (i < Table.Fields.Count - 1)
{
sql.Append(",");
}
}
}
sql.Append(")");
using (SQLiteDataProvider sqlData = new SQLiteDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExecuteNonQuery(sql.ToString());
if (!string.IsNullOrEmpty(Table.SQLForCreate))
{
sqlData.ExecuteNonQuery(Table.SQLForCreate);
}
}
}
public int Compare(Hubble.Core.Data.Field x, Hubble.Core.Data.Field y)
{
return(x.TabIndex.CompareTo(y.TabIndex));
}
public void CreateMirrorTable()
{
Debug.Assert(Table != null);
List <string> primaryKeys = new List <string>();
StringBuilder sql = new StringBuilder();
sql.AppendFormat("create table {0} (", Table.DBTableName);
if (Table.DocIdReplaceField != null)
{
primaryKeys.Add(Table.DocIdReplaceField);
}
else
{
primaryKeys.Add("DocId");
sql.Append("DocId Int NOT NULL,");
}
int i = 0;
for (i = 0; i < Table.Fields.Count; i++)
{
Data.Field field = Table.Fields[i];
string fieldSql = GetFieldLine(field);
if (!String.IsNullOrEmpty(fieldSql))
{
sql.Append(fieldSql);
if (i < Table.Fields.Count - 1)
{
sql.Append(",");
}
}
}
if (primaryKeys.Count > 0)
{
i = 0;
sql.Append(", primary key (");
foreach (string pkName in primaryKeys)
{
if (i == 0)
{
sql.AppendFormat("`{0}`", pkName);
}
else
{
sql.AppendFormat(",`{0}`", pkName);
}
i++;
}
sql.Append(")");
}
sql.Append(")");
using (MysqlDataProvider sqlData = new MysqlDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(sql.ToString());
if (!string.IsNullOrEmpty(Table.SQLForCreate))
{
sqlData.ExcuteSql(Table.SQLForCreate);
}
}
}
private string GetFieldLine(Data.Field field)
{
if (!field.Store)
{
return(null);
}
string sqlType = "";
string defaultValue = null;
if (field.DefaultValue != null)
{
switch (field.DataType)
{
case DataType.TinyInt:
case DataType.SmallInt:
case Hubble.Core.Data.DataType.Int:
case Hubble.Core.Data.DataType.BigInt:
case Hubble.Core.Data.DataType.Float:
defaultValue = field.DefaultValue;
break;
case Hubble.Core.Data.DataType.Date:
case Hubble.Core.Data.DataType.SmallDateTime:
case Hubble.Core.Data.DataType.DateTime:
case Hubble.Core.Data.DataType.Varchar:
case Hubble.Core.Data.DataType.NVarchar:
case Hubble.Core.Data.DataType.Char:
case Hubble.Core.Data.DataType.NChar:
defaultValue = string.Format("'{0}'", field.DefaultValue.Replace("'", "''"));
break;
default:
throw new ArgumentException(field.DataType.ToString());
}
}
switch (field.DataType)
{
case DataType.TinyInt:
sqlType = "TinyInt";
break;
case DataType.SmallInt:
sqlType = "SmallInt";
break;
case Hubble.Core.Data.DataType.Int:
sqlType = "Int";
break;
case Hubble.Core.Data.DataType.Date:
sqlType = "Date";
break;
case Hubble.Core.Data.DataType.SmallDateTime:
case Hubble.Core.Data.DataType.DateTime:
sqlType = "DateTime";
break;
case Hubble.Core.Data.DataType.Float:
sqlType = "Float";
break;
case Hubble.Core.Data.DataType.BigInt:
sqlType = "BigInt";
break;
case Hubble.Core.Data.DataType.Varchar:
if (field.DataLength <= 0)
{
sqlType = "text";
}
else
{
sqlType = "varchar ({1})";
}
break;
case Hubble.Core.Data.DataType.NVarchar:
if (field.DataLength <= 0)
{
sqlType = "text";
}
else
{
sqlType = "varchar ({1})";
}
break;
case Hubble.Core.Data.DataType.Char:
sqlType = "char ({1})";
break;
//case Hubble.Core.Data.DataType.NChar: mysql no N char/varchar
// sqlType = "nchar ({1})";
// break;
default:
throw new ArgumentException(field.DataType.ToString());
}
string sql = string.Format("`{0}`" + sqlType + " ", field.Name,
field.DataLength <= 0 ? "max" : field.DataLength.ToString());
if (!field.CanNull)
{
sql += "NOT NULL ";
}
if (defaultValue != null)
{
sql += "DEFAULT " + defaultValue + " ";
}
return(sql);
}
public void Create()
{
Debug.Assert(Table != null);
List <string> primaryKeys = new List <string>();
StringBuilder sql = new StringBuilder();
sql.AppendFormat("create table {0} (", Table.DBTableName);
sql.Append("DocId Int NOT NULL,");
int i = 0;
for (i = 0; i < Table.Fields.Count; i++)
{
Data.Field field = Table.Fields[i];
string fieldSql = GetFieldLine(field);
if (fieldSql != null)
{
sql.Append(fieldSql);
if (i < Table.Fields.Count - 1)
{
sql.Append(",");
}
}
if (field.PrimaryKey)
{
primaryKeys.Add(field.Name);
}
}
if (primaryKeys.Count > 0)
{
i = 0;
sql.Append(" primary key NONCLUSTERED(");
foreach (string pkName in primaryKeys)
{
if (i == 0)
{
sql.Append(pkName);
}
else
{
sql.Append("," + pkName);
}
i++;
}
sql.Append(")");
}
sql.Append(")");
using (MysqlDataProvider sqlData = new MysqlDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(sql.ToString());
//xx
sqlData.ExcuteSql(string.Format("create UNIQUE CLUSTERED Index I_{0}_DocId on {0}(DocId)",
Table.DBTableName));
if (!string.IsNullOrEmpty(Table.SQLForCreate))
{
sqlData.ExcuteSql(Table.SQLForCreate);
}
}
}
private string GetFieldLine(Data.Field field)
{
if (!field.Store)
{
return(null);
}
string sqlType = "";
string defaultValue = null;
if (field.DefaultValue != null)
{
switch (field.DataType)
{
case DataType.TinyInt:
case DataType.SmallInt:
case Hubble.Core.Data.DataType.Int:
case Hubble.Core.Data.DataType.BigInt:
case Hubble.Core.Data.DataType.Float:
defaultValue = field.DefaultValue;
break;
case Hubble.Core.Data.DataType.Date:
case Hubble.Core.Data.DataType.SmallDateTime:
case Hubble.Core.Data.DataType.DateTime:
DateTime dateTime;
if (!DateTime.TryParseExact(field.DefaultValue, "yyyy-MM-dd HH:mm:ss", null,
System.Globalization.DateTimeStyles.None, out dateTime))
{
dateTime = DateTime.Parse(field.DefaultValue);
}
defaultValue = string.Format("to_date('{0}','yyyy-mm-dd HH24:MI:SS')", dateTime.ToString("yyyy-MM-dd HH:mm:ss"));
break;
case Hubble.Core.Data.DataType.Varchar:
case Hubble.Core.Data.DataType.NVarchar:
case Hubble.Core.Data.DataType.Char:
case Hubble.Core.Data.DataType.NChar:
defaultValue = string.Format("'{0}'", field.DefaultValue.Replace("'", "''"));
break;
default:
throw new ArgumentException(field.DataType.ToString());
}
}
switch (field.DataType)
{
case DataType.TinyInt:
sqlType = "SmallInt";
break;
case DataType.SmallInt:
sqlType = "SmallInt";
break;
case Hubble.Core.Data.DataType.Int:
sqlType = "Int";
break;
case Hubble.Core.Data.DataType.Date:
case Hubble.Core.Data.DataType.SmallDateTime:
case Hubble.Core.Data.DataType.DateTime:
sqlType = "Date";
break;
case Hubble.Core.Data.DataType.Float:
sqlType = "Double";
break;
case Hubble.Core.Data.DataType.BigInt:
sqlType = "Double";
break;
case Hubble.Core.Data.DataType.Varchar:
if (field.DataLength <= 0)
{
sqlType = "clob";
}
else
{
if (field.DataLength > 4000)
{
throw new ArgumentException(string.Format("Invalid data length is set in field:{0}",
field.Name));
}
sqlType = "varchar2 ({1})";
}
break;
case Hubble.Core.Data.DataType.NVarchar:
if (field.DataLength <= 0)
{
sqlType = "nclob";
}
else
{
if (field.DataLength > 2000)
{
throw new ArgumentException(string.Format("Invalid data length is set in field:{0}",
field.Name));
}
sqlType = "nvarchar2 ({1})";
}
break;
case Hubble.Core.Data.DataType.Char:
if (field.DataLength <= 0)
{
sqlType = "clob";
}
else
{
if (field.DataLength > 2000)
{
throw new ArgumentException(string.Format("Invalid data length is set in field:{0}",
field.Name));
}
sqlType = "char ({1})";
}
break;
case Hubble.Core.Data.DataType.NChar:
if (field.DataLength <= 0)
{
sqlType = "nclob";
}
else
{
if (field.DataLength > 1000)
{
throw new ArgumentException(string.Format("Invalid data length is set in field:{0}",
field.Name));
}
sqlType = "nchar ({1})";
}
break;
default:
throw new ArgumentException(field.DataType.ToString());
}
string sql = string.Format("{0} " + sqlType + " ", GetFieldName(field.Name), field.DataLength.ToString());
if (defaultValue != null)
{
sql += "DEFAULT " + defaultValue + " ";
}
if (!field.CanNull)
{
sql += "NOT NULL ";
}
return(sql);
}
