SQLite views - pretty print

[mwallnoefer]

It would be nice to correctly reproduce the line-feeds when scripting SQL views.

CREATE VIEW [vwGroupBibs] AS      SELECT BibFrom, BibTo, idGroup                      FROM GroupBibs;

should become (like it happens on the tables):

CREATE VIEW [vwGroupBibs] AS
      SELECT BibFrom, BibTo, idGroup
                      FROM GroupBibs;

Steps to reproduce

Script as CREATE on the view's context menu but also in the Script Database commands.

Further technical details

Toolbox version: 4.7.232.0 Database engine: SQlite Visual Studio or SSMS version: Visual Studio 2013

[ErikEJ]

Not sure I understand why you are facing this, I already add newlines !?

https://github.com/ErikEJ/SqlCeToolbox/blob/master/src/API/Generator/Generator.cs#L2137

[ErikEJ]

Ping?

[mwallnoefer]

Ah sorry, forgotten to answer. Yes but in view.Definition, do you handle them also there? According to me this is not the case atm.

[ErikEJ]

I do not do anything with view.Definition, I just display it as is from the SQLite schema info.

[ErikEJ]

Is it a linebreak issue, perhaps?

[mwallnoefer]

This means if I use the sqlite3 tool from cmdline I see the view definition in the exactly same manner? I need to re-try this... With SQLite Personal it is different.

Linebreaks: exactly, it is a problem concerning this (spaces are represented correctly).

[ErikEJ]

Pls try the latest daily and let me know!

[mwallnoefer]

I would give it a look but am unable to do so. In the latest daily build at my SQLite connections disappeared (under Data Connections). When I try to add a new connection, the configuration dialog pops up, but after clicking on Close it does not appear in the tree - this is strange.

[ErikEJ]

Could you show me the About dialog in the latest build? Sounds like your DataProvider installation is broken.

[mwallnoefer]

Ah yes. Where do I need to download it?

[ErikEJ]

http://system.data.sqlite.org/downloads/1.0.105.2/sqlite-netFx46-setup-bundle-x86-2015-1.0.105.2.exe from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

[ErikEJ]

Guide here: https://github.com/ErikEJ/SqlCeToolbox/wiki/EF6-workflow-with-SQLite-DDEX-provider

[mwallnoefer]

I have tried to follow your instructions but no change in behaviour. But now the SQLite EF6 DBProvider is in the GAC.

[mwallnoefer]

Now I turned back to the latest stable and everything works again (I can see all SQLite DBs).

[ErikEJ]

Thanks, I will try to reproduce and provide a fix!

[ErikEJ]

Fixed in the latest daily build, thanks for reporting! Could I ask you to try it out an let me know?

[mwallnoefer]

Here my answer:

• SQLite DBs show up again, this works Erik!
• The fix works only for the Script Database commands, but not on Script as CREATE on the single view.
• The indentation is still not correct, first I will check with the cmdline sqlite3 tool and then provide you with an example DB.

[mwallnoefer]

sqlite3 output:

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE VIEW [vwBibs] AS
WITH RECURSIVE
    [tab] AS(
        SELECT 1 AS [BibFrom],
               10 AS [BibTo]
        UNION ALL
        SELECT [BibFrom] + 1,
               [BibTo]
        FROM   [tab]
        WHERE  [BibFrom] < [BibTo]
    )
SELECT [BibFrom]
FROM   [tab];
COMMIT;

test.zip

[mwallnoefer]

@ErikEJ, have you managed to figure out something?

[ErikEJ]

Yes, it was an issue with the System.Data.Sqlite implementation of GetSchema("Views") ! This line of code removes any formatting!

          strItem = rd.GetString(4).Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' ');

Changed to get the raw defintion now - fixed in latest build, please let me know if it works for you.

[mwallnoefer]

It works, wonderful!