d6S8yT7K

1#!/bin/bash
2
3#Using | as separator. ; created problems when file names contained ;
4export S='|'
5
6echo
7echo Gathering information about the filesystem.
8echo
9
10#Run filinfo.sh from root dir
11/home/tn/da-oda2001/oblig/filinfo.sh / > fil_info.txt
12
13echo Done.
14echo
15echo Creating database and importing data.
16echo
17
18
19sqlite3 personbase.db <<EOF
20
21.echo on
22
23DROP TABLE IF EXISTS Users;
24
25CREATE TABLE Users (
26	userName	VARCHAR(20)	NOT NULL,
27	password	VARCHAR(20),
28	UID		SMALLINT	NOT NULL,
29	GID             SMALLINT,
30	name            VARCHAR(250),
31	homeDir         VARCHAR(100),
32	cmdIntrptr      VARCHAR(100),
33
34	PRIMARY KEY(userName),
35	UNIQUE(UID)
36);
37
38.mode csv
39.separator ':'
40.import /etc/passwd Users
41
42
43DROP TABLE IF EXISTS Temp;
44
45CREATE TABLE Temp (
46	accessRights	SMALLINT	NOT NULL,
47	deviceNum	SMALLINT	NOT NULL,
48	fileType	VARCHAR(20)	NOT NULL,
49	GID		SMALLINT	NOT NULL,
50	groupName	VARCHAR(100)	NOT NULL,
51	numHardLinks	SMALLINT	NOT NULL,
52	iNodeNum	INT		NOT NULL,
53	mountPoint	VARCHAR(20)	NOT NULL,
54	fileName	VARCHAR(100)	NOT NULL,
55	transfSize	SMALLINT	NOT NULL,
56	fileSize	SMALLINT	NOT NULL,
57	UID		SMALLINT	NOT NULL,
58	userName	VARCHAR(20)	NOT NULL
59);
60
61
62.mode csv
63.separator '|' #her kommer usage: .separator NEWLINE TINGEN
64.import ./fil_info.txt Temp
65
66DROP TABLE IF EXISTS Files;
67DROP TABLE IF EXISTS Groups;
68DROP TABLE IF EXISTS Inodes;
69DROP TABLE IF EXISTS Hardware;
70
71
72CREATE TABLE Files (
73        fileName        VARCHAR(100)    NOT NULL,
74        iNodeNum        INT             NOT NULL,
75        deviceNum       SMALLINT        NOT NULL,
76
77        PRIMARY KEY     (fileName)
78
79        CONSTRAINT fk_Inodes
80                FOREIGN KEY             (iNodeNum, deviceNum)
81                REFERENCES      Inodes  (iNodeNum, deviceNum)
82);
83
84CREATE TABLE Groups (
85        groupName       VARCHAR(100)    NOT NULL,
86        GID             SMALLINT        NOT NULL,
87
88        PRIMARY KEY     (groupName)
89);
90
91CREATE TABLE Inodes (
92        iNodeNum        INT             NOT NULL,
93        deviceNum       SMALLINT        NOT NULL,
94        fileType        VARCHAR(20)     NOT NULL,
95        fileSize        SMALLINT        NOT NULL,
96        numHardLinks    SMALLINT        NOT NULL,
97        accessRights    SMALLINT        NOT NULL,
98        userName	VARCHAR(20)     NOT NULL,
99        groupName       VARCHAR(100)    NOT NULL,
100
101        PRIMARY KEY     (iNodeNum, deviceNum)
102
103        CONSTRAINT fk_Bruker
104                FOREIGN KEY             (userName)
105                REFERENCES      User    (userName)
106
107        CONSTRAINT fk_Groups
108                FOREIGN KEY             (groupName)
109                REFERENCES      Groups  (groupName)
110);
111
112CREATE TABLE Hardware (
113        deviceNum       SMALLINT        NOT NULL,
114        mountPoint      VARCHAR(20)     NOT NULL,
115        transfSize      SMALLINT        NOT NULL,
116
117        PRIMARY KEY     (deviceNum, mountPoint)
118);
119
120
121INSERT OR IGNORE INTO Files SELECT fileName, iNodeNum, deviceNum FROM Temp;
122
123INSERT OR IGNORE INTO Groups SELECT DISTINCT groupName, GID FROM Temp;
124
125INSERT OR IGNORE INTO Inodes SELECT DISTINCT iNodeNum, deviceNum, fileType, fileSize, numHardLinks, accessRights, userName, groupName FROM Temp;
126
127INSERT OR IGNORE INTO Hardware SELECT deviceNum, mountPoint, transfSize FROM Temp;
128
129DROP TABLE Temp;
130
131EOF
132
133
134rm fil_info.txt
135
136echo Program has finished and will now exit.