javascript - Web SQL get column list from table - Stack Overflow

admin管理员组

文章数量:1289537

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

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

• javascript
• regex
• sqlite
• google-chrome
• web-sql

Share Improve this question Follow edited Mar 5, 2013 at 10:31 Vishal Suthar 17.2k33 gold badges6262 silver badges108108 bronze badges asked Mar 5, 2013 at 10:27 MikeLPMikeLP 19811 gold badge11 silver badge99 bronze badges 4

• select name from sys.columns where object_id = object_id(N'table_name') – Vishal Suthar Commented Mar 5, 2013 at 10:37
• 1 are you sure this query for the web sql? – MikeLP Commented Mar 5, 2013 at 11:08
• No, I'm not sure, as this is for sql server.. – Vishal Suthar Commented Mar 5, 2013 at 11:16
• 1 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 Commented Jan 18, 2016 at 20:41

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 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.)

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;

本文标签： javascriptWeb SQL get column list from tableStack Overflow