here's a very raw and but generally functional class to generate classes for ormlite.mysql from your existing mysql tables in your mysql databases...
set the output directory, connect string, and what db... in the code - no gui
it handles foreign keys too...
handles nullable int
sets string max
and a bunch more
try it...I'll bet it handles most every table you have... point it at the database and see the ORMLITE.mysql classes that just work...
there are exceptions which will pop up in notepad to tell you and you can usually fix it easier than fixing the code
or fix the code for us all and post it to GIT where they won't object to this
generates the multi field indexes but you only get one by ORMLITE
(you have to pick one and comment the ones you don't really have to have)
it generates them all and decorates which you will get errors for,
pick one, didn't look to see why only one is allowed - ask them
handling multi field primary key it does for read only!
GetOrmLiteMySqlClassdef is where most happens
if multi field primary, then no, it redoes it in another call
FREE OPEN SOURCE, I WROTE IT... FREEWARE...YEAH - ugly but works
hack on the dates, I expect you to pass in as a proper string...in the right format prescribed by mysql
vb6 style Format$(Now(), "YYYYMMDDHhNnSs") feed it that way from wherever and be well
folded byte integer into int
retained bigint, if I recall correctly
text ok
byte arrary ok
so here I paste for you all to complain, fix, or use as you see fit...
beats nothing, eh? show me another... whatever...
works for my purposes enough to move on for now...
vs2010
packages.config
<?xml version="1.0" encoding="utf-8"?>
<packages>
<package id="MySql.Data" version="6.6.5" targetFramework="net40" />
<package id="ServiceStack.Common" version="3.9.49" targetFramework="net40" />
<package id="ServiceStack.OrmLite.MySql" version="3.9.49" targetFramework="net40" />
<package id="ServiceStack.Text" version="3.9.49" targetFramework="net40" />
</packages>
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using System.IO;
using System.Text.RegularExpressions;
using Microsoft.VisualBasic;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using NUnit.Framework;
using ServiceStack.DataAnnotations;
using ServiceStack.Logging;
using ServiceStack.Logging.Support.Logging;
using ServiceStack.Text;
using ServiceStack.OrmLite.MySql;
using System.Text;
using MysqlOrmLite1;
namespace TestOrmLiteMysqlGen
{
public class GenMyOrmLiteForMySQL
{
static void Main(string[] args)
{
CustomerOrdersUseCase t = new CustomerOrdersUseCase();
// t.Run();
t.Generate();
}
}
public enum PhoneType
{
Home,
Work,
Mobile,
}
public enum AddressType
{
Home,
Work,
Other,
}
public class Address
{
public string Line1 { get; set; }
public string Line2 { get; set; }
public string ZipCode { get; set; }
public string State { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
public class Customer
{
public Customer()
{
this.PhoneNumbers = new Dictionary<PhoneType, string>();
this.Addresses = new Dictionary<AddressType, Address>();
}
[AutoIncrement] // Creates Auto primary key
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
[Index(Unique = true)] // Creates Unique Index
public string Email { get; set; }
public Dictionary<PhoneType, string> PhoneNumbers { get; set; }
public Dictionary<AddressType, Address> Addresses { get; set; }
public DateTime CreatedAt { get; set; }
}
public class Order
{
[AutoIncrement]
public int Id { get; set; }
[References(typeof(Customer))] //Creates Foreign Key
public int CustomerId { get; set; }
[References(typeof(Employee))] //Creates Foreign Key
public int EmployeeId { get; set; }
public Address ShippingAddress { get; set; } //Blobbed (no Address table)
public DateTime? OrderDate { get; set; }
public DateTime? RequiredDate { get; set; }
public DateTime? ShippedDate { get; set; }
public int? ShipVia { get; set; }
public decimal Freight { get; set; }
public decimal Total { get; set; }
}
public class OrderDetail
{
[AutoIncrement]
public int Id { get; set; }
[References(typeof(Order))] //Creates Foreign Key
public int OrderId { get; set; }
public int ProductId { get; set; }
public decimal UnitPrice { get; set; }
public short Quantity { get; set; }
public decimal Discount { get; set; }
}
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
}
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal UnitPrice { get; set; }
}
public class City : IHasIntId
{
[AutoIncrement]
public int Id { get; set; }
public byte datastate { get; set; }
public int countryid { get; set; }
public int stateid { get; set; }
public int countyid { get; set; }
public decimal timezoneoffset { get; set; }
public decimal taxrate { get; set; }
public int sequence { get; set; }
public int? imageid { get; set; }
public int? thumbimageid { get; set; }
public string editby { get; set; }
public string edited { get; set; }
public string created { get; set; }
public decimal? latitude { get; set; }
public decimal? longitude { get; set; }
public string areacode { get; set; }
public string daylightsavings { get; set; }
public int citynameid { get; set; }
public string city { get; set; }
public int storecount { get; set; }
}
[TestFixture]
public class CustomerOrdersUseCase
{
//Stand-alone class, No other configs, nothing but POCOs.
[Test]
public void Run()
{
LogManager.LogFactory = new ConsoleLogFactory();
//Set once before use (i.e. in a static constructor).
OrmLiteConfig.DialectProvider = MySqlDialect.Provider;
var dbFactory = new OrmLiteConnectionFactory("Server=localhost;Database=test;Uid=tester1;Pwd=tester1;", MySqlDialect.Provider);
//Non-intrusive: All extension methods hang off System.Data.* interfaces
using (IDbConnection db = dbFactory.OpenDbConnection())
{
//Re-Create all table schemas:
db.DropTable<OrderDetail>();
db.DropTable<Order>();
db.DropTable<Customer>();
db.DropTable<Product>();
db.DropTable<Employee>();
db.DropTable<City>();
db.CreateTable<Employee>();
db.CreateTable<Product>();
db.CreateTable<Customer>();
db.CreateTable<Order>();
db.CreateTable<OrderDetail>();
db.CreateTable<City>();
db.Insert(new City { Id = 1, countryid = 1, city = "Test City", stateid = 1, taxrate = 0, countyid = 1, timezoneoffset = -5, areacode = "860" });
db.Insert(new Employee { Id = 1, Name = "Employee 1" });
db.Insert(new Employee { Id = 2, Name = "Employee 2" });
var product1 = new Product { Id = 1, Name = "Product 1", UnitPrice = 10 };
var product2 = new Product { Id = 2, Name = "Product 2", UnitPrice = 20 };
db.Save(product1, product2);
var customer = new Customer
{
FirstName = "Orm",
LastName = "Lite",
Email = "ormlite@servicestack.net",
PhoneNumbers =
{
{ PhoneType.Home, "555-1234" },
{ PhoneType.Work, "1-800-1234" },
{ PhoneType.Mobile, "818-123-4567" },
},
Addresses =
{
{ AddressType.Work, new Address { Line1 = "1 Street", Country = "US", State = "NY", City = "New York", ZipCode = "10101" } },
},
CreatedAt = DateTime.UtcNow,
};
db.Insert(customer);
var customerId = db.GetLastInsertId(); //Get Auto Inserted Id
customer = db.QuerySingle<Customer>(new { customer.Email }); //Query
Assert.That(customer.Id, Is.EqualTo(customerId));
//Direct access to System.Data.Transactions:
using (IDbTransaction trans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
var order = new Order
{
CustomerId = customer.Id,
EmployeeId = 1,
OrderDate = DateTime.UtcNow,
Freight = 10.50m,
ShippingAddress = new Address { Line1 = "3 Street", Country = "US", State = "NY", City = "New York", ZipCode = "12121" },
};
db.Save(order); //Inserts 1st time
order.Id = (int)db.GetLastInsertId(); //Get Auto Inserted Id
var orderDetails = new[] {
new OrderDetail {
OrderId = order.Id,
ProductId = product1.Id,
Quantity = 2,
UnitPrice = product1.UnitPrice,
},
new OrderDetail {
OrderId = order.Id,
ProductId = product2.Id,
Quantity = 2,
UnitPrice = product2.UnitPrice,
Discount = .15m,
}
};
db.Insert(orderDetails);
order.Total = orderDetails.Sum(x => x.UnitPrice * x.Quantity * x.Discount) + order.Freight;
db.Save(order); //Updates 2nd Time
trans.Commit();
}
}
}
[Test]
public void Generate()
{
// what if I have mysql tables already?
//pick the tables with an Id field and generate the OrmLite.Mysql classes
LogManager.LogFactory = new ConsoleLogFactory();
//Set once before use (i.e. in a static constructor).
OrmLiteConfig.DialectProvider = MySqlDialect.Provider;
String connectString = "Server=localhost;Database=test;Uid=YourUserPwd=YourPass!;";
var dbFactory = new OrmLiteConnectionFactory(connectString, MySqlDialect.Provider);
ClassGenUtil gen = new ClassGenUtil();
//gen.GenerateDBClasses(connectString, "YourDB", @"c:\yourOutputDBDir");
}
[Test]
void TestMyOrms()
{
var dbFactory = new OrmLiteConnectionFactory("Server=localhost;Database=test;Uid=tester1;Pwd=tester1;", MySqlDialect.Provider);
//Non-intrusive: All extension methods hang off System.Data.* interfaces
using (IDbConnection db = dbFactory.OpenDbConnection())
{
}
}
}
//for reference on requirements of OrmLite.MySQL
public interface IHasId<T>
{
T Id { get; }
}
public interface IHasStringId : IHasId<string>
{
}
//public interface IHasGuidId : IHasId<Guid>
//{
//}
public interface IHasLongId : IHasId<long>
{
}
public interface IHasIntId : IHasId<int>
{
}
public interface IHasInt64Id : IHasId<Int64>
{
}
public interface IHasByteId : IHasId<byte>
{
}
public class ClassGenUtil
{
private const String TemplateOneFileClass = @"
using MysqlOrmLite1;
using System;
using System.Collections.Generic;
using System.Data;
//using System.Text;
//using System.Linq;
//using NUnit.Framework;
using System.Globalization;
using System.ComponentModel.DataAnnotations;
using ServiceStack.Logging;
using ServiceStack.Logging.Support.Logging;
using ServiceStack.Text;
using ServiceStack.OrmLite.MySql;
using ServiceStack.DataAnnotations;
//using ServiceStack.DesignPatterns.Model;
namespace MysqlOrmLite1
{
";
private const String TemplateOrmLiteMySQL = @"
~tableAlias~
public class ~classname~
{ ~theprops~
}
";
private Dictionary<string, string> dSwapRefs = null;
public void GenerateDBClasses(String dbConnectString, String dbname, String tablename, String outputdir)
{
dSwapRefs = new Dictionary<string, string>();
StringBuilder dbTableClassesText = new StringBuilder();
if (Utils.VerifyDirectory(outputdir))
{
try
{
MySqlConnection conn = new MySqlConnection(dbConnectString);
conn.Open();
try
{
System.Collections.ArrayList alTables = GetTables(conn, dbname);
//for the one table, generate the class from the template (still need all table names for reference - foreign key in show create table does not keep case on windows, not sure about linux
try
{
String sTableORMClass = GetOrmLiteMySqlClassdef(conn, dbname, tablename, ref alTables);
if (String.IsNullOrEmpty(sTableORMClass) == false)
{
dbTableClassesText.Append(sTableORMClass);
SimpleLog.LogSuccess("Created OK an OrmLite MySQL class for " + dbname + "." + tablename);
}
else
{
SimpleLog.LogFailure("Create OrmLite MySQL class for " + dbname + "." + tablename + " failed.");
}
}
catch (Exception ex1) { SimpleLog.RecordException(ex1, "GenerateDBClasses " + tablename); }
//write output file, one per database...who needs all the clutter?
String outFilename = outputdir + @"\" + dbname + "DL.cs";
String sAllProps = dbTableClassesText.ToString();
String sFixAllProps = SwapFixedClassNames(ref sAllProps, ref dSwapRefs);
bool result2 = Utils.WriteToFile(outFilename, TemplateOneFileClass + sFixAllProps + "\r\n}");
if (result2 == true)
System.Diagnostics.Process.Start("notepad.exe", outFilename);
}
catch (Exception ex2) { SimpleLog.RecordException(ex2, "GenerateDBClasses " + tablename); }
finally
{
conn.Close();
}
}
catch (Exception ex3) { SimpleLog.RecordException(ex3, "GenerateDBClasses " + tablename); }
}
try
{
if (File.Exists(SimpleLog.ErrorFile))
System.Diagnostics.Process.Start("notepad.exe", SimpleLog.ErrorFile);
}
catch { }
try
{
if (File.Exists(SimpleLog.SuccessLog))
System.Diagnostics.Process.Start("notepad.exe", SimpleLog.SuccessLog);
}
catch { }
try
{
if (File.Exists(SimpleLog.FailureLog))
System.Diagnostics.Process.Start("notepad.exe", SimpleLog.FailureLog);
}
catch { }
}
public void GenerateDBClasses(String dbConnectString, String dbname, String outputdir)
{
String tablename = String.Empty;
dSwapRefs = new Dictionary<string, string>();
StringBuilder dbTableClassesText = new StringBuilder();
if (Utils.VerifyDirectory(outputdir))
{
try
{
MySqlConnection conn = new MySqlConnection(dbConnectString);
conn.Open();
try
{ //get table list
System.Collections.ArrayList alTables = GetTables(conn, dbname);
for (int t = 0; t < alTables.Count; t++)
{
tablename = alTables[t].ToString();
//for each table, generate the class from the template
//, ref System.Collections.ArrayList theTableNamesCasePreserved
try
{
String sTableORMClass = GetOrmLiteMySqlClassdef(conn, dbname, tablename, ref alTables);
if (String.IsNullOrEmpty(sTableORMClass) == false)
{
dbTableClassesText.Append(sTableORMClass);
SimpleLog.LogSuccess("Created OK an OrmLite MySQL class for " + dbname + "." + tablename);
}
else
{
SimpleLog.LogFailure("Create OrmLite MySQL class for " + dbname + "." + tablename + " failed.");
}
}
catch (Exception ex1) { SimpleLog.RecordException(ex1, "GenerateDBClasses"); }
}
//write output file, one per database...who needs all the clutter?
String outFilename = outputdir + @"\" + dbname + "DL.cs";
String sAllProps = dbTableClassesText.ToString();
String sFixAllProps = SwapFixedClassNames(ref sAllProps, ref dSwapRefs);
bool result2 = Utils.WriteToFile(outFilename, TemplateOneFileClass + sFixAllProps + "\r\n}");
if (result2 == true)
System.Diagnostics.Process.Start("notepad.exe", outFilename);
}
catch (Exception ex2) { SimpleLog.RecordException(ex2, "GenerateDBClasses"); }
finally
{
conn.Close();
}
}
catch (Exception ex3) { SimpleLog.RecordException(ex3, "GenerateDBClasses"); }
}
try
{
if (File.Exists(SimpleLog.ErrorFile))
System.Diagnostics.Process.Start("notepad.exe", SimpleLog.ErrorFile);
}
catch { }
try
{
if (File.Exists(SimpleLog.SuccessLog))
System.Diagnostics.Process.Start("notepad.exe", SimpleLog.SuccessLog);
}
catch { }
try
{
if (File.Exists(SimpleLog.FailureLog))
System.Diagnostics.Process.Start("notepad.exe", SimpleLog.FailureLog);
}
catch { }
}
private String GetClassNameFromTableName(String tablename)
{ //no spaces in table name, too much hassle, can't use for classname
return tablename.Replace(" ", "_");
}
private String GetKeyFieldName(ref ArrayList alFields, out String sIHasType)
{
//public interface IHasId<T>
//{
// T Id { get; }
//}
//public interface IHasStringId : IHasId<string>
//{
//}
//public interface IHasGuidId : IHasId<Guid>
//{
//}
//public interface IHasLongId : IHasId<long>
//{
//}
//public interface IHasIntId : IHasId<int>
//{
//}
sIHasType = "IHasIntId"; //default is int
String sType = String.Empty;
String sKeyFieldName = String.Empty;
MySQLFieldDef fdef;
for (int t = 0; t < alFields.Count; t++)
{
fdef = (MySQLFieldDef)alFields[t];
if (fdef.Key == "PRI")
{
sKeyFieldName = fdef.FieldName;
sType = fdef.FieldType;
//`Id` tinyint(4) NOT NULL,
//IHasInt64Id
if (sType.Substring(0, 6) == "bigint") //(4)")
{
sIHasType = "IHasInt64Id"; //default is int
}
else if (sType.Substring(0, 7) == "tinyint") //(4)")
{
sIHasType = "IHasByteId"; //default is int
}
else if (sType.Substring(0, 3) == "int")
{
sIHasType = "IHasIntId"; //default is int
}
else if (sType == "guid")
{
//sIHasType = "IHasGuidId"; //default is int forget IhasGUID, string is less hassle
sIHasType = "IHasStringId"; //default is int
}
else if (sType.ToLower() == "varchar")
{
sIHasType = "IHasStringId"; //default is int
}
else
{
if (sType == "char(32)")
{
//sIHasType = "IHasGuidId"; //default is int forget IhasGUID, string is less hassle
sIHasType = "IHasStringId"; //default is int
}
else
{
if (sType.ToLower().IndexOf("char") != -1)
{
sIHasType = "IHasStringId"; //default is int
}
else
{
sIHasType = "IHasStringId"; //default is int
}
}
}
break;
}
}
return sKeyFieldName;
}
private String GetOrmLiteMySqlClassdef(MySqlConnection conn, String dbname, String tablename, ref System.Collections.ArrayList theTableNamesCasePreserved)
{
String newclassname = GetClassNameFromTableName(tablename);
String oldClassName = newclassname; //save the tablename we may have to swap if it has a field of same name, so we can swap out the Reference(typeof(newclassname
String sCompositeFields = String.Empty;
Dictionary<string, string> dForeignKey = new Dictionary<string, string>();
Dictionary<string, string> dOneFieldIndex = new Dictionary<string, string>();
ArrayList alFields = new System.Collections.ArrayList();
ArrayList alPRIFields = new System.Collections.ArrayList();
ArrayList alCompositeIndexes = new System.Collections.ArrayList();
System.Collections.ArrayList alDbFieldTypes = new System.Collections.ArrayList();
Hashtable htTypes = new Hashtable();
String fieldname, fieldtype, isnullable, keytypename, defval, extra;
String sOrmLiteClassDef = String.Empty;
StringBuilder sbProps = new StringBuilder();
//get the table fields
//convert fields into OrmLite.MySQL style
//swap strings into template
sbProps.Append("\r\n");
MySqlDataReader reader = null;
MySqlCommand cmd;
String sCreateTable = String.Empty;
try
{
try
{
try
{
//mysql foreign keys not identified with show index, so get it from create table
System.Collections.ArrayList alCreateTable = new System.Collections.ArrayList();
string sqlShow = "SHOW create table " + dbname + "." + tablename + ";";
using (cmd = new MySqlCommand(sqlShow, conn))
{
reader = cmd.ExecuteReader();
reader.Read();
sCreateTable = reader.GetString("Create Table");
reader.Close();
reader = null;
}
}
catch (Exception ex6) { SimpleLog.RecordException(ex6, "GetOrmLiteMySqlClassdef"); }
if (sCreateTable.Length == 0) { return String.Empty; }
else
{
String[] arKeyLine = sCreateTable.Split(new[] { "\n" }, StringSplitOptions.None);
int iMax = arKeyLine.GetUpperBound(0);
for (int i = 0; i <= iMax; i++)
{
String aKeyLine = arKeyLine[i];
if (aKeyLine.IndexOf("KEY") != -1)
{
//alCreateTable.Add(aKeyLine);
//KEY `byEditedEditBy` (`Edited`,`EditBy`),
//KEY `anumber` (`anumber`),
//CONSTRAINT `tester2_ibfk_1` FOREIGN KEY (`anumber`) REFERENCES `tester` (`anumber`)
if (aKeyLine.IndexOf("FOREIGN KEY") != -1)
{
int iFirstPos = aKeyLine.IndexOf("KEY (`");
string sChop = aKeyLine.Substring(iFirstPos + 6);
int iSecondPos = sChop.IndexOf("`) REFERENCES");
if (iSecondPos != -1)
{
string sFKeyField = sChop.Substring(0, iSecondPos);
string sRefClassType = sChop.Substring(iSecondPos + 15);
//[References(typeof(ShipperType))]
int iLastTick = sRefClassType.IndexOf("`");
sRefClassType = sRefClassType.Substring(0, iLastTick);
//mysql windows show create table does not preserve case sensitive on foreign key xxx references tblname(xxx) so we pass in the table list to swap it
try
{
String sRefClassTypeCaseSensitiveTable = String.Empty;
int iCmax = theTableNamesCasePreserved.Count - 1;
for (int c = 0; c < iCmax; c++)
{
if (theTableNamesCasePreserved[c].ToString().ToLower() == sRefClassType)
{
sRefClassTypeCaseSensitiveTable = theTableNamesCasePreserved[c].ToString();
break;
}
}
if (String.IsNullOrEmpty(sRefClassTypeCaseSensitiveTable) == false)
if (dForeignKey.ContainsKey(sFKeyField) == false)
dForeignKey.Add(sFKeyField, "[References(typeof(" + sRefClassTypeCaseSensitiveTable + "))]");
else
{ //you probably erred in your tabledef sql
SimpleLog.RecordException("Duplicate Foreign Key defined!!! FIX your table definition!!!" + sCreateTable);
}
}
catch (Exception ex2) { SimpleLog.RecordException(ex2, "GetOrmLiteMySqlClassdef get foreign keys " + tablename); }
//TO DO [ForeignKey(typeof(ForeignKeyTable2), OnDelete = "CASCADE", OnUpdate = "CASCADE")]
}
}
else
{
try
{
if (aKeyLine.IndexOf("PRIMARY KEY") == -1) //not primary key, lets build strings for composite or just plain index fields
{ //is it composite or singular field?
sCompositeFields = String.Empty;
// alCompositeIndexes.Add(aKeyLine.Trim());
int iPosFieldStart = aKeyLine.IndexOf("(`");
string sIndexFields = aKeyLine.Substring(iPosFieldStart + 1);
int iPosFieldsEnd = sIndexFields.IndexOf("`)");
if (iPosFieldsEnd != -1)
{
sIndexFields = sIndexFields.Substring(1, iPosFieldsEnd);
sIndexFields = sIndexFields.Replace("`", " ").Trim();
String[] arKeyFields = sIndexFields.Split(new[] { " , " }, StringSplitOptions.None);
int iKeyMax = arKeyFields.GetUpperBound(0);
if (iKeyMax > 0)
{
//String sCompIndexName = ""; //what shall we use? oh, not required, how does it generate the index name?
for (int j = 0; j <= iKeyMax; j++)
{
string sOneField = arKeyFields[j].Substring(0, arKeyFields[j].Length);
//arKeyFields[i] = sOneField;
if (sCompositeFields == String.Empty)
{
sCompositeFields = "\"" + sOneField + "\"";
}
else
{
sCompositeFields = sCompositeFields + "," + "\"" + sOneField + "\"";
}
}
String sCompositeIndexPrefix = String.Empty;
if (aKeyLine.IndexOf("UNIQUE") != -1)
{
//sCompositeIndexPrefix = "[CompositeIndex(" + sCompIndexName + "," + sCompositeFields + ", Unique = true)]";
sCompositeIndexPrefix = "[CompositeIndex(" + sCompositeFields + ", Unique = true)]";
}
else
{
//sCompositeIndexPrefix = "[CompositeIndex(" + sCompIndexName + "," + sCompositeFields + ", Unique = false)]";
sCompositeIndexPrefix = "[CompositeIndex(" + sCompositeFields + ", Unique = false)]";
}
alCompositeIndexes.Add(sCompositeIndexPrefix);
}
else
{ // index on just one field
String sKey = arKeyFields[0].ToString().Trim();
if (dOneFieldIndex.ContainsKey(sKey) == false)
{
if (aKeyLine.IndexOf("UNIQUE") == -1)
{
dOneFieldIndex.Add(sKey, "[Index(Unique = false)]");
}
else
{
dOneFieldIndex.Add(sKey, "[Index(Unique = true)]");
}
}
}
}
}
}
catch (Exception ex3) { SimpleLog.RecordException(ex3, "GetOrmLiteMySqlClassdef get indexes and keys " + tablename); }
}
}
}
}
}
catch (Exception ex0) { SimpleLog.RecordException(ex0, "GetOrmLiteMySqlClassdef " + tablename); }
bool bHasMultiFieldPrimary = false;
bool bFoundPRIfield = false;
using (cmd = new MySqlCommand("SHOW fields from " + dbname + "." + tablename, conn))
{
MySQLFieldDef tblfield;
if (reader != null) reader.Close();
reader = null;
reader = cmd.ExecuteReader();
while (reader.Read())
{
fieldname = "";
fieldtype = "";
isnullable = "";
keytypename = "";
defval = "";
extra = "";
fieldname = reader.GetString(0);
fieldtype = reader.GetString(1);// type int(11) varchar(x) timestamp
if (htTypes.ContainsValue(fieldtype) != true)
{
alDbFieldTypes.Add(fieldtype); //record all field types in the database
htTypes.Add(fieldtype, fieldtype); //for when writing the select case code use to generate the sql and class source code
}
isnullable = reader.GetString(2); //isnullable YES or NO
try { keytypename = reader.GetString(3); } // key typename UNI or PRI - blank for none
catch { }//don't throw(ex0);// can only read show fields once thru, can't check null or it goes away?
try
{
defval = reader.GetString(4); //default val not always there... try and fail and ignore it
}
catch
{
defval = String.Empty;
}
try
{
extra = reader.GetString(5); //extra - auto_increment or null should be blank
}
catch { extra = String.Empty; }
tblfield = new MySQLFieldDef();
tblfield.DataBaseName = dbname;
tblfield.TableName = tablename;
tblfield.FieldName = fieldname;
tblfield.FieldType = fieldtype;
tblfield.Nullable = isnullable;
tblfield.Key = keytypename;
tblfield.Default = defval;
tblfield.Extra = extra;
if (SkipField(fieldtype, false) != true)
{
alFields.Add(tblfield); // fields
//Int64 iSize = ParseFieldSize(tblfield.FieldType);
try
{
String sDecorate = String.Empty; // [AutoIncrement] // Creates Auto primary key
if (extra == "auto_increment")
{
sDecorate = "\r\n\t[AutoIncrement]";
if (fieldname != "Id")
{
sDecorate = sDecorate + "\r\n\t[Alias(\"" + fieldname + "\")]";
fieldname = "Id"; //change the class field to use to Id
}
}
else
{
if (keytypename == "PRI")
{
alPRIFields.Add(tblfield.FieldName);
if (bFoundPRIfield == false)
{
bFoundPRIfield = true;
}
else
{
bHasMultiFieldPrimary = true;
}
if (tblfield.FieldName != "Id")
{
sDecorate = sDecorate + "\r\n\t[Alias(\"" + tblfield.FieldName + "\")]";
fieldname = "Id"; //change the class field to use to Id
}
sDecorate = sDecorate + "\r\n\t[PrimaryKey]";
}
}
String dotNetType = GetDotNetType(fieldtype);
if (isnullable != "NO")
{
if (dotNetType.ToLower() != "string")
dotNetType = dotNetType + "?"; // adding the ? to end of type signifies nullable
}
else { sDecorate = sDecorate + "\r\n\t[Required]"; }
//if (keytypename == "UNI")
// sDecorate = sDecorate + "\r\n\t[Index(Unique = true)]";
if (dotNetType.ToLower() == "string")
{
if (fieldtype.ToLower().IndexOf("char") != -1)
{
Int64 iCharSize = ParseFieldSize(fieldtype);
sDecorate = sDecorate + "\r\n\t[StringLength(" + iCharSize.ToString() + ")]";
}
else
{
if (fieldtype.ToLower().IndexOf("datetime") != -1)
{ //Int64 iCharSize = // 2005-12-23 00:12:34.001 ParseFieldSize(fieldtype);
//one example showed "2011-01-01 12:34:59.123456" why 6 digit millisecond? makes no sense
// well it could support this "2011-01-01 12:34:59.123 AM"
//so 26
sDecorate = sDecorate + "\r\n\t[StringLength(26)]";
}
else
{
if (fieldtype.ToLower().IndexOf("timestamp") != -1)
{
sDecorate = sDecorate + "\r\n\t[StringLength(26)]";
}
}
}
}
if (dForeignKey.ContainsKey(fieldname) == true)
sDecorate = sDecorate + "\r\n\t" + dForeignKey[fieldname];
if (dOneFieldIndex.ContainsKey(fieldname) == true)
sDecorate = sDecorate + "\r\n\t" + dOneFieldIndex[fieldname];
String sProp = GetProp(fieldname, dotNetType);
if (String.IsNullOrEmpty(defval) == false)
{
if (defval != "CURRENT_TIMESTAMP") //skip the timestamps
{
String sDefType = dotNetType.Replace("?", " ").Trim();// chop off the ? for those int that can be null
sDecorate = sDecorate + "\r\n\t[Default(typeof(" + sDefType + "), \"" + defval + "\")]";
}
}
String sFieldClassDef = sDecorate + "\r\n" + sProp;
sbProps.Append("\r\n" + sFieldClassDef);
}
catch (Exception ex5) { SimpleLog.RecordException(ex5, "GetOrmLiteMySqlClassdef get field defs"); }
}
}
reader.Close();
reader = null;
if (bHasMultiFieldPrimary == true)
{ //forget what we did, lets redo and just leave it for now? what a mess
String sMultiPriClass = GenMultiPRIclassGuts(ref alFields, ref alPRIFields, ref dForeignKey, ref dOneFieldIndex);
sbProps = new StringBuilder();
sbProps.Append(sMultiPriClass);
}
}
}
catch (Exception ex3) { SimpleLog.RecordException(ex3, "GetOrmLiteMySqlClassdef"); }
finally
{
if (reader != null)
reader.Close();
}
sbProps.Append("\t\t \r\n");
string sIHasTypeInterface = String.Empty;
String sKeyFieldName = GetKeyFieldName(ref alFields, out sIHasTypeInterface); //just to get the type interface, using existing function
try
{
String tableAliasName = "\r\n";
String sFixedClassTemplate = String.Empty;
if (IsClassNameAFieldName(ref alFields, newclassname) == true) //field matching classname(from tablename) not allowed
{
//one of the table fields is the same as the table name, not allowed in OrmLite class!
String sRightChar = newclassname.Substring(newclassname.Length - 1, 1);
if (sRightChar.ToLower() == "y") //So City changes to Cities
{
newclassname = newclassname.Substring(0, newclassname.Length - 1) + "ies";//So City changes to Cities
}
else
{
newclassname = newclassname + "s";
}
}
if (newclassname != tablename)
tableAliasName = "\r\n\t[Alias(\"" + tablename + "\")]"; //can't be changing the tablename without announcing it
sFixedClassTemplate = TemplateOrmLiteMySQL.Replace("~tableAlias~", tableAliasName); //just /r/n or it has a table name alias
if (sIHasTypeInterface.Length > 0)
{
sOrmLiteClassDef = sFixedClassTemplate.Replace("~classname~", newclassname + ":" + sIHasTypeInterface);
}
else
{
sOrmLiteClassDef = sFixedClassTemplate.Replace("~classname~", newclassname);
}
int iMaxZ = alCompositeIndexes.Count - 1;
String sCompositePrefix = String.Empty;
for (int z = 0; z <= iMaxZ; z++)
{
sCompositePrefix = sCompositePrefix + "\r\n" + alCompositeIndexes[z];
}
String sAllProps = sbProps.ToString();
if (oldClassName != newclassname)
dSwapRefs.Add(oldClassName, newclassname);
sOrmLiteClassDef = sCompositePrefix + sOrmLiteClassDef.Replace("~theprops~", sAllProps);
}
catch (Exception ex4) { SimpleLog.RecordException(ex4, "GetOrmLiteMySqlClassdef swap vals and add composite index prefix"); }
dForeignKey = null;
dOneFieldIndex = null;
alFields = null;
alCompositeIndexes = null;
alDbFieldTypes = null;
htTypes = null;
sbProps = null;
return sOrmLiteClassDef;
}
private String GenMultiPRIclassGuts(ref ArrayList alFields, ref ArrayList alPRIFields, ref Dictionary<string, string> dForeignKey, ref Dictionary<string, string> dOneFieldIndex)
{
String sDecorate = String.Empty;
StringBuilder sbMPriProps = new StringBuilder();
String tablename = String.Empty;
String fieldname = String.Empty;
String fieldtype = String.Empty;
String isnullable = String.Empty;
String keytypename = String.Empty;
String defval = String.Empty;
String extra = String.Empty;
int iMax = alFields.Count;
MySQLFieldDef tblfield = null;
String sSpecialCompositeID = String.Empty;
for (int i = 0; i < iMax; i++) //build the special composite field for Id for OrmLite for multiField Primary Key
{ //public string Id { get { return this.OrderId + "/" + this.ProductId; } }
sDecorate = String.Empty;
tblfield = (MySQLFieldDef)alFields[i];
tablename = tblfield.TableName;
fieldname = tblfield.FieldName;
fieldtype = tblfield.FieldType;
isnullable = tblfield.Nullable;
keytypename = tblfield.Key;
defval = tblfield.Default;
extra = tblfield.Extra;
if (alPRIFields.Contains(fieldname) == true)
{
//TO DO - need to handle fieldnames with spaces and add if so, add the required Alias!
//public string Id { get { return this.FieldName1 + ((char)254).ToString() + this.FieldName2 + ((char)254).ToString() ... + this.FieldNameN ; } }
if (String.IsNullOrEmpty(sSpecialCompositeID) == true)
{
sSpecialCompositeID = "this." + fieldname + ".ToString()";
}
else
{
sSpecialCompositeID = sSpecialCompositeID + " + ((char)254).ToString() + this." + fieldname + ".ToString()";
}
}
}
sbMPriProps.Append("\r\n\t[Ignore]\r\n\tpublic string Id { \r\n\tget { return " + sSpecialCompositeID + "; }\r\n\t}");
// all the other fields not marked as PRIMARY KEY FIELDS
for (int i = 0; i < iMax; i++)
{
sDecorate = String.Empty;
tblfield = (MySQLFieldDef)alFields[i];
tablename = tblfield.TableName;
fieldname = tblfield.FieldName;
fieldtype = tblfield.FieldType;
isnullable = tblfield.Nullable;
keytypename = tblfield.Key;
defval = tblfield.Default;
extra = tblfield.Extra;
if (alPRIFields.Contains(fieldname) == true)
{
try
{
if (extra == "auto_increment")
{
sDecorate = "\r\n\t[AutoIncrement]";
}
String dotNetType = GetDotNetType(fieldtype);
if (isnullable != "NO")
{
if (dotNetType.ToLower() != "string")
dotNetType = dotNetType + "?"; // adding the ? to end of type signifies nullable
}
else { sDecorate = sDecorate + "\r\n\t[Required]"; }
if (dotNetType.ToLower() == "string")
{
if (fieldtype.ToLower().IndexOf("char") != -1)
{
Int64 iCharSize = ParseFieldSize(fieldtype);
sDecorate = sDecorate + "\r\n\t[StringLength(" + iCharSize.ToString() + ")]";
}
else
{
if (fieldtype.ToLower().IndexOf("datetime") != -1)
{ //Int64 iCharSize = // 2005-12-23 00:12:34.001 ParseFieldSize(fieldtype);
//one example showed "2011-01-01 12:34:59.123456" why 6 digit millisecond? makes no sense
// well it could support this "2011-01-01 12:34:59.123 AM"
//so 26
sDecorate = sDecorate + "\r\n\t[StringLength(26)]";
}
else
{
if (fieldtype.ToLower().IndexOf("timestamp") != -1)
{
sDecorate = sDecorate + "\r\n\t[StringLength(26)]";
}
}
}
}
if (dForeignKey.ContainsKey(fieldname) == true)
sDecorate = sDecorate + "\r\n\t" + dForeignKey[fieldname];
if (dOneFieldIndex.ContainsKey(fieldname) == true)
sDecorate = sDecorate + "\r\n\t" + dOneFieldIndex[fieldname];
String sProp = GetProp(fieldname, dotNetType);
if (String.IsNullOrEmpty(defval) == false)
{
if (defval != "CURRENT_TIMESTAMP") //skip the timestamps
{
String sDefType = dotNetType.Replace("?", " ").Trim();// chop off the ? for those int that can be null
sDecorate = sDecorate + "\r\n\t[Default(typeof(" + sDefType + "), \"" + defval + "\")]";
}
}
String sFieldClassDef = sDecorate + "\r\n" + sProp;
sbMPriProps.Append("\r\n" + sFieldClassDef);
}
catch (Exception ex5) { SimpleLog.RecordException(ex5, "GetOrmLiteMySqlClassdef get field defs"); }
}
else
{
try
{
if (extra == "auto_increment")
{
sDecorate = "\r\n\t[AutoIncrement]";
if (tblfield.FieldName != "Id")
{
sDecorate = sDecorate + "\r\n\t[Alias(\"" + tblfield.FieldName + "\")]";
tblfield.FieldName = "Id"; //change the class field to use to Id
}
}
String dotNetType = GetDotNetType(fieldtype);
if (isnullable != "NO")
{
if (dotNetType.ToLower() != "string")
dotNetType = dotNetType + "?"; // adding the ? to end of type signifies nullable
}
else { sDecorate = sDecorate + "\r\n\t[Required]"; }
//if (keytypename == "UNI")
// sDecorate = sDecorate + "\r\n\t[Index(Unique = true)]";
if (dotNetType.ToLower() == "string")
{
if (fieldtype.ToLower().IndexOf("char") != -1)
{
Int64 iCharSize = ParseFieldSize(fieldtype);
sDecorate = sDecorate + "\r\n\t[StringLength(" + iCharSize.ToString() + ")]";
}
else
{
if (fieldtype.ToLower().IndexOf("datetime") != -1)
{ //Int64 iCharSize = // 2005-12-23 00:12:34.001 ParseFieldSize(fieldtype);
//one example showed "2011-01-01 12:34:59.123456" why 6 digit millisecond? makes no sense
// well it could support this "2011-01-01 12:34:59.123 AM"
//so 26
sDecorate = sDecorate + "\r\n\t[StringLength(26)]";
}
else
{
if (fieldtype.ToLower().IndexOf("timestamp") != -1)
{
sDecorate = sDecorate + "\r\n\t[StringLength(26)]";
}
}
}
}
if (dForeignKey.ContainsKey(fieldname) == true)
sDecorate = sDecorate + "\r\n\t" + dForeignKey[fieldname];
if (dOneFieldIndex.ContainsKey(fieldname) == true)
sDecorate = sDecorate + "\r\n\t" + dOneFieldIndex[fieldname];
String sProp = GetProp(tblfield.FieldName, dotNetType);
if (String.IsNullOrEmpty(defval) == false)
{
if (defval != "CURRENT_TIMESTAMP") //skip the timestamps
{
String sDefType = dotNetType.Replace("?", " ").Trim();// chop off the ? for those int that can be null
sDecorate = sDecorate + "\r\n\t[Default(typeof(" + sDefType + "), \"" + defval + "\")]";
}
}
String sFieldClassDef = sDecorate + "\r\n" + sProp;
sbMPriProps.Append("\r\n" + sFieldClassDef);
}
catch (Exception ex5) { SimpleLog.RecordException(ex5, "GetOrmLiteMySqlClassdef get field defs"); }
}
}
return sbMPriProps.ToString();
}
private String SwapFixedClassNames(ref String sAllClassDef, ref Dictionary<string, string> dictSwapClassRefs)
{
String FixedClassRef = sAllClassDef;
foreach (var key in dictSwapClassRefs.Keys)
{
String oldClassName = key;
String newclassname = dictSwapClassRefs[key];
if (FixedClassRef.IndexOf("[References(typeof(" + oldClassName) != -1)
FixedClassRef = FixedClassRef.Replace("[References(typeof(" + oldClassName + ")", "[References(typeof(" + newclassname + ")"); // since we changed the classname, now the references are WRONG, fix it!
}
return FixedClassRef;
}
private bool IsClassNameAFieldName(ref ArrayList alFields, String newClassName)
{
bool bNeedChange = false;
for (int t = 0; t < alFields.Count; t++)
{
MySQLFieldDef fdef = (MySQLFieldDef)alFields[t];
if (fdef.FieldName.ToLower() == newClassName.ToLower())
{
bNeedChange = true;
break;
}
}
return bNeedChange;
}
private String GetDotNetType(String p_sMySQLFieldDef)
{
#region ADO.NET supported types in abstract class OrmLiteDialectProviderBase<TDialect>
/* ADO.NET UNDERSTOOD DATA TYPES:
COUNTER DbType.Int64
AUTOINCREMENT DbType.Int64
IDENTITY DbType.Int64
LONG DbType.Int64
TINYINT DbType.Byte
INTEGER DbType.Int64
INT DbType.Int32
VARCHAR DbType.String
NVARCHAR DbType.String
CHAR DbType.String
NCHAR DbType.String
TEXT DbType.String
NTEXT DbType.String
STRING DbType.String
DOUBLE DbType.Double
FLOAT DbType.Double
REAL DbType.Single
BIT DbType.Boolean
YESNO DbType.Boolean
LOGICAL DbType.Boolean
BOOL DbType.Boolean
NUMERIC DbType.Decimal
DECIMAL DbType.Decimal
MONEY DbType.Decimal
CURRENCY DbType.Decimal
TIME DbType.DateTime
DATE DbType.DateTime
TIMESTAMP DbType.DateTime
DATETIME DbType.DateTime
BLOB DbType.Binary
BINARY DbType.Binary
VARBINARY DbType.Binary
IMAGE DbType.Binary
GENERAL DbType.Binary
OLEOBJECT DbType.Binary
GUID DbType.Guid
UNIQUEIDENTIFIER DbType.Guid
MEMO DbType.String
NOTE DbType.String
LONGTEXT DbType.String
LONGCHAR DbType.String
SMALLINT DbType.Int16
BIGINT DbType.Int64
LONGVARCHAR DbType.String
SMALLDATE DbType.DateTime
SMALLDATETIME DbType.DateTime
*
*
protected DbTypes<TDialect> DbTypeMap = new DbTypes<TDialect>();
protected void InitColumnTypeMap()
{
DbTypeMap.Set<string>(DbType.String, StringColumnDefinition);
DbTypeMap.Set<char>(DbType.StringFixedLength, StringColumnDefinition);
DbTypeMap.Set<char?>(DbType.StringFixedLength, StringColumnDefinition);
DbTypeMap.Set<char[]>(DbType.String, StringColumnDefinition);
DbTypeMap.Set<bool>(DbType.Boolean, BoolColumnDefinition);
DbTypeMap.Set<bool?>(DbType.Boolean, BoolColumnDefinition);
DbTypeMap.Set<Guid>(DbType.Guid, GuidColumnDefinition);
DbTypeMap.Set<Guid?>(DbType.Guid, GuidColumnDefinition);
DbTypeMap.Set<DateTime>(DbType.DateTime, DateTimeColumnDefinition);
DbTypeMap.Set<DateTime?>(DbType.DateTime, DateTimeColumnDefinition);
DbTypeMap.Set<TimeSpan>(DbType.Time, TimeColumnDefinition);
DbTypeMap.Set<TimeSpan?>(DbType.Time, TimeColumnDefinition);
DbTypeMap.Set<DateTimeOffset>(DbType.Time, TimeColumnDefinition);
DbTypeMap.Set<DateTimeOffset?>(DbType.Time, TimeColumnDefinition);
DbTypeMap.Set<byte>(DbType.Byte, IntColumnDefinition);
DbTypeMap.Set<byte?>(DbType.Byte, IntColumnDefinition);
DbTypeMap.Set<sbyte>(DbType.SByte, IntColumnDefinition);
DbTypeMap.Set<sbyte?>(DbType.SByte, IntColumnDefinition);
DbTypeMap.Set<short>(DbType.Int16, IntColumnDefinition);
DbTypeMap.Set<short?>(DbType.Int16, IntColumnDefinition);
DbTypeMap.Set<ushort>(DbType.UInt16, IntColumnDefinition);
DbTypeMap.Set<ushort?>(DbType.UInt16, IntColumnDefinition);
DbTypeMap.Set<int>(DbType.Int32, IntColumnDefinition);
DbTypeMap.Set<int?>(DbType.Int32, IntColumnDefinition);
DbTypeMap.Set<uint>(DbType.UInt32, IntColumnDefinition);
DbTypeMap.Set<uint?>(DbType.UInt32, IntColumnDefinition);
DbTypeMap.Set<long>(DbType.Int64, LongColumnDefinition);
DbTypeMap.Set<long?>(DbType.Int64, LongColumnDefinition);
DbTypeMap.Set<ulong>(DbType.UInt64, LongColumnDefinition);
DbTypeMap.Set<ulong?>(DbType.UInt64, LongColumnDefinition);
DbTypeMap.Set<float>(DbType.Single, RealColumnDefinition);
DbTypeMap.Set<float?>(DbType.Single, RealColumnDefinition);
DbTypeMap.Set<double>(DbType.Double, RealColumnDefinition);
DbTypeMap.Set<double?>(DbType.Double, RealColumnDefinition);
DbTypeMap.Set<decimal>(DbType.Decimal, DecimalColumnDefinition);
DbTypeMap.Set<decimal?>(DbType.Decimal, DecimalColumnDefinition);
DbTypeMap.Set<byte[]>(DbType.Binary, BlobColumnDefinition);
}
*/
#endregion
String sDotNetORMClassType = "string"; //Default type for all fields
String sFieldDef = p_sMySQLFieldDef.ToLower();
if (Strings.InStr(sFieldDef, "char", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "string";
}
else
{
if (Strings.InStr(sFieldDef, "tinyint", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "byte";
//sDotNetORMClassType = "int"; //be careful not to exceed the byte!!! how else shall I fix this?
//added IHasByteID interface instead
}
else if (Strings.InStr(sFieldDef, "year", CompareMethod.Text) == 1)
{
sDotNetORMClassType = "Int16";
}
else if (Strings.InStr(sFieldDef, "bigint", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "Int64"; //actually bigger is allowed! 18446744073709551615, but lets be safe, and no unsigned as drivers don't support them
}
else if (Strings.InStr(sFieldDef, "double", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "double";
}
else if (Strings.InStr(sFieldDef, "numeric", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "decimal";
}
else if (Strings.InStr(sFieldDef, "real", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "decimal";
}
else if (Strings.InStr(sFieldDef, "int", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "int";
}
else if (Strings.InStr(sFieldDef, "binary", CompareMethod.Text) == 1 | Strings.InStr(sFieldDef, "bit", CompareMethod.Text) == 1)
{
sDotNetORMClassType = "byte[]";
}
else if (Strings.InStr(sFieldDef, "bit", CompareMethod.Text) == 1)
{
sDotNetORMClassType = "byte";
}
else if (Strings.InStr(sFieldDef, "decimal", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "decimal";
}
else if (Strings.InStr(sFieldDef, "float", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "double";
}
else if (Strings.InStr(sFieldDef, "timestamp", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "string";
}
else if (Strings.InStr(sFieldDef, "datetime", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "string";
}
else if (Strings.InStr(sFieldDef, "tinyblob", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "byte[]";
}
else if (Strings.InStr(sFieldDef, "tinytext", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "string";
}
else if (Strings.InStr(sFieldDef, "mediumtext", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "string";
}
else if (Strings.InStr(sFieldDef, "mediumblob", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "byte[]";
}
else if (Strings.InStr(sFieldDef, "text", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "string";
}
else if (Strings.InStr(sFieldDef, "blob", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "byte[]";
}
else if (Strings.InStr(sFieldDef, "longtext", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "string";
}
else if (Strings.InStr(sFieldDef, "longblob", CompareMethod.Text) > 0)
{
sDotNetORMClassType = "byte[]";
}
}
return sDotNetORMClassType;
}
private String GetProp(String FieldName, String FieldType)
{
String sPropDef = "\tpublic " + FieldType + " " + FieldName + " { get; set; } " + "\r\n";
return sPropDef;
}
private bool SkipField(String MySQLFieldType, bool IsForUpdate)
{
bool bSkipThisType = false;
if (MySQLFieldType.IndexOf("blob") >= 0) //does it work for blobs or what? must test
{
bSkipThisType = true;
}
else
{
if (MySQLFieldType.IndexOf("text") >= 0) // does it work for text or only varchar(255)? can we set it somewere?
{
bSkipThisType = true;
}
}
if (IsForUpdate == true)
{
if (MySQLFieldType.IndexOf("timestamp") >= 0) // we don't want timestamp fiddled with directly... be aware of the unique "features" of mysql timestamp fields, especially if you have more than one! differs by version sometimes too!
{
bSkipThisType = true;
}
}
return bSkipThisType;
}
private System.Collections.ArrayList GetTables(MySqlConnection conn, String dbname)
{
MySqlDataReader reader = null;
System.Collections.ArrayList tblNames = new System.Collections.ArrayList();
MySqlCommand cmd = new MySqlCommand("SHOW full tables from " + dbname, conn);
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
String tbl = reader.GetString(0);
String tblOrView = reader.GetString(1);
if (tblOrView.ToLower() != "view")
if (tbl != "dbinfo")
tblNames.Add(tbl);
}
}
catch (Exception ex3) { SimpleLog.RecordException(ex3, "GetTables"); }
finally
{
if (reader != null) reader.Close();
reader = null;
}
return tblNames;
}
private System.Collections.ArrayList GetDatabases(MySqlConnection conn)
{
MySqlDataReader reader = null;
System.Collections.ArrayList DbNames = new System.Collections.ArrayList();
MySqlCommand cmd = new MySqlCommand("SHOW DATABASES;", conn);
try
{
reader = cmd.ExecuteReader();
while (reader.Read()) { DbNames.Add(reader.GetString(0).ToString()); }
}
catch (Exception ex3) { SimpleLog.RecordException(ex3, "GetDatabases"); }
finally { if (reader != null) reader.Close(); }
return DbNames;
}
private String GetIntegerKeyFieldName(ref ArrayList alFields)
{
String sIntKeyFieldName = String.Empty;
MySQLFieldDef fdef;
for (int t = 0; t < alFields.Count; t++)
{
fdef = (MySQLFieldDef)alFields[t];
if (fdef.Key == "PRI")
{
String sType = fdef.FieldType;
if (sType.Substring(0, 3) == "int")
{
sIntKeyFieldName = fdef.FieldName;
}
}
}
return sIntKeyFieldName;
}
private String GetAssignFromReader(ref ArrayList alFields)
{
StringBuilder sbReaderAssigns = new StringBuilder();
MySQLFieldDef fdef;
String FieldName = String.Empty;
String FieldType = String.Empty;
sbReaderAssigns.Append("\r\n");
for (int t = 0; t < alFields.Count; t++)
{
fdef = (MySQLFieldDef)alFields[t];
FieldName = fdef.FieldName;
FieldType = fdef.FieldType;
String sDefaultVal = "";
String sDotNetType = Utils.GetDotnetVarType(FieldType);
if (Utils.IsNumericField(FieldType) == true)
{
sDefaultVal = "(" + sDotNetType + ") 0";
}
else
{
String sTest = FieldType.ToLower();
Int32 lpos = sTest.IndexOf("date");
if (lpos >= 0)
{
sDefaultVal = "DateTime.Now";
}
else
{
lpos = sTest.IndexOf("timestamp");
if (lpos >= 0)
{
sDefaultVal = "DateTime.Now";
}
else
{
sDefaultVal = "String.Empty";
}
}
}
sbReaderAssigns.Append("\t\t\t\t\t\t\t\t\t\t\tarec." + FieldName.Replace(" ", "") + " = (DBNull.Value == dr[\"" + FieldName + "\"]) ? " + sDefaultVal + " : (" + sDotNetType + ") dr[\"" + FieldName + "\"];\r\n");
}
return sbReaderAssigns.ToString();
}
private String GetMySQLFieldTypeFromName(ref ArrayList alFields, String FieldName)
{
MySQLFieldDef fdef;
String FieldType = String.Empty;
for (int t = 0; t < alFields.Count; t++)
{
fdef = (MySQLFieldDef)alFields[t];
if (FieldName == fdef.FieldName)
{
FieldType = fdef.FieldType;
break;
}
}
return FieldType;
}
private String prvGetWidth(ref MySQLFieldDef afield)
{
String sFieldType = afield.FieldType;
Int32 lpos = sFieldType.IndexOf("char");
if (lpos >= 0)
{
return "15000";
}
else
{
lpos = sFieldType.IndexOf("text");
if (lpos >= 0)
{
return "15000";
}
else
{
return "2500";
}
}
}
private Boolean prvSkipField(string value)
{
return (value == "datastate" || value == "editdate" || value == "editby" || value == "created");
}
private String GetField(String FieldName, String FieldType)
{
String sVarType = Utils.GetDotnetVarType(FieldType);
return "\t\tprotected " + sVarType + " _" + FieldName.Replace(" ", "") + "; \r\n";
}
private void prvAppendFields(ref StringBuilder sbFields, String FieldString)
{
if (sbFields.Length == 0) { sbFields.Append(FieldString); }
else { sbFields.Append(", " + FieldString); }
}
private Int64 ParseFieldSize(String FieldDef)
{
int lpos = 0;
int rpos = 0;
int sizelen = 0;
Int64 fieldlen = 0;
String LenChars = "";
fieldlen = prvGetCharSize(ref FieldDef);
if (fieldlen == -1)
{
lpos = FieldDef.IndexOf("(");
rpos = FieldDef.IndexOf(")");
if (lpos != -1)
{
if (rpos != -1)
{
if (lpos + 1 + sizelen < FieldDef.Length - 1)
{
sizelen = rpos - lpos - 1;
LenChars = FieldDef.Substring(lpos + 1, sizelen);
rpos = LenChars.IndexOf(",");
if (rpos > 1)
{
LenChars = LenChars.Substring(0, rpos);
}
fieldlen = Convert.ToInt32(LenChars);
}
}
}
}
return fieldlen;
}
private Int64 prvGetCharSize(ref string p_sMySQLFieldDef)
{
Int64 functionReturnValue = 0;
int lpos = 0;
string sFieldDef = null;
string[] arParts = null;
sFieldDef = p_sMySQLFieldDef.ToLower();
if (Strings.InStr(sFieldDef, "char", CompareMethod.Text) > 0)
{
lpos = Strings.InStr(sFieldDef, "(", CompareMethod.Text);
if (lpos > 0)
{
if (lpos + 1 <= Strings.Len(sFieldDef))
{
functionReturnValue = (Int32)Conversion.Val(Strings.Mid(sFieldDef, lpos + 1));
}
}
}
else
{
if (Strings.InStr(sFieldDef, "tinyint", CompareMethod.Text) > 0)
{
functionReturnValue = 3;
}
else if (Strings.InStr(sFieldDef, "year", CompareMethod.Text) == 1)
{
functionReturnValue = 4;
}
else if (Strings.InStr(sFieldDef, "bigint", CompareMethod.Text) > 0)
{
functionReturnValue = 18; //actually bigger is allowed! 18446744073709551615, but lets be safe, and no unsigned as drivers don't support them
}
else if (Strings.InStr(sFieldDef, "double", CompareMethod.Text) > 0)
{
functionReturnValue = 64;
}
else if (Strings.InStr(sFieldDef, "numeric", CompareMethod.Text) > 0)
{
functionReturnValue = 64;
}
else if (Strings.InStr(sFieldDef, "real", CompareMethod.Text) > 0)
{
functionReturnValue = 64;
}
else if (Strings.InStr(sFieldDef, "int", CompareMethod.Text) > 0)
{
functionReturnValue = 10;
}
else if (Strings.InStr(sFieldDef, "binary", CompareMethod.Text) == 1 | Strings.InStr(sFieldDef, "bit", CompareMethod.Text) == 1)
{
lpos = Strings.InStr(sFieldDef, "(", CompareMethod.Text);
if (lpos > 0)
{
if (lpos + 1 <= Strings.Len(sFieldDef))
{
functionReturnValue = (Int32)Conversion.Val(Strings.Mid(sFieldDef, lpos + 1));
}
}
}
else if (Strings.InStr(sFieldDef, "decimal", CompareMethod.Text) > 0)
{
lpos = Strings.InStr(sFieldDef, "(", CompareMethod.Text);
if (lpos > 0)
{
if (lpos + 1 <= Strings.Len(sFieldDef))
{
sFieldDef = Strings.Mid(sFieldDef, lpos + 1);
arParts = sFieldDef.Split(',');
if (arParts.GetUpperBound(0) == 1)
{
functionReturnValue = (Int32)Conversion.Val(arParts[0]) + 1;
// + 1 for decimal - Val(arParts(1)) + 2
}
else
{
functionReturnValue = 9;
//default mysql dec(9,0)
}
}
}
}
else if (Strings.InStr(sFieldDef, "float", CompareMethod.Text) > 0)
{
functionReturnValue = 64;
}
else if (Strings.InStr(sFieldDef, "timestamp", CompareMethod.Text) > 0)
{
functionReturnValue = Strings.Len("mm/dd/yyyy hh:mm:ss PM");
}
else if (Strings.InStr(sFieldDef, "datetime", CompareMethod.Text) > 0)
{
functionReturnValue = Strings.Len("mm/dd/yyyy hh:mm:ss AM");
}
//VARCHAR(M), VARBINARY(M) L + 1 bytes if column values require 0 – 255 bytes,
// L + 2 bytes if values may require more than 255 bytes
//TINYBLOB, TINYTEXT L + 1 bytes, where L < 2^8
//BLOB, TEXT L + 2 bytes, where L < 2^16
//MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 2^24
//LONGBLOB, LONGTEXT L + 4 bytes, where L < 2^32
// let us not bump the edge (guessing at least 2 byte charset so make it even sized)
else if (Strings.InStr(sFieldDef, "tinyblob", CompareMethod.Text) > 0)
{
functionReturnValue = 254; //2^8 - 1 = 255, then make it even
}
else if (Strings.InStr(sFieldDef, "tinytext", CompareMethod.Text) > 0)
{
functionReturnValue = 254;
}
else if (Strings.InStr(sFieldDef, "mediumtext", CompareMethod.Text) > 0)
{
functionReturnValue = 65534; //2^16-2
}
else if (Strings.InStr(sFieldDef, "mediumblob", CompareMethod.Text) > 0)
{
functionReturnValue = 65534; //2^16-2
}
else if (Strings.InStr(sFieldDef, "text", CompareMethod.Text) > 0)
{
functionReturnValue = 16777212; //2^24 - 3, then make it even
}
else if (Strings.InStr(sFieldDef, "blob", CompareMethod.Text) > 0)
{
functionReturnValue = 16777212; //2^24 - 3, then make even
}
else if (Strings.InStr(sFieldDef, "longtext", CompareMethod.Text) > 0)
{
functionReturnValue = 4294967292; // 2^32 - 4
}
else if (Strings.InStr(sFieldDef, "longblob", CompareMethod.Text) > 0)
{
functionReturnValue = 4294967292; //2^32 - 4
}
}
return functionReturnValue;
}
public static class SimpleLog
{
private static object oErrorLogLock = new object();
private static String _ErrorLogFile = System.Environment.GetEnvironmentVariable("TEMP") + "\\OrmLiteMySQLClassesErrors.txt";
public static String ErrorFile
{
get
{
return _ErrorLogFile;
}
set
{
_ErrorLogFile = value;
}
}
private static string _SuccessLog = System.Environment.GetEnvironmentVariable("TEMP") + "\\OrmLiteMySQLClassesOK.txt";
public static String SuccessLog
{
get
{
return _SuccessLog;
}
set
{
_SuccessLog = value;
}
}
private static string _FailureLog = System.Environment.GetEnvironmentVariable("TEMP") + "\\OrmLiteMySQLClassesFail.txt";
public static String FailureLog
{
get
{
return _FailureLog;
}
set
{
_FailureLog = value;
}
}
public static void WriteToErrorLog(String ErrorMsg)
{
String sErrData = String.Empty;
try
{
WriteAuxLog(_ErrorLogFile, ErrorMsg);
}
catch { }
}
public static void WriteToErrorLog(Exception ex1)
{
String sErrData = String.Empty;
try
{
sErrData = FormatExceptionMessage(ex1);
WriteAuxLog(_ErrorLogFile, sErrData);
}
catch { }
}
public static void RecordException(Exception exceptionToLog)
{
try
{
String ErrorMsg = FormatExceptionMessage(exceptionToLog);
if (ErrorMsg.Length > 0)
{
WriteToErrorLog(ErrorMsg);
}
}
catch { }
}
public static void RecordException(Exception exceptionToLog, Int32 ErrorNumber, String SourceDescription)
{
try
{
String ErrorMsg = FormatExceptionMessage(exceptionToLog) + " errorNumber=" + ErrorNumber.ToString() + " " + SourceDescription;
if (ErrorMsg.Length > 0)
{
WriteToErrorLog(ErrorMsg);
}
}
catch { }
}
public static void RecordException(Exception exceptionToLog, String SourceDescription)
{
try
{
String ErrorMsg = FormatExceptionMessage(exceptionToLog) + " " + SourceDescription;
if (ErrorMsg.Length > 0)
{
WriteToErrorLog(ErrorMsg);
}
}
catch { }
}
public static void RecordException(Exception exceptionToLog, Int32 ErrorNumber)
{
try
{
String ErrorMsg = ErrorNumber.ToString() + " " + FormatExceptionMessage(exceptionToLog);
if (ErrorMsg.Length > 0)
{
WriteToErrorLog(ErrorMsg);
}
}
catch { }
}
public static void RecordException(String ErrorMsg)
{
try
{
WriteToErrorLog(ErrorMsg);
}
catch { }
}
public static void RecordException(String ErrorMsg, String ErrorSource)
{
try
{
WriteToErrorLog(ErrorSource + (char)253 + ErrorMsg + (char)254);
}
catch { }
}
public static String FormatExceptionMessage(Exception exceptionToLog)
{
String sMsg = String.Empty;
try
{
sMsg = System.Environment.MachineName + "|" + exceptionToLog.Source + "|" + exceptionToLog.Message + "|" + exceptionToLog.InnerException + "|" + exceptionToLog.Data;
}
catch { }
return sMsg;
}
public static void LogSuccess(String p_sData)
{
WriteAuxLog(_SuccessLog, p_sData);
}
public static void LogFailure(String p_sData)
{
WriteAuxLog(_FailureLog, p_sData);
}
public static void LogSuccess(String Prefix, String p_sData)
{
String sFile = System.Environment.GetEnvironmentVariable("TEMP") + "\\EVEmailExportOK-" + Prefix + ".txt";
WriteAuxLog(sFile, p_sData);
}
public static void LogFailure(String Prefix, String p_sData)
{
String sFile = System.Environment.GetEnvironmentVariable("TEMP") + "\\EVEmailExportFail-" + Prefix + ".txt";
WriteAuxLog(_FailureLog, p_sData);
}
public static void WriteAuxLog(String ErrorLogFile, String p_sData)
{
String sErrorLogFile = System.Environment.GetEnvironmentVariable("TEMP") + "\\EVEmailExportErrors.txt";
if (String.IsNullOrEmpty(ErrorLogFile) == false)
sErrorLogFile = ErrorLogFile;
try
{
lock (oErrorLogLock)
{
StreamWriter m_streamWriter = null;
FileStream fs = null;
if (sErrorLogFile.Length > 0)
{
if (p_sData.Length > 0)
{
try
{
String sData = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString() + "|" + p_sData;
fs = new FileStream(sErrorLogFile, FileMode.OpenOrCreate, FileAccess.Write);
m_streamWriter = new StreamWriter(fs);
m_streamWriter.BaseStream.Seek(0, SeekOrigin.End);
m_streamWriter.WriteLine(sData);
m_streamWriter.Flush();
}
catch { }
finally
{
try
{
m_streamWriter.Close();
}
catch { }
try { fs.Close(); }
catch { }
m_streamWriter = null;
fs = null;
}
}
}
}
}
catch { }
}
}
}
public static class Utils
{
public static bool VerifyDirectory(String outdir)
{
DirectoryInfo di = new DirectoryInfo(outdir);
if (!di.Exists)
{
di.Create();
di.Refresh();
}
return di.Exists;
}
public static bool WriteToFile(String outfile, String data)
{
bool result = false;
FileStream fs = null;
StreamWriter w = null;
try
{
fs = new FileStream(outfile, FileMode.Create);
w = new StreamWriter(fs, System.Text.Encoding.ASCII);
w.Write(data);
}
catch (Exception)
{
throw;
}
finally
{
if (fs != null)
{
if (w != null)
{
w.Flush();
w.Close();
result = true;
}
fs.Close();
}
}
return result;
}
//public static Int32 GetIntFromString(ref string sValue)
//{
// Int32 iTest = 0;
// if (int.TryParse(sValue, out iTest) == true)
// return iTest;
// else
// return 0;
//}
public static bool IsNumericField(String MySQLFieldType)
{
bool bIsNumericType = false;
if (MySQLFieldType.IndexOf("int") != -1)
{
bIsNumericType = true;
}
else if (MySQLFieldType.IndexOf("tinyint") != -1)
{
bIsNumericType = true;
}
else if (MySQLFieldType.IndexOf("decimal") != -1)
{
bIsNumericType = true;
}
else if (MySQLFieldType.IndexOf("bigint") != -1)
{
bIsNumericType = true;
}
else if (MySQLFieldType.IndexOf("double") != -1)
{
bIsNumericType = true;
}
else if (MySQLFieldType.IndexOf("numeric") != -1)
{
bIsNumericType = true;
}
else if (MySQLFieldType.IndexOf("real") != -1)
{
bIsNumericType = true;
}
else if (MySQLFieldType.IndexOf("int") != -1)
{
bIsNumericType = true;
}
else if (MySQLFieldType.IndexOf("year") != -1)
{
bIsNumericType = true;
}
return bIsNumericType;
}
public static String GetDotnetVarType(String MySQLFieldType)
{
String PrivPropVarType = "System.String"; //default use this type for everything except some int numbers
Int32 lpos = MySQLFieldType.IndexOf("decimal");
if (lpos >= 0)
{
PrivPropVarType = "System.Decimal";
}
lpos = MySQLFieldType.IndexOf("date");
if (lpos >= 0)
{
PrivPropVarType = "System.Date";
}
//case "char(255)":
// PrivPropVarType = "System.String";
// break;
//case "varchar(255)":
// PrivPropVarType = "System.String";
// break;
//case "varchar(8192)":
// PrivPropVarType = "System.String";
// break;
//case "text":
// PrivPropVarType = "System.String";
// break;
//case "mediumtext":
// PrivPropVarType = "System.String";
// break;
//case "tinytext":
// PrivPropVarType = "System.String";
// break;
//case "longtext":
// PrivPropVarType = "System.String";
// break;
//case "bit":
// PrivPropVarType = "String";
// break;
//case "tinyblob":
// PrivPropVarType = "String"; //PrivPropVarType = "Byte[]";
// break;
//case "mediumblob":
// PrivPropVarType = "String"; //PrivPropVarType = "Byte[]";
// break;
//case "blob":
// PrivPropVarType = "String"; //PrivPropVarType = "Byte[]";
// break;
//case "longblob":
// PrivPropVarType = "String"; //PrivPropVarType = "Byte[]";
// break;
//case "bit(44)":
// PrivPropVarType = "System.UInt64";
// break;
//case "bit(8)":
// PrivPropVarType = "System.UInt64";
// break;
//case "decimal(5,2)":
// PrivPropVarType = "System.Decimal";
// break;
//case "decimal(10,0)":
// PrivPropVarType = "System.Decimal";
// break;
//case "decimal(22,21)":
// PrivPropVarType = "System.Decimal";
// break;
//case "decimal(33,15)":
// PrivPropVarType = "System.Decimal";
// break;
//case "decimal(30,30)":
// PrivPropVarType = "System.Decimal";
// break;
//case "decimal(35,0)":
// PrivPropVarType = "System.Decimal";
// break;
//case "binary(55)":
// PrivPropVarType = "System.Byte[]";
// break;
//case "tinyblob":
// PrivPropVarType = "System.Byte[]";
// break;
//case "mediumblob":
// PrivPropVarType = "System.Byte[]";
// break;
//case "longblob":
// PrivPropVarType = "System.Byte[]";
// break;
//case "blob":
// PrivPropVarType = "System.Byte[]";
// break;
//case "longblob":
// PrivPropVarType = "System.Byte[]";
// break;
switch (MySQLFieldType)
{
case "year(4)":
PrivPropVarType = "System.Int16";
break;
case "tinyint(1)":
PrivPropVarType = "System.SByte";
break;
case "tinyint(2)":
PrivPropVarType = "System.SByte";
break;
case "tinyint(3) unsigned":
PrivPropVarType = "System.SByte";
break;
case "tinyint(4)":
PrivPropVarType = "System.SByte";
break;
case "smallint(6)":
PrivPropVarType = "System.Int16";
break;
case "mediumint(9)":
PrivPropVarType = "System.Int32";
break;
case "int(10) unsigned":
PrivPropVarType = "UInt32";
break;
case "int(11)":
PrivPropVarType = "System.Int32";
break;
case "bigint(20) unsigned":
PrivPropVarType = "UInt64";
break;
case "bigint(20)":
PrivPropVarType = "System.Int64";
break;
case "float":
PrivPropVarType = "System.Single";
break;
case "double":
PrivPropVarType = "System.Double";
break;
case "time":
PrivPropVarType = "System.TimeSpan";
break;
case "timestamp":
PrivPropVarType = "System.DateTime";
break;
case "datetime":
PrivPropVarType = "System.DateTime";
break;
}
return PrivPropVarType;
}
//public static Int64 ParseFieldSize(String FieldDef)
//{
// int lpos = 0;
// int rpos = 0;
// int sizelen = 0;
// Int64 fieldlen = 0;
// String LenChars = "";
// fieldlen = prvGetCharSize(ref FieldDef);
// if (fieldlen == -1)
// {
// lpos = FieldDef.IndexOf("(");
// rpos = FieldDef.IndexOf(")");
// if (lpos != -1)
// {
// if (rpos != -1)
// {
// if (lpos + 1 + sizelen < FieldDef.Length - 1)
// {
// sizelen = rpos - lpos - 1;
// LenChars = FieldDef.Substring(lpos + 1, sizelen);
// rpos = LenChars.IndexOf(",");
// if (rpos > 1)
// {
// LenChars = LenChars.Substring(0, rpos);
// }
// fieldlen = Convert.ToInt32(LenChars);
// }
// }
// }
// }
// return fieldlen;
//}
private static Int64 prvGetCharSize(ref string p_sMySQLFieldDef)
{
Int64 functionReturnValue = 0;
int lpos = 0;
string sFieldDef = null;
string[] arParts = null;
sFieldDef = p_sMySQLFieldDef.ToLower();
if (Strings.InStr(sFieldDef, "char", CompareMethod.Text) > 0)
{
lpos = Strings.InStr(sFieldDef, "(", CompareMethod.Text);
if (lpos > 0)
{
if (lpos + 1 <= Strings.Len(sFieldDef))
{
functionReturnValue = (Int32)Conversion.Val(Strings.Mid(sFieldDef, lpos + 1));
}
}
}
else
{
if (Strings.InStr(sFieldDef, "tinyint", CompareMethod.Text) > 0)
{
functionReturnValue = 3;
}
else if (Strings.InStr(sFieldDef, "year", CompareMethod.Text) == 1)
{
functionReturnValue = 4;
}
else if (Strings.InStr(sFieldDef, "bigint", CompareMethod.Text) > 0)
{
functionReturnValue = 18; //actually bigger is allowed! 18446744073709551615, but lets be safe, and no unsigned as drivers don't support them
}
else if (Strings.InStr(sFieldDef, "double", CompareMethod.Text) > 0)
{
functionReturnValue = 64;
}
else if (Strings.InStr(sFieldDef, "numeric", CompareMethod.Text) > 0)
{
functionReturnValue = 64;
}
else if (Strings.InStr(sFieldDef, "real", CompareMethod.Text) > 0)
{
functionReturnValue = 64;
}
else if (Strings.InStr(sFieldDef, "int", CompareMethod.Text) > 0)
{
functionReturnValue = 10;
}
else if (Strings.InStr(sFieldDef, "binary", CompareMethod.Text) == 1 | Strings.InStr(sFieldDef, "bit", CompareMethod.Text) == 1)
{
lpos = Strings.InStr(sFieldDef, "(", CompareMethod.Text);
if (lpos > 0)
{
if (lpos + 1 <= Strings.Len(sFieldDef))
{
functionReturnValue = (Int32)Conversion.Val(Strings.Mid(sFieldDef, lpos + 1));
}
}
}
else if (Strings.InStr(sFieldDef, "decimal", CompareMethod.Text) > 0)
{
lpos = Strings.InStr(sFieldDef, "(", CompareMethod.Text);
if (lpos > 0)
{
if (lpos + 1 <= Strings.Len(sFieldDef))
{
sFieldDef = Strings.Mid(sFieldDef, lpos + 1);
arParts = sFieldDef.Split(',');
if (arParts.GetUpperBound(0) == 1)
{
functionReturnValue = (Int32)Conversion.Val(arParts[0]) + 1;
// + 1 for decimal - Val(arParts(1)) + 2
}
else
{
functionReturnValue = 9;
//default mysql dec(9,0)
}
}
}
}
else if (Strings.InStr(sFieldDef, "float", CompareMethod.Text) > 0)
{
functionReturnValue = 64;
}
else if (Strings.InStr(sFieldDef, "timestamp", CompareMethod.Text) > 0)
{
functionReturnValue = Strings.Len("mm/dd/yyyy hh:mm:ss PM");
}
else if (Strings.InStr(sFieldDef, "datetime", CompareMethod.Text) > 0)
{
functionReturnValue = Strings.Len("mm/dd/yyyy hh:mm:ss AM");
}
//VARCHAR(M), VARBINARY(M) L + 1 bytes if column values require 0 – 255 bytes,
// L + 2 bytes if values may require more than 255 bytes
//TINYBLOB, TINYTEXT L + 1 bytes, where L < 2^8
//BLOB, TEXT L + 2 bytes, where L < 2^16
//MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 2^24
//LONGBLOB, LONGTEXT L + 4 bytes, where L < 2^32
// let us not bump the edge (guessing at least 2 byte charset so make it even sized)
else if (Strings.InStr(sFieldDef, "tinyblob", CompareMethod.Text) > 0)
{
functionReturnValue = 254; //2^8 - 1 = 255, then make it even
}
else if (Strings.InStr(sFieldDef, "tinytext", CompareMethod.Text) > 0)
{
functionReturnValue = 254;
}
else if (Strings.InStr(sFieldDef, "mediumtext", CompareMethod.Text) > 0)
{
functionReturnValue = 65534; //2^16-2
}
else if (Strings.InStr(sFieldDef, "mediumblob", CompareMethod.Text) > 0)
{
functionReturnValue = 65534; //2^16-2
}
else if (Strings.InStr(sFieldDef, "text", CompareMethod.Text) > 0)
{
functionReturnValue = 16777212; //2^24 - 3, then make it even
}
else if (Strings.InStr(sFieldDef, "blob", CompareMethod.Text) > 0)
{
functionReturnValue = 16777212; //2^24 - 3, then make even
}
else if (Strings.InStr(sFieldDef, "longtext", CompareMethod.Text) > 0)
{
functionReturnValue = 4294967292; // 2^32 - 4
}
else if (Strings.InStr(sFieldDef, "longblob", CompareMethod.Text) > 0)
{
functionReturnValue = 4294967292; //2^32 - 4
}
}
return functionReturnValue;
}
}
public class MySQLFieldDef
{
public MySQLFieldDef()
{
}
public override string ToString()
{
return "`" + _databasename + "`.`" + _tablename + "`.`" + _fieldname + "` " + _fieldtype + " NULLABLE=" + _nullable + " Key=" + _key;
}
private int _fieldsize = 0;
public int Fieldsize
{
get { return _fieldsize; }
set { _fieldsize = value; }
}
private string _tablename = String.Empty;
public string TableName
{
get { return _tablename; }
set { _tablename = value; }
}
private string _databasename = String.Empty;
public string DataBaseName
{
get { return _databasename; }
set { _databasename = value; }
}
private string _fieldname = String.Empty;
public string FieldName
{
get { return _fieldname; }
set { _fieldname = value; }
}
private string _fieldtype = String.Empty;
public string FieldType
{
get { return _fieldtype; }
set { _fieldtype = value; }
}
private string _nullable = String.Empty;
public string Nullable
{
get { return _nullable; }
set { _nullable = value; }
}
private string _key = String.Empty;
public string Key
{
get { return _key; }
set { _key = value; }
}
private string _default = String.Empty;
public string Default
{
get { return _default; }
set { _default = value; }
}
private string _extra = String.Empty;
public string Extra
{
get { return _extra; }
set { _extra = value; }
}
}
}