Twitter Project Database Tables
Jump to navigation
Jump to search
internal documentation
Database Type: MSSQL
Database Name: redJOHN
- BUCKINGHAM_PALACE screen_name, user_id matching
- DEVIOUS - individual tweet ranking
- DINO - empty?
- DISCOVERY - tiger, temp because queued up experiment
- FANATICAL - AFINN-111.txt
- LABMIT - labMIT-1.0.txt
- NRCEmotional - NRCemotionlexicon.csv
- NRCSentimental
- NRCSentimentalTags
- PERSONABLE - ocean.1to3grams.gender_age.rmatrix.csv
- RIVER - website queue and query
- TEDIOUS - LIWC2007.dic
- TIGER - tweet storage from json file
- VERY_LARGE_ARRAY - website data
- YOSEMITE - location metadata for google maps api
BUCKINGHAM_PALACE
USE [redJOHN] GO /****** Object: Table [dbo].[BUCKINGHAM_PALACE] Script Date: 10/18/2014 9:17:41 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[BUCKINGHAM_PALACE]( [user_id_str] [nchar](20) NULL, [screen_name] [nchar](20) NULL ) ON [PRIMARY] GO
COOKIE
USE [redJOHN] GO /****** Object: Table [dbo].[COOKIE] Script Date: 11/5/2014 7:43:29 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[COOKIE]( [user_id_str] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Usage
INSERT INTO COOKIE(user_id_str) SELECT DISTINCT user_id_str FROM [dbo].[TIGER]
DEVIOUS
USE [redJOHN] GO /****** Object: Table [dbo].[DEVIOUS] Script Date: 10/18/2014 9:18:06 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DEVIOUS]( [tweet_id] [varchar](255) NULL, [funct] [int] NULL, [pronoun] [int] NULL, [ppron] [int] NULL, [i] [int] NULL, [we] [int] NULL, [you] [int] NULL, [shehe] [int] NULL, [they] [int] NULL, [ipron] [int] NULL, [article] [int] NULL, [verb] [int] NULL, [auxverb] [int] NULL, [past] [int] NULL, [present] [int] NULL, [future] [int] NULL, [adverb] [int] NULL, [preps] [int] NULL, [conj] [int] NULL, [negate] [int] NULL, [quant] [int] NULL, [number] [int] NULL, [swear] [int] NULL, [social] [int] NULL, [family] [int] NULL, [friend] [int] NULL, [humans] [int] NULL, [affect] [int] NULL, [posemo] [int] NULL, [negemo] [int] NULL, [anx] [int] NULL, [anger] [int] NULL, [sad] [int] NULL, [cogmech] [int] NULL, [insight] [int] NULL, [cause] [int] NULL, [discrep] [int] NULL, [tentat] [int] NULL, [certain] [int] NULL, [inhib] [int] NULL, [incl] [int] NULL, [excl] [int] NULL, [percept] [int] NULL, [see] [int] NULL, [hear] [int] NULL, [feel] [int] NULL, [bio] [int] NULL, [body] [int] NULL, [health] [int] NULL, [sexual] [int] NULL, [ingest] [int] NULL, [relativ] [int] NULL, [motion] [int] NULL, [space] [int] NULL, [time] [int] NULL, [work] [int] NULL, [achieve] [int] NULL, [leisure] [int] NULL, [home] [int] NULL, [money] [int] NULL, [relig] [int] NULL, [death] [int] NULL, [assent] [int] NULL, [nonfl] [int] NULL, [filler] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
DINO
USE [redJOHN] GO /****** Object: Table [dbo].[DINO] Script Date: 10/18/2014 9:18:32 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DINO]( [ID] [int] NULL, [Description] [varchar](20) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
DISCOVERY
USE [redJOHN] GO /****** Object: Table [dbo].[DISCOVERY] Script Date: 10/18/2014 9:21:25 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DISCOVERY]( [contributors] [varchar](10) NULL, [coordinates] [varchar](200) NULL, [created_at] [datetime] NULL, [entities] [varchar](max) NULL, [favourite_count] [varchar](10) NULL, [favorited] [nchar](10) NULL, [geo] [nchar](200) NULL, [id_str] [nchar](20) NULL, [in_reply_to_screen_name] [nchar](20) NULL, [in_reply_to_status_id_str] [nchar](20) NULL, [in_reply_to_user_id_str] [nchar](20) NULL, [lang] [nchar](10) NULL, [place] [nchar](2000) NULL, [retweet_count] [varchar](10) NULL, [retweeted] [nchar](10) NULL, [source] [nvarchar](500) NULL, [text] [nvarchar](max) NULL, [truncated] [nchar](10) NULL, [user_id_str] [nchar](20) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO
FANATICAL
USE [redJOHN] GO /****** Object: Table [dbo].[FANATICAL] Script Date: 10/29/2014 3:56:39 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[FANATICAL]( [word] [varchar](20) NOT NULL, [value] [real] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
LABMIT
USE [redJOHN] GO /****** Object: Table [dbo].[LABMIT] Script Date: 10/29/2014 3:57:01 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[LABMIT]( [word] [varchar](20) NOT NULL, [happiness_rank] [real] NULL, [happiness_average] [real] NULL, [happiness_standard_deviation] [real] NULL, [twitter_rank] [real] NULL, [google_rank] [real] NULL, [nyt_rank] [real] NULL, [lyrics_rank] [real] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
NRCEmotional
USE [redJOHN] GO /****** Object: Table [dbo].[NRCEmotional] Script Date: 10/29/2014 5:47:23 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[NRCEmotional]( [word] [varchar](20) NOT NULL, [positive] [real] NULL, [negative] [real] NULL, [anger] [real] NULL, [anticipation] [real] NULL, [disgust] [real] NULL, [fear] [real] NULL, [joy] [real] NULL, [sadness] [real] NULL, [surprise] [real] NULL, [trust] [real] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
NRCSentimental
USE [redJOHN] GO /****** Object: Table [dbo].[NRCSentimental] Script Date: 10/29/2014 5:47:35 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[NRCSentimental]( [word] [varchar](225) NOT NULL, [sentimentScore] [real] NULL, [numPositive] [real] NULL, [numNegative] [real] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
NRCSentimentalTags
USE [redJOHN] GO /****** Object: Table [dbo].[NRCSentimentalTags] Script Date: 10/29/2014 5:47:46 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[NRCSentimentalTags]( [word] [varchar](40) NOT NULL, [sentimentScore] [real] NULL, [numPositive] [real] NULL, [numNegative] [real] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
PERSONABLE
USE [redJOHN] GO /****** Object: Table [dbo].[PERSONABLE] Script Date: 10/29/2014 3:57:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[PERSONABLE]( [word] [varchar](40) NOT NULL, [ext] [real] NULL, [agr] [real] NULL, [con] [real] NULL, [neu] [real] NULL, [ope] [real] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
RIVER
USE [redJOHN] GO /****** Object: Table [dbo].[RIVER] Script Date: 10/18/2014 9:23:59 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[RIVER]( [line_id] [int] IDENTITY(1,1) NOT NULL, [created_date] [datetime] NULL, [user] [nchar](100) NULL, [type] [nchar](100) NULL, [action] [nchar](100) NULL, [status] [nchar](100) NULL, [ttl] [int] NULL ) ON [PRIMARY] GO
TEDIOUS
USE [redJOHN] GO /****** Object: Table [dbo].[TEDIOUS] Script Date: 10/18/2014 9:24:43 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TEDIOUS]( [word] [varchar](255) NULL, [funct] [int] NULL, [pronoun] [int] NULL, [ppron] [int] NULL, [i] [int] NULL, [we] [int] NULL, [you] [int] NULL, [shehe] [int] NULL, [they] [int] NULL, [ipron] [int] NULL, [article] [int] NULL, [verb] [int] NULL, [auxverb] [int] NULL, [past] [int] NULL, [present] [int] NULL, [future] [int] NULL, [adverb] [int] NULL, [preps] [int] NULL, [conj] [int] NULL, [negate] [int] NULL, [quant] [int] NULL, [number] [int] NULL, [swear] [int] NULL, [social] [int] NULL, [family] [int] NULL, [friend] [int] NULL, [humans] [int] NULL, [affect] [int] NULL, [posemo] [int] NULL, [negemo] [int] NULL, [anx] [int] NULL, [anger] [int] NULL, [sad] [int] NULL, [cogmech] [int] NULL, [insight] [int] NULL, [cause] [int] NULL, [discrep] [int] NULL, [tentat] [int] NULL, [certain] [int] NULL, [inhib] [int] NULL, [incl] [int] NULL, [excl] [int] NULL, [percept] [int] NULL, [see] [int] NULL, [hear] [int] NULL, [feel] [int] NULL, [bio] [int] NULL, [body] [int] NULL, [health] [int] NULL, [sexual] [int] NULL, [ingest] [int] NULL, [relativ] [int] NULL, [motion] [int] NULL, [space] [int] NULL, [time] [int] NULL, [work] [int] NULL, [achieve] [int] NULL, [leisure] [int] NULL, [home] [int] NULL, [money] [int] NULL, [relig] [int] NULL, [death] [int] NULL, [assent] [int] NULL, [nonfl] [int] NULL, [filler] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
TIGER
USE [redJOHN] GO /****** Object: Table [dbo].[TIGER] Script Date: 10/18/2014 9:24:59 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TIGER]( [contributors] [varchar](10) NULL, [coordinates] [varchar](200) NULL, [created_at] [datetime] NULL, [entities] [varchar](max) NULL, [favourite_count] [varchar](10) NULL, [favorited] [nchar](10) NULL, [geo] [nchar](200) NULL, [id_str] [nchar](20) NULL, [in_reply_to_screen_name] [nchar](20) NULL, [in_reply_to_status_id_str] [nchar](20) NULL, [in_reply_to_user_id_str] [nchar](20) NULL, [lang] [nchar](10) NULL, [place] [nchar](2000) NULL, [retweet_count] [varchar](10) NULL, [retweeted] [nchar](10) NULL, [source] [nvarchar](500) NULL, [text] [nvarchar](max) NULL, [truncated] [nchar](10) NULL, [user_id_str] [nchar](20) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO
VERY_LARGE_ARRAY
USE [redJOHN] GO /****** Object: Table [dbo].[VERY_LARGE_ARRAY] Script Date: 10/18/2014 9:25:13 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[VERY_LARGE_ARRAY]( [user_id] [nchar](15) NULL, [category] [nchar](20) NULL, [name] [nchar](300) NULL, [value] [real] NULL ) ON [PRIMARY] GO
YOSEMITE
USE [redJOHN] GO /****** Object: Table [dbo].[YOSEMITE] Script Date: 10/18/2014 9:25:26 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[YOSEMITE]( [coordinates] [varchar](200) NULL, [user_id_str] [nchar](20) NULL, [id_str] [nchar](20) NULL, [text] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO