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..

Kolomna

select name from sys.columns where object_id = object_id(N'table_name') – Stagnant

are you sure this query for the web sql? – Kolomna

No, I'm not sure, as this is for sql server.. – Stagnant

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' – Downandout

Answer 1

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.)

Scratchboard

PRAGMA table_info(my_table) could not prepare statement (23 not authorized) – Kolomna

about SELECT * FROM table_name LIMIT 1; problem begin when table is empty. – Kolomna

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 – Kolomna

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. – Annamarieannamese

Answer 2

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;

Cess