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