SqlAutoGen 0.0.10

SqlAutoGen

Introduction

SqlAutoGen is a C# library designed to generate complete executables using a straightforward naming convention. It integrates a SQLite database using Microsoft.EntityFrameworkCore and exposes a local API via GenHTTP, transforming an ORM database structure into JSON. Additionally, it creates API endpoints for each table with common CRUD methods and includes data validation, creation and generation capabilities through the Bogus Library.

Goal

To provide an easy and efficient way to manage, validate, score, and query the vast amounts of data produced daily. The focus is on local machine usage and ease of integration, aiming to automate repetitive tasks.

Features

Completed Features:

• Roslyn Source Code Generation: Roslyn Source Code Generation: Automatically generates source code to build executables based on naming conventions.
• Local SQLite Database Integration: Utilize Microsoft.EntityFrameworkCore for seamless local SQLite database integration.
• Local API via GenHTTP: Provide a local API facilitating straightforward database interactions.
• ORM to JSON Conversion: Transform ORM database structures into JSON format.
• CRUD API Endpoints: Offer API endpoints for basic CRUD operations.
• Data Generation Using Bogus: Populate databases with synthetic data for testing.
• Command Execution: Spectre for easy Task Execution inside Runtime.

In Development:

• Task Execution and Scheduling: Manage application methods using a database or API.
• Interactive Dashboard: Facilitate data manipulation through a UI frontend and tabulator.js.

Planned:

• Data Scoring: Implement validation and scoring for incoming data.
• Data Creation: Generate aggregated data based on evaluations.
• Data Visualization: Introduce data plotting with relationships using d3-force.
• Logging: Fetch and store logs and exceptions with Serilog.

Usage

Tables

Every partial class with DatabaseCreateAttribute will follow these rules:

Each table created with the TablesCreate has a primary key "Id" which will be used for all the common CRUD operations. Each line represents one Table where the left Side are the Table type.TableName(options) and on the right side are all the Columns with the same structure as the Table type.ColumnName(generate) but inside the brackets are the data creation options

The rules for the SqlAutoGen scheme is as follows:

• multiple lines are allowed where one line is a Table with its Columns
• Table and Columns delimiter is colon and a comma seperates the Columns from ech other
• the common scheme is always:type.TableName(options):type.ColumnName(generate),type.ColumnName(generate.ColumnName) ,type.ColumnName(generate.ColumnName.ColumnName)
• default type for Table is "key" and default for Columns is "string"
• example "Account:Name(firstname),LastName(lastname),datetime.LastSeen(dateupdate), int.Visited(uint)"
• the only requirment is the TableName, the rest is optional
• type and TableName,ColumName allows for letters and numbers. TableName,ColumName also allows special characters as prefix
• inside the brackets all characters are allowed but nested brackets needs to be closed
• all errors get discarded

Table Types

• "link"
• "enum"

Columns

Column Types

• "string"
• "guid"
• "byte"
• "bytes"
• "float"
• "int"
• "datetime"
• "bool"
• "boolean"
• "double"
• "dateupdate"

Column Options

• "!"
• "?"
• "#"
• "+"
• "+"

Examples

Account:Name,LastName,Username,Mail

will create a table with the name Accounts and the columns Id, Name, LastName, Username, Mail and all corresponding methods.

Enum

With the enum prefix, each column is a read-only value in a table with Id, Name, and Description (optional) columns. The id corresponds to the enum value, and the name to the value name. You can choose the value by prefixing it on the column name, for example 22.Regional. You can choose the description by using brackets after the column name 22.Regional(Only for inner country usage).

enum.AddressTypes:Local,Global,Country,22.Regional(Only for inner country usage)

It is also possible to use an existing enum directly by passing the full namespace and name through the brackets

enum.LoggerTypes(Microsoft.Extensions.Logging.LogLevel)

enum.EnumTest(SqlAutoGen.Sample.Enums.TestEnum)

Link

link.Location:Account,Address,Login
enum.AddressTypes:Local,Global,Country,22.Regional(Only for inner country usage)
City:Address(address),Street(street)
Account:Name(firstname),LastName(lastname),Username(username.Name.LastName),Mail(email.Name.LastName),Email
Address:Street, Prefix, Gps, AddressTypes, Number

#TODO finish link explanations

Program Options:

• LoadExternal
• NoCommand

Score

Task

Task:

Examples

Create Database

using SqlAutoGen.Data.Attributes;
using SqlAutoGen.Data.Enums;

namespace TablesExample;

[DatabaseCreate(name: "TestDatabase", path: "database")]
[TablesCreate(@"

enum.AddressType:Local,Global,Country,22.Regional(Only for inner country usage)

Address:AddressTypes,Prefix,Name

")]

Database Options:

• NoBogusData: Prevents the generation of synthetic data using the Bogus library, ensuring only real or manually entered data is used.
• SingleFile: Generates all the source code into a single file for simplicity.
• NoApi: Skips generating API endpoints, leaving only the DatabaseContext for data operations.
• NoMethodInjection: Excludes method injection in the generated code, making it more static and less dynamic.
• NoConsoleDatabase: Disables the ability to interact with the database through console commands.
• LogDatabase: Enables logging for all database operations, storing logs related to the database interactions.
• LogApi: Enables logging for API operations, capturing and storing logs generated by API requests and responses.
• ExceptionDatabase: Captures and stores exceptions raised during database operations.
• ExceptionApi: Captures and stores exceptions raised during API operations.
• Exception: Combines both ExceptionDatabase and ExceptionApi, enabling full exception handling.
• Log: Combines both LogDatabase and LogApi, enabling full logging for both database and API operations.
• LogException: Enables all logging and exception handling features.
• All: Enables all available features, including those that disable certain behaviors and fully enable logging and exception handling.

Naming

using SqlAutoGen.Data.Attributes;
using SqlAutoGen.Data.Enums;
using SqlAutoGen.Data.Enums.Builder;

namespace NamingExample;

[DatabaseCreate(name: "TestDatabase", path: "database")]
[TablesCreate(@"
enum.AddressType:Local,Global,Country,22.Regional(Only for inner country usage)

enum.LoggerTypes(Microsoft.Extensions.Logging.LogLevel)

enum.EnumTest(SqlAutoGen.Sample.Enums.TestEnum)

Email:Provider(protocol), FullEmail(email.Provider)

Street:string.Name(street)

Gps:double.Lat(lat),Hash(hash),double.Lon(lon)

Login:Hash(hash),Password(password)

Account:string.Name(firstname),LastName(lastname),Username(username.Name.LastName),Mail(email.Name.LastName),Email

Address:Street,Prefix(streetsuffix),Gps,int.Number(int),AddressTypes,EnumTest

link.Location:Account,Address,Login
")]

Templates

Templates need to be referenced in the .csproj file and have the ending .template. After that, they can be referenced in TablesCreateAttribute. Important. Any changes done to these templates need a restart of the IDE.

<ItemGroup>
    <AdditionalFiles Include="Templates\testdatabase.template" />
    <None Remove="testdatabase.template" />
    <AdditionalFiles Include="Templates\account.template" />
    <None Remove="accounts.template" />
</ItemGroup>

Program

ProgramCreateAttribute is only active on the Program class and will discarded everwhere else.

using SqlAutoGen.Data.Attributes;
using SqlAutoGen.Data.Enums;
namespace AppExample;

[ProgramCreate("0.0.0.0", 5555)]
partial class Program;

Full Example with all Functionialties

[ProgramCreate(@"
TestApp
ip.0.0.0.0(5555)
")]
internal partial class Program
{


    // If the method located under the class Program is a private static void method and have these specific parameters then they get injected at different points when starting the application
    //
    private static void TestCommandContext(CommandContext command)
    {
        AnsiConsole.WriteLine($"TestCommandContext command:[{command.GetType()}]");
    }

    private static void TestOptions(Options options)
    {
        AnsiConsole.WriteLine($"TestOptions options:[{options.GetType()}]");
    }

    private static void TestLayout(LayoutBuilder layout)
    {
        AnsiConsole.WriteLine($"TestLayout layout:[{layout.GetType()}]");
    }

    private static void TestIServerHost(IServerHost server)
    {
        AnsiConsole.WriteLine($"TestIServerHost server:[{server.GetType()}]");
    }



    // If the method has a Appendix/Prefix "Command" and is a private static void method then the name will be used as a command for the Spectre.Cli CommandLoop and the method can be executed with that command in runtime
    private static void TestCommand()
    {
        AnsiConsole.WriteLine($"TestCommand fired");
    }
    private static void CommandTest()
    {
        AnsiConsole.WriteLine($"CommandTest fired");
    }
    private static void CommandTests()
    {
        AnsiConsole.WriteLine($"CommandTests fired");
    }

    // string parameters are allowed and can be parsed by adding ":" after a command example: "testparameter:Thats a parameter".
    private static void TestParameterCommand(string parameter)
    {
        AnsiConsole.WriteLine($"TestParameterCommand parameter:[{parameter}]");
    }
    private static void CommandTestParameter(string parameter)
    {
        AnsiConsole.WriteLine($"CommandTestParameter parameter:[{parameter}]");
    }
    private static void CommandTestParameters(string parameter)
    {
        AnsiConsole.WriteLine($"CommandTestParameters parameter:[{parameter}]");
    }
    private static void CommandTestus1(string parameter)
    {
        AnsiConsole.WriteLine($"CommandTestParameters parameter:[{parameter}]");
    }
    private static void CommandTestus1()
    {
        AnsiConsole.WriteLine($"CommandTestParameters ");
    }
    private static void Testus1Command()
    {
        AnsiConsole.WriteLine($"CommandTestParameters ");
    }

    // all the non Static methods in StartCommand with either no parameter or a simple string will be added to the CommandLoop/ExecuteCommand
    // DatabaseContext that is flagged as db are available in here
    public partial class StartCommand
    {
        private void Testus1()
        {
            AnsiConsole.WriteLine($"Testus fired");
        }

        private void Testus1(string parameter)
        {
            AnsiConsole.WriteLine($"Testus parameter:[{parameter}]");

        }
    }
}



public enum TestEnum
{
    None,
    Test1 = 333,
    Test2 = 12,
    Test3 = 0,
    Test4,
    Test5 = 3,
    Test6,
    Test7,
    Test8,
    Test9 = 2,
    Test10 = 1,

}


[DatabaseCreate(@"TestDatabases(path/database):SingleFile")]
[TablesCreate(@"

enum.AddressType:Local,Global,Country,22.Regional(Only for inner country usage)
enum.LoggerTypes(Microsoft.Extensions.Logging.LogLevel)
enum.EnumTest(SqlAutoGen.Sample.Enums.TestEnum)

Gps:double.Lat(lat),Hash(hash),double.Lon(lon)
Country:string.Name(country),Gps
Street:string.Name(street),Gps
Address:Street,Prefix(streetsuffix),Gps,int.Number(int),AddressTypes,EnumTest

Mail:Provider(protocol), FullMail(email.Provider)
Login:Hash(hash),Password(password)
Account:string.Name(firstname),LastName(lastname),Username(username.Name.LastName),Mail(email.Name.LastName),Mail
Gmail:Password(protocol), Name(email.Provider)

link.Location:Account,Address,Street,Country
link.Credentials:Account,Mail,Login
link.AccountMail:Account,Gmail

")]


public partial class TestDatabase;



public partial class TestDatabaseContext
{

    private List<TestDatabase.Mail> InsertEmails()
    {
        List<TestDatabase.Mail> emails = new List<TestDatabase.Mail>()
        {
            new TestDatabase.Mail()
            {
                FullMail = "fullmail@mail.com",
                Provider = "provider1"
            },
            new TestDatabase.Mail()
            {
                FullMail = "another_fullmail@mail.com",
                Provider = "provider2"
            }
        };
        return emails;
    }
};




public partial class TestDatabasesService
{

    private static void TestStaticExecute_Runner()
    {
        AnsiConsole.MarkupLine($"[green]Running TestStaticExecute_Runner[/]");
    }

    private static void RunTestExecute()
    {
        AnsiConsole.MarkupLine($"[green]Running RunTestExecute[/]");
    }

    private void TestExecuteRun()
    {
        AnsiConsole.MarkupLine($"[green]Running TestExecuteRun[/]");
    }

    private void TestExecutRun()
    {

        for (int i = 0; i < 50; i++)
        {

            AnsiConsole.MarkupLine($"[yellow]Running TestExecutRun {i}/50[/]");
            Task.Delay(1000).Wait();

        }
    }

}

TODO Incoporate enough examples

data generation available:

• "udouble"
• "ufloat"
• "uint"
• "normalized"
• "alphanumeric"
• "boolean"
• "decimal"
• "double"
• "float"
• "int"
• "long"
• "uuid"
• "bytes"
• "byte"
• "id"
• "avatar"
• "email"
• "exampleemail"
• "username"
• "name"
• "usernameunicode"
• "domainname"
• "domainword"
• "domainsuffix"
• "ip"
• "port"
• "ipendpoint"
• "ipv6"
• "ipv6address"
• "ipv6endpoint"
• "useragent"
• "mac"
• "password"
• "internetcolor"
• "protocol"
• "url"
• "urlwithpath"
• "urlrootedpath"
• "abbreviation"
• "adjective"
• "noun"
• "verb"
• "ingverb"
• "phrase"
• "phone"
• "phonenumber"
• "phonenumberformat"
• "firstname"
• "lastname"
• "fullname"
• "prefix"
• "suffix"
• "findname"
• "jobtitle"
• "jobdescriptor"
• "jobarea"
• "jobtype"
• "word"
• "words"
• "letter"
• "sentence"
• "sentences"
• "paragraph"
• "paragraphs"
• "text"
• "lines"
• "slug"
• "datauri"
• "placeimgurl"
• "picsumurl"
• "placeholderurl"
• "loremflickrurl"
• "lorempixelurl"
• "account"
• "accountname"
• "amount"
• "transactiontype"
• "currency"
• "creditcardnumber"
• "creditcardcvv"
• "bitcoinaddress"
• "ethereumaddress"
• "litecoinaddress"
• "routingnumber"
• "bic"
• "iban"
• "zipcode"
• "postalcode"
• "postal"
• "city"
• "street"
• "cityprefix"
• "citysuffix"
• "streetname"
• "buildingnumber"
• "streetsuffix"
• "secondaryaddress"
• "county"
• "country"
• "fulladdress"
• "countrycode"
• "state"
• "stateabbr"
• "latitude"
• "longitude"
• "direction"
• "cardinaldirection"
• "ordinaldirection"
• "past"
• "pastoffset"
• "soon"
• "soonoffset"
• "future"
• "futureoffset"
• "between"
• "betweenoffset"
• "recent"
• "recentoffset"
• "timespan"
• "month"
• "weekday"
• "timezonestring"
• "companysuffix"
• "companyname"
• "catchphrase"
• "bs"
• "department"
• "price"
• "categories"
• "productname"
• "commercecolor"
• "product"
• "productadjective"
• "productmaterial"
• "productdescription"
• "filename"
• "directorypath"
• "filepath"
• "commonfilename"
• "mimetype"
• "commonfiletype"
• "commonfileext"
• "filetype"
• "fileext"
• "semver"
• "version"
• "exception"
• "androidid"
• "applepushtoken"
• "blackberrypin"
• "column"
• "databasetype"
• "collation"
• "engine"
• "review"
• "reviews"
• "vin"
• "manufacturer"
• "model"
• "vehicletype"
• "fuel"
• "guid"
• "hash"
• "day"
• "year"
• "number"
• "productcategory"
• "currencysymbol"
• "creditcardexpiry"
• "dateofbirth"
• "height"
• "width"
• "createdat"
• "datetimeupdate"
• "datemonthyear"
• "dateyearmonth"
• "monthdateyear"
• "monthyeardate"
• "yearmonthdate"
• "yeardatemonth"
• "addresscity"
• "addresspostalcode"
• "cityaddress"
• "citypostalcode"
• "postalcodeaddress"
• "postalcodecity"
• "addresscitypostalcode"
• "addresspostalcodecity"
• "cityaddresspostalcode"
• "citypostalcodeaddress"
• "postalcodeaddresscity"
• "postalcodecityaddress"
• "hour24minutesecond"
• "second"
• "secondminute"
• "secondhour"
• "secondminutehour"
• "hourminute"
• "currencycode"
• "duration"
• "durationdouble"
• "durationfloat"
• "gender"
• "age"
• "ageadult"
• "ageteen"
• "agechild"
• "agetwenties"
• "agethirties"
• "agefourties"
• "agefifties"
• "agesixties"

data generation: example: double.Accuracy(username)

data generation with params example:

Name(firstname),LastName(lastname),Username(username.Name.LastName),Mail(email.Name.LastName)

A dot after the selected generation type is used to determine which data to take as a parameter from a previously specified column that also generates data with the purpose of using part of it to generate the one its passed to.

generation types with params:

• "username"
• "usernameunicode"
• "email"
• "exampleemail"
• "urlwithpath"
• "urlrootedpath"
• "url"
• "name"
• "firstname"
• "lastname"
• "fullname"
• "findname"
• "prefix"
• "suffix"
• "jobtitle"
• "companyname"
• "companycatchphrase"
• "companybs"
• "phonenumber"
• "phonenumberformat"
• "randomword"
• "randomwords"
• "addresscity"
• "addresszipcode"
• "addresstate"