SqlAutoGen 0.1.12-dev

SqlAutoGen

Idea behind

I love personal data. But personal data should stay personal! I also like to prototype alot. But the data creation and storage is always a mess in these situations. I hope to change these things with this Roslyn SourceCode Generator.

H:\IDE\Toolbox\Rider\plugins\dpa\DotFiles\JetBrains.DPA.Runner.exe --handle=10312 --backend-pid=8480 --etw-collect-flags=67108622 --detach-event-name=dpa.detach.8480.1 --refresh-interval=1 -- "C:\Program Files\dotnet\dotnet.exe" "C:\Program Files\dotnet\sdk\9.0.100-preview.7.24407.12\Roslyn\bincore\csc.dll" @C:\Users\silen\AppData\Local\Temp\JetBrainsRider\RoslynArgs\a1fd39960b410b82b33ecf3ff251ad38

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.

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.
• Run Commands in different Envoirment: Run Shell Commands via SimpleExec through 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.

Priorities

All my decisions are based on these principles:

• ✅ Personal ➡️ Enterprise
• ✅ Changeable ➡️ Scalable
• ✅ Storage ➡️ Performance
• ✅ Accessibility ➡️ Security
• ✅ Sync ➡️ Async

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

• "*": creates a additional column with the name and suffix "Hash" where each entry is unique and will be checked when creating a new entry.

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

Score

Task

Task:

Examples

Create Database

  
[ProgramCreate(@"  
TestApp  
ip.0.0.0.0(5555)  
")]  
internal partial class Program  
{  
    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()}]");    }  
    
    private static void TestCommand()    {        AnsiConsole.WriteLine($"TestCommand fired");    }    
    
    private static void CommandTest()    {        // CommandTest = TestCommand,  so it will be discarded    }    private static void CommandTests()    {        AnsiConsole.WriteLine($"CommandTests fired");    }  
    
    private static void TestParameterCommand(string parameter)    {        AnsiConsole.WriteLine($"TestParameterCommand parameter:[{parameter}]");    }    
    
    private static void CommandTestParameter(string parameter)    {        // CommandTestParameter = TestParameterCommand,  so it will be discarded    }    private static void CommandTestParameters(string parameter)    {        AnsiConsole.WriteLine($"CommandTestParameters 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 TestDatabaseService;  
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;    
    }
};  

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.

Program Options:

• LoadExternal: Load Services from external Sources.
• SingleInstance: Ensure only one Instance of the app is created

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

namespace SqlAutoGen.Sample;  
  
using Data.Attributes;  
using System.Collections.Generic;  
using System.Threading.Tasks;  
using GenHTTP.Api.Infrastructure;  
using GenHTTP.Modules.Layouting.Provider;  
using Spectre.Console;  
using Spectre.Console.Cli;  
[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,  
  
}  
  
  
public enum Models  
{  
    None,  
    Gpt = 1,  
    Llama = 2,  
    SeleniumGpt = 3  
}  
  
[DatabaseCreate(@"ModelsDatabase:SingleFile")]  
[TablesCreate(@"  
  
Llm: Name(word), int.TagCount, int.Pulls, Updated, Readme, License  
License:Content(word)  
Template:Content(word)  
Size:Parameter  
Quantization:Content  
Readme:Content  
Tags:Content  
link.ModelSizes:Llm,Size,Quantization  
enum.Models(SqlAutoGen.Sample.Models)  
  
")]  
  
public partial class ModelsDatabase  
{  
  
    private Template DefaultTemplate()  
    {  
        return new Template()  
        {  
            Content = "22",  
        };  
    }  
}  
  
  
public partial class ModelsDatabaseService  
{  
  
  
  
    private void TestExecute_Runner()  
    {  
        AnsiConsole.MarkupLine($"[green]Running TestStaticExecute_Runner[/]");  
    }  
  
    private  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();  
  
        }  
    }  
  
    private static void TestExecute_NoStatic_Runner()  
    {  
        AnsiConsole.MarkupLine($"[green]Running TestStaticExecute_Runner[/]");  
    }  
      
}  
public partial class ModelsDatabaseContext  
{  
    private List<ModelsDatabase.Template> InsertTemplates()  
    {  
        List<ModelsDatabase.Template> templates = new List<ModelsDatabase.Template>()  
        {  
            new ModelsDatabase.Template()  
            {  
                Content = "some content",  
            },  
            new ModelsDatabase.Template()  
            {  
                Content = "more content",  
            }  
        };  
        return templates;  
    }  
}  
  
  
  
[DatabaseCreate(@"CredentialsDatabase:SingleFile")]  
[TablesCreate(@"  
  
enum.AddressType:Local,Global,Country,22.Regional(Only for inner country usage)  
enum.LoggerTypes(Microsoft.Extensions.Logging.LogLevel)  
  
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  
  
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 CredentialsDatabase  
{  
  
}  
  
public partial class CredentialsDatabaseContext  
{  
  
    // Same as in Program.cs  
    // If the method has a Appendix/Prefix "Insert" and returns a list or array of an Table then it will get inserted into the table when the application starts    private List<CredentialsDatabase.Mail> InsertEmails()  
    {  
        List<CredentialsDatabase.Mail> emails = new List<CredentialsDatabase.Mail>()  
        {  
            new CredentialsDatabase.Mail()  
            {  
                FullMail = "fullmail@mail.com",  
                Provider = "provider1"  
            },  
            new CredentialsDatabase.Mail()  
            {  
                FullMail = "another_fullmail@mail.com",  
                Provider = "provider2"  
            }  
        };  
        return emails;  
    }  
};

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"