1 /************** TabPivot H Declares Source Code File (.H) **************/ 2 /* Name: TABPIVOT.H Version 1.5 */ 3 /* */ 4 /* (C) Copyright to the author Olivier BERTRAND 2005-2013 */ 5 /* */ 6 /* This file contains the PIVOT classes declares. */ 7 /***********************************************************************/ 8 typedef class PIVOTDEF *PPIVOTDEF; 9 typedef class TDBPIVOT *PTDBPIVOT; 10 typedef class FNCCOL *PFNCCOL; 11 typedef class SRCCOL *PSRCCOL; 12 13 /***********************************************************************/ 14 /* This class is used to generate PIVOT table column definitions. */ 15 /***********************************************************************/ 16 class PIVAID : public CSORT { 17 friend class FNCCOL; 18 friend class SRCCOL; 19 public: 20 // Constructor 21 PIVAID(const char *tab, const char *src, const char *picol, 22 const char *fncol, const char *skcol, const char *host, 23 const char *db, const char *user, const char *pwd, int port); 24 25 // Methods 26 PQRYRES MakePivotColumns(PGLOBAL g); 27 bool SkipColumn(PCOLRES crp, char *skc); 28 29 // The sorting function 30 virtual int Qcompare(int *, int *); 31 32 protected: 33 // Members 34 MYSQLC Myc; // MySQL connection class 35 PCSZ Host; // Host machine to use 36 PCSZ User; // User logon info 37 PCSZ Pwd; // Password logon info 38 PCSZ Database; // Database to be used by server 39 PQRYRES Qryp; // Points to Query result block 40 PCSZ Tabname; // Name of source table 41 PCSZ Tabsrc; // SQL of source table 42 PCSZ Picol; // Pivot column name 43 PCSZ Fncol; // Function column name 44 PCSZ Skcol; // Skipped columns 45 PVBLK Rblkp; // The value block of the pivot column 46 int Port; // MySQL port number 47 }; // end of class PIVAID 48 49 /* -------------------------- PIVOT classes -------------------------- */ 50 51 /***********************************************************************/ 52 /* PIVOT: table that provides a view of a source table where the */ 53 /* pivot column is expended in as many columns as there are distinct */ 54 /* values in it and containing the function value matching other cols.*/ 55 /***********************************************************************/ 56 57 /***********************************************************************/ 58 /* PIVOT table. */ 59 /***********************************************************************/ 60 class PIVOTDEF : public PRXDEF { /* Logical table description */ 61 friend class TDBPIVOT; 62 public: 63 // Constructor 64 PIVOTDEF(void); 65 66 // Implementation GetType(void)67 virtual const char *GetType(void) {return "PIVOT";} 68 69 // Methods 70 virtual bool DefineAM(PGLOBAL g, LPCSTR am, int poff); 71 virtual PTDB GetTable(PGLOBAL g, MODE m); 72 73 protected: 74 // Members 75 char *Host; /* Host machine to use */ 76 char *User; /* User logon info */ 77 char *Pwd; /* Password logon info */ 78 char *DB; /* Database to be used by server */ 79 char *Tabname; /* Name of source table */ 80 char *Tabsrc; /* The source table SQL description */ 81 char *Picol; /* The pivot column */ 82 char *Fncol; /* The function column */ 83 char *Function; /* The function applying to group by */ 84 bool GBdone; /* True if tabname as group by format */ 85 bool Accept; /* TRUE if no match is accepted */ 86 int Port; /* MySQL port number */ 87 }; // end of PIVOTDEF 88 89 /***********************************************************************/ 90 /* This is the class declaration for the PIVOT table. */ 91 /***********************************************************************/ 92 class TDBPIVOT : public TDBPRX { 93 friend class FNCCOL; 94 public: 95 // Constructor 96 TDBPIVOT(PPIVOTDEF tdp); 97 98 // Implementation GetAmType(void)99 virtual AMT GetAmType(void) {return TYPE_AM_PIVOT;} 100 101 // Methods GetRecpos(void)102 virtual int GetRecpos(void) {return N;} ResetDB(void)103 virtual void ResetDB(void) {N = 0;} 104 virtual int RowNumber(PGLOBAL g, bool b = FALSE); 105 106 // Database routines 107 virtual PCOL MakeCol(PGLOBAL g, PCOLDEF cdp, PCOL cprec, int n); Cardinality(PGLOBAL g)108 virtual int Cardinality(PGLOBAL g) {return (g) ? 10 : 0;} 109 virtual int GetMaxSize(PGLOBAL g); 110 virtual bool OpenDB(PGLOBAL g); 111 virtual int ReadDB(PGLOBAL g); 112 virtual int WriteDB(PGLOBAL g); 113 virtual int DeleteDB(PGLOBAL g, int irc); 114 virtual void CloseDB(PGLOBAL g); 115 116 protected: 117 // Internal routines 118 bool FindDefaultColumns(PGLOBAL g); 119 bool GetSourceTable(PGLOBAL g); 120 bool MakePivotColumns(PGLOBAL g); 121 bool MakeViewColumns(PGLOBAL g); 122 123 // Members 124 char *Host; // Host machine to use 125 char *User; // User logon info 126 char *Pwd; // Password logon info 127 char *Database; // Database to be used by server 128 char *Tabname; // Name of source table 129 char *Tabsrc; // SQL of source table 130 char *Picol; // Pivot column name 131 char *Fncol; // Function column name 132 char *Function; // The function applying to group by 133 PCOL Fcolp; // To the function column in source 134 PCOL Xcolp; // To the pivot column in source 135 PCOL Dcolp; // To the dump column 136 bool GBdone; // True when subtable is "Group by" 137 bool Accept; // TRUE if no match is accepted 138 int Mult; // Multiplication factor 139 int Ncol; // The number of generated columns 140 int N; // The current table index 141 int M; // The occurrence rank 142 int Port; // MySQL port number 143 BYTE FileStatus; // 0: First 1: Rows 2: End-of-File 144 BYTE RowFlag; // 0: Ok, 1: Same, 2: Skip 145 }; // end of class TDBPIVOT 146 147 /***********************************************************************/ 148 /* Class FNCCOL: for the multiple generated column. */ 149 /***********************************************************************/ 150 class FNCCOL : public COLBLK { 151 friend class TDBPIVOT; 152 public: 153 // Constructor 154 FNCCOL(PCOLDEF cdp, PTDB tdbp, PCOL cprec, int i); 155 156 // Implementation GetAmType(void)157 virtual int GetAmType(void) {return TYPE_AM_FNC;} 158 159 // Methods Reset(void)160 virtual void Reset(void) {} 161 bool InitColumn(PGLOBAL g); 162 bool CompareColumn(void); 163 164 protected: 165 // Member 166 PVAL Hval; // The value containing the header 167 PCOL Xcolp; 168 }; // end of class FNCCOL 169 170 /***********************************************************************/ 171 /* Class SRCCOL: for other source columns. */ 172 /***********************************************************************/ 173 class SRCCOL : public PRXCOL { 174 friend class TDBPIVOT; 175 public: 176 // Constructors 177 SRCCOL(PCOLDEF cdp, PTDB tdbp, PCOL cprec, int n); 178 179 // Implementation GetAmType(void)180 virtual int GetAmType(void) {return TYPE_AM_SRC;} 181 182 // Methods 183 using PRXCOL::Init; Reset(void)184 virtual void Reset(void) {} 185 void SetColumn(void); 186 virtual bool Init(PGLOBAL g, PTDB tp); 187 bool CompareLast(void); 188 189 protected: 190 // Default constructor not to be used SRCCOL(void)191 SRCCOL(void) {} 192 193 // Members 194 }; // end of class SRCCOL 195 196 PQRYRES PivotColumns(PGLOBAL g, const char *tab, const char *src, 197 const char *picol, const char *fncol, 198 const char *skcol, const char *host, 199 const char *db, const char *user, 200 const char *pwd, int port); 201