php获取sqlite数据表和结构信息

转自:https://blog.lqsos.com/archives/55.html

#!/usr/bin/env php
<?php

if(PHP_VERSION_ID < 70100){
    echo "The version is too low";exit;
}

# data file path
$data_file_path = __DIR__.'/../data/blog.sqlite';

$db = new Db($data_file_path);
$tableName = $db->getTablesInfo();

foreach ($tableName as $row){
    print_r($db->getTableSchema($row['name']));
}


class Db extends SQLite3
{
    private $table_info = [];

    public function __construct( string $filename)
    {
        if(false === is_file($filename)){
            echo "The file cannot be opened";exit;
        }
        parent::__construct($filename);
        $result = $this->query('select * from sqlite_master WHERE type = "table"');
        while ( $row = $result->fetchArray(SQLITE3_ASSOC) )
        {
            isset($row['name']) && $this->table_info[] = $row;
        }
    }

    public function getTablesInfo() : array
    {
        return $this->table_info;
    }

    public function getTableSchema( string $table_name) : array
    {
        $table_info = ['table' => $table_name];
        $result = $this->query("PRAGMA table_info([{$table_name}])");
        while ( $row = $result->fetchArray(SQLITE3_ASSOC) )
        {
            isset($row['name']) && $table_info[$row['name']] = $row;
        }
        return $table_info;
    }
}