Web SQL get column list from table

Question

I try get column list from Web sql (Chrome local database). Оne of the decisions - get info from sqlite_master

SELECT name, sql FROM sqlite_master WHERE type="table" AND name = "'+name+'";

For example i get this result

CREATE TABLE table_name ( id INTEGER PRIMARY KEY AUTOINCREMENT, 
number INTEGER unique, description TEXT, password TEXT, url TEXT )

So help me write regex for get column names, or, maybe, show me another simple way get column names.

PS. i dont wont to do select * from table... for getting columns names. I think this is bad solution..

select name from sys.columns where object_id = object_id(N'table_name') — Vishal Suthar, Mar 05 '13 at 10:37
To get the table create syntax (useful for reviewing the columns, but not that usable as part of a codebase) you can do ```SELECT sql FROM sqlite_master WHERE tbl_name = 'your_table_name' AND type = 'table'``` — Harry B, Jan 18 '16 at 20:41

CL. · Answer 1 · 2013-03-05T12:57:41.367

4

To get the columns of a table, execute PRAGMA table_info(table_name):

PRAGMA table_info()

Return a single row for each column of the named table. The columns of the returned data set are:

cid: Column id (numbered from left to right, starting at 0)

name: Column name

type: Column declaration type.

notnull: True if 'NOT NULL' is part of column declaration

dflt_value: The default value for the column, if any.

Unfortunately, Chrome blocks all PRAGMAs, so this doesn't work in WebSQL.

In WebSQL, you can access only tables that were created by your app, so you should just remember which columns your tables have.

Alternatively, you can just try to read from the table:

SELECT * FROM table_name LIMIT 1

With the LIMIT clause, this will be very efficient because you read only some random record. (Except if you have some very big blob in that record.)

edited Mar 05 '13 at 12:57

answered Mar 05 '13 at 12:29

CL.

173,858
17
217
259

5

PRAGMA table_info(my_table) could not prepare statement (23 not authorized) – MikeLP Mar 05 '13 at 12:48
5

about SELECT * FROM table_name LIMIT 1; problem begin when table is empty. – MikeLP Mar 05 '13 at 15:07
In WebSQL, you can access only tables that were created by your app, so you should just remember which columns your tables have. ---------------------------------- Yes I understand. I write own small ORM library. I make, that table automaticly created by model and drop or add column, if I change property of model. Now i refactor my code and seeking an more elegant solution – MikeLP Mar 05 '13 at 15:13
`you should just remember which columns your tables have.` UNACCEPTABLE. My web app works on my entire sub-domain. This completely breaks my "generic SQL table" app for browsing tables, which *requires* being able to introspect tables another app in the same domain created. +1 though for answering my question of WTF Chrome was giving me the "not authorized" garbage. – Michael Apr 25 '16 at 00:21

konsumer · Answer 2 · 2014-11-25T01:59:33.793

In chrome, this worked for me with html5sql. I also made a codepen that uses pure-HTML5 with a cool Promise-based query function, here.

function getDB(cb){
    html5sql.process("SELECT * FROM sqlite_master WHERE name NOT LIKE 'sqlite\\_%' escape '\\' AND name NOT LIKE '\\_%' escape '\\'", function(txTables, rsTables, tables){
        if (!tables.length) return cb(null, []);
        tables.forEach(function(table){
            var s = table.sql.split(',');
            s[0] = s[0].replace(new RegExp('create\\s+table\\s+' + table.name + '\\s*\\(', 'i'),'');
            table.fields = s.map(function(i){
                return i.trim().split(/\s/).shift();
            })
            .filter(function(i){
                return (i.indexOf(')') === -1)
            })
        });
        cb(null, tables)
    }, cb);
}

This will hit your (error, tables) callback like this:

[{
    "type": "table",
    "name": "Users",
    "tbl_name": "Users",
    "rootpage": 6,
    "sql": "CREATE TABLE Users(\n  id INTEGER PRIMARY KEY AUTOINCREMENT,\n  firstName VARCHAR(255),\n  lastName VARCHAR(255),\n  email VARCHAR(255),\n  created TIMESTAMP DEFAULT (DATETIME('now','localtime'))\n)",
    "fields": [
        "id",
        "firstName",
        "lastName",
        "email",
        "created"
    ]
}]

Note the fields section. This works, even if there are not records. The regex/string parsing could probably use some improvement, and you could probably grab type-info with it too, but this seemed to work with all my usecases. An alternate method once you know the fieldnames, in SQL:

SELECT TYPEOF(id) as id, TYPEOF(firstName) AS firstName , TYPEOF(lastName) AS lastName, TYPEOF(email) AS email, TYPEOF(created) AS created FROM Users;

Web SQL get column list from table

2 Answers2