database SQLite 如果列存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2520945/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite If Column Exists
提问by Nathan
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?
我想知道是否有一个很好的 IF NOT EXISTS 来检查 SQLite 中的列和索引，或者我是否需要带回整个数据库模式并对此进行验证？
采纳答案by Brian R. Bondy
Yes the following syntax is supported in sqlite: CREATE INDEX IF NOT EXISTS ...
是的，sqlite 支持以下语法： CREATE INDEX IF NOT EXISTS ...
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.
要检查列是否存在，您可以简单地尝试执行类似SELECT col from TABLE. 如果它没有返回错误，您的表包含col.
回答by Tim
There is a system catalog table called sqlite_masterthat you can use to check index (or other) names:
有一个名为sqlite_master的系统目录表，您可以使用它来检查索引（或其他）名称：
SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;
You can use a pragmato 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);
回答by Pankaj Jangid
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
在 SQLite 中，如果你想检查你的表中是否存在你的列/字段......试试这个答案对我有用...... https://stackoverflow.com/a/28146506/3126569
Cursor res = db.rawQuery("PRAGMA table_info("+tableName+")",null);
int value = res.getColumnIndex(fieldName);
回答by Has AlTaiar
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.
在我的 IDConneciton 中，我有一个名为 ProcessCommandText 的方法可以将查询从 SQL Server 语法更新为仅 SQLite。
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;
}
回答by Lo Juego
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);
}
