CODEWITHSUNDEEP
regexsqlite
rsk82
rsk82

Reputation: 29387

how to extract column parameters from sqlite create string?

in sqlite it is possible to have string by which the table was created:

select sql from sqlite_master where type='table' and tbl_name='MyTable'

this could give:

CREATE TABLE "MyTable" (`id` PRIMARY KEY NOT NULL, [col1] NOT NULL,
 "another_col" UNIQUE, '`and`,''another'',"one"' INTEGER, and_so_on);

Now I need to extract with this string any additional parameters that given column name has been set with.

But this is very difficult since the column name could be enclosed with special characters, or put plain, column name may have some special characters that are used as encapsulation etc.

I don't know how to approach it. The result should be having a column name the function should return anything that is after this name and before , so giving it id it should return PRIMARY KEY NOT NULL.

Upvotes: 0

Views: 247

Answers (1)

user610650
user610650

Reputation:

Use the pragma table_info:

http://www.sqlite.org/pragma.html#pragma_table_info

sqlite> pragma table_info(MyTable);
cid|name|type|notnull|dflt_value|pk
0|id||1||1
1|col1||1||0
2|another_col||0||0
3|`and`,'another',"one"|INTEGER|0||0
4|and_so_on||0||0

Upvotes: 1

Related Questions