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

Sea Quail Is-


Sea Quail Is Not-


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


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")
});