ProgramableWebProjectExercise1/forum.sql at master · aghaznaw/ProgramableWebProjectExercise1 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users(
  user_id INTEGER PRIMARY KEY,
  nickname TEXT UNIQUE,
  regDate INTEGER,
  lastLogin INTEGER,
  timesviewed INTEGER,
  UNIQUE(user_id, nickname));
INSERT INTO "users" VALUES(1,'Mystery',1362015937,1362015937,2311);
INSERT INTO "users" VALUES(2,'AxelW',1357724086,1357724086,8112);
INSERT INTO "users" VALUES(3,'LinuxPenguin',1362012937,1362012937,139);
INSERT INTO "users" VALUES(4,'Koodari',1389260086,1389260086,2198);
INSERT INTO "users" VALUES(5,'HockeyFan',1394357686,1394357686,612);
INSERT INTO "users" VALUES(6,'sqlcoder',1516976196,1516976196,0);
CREATE TABLE users_profile(
  user_id INTEGER PRIMARY KEY,
  firstname TEXT,
  lastname TEXT,
  email TEXT,
  website TEXT,
  picture TEXT,
  mobile TEXT,
  skype TEXT,
  age INTEGER,
  residence TEXT,
  gender TEXT,
  signature TEXT,
  avatar TEXT,
  FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE);
INSERT INTO "users_profile" VALUES(1,'Mystery','Williams','jane@imaginecompany.com',NULL,'photo1.jpg',NULL,NULL,22,'New York','Female','Well, hello there! Blah ...','avatar_2.gif');
INSERT INTO "users_profile" VALUES(2,'Axel','Watts','aw@hotmail.com',NULL,'photo3.png',NULL,NULL,28,NULL,'Male','Without an exception, there is no rule...',NULL);
INSERT INTO "users_profile" VALUES(3,'François','Beaumont','francois@yahoo.com','http://www.francoisbeaumont.fr/','',NULL,NULL,19,'Paris','Male','None','avatar_4.jpg');
INSERT INTO "users_profile" VALUES(4,'Matti','Meikalainen','matti@suomi24.fi','http://www.geocities.com/~matti/','photo_5.jpg',NULL,NULL,30,'Helsinki','Male','Elama on.','avatar_5.png');
INSERT INTO "users_profile" VALUES(5,'Dan','Nicholls','dan@gmail.com','http://www.hockeyfan.com/','photo8.png',NULL,NULL,24,'Washington DC','Male','Washington Capitals rule!','avatar_7.jpg');
INSERT INTO "users_profile" VALUES(6,'Jake','Sully','sully@rda.com','http: //www.pandora.com/','na_vi2.png','83232323','jakesully',24,'USA','Male','I am blue','na_vi.jpg');
CREATE TABLE friends (
  user_id INTEGER,
  friend_id INTEGER,
  PRIMARY KEY(user_id, friend_id),
  FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
  FOREIGN KEY(friend_id) REFERENCES users(user_id) ON DELETE CASCADE);
INSERT INTO "friends" VALUES(4,5);
INSERT INTO "friends" VALUES(4,1);
CREATE TABLE messages (
  message_id INTEGER PRIMARY KEY,
  title TEXT,
  body TEXT,
  timestamp INTEGER,
  ip TEXT,
  timesviewed INTEGER,
  reply_to INTEGER,
  user_nickname TEXT,
  user_id INTEGER,
  editor_nickname TEXT,
  FOREIGN KEY(reply_to) REFERENCES messages(message_id) ON DELETE CASCADE,
  FOREIGN KEY(user_id, user_nickname) REFERENCES users(user_id, nickname) ON DELETE CASCADE);
INSERT INTO "messages" VALUES(1,'CSS: Margin problems with IE','I am using a float layout on my website but I''ve run into some problems with Internet Explorer. I have set the left margin of a float to 100 pixels, but IE uses a margin of 200px instead. Why is that? Is this one of the many bugs in IE?',1362017481,'213.216.232.193',0,NULL,'AxelW',2,NULL);
INSERT INTO "messages" VALUES(2,'Nothing works','The webpage is a shit',1362017481,'217.119.25.162',1,NULL,'Jack',NULL,NULL);
INSERT INTO "messages" VALUES(3,'In case','Just in case you can''t find the book, here''s the equation:
                                p_1 + ρgy_1 + 1/2 * ρ(v_1)^2 = p_2 + ρgy_2 + 1/2 * ρ(v_2)^2',1362017481,'211.213.225.19',0,1,'Science guru',NULL,NULL);
INSERT INTO "messages" VALUES(4,'Fourier transform','I have the following function: x(t) = exp(-a|t|)
                                What is the Fourier transform for x(t)? I tried to calculate it using
                                the definition, but it got too difficult for me. Can anyone help?',1362017481,'232.201.178.44',0,NULL,'Math guru not',NULL,NULL);
INSERT INTO "messages" VALUES(5,'According to a table','I checked my math books and found the transform for the
                                function you gave (provided that a > 0): X(f) = 2a / (a^2 + (2πf)^2)

                                Merry Christmas, by the way!',1362017481,'191.255.187.123',0,1,'Solver',NULL,NULL);
INSERT INTO "messages" VALUES(6,'CSS: Margin problems with IE','I''m using a float layout on my website but I''ve run into
                                some problems with Internet Explorer. I have set the left margin of
                                a float to 100 pixels, but IE uses a margin of 200px instead. Why
                                is that? Is this one of the many bugs in IE?',1362017481,'204.57.77.207',0,NULL,'Koodari',4,NULL);
INSERT INTO "messages" VALUES(7,'Solution','Yep, you guessed it:
                                <http://www.positioniseverything.net/explorer/doubled-margin.html>
                                In brief, you need to change the value of the ''display'' property to
                                ''inline'' in order to fix the problem. Other browsers are not
                                affected by this trick.',1362017481,'66.249.66.206',0,5,'Anonymous',NULL,NULL);
INSERT INTO "messages" VALUES(8,'Idiot','Are you a ¤%!§# moron? IE is the market leader, so other
                                browsers should simply do things the same way as IE. You geeks
                                always think that the W3C "standards" are laws that
                                everybody should follow. Dream on.',1362017481,'255.32.3.10',0,6,'Haha',NULL,NULL);
INSERT INTO "messages" VALUES(9,'ZIP?','Hi all! A friend of mine sent me an e-mail message, and
                                there was a .ZIP file as an attachment. What kind of a program do I
                                need to open the file? Thanks in advance!',1362017481,'143.76.98.211',0,NULL,'ZAP',NULL,NULL);
INSERT INTO "messages" VALUES(10,'WinZip','WinZip, for example. There are plenty of other applications that are capable of decompressing ZIP files, but WinZip is probably the most popular one. You can also try to google for UltimateZip, PowerArchiver, 7-Zip, IZArc and WinRAR. Choose whichever program you like.',1362017481,'166.198.201.200',0,1,'Koodari',4,NULL);
INSERT INTO "messages" VALUES(11,'Great!','Thanks a lot! I tried Winzip and it works like a
                                charm!',1362017481,'188.178.231.234',0,1,'ZAP',NULL,NULL);
INSERT INTO "messages" VALUES(12,'Edmonton''s goalie','Does anyone know where Jussi Markkanen used to play before
                                he came to Edmonton Oilers? He was excellent in the Stanley Cup finals
                                last season! Too bad they lost...',1362017481,'217.119.25.162',0,NULL,'HockeyFan',5,NULL);
INSERT INTO "messages" VALUES(13,'History','As far as I know, he has played in SaiPa and Tappara in the
                                Finnish SM-Liiga. You can probably find more details about his career
                                from the official website of Edmonton Oilers.

                                Edit: I forgot the address of the website. Here it is:
                                http://www.edmontonoilers.com/roster/markkanen_jussi/',1362017481,'66.249.72.235',0,3,'Mystery',1,NULL);
INSERT INTO "messages" VALUES(14,'What about Roloson?','By the way, HockeyFan, what do you think about Dwayne
                                Roloson? Which one should be the #1 goalie in Oilers? An
                                interesting detail is that Roloson has also played in Finland. They
                                didn''t play against each other, however, because Markkanen was in
                                Russia during the 04 - 05 season.',1362017481,'66.249.72.235',0,3,'Mystery',1,NULL);
INSERT INTO "messages" VALUES(15,'Thanks!','Ah, I didn''t realize they had a website, too! Thanks for
                                the tip!',1362017481,'217.119.25.162',0,7,'HockeyFan',5,NULL);
INSERT INTO "messages" VALUES(16,'CSS: Margin problems with IE','I''m using a float layout on my website but I''ve run into some problems with Internet Explorer. I have set the left margin of a float to 100 pixels, but IE uses a margin of 200px instead. Why is that? Is this one of the many bugs in IE?',1362017481,'217.119.25.162',0,9,'HockeyFan',5,NULL);
INSERT INTO "messages" VALUES(17,'Try hello','Use the magic word ''Hello''',1362017481,'127.0.0.1',0,1,'AxelW',2,NULL);
INSERT INTO "messages" VALUES(18,'Favorite team?','What''s your favorite team in the World Championship
                                tournament in 2006? Who do you think will win?',1362017481,'213.216.235.203',0,NULL,'Zidane',NULL,NULL);
INSERT INTO "messages" VALUES(19,'My favourite','I''m a fan of team England, but I think Brazil is going to
                                win the whole thing again.',1362017481,'212.89.186.12',0,1,'England',NULL,NULL);
INSERT INTO "messages" VALUES(20,'Germany of course','Germany is the best! They have a strong team this time,
                                so I hope it''s finally their turn to win!',1362017481,'80.66.88.179',0,1,'Kahn',NULL,NULL);
INSERT INTO "messages" VALUES(21,'testing','ton thoi gian',1516976271,'0.0.0.0',0,NULL,'sqlcoder',6,NULL);
INSERT INTO "messages" VALUES(22,'SQL rules','ayzzlmao',1516976308,'0.0.0.0',0,NULL,'sqlcoder',6,'admin');
COMMIT;