Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Add a table to Links and Admin screen

Quote Reply
Add a table to Links and Admin screen
How to add a table to admin search.
I have found following message. however it seems it is for a early version of linksql.

is some one sucessfully do it in version 2.11?

http://gossamer-threads.com/perl/gforum/gforum.cgi?post=71245;search_string=edit%20new%20table;#71245
Quote Reply
Re: [courierb] Add a table to Links and Admin screen In reply to
Ok, watch out since this is going to be a little long winded. I'm assuming that you have a unix system to which you have telnet access and also a little bit of unix and perl experience.

First off, there's a few things to be aware of. The administration panels for Links SQL are quite flexible, if you look in your cgi/admin/defs directory, you'll see lots of different .def files. All .def files, with the exception of database.def correspond to a database table that Links SQL uses. As well, if you're using a "prefix" for each of your tables (so you don't have table name collisions with other applications) you'll also see that all the .def files will be prefixed with that value.

For the database administration screens you won't need to do too much work. Try this, you probably already have a database table called "ClickTrack". Try the following URL (reformatted so that it functions with your system)

Code:
http://www.yourhost.com/.... admin/admin.cgi?do=editor_table_form&db=ClickTrack

You can do that with any of the other tables that you find in the defs directory. Just remember to remove the prefix from the tablename when you're passing it to the admin.cgi. As an example, if the prefix was lsql_, the def file may be named lsql_ClickTrack.def but you must do ...form&db=ClickTrack. Not ...form&db=lsql_ClickTrack

If you want to create an additional table, using your favorite table creator make a new table as you'd like. There is only one restriction, if you are using prefixes, you must use that prefix as part of your new tablename. Add the appropriate indexes, but dont add things like fulltext indexes quite yet, that's probably better handled by Links SQL.

Once the table is in the database, its format must be converted into a .def file so Links SQL will recognise it. I've uploaded a script, defops.cgi that will do that job for you. Before you upload it to your server however, make sure you change the paths in the script to reflect your installation. It won't work otherwise. I'd recommend uploading it to your admin directory so it's password protected. So to pull the data: if you have a prefix of "lsql_" the table you wanted to pull would be named something like "lsql_Notes". With that tablename, call defops.cgi by doing:

Code:
defops.cgi?db=Notes

This is a script I use for myself so the error reporting is sorely lacking Tongue But if you see no message, the process was probably successful. Check in the .def path to make sure.

Finally, in your admin templates, look for a file called db_nav.html and add your tablename to the option list like the following:

Code:
<option>Category</option>
<option>Links</option>
<option>Users</option>
<option>Notes</option>
<option>Reviews</option>

It should now be available in your admin panel.

A couple of tricks for the road.

If you're going to create the table, try naming the primary key "ID", it will make the quick search function properly on your system.

For indexing, go to the properties and change the indexing there. It will probably show "nonindexed" initially but set your weights on columns and searching will be functional from then on. (you'll have to write some custom code to take advantage of this, however, which is beyond the scope of this post)

Not a trick, but the next time you make a backup, Links SQL will backup this new table as well.

Finally, a really cool trick is a global like the following. This assumes we're using the previous example of lsql_Notes and that is has a primary key named "ID" .

Code:
sub {
my $id = shift;
my $tbl = $DB->table('Notes');
my $rec = $tbl->get( $id ) or return;
my $tags; @$tags{ map { "Notes_" } keys %$rec} = values %$rec;
}

This will allow you to pull the record information for a particular record into any of your templates as long as you know what the ID for that record is. In this case, once the call has been made, all the columns of Notes will be available as a tag, just that you'll have to prefix the column name with "Notes_".

Hope this helps. This is not an "official" feature so things may change in the future or suddenly stop working. You may want to look into using a plugin instead at some point.
Quote Reply
Re: [Aki] Add a table to Links and Admin screen In reply to
Hello Aki!

I have done this years ago. There is a build in Resnc function that does it what you suggested since many years without a seperate cgi. With the current version anathor simple way is to do the following:
  1. Get the table listed in the Admin. In the db_nav.html enter the name of the table, just the name.
  2. Tell the Admin.cgi the table name or which table to capture! I upload a universal def file in this post with a name prefix_table.def
  3. Change def name to correct table name. And change the prefix exactly the one of all the other tables, and the table name that was inserted for others. For e.g. Prefix = Gossamer and table is called LinksSQL then the def will be = Gossamer_LinksSQL.def and copy it to the def directory with correct permissions.
  4. Resync the def file. From the Admin, select the table and choose by properties and then all the functions are available. First you must Resync the file so have the form constructed. Then you can play with the form or the table!!!


Aki will laugh after seeing the universal def file I have attached. I was actuallly wondering why is this simple function not available as a general function as it would be so so so easy to impement.

Last edited by:

rajani: Sep 8, 2002, 8:17 AM
Quote Reply
Re: [rajani] Add a table to Links and Admin screen In reply to
Hmmm.. That's a cool little method too. Guess I'm just in my rut. The script I attached was a converted version of a command line script that pulls databases into .defs as well as pushes .defs into databases.
Quote Reply
Re: [Aki] Add a table to Links and Admin screen In reply to
...and you "use" strict twice because your code is so bad that it needs to be twice as strict right? Wink
Quote Reply
Re: [Aki] Add a table to Links and Admin screen In reply to
Hello Aki!

So now you know how we non-perl people try to come-over with such naive tricks!

But to carry your idea further, it may be interesting to get a list of tables and show it into the Admin pull-down menu. How could I do this? I am simply converting your routine into my idea knowing that it cannot work, but just to let you know my idea Laugh :

1. In the db_nav.html add


<option><%Links::Admin::table_list%></option>


2. In the Admin.pm add at last a new sub_routine

Code:


sub table_list {
my $name = shift;
my $db;
my $show_table = $db->select ("SHOW TABLES");
my $table_list = $show_table->get( $name ) or return;
my $table_name;
@$table_name { map { "$show_table" } keys %$table_list} = values %$table_list;
}


So instead of listing the column names one can list table names in the pull out menu. This would help many users. I have done it manually but some tricks like this may be interesting. Like for e.g.

Code:


my $query = qq!

SHOW TABLES
!;
return $query;
Quote Reply
Re: [rajani] Add a table to Links and Admin screen In reply to
Difficult. The problem is that if you have access to all the tables, it's far too easy to break the installation. It's also confusing.

Code:
SHOW TABLES

Works in mysql but not in other database managers. In fact, the techniques discussed in this thread may only work in mysql (and possibly postgres).
Quote Reply
Re: [Aki] Add a table to Links and Admin screen In reply to
Hello Aki!

Oh, really!

But thanks anyway for your replies.
Quote Reply
Re: [Aki] Add a table to Links and Admin screen In reply to
Hello Aki!

Today, I saw in the phpMyadmin and thought of drawing your attention that it perhaphs_IS_possible may be also in perl.

Code:
/**
* Displays the table structure ('show table' works correct since 3.23.03)
*/
// left.php



// Gets the tables list per database
for ($i = 0; $i < $num_dbs; $i++) {
$db = $dblist[$i];
$j = $i + 2;
if (!empty($db_start) && $db == $db_start) {
$selected_db = $j;
}
$tables = @PMA_mysql_list_tables($db);
$num_tables = ($tables) ? @mysql_numrows($tables) : 0;
$common_url_query = 'lang=' . $lang
. '&amp;convcharset=' . $convcharset
. '&amp;server=' . $server
. '&amp;db=' . urlencode($db);
if ($num_tables) {
$num_tables_disp = $num_tables;
} else {
$num_tables_disp = '-';
}



///////////////////////////////////

<?php
// Displays the list of tables from the current database
for ($j = 0; $j < $num_tables; $j++) {
$table = PMA_mysql_tablename($tables, $j);
$url_title = (!empty($tooltip) && isset($tooltip[$table]))
? str_replace('"', '&quot;', $tooltip[$table])
: '';
echo "\n";
?>
<nobr><a target="phpmain" href="sql.php?<?php echo $common_url_query; ?>&amp;table=<?php echo urlencode($table); ?>&amp;sql_query=<?php echo urlencode('SELECT * FROM ' . PMA_backquote($table)); ?>&amp;pos=0&amp;goto=<?php echo $cfg['DefaultTabTable']; ?>">
<img src="images/browse.gif" width="8" height="8" border="0" alt="<?php echo "$strBrowse: $table"; ?>" title="<?php echo "$strBrowse: $table"; ?>" /></a><bdo dir="<?php echo $text_dir; ?>">&nbsp;</bdo>
<a class="tblItem" id="<?php echo md5($table); ?>" title="<?php echo $url_title; ?>" target="phpmain" href="<?php echo $cfg['DefaultTabTable']; ?>?<?php echo $common_url_query; ?>&amp;table=<?php echo urlencode($table); ?>">
<?php echo $table; ?></a></nobr><br />
<?php
} // end for $j (tables list)
echo "\n";
?>
////////////////////////////////////
Just for your information. Its done very nice in the code.
Quote Reply
Re: [rajani] Add a table to Links and Admin screen In reply to
Yup, mysql is great that you can do that in one query. In fact, mysqlman accessible from your admin panel does that very thing. In contrast, take a look at oracle and what you have to do to get a single primary key http://www.gossamer-threads.com/...orum.cgi?post=215138 (in mysql you can do a "describe tablename" and loop through the results and get the primary key)

So, there are a couple of problems for us to implement a "show tables" solution. One would be database compatibility and another would be database manager errors (if the sql server wasn't running perfectly, you wouldn't even be able to see your menus... big problems on initial installs or if something funny happens to your server) Something like a "show table" can be done, but would require _lots_ of work.

In most cases, I found that if a another table is required, extra programming specific to the implementation of the table is required in which case it's pretty difficult to come up with an abstract solution. It may be better to use a joint installation of Links SQL (For the tree structure) and DBmanSQL which gives you greater control of record ownership as well.
Quote Reply
Re: [Aki] Add a table to Links and Admin screen In reply to
Hello Aki!

Thanks for your feedback! It seems so convincing that to implement anything in this direction would require much more work than actually the return what it can serve. By simply adding it in the menu and copying the universal defs seems to be the easiest solutions instead of a full blast mind blowing script development!

Thanks for your explaination.
Quote Reply
Re: [rajani] Add a table to Links and Admin screen In reply to
THANKS ALL .