Safe Haskell	None
Language	Haskell2010

Hmssql.Catalog

Contents

Tables and views
Reading the catalog

Description

The database's schema, read from its catalog views: every user table and view with its columns, their declared types (SqlType), the primary key, the unique constraints and the foreign keys.

tables <- readTablesIn conn "dbo"
forM_ tables $ \t -> forM_ (tableColumns t) $ \c ->
  putStrLn (T.unpack (objectName (tableName t) <> "." <> colName c <> ": " <> maybe (colTypeName c) sqlTypeName (colType c)))

Everything comes from sys.objects, sys.columns, sys.types and the constraint views, in one request. A column's type is resolved through an alias type (dbo.phone, based on varchar(20), is a varchar(20); sysname is nvarchar(128)), and a CLR user-defined type or a type newer than this library leaves colType Nothing with the name in colTypeName.

What is not read: indexes, check constraints, triggers, the referential actions of foreign keys, identity seeds, procedures and functions.

Synopsis

data ObjectName = ObjectName {
- objectSchema :: !Text
- objectName :: !Text
}
objectNameSql :: ObjectName -> Text
data Table = Table {
- tableName :: !ObjectName
- tableKind :: !TableKind
- tableColumns :: ![Column]
- tablePrimaryKey :: !(Maybe KeyConstraint)
- tableUniqueKeys :: ![KeyConstraint]
- tableForeignKeys :: ![ForeignKey]
}
data TableKind
- = UserTable
- | View
data Column = Column {
- colName :: !Text
- colId :: !Int
- colTypeName :: !Text
- colType :: !(Maybe SqlType)
- colNullable :: !Bool
- colCollation :: !(Maybe Text)
- colIdentity :: !Bool
- colComputed :: !(Maybe Computed)
- colDefault :: !(Maybe Text)
- colGenerated :: !(Maybe GeneratedAlways)
- colHidden :: !Bool
}
data Computed = Computed {
- computedDefinition :: !Text
- computedPersisted :: !Bool
}
data GeneratedAlways
- = AsRowStart
- | AsRowEnd
- | AsTransactionIdStart
- | AsTransactionIdEnd
- | AsSequenceNumberStart
- | AsSequenceNumberEnd
- | AsOther !Int
data KeyConstraint = KeyConstraint {
- keyName :: !Text
- keyColumns :: ![Text]
}
data ForeignKey = ForeignKey {
- fkName :: !Text
- fkColumns :: ![Text]
- fkReferencedTable :: !ObjectName
- fkReferencedColumns :: ![Text]
}
readTables :: Connection -> IO [Table]
readTablesIn :: Connection -> Text -> IO [Table]
readTable :: Connection -> ObjectName -> IO (Maybe Table)
lookupColumn :: Table -> Text -> Maybe Column

Tables and views

data ObjectName Source #

A schema-qualified name: dbo.customer.

Constructors

ObjectName
Fields objectSchema :: !Text objectName :: !Text

Instances

Instances details

Show ObjectName Source #
Instance details Defined in Hmssql.Catalog Methods showsPrec :: Int -> ObjectName -> ShowS # show :: ObjectName -> String # showList :: [ObjectName] -> ShowS #
Eq ObjectName Source #
Instance details Defined in Hmssql.Catalog Methods (==) :: ObjectName -> ObjectName -> Bool # (/=) :: ObjectName -> ObjectName -> Bool #
Ord ObjectName Source #
Instance details Defined in Hmssql.Catalog Methods compare :: ObjectName -> ObjectName -> Ordering # (<) :: ObjectName -> ObjectName -> Bool # (<=) :: ObjectName -> ObjectName -> Bool # (>) :: ObjectName -> ObjectName -> Bool # (>=) :: ObjectName -> ObjectName -> Bool # max :: ObjectName -> ObjectName -> ObjectName # min :: ObjectName -> ObjectName -> ObjectName #

objectNameSql :: ObjectName -> Text Source #

The name as SQL, bracket-quoted: [dbo].[customer].

data Table Source #

A table or view with its columns and constraints.

Constructors

Table
Fields tableName :: !ObjectName tableKind :: !TableKind tableColumns :: ![Column] In column order. tablePrimaryKey :: !(Maybe KeyConstraint) tableUniqueKeys :: ![KeyConstraint] The `UNIQUE` constraints, by name. (Unique indexes that are not constraints are not read.) tableForeignKeys :: ![ForeignKey] By name.

Instances

Instances details

Show Table Source #
Instance details Defined in Hmssql.Catalog Methods showsPrec :: Int -> Table -> ShowS # show :: Table -> String # showList :: [Table] -> ShowS #
Eq Table Source #
Instance details Defined in Hmssql.Catalog Methods (==) :: Table -> Table -> Bool # (/=) :: Table -> Table -> Bool #

data TableKind Source #

Whether an object is a table or a view.

Constructors

UserTable
View

Instances

Instances details

Bounded TableKind Source #
Instance details Defined in Hmssql.Catalog Methods minBound :: TableKind # maxBound :: TableKind #
Enum TableKind Source #
Instance details Defined in Hmssql.Catalog Methods succ :: TableKind -> TableKind # pred :: TableKind -> TableKind # toEnum :: Int -> TableKind # fromEnum :: TableKind -> Int # enumFrom :: TableKind -> [TableKind] # enumFromThen :: TableKind -> TableKind -> [TableKind] # enumFromTo :: TableKind -> TableKind -> [TableKind] # enumFromThenTo :: TableKind -> TableKind -> TableKind -> [TableKind] #
Read TableKind Source #
Instance details Defined in Hmssql.Catalog Methods readsPrec :: Int -> ReadS TableKind # readList :: ReadS [TableKind] # readPrec :: ReadPrec TableKind # readListPrec :: ReadPrec [TableKind] #
Show TableKind Source #
Instance details Defined in Hmssql.Catalog Methods showsPrec :: Int -> TableKind -> ShowS # show :: TableKind -> String # showList :: [TableKind] -> ShowS #
Eq TableKind Source #
Instance details Defined in Hmssql.Catalog Methods (==) :: TableKind -> TableKind -> Bool # (/=) :: TableKind -> TableKind -> Bool #
Ord TableKind Source #
Instance details Defined in Hmssql.Catalog Methods compare :: TableKind -> TableKind -> Ordering # (<) :: TableKind -> TableKind -> Bool # (<=) :: TableKind -> TableKind -> Bool # (>) :: TableKind -> TableKind -> Bool # (>=) :: TableKind -> TableKind -> Bool # max :: TableKind -> TableKind -> TableKind # min :: TableKind -> TableKind -> TableKind #

data Column Source #

A column of a table or view.

Constructors

Column

Fields

colName :: !Text
colId :: !Int
column_id: a column's position, with gaps where columns were dropped.
colTypeName :: !Text
The type the column is declared with, as named: a system type (nvarchar, sysname, vector, hierarchyid), or a user-defined type with its schema (dbo.phone).
colType :: !(Maybe SqlType)
The type with its arguments, an alias type resolved to what it is based on. Nothing for a CLR user-defined type other than hierarchyid, geometry and geography, and for a type newer than this library.
colNullable :: !Bool
colCollation :: !(Maybe Text)
Character types only.
colIdentity :: !Bool
colComputed :: !(Maybe Computed)
colDefault :: !(Maybe Text)
The default constraint's expression as the catalog stores it, e.g. ((0)) or (getdate()).
colGenerated :: !(Maybe GeneratedAlways)
A column the server maintains: a system-versioned (temporal) table's period, or a ledger table's.
colHidden :: !Bool
Left out of SELECT *.

Instances

Instances details

Show Column Source #
Instance details Defined in Hmssql.Catalog Methods showsPrec :: Int -> Column -> ShowS # show :: Column -> String # showList :: [Column] -> ShowS #
Eq Column Source #
Instance details Defined in Hmssql.Catalog Methods (==) :: Column -> Column -> Bool # (/=) :: Column -> Column -> Bool #

data Computed Source #

A computed column's expression, and whether it is stored.

Constructors

Computed
Fields computedDefinition :: !Text computedPersisted :: !Bool

Instances

Instances details

Show Computed Source #
Instance details Defined in Hmssql.Catalog Methods showsPrec :: Int -> Computed -> ShowS # show :: Computed -> String # showList :: [Computed] -> ShowS #
Eq Computed Source #
Instance details Defined in Hmssql.Catalog Methods (==) :: Computed -> Computed -> Bool # (/=) :: Computed -> Computed -> Bool #

data GeneratedAlways Source #

sys.columns.generated_always_type.

Constructors

AsRowStart
AsRowEnd
AsTransactionIdStart
AsTransactionIdEnd
AsSequenceNumberStart
AsSequenceNumberEnd
AsOther !Int	A kind this library doesn't know, with the catalog's number.

Instances

Instances details

Show GeneratedAlways Source #
Instance details Defined in Hmssql.Catalog Methods showsPrec :: Int -> GeneratedAlways -> ShowS # show :: GeneratedAlways -> String # showList :: [GeneratedAlways] -> ShowS #
Eq GeneratedAlways Source #
Instance details Defined in Hmssql.Catalog Methods (==) :: GeneratedAlways -> GeneratedAlways -> Bool # (/=) :: GeneratedAlways -> GeneratedAlways -> Bool #
Ord GeneratedAlways Source #
Instance details Defined in Hmssql.Catalog Methods compare :: GeneratedAlways -> GeneratedAlways -> Ordering # (<) :: GeneratedAlways -> GeneratedAlways -> Bool # (<=) :: GeneratedAlways -> GeneratedAlways -> Bool # (>) :: GeneratedAlways -> GeneratedAlways -> Bool # (>=) :: GeneratedAlways -> GeneratedAlways -> Bool # max :: GeneratedAlways -> GeneratedAlways -> GeneratedAlways # min :: GeneratedAlways -> GeneratedAlways -> GeneratedAlways #

data KeyConstraint Source #

A primary key or unique constraint.

Constructors

KeyConstraint
Fields keyName :: !Text keyColumns :: ![Text] In key order.

Instances

Instances details

Show KeyConstraint Source #
Instance details Defined in Hmssql.Catalog Methods showsPrec :: Int -> KeyConstraint -> ShowS # show :: KeyConstraint -> String # showList :: [KeyConstraint] -> ShowS #
Eq KeyConstraint Source #
Instance details Defined in Hmssql.Catalog Methods (==) :: KeyConstraint -> KeyConstraint -> Bool # (/=) :: KeyConstraint -> KeyConstraint -> Bool #

data ForeignKey Source #

A foreign key constraint.

Constructors

ForeignKey
Fields fkName :: !Text fkColumns :: ![Text] The referencing columns, paired with `fkReferencedColumns`. fkReferencedTable :: !ObjectName fkReferencedColumns :: ![Text]

Instances

Instances details

Show ForeignKey Source #
Instance details Defined in Hmssql.Catalog Methods showsPrec :: Int -> ForeignKey -> ShowS # show :: ForeignKey -> String # showList :: [ForeignKey] -> ShowS #
Eq ForeignKey Source #
Instance details Defined in Hmssql.Catalog Methods (==) :: ForeignKey -> ForeignKey -> Bool # (/=) :: ForeignKey -> ForeignKey -> Bool #

Reading the catalog

readTables :: Connection -> IO [Table] Source #

Every user table and view the login can see, in every schema, in name order. Objects marked as shipped by Microsoft are left out.

readTablesIn :: Connection -> Text -> IO [Table] Source #

The user tables and views of one schema.

readTable :: Connection -> ObjectName -> IO (Maybe Table) Source #

One table or view, if it exists.

lookupColumn :: Table -> Text -> Maybe Column Source #

The column of this name (exact match, as the catalog names it).