//----------------------------------------------------------------------
static XDataTable <DataRow> GetFieldsInSQLEx(String cmd, bool writestate)
{
StringBuilder tmp;
XStringSeg pWord;
XDataTable <DataRow> DataTable = new XDataTable <DataRow>();
//Datatable.fields.field = New( FIELD );
DataTable.dynamic = true;
//Datatable.keys.key = New( DB_KEY_DEF );
//tmp = SegCreateFromText( cmd );
// tmp will become parsed... the first segment is
// not released, it is merely truncated.
tmp = new StringBuilder(cmd);
{
// but first, go through, and remove carriage returns... which even
// if a delimieter need to be considered more like spaces...
int n, m;
//TEXTCHAR* str = GetText( tmp );
for (n = 0, m = tmp.Length; n < m; n++)
{
if (tmp[n] == '\n')
{
tmp[n] = ' ';
}
}
}
XString x = new XString(tmp.ToString(), "\'\"\\({[<>]}):@%/,;!?=*&$^~#`", " \t\n\r", true, true);
if (x.Count > 0)
{
pWord = x[0];
if (ValidateCreateTable(ref pWord))
{
String name;
bool quoted;
if (!GrabName(ref pWord, out name, out quoted))
{
DataTable = null;
}
else
{
DataTable.TableName = name;
if (!GetTableColumns(DataTable, ref pWord))
{
DataTable = null;
}
GetTableExtra(DataTable, ref pWord);
}
}
else
{
DataTable = null;
}
}
//if( writestate )
log(DataTable);
return(DataTable);
}
public MySQLNameTable(string prefix, string name, DsnConnection dsn)
: base(dsn, prefix, name)
{
DataColumn dc = this.Columns.Add(XDataTable.Name(this), typeof(string));
dc.Unique = true;
DsnSQLUtil.CreateDataTable(connection, this);
Fill();
}
public MySQLNameTable(string prefix, string name, bool trim_info)
: base(null, prefix, name, true, true)
{
DataColumn dc = this.Columns.Add(XDataTable.Name(this), typeof(string));
dc.Unique = true;
DsnSQLUtil.CreateDataTable(connection, this);
Fill();
}
public DataRow this[Guid name_id]
{
get
{
DataRow[] row = this.Select(XDataTable.ID(this) + "='" + name_id + "'");
if (row.Length > 0)
{
return(row[0]);
}
return(null);
}
}
//----------------------------------------------------------------------
static bool GetTableExtra(XDataTable <DataRow> table, ref Types.XStringSeg word)
{
// prior code ended us on the ')'
word = word.Next;
if (word != null)
{
table.extra = word.ToString();
return(true);
}
return(false);
}
/// <summary>
/// This is the thing that actually adds the default TableName_id and TableName_name
/// </summary>
/// <param name="trim_info">Option to trim _info and _description</param>
public void AddDefaultColumns(bool trim_info, bool add_auto_id, bool add_auto_name)
{
if (add_auto_id)
{
DataColumn dc = this.Columns.Add(XDataTable.ID(this), typeof(int));
dc.AutoIncrement = true;
(this as DataTable).PrimaryKey = new DataColumn[] { dc };
}
if (add_auto_name)
{
this.Columns.Add(NameColumn, typeof(string)).Unique = true;
}
}
public MySQLNameTable(DsnConnection dsn, string prefix, string name, bool trim_info, bool auto_fill, bool simple_name)
: base(dsn, prefix, name, true, trim_info, false)
{
// otherwise non-simple will be added by base initilalizer.
if (simple_name)
{
DataColumn dc = this.Columns.Add(simple_name ? "name" : XDataTable.Name(this), typeof(string));
dc.Unique = true;
}
if (auto_fill)
{
DsnSQLUtil.CreateDataTable(connection, this);
Fill();
}
}
public object this[string name]
{
get
{
DataRow[] row = this.Select(NameColumn + "='" + name + "'");
if (row.Length > 0)
{
return(row[0][XDataTable.ID(this)]);
}
DataRow dr;
dr = NewRow();
dr[NameColumn] = name;
Rows.Add(dr);
CommitChanges();
return(dr[XDataTable.ID(this)]);
}
}
//----------------------------------------------------------------------
static void AddIndexKey(XDataTable <DataRow> table, ref Types.XStringSeg word, bool has_name, bool primary, bool unique)
{
//table.Columns.
XDataTableKey key = new XDataTableKey();
//key.null = null;
key.primary = primary;
key.unique = unique;
bool quoted = false;
if (has_name)
{
GrabName(ref word, out key.name, out quoted);
}
//else
// table.keys.key[table.keys.count-1].name = null;
//table.keys.key[table.keys.count-1].colnames = New( CTEXTSTR );
//table.keys.key[table.keys.count-1].colnames[0] = null;
// 5.0 database this occurs before the columns
if (word == "USING")
{
word = word.Next;
if (word == "BTREE")
{
word = word.Next;
}
}
GrabKeyColumns(ref word, ref key.columns);
// 5.5 database this occurs after the columns of index
if (word == "USING")
{
word = word.Next;
if (word == "BTREE")
{
word = word.Next;
}
}
table.keys.Add(key);
}
static void table_TableNewRow(object sender, DataTableNewRowEventArgs e)
{
MySQLDataTable that = e.Row.Table as MySQLDataTable;
MySQLDataTable <T> that_t = e.Row.Table as MySQLDataTable <T>;
IXDataTable xthat = e.Row.Table as IXDataTable;
DsnConnection conn = null;
if (that != null)
{
conn = that.connection;
}
else if (that_t != null)
{
conn = that_t.connection;
}
if (xthat != null)
{
e.Row[xthat.PrimaryKeyName] = DsnConnection.GetGUID(conn);
}
else
{
e.Row[XDataTable.ID(e.Row.Table)] = DsnConnection.GetGUID(conn);
}
}
static public void AppendToDatabase(DsnConnection connection, DataTable table)
{
if (connection == null)
{
return;
}
String PrimaryKeyName = XDataTable.ID(table);
IXDataTable xdataTable = table as IXDataTable;
Type key_type = table.Columns[PrimaryKeyName].DataType;
if (xdataTable != null)
{
xdataTable.filling = true;
}
Log.log("AppendToDatabase should check primary key type - if it's Guid, then min/max are irrelavent");
object min_local_id = table.Compute("min(" + PrimaryKeyName + ")", null);
object max_local_id = table.Compute("max(" + PrimaryKeyName + ")", null);
// no rows to commit.
if (min_local_id.GetType() == typeof(DBNull))
{
return;
}
if (key_type == typeof(Int32))
{
object max_real_id = connection.ExecuteScalar("select max(" + PrimaryKeyName + ") from "
+ connection.sql_quote_open + table.Prefix + table.TableName + connection.sql_quote_close);
if ((max_real_id == null) || (max_real_id.GetType() == typeof(DBNull)))
{
//
if (Convert.ToInt32(min_local_id) != 1)
{
int n = 1;
foreach (DataRow row in table.Rows)
{
row[PrimaryKeyName] = n;
n++;
}
}
}
else
{
if (Convert.ToInt32(max_real_id) >= Convert.ToInt32(min_local_id) && Convert.ToInt32(max_real_id) <= Convert.ToInt32(max_local_id))
{
int final_id = Convert.ToInt32(max_real_id) + table.Rows.Count + 1;
if (final_id < Convert.ToInt32(max_local_id))
{
int n = Convert.ToInt32(max_real_id) + 1;
// while moving row ID's we may duplicate, so... look ahead...
foreach (DataRow row in table.Rows)
{
DataRow[] conflict = table.Select(PrimaryKeyName + "=" + n);
if (conflict.Length > 0)
{
int old_id = Convert.ToInt32(row[PrimaryKeyName]);
conflict[0][PrimaryKeyName] = 0;
row[PrimaryKeyName] = n;
conflict[0][PrimaryKeyName] = old_id;
}
else
{
row[PrimaryKeyName] = n;
}
n++;
}
//object o = null;
//o.GetType();
}
else
{
int r;
for (r = table.Rows.Count - 1; r >= 0; r--)
{
table.Rows[r][PrimaryKeyName] = final_id;
final_id--;
}
}
}
else
{
int n = Convert.ToInt32(max_real_id) + 1;
foreach (DataRow row in table.Rows)
{
row[PrimaryKeyName] = n;
n++;
}
}
}
}
else if (key_type == typeof(Guid))
{
foreach (DataRow row in table.Rows)
{
row[PrimaryKeyName] = connection.GetGUID();
}
}
DsnSQLUtil.InsertAllRows(connection, table);
if (xdataTable != null)
{
xdataTable.filling = false;
}
}
static bool GetTableColumns(XDataTable <DataRow> table, ref Types.XStringSeg word)
{
if (word == null)
{
return(false);
}
//DebugBreak();
if (word[0] != '(')
{
//PTEXT line;
Log.log("Failed to find columns... extra data between table name and columns....");
Log.log("Failed at " + word);
return(false);
}
while (word != null && word[0] != ')')
{
String name = null;
String type = null;
String extra = null;
bool bQuoted;
word = word.Next;
while (word.Text.Length == 0)
{
word = word.Next;
}
//if( word && GetText( *word )[0] == ',' )
// word = NEXTLINE( *word );
if (!GrabName(ref word, out name, out bQuoted))
{
Log.log("Failed column parsing...");
}
else
{
if (!bQuoted)
{
if (TextLike(name, ")"))
{
// close of the SQL columns.
break;
}
else if (TextLike(name, "CONSTRAINT"))
{
XDataTable <DataRow> .XDataTableForeignKey fk = new XDataTable <DataRow> .XDataTableForeignKey();
bool con_quote;
if (!TextLike(word, "FOREIGN"))
{
GrabName(ref word, out fk.keyname, out con_quote);
}
if (TextLike(word, "FOREIGN"))
{
word = word.Next;
if (TextLike(word, "KEY"))
{
word = word.Next;
if (word != "(")
{
bool key_quoted;
GrabName(ref word, out fk.child_indexname, out key_quoted);
// no name
}
if (word == "(")
{
GrabKeyColumns(ref word, ref fk.child_columns);
}
else
{
Log.log("parse failure.");
}
}
}
if (TextLike(word, "REFERENCES"))
{
word = word.Next;
{
bool key_quoted;
GrabName(ref word, out fk.parent_table, out key_quoted);
}
if (word == "(")
{
GrabKeyColumns(ref word, ref fk.parent_columns);
}
else
{
Log.log("parse failure.");
}
}
while (TextLike(word, "ON"))
{
word = word.Next;
if (TextLike(word, "DELETE"))
{
word = word.Next;
if (String.Compare(word, "CASCADE", true) == 0)
{
word = word.Next;
fk.fk_DeleteRule = Rule.Cascade;
}
else if (String.Compare(word, "SET", true) == 0)
{
word = word.Next;
if (String.Compare(word, "NULL", true) == 0)
{
word = word.Next;
fk.fk_DeleteRule = Rule.SetNull;
}
if (String.Compare(word, "DEFAULT", true) == 0)
{
word = word.Next;
fk.fk_DeleteRule = Rule.SetDefault;
}
}
if (String.Compare(word, "RESTRICT", true) == 0)
{
word = word.Next;
//fk.fk.DeleteRule = Rule.Restrict;
}
else if (String.Compare(word, "NO", true) == 0)
{
word = word.Next;
if (String.Compare(word, "ACTION", true) == 0)
{
word = word.Next;
fk.fk_DeleteRule = Rule.None;
}
}
}
if (TextLike(word, "UPDATE"))
{
word = word.Next;
if (String.Compare(word, "CASCADE", true) == 0)
{
word = word.Next;
fk.fk_UpdateRule = Rule.Cascade;
}
else if (String.Compare(word, "SET", true) == 0)
{
word = word.Next;
if (String.Compare(word, "NULL", true) == 0)
{
word = word.Next;
fk.fk_UpdateRule = Rule.SetNull;
}
if (String.Compare(word, "DEFAULT", true) == 0)
{
word = word.Next;
fk.fk_UpdateRule = Rule.SetDefault;
}
}
if (String.Compare(word, "RESTRICT", true) == 0)
{
word = word.Next;
//fk.fk.UpdateRule = Rule.Restrict;
}
else if (String.Compare(word, "NO", true) == 0)
{
word = word.Next;
if (String.Compare(word, "ACTION", true) == 0)
{
word = word.Next;
fk.fk_UpdateRule = Rule.None;
}
}
}
}
table.foreign.Add(fk);
}
else if (TextLike(name, "PRIMARY"))
{
if (TextLike((String)word, "KEY"))
{
word = word.Next;
if (TextLike((String)word, "USING"))
{
word = word.Next;
if (TextLike((String)word, "BTREE"))
{
word = word.Next;
}
}
AddIndexKey(table, ref word, false, true, false);
}
else
{
Log.log("PRIMARY keyword without KEY keyword is invalid.");
}
}
else if (TextLike(name, "UNIQUE"))
{
// these are optional words... so skip them...
if ((TextLike((String)word, "KEY")) ||
(TextLike((String)word, "INDEX")))
{
// skip this word.
word = word.Next;
}
AddIndexKey(table, ref word, true, false, true);
}
else if ((TextLike(name, "INDEX")) ||
TextLike(name, "BTREE") ||
(TextLike(name, "KEY")))
{
AddIndexKey(table, ref word, true, false, false);
}
else
{
GrabType(ref word, out type);
GrabExtra(ref word, out extra);
DataColumn dc = table.Columns.Add(name, GetType(type));
SetMaxLength(type, dc);
if (extra != null && extra.Contains("auto_increment"))
{
dc.AutoIncrement = true;
dc.AutoIncrementSeed = 1;
dc.AllowDBNull = false;
}
//table.fields.count++;
//table.fields.field = Renew( FIELD, table.fields.field, table.fields.count + 1 );
//table.fields.field[table.fields.count - 1].name = name;
//table.fields.field[table.fields.count - 1].type = type;
//table.fields.field[table.fields.count - 1].extra = extra;
//table.fields.field[table.fields.count - 1].previous_names[0] = null;
}
}
else
{
GrabType(ref word, out type);
GrabExtra(ref word, out extra);
DataColumn dc = table.Columns.Add(name, GetType(type));
SetMaxLength(name, dc);
if (extra != null && extra.Contains("auto_increment"))
{
dc.AutoIncrement = true;
dc.AutoIncrementSeed = 1;
dc.AllowDBNull = false;
}
//table.fields.count++;
//table.fields.field = Renew( FIELD, table.fields.field, table.fields.count + 1 );
//table.fields.field[table.fields.count - 1].name = name;
//table.fields.field[table.fields.count - 1].type = type;
//table.fields.field[table.fields.count - 1].extra = extra;
//table.fields.field[table.fields.count - 1].previous_names[0] = null;
}
}
}
return(true);
}