/// <summary>
/// Create table given its structure.
/// </summary>
/// <param name="TableName">Table name</param>
/// <param name="fields">Table fields</param>
/// <returns>Return code (int)CLI.ErrorCode</returns>
public int CreateTable(string TableName, FastDbFields fields)
{
CLI.CliFieldDescriptor[] flds = (CLI.CliFieldDescriptor[])Array.CreateInstance(typeof(CLI.CliFieldDescriptor), fields.Count);
//(CLI.CliFieldDescriptor[])aFlds.ToArray(typeof(CLI.CliFieldDescriptor));
for (int i = 0; i < fields.Count; i++)
{
flds[i].type = fields[i].Type;
flds[i].flags = fields[i].Flags;
flds[i].name = Marshal.StringToHGlobalAnsi(fields[i].Name);
flds[i].refTableName = Marshal.StringToHGlobalAnsi(fields[i].RefTable);
flds[i].inverseRefFieldName = Marshal.StringToHGlobalAnsi(fields[i].InvRefField);
}
int rc = CLI.cli_create_table(session, TableName, fields.Count, flds);
for (int i = 0; i < fields.Count; i++)
{
Marshal.FreeCoTaskMem(flds[i].name);
Marshal.FreeCoTaskMem(flds[i].refTableName);
Marshal.FreeCoTaskMem(flds[i].inverseRefFieldName);
}
if (rc < 0 && rc != (int)CLI.ErrorCode.cli_table_already_exists)
{
CLI.CliCheck(rc);
}
return(rc);
}
/// <summary>
/// Extract a DDL of a table
/// </summary>
/// <param name="TableName">Name of a table</param>
/// <returns>A string representing the table's DDL.</returns>
public string ExtractTableDDL(string TableName)
{
FastDbFields flds = DescribeTable(TableName);
StringBuilder result = new StringBuilder("create table " + TableName + " (\n");
int nLen = 0;
for (int i = 0; i < flds.Count; i++)
{
nLen = (nLen > flds[i].Name.Length) ? nLen : flds[i].Name.Length;
}
for (int i = 0; i < flds.Count; i++)
{
result.AppendFormat("\t{0} {1}{2}", flds[i].Name.PadRight(nLen, ' '), CLI.CliTypeToStr(flds[i].Type, true),
(flds[i].RefTable == null) ? "" : " to " + flds[i].RefTable);
result.Append((i == (flds.Count - 1)) ? "" : ",\n");
}
result.Append(");\n");
string IDX_STR = "create {0} on {1}.{2};\n";
for (int i = 0; i < flds.Count; i++)
{
if (Enum.IsDefined(flds[i].Flags.GetType(), CLI.FieldFlags.cli_hashed))
{
result.AppendFormat(IDX_STR, "hash", TableName, flds[i].Name);
}
if (Enum.IsDefined(flds[i].Flags.GetType(), CLI.FieldFlags.cli_indexed))
{
result.AppendFormat(IDX_STR, "index", TableName, flds[i].Name);
}
}
return(result.ToString());
}
/// <summary>
/// Describes a table given its name.
/// </summary>
/// <param name="TableName">Name of the table to describe</param>
/// <param name="RaiseError">If true, an error check will be performed (default: true).</param>
/// <returns>A collection of fields fetched from the database's table.</returns>
public unsafe FastDbFields DescribeTable(string TableName, bool RaiseError)
{
FastDbFields fields = new FastDbFields();
void * p = null;
int rc = CLI.cli_describe(session, TableName, ref p);
if (RaiseError)
{
CLI.CliCheck(rc);
}
if (rc > 0)
{
try {
CLI.CliFieldDescriptor *fld = (CLI.CliFieldDescriptor *)p;
for (int i = 0; i < rc; i++, fld++)
{
Debug.Assert(fld->name != IntPtr.Zero, "Field name is a null pointer!");
string s = Marshal.PtrToStringAnsi(fld->name);
string sr = (fld->refTableName == IntPtr.Zero) ? null : Marshal.PtrToStringAnsi(fld->refTableName);
string si = (fld->inverseRefFieldName == IntPtr.Zero) ? null : Marshal.PtrToStringAnsi(fld->inverseRefFieldName);
fields.Add(s, fld->type, fld->flags, sr, si);
}
}
finally {
CLI.cli_free_memory(session, p);
}
}
return(fields);
}
internal FastDbCommand(FastDbConnection connection)
{
this.connection = connection;
statement = -1;
sql = "";
described = false;
sql_changed = false;
bof = false;
eof = false;
rec_no = -1;
table_name = "";
read_only = true;
row_count = 0;
fields = new FastDbFields();
vars = new FastDbParameters();
}
internal FastDbCommand(FastDbConnection connection)
{
this.connection = connection;
statement = -1;
sql = "";
described = false;
sql_changed = false;
bof = false;
eof = false;
rec_no = -1;
table_name = "";
read_only = true;
row_count = 0;
fields = new FastDbFields();
vars = new FastDbParameters();
}
/// <summary>
/// Describes a table given its name.
/// </summary>
/// <param name="TableName">Name of the table to describe</param>
/// <param name="RaiseError">If true, an error check will be performed (default: true).</param>
/// <returns>A collection of fields fetched from the database's table.</returns>
public unsafe FastDbFields DescribeTable(string TableName, bool RaiseError)
{
FastDbFields fields = new FastDbFields();
void* p = null;
int rc = CLI.cli_describe(session, TableName, ref p);
if (RaiseError) CLI.CliCheck(rc);
if (rc > 0) {
try {
CLI.CliFieldDescriptor* fld = (CLI.CliFieldDescriptor*)p;
for(int i=0; i<rc; i++, fld++) {
Debug.Assert(fld->name != IntPtr.Zero, "Field name is a null pointer!");
string s = Marshal.PtrToStringAnsi(fld->name);
string sr = (fld->refTableName == IntPtr.Zero) ? null : Marshal.PtrToStringAnsi(fld->refTableName);
string si = (fld->inverseRefFieldName == IntPtr.Zero) ? null : Marshal.PtrToStringAnsi(fld->inverseRefFieldName);
fields.Add(s, fld->type, fld->flags, sr, si);
}
}
finally {
CLI.cli_free_memory(session, p);
}
}
return fields;
}
/// <summary>
/// Create table given its structure.
/// </summary>
/// <param name="TableName">Table name</param>
/// <param name="fields">Table fields</param>
/// <returns>Return code (int)CLI.ErrorCode</returns>
public int CreateTable(string TableName, FastDbFields fields)
{
CLI.CliFieldDescriptor[] flds = (CLI.CliFieldDescriptor[])Array.CreateInstance(typeof(CLI.CliFieldDescriptor), fields.Count);
//(CLI.CliFieldDescriptor[])aFlds.ToArray(typeof(CLI.CliFieldDescriptor));
for(int i=0; i<fields.Count; i++) {
flds[i].type = fields[i].Type;
flds[i].flags = fields[i].Flags;
flds[i].name = Marshal.StringToHGlobalAnsi(fields[i].Name);
flds[i].refTableName = Marshal.StringToHGlobalAnsi(fields[i].RefTable);
flds[i].inverseRefFieldName = Marshal.StringToHGlobalAnsi(fields[i].InvRefField);
}
int rc = CLI.cli_create_table(session, TableName, fields.Count, flds);
for(int i=0; i < fields.Count; i++) {
Marshal.FreeCoTaskMem(flds[i].name);
Marshal.FreeCoTaskMem(flds[i].refTableName);
Marshal.FreeCoTaskMem(flds[i].inverseRefFieldName);
}
if (rc < 0 && rc != (int)CLI.ErrorCode.cli_table_already_exists) CLI.CliCheck(rc);
return rc;
}
static void Main(string[] args)
{
int rc;
uint oid = 0;
int nRecords = 3;
FastDbConnection connection = new FastDbConnection("test");
connection.InitDbSize = FastDbConnection.DefaultInitDatabaseSize*2;
connection.InitIdxSize = FastDbConnection.DefaultInitIndexSize*2;
connection.ExtensionQuantum = FastDbConnection.DefaultExtensionQuantum*2;
connection.Open();
FastDbFields fields = new FastDbFields();
fields.Add("int_field", CLI.FieldType.cli_int8, CLI.FieldFlags.cli_hashed);
fields.Add("str_field", CLI.FieldType.cli_asciiz, CLI.FieldFlags.cli_indexed);
rc = connection.CreateTable("test", fields);
if (rc < 0) Console.WriteLine("cli_create_table: {0}", CLI.CliErrorToStr(rc));
FastDbCommand command = connection.CreateCommand("insert into test");
command.Fields.Assign(fields); // You could also do: command.Describe();
DateTime time = DateTime.Now;
long key = 1999;
for (int i = 0; i < nRecords; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
command.Fields[0].asInt64 = key;
command.Fields[1].asString = key.ToString();
oid = command.Insert();
}
connection.Commit();
Console.WriteLine(String.Format("Elapsed time for inserting {0} records: {1} (speed: {2:F2})", nRecords, DateTime.Now - time, nRecords*1000 / (DateTime.Now - time).TotalMilliseconds));
command = connection.CreateCommand("insert into persons");
command.Fields.Add("name", CLI.FieldType.cli_asciiz, CLI.FieldFlags.cli_indexed);
command.Fields.Add("salary", CLI.FieldType.cli_int8);
command.Fields.Add("address", CLI.FieldType.cli_asciiz);
command.Fields.Add("weight", CLI.FieldType.cli_real8);
command.Fields.Add("subordinates", CLI.FieldType.cli_array_of_oid, CLI.FieldFlags.cli_noindex, "persons");
rc = connection.CreateTable("persons", command.Fields);
if (rc < 0)
Console.WriteLine("cli_create_table: {0}", rc);
Console.WriteLine("Name\tType\tSize\tCapacity\tFlags");
for (int i=0; i < command.Fields.Count; ++i)
{
FastDbField f = command.Fields[i];
Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}",
f.Name, f.Type, f.Size, f.Capacity, f.Flags);
}
connection.Commit();
time = DateTime.Now;
for (int i=0; i < nRecords; ++i)
{
int n = 0;
command.Fields[n++].asString = String.Format("Name{0}", i+1);
command.Fields[n++].asInt64 = (i+1) * 1000;
command.Fields[n++].asString = String.Format("Address {0}", 100-i);
command.Fields[n++].asDouble = (i+1) * 100;
command.Fields[n].ArraySize = (i==0) ? 0 : 1;
if (i > 0)
command.Fields[n].SetArrayValue(0, oid);
oid = command.Insert();
}
connection.Commit();
Console.WriteLine("Elapsed time for inserting {0} records: {1} (speed: {2:F2})", nRecords, DateTime.Now - time, nRecords*1000 / (DateTime.Now - time).TotalMilliseconds);
command = connection.CreateCommand("select * from persons");
//command.Execute();
DumpRecords(command);
connection.Commit();
command = connection.CreateCommand(@"select * from persons where length(subordinates) < %subordinates and salary > %salary");
command.Parameters.Add("subordinates", CLI.FieldType.cli_int4);
command.Parameters.Add("salary", CLI.FieldType.cli_int8);
command.Parameters["subordinates"].asInteger = 1;
command.Parameters["salary"].asInteger = 100;
//command.Execute();
DumpRecords(command);
}
static void Main(string[] args)
{
int rc, i, n;
uint oid = 0;
int nRecords = 200000;
FastDbConnection connection = new FastDbConnection("test");
connection.InitDbSize = FastDbConnection.DefaultInitDatabaseSize*2;
connection.InitIdxSize = FastDbConnection.DefaultInitIndexSize*2;
connection.ExtensionQuantum = FastDbConnection.DefaultExtensionQuantum*2;
connection.Open();
FastDbFields fields = new FastDbFields();
fields.Add("int_field", CLI.FieldType.cli_int8, CLI.FieldFlags.cli_hashed);
fields.Add("str_field", CLI.FieldType.cli_asciiz, CLI.FieldFlags.cli_hashed);
rc = connection.CreateTable("test", fields);
if (rc < 0) Console.WriteLine("cli_create_table: {0}", CLI.CliErrorToStr(rc));
FastDbCommand command = connection.CreateCommand("insert into test");
command.Fields.Assign(fields); // You could also do: command.Describe();
DateTime start = DateTime.Now;
long key = 1999;
for (i = 0; i < nRecords; i++) {
key = (3141592621L * key + 2718281829L) % 1000000007L;
command.Fields[0].asInt64 = key;
command.Fields[1].asString = key.ToString();
oid = command.Insert();
}
connection.Commit();
Console.WriteLine("Elapsed time for inserting {0} records: {1:F2} (speed: {2:F2})",
nRecords,
(DateTime.Now - start).TotalSeconds,
nRecords / (DateTime.Now - start).TotalSeconds);
FastDbCommand command1 = connection.CreateCommand("select * from test where int_field = %key");
FastDbCommand command2 = connection.CreateCommand("select * from test where str_field = %key");
command1.Parameters.Add("key", CLI.FieldType.cli_int8);
command2.Parameters.Add("key", CLI.FieldType.cli_asciiz);
command1.Fields.Assign(fields);
command2.Fields.Assign(fields);
int rc1;
int rc2;
key = 1999;
start = System.DateTime.Now;
for (i = 0; i < nRecords; i++) {
key = (3141592621L * key + 2718281829L) % 1000000007L;
command1.Parameters[0].asInt64 = key;
command2.Parameters[0].asString = key.ToString();
rc1 = command1.Execute();
rc2 = command2.Execute();
}
System.Console.WriteLine("Elapsed time for performing {0} index searches {1:F2} (speed: {2:F2})",
nRecords * 2, (DateTime.Now - start).TotalSeconds,
nRecords * 2 / (DateTime.Now - start).TotalSeconds);
command.SQL = "select * from test";
start = System.DateTime.Now;
n = command.Execute();
for(i=0; i < n; i++) {
command.Next();
}
Console.WriteLine(String.Format("Elapsed time for iteration through {0} records: {1:F2} (speed: {2:F2})",
nRecords,
(DateTime.Now - start).TotalSeconds,
nRecords / (DateTime.Now - start).TotalSeconds));
start = System.DateTime.Now;
/*
key = 1999;
for (i = 0; i < nRecords; i++) {
key = (3141592621L * key + 2718281829L) % 1000000007L;
Record rec = (Record) intIndex.Get(new Key(key));
Record removed = (Record)intIndex.Remove(new Key(key));
Debug.Assert(removed == rec);
strIndex.Remove(new Key(System.Convert.ToString(key)), rec);
rec.Deallocate();
}*/
command.Execute(true);
command.Delete();
connection.Commit();
System.Console.WriteLine("Elapsed time for deleting {0} records: {1:F2} (speed: {2:F2})",
nRecords,
(DateTime.Now - start).TotalSeconds,
nRecords/(DateTime.Now - start).TotalSeconds);
}