Coming soon

[Alexander Sviridenkov 357]

 

[Mike Torrettinni 198]

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.

 

 

 

[Alexander Sviridenkov 357]

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.

[Sherlock 663]

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.

[Alexander Sviridenkov 357]

There is nothing interesting done in Object Inspector, just linking Datasource to FDMemTable and DBGrid to Datasource.

Everything is in code.

[Mike Torrettinni 198]

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?

[Alexander Sviridenkov 357]

Yes, classes from HTML Library.

[mvanrijnen 123]

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

[microtronx 38]

Wohoo - crazy stuff ...

[Dave Novo 51]

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.

[Edwin Yip 154]

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.

[Edwin Yip 154]

@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)?

[Alexander Sviridenkov 357]

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;

 

[Alexander Sviridenkov 357]

Nested objects

 

 

 

 

 

Edited October 8, 2023 by Alexander Sviridenkov

[Alexander Sviridenkov 357]

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)

[Alexander Sviridenkov 357]

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