package profileEngine;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ListIterator;

import dataWarehousingTools.LogFile;

public class Pattern 
{
	
//	Pattern codes:
//	  A:	An alphabetic characters
//	  9:	A numeric characters
//	  s:	A normal space (U+0020)
//	  !:	A punctuation mark
//	  $:	A currency symbol
//	  é:	An accented character
//	  +:	A mathematical or other symbol
//	  ^:	A control character
//	  b:	A non-breaking space (U+00a0)

	public Pattern
	(
		String databaseProduct,
		Connection connection, 
		String databaseName, 
		String schemaName,
		String tableName, 
		String partsInName,
		ColumnList columnList, 
		Metadata metadata,
		String encoding,
		LogFile logFile
	)
	{
		ListIterator<ColumnElement> iterator;
		ColumnElement columnElement;
		Statement statement = null;
		ResultSet resultSet = null;
		int i;
		String pattern = null;;
		String query;
		String tableSpecification;

		if (partsInName.equals("3"))
			tableSpecification = new String(databaseName + "." + schemaName + "." + tableName);
		else if (partsInName.equals("2"))
			tableSpecification = new String(schemaName + "." + tableName);
		else
			tableSpecification = new String(tableName);
		
		for (iterator = columnList.listIterator(); iterator.hasNext();)
		{
			columnElement = (ColumnElement) iterator.next(); 
			//
			// Only run the pattern query for character columns with an actual maximum length <= 64.
			// Short strings are quite likely to be codes, structured keys, numbers stored as text, or dates/times 
			// stored as text, and for these patterns are very revealing.
			// Longer strings are likely to be names or other text for which patterns will be much less interesting
			//
			if (columnElement.getProfileDataType().equals("String"))
			{
//				if (columnElement.getMaxLength() <= 64)
				{			
					try
					{
						statement = connection.createStatement();
					} 
					catch (SQLException e)
					{
						e.printStackTrace();
					}
					
					if (databaseProduct.equals("Apache Hive"))
					{
						query = new String
						(
				 			"select " +
				 			    "pattern, " +
				 			    "count(*) as frequency " +
				 			"from " +
				 			"( " +
							    "select " +
									"translate " +
									"( " +
										columnElement.getColumnName() + ", " +
									    "'\\u0000\\u0001\\u0002\\u0003\\u0004\\u0005\\u0006\\u0007\\u0008\\u0009\\u000A\\u000B\\u000C\\u000D\\u000E\\u000F" +
									     "\\u0010\\u0011\\u0012\\u0013\\u0014\\u0015\\u0016\\u0017\\u0018\\u0019\\u001A\\u001B\\u001C\\u001D\\u001E\\u001F" +
									     "\\u0020\\u0021\\u0022\\u0023\\u0024\\u0025\\u0026\\u0027\\u0028\\u0029\\u002A\\u002B\\u002C\\u002D\\u002E\\u002F" +
									     "\\u0030\\u0031\\u0032\\u0033\\u0034\\u0035\\u0036\\u0037\\u0038\\u0039\\u003A\\u003B\\u003C\\u003D\\u003E\\u003F" +
									     "\\u0040\\u0041\\u0042\\u0043\\u0044\\u0045\\u0046\\u0047\\u0048\\u0049\\u004A\\u004B\\u004C\\u004D\\u004E\\u004F" +
									     "\\u0050\\u0051\\u0052\\u0053\\u0054\\u0055\\u0056\\u0057\\u0058\\u0059\\u005A\\u005B\\u005C\\u005D\\u005E\\u005F" +
									     "\\u0060\\u0061\\u0062\\u0063\\u0064\\u0065\\u0066\\u0067\\u0068\\u0069\\u006A\\u006B\\u006C\\u006D\\u006E\\u006F" +
									     "\\u0070\\u0071\\u0072\\u0073\\u0074\\u0075\\u0076\\u0077\\u0078\\u0079\\u007A\\u007B\\u007C\\u007D\\u007E\\u007F" +
									     "\\u0080\\u0081\\u0082\\u0083\\u0084\\u0085\\u0086\\u0087\\u0088\\u0089\\u008A\\u008B\\u008C\\u008D\\u008E\\u008F" +
									     "\\u0090\\u0091\\u0092\\u0093\\u0094\\u0095\\u0096\\u0097\\u0098\\u0099\\u009A\\u009B\\u009C\\u009D\\u009E\\u009F" +
									     "\\u00A0\\u00A1\\u00A2\\u00A3\\u00A4\\u00A5\\u00A6\\u00A7\\u00A8\\u00A9\\u00AA\\u00AB\\u00AC\\u00AD\\u00AE\\u00AF" +
									     "\\u00B0\\u00B1\\u00B2\\u00B3\\u00B4\\u00B5\\u00B6\\u00B7\\u00B8\\u00B9\\u00BA\\u00BB\\u00BC\\u00BD\\u00BE\\u00BF" +
									     "\\u00C0\\u00C1\\u00C2\\u00C3\\u00C4\\u00C5\\u00C6\\u00C7\\u00C8\\u00C9\\u00CA\\u00CB\\u00CC\\u00CD\\u00CE\\u00CF" +
									     "\\u00D0\\u00D1\\u00D2\\u00D3\\u00D4\\u00D5\\u00D6\\u00D7\\u00D8\\u00D9\\u00DA\\u00DB\\u00DC\\u00DD\\u00DE\\u00DF" +
									     "\\u00E0\\u00E1\\u00E2\\u00E3\\u00E4\\u00E5\\u00E6\\u00E7\\u00E8\\u00E9\\u00EA\\u00EB\\u00EC\\u00ED\\u00EE\\u00EF" +
									     "\\u00F0\\u00F1\\u00F2\\u00F3\\u00F4\\u00F5\\u00F6\\u00F7\\u00F8\\u00F9\\u00FA\\u00FB\\u00FC\\u00FD\\u00FE\\u00FF', " +
//									      0123456789ABCDEF0123456789ABCDEF
									    "'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^" +    // 0, 1
									     "s!!!C!!!!!!+!!!!9999999999!!!!!!" +    // 2, 3
									     "!AAAAAAAAAAAAAAAAAAAAAAAAAA!!!!!" +    // 4, 5
									     "!AAAAAAAAAAAAAAAAAAAAAAAAAA!!!!^" +    // 6, 7
									     "WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW" +    // 8, 9
									     "b!CCCCF+F+++++++++++F+++F+++FFF!" +    // A, B
									     "FFFFFFFFFFFFFFFFFFFFFFF+FFFFFFFF" +    // C, D
									     "FFFFFFFFFFFFFFFFFFFFFFF+FFFFFFFF' " +  // E, F
									") as pattern " +
								"from " +
									tableSpecification + " " +
						    ") as a " +
						    "group by " +
							    "pattern" + " " +
							"order by " +
							    "frequency desc"
						);
					}
					else if ((databaseProduct.equals("PostgreSQL")) && encoding.equals("UTF8"))
					{
						query = new String
						(
				 			"select " +
				 			    "a.pattern, " +
				 			    "count(*) as frequency " +
				 			"from " +
				 			"( " +
							    "select " +
									"translate " +
									"( " +
										"substring(" + columnElement.getColumnName() + " from 1 for 64), " +
				                             "E'\\u0001\\u0002\\u0003\\u0004\\u0005\\u0006\\u0007\\u0008\\u0009\\u000a\\u000b\\u000c\\u000d\\u000e\\u000f' || " +
				                      "E'\\u0010\\u0011\\u0012\\u0013\\u0014\\u0015\\u0016\\u0017\\u0018\\u0019\\u001a\\u001b\\u001c\\u001d\\u001e\\u001f' || " +
				                      "E'\\u0020\\u0021\\u0022\\u0023\\u0024\\u0025\\u0026\\u0027\\u0028\\u0029\\u002a\\u002b\\u002c\\u002d\\u002e\\u002f' || " +
				                      "E'\\u0030\\u0031\\u0032\\u0033\\u0034\\u0035\\u0036\\u0037\\u0038\\u0039\\u003a\\u003b\\u003c\\u003d\\u003e\\u003f' || " +
				                      "E'\\u0040\\u0041\\u0042\\u0043\\u0044\\u0045\\u0046\\u0047\\u0048\\u0049\\u004a\\u004b\\u004c\\u004d\\u004e\\u004f' || " +
				                      "E'\\u0050\\u0051\\u0052\\u0053\\u0054\\u0055\\u0056\\u0057\\u0058\\u0059\\u005a\\u005b\\u005c\\u005d\\u005e\\u005f' || " +
				                      "E'\\u0060\\u0061\\u0062\\u0063\\u0064\\u0065\\u0066\\u0067\\u0068\\u0069\\u006a\\u006b\\u006c\\u006d\\u006e\\u006f' || " +
				                      "E'\\u0070\\u0071\\u0072\\u0073\\u0074\\u0075\\u0076\\u0077\\u0078\\u0079\\u007a\\u007b\\u007c\\u007d\\u007e\\u007f' || " +
				                      "E'\\u0080\\u0081\\u0082\\u0083\\u0084\\u0085\\u0086\\u0087\\u0088\\u0089\\u008a\\u008b\\u008c\\u008d\\u008e\\u008f' || " +
				                      "E'\\u0090\\u0091\\u0092\\u0093\\u0094\\u0095\\u0096\\u0097\\u0098\\u0099\\u009a\\u009b\\u009c\\u009d\\u009e\\u009f' || " +
				                      "E'\\u00a0\\u00a1\\u00a2\\u00a3\\u00a4\\u00a5\\u00a6\\u00a7\\u00a8\\u00a9\\u00aa\\u00ab\\u00ac\\u00ad\\u00ae\\u00af' || " +
				                      "E'\\u00b0\\u00b1\\u00b2\\u00b3\\u00b4\\u00b5\\u00b6\\u00b7\\u00b8\\u00b9\\u00ba\\u00bb\\u00bc\\u00bd\\u00be\\u00bf' || " +
				                      "E'\\u00c0\\u00c1\\u00c2\\u00c3\\u00c4\\u00c5\\u00c6\\u00c7\\u00c8\\u00c9\\u00ca\\u00cb\\u00cc\\u00cd\\u00ce\\u00cf' || " +
				                      "E'\\u00d0\\u00d1\\u00d2\\u00d3\\u00d4\\u00d5\\u00d6\\u00d7\\u00d8\\u00d9\\u00da\\u00db\\u00dc\\u00dd\\u00de\\u00df' || " +
				                      "E'\\u00e0\\u00e1\\u00e2\\u00e3\\u00e4\\u00e5\\u00e6\\u00e7\\u00e8\\u00e9\\u00ea\\u00eb\\u00ec\\u00ed\\u00ee\\u00ef' || " +
				                      "E'\\u00f0\\u00f1\\u00f2\\u00f3\\u00f4\\u00f5\\u00f6\\u00f7\\u00f8\\u00f9\\u00fa\\u00fb\\u00fc\\u00fd\\u00fe\\u00ff' || " +
				                      "E'\\u0100\\u0101\\u0102\\u0103\\u0104\\u0105\\u0106\\u0107\\u0108\\u0109\\u010a\\u010b\\u010c\\u010d\\u010e\\u010f' || " +
				                      "E'\\u0110\\u0111\\u0112\\u0113\\u0114\\u0115\\u0116\\u0117\\u0118\\u0119\\u011a\\u011b\\u011c\\u011d\\u011e\\u011f' || " +
				                      "E'\\u0120\\u0121\\u0122\\u0123\\u0124\\u0125\\u0126\\u0127\\u0128\\u0129\\u012a\\u012b\\u012c\\u012d\\u012e\\u012f' || " +
				                      "E'\\u0130\\u0131\\u0132\\u0133\\u0134\\u0135\\u0136\\u0137\\u0138\\u0139\\u013a\\u013b\\u013c\\u013d\\u013e\\u013f' || " +
				                      "E'\\u0140\\u0141\\u0142\\u0143\\u0144\\u0145\\u0146\\u0147\\u0148\\u0149\\u014a\\u014b\\u014c\\u014d\\u014e\\u014f' || " +
				                      "E'\\u0150\\u0151\\u0152\\u0153\\u0154\\u0155\\u0156\\u0157\\u0158\\u0159\\u015a\\u015b\\u015c\\u015d\\u015e\\u015f' || " +
				                      "E'\\u0160\\u0161\\u0162\\u0163\\u0164\\u0165\\u0166\\u0167\\u0168\\u0169\\u016a\\u016b\\u016c\\u016d\\u016e\\u016f' || " +
				                      "E'\\u0170\\u0171\\u0172\\u0173\\u0174\\u0175\\u0176\\u0177\\u0178\\u0179\\u017a\\u017b\\u017c\\u017d\\u017e\\u017f' || " +
				                      "E'\\u0180\\u0181\\u0182\\u0183\\u0184\\u0185\\u0186\\u0187\\u0188\\u0189\\u018a\\u018b\\u018c\\u018d\\u018e\\u018f' || " +
				                      "E'\\u0190\\u0191\\u0192\\u0193\\u0194\\u0195\\u0196\\u0197\\u0198\\u0199\\u019a\\u019b\\u019c\\u019d\\u019e\\u019f' || " +
				                      "E'\\u01a0\\u01a1\\u01a2\\u01a3\\u01a4\\u01a5\\u01a6\\u01a7\\u01a8\\u01a9\\u01aa\\u01ab\\u01ac\\u01ad\\u01ae\\u01af' || " +
				                      "E'\\u01b0\\u01b1\\u01b2\\u01b3\\u01b4\\u01b5\\u01b6\\u01b7\\u01b8\\u01b9\\u01ba\\u01bb\\u01bc\\u01bd\\u01be\\u01bf' || " +
				                      "E'\\u01c0\\u01c1\\u01c2\\u01c3\\u01c4\\u01c5\\u01c6\\u01c7\\u01c8\\u01c9\\u01ca\\u01cb\\u01cc\\u01cd\\u01ce\\u01cf' || " +
				                      "E'\\u01d0\\u01d1\\u01d2\\u01d3\\u01d4\\u01d5\\u01d6\\u01d7\\u01d8\\u01d9\\u01da\\u01db\\u01dc\\u01dd\\u01de\\u01df' || " +
				                      "E'\\u01e0\\u01e1\\u01e2\\u01e3\\u01e4\\u01e5\\u01e6\\u01e7\\u01e8\\u01e9\\u01ea\\u01eb\\u01ec\\u01ed\\u01ee\\u01ef' || " +
				                      "E'\\u01f0\\u01f1\\u01f2\\u01f3\\u01f4\\u01f5\\u01f6\\u01f7\\u01f8\\u01f9\\u01fa\\u01fb\\u01fc\\u01fd\\u01fe\\u01ff' || " +
				                      "E'\\u0200\\u0201\\u0202\\u0203\\u0204\\u0205\\u0206\\u0207\\u0208\\u0209\\u020a\\u020b\\u020c\\u020d\\u020e\\u020f' || " +
				                      "E'\\u0210\\u0211\\u0212\\u0213\\u0214\\u0215\\u0216\\u0217\\u0218\\u0219\\u021a\\u021b\\u021c\\u021d\\u021e\\u021f' || " +
				                      "E'\\u0220\\u0221\\u0222\\u0223\\u0224\\u0225\\u0226\\u0227\\u0228\\u0229\\u022a\\u022b\\u022c\\u022d\\u022e\\u022f' || " +
				                      "E'\\u0230\\u0231\\u0232\\u0233\\u0234\\u0235\\u0236\\u0237\\u0238\\u0239\\u023a\\u023b\\u023c\\u023d\\u023e\\u023f' || " +
				                      "E'\\u0240\\u0241\\u0242\\u0243\\u0244\\u0245\\u0246\\u0247\\u0248\\u0249\\u024a\\u024b\\u024c\\u024d\\u024e\\u024f', " +
				                      " '^^^^^^^^^^^^^^^' || " +
				                      "'^^^^^^^^^^^^^^^^' || " +
				                      "'s!!!$!!!!!!+!!!!' || " +
				                      "'9999999999!!+++!' || " +
				                      "'!AAAAAAAAAAAAAAA' || " +
				                      "'AAAAAAAAAAA!!!+!' || " +
				                      "'!AAAAAAAAAAAAAAA' || " +
				                      "'AAAAAAAAAAA!!!!^' || " +
				                      "'^^^^^^^^^^^^^^^^' || " +
				                      "'^^^^^^^^^^^^^^^^' || " +
				                      "'b!$$$$!!!+!!+!+!' || " +
				                      "'++++++!!!+!!+++!' || " +
				                      "'éééééééééééééééé' || " +
				                      "'ééééééé+éééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'ééééééé+éééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'ééééééé99ééééééé' || " +
				                      "'ééééééééééé999éé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' || " +
				                      "'éééééééééééééééé' " +
									") as pattern " +
								"from " +
									tableSpecification + " " +
							    ") as a " +
						    "group by " +
							    "pattern" + " " +
							"order by " +
							    "frequency desc"
						);						
					}
					else if ((databaseProduct.equals("PostgreSQL")) && encoding.equals("WIN1252"))
					{
						query = new String
						(
				 			"select " +
				 			    "a.pattern, " +
				 			    "count(*) as frequency " +
				 			"from " +
				 			"( " +
							    "select " +
									"translate " +
									"( " +
										"substring(" + columnElement.getColumnName() + " from 1 for 64), " +
										"               chr(1)  ||   chr(2)  ||   chr(3)  ||   chr(4)  ||   chr(5)  ||   chr(6)  ||   chr(7)  || " +
										"  chr(8)  ||   chr(9)  ||  chr(10)  ||  chr(11)  ||  chr(12)  ||  chr(13)  ||  chr(14)  ||  chr(15)  || " +
									    " chr(16)  ||  chr(17)  ||  chr(18)  ||  chr(19)  ||  chr(20)  ||  chr(21)  ||  chr(22)  ||  chr(23)  || " +
									    " chr(24)  ||  chr(25)  ||  chr(26)  ||  chr(27)  ||  chr(28)  ||  chr(29)  ||  chr(30)  ||  chr(31)  || " +
									    " chr(32)  ||  chr(33)  ||  chr(34)  ||  chr(35)  ||  chr(36)  ||  chr(37)  ||  chr(38)  ||  chr(39)  || " +
									    " chr(40)  ||  chr(41)  ||  chr(42)  ||  chr(43)  ||  chr(44)  ||  chr(45)  ||  chr(46)  ||  chr(47)  || " +
									    " chr(48)  ||  chr(49)  ||  chr(50)  ||  chr(51)  ||  chr(52)  ||  chr(53)  ||  chr(54)  ||  chr(55)  || " +
									    " chr(56)  ||  chr(57)  ||  chr(58)  ||  chr(59)  ||  chr(60)  ||  chr(61)  ||  chr(62)  ||  chr(63)  || " +
									    " chr(64)  ||  chr(65)  ||  chr(66)  ||  chr(67)  ||  chr(68)  ||  chr(69)  ||  chr(70)  ||  chr(71)  || " +
									    " chr(72)  ||  chr(73)  ||  chr(74)  ||  chr(75)  ||  chr(76)  ||  chr(77)  ||  chr(78)  ||  chr(79)  || " +
									    " chr(80)  ||  chr(81)  ||  chr(82)  ||  chr(83)  ||  chr(84)  ||  chr(85)  ||  chr(86)  ||  chr(87)  || " +
									    " chr(88)  ||  chr(89)  ||  chr(90)  ||  chr(91)  ||  chr(92)  ||  chr(93)  ||  chr(94)  ||  chr(95)  || " +
									    " chr(96)  ||  chr(97)  ||  chr(98)  ||  chr(99)  || chr(100)  || chr(101)  || chr(102)  || chr(103)  || " +
									    "chr(104)  || chr(105)  || chr(106)  || chr(107)  || chr(108)  || chr(109)  || chr(110)  || chr(111)  || " +
									    "chr(112)  || chr(113)  || chr(114)  || chr(115)  || chr(116)  || chr(117)  || chr(118)  || chr(119)  || " +
									    "chr(120)  || chr(121)  || chr(122)  || chr(123)  || chr(124)  || chr(125)  || chr(126)  || chr(127)  || " +
									    "chr(128)  || chr(129)  || chr(130)  || chr(131)  || chr(132)  || chr(133)  || chr(134)  || chr(135)  || " +
									    "chr(136)  || chr(137)  || chr(138)  || chr(139)  || chr(140)  || chr(141)  || chr(142)  || chr(143)  || " +
									    "chr(144)  || chr(145)  || chr(146)  || chr(147)  || chr(148)  || chr(149)  || chr(150)  || chr(151)  || " +
									    "chr(152)  || chr(153)  || chr(154)  || chr(155)  || chr(156)  || chr(157)  || chr(158)  || chr(159)  || " +
									    "chr(160)  || chr(161)  || chr(162)  || chr(163)  || chr(164)  || chr(165)  || chr(166)  || chr(167)  || " +
									    "chr(168)  || chr(169)  || chr(170)  || chr(171)  || chr(172)  || chr(173)  || chr(174)  || chr(175)  || " +
									    "chr(176)  || chr(177)  || chr(178)  || chr(179)  || chr(180)  || chr(181)  || chr(182)  || chr(183)  || " +
									    "chr(184)  || chr(185)  || chr(186)  || chr(187)  || chr(188)  || chr(189)  || chr(190)  || chr(191)  || " +
									    "chr(192)  || chr(193)  || chr(194)  || chr(195)  || chr(196)  || chr(197)  || chr(198)  || chr(199)  || " +
									    "chr(200)  || chr(201)  || chr(202)  || chr(203)  || chr(204)  || chr(205)  || chr(206)  || chr(207)  || " +
									    "chr(208)  || chr(209)  || chr(210)  || chr(211)  || chr(212)  || chr(213)  || chr(214)  || chr(215)  || " +
									    "chr(216)  || chr(217)  || chr(218)  || chr(219)  || chr(220)  || chr(221)  || chr(222)  || chr(223)  || " +
									    "chr(224)  || chr(225)  || chr(226)  || chr(227)  || chr(228)  || chr(229)  || chr(230)  || chr(231)  || " +
									    "chr(232)  || chr(233)  || chr(234)  || chr(235)  || chr(236)  || chr(237)  || chr(238)  || chr(239)  || " +
									    "chr(240)  || chr(241)  || chr(242)  || chr(243)  || chr(244)  || chr(245)  || chr(246)  || chr(247)  || " +
									    "chr(248)  || chr(249)  || chr(250)  || chr(251)  || chr(252)  || chr(253)  || chr(254)  || chr(255), " +
//									      0123456789ABCDEF0123456789ABCDEF
									    " '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^' || " +    // 0, 1
									    "'s!!!$!!!!!!+!!!!9999999999!!+++!' || " +    // 2, 3
									    "'!AAAAAAAAAAAAAAAAAAAAAAAAAA!!!+!' || " +    // 4, 5
									    "'!AAAAAAAAAAAAAAAAAAAAAAAAAA!!!!^' || " +    // 6, 7
									    "'$X!+!!!!++é!éXéXX!!!!!!!++é!éXéé' || " +    // 8, 9
									    "'b!$$$$++é++!+^+é++++é+++é++!+++!' || " +    // A, B
								        "'ééééééééééééééééééééééé+éééééééé' || " +    // C, D
							            "'ééééééééééééééééééééééé+éééééééé' " +       // E, F
									") as pattern " +
								"from " +
									tableSpecification + " " +
						    ") as a " +
						    "group by " +
							    "pattern" + " " +
							"order by " +
							    "frequency desc"
						);
					}
					else if (databaseProduct.equals("Microsoft SQL Server"))    // Assume encoding.equals("WIN1252")) - DODGY!
					{
						query = new String
						(
				 			"select " +
				 			    "a.pattern, " +
				 			    "count(*) as frequency " +
				 			"from " +
				 			"( " +
							    "select " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( replace ( " +
								    "replace ( replace ( replace ( replace (replace ( replace ( replace ( " +
								    "substring(" + columnElement.getColumnName() + ", 1, 64), " + 
								    "                   char(1), '^'),   char(2), '^'),   char(3), '^'),  " +
								    "  char(4), '^'),   char(5), '^'),   char(6), '^'),   char(7), '^'), " +
								    "  char(8), '^'),   char(9), '^'),  char(10), '^'),  char(11), '^'), " +
								    " char(12), '^'),  char(13), '^'),  char(14), '^'),  char(15), '^'), " +
								    " char(16), '^'),  char(17), '^'),  char(18), '^'),  char(19), '^'), " +
								    " char(20), '^'),  char(21), '^'),  char(22), '^'),  char(23), '^'), " +
								    " char(24), '^'),  char(25), '^'),  char(26), '^'),  char(27), '^'), " +
								    " char(28), '^'),  char(29), '^'),  char(30), '^'),  char(31), '^'), " +
//								    "--  s!!!$!!!!!!+!!!!9999999999!!+++! " +
								    "                  char(33), '!'),  char(34), '!'),  char(35), '!'), " +    // Do space, char(32), at end
								    " char(36), '$'),  char(37), '!'),  char(38), '!'),  char(39), '!'), " +
								    " char(40), '!'),  char(41), '!'),  char(42), '!'),  char(43), '+'), " +
								    " char(44), '!'),  char(45), '!'),  char(46), '!'),  char(47), '!'), " +
								    " char(48), '9'),  char(49), '9'),  char(50), '9'),  char(51), '9'), " +
								    " char(52), '9'),  char(53), '9'),  char(54), '9'),  char(55), '9'), " +
								    " char(56), '9'),  char(57), '9'),  char(58), '!'),  char(59), '!'), " +
								    " char(60), '+'),  char(61), '+'),  char(62), '+'),   char(63), '!'), " +
//								    "--  !AAAAAAAAAAAAAAAAAAAAAAAAAA!!!+! " +
								    " char(64), '!'),  char(65), 'A'),  char(66), 'A'),  char(67), 'A'), " +
								    " char(68), 'A'),  char(69), 'A'),  char(70), 'A'),  char(71), 'A'), " +
								    " char(72), 'A'),  char(73), 'A'),  char(74), 'A'),  char(75), 'A'), " +
								    " char(76), 'A'),  char(77), 'A'),  char(78), 'A'),  char(79), 'A'), " +
								    " char(80), 'A'),  char(81), 'A'),  char(82), 'A'),  char(83), 'A'), " +
								    " char(84), 'A'),  char(85), 'A'),  char(86), 'A'),  char(87), 'A'), " +
								    " char(88), 'A'),  char(89), 'A'),  char(90), 'A'),  char(91), '!'), " +
								    " char(92), '!'),  char(93), '!'),  char(94), '+'),  char(95), '!'), " +
//								    "--  !AAAAAAAAAAAAAAAAAAAAAAAAAA!!!!^ " +
								    " char(96), '!'),  char(97), 'A'),  char(98), 'A'),  char(99), 'A'), " +
								    "char(100), 'A'), char(101), 'A'), char(102), 'A'), char(103), 'A'), " +
								    "char(104), 'A'), char(105), 'A'), char(106), 'A'), char(107), 'A'), " +
								    "char(108), 'A'), char(109), 'A'), char(110), 'A'), char(111), 'A'), " +
								    "char(112), 'A'), char(113), 'A'), char(114), 'A'), char(115), 'A'), " +
								    "char(116), 'A'), char(117), 'A'), char(118), 'A'), char(119), 'A'), " +
								    "char(120), 'A'), char(121), 'A'), char(122), 'A'), char(123), '!'), " +
								    "char(124), '!'), char(125), '!'), char(126), '!'), char(127), '^'), " +
//								    "--  $X!+!!!!++é!éXéXX!!!!!!!++é!éXéé " +
								    "char(128), '$'), char(129), 'X'), char(130), '!'), char(131), '+'), " +
								    "char(132), '!'), char(133), '!'), char(134), '!'), char(135), '!'), " +
								    "char(136), '+'), char(137), '+'), char(138), 'é'), char(139), '!'), " +
								    "char(140), 'é'), char(141), 'X'), char(142), 'é'), char(143), 'X'), " +
								    "char(144), 'X'), char(145), '!'), char(146), '!'), char(147), '!'), " +
								    "char(148), '!'), char(149), '!'), char(150), '!'), char(151), '!'), " +
								    "char(152), '+'), char(153), '+'), char(154), 'é'), char(155), '!'), " +
								    "char(156), 'é'), char(157), 'X'), char(158), 'é'), char(159), 'é'), " +
//								    "--  b!$$$$++é++!+^+é++++é+++é++!+++! " +
								    "                 char(161), '!'), char(162), '$'), char(163), '$'), " +   // Do no-break space (char(160) at end
								    "char(164), '$'), char(165), '$'), char(166), '+'), char(167), '+'), " +
								    "char(168), 'é'), char(169), '+'), char(170), '+'), char(171), '!'), " +
								    "char(172), '+'), char(173), '^'), char(174), '+'), char(175), 'é'), " +
								    "char(176), '+'), char(177), '+'), char(178), '+'), char(179), '+'), " +
								    "char(180), 'é'), char(181), '+'), char(182), '+'), char(183), '+'), " +
								    "char(184), 'é'), char(185), '+'), char(186), '+'), char(187), '!'), " +
								    "char(188), '+'), char(189), '+'), char(190), '+'), char(191), '!'), " +
//								    "--   ééééééééééééééééééééééé+éééééééé " +
								    "char(192), 'é'), char(193), 'é'), char(194), 'é'), char(195), 'é'), " +
								    "char(196), 'é'), char(197), 'é'), char(198), 'é'), char(199), 'é'), " +
								    "char(200), 'é'), char(201), 'é'), char(202), 'é'), char(203), 'é'), " +
								    "char(204), 'é'), char(205), 'é'), char(206), 'é'), char(207), 'é'), " +
								    "char(208), 'é'), char(209), 'é'), char(210), 'é'), char(211), 'é'), " +
								    "char(212), 'é'), char(213), 'é'), char(214), 'é'), char(215), '+'), " +
								    "char(216), 'é'), char(217), 'é'), char(218), 'é'), char(219), 'é'), " +
								    "char(220), 'é'), char(221), 'é'), char(222), 'é'), char(223), 'é'), " +
//								    "--   ééééééééééééééééééééééé+éééééééé " +
								    "char(224), 'é'), char(225), 'é'), char(226), 'é'), char(227), 'é'), " +
								    "char(228), 'é'), char(229), 'é'), char(230), 'é'), char(231), 'é'), " +
								    "char(232), 'é'), char(233), 'é'), char(234), 'é'), char(235), 'é'), " +
								    "char(236), 'é'), char(237), 'é'), char(238), 'é'), char(239), 'é'), " +
								    "char(240), 'é'), char(241), 'é'), char(242), 'é'), char(243), 'é'), " +
								    "char(244), 'é'), char(245), 'é'), char(246), 'é'), char(247), '+'), " +
								    "char(248), 'é'), char(249), 'é'), char(250), 'é'), char(251), 'é'), " +
								    "char(252), 'é'), char(253), 'é'), char(254), 'é'), char(255), 'é'), " +
								    " char(32), 's'),  " +       // Do space at end because if we change it to s it then gets changed to A
								    " char(160), 'b') " +        // Do no-break space at end because if we change it to b it then gets changed to A
									" as pattern " +
								"from " +
									tableSpecification + " " +
						    ") as a " +
						    "group by " +
							    "pattern" + " " +
							"order by " +
							    "frequency desc"
						);
					}
					else
					{
						query = new String
						(
				 			"select " +
				 			    "a.pattern, " +
				 			    "count(*) as frequency " +
				 			"from " +
				 			"( " +
							    "select " +
									"translate " +
									"( " +
										"substring(" + columnElement.getColumnName() + " from 1 for 64), " +
										"               chr(1)  ||   chr(2)  ||   chr(3)  ||   chr(4)  ||   chr(5)  ||   chr(6)  ||   chr(7)  || " +
										"  chr(8)  ||   chr(9)  ||  chr(10)  ||  chr(11)  ||  chr(12)  ||  chr(13)  ||  chr(14)  ||  chr(15)  || " +
									    " chr(16)  ||  chr(17)  ||  chr(18)  ||  chr(19)  ||  chr(20)  ||  chr(21)  ||  chr(22)  ||  chr(23)  || " +
									    " chr(24)  ||  chr(25)  ||  chr(26)  ||  chr(27)  ||  chr(28)  ||  chr(29)  ||  chr(30)  ||  chr(31)  || " +
									    " chr(32)  ||  chr(33)  ||  chr(34)  ||  chr(35)  ||  chr(36)  ||  chr(37)  ||  chr(38)  ||  chr(39)  || " +
									    " chr(40)  ||  chr(41)  ||  chr(42)  ||  chr(43)  ||  chr(44)  ||  chr(45)  ||  chr(46)  ||  chr(47)  || " +
									    " chr(48)  ||  chr(49)  ||  chr(50)  ||  chr(51)  ||  chr(52)  ||  chr(53)  ||  chr(54)  ||  chr(55)  || " +
									    " chr(56)  ||  chr(57)  ||  chr(58)  ||  chr(59)  ||  chr(60)  ||  chr(61)  ||  chr(62)  ||  chr(63)  || " +
									    " chr(64)  ||  chr(65)  ||  chr(66)  ||  chr(67)  ||  chr(68)  ||  chr(69)  ||  chr(70)  ||  chr(71)  || " +
									    " chr(72)  ||  chr(73)  ||  chr(74)  ||  chr(75)  ||  chr(76)  ||  chr(77)  ||  chr(78)  ||  chr(79)  || " +
									    " chr(80)  ||  chr(81)  ||  chr(82)  ||  chr(83)  ||  chr(84)  ||  chr(85)  ||  chr(86)  ||  chr(87)  || " +
									    " chr(88)  ||  chr(89)  ||  chr(90)  ||  chr(91)  ||  chr(92)  ||  chr(93)  ||  chr(94)  ||  chr(95)  || " +
									    " chr(96)  ||  chr(97)  ||  chr(98)  ||  chr(99)  || chr(100)  || chr(101)  || chr(102)  || chr(103)  || " +
									    "chr(104)  || chr(105)  || chr(106)  || chr(107)  || chr(108)  || chr(109)  || chr(110)  || chr(111)  || " +
									    "chr(112)  || chr(113)  || chr(114)  || chr(115)  || chr(116)  || chr(117)  || chr(118)  || chr(119)  || " +
									    "chr(120)  || chr(121)  || chr(122)  || chr(123)  || chr(124)  || chr(125)  || chr(126)  || chr(127)  || " +
									    "chr(128)  || chr(129)  || chr(130)  || chr(131)  || chr(132)  || chr(133)  || chr(134)  || chr(135)  || " +
									    "chr(136)  || chr(137)  || chr(138)  || chr(139)  || chr(140)  || chr(141)  || chr(142)  || chr(143)  || " +
									    "chr(144)  || chr(145)  || chr(146)  || chr(147)  || chr(148)  || chr(149)  || chr(150)  || chr(151)  || " +
									    "chr(152)  || chr(153)  || chr(154)  || chr(155)  || chr(156)  || chr(157)  || chr(158)  || chr(159)  || " +
									    "chr(160)  || chr(161)  || chr(162)  || chr(163)  || chr(164)  || chr(165)  || chr(166)  || chr(167)  || " +
									    "chr(168)  || chr(169)  || chr(170)  || chr(171)  || chr(172)  || chr(173)  || chr(174)  || chr(175)  || " +
									    "chr(176)  || chr(177)  || chr(178)  || chr(179)  || chr(180)  || chr(181)  || chr(182)  || chr(183)  || " +
									    "chr(184)  || chr(185)  || chr(186)  || chr(187)  || chr(188)  || chr(189)  || chr(190)  || chr(191)  || " +
									    "chr(192)  || chr(193)  || chr(194)  || chr(195)  || chr(196)  || chr(197)  || chr(198)  || chr(199)  || " +
									    "chr(200)  || chr(201)  || chr(202)  || chr(203)  || chr(204)  || chr(205)  || chr(206)  || chr(207)  || " +
									    "chr(208)  || chr(209)  || chr(210)  || chr(211)  || chr(212)  || chr(213)  || chr(214)  || chr(215)  || " +
									    "chr(216)  || chr(217)  || chr(218)  || chr(219)  || chr(220)  || chr(221)  || chr(222)  || chr(223)  || " +
									    "chr(224)  || chr(225)  || chr(226)  || chr(227)  || chr(228)  || chr(229)  || chr(230)  || chr(231)  || " +
									    "chr(232)  || chr(233)  || chr(234)  || chr(235)  || chr(236)  || chr(237)  || chr(238)  || chr(239)  || " +
									    "chr(240)  || chr(241)  || chr(242)  || chr(243)  || chr(244)  || chr(245)  || chr(246)  || chr(247)  || " +
									    "chr(248)  || chr(249)  || chr(250)  || chr(251)  || chr(252)  || chr(253)  || chr(254)  || chr(255), " +
//									      0123456789ABCDEF0123456789ABCDEF
									    " '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^' || " +    // 0, 1
									    "'s!!!C!!!!!!+!!!!9999999999!!!!!!' || " +    // 2, 3
									    "'!AAAAAAAAAAAAAAAAAAAAAAAAAA!!!!!' || " +    // 4, 5
									    "'!AAAAAAAAAAAAAAAAAAAAAAAAAA!!!!^' || " +    // 6, 7
									    "'WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW' || " +    // 8, 9
									    "'b!CCCCF+F+++++++++++F+++F+++FFF!' || " +    // A, B
								        "'FFFFFFFFFFFFFFFFFFFFFFF+FFFFFFFF' || " +    // C, D
							            "'FFFFFFFFFFFFFFFFFFFFFFF+FFFFFFFF' " +       // E, F
									") as pattern " +
								"from " +
									tableSpecification + " " +
						    ") as a " +
						    "group by " +
							    "pattern" + " " +
							"order by " +
							    "frequency desc"
						);
						
					}
					
					logFile.logMessage(query);
					
					try 
					{
						statement.setMaxRows(100);
					} 
					catch (SQLException e1) 
					{
						e1.printStackTrace();
					}
					
					try
					{
						resultSet = statement.executeQuery(query);
					} 
					catch (SQLException e)
					{
						e.printStackTrace();
					}
					
					i = 0;
					try
					{
						while(resultSet.next())
						{
							if (i > 100)
								break;
							i++;
	            			pattern = null;
		            		try
		            		{
		            			pattern = new String(resultSet.getString("pattern"));
		            		}
		            		catch (NullPointerException e)
		            		{
		            			if (resultSet.wasNull())
		            				pattern = new String("<null>");
		            			else
		            				e.printStackTrace();
		            		}
							
							metadata.insertPattern
							(
								columnElement.getColumnProfileId(),
								pattern,
								resultSet.getLong("frequency")
							);
						}
					} 
					catch (SQLException e)
					{
						e.printStackTrace();
					}
				}
			}
		}
	}
}