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/***********************************************************************/
8typedef class PIVOTDEF *PPIVOTDEF;
9typedef class TDBPIVOT *PTDBPIVOT;
10typedef class FNCCOL *PFNCCOL;
11typedef class SRCCOL *PSRCCOL;
12
13/***********************************************************************/
14/* This class is used to generate PIVOT table column definitions. */
15/***********************************************************************/
16class 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/***********************************************************************/
60class PIVOTDEF : public PRXDEF { /* Logical table description */
61 friend class TDBPIVOT;
62 public:
63 // Constructor
64 PIVOTDEF(void);
65
66 // Implementation
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/***********************************************************************/
92class TDBPIVOT : public TDBPRX {
93 friend class FNCCOL;
94 public:
95 // Constructor
96 TDBPIVOT(PPIVOTDEF tdp);
97
98 // Implementation
99 virtual AMT GetAmType(void) {return TYPE_AM_PIVOT;}
100
101 // Methods
102 virtual int GetRecpos(void) {return N;}
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);
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 occurence 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/***********************************************************************/
150class FNCCOL : public COLBLK {
151 friend class TDBPIVOT;
152 public:
153 // Constructor
154 FNCCOL(PCOLDEF cdp, PTDB tdbp, PCOL cprec, int i);
155
156 // Implementation
157 virtual int GetAmType(void) {return TYPE_AM_FNC;}
158
159 // Methods
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/***********************************************************************/
173class SRCCOL : public PRXCOL {
174 friend class TDBPIVOT;
175 public:
176 // Constructors
177 SRCCOL(PCOLDEF cdp, PTDB tdbp, PCOL cprec, int n);
178
179 // Implementation
180 virtual int GetAmType(void) {return TYPE_AM_SRC;}
181
182 // Methods
183 using PRXCOL::Init;
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
191 SRCCOL(void) {}
192
193 // Members
194 }; // end of class SRCCOL
195
196PQRYRES 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