Web SQL从表中获取列列表

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

我尝试从Web sql(Chrome本地数据库)获取列列表。 Оne的决定 - 从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.

所以帮我写regex获取列名,或者,或许,给我看另一个简单的方法获取列名。

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

PS。我不会选择*从表...获取列名称。我认为这是不好的解决方案..

2 个解决方案

#1

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

要获取表的列,请执行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)

cid:列ID(从左到右编号,从0开始)

name: Column name

name:列名

type: Column declaration type.

type:列声明类型。

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

notnull:如果'NOT NULL'是列声明的一部分,则为真

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

dflt_value:列的默认值(如果有)。

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

不幸的是,Chrome会阻止所有PRAGMA,因此这在WebSQL中不起作用。

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

在WebSQL中,您只能访问应用程序创建的表,因此您应该只记住表中的列。

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

使用LIMIT子句,这将非常有效,因为您只读取一些随机记录。 (除非你在那张唱片中有一些非常大的斑点。)

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

在chrome中,这对我使用html5sql。我还制作了一个使用纯HTML5的编解码器,这里有一个很酷的基于Promise的查询功能。

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:

请注意字段部分。即使没有记录,这也有效。正则表达式/字符串解析可能会使用一些改进,你也可以用它来获取type-info,但这似乎适用于我的所有用例。在SQL中知道字段名后的另一种方法:

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

#1