CODEWITHSUNDEEP
databasesqlite
Nathan
Nathan

Reputation: 5233

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?

Upvotes: 24

Views: 42019

Answers (5)

Pankaj Jangid
Pankaj Jangid

Reputation: 283

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);

Upvotes: 2

Has AlTaiar
Has AlTaiar

Reputation: 4152

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;
}

Upvotes: 0

Lo Juego
Lo Juego

Reputation: 1325

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);

}

Upvotes: -3

Tim
Tim

Reputation: 1023

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);

Upvotes: 38

Brian R. Bondy
Brian R. Bondy

Reputation: 347216

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.

Upvotes: 10

Related Questions