[XAPID-1037] move sql queries to different file
diff --git a/data.go b/data.go
index 695ef8e..6270b43 100644
--- a/data.go
+++ b/data.go
@@ -59,42 +59,13 @@
getApiKeyDetails(dataWrapper *VerifyApiKeyRequestResponseDataWrapper) error
}
-func (dbc *dbManager) getKmsAttributesOld(tenantId, entityId string) []Attribute {
-
- db := dbc.db
- var attName, attValue sql.NullString
- sql := "select name, value from kms_attributes where tenant_id = $1 and entity_id = $2"
- attributesForQuery := []Attribute{}
- attributes, err := db.Query(sql, tenantId, entityId)
- if err != nil {
- log.Error("Error while fetching attributes for tenant id : %s and entityId : %s", tenantId, entityId, err)
- return attributesForQuery
- }
-
- for attributes.Next() {
- err := attributes.Scan(
- &attName,
- &attValue,
- )
- if err != nil {
- log.Error("error fetching attributes for entityid ", entityId, err)
- }
- if attName.String != "" {
- att := Attribute{Name: attName.String, Value: attValue.String}
- attributesForQuery = append(attributesForQuery, att)
- }
- }
- log.Debug("attributes returned for query ", sql, " are ", attributesForQuery, tenantId, entityId)
- return attributesForQuery
-}
-
func (dbc *dbManager) getKmsAttributes(tenantId string, entities ...string) map[string][]Attribute {
db := dbc.db
var attName, attValue sql.NullString
var entity_id string
// TODO : is there no other better way to do in caluse???
- sql := `select entity_id, name, value from kms_attributes where tenant_id = $1 and entity_id in ('` + strings.Join(entities, `','`) + `')`
+ sql := sql_GET_KMS_ATTRIBUTES_FOR_TENANT + ` and entity_id in ('` + strings.Join(entities, `','`) + `')`
mapOfAttributes := make(map[string][]Attribute)
attributes, err := db.Query(sql, tenantId)
if err != nil {
@@ -122,105 +93,8 @@
func (dbc dbManager) getApiKeyDetails(dataWrapper *VerifyApiKeyRequestResponseDataWrapper) error {
db := dbc.db
- sSql := `
- SELECT
- COALESCE("developer","") as ctype,
- COALESCE(c.tenant_id,""),
- COALESCE(c.status,""),
- COALESCE(c.consumer_secret,""),
-
- COALESCE(ad.id,"") as dev_id,
- COALESCE(ad.username,"") as dev_username,
- COALESCE(ad.first_name,"") as dev_first_name,
- COALESCE(ad.last_name,"") as dev_last_name,
- COALESCE(ad.email,"") as dev_email,
- COALESCE(ad.status,"") as dev_status,
- COALESCE(ad.created_at,"") as dev_created_at,
- COALESCE(ad.created_by,"") as dev_created_by,
- COALESCE(ad.updated_at,"") as dev_updated_at,
- COALESCE(ad.updated_by,"") as dev_updated_by,
-
- COALESCE(a.id,"") as app_id,
- COALESCE(a.name,"") as app_name,
- COALESCE(a.access_type,"") as app_access_type,
- COALESCE(a.callback_url,"") as app_callback_url,
- COALESCE(a.display_name,"") as app_display_name,
- COALESCE(a.status,"") as app_status,
- COALESCE(a.app_family,"") as app_app_family,
- COALESCE(a.company_id,"") as app_company_id,
- COALESCE(a.created_at,"") as app_created_at,
- COALESCE(a.created_by,"") as app_created_by,
- COALESCE(a.updated_at,"") as app_updated_at,
- COALESCE(a.updated_by,"") as app_updated_by
-
- FROM
- KMS_APP_CREDENTIAL AS c
- INNER JOIN KMS_APP AS a
- ON c.app_id = a.id
- INNER JOIN KMS_DEVELOPER AS ad
- ON ad.id = a.developer_id
- INNER JOIN KMS_APP_CREDENTIAL_APIPRODUCT_MAPPER as mp
- ON mp.appcred_id = c.id
- INNER JOIN KMS_ORGANIZATION AS o
- ON o.tenant_id = c.tenant_id
- WHERE (
- mp.app_id = a.id
- AND mp.appcred_id = c.id
- AND c.id = $1
- AND o.name = $2)
- UNION ALL
- SELECT
- COALESCE("company","") as ctype,
- COALESCE(c.tenant_id,""),
-
- COALESCE(c.status,""),
- COALESCE(c.consumer_secret,""),
-
- COALESCE(ad.id,"") as dev_id,
- COALESCE(ad.display_name,"") as dev_username,
- COALESCE("","") as dev_first_name,
- COALESCE("","") as dev_last_name,
- COALESCE("","") as dev_email,
- COALESCE(ad.status,"") as dev_status,
- COALESCE(ad.created_at,"") as dev_created_at,
- COALESCE(ad.created_by,"") as dev_created_by,
- COALESCE(ad.updated_at,"") as dev_updated_at,
- COALESCE(ad.updated_by,"") as dev_updated_by,
-
- COALESCE(a.id,"") as app_id,
- COALESCE(a.name,"") as app_name,
- COALESCE(a.access_type,"") as app_access_type,
- COALESCE(a.callback_url,"") as app_callback_url,
- COALESCE(a.display_name,"") as app_display_name,
- COALESCE(a.status,"") as app_status,
- COALESCE(a.app_family,"") as app_app_family,
- COALESCE(a.company_id,"") as app_company_id,
- COALESCE(a.created_at,"") as app_created_at,
- COALESCE(a.created_by,"") as app_created_by,
- COALESCE(a.updated_at,"") as app_updated_at,
- COALESCE(a.updated_by,"") as app_updated_by
-
- FROM
- KMS_APP_CREDENTIAL AS c
- INNER JOIN KMS_APP AS a
- ON c.app_id = a.id
- INNER JOIN KMS_COMPANY AS ad
- ON ad.id = a.company_id
- INNER JOIN KMS_APP_CREDENTIAL_APIPRODUCT_MAPPER as mp
- ON mp.appcred_id = c.id
- INNER JOIN KMS_ORGANIZATION AS o
- ON o.tenant_id = c.tenant_id
- WHERE (
- mp.app_id = a.id
- AND mp.appcred_id = c.id
- AND c.id = $1
- AND o.name = $2)
- ;`
-
- //cid,csecret,did,dusername,dfirstname,dlastname,demail,dstatus,dcreated_at,dcreated_by,dlast_modified_at,dlast_modified_by, aid,aname,aaccesstype,acallbackurl,adisplay_name,astatus,aappfamily, acompany,acreated_at,acreated_by,alast_modified_at,alast_modified_by,pid,pname,pdisplayname,pquota_limit,pqutoainterval,pquotatimeout,pcreated_at,pcreated_by,plast_modified_at,plast_modified_by sql.NullString
-
- err := db.QueryRow(sSql, dataWrapper.verifyApiKeyRequest.Key, dataWrapper.verifyApiKeyRequest.OrganizationName).
+ err := db.QueryRow(sql_GET_API_KEY_DETAILS_SQL , dataWrapper.verifyApiKeyRequest.Key, dataWrapper.verifyApiKeyRequest.OrganizationName).
Scan(
&dataWrapper.ctype,
&dataWrapper.tenant_id,
@@ -273,36 +147,8 @@
db := dbc.db
allProducts := []ApiProductDetails{}
var proxies, environments, resources string
- sSql := `
- SELECT
- COALESCE(ap.id,"") as prod_id,
- COALESCE(ap.name,"") as prod_name,
- COALESCE(ap.display_name,"") as prod_display_name,
- COALESCE(ap.quota,"") as prod_quota,
- COALESCE(ap.quota_interval, 0) as prod_quota_interval,
- COALESCE(ap.quota_time_unit,"") as prod_quota_time_unit,
- COALESCE(ap.created_at,"") as prod_created_at,
- COALESCE(ap.created_by,"") as prod_created_by,
- COALESCE(ap.updated_at,"") as prod_updated_at,
- COALESCE(ap.updated_by,"") as prod_updated_by,
- COALESCE(ap.proxies,"") as prod_proxies,
- COALESCE(ap.environments,"") as prod_environments,
- COALESCE(ap.api_resources,"") as prod_resources
- FROM
- KMS_APP_CREDENTIAL AS c
- INNER JOIN KMS_APP_CREDENTIAL_APIPRODUCT_MAPPER as mp
- ON mp.appcred_id = c.id
- INNER JOIN KMS_API_PRODUCT as ap
- ON ap.id = mp.apiprdt_id
- WHERE (mp.apiprdt_id = ap.id
- AND mp.appcred_id = c.id
- AND c.id = $1
- AND ap.tenant_id = $2)
- ;`
- //cid,csecret,did,dusername,dfirstname,dlastname,demail,dstatus,dcreated_at,dcreated_by,dlast_modified_at,dlast_modified_by, aid,aname,aaccesstype,acallbackurl,adisplay_name,astatus,aappfamily, acompany,acreated_at,acreated_by,alast_modified_at,alast_modified_by,pid,pname,pdisplayname,pquota_limit,pqutoainterval,pquotatimeout,pcreated_at,pcreated_by,plast_modified_at,plast_modified_by sql.NullString
-
- rows, err := db.Query(sSql, key, tenantId)
+ rows, err := db.Query(sql_GET_API_PRODUCTS_FOR_KEY_SQL, key, tenantId)
if err != nil {
log.Error("error fetching apiProduct details", err)
diff --git a/sqlQueries.go b/sqlQueries.go
new file mode 100644
index 0000000..609c3b3
--- /dev/null
+++ b/sqlQueries.go
@@ -0,0 +1,142 @@
+// Copyright 2017 Google Inc.
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+package apidVerifyApiKey
+
+const sql_GET_API_KEY_DETAILS_SQL = `
+ SELECT
+ COALESCE("developer","") as ctype,
+ COALESCE(c.tenant_id,""),
+
+ COALESCE(c.status,""),
+ COALESCE(c.consumer_secret,""),
+
+ COALESCE(ad.id,"") as dev_id,
+ COALESCE(ad.username,"") as dev_username,
+ COALESCE(ad.first_name,"") as dev_first_name,
+ COALESCE(ad.last_name,"") as dev_last_name,
+ COALESCE(ad.email,"") as dev_email,
+ COALESCE(ad.status,"") as dev_status,
+ COALESCE(ad.created_at,"") as dev_created_at,
+ COALESCE(ad.created_by,"") as dev_created_by,
+ COALESCE(ad.updated_at,"") as dev_updated_at,
+ COALESCE(ad.updated_by,"") as dev_updated_by,
+
+ COALESCE(a.id,"") as app_id,
+ COALESCE(a.name,"") as app_name,
+ COALESCE(a.access_type,"") as app_access_type,
+ COALESCE(a.callback_url,"") as app_callback_url,
+ COALESCE(a.display_name,"") as app_display_name,
+ COALESCE(a.status,"") as app_status,
+ COALESCE(a.app_family,"") as app_app_family,
+ COALESCE(a.company_id,"") as app_company_id,
+ COALESCE(a.created_at,"") as app_created_at,
+ COALESCE(a.created_by,"") as app_created_by,
+ COALESCE(a.updated_at,"") as app_updated_at,
+ COALESCE(a.updated_by,"") as app_updated_by
+
+ FROM
+ KMS_APP_CREDENTIAL AS c
+ INNER JOIN KMS_APP AS a
+ ON c.app_id = a.id
+ INNER JOIN KMS_DEVELOPER AS ad
+ ON ad.id = a.developer_id
+ INNER JOIN KMS_APP_CREDENTIAL_APIPRODUCT_MAPPER as mp
+ ON mp.appcred_id = c.id
+ INNER JOIN KMS_ORGANIZATION AS o
+ ON o.tenant_id = c.tenant_id
+ WHERE (
+ mp.app_id = a.id
+ AND mp.appcred_id = c.id
+ AND c.id = $1
+ AND o.name = $2)
+ UNION ALL
+ SELECT
+ COALESCE("company","") as ctype,
+ COALESCE(c.tenant_id,""),
+
+ COALESCE(c.status,""),
+ COALESCE(c.consumer_secret,""),
+
+ COALESCE(ad.id,"") as dev_id,
+ COALESCE(ad.display_name,"") as dev_username,
+ COALESCE("","") as dev_first_name,
+ COALESCE("","") as dev_last_name,
+ COALESCE("","") as dev_email,
+ COALESCE(ad.status,"") as dev_status,
+ COALESCE(ad.created_at,"") as dev_created_at,
+ COALESCE(ad.created_by,"") as dev_created_by,
+ COALESCE(ad.updated_at,"") as dev_updated_at,
+ COALESCE(ad.updated_by,"") as dev_updated_by,
+
+ COALESCE(a.id,"") as app_id,
+ COALESCE(a.name,"") as app_name,
+ COALESCE(a.access_type,"") as app_access_type,
+ COALESCE(a.callback_url,"") as app_callback_url,
+ COALESCE(a.display_name,"") as app_display_name,
+ COALESCE(a.status,"") as app_status,
+ COALESCE(a.app_family,"") as app_app_family,
+ COALESCE(a.company_id,"") as app_company_id,
+ COALESCE(a.created_at,"") as app_created_at,
+ COALESCE(a.created_by,"") as app_created_by,
+ COALESCE(a.updated_at,"") as app_updated_at,
+ COALESCE(a.updated_by,"") as app_updated_by
+
+ FROM
+ KMS_APP_CREDENTIAL AS c
+ INNER JOIN KMS_APP AS a
+ ON c.app_id = a.id
+ INNER JOIN KMS_COMPANY AS ad
+ ON ad.id = a.company_id
+ INNER JOIN KMS_APP_CREDENTIAL_APIPRODUCT_MAPPER as mp
+ ON mp.appcred_id = c.id
+ INNER JOIN KMS_ORGANIZATION AS o
+ ON o.tenant_id = c.tenant_id
+ WHERE (
+ mp.app_id = a.id
+ AND mp.appcred_id = c.id
+ AND c.id = $1
+ AND o.name = $2)
+ ;`
+
+
+const sql_GET_API_PRODUCTS_FOR_KEY_SQL = `
+ SELECT
+ COALESCE(ap.id,"") as prod_id,
+ COALESCE(ap.name,"") as prod_name,
+ COALESCE(ap.display_name,"") as prod_display_name,
+ COALESCE(ap.quota,"") as prod_quota,
+ COALESCE(ap.quota_interval, 0) as prod_quota_interval,
+ COALESCE(ap.quota_time_unit,"") as prod_quota_time_unit,
+ COALESCE(ap.created_at,"") as prod_created_at,
+ COALESCE(ap.created_by,"") as prod_created_by,
+ COALESCE(ap.updated_at,"") as prod_updated_at,
+ COALESCE(ap.updated_by,"") as prod_updated_by,
+ COALESCE(ap.proxies,"") as prod_proxies,
+ COALESCE(ap.environments,"") as prod_environments,
+ COALESCE(ap.api_resources,"") as prod_resources
+ FROM
+ KMS_APP_CREDENTIAL AS c
+ INNER JOIN KMS_APP_CREDENTIAL_APIPRODUCT_MAPPER as mp
+ ON mp.appcred_id = c.id
+ INNER JOIN KMS_API_PRODUCT as ap
+ ON ap.id = mp.apiprdt_id
+ WHERE (mp.apiprdt_id = ap.id
+ AND mp.appcred_id = c.id
+ AND c.id = $1
+ AND ap.tenant_id = $2
+ AND mp.status = 'APPROVED')
+ ;`
+
+
+const sql_GET_KMS_ATTRIBUTES_FOR_TENANT = "select entity_id, name, value from kms_attributes where tenant_id = $1"
