Twitter Project Database Tables

From wiki
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
Retrieved from ‘http://alaning.me/index.php?title=Twitter_Project_Database_Tables&oldid=249’