dmsManager(Data Provider)

/************************************************************************
--[ License ] -----------------------------------------------------------

  This program is free software; you can redistribute it and/or
  modify it under the terms of the GNU General Public License
  as published by the Free Software Foundation; either version 3
  of the License, or (at your option) any later version.

  This program is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.

  You should have received a copy of the GNU General Public License
  along with this program; if not, write to the Free Software
  Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.

  You may also access the licence here: http://www.gnu.org/licenses/gpl.html

 ------------------------------------------------------------------------
  Copyright (c) 2009-11 Gomez & Associates
/************************************************************************/

using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Runtime.InteropServices;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using Newtonsoft.Json;

namespace dmsManager
{
    public partial class DataProvider
    {
        const int SQLITE_OK         =   0;   /* Successful result */
        const int SQLITE_ERROR      =   1;   /* SQL error or missing database */
        const int SQLITE_INTERNAL   =   2;   /* An internal logic error in SQLite */
        const int SQLITE_PERM       =   3;   /* Access permission denied */
        const int SQLITE_ABORT      =   4;   /* Callback routine requested an abort */
        const int SQLITE_BUSY       =   5;   /* The database file is locked */
        const int SQLITE_LOCKED     =   6;   /* A table in the database is locked */
        const int SQLITE_NOMEM      =   7;   /* A malloc() failed */
        const int SQLITE_READONLY   =   8;   /* Attempt to write a readonly database */
        const int SQLITE_INTERRUPT  =   9;   /* Operation terminated by sqlite_interrupt() */
        const int SQLITE_IOERR      =  10;   /* Some kind of disk I/O error occurred */
        const int SQLITE_CORRUPT    =  11;   /* The database disk image is malformed */
        const int SQLITE_NOTFOUND   =  12;   /* (Internal Only) Table or record not found */
        const int SQLITE_FULL       =  13;   /* Insertion failed because database is full */
        const int SQLITE_CANTOPEN   =  14;   /* Unable to open the database file */
        const int SQLITE_PROTOCOL   =  15;   /* Database lock protocol error */
        const int SQLITE_EMPTY      =  16;   /* (Internal Only) Database table is empty */
        const int SQLITE_SCHEMA     =  17;   /* The database schema changed */
        const int SQLITE_TOOBIG     =  18;   /* Too much data for one row of a table */
        const int SQLITE_CONSTRAINT =  19;   /* Abort due to constraint violation */
        const int SQLITE_MISMATCH   =  20;   /* Data type mismatch */
        const int SQLITE_MISUSE     =  21;   /* Library used incorrectly */
        const int SQLITE_NOLFS      =  22;   /* Uses OS features not supported on host */
        const int SQLITE_AUTH       =  23;   /* Authorization denied */
        const int SQLITE_FORMAT     =  24;   /* Auxiliary database format error */
        const int SQLITE_RANGE      =  25;   /* 2nd parameter to sqlite3_bind out of range */
        const int SQLITE_NOTADB     =  26;   /* File opened that is not a database file */
        const int SQLITE_ROW        = 100;   /* sqlite_step() has another row ready */
        const int SQLITE_DONE       = 101;   /* sqlite_step() has finished executing */

        const int SQLITE_UTF8          = 1;
        const int SQLITE_UTF16LE       = 2;
        const int SQLITE_UTF16BE       = 3;
        const int SQLITE_UTF16         = 4;    /* Use native byte order */
        const int SQLITE_ANY           = 5;    /* sqlite3_create_function only */
        const int SQLITE_UTF16_ALIGNED = 8;    /* sqlite3_create_collation only */

        const int SQLITE_INTEGER = 1;
        const int SQLITE_FLOAT   = 2;
        const int SQLITE_TEXT    = 3;
        const int SQLITE_BLOB    = 4;
        const int SQLITE_NULL    = 5;


        internal static IntPtr SQLITE_STATIC    = IntPtr.Zero;
        internal static IntPtr SQLITE_TRANSIENT = (IntPtr)(-1);

        internal static IntPtr NULL_POINTER = IntPtr.Zero;

        const string SQLITE_DLL = "sqlite3.dll";

        internal bool   _cmudFormat   = Properties.Settings.Default.cmudFormat;
        internal string _dQuote       = "\"";
        internal string _hashEntrySep = Properties.Settings.Default.hashEntrySep;
        internal string _keyValueSep  = Properties.Settings.Default.keyValueSep;
        internal string _errormsg     = "";
        internal string _null         = Properties.Settings.Default.NULL;
        internal bool   _open         = false;
        internal string _path         = "";
        internal string _procPath     = "";
        internal string _provider     = Properties.Settings.Default.Provider;
        internal string _resultSep    = Properties.Settings.Default.resultSep;
        internal bool   _useJson      = Properties.Settings.Default.jsonFormat;
        internal bool   _useMap       = Properties.Settings.Default.useMap;
        internal int    _version      = 0;

        internal static UTF8Encoding encoding = null; //for string to UTF-8 translation.

        DataSet _ds = null;

        statement_context _sc = null;

        /// <summary>
        /// This method is called by the constructor and does the actually object initalization.
        /// The sqlite3.dll is checked for version and the application terminates if lower than 3.0.0.0.
        /// </summary>
        /// <param name="path">
        /// Path to the database file (optional).
        /// </param>
        /// <param name="json">
        /// Flag indicating whether json-formatted result sets are returned.
        /// If this flag is reset an arraylist is returned instead.
        /// </param>
        private void init(string path, bool jason)
        {
            _version = sqlite3_libversion_number();

            if (3000000 > _version)
            {
                MessageBox.Show("Invalid SQLITE.DLL VERSION: " + _version);

                Application.Exit();
            }

            encoding = new UTF8Encoding(false, true);

            _ds = new DataSet();

            _sc = new statement_context();

            if (path != null) this.open(path);

            this.JSONFormat = jason;
        }

        /// <summary>
        ///  This function builds the SQlite3 custom functions found in the function table.
        /// </summary>
        /// <returns>
        /// Returns whether the operation is successful or not.
        /// If any of the function installations fails the installation terminates at that point.
        /// </returns>
        private int installUserFunctions()
        {
            int rc = SQLITE_OK;

            foreach (var f in uf) //create user functions.
            {
                rc = sqlite3_create_function(_conn, encoding.GetBytes(f.Key), f.Value.Count, SQLITE_ANY, NULL_POINTER, f.Value.Func, null, null);

                if (rc != SQLITE_OK) break;
            }

            return rc;
        }

        private string readFile(string filename)
        {
            StreamReader sr = null;

            string query = null;

            try
            {
                sr = new StreamReader(_procPath + filename);

                query = sr.ReadToEnd();

                sr.Close();
            }
            catch (IOException ioe)
            {
                _errormsg = ioe.Message;

                query = null;
            }
            finally
            {
                if (sr != null) sr.Close();
            }

            return query;
        }

        private void BindParameters()
        {
            int pCount = sqlite3_bind_parameter_count(_sc.stmtHandle), rCount = _sc.argc - _sc.argp, index = 0, mCount = pCount - rCount;

            int limit = _sc.argp + Math.Min(pCount, rCount);

            _sc.rc = SQLITE_OK;

            for (; _sc.argp < limit; _sc.argp++)
            {
                if (_sc.args[_sc.argp] is Int32) _sc.rc = sqlite3_bind_int(_sc.stmtHandle, ++index, (int) _sc.args[_sc.argp]);

                else if (_sc.args[_sc.argp] is Int64) _sc.rc = sqlite3_bind_double(_sc.stmtHandle, ++index, (double) _sc.args[_sc.argp]);

                else if (_sc.args[_sc.argp] != null) _sc.rc = sqlite3_bind_text(_sc.stmtHandle, ++index, (string) _sc.args[_sc.argp], -1, SQLITE_TRANSIENT);

                    else _sc.rc = sqlite3_bind_null(_sc.stmtHandle, ++index);

                if (_sc.rc != SQLITE_OK) break;
            }

            if (mCount > 0 && _sc.rc == SQLITE_OK)
            {
                for (; index < mCount; index++)
                {
                    _sc.rc = sqlite3_bind_null(_sc.stmtHandle, index);

                    if (_sc.rc != SQLITE_OK) break;
                }
            }
        }

        private void PrepareStatement()
        {
            string query = _sc.getNext();

            _sc.rc = sqlite3_prepare_v2(_conn, query, query.Length, out _sc.stmtHandle, out NULL_POINTER);

            if (_sc.rc != SQLITE_OK) _errormsg = sqlite3_errmsg(_conn);
        }

        private void FinalizeStatement()
        {
            _sc.rc = sqlite3_finalize(_sc.stmtHandle);

            _sc.stmtHandle = NULL_POINTER;

            if ( _sc.rc != SQLITE_OK) _errormsg = sqlite3_errmsg(_conn);
        }

        private int processScript(string query)
        {
            if (_open)
            {
                _errormsg = "";

                IntPtr errMsg;

                sqlite3_exec(_conn, query, null, NULL_POINTER, out errMsg);

                if (errMsg != NULL_POINTER)
                {
                    _errormsg = Marshal.PtrToStringAnsi(errMsg);

                    sqlite3_free(errMsg);

                    return SQLITE_ERROR;
                }

                return SQLITE_OK;
            }

            _errormsg = "Cannot operate on a closed database.";

            return SQLITE_ERROR;
        }

        private string processScalarQuery(string query, params object[] args)
        {
            if (_open)
            {
                _errormsg = ""; //bug fixed 01/29/2011.

                _sc.setContext(query, args);

                object result = null;

                PrepareStatement();

                if (_sc.rc == SQLITE_OK)
                {
                    BindParameters();

                    if (_sc.rc == SQLITE_OK)
                    {
                        if (sqlite3_column_count(_sc.stmtHandle) > 0)
                        {
                            if (sqlite3_step(_sc.stmtHandle) == SQLITE_ROW)
                            {
                                switch (sqlite3_column_type(_sc.stmtHandle, 0))
                                {
                                    case SQLITE_NULL:
                                        result = "";
                                        break;
                                    default:
                                        result = sqlite3_column_text(_sc.stmtHandle, 0);
                                        break;
                                }
                            }

                            else result = "";

                            FinalizeStatement();

                            if (result != null) return result.ToString();

                            else return "";
                        }
                    }
                }

                FinalizeStatement();

                return "";
            }

            _errormsg = "Cannot operate on a closed database.";

            return "";
        }

        private void processQuery(string query, params object[] args)
        {
            if (_open)
            {
                _errormsg = ""; //bug fixed 01/29/2011.

                _sc.setContext(query, args);

                _ds.Clear();

                while (!_sc.isComplete)
                {
                    PrepareStatement();

                    if (_sc.rc == SQLITE_OK)
                    {
                        BindParameters();

                        if (_sc.rc == SQLITE_OK) getRows();

                        else break;
                    }
                }

                FinalizeStatement();
            }

            else _errormsg = "Cannot operate on a closed database.";
        }

        private void getRows()
        {
            int columnCount = sqlite3_column_count(_sc.stmtHandle);

            DataTable dt = _ds.Tables.Add();

            double temp;

            for (int i = 0; i < columnCount; i++) dt.Columns.Add(sqlite3_column_name(_sc.stmtHandle, i));

            while (sqlite3_step(_sc.stmtHandle) == SQLITE_ROW)
            {
                object[] row = new object[columnCount];

                for (int i = 0; i < columnCount; i++)
                {
                    if (SQLITE_NULL == sqlite3_column_type(_sc.stmtHandle, i)) row[i] = _null;

                    else row[i] = sqlite3_column_text(_sc.stmtHandle, i);

                    if (double.TryParse((string) row[i], out temp)) row[i] = temp;
                }

                dt.Rows.Add(row);
            }

            FinalizeStatement();
        }

        private string testnull(string arg)
        {
            return (arg != null) ? arg : _null;
        }

        private string normalize(string arg)
        {
            string m = @"\""|\|";
            string s = arg.Replace("\"", "\"\"");

            return Regex.IsMatch(s, m) == false ? s : "\"" + s + "\"";
        }

        private string getJsonData()
        {
            return JsonConvert.SerializeObject(getJsonDataRecord());
        }

        private string getData()
        {
            if (_useJson) return getJsonData();

            List<string> list = new List<string>();

            foreach (DataTable dt in _ds.Tables)
            {
                if (dt == null || dt.Rows.Count == 0) continue;

                List<string> list2 = new List<string>(), result = new List<string>();

                string k, v;

                foreach (DataRow r in dt.Rows)
                {
                    list2.Clear();

                    foreach (DataColumn c in dt.Columns)
                    {
                        k = c.ColumnName;
                        v = testnull(r[k].ToString());

                        if (this._useMap) list2.Add(normalize(k) + this._keyValueSep + normalize(v));

                        else list2.Add(normalize(v));
                    }

                    result.Add(string.Join(_hashEntrySep, list2.ToArray()));
                }

                list.Add(string.Join(_resultSep, result.ToArray()));
            }

            return string.Join(ResultSep, list.ToArray());
        }

        private ArrayList getJsonDataRecord()
        {
            ArrayList record = new ArrayList();

            string k;
            object v;

            Hashtable h = null;

            foreach (DataTable dt in _ds.Tables)
            {
                foreach (DataRow r in dt.Rows)
                {
                    if (_useMap) h = new Hashtable();

                    foreach (DataColumn c in dt.Columns)
                    {
                        double nv;

                        k = c.ColumnName;
                        v = r[k];

                        if (!double.TryParse(v.ToString(), out nv)) v = testnull(v.ToString());

                        if (_useMap) h.Add(k, v);

                        else record.Add(v);
                    }

                    if (_useMap) record.Add(h);
                }
            }

            return record;
        }

        internal object[] getParams(string data, out string query)
        {
            query = null;

            ArrayList al = JsonConvert.DeserializeObject<ArrayList>(data);

            if (al.Count == 0) return null;

            query = (string) al[0];

            al.RemoveAt(0);

            return al.ToArray();
        }
    }
}