获取SQLite中列的类型 [英] Getting the type of a column in SQLite

问题描述

我的队友国产数据库为我的Andr​​oid应用程序。例如，其中一个表是这样创建的：

My mate made the database for my Android app. For example, one of the tables is created like this:

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
)

从表中我需要知道其中哪些是 INTEGER NOT NULL 和领域是 INTEGER NOT NULL DEFAULT 1 ，因为第一种情况下我必须创建dinamically一个的EditText 和第二种情况下我必须创建一个复选框。你知道吗？

From the fields of a table I need to know which of them is INTEGER NOT NULL and which is INTEGER NOT NULL DEFAULT 1, because for the first case I must create dinamically an EditText and for the second case I must create a CheckBox. Any idea?

推荐答案

调用：

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

和简单的发现，在光标一行 NOTNULL = 1 和 dflt_value = 1

---

编辑：

and simply find the row in the cursor with notnull=1 and dflt_value=1

---

要列出定义为 INTEGER的所有列NOT NULL DEFAULT 1 这会工作（帮助是实例 SQLiteOpenHelper ）：

To list all columns defined as INTEGER NOT NULL DEFAULT 1 this would work (helper is your instance of 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();
}

这篇关于获取SQLite中列的类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！