This is the first in a series of three articles describing a project that I undertook to solve a specific problem in my working environment. The project has two core outcomes; a new XML file format for describing the schema of a database, and an executable for transforming the new format into SQL. Along the way I'll share thoughts from the design process and introduce some supporting code libraries which may have applications outside this project. I have assumed a basic understanding of several technologies, but will also provide links and definitions so hopefully nobody feels excluded. In this first article I will describe the background to the project and talk about the development of the file format and an XSD schema. In the second article I will focus on creating a code model that maps onto the file format. The third and final article will cover a simple approach to code generation and tie up all the loose ends.
BackgroundI use Microsoft SQL Server (usually just known as SQL Server) regularly in a team environment. When I talk about SQL in these articles, I am really referring to Microsoft's version of the language called T-SQL, though other versions of the language could easily be addressed in the future. SQL has a long and distinguished history and is used by millions of people in all sorts of projects including mine, but this project exists because it has shortcomings that I find frustrating. SQL is commonly used in two distinct ways; as a DDL and for CRUD queries. When a new database is created it is not only empty of data, but also empty of structure. Before data can be added (e.g. the details of a person) a table must be added to contain the data. This is done in SQL with statements such as these:
CollapseCREATE TABLE [dbo].[EXAMPLE_TABLE](
[PK] [int] IDENTITY(1,1) NOT NULL,
[EXAMPLE_COLUMN1] [varchar](50) NOT NULL,
)Definition of a table called 'EXAMPLE_TABLE' with two columns; 'PK' and 'EXAMPLE_COLUMN1'. Read more: Codeproject
BackgroundI use Microsoft SQL Server (usually just known as SQL Server) regularly in a team environment. When I talk about SQL in these articles, I am really referring to Microsoft's version of the language called T-SQL, though other versions of the language could easily be addressed in the future. SQL has a long and distinguished history and is used by millions of people in all sorts of projects including mine, but this project exists because it has shortcomings that I find frustrating. SQL is commonly used in two distinct ways; as a DDL and for CRUD queries. When a new database is created it is not only empty of data, but also empty of structure. Before data can be added (e.g. the details of a person) a table must be added to contain the data. This is done in SQL with statements such as these:
CollapseCREATE TABLE [dbo].[EXAMPLE_TABLE](
[PK] [int] IDENTITY(1,1) NOT NULL,
[EXAMPLE_COLUMN1] [varchar](50) NOT NULL,
)Definition of a table called 'EXAMPLE_TABLE' with two columns; 'PK' and 'EXAMPLE_COLUMN1'. Read more: Codeproject