Sqlite3 - I\'m having problems solving this issue and am looking for some help.

Question

Sqlite3 - I'm having problems solving this issue and am looking for some help. Here are the problem requirements:

*********************************************************************************************************************************

Find an electronic book source and copy/paste all text into a new text file called book.txt. Using SQLite3, create a table from the content of book.txt that has one line per sentence along with an id number (For ex...id | content). Write the source code to do this and save it as build.sql. Essentially, you need to import the data from book.txt into a new table with one line per sentence and a unique ID for each line.

*********************************************************************************************************************************

I've used the following commands from the sqlite3 command line to import the data, but i can't figure out how to get the Id column set and to auto increment. I tried several times without success.

CREATE TABLE table1(id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT);

INSERT INTO table1(id,content) VALUES('book.txt',readfile('book.txt'));

When using a select statement on table1 after running these command it simply returns the data from the text file. There are No id or columns. Any help woult be appreciated as I am stuck and our book nor professor have given us anything close to this type of problem.

Explanation / Answer

Hi,

I am able to solve this problem. Please find the answer below.

Step 1:

Paste the ebook contents in a file name as book.txt.

Step 2:

Copy and paste the below content in a script file named as build.sql. Please make sure that both book.txt and build.sql are in same folder.

#Script Content

CREATE TABLE textbook (
id INTEGER PRIMARY KEY AUTOINCREMENT,
content blob NOT NULL
);
CREATE TEMP TABLE lines(text);
.import book.txt lines
.separator |
INSERT INTO textbook(content) SELECT text FROM lines;
DROP TABLE lines;

Step 3:

Open SQLite shell/terminal access with required DB name and make sure the db shell is opened in the same path where our book.txt and build.sql file is present.

Step 4:

Type the following in the sqlite shell.

.read build.sql

SELECT * FROM textbook;

Thats it. You should now able to see the ebook contents inside the table with each line having it's own unique primary key.

Hope this anwser helped you !!

Have a great day.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.