17th Jan 2023 1. Refactored irdbHttpServer, to make custom http servers easier to build. Added new irdbHttp default authentication module. 2. Fixed query compilation error with Sign function in irdb-SQL 3. Fixed a problem with listDatabases not working correctly in http api. 4. Added new option to irdb.ini where if you set echoQueryCompilationDebug to true, it will output the CS code to the log file, whenever there is a query compilation error. This is designed to help troubleshoot queryCompilationErrors. 5. Fixed a bug with getDate() and date() functions in tiImport, only returning the time that the script was parsed, rather than executing time. 6. fixed a bug in unload_temptables irdb.ini setting, when it parsed, it actually used the unload_timer value 7. Fixed a bug in multi-line Table Value Functions, where the temp output table, could be dropped if the background garbage timer ran inside irdbServer 31st Oct 2022 1. Fixed a bug in Right Joined aggregation queries, where it was giving Zero values instead of nulls in Aggregates. 2. Fixed a bug in Right Joined queries, where calculated expressions that should have been null where evaluating as default instead of null 3. Fixed a bug in Inner Joined queries, where it was making the default type of all calculated expressions as null types internally, where this wasn't necessary. THis may help improve performance 4. Added support for the TempDB api to IPClient 5. Added http support to Reloading and unloading to irdbLoad 6. Added new method IRDBTest UPDATE, that Uploads an data file to a server and overwrites the existing database with the uploaded tables, but keeping any tables NOT within the upload. This would be used, where you might want to just update 1 or 2 tables, without updating the entire database. 14th Oct2022 1. Fixed a bug where Database Names and Table Names were not being URL Decoded in the HTTPClient 2. Fixed a bug in listFields where if an invalid table was returning, it wasn't setting a non 200 Status Code, in the HTTPClient. 3. Updated Dot Net 6 installer to install Dot Net 6.0.9 Desktop Runtime, and to also install Dot Net 6.0.9 ASP.Net Runtime 5th Oct 2022 1. Added support for datepart ( hh,getdate()) 2. increased no of reconnection attempts from 25 to 160 in irdbLoad, if the server is busy, to see if it helps solve issue with long running Garbage Collection times. 3. Added a new command line option irdbLoad, to specify a time to explicitly wait after the initial request is sent. e.g. /wait=30 will cause now cause irdbLoad to wait for 30 minutes additional minute, before starting its main polling algorithm, to check reload status 4. Fixed a bug in the minSizeToLoadBeforeGC, so that the file size loaded counter is reset back to 0, after a related GC is triggered. 5. Dot Net core 6 versions have proper AssemblyVersions now. 1.6.x.0 where x is the version from irdb.dll 6. Initial Version of IRDB with http support. Supported in irdb.dll Client side and irdbServer/irdbServerCore in Dot Net 6 Version http support is enabled by add http_enabled=true to irdb.ini and restarting the server At the moment, it is the standard kestrel default ports 5000 for http & 5001 for https You can use the http client in irdbQuery by using the url in the Server part e.g. http://localhost:5000 or https://localhost:5001 as example The ports that are bound can also be changed by using the kestrel section of appsettings.json or --urls command line parameter 7. in IRDB, http connections are controlled by a class called HttpClient Previously IPClient was used to communicate with irdbServer. There is a new interface called CommonClient, that wraps these two classes, and allows you to support both methods. IpClient & HttpClient implement this interface. The following methods are implemented in CommonClient int isLoading(String dbName); bool load(String dbName ,out String errors); Object[] listTables(String dbName); InMemoryTable execute(String database, string sql, out string errors); InMemoryTable executeWithTag(String dbName, String sql, String tag, out String errors); Object[] listDatabases(); DBStats[] listDatabasesDetailed(); Object[] listFields(String dbName, String tableName, List datatypes); bool markActivity(List databases); void close(); The following static method in IPClient can be used as a factory method to construct CommonClient connections public static CommonClient createCommonClient(String connectionString, out bool success) The connectionString for http connection method should be something like host=https://localhost:5001;pwd=!TopSecret Http connections have an authorization token, that will expire the connection after 24 hours. ToDo 1. Add support to irdbLoad for http Connections 8. Added support for Group_Concat( Distinct somefield ) & Group_Concat( Distinct somefield separator ',' ) 14th July 2022 1. Added the following command to irdbIMport SET DEBUGIMPORT TRUE/FALSE This turns on extra debugging information, like displaying the related exceptions and values when you into errors importing certain rows. 2. Added support to irdbImport When working with the Oracle Managed Dot Net Provider, Oracle.ManagedDataAccess.Client.OracleDataReader After an error occurs reading a row , to try turning on SuppressGetDecimalInvalidCastException property on the DBDataReader by reflection for that row, and then turn it off This property cannot be left on in general though, because the importer will then run much slower. 3. Added new irdb.ini option called bitpack_rowcutoff If you turn on bitpacking, by default it will not turn on bitpacking for tables with less than 100,000 rows. This allows you to customize the row cutoff for bitpacking. This has no effect, if Bitpacking is not turned on. e.g. adding the line bitpack_rowcutoff=200000 will mean tables with less than 200k rows are not bitpacked. 6th May 2022 ( irdb.dll version 1.0.0.47 1. Fixed a bug in irdbQuery where the stop Query button, did not align to the right, when the window was resized. 2. Fixed a bug where if you left joined a nocache subquery, and the where clause contained a NOt on expression containing that subquery, then you could end up with invalid results. e.g. select x.a,y.b from ( select 1 as A union all select 2 as A union all select 3 as A ) X left join ( select nocache 1 as B, 1 as C union all select 2 as B, 2 as C ) Y on X.A=Y.B where not y.C=2 3. Fixed a bug in aggregate query simplification. If an aggregate query contained an expression of a dbfield, and and a dbfield, then we try to simplify it by removing terms involving expressions of the dbfield. E.g. Group by [MyDate] , Month([date]) can be simplified to Group by [MyDate] However if your group by was Group by [MyDate] , Month([date]) , COL_A+COL_B , then it would have previously given an error. The simplification routine forgot to leave Col_A+COL_B ( Assuming they are from the same table). The system now simplifies these kind of expressions correctly. So Group by [MyDate] , Month([date]) , COL_A+COL_B would be simplified to Group by [MyDate] ,COL_A+COL_B 18th April 2022 1. Added Dot Net 6 Setup Build 2. Removed dependecy on MSMQ in irdbServer 3. Added some basic methods back in that don't require CancellationTokens, but are flagged with Obsolete 4. Added new reloadUsers method to ipClient / irdbServer 5. irdb.dll version should be incremented correctly now. 6. Dot Net 6 version of irdbQuery has some new features, like ability to export as a csv, and keep the header labels when copying and pasting to Excel. 7. irdbCore6.zip download avaiable for non windows platforms. 10th Feb 2022 1. Fixed a bug, with generating code for addition, where it was taking exponentially longer to parse based on the number of terms of addition. E.g. select 1+2+3+4+5+6+7+8+9+10+11+12+13+14+15+16+17+18+19+20+21+22+23+24+25+26+27+28+29+30+31+32+33+34+35+36+37+38+39+40 would take a very long time to parse 2. irdbServer now listens on both IPv4 & Ipv6 localhost. Previously wasn't being to ipv6 localhost, and was causing a 2s delay in Dot net Core 6 connections 3. Fix a bug where views / Stored Procedured were not being loaded correctly. Bug introduced in build of 27th Jan. 27th Jan 2022 1. Fixed a bug when saving databases, where it could write out an incorrect table number, when concurrent queries are adding / dropping tables 2. Fixed a bug, where saving a database could generate an exception, where a concurrent query dropped a table, that was being saved. In General saving databases should work, with concurrent queries add or removing tables. Internally the save procedure, now create a shallow clone of the current tables, and then saves that. 24th Jan 2022 1. Fixed a bug in IRDB Query, when you tried you list all the fields in a table, introduced in Nov build, by right clicking on the left hand navigation 2. When reading a table, or opening a database, will check all Double and Float columns, to see if they two zero values, and if they do, repair the column, so it only contains one zero. 17th Nov 2021 1. Added for StatementExecute in IPClient to take a user defined tag parameter, that can be used to kill a connection. 15ths Nov 2021 1. IRDB_Query now supports a command called listConns that shows you a list of currently executing queries on the server 2. Added support to irdbServer, to support the above feature 3. IRDB_Query can stop a connection given its ID/ 4. IRDB_Query can also stop a long running query, that it started. There is a Stop button, next to the Execute button 5. Starting a long running query, will no longer completely lock up the main IRDB_Query thread. 6. All execute API calls in InMemoryDatabase, now take a CancellationToken, that can be used to stop execution. 7. Added the following methods to IPClient public bool killConnectionByTag(string tag, out String errors) public bool killConnection(long connectionID, out String errors) public InMemoryTable executeWithTag(String dbName, String sql, String tag,out String errors) public InMemoryTable listConnections( out String errors) executeWithTag / killConnectionByTag, are designed to make it easy for the user client, to assign a unique query tag, e.g. a guid, and terminate it easily. It is used internally by irdb_query to stop its own remote queries. 8. Added basic support for Grouping Sets Note, it does contain any performance optimisations, to use the result of one grouping set to calculate another. This will be added in a later release. 9. Fixed a bug, if you had two string constants, with different cases in the same sql statement. causing the following exceptino. Exception occurred executing query. System.ArgumentException: An item with the same key has already been added. 26th October 2021 1. Added support for Grouping Sets to irdb sql 7th October 2021 1. Fixed a bug involving in subqueries when you had an in query within a aggregation query that had expressions. It was generating an error where it couldn't find a temp table with or_list in the name. e.g. select avg(freight) from orders where orderid in (select orderid from orders where employeeid in (1,2,3,4) ) 2. Added a new setting to irdb.ini called Readonly. By default it is set to false. If you add READONLY=TRUE to irdb.ini it will cause any command that can change to data, to be disabled. This is designed to enable a public server with test data, in IRDB. The following SQL Commands are disabled. SELECT INTO , DELETE , DROP TABLE,UPDATE , SET QUERYLOG , SET_QUERYLIMIT, CREATE PROCEDURE , DROP_PROCEDURE , CREATE TABLE , COLUMNIZE , INSERT , CREATE_FUNCTION , DROP_FUNCTION , CREATE VIEW , DROP VIEW , BREAKPOINT, CREATE CLUSTERED INDEX, ALTER TABLE SET VOLATILE , SET_CALENDARWEEKRULE The following tiserver methods are also disabled as they can allow changes in data. UNLOAD , TRANSFER_DB_TO_SERVER, DELETE_DB, and the following TEMPDB related methods. CREATE_TEMPDB,ADD_TABLE_TO_TEMPDB,ADD_TABLE_CHUNK_TO_TEMPDB,COLUMNIZE_TABLE_IN_TEMPDB,SAVE_TEMPDB,REMOVE_TEMPDB,LOAD_DB_TO_TEMPDB,DROP_TABLE_FROM_TEMPDB, LOAD & RELOAD are currently still available. One current limitation of running in READONLY is the OPENBAL And CLOSEBAL functions are not currently available, as they use other SQL statements like UPDATE, as part of their processing. 3. Fixed a bug in irdbImport when exporting data, when it was only allowing 30s for the Drop Table/ Truncate Table/ Create Table part of the process. This has now been extended to use the full ExportTimeout value. ( By default 120s) 24th August 2021 1. Fixed a bug when you were using lazyload, and had a join involving 2 or more fields, and the fields hadn't been loaded. 2. Fixed a bug in lazy loading when you had a join involving an expression 3. Fixed a bug in lazy loading, if you tried to create a clustered index in sql on a table that is lazy loaded. 5th August 2021 05/08/2021 1. Improved Join Performance when you have 3 or more columns. These kind of joins are now about 4-5 times faster. 2. BitPacking is turned off for tables with less than 100k rows 3. Added command ExportTimeout to help set a timeout when exporting data from Sql Server in irdbImport E.g. ExportTimeout 300 will set a timeout of 300 second when exporting data. The default Export timeout was also increased to 120s 4. Fixed a query compilation error when casting a datetime column to a double which could contain a null value to a double Note that you can't currently cast datetime to a INT/DECIMAL, and some of the other numeric types 5. Fixed a bug when creating a cursor in IPClient, where it was looking for a name, which on the out cursorname variable. This fixed a bug in Streamable Remote INMemoryDB connections, when you tried to import with a union all from a second table. 6. Fixed a bug in using SLURP against an ODBC connection, and the tablename contained a space. 7. Fixed a bug when creating a statement in IPClient where it was looking for a statement name to be specified. 8. Turned on the storing of hash in Dictionarys in uncolumnized mode, so it stores the Hash code for Longs and Doubles, unless you enable bitpacking. Turning on the hash improves performance for these cases. 9. Fixed some bugs in MaxList and MinList that was preventing them from working 10. Added a sleep command to irdbImport Sleep 10 will cause it to sleep for 10 seconds 11. When using irdbLoad, it will now try to reconnect 20 times instead of 5, before erroring out. When the system is memory constrained, and the garbage collection is taking a long time, irdbServer can become unresponsive. It will try 20*15s to try and reconnect after issuing the load/reload command. If it can reconnect, it will still wait an hour before it times out. 12. Added new ConstuctColumn & ConstructRandomColumn test routines to irdbTest 16th June 2021 1. Fixed a bug in Full Joins and Right Joins causing invalid results, when the where clause, involved a filter involving null 2. Reduced memory usage in Uncolumnized format using new Dictionary Memory is not allocated in chunks of 16k records for the Entries tables. No Need to store Value in Dictionary Entry, and the Entry index is the value used No need to store hash code for Certain basic types like Ints,long,Doubles, in the Dictionary Will use hashcodes otherwise. 3. Improved Dictionary performance, when used in uncolumnized format 4. Performance improvements in Hash Aggregation Queries processing results and reduction, using improved iteration methods on dictionary. 5. Added general bitpacking support. Turned on in irdbQuery & irdbImport by default. Turned off in irdbServer. It can be turned on in irdbServer, by adding bitpack=true, and restarting tiServer. There is a tradeoff, that queries will run a little slower, but memory usage will be about 25% lower ( depending on dataset) 6. irdbImport now supports the command SET BITPACK to turn on and off bitpacking. e.g. SET BITPACK TRUE will turn on Bitpacking. SET BITPACK FALSE will turn it off. 7. Dot Net Core Build, should now be built automatically 14/15th April 2021 1. Hash Aggregation Limit increased to 2e9 row. 2. Updated Dot Net Build 6th April 2021 1. Fixed an issue introduced in March 17th Build, where incorrect results could be given if using the Hash Aggregation Algorithm, and the cardinality of the Group ended up between 1e9 and 4e9 2. Performance improvement in Aggregation Queries using Hash Join, by using Optimized Dictionary logic . Current 1e9 row limit 3. Limit show now run twice as fast, by using new Optimized Dictionary. 17th March 2021 1. Fixed a concurrency issues, when multiple threads are running UPDATE against the same table, and the database could give incorrect data. 2. Bumped Minimum dot net version to 4.5 3. Fixed a regression where a long was being used, ( where a uint would suffice) in aggregative queries with cardinality between 500k and 4e10 4. Fixed an issue in count distinct, when using the whereclause, that was causing an exception, in some cases. 14th Feb 2021 1. irdbServer now logs database name when logging SQL statements and errors, to help with troubleshooting 2. When doing an update, if the column, with the clustered index has not been updated, then the clustered index will be kept. 12th Feb 2021 1. Improved Concurrency Operations with Crud Statements 2. Added new Concurrency test to irdbTest 3. When you do an Update/Insert on an existing table, it will remove the Clustered Index This helps fix a bug, where if you did an update on the clusteredindex column, the clustered index became corrupted. 4. Updates, preserve Indexes on 1-D columns, that haven't been updated. InMemoryDatabase.FORCE_NEW_TABLE_AFTER_UPDATE = true is now default 20th Jan 2021 1. Fixed a bug in unary expressions like select -a-b being parsed incorrectly as select -(a-b) If the first expression was negative constant, than it was not an issue. 2. Fixed an issue in OPENBALWITHZEROES and CLOSEBALWITHZEROES, where it wasn't adding the missing entries, if the group by clause only contained related dimensions of the balance dimension. 11th Jan 2021. 1. Added new irdb.ini CalendarWeekRule, that can expose CalendarWeekRule setting in Dot Net use by DatePart (Week. 2. Set CalendarWeekRule, now support by irdb sql to manually set via sql 8th Jan 2021 1. Improved Concurrency with SELECT, INSERT, DELETE & UPdate statements Note set InMemoryDatabase.FORCE_NEW_TABLE_AFTER_UPDATE = true, for best concurrency in UPdate statements. 20th Dec 2020 1. Improved Concurrency when Saving Datbase. 2. Improved Concurrency when doing CRUD SQL Statements. For full concurrency on SELECT operations use Alter Table MyTableName Set Volatile TRUE to let IRDB know, this table requires special handling for better concurrency Alter Table MyTableName Set Volatile FALSE to let IRDB know, this table no longer requires special handling. The volatile property is not persisted to disk at the moment. 3. Now wait upto 5 minutes instead of 30s, to delete previous irDB File 4. When Exporting to SQL Server, don't export temporary hidden columns. 4th Dec 2020 1. Added Support for cast/safecast ( getdate() as float ) } 2. Added Support for cast/safecast ( getdate() as double ) } 3. Added Support for DATEPART(weekday) 4. UPdated IRDB Manual 13th Nov 2020 1. Fixed a bug in invalid results in aggregate functions in CONCAT expressions. Functions with multiple parameters were being changed to MAXLIST in Query Rewriting. 2. Fixed problems with DECIMAL and GUID constants not being copied during evaluation of expressions with Aggregates. 3. Added suppport for DAY/MONTH/DAYOFWEEK/ QUARTER/YEAR/HOUR/MINUTE/SECOND/WEEK/MONTHNAME ( null) 4. Added support for datepart ( datatype, null ) 5. Added Some Additional brackets to functions in 1,2 in Code Generation. 6. Added additional brackets to CSTR code generation, when dealing with nullable Types. This fixes some query compilation issues involving agregate queries and some expressions that involving Date Part functions. 7. Added support for filters involving joins on the main fact table and the main fact table is a nocache subquery, to propagate filters on those joins into the subquery E.g select sum(1) from (select nocache * from orders ) orders inner join employees on orders.employeeid = employees.employeeid where employees.employeeid = 1 will be rewritten as select sum(1) from (select nocache * from orders where employeeid = 1 ) orders inner join employees on orders.employeeid = employees.employeeid where employees.employeeid = 1 8. Fixed a problem with Carriage Returns in the Row Limit Field in irdbQuery. 9. It now tries to parse the Row Limit field as an integer in irdbQuery. If it doesn't parse, it uses 1,000 as a default. 10. If there is an error compiling the query, the compilation error if any are now returned in the DebugText variable. 11. irdbCore Distribution Updated 12. When writing IRDB data to disk, limit things to at most 4 threads. 23/09/2020 1. Removed Missing Bitmap in irdbQuery. 2. Each tab in irdbquery can now remembers the value of Database that was selected for that tab. 3. Improved garbage collection on Query Cache. The memory utilized by the QueryCache wasn't being efficiently released, when databases were reloaded. This could end up eating large amount of the system ram, unnecessarily, 4. Every 6 minutes irdbServer will purge 1% of the items in the QueryCache. This will cause also expired items to be purged. Without this, expired items could linger for long time period. For the time being, this will also log, how many items are in the Query Cache for each database when it runs. 18th August 2020 1. added new markActivity ( List databases ) method to IPClient. When called this will the server know, the attached list of databases, are actively being used by an appServer, and not to unload them. 2 Fixed a bug in irdbQuery, that was causing errors to display incorrectly if the length of the message was exactly 200 chars. 3. Initial support for irdb.log file format moving to irdb_YYYYMMDD.log and deleting after 30 days. 4. Added a new option to irdb.ini help reduce memory consumption in irdbServer You can define a comma separated list of like expressions, that represent tables that you want irdbServer NOT to load, or to skip E.G skiptables=stage% will skip all tables that begin with stage 5. Trigonometric functions can now accept decimal parameters ( Internally a conversion to double will happen). Before this could generate a query compilation error. 30th June 2020 1. Added new addColumn API to irdbCOM, that takes an array of Object via com, and will create a database column with that type. public bool addColumn(String tableName, String columnName, String datatype, ref Object values) If tableName does not exist, it will be created. 26th June 2020 1. Updated irdbCore distribution so that irdbServerCore & irdbServerConsoleCore, instead of logging everything when run in console mode, to the console will check to see if log_dir is defined in irdb.ini, and log to the appropriate directory. 25th June 2020 1. Added a static variable to InMemoryDatabase called RIGHT_TRIM_STRINGS_ON_IMPORT, that when set to false will prevent spaces being truncated on the right when importing data. 24th June 2020 1. Fixed a bug involving ConCat function in aggregation queries. select concat ( 'a','b',orderid ) , orderid from orders group by orderid was not working. 21st June 2020 1. Fixed a bug in UNION ALL, if it contained N clauses, and the first N-1 clauses return 0 records , and the last clause had different column names from the first one. 2. Fixed a bug, when in aggregative queries involving group by expressions involving Coalesce on the non driving table(s). In previous builds, this could be evaluated as Null instead of as a concrete value 3. Added support in irdbImport for tableName.RowCount to return the number of rows in a table. 22nd May 2020 1. Improved checking for Statement Names & Cursor Names on Server Side 2. Improved Error Checking in Parameterized Queries with ? 3. Fix a problem with Decimal Type in Parameterized Queries with ? 4. Added support for GUID Types to Paramaterized Queries. 5. Added missing irdbImportCore to irdbCore Distribution. 21st May 2020 1. Fixed a bug when table names contains ']' in the SLURP commaand in irdbImport 2. Fixed a bug where a problem with loadDBtoTempDB could crash irdbServer 3. Fixed a bug in loadDBtoTempDB when a table Name contained ] Note irdbQuery has a hard limit of 654 columns atm. 18th May 2020 1. Added new SQL Ceiling Function to irdb SQL 2. Added a new feature, that when irdbServer is running in lazyLoad mode, Columns can be unloaded if they are inactive after a set time. Turned off by default. To enable this feature edit irdb.ini lazyload=true This must be set so that irdbServer, will run in lazyLoad mode lazyload_unload_column_timer=15 This sets the unload time to 15 minutes for inactive columns within the database. Optional lazyload_debug=true This allows to you to see in irdb.log when columns are loaded and unloaded to debug things Select * from information_schema.column_usage has two fields Column_loaded & Last_Accessed Currently Last_Accessed is only updated when a database is running in lazyload mode. To be able to lazyload a database. it needs to have a new block pointer header, that had been added since the lazyload feature has been added. Older database, without this header, will load, in standard mode. 3. Updated DotNet Core build to Dot Net Core 3.1 LTS from Dot Net Core 3.1 4. Removed a column limit of 1000 columns 1st May 2020 1. Fixed a Bug with Min and Max aggregate functions not working correctly with Date Expressions or GUID Expressions 2. Added a new keyword to Datasource to disable connection pooling, and force a new connection, every time Import is run. e.g. DATASOURCE a1=ODBC NOPOOL 'dsn=ir_northwind' This is designed to help improve stability when certain (odbc) drivers, are crashing during an import program. We noticed this happening in one case on legacy 2008 ( R2) instances, with newer ODBC Drivers. Disabling the connection pooling, and not reusing connections, seemed to improve stability. Imports will run slower though. 3. Add support for Encrypt to irdbProvider.dll 4. Fixed a bug, where the encryption parameters, wasn't being correctly setup in irdbdbcom, after using a connection string with encrypt set within it. 5. Fixed a concurrency problem with several queries trying to create the same index, with multiple columns at the same time. This also fixes an issue, where you could get an exception the first time loading a dashboard, and need to press refresh. 17th March 2019 1. Fixed some general bugs with Select Into, where a flag containing whether the columns contains nulls values was not being set. This could cause an issue where a query would give the correct result on saving and opening a file, but an incorrect result after several Select INTO 2. Reading and Writing data, should now use less memory, because it reuses certain buffers. Reading and Writing files should also be faster, especially on databases with large numbers of columns. 3. tableCount in InMemoryDatbase variable is now incremented and decremented using an Interlocked method. This should help fix certain situations where it could end up negative 4. irdbImport will now examines its arguments and remove -stats from the list of arguments, so irdbImport programs do not need to worry about its presence. 19th Feb 2020 1. Added some checks to make sure rows don't go over 2.1 billion row limit When Importing data from a datasource During a Union All, that appends two tables. It will do a row check first When running a regular non aggregative Query 2. Added a check into appendTable in InMemoryTable, so that when you call that method, it makes sure the table is decompiled 3. Enhanced the Concat function in IRDB-SQL to treat nulls as empty strings 4. Enhanced the Concat function in IRDBImport to treat nulls as empty strings 5. Enhanced CONCAT function irdb-SQL & IRDBImport to accept multiple parameters and use a stringbuild to concatenate them 6. IRDB-SQL, can now rewrite nest concat functions to the multiple parameter version 7. Fixed a bug, if you had a join with a table involving a constant, and the resulting table had fields that were referenced in the having clause, that weren't referenced in the select/where. 8. When a grouped expression on a table, is created, it adds a temp table. The algorithm on these has changed. The length of the hash has been extended. Dropping/updating the parent table of these, should drop the associated temp tables. This could help fix some accuracy issues, if the underlying table had changed, and the grouped temp table had not. 9. If you had select null inside a subquery, and then in non cached query mode, it tried to apply a filter null = someParenValue it could not evaluate this. The system can now handle nulls on the LHS of expressions better, and not generate an error in queries of this form. E.g select * from ( select nocache 1 as col1 union all select null ) as x where col1 = 1 should now run correctly 10. Fixed a bug, if the first clause of a UNION all was a simple select query, with a limit select * from orders limit 100 union all select * from orders 04/02/2020 1. Count ( * ) no longer returns null on empty tables. It now returns 0. 2. Added support for getUTCDate() to irdb-sql and irdbImport 3. Added Support for DESC to reorder table command in irdbImport 4. Fixed a series of bugs related to the ROUND Function. round ( 2.5 ,0 ) was returing 2 instead of 3 round (2, 0 ) was returning a compile 5. Added support for SSL to irdbServer, irdb Apis and irdbQuery irdbQueryhas a new Use Encryption Checkbox, when you create a Connection irdbServer has 3 new variables for irdb.ini Setting the encrypt variable to true will enable SSL/TLS You also need to provide a certificate file ( that can be read by X509Certificate2 ), and an optional password encrypt=true encrypt_cert=C:\some_path\yourCertName.pfx encrypt_password=passwordForCertFile There is a new option in connection strings. Adding encrypt=true, will force an encrypted connection. 6. IS NULL and = 0 return same result for float/double and Integer when all values are NULL 22/12/2019 1. Fixed a bug, where iF a numeric column contained all nulls, then a where expression with COLUMNNAME =0 would return true on the null values. This bug does not happen if there is at least one non null value in the table. 2. Updated IRDB Core Distribution as well 19th Dec 2019 1. Added support for Clustered indexes with the Lazy Loading Feature 2. Fixed a potential bug when using Date constants with Clustered Indexes. 3. Updated IRDB Core Distribution as well 18th Dec 2019 1. Added lazy loading support for IRDB Server. To enable lazy loading support on the server, you need to add lazyload=true to irdb.ini and restart the Irdb Service. 2. You can use lazy loading within IRDB Query by selecting the Open Database ( Lazy Load ) menu option from the File Menu. 3. You can lazy load a database in irdbImport by using the following syntax Datasource A1 = lazyload local irdb 'c:\irdb\data\northwind.irdb' I.E. You add the lazyload word before local irdb or local inmemorydb 4. When you Lazy Open a database it creates a tmp file containing a copy of the database. this tmp files will be named database.query_1.tmp .. database.query_5.tmp database.server_1.tmp .. database.server_5.tmp and database.import_1.tmp .. database.import_5.tmp and These filenames will be reused. If you are using a server file, in normal operation, when reloading it will cycle between 1 & 2. When the service is shutdown normally, the tmp file should be deleted When an irdbImport file terminates normally, the tmp file should be deleted. When an irdb_query instance closes normally, the tmp file should be deleted. It is possible though, that the tmp files could be left behind. In normal operation, the system, should eventually delete it, the next time it lazy loads the file. The main purpose of this feature is to reduce memory consumption, because it doesn't need to load the entire database. This is still an experimental feature. Some features like clustered indexes do not work correctly yet. Any time, a file is saved with a build later than Dec 2019, it will add the appropriate Lazy Load Header to the irdb file. Without this header, the lazyload feature cannot work, and the system will try and load the file normally. You can convert a file, by resaving it in irdbImport, or just opening and saving again with Dot net code. 16th Dec 2019 1. Fixed a bug involving clustered indexes on table with zero rows. If you used a > or >= on the clustered column in a whereclause , on a clustered table with zero rows, it would generate an Index out of range exception. 15th Dec 2019 1. Fix a bug that was causing the following exception, when running queries that involved clustered indexes, and you had a filter that involved a child table, that was joined on the clustered index but filtered on that column in the child table with 5 or more values. Exception occurred executing query. System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. 2. More Lazyloading development. Feature still under development. 31st Oct 2019 1. Added a check when you call addColumn in InMemoryTable, to make sure the the column is not null. If it is, it will generate an exception 2. Added some checks to make sure the parent column and child column generated correctly, during index creation 3. Added an option InMemoryDatabase.displayLowLevelSelectQueries, that can programmatically be set to true, to see the SQL Select statement being executed in InmemoryDatabase 4. When doing a Union All, and when you are about to execute the last query, it does a check to see if the previous parts returns 0 rows. If it does, it can skip appending the last result, and just execute it directly. 30th Oct 2019. 1. Added experimentlal support for Lazy Loading an IRDB file. added file summary onto end of IRDB file, with a pointer to it, near the start. Usable by irdb_query & irdbImport. Please do not use for the time being. 2. Fixed a bug in Aggregative Queries that used the Hash algorithm ,and had more than 134,217,728 rows in the output, or in an internal query. 3. Fixed a bug when a with rollup query with no results was returning 1 null row. It now returns 0 rows 4. Fixed a bug where a with rollup query, that had no aggregative functions, was returning extra null rows. 7th Oct 2019 1. Variables in irdbImport are now dynamically substituted in descending order of variable name, when the @@variable_name syntax is used. 2. Fix for Left Join Issue. Irdb, keeps track of columns containing nulls, because if it can work with non null columns it is 4-5 times faster. The problem was when it tries to determine which tables are related / unrelated, which can alter the above datatype, this step was happening after the column data types had been setup. I fixed things by moving the join compilation before the column data type setup. 3. If you have a query that groups on a dimension with 1 value, and the results are filtered complete by a where clause, it should now report No rows instead of 1 row with null values 4. Fixed a Query compilation issue if you had a nullable byte value, and the query had a group by and where with that expression. e.g. SELECT count(1), CONCAT('0',WEEK([OrderDate]) ) FROM [orders] WHERE CONCAT('0',WEEK([OrderDate])) IN ('02') GROUP BY CONCAT('0',WEEK([OrderDate]) ) 30th Sept 2019 1. Fixed a bug when you tried to use a view through the ODBC driver 12th Sept 2019 1. Fixed a connection leak when use have USEDISK in an import statement in irdbImport. 2. Fixed a performance regression when doing any kind of UNION ALL or table Append. This is now done in parallel (again). There was a change, that made it not parallel, and this could have impacted performance in some situations. 3. In irdbImport, you can now read data of columns when data is in uncolumnized mode. E.g. tableName.FieldName will now return the current value when in uncolumnized mode.or tableName.FieldName ( RowNumber ) To make this work, it will dynamically swith the uncolumnzed storage format from Dictionary / index lookup form, to plain array format, so using this feature, could cause your code, to use more memory. MoveFirst, MoveNext commands now work with Uncolumnized tables 4. Added Uncolumnize Command to irdbImport 5. Fixed a bug in sorting in queries using the WITH Rollup Option, when the order by column was not contained in the SELECT Statement 6. Improved INSERT INTO performance for insert statement with large no of columns and values. ( > 20 ) Adding internal cachedColumnIndex to InMemoryColumnBase to support this. 7. You can set a variable to NULL with SET in irdbImport 8. You can now check to see if a variable is = NULL in if commands This adds some basic Null capability to SET and checking for NULL. in other cases though NULL will not be handled correctly in irdbIMport 9. Added Assert command to irdbImport. This is designed to help test irdbImport. Execution will stop if the assertion fails. E.g. Assert 2+2=4 10. The SET Command now supports setting values of fields in tables in irdbImport, when the table is uncolumnized. e.g. There are two formats set tablename.columname = 100. This will set the current row of field columnname in table, tableName to 100. or set tablename.columname(RowNum) = 100. This will set the row RowNum of field columnname in table, tableName to 100. 11 Added REORDER TABLE command to irdbImport e.g. REORDER TABLE ORDERS by employeeid,orderid 30th Aug 2019 1. When the userdatabase option in irdb.ini is configured, and the database is not responding, irdbServer now enters a loop and retries connecting every 60s after start up when running in service mode on windows platforms. This is designed to make startup more resilient, after windows reboots, or window UPdates, so that the database server has a chance to start up. 2. In irdbImport, you can now read data of column when data is in uncolumnized mode. E.g. tableName.FieldName will now return the current value when in uncolumnized mode. To make this work, it will dynamically swith the uncolumnzed storage format from Dictionary / index lookup form, to plain array format, so using this feature, could cause your code, to use more memory. 2ng Aug 2019 1. INSERT SQL table can now include the table you are inserting into , as part of the source SQL Statement. 1st Aug 2019 1. Added -stats option to irdbIMport 2. Fixed a bug when Inserting into a table that had deleted records and joins created on it. 25th July 2019 1. Modified SQL Like Function to support back tracking, when matching wild characters. Complete rewrite of SQL Like 2. Fixed some issues, when we have optimizations for SQL Like, (test% %test %test% ), to check for the presence of [ , _ characters 3. Modified toDataTable() method in InMemoryTable, to ignore hidden columns. 12th July 2019 1. Fixed a bug when dropping tables, where it could generate an "Object reference not set to an instance" exception, in the Foreign Index Cleanup code. 6th July 2019 1. Added new backend handling for executeTempDB, that allows you to execute SQL statements against a TempDB 2. Improved clustered index support, to now analyze LEft and Inner joins on the clustered column. Filters on the joined table can now take advantage of the clustered index. 3. You can now create clustered indexes, with IRDB-SQL. Same syntax as IRBDIMPORT 4. Before if you have the wrong data type on the main clustered column, it could generate exceptions, if the datatypes didn't match Now it is more robust in handling data type convertsion on the clustered column. 5. Fixed a bug if you joined the same table using a clustered column, and had a filter on the same column, in the joined table, it would not consider the alias name. It could result in an incorrect filter being generated, if there was a filter on the column in the joined table. Now it explicitly checks the table alias 20th June 2019 1. Fixed a Potential case sensitivity issue in Non English Locales, in IRDB-SQL / IRDBIMPORT table names, Aliases and Column Names. This manifests itself in the danish locale, when you have a table name say with aA in the name, and try and use aa to access it. 2. Added protocol support for NOOP. 3. Server will now disconnect the connection, if it does not recognise the command. 4. Updated irdbCore to June 20th 2019 Build 13th June 2019 1. Added support for GUIDs / UniqueIdentifier. In IRDBIMPORT to import GUIDS as actual GUIDS type set preserve uniqueidentifier true Otherwise they will import as strings The advantage of GUIDs is they only use 16 bytes as opposed to 100+ bytes for a GUID stored as a string. 2. Added support for UniqueIdentifier Data Type In IRDB-SQL and IRDBIMport 3. Added support for newid() function to create GUIDS in IRDB-SQL 4. Fixed a bug, when using coalesce with a subquqery in parameter 1 that returned null or no results. 7th June 2019 1. Added optimization to reduce memory space, when a tables contains more than 1 null column. System will try and use the byte array from one column for the other null columns. Heurisitic is applied when loading tables, and when compiling tables 2. Added an optimization, that when working with null columns, to use a Zero in the code, than looking up a zero in an array. Potentially speeds things up, by reducing memory access. 3. Fixed a bug in aggregative queries when you are joining from one table to the same table with different table aliases, and you then have a group by that involve the two different aliases, and where at least one of the group by aliases cardinality matches the number of rows in its table. Internally we have an optimization, that tries to reduce unnecessary group by's and it was not considering aliases. 23rd May 2019 1. Added experimental support for COLLATE statement in order by clause. Currently uses Dot Net Globalization strings . e.g. Select * from tablename order by columnname collate 'de-de' select * from o 15th May 2019 1. Improved performance with group expression with 1 field and null values ( Compared to May13th build) If the null value is mapped to a null value, it can use the JOIN Algorithm versus created a new separate field 13th May 2019 1. Fixed a bug in group by expressions that involve nulls evaluating to definite values This fixes a related bug in SELECT Distinct. e.g. in select distinct case when shipregion is null then 1 else 0 end from orders not returning a row with 1. 2. Select DISTINCT should now return more correct results There is still a known issue though in SELECT DISINCT when you have a group by expression, that through a LEFT, or Full Join, introduces nulls into a field. If you have a group by expression, that converts NULLS into definite values, then this expression will still evaluate as a null, for the case of nulls introduced through a join. This is not a problem, if your expression returns nulls for null inputs. 3. Added parsing support to ORDER BY Clause for COLLATE. Feature not implemented yet. 8th May 2019 1. Decimals are no longer truncated to integers in COM Interface. Does not affect Dot net APIs 2. Fixed a bug in queries that use WITH ROLLUP, and the order by clause not working correctly in certain cases. 16th April 2019 1. Fixed a bug in Count Distinct, on simple Database columns, when running on larger numbers of CPUS, and it was generating occasionally an exception Object reference not set to an instance of an object, CDMerge.SyncMerge. There were some rare null cases that needed to be handled 2. Fixed a bug when you had a calculated expression on one of the join sides, and it would evaluate once, but not the second time. E.g. The third statement in this program would cause a problem. This bug was introduced by the enhanced join logic introduced in Feb/March 2019 e.g. select orderid + '_' + customerid + '_' + employeeid as empid , * into #joinexp3 from orders select t1.empid from #joinexp3 as t1 inner join orders as t2 on t1.empid = t2.orderid + '_' + t2.customerid + '_' + t2.employeeid select t1.empid from #joinexp3 as t1 inner join orders as t2 on t1.empid = t2.orderid + '_' + t2.customerid + '_' + t2.employeeid 10th April 2019 1. When loading / reloading in memory datafiles, and it will now add up the file sizes on disk. When a threshold is reached, which is 500 MB by default, it will then do a Large Object Garbage Collection Instead of every load / reload as before. This is designed to improve performance, when you have large numbers of data files loaded, and don't want to do a GC For the smaller data files. There is a new irdb.ini setting that controls this behaviour. minSizeToLoadBeforeGC=500 will set the cumulative minimum size of files to load of 500 Mb, before triggering a LOH GC The Number specified will be multiplied by a million to get the file size limit. 500 is the default. 2nd April 2019 1. Fixed a bug in irdbQuery, where if you ran dbstats, then you couldn't sort the columns. 2. Fixed a potential bug, in expressions, that could contain null in COUNT expressions, being converted to sum(1). e.g. count ( case when col1='a' then col1 else null end ) select * into #temp1 from ( select 'a' as col1 union all select 'b' as col1 union all select 'c' as col1 ) a select count ( case when col1='a' then col1 else null end ) from #temp1 This now returns 1 instead of 3 This also fixes a potential bug with AVG 3. Fixed a bug in the EXPORT command in irdbImport, where it didn't support string columns that had 0 chars 4. Added Export command to irdbImport Syntax is EXPORT a1.table1 from me.table2 This will export table2 to datasource me to datasource a1 with name table1. It will try and create the table if it does not already exist. You can only currently export to SqlServer Data sources from the ME datasource. 5. Added EXPORT with TRUNCATE & EXPORT WITH DROP options to the export, command to truncate or drop the existing table. ( In the create table syntax ) Because of the risk of data loss, is this command is used against client OLTP databases, you need to use the command ALLOW TRUNCATE on {Datasouce} or ALLOW DROP on {Datasouce} , before running this command 6. Like on datetime columns now works 7. LIKE operator on non-string columns fails if there are no '%' in the like string 8. Equality in Where clause can now support having integer expressions checking against string constants e.g. select * from orders where orderid='10248' 9. Removed column level debug info from Export command in irdbImport 10. Increased Batchsize from 5,000 to 100,000 in SqlBulkCopy in export command to improve performance. 25% time reduction. 15th March 2019 1. When using temp tables that start with #, subqueries that use a #temp table will now be NOCACHE by default. In previous builds, it was possible for the system to leak lots of temp tables if you used a query that had an inner table that depended on #temp tables 13th March 2019 1. Fixed a bug in stepping in SQL Debugging 2. Added new Commmand Line debugging tool for running IMP Scripts it is called using irdbtest impdebug yourImportScript.imp followed by optional parameters for your script If your script contains the word BREAKPOINT, it will return control to the debugger. While in the debugger, you can execute Ad Hoc irdbImport commands using the current context C or CONTINUE will make the debugger continue execution until the next break point or program finish V or VARS will display the current variables in the current context S will step to the next IMP Statement quit, or CTRL+C will cause the debugger to exit ! {text} - will execute the {text} part of your command as SQL The last SQL command results will be displayed as CSV {text} - will execute the {text} part of your command as an IRDBImport script using the current context 11th March 2019 1. Error Message when select from an unknown function with no parameters, now displays a proper function name does not exist 2. Error message when joining tables on multiple fields, and they have incompatible data types is now clearer 3. Added new Command Line Debug Console for SQL Scripts. It is called using irdbtest debug database.irdb yoursqlscript.sql If your script contains the word BREAKPOINT, it will return control to the debugger. While in the debugger, you can execute Ad Hoc SQL commands using the current context C or CONTINUE will make the debugger continue execution until the next break point or program finish V or VARS will display the current variables in the current context S will step to the next SQL Statement quit, or CTRL+C will cause the debugger to exit 4. Added new Command Line MultiRun program to test consistency of the an sql script. It takes 3 parameters. Param 1, is the name of the InMemory database. Param 2 is the sql script to run. Param 3 is the number of threads to run. This will cause the program to launch Param 3 threads, and it will pause execution after each step in the sql script, and compare the variable context, and the check the number of tables, and a summary of their columns, rows and value count, to make sure it is consistent. If it finds an error, it will display the next step in the script, along with the differing values, between the two different contexts e.g. irdbtest multirun northwind.irdb multiruntest2.sql 8 5. Added new build in debug function that can act as a table source, and give you an overview of the current context, to help debug SQL SCripts. e.g. SELECT * from sp_debug() as xyz 6. Added option to irdb.ini to help turn off new speedy append and decompilation routines to see if it helps consistency This is enabled by adding oldappend=true to irdb.ini 07 March 2019 1. Increased the amount of time irdbLoad waits from 10 minutes to 60 minutes, before timing out. 01 March 2019 1. Improved Substring to handle a starting postition of 0 and negative numbers, to improve compatibility with SQL Server substring function 2. Updated irdbProvider, so if there is an IOException, when executing a DBCommand, it will try the failover host to run it. 3. Updated irdbServer, so that if the database has been shreaded by an unload Event, it will rerun the query against the new version of the database. This partly interacts with the Shred_Timer irdb.ini file variable. A database will not be unloaded if the inactivity threshold has not been set. Setting Shred_timer to 0, may potentially cause shreadding immediately after reload, potentially freeing up the memory quicker. 28th Feb 2019 1. Execute Command with stored procedures can now take @variables 2. Added CAST_AS_LONG/CAST_AS_INT/CAST_AS_DOUBLE/CAST_AS_FLOAT/CAST_AS_DECIMAL/CAST_AS_BOOL/CAST_AS_SHORT functions These functions were needed to help fix a related bug in assign variables with null values. 27th Feb 2019 1. Added support for Custom IO API Interface 2. Subquery expressions that return no rows or Null will now be aware of their datatype. 25th Feb 2019 1. Added Support for windows authentication to IRDB In a connection string to specify windows authentication add windowsauth=true to the connection string e.g. irdb=northwind;windowsauth=true; 2. IRDB_Query now supports Windows Authentication 3. IRDBProvider supports Windows Authentication 4. IRDB Server now supports Windows Authentication. To enable Windows Authentication in the irdb Server, you need to set two variables windowsauth=true windowsauth_role=Some Role/Group to check for membership of. A valid value for windowsauth_role is Everyone 21st Feb 2019 1. Added support to the Connection String for failover_host e.g. host=server1;pwd=mypassword;failover_host=server2 This is currently supported in the IpClient API and dot net data provider During connection, if a connection error is caused in connecting to the host, it will try the failover_host It does not retry, if there is a username/password error. 2. Allow SubQuery Expressions with no ROWS e.g. select ( select orderid from orders where 1=0 ) 20th Feb 2019 1. Fixed a bug when a variable was declared inside an SQL Fix Statement. Query would fail to run. 20th Feb 2019 1. Added new interface, so you can get notifications, and control, when database unload events should occur. public interface AutoUnloadNotify { bool canIUnload(string datafile); void unloadNotification(string datafile); } You can set your interface with the following call in InMemoryDBHost. public void setAutoUnloadNotify(AutoUnloadNotify unloadNotify) 17th Feb 2019 1. Fixed a potential bug in the limit clause if the sum of start and end > 2^31 15th Feb 2019 1. More work on Appending Data speedup. Added some cases missed in 5th Feb Build. Now should be able handle adding uncompiled tables more efficiently 2. Adding special handling to use Array grouping calculation method, when query contains 1 grouping criteria and no where clause Hashing routine is slower in this case 3. Overhauled IRDBTest. This now offers a list of test subroutines. Stress - needs to be added as the initial for previous versions. New Decompile and APpend subroutines to test Decompiling and Appending Data 4. Tweaked no cpus to use in aggregative calculation to be half the number of available processors, if cpus>16. ( before it would only do this is no of Calculations> 5) 5. In case 2 Above, it will half the number of CPUS again when Cpus>32 and no of grouped rows>3000000 6. Fixed a series of compilation issues if combining TinyInt and SmallInt values. It was giving compilation errors. 5th Feb 2019 1. New Routine that decolumnizes tables is 3-10 times faster, and uses less system resources 2. Appending a table that is compiles to an uncompiled table should be faster. 25% time reduction appending and compiling data 1 + 2 should help with Incremental updates 3. Byte Arrays were being duplicated in previous version of IRDB. They are now being checked for uniqueness. POtential change may reduce memory consumption, by reducing duplicate byte arrays. 4. Added new hash routine to help calcualte hash of Byte Array type. 5. Handle case when duplicate byte arrays are being appended. 24th Jan 2019 1. IRDB_Query now uses 4 times less memory and is 4 times faster rendering large tables. Can easily render 10 million rows with less 1 GB of ram uses 2. Views are now appear in getTables, & GetColumns IpClient Methods 3. Views are now validated when created. 4. View columns are now visible in Information_Schema.Columns 5. Added support for basic Correlated Queries, that appear as expressions in the SELECT statement. All the parent query fields must appear in the Where Clause, joined to child query fields, and only AND Is supported E.g. select employeeid, (select sum(1) from orders where bbb.employeeid =orders.employeeid and bbb.customerid =orders.customerid ) as total from orders as bbb order by employeeid 6. IRDB_Query now uses the Faster Roslyn Query Compiler. 7. IRDB_Query now will run a background garbage collection every half hour, to try and release memory. 17th Jan 2019 1. You can do inner Joins in queries just by using the JOIN Keyword 2. Fixed an efficiency in our new Cyclic Join feature, that was runing non cyclic joins into cyclic joins if the first clause of the join, was a join involving expressions 3. Fixed a potential problem with left Joins if the child table was one to many with the parent, and the child table contained deleted rows, then it could add multiple nulls corresponding to the child. Only one null should be possible 4. Fixed a similar issue when doing cyclic left joins and the child table was one to many with the parent ( Joins involves 3 or more tables), then it was sometimes adding unnecessary null records on the child table, even if there was a match It should only add a null record if there are no matches. 16/01/2018 1. Added support for CREATE VIEW and DROP View to IRDB-SQL e.g. create view test101 as select * from orders 2. Added support to information_schema for views information_schema.columns does not currently show columns for views. IPClient API methods do not current show views. 3. Views should now work with SELECT,WITH,UPDATE,DELETE queries 4. Added SQLEXEC command to irdbImport to execute a file containing sql from DISK e.g. sqlexec 'yoursqlfile.sql' 15th Jan 2019 1. Added support for CREATE VIEW and DROP View to IRDB-SQL e.g. create view test101 as select * from orders 2. Added support to information_schema for views information_schema.columns does not currently show columns for views. IPClient API methods do not current show views. 3. Views should now work with SELECT,WITH,UPDATE,DELETE queries 4. Added SQLEXEC command to irdbImport to execute a file containing sql from DISK e.g. sqlexec 'yoursqlfile.sql' 14th Jan 2019 1. Fixed a potential bug, with DROP Table when used within Stored Procedures 2. WITH Statement can now be used with INSERT,UPDATE,DELETE 3. Restructured JOIN Generation code, to try and follow order of original SQL. Previously it went Depth First, through the original Join Tree. Now it orders how things are joined, based on the table order within the FROM Clause This ensures, that if you are using non parent child aliases as part of the join, it will be more robust. 13th Jan 2019 1. With Statement now only applies to the following SELECT Statement 2. You can now have multiple WITH statements, comma separated. 7th Jab 2019 Build 2. 1. Fixed a bug, where Stored User Defined Functions were not being saved correctly 2. Information SChema now works correctly within EXISTS e.g. if exists ( select * from information_schema.routines where routine_name='my_function') drop function my_function 3. OBJECT_ID now supports FN,IF,TF as parameters to test to see if a function exists. 7th Jan 2019 1. Added new MONTHNAME function to IRDB-SQL. This returns the month of the given date function 2. if EXISTS ( SELECT STATEMENT ) should now work 3. When you have complex expressions in the SELECT statement, you no longer need to have the group by expressions as distinct columns in the select statement ( for database fields). E.g. Select sum(1) + employeeid from orders group by employeeid will now work. 4th Jan 2019 1. You can put RANK(), DENSE_RANK() & ROW_NUMBER() functions in Aggregate expression, involving more than 1 term. 3rd Jan 2019 1. Added Print command to IRDB-SQL 2. Made the new table joining feature more robust, fixing some issues. 2nd Jan 2019 1. Added Support for scalar functions. Scalar functions only work on constants at the moment, and cannot work on database fields as part of a query 2. When joining Table X in a JOIN clause, it now supports putting in constant parameters for table X. Constants cannot occur in the first part of the ON clause. This must contain DB Fields, and it defines the Parent Child Relationship e.g. select * from orders inner join [order details] od on orders.orderid = od.orderid and od.productid=11 3. When joining Table X in a JOIN clause, it now supports putting in other tables and fields apart from the parent & Child. This cannot occur in the first part of the ON clause. This must contain DB Fields from the Parent and Child. The other DB Fields, must be previously defined in the list of tables. select * from orders inner join orders as orders2 on orders.orderid=orders2.orderid inner join [order details] od on orders2.orderid = od.orderid and orders.shipvia =1 4. Stored Procedure now support Schema Prefix in Execute command. 28th Dec 2018 1. Added new Replicate Function, that takes 2 parameters. Param 1 is a string and Param 2 is an integer. It returns a string that contains Param1 repeated Param 2 number of times. 2. Added a special built in function to take a table, that contains a column, with repeated values, that need to be split out, and generate a version of that table, with rows repeated by the split column. The function is called split_tables, and takes 3 parameters. Param 1 is a query in parentheiss Param 2 is the name of the column to split Param 3 is the separator character. E.g. Param 1 is a query in parentheiss select employeeid, group_concat(orderid separator ',' ) as combined into #test1 from orders group by employeeid select * from split_table( (select * from #test1 ) , combined,',' ) as xyz The first line combines everything by employeeid, and the second splits it out again. 27th Dec 2018 Build 2 1. Fixed a bug where subqueries in SET statements weren't working with variables 27th Dec 2018 1. When you select on a table that is not compiled, the system will now autocompile the table. 2. Added support for Table value functions that are made up of several programming statements. e.g. create function test1 ( @param1 integer ) returns @outputTable table (col1 int ,col2 int ) as BEGIN insert into @outputTable select 1,2 insert into @outputTable select @param1,@param1 end select * from test1 (10) as xyz 3. In General the database will uncompile tables when you insert and recompile when you select, update or delete on it. This programming is suitable for working on Temp Tables. However it is NOT multi thread safe, if different threads are inserting and select at the same time on the same table. 4. RETURN Keyword will cause a user defined function to return. 5. Cloning InMemory Tables, now works with uncompiled Empty Tables. 23rd Dec 2018 1. Uploaded New Dot Net Core Distribution at irdb_core.zip. Contains the following. ( No IRDB_QUERY yet ). irdbLoad dotNet irdbLoad.dll params irdbImport dotnet irdbImportCore.dll yourimpfile.imp Windows Service - will probably only run correctly on windows dotnet irdbServerCore.dll irdb.ini Simple irdbServer Console app dotnet irdbServerConsoleCore.dll irdb.ini 21st Dec 2018 1. IRDB-SQL now supports CONVERT ( String Expression, datetime, 101 ) to explicitly parse 'MM/DD/YYYY' Date Strings 2. IRDB-SQL now supports setting multiple variables with a SELECT statement 3. IRDB-SQL now supports the EXISTS Clause 4. IRDB-SQL now support SQL Server UPDATE syntax, with a FROM Clause 5. IRDB-SQL now support INSERT without the initial column list e.g. INSERT INTO TEst values ('a' ) or Insert into table1 select * from orders 20th Dec 2018 1. Initial Query Compilation is up to 3 times faster 2. Added new Object_ID function that takes two string constant parameters in IRDB-SQL The first is the name of the object. If the second parameter is a 'U', it will check to see if a (temp) table with that names exists. If the second parameter is a 'P', it will check to see if a stored procedure with that names exists. E.g. if object_id ( '#test22' , 'u' ) is not null drop table #test22 will check to see if temp table #test22 exists and then drop it 3. Added support for Try / Catch logic in IRDB-SQL e.g. BEGIN TRY select * from ordersxxxx select 'ssss' END TRY BEGIN CATCH select 'an error has occured' END CATCH 19th Dec 2018 1. Added support for SQL SELECT Pivot Operator e.g. select pivotTable.* from ( select customerid ,year(orderdate) as orderYear,freight,employeeid from orders ) as sourceTavle pivot ( max( freight) for employeeid in ([1],[2],[3],[4],[5],[6],[7],[8] ,[9])) as pivotTable order by customerid,orderyear Support for MIN,MAX,SUM atm. 2. Added support for INSERT INTO now supports using a SELECT query as its source 3. CREATE TABLE now supports a default value for columns 18th Dec 2018 1. Added support for inline table value functions in IRDB-SQL They are created with the CREATE FUNCTION and can be dropped with DROP FUNCTION Inline table value functions only contain one select statement ( or union all of select) statements. As example would be Declaring CREATE FUNCTION xyz ( @orderid int ,@orderid2 int ) RETURNS TABLE as RETURN ( select * from orders where orderid in (@orderid , @orderid2 )) Using select * from xyz(10248,10249) as test22 2. Added support for WHILE loops in IRDB-SQL. BREAK & CONTINUE also supported 3. Fixed a bug in CHARINDEX to make it more SQL COMPLIANT The corrected version is now defined as : CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] ) The old verion was definited as: CHARINDEX (expressionToSearch ,expressionToFind [ , start_location ] ) 4. Added a new function to IRDB-SQL called isNumeric, that returns if a given parameter is null or not. 17th Dec 2018 1. Added support for Temp tables that start with a # in the name to IRDB-SQ: These are automatically dropped at the end of the program Temp Tables also work within Stored Procedures. 2. CREATE TABLE INSERT INTO, SELECT INTO, COLUMNIZE, now accept Schema Name as part of the naming convention. 14th Dec 2018 1. Added support for passing Null Parameters in the irdbProvider.dll 2. Added CREATE TABLE, INSERT INTO , Statements in IRDB-SQL 3. Added Columnize TableName into IRDB-SQL. Call columnize on a table where you have been inserting records, in order to query it. Example 1 create table ct_1 ( col1 integer , col2 long , col3 long , col4 varchar , col5 varchar(50) , col6 nvarchar(100), col7 nvarchar(max) , col8 decimal, col9 bit, col10 real, col11 float,col12 smallint, col13 tinyint, col14 date, col15 datetime, [col 16] ntext ) columnize ct_1 Example 2 create table test902 ( col1 varchar, col2 datetime, col3 long, col4 decimal, col5 integer, col6 bit , col7 double) insert into test902 (col1,col2,col3,col4,col5, col6,col7) values ( 'aaaaaa' , '2018-12-31' ,11111,1111,1111,false,92.2 ) columnize test902 4. Fixed a problem integer keyword not being recognised in irdb_Import, when using Create Table 5. Fixed a problem with BOOL Variables not being created properly 6. Fixed a problem with TINYINTs not being created properly in irdb_import CREATE TABLE 13th Dec 2018 1. Overhauled irdbProvider to make it an independent DLL. It has not external dependencies. 2. irdbProvider.dll now supports Dot Net 3.5. This means more classic Dot Net Apps can now connect to the InMemory Database 12th Dec2018 1. Added support for default values in stored Procedures 2. Added Support for NULL to SET Command 3. Operator + Will work better with NULL Types 4. Added support for NULLS to stored procedure parameters 5. added support for calling stored procedures with names equal values e.g. exec test101 @orderid=10500 6. Not all parameters need to be specified in Stored Procedures 7. Added parsing support for varchar(MAX) 11th Dec 2018 1. Added ability to execute dynamic sql with EXEC ('select * from orders') The string must be contained in paranthesis 2. Added if support to IRDB-SQL declare @testif3 as varchar set @testif3 = 'AAAA' if @testif3 ='AAAA' begin select * from orders end else begin select * from customers end OR declare @testif3 as varchar set @testif3 = 'AAAA' if @testif3 ='AAAA' select * from orders else select * from customers 3. Added built in support for a stored procedure called sp_executesql. This is a special stored procedure to help compatibility with SQL Server. The First Parameter is a parameterized sql statement with parameters The second Parameter gives a list of the parameters and their datatypes ( N-2) in total. Parameters 3 ,... N are the N-2 values that map to the list of parameters e.g. exec sp_executesql 'select * from orders where orderid=@orderid or orderid=@orderid2' , '@orderid int , @orderid2 int' , 10248 ,10249 6th Dec 2018 1. Adding new GROUP_CONCAT aggregate function function, to concatenate a list of strings together GROUP_CONCAT ( orderid ) or GROUP_CONCAT ( orderid SEPARATOR ',' ) will add a separator between the entries. Currently it sorts the data, but this could change 2. Add support for Stored Procedures. CREATE PROCEDURE and DROP PROCEDURE and EXEC are now supported by IRDB SQL E.g. CREATE PROCEDURE test104 @param1 integer , @param2 integer AS BEGIN SELECT * FROM ORDERS where orderid in (@param1 , @param2) END EXEC test104 10248,10249 3. Updated IRDBProvider to remove dependance on dynamic variables 4. Updated IRDBProvider to support CommandType.StoredProcedure , so Stored Procedure can be called directly through the Data Provider with Parameters 5. Updated Server backed to support 4 6. Added Explicily Flush, to several server methods, to improve robustness. 7. Added information_schema.Routines 25/11/2018 1. Fixed a bug when new Agg functions MODE,MEDIAN, PERCENTILE_DISC, PERCENTILE_CONT, where combined in select statements that had aggregative expressions e.g. select mode(x), case when min(x) > 100 then 1 else 0 end from sometable 2. Fixed a bug when using @Variables and getDate() 3. Fixed a bug when using @variables with strings containing a single quote 4. Added a perforance optimisation, when setting an @variable to a constant. 5. Fixed a bug with IN clause in HAVING Clause. E.g. select employeeid,sum(1) from orders group by employeeid having employeeid in ( 1,2,3,4,5,6,7,8) 12/11/2018 1. Added new Aggregative functions MODE,MEDIAN, PERCENTILE_DISC, PERCENTILE_CONT. There is no support for the OVER Clause or by ordering DESC. E.g. select customerid, mode(employeeid) , median(employeeid+1000) , PERCENTILE_DISC ( 0.5) within group ( order by employeeid) , PERCENTILE_CONT ( 0.5) within group ( order by freight ) from orders group by customerid The Median (X) function is shorthand for PERCENTILE_CONT ( 0.5) within group ( X ) PERCENTILE_CONT returns a double for numeric inputs and a date for date inputs PERCENTILE_DISC,MODE returns the datetype of the parameter. 05/11/2018 1. Added new backend API support for dropTablefromTempDB 2. When a table or table chunk is uploaded to tempDB and the table is not decompiled, it will now resort any string columns, so that the Dot Net String order will be used rather than Java String order. 3. Similarly if a database is uploaded with the TransferDB method, it will resort all string columns 4. The following code snippet can be used to resort all the string columns in a table. InMemoryDatabase db = new InMemoryDatabase(@"c:\irdb\data\zzz.irdb"); db.resortStringColumns(); db.save(@"c:\irdb\data\zzz.irdb"); 09/10/2018 1. IRDB Query error messages, resize better, when the error message doesn't contain many carriage returns 2. IRDB Query can connect on non standard ports. ( Servername:port ) in the server name windows 3. Added backend support for loading existing databases into a temp database, to help incremental updates. 23/09/2018 1. IRDB SQL Now supports multiple line sql program. You can now send several statements at the same time 2. IRDB SQL now supports declare and set commands 3. IRDB Sql now support set querylimit = 500 to set an automatic limit to the results retuned by a query 4. IRDB Query error messages are now scrollable if they dont fit on the screen. 5. IRDB Query build in query limit, should now work with more SQL Statement automatically. 30/07/2018 1. Fixed a mispelling in irdbImport. Datasource was spelt Datasouce when loading local irdb DBs 2. Improved Accuracy of DatePart H,N,S and DATEDIFFMILLISECOND. In certain cases there was a rounding error of 1. 3. Added support for RIGHT JOINS & FULL OUTER JOINS in the first set of tables, before any cross joins 4. Added a new enhanced version of lastprice, called LastPriceWithZeroes to add zero rows for all dates. It takes a potential query, and extracts the unique date dimension part with all the non date filters removed, and adds it to the range of possible output values, for each grouped values, that it does the lastprice function for. 5. Booleans can now be part of Joins. 15/06/2018 1. Fixed a bug where appending one table to another, in irdbImport, was causing an exception, when you did an information_schema.columns query. This was caused by the 21/05/18 build, where the memory improvements, was shredding the old table when it was deleted. 2. Fixed a bug where the REPLACE command in irdbImport was not working. This was caused by the 21/05/18 build, where the memory improvements, was shredding the old table when it was deleted. 07/06/2018 1. irdbImport will now autodetect UNIcode ByteOrderMarks. This will help some scripts with UNICODE characters work better. 2. You can now specify semi colon a separator in the CSV Parser. You need to use the word "semi", without the quotes. 27/05/2018 1. Fixed an issue, where if you tried saving a tempDB for a user, who didn't have a data directory, it would crash the server. Now it will return an error. 2. Fixed an issue, where if an exception occurred saving a tempdb, it would crash the server. Now, it should catch the exception, and return an error msg to the client 3. Fixed an error if you were trying to columnize a table in a tempdb, it would crash the server. Now, it should catch the exception and return an error msg to the client. 4. Major error messages with tempDBs should now be logged, to the irdb.log file as well 5. Fixed an error, when trying to save an InMemoryDatabase file, and the old File was locked, it would generate an exception, and leave a temp file lieing around 23/05/2018 1. Fixed a bug in irdb_query, where it couldn't open databases. ( Introduced in 21/05/2018 ) build 2. When compiling tables, with the compileTable in InMemoryTable, it now introduces a lock object, so 2 calls cant compile simultaneously 3. When saving TempDBS, this now happens in a background thread, so if the client disconnects, the process can finish 4. When columnizing tables in TempDBs. this now happens in a background thread, so if the client disconnects, the process can finish 5. Added new backend support for new IPClient method listTempDBTables, that returns, tableName, rowCount and Compile Status. 21/05/2018 1. Improved algorithm to help garbage collector, when loading, reloading and unloading databases in irdbServer Associated new ini setting called shred_timer. We perform a shred operation on inactive databases, when they are unloaded, or hit by the unload timer. When a database is reloaded, it is shred, if it has been inactive for 120s, to allow existing queries to finish running. Setting this to 0, will cause the unloaded database, to be shredded, so its memory is garbage collected immediately after reloading, at a cost of some queries potentially erroring out. 2. Improved algorithm to help garbage collector, when loading, reloading and unloading databases in irdbImport helps potentially reduce memory usage 18/05/2018 1. Fixed a bug when you had a case statement involving a double and decimal in the outputs, and it would generate a compilation error 2. Improved Importing Data from Mysql. Can now handle signed Bytes, Unsigned shorts,ints,longs. These get converted to the next biggest datatype Short, int,long,decimal resp. Also we recommend adding Treat Tiny As Boolean=false as a parameter to your connection string. e.g. When connecting from irdbImport and if you want to you the MySql Dot net Connector load assembly 'mysql.data.dll' datasource a1 = DOTNET CONNECTION 'MySql.Data.MySqlClient.MySqlConnection' 'Server=YourServerName;Database=DBName;Uid=yourUserName;Pwd=yourpassword;Convert Zero Datetime=True;Allow Zero Datetime=True;Treat Tiny As Boolean=false' 17/05/2018 1. Added new mechanism to unload temp databases on the server, if they are inactive for 120 mins. 2. New irdb.ini setting unload_tempdb_timer, can be used to control this. ( Time in minutes ) 3. Added new backend routines to list tempDBS, and remove them 4. COALESCE now works correctly in JOIN expression on subqueries 5. You should now be able to use expressions in the ON Clause for NOCACHE subqueries, which you couldn't do before. 16/05/2018 1. Better support for Out of memory errors when uploading to IRDB Server. 15/05/2018 1. Updated new APIS for uploading data to the irdb server. Now a GUID string is returned with a reference to the tempDB. 15/05/2018 1. Added new APIS for uploading data to the irdb server. Currently only available in the Java Client. 06/05/2018 1. Else is now optional in CASE statements 2. Fixed a bug when running in nocache mode, and a subquery had a UNION ALL without columnnames and was causing problems with query simplification. select * from ( select 1 as test1 union all select 2 ) xyz where test1=1 3. ImpStringProgram, works better when you pass a null string array for the parameters 4. What you can group by is now less restricted. You can now group by expressions of multiple database fields, as long as they are all from the same database table. Is is implemented by dynamically adding a hidden field into the table, with the expression evaluated. This will help simplify implementations, as this will now not need to be done so much 5. DayOfWeek function was returning 0..6 instead of 1..7 when it was evaluating against date columns that could contain nulls 6. Balance functions now work with DECIMAL Types 7. Contains an enhancement to the LastPrice functionality. - Test Build for non production use The calculate the min value of the date ( or driving dimension ) , and then calculate the last value before that date , and then apply it into the lastPrice cross tab. In the case your query does not return any data at all, it will not calculate anything, as it does not know what min date to use. 8. Improved progress messages in irdbImport, to help improve troubleshooting 9. irdb will now return the specific expression, that is causing errors, to help troubleshooting. 11/02/2018 1. Added new API to IPClient for Deleting DB Files + related irdbServer changes. public bool deleteDB(String dbName, out String errors) 07/02/2018 1. Added new API to IPClient for uploading InMemoryDatabases + Appropriate server changes public bool transferDBToServer(String dbName, InMemoryDatabase db, bool loadWhenFinished, out String errors) 2. Added new command to SQL Query language, to turn on and off the query_log SET QUERYLOG=10000 ( Enable querylog with a log size of 10,000) SET QUERYLOG=0 ( Turn off query log ) This new method also allows you to turn on and off the query log at a database level 3. Added new information_schema, called query_log_delta select * from information_schema.query_log_delta. Same as querying query log, but it removes all entries from the server side query log 4. irdbQuery supports a query_log, on any local database it loads. 5. Added initial function called LastPrice, which is designed as an enhanced version of LastPrice, and is able to propagate the values forward of the last child, across the dependent ( date) dimensions. Example Query select customerid,year(orderdate) as theyear, month(orderdate) as theMonth, sum(lastprice(freight, orderdate )) as lp , sum(lastchild(freight, orderdate )) as lc from orders group by customerid,year(orderdate),month(orderdate) The order of the dependent dimension in the group by is important, as it uses that to define the propagation order. It generates a cross product, of the dependent, and non dependent dimensions in the group by, propagating the last child values along the dependent dimensions If there are no dependent dimensions in the groupby, it default to lastPrice algorithm. If there are no non-dependent dimensions in the group by, it defaults to the last Price. This is an initial version, and it does not currently do anything about "Opening values" or values that occur before the time scope. THis will come in a later version. 6. Fixed a bug where a Not whereclause containing an AND with 2 different tables ( checking against constants ) could give a wrong result. 23/01/2018 1. Increased number of fields that can be in a join from 8 to 50. 22/01/2018 1. Fixed a bug where columnExists was behaving incorrectly if param1 was using a table alias. 2. Fixed a bug with simpleExec in ImpStringProgram, not returning the InMemoryDatabase db out parameter correctly. 3. Added Expliciy Close of DbDataReaders & Dispose, in core import routine, to help with Release of DB Connections. 4. Made irdb.log logging messages when loading & reloading database more robust, to help debug loading larger databases 5. Fixed a bug in the irdb data provider, so that when you Fill a Dataset with the IrdbDataReader, it doesn't cause a Constraint Exception, if you have string columns with more than 128 chars Previous versions set all ColumnWidth properties to 128. Now if the columnwidth is > 128 it will set it to the actual width. For values < 128, we set the columnwidth property to 128, in the IRDBDataReader MetaData. 08/01/2018 1. Fixed a bug where you couldn't have a SUM (DISTINCT .. and sum ( LASTHCILD .. in the same sql statement. 2. Added a performance optimisation to speed up comparisons involving >=, >, <=, < operators, and DB fields and String constants E.g. theYear >='2016' can now run without an actual string comparison 3. Added a new information_schema called query_log that allows you to see a log of recent queries. It is controlled via the irdb.ini file option called query_log_size By default it is turned off, and has a value of 0. By setting a value>0 it will keep upto that number of queries in the log It currently tracks sql, query start time, exec time, success, error message & whether the cache was used. 4. Modified irdbTest to accept a irdb as input as the fourth parameter. It runs "select query from queries", to get a list of queries to execute. 5. Added new program irdbgz.exe , that can gzip compress and decompress a file. Reduces sizes of files by 50% typically, and is designed to reduce files for transfering to other nodes. irdbgz requires one or two parameters. To compress a file: irdbgz data\northwind.irdb To decompress a file: irdbgz -decompress data\northwind.irdb.gz 6. Fixed a bug so that you can have 2 sum(lastchild(x,y)) expressions in the same SQL statement with an or where filter with 5+ elements. 7. Added functions openbalwithzeroes , closebalwithzeroes to go with openbal & closebal functions. The differense is withzeroes adds a cross product of the balance dimension ( and related dimension ) and the non balance dimensions, to help with things like inventory charts, and having values appear each day. the base balance function still maintain the enhanced functionality to display something even, when there may be values that dont match the general where clause. 8. Fixed a bug with simpleExec in ImpStringProgram, not returning the InMemoryDatabase db out parameter correctly. 24/11/2017 1. Added Server Timeout feature Edit irdb.ini ( restart service) and add the variable client_timeout=xxx, where xxx is an integer, that will specify the timeout in seconds on the server side. 2. Dot Net IPClient Added 2 new static variables defaultReadTimeout and defaultWriteTimeout to IPClient, that specify the respective default timeouts in seconds. There is also a new constructor public IPClient(String host, int port, String username, String password, out bool success, bool enableTimeOut, int readTimeout, int writeTimeout) where you can specify the timeouts directly. 22/11/2017 1. IPClient & irdbcom classes now implement IDisposable, so they can be used with using 2. IRDB_Query now supports running queries that take longer than 30s. By default IRDB_Query uses a connection that doesn't autodisconnect after 30s 20/11/2017 1. Added fix for Query compilation issue in mono. Changed System.Core.Dll -> System.Core.dll & System.Dll -> System.dll 15/11/2017 ( Released - Build date 01/11 ) 1. Fixed a bug in Count/SUM Distinct that was introduced on July 17th. You would see it in count distinct with low cardinalities ( 2-3 ) over many 10,000s of rows 2. Fixed an issue where Count Distinct was calculating twice when used in a having clause 3. Fixed an issue where it may have confused a Count Distinct complex aggregate expression as a Count 4. Balance function is now enhanced to add a cross product of the Non Date dimensions of the data less than the cutoff, with the dates that satisfy the date part of the whereclause, to the balanced data stream. It will now display data for all date elements that satisfy the date part of the whereclause cross with the output non date dimensions. 5. Balance function how an improved algorithm for calculating the cutoff end date, for balance calculating purposes. It removes all non (Balance dimension and connected aliases ) from the query, to calculate the end cut off date I.e. it keeps the balance dimension and connected aliases in the where filter. Also balance can now return data, when no rows, match the initial where criteria. ( This helps display opening balances that might otherwise be filtered out ) 6. Fixed a bug where irdb was doing a Garbage Collect after every import statement, after using the FORCEGC command 28/09/2017 1. Fixed a bug where BOOLEAN Types in CASE statements couldn't be combined in the then and else clause e.g. select case when 1=1 then true else false end 27/09/2017 1. Fixed a bug in Clustered indexes, where they weren't working correctly after the first 27/09 build. 27/09/2017 1. Sorting is now more compatible with other databases. Null values are now ordered before non-null values. E.g. 1,2,NULL,4 would be sorted as 1,2,4,NULL before, but is now sorted as NULL,1,2,4 2. Added new ImpStringProgram class to irdb.import , to make it easier to run IRDB Scripts. The api provides methods for parsing & running irdbImport scripts, and allows several script to be run against the same context. The simpleExec static method providers a one stop method for executing a script. It returns an InMemoryDatabase as an out parameters. It does directly support the save method. BY instantiating the class, you are given more control, like passing a series of String [] values that will be mapped to @param1, @param2,... A Saved file name & directory, An InMemoryDatabase ( This will is available from the ME Context) . Passing a null db, will create a new one. A series of variables in the format of Dictionary There are separate parse and execute methods that can be called in pairs, or a combined parseAndExecute method. BY using this method, you can run some IRDB Script, then do some Dot Net code, then do some IRDBImport SCript. Some example code is available here http://www.interactivereporting.com/irdb/ImpStringProgram.txt You also need to call closeDBConnections against your instance to close any database connections opened by the script in the more complex example. 25/09/2017 1. Fixed a bug where using USEDISK with a zero row result table, in irdbImport, was causing a "Could not combine table" error. 2. Fixed a potential bug where using limit on a zero row table, was returning a table of 1 row. 3. Added support for AVG ( LASTCHILD ( x,y )) 07/09/2017 1. Fixed a bug in irdb where conditions involving Decimals and ( Doubles/ Reals ) were generating Query compilation errors 2. Fixed a bug in irdb where conditions involving nullable Decimals and ( Doubles/ Reals ) were generating Query compilation errors 06/09/2017 1. Fixed a bug in IRDBImport where Alter Table Add column, wasn't binding irdbimport variables like @variable1 into the statement. 05/09/2017 1. Fixed a bug where WITH ROLLUP type queries where generating an error temp tables could not be found, when running under nocache=true in irdb.ini 04/09/2017 1. Enhanced irdbProvider to add support for DBParameters 2. Updated communication protocols to support relevant backend methods for 1. 3. Add Decimal Type to irdbImport 4. IRDBCommand also supports ExecuteScalar & Cancel Method ( which does nothing ) . 03/09/2017 1. Fixed a bug in decimals types causing Cast Exceptions in irdbProvider 2. Added index support to IRDBDataReader. 01/09/2017 1. Added IRDBDataAdapter to IRDBProvider 2. IRDB SQL / IRDBImport now should support most unicode characters for labels 3. IRDB SQL Errors should display the line number and character position, along with a hint of why the error was occurring. 29/08/2017 build 2 1. Incremented Build version 29/08/2017 1. Fixed a bug where UNION ALL type queries were not working through ODBC 2. Added support SELECT TOP N . Treats as LIMIT N 3. Fixed an issue where IRDB_QUERY was defaulting to CASE Sensitive mode, when opening local files. 25/08/2017 1. CSV parser trims column names 2. CSV Parser correctly copies decimal digit length, so the InMemory Database ingests certain types better. 3. Fixed a bug in running WITH ROLLUP queries through ODBC 23/08/2017 1. Fixed a bug where queries run through ODBC wasn't working 2. Added Experimental WITH ROLLUP Support Supports grouping function, ORDER BY, Limit . 3. Added support for Grouped Expressions in complex aggregates where sql like select customerid , customerid + sum(1) from order group by customerid 4. Fixed a case where select cast( null as string) , sum(1) from sometable was not working 16/08/2017 1. Added new Collation feature, so you can run irdb in CASE Sensitive mode. To use this feature you need to A. Add the statement SET COLLATION 'Ordinal' as the first line in the irdbImport script B. Add the following line to irdb.ini ( and restart the irdbServer ) collation=ordinal The currently mixing and maxing Collations will lead to unpredicted results. Another iteration, will apply more checks to help minimize this. Collation is a system wide setting, and an installation can only use one Collation. This feature is currently very experimental 2. Fixed a bug in INTERSECT / EXCEPT where it was ignoring the last column. 3. Small performance improvement in generating code, with large number of columns. 4. Can now use grouping variables in select statements with complex aggregates e.g. select customerid, case when customerid is null then 0 else sum(1) end from orders group by customerid 5. New Count Distinct algortihm of 31/07 is tweaked to remove some unneccessary locking. 31/07/2017 1. Added improved algorithm to use less memory on Count Distinct/Sum Distinct Queries with group cardinality less 500k using one database field. 2. Parts of the columization of data is now done in parallel. This helps speed up 3. Parallel sorting now supports Strings 4. Added new function called COLUMNEXISTS that takes 3 parameters. If the first expression, is an expression, where all the columns exists in the list of tables, the function will results in the second parameter else, the third parameter. If you want to use a Null in the third parameter, please cast the NULL to the appropriate data type. Some examples select sum( columnexists ( orderid, orderid, 0 ) ) from orders select sum( columnexists ( orderid22, orderid22, 0 ) ) from orders select columnexists (orderid , sum(1) , sum(0)) from orders select columnexists (orderid22 , sum(1) , sum(0)) from orders select columnexists ( orderid22, orderid, 0 ) from orders select columnexists (orderid22 , sum(orderid22) , cast ( null as double)) from orders 5. Fixed a bug in the USEDISK feature when the data being imported was less than 1 million records. 28/06/2017 1. New feature in irdbImport that allows you to buffer data to disk while running extracts. IMPORT table=USEDISK a1.table1 UNION ALL a2.table2 IMPORT table=USEDISK FLUSH a1.table1 UNION ALL a2.table2 It is triggered by adding the work USEDISK or USEDISK FLUSH after the = in an import statement. USEDISK will cause it to load the data in chunks of 1,000,000 rows, save to a temp file, then columnize one column at a time to a different file containing an InMemoryTable. This table is then entirely in memory when finished. USEDISK FLUSH does the same except it doesn't load the file in the end. It flushes it out as a single flushed table, that is then consolidated when you call SAVE. Tables will not be available for Querying, updates, or deletes, with the FLUSH variant. This feature does not work with STREAMABLE REMOTE IRDB type datasources. The effect of this feature is you can generate larger extract files using less memory. However extracts will run slower in this mode. 2. Some parts of compiling columns now run in parallel. Could speed up certain operations. 3. Better cleanup of flushed files, if there is a problem while running an irdbImport 4. You can programatically access the functionality in Step 1, using the DiskCombiner class. 5. Fixed a bug in irdbImport where if you deleted rows, the current record pointer could end up on a deleted row. 6. InMemory.Net Documentation improvements. 19/06/2017 1. Added new Faster Writing routines for InMemoryDatabase. In our test instance saving a version of contoso, which had 840MB on Disk when from about 5s to about 1.4s 14/06/2017 1. Time tracking code should now be more robust, and not display large negative numbers in the irdb.log when under load. 2. CSV parser is now using version 3.8 of Lumenworks CSV parser, which seems a bit faster 3. CSV autodetection of datatypes will now import integers with 19-29 digits as decimal rather than double types 4. CSV parsing now is not verbose. Debug info like autodetection of datatypes is now not displayed on console by default. Can be displayed by adding debug=true to the connection string. 5. Added option Bypass Datatype Detection=true to the CSV Connection string syntax, to disable to the autodection routine. 6. Parallelized Detection of datatypes in CSV Parser 7. Added command SET PRESERVE DECIMALS true / SET PRESERVE DECIMALS falso to irdbImport, to preserve columns that are decimals instead of using our default type translation method. 8. Modified import routine to parallel process batches of rows, when importing data, when the import queue starts growing. This can help import some types of data faster, espcially when it contains lots of unique values. YOu will see a "o" in the console instead of a ".", as a progress indicator. 03/06/2017 1. Importing CSV Now pays attention to the SET CULTURE command setting in irdbImport, when parsing dates. 2. Importing CSV now correctly parses Escapes Quotes in Quotes e.g. "Data""s" 31/05/2017 1. Added CSV import support to IRDBImport. See Blog Post https://wordpress.com/post/inmemory.net/887 2. Improved IRDB File loading time by parallelizing Bit Unpacking and doing some column calculations in parallel. On a test of contoso IRDB file size of 6.2s, reading from an SSD, time to load was reduced to 1.48s on a 4 core machine. 3. Removed Unneccessary MAXes involving grouped dimensions in SQL when doing Queries using Balance Functions. This enables IR to use the Balance Calculation 4. Fixed a bug where an ORDER BY in the a Query involing BALANCE functions was not running. 5. Added the following new methods to InMemoryTable to make it easier to Create Tables and Add data programmatically from DOt Net public void addColumnDotNet(string columnName, string datatype) datatype must be one of "System.String" "System.DateTime" "System.Boolean" "System.Int16" "System.Int32": "System.Int64" "System.Single" "System.Float" "System.Byte" "System.Double" "System.Decimal": public void add(List columnNames, List values, out string error) Add a number of columns and values. Columns not given values will be nulled out. If error.length>0 you have a problem public void add(String[] values, out string error) Order of Values must correspond to the InMemoryTable If error.length>0 you have a problem 6. Aggregative Queries that have grouped Cardinality 500k and 4e9, can now run upto 20% faster, and use 5-20% less memory, because Wea re able to use UINT instead of long, for certain Dictionaries 11/05/2017 1. Updated irdbQuery to fix coloring issues on some versions of Windows 2. Fixed a bug in irdbQuery where subsequent queries can display columns based on previous queries. 3. New version of irdbQuery now has an edit menu, with Bookmark functionality. 4. Added Tables_Rows column to information_schema.tables which displays the number of rows in the table. Does not account for deleted rows. 5. Fixed a bug in Sum/Count Distinct with multiple columns, where it was producing incorrect results. Bug was a reduction bug, apparent when consolidating larger data sets. Recommend immediate upgrade to this 6. Fixed a bug in Sum/Count Distinct with multiple columns, where the query was not compiling, when dealing with larger data cardinalities 7. Added a performance to improve execution speed on Sum/Count Distinct, by using a uint instead of long on smaller data cardinalities. 33% performance enhancement. 8. Added command MOVEFIRST to irdbImport. Allows you to reiterate through tables again. 9. Deployed new sorting algorithm that speeds up sorting large tables, with large number of sort columns. The new algorithm kicks in when sort cardinality is > 9e18, and no rows * no columns > 200000. 10. Fixed major bug in Intersect / Except functionality Set operations. 12/04/2017 1. Made a better fix, for NumericPrecision and NumericScale support, that works with Unidata. 2. Made the code that handles NumericPrecision and NumericScale, when analyzing data types a bit more robust. Checked Access,Sql Server. 3. Added ALPHA initial support for SET OPERATIONS INTERSECT and EXCEPT. However performance is still very slow on bigger tables, due to use of BIGINTEGER in sorting routines. Will resolve in future release. 4. Added group17 to group96, but not used yet. Made groupn support IComparable. Not used. Will be used in enhanced sorting routine. 5. Fixed a bug in DateDiff(w ) causing a compilation error 6. Added parsing support for NOT LIKE 7. LastChild now supports expressions in the second parameter, of one database field. 8. Fixed a bug in UNION ALL, if the secondary columns did not have names in NOCACHE Mode E.g. select 1 as salesrep,2017 as [theyear],1 as themonth ,28 as thedate ,100 as amount union all select 1,2017,1,29,200 should now work as an inner query in irdbImport 9 Added beta of sum(lastchild ( qty, dbfield )) e.g. select employeeid, sum(1), sum(lastchild(1,orderdate )) from orders group by employeeid Second parameter should be a DBField. Does not work with more complex expressions atm. 10. Added support for CASE Statement and IIF to irdb 11. Fixed a bug involving date lookups when using the timeshift function. Function was caused by incomplete Date substitution in the join. 12. Fixed a bug in the FORMAT function causing compilation errors when working with nullable numeric data 13. Fixed a bug when an inner query was using DISTINCT, and irdb was running in NOCACHE mode or inside irdbimport 04/02/2017 1. Fixed a bug in irdbImport, when appending data, that it was using the underlying table, and renaming in part of the append process in usetemp. The result was that, this table gained a new name, and as a result any dependant foreign indexes keys were not being dropped correctly. It now uses a cloned shallow copy. 2. Like now supports numeric values as long as the RHS is a string constant and contains a '%'. If the RHS side doesn't contain a % and the LHS is numeric it will not work. 3. Added support for POW as an alias for POWER, to increase SQL Server compatibility 4. Fixed the following bug where expressions of aggregates in UNION all could cause inaccurate results SELECT Sum( 1) + sum ( 1) as exp1, 0.0 as exp2, 'source1' as source FROM orders Union ALL SELECT 0.0 AS exp1, SUM( 1.0) as exp2, 'source2' as source FROM orders 5. Added support for uncolumnized to IMPORT command in irdbIMport, so that tables can remain in uncolumnized form. This will speed up operations, where you want to append large numbers of smaller tables, without having to columnize and uncolumnize each time. e.g. import t1 = uncolumnized a1.orders import t1 = uncolumnized a1.orders columnize t1 6. Added varexists function to irdbImport. E.g. varexists ( @var1 ) will return true, if @var1 is declared, otherwise false. any non variable expressions will also return false. 7. Tweaked Random function to be more thread safe. To try and fix the following bug. Random started returning a constant or 0 zero after running, and stopped generated random results. 8. Added initial beta version of new TimeShift Function. Needs to be used in Combination with sum. Timeshift function, will generate a metric, summing term1, with database field in column 2 substituted by the expression in column 3. The timeshift part is run as a separate subquery and then combined with the rest of the query E.g. YOY Analysis. select year( orderdate) , sum(1) , sum( timeshift ( 1, orderdate,orderdate+365) ) from orders where orderdate>='2015-01-01' and orderdate<='2015-12-31' group by year(orderdate) 9. Fixed a potential issue in irdbImport, where certain run time sql errors generated after reading some rows of data, were not causing a stop in irdbImport This was potentially caused by running invalid casts on certain columns, e.g. Invalid Strings to Dates, that would fail occasionally with unclean data. The previous behaviour was the program would stop importing from that table, and continue on. 02/12/2016 1. Dynamic tables will now show up in table tree in irdbQuery, like a regular table, when they are enabled in irdbServer Will also appear in appropriate information_schema. 2. Fixed a bug where rowcounter(1) in the where clause was causing a query compilation error 3. Added support for PARTITION WITHOUT to over clause. Essentially it takes the group by clause, and makes it the partition clause, and then removes certain columns that you specify. Format is PARTITION WITHOUT LAST PARTITION WITHOUT FIRST PARTITION WITHOUT Comma Separated list of expressions. E.g. Over ( partition without last ) It will ignore columns it the without clause that are not contained within the query. e.g. select employeeid,orderid, sum ( sum ( orderid )) over ( partition without orderid) from orders group by employeeid,orderid select employeeid,orderid, sum ( sum ( orderid )) over ( partition without LAST) from orders group by employeeid,orderid 4. Changed progress in irdbImport feedback to give a carriage return every 7900000 rows 5. Issue carriage return after displaying some dots 6. Query Cache in database is emptied if an update or delete statement is issued 7. When an update statement is issued, all temp columns in a table are dropped. These temp columns are used in joins, and could be inconsistent because of the update 8. When an update statement is issued, dependant summary tables used in grouping calculations are dropped, because they could now be inconsistent 9. irdbImport now sets NOCACHE query as its default behaviour so inner queries are not materialized into temp tables that could then become inconsistent There is still a possibility that a temp table created for an inner query, could be inconsistent in irServer after an update/delete, if its underlying data changes. For accurate results you will need to set nocache=true in irdb.ini , when using update/deletes with inner queries 10. When Cloning columns, it now creates a shallow copy rather than an entire full deep copy of the column. This can help reduce memory usage by not creating so many copies 11 Fixed a bug, where some query optimisations were not running, and using the regular engine for execution Select discount column1 from table, Select discount column1 , column1 from table, select count ( distinct column1 ) from table 12. Added a new optimisation that bypasses execution of a query for simple select statements from 1 table, involving just database columns, and constant expressions E.g. Select field1,field2,1+1,0 from table will now run very quickly, because it does not need to do a bunch of processing. field1, and field2 will be shallow copies of original table, and a 1 row query will be executed to evaluate the constant expressions. E.g. queries like select * from table should now return very quickly. This will help reduce memory usage. 13. Day/Month/Year precalculations on dates are only stored when a day/month/year function is called for the first time. 14. Fixed a bug in constant columns, where the null value indicator could be setup incorrectly 15. Refactored to reduce duplicated code in ImportCommand, when importing data in irdbImport. Potential to introduce bugs 16. Added support for a REMOTE STREAMABLE INMEMORYDB, in irdbimport, to help reduce memory consumption. When using this type of datasource, and doing an append in UNION ALL in an Import command, it will stream the records back in chunks of 1,000 rows. This should help reduce memory usage, in cases where memory is tight, and you are doing an incremental update, and are using the copy on the server as the base reference. e.g. datasource a1=remote streamable irdb 'irdb=contoso;pwd=!TopSecret' import FactOnlineSales = me.somesmalltable union all a1.FactOnlineSales SAVE 17 Added currentDir() as a function to irdbImport. Potentially useful, when you need the current directory in a irdbImport script 18. Fixed a bug when you had 3 or expressions in a where statement, and they involved expressions on the lhs. Simplification optimization code, was failing in this case. e.g. select * from test1 where (isnull(col1,'') <> '' or isnull(col1,'') <> '' or isnull(col1,'') <> '' or isnull(col1,'') <> '') and col1 like 'abcdef' 20 Changed the precedence order for *,/ . When put into expressions next to each other they now evaluate from left to right 1.0/2*2 will now evaluate as 1, where as before it evaluated as 0.25 This mirrors the behaviour found in Excel , some other database servers like SQL Server 21. Left,Right, Contains now support a numeric first parameter 22. Like now supports numeric values as long as the RHS contains a '%'. If the RHS side doesn't contain a % and the LHS is numeric it will not work. This fixes an issue with the search popup box in IR giving a sql error 26/10/2016 1. Added a caching layer to remember the result of getEstimatedSize() in InMemoryDatabase for 60s, to reduce cpu usage, if it is called alot. 2. Added support for Count Distinct & Sum Distinct with multiple columns. First column needs to be numeric, for Sum Distinct. Currently Count Distinct & Sum Distinct when working with multiple columns, only work complete columns. select sum ( distinct a,b ) from orders , a has to be numeric 3. Added support for Asc 128-154,160-165 as allowed characters in columnnames in irdb sql & irdbImport 4. Added Save Table command to irdbImport. This commands saves a single table as a database Example import a1 = me.{select 1 as column1 union all select 2 as column2 } save table 'a1' to 'c:\irdb\data\test_savetable1.irdb' 5. Added cmdExec to irdbImport. It allows you to run external programs. It takes 2 parameters. Parameter 1 is the name of the Exe. Parameter 2 is the list of command line parameters for the exe Example cmdexec 'c:\irdb\irdbimport32.exe' 'c:\irdb\data\northwind.imp' 6. Fixed a bug in the listDetailedDatabases, where it was not distinguisihing between LOADING and RELOADING. Databases that are now reloading, now have the reloading status. 7. typing dbstats as a standlone word in irdb_query, will run listDatabasesDetailed, as a table, allowing you to see detailed state on the server 8. Fixed a bug in the listDetailedDatabases, where it was having trouble if the data_dir ended with a \ 9. Added new SAVE AS command, to allow you to give a file name when saving a database. save as 'c:\irdb\data\test_savetable1.irdb' 10. Added CSV option to SAVE TABLE e.g. save table 'a1' to csv 'c:\irdb\data\test_savetable1.csv' 11. Added support for UNION select * from table1 union select * from table2 If a list of queries contains at least one UNION, then it will treat it as a UNION else UNION ALL 12. Added support for expressions of aggregates of SUM with over clause e.g. select employeeid , sum(1) , 100 + sum(sum( 1) ) over ( order by employeeid ) from orders group by employeeid 13. Fixed a bug in the trunc function not taking nullable dates. 14. Updated irdb_Query, to support multiple tabs. 15. irdb_Query, can now save & open sql files 16. irdb_Query, now uses syntax highlighting 17. irdb_Query, now has buttons to comment/uncomment out a bunch of text 18. You can now filter the databases on top, of the database tree in irdb_query 19. irdbImport now supports [@@variableName].exists , to dynamically see if a table exists. Also works with columnNames e.g. [@@variableName].orderid 20. Most irdbImport is now under irdb.import . This will mean any code referencing irdbimport programmatically will need to be updated. 21. Added new Dynamic Table Feature Example Function ( Saved in separate .imp file ) Line 1 must be a create table statement, that is run to setup the schema information Return returns a string containing the name of a temp table , that contains the results genTemp() will generate a random string that get be used a temp table name 3 special params. @where -> filtered where clause @columns -> comma separated list of columns the parent query actually uses @parent_query -> the entire parent query Dynamic Tables must start with dyntable_ Once setup a dynamic table can be queried like other tables. create table return_format ( orderid int,orderdate datetime ) declare @tempTable as string set @tempTable= genTemp() import [@@tempTable] = me.{select orderid , orderdate from orders } print 'where=' + @where print 'parent_query=' + @parent_query print 'columns=' + @columns return @tempTable Example setup datasource a1=local irdb 'c:\irdb\data\northwind.irdb' import orders= a1.orders create dynamic table dyntable_Orders = 'c:\irdb\data\dynamictable.imp' save 22. By default dynamic tables are disabled in irdbServer, but are enabled in irdbImport You can enable dynamic_tables by adding dynamic_tables=true in irdb.ini 18/09/2016 1. Added support for case when a=1 then A else null end. It will now cast the null to the appropriate type 2. Fixed a bug where string expressions (E.g. A+B is NULL ) was evaluating incorrectly 3. Fixed a bug where date expressions (E.g. date+1 is NULL ) was not evaluating 4. Fixed a bug where it could generate the wrong type when combining different data types in Case expressions. 5. SUM aggregate function no longer default to 0. if it sums a column that is all null it will return null. 6. Fixed a small bug in Count Distinct 7. Fixed a bug where select sum(ColumnWithNulls/2.0 ) was generating an exception. 8. Updated select * from information_schema.updated to return the following 6 fields about the loaded database and current database in the filesystem last_loaded,loaded_file_created,loaded_file_size,estimated_memory_usage,current_file_size,current_file_created 9. Fixed a bug where queries like select null from orders, could not be serialized. By default now, if a type is not explicitly to a null, it will save as an int. 10. Fixed a bug in update, where it was possible to change the datatype in an update statement on one table, that updated all rows e.g. update orders set orderid = orderid+0.5 Added a check to make sure datatypes on column match before applying simple algorithm. Slower complex, used when datatypes are different. 11. Added new api method listDatabasesDetailed(), that returns an array of all the databases and detailed information on them. The stats in the structure are as follows public class DBStats { public string dbName; public LoadStatus loadStatus; public long estimatedSize=0; /* Estimate Memory Usage */ public DateTime loadedTime=default(DateTime); /* UTC time file was loaded */ public DateTime loadedFileCreated = default(DateTime); /* Last modified date time of file when last loaded. */ public long loadedFileSize = 0; /* Size on disk of file, when it was loaded */ public DateTime currentFileCreated = default(DateTime); /* Current modified date time of file current in disk */ public long currentFileSize = 0; /* Current File size on disk */ } Sample code to use public static void test267() { irdbcom client = new irdbcom(); client.remoteConnect("database=northwind;pwd=!TopSecret"); DBStats[] dbstats = client.remoteListDatabasesDetailed(); foreach ( DBStats stats in dbstats) { Console.Write(stats.dbName + ", size=" + stats.estimatedSize + " , loaded=" + stats.loadedTime + " ,loadedCreated=" + stats.loadedFileCreated); Console.WriteLine(" loadedSize" + stats.loadedFileSize + " currentDate=" + stats.currentFileCreated + " currentSize=" + stats.currentFileSize); } } 12. Can now declare long variables in irdbImport 14. (BETA) REMOTE IRDB now works as a datasource in irdbImport e.g. datasource a1 = remote irdb 'irdb=northwind;pwd=whatever' This allows you to use other InMemory.Net Servers as a datasource directly in irdbImport 15. Fixed a stack overflow bug where substition in expressions involving the first param of 3 parameter functions, doing a group substitution. 16. Fixed a bug where select YEAR([OrderDate]), DATESERIAL(YEAR([OrderDate]), MONTH([OrderDate]), 1) From [Combined_Sales] GROUP BY YEAR([OrderDate]), DATESERIAL(YEAR([OrderDate]), MONTH([OrderDate]), 1) Was causing an error. It relates to grouping stitution on first column into second expression, creating a grouped var with 2 variables. 05/09/2016 1. Added new getEstimatedSize() method to InMemoryDatabase, InMemoryTable & InMemoryColumn 2. Improved Null support for Non String Datatypes. In general you should be able to perform functions and arithmetic on date and numeric types and get the correct output Before 2+ null would be equivalent to 2+0 = 2, will now give the correct null result (Null DB Field +2 ) is Null will now evaluate to null 3. You should use an explicit cast with null , e.g. cast ( null as int ) when using null, for the time. cast ( null as type ) should now work in general a method to specify a null of a particular type Specifying null as a general parameter directly in all functions may not work in general 4. When using select null in columns in UNION ALL, the system can now infer a cast to use, so select null as col1 union all select 1, will now evaluate correctly If all columns in a null column are set to null it may not work. 5. Count Distinct now works correctly on expressions involving nullables 6. API Change. IsLoading now returns a RELOADING Status, when the database is Reloading. 7. Non Safe Cast, Cast functions like Cast ( xx as int ) dont return a nullable type for non null string constants. This reduces a few instances, where nullable types can come into the system. E.g. Cdate ( '2010-10-10') does not create a nullable type now. 8. OR and And can now work as 2 parameter functions 9. For each column in each table, it tracks whether that columns contains nulls or not. This is not persisted, but regenerated when loading. Ideally it should be persisted, but this would cause a change in the file format. 10. Fixed a bug where select orderid, orderid2 as test1 from orders left join ( select 10248 as orderid2 ) as od on orders.orderid=od.orderid2 was generating repeated 10248 in test1 11. Whereclause + logic should be able to handle nulls correctly. Before the system would match null strings to null strings. However full tristate logic is not implemented in the whereclause A predicate can only be True or False. Currently we don't handle null predicates, although the boolean value type can handle nulls. 12. Added support for min & max to work with over clause 13. Improved support for min & max function to work better with database fields that may contain null data. 14. Fix for irdbQuery messing up column order when running queries that share similar column names. 20/07/2016 1. Updated Documentation 2. Added FileExists function to irdbImport, to detect if a particular file exists. 05/07/2016 1. IRDB now displays the correct error message in the case, when it can't find the Partition BY or Order By variable, from an OVER clause, in the select list 2. IRDB should now handle the case where you repeat a column in both the PArtition By and Order By from an Over Clause 04/07/2016 1. Added support for ROW_NUMBER(), RANK() & DENSE_RANK functions for use with the OVER Clause 2. SUM ... OVer by, now displays a sum over the entire partition, rather than a cumulative sum 3. To do a cumulative SUM when using over use the following SUM ( XYZ ) over ( Partition by partitionList Order by OrderList ROWS UNBOUNDED PRECEDING 4. Added support for the command IMPORTTIMEOUT to irdbImport e.g. IMPORTTIMEOUT 1800 will set the timeout to 1800s It is the time, irdbImport will wait to get rows back from a query. default value is 900s 5. Fixed a bug where 'Q' + Quarter ( someDate) was giving a compilation error. Fixed a bunch of similar errors as well. 6. Added new feature to do count disinct on generic expressions. No longer limited to just doing a database field. 7. irdbQuery now supports specifying a port number a non standard port is entered as servername:port in the servername box. The port is then parsed out of there 8. IRDB Sql now supports putting a condition in with Count DISTINCT Expressions. Add the keyword WHERE after the database field, and then put in the condition e.g. select employeeid, count (distinct customerid where employeeid=1 or employeeid=4) from orders group by employeeid 9. IRDBTest, now reuses the Connection, rather than recreating a socket for each query. 10. there is a new option in irdb.ini called logall. Setting LOGALL=false will reduce the verbosity of the amount logged to the SQL. No Timing, or sql, or other blurb. This helps performance when running large numbers of queries. 15/06/2016 1. irdbImport string constants now support escaped quotes e.g. '' 2. Added Try statement to irdbImport. This can be used to catch a bunch of errors, and continue execution. Try Some Steps End Try Catch // Some Steps to do when an error End Catch 3. When joining a single dimension, you can now join any numeric type against any other numeric type. E.g int -> double. Decimal->long 4. ALL irdb functions show now work directly in irdbImport. ( 40+ New functions ) 5. Fixed a bug where it was not displaying error messages correctly in irdbImport with a function of 2 params. 6. Added grouping optimization to remove redundant group by fields from the same table, if the group by also contains a unique column, from the same table This helps reduce group cardinality which can help improve performance. 7. Added new irdb.ini option called querycache , querycache_timeout querycache=true will enable query caching on irdbServer. This can help performance, to stop irdbServer, rerunning the same queries again and again. Default time to keep something in the cache is 10 minutes querycache_timeout=20 can be used in irdb.ini to change the default timeout, e.g. to 20 minutes. Currently cache using Microsoft MemoryCache object, so this may release objects with its own contraints. Tables with more than 10 million rows or where rows * columns > 100 million will also not be cached. 07/06/2016 1. Alter Table now works with tables that contain deleted records. 2 ALTER TABLE command in irdbImport now support @@variable substitution. 3. COLUMNIZE in capitals should work 4. Moving the Tmp file to the final .irdb should now be more robust. It checks to see if the file is deleted, and go goes into a polling mode every 50ms for upto 30s, before moving 5. Fixed . select distinct orderid, invalidColumnName from orders Should now generate a correct error message. Fixed a bunch of related errors, where the error was displaying incorrectly 6. Fixed. select orders.customerid from orders inner join invalidTable on orders.orderid=invalidTable.orderid2 will now display a correct error message saying table invalidTable is not present 7. Note the error checking should be more robust. However some queries like the following select orders.customerid from orders inner join orders2 on orders.orderid=orders2.orderid where orderid=10248 Will now generate an error about orderid being an ambiguous column, where it would have worked before. 8. Joins involving multiple columns, and large number of unique values should generate the initial index faster The initial merge part is now up to 10 times faster. 9. Joins involving mutlitple columns now support joining bytes,shorts and ints against each other. Internally all 3 are represented as ints from the joins point of view Does NOT support joining int & longs in this case Performance tip 12 * YEAR(DATE) + MONTH ( DATE) = 12 * YEAR(dATE2) + mONTH(DATE2) will perform faster than Year(date) = year(date2) and month(date) = month(date2) 10. CAST now supports INT & LONG in irdb sql and in create table in irdbImport. 11. COM interface now returns nulls 12. Fixed a bug where an uncaught ioexception could be generated in irdbImport. when the filesystem ran out of disk space 13 . Fixed a bug involving joins of 3 or more fields, where the joining was case sensitive. This could cause problems when joining lower case with upper case data 14. When creating joins involving 3 or more fields, it can do some sorting in parallel, speeding up this index creation a small bit 15. Now uses ByteArray class for byte [] . Some code to access might look like case DataTypes.BYTE_ARRAY: { ByteArray bytes = ((InMemoryColumn)column).getValue(row); dataRow[columnNames[j]] = bytes==null?null:bytes.bytes; break; } 16. Fixed a bug where it was displaying a misleading error in the case Select sum(ASD) from table where asd was not in the table. It now displays the correct error message saying asd in not there. 17. Fixed a problem with irdbload reloading giving a failed error, even though the file had loaded okay. 18. Fixed an issue with irdbload when a corrupted file being reloaded would give a file loaded messgae, when it actually failed. 19. Flush command now uses random file chunks 20. Opening database method is now more robust 21. Save command now uses a random file chunk in the temp file name. 22. Made different properties of irdbImport Commands and ImpProgram public, so the structure used by the parser is more reusable 23. Right Curly brackets in embedded sql can now be escaped by adding an extra right curly brackets. E.g. }} -> } Import test1= me.{select '}}' as testfield } 24. Updated FLUSH command in irdbImport to take an optional parameter. If a parameter is included, it will flush only that table 25. Updated the saving process of the in memory database. It will now save to a .tmp file, before overwriting existing. 26 irdbImport now displays the underlying SQL error when it runs into an import issue 27. maxcpus defaults to half system cpus when number of cpus > 8 28. Fixed a roslyn related bug that effected trying to run irdb on a pure dot net 4.0 version 29. irdbImport should now display a column number, when it runs into an issue with a union statement in an import and incompatible types. 17/05/2016 1. Fixed a roslyn related bug that effected trying to run IRDB on a pure dot net 4.0 version. 2. Fixed some bugs when doing a join with 2 or more fields, and those fields are expressions 3. Added Create Table command to irdbImport Create Table creates a table in uncolumnized form, where data can be inserted 4. Added INSERT INto command to irdbImport Insert works on tables that are uncolumnized 5. Added COLUMNIZE command to irdbImport This colunmizes uncolumnized tables, so you can execute SQL and other import commands against them. Tables that are not columized, will be columnized by a flush or save command. Example create table test1 ( str1 string , int1 int , long1 long ,date1 datetime ) insert into test1 ( str1,int1 ) values ('aaaa',1) insert into test1 ( str1,int1 ) values ('aabb',2) insert into test1 ( str1,int1 ) values ('aabbc',3) insert into test1 ( int1 ) values (3) insert into test1 ( long1 ) values (3) insert into test1 ( str1) values ('dddd') declare @strVar as string set @strVar= '100' insert into test1( str1 ) values ( @strVar ) insert into test1( str1 ) values ( @strVar + '1000' ) columnize test1 11/05/2016 1. Added support for Slurp in IRDBImport for Mysql Dot Net Data Provider 2. When using the dot net data provider for mysql you will need a connection string like Server=localhost;Database=DBName;Uid=root;Pwd=yourpassword;Convert Zero Datetime=True;Allow Zero Datetime=True When connecting from IRDBIMport and if you want to you the MySql Dot net Connector load assembly 'mysql.data.dll' datasource a1 = DOTNET CONNECTION 'MySql.Data.MySqlClient.MySqlConnection' 'Server=localhost;Database=DBName;Uid=root;Pwd=yourpassword;Convert Zero Datetime=True;Allow Zero Datetime=True' 11/05/2016 1. Added support for Slurp in IRDBImport for Mysql - Handles spaces in tablenames 2. Import data from mysql should work with the Mysql Dot Net Data provider. 09/05/2016 1. Slurp will now append if a table already exists in irdbImport 04/05/2016 1. Safecast now uses InvariantCulture by default for numbers and CurrentCulture for dates; 2. Added support for SET CULTURE command to irdbImport e.g. import date1 = me.{select safecast ('09/01/2010' as datetime ) as col1} print date1.col1 set culture 'en-US' import date2 = me.{select safecast ('09/01/2010' as datetime ) as col1} print date2.col1 3. Added a bug fix for when using inmemory.net against the odbc driver. Row 2500 was being duplicated as Row 2501 and the last row omitted if the resultset was more than 2500 rows. 28/04/2016 1. Added new cancel method to impProgram in irdbImport e.g. program.cancel(); will cancel a program. CancellationToken & CancellationTokenSource are available through public properties. You should set or get them directly both, though the public method, if you need to manipulate at that level In irdbImport, once one step has a program, it will issue a cancellation request, that should stop the other steps. Related Import apis support cancellation of the task. 2. Improved IO routines, for reading * writing the arrays of ints,doubles,longs, floats that are part of the value lookups. Essentially now use Buffer.Blockcopy to convert arrays to a byte array, that can be written in bulk rather than writing element, by element. 3. Added support for select * from information_schema.tables 4. Added support for select * from information_schema.columns 5. Added support for import [@@varname] = erp.[@@varname] type syntax. 6. Added support for SAFECAST FUNCTION. ( NOTE IT REQUIRES AS ) e.g, with the 10 major dataypes supported by irdb STRING -> SAFECAST ( XXX as text ) DATETIME -> SAFECAST(XXX as datetime ) BOOL -> SAFECAST(XXX as bit ) BYTE-> SAFECAST(XXX as TINYINT ) SHORT-> SAFECAST(XXX as SMALLINT ) INT-> SAFECAST(XXX as INTEGER ) LONG-> SAFECAST(XXX as BIGINT ) real-> SAFECAST(XXX as real ) double-> SAFECAST(XXX as double ) decimal-> SAFECAST(XXX as decimal ) 7. Added support for putting expressions into INNER & LEFT JOINS e.g. select orders.* from orders inner join [order details] on orders.orderid+'1' + orders.orderid =[order details].orderid +'1' + [order details].orderid Note this feature only currently works with tables. It does NOT work with Subqueries. 8. Convert numerator in avg calculation to a double ( with an explicit cast) to avoid divide by zero errors You may see NaN in some cases now. 9. Added Roslyn Compiler Support The following files are now required to be available for Roslyn: RosCompiler.dll Microsoft.CodeAnalysis.dll Microsoft.CodeAnalysis.CSharp.dll System.Collections.Immutable.dll System.Reflection.Metadata.dll In case of running irdbImport on a .NET FW 4.0 machine a downgrade to non-Roslyn (CSC) will happen automatically When embedding into other products the Roslyn SetupCompiler function should be executed. This helps reduce query compilation time. Dot Net 4.5 is required for Roslyn. 10. Added While Loop to irdbImport Example declare @i as int set @i=1 while @i<=10 print @i set @i=@i+1 loop 3. Added For Loop to irdbImport Example declare @i as int for @i=1 to 10+1 print 'FOR ' + @i next 11. Added >=, >, <, <=, <> , != as predicates for comparing things in irdbImport script e.g. while @i<=10 12. Added support for embedded sql as part of variable expressions. e.g declare @count as int set @count = {select sum(1) from orders } print @count Must return at least one row. Only first row, column considered. 13. If you run an Import command, and data already exists, it will do an append of the data, in irdbImport Effectively it will do a usetemp operation, with the old table, and any new tables, that are part of the union all Warning potential Triangular O ( n^2) performance issues, when appending large number of tables. 18. Added MoveNext command. This moves the current record pointer forward one record. Used as part of iterating through data 19. Added tablename.Eof property that can be used an expression to see if you are at the EOF 20. Added tableName.Fieldname variables to irdbImport. This returns the fieldname value from the tablename. e.g. while orders.eof=false print orders.orderid + ' ' + orders.employeeid movenext orders loop 21. Added support for true/false bool constant in irdbImport 22. Added support for tableName.exists property e.g. if Orders.exists=false then ... 23. Added Support for WITH CTE TYype expressions. with myCustomTable as ( select 1 as theLevel union all recursive select theLevel+1 as theLevel from myCustomTable where theLevel<10 ) You need to explicily name all columns in each sub query in the union , and use the keyword RECURSIVE before any recursive parts. 7th April 2016 1. New build that fixes some problems with the new import algorithm 2. Use a dictionary rather than list to build list of unique constants - Improves Performance 3. irdbQuery typo Datbase is now Database 4. irdb_Query - You can select a subsection of the query, and execute that by itself 5. irdb_query - Double clicking on a table, will generate a simple select * from tablename when there is no text in the text area 6. Fixed a bug with cross join agg. queries giving invalid results 7. Added sum ( xyz) over ( partitiion by xyz order by xyz ) support Currently does a Cum Sum. will fix in later build. 8. Special new functions for calculating Open & Close balances e.g. SUM ( OPENBAL ( Amount, datefield )) or SUM ( CLOSEBAL ( AMOUNT,datefield)) This kind of metrics will do cumulative sums involving groupby dimensions involving datefield, or a table joined with datefield. 9. Fixed the parser so expressions like case when a=1 then 'a' else 'b' end + 'cc' will parse 10. Can now escape " in "string""s" like that ( reported by Jan ) 11. Can now escape ] in [string]]s] like that. ( reported by Jan ) 12. Now gives correct error messages for functions involving 2 & 3 parameters with invalid params. 13. fixed an example where like '%a%%m' was not parsing correctly 14. Added new command called FLUSH to irdbImport This writes all the tables imported to far to disk, clears all table from RAM, Does an LOH GarbageCollect. Flushed Tables, are appended to the end then as part of the SAVE Command Supports multiple flush commands. This helps reduce memory consumption when importing large numbers of tables, and the tables dont depend on each other 15. Added FORCEGC rowNumberInt command to irdbImport - forces garbage collection after importing rowNumberInt rows, and help reduce memory usage and fragmentation 16. Fixed a bug with the odbc driver, where it was not keeping correct reference on the number of opened/closed connections, and hence it was possible for Winsock to end up uninitialised. Mar 17th 2016 1. Fixed a few bugs in the new Import Algorithm introduced on the 16th of March 2. When a query has a large list of constants, it should be able to generate the list of unique constants faster. New algorithm is constant time versus O (N^2) of the previous. Mar 16th 2016 1. Added new Close command to close a datasource to irdbImport e.g. Close a1 2. Added new Rename command to rename a table in irdbImport e.g. rename oldName newName 3. Added new update sql functionality, designed for batch updating certain columns in irdbImport e.g. inside an irdbImport script. Embedded in irdbImport by curly brackets. {Update table1 set column1 = somevalue wherea customer='abc' } Curly brackets can also be for other sql commands like Delete 4. Import a table from another IRDB Datasource should be faster. It makes a clone, rather than recalculation 5. New algorithm that reduces memory consumption during import, when columns have high value cardinality relative to the number of rows. 6. Bug fix for decimal/double filters, when running in locales which dont use a dot as the decimal point 7. Long list of OR nodes are now stored as a List, instead of recursively. This reduces stack overflow issues with long list of nodes. 8. Long List of OR Nodes should now generate query faster as it uses a stringbuffer instead of string. 9. Long lists of OR expressions of the type DBField = Constant are now rewritten to tempTable containing the constant list and a in subQuery. This should help performance with long list of nodes. 10. Select * from intDBField = 10248.5 should now return correct Results 11. irdbImport import subqueries now support using the irdbImport variable inside sql statements by dynamically substituing the variable name with an extra @ in front r.g. import someTable=a1.{select * from @@myVarName } 12. Added a fix for problems with decimal numbers in locales with a , decimal separator. Feb 18th 2016 1. Fixed an issue when listing the metadata on a table, after it had been in a query with a multi column join 2. Added new API methods to IPClient prepareStatement Returns InMemoryTable in MetaDataFormat Returns a statement handle name, and the expected format, no of parameters dropstatement Drops the statement createCursor Returns InMemoryTable in MetaDataFormat Creates a Cursor with an SQL Statement fetchFromCursor Fetch a limited number of rows of data from a server side cursor closeCursor Closes the Cursor statementExecute Executes a statement and return the results in 1 InmemoryTable. Query Parameters are a list of strings statementCreateCursor Returns InMemoryTable in MetaDataFormat Executes a statement and return the results using a Server Side Cursor. Query Parameters are a list of strings 3. Improved performance when creating the index when joining on 2 columns, when rows > 10000000 and Group cardinality is less than 1 billion It uses a bitmap in parallel, rather than a dictionary. Feb 2nd 2016 1. Improved performance when you have an Inner Join between 2 tables that is specified in the whereclause 2. Fixed a server side exception that was caused by the query select * from sometable where 1=0 limit 10,10 3. Added an experimental feature to IRDBImport, to allow parallel imports DO PARALLEL List of Import steps END PARALLEL 4. Added better handling of parenthesis in Join conditions when dealing with multiple fields 5. Added an optimisation to ignore grouping by constants 6. Added the ability to add a new column to an existing table in IRDBImport alter table orders add column monthdate = { month(orderdate) } Jan 17th 2016 1. Added an optimisation that when you have conditions like DBField = CONSTANT for non strings, it will do a search using value indexes, rather than evaluation the value explicitly This Can speed up SQL with large numbers of conditions in the where c5. lause 2. Added an optimisation to speed up grouping calculation where the group cardinality > 2^64 3. Added an optimisation to remove unneccessary expressions involving one DBFIELD from the group by clause, if you are grouping by the DBFIeld anyway e.g. select date,year(date),sum(1) from sales group by date , year(date) will be simplified to select date,year(date),sum(1) from sales group by date Jan 14th 2016 1. Updated User Manual 2. Fixed a bug in the odbc drivers where Null Strings where being returned as blank. Jan 10th 2016 1. Fixed a bug with select count( fieldName) from tablename not working via ODBC 2. Fixed a bug with select avg( fieldName) from tablename not working via ODBC. 3. Fixed a bug with subselect queries not working via odbc 4. Fixed a bug with columns who had there alias set via AS bring labelled as ExprNN via odbc 5. Modified getTableNames() in InMemoryDatabase to not return tempTables. Internal Temp Tables should be hidden now. Jan 7th 2016 1. Updated 32 bit and 64 bit odbc drivers to fix a bug, that could results when there was between 128-254 unique values in an outputed column Dec 30th 2015 1. Fixed a bug that could effect results of joins that involved 3 or more fields in the join Nov 10th 2015 1. Fixed a bug when saving an Inmemory Database, where some of the columns in the last row could become corrupted, if their value cardinality was greater than 65536 2. Added a feature to remove temp tables generated by subselects, after 30 minutes if they are not used This time can be controlled via the unload_temptables variable in irdb.ini Setting a value of 0 here, will not cause any temp tables to be unloaded. This is designed to reduce garbage collection from these tamp tables 3. Reversed Enhancement 2 from Oct 26th. 4. Added a CACHE keyword that does the opposite of NOCACHE for subselects. This causes the subselect to be cached, even if nocache=true in the irdb.ini file Nov 4th 2015 1. Reversed Enhancement 2 from Oct 26th. Nov 3rd 2015 1. Fixed a bug in the parallel sorting routine ( Kicks in after 1,000,000 rows ).It was caused because a heuristic assumed a smaller task would finish first when it doesn't necessarily 2. Added a new option called LogServer to InMemoryDBHost. Setting to true, will start rendering sql statements to console Oct 26th 2015 1. Added a new enhancement, to detect which columns are constant in an sql statement, and hence to process them more efficiently 2. Added an enhancement to try and reduce memory usage during the execution of a query, on many cores. Memory is now generally allocated, once a core is actually used during parallel execution. 3. When loading Databases from Disks, containing clustered indexes it no longer resorts the table on the clustered indexes, assuming it has been done when the db was saved. This speeds up loading databases with clustered indexes, as it will no longer do a bunch of unneccessary sorting. 4. Added a caching layer to InMemoryDBHos. Off by default. Enable as follows InMemoryDBHost.QueryCacheCount = 2; // No of times a query is executed before it is added to cache. 0 Means dont cache. InMemoryDBHost.QueryCacheTimeOut= 15; // How long to keep the result in the cache. Will turn on in IRDBServer in a future build. Oct 23rd 2015 1. Fixed a bug in clustered indexes when the column datatype was long, and it was comparing it against a integer constant Oct 20th 2015 1. Added new Aggregative Statistical functions. VAR,VARP, STDEV,STDEVP . VARIANCE, VARIANCE_P, STDDEV,STDDEV_P are alliases of the above. Oct 19th 2015. 1. Atan2 function now takes 2 params 2. Remove function does not now generate an exception if the length + position move it beyond the length of the current string Oct 18th 2015 1. Added the following New SQL Functions - Substring(someString, startPos) - Ascii(someChar) - Returns the ASCII code for a given char - or the first char, in case of a char array (string) - Char(number) - Returns a char from an ASCII code - Reverse(someString) - Reverse character positions of the given string - Insert(baseString, startPos, strToInsert) - Insert strToInsert into baseString at startPos - CharIndex(someString, innerString) - Returns the position (1-based) of the first ocurrence of innerString in someString. If not found, return zero. - CharIndex(someString, innerString, seekStartPos) - Returns the position (1-based) of the first ocurrence of innerString (starting from seekStartPos position) in someString. If not found, return zero. - Remove(someString, startPos) - Removes all characters in someString from startPos on. - Remove(someString, startPos, length) - Removes characters in someString from startPos delimited by length. - Srqt(number) - Returns the square root for the given number. - [Sin | Cos | Tan | Sinh | Cosh | Tanh | Asin | ACos | ATan | ATan2] (number) - Returns, respectively, the Sine, Cosine, Tangent (+ respective Hyperbolic versions and others...) of a number representing an angle in radians. - Rand() - Returns a random double that will vary from 0 to 1. - Log(number) - Returns the natural logarithm of a number. - Log(number, base) - Returns the logarithm of a number for the given base. - Pow(number, power) - Returns number raised to power. - [Max | Min] (number1, number2) - Returns, respectively, the greater or the lowest number. - Round(number, decimalPlaces) - Rounds a given number to the decimalPlaces limit. - IsNullOrEmpty(aField) - Returns True (1) if a field is null. If this field is a string, also returns true if its length is zero. Else returns False (0). Only works with Strings. - StartsWith(string, substring) - Returns True if a string starts with substring. - EndsWith(string, substring) - Returns True if a string ends with substring. - DateDiffMillisecond(endDate, startDate) - Date Diff returning elapsed milliseconds - DateDiffTick(endDate, startDate) - Date Diff returning elapsed ticks - MinList ( param1,param2,param ,.. paramN ) returns the minimum of list of params param1 through paramN 2. Fixed a bug in Maxlist, where it was not considering the first value Sept 23rd 1. Fixed a bug where the table name was not being set in irdbimport with the new tempDB option. Sept 21st 1. OPtimiisation to remove one unneccessary String Replace in Code Compilation 2. Optimized Column generation process, to only generate the required number of columns when processing results, and to try and reduce lock contention. Sept 15 2015 Build 2 1. Added a new feature to irdbImport. When combining tables from different data sources, before, they all had to have the exact same data type. Now there is an option called USETEMP that takes all different source tables, puts them into a temp database, and then combines them This is more flexible because it can then merge different numeric types together. E.g. IMPORT destable = TEMPDB a1.sourceTableName UNION ALL a2.sourceTableName If you leave out the TEMPDB, it will not use the Temp database Sept 15th 2015 1. Added a build which fixes problems with the rowcounter() and random function introduced in Aug 7th build 2. Turned off Time Tracking code within IRDB, as it was slowing down servers with large no of cpus 3. Reduced memory consumption when creating tables. Before it would allocate a 1 Megabyte array for each column in a resultset. Now it grows this more dynamically, to reduce memory consumption and churn, hence boost performance. Will benefit queries like select sum(1) from sometable. Aug 30th 2015 Build 2 1. Fixed a Server side exception with joins when running on computers with less than 4 CPUS. 2. Fixed the following where the following query code give inaccurate results. There is an algorith, that tries to extract partial parts of the Where Clause, and it was removing parts from OR clauses when it shouldn't have SELECT SUM(1) AS REVENUE FROM LINEITEM inner join PART on part.P_PARTKEY = lineitem.L_PARTKEY WHERE( L_QUANTITY >=10 AND L_QUANTITY <=20 AND P_SIZE BETWEEN 1 AND 10 ) OR ( L_QUANTITY >=20 AND L_QUANTITY <= 30 AND P_SIZE BETWEEN 1 AND 15) Aug 30th 2015 1. Fixed a related bug in JOINS on computers with less than 4 cores. CRITICAL FIX. Would effect Joins involving child tables, where some child records are missing from the parent. Bug introduced Aug 10th build. Aug 28th 2015 1. Fixed a bug in Query Compilation, where it was not handling duplicate COUNT Distincts 2. Fixed a bug in Count Distinct setup bit masks, so it is more concurrent. Aug 27th 2015 1. Added support for null checking for string expressions e.g. select * from somtable where trim(somefield) is null or coalesce ( trim(somefield) , 'dummy' ) Aug 26th 2015 1. Fixed a bug that was introduced in indexes in the Aug 10th code, by the new index performance. Joins involving Parent table and whose child table had cardinality in unique column values less than the parent table could produce incorrect results. Recommend that clients up grade to this build. Aug 24th 2015 1. Modified the InMemory.Net Parallel Quick Sort, to work better with Custom Task Schedulers and Task Factories. There were situations where it could deadlock with a custom Task Scheduler, as it got stuck waiting for child tasks to finish, that never started because of constraints of the custom task scheduler. Aug 23rd 2015 Build 2 1. Fixed a bug where it was saving temporary columns to disk, that shouldn't have been saved. This was causing a problem, because it was trying to save some temporary combined columns 2. Fixed a bug where joins involving multiple columns were generating a server side exception when one of the parts of the column was null Aug 23rd 2015. 1. Fixed a memory leak where indexes against temporary tables from queries were not being dropped 2. Added a new irdb.ini option called maxcpus This allows you to limit the number of cpus a particular query will use during execution. Aug 20th 2015 1. Removed Locks from Aug 14th & Aug 14th Builds, as it could result in Deadlock issues. Replaced Dictionary with ConcurrentDictionary in InMemoryDatabase for list of tables and in InMemoryColumnBase, for list of Indexes and foreign Keys Aug 17th 2015 1. Added a lock when adding or removing indexes to a InMemory Table. This prevents an exception, when 2 parallel threads try to add the same index as each other 2. Added a check to see if a column name already exists before adding This stops 2 concurrent threads adding the same column. 3. Added a check to see if a table already exists before adding it. This is designed to prevent 2 concurrent threads having a problem, When they try to add the same temp table Aug 14th 2015 1. Added a lock when adding and removing and getting tables from an InMemoryDatabase. This helps improve concurrency of this operation. Aug 10th 2015 1. Improved performance when creating joins between tables. In our testing it was 5 times faster. 2. Fixed an exception in recent builds with case e.g. SELECT CASE WHEN field In ('A','B') THEN 'val1' ELSE 'val2' END FROM [Customers] Aug 7th 2015 1. New feature to boost performnace, by factoring out expressions of constants, so they only need to be execute 1 time 2. Some performance enhancement for like '%a' , like 'xyz%' , like '%xyz%' , like '%ab%xy%' 3. Added a new feature to allow queries that return 1 result as part of an expression e.g. Select * from orders where ORDERID = ( select max(orderid) from orders ) 4. Fixed some internal handling to handle SQL, that don't have aliases in some of the fields. 5. Fixed some bugs in the code that refactors INNER JOINS from the WHERE CLAUSE 6. Rewrite SomeStringColumn <> 'abc' as NOT SomeStringColumn = 'abc' so it runs faster. July 27th 2015 1. Optimisation of Dot Net .Config files. Commented out unneeded elements. In some situations this can help performance July 22nd 1. Fixed a bug where tables&columns that were loaded, were not marked as being compiled. 2. Added a new feature to IRDBImport to be able to add a constant column value to every table in a database. Syntax is ADD CONSTANT COLUMN columnName = Expression TO datasourceName e.g. ADD CONSTANT COLUMN company_id = 101 TO ME e.g. ADD CONSTANT COLUMN company_id = 'ABC' TO A1 This feature is designed to help with consolidation multiple files. The Datasource Name must be ME or a LOCAL IRDB Datasource. July 21st 1. Fixed a bug with the Sqlserer Datasource type that was broken in the July 19th build July 19th 1. Added Initial Linux and Mac OS X Distribution for InMemory.Net 2. Fixed a problem with using the Postgres Sql Dot Net Data Provider in IRDBImport 3. Added a new ability to specify Dot Net Connections directly in .imp IRDBImport Scripts This skips the need to configure Dot Net Providers, which can be complicated. In IRDBImport there is a new command called LOAD ASSEMBLY 'assembyname.dll' that allows you to dynamically load assemblies into IRDBImport. The typical use case for this is so you can then use DBConnections within those Assemblies. A Datasource can then be defined using the DOTNET CONNECTION keywords which take 2 parameters. The first is the full name of the class. The second is the connection string. So for example to Add a Postgres DBConnection 1. Goto http://pgfoundry.org/frs/?group_id=1000140 and download the latest version of Npgsql for Dot Net 4.0 2. Extract Npgsql.dll and Mono.Security.dll to your irdb directory 3. Use the following code to define your Postgres Dot net Data Connection LOAD ASSEMBLY 'Npgsql.dll' DATASOURCE a1 = DOTNET CONNECTION 'Npgsql.NpgsqlConnection' 'User ID=your_user_id;Password=your_password;Host=your_host;Port=5432;Database=your_database' Note when extracting Dot Net DLLs on windows, on more recent Windows OS, the DLLs may be blocked as executable content downloaded from the internet. You can fix this, by going into the properties of the dll, and unblocking them. July 9th 1. Updated 64 bit odbc driver installer to Installshield 2015. This should now install the Visual c++ 2013 runtime and install the odbc driver, without having to run the installer twice. June 26th 1. Removed confusing Exception messsages in error messages, when certain columns could not be found in the query. 2. Clean up of error messages, to only display one error message, and not display multiple repitions, and different error messages. June 25th 1. Added new joining feature to allow Left Join and Inner Joins with upto to 8 fields. select * from A inner join B on A.ID1=B.id1 AND A.ID2=B.id2 June 21st 1. Now support more complex expressions in SELECT clause. Can now build expression with multiple grouped variables e.g. Select A+B,sum(1) from sometable group by A,B 2. Now support combining Aggregate functions with grouped variables that are DBFIelds e.g Select A + SUM(!) from sometable group by A 3. Now support expressions ( that dont involve aggregate functions ) in SELECT that use grouped expressions of 1 variable E.g. Select (A+1) * 2 ,sum(1) from sometable gruop by A+1 Note Select (A+1) + sum(1) from sometable gruop by A+1 is not currently supported June 18th 1. IRDBImport32 should now support twice as much memory on 64 bit systems. 2. Add LOH GC Flag so LOH Garbage collection will happen in IRDBIMport when GC occurs. 3. Fixed a bug where Out of Memory Errors were causing irdbimport/irdbimport32 to hang, while during the table reading phase ( before compilation ). One thread was getting stuck in a infinite loop 4. Fixed a bug with the slurp command in irdbimport, where it continued reading when it encountered Out of Memory 5. Added experimental command called NO PARALLEL which compiles tables one column at a time rather than in parallel It may help a small bit with reducing memory consumption June 17th 1. Updated IRDBImport/IRDB SQL Manual 2. Added new Datasource type to IrdbImport. YOu can now use an arbitrary Dot net Data provider, by specifying the keywords DOTNET PROVIDER after the = symbol. It takes 2 params. Param 1 is the dot net name of the class for the provider. Param 2 is the connection string for the provider. e.g. DATASOURCE a1=DOTNET PROVIDER 'System.Data.SqlClient' 'Data Source=localhost;Initial Catalog=yourDB;User Id=your_username;Password=your_password;' The Dot Net provider Class needs to be accessible from IRDBImport/IRDBImport32 June 16th 2015 1. Fixed a bug where repeated string constants was causing a query to not compile 2. Added support for count ( * ) ( Treats it as sum (1 ) 3. Added Support for AVG ( SomeParam ) treats it as Sum ( SomeParam ) / Count ( SomeParam ) 4. Added support for Substring, Contains, Random, Mod, Exp functions e.g. Random() , Exp ( 2) , 5 Mod 2 June 15th 2015 1. Fixed a bug where a exception was being generated by a duplicate join in WHERE was clause. 2. Fixed a bug where Count Distinct was being treated as Count. Regression bug introduced in May 17th build. June 14th 1. Fixed a bug, when using a clustered index on a table, and the a query contained a range predicated in the whereclause that did not involve the clustered index 2. IRDB Server, should not support greater connection throughput. Upto 5x more connections opening and closing 3. Select queries on large tables, that return a small number of rows relative to the number of rows in the table, should run faster. 5x in certain cases. 4. IRDB Server will display a message to the console if it cant bind to its port, instead of triggering a crash and an exception, when running console mode 5. IRDB Server will generate an error in the application log, if it cant bind to its port, when running in service mode. June 5th 1. Added support for cast Functions 2. Added support for numeric parameters for the 2nd param of datediff and 3 param of dateadd. 3. Fixed a bug where queries were not compiling with an expression with constants and a BETWEEN clause. May 28th 1. We have released a 64 bit ODBC Driver for IRDB 2. IRDB has improved ODBC Function support, supporting many standard functions for ODBC. and odbc fn escape sequence. 3. IRDB should now support the unary operator. E.g. select -dbfield from sometable 4. New one Parameter functions are CAST_AS_LONG, CAST_AS_SINGLE, CAST_AS_DOUBLE, CAST_AS_BYTE, CAST_AS_SHORT, CAST_AS_DECIMAL, CAST_AS_BOOL,CAST_AS_INT , QUARTER , HOUR, MINUTE,SECOND,WEEK,DAYOFYEAR 5. New 2 Parameter functions are DatePart , Truncate ( Number, no_of_digits ) . No of_digits=0 currently supported , EXTRACT ( type FROM expression ) 6. ODBC Compatible convert function e.g. Convert 2.5 , SQL_BIGINT) 7. New build should be compatible with Tableau, and using the new 64 bit odbc driver. 8. Support for SELECT INTO. ( May not work through ODBC ) 9. DROP TABLE added to IRDB SQL. ( May not work through ODBC ) May 17th 1. IRDB has support for Count ( DBFIELD ) and count ( Constant) 2. IRDB supports simple select statements without a FROM Clause e.g. select 1 as somevalue 3. We have updated the IRDB documentation to reflect the changes in IRDB 4. IRDB now performs a Garbage Collection after File Load, Unload & Reload with LOH Compaction if available, to reduce memory consumption 5. New IRDB.ini setting called unload_timer that allows you to specify a time in minutes after which the IRDB Server will unload inactive IRDB files e.g unload_timer=60 By default this setting is not set. You will need to restart the irdb server service after changes to the irdb.ini 6. New IRDB.ini setting called nocache that by setting to true, will cause IRDB not to store the results of select subqueries. This will reduce memory consumption, but could degrade performance. e.g nocache=true 7. If you use a username other than "default", in the connection string, irdb will now look to a subdirectory in the data_dir e.g. username=bob will go to d:\irdb\data\bob 8. New IRDB.ini setting called user_database that allows you to specify an external odbc database to use for authentication. The odbc connection syntax should match the bitness of your OS. You will need a table called organisations with 2 text fields org_name & password The user database is loaded once at IRDB Server start. If you want IRDB to reload the user database which IRDB is running, then you will need to install MSMQ and set up a private message queue called irdb_server_queue. The IRDB Server process will need Access to the private queue. YOu can do this by going into the properties of the irdb_server_queue And granting full permission to everyone. YOu should then send a message with a subject of "reloadusers" Whenever there is a change in the underlying user database. You should also create an empty directory When you create a new user in the irdb data dir. YOu should also probably have an entry for the "default" in your organisations table. May 12th 2015 1. Updated IRDB & IRDB Programming Reference Manual Documentation 2. Added support to irdb server for multi user support, authenticating against an external odbc database. Apr 30th 2015 1. Fixed a bug where spurious junk characters after a valid SELECT SQL statement were not generating a parse error Apr 21st 2015 1. Fixed a bug where select 0.0 from orders union select max(0) from orders was not working Apr 14th 2015 1. Fixed a bug so that Len function does not crash on Null values. Currently Len will return 0 for nulls Apr 13th 2015 1. Added Support to SLURP for LOCAL IRDB Data sources Apr 6th 2015 1. Added support for DELETE in IRDB. Advise using NoCache with embedded_queries Syntax is Delete from TABLENAME (wherecondition ) . 2. Added Trunc function to remove Time Parts from DateTimes Feb 27th 2015 ( Approx ) 1. Can reference IRBDIMport variables inside embedded IRDB Sql Commands 2. IRDBIMPORT If command not supports NOT, OR & AND and Parentheses. ( Equality is still the only predicate ) 3. CRITICAL. Fixed an issue where a non aggregative query was not returning all the results on computers with large numbers of cores. Feb 12th 2015 1. Added support for Cartestian Product e.g. select * from a,b 2. fixed bug that prevented TableDateReader being added in Dot Net API Feb 9th 2015 1. Added INT, DOUBLE , LONG & DATETIME as new datatypes in IRDBImport Script for Variables 2. Added Improved Expression support for variables. E.g. @A + @B * 2.5 + (3-5 ) 3. Added Print command for displaying the content of variables 4. IRDBImport Script variables can be referenced by the EXECUTE command and by the IMPORT command if it references the ME Database or another IRDB that has been loaded locally 5. Added the following functions to IRDBImport Script date() - Current Date getdate() - Current Date & Time () 1 Parameter Functions DAY , MONTH , YEAR , ABS , FLOOR , INT , LEN , UCASE , LCASE, TRIM , LTRIM , RTRIM , SIGN , WEEKDAY 2 Parameter Functions LEFT,RIGHT,CONCAT,FORMAT 3 Parameter functions DATEADD 6. Added the function MAXLIST ( p1,....,param_n ) to irdb sql. It returns the max value of n parameters. 7. Added TRUE / FALSE as boolean constants to IRDB SQL. 8. Added /unload as an option to irdbload so you can force files to unload. Dec 12th 2014 1. Released 32 bit odbc driver Beta for IRDB. Driver is currently in Server Install. Adds Dependancy on C++ Runtime 2013 Limitations Currently handles Inner Joins and Left joins. Some Tools will generate sql with INNER joins in Where clause which we dont support atm Currently only supports one { oj } still left join. Add dummy support for catalog token. This is ignored by IRDB in general. 2. Added support in IRDB for Cursors and Statements. 3. Added Improved Decimal Support.Integer Decimals with 9 digits or less are imported as ints. With 18 digits or less are imported as longs. With 19 Digits or more ( upto 29 Digits ) are imported as Dot Net Decimals. Decimals with a floating point part are still imported as doubles 4. Improved database protocol to support Column Width and Precision. ( DB & Communication Protocol changed in November 2014 to support this ). Older format irdb files from before Mid November will need to be regenerated 5. Updated List of Functions 1 Param: DAY, MONTH, YEAR, ABS, FLOOR,LEN,LCASE,UCASE,SIGN,CSTR,CDATE, CAST_NUM_AS_BYTE,CAST_NUM_AS_SHORT,CAST_NUM_AS_INT,CAST_NUM_AS_LONG,CAST_NUM_AS_SINGLE,CAST_NUM_AS_DOUBLE, CAST_STR_AS_INT, CAST_STR_AS_LONG, CAST_STR_AS_DOUBLE, TRIM, LTRIM, RTRIM, CAST_NUM_AS_DECIMAL, CAST_STR_AS_DECIMAL 2 Param: LEFT, RIGHT, CONCAT,FORMAT 3 Param: DATESERIAL,DATEADD,DATEDIFF,REPLACE 6. IRDBIMport now returns a negative number on a major import failures 7. IRDBImport now does not deadlock on rows with ERROR Values in Access 8. IRDBServer Log now include timestamps, and zero timings are not displayed to make log less verbose 9. Precidence bug fix in some formulas !0. Misc Bug Fixes Oct 20th 2014 1. Added support for Union ALL to derived Tables. Oct 6th 2014 1. Improved irdbload /Reload routine, to make it more stabable 2. Added Between support 3. Added Replace function 4. Added Case expression when 'a' then 1 .. else end support, 5. Added support for Coalesce 6. Added support for Constant Expressions when doing aggregation queries. Oct 2nd 2014 1. Added a new Optimized Count Distinct routine for grouping with large Cardinality ( > 500k ) 2. Added support for DateDiff 3. Added support for String Constants to DateAdd & DateDiff 4. DateAdd & DateDiff now support sql server syntax ( without quotes ) around the 1st param 5. Fixed a bug where it wasn't parsing queries like select * from table where 1 > 1+1 6. Fixed a bug with Duplicate Columns with different combined data types in UNion All Sept 23rd 2014 1. There was a problem with the error message on Simple Select distinct queries, where the column is missing from DB, it now prompts the name of the missing column correctly. 2. When a database is pending loading, and already loaded, you can still execute queries against it. 3. IRDB Installer now leaves being irdb.ini, and future versions keep the changes you have made to the .ini 4. Radixes are now generalized, and not hard coded in generated code. Helps with scalability and means less queries need to be compiled, and helps with reuse, with the new /reload option Sept 19th 2014 1. Fixed some bugs in "IN (SELECT .. ) support 2. Added a /reload option to irdbimport e.g. irdbimport irdb=mydb;pwd=Mypass /reload 3. Temp Columns added by Order by and Having, are now removed from Table Output 4. Added the following conversion functions CAST_NUM_AS_BYTE , CAST_NUM_AS_SHORT, CAST_NUM_AS_INT , CAST_NUM_AS_LONG, CAST_NUM_AS_SINGLE ,CAST_NUM_AS_DOUBLE CAST_STR_AS_INT, CAST_STR_AS_LONG, CAST_STR_AS_DOUBLE 5. SUM now uses DOUBLE for REAL & DOUBLEs 6. SUM now uses LONG for any integer sum type. 5 & 6 could impact performance on 32 bit systems. 7. "Select 0,0.0 from sometable" now works 8. "Select client from sometable having sum(a) > 0" now works. Sept 10th 2014 1. Month , Day, & Year functions now work on all expressions. Most efficient on DB Fields, as it will precalculate this 2. Added support for subquerys e.g. select * from table where somekey in ( select x from y ) 3. Fixed bug in Execute command in IRDBImport, where it was not using the given tableName Sept 2nd 2014 1. + can now be used to Concat strings ( Sql Server Compatible ) 2. & can bow be used to Concat strings ( MS ACCESS Compatible ) 3. IS NULL can now be used to check if something is null ( SQL Server Compatible ) 4. iif ( ) support ( MS Access Compatible ) 5. Updated Lists of Functions 1 Param DAY | MONTH | YEAR | ABS | FLOOR | INT | LEN | UCASE | LCASE | SIGN | CSTR | CDATE 2 Param LEFT | RIGHT | CONCAT | FORMAT 3 Param DATESERIAL | DATEADD 6. ISNULL ( someColumnName ) returns true or false depending if someColumnName is Null ( Access Compatible ) 7. ISNULL ( SomeExpression, someValue ) returns someExpression is someExpression is not Null else returns someValue 8. Updated Parsers to use Antlr 4.3 9. Having Clause now supports aggregates e.g. Having SUM ( A ) > 100 Also still supports column aliases. This makes IRDB more compatible with other SQL Syntax 10. UNION ALL now supports combining numeric types with different precision. 11. UNION IS parsed but is treated as UNION ALL for the time being. Aug 5th 2014 1. Updated the main IRDB manual to document IRDB Query, new LOCAL & REMOTE IRDB & ME Datasources, and UNION ALL in IRDBIMPORT 2. Updated the main IRDB manual to document enhancements in IRDB Sql, i.E. multiple level Joins, Having Support, Limit Clause Support, NOCACHE keyword for subqueries, UNION ALL Support and list of Database functions like Concat, Left, Right, Ucase, Lcase, Sign & Floor 3. Updated Link to IRDB Client Download. July 31st 2014 1. Fixed a bug with having a Null Key value in the child table of a join 2. Fixed a bug where if 2nd position table or higher in a join, and it was 1-many with the first table, and in turn was joined to another, was failing to compile 3. Added Support for Concat Function which can take Numeric or String Operators. E.g. Concat ( 'Hello' , 'World' ). Useful if you want to create a compound key, to do a join on multiple keys 4. Reduced Memory consumption on In Memory Column object. John July 6th 2014 1. Added new IRDB Query program to allow you connect to local databases and remote database to list databases, tables and columns, execute SQL Queries, and see the results 2. Fixed Installer for Windows 8/8.1 3. Fix for RemoteListTables in COM API that was buggy 4. Added support for RemoteListFields to COM Api Apr 29th 2014 1. Fixed a Bug Where queries using Having Where not working 2. Fixed a bug Where Some Count Distinct Queries where not working 3. Added support so that the Like operator can be used against Numeric Field Types Apr 22nd 2014 1. Added New Union ALL feature to IRDBImport command 2. Added new IRDBLoad command line program to allow you to tell an irdb server to load a database 3. Added New Union ALL feature to IRDB. Only currently works on outer queries. 4. Added new feature to IRDBImport to open a LOCAL IRDB Data source, and import data from it. 5. Added new special datasource called ME, that referes to the current IRDB Database. This means the Import command can now self reference itself 6. IRDB Now supports more columns in the grouping. Should now be to group by 16 times more columns. This means the range of tables that SELECT DISTINCT will work is expanded. Apr 10th 2014 1. Fixed issue when Using Like ( left & Right ) when the relevant string is null 2. Added support for Select NOCACHE. Nocache is an option that you can add for subqueries, and it tells irdb to not create a permant temp table Instead it analyzes the parent query, and only select the columns is needs, and tries to add an additional where filter based on the parents criteria 3. IRDB now uses a Parallel Quicksort sorting routine to improve sorting performance on large tables . 4. Some setup work in Ordering is now parallel and should now run quicker 5. IRDB now generates more generic code internally and should now need to use less compilations when running queries. This can help with performance as each query compiled cab taje 60-70 ms to compile Apr 3rd 2014 1. Added support for simple expressions of Aggregate Variables. E.g. Select Sum(A) + SUM(B) from sometable order by SUM(A)/Sum(B) 2. Fixed a bug with String JOINS, were it was not working correctly with mixed case data 3. Fixed a bug when working with multiple transaction tables, where it was only caching the first instance of a date expression and applying for all tables. It now uses the Table and Expression as the unique identifier 4. Reduced Memory consumption for Count Distinct reports, with Reports of Grouping Cardinality < 500k 5. Fixed a bug which could when loading tables Mar 11th 2014 1. Added Support for Having 2. Fixed Bug with limits in Subselects 3. Added support for join on Longs 4. IRDB should now be Case Insensitive in General, and be able to handle data with mixed case data 5. IRDB Now compresses columns with cardinality bigger than 64k better, when saving and loading data 6. Use Dictionarys for Table Lookups 7. Use Dictionary for Column Lookups 8. Added check to not run limiting on Query if not required 9. IR Also has core enhancements, and IR Build date 11th Mar 2014 or later should be run. IRDB record is updated in system33.mdb Feb 11th 2014 1. Added Support for Subselects. E.g. select * from ( select * from as xyz ) as ddd 2. Performance enhancement for many cored machine setup 3. Added support for joins of the form From A Inner Join B on A.id1=b.id1 Inner Join C on B.id2=C.id2 4. Added support for the following String functions. They may not perform quickly at the moment. LEN,UCASE,LCASE, LEFT,RIGHT Jan 24th 2014 1. Fixed a bug with Detail Reports that Contained Columns with Cardinality greater than 65,535 2. Fixed a bug in ( small) detail reports where inconsistent results or no results were being returned 3. Fixed a potential bug in date joins 4. Added support for Somefield not in ( 'A' , 'B' ,... ) Nov 7th 2013 1. Changed format of installer to IRDB_Setup.exe 2. Rebranded as IRDB Aug 1st 2013 1. Fixed bug with Service Not stopping correctly with net stop "irdb server" 2. Fixed a bug in Clustered Index range searches excluding 1 record at end. July 11th 2013 1. Add new Clustered Index Feature. Each table can have one clustered index to improve performance. The table will be sorted by that clustered index 2. Added new Parallel Reduction for Array Grouping Queries 3. Improved Performance for 2D Array Grouping Queries 4. Added Parallelization for 2D Array Grouping Queries 5. Improved Performance for Detail Queries 6. Added Parallelization for Detail Queries 7. Fixed bug in IRDB Datasource to allow \\ in strings. You should now be able to connect to Sql Server data sources that have an instance June 4th 2013 1. Fixed a bug, where the execute command in irdbimport, was saving a generated table as results001 instead of the actual name 2. Fixed irdbimport to work on windows xp 3. Fixed multiple issues with Left Joins 4. Fixed a saving/loading bug when columns had 2^n values 5. 1 to many joins should work correctly now. 6. Updated mapping in ir3.5 to use combined_sales instead of results001 June 3rd 2013 1. IRDB now installs the IRDB Server as a Windows Server process, in the c:\irdb directory 2. New configuration file for irdb server called irdb.ini 3. New Command line import utilitiy called IRDBImport, for generating IRDB data files. data files go in c:\irdb\data in the Std Install 4. IR 3.5 now supports new connectiong string syntax for IRDB 5. northwind.irdb included in default data dir, along with northwind.imp script that generates it, and a mapping to analyze it, in IR 3.5 6. Support for LEFT Joins, and Basic NULL Support in IRDB 7. Support for CASE statements with one WHEN 8. Support for [] and mixed case in IRDB SQL Mar 22nd 2013 1. Initial Upload of new version of IR That works with the in memory database 2. Initial Upload of IRDB