Bash Script / Regular Expression Problem

rm_-rf_windows · 03-26-2012, 04:59 PM

Hi all,

I'm making progress in Bash regular expressions and scripting and am enjoying it thanks in part to the participants who have given really good advice in the forum.

I've got yet a new question, probably not technically a regular expression question but similar.

I've got a list of words which has the following form:

Code:

CATEGORY
Subcategory
1 specific type
2 specific type n° 2
Subcategory2
3 specific type n° 3
etc.
CATEGORY2
Subcategory3
89 specific type n° 89
90 specific type n° 90
Subcategory4
91 specific type n° 91
CATEGORY3
etc.

The idea is to convert this into a database script (sql).

The lines with numbers are going to be the main tuples. Their foreign keys will point to another id in the "subcategory" table. The "subcategory" table will have a foreign key pointing to the id of the "category" table. A very simple structure.

So my question is, keeping in mind that CATEGORIES are all uppercase, Subcategories all begin with an uppercase followed by lower case and the main tuple entries have a number followed by a tab and a word... All lines can be multiword (including spaces, hyphens, etc.)... How can I store the CATEGORY and Subcategory values into a variable in order to link the respective values to each other? It is in list form, the "titles" and "subtitles" correspond to the values which are directly below them (until another subtitle/title is encountered).

In other words, the end result should be:

Code:

CREATE TABLE category...
CREATE TABLE subcategory...
CREATE TABLE main_table...

INSERT INTO category VALUES (0, "CATEGORY");
INSERT INTO category VALUES (1, "CATEGORY2");
INSERT INTO category VALUES (2, "CATEGORY3");
etc.
INSERT INTO subcategory VALUES (0, "subcategory", 0);
INSERT INTO subcategory VALUES (1, "subcategory2", 0);
INSERT INTO subcategory VALUES (2, "subcategory3", 1);
INSERT INTO subcategory VALUES (3, "subcategory4", 1);
INSERT INTO subcategory VALUES (4, "subcategory5", 2);
etc.
INSERT INTO main_table VALUES (0, "specific type", 0);
INSERT INTO main_table VALUES (1, "specific type n° 2", 0);
etc.
INSERT INTO main_table VALUES ("specific type n° 91", 1); 
etc.

What I don't know how to do is to link CATEGORY to Subcategory and the main tuples in my main script in order to generate an .sql file. The rest is okay.

(I don't know if I've been clear this time round, if not, let me know and I'll respond again).

Thanks in advance,

rm

bigrigdriver · 03-26-2012, 07:10 PM

It's been a few years since I did anything with sql, but I'll give it a try.

When you create the category table, you populate it with the names of each category. For the sake of consistency, I suggest you name CATEGORY as CATEGORY1 as a means of preventing unexpected errors when running queries, since there other categories with a number in the name. Also, rename subcategory as subcategory1 in the subcategory table.

Now, each CATEGORY in the category table should have a unique identifier (category_id) as the primary id, as well as the category name.

In the subcategory table, each subcategory should have a unique identifier (subcatetory_id) as the primary id, the subcategory name, AND a foreign key which is the same as the primary id from the category table.

In the main_table, which I gather has all the items you want to use to populate the subcategory table, each item in the main_table has a unique id (item_id) as the primary id, the item name, AND a foreign key which is the same as the primary id from the subcategory table.

If I have remembered it correctly, that's how the tables are linked together.

Since my study of sql, mysql has added something called triggers, which I must say I haven't studied so I don't know how they work. I'll leave it to you to research that.

rm_-rf_windows · 03-27-2012, 06:04 AM

Thank you Bigrigdriver, but that wasn't my question!

I want to know how to generate a script that will give me:

Code:

0, "subcategory", 0
1, "subcategory2", 0
2, "subcategory3", 1
3, "subcategory4", 1
4, "subcategory5", 2
etc
0, "specific type", 0
1, "specific type n° 2", 0

In other words, how do I store "heading" and "subheading" into variables in order to place them next to their respective words ("specific type n° 2" is a word. I guess my example wasn't clear.

Here's another example:

Code:

SCIENCE
Biology
0 cell
1 organ
2 organism
3 virus
4 bacteria
5 ... etc.

I'm going to have a table for SCIENCE, LAW, POLITICS, JOURNALISM, etc.
Another table for Biology, Chemistry, etc. which are under science,
And yet another with organ, organism, virus, bacteria, etc.

This isn't a database question, it's a script question.

How do ti get:

Code:

0 cell "Biology id"
1 organ "Biology id"

lined up in a text file...

And how do I get

Code:

"Biology id" "SCIENCE id"
"Chemistry" "SCIENCE id"

lined up...

etc.

Or, to simplify my example further (this would be ok), how do I get:

Code:

0 cell, Biology, SCIENCE
1 organ, Biology, SCIENCE
2 organism, Biology, SCIENCE
3 virus, Biology, SCIENCE
4 bacteria, Biology, SCIENCE
...
78 contract, Employment law, LAW
79 severance pay, Employment law, LAW, ... 
etc.

I want to be able to store headings and subheadings in a variable and then place them beside their corresponding words (word = "0 cell"). When a new heading or subheading appears while going down in the text, I want the subheading variable to change so that the words below them have the correct categories associated with them (and likewise for headings).

Reminder: HEADINGS ARE IN UPPERCASE; Subheadings Begin With An Uppercase, The Rest Is Lower Case;
specific words are in the form "0 cell", in other words, a number followed by a space or tab and then the actual word.
All words, headings and subheadings can be either one word or several words.

What I want is rather simple, sorry if my explanations aren't very clear.

Many thanks Bigrigdriver for your response nevertheless, I don't think my question was very clear.

rm

catkin · 03-27-2012, 06:41 AM

OK to do it with awk?

Code:

c@CW8:~/d/bin/try/LQ$ cat LQ-936561.input
SCIENCE
Biology
0 cell
1 organ
2 organism
3 virus
4 bacteria
c@CW8:~/d/bin/try/LQ$ ./LQ-936561.awk LQ-936561.input
Biology SCIENCE
0 cell Biology
1 organ Biology
2 organism Biology
3 virus Biology
4 bacteria Biology
c@CW8:~/d/bin/try/LQ$ cat LQ-936561.awk
#!/usr/bin/awk -f

$0 ~ /^[A-Z]*$/ {
    #print "DEBUG: " $0 " matched heading"
    heading = $0
    next
}

/[A-Z][a-z]*/ {
    #print "DEBUG: " $0 " matched subheading"
    subheading = $0
    print subheading " " heading
    next
}

{
    #print "DEBUG: " $0 " matched ordinary line"
    print $0 " " subheading
}

Note: the pattern $0 ~ /^[A-Z]*$/ may be controversial. It is intended to be portable between versions of awk.

pan64 · 03-28-2012, 01:05 PM

It depends on your knowledge. You can follow the idea of catkin (above), but also you can use perl script or java, it really depends on you. Maybe you can solve it using bash, but looks not really convenient.