Getting the type of a column in SQLite

user_784766 0
Viewed 33322

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
)

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?

sqlite android
3 Answers

Here's a query that I use to get table / column / data type / primary key info for all tables and views:

SELECT m.name AS table_name, UPPER(m.type) AS table_type,
  p.name AS column_name, p.type AS data_type,
  CASE p.pk WHEN 1 THEN 'PRIMARY KEY' END AS const
FROM sqlite_master AS m
  INNER JOIN pragma_table_info(m.name) AS p
WHERE m.name NOT IN ('sqlite_sequence')
ORDER BY m.name, p.cid
user_50687580
Related