Android 在 SQLite 中获取列的类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11753871/
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
Getting the type of a column in SQLite
提问by Gabrielle
My mate made the database for my Android app. For example, one of the tables is created like this:
我的伙伴为我的 Android 应用制作了数据库。例如，其中一个表是这样创建的：
CREATE TABLE table1(
id_fields_starring INTEGER PRIMARY KEY AUTOINCREMENT
, fields_descriptor_id INTEGER NOT NULL
, starring_id INTEGER NOT NULL
, form_mandatory INTEGER NOT NULL DEFAULT 1
, form_visible INTEGER NOT NULL DEFAULT 1
, FOREIGN KEY(fields_descriptor_id) REFERENCES pr_fields_descriptor(id_fields_descriptor) ON DELETE CASCADE ON UPDATE CASCADE
, FOREIGN KEY(starring_id) REFERENCES starring(id_starring) ON DELETE CASCADE ON UPDATE CASCADE
)
From the fields of a table I need to know which of them is INTEGER NOT NULLand which is INTEGER NOT NULL DEFAULT 1, because for the first case I must create dinamically an EditTextand for the second case I must create a CheckBox. Any idea?
从表的字段中，我需要知道它们中的INTEGER NOT NULL哪些是INTEGER NOT NULL DEFAULT 1，哪些是，因为对于第一种情况，我必须以动态方式创建 an EditText，对于第二种情况，我必须创建一个CheckBox. 任何的想法？
回答by Jens
Calling:
调用：
PRAGMA table_info(table1);
will dump the table information, e.g.
将转储表信息，例如
cid|name |type |notnull |dflt_value |pk
0 |id_fields_starring |INTEGER |0 | |1
1 |fields_descriptor_id |INTEGER |1 | |0
2 |starring_id |INTEGER |1 | |0
3 |form_mandatory |INTEGER |1 |1 |0
4 |form_visible |INTEGER |1 |1 |0
and simply find the row in the cursor with notnull=1and dflt_value=1
并简单地使用notnull=1和找到光标中的行dflt_value=1
Edit:编辑：
To list all columns defined as INTEGER NOT NULL DEFAULT 1this would work (helperis your instance of SQLiteOpenHelper):
列出所有定义为INTEGER NOT NULL DEFAULT 1这将起作用的列（helper是您的实例SQLiteOpenHelper）：
SQLiteDatabase db = helper.getWritableDatabase();
Cursor cursor = db.rawQuery("PRAGMA table_info(table1)", null);
try {
int nameIdx = cursor.getColumnIndexOrThrow("name");
int typeIdx = cursor.getColumnIndexOrThrow("type");
int notNullIdx = cursor.getColumnIndexOrThrow("notnull");
int dfltValueIdx = cursor.getColumnIndexOrThrow("dflt_value");
ArrayList<String> integerDefault1NotNull = new ArrayList<String>();
while (cursor.moveToNext()) {
String type = cursor.getString(typeIdx);
if ("INTEGER".equals(type)) {
// Integer column
if (cursor.getInt(notNullIdx) == 1) {
// NOT NULL
String defaultValue = cursor.getString(dfltValueIdx);
if ("1".equals(defaultValue)) {
integerDefault1NotNull.add(cursor.getString(nameIdx));
}
}
}
}
System.out.println("integerDefault1NotNull now contains a list of all columns " +
" defined as INTEGER NOT NULL DEFAULT 1, " + integerDefault1NotNull);
} finally {
cursor.close();
}
回答by The Senator
For those of you looking to do this programmatically in Xamarin perhaps (like me) you can execute the PRAGMA query against a SQLiteConnection as follows:
对于那些希望在 Xamarin 中以编程方式执行此操作的人（像我一样），您可以对 SQLiteConnection 执行 PRAGMA 查询，如下所示：
connection.Query<SqliteColumninfo>($"PRAGMA table_info({tableName})");
I couldn't find an object defined in the PCL Sqlite package for collecting the result so I created a class to collect the aforementioned components:
我找不到在 PCL Sqlite 包中定义的用于收集结果的对象，因此我创建了一个类来收集上述组件：
public class SqliteColumninfo
{
public string Cid { get; set; }
public string Name { get; set; }
public string Type { get; set; }
public int NotNull { get; set; }
public int Dflt_Value { get; set; }
public int PK { get; set; }
}
