SoftTree Technologies Technical Support Forums Register • Search • FAQ • Memberlist • Usergroups • Log in [SA 7.1.166 Pro] - Weird formatting      SoftTree Technologies Forum Index » SQL Assistant View previous topic View next topic [SA 7.1.166 Pro] - Weird formatting Author Message gemisigo Joined: 11 Mar 2010 Posts: 2165 [SA 7.1.166 Pro] - Weird formatting Something bizarre happens when I try to format my statements (MySQL) in SQL Editor. My Code formatting for CREATE TABLE statements is the following: Code: CREATE TABLE ... (    ,...    ... ) The code part I want to format is: Code: DROP TABLE IF EXISTS ertekesites.idoszakok; CREATE TABLE IF NOT EXISTS ertekesites.idoszakok (         id_idoszakok INT UNSIGNED NOT NULL         ,megnevezes VARCHAR(32) NOT NULL         ,leiras VARCHAR(96)         ,erintett_ev TINYINT UNSIGNED         ,erintett_felev TINYINT UNSIGNED         ,erintett_negyedev TINYINT UNSIGNED         ,erintett_honap TINYINT UNSIGNED          ,erintett_felhonap TINYINT UNSIGNED         ,erintett_het TINYINT UNSIGNED         ,erintett_nap TINYINT UNSIGNED         ,kezdo_felev_offset TINYINT         ,kezdo_negyedev_offset TINYINT         ,kezdo_honap_offset TINYINT         ,kezdo_felhonap_offset TINYINT         ,kezdo_het_offset TINYINT         ,kezdo_nap_offset TINYINT                         ,kezdo_felev    TINYINT unsigned ,kezdo_negyedev TINYINT unsigned ,kezdo_honap    TINYINT unsigned ,kezdo_felhonap TINYINT unsigned ,kezdo_het      TINYINT unsigned                  ,kezdo_nap TINYINT UNSIGNED                  ,zaro_felev_offset TINYINT UNSIGNED         ,zaro_negyedev_offset TINYINT UNSIGNED         ,zaro_honap_offset TINYINT         ,zaro_felhonap_offset TINYINT         ,zaro_het_offset TINYINT         ,zaro_nap_offset TINYINT         ,zaro_felev    TINYINT unsigned ,zaro_negyedev TINYINT unsigned ,zaro_honap    TINYINT unsigned ,zaro_felhonap TINYINT unsigned ,zaro_het      TINYINT unsigned         ,vege_honap_offset TINYINT         ,vege_nap_offset TINYINT         ,vege_nap TINYINT UNSIGNED         ,id_ervenyessegi_idok INT UNSIGNED NOT NULL         ,kezdo_ev_offset TINYINT         ,vege_ev_offset TINYINT         ,CONSTRAINT psk_idoszakok PRIMARY KEY(id_idoszakok)         CONSTRAINT uq_idoszakok_1 UNIQUE(megnevezes)      ); CREATE INDEX fk_ervenyessegi_idok_idoszakok_id_ervenyessegi_idok ON ertekesites.idoszakok ( id_ervenyessegi_idok ); ALTER TABLE ertekesites.idoszakok ADD CONSTRAINT fk_ervenyessegi_idok_idoszakok_id_ervenyessegi_idok FOREIGN KEY ( id_ervenyessegi_idok ) REFERENCES ertekesites.ervenyessegi_idok( id_ervenyessegi_idok ) ON DELETE NO ACTION ON UPDATE NO ACTION; The (unexpected) result is: Code: --/*  Formatted to MySQL default at 2014.08.27. 15:22:05*/ DROP TABLE IF EXISTS ertekesites.idoszakok; CREATE TABLE IF NOT EXISTS ertekesites.idoszakok (    id_idoszakok INT UNSIGNED NOT NULL,megnevezes VARCHAR(32) NOT NULL,leiras VARCHAR(96),erintett_ev TINYINT UNSIGNED,erintett_felev TINYINT UNSIGNED,    erintett_negyedev TINYINT UNSIGNED,erintett_honap TINYINT UNSIGNED,erintett_felhonap TINYINT UNSIGNED,erintett_het TINYINT UNSIGNED,erintett_nap TINYINT    UNSIGNED,kezdo_felev_offset TINYINT,kezdo_negyedev_offset TINYINT,kezdo_honap_offset TINYINT,kezdo_felhonap_offset TINYINT,kezdo_het_offset TINYINT,    kezdo_nap_offset TINYINT,kezdo_felev TINYINT UNSIGNED,kezdo_negyedev TINYINT UNSIGNED,kezdo_honap TINYINT UNSIGNED,kezdo_felhonap TINYINT UNSIGNED,kezdo_het    TINYINT UNSIGNED,kezdo_nap TINYINT UNSIGNED,zaro_felev_offset TINYINT UNSIGNED,zaro_negyedev_offset TINYINT UNSIGNED,zaro_honap_offset TINYINT,    zaro_felhonap_offset TINYINT,zaro_het_offset TINYINT,zaro_nap_offset TINYINT,zaro_felev TINYINT UNSIGNED,zaro_negyedev TINYINT UNSIGNED,zaro_honap TINYINT    UNSIGNED,zaro_felhonap TINYINT UNSIGNED,zaro_het TINYINT UNSIGNED,vege_honap_offset TINYINT,vege_nap_offset TINYINT,vege_nap TINYINT UNSIGNED,    id_ervenyessegi_idok INT UNSIGNED NOT NULL,kezdo_ev_offset TINYINT,vege_ev_offset TINYINT,CONSTRAINT psk_idoszakok PRIMARY KEY(id_idoszakok)    CONSTRAINT uq_idoszakok_1 UNIQUE(megnevezes) ) As you can see, the last two statements (CREATE INDEX and ALTER TABLE) are vanquished completely, and the CREATE TABLE became as compact as it could be, though it should be formatted one column per line. Wed Aug 27, 2014 9:24 am SysOp Site Admin Joined: 26 Nov 2006 Posts: 7948 Please try replacing rule Code: CREATE TABLE ... (    ,...    ... ) with the same text pasted from Notepad to ensure you get CL LF after each line. What you have now was likely pasted or edited in an editor using only CR or only LF as end-of-line breaks. Also for a better result create a separate rule for Code: CREATE TABLE IF NOT EXISTS ... (    ,...    ... ) or you will get them separated into 2 lines after formatting. suggesting the same new rule for Code: DROP TABLE IF EXISTS ... Wed Aug 27, 2014 10:46 pm gemisigo Joined: 11 Mar 2010 Posts: 2165 I tried your suggestion but it did not help. However, it's strange but until I read your reply I didn't notice that the rule I copy-pasted here was mingled in the first place. It was: Code: CREATE TABLE ... (    ,...    ... ) instead of Code: CREATE TABLE ... (    ...    ,... ) I fixed that error and now it works almost properly. When I format the code that was compacted by faulty rule I get: Code: CREATE TABLE IF NOT EXISTS ertekesites.idoszakok (    id_idoszakok               INT UNSIGNED NOT NULL    ,megnevezes                VARCHAR(32) NOT NULL    ,leiras                    VARCHAR(96)    ,erintett_ev               TINYINT UNSIGNED    ,erintett_felev            TINYINT UNSIGNED    ,erintett_negyedev         TINYINT UNSIGNED    ,erintett_honap            TINYINT UNSIGNED    ,erintett_felhonap         TINYINT UNSIGNED    ,erintett_het              TINYINT UNSIGNED    ,erintett_nap              TINYINT     UNSIGNED    ,kezdo_felev_offset        TINYINT    ,kezdo_negyedev_offset     TINYINT    ,kezdo_honap_offset        TINYINT    ,kezdo_felhonap_offset     TINYINT    ,kezdo_het_offset          TINYINT    ,kezdo_nap_offset          TINYINT    ,kezdo_felev               TINYINT UNSIGNED    ,kezdo_negyedev            TINYINT UNSIGNED    ,kezdo_honap               TINYINT UNSIGNED    ,kezdo_felhonap            TINYINT UNSIGNED    ,kezdo_het     TINYINT UNSIGNED    ,kezdo_nap                 TINYINT UNSIGNED    ,zaro_felev_offset         TINYINT    ,zaro_negyedev_offset      TINYINT    ,zaro_honap_offset         TINYINT    ,zaro_felhonap_offset      TINYINT    ,zaro_het_offset           TINYINT    ,zaro_nap_offset           TINYINT    ,zaro_felev                TINYINT UNSIGNED    ,zaro_negyedev             TINYINT UNSIGNED    ,zaro_honap                TINYINT UNSIGNED    ,zaro_felhonap             TINYINT     UNSIGNED    ,zaro_het                  TINYINT UNSIGNED    ,zaro_nap                  TINYINT UNSIGNED ) which is not quite properly formatted. But there's more of it. When I format the following code selected (and I mean by that that I select only this part of the code and apply the formatting rules only to selection): Code: CREATE TABLE IF NOT EXISTS ertekesites.idoszakok ( id_idoszakok INT UNSIGNED NOT NULL ,megnevezes VARCHAR(32) NOT NULL ,leiras VARCHAR(96) ,erintett_ev TINYINT UNSIGNED ,erintett_felev TINYINT UNSIGNED ,erintett_negyedev TINYINT UNSIGNED ,erintett_honap TINYINT UNSIGNED  ,erintett_felhonap TINYINT UNSIGNED ,erintett_het TINYINT UNSIGNED ,erintett_nap TINYINT UNSIGNED ,kezdo_felev_offset TINYINT ,kezdo_negyedev_offset TINYINT ,kezdo_honap_offset TINYINT ,kezdo_felhonap_offset TINYINT ,kezdo_het_offset TINYINT ,kezdo_nap_offset TINYINT        ,kezdo_felev    TINYINT unsigned ,kezdo_negyedev TINYINT unsigned ,kezdo_honap    TINYINT unsigned ,kezdo_felhonap TINYINT unsigned ,kezdo_het      TINYINT unsigned ,kezdo_nap TINYINT UNSIGNED ,zaro_felev_offset TINYINT ,zaro_negyedev_offset TINYINT ,zaro_honap_offset TINYINT ,zaro_felhonap_offset TINYINT ,zaro_het_offset TINYINT ,zaro_nap_offset TINYINT ,zaro_felev    TINYINT unsigned ,zaro_negyedev TINYINT unsigned ,zaro_honap    TINYINT unsigned ,zaro_felhonap TINYINT unsigned ,zaro_het      TINYINT unsigned ,zaro_nap      TINYINT unsigned      ); it is properly formatted to Code: CREATE TABLE IF NOT EXISTS ertekesites.idoszakok (    id_idoszakok               INT UNSIGNED NOT NULL    ,megnevezes                VARCHAR(32) NOT NULL    ,leiras                    VARCHAR(96)    ,erintett_ev               TINYINT UNSIGNED    ,erintett_felev            TINYINT UNSIGNED    ,erintett_negyedev         TINYINT UNSIGNED    ,erintett_honap            TINYINT UNSIGNED    ,erintett_felhonap         TINYINT UNSIGNED    ,erintett_het              TINYINT UNSIGNED    ,erintett_nap              TINYINT UNSIGNED    ,kezdo_felev_offset        TINYINT    ,kezdo_negyedev_offset     TINYINT    ,kezdo_honap_offset        TINYINT    ,kezdo_felhonap_offset     TINYINT    ,kezdo_het_offset          TINYINT    ,kezdo_nap_offset          TINYINT    ,kezdo_felev               TINYINT UNSIGNED    ,kezdo_negyedev            TINYINT UNSIGNED    ,kezdo_honap               TINYINT UNSIGNED    ,kezdo_felhonap            TINYINT UNSIGNED    ,kezdo_het                 TINYINT UNSIGNED    ,kezdo_nap                 TINYINT UNSIGNED    ,zaro_felev_offset         TINYINT    ,zaro_negyedev_offset      TINYINT    ,zaro_honap_offset         TINYINT    ,zaro_felhonap_offset      TINYINT    ,zaro_het_offset           TINYINT    ,zaro_nap_offset           TINYINT    ,zaro_felev                TINYINT UNSIGNED    ,zaro_negyedev             TINYINT UNSIGNED    ,zaro_honap                TINYINT UNSIGNED    ,zaro_felhonap             TINYINT UNSIGNED    ,zaro_het                  TINYINT UNSIGNED    ,zaro_nap                  TINYINT UNSIGNED ); But when I apply the formatting to it unselected (formatting the contents of the entire editor window) and the statement preceding this one is missing its terminating ';' the results get a bit twisted: Code: CREATE TABLE IF NOT EXISTS ertekesites.idoszakok (                                                     id_idoszakok INT UNSIGNED NOT NULL,                                                     megnevezes VARCHAR(32) NOT NULL,                                                     leiras VARCHAR(96),                                                     erintett_ev TINYINT UNSIGNED,                                                     erintett_felev TINYINT UNSIGNED,                                                     erintett_negyedev TINYINT UNSIGNED,                                                     erintett_honap TINYINT UNSIGNED,                                                     erintett_felhonap TINYINT UNSIGNED,                                                     erintett_het TINYINT UNSIGNED,                                                     erintett_nap TINYINT UNSIGNED,                                                     kezdo_felev_offset TINYINT,                                                     kezdo_negyedev_offset TINYINT,                                                     kezdo_honap_offset TINYINT,                                                     kezdo_felhonap_offset TINYINT,                                                     kezdo_het_offset TINYINT,                                                     kezdo_nap_offset TINYINT,                                                     kezdo_felev TINYINT UNSIGNED,                                                     kezdo_negyedev TINYINT UNSIGNED,                                                     kezdo_honap TINYINT UNSIGNED,                                                     kezdo_felhonap TINYINT UNSIGNED,                                                     kezdo_het TINYINT UNSIGNED,                                                     kezdo_nap TINYINT UNSIGNED,                                                     zaro_felev_offset TINYINT,                                                     zaro_negyedev_offset TINYINT,                                                     zaro_honap_offset TINYINT,                                                     zaro_felhonap_offset TINYINT,                                                     zaro_het_offset TINYINT,                                                     zaro_nap_offset TINYINT,                                                     zaro_felev TINYINT UNSIGNED,                                                     zaro_negyedev TINYINT UNSIGNED,                                                     zaro_honap TINYINT UNSIGNED,                                                     zaro_felhonap TINYINT UNSIGNED,                                                     zaro_het TINYINT UNSIGNED,                                                     zaro_nap TINYINT UNSIGNED                                                  ); Not entirely an error, though, it's a good indicator that the preceding statement is missing something important. Thu Aug 28, 2014 2:50 am SysOp Site Admin Joined: 26 Nov 2006 Posts: 7948 I compared that to default formatting rules for MySQL and I see they all include the ending semicolon. Here is the default DROP rule. Code: DROP ... IF EXISTS ...; Can you try that? Thu Aug 28, 2014 8:30 am gemisigo Joined: 11 Mar 2010 Posts: 2165 SysOp wrote: I compared that to default formatting rules for MySQL and I see they all include the ending semicolon. I'm afraid you misinterpreted that. Though the rules for CREATE TABLE did lack the terminating semicolon, so thanks for pointing that out, the rest of them include it. The missing semicolon I was referring to was (or to be more precise, it was not) in the editor window, that is, part (or correspondingly, not part :) ) of the code formatted. My rule for DROP is identical to yours. Thu Aug 28, 2014 8:50 am SysOp Site Admin Joined: 26 Nov 2006 Posts: 7948 Here is the formatted result I get using the default rules for MySQL and default line length wrapping at 80 chars Code: DROP TABLE IF EXISTS ertekesites.idoszakok; CREATE TABLE IF NOT EXISTS ertekesites.idoszakok (     id_idoszakok              INT UNSIGNED NOT NULL,     megnevezes                VARCHAR(32) NOT NULL,     leiras                    VARCHAR(96),     erintett_ev               TINYINT UNSIGNED,     erintett_felev            TINYINT UNSIGNED,     erintett_negyedev         TINYINT UNSIGNED,     erintett_honap            TINYINT UNSIGNED,     erintett_felhonap         TINYINT UNSIGNED,     erintett_het              TINYINT UNSIGNED,     erintett_nap              TINYINT UNSIGNED,     kezdo_felev_offset        TINYINT,     kezdo_negyedev_offset     TINYINT,     kezdo_honap_offset        TINYINT,     kezdo_felhonap_offset     TINYINT,     kezdo_het_offset          TINYINT,     kezdo_nap_offset          TINYINT,     kezdo_felev               TINYINT UNSIGNED,     kezdo_negyedev            TINYINT UNSIGNED,     kezdo_honap               TINYINT UNSIGNED,     kezdo_felhonap            TINYINT UNSIGNED,     kezdo_het                 TINYINT UNSIGNED,     kezdo_nap                 TINYINT UNSIGNED,     zaro_felev_offset         TINYINT UNSIGNED,     zaro_negyedev_offset      TINYINT UNSIGNED,     zaro_honap_offset         TINYINT,     zaro_felhonap_offset      TINYINT,     zaro_het_offset           TINYINT,     zaro_nap_offset           TINYINT,     zaro_felev                TINYINT UNSIGNED,     zaro_negyedev             TINYINT UNSIGNED,     zaro_honap                TINYINT UNSIGNED,     zaro_felhonap             TINYINT UNSIGNED,     zaro_het                  TINYINT UNSIGNED,     vege_honap_offset         TINYINT,     vege_nap_offset           TINYINT,     vege_nap                  TINYINT UNSIGNED,     id_ervenyessegi_idok      INT UNSIGNED NOT NULL,     kezdo_ev_offset           TINYINT,     vege_ev_offset            TINYINT,     CONSTRAINT psk_idoszakok PRIMARY KEY(id_idoszakok)     CONSTRAINT uq_idoszakok_1 UNIQUE(megnevezes) ); CREATE INDEX fk_ervenyessegi_idok_idoszakok_id_ervenyessegi_idok ON ertekesites.idoszakok (id_ervenyessegi_idok); ALTER TABLE ertekesites.idoszakok ADD CONSTRAINT fk_ervenyessegi_idok_idoszakok_id_ervenyessegi_idok FOREIGN KEY(id_ervenyessegi_idok) REFERENCES ertekesites.ervenyessegi_idok(id_ervenyessegi_idok) ON DELETE NO ACTION ON UPDATE NO ACTION; I can add extra lines between statements in the formatted text by adding them to the formatting rules. I 'm unable to reproduce your results. Could you please check if you have other rules in your custom formatting style that perhaps interfering when you are formatting the entire script and not interfering when formatting just the CREATE TABLE statement. Some generic rule like ..., ... or something else matched in a middle of code may get in the way. Thu Aug 28, 2014 9:07 am gemisigo Joined: 11 Mar 2010 Posts: 2165 SysOp wrote: I can add extra lines between statements in the formatted text by adding them to the formatting rules. That was a result, not a goal. SysOp wrote: I 'm unable to reproduce your results. Could you please check if you have other rules in your custom formatting style that perhaps interfering when you are formatting the entire script and not interfering when formatting just the CREATE TABLE statement. Some generic rule like ..., ... or something else matched in a middle of code may get in the way. Almost there. By adding the semicolons where they were missing I put an end to most of the messings :) Except the one where part of the type (TINYINT or UNSIGNED or both TINYINT UNSIGNED) was snapped into the next line. This one seems to be pretty stubborn. There's a generic rule Code: (    ...,    ...    AND ...    OR ... ) that might interfere but I'm not sure if it's a default factory rule or made by me in my previous life. It also might be related to line length and editor wrapping settings. I'll check those later, for now this will suffice. Thu Aug 28, 2014 9:33 am Display posts from previous:  All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year  Oldest FirstNewest First     SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours Page 1 of 1   Jump to:  Select a forum ----------------SQL AssistantDB Audit, DB Mail, DB Tools24x7 Scheduler, Event Server, Automation SuiteTips & Snippets Repository You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum     Powered by phpBB © 2001, 2005 phpBB Group Design by Freestyle XL / Flowers Online.