Relationship Oriented Programming - The IDE plus More on Agile Project Management

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;

using Clifton.Tools.Strings;
using Clifton.Tools.Strings.Extensions;

using ROPLib;
using ROPLib.Interfaces;

namespace SQLLiteDataProvider
{
	public class SQLite	: IDataProvider
	{
		public StringBuilder Errors { get; protected set; }
		public DataSet DataSet { get { throw new NotImplementedException(); } }

		protected string filename;
		protected SQLiteConnection conn;
		protected Dictionary<DataType, string> dataTypeMap = new Dictionary<DataType, string>()
		{
			{DataType.Bool, "boolean"},
			{DataType.DateTime, "datetime"},
			{DataType.Int, "integer"},
			{DataType.String, "text"},
		};

		public SQLite()
		{
			Errors = new StringBuilder();
		}

		public void CreateDatabase(string filename, bool replaceExisting)
		{
			this.filename = filename;

			if (replaceExisting)
			{
				File.Delete(filename);
			}

			// Merely opening the database will create it.
			Open();
			Close();
		}

		public void CreateTables()
		{
			Open();

			Schema.Instance.EntitiesContainer.ForEach(t => t.Entities.ForEach(e =>
				{
					StringBuilder sql = GetCreateTableSql(e);
					ExecuteNonQuery(sql);
				}));

			Close();
		}

		public void CreateConstraints()
		{
			// SQLite does not support "alter table add constraint" capability.  Foreign Key constraints are created
			// when the table is created.
		}

		public StringBuilder GetCreateTableSql(Entity entity)
		{
			string comma = string.Empty;
			StringBuilder sb = new StringBuilder();
			sb.Append("create table ");
			sb.Append(entity.Name.Brace());
			sb.Append("(");

			entity.EntityAttributes.ForEach(a =>
				{
					sb.Append(comma);
					ROPLib.Attribute attr=Schema.Instance.GetAttribute(a.Name);
					sb.Append(a.FieldNameOrName.Brace());
					sb.Append(" ");
					sb.Append(dataTypeMap[attr.DataType]);

					if (a.IsPrimaryKey)
					{
						sb.Append(" PRIMARY KEY AUTOINCREMENT NOT NULL");
					}

					comma = ", ";
				});

			sb.Append(")");

			return sb;
		}

		public void Open()
		{
			conn = new SQLiteConnection("Data Source=" + filename + "; Pooling=true; FailIfMissing=false");
			conn.Open();
		}

		public void Close()
		{
			conn.Close();
			conn = null;
		}

		public void ExecuteNonQuery(StringBuilder sql)
		{
			SQLiteCommand cmd = conn.CreateCommand();
			cmd.CommandText = sql.ToString();
			cmd.ExecuteNonQuery();
		}
	}
}


/*
			SQLiteCommand cmd = conn.CreateCommand();
			cmd.CommandText = "create table Foobar (FoobarID integer PRIMARY KEY AUTOINCREMENT NOT NULL)";
			cmd.ExecuteNonQuery();
*/