SQLite If Column Exists

I was wondering if there is a nice IF NOT EXISTS for checking开发者_如何学编程 columns and indexes in SQLite, or do I need to bring back the entire database schema and validate against that?

There is a system catalog table called sqlite_master that you can use to check index (or other) names:

SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;

You can use a pragma to get the indexed columns:

PRAGMA index_info(index-name);

And this one to get the column names for a table:

PRAGMA table_info(table-name);

Yes the following syntax is supported in sqlite: CREATE INDEX IF NOT EXISTS ...

See here

To check existence of a column you could simply try to do something like SELECT col from TABLE. If it does not return an error your table contains col.

In SQLite, If you want to check whether your column/field exist in your table or not....try this answer work for me... https://stackoverflow.com/a/28146506/3126569

Cursor res = db.rawQuery("PRAGMA table_info("+tableName+")",null);
int value = res.getColumnIndex(fieldName);

This is how I do it,

INSERT  INTO AUDITEVENT (CODE, DESCRIPTION, AUDITEVENTPK)  
SELECT 'value1', 'value2', 'value3' 
WHERE NOT EXISTS (SELECT 1 FROM AUDITEVENT WHERE CODE = 'value1')

In my IDConneciton, I have a method called ProcessCommandText to update the query from SQL Server syntax to SQLite only.

This is the implementation of that method

public string ProcessSqlText(string text)
{
    var result = text.Replace("SET ANSI_NULLS ON", "").
            Replace("SET ANSI_NULLS ON", "").
            Replace(" ASC)", ")").
            Replace("TOP 100", "TOP(100)").
            Replace("TOP 1", "TOP(1)").
            Replace(" VARCHAR", " NVARCHAR").
            Replace(" CHAR", " NCHAR").
            Replace(" TEXT", " NTEXT").
            Replace("WITH ( IGNORE_DUP_KEY = OFF)", "").
            Replace("SET QUOTED_IDENTIFIER ON", "").
            Replace("SET ANSI_PADDING ON", "").
            Replace("SET ANSI_PADDING OFF", "").
            Replace("SET ANSI_WARNINGS ON", "").Trim(' ').
            Replace("WITH NOCHECK", "").
            Replace("(nolock)", "").
            Replace("CREATE CLUSTERED INDEX", "CREATE NONCLUSTERED INDEX").
            Replace("CREATE UNIQUE CLUSTERED INDEX", "CREATE UNIQUE NONCLUSTERED INDEX").
            Replace("[dbo].", "").
            ToUpper().
            Replace("NEWID()", "'" + Guid.NewGuid().ToString() + "'"). // NEWID() is not supported
            Replace("GO", ""); // GO is not supported


    if (result.Contains("TOP(100)"))
    {
        result = result.Replace("TOP(100)", "");
        result += " LIMIT 100";
    }
    if (result.Contains("TOP(1)"))
    {
        result = result.Replace("TOP(1)", "");
        result += " LIMIT 1";
    }
    if (result.Contains("DATEPART"))
    {
        result = result.Replace("DATEPART", "strftime");
        result = result.Replace("minute", "'%M'");
        result = result.Replace("day", "'%d'");
        result = result.Replace("month", "'%m'");
        result = result.Replace("year", "'%Y'");
    }

    result = TransformIfNotExistsQueriesToSqlLite(result);
    return result;
}

private string TransformIfNotExistsQueriesToSqlLite(string query)
{
    var ifNotExistsRegEx = @"(IF NOT EXISTS\s+)\(+.+\)+\r+\n+INSERT+";
    var m = Regex.Match(query, @"(IF NOT EXISTS\s+)\(+.+\)+\r+\n+INSERT+", RegexOptions.IgnoreCase);

    if (m.Groups.Count > 0 && m.Groups[0].Value.Contains("IF NOT EXISTS"))
    {
        var conditionalStatement = m.Groups[0].Value;
        var newQuery = query.Replace(conditionalStatement, " INSERT ");
        conditionalStatement = conditionalStatement.ToUpper().Replace("IF", "WHERE").Replace("INSERT", "");

        newQuery = Regex.Replace(newQuery.ToUpper(), @"VALUES\s+\(+", " SELECT ");
        var lastIndexOfClosingBracket = newQuery.LastIndexOf(')');
        newQuery = newQuery.Substring(0, lastIndexOfClosingBracket);
        return newQuery + " " + conditionalStatement;   
    }
    return query;
}

Why dont you just catch the exception? For example:

try{
    db.execSQL("ALTER TABLE ACCOUNT_ENTRY ADD COLUMN ID_CONCEPT NUMBER(10)");
}catch(Exception e){
    System.out.println(e);

}