Mike Torrettinni 198 Posted October 6, 2023 Looks good, and short description would be good, too. I'm trying to figure out if anything can be used for my projects or some of my clients. Is this SQL highlighting control? Will you have dedicated SQL stuff page on your website? I see the SQL framework pdf, but I'm trying to understand how HTML library, HTML editor, Office controls fit with SQL stuff. Share this post Link to post
Alexander Sviridenkov 345 Posted October 6, 2023 4 minutes ago, Mike Torrettinni said: Is this SQL highlighting control? No, this is ability to execute SQL on any source - list of objects, array of records, dataset, CSV, JSON, XML, etc. And put result into dataset, stringlist, array, etc. 1 Share this post Link to post
Sherlock 648 Posted October 6, 2023 Great work! I'm guessing you zoomed away from the object inspector on purpose, because you are not quite done yet? It would be great to see what's happening there otherwise. Share this post Link to post
Alexander Sviridenkov 345 Posted October 6, 2023 There is nothing interesting done in Object Inspector, just linking Datasource to FDMemTable and DBGrid to Datasource. Everything is in code. Share this post Link to post
Mike Torrettinni 198 Posted October 6, 2023 13 minutes ago, Alexander Sviridenkov said: No, this is ability to execute SQL on any source - list of objects, array of records, dataset, CSV, JSON, XML, etc. And put result into dataset, stringlist, array, etc. THtSQL* and TSQL* are your components? Share this post Link to post
Alexander Sviridenkov 345 Posted October 6, 2023 Yes, classes from HTML Library. 1 Share this post Link to post
mvanrijnen 121 Posted October 6, 2023 (edited) Looks good, you gonna release the SQL classes apart from the HTML Library ? btw where did you learn to type that fast ? 😉 Edited October 6, 2023 by mvanrijnen Share this post Link to post
Dave Novo 51 Posted October 6, 2023 Instead of just using SQL strings, did you think of using some of the existing ORM syntax as a model. There are many examples for Delphi. See TMS Aurelius or Spring4D for examples. Share this post Link to post
Edwin Yip 154 Posted October 7, 2023 19 hours ago, Dave Novo said: Instead of just using SQL strings, did you think of using some of the existing ORM syntax as a model. There are many examples for Delphi. See TMS Aurelius or Spring4D for examples. SQL is the most proven and widely-used DSL on earth, and it's the best option. Forget about those clunky and strange syntax of the so-called full-fledged ORM's. 1 Share this post Link to post
Edwin Yip 154 Posted October 7, 2023 @Alexander Sviridenkov This is very cool and can be very useful! While I have a faith in your libraries, the only thing I'm concerning about is the performance. Is it based on the good old and fast RTTI (System.TypInfo.pas) or the new but slow extended RTTI (System.Rtti.pas)? Share this post Link to post
Alexander Sviridenkov 345 Posted October 7, 2023 8 hours ago, Edwin Yip said: @Alexander Sviridenkov This is very cool and can be very useful! While I have a faith in your libraries, the only thing I'm concerning about is the performance. Is it based on the good old and fast RTTI (System.TypInfo.pas) or the new but slow extended RTTI (System.Rtti.pas)? System.Rtti. AFAIK TypInfo do not support records and field values. But there are some optimizations so loading list of test objects containing 1 million objects takes 850 ms. TTest = class public A: integer; b: string; c: double; end; Share this post Link to post
Alexander Sviridenkov 345 Posted October 8, 2023 (edited) Nested objects Edited October 8, 2023 by Alexander Sviridenkov Share this post Link to post
Alexander Sviridenkov 345 Posted October 8, 2023 On 10/6/2023 at 5:15 PM, mvanrijnen said: Looks good, you gonna release the SQL classes apart from the HTML Library ? btw where did you learn to type that fast ? 😉 No, this will be included in Reports (Bundle) Share this post Link to post
Alexander Sviridenkov 345 Posted October 19, 2023 (edited) Sample test application for SQL framework. https://delphihtmlcomponents.com/sqltest.rar What it can do? Extract and transform data directly from Excel, CSV, JSON and XML files and archives. How to use: choose file or folder and write SQL query. Example: select all data from Excel sheet (sample.xlsx is included): select * from sample.sheet1 List of countries (we should exclude first row with caption using _row pseudo field). select distinct b from sample.sheet1 where _row > 0 Calculate sum of sales by country select B country, sum(H) sales from sample.sheet1 where _row > 0 group by 1 Countries (B) present in both segments (A) select B from sample.sheet1 where A = 'Government' intersect select B from sample.sheet1 where A = 'Midmarket' JSON file (customers.json is included): select * from customers where email like '%.ca' Using joins (sales_data.zip is included, it contains four JSON files: sales, orders, customers and products): select c.name, o.orderId, o.date, p.name, s.quantity from sales_data.sales s join sales_data.orders o on o.orderid=s.orderid join sales_data.customers c on c.customerid=o.customerid join sales_data.products p on p.productid=s.productid Calc percent from max quantity select c.name, o.orderId, o.date, p.name, round(100 * s.quantity / (select max(cast(quantity as float)) from sales_data.sales)) qpercent from sales_data.sales s join sales_data.orders o on o.orderid=s.orderid join sales_data.customers c on c.customerid=o.customerid join sales_data.products p on p.productid=s.productid List of products by order select o.orderId, o.date, list(p.name, ', ') products from sales_data.sales s join sales_data.orders o on o.orderid=s.orderid join sales_data.products p on p.productid=s.productid group by 1, 2 XML (cust.xml example included). Sometimes XML files has comples structure and contain different objects. To select only necessary objects we use XPATH. Also XPATH can be used in field names: select CustomerID, "/CompanyName" Company, "/FullAddress/City" City from cust('/Customers/Customer') Join with orders in the same file: select c.CustomerID, c."/CompanyName" Company, c."/FullAddress/City" City, cast(o."/OrderDate" as Date) OrderDate from cust('/Customers/Customer') c join cust('/Orders/Order') o on o."/CustomerID" = c.CustomerID CSV (survey.csv is included) Same naming scheme as for Excel. Excample: top 10 industries by income select C industry, sum(H) income from survey where F = 'Total income' group by 1 order by 2 desc rows 1 to 10 What is supported: select from select join, left join union, union all, intersect distinct, order by, group by, rollup, having first skip last rows limit fetch nulls last sum, max, min, avg, list/listagg = <> > >= < <= + - * / mod || in between like not like starting with and or exists, any, some, all subselects cast, decode, iif, case, coalesce Almost all Firebird funcions https://firebirdsql.org/refdocs/langrefupd21-intfunc.html, Oracle and MSSQL functions. Edited October 19, 2023 by Alexander Sviridenkov 1 Share this post Link to post