Function dbo.StripUnwantedCharacters - Cleaning up unwanted ASCII characters

Having determined I had some unwanted NUL characters in my data (see ASCII Value Character Counts Procedure) I set out to remove them.

I tried permutations of LIKE and CHAR to find the character as well as REPLACE and CHARINDEX , all to no avail. Then I spotted this -

 0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in CHARINDEX.

This was on the MSDN page for CHARINDEX

You can read the whole string character by character however and recreate it, removing those characters. This is what my function below does.

CREATE FUNCTION dbo.StripUnwantedCharacters (@InputString VARCHAR(MAX)) 
RETURNS VARCHAR(MAX)
AS 
BEGIN

DECLARE @OutputString varchar(MAX)
DECLARE @CurrentCharacterValue INT
DECLARE @Position INT

SET @OutputString = ''
SET @Position = 1

WHILE @Position < LEN(@InputString)+1
 BEGIN
 SET @CurrentCharacterValue = ASCII(SUBSTRING(@InputString, @Position, 1))

 IF @CurrentCharacterValue >= 9 -- Exclude Characters where ASCII value less that 9
  BEGIN
  SET @OutputString = @OutputString + CHAR(@CurrentCharacterValue)
  END

 SET @Position = @Position + 1
 END
RETURN @OutputString

END
GO

Code to use this procedure is as simple as ...

UPDATE tablename
SET cleandata = dbo.StripUnwantedCharacters(dirtydata)

Or too generate sql for all columns ...

SELECT 
'UPDATE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] SET ' + COLUMN_NAME + ' = RTRIM(dbo.StripUnwantedCharacters(' + COLUMN_NAME + '))' 
 from INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE IN ('char','varchar')
GO

ASCII Value Character Counts Procedure

I wrote this to determine the ASCII values being stored inside my data. It builds upon Determine ASCII Values of a string and produces a breakdown of all the ASCII values in a column.
To cut a long story short I had an issue where NUL characters (ASCII value 0) were stored and were producing unexpected results.

Code for the results table and procedure is here -

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.charactercount'))
 CREATE TABLE dbo.charactercount
 (
  [id] int identity(1,1)
 ,[table_schema] sysname
 ,[table_name] sysname
 ,[column_name] sysname
 ,[character] int
 ,[column_count] int
 )
GO

IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'charactercount_insert')
 DROP PROCEDURE dbo.charactercount_insert
GO
CREATE PROCEDURE dbo.charactercount_insert
(@table_schema sysname
,@table_name sysname
,@column_name sysname
,@sample_rows int = NULL)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ColumnData table
(
UniqueRowID int IDENTITY (1, 1) Primary key NOT NULL ,
testeddata nvarchar(MAX)
)
DECLARE @sql NVARCHAR(MAX)
DECLARE @CurrentRow int  
DECLARE @TotalRows int  
DECLARE @Index INT
DECLARE @IntASCII INT
DECLARE @StatusMessage varchar (100)
DECLARE @currentdata NVARCHAR(MAX)
DECLARE @currentrowlength INT

IF @sample_rows IS NULL 
 SET @sql = 'SELECT [' + @column_name + '] FROM [' + @table_schema + '].[' + @table_name + ']'
ELSE
 SET @sql = 'SELECT TOP (' + CAST(@sample_rows AS VARCHAR(10)) + ') [' + @column_name + '] FROM [' + @table_schema + '].[' + @table_name + ']'
  
SET @StatusMessage = 'Fetching: [' + @column_name + '] FROM [' + @table_schema + '].[' + @table_name + ']'
RAISERROR (@StatusMessage , 10, 1) WITH NOWAIT 


INSERT INTO @ColumnData (testeddata)
EXEC(@sql)

SELECT @TotalRows = MAX(UniqueRowID) FROM @ColumnData

SET @StatusMessage = CAST(@TotalRows AS VARCHAR(20)) + ' rows fetched'
RAISERROR (@StatusMessage , 10, 1) WITH NOWAIT 

SELECT @CurrentRow = 1

SET @StatusMessage = 'Counting Characters'
RAISERROR (@StatusMessage , 10, 1) WITH NOWAIT 

 IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#charactercount')  
    DROP TABLE #charactercount

 CREATE TABLE #charactercount
 (
  [id] int identity(1,1)
 ,[table_schema] sysname
 ,[table_name] sysname
 ,[column_name] sysname
 ,[character] int
 ,[column_count] int
 )

WHILE @CurrentRow <= @TotalRows  
 BEGIN   

 SET @Index = 1

 SELECT @currentdata = testeddata
 FROM @ColumnData
 WHERE [UniqueRowID] = @CurrentRow

 SELECT @currentrowlength = LEN(@currentdata)

 IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#charactercount')  
    TRUNCATE TABLE #charactercount

 WHILE @Index < 1 +(@currentrowlength)
  BEGIN

  SELECT @IntASCII = ASCII(SUBSTRING(@currentdata, @Index, 1))
  FROM @ColumnData
  WHERE [UniqueRowID] = @CurrentRow

  INSERT INTO #charactercount
  (table_schema ,table_name ,column_name ,character,column_count )
  SELECT @table_schema AS Table_Schema
    ,@table_name AS Table_Name
    ,@column_name AS Column_Name
    ,@IntASCII AS [Character]
    ,1 AS Number 

  SET @Index = @Index + 1
  END

  -- At end of text, update the character count 
  MERGE [dbo].[charactercount] AS [Target]
  USING (SELECT table_schema AS Table_Schema
      ,table_name AS Table_Name
      ,column_name AS Column_Name
      ,character AS character
      ,SUM(column_count) AS Number
      FROM #charactercount
      GROUP BY table_schema, table_name, column_name,character) AS [Source]
  ON  Source.Table_Schema = Target.Table_Schema
  AND Source.Table_Name   = Target.Table_Name
  AND Source.Column_Name  = Target.Column_Name
  AND Source.character  = Target.Character
  WHEN MATCHED 
   THEN UPDATE SET [Target].[column_count] = [Target].[column_count] + Source.Number
  WHEN NOT MATCHED 
   THEN INSERT ([table_schema] 
      ,[table_name] 
      ,[column_name] 
      ,[character] 
      ,[column_count])
     VALUES
      (Source.table_schema
      ,Source.table_name 
      ,Source.column_name
      ,Source.Character
      ,Source.Number);

 SELECT @CurrentRow = @CurrentRow + 1  
  END


 END 
SET @StatusMessage = 'Done - Check dbo.charactercount table'
RAISERROR (@StatusMessage , 10, 1) WITH NOWAIT 
GO

Code to use this procedure is as follows ...

--Remember to clear down the results table before you run

TRUNCATE TABLE dbo.charactercount
GO


-- Provide the name of a column, like this
-- The final parameter is the number of rows to test.
EXEC charactercount_insert 'schemaname', 'tablename', 'columnname', 200
GO



-- View the data like this 
SELECT * FROM charactercount ORDER BY [character]
GO


-- Dynamically generate statements to interogate all columns, as follows

SELECT command = 'EXEC charactercount_insert ''' + TABLE_SCHEMA + ''',''' + TABLE_NAME+ ''',''' +  COLUMN_NAME + ''',200'
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE LIKE '%char%'

Stored Procedure Parameter Validation

This procedure isn't supposed to do anything. It's merely here as a reminder to validate your input parameters!
Here I'm using RAISERROR to return error code 18 and terminate the stored procedure...
18 is a Nonfatal Internal Error that allows you to return the message to the log.

CREATE PROCEDURE [tools].[dosomething]
(
     @databasename  varchar(200) 
    ,@recoverymodel             varchar(11)
 
)
AS

BEGIN

IF @recoverymodel NOT IN ('FULL','SIMPLE','BULK_LOGGED')
 BEGIN
     RAISERROR('Invalid parameter: @recoverymodel should be FULL, SIMPLE or BULK_LOGGED', 18, 0)
  RETURN
 END

-- Do stuff here...

END

TSQL : Finding the last occurrence of a character

DECLARE @fullpath VARCHAR(500)
SET @fullpath = 'E:\Masters\MP3 DJ Masters\MP3 CD 320\Artist Albums\Sonique\Hear My Cry\01 Sonique - It Feels So Good.mp3'
 
SELECT   CHARINDEX('\', REVERSE(@fullpath)) AS [CharPosition] -- Final '\' character in the data
                ,LEFT(@fullpath,LEN(@fullpath) - CHARINDEX('\', REVERSE(@fullpath))) AS [Folder]
                ,RTRIM(RIGHT(@fullpath, CHARINDEX('\', REVERSE(@fullpath)) - 1)) AS [FileName] 

SQL : Numeric Data Search Script - Version 1

After investigating another unknown database, I have adapted my Data Search Script to search for values in numeric columns.

/*
NUMERIC DATA SEARCH SCRIPT

version 1 :  

YOU MUST RUN THIS IN GRID VIEW!!! 
*/

declare @columncount int
declare @searchdata int
-- set string to search for here
set @searchdata = 200908

select @columncount = count(*) from information_schema.columns
 where data_type in ('int','bigint')
 and table_name not like 'sync%'
 and column_name <> 'order' 
 and table_name in (select name from sys.sysobjects with (nolock)  where type = 'u')
select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = '''''
union
select 'if exists (select 1 from [' + 
  table_schema + '].[' + table_name + 
  '] with (nolock) where [' + 
  column_name  + '] = ' + CAST(@searchdata as VARCHAR(15)) + ' ) ' + char(10) +
  ' begin ' + char(10) +
  '  set @resultslist = @resultslist + char(10) + ''select ''''' + table_name + ''''' as [table_name], [' + column_name + '],* from ['+ table_schema + '].[' + table_name + '] with (nolock) where [' + column_name + '] = ' + CAST(@searchdata  as VARCHAR(15)) + ''''+ char(10) +
  '  raiserror( ''' + table_schema + '.' + table_name  + '/' +column_name  + ''', 10, 1) WITH NOWAIT' + char(10) + 
  ' end ' + char(10) + 
  '' + char(10) +
  ' raiserror( ''' + cast(cast(cast(rank() OVER (ORDER BY table_schema, table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%%' +
  ''', 10, 1) WITH NOWAIT' + char(10)
 from  information_schema.columns with (nolock)
 where data_type in ('int','bigint')
 and table_name not like 'sync%'
 and column_name <> 'order' 
 and table_name in (select name from sys.sysobjects with (nolock) where type = 'u')
union
select 'print @resultslist' + char(10) 

SQL Resources #2 : Bad habits to kick

Aaron Bertrand has a great collection of blog posts in the series 'Bad habits to kick'.
They also serve as a great list of 'bad coding practice' i.e things NOT to do in the first place. If you are learning SQL or revising / updating your knowledge they also make great reading.

In short, 'Learn from the mistakes of others '...


Further Reading -

• Bad habits to kick : inconsistent naming conventions 
• Bad habits to kick : using SELECT or RETURN instead of OUTPUT 
• Bad habits to kick : using dashes and spaces in entity names 
• Bad habits to kick : declaring VARCHAR without (length)  
• Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)  
• Bad habits to kick : using old-style JOINs  
• Bad habits to kick : using loops to populate large tables 
• Bad habits to kick : ORDER BY ordinal  
• Bad habits to kick : abusing triggers  
• Bad habits to kick : choosing the wrong data type  
• Bad habits to kick : making assumptions about IDENTITY 
• Ladies and gentlemen, start your semi-colons!

My Perfect World

This is an document on 'approach' I've composed this for a number of reasons. It covers a lot of things I've done/could do/will do on future projects. Each area could be massively expanded in terms of detail but it should be enough to inspire ideas in others / keep me on the right track.

Introduction

The industry has encouraged front end developers to treat databases purely for storage. Sadly the average developer knows little beyond SELECT, INSERT, UPDATE & DELETE.

ORM frameworks are the current trend, marketed at speeding development, portability of code and encapsulating business rules. This however can be at the cost of SQL efficiency and ultimately performance.

On my current project, the following facts are regularly ignored –

• The database server has far greater processing capability & memory than the client.
• SQL has a wealth of functionality which (when used correctly) performs fantastically.
• RDBMS programmability is constantly evolving.

This post therefore is to put on record the things I would do in a perfect world.  They aim to be all encompassing for those starting out on a new transactional application.

Database design

Normalise as much is practical. Aim for 3NF (3^rd Normal Form) but be flexible if performance is going to be compromised by this goal. Do not let an ORM tool design the database. The result will have far too many objects and as a result will be heavy on the number of joins.

Data Types

Closely match the data you want to store with a data type.  For Numeric types this means the precision.  For text this means looking at the length and characters you need to store.

Only use Unicode column types (NCHAR, NVARCHAR) when you need to store Unicode. They require twice the storage yet are frequently used ‘just in case’.

Ensure you don’t use Deprecated data types, i.e. ones that will be removed in future versions of SQL Server. Ntext, text and image were listed as deprecated for SQL 2008, but actually still appeared in both SQL 2012 & 2014.

Data Sizing

Numerical Data types should allow for growth but not be wasteful of storage. Chose tinyint, int, bigint etc appropriately.

Character Columns should be sized appropriately for their content. Do not just guess and oversize them due to not researching the data. Chose between CHAR , VARCHAR  and their Unicode equivalents appropriately.

By default CHAR(n) will store n characters and the storage requirement will be n bytes. Unless overridden by ANSI_PADDING settings, CHAR columns containing less data will be padded with blanks up to n characters.

VARCHAR(n) will store up to n characters without padding. There is a 2 byte storage overhead for defining a column as variable length, hence  storage required is n+2 bytes.

Stay away from VARCHAR(Max) unless you really need over 8000 characters (the maximum VARCHAR can be defined as). VARCHAR(MAX) can hold up to 2147483647 characters. In reality, the sizes and types of the other columns present in your table will determine  if data is not stored ‘in row’.

Nullability

NULL is the presence of no data and is different from an empty string. NULL values represent data that we do not have.  NULLability often provokes strong feelings from those who have not been involved in database design. Some languages (e.g coldfusion) treat it as an empty string which simplifies matters for front end developers.

• Numeric NULLs are important as you cannot represent an unknown number using 0 (zero).  Zero is not the same as unknown when dealing with finances or statistics.
• Character columns could represent missing data as either an empty string ‘’ or as NULL.  This depends on the application that entered the data. Allowing both would mean you must test for both to return empty columns.

There are a lot of theories, personal preferences and corresponding web posts surrounding the use of NULLs. Proving or disproving them is difficult across multiple database platforms, implementations of SQL and table sizes.

Storage

1. In a fixed size column e.g. INT, storing NULL takes the size of the column. (For an INT, 4 bytes). 
2.  In a fixed length column e.g.CHAR, storing NULL takes the length of the column.  (For CHAR(10), 10 bytes).
3. In a variable length column e.g. VARCHAR , a NULL is stored as 0 bytes (for the data) + 2 bytes (the storage overhead for defining ‘variable’ length).

Code Complexity 

Handling NULL values correctly in SQL Server is done via

1. The WHERE clause e.g. WHERE column IS [NOT] NULL
2. Inline Functions e.g. COALESCE, ISNULL, NULLIF.

NULL values cannot be compared using equals (=). If an application allows a character column to be either NULL or an empty string  (‘’) then you must account for both.

Performance

If there are many NULL values in a queried column, the index SQL Server may decide it is easier to perform a ‘table scan’, i.e. ignore an index in place. A table or clustered index scan means the value of the queried column is compared for each row in turn. This could become an issue as a system grows.
From SQL 2008 onwards a filtered index could be utilised to explicitly ignore NULL values.

NULL By Design

By allowing NULLs in a (traditionally Boolean) BIT column, the data type can store 3 states.
1, 0, NULL representing TRUE, FALSE and UNKNOWN.
1 byte is used to store up to 8 bit columns, 9 to 16 bit columns will be stored in a 2^nd byte etc.
Storing multiple BIT columns in the same table occupies a minimal amount of storage.

Some ideas to avoid NULLs

• Use Left Joins to a second table to return the presence of data, rather than hold NULLs in the source.
• Horizontally partition a table into mandatory , frequently used & optional columns. Place optional columns where NULLs are frequently present in the second table with a 1-2-1 mapping on primary key.
• Replace NULLs with a fixed value  ( -1) to represent unknown. This would be indexable. Use default values when defining columns to populate this default value
•  Expand upon this idea with further keys for Not Applicable, Not Yet Known, Truly Unknown.
• Mask NULLs from the application by using SQL Programability Objects (views , functions & stored procedures).

Constraints 

There are 4 types of constraint that can be defined on a column in a table.

Primary Key Constraint

A Primary Key is a column (or group of columns) that uniquely identify a row.
A Primary Key cannot be null.  No duplicates are allowed in primary keys.

Foreign Key Constraint

Foreign Keys enforce integrity of relationships and prevent orphaned records. 
They will have an overhead in terms of enforcing the order in which data is loaded.

Check Constraint

A check constraint can ensure data validity and enforce business logic by only allowing values that match a defined range.

Unique Constraint

A column can be required to have unique contents by a UNIQUE Constraint in the definition of the table. It is implemented as a UNIQUE Index internally to SQL Server. Personally I think it is better to explicitly define a unique index on a column instead.

Advantages of a unique index are that it can be -

• explicitly named (and therefore tested for in rollout scripts)
• searched (it is an index after all)
• disabled (if you wanted to allow duplicate values, you cannot disable a unique constraint without altering the table itself
•  named in an index hint (if you find it necessary to force index use)

In a transactional database, having these constraints in place is best practice and ensures data integrity. Attempts to violate these rules could be captured by a well written application.

In a data warehouse scenario where speed of loading records is paramount, lots of constraints would not be appropriate. Having them would slow down record insertion, but data would also have already been validated at source.

Style

Chose a style and stick to it!
Have a naming convention for objects.
Avoid punctuation or characters in object names that you cannot quickly type.
Avoid Reserved Words, i.e. don’t call a table ‘table’, a procedure ‘procedure’ (or vice versa) or a column ‘datetime’.

Adopt a coding style you can easily maintain.
Place SQL keywords on separate lines.
Use tabs and spaces to align column and table names.
Sensibly alias joined tables with meaningful names.
Format complicated WHERE clauses with brackets to make them more readable and split them over multiple lines. Operator Precedence is not an easy thing to memorise

Schemas 

Schemas were introduced in SQL 2005. Whilst they have management and security benefits their most immediate use is to allow the separation of database objects into logical groups. The AdventureWorks downloads on codeplex are a good place to see this in action.
I would use them to separate Application data into areas of Configuration, Access Control, Auditing as well as the application data itself.

SQL Solace : Schema Separation

Programming SQL - Use the database …

At the time of writing, I would approach a new application with the following techniques.

• Use Table Valued Functions and Stored Procedures to return data.
• Use Views (carefully) if sql code is reused frequently. Do not build views on views.
• Use Stored Procedures to perform DML operations (INSERT, UPDATE & DELETE) rather than allowing an application direct access to the tables.
  (ORM Frameworks can still use stored procedures – You can market this as the best of both worlds).
• Perform error checking with TRY/CATCH blocks.

I would do my upmost to avoid …

Scalar functions
These are called once for every row in the results set
They run on a single thread
(they do not scale)

RBAR Processing

An acronym for ‘Row By Agonising Row’, this describes the iterative nature or repeating the same code again and again. This could be in CURSORS or a WHILE loop, the thinking here is the opposite of a SQL SET based approach.

Triggers

I am not a massive fan of triggers as they can quickly make a database difficult to maintain.
They hide business logic and need to be documented upfront (which we all do, right?)
One of the first things to do when joining a development project is to look for the existence of triggers.

They do allow some useful functionality e.g. an INSTEAD OF trigger can allow a view to become updatable by containing a code to update multiple underlying tables correctly.

Security – Authentication & Authorization

From Day 1 define the necessary security and create ;

1. User accounts the application will require. Use the principle of least privilege to assign permissions to these logins.
2. A maintenance account for executing jobs that manage the SQL environment e.g. backups, maintenance plans etc.
3. An administrator account for managing the server and applying updates.

You can chose to tie your application security and sql security together via Windows Authentication or develop separate tables for logins & passwords.

Testing

The world of testing has multiple disciplines nowadays. From a database developers perspective there are a number of specific areas of interest.

Unit Testing

Unit Testing is a method of functional testing that can be applied to small modules of code (units). SQL Stored procedures and functions lend themselves well to Unit Testing as they perform actions and provide outputs according to supplied parameters i.e. their inputs.

Detailed Database Testing can also be done to ensure all aspects of database programming, data validity and referential integrity. I would personally be doing this as I go along but the technique is perfectly valid for checking the work of others. http://en.wikipedia.org/wiki/Database_testing

Several ‘Non Functional’ testing disciplines are essential too.

Performance Testing

These are conducted on designated hardware and can be subdivided into

• Configuration Testing – How making configuration changes on known hardware affects database performance.
• Load Testing – How the system behaves under a defined load
• Stress Testing – Increasing the load to determine the limits of a system.
• Soak Testing – Endurance testing to ensure a system can cope with a continuous load.
• Spike Testing – How the system performs when suddenly under a greatly increased load, i.e. number of concurrent users, number of active transactions.
• Volume Testing – How the size of a database can influence all the above tests.

Scalability Testing adds to the above list by measuring how well a system can –

• Scale up (improve hardware by adding RAM, CPUs or disks)
• Scale out (add extra servers)

Both approaches could enable more users and faster response times if the application/database code also scales.

Applied SQL Testing

Database performance testing does not need to become complicated and can be aspired to and repeated as development progresses.

Data Generation

Generating a volume of test data is relatively easy. Especially as test data sets and even random data generators are freely available on the web. Random transactional data can be generated ad-hoc. If stored procedures have been used by the application to enter data, calling them again from a TSQL WHILE loop is an easy task and will quickly build a valid data set far faster than the application is able to.

Concurrency

Testing concurrency means a reproducing a realistic load.  In reality users will simultaneous be performing different actions at the same time. User A will be entering records, User B will be searching, User C will be amending data etc.

Testing an application can be automated with expensive tools, or could be mimicked in SQL. To do this, we need to know patterns of usage i.e. the order in which stored procedures are called for a given application action. These can be reproduced in test scripts and populated with random data at run time. Timings can also be recorded in a table with little overhead. 

Dynamically generating SQL agent jobs is a technique we can draw on to generate multiple requests. We can randomise the number of simultaneous users, the actions they are performing and the timings between requests. This will not reproduce client or network issues as results would not leave the server. It provides a solid benchmark for the capability of the SQL backend.

Data Changes

Knowing what data has changed, when, how and by whom fulfils auditing requirements. It can also be used to intelligently load a data warehouse with incremental changes. To achieve this I advocate ensuring development efforts are compatible with a form of change tracking right from the start.

Change Tracking (SQL 2008+)

Change Tracking provides a synchronous update of data changes. Once enabled on a table, you need to query the functions that return the changes within 24 hours, else they will be lost.

Change Data Capture (SQL 2008+)

This requires Enterprise Edition of SQL and utilises the transaction log and sql server agent to provide an asynchronous (after transaction)  update.

Roll your own!

A variety of methods can tell if a row has changed. Using TRIGGERs is the simplest, but deploying a custom auditing solution could also be achieved using either ROWVERSION columns or column CHECKSUMs.

Documentation

Document your schema, code, standards and changes from day one.
Tools exist to assist with documentation, but SQL Server’s own ‘Extended Properties’ are a highly flexible way to achieve this too. The end result is a self-documenting database.

Application Design Considerations 

Here are the things I wish applications would do.

Keep application code light  

Push all data manipulation back to the database server.

Perform connectivity handling 

By this I mean handle timeouts by allowing retries, customising retry intervals and the number of retry attempts performed. During this process provide the user with a display e.g. a countdown to the next retry.

Be a true multi user application

Implement checking to see if a record has been changed by another user since it was loaded. It may be a rare event that two users would change the same client record, but I would advocate adding checking to ensure data is not lost in this way.

Centralise Configuration Information

Hold Configuration information in SQL Tables. Ideally an application configuration file would be a single xml file containing a server, database, and user names as well as an (encrypted) password. All other configuration items would be fetched from database table (in the Config schema!). Login would be permitted by tables in the AccessControl schema and tables in the Config schema would them be queried again for user specific settings.