create rule ZeroToOne as @Z >=0 AND @Z <=1 GO create rule PercentByte as @P >= 0 AND @P <= 100 GO setuser N'dbo' GO EXEC sp_addtype N'Percent', N'tinyint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'ShipID', N'int', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'ZeroToOne', N'real', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_bindrule N'[dbo].[ZeroToOne]', N'[ZeroToOne]' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'AccessCode', N'char (32)', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'AttachpointID', N'smallint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'BuyableGroupID', N'tinyint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'Description', N'char (200)', N'null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'EffectID', N'smallint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'ExpendableID', N'smallint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'FileName', N'char (12)', N'null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'GlobalAttributeID', N'smallint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'HitPoints', N'smallint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'MapID', N'smallint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'Name', N'char (24)', N'null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'PartID', N'smallint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'PercentByte', N'tinyint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[PercentByte]' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'ProjectileID', N'smallint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'SectorID', N'smallint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'ShipTypeID', N'smallint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'SortOrderType', N'int', N'null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'SoundID', N'smallint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'StationClassID', N'tinyint', N'not null' GO setuser GO setuser N'dbo' GO EXEC sp_addtype N'TechBits', N'char (256)', N'null' GO setuser GO CREATE TABLE [dbo].[AccessCodes] ( [AccessCode] [AccessCode] NOT NULL , [Status] [smallint] NOT NULL , [CodeReassignedFrom] [AccessCode] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[AccessCodeUsage] ( [AccessCode] [AccessCode] NOT NULL , [ZoneName] [Name] NOT NULL , [FirstUsage] [smalldatetime] NOT NULL , [LastUsage] [smalldatetime] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Chaff] ( [ExpendableID] [ExpendableID] NOT NULL , [Strength] [real] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharacterInfo] ( [CharacterID] [int] NOT NULL , [CharacterName] [Name] NOT NULL , [Description] [Description] NOT NULL , [CreationDate] [datetime] NOT NULL , [CanCheat] [bit] NOT NULL , [ICQID] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Constants] ( [PointsWin] [float] NOT NULL , [PointsWarpsSpotted] [float] NOT NULL , [PointsAsteroidsSpotted] [float] NOT NULL , [PointsMinerKills] [float] NOT NULL , [PointsBuilderKills] [float] NOT NULL , [PointsLayerKills] [float] NOT NULL , [PointsBaseKills] [float] NOT NULL , [PointsBaseCaptures] [float] NOT NULL , [PointsTechsRecovered] [float] NOT NULL , [PointsPlayerKills] [float] NOT NULL , [PointsDeaths] [float] NOT NULL , [PointsPilotBaseKills] [float] NOT NULL , [PointsPilotBaseCaptures] [float] NOT NULL , [PointsFlagCapture] [float] NOT NULL , [PointsArtifactCapture] [float] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Drones] ( [DroneID] [smallint] NOT NULL , [Name] [char] (16) NOT NULL , [Description] [Description] NOT NULL , [Price] [int] NOT NULL , [ShipTypeID] [ShipTypeID] NOT NULL , [TechBitsRequired] [TechBits] NOT NULL , [TechBitsEffect] [TechBits] NOT NULL , [SecondsToBuild] [smallint] NOT NULL , [DroneType] [int] NOT NULL , [GroupID] [BuyableGroupID] NULL , [SortOrder] [SortOrderType] NULL , [ExpendableID] [ExpendableID] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Effects] ( [EffectID] [EffectID] NOT NULL , [Description] [Description] NOT NULL , [FileSound] [FileName] NOT NULL , [IsLooping] [bit] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Events] ( [EventID] [int] IDENTITY (1, 1) NOT NULL , [Event] [int] NOT NULL , [DateTime] [datetime] NULL , [ComputerName] [varchar] (15) NOT NULL , [Subject] [int] NOT NULL , [SubjectName] [varchar] (32) NOT NULL , [Context] [varchar] (24) NOT NULL , [ObjectRef] [varchar] (4000) NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Expendables] ( [ExpendableID] [ExpendableID] NOT NULL , [Price] [int] NOT NULL , [FileModel] [FileName] NOT NULL , [Name] [Name] NOT NULL , [Description] [Description] NOT NULL , [TechBitsRequired] [TechBits] NOT NULL , [TechBitsEffect] [TechBits] NOT NULL , [SignatureShip] [real] NOT NULL , [Mass] [real] NOT NULL , [LoadTime] [real] NOT NULL , [LifeSpan] [real] NOT NULL , [SignatureInstance] [real] NOT NULL , [HitPoints] [HitPoints] NOT NULL , [FileModelInstance] [FileName] NOT NULL , [FileTextureInstance] [FileName] NOT NULL , [Radius] [real] NOT NULL , [RateRotation] [real] NOT NULL , [percentRed] [PercentByte] NOT NULL , [percentGreen] [PercentByte] NOT NULL , [percentBlue] [PercentByte] NOT NULL , [percentAlpha] [PercentByte] NOT NULL , [PRIcon] [FileName] NULL , [PRIconInstance] [FileName] NULL , [PartMask] [smallint] NOT NULL , [DefenseType] [tinyint] NOT NULL , [SecondsToBuild] [smallint] NOT NULL , [GroupID] [BuyableGroupID] NULL , [ExpendableSize] [smallint] NOT NULL , [Capabilities] [smallint] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[GameResults] ( [GameID] [char] (17) NOT NULL , [Name] [char] (64) NOT NULL , [WinningTeam] [Name] NOT NULL , [WinningTeamID] [smallint] NOT NULL , [IsGoalConquest] [bit] NOT NULL , [IsGoalCountdown] [bit] NOT NULL , [IsGoalTeamKills] [bit] NOT NULL , [IsGoalProsperity] [bit] NOT NULL , [IsGoalArtifacts] [bit] NOT NULL , [IsGoalFlags] [bit] NOT NULL , [GoalConquest] [smallint] NOT NULL , [GoalCountdown] [int] NOT NULL , [GoalTeamKills] [smallint] NOT NULL , [GoalProsperity] [real] NOT NULL , [GoalArtifacts] [smallint] NOT NULL , [GoalFlags] [smallint] NOT NULL , [Duration] [int] NOT NULL , [StartTime] AS (dateadd(second,(((-[Duration]))),[EndTime])) , [EndTime] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[GlobalAttributes] ( [GlobalAttributeID] [GlobalAttributeID] NOT NULL , [MaxSpeed] [smallint] NOT NULL , [Thrust] [smallint] NOT NULL , [TurnRate] [smallint] NOT NULL , [TurnTorque] [smallint] NOT NULL , [MaxArmorStation] [smallint] NOT NULL , [ArmorRegenerationStation] [smallint] NOT NULL , [MaxShieldStation] [smallint] NOT NULL , [ShieldRegenerationStation] [smallint] NOT NULL , [MaxArmorShip] [smallint] NOT NULL , [MaxShieldShip] [smallint] NOT NULL , [ShieldRegenerationShip] [smallint] NOT NULL , [ScanRange] [smallint] NOT NULL , [Signature] [smallint] NOT NULL , [MaxEnergy] [smallint] NOT NULL , [SpeedAmmo] [smallint] NOT NULL , [LifespanEnergy] [smallint] NOT NULL , [TurnRateMissile] [smallint] NOT NULL , [MiningRate] [smallint] NOT NULL , [MiningYield] [smallint] NOT NULL , [RipcordTime] [smallint] NOT NULL , [DamageGuns] [smallint] NOT NULL , [DamageMissiles] [smallint] NOT NULL , [DevelopmentCost] [smallint] NOT NULL , [DevelopmentTime] [smallint] NOT NULL , [MiningCapacity] [smallint] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[InvitationLists] ( [ListID] [int] NOT NULL , [TeamIndex] [tinyint] NOT NULL , [SubjectName] [Name] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Maps] ( [MapID] [smallint] NOT NULL , [MapName] [Name] NULL , [FileName] [FileName] NOT NULL , [Description] [Description] NULL , [IsTrainingOnly] [bit] NOT NULL , [IsUserOption] [bit] NOT NULL , [NumTeams] [tinyint] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Medals] ( [MedalID] [int] NOT NULL , [MedalName] [char] (48) NOT NULL , [MedalDescription] [Description] NOT NULL , [MedalSortOrder] [int] NOT NULL , [MedalBitmap] [char] (12) NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[MineInstances] ( [MapID] [MapID] NOT NULL , [MineID] [smallint] NOT NULL , [MineTypeID] [smallint] NOT NULL , [SideID] [tinyint] NOT NULL , [SectorID] [SectorID] NOT NULL , [LocationX] [real] NOT NULL , [LocationY] [real] NOT NULL , [LocationZ] [real] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[PartTypes] ( [PartTypeID] [tinyint] NOT NULL , [PartTypeName] [char] (16) NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[PlayerResults] ( [GameID] [char] (17) NOT NULL , [TeamID] [smallint] NOT NULL , [Name] [Name] NOT NULL , [PlayerKills] [smallint] NOT NULL , [BuilderKills] [smallint] NOT NULL , [LayerKills] [smallint] NOT NULL , [MinerKills] [smallint] NOT NULL , [BaseKills] [smallint] NOT NULL , [BaseCaptures] [smallint] NOT NULL , [PilotBaseKills] [smallint] NOT NULL , [PilotBaseCaptures] [smallint] NOT NULL , [Deaths] [smallint] NOT NULL , [Ejections] [smallint] NOT NULL , [Rescues] [smallint] NOT NULL , [Flags] [smallint] NOT NULL , [Artifacts] [smallint] NOT NULL , [TechsRecovered] [smallint] NOT NULL , [AlephsSpotted] [smallint] NOT NULL , [AsteroidsSpotted] [smallint] NOT NULL , [CombatRating] [real] NOT NULL , [Score] [real] NOT NULL , [TimePlayed] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[ProbeInstances] ( [MapID] [MapID] NOT NULL , [ProbeID] [smallint] NOT NULL , [ProbeTypeID] [smallint] NOT NULL , [SideID] [tinyint] NOT NULL , [SectorID] [SectorID] NOT NULL , [LocationX] [real] NOT NULL , [LocationY] [real] NOT NULL , [LocationZ] [real] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Projectiles] ( [ProjectileID] [ProjectileID] NOT NULL , [TimeDuration] [smallint] NOT NULL , [HitPointsInflict] [real] NOT NULL , [DamageType] [tinyint] NOT NULL , [FileModel] [FileName] NOT NULL , [FileTexture] [FileName] NOT NULL , [SpeedMax] [smallint] NOT NULL , [Acceleration] [smallint] NOT NULL , [Size_cm] [smallint] NOT NULL , [RadiusBlast_m] [tinyint] NOT NULL , [percentPenetration] [PercentByte] NOT NULL , [RateRotation] [real] NOT NULL , [IsAbsoluteSpeed] [bit] NOT NULL , [IsDirectional] [bit] NOT NULL , [IsExplodeOnExpire] [bit] NOT NULL , [IsProximityFuse] [bit] NOT NULL , [percentRed] [PercentByte] NOT NULL , [percentGreen] [PercentByte] NOT NULL , [percentBlue] [PercentByte] NOT NULL , [percentAlpha] [PercentByte] NOT NULL , [BlastPower] [real] NOT NULL , [BlastRadius] [real] NOT NULL , [WidthOverHeight] [real] NOT NULL , [PRIcon] [FileName] NULL , [AmbientSound] [SoundID] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[PSSInstances] ( [AccessCode] [AccessCode] NOT NULL , [TrackingID] [char] (20) NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Ranks] ( [Rank] [smallint] NOT NULL , [CivID] [smallint] NULL , [Requirement] [int] NOT NULL , [Name] [Name] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[ShipClasses] ( [ShipClassID] [smallint] NOT NULL , [ShipClassName] [Name] NOT NULL , [ShipClassDescription] [Description] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Squads] ( [SquadID] [int] NOT NULL , [SquadName] [Name] NOT NULL , [Score] [smallint] NOT NULL , [Wins] [smallint] NOT NULL , [Losses] [smallint] NOT NULL , [OwnershipLog] [varchar] (500) NOT NULL , [CivID] [smallint] NULL , [GamesPlayed] [smallint] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[StationClasses] ( [StationClassID] [smallint] NOT NULL , [StationClassName] [Name] NOT NULL , [StationClassDescription] [Description] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[StationTypes] ( [StationTypeID] [smallint] NOT NULL , [Name] [Name] NOT NULL , [Model] [FileName] NOT NULL , [Description] [Description] NULL , [Radius] [real] NOT NULL , [HitPointsArmor] [HitPoints] NOT NULL , [HitPointsShield] [HitPoints] NOT NULL , [RateRegenShield] [real] NOT NULL , [RateRegenArmor] [real] NOT NULL , [Price] [int] NOT NULL , [Signature] [smallint] NOT NULL , [SecondsToBuild] [smallint] NOT NULL , [TechBitsLocal] [TechBits] NOT NULL , [TechBitsRequired] [TechBits] NOT NULL , [TechBitsEffect] [TechBits] NOT NULL , [UpgradeStationTypeID] [smallint] NULL , [Capabilities] [smallint] NOT NULL , [ScanRange] [real] NOT NULL , [AsteroidDiscountMask] [smallint] NOT NULL , [Income] [int] NOT NULL , [PRIcon] [FileName] NULL , [BuildLocationSound] [SoundID] NOT NULL , [ConstructorAttackedSound] [SoundID] NOT NULL , [ConstructorDestroyedSound] [SoundID] NOT NULL , [CompletedSound] [SoundID] NOT NULL , [ExteriorSound] [SoundID] NOT NULL , [InteriorSound] [SoundID] NOT NULL , [InteriorAlertSound] [SoundID] NOT NULL , [UnderAttackSound] [SoundID] NOT NULL , [CriticalSound] [SoundID] NOT NULL , [DestroyedSound] [SoundID] NOT NULL , [CapturedSound] [SoundID] NOT NULL , [EnemyDestroyedSound] [SoundID] NOT NULL , [EnemyCapturedSound] [SoundID] NOT NULL , [GroupID] [BuyableGroupID] NOT NULL , [ClassID] [StationClassID] NOT NULL , [SecondsToBuy] [smallint] NOT NULL , [DefenseTypeArmor] [tinyint] NOT NULL , [DefenseTypeShield] [tinyint] NOT NULL , [BuilderName] [Name] NULL , [ConstructionDroneTypeID] [smallint] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TeamResults] ( [GameID] [char] (17) NOT NULL , [TeamID] [smallint] NOT NULL , [Name] [Name] NOT NULL , [CivID] [smallint] NOT NULL , [Techs] [char] (100) NOT NULL , [PlayerKills] [smallint] NOT NULL , [BaseKills] [smallint] NOT NULL , [BaseCaptures] [smallint] NOT NULL , [Deaths] [smallint] NOT NULL , [Ejections] [smallint] NOT NULL , [Flags] [smallint] NOT NULL , [Artifacts] [smallint] NOT NULL , [ConquestPercent] [smallint] NOT NULL , [ProsperityPercentBought] [smallint] NOT NULL , [ProsperityPercentComplete] [smallint] NOT NULL , [TimeEndured] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TechBits] ( [BitID] [int] NOT NULL , [BitShortName] [char] (35) NOT NULL , [BitLongName] [char] (48) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TreasureInstances] ( [MapID] [MapID] NOT NULL , [Lifespan] [real] NOT NULL , [TreasureID] [smallint] NOT NULL , [LocationX] [real] NOT NULL , [LocationY] [real] NOT NULL , [LocationZ] [real] NOT NULL , [SectorID] [SectorID] NOT NULL , [Amount] [smallint] NOT NULL , [TreasureCode] [smallint] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TreasureSets] ( [TreasureSetName] [char] (40) NOT NULL , [TreasureSetID] [smallint] NOT NULL , [IsZoneOnly] [bit] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Developments] ( [Price] [int] NOT NULL , [LoadoutBitmap] [FileName] NOT NULL , [Name] [Name] NOT NULL , [Description] [Description] NOT NULL , [TechBitsRequired] [TechBits] NOT NULL , [TechBitsEffect] [TechBits] NOT NULL , [DevelopmentID] [smallint] NOT NULL , [SecondsToBuild] [smallint] NOT NULL , [GlobalAttributeID] [GlobalAttributeID] NULL , [PRIcon] [char] (12) NULL , [GroupID] [BuyableGroupID] NULL , [SortOrder] [SortOrderType] NULL , [CompletedSound] [SoundID] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Mines] ( [ExpendableID] [ExpendableID] NOT NULL , [PlacementRadius] [real] NOT NULL , [MunitionCount] [tinyint] NOT NULL , [Power] [real] NOT NULL , [DamageType] [tinyint] NOT NULL , [FileTextureMunition] [FileName] NOT NULL , [percentRedMunition] [PercentByte] NOT NULL , [percentGreenMunition] [PercentByte] NOT NULL , [percentBlueMunition] [PercentByte] NOT NULL , [percentAlphaMunition] [PercentByte] NOT NULL , [PRIconMunition] [FileName] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Missiles] ( [ExpendableID] [ExpendableID] NOT NULL , [Acceleration] [real] NOT NULL , [TurnRate] [real] NOT NULL , [InitialSpeed] [real] NOT NULL , [ArmTime] [real] NOT NULL , [LockTime] [real] NOT NULL , [ReadyTime] [real] NOT NULL , [Dispersion] [real] NOT NULL , [LockAngle] [real] NOT NULL , [Power] [real] NOT NULL , [DamageType] [tinyint] NOT NULL , [BlastPower] [real] NOT NULL , [BlastRadius] [real] NOT NULL , [IsDirectional] [bit] NOT NULL , [WidthOverHeight] [real] NOT NULL , [MaxLock] [real] NOT NULL , [ChaffResistance] [real] NOT NULL , [LaunchSound] [SoundID] NOT NULL , [FlightSound] [SoundID] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Parts] ( [PartID] [PartID] NOT NULL , [Price] [int] NOT NULL , [FileModel] [FileName] NOT NULL , [FileTexture] [FileName] NOT NULL , [Description] [Description] NOT NULL , [Name] [Name] NOT NULL , [PartTypeID] [tinyint] NOT NULL , [LastUpdated] [smalldatetime] NULL , [Mass] [smallint] NOT NULL , [dSignatureMounted] [smallint] NOT NULL , [dSignatureActive] [smallint] NOT NULL , [TechBitsRequired] [TechBits] NOT NULL , [TechBitsEffect] [TechBits] NOT NULL , [ChanceTreasure] [PercentByte] NOT NULL , [PRIcon] [FileName] NULL , [PartMask] [smallint] NOT NULL , [SuccessorPartID] [PartID] NULL , [InventoryLineMDL] [FileName] NOT NULL , [SortOrder] [SortOrderType] NULL , [SecondsToBuild] [smallint] NOT NULL , [GroupID] [BuyableGroupID] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Probes] ( [ExpendableID] [ExpendableID] NOT NULL , [ScanRange] [real] NOT NULL , [ProjectileID] [ProjectileID] NULL , [dtimeBurst] [smallint] NOT NULL , [accuracy] [PercentByte] NOT NULL , [dispersion] [float] NOT NULL , [AmbientSound] [SoundID] NOT NULL , [Ammo] [smallint] NOT NULL , [dtRipcord] [tinyint] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[SectorInfo] ( [MapID] [MapID] NOT NULL , [SectorID] [SectorID] NOT NULL , [SectorName] [Name] NOT NULL , [LightX] [real] NOT NULL , [LightY] [real] NOT NULL , [LightZ] [real] NOT NULL , [LightColor] [int] NOT NULL , [StarsCount] [smallint] NOT NULL , [DebrisCount] [smallint] NOT NULL , [starSeed] [int] NOT NULL , [ScreenX] [real] NOT NULL , [ScreenY] [real] NOT NULL , [PosterFile] [FileName] NULL , [PlanetFile] [FileName] NULL , [PlanetSinLatitude] [PercentByte] NULL , [PlanetLongitude] [PercentByte] NULL , [PlanetRadius] [tinyint] NULL , [IsHomeSector] [bit] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Afterburners] ( [PartID] [PartID] NOT NULL , [BurnRate] [real] NOT NULL , [MaxThrust] [real] NOT NULL , [HullThrustMultiplier] [real] NOT NULL , [rateOn] [real] NOT NULL , [rateOff] [real] NOT NULL , [InteriorSound] [SoundID] NOT NULL , [ExteriorSound] [SoundID] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[AlephInstances] ( [AlephID] [int] NOT NULL , [MapID] [MapID] NOT NULL , [SectorID] [SectorID] NOT NULL , [LocationX] [real] NOT NULL , [LocationY] [real] NOT NULL , [LocationZ] [real] NOT NULL , [TargetAlephID] [int] NOT NULL , [RotationR] [real] NOT NULL , [ForwardX] [real] NOT NULL , [ForwardY] [real] NULL , [ForwardZ] [real] NULL , [Radius] [smallint] NOT NULL , [Signature] [real] NOT NULL , [Texture] [FileName] NOT NULL , [PRIcon] [FileName] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Ammo] ( [PartID] [PartID] NOT NULL , [Qty] [smallint] NOT NULL , [AmmoType] [tinyint] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Asteroids] ( [MapID] [MapID] NOT NULL , [SectorID] [SectorID] NOT NULL , [AsteroidID] [smallint] NOT NULL , [Ore] [real] NOT NULL , [AbilitiesMask] [smallint] NOT NULL , [HitPoints] [HitPoints] NOT NULL , [Radius] [smallint] NOT NULL , [FileModel] [FileName] NOT NULL , [FileTexture] [FileName] NULL , [FileIcon] [FileName] NOT NULL , [Mass] [real] NOT NULL , [Signature] [real] NOT NULL , [LocationX] [real] NOT NULL , [LocationY] [real] NOT NULL , [LocationZ] [real] NOT NULL , [UpX] [real] NULL , [UpY] [real] NULL , [UpZ] [real] NULL , [ForwardX] [real] NULL , [ForwardY] [real] NULL , [ForwardZ] [real] NULL , [RotationAx] [real] NULL , [RotationAy] [real] NULL , [RotationAz] [real] NULL , [RotationR] [real] NULL , [Name] [Name] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Civs] ( [CivID] [smallint] NOT NULL , [Name] [Name] NOT NULL , [Description] [Description] NOT NULL , [EscapePodShipTypeID] [ShipTypeID] NOT NULL , [TechBitsCiv] [char] (800) NOT NULL , [StartingGlobalAttributeID] [GlobalAttributeID] NULL , [InitialStationTypeID] [smallint] NOT NULL , [IconName] [FileName] NOT NULL , [StationHUD] [FileName] NOT NULL , [PreferredPartID15] [PartID] NULL , [PreferredPartID14] [PartID] NULL , [PreferredPartID13] [PartID] NULL , [PreferredPartID12] [PartID] NULL , [PreferredPartID11] [PartID] NULL , [PreferredPartID10] [PartID] NULL , [PreferredPartID9] [PartID] NULL , [PreferredPartID8] [PartID] NULL , [PreferredPartID7] [PartID] NULL , [PreferredPartID6] [PartID] NULL , [PreferredPartID5] [PartID] NULL , [PreferredPartID4] [PartID] NULL , [PreferredPartID3] [PartID] NULL , [PreferredPartID2] [PartID] NULL , [PreferredPartID1] [PartID] NULL , [TechBitsCivNoDev] [char] (800) NOT NULL , [BonusMoney] [real] NOT NULL , [IncomeMoney] [real] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Cloaks] ( [PartID] [PartID] NOT NULL , [EnergyConsumption] [real] NOT NULL , [MaxCloaking] [real] NOT NULL , [OnRate] [real] NOT NULL , [OffRate] [real] NOT NULL , [EngageSound] [SoundID] NOT NULL , [DisengageSound] [SoundID] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Magazines] ( [PartID] [smallint] NOT NULL , [Amount] [smallint] NOT NULL , [ExpendableID] [ExpendableID] NOT NULL , [SuccessorPartID] [PartID] NULL , [LaunchCount] [smallint] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[PosterInstances] ( [PosterID] [int] NOT NULL , [PosterName] [Name] NOT NULL , [Texture] [FileName] NOT NULL , [MapID] [MapID] NOT NULL , [SectorID] [SectorID] NOT NULL , [Radius] [int] NOT NULL , [LocationX] [int] NOT NULL , [LocationY] [int] NOT NULL , [LocationZ] [int] NOT NULL , [Color] [int] NOT NULL , [Longitude] [smallint] NOT NULL , [Latitude] [smallint] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Shields] ( [PartID] [PartID] NOT NULL , [RegenRate] [real] NOT NULL , [HitPoints] [HitPoints] NOT NULL , [ActivateSound] [SoundID] NOT NULL , [DeactivateSound] [SoundID] NOT NULL , [DefenseType] [tinyint] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[ShipTypes] ( [Name] [char] (20) NOT NULL , [ShipTypeID] [ShipTypeID] NOT NULL , [Model] [FileName] NOT NULL , [Length] [real] NOT NULL , [Weight] [real] NOT NULL , [Price] [int] NOT NULL , [BaseMaxSpeed] [int] NOT NULL , [BaseHitPoints] [HitPoints] NOT NULL , [BaseScannerRange] [int] NOT NULL , [Description] [Description] NOT NULL , [MaxRollRate] [real] NOT NULL , [MaxPitchRate] [real] NOT NULL , [MaxYawRate] [real] NOT NULL , [DriftRoll] [real] NOT NULL , [DriftPitch] [real] NOT NULL , [DriftYaw] [real] NOT NULL , [Acceleration] [real] NOT NULL , [AccelSideMultiplier] [real] NOT NULL , [AccelBackMultiplier] [real] NOT NULL , [BaseSignature] [smallint] NOT NULL , [EnergyMax] [real] NOT NULL , [RateRechargeEnergy] [real] NOT NULL , [TechBitsRequired] [TechBits] NOT NULL , [TechBitsEffect] [TechBits] NOT NULL , [Capabilities] [smallint] NOT NULL , [MaxAmmo] [smallint] NOT NULL , [MaxFuel] [smallint] NOT NULL , [PRIcon] [FileName] NOT NULL , [ShieldPartMask] [smallint] NOT NULL , [AfterburnerPartMask] [smallint] NOT NULL , [CloakPartMask] [smallint] NOT NULL , [MagazinePartMask] [smallint] NOT NULL , [DispenserPartMask] [smallint] NOT NULL , [ChaffPartMask] [smallint] NOT NULL , [MagazineCapacity] [smallint] NOT NULL , [DispenserCapacity] [smallint] NOT NULL , [ChaffCapacity] [smallint] NOT NULL , [SuccessorHullID] [ShipTypeID] NULL , [PilotHUD] [FileName] NOT NULL , [ObserverHUD] [FileName] NOT NULL , [InteriorSound] [SoundID] NOT NULL , [ExteriorSound] [SoundID] NOT NULL , [ThrustInteriorSound] [SoundID] NOT NULL , [ThrustExteriorSound] [SoundID] NOT NULL , [TurnInteriorSound] [SoundID] NOT NULL , [TurnExteriorSound] [SoundID] NOT NULL , [SortOrder] [SortOrderType] NULL , [PreferredPartID14] [PartID] NULL , [PreferredPartID13] [PartID] NULL , [PreferredPartID12] [PartID] NULL , [PreferredPartID11] [PartID] NULL , [PreferredPartID10] [PartID] NULL , [PreferredPartID9] [PartID] NULL , [PreferredPartID8] [PartID] NULL , [PreferredPartID7] [PartID] NULL , [PreferredPartID6] [PartID] NULL , [PreferredPartID5] [PartID] NULL , [PreferredPartID4] [PartID] NULL , [PreferredPartID3] [PartID] NULL , [PreferredPartID2] [PartID] NULL , [PreferredPartID1] [PartID] NULL , [DefenseType] [tinyint] NOT NULL , [SecondsToBuild] [smallint] NOT NULL , [GroupID] [BuyableGroupID] NULL , [RipcordSpeed] [real] NOT NULL , [ECM] [real] NOT NULL , [RipcordCost] [real] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[StationInstances] ( [MapID] [MapID] NOT NULL , [StationID] [smallint] NOT NULL , [Name] [Name] NOT NULL , [SideID] [tinyint] NOT NULL , [SectorID] [SectorID] NOT NULL , [LocationX] [real] NOT NULL , [LocationY] [real] NOT NULL , [LocationZ] [real] NOT NULL , [RotationAx] [real] NOT NULL , [RotationAy] [real] NOT NULL , [RotationAz] [real] NOT NULL , [RotationR] [real] NOT NULL , [ForwardX] [real] NOT NULL , [ForwardY] [real] NOT NULL , [ForwardZ] [real] NOT NULL , [UpX] [real] NOT NULL , [UpY] [real] NOT NULL , [UpZ] [real] NOT NULL , [StationTypeID] [smallint] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TreasureChances] ( [ObjectID] [smallint] NOT NULL , [Chance] [tinyint] NOT NULL , [TreasureSetID] [smallint] NOT NULL , [TreasureCode] [tinyint] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Weapons] ( [PartID] [PartID] NOT NULL , [dTimeBurstShots] [smallint] NOT NULL , [dTimeReady] [smallint] NOT NULL , [EnergyPerShot] [real] NOT NULL , [ProjectileID1] [ProjectileID] NOT NULL , [cBulletsPerShot] [smallint] NOT NULL , [ActivateSound] [SoundID] NOT NULL , [SingleShotSound] [SoundID] NOT NULL , [BurstShotSound] [SoundID] NOT NULL , [Dispersion] [real] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[AttachPoints] ( [ShipTypeID] [ShipTypeID] NOT NULL , [AttachPointID] [AttachpointID] NOT NULL , [FrameName] [FileName] NOT NULL , [PartTypeID] [tinyint] NOT NULL , [FieldOfFire] [smallint] NOT NULL , [PartMask] [smallint] NOT NULL , [GunnerHUD] [FileName] NOT NULL , [InteriorSound] [SoundID] NOT NULL , [TurnSound] [SoundID] NOT NULL , [LocationAbreviation] [char] (8) NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[CharMedals] ( [CharacterID] [int] NOT NULL , [MedalID] [int] NOT NULL , [CivID] [smallint] NULL , [SpecificInfo] [char] (48) NULL ) GO CREATE TABLE [dbo].[CharStats] ( [CharacterID] [int] NOT NULL , [CharacterName] [Name] NOT NULL , [CivID] [smallint] NULL , [Rank] [smallint] NOT NULL , [Score] [real] NOT NULL , [MinutesPlayed] [int] NOT NULL , [BaseKills] [smallint] NOT NULL , [BaseCaptures] [smallint] NOT NULL , [Kills] [smallint] NOT NULL , [Rating] [smallint] NOT NULL , [GamesPlayed] [smallint] NOT NULL , [Deaths] [smallint] NOT NULL , [Wins] [smallint] NOT NULL , [Losses] [smallint] NOT NULL , [WinsCmd] [smallint] NOT NULL , [LossesCmd] [smallint] NOT NULL , [LastPlayed] [smalldatetime] NOT NULL ) ON [PRIMARY] GO CREATE CLUSTERED INDEX [IX_CharMedals] ON [dbo].[CharMedals]([CharacterID], [CivID]) ON [PRIMARY] GO ALTER TABLE [dbo].[AccessCodes] WITH NOCHECK ADD CONSTRAINT [DF_AccessCodes_Status] DEFAULT (0) FOR [Status], CONSTRAINT [PK_AccessCodes] PRIMARY KEY NONCLUSTERED ( [AccessCode] ) ON [PRIMARY] GO ALTER TABLE [dbo].[AccessCodeUsage] WITH NOCHECK ADD CONSTRAINT [DF_AccessCodeUsage_FirstUsage] DEFAULT (getdate()) FOR [FirstUsage], CONSTRAINT [DF_AccessCodeUsage_LastUsage] DEFAULT (getdate()) FOR [LastUsage], CONSTRAINT [PK_AccessCodeUsage] PRIMARY KEY NONCLUSTERED ( [AccessCode], [ZoneName] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Chaff] WITH NOCHECK ADD CONSTRAINT [DF_Chaff_Strength] DEFAULT (1) FOR [Strength], CONSTRAINT [PK_Chaff] PRIMARY KEY NONCLUSTERED ( [ExpendableID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharacterInfo] WITH NOCHECK ADD CONSTRAINT [DF_CharacterInfo_Description] DEFAULT ('') FOR [Description], CONSTRAINT [DF__character__creat__0F8D3381] DEFAULT (getdate()) FOR [CreationDate], CONSTRAINT [DF_CharacterInfo_CanCheat] DEFAULT (0) FOR [CanCheat], CONSTRAINT [PK_CharacterInfo] PRIMARY KEY NONCLUSTERED ( [CharacterID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Drones] WITH NOCHECK ADD CONSTRAINT [DF_Drones_TechBitsRequired] DEFAULT (0) FOR [TechBitsRequired], CONSTRAINT [DF_Drones_TechBitsEffect] DEFAULT (0) FOR [TechBitsEffect], CONSTRAINT [DF_Drones_SecondsToBuild] DEFAULT (30) FOR [SecondsToBuild], CONSTRAINT [DF_Drones_GroupID] DEFAULT (0) FOR [GroupID], CONSTRAINT [PK_Drones] PRIMARY KEY NONCLUSTERED ( [DroneID] ) ON [PRIMARY] , CONSTRAINT [CK_Drones] CHECK ([Price] > 0 or [DroneType] = 6) GO ALTER TABLE [dbo].[Effects] WITH NOCHECK ADD CONSTRAINT [PK_Effects] PRIMARY KEY NONCLUSTERED ( [EffectID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Events] WITH NOCHECK ADD CONSTRAINT [DF_Events_DateTime] DEFAULT (getdate()) FOR [DateTime] GO ALTER TABLE [dbo].[Expendables] WITH NOCHECK ADD CONSTRAINT [DF_Expendables_TechBitsRequired] DEFAULT ('') FOR [TechBitsRequired], CONSTRAINT [DF_Expendables_TechBitsEffect] DEFAULT ('') FOR [TechBitsEffect], CONSTRAINT [DF_Expendable_ShipSignatureMod] DEFAULT (1) FOR [SignatureShip], CONSTRAINT [DF_Expendables_PartSize] DEFAULT (0) FOR [PartMask], CONSTRAINT [DF_Expendables_SecondsToBuild] DEFAULT (0) FOR [SecondsToBuild], CONSTRAINT [DF_Expendables_GroupID] DEFAULT (0) FOR [GroupID], CONSTRAINT [PK_Expendable] PRIMARY KEY NONCLUSTERED ( [ExpendableID] ) ON [PRIMARY] , CONSTRAINT [CK_Expendables] CHECK ([Price] >= 0 and [Mass] > 0 and [Radius] > 0 and [LoadTime] >= 0 and [LifeSpan] >= 0 and [HitPoints] > 0) GO ALTER TABLE [dbo].[GameResults] WITH NOCHECK ADD CONSTRAINT [DF__GameResul__EndTi__36B12243] DEFAULT (getdate()) FOR [EndTime] GO ALTER TABLE [dbo].[GlobalAttributes] WITH NOCHECK ADD CONSTRAINT [DF_GlobalAttributes_MaxSpeed] DEFAULT (0) FOR [MaxSpeed], CONSTRAINT [DF_GlobalAttributes_Thrust] DEFAULT (0) FOR [Thrust], CONSTRAINT [DF_GlobalAttributes_TurnRate] DEFAULT (0) FOR [TurnRate], CONSTRAINT [DF_GlobalAttributes_TurnTorque] DEFAULT (0) FOR [TurnTorque], CONSTRAINT [DF_GlobalAttributes_MaxArmorStation] DEFAULT (0) FOR [MaxArmorStation], CONSTRAINT [DF_GlobalAttributes_ArmorRegenerationStation] DEFAULT (0) FOR [ArmorRegenerationStation], CONSTRAINT [DF_GlobalAttributes_MaxShieldStation] DEFAULT (0) FOR [MaxShieldStation], CONSTRAINT [DF_GlobalAttributes_ShieldRegenerationStation] DEFAULT (0) FOR [ShieldRegenerationStation], CONSTRAINT [DF_GlobalAttributes_MaxArmorShip] DEFAULT (0) FOR [MaxArmorShip], CONSTRAINT [DF_GlobalAttributes_MaxShieldShip] DEFAULT (0) FOR [MaxShieldShip], CONSTRAINT [DF_GlobalAttributes_ShieldRegenerationShip] DEFAULT (0) FOR [ShieldRegenerationShip], CONSTRAINT [DF_GlobalAttributes_ScanRange] DEFAULT (0) FOR [ScanRange], CONSTRAINT [DF_GlobalAttributes_Signature] DEFAULT (0) FOR [Signature], CONSTRAINT [DF_GlobalAttributes_MaxEnergy] DEFAULT (0) FOR [MaxEnergy], CONSTRAINT [DF_GlobalAttributes_SpeedAmmo] DEFAULT (0) FOR [SpeedAmmo], CONSTRAINT [DF_GlobalAttributes_LifespanEnergy] DEFAULT (0) FOR [LifespanEnergy], CONSTRAINT [DF_GlobalAttributes_TurnRateMissile] DEFAULT (0) FOR [TurnRateMissile], CONSTRAINT [DF_GlobalAttributes_MiningRate] DEFAULT (0) FOR [MiningRate], CONSTRAINT [DF_GlobalAttributes_MiningYield] DEFAULT (0) FOR [MiningYield], CONSTRAINT [DF_GlobalAttributes_RipcordTime] DEFAULT (0) FOR [RipcordTime], CONSTRAINT [DF_GlobalAttributes_DamageGuns] DEFAULT (0) FOR [DamageGuns], CONSTRAINT [DF_GlobalAttributes_DamageMissiles] DEFAULT (0) FOR [DamageMissiles], CONSTRAINT [DF_GlobalAttributes_DevelopmentCost] DEFAULT (0) FOR [DevelopmentCost], CONSTRAINT [DF_GlobalAttributes_DevelopmentTime] DEFAULT (0) FOR [DevelopmentTime], CONSTRAINT [DF_GlobalAttributes_MiningCapacity] DEFAULT (0) FOR [MiningCapacity], CONSTRAINT [PK_GlobalAttributes] PRIMARY KEY NONCLUSTERED ( [GlobalAttributeID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Maps] WITH NOCHECK ADD CONSTRAINT [DF_Maps_IsUserMap] DEFAULT (0) FOR [IsUserOption], CONSTRAINT [DF_Maps_NumTeams] DEFAULT (2) FOR [NumTeams], CONSTRAINT [PK_Maps] PRIMARY KEY NONCLUSTERED ( [MapID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Medals] WITH NOCHECK ADD CONSTRAINT [DF_Medals_MedalRanking] DEFAULT (100000) FOR [MedalSortOrder], CONSTRAINT [PK_Medals] PRIMARY KEY NONCLUSTERED ( [MedalID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[PartTypes] WITH NOCHECK ADD CONSTRAINT [PK_PartTypes] PRIMARY KEY NONCLUSTERED ( [PartTypeID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Projectiles] WITH NOCHECK ADD CONSTRAINT [DF_Projectiles_DamageType] DEFAULT (0) FOR [DamageType], CONSTRAINT [DF_Projectiles_BlastPower] DEFAULT (0) FOR [BlastPower], CONSTRAINT [DF_Projectiles_BlastRadius] DEFAULT (0) FOR [BlastRadius], CONSTRAINT [DF_Projectiles_WidthOverHeight] DEFAULT (1) FOR [WidthOverHeight], CONSTRAINT [DF_Projectiles_AmbientSound] DEFAULT ((-1)) FOR [AmbientSound], CONSTRAINT [PK_Projectiles] PRIMARY KEY NONCLUSTERED ( [ProjectileID] ) ON [PRIMARY] , CONSTRAINT [CK_Projectiles] CHECK ([Size_cm] > 0 and [TimeDuration] >= 200 and [SpeedMax] <= 5000 and [SpeedMax] > 50) GO ALTER TABLE [dbo].[PSSInstances] WITH NOCHECK ADD CONSTRAINT [PK_PSSInstances] PRIMARY KEY NONCLUSTERED ( [AccessCode], [TrackingID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Ranks] WITH NOCHECK ADD CONSTRAINT [DF_Ranks_Requirement] DEFAULT (0) FOR [Requirement], CONSTRAINT [DF_Ranks_Name] DEFAULT ('') FOR [Name], CONSTRAINT [IX_Ranks] UNIQUE NONCLUSTERED ( [CivID], [Rank] ) ON [PRIMARY] GO ALTER TABLE [dbo].[ShipClasses] WITH NOCHECK ADD CONSTRAINT [PK_ShipClasses] PRIMARY KEY NONCLUSTERED ( [ShipClassID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Squads] WITH NOCHECK ADD CONSTRAINT [DF_Squads_Score] DEFAULT (0) FOR [Score], CONSTRAINT [DF_Squads_Wins] DEFAULT (0) FOR [Wins], CONSTRAINT [DF_Squads_Losses] DEFAULT (0) FOR [Losses], CONSTRAINT [DF_Squads_OwnershipLog] DEFAULT ('') FOR [OwnershipLog], CONSTRAINT [DF_Squads_GamesPlayed] DEFAULT (0) FOR [GamesPlayed], CONSTRAINT [PK_Squads] PRIMARY KEY NONCLUSTERED ( [SquadID] ) ON [PRIMARY] , CONSTRAINT [IX_Squads_Name] UNIQUE NONCLUSTERED ( [SquadName] ) ON [PRIMARY] GO ALTER TABLE [dbo].[StationClasses] WITH NOCHECK ADD CONSTRAINT [PK_StationClasses] PRIMARY KEY NONCLUSTERED ( [StationClassID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[StationTypes] WITH NOCHECK ADD CONSTRAINT [PK_StationTypes] PRIMARY KEY NONCLUSTERED ( [StationTypeID] ) ON [PRIMARY] , CONSTRAINT [CK_StationTypes] CHECK ([Radius] > 0 and [HitPointsArmor] > 0 and [HitPointsShield] >= 0 and [RateRegenArmor] >= 0 and [RateRegenShield] >= 0 and [Price] > 0 and [Signature] >= 0 and [SecondsToBuild] > 0 and [ScanRange] >= 0 and [UpgradeStationTypeID] < [StationTypeID]) GO ALTER TABLE [dbo].[TechBits] WITH NOCHECK ADD CONSTRAINT [PK_TechBits] PRIMARY KEY NONCLUSTERED ( [BitShortName] ) ON [PRIMARY] , CONSTRAINT [IX_TechBits_ID] UNIQUE NONCLUSTERED ( [BitID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TreasureSets] WITH NOCHECK ADD CONSTRAINT [DF_TreasureSets_IsZoneOnly] DEFAULT (0) FOR [IsZoneOnly], CONSTRAINT [PK_TreasureSets] PRIMARY KEY NONCLUSTERED ( [TreasureSetID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Developments] WITH NOCHECK ADD CONSTRAINT [DF_Developments_Price] DEFAULT (1000) FOR [Price], CONSTRAINT [DF_Development_TechBitsReq] DEFAULT (0) FOR [TechBitsRequired], CONSTRAINT [DF_Developments_TechBitsEffect] DEFAULT (0) FOR [TechBitsEffect], CONSTRAINT [DF_Developments_SecondsToBuild] DEFAULT (30) FOR [SecondsToBuild], CONSTRAINT [DF_Developments_GroupID] DEFAULT (1) FOR [GroupID], CONSTRAINT [PK_Developments] PRIMARY KEY NONCLUSTERED ( [DevelopmentID] ) ON [PRIMARY] , CONSTRAINT [CK_Developments] CHECK ([Price] > 0) GO ALTER TABLE [dbo].[Mines] WITH NOCHECK ADD CONSTRAINT [DF_Mines_DamageType] DEFAULT (0) FOR [DamageType], CONSTRAINT [PK_Mines] PRIMARY KEY NONCLUSTERED ( [ExpendableID] ) ON [PRIMARY] , CONSTRAINT [CK_Mines] CHECK ([MunitionCount] <= 50) GO ALTER TABLE [dbo].[Missiles] WITH NOCHECK ADD CONSTRAINT [DF_Missiles_BlastPower] DEFAULT (0) FOR [BlastPower], CONSTRAINT [DF_Missiles_BlastRadius] DEFAULT (0) FOR [BlastRadius], CONSTRAINT [DF_Missiles_WidthOverHeight] DEFAULT (1) FOR [WidthOverHeight], CONSTRAINT [DF_Missiles_MaxLock] DEFAULT (1) FOR [MaxLock], CONSTRAINT [DF_Missiles_ChaffResistance] DEFAULT (1) FOR [ChaffResistance], CONSTRAINT [DF_Missiles_LaunchSound] DEFAULT (500) FOR [LaunchSound], CONSTRAINT [DF_Missiles_FlightSound] DEFAULT (540) FOR [FlightSound], CONSTRAINT [PK_Missiles] PRIMARY KEY NONCLUSTERED ( [ExpendableID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Parts] WITH NOCHECK ADD CONSTRAINT [DF_Parts_dSignatureMounted] DEFAULT (0) FOR [dSignatureMounted], CONSTRAINT [DF_Parts_dSignatureActive] DEFAULT (0) FOR [dSignatureActive], CONSTRAINT [DF_Parts_TechBitsRequired] DEFAULT (0) FOR [TechBitsRequired], CONSTRAINT [DF_Parts_TechBitsEffect] DEFAULT (0) FOR [TechBitsEffect], CONSTRAINT [DF_Parts_ChangeTreasure] DEFAULT (5) FOR [ChanceTreasure], CONSTRAINT [DF_Parts_PartSize] DEFAULT (0) FOR [PartMask], CONSTRAINT [DF_Parts_InventoryLineMDL] DEFAULT ('invdefault') FOR [InventoryLineMDL], CONSTRAINT [DF_Parts_SecondsToBuild] DEFAULT (0) FOR [SecondsToBuild], CONSTRAINT [DF_Parts_GroupID] DEFAULT (0) FOR [GroupID], CONSTRAINT [PK_Parts] PRIMARY KEY NONCLUSTERED ( [PartID] ) ON [PRIMARY] , CONSTRAINT [CK_Parts] CHECK ([Price] >= 0 and [Mass] > 0) GO ALTER TABLE [dbo].[Probes] WITH NOCHECK ADD CONSTRAINT [DF_Probes_dtimeBurst] DEFAULT (200) FOR [dtimeBurst], CONSTRAINT [DF_Probes_accuracy] DEFAULT (50) FOR [accuracy], CONSTRAINT [DF_Probes_dispersion] DEFAULT (0.01) FOR [dispersion], CONSTRAINT [DF_Probes_AmbientSound] DEFAULT (720) FOR [AmbientSound], CONSTRAINT [DF_Probes_Ammo] DEFAULT (0) FOR [Ammo], CONSTRAINT [DF_Probes_dtRipcord] DEFAULT (255) FOR [dtRipcord], CONSTRAINT [PK_Probes] PRIMARY KEY NONCLUSTERED ( [ExpendableID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[SectorInfo] WITH NOCHECK ADD CONSTRAINT [DF_SectorInfo_LightX] DEFAULT (1) FOR [LightX], CONSTRAINT [DF_SectorInfo_LightY] DEFAULT (1) FOR [LightY], CONSTRAINT [DF_SectorInfo_LightZ] DEFAULT (1) FOR [LightZ], CONSTRAINT [DF_SectorInfo_LightColor] DEFAULT (10000000) FOR [LightColor], CONSTRAINT [DF_SectorInfo_StarsCount] DEFAULT (500) FOR [StarsCount], CONSTRAINT [DF_SectorInfo_DebrisCount] DEFAULT (500) FOR [DebrisCount], CONSTRAINT [DF_SectorInfo_starSeed] DEFAULT (1) FOR [starSeed], CONSTRAINT [PK_SectorInfo] PRIMARY KEY NONCLUSTERED ( [MapID], [SectorID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Afterburners] WITH NOCHECK ADD CONSTRAINT [DF_Afterburners_InteriorSound] DEFAULT (680) FOR [InteriorSound], CONSTRAINT [DF_Afterburners_ExteriorSound] DEFAULT (700) FOR [ExteriorSound], CONSTRAINT [PK_Afterburners] PRIMARY KEY NONCLUSTERED ( [PartID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[AlephInstances] WITH NOCHECK ADD CONSTRAINT [DF_AlephInstances_RotationR] DEFAULT (1) FOR [RotationR], CONSTRAINT [DF_AlephInstances_Radius] DEFAULT (90) FOR [Radius], CONSTRAINT [DF_AlephInstances_Signature] DEFAULT (1.0) FOR [Signature], CONSTRAINT [DF_AlephInstances_Texture] DEFAULT ('plnt42') FOR [Texture], CONSTRAINT [DF_AlephInstances_PRIcon] DEFAULT ('aleph') FOR [PRIcon], CONSTRAINT [PK_AlephInstances] PRIMARY KEY NONCLUSTERED ( [AlephID], [MapID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Ammo] WITH NOCHECK ADD CONSTRAINT [DF_Ammo_AmmoType] DEFAULT (0) FOR [AmmoType], CONSTRAINT [PK_Ammo] PRIMARY KEY NONCLUSTERED ( [PartID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Asteroids] WITH NOCHECK ADD CONSTRAINT [DF_Asteroids_FileModel] DEFAULT ('bgrnd03') FOR [FileModel], CONSTRAINT [DF_Asteroids_FileIcon] DEFAULT ('meteoricon') FOR [FileIcon], CONSTRAINT [PK_Asteroids] PRIMARY KEY NONCLUSTERED ( [MapID], [AsteroidID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Civs] WITH NOCHECK ADD CONSTRAINT [DF_Civs_TechBitsCiv] DEFAULT (0) FOR [TechBitsCiv], CONSTRAINT [DF_Civs_StationHUD] DEFAULT ('dialog') FOR [StationHUD], CONSTRAINT [DF_Civs_TechBitsCivNoDev] DEFAULT (0) FOR [TechBitsCivNoDev], CONSTRAINT [DF_Civs_BonusMoney] DEFAULT (0.0) FOR [BonusMoney], CONSTRAINT [DF_Civs_IncomeMoney] DEFAULT (0.0) FOR [IncomeMoney], CONSTRAINT [PK_Civs] PRIMARY KEY NONCLUSTERED ( [CivID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Cloaks] WITH NOCHECK ADD CONSTRAINT [DF_Cloaks_EngageSound] DEFAULT (640) FOR [EngageSound], CONSTRAINT [DF_Cloaks_DisengageSound] DEFAULT (660) FOR [DisengageSound], CONSTRAINT [PK_Cloaks] PRIMARY KEY NONCLUSTERED ( [PartID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Magazines] WITH NOCHECK ADD CONSTRAINT [PK_Magazines] PRIMARY KEY NONCLUSTERED ( [PartID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[PosterInstances] WITH NOCHECK ADD CONSTRAINT [PK_PosterInstances] PRIMARY KEY NONCLUSTERED ( [PosterID], [MapID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Shields] WITH NOCHECK ADD CONSTRAINT [DF_Shields_ActivateSound] DEFAULT (580) FOR [ActivateSound], CONSTRAINT [DF_Shields_DeactivateSound] DEFAULT (620) FOR [DeactivateSound], CONSTRAINT [PK_Shields] PRIMARY KEY NONCLUSTERED ( [PartID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[ShipTypes] WITH NOCHECK ADD CONSTRAINT [DF_ShipTypes_MaxRollRate] DEFAULT (50) FOR [MaxRollRate], CONSTRAINT [DF_ShipTypes_MaxPitchRate] DEFAULT (50) FOR [MaxPitchRate], CONSTRAINT [DF_ShipTypes_MaxYawRate] DEFAULT (50) FOR [MaxYawRate], CONSTRAINT [DF_ShipTypes_DriftRoll] DEFAULT (50) FOR [DriftRoll], CONSTRAINT [DF_ShipTypes_DriftPitch] DEFAULT (50) FOR [DriftPitch], CONSTRAINT [DF_ShipTypes_DriftYaw] DEFAULT (50) FOR [DriftYaw], CONSTRAINT [DF_ShipTypes_AccelSideMultiplier] DEFAULT (1) FOR [AccelSideMultiplier], CONSTRAINT [DF_ShipTypes_AccelBackMultiplier] DEFAULT (1) FOR [AccelBackMultiplier], CONSTRAINT [DF_ShipTypes_EnergyMax] DEFAULT (10) FOR [EnergyMax], CONSTRAINT [DF_ShipTypes_RateRecharg] DEFAULT (1) FOR [RateRechargeEnergy], CONSTRAINT [DF_ShipTypes_TechBitsRequired] DEFAULT (0) FOR [TechBitsRequired], CONSTRAINT [DF_ShipTypes_TechBitsEffect] DEFAULT (0) FOR [TechBitsEffect], CONSTRAINT [DF_ShipTypes_ShieldPartSize] DEFAULT (0) FOR [ShieldPartMask], CONSTRAINT [DF_ShipTypes_AfterburnerPartSize] DEFAULT ((-1)) FOR [AfterburnerPartMask], CONSTRAINT [DF_ShipTypes_CloakPartSize] DEFAULT (0) FOR [CloakPartMask], CONSTRAINT [DF_ShipTypes_MagazinePartSize] DEFAULT (0) FOR [MagazinePartMask], CONSTRAINT [DF_ShipTypes_DispenserPartSize] DEFAULT (0) FOR [DispenserPartMask], CONSTRAINT [DF_ShipTypes_ChaffPartSize] DEFAULT (0) FOR [ChaffPartMask], CONSTRAINT [DF_ShipTypes_MagazineCapacity] DEFAULT (0) FOR [MagazineCapacity], CONSTRAINT [DF_ShipTypes_DispenserCapacity] DEFAULT (0) FOR [DispenserCapacity], CONSTRAINT [DF_ShipTypes_ChaffCapacity] DEFAULT (0) FOR [ChaffCapacity], CONSTRAINT [DF_ShipTypes_PilotHUD] DEFAULT ('dialog') FOR [PilotHUD], CONSTRAINT [DF_ShipTypes_ObserverHUD] DEFAULT ('dialog') FOR [ObserverHUD], CONSTRAINT [DF_ShipTypes_InteriorSound] DEFAULT (140) FOR [InteriorSound], CONSTRAINT [DF_ShipTypes_ExteriorSound] DEFAULT (180) FOR [ExteriorSound], CONSTRAINT [DF_ShipTypes_ThrustInteriorSound] DEFAULT (220) FOR [ThrustInteriorSound], CONSTRAINT [DF_ShipTypes_ThrustExteriorSound] DEFAULT (260) FOR [ThrustExteriorSound], CONSTRAINT [DF_ShipTypes_TurnInteriorSound] DEFAULT (300) FOR [TurnInteriorSound], CONSTRAINT [DF_ShipTypes_TurnExteriorSound] DEFAULT (340) FOR [TurnExteriorSound], CONSTRAINT [DF_ShipTypes_SecondsToBuild] DEFAULT (0) FOR [SecondsToBuild], CONSTRAINT [DF_ShipTypes_GroupID] DEFAULT (0) FOR [GroupID], CONSTRAINT [PK_ShipStats] PRIMARY KEY NONCLUSTERED ( [ShipTypeID] ) ON [PRIMARY] , CONSTRAINT [CK_ShipTypes] CHECK ([Length] > 0 and [Weight] > 0 and [Price] >= 0 and [BaseMaxSpeed] > 0 and [BaseHitPoints] > 0 and [BaseScannerRange] > 0 and [MaxRollRate] >= 0 and [MaxPitchRate] >= 0 and [DriftRoll] > 0 and [DriftPitch] > 0 and [DriftYaw] > 0 and [Acceleration] > 0 and [BaseSignature] >= 10 and [EnergyMax] >= 0 and [RateRechargeEnergy] >= 0 and [MaxAmmo] >= 0 and [MaxFuel] >= 0) GO ALTER TABLE [dbo].[StationInstances] WITH NOCHECK ADD CONSTRAINT [DF_StationInstances_RotationAx] DEFAULT (0) FOR [RotationAx], CONSTRAINT [DF_StationInstances_RotationAy] DEFAULT (1) FOR [RotationAy], CONSTRAINT [DF_StationInstances_RotationAz] DEFAULT (0) FOR [RotationAz], CONSTRAINT [DF_StationInstances_RotationR] DEFAULT (0) FOR [RotationR], CONSTRAINT [PK_StationInstances] PRIMARY KEY NONCLUSTERED ( [MapID], [StationID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TreasureChances] WITH NOCHECK ADD CONSTRAINT [DF_TreasureChances_Chance] DEFAULT (0) FOR [Chance], CONSTRAINT [DF_TreasureChances_TreasureCode] DEFAULT (1) FOR [TreasureCode], CONSTRAINT [PK_TreasureChances] PRIMARY KEY NONCLUSTERED ( [TreasureSetID], [ObjectID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Weapons] WITH NOCHECK ADD CONSTRAINT [DF_WeaponTypes_RateOfFire] DEFAULT (0) FOR [dTimeBurstShots], CONSTRAINT [DF_Weapons_TimeBetween] DEFAULT (0) FOR [EnergyPerShot], CONSTRAINT [DF_WeaponTypes_ProjectileID1] DEFAULT (0) FOR [ProjectileID1], CONSTRAINT [DF_Weapons_ActivateSound] DEFAULT (80) FOR [ActivateSound], CONSTRAINT [DF_Weapons_BurstShotSound] DEFAULT ((-1)) FOR [BurstShotSound], CONSTRAINT [DF_Weapons_Dispersion] DEFAULT (0.005) FOR [Dispersion], CONSTRAINT [PK_WeaponTypes] PRIMARY KEY NONCLUSTERED ( [PartID] ) ON [PRIMARY] , CONSTRAINT [CK_Weapons] CHECK ([dTimeBurstShots] > 0 and [dTimeReady] > 0 and [EnergyPerShot] >= 0 and [cBulletsPerShot] >= 0 and [Dispersion] >= 0 and [Dispersion] <= 0.15) GO ALTER TABLE [dbo].[AttachPoints] WITH NOCHECK ADD CONSTRAINT [DF_AttachPoints_FieldOfFire] DEFAULT (120) FOR [FieldOfFire], CONSTRAINT [DF_AttachPoints_PartSize] DEFAULT (0) FOR [PartMask], CONSTRAINT [DF_AttachPoints_GunnerHUD] DEFAULT ('dialog') FOR [GunnerHUD], CONSTRAINT [DF_AttachPoints_InteriorSound] DEFAULT (140) FOR [InteriorSound], CONSTRAINT [DF_AttachPoints_TurnSound] DEFAULT (121) FOR [TurnSound], CONSTRAINT [DF_AttachPoints_LocationAbreviation] DEFAULT (' ') FOR [LocationAbreviation], CONSTRAINT [PK_AttachPoints] PRIMARY KEY NONCLUSTERED ( [ShipTypeID], [AttachPointID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CharStats] WITH NOCHECK ADD CONSTRAINT [DF_CharStats_Rank] DEFAULT (1) FOR [Rank], CONSTRAINT [DF_CharStats_Overall Rating] DEFAULT (0) FOR [Score], CONSTRAINT [DF_CharStats_MinutesPlayed] DEFAULT (0) FOR [MinutesPlayed], CONSTRAINT [DF_CharStats_BaseKills] DEFAULT (0) FOR [BaseKills], CONSTRAINT [DF_CharStats_BaseCaptures] DEFAULT (0) FOR [BaseCaptures], CONSTRAINT [DF_CharStats_Kills] DEFAULT (0) FOR [Kills], CONSTRAINT [DF_CharStats_Combat Rating] DEFAULT (1000) FOR [Rating], CONSTRAINT [DF_CharStats_GamesPlayed] DEFAULT (0) FOR [GamesPlayed], CONSTRAINT [DF_CharStats_Deaths] DEFAULT (0) FOR [Deaths], CONSTRAINT [DF_CharStats_Wins] DEFAULT (0) FOR [Wins], CONSTRAINT [DF_CharStats_Losses] DEFAULT (0) FOR [Losses], CONSTRAINT [DF_CharStats_WinsCmd] DEFAULT (0) FOR [WinsCmd], CONSTRAINT [DF_CharStats_LossesCmd] DEFAULT (0) FOR [LossesCmd], CONSTRAINT [IX_CharStats] UNIQUE NONCLUSTERED ( [CharacterID], [CivID] ) ON [PRIMARY] , CONSTRAINT [CK_CharStats] CHECK ([CivID] > 0) GO CREATE INDEX [IX_AccessCodes_ReassignedFrom] ON [dbo].[AccessCodes]([CodeReassignedFrom]) ON [PRIMARY] GO CREATE INDEX [IX_AccessCodeUsage_ZoneName] ON [dbo].[AccessCodeUsage]([ZoneName]) ON [PRIMARY] GO CREATE UNIQUE INDEX [IX_CharacterName] ON [dbo].[CharacterInfo]([CharacterName]) ON [PRIMARY] GO CREATE INDEX [IX_GameResults_GameID] ON [dbo].[GameResults]([GameID]) ON [PRIMARY] GO CREATE INDEX [IX_PlayerResults_GameID] ON [dbo].[PlayerResults]([GameID]) ON [PRIMARY] GO CREATE INDEX [IX_PlayerResults_Name] ON [dbo].[PlayerResults]([Name]) ON [PRIMARY] GO CREATE INDEX [IX_TeamResults_GameID] ON [dbo].[TeamResults]([GameID]) ON [PRIMARY] GO CREATE INDEX [IX_GameResults_EndTime] ON [dbo].[GameResults]([EndTime]) ON [PRIMARY] GO CREATE INDEX [IX_PSSInstances_AccessCode] ON [dbo].[PSSInstances]([AccessCode]) ON [PRIMARY] GO CREATE INDEX [IX_PSSInstances_TrackingID] ON [dbo].[PSSInstances]([TrackingID]) ON [PRIMARY] GO CREATE INDEX [IX_Ranks_Requirement] ON [dbo].[Ranks]([CivID], [Requirement]) ON [PRIMARY] GO CREATE INDEX [IX_Squads_Score] ON [dbo].[Squads]([Score]) ON [PRIMARY] GO CREATE INDEX [IX_Squads_Wins] ON [dbo].[Squads]([Wins]) ON [PRIMARY] GO CREATE INDEX [IX_Squads_Losses] ON [dbo].[Squads]([Losses]) ON [PRIMARY] GO CREATE INDEX [IX_AlephInstances_SectorID] ON [dbo].[AlephInstances]([SectorID]) ON [PRIMARY] GO CREATE INDEX [IX_Asteroids] ON [dbo].[Asteroids]([SectorID]) ON [PRIMARY] GO CREATE INDEX [IX_StationInstances] ON [dbo].[StationInstances]([SectorID]) ON [PRIMARY] GO CREATE INDEX [IX_TreasureChance_PartID] ON [dbo].[TreasureChances]([ObjectID]) ON [PRIMARY] GO CREATE INDEX [IX_TreasureChance_SetID] ON [dbo].[TreasureChances]([TreasureSetID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_GamesPlayed] ON [dbo].[CharStats]([CivID], [GamesPlayed], [CharacterID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_Deaths] ON [dbo].[CharStats]([CivID], [Deaths], [CharacterID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_Wins] ON [dbo].[CharStats]([CivID], [Wins], [CharacterID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_Losses] ON [dbo].[CharStats]([CivID], [Losses], [CharacterID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_WinsCmd] ON [dbo].[CharStats]([CivID], [WinsCmd], [CharacterID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_LossesCmd] ON [dbo].[CharStats]([CivID], [LossesCmd], [CharacterID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_Score] ON [dbo].[CharStats]([CivID], [Score], [CharacterID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_MinutesPlayed] ON [dbo].[CharStats]([CivID], [MinutesPlayed], [CharacterID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_BaseKills] ON [dbo].[CharStats]([CivID], [BaseKills], [CharacterID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_BaseCaptures] ON [dbo].[CharStats]([CivID], [BaseCaptures], [CharacterID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_Kills] ON [dbo].[CharStats]([CivID], [Kills], [CharacterID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_Rating] ON [dbo].[CharStats]([CivID], [Rating], [CharacterID]) ON [PRIMARY] GO CREATE INDEX [IX_CharStats_Name] ON [dbo].[CharStats]([CivID], [CharacterName], [CharacterID]) ON [PRIMARY] GO setuser N'dbo' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Expendables].[percentAlpha]' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Expendables].[percentBlue]' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Expendables].[percentGreen]' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Expendables].[percentRed]' GO setuser GO setuser N'dbo' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Projectiles].[percentAlpha]' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Projectiles].[percentBlue]' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Projectiles].[percentGreen]' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Projectiles].[percentPenetration]' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Projectiles].[percentRed]' GO setuser GO setuser N'dbo' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Mines].[percentAlphaMunition]' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Mines].[percentBlueMunition]' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Mines].[percentGreenMunition]' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Mines].[percentRedMunition]' GO setuser GO setuser N'dbo' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Parts].[ChanceTreasure]' GO setuser GO setuser N'dbo' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[Probes].[accuracy]' GO setuser GO setuser N'dbo' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[SectorInfo].[PlanetLongitude]' GO EXEC sp_bindrule N'[dbo].[PercentByte]', N'[SectorInfo].[PlanetSinLatitude]' GO setuser GO ALTER TABLE [dbo].[StationTypes] ADD CONSTRAINT [FK_StationTypes_Drones] FOREIGN KEY ( [ConstructionDroneTypeID] ) REFERENCES [dbo].[Drones] ( [DroneID] ), CONSTRAINT [FK_StationTypes_StationTypes] FOREIGN KEY ( [UpgradeStationTypeID] ) REFERENCES [dbo].[StationTypes] ( [StationTypeID] ) GO ALTER TABLE [dbo].[Developments] ADD CONSTRAINT [FK_Developments_GlobalAttributes] FOREIGN KEY ( [GlobalAttributeID] ) REFERENCES [dbo].[GlobalAttributes] ( [GlobalAttributeID] ) GO ALTER TABLE [dbo].[Mines] ADD CONSTRAINT [FK_Mines_Expendable] FOREIGN KEY ( [ExpendableID] ) REFERENCES [dbo].[Expendables] ( [ExpendableID] ) GO ALTER TABLE [dbo].[Missiles] ADD CONSTRAINT [FK_Missiles_Expendable] FOREIGN KEY ( [ExpendableID] ) REFERENCES [dbo].[Expendables] ( [ExpendableID] ) GO ALTER TABLE [dbo].[Parts] ADD CONSTRAINT [FK_Parts_Parts] FOREIGN KEY ( [SuccessorPartID] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Parts_PartTypes] FOREIGN KEY ( [PartTypeID] ) REFERENCES [dbo].[PartTypes] ( [PartTypeID] ) GO ALTER TABLE [dbo].[Probes] ADD CONSTRAINT [FK_Probes_Expendable] FOREIGN KEY ( [ExpendableID] ) REFERENCES [dbo].[Expendables] ( [ExpendableID] ), CONSTRAINT [FK_Probes_Projectiles] FOREIGN KEY ( [ProjectileID] ) REFERENCES [dbo].[Projectiles] ( [ProjectileID] ) GO ALTER TABLE [dbo].[SectorInfo] ADD CONSTRAINT [FK_SectorInfo_Maps] FOREIGN KEY ( [MapID] ) REFERENCES [dbo].[Maps] ( [MapID] ) GO ALTER TABLE [dbo].[Afterburners] ADD CONSTRAINT [FK_Afterburners_Parts] FOREIGN KEY ( [PartID] ) REFERENCES [dbo].[Parts] ( [PartID] ) GO ALTER TABLE [dbo].[AlephInstances] ADD CONSTRAINT [FK_AlephInstances_SectorInfo] FOREIGN KEY ( [MapID], [SectorID] ) REFERENCES [dbo].[SectorInfo] ( [MapID], [SectorID] ) GO ALTER TABLE [dbo].[Ammo] ADD CONSTRAINT [FK_Ammo_Parts] FOREIGN KEY ( [PartID] ) REFERENCES [dbo].[Parts] ( [PartID] ) GO ALTER TABLE [dbo].[Asteroids] ADD CONSTRAINT [FK_Asteroids_SectorInfo] FOREIGN KEY ( [MapID], [SectorID] ) REFERENCES [dbo].[SectorInfo] ( [MapID], [SectorID] ) GO ALTER TABLE [dbo].[Civs] ADD CONSTRAINT [FK_Civs_Parts] FOREIGN KEY ( [PreferredPartID1] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts1] FOREIGN KEY ( [PreferredPartID2] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts10] FOREIGN KEY ( [PreferredPartID11] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts11] FOREIGN KEY ( [PreferredPartID12] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts12] FOREIGN KEY ( [PreferredPartID13] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts13] FOREIGN KEY ( [PreferredPartID14] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts14] FOREIGN KEY ( [PreferredPartID15] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts2] FOREIGN KEY ( [PreferredPartID3] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts3] FOREIGN KEY ( [PreferredPartID4] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts4] FOREIGN KEY ( [PreferredPartID5] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts5] FOREIGN KEY ( [PreferredPartID6] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts6] FOREIGN KEY ( [PreferredPartID7] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts7] FOREIGN KEY ( [PreferredPartID8] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts8] FOREIGN KEY ( [PreferredPartID9] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Civs_Parts9] FOREIGN KEY ( [PreferredPartID10] ) REFERENCES [dbo].[Parts] ( [PartID] ) GO ALTER TABLE [dbo].[Cloaks] ADD CONSTRAINT [FK_Cloaks_Parts] FOREIGN KEY ( [PartID] ) REFERENCES [dbo].[Parts] ( [PartID] ) GO ALTER TABLE [dbo].[Magazines] ADD CONSTRAINT [FK_Magazines_Expendable] FOREIGN KEY ( [ExpendableID] ) REFERENCES [dbo].[Expendables] ( [ExpendableID] ), CONSTRAINT [FK_Magazines_Magazines] FOREIGN KEY ( [SuccessorPartID] ) REFERENCES [dbo].[Magazines] ( [PartID] ), CONSTRAINT [FK_Magazines_Parts] FOREIGN KEY ( [PartID] ) REFERENCES [dbo].[Parts] ( [PartID] ) GO ALTER TABLE [dbo].[PosterInstances] ADD CONSTRAINT [FK_PosterInstances_SectorInfo] FOREIGN KEY ( [MapID], [SectorID] ) REFERENCES [dbo].[SectorInfo] ( [MapID], [SectorID] ) GO ALTER TABLE [dbo].[Shields] ADD CONSTRAINT [FK_Shields_Parts] FOREIGN KEY ( [PartID] ) REFERENCES [dbo].[Parts] ( [PartID] ) GO ALTER TABLE [dbo].[ShipTypes] ADD CONSTRAINT [FK_ShipTypes_Parts] FOREIGN KEY ( [PreferredPartID1] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts1] FOREIGN KEY ( [PreferredPartID2] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts10] FOREIGN KEY ( [PreferredPartID11] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts11] FOREIGN KEY ( [PreferredPartID12] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts12] FOREIGN KEY ( [PreferredPartID13] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts13] FOREIGN KEY ( [PreferredPartID14] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts2] FOREIGN KEY ( [PreferredPartID3] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts3] FOREIGN KEY ( [PreferredPartID4] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts4] FOREIGN KEY ( [PreferredPartID5] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts5] FOREIGN KEY ( [PreferredPartID6] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts6] FOREIGN KEY ( [PreferredPartID7] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts7] FOREIGN KEY ( [PreferredPartID8] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts8] FOREIGN KEY ( [PreferredPartID9] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_Parts9] FOREIGN KEY ( [PreferredPartID10] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_ShipTypes_ShipTypes] FOREIGN KEY ( [SuccessorHullID] ) REFERENCES [dbo].[ShipTypes] ( [ShipTypeID] ) GO ALTER TABLE [dbo].[StationInstances] ADD CONSTRAINT [FK_StationInstances_SectorInfo] FOREIGN KEY ( [MapID], [SectorID] ) REFERENCES [dbo].[SectorInfo] ( [MapID], [SectorID] ) GO ALTER TABLE [dbo].[TreasureChances] ADD CONSTRAINT [FK_TreasureChance_Parts] FOREIGN KEY ( [ObjectID] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_TreasureChance_TreasureSets] FOREIGN KEY ( [TreasureSetID] ) REFERENCES [dbo].[TreasureSets] ( [TreasureSetID] ) GO ALTER TABLE [dbo].[Weapons] ADD CONSTRAINT [FK_Weapons_Parts] FOREIGN KEY ( [PartID] ) REFERENCES [dbo].[Parts] ( [PartID] ), CONSTRAINT [FK_Weapons_Projectiles] FOREIGN KEY ( [ProjectileID1] ) REFERENCES [dbo].[Projectiles] ( [ProjectileID] ) GO ALTER TABLE [dbo].[AttachPoints] ADD CONSTRAINT [FK_AttachPoints_ShipTypes] FOREIGN KEY ( [ShipTypeID] ) REFERENCES [dbo].[ShipTypes] ( [ShipTypeID] ) GO ALTER TABLE [dbo].[CharMedals] ADD CONSTRAINT [FK_CharMedals_CharacterInfo] FOREIGN KEY ( [CharacterID] ) REFERENCES [dbo].[CharacterInfo] ( [CharacterID] ), CONSTRAINT [FK_CharMedals_Civs] FOREIGN KEY ( [CivID] ) REFERENCES [dbo].[Civs] ( [CivID] ), CONSTRAINT [FK_CharMedals_Medals] FOREIGN KEY ( [MedalID] ) REFERENCES [dbo].[Medals] ( [MedalID] ) GO ALTER TABLE [dbo].[CharStats] ADD CONSTRAINT [FK_CharStats_CharacterInfo] FOREIGN KEY ( [CharacterID] ) REFERENCES [dbo].[CharacterInfo] ( [CharacterID] ), CONSTRAINT [FK_CharStats_Civs] FOREIGN KEY ( [CivID] ) REFERENCES [dbo].[Civs] ( [CivID] ) GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetCharSquads ( @characterID int ) As set nocount on create table #playerTeams ( vc_team_name varchar(30), i_status int, i_team_id int, i_secondary_status int ) insert into #playerTeams exec profile.profile.dbo.p_get_player_teams_aleg @characterID select rtrim(s.SquadName), i_status, i_team_id, i_secondary_status from #playerTeams p, Squads s where s.squadID = p.i_team_id GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure IsTechBitsSuperset ( @tb1 TechBits, /* Is THIS techbits fully superceded by ...*/ @tb2 TechBits, /* ...THIS techbits? */ @ok bit Output ) As set nocount on select @ok = 1 /* ok until we find a char that's not */ return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadAcceptJoinRequest ( @MemberID int, @SquadID int ) As set nocount on declare @ErrorMsg as varchar(128) declare @ErrorCode as int exec profile.profile.dbo.p_grant_petitions_aleg @SquadID, @MemberID, @ErrorCode output, @ErrorMsg output if @ErrorCode is null select 0 else select @ErrorCode return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadDemoteToMember ( @MemberID int, @SquadID int ) As set nocount on declare @ErrorMsg as varchar(128) declare @ErrorCode as int exec profile.profile.dbo.p_edit_team_member_status2_aleg @SquadID, @MemberID, 0, @ErrorCode output, @ErrorMsg output select @ErrorCode return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadDetailsPlayers ( @SquadID int ) As set nocount on CREATE TABLE #members ( [i_account_id] [int] NOT NULL , [vc_member_id] [varchar] (30) NOT NULL , [i_status] [int] NOT NULL , [i_secondary_status] [int] NULL , [dt_granted] [datetime] NOT NULL ) insert into #members exec profile.profile.dbo.p_get_team_members_aleg @SquadID select m.i_account_id, m.vc_member_id, m.i_status, m.i_secondary_status, m.dt_granted, c.rank from #members m, charstats c where c.characterid=m.i_account_id and c.civid is null drop table #members return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadGetDudeXSquads ( @characterID int ) As set nocount on create table #playerTeamsX ( vc_team_name varchar(30), i_status int, i_team_id int, i_secondary_status int ) insert #playerTeamsX exec profile.profile.dbo.p_get_player_teams_aleg @characterID select s.* from Squads s, #playerTeamsX p where s.squadID=p.i_team_ID return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadMakeJoinRequest ( @MemberID int, @SquadID int ) As set nocount on declare @ErrorMsg as varchar(128) declare @ErrorCode as int exec profile.profile.dbo.p_petition_team_aleg @SquadID, @MemberID, @ErrorCode output, @ErrorMsg output if @ErrorCode is null select 0 else select @ErrorCode return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadPromoteToASL ( @MemberID int, @SquadID int ) As set nocount on declare @ErrorMsg as varchar(128) declare @ErrorCode as int exec profile.profile.dbo.p_edit_team_member_status2_aleg @SquadID, @MemberID, 1, @ErrorCode output, @ErrorMsg output select @ErrorCode return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadRejectJoinRequest ( @MemberID int, @SquadID int ) As set nocount on declare @ErrorMsg as varchar(128) declare @ErrorCode as int exec profile.profile.dbo.p_deny_petitions_aleg @SquadID, @MemberID, @ErrorCode output, @ErrorMsg output if @ErrorCode is null select 0 else select @ErrorCode return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO Create Procedure CreateLotsOfRandomSquads ( @startingnumber int, @numsquads int ) As set nocount on declare @i int select @i=0 while (@i < @numsquads) begin declare @squadname char(12) declare @squadid int declare @score smallint declare @wins smallint declare @losses smallint select @wins=convert(smallint, 1000 * rand()) select @losses=convert(smallint, 1000 * rand()) select @score=1000 * @wins / (@wins + @losses) select @squadname='Random' + convert(char(6), @i + @startingnumber) insert into squads (SquadID, SquadName, Score, Wins, Losses) values (@i, @squadname, @score, @wins, @losses) select @i=@i+1 end return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure CreatePigsMachine (@szComputerName Name) As declare @szTemp Name declare @szNumber char(10) declare @i int select @i = 0 while @i < 30 BEGIN select @szNumber = convert(varchar, @i) if (@i < 10) select @szNumber = '0' + convert(varchar, @szNumber) if (@i < 100) select @szNumber = '0' + convert(varchar, @szNumber) select @szTemp = (rtrim(convert(varchar, @szComputerName)) + '-' + convert(varchar, @szNumber)) insert into characterinfo (CharacterName) values(@szTemp) select @i = @i + 1 END GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNewRank ( @CivID smallint, @Score int, @Rank smallint output /* IN: old rank, OUT: new rank */ ) As set nocount on declare @requiredScore int /* Advance rank if a) there is another rank to advance to, and b) reached the requirement for it */ select @requiredScore=Requirement from Ranks where (civid=@civid or (@civID is null and civid is null)) and Rank=@Rank+1 if (@requiredScore is not null) and @Score>=@requiredScore select @Rank=@Rank+1 return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure IsValidTechBits ( @TechBitsStr char(800), @fok bit OUTPUT ) As /* Determine whether every techbit specified is a valid techbit */ /* Assumption: The list of tech bits are space delimited, with only one space between each tech bit */ set nocount on select @fok = 0 /* assume failure until all tech bits have been verified */ declare @itb int declare @lenBits int declare @ispace int declare @tech char(40) /* sync up with size of short name in TechBits */ Select @itb=1 Select @lenbits=LEN(@TechBitsStr) + 1 /* zero-based */ while (@itb < @lenbits) begin select @ispace=charindex(' ', @TechBitsStr, @itb) /* find the next space */ if (@ispace=0) /* no space found, last token */ select @ispace=@lenbits+1 select @tech=SubString(@TechBitsStr, @itb, @ispace-@itb) if (Exists(Select * From TechBits Where BitShortName=@tech)) select @itb=@ispace+1 else return /* failure */ end Select @fok=1 return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadAddNewLeaderToLog ( @squadid int, @leadername name ) As set nocount on update squads set ownershiplog=convert(char(8), GetDate(), 1) + ';' + rtrim(@leadername) + ';' + cast(ownershiplog as varchar(465)) where squadid = @squadid return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadDetails ( @SquadID int ) As set nocount on -- todo include rank and civid create table #temp ( vc_team_id varchar(30), vc_member_id varchar(30), vc_description varchar(510), vc_favorite_game1 varchar(4), vc_favorite_game2 varchar(4), vc_favorite_game3 varchar(4), vc_favorite_game4 varchar(4), vc_favorite_game5 varchar(4), vc_favorite_link varchar(255), b_closed bit, b_award bit, i_team_id int, vc_edit_restrictions varchar(8), dt_created datetime ) insert #temp exec profile.profile.dbo.p_get_team_profile_aleg @SquadID select #temp.*, s.civid/*, s.rank*/ from #temp, Squads s where s.SquadID = @SquadID declare @c_rows as int select @c_rows = count(*) from #temp if @c_rows = 0 delete from squads where squadid = @squadID return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadEdit ( @SquadID int, @Description varchar(510), @URL varchar(255), @civID smallint ) As set nocount on select @Description = rtrim(@Description) select @URL = rtrim(@URL) declare @ErrorCode int declare @ErrorMsg varchar(128) exec profile.profile.dbo.p_edit_team_profile_aleg @SquadID, @Description, @URL, @ErrorCode output, @ErrorMsg output if @ErrorCode = 0 begin -- now update federation database update squads set civid = @civid where squadid = @squadid end select @ErrorCode, @ErrorMsg GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO Create Procedure SquadGetNearLosses ( @squadID int, -- If they specify squadID=-1, then they want the top @squadname char(30) = '' -- (only) if they specify a name, we need to look up the squadID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @Losses real declare @firstordinal int -- If they specify a name, then we need to look up the squadID if @squadname<>'' begin select @squadid=NULL select @squadid=squadid from squads where squadname=@squadname if @squadid is null begin -- Make sure we still create a result set of the expectant type select * from squads where (0=1) return end end -- If they specify squadID=-1, then they want the top if @squadID=-1 begin select @firstordinal=1 select top 35 @firstordinal as 'FirstOrdinal', * from squads order by Losses desc, squadid desc end else begin select @Losses=Losses from squads where squadID=@squadid -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from squads where (Losses>@Losses or (Losses=@Losses and squadid>=@squadID or @Losses is null)) select top 35 * into #higher from squads where (Losses>@Losses or (Losses=@Losses and squadid>=@squadID) or @Losses is null) order by Losses, squadid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 34 * into #lower from squads where (Losses<@Losses or (Losses=@Losses and squadid<@squadID)) order by Losses desc, squadid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by Losses desc, squadid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadGetNearNames ( @SquadID int, -- If they specify squadID=-1, then they want the top @SquadName Name = '' -- (only) if they specify a name, we need to look up the squadID (ignoring what was passed in) ) As set nocount on /* All the SquadGetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @firstordinal int -- If they specify a name, then we need to look up the squadID if @SquadName<>'' begin select @squadid=NULL select top 1 @squadid=squadid from squads where squadname >= @SquadName order by Squadname if @SquadId is null begin -- Make sure we still create a result set of the expectant type select * from squads s where (0=1) return end end -- If they specify SquadID=-1, then they want the top if @SquadID=-1 begin select @firstordinal=1 select top 35 @firstordinal as 'FirstOrdinal', * from squads order by SquadName end else begin if @SquadName = '' select @SquadName=SquadName from squads where SquadID=@Squadid -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=1 -- don't need to spend this cost, since ordinals for names don't mean much, but Currie wants it :-) select @firstordinal=count(*) from Squads where (SquadName<=@SquadName) select top 35 * into #higher from squads where SquadName<=@SquadName order by SquadName desc select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 34 * into #lower from squads where SquadName>@SquadName order by SquadName select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by SquadName drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO Create Procedure SquadGetNearScore ( @squadID int, -- If they specify squadID=-1, then they want the top @squadname char(30) = '' -- (only) if they specify a name, we need to look up the squadID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @Score real declare @firstordinal int -- If they specify a name, then we need to look up the squadID if @squadname<>'' begin select @squadid=NULL select @squadid=squadid from squads where squadname=@squadname if @squadid is null begin -- Make sure we still create a result set of the expectant type select * from squads where (0=1) return end end -- If they specify squadID=-1, then they want the top if @squadID=-1 begin select @firstordinal=1 select top 35 @firstordinal as 'FirstOrdinal', * from squads order by Score desc, squadid desc end else begin select @Score=Score from squads where squadID=@squadid -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from squads where (Score>@Score or (Score=@Score and squadid>=@squadID or @Score is null)) select top 35 * into #higher from squads where (Score>@Score or (Score=@Score and squadid>=@squadID) or @Score is null) order by Score, squadid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 34 * into #lower from squads where (Score<@Score or (Score=@Score and squadid<@squadID)) order by Score desc, squadid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by Score desc, squadid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO Create Procedure SquadGetNearWins ( @squadID int, -- If they specify squadID=-1, then they want the top @squadname char(30) = '' -- (only) if they specify a name, we need to look up the squadID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @Wins real declare @firstordinal int -- If they specify a name, then we need to look up the squadID if @squadname<>'' begin select @squadid=NULL select @squadid=squadid from squads where squadname=@squadname if @squadid is null begin -- Make sure we still create a result set of the expectant type select * from squads where (0=1) return end end -- If they specify squadID=-1, then they want the top if @squadID=-1 begin select @firstordinal=1 select top 35 @firstordinal as 'FirstOrdinal', * from squads order by Wins desc, squadid desc end else begin select @Wins=Wins from squads where squadID=@squadid -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from squads where (Wins>@Wins or (Wins=@Wins and squadid>=@squadID or @Wins is null)) select top 35 * into #higher from squads where (Wins>@Wins or (Wins=@Wins and squadid>=@squadID) or @Wins is null) order by Wins, squadid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 34 * into #lower from squads where (Wins<@Wins or (Wins=@Wins and squadid<@squadID)) order by Wins desc, squadid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by Wins desc, squadid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNearWinsCmd ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @charname Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @WinsCmd smallint declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @charname<>'' begin select @characterid=NULL select @characterid=characterid from characterinfo where charactername=@charname if @characterid is null begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where civid=@civID or (@civID is null and civid is null) order by civid desc, WinsCmd desc, cs.characterid desc end else begin select @WinsCmd=WinsCmd from charstats where CharacterID=@characterid And (civid=@civid or (@civID is null and civid is null)) -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from charstats where (WinsCmd>@WinsCmd or (WinsCmd=@WinsCmd and characterid>=@characterID or @WinsCmd is null)) and (civid=@civID or (@civID is null and civid is null)) select top 42 cs.* into #higher from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (WinsCmd>@WinsCmd or (WinsCmd=@WinsCmd and cs.characterid>=@characterID) or @WinsCmd is null) and (civid=@civID or (@civID is null and civid is null)) order by civid, WinsCmd, cs.characterid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 cs.* into #lower from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (WinsCmd<@WinsCmd or (WinsCmd=@WinsCmd and cs.characterid<@characterID)) and (civid=@civID or (@civID is null and civid is null)) order by civid desc, cs.WinsCmd desc, cs.characterid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by WinsCmd desc, characterid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadReportGame ( @squadIDWon int, @squadIDLost1 int, @squadIDLost2 int = -1, @squadIDLost3 int = -1, @squadIDLost4 int = -1, @squadIDLost5 int = -1 ) As set nocount on declare @scoreWinner smallint declare @scoreLoser smallint declare @k real select @scoreWinner = Score from Squads where SquadID=@squadIDWon select @scoreLoser = Score from Squads where SquadID=@squadIDLost1 select @scoreWinner select @scoreLoser select @k = (1.25 + (@scoreLoser - @scoreWinner) / 1000.0) / 50.0; select @k update Squads set GamesPlayed = GamesPlayed + 1, Wins = Wins+1, Score = @scoreWinner * (1 - @k) + @k * 1000 Where SquadID = @squadIDWon update Squads set GamesPlayed = GamesPlayed + 1, Losses = Losses + 1, Score = @scoreLoser * (1 - @k) Where SquadID = @squadIDLost1 return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO Create Procedure SyncZoneSquads As set nocount on Declare cur Cursor Local For Select characterID From CharacterInfo Open cur create table #playerTeams ( vc_team_name varchar(30), i_status int, i_team_id int, i_secondary_status int ) Declare curTeam Cursor Local For select i_team_id, vc_team_name from #playerTeams declare @characterID int declare @vc_team_name varchar(30) declare @i_status int declare @i_team_id int declare @i_secondary_status int Fetch Next From cur Into @characterID While @@FETCH_STATUS = 0 Begin insert into #playerTeams exec profile.profile.dbo.p_get_player_teams_aleg @characterID Open curTeam declare @teamID int declare @squadName varchar(30) Fetch Next From curTeam Into @i_team_id, @vc_team_name While @@FETCH_STATUS = 0 Begin if not exists (select * from Squads where SquadID=@i_team_id) insert into squads (squadid, squadname) values (@i_team_id, @vc_team_name) Fetch Next From curTeam Into @i_team_id, @vc_team_name End Close curTeam delete from #playerTeams Fetch Next From cur Into @characterID End Close cur return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadBootMember ( @MemberID int, @SquadID int ) As set nocount on declare @ErrorMsg as varchar(128) declare @ErrorCode as int declare @status as int declare @newowner as varchar(30) exec profile.profile.dbo.p_withdraw_team_aleg @SquadID, @MemberID, @status output, @newowner output, @ErrorCode output, @ErrorMsg output if @status = -1 delete from squads where squadid = @squadid else if @newowner is not null exec SquadAddNewLeaderToLog @SquadID, @newowner if (@status is null) select @ErrorCode, 0 else select @ErrorCode, @status -- status is -1 if team was destroyed because last person left return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadCancelJoinRequest ( @MemberID int, @SquadID int ) As set nocount on declare @ErrorMsg as varchar(128) declare @ErrorCode as int declare @status as int declare @newowner as varchar(30) exec profile.profile.dbo.p_withdraw_team_aleg @SquadID, @MemberID, @status output, @newowner output, @ErrorCode output, @ErrorMsg output -- this shouldn't ever happen, but let's be safe if @status = -1 delete from squads where squadid = @squadid else if @newowner is not null exec SquadAddNewLeaderToLog @SquadID, @newowner if @ErrorCode is null select 0 else select @ErrorCode return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadCreate ( @SquadName varchar(30), @Description varchar(510), @URL varchar(255), @OwnerID int, @civID smallint -- @SquadID int OUTPUT, -- @ErrorCode int OUTPUT, -- @ErrorMsg varchar(128) OUTPUT ) As set nocount on -- trim these down so we don't waste space select @squadname = rtrim(@squadname) select @Description = rtrim(@Description) select @URL = rtrim(@URL) declare @SquadID int declare @ErrorCode int declare @ErrorMsg varchar(128) exec profile.profile.dbo.p_create_team_profile_aleg @SquadName, @Description, @URL, @OwnerID, @SquadID output, @ErrorCode output, @ErrorMsg output /* -- This is a workaround until the p_create_team_profile_aleg returns a squadid properly create table #temp2 ( vc_team_name varchar(30), i_status int, i_team_id int, i_status2 int ) insert #temp2 exec profile.profile.dbo.p_get_player_teams_aleg @OwnerID select @squadID = i_team_id from #temp2 where vc_team_name = @SquadName */ if @ErrorCode = 0 begin delete from squads where SquadID = @SquadID or squadname = @squadname -- remove existing, if any insert Squads -- TODO make more robust using pairing of Columns and Values -- select @SquadID, @SquadName, 0, 0, 0, (select CharacterName from CharacterInfo where CharacterID = @OwnerID), @civID select @SquadID, @SquadName, 0, 0, 0, '', @civID, 0 -- add new leader to ownership log declare @charname name select @charname = charactername from characterinfo where characterid = @ownerid if @charname is not null exec SquadAddNewLeaderToLog @SquadID, @charname end select @SquadID, @ErrorCode, @ErrorMsg GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadGetNear ( @SortColumn int = 1, -- 1 is score, 2 is name, etc. defined Squad.h @SquadID int = -1, -- used for positioning; -1 means top @CharacterID int = -1 -- used for DudeX; -1 means any/all characters ) As set nocount on -- TODO convert ifs into switch/case if SQL has such a thing if @SortColumn = 1 exec SquadGetNearNames @SquadID else if @SortColumn = 2 exec SquadGetNearScore @SquadID else if @SortColumn = 3 exec SquadGetNearWins @SquadID else if @SortColumn = 4 exec SquadGetNearLosses @SquadID -- TODO else if @SortColumn = 5 exec SquadGetNearHours @SquadID GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadQuit ( @MemberID int, @SquadID int ) As set nocount on declare @ErrorMsg as varchar(128) declare @ErrorCode as int declare @status as int declare @newowner as varchar(30) exec profile.profile.dbo.p_withdraw_team_aleg @SquadID, @MemberID, @status output, @newowner output, @ErrorCode output, @ErrorMsg output if @status = -1 delete from squads where squadid = @squadid else if @newowner is not null exec SquadAddNewLeaderToLog @SquadID, @newowner if (@status is null) select @ErrorCode, 0 else select @ErrorCode, @status -- status is -1 if team was destroyed because last person left return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SquadTransferControl ( @NewOwnerID int, @SquadID int ) As set nocount on declare @ErrorMsg as varchar(128) declare @ErrorCode as int exec profile.profile.dbo.p_change_team_ownership_aleg @SquadID, @NewOwnerID, @ErrorCode output, @ErrorMsg output -- add new leader to ownership log if @errorcode = 0 begin declare @charname name select @charname = charactername from characterinfo where characterid = @newownerid if @charname is not null exec SquadAddNewLeaderToLog @SquadID, @charname end select @ErrorCode return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNearBaseCaptures ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @charname Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @BaseCaptures smallint declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @charname<>'' begin select @characterid=NULL select @characterid=characterid from characterinfo where charactername=@charname if @characterid is null begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where civid=@civID or (@civID is null and civid is null) order by civid desc, BaseCaptures desc, cs.characterid desc end else begin select @BaseCaptures=BaseCaptures from charstats where CharacterID=@characterid And (civid=@civid or (@civID is null and civid is null)) -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from charstats where (BaseCaptures>@BaseCaptures or (BaseCaptures=@BaseCaptures and characterid>=@characterID or @BaseCaptures is null)) and (civid=@civID or (@civID is null and civid is null)) select top 42 cs.* into #higher from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (BaseCaptures>@BaseCaptures or (BaseCaptures=@BaseCaptures and cs.characterid>=@characterID) or @BaseCaptures is null) and (civid=@civID or (@civID is null and civid is null)) order by civid, BaseCaptures, cs.characterid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 cs.* into #lower from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (BaseCaptures<@BaseCaptures or (BaseCaptures=@BaseCaptures and cs.characterid<@characterID)) and (civid=@civID or (@civID is null and civid is null)) order by civid desc, cs.BaseCaptures desc, cs.characterid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by BaseCaptures desc, characterid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNearBaseKills ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @charname Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @BaseKills smallint declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @charname<>'' begin select @characterid=NULL select @characterid=characterid from characterinfo where charactername=@charname if @characterid is null begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where civid=@civID or (@civID is null and civid is null) order by civid desc, BaseKills desc, cs.characterid desc end else begin select @BaseKills=BaseKills from charstats where CharacterID=@characterid And (civid=@civid or (@civID is null and civid is null)) -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from charstats where (BaseKills>@BaseKills or (BaseKills=@BaseKills and characterid>=@characterID or @BaseKills is null)) and (civid=@civID or (@civID is null and civid is null)) select top 42 cs.* into #higher from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (BaseKills>@BaseKills or (BaseKills=@BaseKills and cs.characterid>=@characterID) or @BaseKills is null) and (civid=@civID or (@civID is null and civid is null)) order by civid, BaseKills, cs.characterid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 cs.* into #lower from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (BaseKills<@BaseKills or (BaseKills=@BaseKills and cs.characterid<@characterID)) and (civid=@civID or (@civID is null and civid is null)) order by civid desc, cs.BaseKills desc, cs.characterid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by BaseKills desc, characterid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNearDeaths ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @charname Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @Deaths smallint declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @charname<>'' begin select @characterid=NULL select @characterid=characterid from characterinfo where charactername=@charname if @characterid is null begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where civid=@civID or (@civID is null and civid is null) order by civid desc, Deaths desc, cs.characterid desc end else begin select @Deaths=Deaths from charstats where CharacterID=@characterid And (civid=@civid or (@civID is null and civid is null)) -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from charstats where (Deaths>@Deaths or (Deaths=@Deaths and characterid>=@characterID or @Deaths is null)) and (civid=@civID or (@civID is null and civid is null)) select top 42 cs.* into #higher from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (Deaths>@Deaths or (Deaths=@Deaths and cs.characterid>=@characterID) or @Deaths is null) and (civid=@civID or (@civID is null and civid is null)) order by civid, Deaths, cs.characterid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 cs.* into #lower from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (Deaths<@Deaths or (Deaths=@Deaths and cs.characterid<@characterID)) and (civid=@civID or (@civID is null and civid is null)) order by civid desc, cs.Deaths desc, cs.characterid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by Deaths desc, characterid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNearGamesPlayed ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @charname Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @GamesPlayed smallint declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @charname<>'' begin select @characterid=NULL select @characterid=characterid from characterinfo where charactername=@charname if @characterid is null begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where civid=@civID or (@civID is null and civid is null) order by civid desc, GamesPlayed desc, cs.characterid desc end else begin select @GamesPlayed=GamesPlayed from charstats where CharacterID=@characterid And (civid=@civid or (@civID is null and civid is null)) -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from charstats where (GamesPlayed>@GamesPlayed or (GamesPlayed=@GamesPlayed and characterid>=@characterID or @GamesPlayed is null)) and (civid=@civID or (@civID is null and civid is null)) select top 42 cs.* into #higher from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (GamesPlayed>@GamesPlayed or (GamesPlayed=@GamesPlayed and cs.characterid>=@characterID) or @GamesPlayed is null) and (civid=@civID or (@civID is null and civid is null)) order by civid, GamesPlayed, cs.characterid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 cs.* into #lower from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (GamesPlayed<@GamesPlayed or (GamesPlayed=@GamesPlayed and cs.characterid<@characterID)) and (civid=@civID or (@civID is null and civid is null)) order by civid desc, cs.GamesPlayed desc, cs.characterid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by GamesPlayed desc, characterid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNearKills ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @charname Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @Kills smallint declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @charname<>'' begin select @characterid=NULL select @characterid=characterid from characterinfo where charactername=@charname if @characterid is null begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where civid=@civID or (@civID is null and civid is null) order by civid desc, Kills desc, cs.characterid desc end else begin select @Kills=Kills from charstats where CharacterID=@characterid And (civid=@civid or (@civID is null and civid is null)) -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from charstats where (Kills>@Kills or (Kills=@Kills and characterid>=@characterID or @Kills is null)) and (civid=@civID or (@civID is null and civid is null)) select top 42 cs.* into #higher from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (Kills>@Kills or (Kills=@Kills and cs.characterid>=@characterID) or @Kills is null) and (civid=@civID or (@civID is null and civid is null)) order by civid, Kills, cs.characterid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 cs.* into #lower from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (Kills<@Kills or (Kills=@Kills and cs.characterid<@characterID)) and (civid=@civID or (@civID is null and civid is null)) order by civid desc, cs.Kills desc, cs.characterid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by Kills desc, characterid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNearLosses ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @charname Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @Losses smallint declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @charname<>'' begin select @characterid=NULL select @characterid=characterid from characterinfo where charactername=@charname if @characterid is null begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where civid=@civID or (@civID is null and civid is null) order by civid desc, Losses desc, cs.characterid desc end else begin select @Losses=Losses from charstats where CharacterID=@characterid And (civid=@civid or (@civID is null and civid is null)) -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from charstats where (Losses>@Losses or (Losses=@Losses and characterid>=@characterID or @Losses is null)) and (civid=@civID or (@civID is null and civid is null)) select top 42 cs.* into #higher from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (Losses>@Losses or (Losses=@Losses and cs.characterid>=@characterID) or @Losses is null) and (civid=@civID or (@civID is null and civid is null)) order by civid, Losses, cs.characterid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 cs.* into #lower from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (Losses<@Losses or (Losses=@Losses and cs.characterid<@characterID)) and (civid=@civID or (@civID is null and civid is null)) order by civid desc, cs.Losses desc, cs.characterid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by Losses desc, characterid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNearMinutesPlayed ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @charname Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @MinutesPlayed int declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @charname<>'' begin select @characterid=NULL select @characterid=characterid from characterinfo where charactername=@charname if @characterid is null begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where civid=@civID or (@civID is null and civid is null) order by civid desc, MinutesPlayed desc, cs.characterid desc end else begin select @MinutesPlayed=MinutesPlayed from charstats where CharacterID=@characterid And (civid=@civid or (@civID is null and civid is null)) -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from charstats where (MinutesPlayed>@MinutesPlayed or (MinutesPlayed=@MinutesPlayed and characterid>=@characterID or @MinutesPlayed is null)) and (civid=@civID or (@civID is null and civid is null)) select top 42 cs.* into #higher from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (MinutesPlayed>@MinutesPlayed or (MinutesPlayed=@MinutesPlayed and cs.characterid>=@characterID) or @MinutesPlayed is null) and (civid=@civID or (@civID is null and civid is null)) order by civid, MinutesPlayed, cs.characterid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 cs.* into #lower from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (MinutesPlayed<@MinutesPlayed or (MinutesPlayed=@MinutesPlayed and cs.characterid<@characterID)) and (civid=@civID or (@civID is null and civid is null)) order by civid desc, cs.MinutesPlayed desc, cs.characterid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by MinutesPlayed desc, characterid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNearNames ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @CharacterName Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @CharacterName<>'' begin select @characterid=NULL select top 1 @characterid=characterid from characterinfo where charactername like RTRIM(@CharacterName) + '%' order by charactername if @characterid is null begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where civid=@civID or (@civID is null and civid is null) order by civid desc, CharacterName desc, cs.characterid desc end else begin if @CharacterName = '' select @CharacterName=CharacterName from charstats where CharacterID=@characterid And (civid=@civid or (@civID is null and civid is null)) -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=1 /* don't need to spend this cost, since ordinals for names don't mean much select @firstordinal=count(*) from charstats where (CharacterName<=@CharacterName) and (civid=@civID or (@civID is null and civid is null)) */ select top 42 cs.* into #higher from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (CharacterName<=@CharacterName) and (civid=@civID or (@civID is null and civid is null)) order by civid desc, CharacterName desc, cs.characterid desc -- select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 cs.* into #lower from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (CharacterName>@CharacterName) and (civid=@civID or (@civID is null and civid is null)) order by civid, cs.CharacterName, cs.characterid select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by CharacterName drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO Create Procedure GetNearNamesOld ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @charname Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* +-----+ This proc is | NOT | exactly the same as the other GetNear* procs, due to the fact +-----+ that we're not sorting on a charstats columns. There are some subtle differences. We also supprt partial name matching here, which we do not in the others. */ declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @charname<>'' begin select @characterid=NULL -- find a close match select top 1 @characterid=characterid from characterinfo where charactername like RTRIM(@charname) + '%' order by CharacterName if @characterid is null begin begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', ci.CharacterName, ci.CharacterID, cs.CivID, cs.Rank, cs.Score, cs.MinutesPlayed, cs.BaseKills, cs.BaseCaptures, cs.Kills, cs.Rating, cs.GamesPlayed, cs.Deaths, cs.Wins, cs.Losses, cs.LastPlayed from charstats cs right outer join characterinfo ci on cs.characterid=ci.characterid and (cs.civid=@civID or (@civID is null and cs.civid is null)) order by ci.CharacterName end else begin -- Map the id to a name if they didn't pass in a name if @charname='' select @charname=CharacterName from CharacterInfo where CharacterId=@characterID -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from CharacterInfo where (CharacterName <= @charname) select top 42 ci.CharacterName, ci.CharacterID, cs.CivID, cs.Rank, cs.Score, cs.MinutesPlayed, cs.BaseKills, cs.BaseCaptures, cs.Kills, cs.Rating, cs.GamesPlayed, cs.Deaths, cs.Wins, cs.Losses, cs.LastPlayed into #higher from charstats cs right outer join characterinfo ci on cs.characterid=ci.characterid and (cs.civid=@civID or (@civID is null and cs.civid is null)) where (ci.CharacterName <= @charname) order by ci.CharacterName desc select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 ci.CharacterName, ci.CharacterID, cs.CivID, cs.Rank, cs.Score, cs.MinutesPlayed, cs.BaseKills, cs.BaseCaptures, cs.Kills, cs.Rating, cs.GamesPlayed, cs.Deaths, cs.Wins, cs.Losses, cs.LastPlayed into #lower from charstats cs right outer join characterinfo ci on cs.characterid=ci.characterid and (cs.civid=@civID or (@civID is null and cs.civid is null)) where (ci.CharacterName > @charname) order by ci.CharacterName select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by CharacterName drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNearRating ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @charname Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @Rating smallint declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @charname<>'' begin select @characterid=NULL select @characterid=characterid from characterinfo where charactername=@charname if @characterid is null begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where civid=@civID or (@civID is null and civid is null) order by civid desc, Rating desc, cs.characterid desc end else begin select @Rating=Rating from charstats where CharacterID=@characterid And (civid=@civid or (@civID is null and civid is null)) -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from charstats where (Rating>@Rating or (Rating=@Rating and characterid>=@characterID or @Rating is null)) and (civid=@civID or (@civID is null and civid is null)) select top 42 cs.* into #higher from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (Rating>@Rating or (Rating=@Rating and cs.characterid>=@characterID) or @Rating is null) and (civid=@civID or (@civID is null and civid is null)) order by civid, Rating, cs.characterid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 cs.* into #lower from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (Rating<@Rating or (Rating=@Rating and cs.characterid<@characterID)) and (civid=@civID or (@civID is null and civid is null)) order by civid desc, cs.Rating desc, cs.characterid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by Rating desc, characterid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNearScore ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @charname Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @Score real declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @charname<>'' begin select @characterid=NULL select @characterid=characterid from characterinfo where charactername=@charname if @characterid is null begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where civid=@civID or (@civID is null and civid is null) order by civid desc, Score desc, cs.characterid desc end else begin select @Score=Score from charstats where CharacterID=@characterid And (civid=@civid or (@civID is null and civid is null)) -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from charstats where (Score>@Score or (Score=@Score and characterid>=@characterID or @Score is null)) and (civid=@civID or (@civID is null and civid is null)) select top 42 cs.* into #higher from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (Score>@Score or (Score=@Score and cs.characterid>=@characterID) or @Score is null) and (civid=@civID or (@civID is null and civid is null)) order by civid, Score, cs.characterid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 cs.* into #lower from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (Score<@Score or (Score=@Score and cs.characterid<@characterID)) and (civid=@civID or (@civID is null and civid is null)) order by civid desc, cs.Score desc, cs.characterid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by Score desc, characterid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetNearWins ( @characterID int, -- If they specify characterID=-1, then they want the top @civID smallint, -- If they specify -1, then they meant NULL @charname Name = '' -- (only) if they specify a name, we need to look up the characterID (ignoring what was passed in) ) As set nocount on /* All the GetNear* functions are exactly the same, except for the sort column, and the type of the sort column. Any changes made to one needs to be made to all the others. I typically just modify one, then cut & paste the whole thing to all others, then use search & replace on the single */ declare @Wins smallint declare @firstordinal int -- Map -1 to NULL since passing NULL from ODBC is a pain if @civID = -1 select @civID = NULL -- If they specify a name, then we need to look up the characterID if @charname<>'' begin select @characterid=NULL select @characterid=characterid from characterinfo where charactername=@charname if @characterid is null begin -- Make sure we still create a result set of the expectant type select cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (0=1) return end end -- If they specify characterID=-1, then they want the top if @characterID=-1 begin select @firstordinal=1 select top 56 @firstordinal as 'FirstOrdinal', cs.* from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where civid=@civID or (@civID is null and civid is null) order by civid desc, Wins desc, cs.characterid desc end else begin select @Wins=Wins from charstats where CharacterID=@characterid And (civid=@civid or (@civID is null and civid is null)) -- it's not quite the first ordinal yet, just count for now. Will patch up to be ordinal down below select @firstordinal=count(*) from charstats where (Wins>@Wins or (Wins=@Wins and characterid>=@characterID or @Wins is null)) and (civid=@civID or (@civID is null and civid is null)) select top 42 cs.* into #higher from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (Wins>@Wins or (Wins=@Wins and cs.characterid>=@characterID) or @Wins is null) and (civid=@civID or (@civID is null and civid is null)) order by civid, Wins, cs.characterid select @firstordinal=@firstordinal - count(*) + 1 from #higher select top 42 cs.* into #lower from charstats cs --inner join characterinfo ci on ci.characterid=cs.characterid where (Wins<@Wins or (Wins=@Wins and cs.characterid<@characterID)) and (civid=@civID or (@civID is null and civid is null)) order by civid desc, cs.Wins desc, cs.characterid desc select @firstordinal as 'FirstOrdinal', o.* from ( select * from #higher union all select* from #lower ) AS o order by Wins desc, characterid desc drop table #higher drop table #lower end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure LogonUser @characterName Name, @characterID int, @fCanCheat bit output As set nocount on -- Called anytime we want to validate a username/id declare @charID1 int declare @name1 Name Select @name1 = characterName, @fCanCheat = CanCheat, @charID1 = characterID from CharacterInfo Where characterID=@characterID or charactername=@characterName if @@rowcount <> 1 or @name1 <> @characterName or @charID1 <> @characterID --everything's fine begin delete from CharStats where characterID=@characterID or charactername=@charactername delete from CharMedals where characterID=@characterID or characterid=@charID1 delete from CharacterInfo where characterID=@characterID or charactername=@charactername Insert Into CharacterInfo (CharacterName, CharacterID) Values(@characterName, @characterID) select @fCanCheat=0 end return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SetCharacterStats ( -- The caller will be sending the values for ONE GAME ONLY, except combat rating, which is overall @bScoresCount bit, @CharacterId int, @CivId smallint, @Rating smallint, -- Combat rating (overall) -- Stuff that affects Score (values from only one game): @WarpsSpotted real, @AsteroidsSpotted real, @TechsRecovered real, @MinerKills real, @BuilderKills real, @LayerKills real, @PlayerKills real, @BaseKills real, @BaseCaptures real, @Deaths smallint, @PilotBaseKills smallint, @PilotBaseCaptures smallint, @MinutesPlayed int, @bWin bit, @bLose bit, @bWinCmd bit, @bLoseCmd bit, @RankOld smallint, @Score real -- score that they got FOR THIS GAME ) As set nocount on declare @Kills smallint -- how do all the types of kills get mapped to THE kills stat? declare @TotalScore real declare @RankNew smallint /* We could do this, but let's not due to perf if @civid not in (select civID from Civs) Begin RaisError ('SetCharStats: You must specify a valid civ to update', 18, 1) WITH NOWAIT return End */ if @bScoresCount=1 begin if @Score < 0 select @Score = 0 Select @Kills=@PlayerKills --Check for Civ rank advancement select @TotalScore=Score from CharStats where CharacterId=@characterid and civid=@civid if @TotalScore is null select @TotalScore=0 select @TotalScore=@TotalScore + @Score select @RankNew = max(r.rank) from ranks r where (r.civid=@CivId or (r.civid is null and @CivId is null)) and r.requirement <= @TotalScore end declare @charname Name -- not doing begin tran because it's just too slow! -- Update the civ-specific stats if exists (select * from CharStats where CharacterId=@characterid and civid=@civid) if @bScoresCount=1 update CharStats set Rank=@RankNew, Score=@TotalScore, MinutesPlayed=MinutesPlayed+@MinutesPlayed, BaseKills=BaseKills+@BaseKills, BaseCaptures=BaseCaptures+@BaseCaptures, Kills=Kills+@Kills, Rating=@Rating, GamesPlayed=GamesPlayed+1, Deaths=Deaths+@Deaths, Wins=Wins+@bWin, Losses=Losses+@bLose, WinsCmd=WinsCmd+@bWinCmd, LossesCmd=LossesCmd+@bLoseCmd, LastPlayed=GetDate() where characterid=@characterid and civid=@civid else update CharStats set MinutesPlayed=MinutesPlayed+@MinutesPlayed, GamesPlayed=GamesPlayed+1, LastPlayed=GetDate() where characterid=@characterid and civid=@civid else begin select @charname=CharacterName from CharacterInfo Where CharacterId=@CharacterID if @bScoresCount=1 insert into CharStats ( CharacterId, CharacterName, CivID, Rank, Score, MinutesPlayed, BaseKills, BaseCaptures, Kills, Rating, GamesPlayed, Deaths, Wins, Losses, WinsCmd, LossesCmd, LastPlayed) Values(@CharacterId, @charname, @CivID, @RankNew, @TotalScore, @MinutesPlayed, @BaseKills, @BaseCaptures, @Kills, @Rating, 1, @Deaths, @bWin, @bLose, @bWinCmd, @bLoseCmd, GetDate()) else insert into CharStats ( CharacterId, CharacterName, CivID, MinutesPlayed, GamesPlayed, LastPlayed) Values(@CharacterId, @charname, @CivID, @MinutesPlayed, 1, GetDate()) end -- Update overall stats (ignore scores count, because we're just aggregating the per civ stuff declare @GamesPlayed smallint, @Wins smallint, @Losses smallint, @WinsCmd smallint, @LossesCmd smallint, @OverallScore real, @OverallRank smallint -- First calculate them. We know we have at least one civ at this point, since we just added/updated it select @OverallScore=Sum(Score), @MinutesPlayed=Sum(MinutesPlayed), @BaseKills=Sum(BaseKills), @BaseCaptures=Sum(BaseCaptures), @Kills=Sum(Kills), @Rating=Max(Rating), @GamesPlayed=Sum(GamesPlayed), @Deaths=Sum(Deaths), @Wins=Sum(Wins), @Losses=Sum(Losses), @WinsCmd=Sum(WinsCmd), @LossesCmd=Sum(LossesCmd) from charstats where characterid=@characterid and not (civid is NULL) -- Now update them (getting the new rank for overall) select @OverallRank = max(r.rank) from ranks r where r.civid is null and r.requirement <= @OverallScore if exists (select * from CharStats where CharacterId=@characterid and civid is null) update CharStats set Rank=@OverallRank, Score=@OverallScore, MinutesPlayed=@MinutesPlayed, BaseKills=@BaseKills, BaseCaptures=@BaseCaptures, Kills=@Kills, Rating=@Rating, GamesPlayed=@GamesPlayed, Deaths=@Deaths, Wins=@Wins, Losses=@Losses, WinsCmd=@WinsCmd, LossesCmd=@LossesCmd, LastPlayed=GetDate() where characterid=@characterid and civid is null else begin if @charname is null select @charname=CharacterName from CharacterInfo Where CharacterId=@CharacterID insert into CharStats ( CharacterId, CharacterName, CivID, Rank, Score, MinutesPlayed, BaseKills, BaseCaptures, Kills, Rating, GamesPlayed, Deaths, Wins, Losses, WinsCmd, LossesCmd, LastPlayed) values(@CharacterId, @charname, null, @OverallRank, @Score, @MinutesPlayed, @BaseKills, @BaseCaptures, @Kills, @Rating, @GamesPlayed, @Deaths, @Wins, @Losses, @WinsCmd, @LossesCmd, GetDate()) end -- commit tran return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetLobbyLogonInfo ( @characterName Name, @characterID int, @accesscode AccessCode, @fValidCode bit output, @fCanCheat bit output ) As set nocount on select @fValidCode=0 select @fCanCheat=0 -- We're gonna assume if they got this far they're kosher, so add 'em if they're not there already exec LogonUser @characterName, @characterID, @fCanCheat output declare @status smallint /* Status Codes 0: code never been used. There must NOT be any rows in AccessCodeUsage for this code 1: code in use (normal case). 2: code has been reassigned. Invalid code now 3: code has been disabled for whatever random reason. Invalid code now 4: code is reserved for future use. Invalid code at this time */ select @status=status from AccessCodes where accesscode=@accesscode if @@rowcount=1 and @status <= 1 --valid access code begin if @status = 0 -- code never been used update AccessCodes set status = 1 where accesscode=@accesscode -- Assume that more often than not, the code will be used by a name that's used it before update AccessCodeUsage set LastUsage=getdate() where AccessCode=@accesscode and ZoneName=@characterName if @@rowcount=0 --new zone name used with this key insert into AccessCodeUsage (AccessCode, ZoneName) values (@accesscode, @characterName) select @fValidCode=1 end -- We need to tell them which squads they're in. This generates a rowset -- I can't really skip this if the access code is invalid because -- a) the lobby might not be enforcing access codes, and -- b) the lobby will require a properly formed rowset anyway exec GetCharSquads @characterID GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure GetLogonStats ( @characterName Name, @characterID int, @fCanCheat bit output ) As set nocount on -- We're gonna assume if they got this far they're kosher, so add 'em if they're not there already exec LogonUser @characterName, @characterID, @fCanCheat output Select ISNULL(CivID, -1), Rank, Score, MinutesPlayed, BaseKills, BaseCaptures, Kills, Rating, GamesPlayed, Deaths, Wins, Losses From CharStats Where CharacterId=@characterID GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure SetRandomStats ( @charID int, @civID smallint ) As declare @Rank smallint declare @Score real declare @MinutesPlayed smallint declare @Kills smallint declare @Deaths smallint declare @Wins smallint declare @Losses smallint declare @Rating smallint declare @Warps real declare @Asteroids real declare @Techs real declare @MinerKills real declare @BuilderKills real declare @LayerKills real declare @PlayerKills real declare @BaseKills real declare @BaseCaptures real declare @PilotBaseKills smallint declare @PilotBaseCaptures smallint declare @bWin bit select @MinutesPlayed=convert(smallint, 300 * rand()) select @kills=convert(smallint, 100 * rand()) select @Deaths=convert(smallint, 10 * rand()) select @Wins=convert(smallint, 1000 * rand()) select @Losses=convert(smallint, 1000 * rand()) select @Rating=convert(smallint, 1000 * rand()) select @Warps=convert(real, 10 * rand()) select @Asteroids=convert(real, 10 * rand()) select @Techs=convert(real, 5 * rand()) select @MinerKills=convert(real, 3 * rand()) select @BuilderKills=convert(real, 1 * rand()) select @LayerKills=convert(real, 1 * rand()) select @PlayerKills=convert(real, 10 * rand()) select @BaseKills=convert(real, 1 * rand()) select @BaseCaptures=convert(real, 2 * rand()) select @PilotBaseKills=convert(smallint, 3 * rand()) select @PilotBaseCaptures=convert(smallint, 2 * rand()) select @bWin=convert(smallint, 2 * rand()) select @Rank=1 exec SetCharacterStats @CharID, @CivId, @Rating, @Warps, @Asteroids, @Techs, @MinerKills, @BuilderKills, @LayerKills, @PlayerKills, @BaseKills , @BaseCaptures , @Deaths , @PilotBaseKills, @PilotBaseCaptures, @MinutesPlayed , @bWin, @Rank output, @Score output return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Procedure CreateLotsOfRandomUsers ( @startingnumber int, @numusers int, @civid1 smallint, @civid2 smallint = NULL, @civid3 smallint = NULL ) As set nocount on declare @i int select @i=0 while (@i < @numusers) begin declare @charname char(12) declare @charid int select @charname='Random' + convert(char(6), @i + @startingnumber) insert into characterinfo (CharacterID, CharacterName) values (@i + @startingnumber, @charname) select @charid=characterid from characterinfo where charactername=@charname --exec SetRandomStats @charID, @CivID1 if @civID2 is not null exec SetRandomStats @charID, @CivID2 if @civID3 is not null exec SetRandomStats @charID, @CivID3 select @i=@i+1 end return GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO Create Trigger Constants_Insert On dbo.Constants For Insert As If (Select count(*) from Constants) > 1 Begin Rollback Transaction RaisError ('Only one row is allowed in the Constants table', 18, 1) WITH NOWAIT End GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO Create Trigger Drones_Trigger_InsUpd On dbo.Drones For Insert, Update As If Update (TechBitsRequired) or Update (TechBitsEffect) Begin declare @okR bit, @okE bit declare @techbitsR TechBits, @techbitsE TechBits select @okR = 0 select @okE = 0 /* need to see if ANY modified row has bogus techbits */ Declare curIns Cursor Local For Select TechBitsRequired, TechBitsEffect From Inserted Open curIns Fetch Next From curIns Into @techbitsR, @techbitsE While @@FETCH_STATUS = 0 Begin Execute IsValidTechBits @techbitsR, @okR Output If @okR=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsRequired', 18, 1) WITH NOWAIT End Execute IsValidTechBits @techbitsE, @okE Output If @okE = 0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsEffect', 18, 1) WITH NOWAIT End Fetch Next From curIns Into @techbitsR, @techbitsE End Close curIns End GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Trigger Expendables_Trigger_InsUpd On dbo.Expendables For Insert, Update As If Update (TechBitsRequired) or Update (TechBitsEffect) Begin declare @ok bit declare @techbitsR TechBits, @techbitsE TechBits select @ok = 0 /* need to see if ANY modified row has bogus techbits */ Declare curIns Cursor Local For Select TechBitsRequired, TechBitsEffect From Inserted Open curIns Fetch Next From curIns Into @techbitsR, @techbitsE While @@FETCH_STATUS = 0 Begin Execute IsValidTechBits @techbitsR, @ok Output If @ok=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsRequired', 18, 1) WITH NOWAIT End Execute IsValidTechBits @techbitsE, @ok Output If @ok=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsEffect', 18, 1) WITH NOWAIT End Fetch Next From curIns Into @techbitsR, @techbitsE End Close curIns End GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Trigger StationTypes_Trigger_InsUpd On dbo.StationTypes For Insert, Update As declare @ok bit If Update (TechBitsRequired) or Update (TechBitsEffect) or Update(TechBitsLocal) Begin declare @techbitsR TechBits, @techbitsE TechBits, @techbitsL TechBits select @ok = 0 /* need to see if ANY modified row has bogus techbits */ Declare curIns Cursor Local For Select TechBitsRequired, TechBitsEffect, TechBitsLocal From Inserted Open curIns Fetch Next From curIns Into @techbitsR, @techbitsE, @techbitsL While @@FETCH_STATUS = 0 Begin Execute IsValidTechBits @techbitsR, @ok Output If @ok=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsRequired', 18, 1) WITH NOWAIT End Execute IsValidTechBits @techbitsE, @ok Output If @ok=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsEffect', 18, 1) WITH NOWAIT End Execute IsValidTechBits @techbitsL, @ok Output If @ok=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsLocal', 18, 1) WITH NOWAIT End Fetch Next From curIns Into @techbitsR, @techbitsE, @techbitsL End Close curIns End /* why the freal won't nested comments work??? */ /* why can't I use false in logical expressions??? */ If (0=1 and (Update (TechBitsLocal) or Update(TechBitsEffect))) Begin declare @iIndex int declare @siStationType smallint declare @siUpgradeType smallint declare @szStationEffect TechBits declare @szUpgradeEffect TechBits declare @iStationEffect int declare @iUpgradeEffect int declare @szStationLocal TechBits declare @szUpgradeLocal TechBits declare @iStationLocal int declare @iUpgradeLocal int declare @szProblemBits char(20) declare curStations cursor local for select st1.stationtypeid, st2.stationtypeid, st1.techbitseffect, st2.techbitseffect, st1.techbitslocal, st2.techbitslocal from inserted st1, stationtypes st2 where (st1.upgradestationtypeid = st2.stationtypeid) or (st1.stationtypeid = st2.upgradestationtypeid) order by st1.stationtypeid open curStations fetch next from curStations into @siStationType, @siUpgradeType, @szStationEffect, @szUpgradeEffect, @szStationLocal, @szUpgradeLocal while (@@fetch_status <> -1) begin if (@@fetch_status <> -2) begin /* the inserted table could have either the base OR upgraded station */ If (@siStationType < @siUpgradeType) Begin /* need to rearrange the stations */ declare @siT smallint Select @siT = @siStationType Select @siStationType = @siUpgradeType Select @siUpgradeType = @siT declare @tbT TechBits Select @tbT = @szStationEffect Select @szStationEffect = @szUpgradeEffect Select @szUpgradeEffect = @tbT Select @tbT = @szStationLocal Select @szStationLocal = @szUpgradeLocal Select @szUpgradeLocal = @tbT End Execute IsTechBitsSuperset @szStationEffect, @szUpgradeEffect, @ok Output if @ok=0 select @szProblemBits = '(Effect bits)' else Begin Execute IsTechBitsSuperset @szStationLocal, @szUpgradeLocal, @ok Output if @ok=0 select @szProblemBits = '(Local bits)' End if @ok=0 Begin Rollback Transaction declare @msg varchar(100) select @msg = 'Warning: Illegal upgrade from station (' + Convert(char(3), @siStationType) + ') to station (' + Convert(char(3), @siUpgradeType) + ') ' + @szProblemBits RaisError (@msg, 18, 1) WITH NOWAIT End end fetch next from curStations into @siStationType, @siUpgradeType, @szStationEffect, @szUpgradeEffect, @szStationLocal, @szUpgradeLocal end close curStations deallocate curStations End GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO Create Trigger Developments_Trigger_InsUpd On dbo.Developments For Insert, Update As If Update (TechBitsRequired) or Update (TechBitsEffect) Begin declare @okR bit, @okE bit declare @techbitsR TechBits, @techbitsE TechBits select @okR = 0 select @okE = 0 /* need to see if ANY modified row has bogus techbits */ Declare curIns Cursor Local For Select TechBitsRequired, TechBitsEffect From Inserted Open curIns Fetch Next From curIns Into @techbitsR, @techbitsE While @@FETCH_STATUS = 0 Begin Execute IsValidTechBits @techbitsR, @okR Output If @okR=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsRequired', 18, 1) WITH NOWAIT End Execute IsValidTechBits @techbitsE, @okE Output If @okE=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsEffect', 18, 1) WITH NOWAIT End Fetch Next From curIns Into @techbitsR, @techbitsE End Close curIns End GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Trigger PartsLastUpdated On dbo.Parts For Insert, Update As UPDATE Parts Set LastUpdated=GetDate() WHERE Parts.PartID IN (SELECT PartID FROM inserted) GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Trigger Parts_Trigger_InsUpd On dbo.Parts For Insert, Update As If Update (TechBitsRequired) or Update (TechBitsEffect) Begin declare @ok bit declare @techbitsR TechBits, @techbitsE TechBits select @ok = 0 /* need to see if ANY modified row has bogus techbits */ Declare curIns Cursor Local For Select TechBitsRequired, TechBitsEffect From Inserted Open curIns Fetch Next From curIns Into @techbitsR, @techbitsE While @@FETCH_STATUS = 0 Begin Execute IsValidTechBits @techbitsR, @ok Output If @ok=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsRequired', 18, 1) WITH NOWAIT End Execute IsValidTechBits @techbitsE, @ok Output If @ok=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsEffect', 18, 1) WITH NOWAIT End Fetch Next From curIns Into @techbitsR, @techbitsE End Close curIns End GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Trigger Aleph_InsUpd On dbo.AlephInstances For Insert, Update As DECLARE @dupes integer SELECT @dupes=count(*) FROM Inserted WHERE AlephID=TargetAlephID IF @dupes > 0 BEGIN RaisError('Do you really want an aleph to lead to itself? No? I didn''t think you would, so fix it.', 16, -1) ROLLBACK TRANSACTION END GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Trigger Civs_Trigger_InsUpd On dbo.Civs For Insert, Update As If Update (TechBitsCiv) Begin declare @ok bit declare @techbits char(800) select @ok = 0 /* need to see if ANY modified row has bogus techbits */ Declare curIns Cursor Local For Select TechBitsCiv From Inserted Open curIns Fetch Next From curIns Into @techbits While @@FETCH_STATUS = 0 Begin Execute IsValidTechBits @techbits, @ok Output If @ok=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsCiv', 18, 1) WITH NOWAIT End Fetch Next From curIns Into @techbits End Close curIns End If Update (TechBitsCivNoDev) Begin declare @okNoDev bit declare @techbitsNoDev char(800) select @okNoDev = 0 /* need to see if ANY modified row has bogus techbits */ Declare curIns Cursor Local For Select TechBitsCivNoDev From Inserted Open curIns Fetch Next From curIns Into @techbitsNoDev While @@FETCH_STATUS = 0 Begin Execute IsValidTechBits @techbitsNoDev, @okNoDev Output If @okNoDev=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsCivNoDev', 18, 1) WITH NOWAIT End Fetch Next From curIns Into @techbitsNoDev End Close curIns End GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Trigger ShipTypes_Trigger_InsUpd On dbo.ShipTypes For Insert, Update As If Update (TechBitsRequired) or Update (TechBitsEffect) Begin declare @ok bit declare @techbitsR TechBits, @techbitsE TechBits select @ok = 0 Declare curIns Cursor Local For Select TechBitsRequired, TechBitsEffect From Inserted Open curIns Fetch Next From curIns Into @techbitsR, @techbitsE While @@FETCH_STATUS = 0 Begin Execute IsValidTechBits @techbitsR, @ok Output If @ok=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsRequired', 18, 1) WITH NOWAIT End Execute IsValidTechBits @techbitsE, @ok Output If @ok=0 Begin Rollback Transaction RaisError ('Invalid tech in TechBitsEffect', 18, 1) WITH NOWAIT End Fetch Next From curIns Into @techbitsR, @techbitsE End Close curIns End GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO Create Trigger StationInstances_Trigger_InsUpd On dbo.StationInstances For Insert, Update As DECLARE @zerocount integer, @coincidentcount integer SELECT @zerocount=count(*) FROM Inserted WHERE (UpX=0 AND UpY=0 AND UpZ=0) OR (ForwardX=0 AND ForwardY=0 AND ForwardZ=0) SELECT @coincidentcount=count(*) FROM Inserted WHERE (UpX=ForwardX) AND (UpY=ForwardY) AND (UpZ=ForwardZ) IF @zerocount > 0 BEGIN RaisError('Cannot have zero length orientation vectors', 16, -1) ROLLBACK TRANSACTION END IF @coincidentcount > 0 BEGIN RaisError('Cannot have coincident Forward and Up orientation vectors', 16, -1) ROLLBACK TRANSACTION END GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE Trigger AttachPoints_Trigger_InsUpd On dbo.AttachPoints For Insert, Update As If Update (ShipTypeID) Begin /* Let's just be lazy and create a cursor over the Inserted table, and rather just check the whole regular table, since it won't be that big. Only trick will be that if bad data DOES get in, then ANY change (involving a shiptypeid) will cause the transaction to be rejected.s */ If exists (Select * from attachpoints group by shiptypeid, parttypeid having COUNT(*) > 4) Begin Rollback Transaction RaisError ('Each ship can have no more than 4 attach points per part type', 18, 1) WITH NOWAIT End End GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO