def post(self, request):
sql = request.POST.get("sql", None)
if not sql:
return PlayQueryView.render(request)
query = Query(sql=sql, title="Playground")
query.log(request.user)
return self.render_with_sql(request, query)
def post(self, request):
sql = request.POST.get('sql')
database = request.POST.get('database')
show_results = request.POST.get('show', True)
query = Query(sql=sql, title="Playground", database=database)
query.log(request.user)
return self.render_with_sql(request, query, show_results)
def post(self, request):
sql = request.POST.get('sql', None)
if not sql:
return PlayQueryView.render(request)
query = Query(sql=sql, title="Playground")
query.params = url_get_params(request)
return self.render_with_sql(request, query)
def post(self, request):
sql = request.POST.get('sql', None)
if not sql:
return PlayQueryView.render(request)
query = Query(sql=sql, title="Playground")
query.log(request.user)
return self.render_with_sql(request, query)
def post(self, request, *args, **kwargs):
sql = request.POST.get('sql')
connection = request.POST.get('connection')
query = Query(sql=sql, connection=connection, title='')
ql = query.log(request.user)
query.title = 'Playground - %s' % ql.id
return _export(request, query)
def post(self, request):
sql = request.POST.get('sql')
show_results = request.POST.get('show', True)
query = Query(sql=sql, title="Playground")
passes_blacklist, failing_words = query.passes_blacklist()
error = MSG_FAILED_BLACKLIST % ', '.join(failing_words) if not passes_blacklist else None
run_query = not bool(error) if show_results else False
return self.render_with_sql(request, query, run_query=run_query, error=error)
def validate(self, value):
query = Query(sql=value)
if not query.available_params():
error = query.error_messages()
if error:
raise ValidationError(_(error),
params={'value': value},
code="InvalidSql")
def csv_from_sql(request):
sql = request.POST.get('sql', None)
if not sql:
return PlayQueryView.render(request)
query = Query(sql=sql)
query.params = url_get_params(request)
fn = generate_report_action()
return fn(None, None, [query, ])
def csv_from_sql(request):
sql = request.POST.get('sql', None)
if not sql:
return PlayQueryView.render(request)
query = Query(sql=sql)
query.params = url_get_params(request)
fn = generate_report_action()
return fn(None, None, [
query,
])
def validate(self, value):
query = Query(sql=value)
if not query.available_params():
error = query.error_messages()
if error:
raise ValidationError(
_(error),
params={'value': value},
code="InvalidSql"
)
def post(self, request):
sql = request.POST.get('sql')
show = url_get_show(request)
c = request.POST.get('connection', '')
query = Query(sql=sql, title="Playground", connection=c)
passes_blacklist, failing_words = query.passes_blacklist()
error = MSG_FAILED_BLACKLIST % ', '.join(
failing_words) if not passes_blacklist else None
run_query = not bool(error) if show else False
return self.render_with_sql(request,
query,
run_query=run_query,
error=error)
def validate(self, value):
"""
Ensure that the SQL passes the blacklist.
:param value: The SQL for this Query model.
"""
query = Query(sql=value)
passes_blacklist, failing_words = query.passes_blacklist()
error = MSG_FAILED_BLACKLIST % ', '.join(
failing_words) if not passes_blacklist else None
if error:
raise ValidationError(error, code="InvalidSql")
def validate(self, value):
"""
Ensure that the SQL passes the blacklist and executes. Execution check is skipped if params are present.
:param value: The SQL for this Query model.
"""
query = Query(sql=value)
error = MSG_FAILED_BLACKLIST if not query.passes_blacklist() else None
if not error and not query.available_params():
error = query.try_execute()
if error:
raise ValidationError(_(error), code="InvalidSql")
def validate(self, value):
"""
Ensure that the SQL passes the blacklist.
:param value: The SQL for this Query model.
"""
query = Query(sql=value)
passes_blacklist, failing_words = query.passes_blacklist()
error = MSG_FAILED_BLACKLIST % ', '.join(failing_words) if not passes_blacklist else None
if error:
raise ValidationError(
error,
code="InvalidSql"
)
def get(self, request):
if url_get_query_id(request):
query = get_object_or_404(Query, pk=url_get_query_id(request))
return self.render_with_sql(request, query)
if url_get_log_id(request):
log = get_object_or_404(QueryLog, pk=url_get_log_id(request))
query = Query(sql=log.sql, title="Playground")
return self.render_with_sql(request, query)
return self.render(request)
def validate(self, value):
"""
Ensure that the SQL passes the blacklist and executes. Execution check is skipped if params are present.
:param value: The SQL for this Query model.
"""
query = Query(sql=value)
error = MSG_FAILED_BLACKLIST if not query.passes_blacklist() else None
if not error and not query.available_params():
error = query.try_execute()
if error:
raise ValidationError(
_(error),
code="InvalidSql"
)
def get(self, request):
if url_get_query_id(request):
query = get_object_or_404(Query, pk=url_get_query_id(request))
return self.render_with_sql(request, query, run_query=False)
if url_get_log_id(request):
log = get_object_or_404(QueryLog, pk=url_get_log_id(request))
c = log.connection or ''
query = Query(sql=log.sql, title="Playground", connection=c)
return self.render_with_sql(request, query)
return self.render()
def validate(self, value):
"""
Ensure that the SQL passes the blacklist and executes. Execution check is skipped if params are present.
:param value: The SQL for this Query model.
"""
query = Query(sql=value)
passes_blacklist, failing_words = query.passes_blacklist()
error = MSG_FAILED_BLACKLIST % ', '.join(failing_words) if not passes_blacklist else None
if not error and not query.available_params():
try:
query.execute_query_only()
except DatabaseError as e:
error = str(e)
if error:
raise ValidationError(
_(error),
code="InvalidSql"
)
def validate(self, value):
"""
Ensure that the SQL passes the blacklist and executes. Execution check is skipped if params are present.
:param value: The SQL for this Query model.
"""
query = Query(sql=value)
passes_blacklist, failing_words = query.passes_blacklist()
error = MSG_FAILED_BLACKLIST % ', '.join(
failing_words) if not passes_blacklist else None
if not error and not query.available_params():
try:
query.execute_query_only()
except DatabaseError as e:
error = str(e)
if error:
raise ValidationError(error, code="InvalidSql")
def test_playground_query_log(self):
query = Query(sql='select 1;', title="Playground")
query.log(None)
log = QueryLog.objects.first()
self.assertTrue(log.is_playground)
def post(self, request):
sql = request.POST.get('sql')
query = Query(sql=sql, title="Playground")
query.log(request.user)
return self.render_with_sql(request, query)
def post(self, request):
sql = request.POST.get('sql')
show = url_get_show(request)
query = Query(sql=sql, title="Playground", connection=request.POST.get('connection'))
run_query = True if show else False
return self.render_with_sql(request, query, run_query=run_query)
def handle(self, **options):
if 'explorer' in settings.INSTALLED_APPS:
from explorer.models import Query
queries = (
('All Interactive Users',
"""SELECT u.first_name, u.last_name, u.email, u.username, u.is_staff, u.is_superuser,
p.salutation, p.company, p.position_title, p.phone, p.address, p.address2,
p.member_number, p.city, p.state, p.zipcode, p.country, p.url, p.sex,
p.address_type, p.phone2, p.fax, p.work_phone, p.home_phone, p.mobile_phone,
p.notes, p.admin_notes
FROM auth_user u INNER JOIN profiles_profile p
ON u.id=p.user_id
WHERE u.is_active=True
AND p.status=True
AND p.status_detail='active'"""),
('All Members',
"""SELECT u.first_name, u.last_name, u.email, u.username, u.is_staff, u.is_superuser,
p.salutation, p.company, p.position_title, p.phone, p.address, p.address2,
p.member_number, p.city, p.state, p.zipcode, p.country, p.url, p.sex,
p.address_type, p.phone2, p.fax, p.work_phone, p.home_phone, p.mobile_phone,
m.membership_type_id, m.renewal, m.certifications, m.work_experience,
m.referer_url, m.referral_source, m.join_dt, m.expire_dt, m.renew_dt,
m.primary_practice, m.how_long_in_practice, m.application_approved,
m.application_approved_dt, m.areas_of_expertise, m.home_state,
m.year_left_native_country, m.network_sectors, m.networking,
m.government_worker, m.government_agency, m.license_number,
m.license_state, m.status_detail
FROM auth_user u
INNER JOIN profiles_profile p
ON u.id=p.user_id
INNER JOIN memberships_membershipdefault m
ON m.user_id=u.id
WHERE u.is_active=True
AND p.status=True
AND m.status_detail <> 'archive'"""),
('All Corporate Members',
"""SELECT cp.name, cp.address, cp.address2, cp.city, cp.state, cp.zip, cp.country,
cp.phone, cp.email, cp.url, cp.number_employees, cp.chapter, cp.tax_exempt,
cp.annual_revenue, cp.annual_ad_expenditure, cp.description, cp.expectations,
cp.notes, cp.referral_source, cp.ud1, cp.ud2, cp.ud3, cp.ud4, cp.ud5, cp.ud6,
cp.ud7, cp.ud8, cm.corporate_membership_type_id, cm.renewal, cm.renew_dt,
cm.join_dt, cm.expiration_dt, cm.approved, cm.admin_notes, cm.status_detail
FROM corporate_memberships_corpprofile cp
INNER JOIN corporate_memberships_corpmembership cm
ON cp.id=cm.corp_profile_id
WHERE cm.status_detail <> 'archive'"""),
('All Users in a Specific Group (replace <YOUR GROUP ID> with your group id)',
"""SELECT ug.name, u.first_name, u.last_name, u.email, u.username, u.is_staff,
u.is_superuser, p.salutation, p.company, p.position_title, p.phone,
p.address, p.address2, p.member_number, p.city, p.state, p.zipcode,
p.country, p.url, p.sex, p.address_type, p.phone2, p.fax, p.work_phone,
p.home_phone, p.mobile_phone
FROM auth_user u INNER JOIN profiles_profile p
ON u.id=p.user_id INNER JOIN user_groups_groupmembership ugm
on u.id=ugm.member_id INNER JOIN user_groups_group ug on ug.id=ugm.group_id
WHERE ug.id=<YOUR GROUP ID>
AND ugm.status=True
AND ugm.status_detail='active'"""),
)
for title, sql in queries:
query = Query(title=title, sql=sql)
query.save()
print 'Inserted: ', title
else:
print 'NO default sqls loaded for SQL Explorer because django-sqlexplorer is not installed'
def handle(self, **options):
if "explorer" in settings.INSTALLED_APPS:
from explorer.models import Query
queries = (
(
"All Interactive Users",
"""SELECT u.first_name, u.last_name, u.email, u.username, u.is_staff, u.is_superuser,
p.salutation, p.company, p.position_title, p.phone, p.address, p.address2,
p.member_number, p.city, p.state, p.zipcode, p.country, p.url, p.sex,
p.address_type, p.phone2, p.fax, p.work_phone, p.home_phone, p.mobile_phone,
p.notes, p.admin_notes
FROM auth_user u INNER JOIN profiles_profile p
ON u.id=p.user_id
WHERE u.is_active=True
AND p.status=True
AND p.status_detail='active'""",
),
(
"All Members",
"""SELECT u.first_name, u.last_name, u.email, u.username, u.is_staff, u.is_superuser,
p.salutation, p.company, p.position_title, p.phone, p.address, p.address2,
p.member_number, p.city, p.state, p.zipcode, p.country, p.url, p.sex,
p.address_type, p.phone2, p.fax, p.work_phone, p.home_phone, p.mobile_phone,
m.membership_type_id, m.renewal, m.certifications, m.work_experience,
m.referer_url, m.referral_source, m.join_dt, m.expire_dt, m.renew_dt,
m.primary_practice, m.how_long_in_practice, m.application_approved,
m.application_approved_dt, m.areas_of_expertise, m.home_state,
m.year_left_native_country, m.network_sectors, m.networking,
m.government_worker, m.government_agency, m.license_number,
m.license_state, m.status_detail
FROM auth_user u
INNER JOIN profiles_profile p
ON u.id=p.user_id
INNER JOIN memberships_membershipdefault m
ON m.user_id=u.id
WHERE u.is_active=True
AND p.status=True
AND m.status_detail <> 'archive'""",
),
(
"All Corporate Members",
"""SELECT cp.name, cp.address, cp.address2, cp.city, cp.state, cp.zip, cp.country,
cp.phone, cp.email, cp.url, cp.number_employees, cp.chapter, cp.tax_exempt,
cp.annual_revenue, cp.annual_ad_expenditure, cp.description, cp.expectations,
cp.notes, cp.referral_source, cp.ud1, cp.ud2, cp.ud3, cp.ud4, cp.ud5, cp.ud6,
cp.ud7, cp.ud8, cm.corporate_membership_type_id, cm.renewal, cm.renew_dt,
cm.join_dt, cm.expiration_dt, cm.approved, cm.admin_notes, cm.status_detail
FROM corporate_memberships_corpprofile cp
INNER JOIN corporate_memberships_corpmembership cm
ON cp.id=cm.corp_profile_id
WHERE cm.status_detail <> 'archive'""",
),
(
"All Users in a Specific Group (replace <YOUR GROUP ID> with your group id)",
"""SELECT ug.name, u.first_name, u.last_name, u.email, u.username, u.is_staff,
u.is_superuser, p.salutation, p.company, p.position_title, p.phone,
p.address, p.address2, p.member_number, p.city, p.state, p.zipcode,
p.country, p.url, p.sex, p.address_type, p.phone2, p.fax, p.work_phone,
p.home_phone, p.mobile_phone
FROM auth_user u INNER JOIN profiles_profile p
ON u.id=p.user_id INNER JOIN user_groups_groupmembership ugm
on u.id=ugm.member_id INNER JOIN user_groups_group ug on ug.id=ugm.group_id
WHERE ug.id=<YOUR GROUP ID>
AND ugm.status=True
AND ugm.status_detail='active'""",
),
)
for title, sql in queries:
query = Query(title=title, sql=sql)
query.save()
print "Inserted: ", title
else:
print "NO default sqls loaded for SQL Explorer because django-sqlexplorer is not installed"
def post(self, request):
sql = request.POST.get('sql')
show_results = request.POST.get('show', True)
query = Query(sql=sql, title="Playground")
query.log(request.user)
return self.render_with_sql(request, query, show_results)
def handle(self, **options):
if 'explorer' in settings.INSTALLED_APPS:
from explorer.models import Query
queries = (
('All Interactive Users',
'All Interactive Users - People Who Can Login to the Site',
"""SELECT u.first_name, u.last_name, u.email, u.username, u.is_staff, u.is_superuser,
p.salutation, p.company, p.position_title, p.phone, p.address, p.address2,
p.member_number, p.city, p.state, p.zipcode, p.country, p.url, p.sex,
p.address_type, p.phone2, p.fax, p.work_phone, p.home_phone, p.mobile_phone,
p.notes, p.admin_notes
FROM auth_user u INNER JOIN profiles_profile p
ON u.id=p.user_id
WHERE u.is_active=True
AND p.status=True
AND p.status_detail='active'"""),
('All Memberships',
'All Memberships',
"""SELECT u.first_name, u.last_name, u.email, u.username, u.is_staff, u.is_superuser,
p.salutation, p.company, p.position_title, p.phone, p.address, p.address2,
p.member_number, p.city, p.state, p.zipcode, p.country, p.url, p.sex,
p.address_type, p.phone2, p.fax, p.work_phone, p.home_phone, p.mobile_phone,
m.membership_type_id, m.renewal, m.certifications, m.work_experience,
m.referer_url, m.referral_source, m.join_dt, m.expire_dt, m.renew_dt,
m.primary_practice, m.how_long_in_practice, m.application_approved,
m.application_approved_dt, m.areas_of_expertise, m.home_state,
m.year_left_native_country, m.network_sectors, m.networking,
m.government_worker, m.government_agency, m.license_number,
m.license_state, m.status_detail
FROM auth_user u
INNER JOIN profiles_profile p
ON u.id=p.user_id
INNER JOIN memberships_membershipdefault m
ON m.user_id=u.id
WHERE u.is_active=True
AND p.status=True
AND m.status_detail <> 'archive'"""),
('All Corporate Memberships',
'All corporate memberships',
"""SELECT cp.name, cp.address, cp.address2, cp.city, cp.state, cp.zip, cp.country,
cp.phone, cp.email, cp.url, cp.number_employees, cp.chapter, cp.tax_exempt,
cp.annual_revenue, cp.annual_ad_expenditure, cp.description, cp.expectations,
cp.notes, cp.referral_source, cp.ud1, cp.ud2, cp.ud3, cp.ud4, cp.ud5, cp.ud6,
cp.ud7, cp.ud8, cm.corporate_membership_type_id, cm.renewal, cm.renew_dt,
cm.join_dt, cm.expiration_dt, cm.approved, cm.admin_notes, cm.status_detail
FROM corporate_memberships_corpprofile cp
INNER JOIN corporate_memberships_corpmembership cm
ON cp.id=cm.corp_profile_id
WHERE cm.status_detail <> 'archive'"""),
('Users By Group ID (All Groups)',
'All groups - dump this into Excel and filter by the group_name field as needed',
"""SELECT ug.name as group_name, u.first_name, u.last_name, u.email, u.username, u.is_staff,
u.is_superuser, p.salutation, p.company, p.position_title, p.phone,
p.address, p.address2, p.member_number, p.city, p.state, p.zipcode,
p.country, p.url, p.sex, p.address_type, p.phone2, p.fax, p.work_phone,
p.home_phone, p.mobile_phone
FROM auth_user u
INNER JOIN profiles_profile p ON u.id=p.user_id
INNER JOIN user_groups_groupmembership ugm on u.id=ugm.member_id
INNER JOIN user_groups_group ug on ug.id=ugm.group_id
WHERE ug.id>0
AND ugm.status=True
AND ugm.status_detail='active'"""),
('Users By Group ID (Edit the Group ID)',
'Users by Group ID - this query shows group id = 1 on line number 10, so edit that for whichever group you are looking for.',
"""SELECT ug.name as group_name, u.first_name, u.last_name, u.email, u.username, u.is_staff,
u.is_superuser, p.salutation, p.company, p.position_title, p.phone,
p.address, p.address2, p.member_number, p.city, p.state, p.zipcode,
p.country, p.url, p.sex, p.address_type, p.phone2, p.fax, p.work_phone,
p.home_phone, p.mobile_phone
FROM auth_user u
INNER JOIN profiles_profile p ON u.id=p.user_id
INNER JOIN user_groups_groupmembership ugm on u.id=ugm.member_id
INNER JOIN user_groups_group ug on ug.id=ugm.group_id
WHERE ug.id=1
AND ugm.status=True
AND ugm.status_detail='active'"""),
('Tables - List All Database Tables',
'A list of all tables including system tables',
"""select tablename from pg_tables"""),
('Users In the Database On The Site, Not All Can Login',
'This lists everyone in the auth_user table which is the default django table for authentication but also used for anyone who has filled out a contact form. The passwords are encrypted and cant be decrypted (no way around that) but it does have the basics of all humans (does NOT mean they can login.)',
"""select id, first_name, last_name, email, username, last_login, is_superuser, is_staff, is_active, date_joined from auth_user;"""),
('Users in Database with Membership Details',
'Users in Database with Membership Details',
"""select u.id, u.first_name, u.last_name, u.email, u.username, u.last_login, u.is_superuser, u.is_staff, u.is_active, m.member_number, m.join_dt, m.expire_dt
from auth_user u
inner join memberships_membershipdefault m on m.user_id = u.id
where m.status=true
and m.status_detail<>'archive'"""),
)
for title, description, sql in queries:
query = Query(title=title,
description=description,
sql=sql)
query.save()
print 'Inserted: ', title
else:
print 'NO default sqls loaded for SQL Explorer because django-sqlexplorer is not installed'
def post(self, request):
sql = request.POST.get('sql')
query = Query(sql=sql, title="Playground")
query.log(request.user)
return self.render_with_sql(request, query)
def download_csv_from_sql(request):
sql = request.POST.get('sql')
return build_download_response(
Query(sql=sql, title="Playground", params=url_get_params(request)))
def download_from_sql(request):
sql = request.POST.get('sql')
query = Query(sql=sql, title='')
ql = query.log(request.user)
query.title = 'Playground - %s' % ql.id
return _export(request, query)
def test_playground_query_log(self):
query = Query(sql='select 1;', title="Playground")
query.log(None)
log = QueryLog.objects.first()
self.assertTrue(log.is_playground)
def download_from_sql(request):
sql = request.POST.get('sql')
query = Query(sql=sql, title='')
ql = query.log(request.user)
query.title = 'Playground - %s' % ql.id
return _export(request, query)
def csv_from_sql(request):
sql = request.POST.get('sql', None)
if not sql:
return PlayQueryView.render(request)
return build_download_response(Query(sql=sql, title="Playground"), request)
def handle(self, **options):
if 'explorer' in settings.INSTALLED_APPS:
from explorer.models import Query
queries = (
('All Interactive Users',
'All Interactive Users - People Who Can Login to the Site',
"""SELECT u.first_name, u.last_name, u.email, u.username, u.is_staff, u.is_superuser,
p.salutation, p.company, p.position_title, p.phone, p.address, p.address2,
p.member_number, p.city, p.state, p.zipcode, p.country, p.url, p.sex,
p.address_type, p.phone2, p.fax, p.work_phone, p.home_phone, p.mobile_phone,
p.notes, p.admin_notes
FROM auth_user u INNER JOIN profiles_profile p
ON u.id=p.user_id
WHERE u.is_active=True
AND p.status=True
AND p.status_detail='active'"""),
('All Memberships',
'All Memberships',
"""SELECT u.first_name, u.last_name, u.email, u.username, u.is_staff, u.is_superuser,
p.salutation, p.company, p.position_title, p.phone, p.address, p.address2,
p.member_number, p.city, p.state, p.zipcode, p.country, p.url, p.sex,
p.address_type, p.phone2, p.fax, p.work_phone, p.home_phone, p.mobile_phone,
m.membership_type_id, m.renewal, m.certifications, m.work_experience,
m.referer_url, m.referral_source, m.join_dt, m.expire_dt, m.renew_dt,
m.primary_practice, m.how_long_in_practice, m.application_approved,
m.application_approved_dt, m.areas_of_expertise, m.home_state,
m.year_left_native_country, m.network_sectors, m.networking,
m.government_worker, m.government_agency, m.license_number,
m.license_state, m.status_detail
FROM auth_user u
INNER JOIN profiles_profile p
ON u.id=p.user_id
INNER JOIN memberships_membershipdefault m
ON m.user_id=u.id
WHERE u.is_active=True
AND p.status=True
AND m.status_detail <> 'archive'"""),
('All Corporate Memberships',
'All corporate memberships',
"""SELECT cp.name, cp.address, cp.address2, cp.city, cp.state, cp.zip, cp.country,
cp.phone, cp.email, cp.url, cp.number_employees, cp.chapter, cp.tax_exempt,
cp.annual_revenue, cp.annual_ad_expenditure, cp.description, cp.expectations,
cp.notes, cp.referral_source, cp.ud1, cp.ud2, cp.ud3, cp.ud4, cp.ud5, cp.ud6,
cp.ud7, cp.ud8, cm.corporate_membership_type_id, cm.renewal, cm.renew_dt,
cm.join_dt, cm.expiration_dt, cm.approved, cm.admin_notes, cm.status_detail
FROM corporate_memberships_corpprofile cp
INNER JOIN corporate_memberships_corpmembership cm
ON cp.id=cm.corp_profile_id
WHERE cm.status_detail <> 'archive'"""),
('Users By Group ID (All Groups)',
'All groups - dump this into Excel and filter by the group_name field as needed',
"""SELECT ug.name as group_name, u.first_name, u.last_name, u.email, u.username, u.is_staff,
u.is_superuser, p.salutation, p.company, p.position_title, p.phone,
p.address, p.address2, p.member_number, p.city, p.state, p.zipcode,
p.country, p.url, p.sex, p.address_type, p.phone2, p.fax, p.work_phone,
p.home_phone, p.mobile_phone
FROM auth_user u
INNER JOIN profiles_profile p ON u.id=p.user_id
INNER JOIN user_groups_groupmembership ugm on u.id=ugm.member_id
INNER JOIN user_groups_group ug on ug.id=ugm.group_id
WHERE ug.id>0
AND ugm.status=True
AND ugm.status_detail='active'"""),
('Users By Group ID (Edit the Group ID)',
'Users by Group ID - this query shows group id = 1 on line number 10, so edit that for whichever group you are looking for.',
"""SELECT ug.name as group_name, u.first_name, u.last_name, u.email, u.username, u.is_staff,
u.is_superuser, p.salutation, p.company, p.position_title, p.phone,
p.address, p.address2, p.member_number, p.city, p.state, p.zipcode,
p.country, p.url, p.sex, p.address_type, p.phone2, p.fax, p.work_phone,
p.home_phone, p.mobile_phone
FROM auth_user u
INNER JOIN profiles_profile p ON u.id=p.user_id
INNER JOIN user_groups_groupmembership ugm on u.id=ugm.member_id
INNER JOIN user_groups_group ug on ug.id=ugm.group_id
WHERE ug.id=1
AND ugm.status=True
AND ugm.status_detail='active'"""),
('Tables - List All Database Tables',
'A list of all tables including system tables',
"""select tablename from pg_tables"""),
('Users In the Database On The Site, Not All Can Login',
'This lists everyone in the auth_user table which is the default django table for authentication but also used for anyone who has filled out a contact form. The passwords are encrypted and cant be decrypted (no way around that) but it does have the basics of all humans (does NOT mean they can login.)',
"""select id, first_name, last_name, email, username, last_login, is_superuser, is_staff, is_active, date_joined from auth_user;"""),
('Users in Database with Membership Details',
'Users in Database with Membership Details',
"""select u.id, u.first_name, u.last_name, u.email, u.username, u.last_login, u.is_superuser, u.is_staff, u.is_active, m.member_number, m.join_dt, m.expire_dt
from auth_user u
inner join memberships_membershipdefault m on m.user_id = u.id
where m.status=true
and m.status_detail<>'archive'"""),
)
for title, description, sql in queries:
query = Query(title=title,
description=description,
sql=sql)
query.save()
print('Inserted: ', title)
else:
print('NO default sqls loaded for SQL Explorer because django-sqlexplorer is not installed')
