SQL Reporting Workbench for FreeCAD

[furti]

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.
For help requests or Bugs use this Topic: https://forum.freecadweb.org/viewtopic.php?f=3&t=38225
For new Features post to this Topic: https://forum.freecadweb.org/viewtopic.php?f=10&t=38224

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]]

[furti]

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

[Jee-Bee]

Sounds cool. Could you add this to addon manager?

[furti]

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.

[Kunda1]

very cool!
@kkremitzki would be interested in this

[chrisb]

You can not mix single Properties and functions

No grouping?

[furti]

No grouping?

Not at the current stage. But this feature is on my list

[saso]

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

[saso]

@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.

[bitacovir]

very cool!
@kkremitzki would be interested in this

And @yorik too

ping