· 6 years ago · Oct 27, 2019, 08:02 PM
1#!/bin/zsh
2#
3# Create/update a SQLite3 database file with all files in a given path.
4#
5# Usage:
6#
7# filestosql SQLITE_DATABASE [PATH]
8#
9
10DB_FILE=${1}
11SEARCH_ROOT=${2-${PWD}}
12
13FORMAT_ITEMS=(
14 "%i" # Inode
15 "%l" # Number of hard links
16 "%a" # Last access date
17 "%c" # Last change date
18 "%m" # Last modification date
19 "%B" # Birth date date
20 "%z" # Size of file in bytes
21 "%T" # File type
22 "%p" # File type
23 "%N" # Name
24)
25
26# -----------------------------------------------------------------------
27#
28
29CSV_FILE=$(mktemp)
30
31FORMAT=${(j:%t:)FORMAT_ITEMS}
32
33echo Generating file list...
34
35find $SEARCH_ROOT -exec stat -f $FORMAT {} \; > $CSV_FILE
36
37sqlite3 -batch $DB_FILE << EOF
38.mode tabs
39
40.print Creating table...
41CREATE TABLE IF NOT EXISTS files (
42 inode INT,
43 hardlink_count INT,
44 last_access_ts INT,
45 last_change_ts INT,
46 last_modification_ts INT,
47 birth_ts INT,
48 size INT,
49 type STRING,
50 permissions STRING,
51 name STRING
52);
53
54.print Emptying table...
55DELETE FROM files;
56
57.print Importing file...
58.import $CSV_FILE files
59.quit
60EOF