User:Andre Castro/2/spam

Characters

I began identifying and describing the characters I find in the spam messages from the database.

several reasons sparked my interest for the characters found in spam:

• one character reappears across emails
• often, if portrayed as a rich character happens to be a real person

• the rich (real) character (often):
  • is known publicly
  • is rich
  • got killed
  • is referred to in mass-media, specially due to its controversial death
  • left money behind, who the narrator-sender is in possession but needs the email recipient's help to get hold of it
  • is related to other characters (either mentioned or not mentioned on the emails)

What characters do we find?

in spanish prison/fraud emails

• sender
  • A)simple kind a guy. Working on a bank or insurance company who proposes a business with you, for the profit of both. It is somehow a corrupt character, but not utterly corrupt. Only wants a bit more than he has, a better living. He is not greedy, or at least is greedy as any of us is. Often he is Africa.
  • B) A sick and religious widow, who want to donate her deceased husband fortune. She has no family left, therefore asks for the recipient's help to help her transfer the money to charity. She trust in the good-willingness and loyalty of the recipient.
  • C) An America soldier in Iraq or Afghanistan who comes across a large some of money and needs the recipient' s help to take it out the country he is in. (Goes often by the name of David Joseph - clear Christian tone)

• the rich man - the source of the money
  • A) often a rich man, with businesses going on, often in an African country, but he is a foreigner. Often he appears to be related to other characters that the email doesn't mention.
  • B) A wealthy man who died in either Iraq or Afghanistan. His fortune was left to his wife.

method

I have added to the database of spam a new table: Character

A character describes an individual that:

• can present on one or more emails
• can related to other character (present or not within the emails)
• has a role in the emails
• has a history to herself
• has a future

It would be interesting to translate the relationships of characters to a graph. GRAPHYS??

db

In the db I used a Character table. The elements of this table are related to the emails, where they are referred. They are also related to other characters.

        EMAIL A                  EMAIL B
         /    \                        |
        /      \                       |
CHARACTER A    CHARACTER B       CHARACTER C    CHARACTER D
    |  |         |                    | |             |
    |  -----------                    | |             |
    -----------------------------------  -------------

• Who is each of the characters?
• How is he related to other characters?

in django

Currently in django I have the following table

CREATE TABLE "spam_spam_character" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(200) NOT NULL UNIQUE,
    "birth" date,
    "death" date,
    "nationality" varchar(200),
    "occupation" varchar(200),
    "bio" text,
    "in_media" text,
    "other" text,
    "photo" varchar(100),
    "rate" integer NOT NULL
);

• Redundant fields (currently):
  • other

And the intermediary tables

CREATE TABLE "spam_characterxmsg" (
    "id" integer NOT NULL PRIMARY KEY,
    "msg_id" integer NOT NULL REFERENCES "spam_spam_msg" ("id"),
    "character_id" integer NOT NULL REFERENCES "spam_spam_character" ("id"),
    "role" varchar(300),
    "pseudonyms" varchar(200)
);

• Redundant fields (currently):
  • Role

CREATE TABLE "spam_characterxcharacter" (
    "id" integer NOT NULL PRIMARY KEY,
    "character1_id" integer NOT NULL REFERENCES "spam_spam_character" ("id"),
    "character2_id" integer NOT NULL REFERENCES "spam_spam_character" ("id"),
    "relationship" varchar(300),
    "history" text,
    "in_media" text,
    "other" text
);

• Redundant fields (currently):
  • history
  • in media
  • other

The redundant field must be hidden in the admin

---

other alterations

• spam_msg field must be visible in the admin