Author Topic: SQL Projects and tables  (Read 3293 times)

Absinthe

  • Guest
SQL Projects and tables
« on: August 08, 2009, 11:18:37 pm »
When working with a database I use a PROJECT with some very specific structure. For instance:

I will simplify this for brevity but conceptually the layout is right

my project (prj) goes in a folder:

c:\sandboxes\projects\Install_<ProjectName>_<VersionNumber>\

If my project was for a database called Test_Data and it was the first one it would be
c:\sandboxes\projects\Install-Test_Data-1.0.1\Test_Data.prj
I assume this is my "Project Directory"

The source files in this case would reside in:
c:\sandboxes\packages\<ProjectName>\
With no version information. This is what I have defined as the "Working Directory" in the project properties

Which in this case would look like:
c:\sandboxes\packages\Test_Data\
And have directories like:

c:\sandboxes\packages\Test_Data\Tables\
This will contain my table definitions in files named like:
    dbo.Customer.sql
    dbo.Invoice.sql
and so forth with the first part of the name being the "Owner" and the second part being the actual table name.

c:\sandboxes\packages\Test_Data\Views\
c:\sandboxes\packages\Test_Data\Triggers\
c:\sandboxes\packages\Test_Data\Stored Procedures\

That is all background information to the next set of questions.

Since, within the confines of my project definition, I have access to all the table definitions (DDL), I would like to be able to use the data they contain to do some intelligent coding. For example, if I were doing a SELECT statement, it is very common to specifically name each column but in large tables that becomes a lot of typing to a visit to some other tool to accomplish it.

So I want to be able to prompt for a list of table names. These would be almost equivalent to the list of files in the tables subdirectory to my working directory (not my project directory)

After that, I would like to use the text in those table definitions to generate a list of attributes, or arguments, or where clause, or even allow me to intellisence what follows the table name and a period.

Can anyone give me a push in the right direction to get me started with this?

Graeme

  • Senior Community Member
  • Posts: 2379
  • Hero Points: 314
Re: SQL Projects and tables
« Reply #1 on: August 26, 2009, 11:43:52 am »
I don't know if you're familiar with Slick C and macro programming but here's a rough outline of what you might have to do.  In slick help, you'll need to look at "macro functions by category" and various topics such as file list box, list box, edit control functions, file functions and string functions.  You can also search the slick macros folder for examples of the use of a file list box etc.

This should get the workspace directory but I haven't tried it.
_str wd = _GetWorkspaceDir() ;

You can probably use a file list box to present a list of filenames.  I've never used a file list box so I don't know how it works but I'm guessing you could do
my_listbox._flfilename('*.sql', wd :+ '/Tables/');

then you would use the on_change event to get the name of a selected file

my_listbox.on_change(int reason)
{
    if(reason == CHANGE_SELECTED)
    {
        // maybe call _lbget_seltext - see list box methods in the help
    }
}

If the file list box doesn't work, you can use _sellist_form to present a list of choices - see _sellist_form in the help and examples.e in the slick macro folder.  You probably have to use the file_match function to get a list of files - see this thread http://community.slickedit.com/index.php?topic=847.msg3680#msg3680

Having got the name of a file you can use the parse statement to get the name of the table - see parse in the help.

To read the actual .sql file, you can use _open_temp_view to get the file into a hidden buffer, then use editor control functions like top() and get_line(), get_text(), or cur_word() to read the text from the file and maybe the parse statement to parse it. 

To present a drop-down "intellisense" list, maybe look at _auto_complete_list_form and how it's used in auto_complete.e  - in sysobjs.e you can see it's declared as a borderless form.

Graeme