System.Data.SQLite

    /// <param name="strTable">The table to retrieve index information for, can be null</param>
    /// <returns>DataTable</returns>
    private DataTable Schema_Indexes(string strCatalog, string strTable, string strIndex)
    {
      DataTable tbl = new DataTable("Indexes");
      DataRow row;
      DataTable tblSchema;
      string primaryKey;

      tbl.Locale = CultureInfo.InvariantCulture;
      tbl.Columns.Add("TABLE_CATALOG", typeof(string));
      tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
      tbl.Columns.Add("TABLE_NAME", typeof(string));
      tbl.Columns.Add("INDEX_CATALOG", typeof(string));
      tbl.Columns.Add("INDEX_SCHEMA", typeof(string));

      using (SQLiteCommand cmdTables = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'table'", strCatalog), this))
      {
        using (SQLiteDataReader rdTables = cmdTables.ExecuteReader())
        {
          while (rdTables.Read())
          {
            primaryKey = "";
            if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0)
            {
              // First, look for any rowid indexes -- which sqlite defines are INTEGER PRIMARY KEY columns.
              // Such indexes are not listed in the indexes list but count as indexes just the same.
              using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this))
              {
                using (SQLiteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                  tblSchema = rdTable.GetSchemaTable(false);
                  foreach (DataRow schemaRow in tblSchema.Rows)
                  {
                    if (schemaRow.IsNull("DeclaredType") == false)
                    {
                      if ((bool)schemaRow[SchemaTableColumn.IsKey] == true)
                      {
                        row = tbl.NewRow();

                        row["TABLE_CATALOG"] = strCatalog;
                        row["TABLE_NAME"] = rdTables.GetString(2);
                        row["INDEX_CATALOG"] = strCatalog;

                        row["PRIMARY_KEY"] = true;

                        // If the primary key is of type INTEGER, then its a rowid and we need to make a fake index entry for it.
                        if (String.Compare((string)schemaRow["DeclaredType"], "INTEGER", true, CultureInfo.InvariantCulture) == 0)
                        {
                          row["INDEX_NAME"] = String.Format(CultureInfo.InvariantCulture, "sqlite_master_PK_{0}", rdTables.GetString(2));
                          row["UNIQUE"] = true;
                          tbl.Rows.Add(row);
                          break;
                        }
                        // Otherwise, record this primary key's index name so we can match it up later and mark it as a primary key
                        else primaryKey = String.Format(CultureInfo.InvariantCulture, "sqlite_autoindex_{0}_", rdTables.GetString(2));
                      }
                    }
                  }
                }
              }

              // Now fetch all the rest of the indexes.
              using (SQLiteCommand cmd = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_list([{1}])", strCatalog, rdTables.GetString(2)), this))
              {
                using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader())
                {
                  while (rd.Read())
                  {
                    if (String.Compare(rd.GetString(1), strIndex, true, CultureInfo.InvariantCulture) == 0
                    || strIndex == null)
                    {
                      row = tbl.NewRow();

                      row["TABLE_CATALOG"] = strCatalog;
                      row["TABLE_NAME"] = rdTables.GetString(2);
                      row["INDEX_CATALOG"] = strCatalog;
                      row["INDEX_NAME"] = rd.GetString(1);
                      row["UNIQUE"] = rd.GetBoolean(2);
                      
                      if (rd.GetString(1).StartsWith(primaryKey, true, CultureInfo.InvariantCulture) == true)
                        row["PRIMARY_KEY"] = true;

                      tbl.Rows.Add(row);
                    }
                  }
                }
              }
            }

      {
        using (SQLiteDataReader rdTables = cmdTables.ExecuteReader())
        {
          while (rdTables.Read())
          {
            if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0)
            {
              using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this))
              {
                using (SQLiteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                  tblSchema = rdTable.GetSchemaTable(false);
                  foreach (DataRow schemaRow in tblSchema.Rows)
                  {
                    if (schemaRow.IsNull("DeclaredType") == false)
                    {
                      if (String.Compare((string)schemaRow["DeclaredType"], "INTEGER", true, CultureInfo.InvariantCulture) == 0
                        && Convert.ToBoolean(schemaRow[SchemaTableColumn.IsKey], CultureInfo.InvariantCulture) == true)
                      {
                        row = tbl.NewRow();
                        row["CONSTRAINT_CATALOG"] = strCatalog;
                        row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "sqlite_master_PK_{0}", rdTables.GetString(2));
                        row["TABLE_CATALOG"] = strCatalog;
                        row["TABLE_NAME"] = rdTables.GetString(2);
                        row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.BaseColumnName];
                        row["INDEX_NAME"] = row["CONSTRAINT_NAME"];
                        row["ORDINAL_POSITION"] = schemaRow[SchemaTableColumn.ColumnOrdinal];
                        if ((String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, row["INDEX_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0)
                            && (String.IsNullOrEmpty(strColumn) || String.Compare(strColumn, row["COLUMN_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0))
                        {
                          tbl.Rows.Add(row);
                          break;
                        }
                      }
                    }
                  }
                }
              }

              using (SQLiteCommand cmdIndexes = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'index' AND [tbl_name] LIKE '{1}'", strCatalog, rdTables.GetString(2)), this))
              {
                using (SQLiteDataReader rdIndexes = cmdIndexes.ExecuteReader())
                {
                  while (rdIndexes.Read())
                  {
                    if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, rdIndexes.GetString(1), true, CultureInfo.InvariantCulture) == 0)

      {
        using (SQLiteDataReader rdTables = cmdTables.ExecuteReader())
        {
          while (rdTables.Read())
          {
            if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTables.GetString(2), true, CultureInfo.InvariantCulture) == 0)
            {
              using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this))
              {
                using (SQLiteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                  using (SQLiteCommand cmdKey = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].foreign_key_list([{1}])", strCatalog, rdTables.GetString(2)), this))
                  {
                    using (SQLiteDataReader rdKey = cmdKey.ExecuteReader())
                    {

      tbl.Columns.Add(SchemaTableOptionalColumn.IsAutoIncrement, typeof(Boolean));
      tbl.Columns.Add(SchemaTableOptionalColumn.IsRowVersion, typeof(Boolean));
      tbl.Columns.Add(SchemaTableOptionalColumn.IsHidden, typeof(Boolean));
      tbl.Columns.Add(SchemaTableColumn.IsLong, typeof(Boolean));
      tbl.Columns.Add(SchemaTableOptionalColumn.IsReadOnly, typeof(Boolean));
      tbl.Columns.Add(SchemaTableOptionalColumn.ProviderSpecificDataType, typeof(Type));
      tbl.Columns.Add(SchemaTableOptionalColumn.DefaultValue, typeof(object));
      tbl.Columns.Add("DeclaredType", typeof(string));

      tbl.BeginLoadData();

      SQLiteConnection cnn = (SQLiteConnection)_command.Connection;

      // Create a new command based on the original.  The only difference being that this new command returns
      // fully-qualified Database.Table.Column column names because of the above pragma

                      _command.Connection._sql.ColumnMetaData(
                (string)row[SchemaTableOptionalColumn.BaseCatalogName],
                (string)row[SchemaTableColumn.BaseTableName],
                strColumn,
                out dataType, out collSeq, out bNotNull, out bPrimaryKey, out bAutoIncrement);

                      if (bNotNull) row[SchemaTableColumn.AllowDBNull] = false;

                      row[SchemaTableColumn.IsKey] = bPrimaryKey;
                      row[SchemaTableOptionalColumn.IsAutoIncrement] = bAutoIncrement;

                      if (String.IsNullOrEmpty(dataType) == false)
                        row["DeclaredType"] = dataType;

                      if (rdTable.IsDBNull(4) == false)
                        row[SchemaTableOptionalColumn.DefaultValue] = rdTable[4];
                      break;
                    }
                  }
                }
              }