This project is read-only.
2012-05-13

The database diagram tool has a new domain.http://diagrams.seaquail.net/


2011-03-19

Check out the database diagram tool written for Chrome and the HTML 5 canvas. It generates scripts for a database schema based on the diagrams you set up. It's only a proof of concept, but it's fun to play with. http://www.seaquail.cz.cc/Diagram.aspx


Project Description

Sea Quail is a library for building SQL queries using objects in .Net. It's intended to work with any mainstream RDBMS. Add/remove tables, columns, and foreign keys, insert, update, delete, and select programmatically, without string building. It's developed in C#.


Uses
  • Database Agnostic applications
  • Data migration between different database management systems
  • Scenarios necessitating a dynamically alterable database schema
  • Construct Ad Hoc search queries without string building


Sea Quail Is-
  • A library for writing SQL queries
  • Intended to be used for querying and manipulating the database schema and data


Sea Quail Is Not-
  • An O/RM and isn't intended to be used in place of an O/RM
  • A DAL. It's more like a layer between the DAL and the database
  • Thoughtfully named


History


Feeling tired of O/RM frameworks for which class definitions were generated based on a database table schema, I was motivated to flip the paradigm and construct the database schema (at runtime) based on the class definitions. I decided that I'd like to try treating the database less like the model or foundation of the project and more like just a means of persisting and searching data. I thought, it'd be cool to just throw whatever I want into the database and have tables set up for it on demand.

With this as the goal, the first problem it seemed, would be to find a tool set for manipulating a database schema, and in keeping with the "I don't care about the DB" philosophy, it should not just work for one RDBMS, but should be flexible enough to work with any of them. I set out looking for this and came back empty-handed. There were proprietary libraries made for a specific RDBMS, most notably SQL Server's SMO, but I could find nothing that would operate independent of RDBMS.

So, generic SQL management and querying tools would have to be developed.


Goals
  • No other library in a solution incorporating this project should have to write SQL. All actual SQL should be generated (and preferably executed) in this library.
  • Queries should be built in an OO fashion, and objects should be used to represent the database objects(tables and columns) for their manipulation.
  • Support the widely used, powerful RDBMS's and the greatest of their common capabilities.


Code Sample

//===============================================================
// This will demonstrate table creation and record 
// inserting/selecting
//===============================================================


// Instantiate an SQAdapter, and set the connection string. 
// This adapter will write SQL intended for SQL Server.
SQLServerAdapter adapter = new SQLServerAdapter() 
{ 
    ConnectionString = "A SQL Server Connection String" 
};

// Check to see if the table, "Song" exists in the database.
// If it doesn't, then create it.
if (adapter.GetTable("Song") == null)
{
    // Create an SQTable and set the name, then add SQColumns.
    // This table will 
    SQTable song = new SQTable() { Name = "Song" };
    song.Columns.AddRange(new SQColumn[]
    {
        new SQColumn() 
        { 
            Name = "ID", 
            IsIdentity = true, 
            IsPrimary = true, 
            DataType = SQDataTypes.Int64
        },
        new SQColumn() 
        { 
            Name = "Artist", 
            DataType = SQDataTypes.String, 
            Length = 250
        },
        new SQColumn() 
        { 
            Name = "Album", 
            DataType = SQDataTypes.String, 
            Length = 250
        },
        new SQColumn() 
        { 
            Name = "Name", 
            DataType = SQDataTypes.String, 
            Length = 250
        },
        new SQColumn() 
        { 
            Name = "Year", 
            DataType = SQDataTypes.Int32
        },
    });

    // Pass the table to the adapter to create it in the database
    adapter.CreateTable(song);
}


// Insert data into the table using an SQInsertQuery
SQInsertQuery insert = new SQInsertQuery();
// Set the focal table of the insert statement
insert.PrimaryTable = new SQAliasableObject("Song");
// Set the column/value pairs. For year, I'm passing a variable
// to demonstrate paramaterization
string yearVar;
insert.SetPairs.AddRange(new SetQueryPair[]
{
    new SetQueryPair("Artist", "Air"),
    new SetQueryPair("Name", "Highschool Lover"),
    new SetQueryPair("Album", "The Virgin Suicides"),
    new SetQueryPair("Year", yearVar = adapter.CreateVariable("Year"))
});
// Add the parameter for the year data
insert.Parameters.Add(new SQParameter(yearVar, 1999));

// Execute the insert statement using the adapter
adapter.Insert(insert);


// Here's a select query to demonstrate conditions
SelectResult res = adapter.Select(new SQSelectQuery()
{
    Columns = new List<SQAliasableObject("*"),
    PrimaryTable = new SQAliasableObject("Song"),
    Condition = new SQCondition("Artist", RelationOperators.Equal, "Air")
        .And("Name", RelationOperators.Equal, "Highschool Lover")
});

Last edited May 13, 2012 at 2:46 PM by sledgebox, version 32