SQL Reporting Workbench for FreeCAD

Info about new community or project announcements, implemented features, classes, modules or APIs. Might get technical!
PLEASE DO NOT POST HELP REQUESTS OR OTHER DISCUSSIONS HERE!
User avatar
furti
Posts: 197
Joined: Mon Nov 27, 2017 5:27 pm

SQL Reporting Workbench for FreeCAD

Postby furti » Sat Jan 12, 2019 10:53 am

Hi there :)

I'm currently working on an personal Architecture Project. Now I have some stuff created and want to get some informations out of the model.

I know there is the "Arch Schedule" tool available. But i think it is not flexible enough to get all sorts of Information one can dream about out of the model. As I work with SQL (Structured Query Language https://en.wikipedia.org/wiki/SQL) a lot during work, I decided to give it a try and create a SQL Module for FreeCAD.
After a few hours of playing around with it I have something to show to you. It is by far not finished, but I think it is still useable (at least in the python console).

The workbench can be found at https://github.com/furti/FreeCAD-Reporting.


EDIT: The following is a bit outdated. See the up to date documentation (https://github.com/furti/FreeCAD-Reporting) for a list of all available features.

There is no documentation available right now. But i will give you a short introduction here. I think this should be enough to get you going :)
The workbench has no GUI tools yet. So everything works via the python console right now.

1. At first you might want to open a document that has some objects in it. E.g. the attached one.
2. Go to the python console and create a new SQL parser. You can reuse this parser to parse multiple SQL Statements

Code: Select all

from sql import freecad_sql_parser
sql_parser = freecad_sql_parser.newParser()
3. Now you can use the parser to parse a Statement and select something from the document

Code: Select all

# Simply select all objects from the document
select_all = sql_parser.parse('Select * from document')
select_all.execute()
[[<Sketcher::SketchObject>], [<Part::PartFeature>], [<Sketcher::SketchObject>], [<Part::PartFeature>], [<Sketcher::SketchObject>], [<Part::PartFeature>], [<Part::PartFeature>], [<Part::PartFeature>], [<Part::PartFeature>]]

4. You can select only some properties of the object if you want

Code: Select all

select_label = sql_parser.parse('Select Label from document')
select_label.execute()
[['WallTrace'], ['Wall'], ['WallTrace001'], ['Wall001'], ['WallTrace002'], ['Wall002'], ['Structure'], ['Structure001'], ['Structure002']]
5. You can also Limit the objects in a "Where" Clause. The where clause supports AND OR and even Brackets should work Right now. To compare properties you can use "=", "!=", ">", "<", ">=", "<="

Code: Select all

select_something = sql_parser.parse("Select Label from document Where IfcRole = 'Column' OR Label = 'Wall'")
select_something.execute()
[['Wall'], ['Structure'], ['Structure001'], ['Structure002']]
6. You can even select multiple properties at once

Code: Select all

select_name_area_columns = sql_parser.parse("SELECT Label, VerticalArea From document Where IfcRole = 'Column'")
select_name_area_columns.execute()
[['Structure', 400000 mm^2], ['Structure001', 400000 mm^2], ['Structure002', 4e+06 mm^2]]
7. You can also use functions (Count, Sum, Min, Max)

Code: Select all

select_number_of_objects = sql_parser.parse('Select count(*) From document')
select_number_of_objects.execute()
[[9]]
Attachments
stuff.FCStd
(20.58 KiB) Downloaded 12 times
Last edited by furti on Sat Jan 26, 2019 11:52 am, edited 1 time in total.
User avatar
furti
Posts: 197
Joined: Mon Nov 27, 2017 5:27 pm

Re: SQL Reporting Workbench for FreeCAD

Postby furti » Sat Jan 12, 2019 11:02 am

Edit: I updated the Readme in the repository (https://github.com/furti/FreeCAD-Reporting
) with a "SQL Reference" section now. So this post should be considered outdated. Please check the Readme for an up to date documentation.

How does SQL Work. Basically a Select Statement Looks like this:

Code: Select all

SELECT <Columns>
FROM <Source>
WHERE <Expression>

The "Select" And "From" Clause are mandatory. The where clause is completely optional.


Columns
Columns is a comma separated list of properties you want in the result for each object.

* is a special property that returns the whole object instead of a single property. This might be useful when you want to perform certain operations on some objects in python. You can select them with a select statement, and process them afterwards.

When you use a function (Sum, Min, Max, Count) here, only a single row will be returned with the function applied to each object. You can not mix single Properties and functions. But it should be possible to use multiple functions in a single select statement.


Source
The objects you want to process with your query.

document is a Special Keyword, that processes all objects in the active document.
In the future it should be possible to only use a subset of objects (Groups, single objects) in the from clause.

Expression
The Expression can be used to Limit the result to only a few objects.

It supports comparisons using "=", "!=", ">", ">=", "<", "<=". And comparisons can be combined using "AND" and "OR" Keywords. Even brackets "()" are allowed.


Hope you can make some use out of it in the future :)
Last edited by furti on Fri Jan 18, 2019 8:09 pm, edited 1 time in total.
Jee-Bee
Posts: 1688
Joined: Tue Jun 16, 2015 10:32 am
Location: Netherlands

Re: SQL Reporting Workbench for FreeCAD

Postby Jee-Bee » Sat Jan 12, 2019 11:05 am

Sounds cool. Could you add this to addon manager?
User avatar
furti
Posts: 197
Joined: Mon Nov 27, 2017 5:27 pm

Re: SQL Reporting Workbench for FreeCAD

Postby furti » Sat Jan 12, 2019 11:12 am

Jee-Bee wrote:
Sat Jan 12, 2019 11:05 am
Could you add this to addon manager?
I think it would be best to add it to the addon manager, when some Features are useable from the GUI and some documentation is available. Otherwise "regular" users might be a bit confused by a empty workbench.
User avatar
Kunda1
Posts: 3925
Joined: Thu Jan 05, 2017 9:03 pm

Re: SQL Reporting Workbench for FreeCAD

Postby Kunda1 » Sat Jan 12, 2019 11:32 am

very cool!
@kkremitzki would be interested in this
kkremitzki wrote:
Want to contribute back to FC? Checkout:
#lowhangingfruit | Use the Source, Luke. | How to Help FreeCAD | How to report FC bugs and features
chrisb
Posts: 14477
Joined: Tue Mar 17, 2015 9:14 am

Re: SQL Reporting Workbench for FreeCAD

Postby chrisb » Sun Jan 13, 2019 12:11 am

furti wrote:
Sat Jan 12, 2019 11:02 am
You can not mix single Properties and functions
No grouping?
User avatar
furti
Posts: 197
Joined: Mon Nov 27, 2017 5:27 pm

Re: SQL Reporting Workbench for FreeCAD

Postby furti » Sun Jan 13, 2019 7:14 am

chrisb wrote:
Sun Jan 13, 2019 12:11 am
No grouping?
Not at the current stage. But this feature is on my list :)
User avatar
saso
Posts: 1159
Joined: Fri May 16, 2014 1:14 pm
Contact:

Re: SQL Reporting Workbench for FreeCAD

Postby saso » Sun Jan 13, 2019 11:32 am

Yes, this is very very nice! I have expressed the wish to do this in the past, it is very close to what I have suggested here https://forum.freecadweb.org/viewtopic. ... 20#p221422

So this is using the queries to export the data from the model (is of course already very useful). Next level is to use the queries to also do visual presentation of the data on the model (for example, create a view with all concrete walls colored red, everything else show as grey with 80% transparency), something similar is shown here https://www.youtube.com/watch?v=I0V_8EGYHgU

:)
User avatar
saso
Posts: 1159
Joined: Fri May 16, 2014 1:14 pm
Contact:

Re: SQL Reporting Workbench for FreeCAD

Postby saso » Sun Jan 13, 2019 12:20 pm

Kunda1 wrote:
Sat Jan 12, 2019 11:32 am
@kkremitzki would be interested in this
If I understand his idea correctly he wants to put (save) the complete model in to a database (PostgreSQL) instead of saving it to files (one or more), so it is something different to what this is, but it is of course also very powerful.
User avatar
bitacovir
Posts: 507
Joined: Sat Apr 19, 2014 6:23 am
Contact:

Re: SQL Reporting Workbench for FreeCAD

Postby bitacovir » Sun Jan 13, 2019 3:24 pm

Kunda1 wrote:
Sat Jan 12, 2019 11:32 am
very cool!
@kkremitzki would be interested in this
kkremitzki wrote:
And @yorik too
yorik wrote:ping
::bitacovir::
===================================
One must be absolutely modern.
Arthur Rimbaud (A Season in Hell -1873)

My Blog
Mini Airflow Tunnel Project