Test.cs

Go to the documentation of this file.

using System.Text.RegularExpressions;
using kinetica;
using Newtonsoft.Json;
using JDBC.NET.Data;
using NetTopologySuite.Geometries;


namespace Test
{
    public static class StringExt
    {
        public static string Truncate( this string value, int maxLength )
        {
            if ( string.IsNullOrEmpty( value ) )
                return value;

            return ( value.Length <= maxLength ) ? value : value[..maxLength];
        }
    }


    class Test
    {
        static void Main( string[] args )
        {
            Console.WriteLine("=============================");
            Console.WriteLine("= Test C# Project - Running =");
            Console.WriteLine("=============================");
            Console.WriteLine();

            if ( args.Length < 1 )
            {
                Console.WriteLine( "Missing URL as command-line parameter." );
                Console.WriteLine( "E.g., http://kinetica:9191" );
                return;
            }

            try
            {
                string server_url = args[0];
                Console.WriteLine( "URL: {0}", server_url );
                Console.WriteLine();

                ProcessArgs(args, out Kinetica.Options?  _ServerOptions, out string? _JdbcDriverPath);
                
                if (_ServerOptions == null) {
                    Console.WriteLine("Command line parameters can be either [<oauth_token>] or [<username> <password> <JDBC Bridge Driver JAR]");
                    throw new Exception("Invalid command line parameters");
                }


                // Run various tests
                // ----------------------------------
                TestDml(server_url, _ServerOptions);
                TestOauth2Authentication( server_url, _ServerOptions );
                TestAuthentication( server_url, _ServerOptions );
                TestAllTypesAllShard( server_url, _ServerOptions );
                TestAllTypesIpRegex3Shard0Pk(server_url, _ServerOptions);
                TestAllTypes3Pk3Shard( server_url, _ServerOptions );
                TestAllTypes3Pk0Shard( server_url, _ServerOptions );
                TestRecordRetrievalByKey( server_url, _ServerOptions );
                TestJdbc(server_url, _ServerOptions, _JdbcDriverPath);
            }
            catch ( kinetica.KineticaIngestor<RecordTypeAll>.InsertException<RecordTypeAll> ex )
            {
                Console.WriteLine( "Caught InsertException: {0}", ex.Message );
            }
            catch ( KineticaException ex )
            {
                Console.WriteLine( "Caught KineticaException: {0}", ex.Message );
            }
            catch ( Exception ex )
            {
                Console.WriteLine( "Caught Exception: {0}", ex.Message );
            }

            Console.WriteLine();
            Console.WriteLine("==========================");
            Console.WriteLine("= Test C# Project - Done =");
            Console.WriteLine("==========================");
        }  // end Main

        public static void ProcessArgs(string []args, out Kinetica.Options? options, out string? jdbcDriverPath) {
            jdbcDriverPath = null;
            if (args.Length <= 1)
            {
                // only host supplied no other options
                options = null;
            }
            else if (args.Length <= 2)
            {
                // host and oauth_token have been passed
                options = new()
                {
                    OauthToken = args[1]
                };
            }
            else
            {
                // host, username, & password have been passed
                options = new()
                {
                    Username = args[1],
                    Password = args[2]
                };

                if (args.Length >= 4)
                    jdbcDriverPath = args[3];
            }
        }

#region TestDml

        private static string GenerateRandomId()
        {
            Random random = new();
            int randomNumber = random.Next(1000, 9999); // Generates a random number between 1000 and 9999
            return $"ID_{randomNumber}";
        }
     
        private static string GenerateRandomUrl()
        {
            string[] domains = ["com", "net", "org", "io", "dev"];
            string chars = "abcdefghijklmnopqrstuvwxyz0123456789";
            Random random = new();

            string randomSubdomain = new(
                [.. new char[8].Select(_ => chars[random.Next(chars.Length)])]);

            string randomDomain = new(
                [.. new char[5].Select(_ => chars[random.Next(chars.Length)])]);

            string randomPath = new(
                [.. new char[6].Select(_ => chars[random.Next(chars.Length)])]);

            string domainExtension = domains[random.Next(domains.Length)];

            return $"https://{randomSubdomain}.{randomDomain}.{domainExtension}/{randomPath}";
        }

        private static string GenerateRandomName()
        {
            string[] firstNames = ["Alice", "Bob", "Charlie", "David", "Eve", "Frank", "Grace", "Helen", "Ivy", "Jack"];
            string[] lastNames = ["Smith", "Johnson", "Williams", "Brown", "Jones", "Garcia", "Miller", "Davis", "Rodriguez", "Martinez"];

            Random random = new();
            string firstName = firstNames[random.Next(firstNames.Length)];
            string lastName = lastNames[random.Next(lastNames.Length)];

            return $"{firstName} {lastName}";
        }

        private static string GenerateGeometry() {
            GeometryFactory factory = new();

            // Create a Polygon
            Polygon polygon = factory.CreatePolygon(
            [
                new Coordinate(0, 0),
                new Coordinate(10, 0),
                new Coordinate(10, 10),
                new Coordinate(0, 10),
                new Coordinate(0, 0) // Closing the polygon
            ]);
            return polygon.AsText();

        }

        private static string GenerateRandomPolygon()
        {
            GeometryFactory factory = new();
            Random rand = new();

            // Generate a random number of vertices (between 3 and 10)
            int numVertices = rand.Next(3, 11);

            // Generate random coordinate bounds
            double minX = rand.NextDouble() * 50;   // Min X in range [0, 50]
            double maxX = minX + rand.NextDouble() * 50; // Max X in range [minX, minX + 50]
            double minY = rand.NextDouble() * 50;   // Min Y in range [0, 50]
            double maxY = minY + rand.NextDouble() * 50; // Max Y in range [minY, minY + 50]

            Coordinate[] coordinates = new Coordinate[numVertices + 1];

            for (int i = 0; i < numVertices; i++)
            {
                double x = rand.NextDouble() * (maxX - minX) + minX;
                double y = rand.NextDouble() * (maxY - minY) + minY;
                coordinates[i] = new Coordinate(x, y);
            }

            // Ensure the polygon is closed (first and last point must be the same)
            coordinates[numVertices] = coordinates[0];

            // Create LinearRing and Polygon
            LinearRing ring = factory.CreateLinearRing(coordinates);
            Polygon polygon = factory.CreatePolygon(ring);

            return polygon.AsText();
        }

        private static DateTime GenerateRandomDate(DateTime start, DateTime end)
        {
            Random random = new();
            int range = (int)(end - start).TotalSeconds; // Get total seconds between the two dates
            return start.AddSeconds(random.Next(range));
        }

        private class LayerDTO
        {
            public string? ObjectId { get; set; }
            public string? Name { get; set; }
            public string? Url { get; set; }
            public string? BoundingBox { get; set; }
            public string? CreatedAt { get; set; }
            public string? UpdatedAt { get; set; }
        }

        private static void TestDml( string server_url, Kinetica.Options _ServerOptions )
        {
            Console.WriteLine("\n\n");
            Console.WriteLine("Test DML");
            Console.WriteLine("========");
            Console.WriteLine();

            string Ddl = @"CREATE OR REPLACE TABLE layers
            (
                ObjectId VARCHAR (primary_key) NOT NULL,
                Name VARCHAR NOT NULL,
                Url VARCHAR NOT NULL,
                BoundingBox GEOMETRY NOT NULL,
                CreatedAt DATETIME NOT NULL,
                UpdatedAt DATETIME NOT NULL
            )";

            // Establish a connection with Kinetica
            Kinetica kdb = new( server_url, _ServerOptions );
            string table_name = "layers";

            kdb.executeSql(Ddl);

            // Next two lines mandatory to register user defined record types
            KineticaType kineticaType = KineticaType.fromClass(typeof(LayerDTO));
            kdb.SetKineticaSourceClassToTypeMapping(kineticaType.getSourceType(), kineticaType);

            List<LayerDTO> records = [];

            // Insert Records
            for( int i=0; i < 10; i++ ) {
                LayerDTO record = new()
                {
                    ObjectId = GenerateRandomId(),
                    Name = GenerateRandomName(),
                    Url = GenerateRandomUrl(),
                    BoundingBox = GenerateRandomPolygon(),
                    CreatedAt = GenerateRandomDate(new DateTime(2024, 1, 1), DateTime.Now).ToString("yyyy-MM-dd HH:mm:ss"),
                    UpdatedAt = GenerateRandomDate(new DateTime(2024, 1, 1), DateTime.Now).ToString("yyyy-MM-dd HH:mm:ss")
                };
                records.Add(record);
            }

            Console.WriteLine($"Inserting {records.Count} records...");
            kdb.insertRecords(table_name, records);

            Console.WriteLine($"IDs inserted:");
            string? firstId = null;
            string? lastId = null;
            GetRecordsResponse<LayerDTO> getRecordsResponse = kdb.getRecords<LayerDTO>(table_name);
            foreach( var record in getRecordsResponse.data ) {
                firstId ??= record.ObjectId;
                lastId = record.ObjectId;
                Console.WriteLine(lastId);
            }
            Console.WriteLine();

            // Update Records
            Console.WriteLine($"Updating record {lastId}...");
            string expression = $"ObjectId = '{lastId ?? ""}'";
            var newValueMap = new Dictionary<string, string>
            {
                ["Name"] = "John Doe",
                ["Url"] = "www.kinetica.com"
            };
            UpdateRecordsResponse updateRecordsResponse = kdb.updateRecords<LayerDTO>(table_name, [expression], [newValueMap]);
            Console.WriteLine($"Updated {updateRecordsResponse.count_updated} record\n");

            // Delete Records
            Console.WriteLine($"Deleting record {firstId}...");
            expression = $"ObjectId = '{firstId ?? ""}'";
            DeleteRecordsResponse deleteRecordsResponse = kdb.deleteRecords(table_name, [expression]);
            Console.WriteLine($"Deleted {deleteRecordsResponse.count_deleted} record\n");
        }

        #endregion TestDml

        private class RecordTypeShort
        {
            public int i { get; set; }
            public int i8 { get; set; }

            public override string ToString()
            {
                return $"{{ i={i}, i8={i8} }}";
            }
        }  // end class RecordTypeShort


        private static void TestOauth2Authentication( string serverUrl, Kinetica.Options serverOptions )
        {
            Console.WriteLine( "\n\n" );
            Console.WriteLine( "Test OAuth2 Authentication" );
            Console.WriteLine( "==========================" );
            Console.WriteLine();

            if (serverOptions.OauthToken == string.Empty)
                Console.WriteLine("Skipping--OAuth token not passed");
            else
            {
                // Establish a connection with Kinetica
                Console.WriteLine("Token passed : " + serverOptions.OauthToken);
                Kinetica kdb_ = new(serverUrl, serverOptions);
                string json = JsonConvert.SerializeObject(kdb_.showSystemStatus(new Dictionary<string, string>()).status_map, Formatting.Indented);
                Console.WriteLine(json);
            }
        }  // end TestOauth2Authentication()

        private static void TestAuthentication(string serverUrl, Kinetica.Options serverOptions)
        {
            Console.WriteLine("\n\n");
            Console.WriteLine("Test Authentication");
            Console.WriteLine("===================");
            Console.WriteLine();

            string username = "csharp_test_user";
            string password = "csharp_test_pass";

            Console.WriteLine($"Establishing connection with {serverOptions.Username} user...\n");
            Kinetica kdb_ = new(serverUrl, serverOptions);


            Console.WriteLine($"Creating a user {username}...\n");
            try
            {
                if (kdb_.showSecurity([username]).types.Count != 0)
                    kdb_.deleteUser(username);
                kdb_.createUserInternal(username, password);
            }
            catch (KineticaException ex)
            {
                Console.WriteLine($"Error creating user: {ex.Message}");
            }


            Console.WriteLine($"Establishing a connection with {username} user and valid password...\n");
            Kinetica.Options connectOptions = new()
            {
                Username = username,
                Password = password
            };
            Kinetica kdbUser = new(serverUrl, connectOptions);
            object gotUsername;
            kdbUser.getRecordsByColumn("ITER", ["USER() AS username"]).data.First().TryGetValue("username", out gotUsername);

            Console.WriteLine($"Created user name: {gotUsername}\n");


            Console.WriteLine($"Establishing a connection with {username} user and invalid password...\n");
            try
            {
                connectOptions.Password = "";

                kdbUser = new(serverUrl, connectOptions);
                kdbUser.getRecordsByColumn("ITER", ["USER() AS username"]).data.First().TryGetValue("username", out gotUsername);
                Console.WriteLine($"Unexpected success logging in with invalid credentials for {gotUsername}!");
            }
            catch (KineticaException ex)
            {
                Console.WriteLine($"Expected error logging in with invalid credentials: {ex.Message}");
            }
        }  // end TestAuthentication()


        private class RecordTypeAll
        {
            public int? i { get; set; }
            public int? i8 { get; set; }
            public int? i16 { get; set; }
            public long l { get; set; }  // not making nullable here so that can be used as a primary key
            public float? f { get; set; }
            public double? d { get; set; } // nullable
            public string? s { get; set; }
            public string? c1 { get; set; }
            public string? c2 { get; set; }
            public string? c4 { get; set; }
            public string? c8 { get; set; }
            public string? c16 { get; set; }
            public string? c32 { get; set; }
            public string? c64 { get; set; }
            public string? c128 { get; set; }
            public string? c256 { get; set; }
            public string? date { get; set; }
            public string? datetime { get; set; }
            public string? decimal_ { get; set; }
            public string? ipv4 { get; set; }
            public string? time { get; set; }
            public long? timestamp { get; set; }

            public override string ToString()
            {
                string d_;
                if ( d != null )
                    d_ = $"{d}";
                else
                    d_ = "<null>";
                return $"{{ i={i}, i8={i8},  i16={i16}, l={l}, f={f}, d={d_}, s={s}, c1={c1}, c2={c2}, c4={c4}, c8={c8}, c16={c16}, c32={c32}, c64={c64}, c128={c128}, c256={c256}, date={date}, datetime={datetime}, decimal_={decimal_}, ipv4={ipv4}, time={time}, timestamp={timestamp} }}";
            }
        }  // end class RecordTypeAll


        private static void TestAllTypesAllShard( string serverUrl, Kinetica.Options serverOptions )
        {
            // Establish a connection with Kinetica
            Kinetica kdb = new( serverUrl, serverOptions );

            Console.WriteLine( "\n\n" );
            Console.WriteLine( "Test Multihead Ingest: One column per Type; All are Shard Keys, 0 Primary Keys" );
            Console.WriteLine( "==============================================================================" );
            Console.WriteLine();

            Console.WriteLine( "Creating a type with all column types (all are nullable and shard columns).\n" );

            // Create a type for our RecordTypeAll class
            // Add some interesting properties for some of the data types
            Dictionary<string, IList<string>> column_properties = new()
            {
                { "i", [ColumnProperty.SHARD_KEY] },
                { "i8", [ColumnProperty.SHARD_KEY, ColumnProperty.INT8] },
                { "i16", [ColumnProperty.SHARD_KEY, ColumnProperty.INT16] },
                { "d", [ColumnProperty.SHARD_KEY] },
                { "f", [ColumnProperty.SHARD_KEY] },
                { "l", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE] },
                { "s", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE] },
                { "c1", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.CHAR1] },
                { "c2", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.CHAR2] },
                { "c4", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.CHAR4] },
                { "c8", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.CHAR8] },
                { "c16", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.CHAR16] },
                { "c32", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.CHAR32] },
                { "c64", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.CHAR64] },
                { "c128", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.CHAR128] },
                { "c256", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.CHAR256] },
                { "date", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.DATE] },
                { "datetime", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.DATETIME] },
                { "decimal_", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.DECIMAL] },
                { "ipv4", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.IPV4] },
                { "time", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.TIME] },
                { "timestamp", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.TIMESTAMP] }
            };

            // Create the KineticaType object which facilitates creating types in the database
            KineticaType allType = KineticaType.fromClass( typeof( RecordTypeAll ), column_properties );

            // Create the type in the database
            string type_id = allType.create( kdb );
            Console.WriteLine( "Created type.\n" );

            string tableName = "csharp_test_table_all_shards";
            // Clear any previously made table with the same name
            Console.WriteLine( $"Clearing any existing table named '{tableName}'..." );
            kdb.clearTable(tableName, "", new Dictionary<string, string> { [ClearTableRequest.Options.NO_ERROR_IF_NOT_EXISTS] = ClearTableRequest.Options.TRUE });

            // Create a table of the given type
            Console.WriteLine( $"Creating table named '{tableName}'\n" );
            kdb.createTable( tableName, type_id );
            Console.WriteLine();

            // Create the ingestor
            int batchSize = 1000;
            KineticaIngestor<RecordTypeAll> ingestor = new( kdb, tableName, batchSize, allType );


            // Generate data to be inserted
            int totalRecords = batchSize * 10;
            Console.WriteLine( $"Starting to generate {totalRecords} records.\n" );
            List<RecordTypeAll> records = [];
            Random rng = new();
            double nullProbability = 0.15;  // 15% chance of creating nulls
            for ( int i = 0; i < totalRecords; ++i )
            {
                // Restricting string length to 256
                int max_str_len = rng.Next( 0, 256 );
                RecordTypeAll record = new()
                {
                    i    = ( rng.NextDouble() < nullProbability ) ? null : (int?) rng.Next(),
                    i8   = ( rng.NextDouble() < nullProbability ) ? null : (int?) rng.Next( -128, 128 ), // upper exclusive
                    i16  = ( rng.NextDouble() < nullProbability ) ? null : (int?) rng.Next( -32768, 32768), // upper exclusive
                    l    = (long)rng.Next(),
                    //l  = ( rng.NextDouble() < nullProbability ) ? null : (long)rng.Next(),  // can't be made nullable if not declared as long?
                    f    = ( rng.NextDouble() < nullProbability ) ? null : (float?)(rng.NextDouble() * rng.Next()),
                    d    = ( rng.NextDouble() < nullProbability ) ? null : ( double? ) ( rng.NextDouble() * rng.Next() ),
                    s    = ( rng.NextDouble() < nullProbability ) ? null : System.IO.Path.GetRandomFileName().Truncate( max_str_len ),
                    c1   = ( rng.NextDouble() < nullProbability ) ? null : System.IO.Path.GetRandomFileName().Truncate( 1 ),
                    c2   = ( rng.NextDouble() < nullProbability ) ? null : System.IO.Path.GetRandomFileName().Truncate( 2 ),
                    c4   = ( rng.NextDouble() < nullProbability ) ? null : System.IO.Path.GetRandomFileName().Truncate( rng.Next( 0, 5) ), // any size between empty string to 4 length
                    c8   = ( rng.NextDouble() < nullProbability ) ? null : System.IO.Path.GetRandomFileName().Truncate( rng.Next( 0, 9) ), // any size between empty string to 8 length
                    c16  = ( rng.NextDouble() < nullProbability ) ? null : System.IO.Path.GetRandomFileName().Truncate( rng.Next( 0, 17) ), // any size between empty string to 16 length
                    c32  = ( rng.NextDouble() < nullProbability ) ? null : System.IO.Path.GetRandomFileName().Truncate( rng.Next( 5, 33) ), // any size between [5, 32] in length
                    c64  = ( rng.NextDouble() < nullProbability ) ? null : System.IO.Path.GetRandomFileName().Truncate( rng.Next( 10, 65) ), // any size between [10, 64] in length
                    c128 = ( rng.NextDouble() < nullProbability ) ? null : System.IO.Path.GetRandomFileName().Truncate( rng.Next( 10, 129) ), // any size between [10, 128] in length
                    c256 = ( rng.NextDouble() < nullProbability ) ? null : System.IO.Path.GetRandomFileName().Truncate( rng.Next( 25, 257) ), // any size between [25, 256] in length
                    date     = ( rng.NextDouble() < nullProbability ) ? null : Test.GenerateDate( rng ),
                    datetime = (rng.NextDouble() < nullProbability) ? null : Test.GenerateDateTime(rng),
                    decimal_ = (rng.NextDouble() < nullProbability) ? null : Test.GenerateDecimal(rng),
                    ipv4     = ( rng.NextDouble() < nullProbability ) ? null : Test.GenerateIpv4( rng ),
                    time = (rng.NextDouble() < nullProbability) ? null : Test.GenerateTime(rng),
                    timestamp = ( rng.NextDouble() < nullProbability ) ? null : ((long?)rng.Next( -306102240, 293795424 ) * rng.Next( 100000 ))
                };

                //Console.WriteLine( "#" + i + ": " + record.ToString() + "\n" );  // debug~~~~~~~~
                records.Add( record );
                ingestor.insert( record );
            }  // end for loop

            Console.WriteLine( $"Generated {totalRecords} records." );

            // Insert the records into the ingestor
            Console.WriteLine( $"Inserting {totalRecords} records..." );
            ingestor.insert( records );

            // Flush the ingestor (which actually inserts the records)
            Console.WriteLine( "\nFlushing any remaining records." );
            ingestor.flush();
        }  // end TestAllTypesAllShard()



        private static void TestAllTypesIpRegex3Shard0Pk( string serverUrl, Kinetica.Options serverOptions )
        {
            // Establish a connection with Kinetica
            Kinetica kdb = new( serverUrl, serverOptions );

            Console.WriteLine( "\n\n" );
            Console.WriteLine( "Test Multihead Ingest with a Regex Given for Worker IP Addresses (3 shard keys, no PKs)" );
            Console.WriteLine( "=======================================================================================" );
            Console.WriteLine();

            // Create a type for our record_type_1 class
            // Add some interesting properties for some of the data types
            Dictionary<string, IList<string>> column_properties = new()
            {
                { "i", [ColumnProperty.SHARD_KEY] },
                { "d", [ColumnProperty.SHARD_KEY] },
                { "i16", [ColumnProperty.SHARD_KEY] }
            };

            // Create the KineticaType object which facilitates creating types in the database
            KineticaType recordAllType = KineticaType.fromClass( typeof( RecordTypeAll ), column_properties );

            // Create the type in the database
            string recordAllTypeId = recordAllType.create( kdb );

            string tableName = "csharp_test_table_3s0pk";
            // Clear any previously made table with the same name
            Console.WriteLine($"Clearing any existing table named '{tableName}'...");
            kdb.clearTable(tableName, "", new Dictionary<string, string> { [ClearTableRequest.Options.NO_ERROR_IF_NOT_EXISTS] = ClearTableRequest.Options.TRUE });

            // Create a table of the given type
            Console.WriteLine( $"Creating table named '{tableName}'...\n" );
            kdb.createTable( tableName, recordAllTypeId );

            // Create the ingestor
            int batchSize = 100;
            // Note: May need to adjust the regex for this test to pass
            Regex ipRegex = new( "127.0.*" );
            kinetica.Utils.WorkerList workerList = new( kdb, ipRegex );
            KineticaIngestor<RecordTypeAll> ingestor = new( kdb, tableName, batchSize, recordAllType, null, workerList );

            // Generate data to be inserted
            int totalRecords = batchSize * 5;
            List<RecordTypeAll> records = [];
            Random rng = new();
            double nullProbability = 0.2;
            for ( int i = 0; i < totalRecords; ++i )
            {
                // Restricting string length to 256
                int max_str_len = rng.Next( 0, 256 );
                RecordTypeAll record = new()
                {
                    i = rng.Next(),
                    i8 = rng.Next(),
                    i16 = rng.Next(),
                    l  = (long)rng.Next(),
                    f  = (float)(rng.NextDouble() * rng.Next()),
                    d  = ( rng.NextDouble() < nullProbability ) ? null : ( double? ) ( rng.NextDouble() * rng.Next() ),
                    s  = System.IO.Path.GetRandomFileName().Truncate( max_str_len ),
                    c1   = System.IO.Path.GetRandomFileName().Truncate( 1 ),
                    c2   = System.IO.Path.GetRandomFileName().Truncate( 2 ),
                    c4   = System.IO.Path.GetRandomFileName().Truncate( 4 ),
                    c8   = System.IO.Path.GetRandomFileName().Truncate( 8 ),
                    c16  = System.IO.Path.GetRandomFileName().Truncate( 16 ),
                    c32  = System.IO.Path.GetRandomFileName().Truncate( 32 ),
                    c64  = System.IO.Path.GetRandomFileName().Truncate( 64 ),
                    c128 = System.IO.Path.GetRandomFileName().Truncate( 128 ),
                    c256 = System.IO.Path.GetRandomFileName().Truncate( 256 ),
                    date = Test.GenerateDate( rng ),
                    datetime = Test.GenerateDateTime( rng ),
                    decimal_ = Test.GenerateDecimal( rng ),
                    ipv4 = Test.GenerateIpv4( rng ),
                    time = Test.GenerateTime( rng ),
                    timestamp = (long) rng.Next()
                };

                //Console.WriteLine( record.ToString() );  // debug~~~~~~~~
                records.Add( record );
            }  // end for loop

            Console.WriteLine( $"Generated {totalRecords} records." );

            // Insert the records into the ingestor
            Console.WriteLine( $"Inserting {totalRecords} records..." );
            ingestor.insert( records );

            // Flush the ingestor (which actually inserts the records)
            Console.WriteLine( "\nFlushing any remaining records." );
            ingestor.flush();
        }  // end TestAllTypesIpRegex3Shard0Pk()




        private static void TestAllTypes3Pk3Shard( string serverUrl, Kinetica.Options serverOptions )
        {
            // Establish a connection with Kinetica
            Kinetica kdb = new( serverUrl, serverOptions );

            Console.WriteLine( "\n\n" );
            Console.WriteLine( "Test Multihead Ingest: One column per Type; 3 Primary Keys, 3 Shard Keys" );
            Console.WriteLine( "========================================================================" );
            Console.WriteLine();

            // Create a type for our record_type_1 class
            // Add some interesting properties for some of the data types
            IDictionary<string, IList<string>> column_properties = new Dictionary<string, IList<string>>
            {
                { "l", [ColumnProperty.PRIMARY_KEY, ColumnProperty.SHARD_KEY] },
                { "date", [ColumnProperty.PRIMARY_KEY, ColumnProperty.SHARD_KEY, ColumnProperty.DATE] },
                { "time", [ColumnProperty.PRIMARY_KEY, ColumnProperty.SHARD_KEY, ColumnProperty.TIME] }
            };

            // Create the KineticaType object which facilitates creating types in the database
            KineticaType recordAllType = KineticaType.fromClass( typeof( RecordTypeAll ), column_properties );

            // Create the type in the database
            string recordAllTypeId = recordAllType.create( kdb );

            string tableName = "csharp_test_table_3s3pk";
            // Clear any previously made table with the same name
            Console.WriteLine($"Clearing any existing table named '{tableName}'...");
            kdb.clearTable(tableName, "", new Dictionary<string, string> { [ClearTableRequest.Options.NO_ERROR_IF_NOT_EXISTS] = ClearTableRequest.Options.TRUE });

            // Create a table of the given type
            Console.WriteLine( $"Creating table named '{tableName}'...\n");
            kdb.createTable( tableName, recordAllTypeId );

            // Create the ingestor
            int batchSize = 100;
            KineticaIngestor<RecordTypeAll> ingestor = new( kdb, tableName, batchSize, recordAllType );

            // Generate data to be inserted
            int totalRecords = batchSize * 50;
            Random rng = new();
            double nullProbability = 0.2;
            for ( int i = 0; i < totalRecords; ++i )
            {
                // Restricting string length to 256
                int maxStringLength = rng.Next( 0, 256 );
                RecordTypeAll record = new()
                {
                    i = rng.Next(),
                    i8 = rng.Next(),
                    i16 = rng.Next(),
                    l  = (long)rng.Next(),
                    f  = (float)(rng.NextDouble() * rng.Next()),
                    d  = ( rng.NextDouble() < nullProbability ) ? null : ( double? ) ( rng.NextDouble() * rng.Next() ),
                    s  = System.IO.Path.GetRandomFileName().Truncate( maxStringLength ),
                    c1   = System.IO.Path.GetRandomFileName().Truncate( 1 ),
                    c2   = System.IO.Path.GetRandomFileName().Truncate( 2 ),
                    c4   = System.IO.Path.GetRandomFileName().Truncate( 4 ),
                    c8   = System.IO.Path.GetRandomFileName().Truncate( 8 ),
                    c16  = System.IO.Path.GetRandomFileName().Truncate( 16 ),
                    c32  = System.IO.Path.GetRandomFileName().Truncate( 32 ),
                    c64  = System.IO.Path.GetRandomFileName().Truncate( 64 ),
                    c128 = System.IO.Path.GetRandomFileName().Truncate( 128 ),
                    c256 = System.IO.Path.GetRandomFileName().Truncate( 256 ),
                    date = Test.GenerateDate( rng ),
                    datetime = Test.GenerateDateTime( rng ),
                    decimal_ = Test.GenerateDecimal( rng ),
                    ipv4 = Test.GenerateIpv4( rng ),
                    time = Test.GenerateTime( rng ),
                    timestamp = (long) rng.Next()
                };

                ingestor.insert( record );
            }  // end for loop

            Console.WriteLine( $"Generated {totalRecords} records." );

            // Flush the ingestor (which actually inserts the records)
            Console.WriteLine( "Flushing any remaining records..." );
            ingestor.flush();
        }  // end TestAllTypes3Pk3Shard()



        private static void TestAllTypes3Pk0Shard( string serverUrl, Kinetica.Options serverOptions )
        {
            // Establish a connection with Kinetica
            Kinetica kdb = new( serverUrl, serverOptions );

            Console.WriteLine( "\n\n" );
            Console.WriteLine( "Test Multihead Ingest: One column per type; 3 Primary Keys, No Shard Key" );
            Console.WriteLine( "========================================================================" );
            Console.WriteLine();

            // Create a type for our record_type_1 class
            // Add some interesting properties for some of the data types
            Dictionary<string, IList<string>> column_properties = new()
            {
                { "l", [ColumnProperty.PRIMARY_KEY] },
                { "date", [ColumnProperty.PRIMARY_KEY] },
                { "time", [ColumnProperty.PRIMARY_KEY] }
            };

            // Create the KineticaType object which facilitates creating types in the database
            KineticaType recordAllType = KineticaType.fromClass( typeof( RecordTypeAll ), column_properties );

            // Create the type in the database
            string recordAllTypeId = recordAllType.create( kdb );

            string tableName = "csharp_test_table_0s3pk";
            // Clear any previously made table with the same name
            Console.WriteLine($"Clearing any existing table named '{tableName}'...");
            kdb.clearTable(tableName, "", new Dictionary<string, string> { [ClearTableRequest.Options.NO_ERROR_IF_NOT_EXISTS] = ClearTableRequest.Options.TRUE });

            // Create a table of the given type
            Console.WriteLine( $"Creating table named '{tableName}'...\n" );
            kdb.createTable( tableName, recordAllTypeId );

            // Create the ingestor
            int batchSize = 100;
            KineticaIngestor<RecordTypeAll> ingestor = new( kdb, tableName, batchSize, recordAllType );

            // Generate data to be inserted
            int totalRecords = batchSize * 50;
            Random rng = new();
            double nullProbability = 0.2;
            for ( int i = 0; i < totalRecords; ++i )
            {
                // Restricting string length to 256
                int maxStringLength = rng.Next( 0, 256 );
                RecordTypeAll record = new()
                {
                    i = rng.Next(),
                    i8 = rng.Next(),
                    i16 = rng.Next(),
                    l  = (long)rng.Next(),
                    f  = (float)(rng.NextDouble() * rng.Next()),
                    d  = ( rng.NextDouble() < nullProbability ) ? null : ( double? ) ( rng.NextDouble() * rng.Next() ),
                    s  = System.IO.Path.GetRandomFileName().Truncate( maxStringLength ),
                    c1   = System.IO.Path.GetRandomFileName().Truncate( 1 ),
                    c2   = System.IO.Path.GetRandomFileName().Truncate( 2 ),
                    c4   = System.IO.Path.GetRandomFileName().Truncate( 4 ),
                    c8   = System.IO.Path.GetRandomFileName().Truncate( 8 ),
                    c16  = System.IO.Path.GetRandomFileName().Truncate( 16 ),
                    c32  = System.IO.Path.GetRandomFileName().Truncate( 32 ),
                    c64  = System.IO.Path.GetRandomFileName().Truncate( 64 ),
                    c128 = System.IO.Path.GetRandomFileName().Truncate( 128 ),
                    c256 = System.IO.Path.GetRandomFileName().Truncate( 256 ),
                    date = Test.GenerateDate( rng ),
                    datetime = Test.GenerateDateTime( rng ),
                    decimal_ = Test.GenerateDecimal( rng ),
                    ipv4 = Test.GenerateIpv4( rng ),
                    time = Test.GenerateTime( rng ),
                    timestamp = (long) rng.Next()
                };

                ingestor.insert( record );
            }  // end for loop

            Console.WriteLine( $"Generated {totalRecords} records." );

            // Flush the ingestor (which actually inserts the records)
            Console.WriteLine( "Flushing any remaining records..." );
            ingestor.flush();
        }  // end TestAllTypes3Pk0Shard()



        private static void TestRecordRetrievalByKey(string serverUrl, Kinetica.Options serverOptions)
        {
            // Establish a connection with Kinetica
            Kinetica kdb = new(serverUrl, serverOptions);

            Console.WriteLine("\n\n");
            Console.WriteLine("Test Multihead Key Lookup: One column per Type; one numeric and one string");
            Console.WriteLine("shard key (no primary key)");
            Console.WriteLine("==========================================================================");
            Console.WriteLine();

            Console.WriteLine("Creating a type with all column types (all are nullable; one numeric and one string shard columns)...\n");

            // Create a type for our record_type_1 class
            // Add some interesting properties for some of the data types
            Dictionary<string, IList<string>> column_properties = new()
            {
                { "i", [] },
                { "i8", [ColumnProperty.SHARD_KEY, ColumnProperty.INT8] },
                { "i16", [ColumnProperty.INT16] },
                { "d", [] },
                { "f", [] },
                { "l", [ColumnProperty.NULLABLE] },
                { "s", [ColumnProperty.NULLABLE] },
                { "c1", [ColumnProperty.SHARD_KEY, ColumnProperty.NULLABLE, ColumnProperty.CHAR1] },
                { "c2", [ColumnProperty.NULLABLE, ColumnProperty.CHAR2] },
                { "c4", [ColumnProperty.NULLABLE, ColumnProperty.CHAR4] },
                { "c8", [ColumnProperty.NULLABLE, ColumnProperty.CHAR8] },
                { "c16", [ColumnProperty.NULLABLE, ColumnProperty.CHAR16] },
                { "c32", [ColumnProperty.NULLABLE, ColumnProperty.CHAR32] },
                { "c64", [ColumnProperty.NULLABLE, ColumnProperty.CHAR64] },
                { "c128", [ColumnProperty.NULLABLE, ColumnProperty.CHAR128] },
                { "c256", [ColumnProperty.NULLABLE, ColumnProperty.CHAR256] },
                { "date", [ColumnProperty.NULLABLE, ColumnProperty.DATE] },
                { "datetime", [ColumnProperty.NULLABLE, ColumnProperty.DATETIME] },
                { "decimal_", [ColumnProperty.NULLABLE, ColumnProperty.DECIMAL] },
                { "ipv4", [ColumnProperty.NULLABLE, ColumnProperty.IPV4] },
                { "time", [ColumnProperty.NULLABLE, ColumnProperty.TIME] },
                { "timestamp", [ColumnProperty.NULLABLE, ColumnProperty.TIMESTAMP] }
            };

            // Create the KineticaType object which facilitates creating types in the database
            KineticaType recordAllType = KineticaType.fromClass(typeof(RecordTypeAll), column_properties);

            // Create the type in the database
            string recordAllTypeId = recordAllType.create(kdb);

            string tableName = "csharp_test_record_retrieval_by_key";

            // Clear any previously made table with the same name
            Console.WriteLine($"Clearing any existing table named '{tableName}'...");
            kdb.clearTable(tableName, "", new Dictionary<string, string> { [ClearTableRequest.Options.NO_ERROR_IF_NOT_EXISTS] = ClearTableRequest.Options.TRUE });

            // Create a table of the given type
            Console.WriteLine($"Creating table named '{tableName}'...\n");
            kdb.createTable(tableName, recordAllTypeId);

            // Create indices on the shard columns (6.2 limitation for key lookup)
            kdb.alterTable(tableName, "create_index", "i8");
            kdb.alterTable(tableName, "create_index", "c1");

            // Create the ingestor
            int batchSize = 1000;
            KineticaIngestor<RecordTypeAll> ingestor = new(kdb, tableName, batchSize, recordAllType);

            // Generate data to be inserted
            // Note that the current sharded columns (i8 and c1) will not
            // get nulls since in 6.2, Kinetica does not support nulls for 
            // key lookup
            int totalRecords = batchSize * 10;
            Console.WriteLine($"Starting to generate {totalRecords} records...");
            List<RecordTypeAll> records = [];
            Random rng = new();
            double nullProbability = 0.15;  // 15% chance of creating nulls
            double shardNullProbability = 0; // in 6.2, key lookup can't handle nulls
            for (int i = 0; i < totalRecords; ++i)
            {
                // Restricting string length to 256
                int maxStringLength = rng.Next(0, 256);
                RecordTypeAll record = new()
                {
                    i = (rng.NextDouble() < nullProbability) ? null : (int?)rng.Next(),
                    i8 = (rng.NextDouble() < shardNullProbability) ? null : (int?)rng.Next(110, 128), // Limiting the range in order to get multiple records per shard key
                    //i8   = ( rng.NextDouble() < shardNullProbability ) ? null : (int?) rng.Next( -128, 128 ), // upper exclusive
                    i16 = (rng.NextDouble() < nullProbability) ? null : (int?)rng.Next(-32768, 32768), // upper exclusive
                    l = (long)rng.Next(),
                    //l  = ( rng.NextDouble() < nullProbability ) ? null : (long)rng.Next(),  // can't be made nullable if not declared as long?
                    f = (rng.NextDouble() < nullProbability) ? null : (float?)(rng.NextDouble() * rng.Next()),
                    d = (rng.NextDouble() < nullProbability) ? null : (double?)(rng.NextDouble() * rng.Next()),
                    s = (rng.NextDouble() < nullProbability) ? null : System.IO.Path.GetRandomFileName().Truncate(maxStringLength),
                    c1 = (rng.NextDouble() < shardNullProbability) ? null : System.IO.Path.GetRandomFileName().Truncate(1),
                    c2 = (rng.NextDouble() < nullProbability) ? null : System.IO.Path.GetRandomFileName().Truncate(2),
                    c4 = (rng.NextDouble() < nullProbability) ? null : System.IO.Path.GetRandomFileName().Truncate(rng.Next(0, 5)), // any size between empty string to 4 length
                    c8 = (rng.NextDouble() < nullProbability) ? null : System.IO.Path.GetRandomFileName().Truncate(rng.Next(0, 9)), // any size between empty string to 8 length
                    c16 = (rng.NextDouble() < nullProbability) ? null : System.IO.Path.GetRandomFileName().Truncate(rng.Next(0, 17)), // any size between empty string to 16 length
                    c32 = (rng.NextDouble() < nullProbability) ? null : System.IO.Path.GetRandomFileName().Truncate(rng.Next(5, 33)), // any size between [5, 32] in length
                    c64 = (rng.NextDouble() < nullProbability) ? null : System.IO.Path.GetRandomFileName().Truncate(rng.Next(10, 65)), // any size between [10, 64] in length
                    c128 = (rng.NextDouble() < nullProbability) ? null : System.IO.Path.GetRandomFileName().Truncate(rng.Next(10, 129)), // any size between [10, 128] in length
                    c256 = (rng.NextDouble() < nullProbability) ? null : System.IO.Path.GetRandomFileName().Truncate(rng.Next(25, 257)), // any size between [25, 256] in length
                    date = (rng.NextDouble() < nullProbability) ? null : Test.GenerateDate(rng),
                    datetime = (rng.NextDouble() < nullProbability) ? null : Test.GenerateDateTime(rng),
                    decimal_ = (rng.NextDouble() < nullProbability) ? null : Test.GenerateDecimal(rng),
                    ipv4 = (rng.NextDouble() < nullProbability) ? null : Test.GenerateIpv4(rng),
                    time = (rng.NextDouble() < nullProbability) ? null : Test.GenerateTime(rng),
                    timestamp = (rng.NextDouble() < nullProbability) ? null : ((long?)rng.Next(-306102240, 293795424) * rng.Next(100000))
                };

                //Console.WriteLine( "#" + i + ": " + record.ToString() + "\n" );  // debug~~~~~~~~
                records.Add(record);
                ingestor.insert(record);
            }  // end for loop

            Console.WriteLine($"Generated {totalRecords} records.\n");

            // Flush the ingestor (which actually inserts the records)
            Console.WriteLine("Flushing any remaining records...");
            ingestor.flush();

            // Look up records by any key
            Console.WriteLine("Creating the record retriever...");
            RecordRetriever<RecordTypeAll> retriever = new(kdb, tableName, recordAllType);
            Console.WriteLine("Attempting record retrieval...");
            GetRecordsResponse<RecordTypeAll> response = retriever.getRecordsByKey(records[0]);
            Console.WriteLine("Number of records fetched: {0}", response.total_number_of_records);
            Console.WriteLine("Records fetched: ");
            foreach (var record in response.data)
                Console.WriteLine(record.ToString());

            Console.WriteLine($"Are there more records to fetch?: {response.has_more_records}\n");
        }  // end TestRecordRetrievalByKey()



        public static void TestJdbc(string serverUrl, Kinetica.Options serverOptions, string? jdbcDriverPath)
        {
            Console.WriteLine("\n\n");
            Console.WriteLine("JDBC Bridge Test");
            Console.WriteLine("================");
            Console.WriteLine();

            if (jdbcDriverPath == null)
                Console.WriteLine("Skipping--JDBC driver JAR not passed");
            else
            {
                string jdbcUrl = $"jdbc:kinetica:URL={serverUrl};UID={serverOptions.Username};PWD={serverOptions.Password}";

                var builder = new JdbcConnectionStringBuilder
                {
                    DriverPath = jdbcDriverPath,
                    DriverClass = "com.kinetica.jdbc.Driver",
                    JdbcUrl = jdbcUrl
                };

                Console.WriteLine($"JDBC URL: {jdbcUrl}");
                using var connection = new JdbcConnection(builder);
                connection.Open();
                string? rows = connection.CreateCommand("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'information_schema'").ExecuteScalar().ToString();
                Console.WriteLine($"Connection successful!  {rows} tables in INFORMATION_SCHEMA.\n");
            }
        }



        public static string GenerateDate( Random rng )
        {
            int pastRange   = -90 * 365; //980 years
            int futureRange = 20 * 365; // 20 years
            int offset = rng.Next( pastRange, futureRange );
            DateTime randomDate = DateTime.Today.AddDays( offset );
            return String.Format( "{0:yyyy-MM-dd}", randomDate );
        }  // end GenerateDate



        public static string GenerateDateTime( Random rng )
        {
            int year    = rng.Next(1900, 2500);
            int month   = rng.Next(1, 12);
            int day     = rng.Next(1, 28);
            int hour    = rng.Next(0, 23);
            int minute  = rng.Next(0, 59);
            int second  = rng.Next(0, 59);
            int msecond = rng.Next(0, 999);

            DateTime randomDateTime = new( year, month, day, hour, minute, second, msecond );

            // We don't have to have any time component
            int timeChancePercent = 90;
            if (rng.Next(101) > timeChancePercent)
                return String.Format("{0:yyyy-MM-dd}", randomDateTime);
            else
            {
                // Don't have to have the millisecond
                int numMsDigits = rng.Next(7);
                return numMsDigits switch
                {
                    0 => String.Format("{0:yyyy-MM-dd HH:mm:ss}", randomDateTime),
                    1 => String.Format("{0:yyyy-MM-dd HH:mm:ss.F}", randomDateTime),
                    2 => String.Format("{0:yyyy-MM-dd HH:mm:ss.FF}", randomDateTime),
                    3 => String.Format("{0:yyyy-MM-dd HH:mm:ss.FFF}", randomDateTime),
                    4 => String.Format("{0:yyyy-MM-dd HH:mm:ss.FFFF}", randomDateTime),
                    5 => String.Format("{0:yyyy-MM-dd HH:mm:ss.FFFFF}", randomDateTime),
                    _ => String.Format("{0:yyyy-MM-dd HH:mm:ss.FFFFFF}", randomDateTime),
                };
            }
        }  // end GenerateDateTime



        private static readonly string numbers = "0123456789";

        public static string GenerateDecimal( Random rng )
        {
            // Using up to 14 integral digits to keep things simple since the decimal
            // range is [-922337203685477.5808, 922337203685477.5807]
            string precision = new([.. Enumerable.Repeat( '1', rng.Next( 15 ) ).Select( i => Test.numbers[ rng.Next( numbers.Length ) ] )]);
            string scale = rng.Next( 0, 10000 ).ToString();
            string sign = (rng.Next(2) == 0 && Double.Parse(precision + "." + scale) != 0) ? "-" : "";
            return ( sign + precision + "." + scale );
        }  // end GenerateDecimal

        

        public static string GenerateIpv4( Random rng )
        {
            string n1 = rng.Next( 256 ).ToString();
            string n2 = rng.Next( 256 ).ToString();
            string n3 = rng.Next( 256 ).ToString();
            string n4 = rng.Next( 256 ).ToString();
            string dot = ".";
            return ( n1 + dot + n2 + dot + n3 + dot + n4 );
        }  // end generate_ipv4



        public static string GenerateTime( Random rng )
        {
            int maxMiliseconds = 86400000;
            DateTime time = DateTime.Today;
            time = time.Add( TimeSpan.FromMilliseconds( rng.Next( maxMiliseconds ) ) );
            string time_str;
            if ( (time.Millisecond > 0)  && (rng.Next( 1, 6 ) > 3) ) // two in five will get the milliseconds
                time_str = String.Format( "{0:HH}:{0:mm}:{0:ss}.{0:FFF}", time );
            else
                time_str = String.Format( "{0:HH}:{0:mm}:{0:ss}", time );
            return time_str;
        }  // end GenerateTime


    }  // end class Test
}  // end namespace Test