14 Replies - 652 Views - Last Post: 27 January 2014 - 01:21 PM Rate Topic: -----

#1 cfoley  Icon User is offline

  • Cabbage
  • member icon

Reputation: 1940
  • View blog
  • Posts: 4,028
  • Joined: 11-December 07

SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 06:54 AM

Motivation: (skip to the questions if you don't care)

In a personal project that I'm planning, I want to keep as much of the logic in the database as possible. I usually avoid all things database and have been looking for an excuse to get my hands dirty for a while. This project seems ideal because the data that the user will want to see is a distilled summary of the data that needs to be stored in the database.

At the same time, I have a work project that deals with analysing some scientific data. I've done the required analysis for some experiments "manually" in a spreadsheet and think it's time to automate it. Rather than my usual approach of writing some scripts to parse the data, I thought it was about time to do this sort of stuff in a database.

SQLite seemed great for both these (embedded application database for the former and standalone engine for data processing for the latter)

Questions:

What are the best techniques for decomposing SQL. This is difficult to google because I keep getting this. However, I have found a couple of pages saying views are useful for decomposing and reusing SQL. I've also found some comments saying it's a bad idea and others saying it helps performance, although I'm sure this is dependant on the DBMS. Are there better things I could be doing?

Following on from this, what is the best way of separating logical aspects of my design? For example, my second project might have several tables in each category of raw data, interim processing, final results, visualisation scripts. Java has packages. .NET has namespaces. Is there any equivalent for databases?

Finally, what about testing? I would usually do this stuff in a programming language and write unit tests. I could still use unit tests to populate some data, test the results and clean up the test database but I can't help wondering if there is a better tool.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL decomposition and testing (SQLite)

#2 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 3,009
  • Joined: 12-January 10

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 07:00 AM

IM kinda lost as to what you are askign for. SQL has proceedures you can use and functions using both table and scaler.

is this what you are asking about?
Was This Post Helpful? 1
  • +
  • -

#3 cfoley  Icon User is offline

  • Cabbage
  • member icon

Reputation: 1940
  • View blog
  • Posts: 4,028
  • Joined: 11-December 07

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 07:26 AM

I guess one example would be extracting subqueries for reuse. Another would be removing some of the WHERE clause to make a more general query/view that could be used as part of other queries.

Are you talking about stored procedures? SQLite doesn't have them.
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,595
  • Joined: 16-October 07

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 07:41 AM

I'm also not entirely following this...

Right, you have tables. Most of your joins are those tables are to essentially de-normalize things. Most of the rest aggregate. You can make views to those tables that are all the foreign key style joins you could make. Make the logical group bys. There are probably more, but you'll figure it out as you go. Now, from this point forward, you consider your tables and views the record sets available: no more joins or grouping.

At this point, the only thing you need work with are essentially filters. Where clauses. These can simply be picked from the available fields and use basic where grammar.
Was This Post Helpful? 1
  • +
  • -

#5 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 3,009
  • Joined: 12-January 10

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 07:59 AM

View Postcfoley, on 27 January 2014 - 09:26 AM, said:

I guess one example would be extracting subqueries for reuse. Another would be removing some of the WHERE clause to make a more general query/view that could be used as part of other queries.

Are you talking about stored procedures? SQLite doesn't have them.



i would reccommend dl ms sql -- it will make your life a lot easier
Was This Post Helpful? 1
  • +
  • -

#6 cfoley  Icon User is offline

  • Cabbage
  • member icon

Reputation: 1940
  • View blog
  • Posts: 4,028
  • Joined: 11-December 07

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 09:34 AM

Quote

Right, you have tables. Most of your joins are those tables are to essentially de-normalize things. Most of the rest aggregate. You can make views to those tables that are all the foreign key style joins you could make. Make the logical group bys. There are probably more, but you'll figure it out as you go. Now, from this point forward, you consider your tables and views the record sets available: no more joins or grouping.


I think this is pretty much what I was talking about, but expressed far more clearly. Thanks!

What about testing then? In making those views of joins and group bys, I will also be including formulae. One example would be standard deviation (chosen because SQLite doesn't appear to have a function for that). This is something I would want to test if it were coded in a traditional language and I feel like I should do so here. Is the done thing to throw together some unit tests in another language or is there something better?

Quote

i would reccommend dl ms sql -- it will make your life a lot easier


It seems to be Windows only. The attraction of SQLite to me is the lack of installation, configuration and its availability on multiple platforms (I work in Windows and Linux and aspire to own a Mac one day). What makes you recommend MS SQL? Is it just the stored procedures thing?
Was This Post Helpful? 0
  • +
  • -

#7 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,595
  • Joined: 16-October 07

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 09:45 AM

View Postcfoley, on 27 January 2014 - 11:34 AM, said:

This is something I would want to test if it were coded in a traditional language


SQL is a declarative language. In essence, it is a functional language. If you've written your expression correctly and it returns the result set you expect, you're pretty much done.

The only way it would be invalid is if the data returned didn't conform to expectations. That test is done by users. If you can think of a unit test for a SQL query, then you've probably already written your SQL to pass it.

It's more of a GIGO (Garbage In, Garbage Out) thing. Your SQL will always validate, even if your question is dumb.
Was This Post Helpful? 2
  • +
  • -

#8 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 3,009
  • Joined: 12-January 10

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 09:50 AM

yes the procedures are a huge help when programming and having to keep reusable searches for a long time

here is a simple proceedure that we use everyday -- i have removed the procedure name because of obvious reasons

	
alter procedure[some_procedure]
@business_id int,
	@vendor_class_id int
AS
BEGIN

SELECT distinct t_Vendor_Employee.vendor_id,t_Vendor_Employee.vendor_employee_id,
t_Vendor.txt_Company_Name, t_Employee.txt_first_name, t_Employee.txt_last_name, 
t_Employee.txt_email, t_state.txt_state, t_vendor_address.txt_address_1, t_vendor_address.txt_city,
t_vendor_address.txt_zip_code ,t_Business_Vendor_Status.txt_Status_Name ,
(select  top 1 t_Vendor_Class.txt_Vendor_Class_Name
from t_Vendor_Class
inner join t_Vendor_Department on t_Vendor_Department.vendor_class_ID = t_Vendor_Class.vendor_class_ID
where t_Vendor_Department.vendor_class_ID=@vendor_class_id)as V_class

from t_Vendor_Employee
INNER JOIN t_Employee with(nolock)ON t_Vendor_Employee.employee_ID = t_Employee.employee_ID 
INNER JOIN t_Vendor with(nolock)ON t_Vendor_Employee.Vendor_ID = t_Vendor.vendor_ID 
inner join t_Business_Vendor with(nolock)on t_business_vendor.Vendor_ID=t_vendor_employee.Vendor_ID
inner join t_Business_Vendor_Employee_Detail with(nolock)on t_Business_Vendor_Employee_Detail.Business_Vendor_ID=t_Business_Vendor.business_Vendor_ID
and t_Business_Vendor_Employee_Detail.Vendor_Employee_ID=t_vendor_employee.vendor_employee_id
INNER JOIN t_Business_Vendor_Status with(nolock)ON t_Business_Vendor_Status.Business_Vendor_Status_ID=t_Business_Vendor_Employee_Detail.Business_Vendor_Status_ID
inner join t_vendor_address_detail with(nolock)on t_vendor.vendor_ID = t_vendor_address_detail.vendor_id 
inner join t_vendor_address with(nolock)on t_vendor_address_detail.vendor_address_id = t_vendor_address.vendor_address_id
inner join t_State with(nolock)on t_vendor_address.state_ID = t_State.state_id
left outer join t_Vendor_Department with(nolock) on t_Vendor_Department.Vendor_ID = t_Business_Vendor.Vendor_ID
WHERE t_Business_Vendor.business_id=@business_id and t_vendor.vendor_id in (select vendor_id
from t_Vendor_Department
where t_Vendor_Department.vendor_class_ID=@vendor_class_id) 
and t_Business_Vendor_Status.Business_Vendor_Status_ID = 5

order by t_state.txt_state, t_Vendor_Employee.vendor_employee_id

end

Was This Post Helpful? 1
  • +
  • -

#9 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,595
  • Joined: 16-October 07

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 10:36 AM

Great mother of crap, DarenR!

Right, rule #1 with stored procedures. They should only be used if a view won't work. In particular, loops should only be used if all else fails. Additionally, using IN with a select is worst case scenario.

Forgive, but I had to refactor:
SELECT distinct ve.vendor_id, ve.vendor_employee_id,
        v.txt_Company_Name, e.txt_first_name, e.txt_last_name, 
        e.txt_email, s.txt_state, va.txt_address_1, va.txt_city,
        va.txt_zip_code ,bvs.txt_Status_Name, vc.V_class,
        vd.vendor_class_ID, be.business_id
    from t_Vendor_Employee ve
        INNER JOIN t_Employee e
            ON ve.employee_ID = e.employee_ID 
        INNER JOIN t_Vendor v
            ON ve.Vendor_ID = v.vendor_ID 
        inner join t_Business_Vendor be
            on be.Vendor_ID=ve.Vendor_ID
        inner join t_Business_Vendor_Employee_Detail bved
            on bved.Business_Vendor_ID=be.business_Vendor_ID
                and bved.Vendor_Employee_ID=ve.vendor_employee_id
        INNER JOIN t_Business_Vendor_Status bvs
            ON bvs.Business_Vendor_Status_ID=bved.Business_Vendor_Status_ID
                and bvs.Business_Vendor_Status_ID = 5
        inner join t_vendor_address_detail vad
            on v.vendor_ID = vad.vendor_id 
        inner join t_vendor_address va
            on vad.vendor_address_id = va.vendor_address_id
        inner join t_State s
            on va.state_ID = s.state_id
        inner join t_Vendor_Department vd
            on vd.Vendor_ID = be.Vendor_ID
        left outer join (
            select  vendor_class_ID, min(txt_Vendor_Class_Name) as v_class from t_Vendor_Class 
        ) vc on vd.vendor_class_ID=vc.vendor_class_id
    WHERE be.business_id=@business_id 



I'm not 100% sure I got this right. I am, however, 100% sure you don't need a stored procedure to generate the result set. Make a view. Query by the keys.
Was This Post Helpful? 1
  • +
  • -

#10 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 3,009
  • Joined: 12-January 10

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 11:02 AM

View Postbaavgai, on 27 January 2014 - 12:36 PM, said:

I'm not 100% sure I got this right. I am, however, 100% sure you don't need a stored procedure to generate the result set. Make a view. Query by the keys.



this is for a crystal report pull and had to be written as stated that uses a back ground runner to pull the data using xsl and vb.net. We do not use alias's here unless we absolutely have to.
Was This Post Helpful? 1
  • +
  • -

#11 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,595
  • Joined: 16-October 07

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 11:55 AM

View PostDarenR, on 27 January 2014 - 01:02 PM, said:

this is for a crystal report pull


Ok... not sure how that matters.

View PostDarenR, on 27 January 2014 - 01:02 PM, said:

had to be written as stated that uses a back ground runner to pull the data using xsl and vb.net.


If you can execute a stored procedure, you should be able to execute a query against a view.

View PostDarenR, on 27 January 2014 - 01:02 PM, said:

We do not use alias's here unless we absolutely have to.


Ok. Your loss.

However, if an alias is the difference between:
WHERE t_vendor.vendor_id in (
    select vendor_id
        from t_Vendor_Department
        where t_Vendor_Department.vendor_class_ID=@vendor_class_id
    ) 


and
inner join t_Vendor_Department vd
    on vd.Vendor_ID = t_vendor.Vendor_ID
        and vd.vendor_class_ID=@vendor_class_id



Then you absolutely have to.
Was This Post Helpful? 1
  • +
  • -

#12 cfoley  Icon User is offline

  • Cabbage
  • member icon

Reputation: 1940
  • View blog
  • Posts: 4,028
  • Joined: 11-December 07

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 12:27 PM

Quote

SQL is a declarative language. In essence, it is a functional language. If you've written your expression correctly and it returns the result set you expect, you're pretty much done.


Hmm... I see similar things written about functional languages. However, from the little functional code I've written, unit testing has helped there. I know I have written SQL that returned the wrong result in the past. It wasn't until I threw some data at it that I noticed my mistake. I thought unit testing was just that: throwing data at some code to see if it meets expectations. I have a hard time believing that not keeping and rerunning the test is a good thing.

I'm torn between just going along with your experience or bashing out test code until I reach enlightenment.

Quote

Right, rule #1 with stored procedures. They should only be used if a view won't work.


I'm intrigued as to why. If I'm using SQLite then I don't have much choice but what makes views the preferred option?
Was This Post Helpful? 0
  • +
  • -

#13 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,595
  • Joined: 16-October 07

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 12:45 PM

View Postcfoley, on 27 January 2014 - 02:27 PM, said:

from the little functional code I've written, unit testing has helped there.


Agreed. However, keep in mind that SQL is a very domain specific language; not general purpose. There's only so much it can, will, and should do. This makes the test domain even smaller. Essentially, testing involves feeding a query all possible data variations. Once you've done that, what else can you do?

View Postcfoley, on 27 January 2014 - 02:27 PM, said:

I know I have written SQL that returned the wrong result in the past. It wasn't until I threw some data at it that I noticed my mistake.


Right. And, once you've seen it work with the data... what is there to test. Also, YOU had to observe the behavior. A test harness would do... what?

For procedure vs. view:
Well, if you can express your request in the form of a SQL query, you're playing to the strength of your database. Databases are most efficient when processing a SQL expressions.

A procedure is always a bit of a hack. You can only ask it one question and the black box that answers might do all kinds of heinous things behind the scenes. It can be the only way to solve a problem, but it only solves one.

A view is transparent. Since it must conform to query rules, there's only so bad it can get. It is also reusable. You can treat a view as a table and ask it any questions you like. A procedure is a one trick pony.
Was This Post Helpful? 1
  • +
  • -

#14 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 3,009
  • Joined: 12-January 10

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 01:04 PM

Hey baav I dont make my company rules i just follow them. your way does look a lot nicer but i have to stick with how they want them done.
Was This Post Helpful? 1
  • +
  • -

#15 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,595
  • Joined: 16-October 07

Re: SQL decomposition and testing (SQLite)

Posted 27 January 2014 - 01:21 PM

@DarenR Understood. You're stuck with what you have.

Just know that WHERE x in (select y... scales for absolute crap. If you should ever get to the point where an IN query takes so long that people complain it's time to suggest a change.

Good luck.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1