Jump to content

Alexander Sviridenkov

Members
 View Profile  See their activity

  • Content Count

    255

  • Joined

  • Last visited

  • Days Won

    25

Posts posted by Alexander Sviridenkov

  3. Posted

    9 minutes ago, Arnaud Bouchez said:

    I missed the info, sorry.

     

    For a SQL solution, it is very good.

    Out of curiosity, how much memory does it need for the 16.5 GB file?
    Does it use SQLite3 and its virtual tables internally for its SQL dialect (something like https://www.sqlite.org/csv.html)?

    Memory is around 20Mb and doesn;t depend on file size.

    No SQLite or other external solutions are used, everything is written in plain Delphi - SQL parser, SQL execution classes, etc.
    https://delphihtmlcomponents.com/sql/

  4. Posted

    17 minutes ago, Arnaud Bouchez said:

    Easy, and wrong.

    You are reading the station weathers reference data with one row per station.
    And making a min/max/average of a single data per station.

     

    The challenge is to read a 1 billion (1,000,000,000) rows of CSV data for all those 41343 stations, and compute it.

    There is a generator of a 16GB CSV file to inject and process.

    So 0.33s for 41343 rows would make around 8000 seconds, i.e. 5.5 days.

     

  5. Posted

    16 minutes ago, Clément said:

    The unit I'm tracking does not appear in the graph.

    If I remove the path or rename the unit the project doesn't compile.

    I deleted all dcus from the library root to be sure only valid ".pas" files are compiled.

    This is quite strange. Does unit map looks correct (all other units are present)?

     

  6. Posted · Edited by Alexander Sviridenkov

    33 minutes ago, Clément said:

    Very nice tool! But it didn't display the unit I'm looking for. The unit is not directly linked in the project file, instead it is in a folder defined in the IDE environment. I would have to add folders to a list of folders to look for. Is it possible with the latest version you made available?

    Last version should show all files used in application, not only ones mentioned in .dpr.

    It starts from .dpr units and recursively find all units listed in inteface and implementation sections. Of course IDE Library path and projects paths settings are used when scanning.

    BTW it can extract interface and implementation units even if only .dcu is available.

  11. Posted

    38 minutes ago, gioma said:

    then if I have a unicode string like this: 'ABCDEFGHI' and I put it inside a byte array if I want to remove the first character from the byte array do I have to remove two bytes and not one?

    Yes, two or four (for symbols behind BMP).

    • Like 1

  15. Posted

    Delphi 12 support.
    New htide unit - installed Delphi versions, Library paths, PAS and DCU parsing, etc.
    New demo (/Sample Projects/graph) - Unit dependency viewer.

    Cairo canvas support on Linux and Windows for Delphi and Lazarus.
      Canvas supports precise text rendering so PDF and other documents converted by HTML Office library  now can be properly displayed on Linux.
    PDF export on Linux via Cairo.
    Reports library now supports Lazarus
    Big upgrade of SQL framework: perform SQL queries on CSV and text files, JSON, XML, Excel,
    Outlook, SQL scripts and other sources.

    What it can do:

    • Convert to/from CSV, JSON, XML, HTML, Excel, SQL script.
    • Upload files/objects to database
    • Export data from database
    • Check data consistency
    • Display data/objects in DB-aware controls, HtPanel, StringGrid, VirtualTreeview, ControlList
    • Extend database capabilities (f.e. use Pivot in Firebird).
    • Serialize and deserialize objects
    • Reduce calls to REST server by using already loaded data.

    Supported data sources:

    • CSV file
    • JSON file
    • JSON string
    • XML file
    • HTML file
    • Text file with fixed size fields
    • SQL script
    • ZIP archive
    • XLS/XLSX Excel sheet (requires HTML Office Library).
    • Outlook PST/OST file (requires HTML Office Library).
    • TList<T>
    • TObjectList
    • Any class with IEnumerable or GetEnumerator support
    • TDataset
    • array of T
    • SQL Query
    • Custom sources can be added by implementing IDMDatasource interface.

    Supported dialects:

    • SQL 92
    • Oracle
    • MS SQL Server
    • Firebird
    • MySQL
    • PostrgesSQL
    • SQLite
    • ElevateDB

    Result can be exported to

    • CSV
    • XML
    • JSON
    • SQL script
    • TDataset
    • TStringList
    • TObjectList
    • SQL table
    • HTML (plain table or using template)
    • Array of variant
    • Binary file
    • or any other format, using custom processing of result data.
    • Like 2
    • Thanks 4

  20. Posted · Edited by Alexander Sviridenkov

    Sample test application for SQL framework.

    https://delphihtmlcomponents.com/sqltest.rar

    image.thumb.png.55180bd93e84e3af238ee1c583bcf319.png

    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.

     

     

     

     

    • Thanks 1

  25. Posted

    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)

×