· 10 years ago · Sep 15, 2016, 12:18 PM
1-- YP.UPLOADS_HISTORY
2DROP TABLE IF EXISTS yp.uploads_history CASCADE;
3CREATE TABLE yp.uploads_history(
4 id integer DEFAULT "identity"(110343, 0, ('1,1'::character varying)::text) NOT NULL,
5 filename VARCHAR(250),
6 sha512 VARCHAR(250),
7 uploaded_at timestamp
8);
9ALTER TABLE yp.uploads_history
10 ADD CONSTRAINT uploads_history_pkey
11 PRIMARY KEY (id);
12GRANT RULE, INSERT, REFERENCES, UPDATE, TRIGGER, SELECT, DELETE ON yp.uploads_history TO api_writer;
13COMMIT;
14
15-- YP.FACEBOOK
16DROP TABLE IF EXISTS yp.facebook CASCADE;
17CREATE TABLE yp.facebook(
18 id integer DEFAULT "identity"(110343, 0, ('1,1'::character varying)::text) NOT NULL,
19 uploads_history_id INTEGER,
20 account VARCHAR(250),
21 reporting_starts VARCHAR(250),
22 reporting_ends VARCHAR(250),
23 ad_id VARCHAR(250),
24 ad_name VARCHAR(250),
25 ad_set_name VARCHAR(250),
26 campaign_name VARCHAR(250),
27 delivery VARCHAR(250),
28 amount_spent_usd VARCHAR(250),
29 impressions VARCHAR(250),
30 clicks_all VARCHAR(250),
31 link_clicks VARCHAR(250),
32 actions VARCHAR(250),
33 results VARCHAR(250),
34 result_type VARCHAR(250),
35 reach VARCHAR(250),
36 frequency VARCHAR(250),
37 relevance_score VARCHAR(250),
38 cost_per_lead_conversion_pixel_usd VARCHAR(250),
39 registrations_conversion_pixel VARCHAR(250),
40 add_user varchar(250) DEFAULT "current_user"(),
41 add_date timestamp DEFAULT ('now'::text)::date
42);
43ALTER TABLE yp.facebook
44 ADD CONSTRAINT facebook_pkey
45 PRIMARY KEY (id);
46GRANT RULE, INSERT, REFERENCES, UPDATE, TRIGGER, SELECT, DELETE ON yp.facebook TO api_writer;
47COMMIT;
48
49-- YP.SEARCHFORCE
50DROP TABLE IF EXISTS yp.searchforce CASCADE;
51CREATE TABLE yp.searchforce(
52 id integer DEFAULT "identity"(110343, 0, ('1,1'::character varying)::text) NOT NULL,
53 uploads_history_id INTEGER,
54 account_name VARCHAR(250),
55 date VARCHAR(250),
56 publisher_name VARCHAR(250),
57 campaign_name VARCHAR(250),
58 ad_group_name VARCHAR(250),
59 keyword_name VARCHAR(250),
60 match_type VARCHAR(250),
61 max_cpc VARCHAR(250),
62 impressions VARCHAR(250),
63 clicks VARCHAR(250),
64 cost VARCHAR(250),
65 avg_cpc VARCHAR(250),
66 avg_position VARCHAR(250),
67 ctr VARCHAR(250),
68 leads VARCHAR(250),
69 add_user varchar(250) DEFAULT "current_user"(),
70 add_date timestamp DEFAULT ('now'::text)::date
71);
72ALTER TABLE yp.searchforce
73 ADD CONSTRAINT searchforce_pkey
74 PRIMARY KEY (id);
75GRANT RULE, INSERT, REFERENCES, UPDATE, TRIGGER, SELECT, DELETE ON yp.searchforce TO api_writer;
76COMMIT;
77
78-- YP.ADWORDS
79DROP TABLE IF EXISTS yp.adwords CASCADE;
80CREATE TABLE yp.adwords
81(
82 id integer DEFAULT "identity"(110343, 0, ('1,1'::character varying)::text) NOT NULL,
83 uploads_history_id integer,
84 customer_id VARCHAR(12),
85 account varchar(250),
86 day date,
87 ad_state varchar(250),
88 ad_name varchar(250),
89 campaign varchar(250),
90 ad_group varchar(250),
91 status varchar(250),
92 ad_id varchar(250),
93 cost varchar(250),
94 impressions varchar(250),
95 clicks varchar(250),
96 converted_clicks varchar(250),
97 conversions varchar(250),
98 view_through_conversions varchar(250),
99 add_user varchar(250) DEFAULT "current_user"(),
100 add_date timestamp DEFAULT ('now'::text)::date
101);
102ALTER TABLE yp.adwords
103 ADD CONSTRAINT adwords_pkey
104 PRIMARY KEY (id);
105GRANT RULE, INSERT, REFERENCES, UPDATE, TRIGGER, SELECT, DELETE ON yp.adwords TO api_writer;
106COMMIT;
107
108-- YP.SALESFORCE
109DROP TABLE IF EXISTS yp.salesforce CASCADE;
110CREATE TABLE yp.salesforce(
111 id integer DEFAULT "identity"(110343, 0, ('1,1'::character varying)::text) NOT NULL,
112 sid VARCHAR(250),
113 activation_date VARCHAR(250),
114 week_start VARCHAR(250),
115 month_start VARCHAR(250),
116 leadsource VARCHAR(250),
117 combined_lead_type VARCHAR(250),
118 channel VARCHAR(250),
119 combined_mediums VARCHAR(250),
120 email VARCHAR(250),
121 campaign_medium__c VARCHAR(250),
122 campaign_name__c VARCHAR(250),
123 campaign_content__c VARCHAR(250),
124 campaign_source__c VARCHAR(250),
125 campaign_term__c VARCHAR(250),
126 master_lead__c VARCHAR(250),
127 assigned VARCHAR(250),
128 add_user varchar(250) DEFAULT "current_user"(),
129 add_date timestamp DEFAULT ('now'::text)::date
130);
131ALTER TABLE yp.salesforce
132 ADD CONSTRAINT salesforce_pkey
133 PRIMARY KEY (id);
134GRANT RULE, INSERT, REFERENCES, UPDATE, TRIGGER, SELECT, DELETE ON yp.salesforce TO api_writer;
135COMMIT;
136
137-- YP.ELOQUA_BASIC
138DROP TABLE IF EXISTS yp.eloqua_basic CASCADE;
139CREATE TABLE yp.eloqua_basic(
140 id integer DEFAULT "identity"(110343, 0, ('1,1'::character varying)::text) NOT NULL,
141 user_form_submit VARCHAR(250),
142 form_submit_date VARCHAR(250),
143 your_email VARCHAR(250),
144 primary_phone_number_for_your_business VARCHAR(250),
145 business_name VARCHAR(250),
146 business_owner_first_name VARCHAR(250),
147 last_name VARCHAR(250),
148 categories_that_best_describe_your_business VARCHAR(250),
149 business_category_2 VARCHAR(250),
150 business_category_3 VARCHAR(250),
151 business_category_4 VARCHAR(250),
152 business_category_5 VARCHAR(250),
153 street_address VARCHAR(250),
154 city VARCHAR(250),
155 state_or_province VARCHAR(250),
156 zip_or_postal_code VARCHAR(250),
157 year_established VARCHAR(250),
158 campaign_medium VARCHAR(250),
159 campaign_source VARCHAR(250),
160 campaign_name VARCHAR(250),
161 campaign_term VARCHAR(250),
162 campaign_category VARCHAR(250),
163 campaign_content VARCHAR(250),
164 lead_type VARCHAR(250),
165 lead_source_original VARCHAR(250),
166 lead_source_most_recent VARCHAR(250),
167 visitor_id VARCHAR(250),
168 yp_id VARCHAR(250),
169 form_name VARCHAR(250),
170 offer_flow_progress VARCHAR(250),
171 form_url VARCHAR(250),
172 username VARCHAR(250),
173 offer_type VARCHAR(250),
174 eloqua_campaign_id VARCHAR(250),
175 impersonation VARCHAR(250),
176 internal_user VARCHAR(250),
177 add_user varchar(250) DEFAULT "current_user"(),
178 add_date timestamp DEFAULT ('now'::text)::date
179);
180ALTER TABLE yp.eloqua_basic
181 ADD CONSTRAINT eloqua_basic_pkey
182 PRIMARY KEY (id);
183GRANT RULE, INSERT, REFERENCES, UPDATE, TRIGGER, SELECT, DELETE ON yp.eloqua_basic TO api_writer;
184COMMIT;
185
186-- YP.ELOQUA_EBOOK
187DROP TABLE IF EXISTS yp.eloqua_ebook CASCADE;
188CREATE TABLE yp.eloqua_ebook(
189 id integer DEFAULT "identity"(110343, 0, ('1,1'::character varying)::text) NOT NULL,
190 record_id VARCHAR(250),
191 date_submitted VARCHAR(250),
192 first_name VARCHAR(250),
193 last_name VARCHAR(250),
194 company VARCHAR(250),
195 email_address VARCHAR(250),
196 job_function VARCHAR(250),
197 newsletter_signup VARCHAR(250),
198 ebook_id VARCHAR(250),
199 visitor_id VARCHAR(250),
200 lead_type VARCHAR(250),
201 lead_source_original VARCHAR(250),
202 lead_source_most_recent VARCHAR(250),
203 campaign_medium VARCHAR(250),
204 campaign_source VARCHAR(250),
205 campaign_name VARCHAR(250),
206 campaign_term VARCHAR(250),
207 form_url VARCHAR(250),
208 campaign_category VARCHAR(250),
209 campaign_content VARCHAR(250),
210 form_name_original VARCHAR(250),
211 business_phone VARCHAR(250),
212 zip_or_postal_code VARCHAR(250),
213 submit VARCHAR(250),
214 add_user varchar(250) DEFAULT "current_user"(),
215 add_date timestamp DEFAULT ('now'::text)::date
216);
217ALTER TABLE yp.eloqua_ebook
218 ADD CONSTRAINT eloqua_ebook_pkey
219 PRIMARY KEY (id);
220GRANT RULE, INSERT, REFERENCES, UPDATE, TRIGGER, SELECT, DELETE ON yp.eloqua_ebook TO api_writer;
221COMMIT;
222
223-- YP.ELOQUA_INQUIRY
224DROP TABLE IF EXISTS yp.eloqua_inquiry CASCADE;
225CREATE TABLE yp.eloqua_inquiry(
226 id integer DEFAULT "identity"(110343, 0, ('1,1'::character varying)::text) NOT NULL,
227 record_id VARCHAR(250),
228 date_submitted VARCHAR(250),
229 first_name VARCHAR(250),
230 last_name VARCHAR(250),
231 email_address VARCHAR(250),
232 phone_number VARCHAR(250),
233 business_name VARCHAR(250),
234 business_type VARCHAR(250),
235 zip_or_postal_code VARCHAR(250),
236 website VARCHAR(250),
237 business_solution_that_interest_you VARCHAR(250),
238 visitor_id VARCHAR(250),
239 lead_type VARCHAR(250),
240 lead_source_original VARCHAR(250),
241 lead_source_most_recent VARCHAR(250),
242 campaign_medium VARCHAR(250),
243 campaign_source VARCHAR(250),
244 campaign_name VARCHAR(250),
245 campaign_term VARCHAR(250),
246 form_url VARCHAR(250),
247 campaign_category VARCHAR(250),
248 campaign_content VARCHAR(250),
249 form_name VARCHAR(250),
250 elq_campaign_id VARCHAR(250),
251 interest_type VARCHAR(250),
252 submit VARCHAR(250),
253 add_user varchar(250) DEFAULT "current_user"(),
254 add_date timestamp DEFAULT ('now'::text)::date
255);
256ALTER TABLE yp.eloqua_inquiry
257 ADD CONSTRAINT eloqua_inquiry_pkey
258 PRIMARY KEY (id);
259GRANT RULE, INSERT, REFERENCES, UPDATE, TRIGGER, SELECT, DELETE ON yp.eloqua_inquiry TO api_writer;
260COMMIT;
261
262-- YP.ELOQUA_SEM
263DROP TABLE IF EXISTS yp.eloqua_sem CASCADE;
264CREATE TABLE yp.eloqua_sem(
265 id integer DEFAULT "identity"(110343, 0, ('1,1'::character varying)::text) NOT NULL,
266 user_form_submit VARCHAR(250),
267 form_submit_date VARCHAR(250),
268 first_name VARCHAR(250),
269 last_name VARCHAR(250),
270 email_address VARCHAR(250),
271 phone_number VARCHAR(250),
272 business_name VARCHAR(250),
273 business_type VARCHAR(250),
274 zip_or_postal_code VARCHAR(250),
275 no_of_employees VARCHAR(250),
276 advertiser_comments VARCHAR(250),
277 visitor_id VARCHAR(250),
278 lead_type VARCHAR(250),
279 lead_source_original VARCHAR(250),
280 lead_source_most_recent VARCHAR(250),
281 campaign_medium VARCHAR(250),
282 campaign_source VARCHAR(250),
283 campaign_name VARCHAR(250),
284 campaign_term VARCHAR(250),
285 form_url VARCHAR(250),
286 campaign_category VARCHAR(250),
287 campaign_content VARCHAR(250),
288 form_name VARCHAR(250),
289 website VARCHAR(250),
290 redirect VARCHAR(250),
291 add_user varchar(250) DEFAULT "current_user"(),
292 add_date timestamp DEFAULT ('now'::text)::date
293);
294ALTER TABLE yp.eloqua_sem
295 ADD CONSTRAINT eloqua_sem_pkey
296 PRIMARY KEY (id);
297GRANT RULE, INSERT, REFERENCES, UPDATE, TRIGGER, SELECT, DELETE ON yp.eloqua_sem TO api_writer;
298COMMIT;