-- ================================================== -- Sören Preibusch -- 2009-06-12 -- -- http://preibusch.de/publ/privacy_jungle -- ================================================== CREATE TYPE [dbo].[DefaultYesNo] FROM [tinyint] NOT NULL CREATE TYPE [dbo].[DefaultYesNoNa] FROM [tinyint] NOT NULL CREATE TYPE [dbo].[HtmlFileName] FROM [varchar](100) NOT NULL CREATE TYPE [dbo].[MandatoryYesNo] FROM [tinyint] NOT NULL CREATE TYPE [dbo].[UserName] FROM [varchar](10) NOT NULL CREATE TYPE [dbo].[YesNo] FROM [bit] NOT NULL CREATE TYPE [dbo].[YesNoNa] FROM [tinyint] NOT NULL CREATE TYPE [dbo].[YesNoNaUnspecified] FROM [tinyint] NOT NULL CREATE TYPE [dbo].[YesNoSecretly] FROM [tinyint] NOT NULL CREATE TYPE [dbo].[YesNoUnknown] FROM [tinyint] NOT NULL CREATE TYPE [dbo].[YesNoUnspecified] FROM [tinyint] NOT NULL CREATE TABLE [dbo].[Sites]( [ID] [int] NOT NULL, [BaEditionDate] [smalldatetime] NULL, [BaSiteName] [varchar](50) NOT NULL, [BaSiteUrl] [varchar](100) NOT NULL, [BaSignUpUrl] [varchar](300) NOT NULL, [BaOperatorName] [varchar](100) NOT NULL, [BaOperatorCountry] [varchar](50) NULL, [BaGeneralPurpose] [dbo].[YesNo] NULL, [BaBusinessTargeted] [dbo].[YesNo] NULL, [BaYouthTargeted] [dbo].[YesNo] NULL, [BaReunionTargeted] [dbo].[YesNo] NULL, [BaMediaSharing] [dbo].[YesNo] NULL, [BaImaginaryIdentity] [dbo].[YesNo] NULL, [BaLaunchDate] [smalldatetime] NULL, [BaIsCommercial] [dbo].[YesNoUnknown] NOT NULL, [BaIsProfitable] [dbo].[YesNoUnknown] NOT NULL, [BaLanguagesSwitchable] [dbo].[YesNo] NOT NULL, [BaLanguagesOffered] [varchar](500) NOT NULL, [BaLanguagesOfferedCount] [tinyint] NULL, [BaTargetedNationalMarkets] [varchar](500) NOT NULL, [BaUserCount] [bigint] NULL, [BaUserCountDate] [smalldatetime] NULL, [BaUserCountSource] [varchar](100) NULL, [BaAlexaRank] [int] NULL, [BaAlexaRankDate] [smalldatetime] NULL, [BaAlexaReachChange] [float] NULL, [BaAlexaTrafficRankChange] [float] NULL, [BaAlexaPageViewsChange] [float] NULL, [BaPremiumAccounts] [bit] NULL, [BaShowsAdvertisements] [bit] NULL, [BaNotes] [varchar](max) NULL, [SuDate] [smalldatetime] NULL, [SuProfile] [varchar](50) NOT NULL, [SuSslSupported] [varchar](50) NOT NULL, [SuSslDefault] [dbo].[YesNo] NOT NULL, [SuSslOffered] [dbo].[YesNo] NOT NULL, [SuSslCertificateOk] [dbo].[YesNoNa] NOT NULL, [SuPrivPolCheckboxPresent] [dbo].[YesNo] NOT NULL, [SuPrivPolCheckboxPreChecked] [dbo].[YesNoNa] NOT NULL, [SuPrivPolCheckboxLabel] [varchar](max) NULL, [SuPrivPolCheckboxPosition] [varchar](50) NULL, [SuPrivPolCheckboxMandatory] [dbo].[YesNoNa] NOT NULL, [SuTermsCheckboxPresent] [dbo].[YesNo] NOT NULL, [SuTermsCheckboxPreChecked] [dbo].[YesNoNa] NOT NULL, [SuTermsCheckboxLabel] [varchar](max) NULL, [SuTermsCheckboxPosition] [varchar](50) NULL, [SuTermsCheckboxMandatory] [dbo].[YesNoNa] NOT NULL, [SuCaptchaRequired] [dbo].[YesNo] NOT NULL, [SuCaptchaSecurity] [varchar](50) NOT NULL, [SuPrivPolLinkAvailable] [dbo].[YesNo] NOT NULL, [SuPrivPolLinkPosition] [varchar](50) NOT NULL, [SuPrivPolLinkRequiresJavaScript] [dbo].[YesNoNa] NOT NULL, [SuPrivPolLinkOpensNewWindow] [dbo].[YesNoNa] NOT NULL, [SuPrivPolLinkCount] [tinyint] NULL, [SuTermsLinkAvailable] [dbo].[YesNo] NOT NULL, [SuTermsLinkPosition] [varchar](50) NOT NULL, [SuTermsLinkRequiresJavaScript] [dbo].[YesNoNa] NOT NULL, [SuTermsLinkOpensNewWindow] [dbo].[YesNoNa] NOT NULL, [SuTermsLinkCount] [tinyint] NULL, [SuImprintLinkAvailable] [dbo].[YesNo] NOT NULL, [SuImprintLinkPosition] [varchar](50) NOT NULL, [SuImprintLinkRequiresJavaScript] [dbo].[YesNoNa] NOT NULL, [SuImprintLinkOpensNewWindow] [dbo].[YesNoNa] NOT NULL, [SuP3Penabled] [dbo].[YesNo] NOT NULL, [SuP3Pcorrect] [dbo].[YesNoNa] NOT NULL, [SuP3PcompactEnabled] [dbo].[YesNo] NOT NULL, [SuP3PcompactCorrect] [dbo].[YesNoNa] NOT NULL, [SuP3Ppolicy] [nvarchar](max) NULL, [SuMandatoryFieldsMarked] [dbo].[YesNo] NOT NULL, [SuPrivPolInAllLanguages] [dbo].[YesNoNa] NOT NULL, [SuTermsInAllLanguages] [dbo].[YesNoNa] NOT NULL, [SuPrivPolInEnglish] [dbo].[YesNoNa] NOT NULL, [SuTermsInEnglish] [dbo].[YesNoNa] NOT NULL, [SuPromotionFree] [dbo].[YesNo] NOT NULL, [SuPromotionPhotos] [dbo].[YesNo] NOT NULL, [SuPromotionCommunicate] [dbo].[YesNo] NOT NULL, [SuPromotionBlogging] [dbo].[YesNo] NOT NULL, [SuPromotionMedia] [dbo].[YesNo] NOT NULL, [SuPromotionReunion] [dbo].[YesNo] NOT NULL, [SuPromotionDiscoverNew] [dbo].[YesNo] NOT NULL, [SuPromotionActivity] [dbo].[YesNo] NOT NULL, [SuPromotionPersonal] [dbo].[YesNo] NOT NULL, [SuPromotionFriends] [dbo].[YesNo] NOT NULL, [SuPromotionRomance] [dbo].[YesNo] NOT NULL, [SuPromotionTrendy] [dbo].[YesNo] NOT NULL, [SuPromotionExternal] [dbo].[YesNo] NOT NULL, [SuPromotionApplications] [dbo].[YesNo] NOT NULL, [SuPromotionGames] [dbo].[YesNo] NOT NULL, [SuPromotionSample] [dbo].[YesNo] NOT NULL, [SuPromotionNumber] [dbo].[YesNo] NOT NULL, [SuPromotionCertified] [dbo].[YesNo] NOT NULL, [SuPromotionPrivate] [dbo].[YesNo] NOT NULL, [SuPromotionBusiness] [dbo].[YesNo] NOT NULL, [SuPromotionNotes] [varchar](max) NULL, [SuDataName] [varchar](50) NOT NULL, [SuDataFirstName] [varchar](50) NOT NULL, [SuDataMiddleName] [varchar](50) NOT NULL, [SuDataMiddleInitialName] [varchar](50) NOT NULL, [SuDataLastName] [varchar](50) NOT NULL, [SuDataMaidenName] [varchar](50) NOT NULL, [SuDataEmail] [varchar](50) NOT NULL, [SuDataEmailAlternate] [varchar](50) NOT NULL, [SuDataAddressContinent] [varchar](50) NOT NULL, [SuDataAddressTimezone] [varchar](50) NOT NULL, [SuDataAddressCountry] [varchar](50) NOT NULL, [SuDataAddressState] [varchar](50) NOT NULL, [SuDataAddressCity] [varchar](50) NOT NULL, [SuDataAddressPostalCode] [varchar](50) NOT NULL, [SuDataAddressStreet] [varchar](50) NOT NULL, [SuDataAddressHouseNumber] [varchar](50) NOT NULL, [SuDataAddressHouseName] [varchar](50) NOT NULL, [SuDataPhone] [varchar](50) NOT NULL, [SuDataPhoneMobile] [varchar](50) NOT NULL, [SuDataPhoneLandline] [varchar](50) NOT NULL, [SuDataPhoneFax] [varchar](50) NOT NULL, [SuDataLoginName] [varchar](50) NOT NULL, [SuDataLoginPassword] [varchar](50) NOT NULL, [SuDataSex] [varchar](50) NOT NULL, [SuDataUniversity] [varchar](50) NOT NULL, [SuDataSchool] [varchar](50) NOT NULL, [SuDataEmployer] [varchar](50) NOT NULL, [SuDataEmployerPrevious] [varchar](50) NOT NULL, [SuDataDateOfBirth] [varchar](50) NOT NULL, [SuDataYearOfBirth] [varchar](50) NOT NULL, [SuDataAge] [varchar](50) NOT NULL, [SuSettingPrivacyVisibilityDateOfBirth] [varchar](50) NOT NULL, [SuDataPhoto] [varchar](50) NOT NULL, [SuRetrieveFriends] [varchar](50) NOT NULL, [SuInviteFriends] [varchar](50) NOT NULL, [SuSecurityAntiPhishing] [varchar](50) NOT NULL, [SuPrivacySeals] [varchar](500) NULL, [SuPageCount] [tinyint] NULL, [SuNotes] [varchar](2000) NULL, [UsLogonSslSupported] [varchar](10) NOT NULL, [UsLogonSslDefault] [dbo].[YesNo] NOT NULL, [UsLogonSslOffered] [dbo].[YesNo] NOT NULL, [UsLogonSslCertificateOk] [dbo].[YesNoNa] NOT NULL, [UsLogonLifetime] [varchar](10) NOT NULL, [UsLogonUserNameRemembered] [varchar](10) NOT NULL, [UsLogoutPossible] [dbo].[YesNo] NOT NULL, [UsPhishingTips] [dbo].[YesNo] NOT NULL, [UsProfilePubliclyViewable] [dbo].[DefaultYesNo] NOT NULL, [UsProfileGloballyViewable] [dbo].[DefaultYesNo] NOT NULL, [USProfileNetworkViewable] [dbo].[DefaultYesNo] NOT NULL, [UsProfileFOFViewable] [dbo].[DefaultYesNo] NOT NULL, [UsProfileFriendViewable] [dbo].[DefaultYesNo] NOT NULL, [UsProfileBlockable] [dbo].[YesNo] NOT NULL, [UsFriendLists] [dbo].[MandatoryYesNo] NOT NULL, [UsStatusViewable] [dbo].[DefaultYesNo] NOT NULL, [UsLocationViewable] [dbo].[DefaultYesNo] NOT NULL, [UsProfileViewsListes] [dbo].[DefaultYesNo] NOT NULL, [UsEmailCAPTCHA] [dbo].[YesNo] NOT NULL, [UsProfileLineItemACL] [dbo].[YesNo] NOT NULL, [UsProfileNumLineItems] [smallint] NULL, [UsProfileAudienceView] [dbo].[YesNoNa] NOT NULL, [UsProfileSearchable] [dbo].[DefaultYesNo] NOT NULL, [UsDemographicSearch] [dbo].[YesNo] NOT NULL, [UsSearchViewSettings] [dbo].[YesNo] NOT NULL, [UsFriendRemovable] [dbo].[YesNoSecretly] NOT NULL, [UsNumPrivacySettingsPages] [tinyint] NULL, [UsNumPrivacySettingItems] [tinyint] NULL, [UsPrivacySettingsHelp] [dbo].[YesNo] NULL, [UsMessaging] [dbo].[YesNo] NOT NULL, [UsMessagingACL] [dbo].[DefaultYesNoNa] NOT NULL, [UsMessagingBlockable] [dbo].[YesNoNa] NOT NULL, [UsProfileComments] [dbo].[YesNo] NOT NULL, [UsProfileCommentsACL] [tinyint] NOT NULL, [UsProfileCommentsRemovable] [dbo].[YesNoNa] NOT NULL, [UsMediaUpload] [dbo].[YesNo] NOT NULL, [UsMediaACL] [tinyint] NOT NULL, [UsMediaTagging] [dbo].[YesNoNa] NOT NULL, [UsMediaTagRequireApproval] [dbo].[DefaultYesNo] NOT NULL, [UsU18Separation] [dbo].[YesNo] NOT NULL, [UsU18CanView] [dbo].[YesNoNa] NOT NULL, [UsU18CanFriendRequest] [dbo].[YesNoNa] NOT NULL, [UsParentalControls] [dbo].[YesNoNa] NOT NULL, [UsApplicationsExist] [dbo].[YesNo] NOT NULL, [UsApplicationsRestrictable] [dbo].[DefaultYesNo] NOT NULL, [UsBadgesAllowed] [dbo].[YesNo] NOT NULL, [UsExportAllowed] [dbo].[YesNo] NOT NULL, [UsOpenIDProvided] [dbo].[YesNo] NOT NULL, [UsOpenIDAccepted] [dbo].[YesNo] NOT NULL, [UsEmailControllable] [dbo].[YesNo] NOT NULL, [UsNumEmailSettings] [tinyint] NULL, [UsEmailsContainLinks] [dbo].[YesNoNa] NOT NULL, [UsPrivacyTips] [dbo].[YesNo] NOT NULL, [UsSafetyTips] [dbo].[YesNo] NOT NULL, [UsParentTips] [dbo].[YesNo] NOT NULL, [UsPrivacySuites] [dbo].[YesNo] NOT NULL, [UsNumSuites] [tinyint] NULL, [UsReportUsers] [dbo].[YesNo] NOT NULL, [UsReportAbuse] [dbo].[YesNo] NOT NULL, [UsHotlineProvided] [dbo].[YesNo] NOT NULL, [UsCanDeleteAccount] [tinyint] NOT NULL, [UsCanEditBaseData] [dbo].[YesNo] NOT NULL, [UsEventStreamProfile] [tinyint] NOT NULL, [UsEventStreamGlobal] [tinyint] NOT NULL, [UsEventStreamGlobalRestrictions] [varchar](10) NOT NULL, [UsNotes] [varchar](max) NULL, [PpPresent] [dbo].[YesNo] NOT NULL, [PpDated] [dbo].[YesNo] NOT NULL, [PpRequiresJavaScript] [dbo].[YesNo] NOT NULL, [PpWordCount] [smallint] NULL, [PpCanZoom] [dbo].[YesNo] NOT NULL, [PpValidatedMobile] [tinyint] NULL, [PpDurableUrl] [dbo].[YesNo] NOT NULL, [PpPrintable] [dbo].[YesNo] NOT NULL, [PpSavable] [dbo].[YesNo] NULL, [PpStructured] [dbo].[YesNo] NOT NULL, [PpContainsOperatorEmailAddress] [dbo].[YesNo] NOT NULL, [PpContainsOperatorPostalAddress] [dbo].[YesNo] NOT NULL, [PpContainsSeal] [dbo].[YesNo] NOT NULL, [PpVauntsGoodPrivacyPractice] [dbo].[YesNo] NULL, [PpDisputeResolution] [dbo].[YesNo] NOT NULL, [PpDisputeResolver] [varchar](max) NULL, [PpEUSafeHarborParticipant] [dbo].[YesNo] NOT NULL, [PpSpecifiesNationalLawsObeyed] [dbo].[YesNo] NOT NULL, [PpLawsObeyed] [varchar](2000) NULL, [PpDataLocations] [varchar](2000) NULL, [PpIPAddressStored] [dbo].[YesNoUnspecified] NOT NULL, [PpBrowserDataStored] [dbo].[YesNoUnspecified] NOT NULL, [PpExternalDataCollected] [dbo].[YesNoUnspecified] NOT NULL, [PpDataSharedWithThirdParties] [dbo].[YesNoUnspecified] NOT NULL, [PpDataAnonymisedForThirdParties] [dbo].[YesNoNaUnspecified] NOT NULL, [PpDataSharedWithSearchEngine] [dbo].[YesNoUnspecified] NOT NULL, [PpDataSharedLawEnforcement] [dbo].[YesNoUnspecified] NOT NULL, [PpThirdPartyAdvertisers] [dbo].[YesNoUnspecified] NOT NULL, [PpThirdPartyAdCookies] [dbo].[YesNoNaUnspecified] NOT NULL, [PpDataRetentionPeriodSpecified] [dbo].[YesNo] NOT NULL, [PpDataRetentionPeriod] [varchar](max) NULL, [PpDataRetentionPeriodNotes] [varchar](max) NULL, [PpCanDeleteData] [tinyint] NULL, [PpMustNotifyOfChanges] [tinyint] NULL, [PpChangesTakeEffect] [smallint] NULL, [PpHtmlSource] [varchar](200) NULL, [PpScreenshot] [varchar](200) NULL, [TcMinimumAge] [smallint] NULL, [ImOriginalPrivacyScore] [float] NULL, [ImDataCollectionScore] AS ([dbo].[DataCollectionScore]([ID],(2),(2),(1),(0.5))), [ImDataCollectionScoreRelativeToSample] AS ([dbo].[DataCollectionScoreRelativeToSample]([ID])), [ImFunctionalityScore] AS ([dbo].[FunctionalityScore]([ID])), [ImPrivacyControlScore] AS ([dbo].[PrivacyControlScore]([ID])), [ImPrivacyPolicyScore] AS ([dbo].[PrivacyPolicyScore]([ID])), [ImPrivacyScoreRaw] AS ([dbo].[PrivacyScoreRaw]([ID])), [ImPrivacyScoreRelativeToSample] AS ([dbo].[PrivacyScoreRelativeToSample]([ID])), CONSTRAINT [PK_Sites] PRIMARY KEY CLUSTERED ( [ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ALTER TABLE [dbo].[Sites] WITH CHECK ADD CONSTRAINT [CK_Sites_BaIsCommercial] CHECK (([BaIsCommercial]=(1) OR [BaIsCommercial]=(0) OR [BaIsCommercial]=(201))) ALTER TABLE [dbo].[Sites] CHECK CONSTRAINT [CK_Sites_BaIsCommercial] ALTER TABLE [dbo].[Sites] WITH CHECK ADD CONSTRAINT [CK_Sites_BaIsProfitable] CHECK (([BaIsProfitable]=(1) OR [BaIsProfitable]=(0) OR [BaIsProfitable]=(201))) ALTER TABLE [dbo].[Sites] CHECK CONSTRAINT [CK_Sites_BaIsProfitable] ALTER TABLE [dbo].[Sites] WITH CHECK ADD CONSTRAINT [CK_Sites_BaLanguagesSwitchable] CHECK (([BaLanguagesSwitchable]=(1) OR [BaLanguagesSwitchable]=(0))) ALTER TABLE [dbo].[Sites] CHECK CONSTRAINT [CK_Sites_BaLanguagesSwitchable] ALTER TABLE [dbo].[Sites] WITH CHECK ADD CONSTRAINT [CK_Sites_SuCaptchaSecurity] CHECK (([SuCaptchaSecurity]='trivial' OR [SuCaptchaSecurity]='normal')) ALTER TABLE [dbo].[Sites] CHECK CONSTRAINT [CK_Sites_SuCaptchaSecurity] ALTER TABLE [dbo].[Sites] WITH CHECK ADD CONSTRAINT [CK_Sites_SuImprintLinkPosition] CHECK (([SuImprintLinkPosition]='n/a' OR [SuImprintLinkPosition]='header' OR [SuImprintLinkPosition]='footer' OR [SuImprintLinkPosition]='near checkbox')) ALTER TABLE [dbo].[Sites] CHECK CONSTRAINT [CK_Sites_SuImprintLinkPosition] ALTER TABLE [dbo].[Sites] WITH CHECK ADD CONSTRAINT [CK_Sites_SuSslCertificateOk] CHECK (([SuSslCertificateOk]=(202) OR [SuSslCertificateOk]=(0) OR [SuSslCertificateOk]=(1))) ALTER TABLE [dbo].[Sites] CHECK CONSTRAINT [CK_Sites_SuSslCertificateOk] ALTER TABLE [dbo].[Sites] WITH CHECK ADD CONSTRAINT [CK_Sites_SuSslSupported] CHECK (([SuSslSupported]='full' OR [SuSslSupported]='post-only' OR [SuSslSupported]='get-only' OR [SuSslSupported]='no')) ALTER TABLE [dbo].[Sites] CHECK CONSTRAINT [CK_Sites_SuSslSupported] ALTER TABLE [dbo].[Sites] WITH CHECK ADD CONSTRAINT [CK_Sites_SuTermsLinkPosition] CHECK (([SuTermsLinkPosition]='n/a' OR [SuTermsLinkPosition]='header' OR [SuTermsLinkPosition]='footer' OR [SuTermsLinkPosition]='near checkbox')) ALTER TABLE [dbo].[Sites] CHECK CONSTRAINT [CK_Sites_SuTermsLinkPosition] ALTER TABLE [dbo].[Sites] ADD CONSTRAINT [DF_Sites_SuP3PcompactEnabled] DEFAULT ((0)) FOR [SuP3PcompactEnabled] ALTER TABLE [dbo].[Sites] ADD CONSTRAINT [DF_Sites_SuP3PcompactCorrect] DEFAULT ((202)) FOR [SuP3PcompactCorrect]