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