时间戳

首页 > 解决方案 > SQLITE3：将多个 sqlite3 文件合并到一个文件中

bash - SQLITE3：将多个 sqlite3 文件合并到一个文件中

问题描述

我有许多不同的 .sqlite 数据库文件（大约 1000 个，甚至更多），都具有相同的架构，我需要将它们全部合并到两个 sqlite 文件中，如下所述。

文件名是"con_cookies_{domain.TLD}.sqlite"和"sin_cookies_{domain.TLD}.sqlite"所以我想要"con_cookies.sqlite"和"sin_cookies.sqlite"。

最好的方法是什么？我对 sqlite 了解不多，因此不胜感激 bash 脚本。

编辑：

我与数据库结构共享屏幕截图。如果保留数据的顺序，则主键并不重要。另一方面，每个文件大约有 40-50 行。

来自源文件的数据库结构 Some SELECT {...} FROM rows example from source

更新：文本格式的数据库模式

id INTEGER PRIMARY KEY, 
baseDomain TEXT, 
originAttributes TEXT NOT NULL DEFAULT '', 
name TEXT, 
value TEXT, 
host TEXT, 
path TEXT, 
expiry INTEGER, 
lastAccessed INTEGER, 
creationTime INTEGER, 
isSecure INTEGER, 
isHttpOnly INTEGER, 
inBrowserElement INTEGER DEFAULT 0, 
sameSite INTEGER DEFAULT 0, 
Domain TEXT, 
DurationDays INTEGER, 
WebCategory TEXT, 
CookieCategory TEXT, 
Action TEXT, 
TLD TEXT, 
CMP_VENDOR TEXT, 
CMP_IAB_V1 TEXT, 
CMP_IAB_V2 TEXT, 
Country TEXT, 
CookiesByDefault, 
CONSTRAINT moz_uniqueid UNIQUE (name, host, path, originAttributes));
CREATE INDEX moz_basedomain ON moz_cookies (baseDomain, originAttributes);

此外，如果更容易将所有数据库大量导出到 CSV 也可以，而不是加入所有数据库。

标签： bashsqlite

解决方案

没有办法保留 id。如果有任何事情取决于 id，这将不起作用。您必须首先添加一个全局唯一 id，更改为使用它，然后迁移。

最简单的做法是创建一个具有自动递增主键的新数据库，然后将每个数据库导入其中，跳过 ID。在 SQLite 中，integer primary key是rowid. 这足以使其自动递增。通常，您可以通过转储除 ID 列之外的表来执行此操作，但 SQLite 内置的转储和加载设施是乏力的。

相反，我们可以编写一个连接到每个数据库的应用程序，跳过 ID 加载每一行，并将其插入到主数据库中。将分配一个新的 ID。这不是最快的，但对于一次性来说应该没问题。

-- Recreate the database in your new table.
create table moz_cookies (
  id integer primary key, -- this will autoincrement
  ...and all the rest...
)

然后在程序中循环遍历每个文件，选择每一行，并将除 ID 之外的所有内容插入到新的主表中。

这是一个使用sqlite3-ruby的 Ruby 示例程序。

require "sqlite3"
require 'pry'

# The file you're merging to.
MASTER_FILE = 'con_cookies.sqlite'.freeze

# The pattern to match the files you're pulling from.
DIR_GLOB = 'con_cookies_*.sqlite'.freeze

# All the columns except ID
COLS = ['baseDomain', 'originAttributes'].freeze

# A bind string for all the columns
BINDS = COLS.map { "?" }.join(",").freeze

# The table you're transferring.
TABLE = 'moz_cookies'.freeze

# Connect to the master database.
master = SQLite3::Database.new(MASTER_FILE)

# Prepare the insert statement.
# insert into moz_cookies ('baseDomain', 'originAttributes') values (?,?)
master.prepare("insert into #{TABLE} (#{COLS.join(",")}) values (#{BINDS})") do |insert_stmt|
  # Find each database file
  Dir.glob(DIR_GLOB) do |file|
    puts "Connecting to #{file}"

    # Connect to it
    db = SQLite3::Database.new(file)

    # select baseDomain, originAttributes from moz_cookies
    db.prepare("select #{COLS.join(",")} from #{TABLE}") do |select_stmt|
      # Fetch each row...
      rows = select_stmt.execute
      rows.each do |row|
        # Insert into the master table. It will fill in a new ID.
        insert_stmt.execute(row)
      end
    end
  end
end

请注意，任何需要由 TLD 选择的查询的性能都可能会受到影响。如果您经常这样做，您可能需要制作包含 TLD的复合索引。或者您可以迁移到具有表分区功能的更强大的数据库。

推荐阅读