Gossamer Forum: Products: Gossamer Links: Discussions: Generate Form Fields from a different LinksSQL Instance

Enthusiast (524 posts)

Oct 18, 2002, 6:25 AM

Post #1 of 24

Shortcut

Generate Form Fields from a different LinksSQL Instance

Heya all,

A while ago I asked how to automatically generate dropdown or form fields from your DB. Alex came up with some code that works fine for generating this from the tables the local LinksSQL installation. (See that post here)

I have 4 installations of LinksSQL and would like to generate form fields in the one install from DB values in the tables of another. Both instances of LinksSQL is on the same domain.

In other words, I have a Gear Review db with all the brands of gear listed in DB1. In DB2 is a Stolen Gear db and I'd like to automatically generate a dropdown of all the possible gear brands from DB1 for them to choose from when they list a stolen item.

Here's the piece of code Alex wrote to generate it from the local DB:

Quote:

So let's assume we are dealign with the Status field in the User table which has values 'Not Validated', .., 'Administrator'. We want to build a select list out of this now. I think this would be the quickest way:

Code:
sub {    
my $html = $DB->html ( ['Users'], $IN );    
return $html->select ( { name => 'Status', value => 'Default' }); }

Any ideas on how to modify this to do what I described above? Smile

Safe swoops
Sangiro

Nov 4, 2002, 3:33 AM

Enthusiast (524 posts)

Nov 4, 2002, 3:33 AM

Post #2 of 24

Shortcut

Re: [sangiro] Generate Form Fields from a different LinksSQL Instance In reply to

Bumping this one up to see if someone can help...

Safe swoops
Sangiro

Mar 23, 2003, 1:14 PM

Enthusiast (524 posts)

Mar 23, 2003, 1:14 PM

Post #3 of 24

Shortcut

Re: [sangiro] Generate Form Fields from a different LinksSQL Instance In reply to

Trying again... any help? Smile

Safe swoops
Sangiro

Mar 23, 2003, 1:45 PM

Paul

Veteran (19537 posts)

Mar 23, 2003, 1:45 PM

Post #4 of 24

Shortcut

Re: [sangiro] Generate Form Fields from a different LinksSQL Instance In reply to

If I understand what you mean you want to connect to a second database?

If so you'll need to create a new GT::SQL object pointing to the other def directory and also using your different username/pass/db/host.

Mar 23, 2003, 2:49 PM

Enthusiast (524 posts)

Mar 23, 2003, 2:49 PM

Post #5 of 24

Shortcut

Re: [Paul] Generate Form Fields from a different LinksSQL Instance In reply to

Paul,

I'm not technically trying to connect to a different database. I'm trying to parse a global in one instance of LinksSQL that will pull data from tables associated with another instance of LinksSQL.

When you install LinksSQL you put a prefix on all your table names. With multiple installations you can have multiple "Links" and "Users" tables in one database, with different prefixes.

In a piece of code like the one below, you simply refer to the table name, without the prefix...

Code:
sub {     
my $html = $DB->html ( ['Users'], $IN );     
return $html->select ( { name => 'Status', value => 'Default' });  
}

What I'm trying to do is a similar global in LinksSQL but get the values from the fields of installation with a different prefix. Sorry for the long explanation. Hope this is clear.

Safe swoops
Sangiro

Mar 23, 2003, 7:43 PM

Veteran / Moderator (6956 posts)

Mar 23, 2003, 7:43 PM

Post #6 of 24

Shortcut

Re: [sangiro] Generate Form Fields from a different LinksSQL Instance In reply to

Hi,

I think I know what you are asking.

I have not yet found a way to get around the prefix thing.

I asked alex (I think) if there was a way to pass in a parameter to turn off the prefix, but didn't get an answer (but I have had bad email problems).

Vaguely, in some version of links, I remember a switch that turned the prefix on/off by bypassing the test (I think).

PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Mar 25, 2003, 3:58 PM

Enthusiast (524 posts)

Mar 25, 2003, 3:58 PM

Post #7 of 24

Shortcut

Re: [pugdog] Generate Form Fields from a different LinksSQL Instance In reply to

Quote:

I asked alex (I think) if there was a way to pass in a parameter to turn off the prefix, but didn't get an answer (but I have had bad email problems).

Yeah - I think you're onto it. Obviously the alternative would be to find a way to not turn it off but to specify a prefix of the table that you want it to look at...

With multiple installations of LinksSQL this would be very useful. For example: I have a Links instance that lists and manages skydiving Gear. I have another that manages my User Profiles. It would be cool if people could select the gear they jump (in their User Profiles) from a dropdown generated from the values entered through the first (gear) instance. This way I don't have to create an entire new list of options for them to choose from and, as I keep my gear database up to date, the choices they have will always be current and relevant. You get the idea.

Safe swoops
Sangiro

Mar 25, 2003, 4:44 PM

Paul

Veteran (19537 posts)

Mar 25, 2003, 4:44 PM

Post #8 of 24

Shortcut

Re: [sangiro] Generate Form Fields from a different LinksSQL Instance In reply to

If I understand what you want correctly then it is very simple. Just call $DB->prefix with your new prefix as an argument, eg...

$DB->prefix('foo_');

Mar 26, 2003, 1:53 AM

Veteran / Moderator (6956 posts)

Mar 26, 2003, 1:53 AM

Post #9 of 24

Shortcut

Re: [Paul] Generate Form Fields from a different LinksSQL Instance In reply to

Hmmm... there is a problem with that. It seems to be really meant to be used at initial set up, not as a run-time option.

Code:

Table Prefixes[/url]  

GT::SQL supports the concepts of table prefixes. If you specify a prefix using the accessor, it is saved in the database.def file and will be used in all future calls to table(),editor() and creator().

To set a prefix:  

    $db->prefix("foo");  

to get the current prefix:  

    my $prefix = $db->prefix;  

What this will do is prepend 'foo' to the beginning of every table name.

If you use that, you alter the access method. You need to reset the prefix, or you stop the system from working. Seems dangerous, at best, disasterous at worst.

PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Mar 26, 2003, 2:33 AM

Paul

Veteran (19537 posts)

Mar 26, 2003, 2:33 AM

Post #10 of 24

Shortcut

Re: [pugdog] Generate Form Fields from a different LinksSQL Instance In reply to

Not really, you just call $DB->prefix again to reset the prefix. Not hard =)

Mar 26, 2003, 4:47 PM

Veteran / Moderator (6956 posts)

Mar 26, 2003, 4:47 PM

Post #11 of 24

Shortcut

Re: [Paul] Generate Form Fields from a different LinksSQL Instance In reply to

The problem is that it says it WRITES to the database.def file.

If that's so, any running implementation (instance) of links will have the wrong information. If your script terminates improperly, it will also have the improper information.

The part that got me is that it _writes_ to the file, not simply update the instance of the database object.

PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Apr 10, 2003, 11:04 AM

Alex

Administrator (9387 posts)

Apr 10, 2003, 11:04 AM

Post #12 of 24

Shortcut

Re: [sangiro] Generate Form Fields from a different LinksSQL Instance In reply to

Hi,

Basically what you need to do is get a new $DB object. In your global:

my $other_db = GT::SQL->new('/path/to/other/install/defs');
my $other_links_table = $other_db->table('Links');

and you can go from there.

Hope that helps,

Alex
--
Gossamer Threads Inc.

Apr 10, 2003, 4:59 PM

Veteran (2312 posts)

Apr 10, 2003, 4:59 PM

Post #13 of 24

Shortcut

Re: [Alex] Generate Form Fields from a different LinksSQL Instance In reply to

Alex,

Code:
    use GT::SQL;  
    my $obj = new GT::SQL '/path/to/def_newdatabase';  
    $obj->set_connect ({  
                        driver     => "Mysql",  
                        host       => "localhost",  
                        port       => 3243,  
                        database => "mydatabase",  
                        login      => 'user',  
                        password => 'password'  
                    });

1) Will that solution work, if you want to connect to another database, and you don't have the def files created, just a dir named /path/to/def_newdatabase?
2) Will the $obj->set_connect create the database.def file and re-sync the tables?
3) Is there a better solution?
4) Or other kind of solution?

Best regards,
Webmaster33

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

Apr 11, 2003, 9:23 AM

Alex

Administrator (9387 posts)

Apr 11, 2003, 9:23 AM

Post #14 of 24

Shortcut

Re: [webmaster33] Generate Form Fields from a different LinksSQL Instance In reply to

Hi,

1. Yes, that will work.
2. Yes, it will save the database.def file in that directory. But no, it won't re-sync any tables.

Other solution to do what? =)

Cheers,

Alex
--
Gossamer Threads Inc.

Apr 11, 2003, 9:41 AM

Veteran (2312 posts)

Apr 11, 2003, 9:41 AM

Post #15 of 24

Shortcut

Re: [Alex] Generate Form Fields from a different LinksSQL Instance In reply to

Quote:

2. Yes, it will save the database.def file in that directory. But no, it won't re-sync any tables.

Oh, so after doing the set_connect, I have to do re-sync myself this way:

Code:
$DB->table("tablename")->creator($table)->load_table (one by one for each tables) 
or 
Links::SQL::load_from_sql() (to re-sysc all tables used by Links SQL)

Is that correct?

Quote:

Other solution to do what? =)

I meant if there is other solution to connect other database from Links SQL.
Just being curious & eager for knowledge Smile

Best regards,
Webmaster33

Apr 11, 2003, 9:48 AM

Alex

Administrator (9387 posts)

Apr 11, 2003, 9:48 AM

Post #16 of 24

Shortcut

Re: [webmaster33] Generate Form Fields from a different LinksSQL Instance In reply to

Hi,

Yes, you would call load_table() to create a .def file off of an existing table. I don't think Links::SQL::load_from_sql() would work as it's going to use $Links::DB as the GT::SQL object.

Cheers,

Alex
--
Gossamer Threads Inc.

Apr 11, 2003, 10:07 AM

Veteran (2312 posts)

Apr 11, 2003, 10:07 AM

Post #17 of 24

Shortcut

Re: [Alex] Generate Form Fields from a different LinksSQL Instance In reply to

Quote:

Yes, you would call load_table() to create a .def file off of an existing table.

Good.

Quote:

I don't think Links::SQL::load_from_sql() would work as it's going to use $Links::DB as the GT::SQL object.

Yes, that's true.
If there is $Links::DB object already exists, then we are in trouble, since we want to operate on a new db connection, and re-sync tables from them.
1) Could be possible to change the $DB object, since it's just a hash reference, so I can touch/modify it like any other variable. Would this action cause any problems?

2) Could be possible to have improved the GT::SQL modules in later LSQL release, so the methods should not be based directly on $DB object? So we could anytime pass a new database connection object, to be the database action based on the new passed in database connection?
Uh, that sounds very confusing. Angelic

I just try to find solutions to handle multiple databases with existing modules, like Links::SQL::load_from_sql(), which is based on existing GT::SQL object.

Best regards,
Webmaster33

Apr 13, 2003, 9:04 PM

Veteran / Moderator (6956 posts)

Apr 13, 2003, 9:04 PM

Post #18 of 24

Shortcut

Re: [Alex] Generate Form Fields from a different LinksSQL Instance In reply to

Alex,

What you are saying, I think, is if you have two installs of Links SQL (or maybe any other GT::SQL based product) you can request a database object from that install (on the same server, same access to the perl, etc), and, requests made on that object will access the database tables in the install they are from.

Calls within the script, though, will still access the local paths (the inits in the top of the scripts).

To pass data between the two installs, you request both a DB1 object and a DB2 object, then copy the results from the query on DB2 to DB1 via some software process (simple or complex).

Standard methods will work on both DB1 and DB2 properly?

(pseudo code)

$S = $DB1->fetchrow;
$DB2->Insert ($S);

In the above problem, this would get around the prefix issue, by actually using the configuration information of that install?

I can see potential problems here, but not knowing (and purposely avoiding) the deep internals of the SQL module, and init process, is this possible?

In otherwords, is this how it actually works?

If so, I'm _really_ impressed on the encapsulation/objectification :)

PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Oct 26, 2003, 5:43 PM

Enthusiast (524 posts)

Oct 26, 2003, 5:43 PM

Post #19 of 24

Shortcut

Re: [pugdog] Generate Form Fields from a different LinksSQL Instance In reply to

Guys,

Just to wrap this one up. Maybe someone else can use this.... Smile

Alex solved this problem for me. The global below generates a drop down from another LinksSQL database. In this case it builds a drop down of all the "Titles" where the "Purpose" = "Main" and the link is validated.

In my case I'm using this global in GForum to build drop downs from values in a LinksSQL database. It assigns the values to user_main in GForum and will also show the appropriate option as "selected" if user_main already has a value.

Code:
sub { 
    my $db  = GT::SQL->new('/path/db/dropdown/options/cgi-bin/links/admin/defs'); 
    my $tbl = $db->table('Links'); 
    my $selected = GT::Template->tags->{user_main}; 
    $tbl->select_options('ORDER BY Title'); 
    my $sth = $tbl->select(['Title'], { Purpose => 'Main', isValidated => 'Yes' }); 
    my $output = '<select name="user_main" size="1"><option></option>'; 
    while (my ($title) = $sth->fetchrow_array) { 
        $title = $IN->html_escape($title);          
        if ($title eq $selected) { 
           $output .= "<option selected>$title"; 
        } 
        else { 
           $output .= "<option>$title"; 
        } 
    } 
    $output .= "</select>"; 
    return \$output; 
}

This will probably also help solve this problem.

Thanks Alex. Enjoy!

Safe swoops
Sangiro

Last edited by:

sangiro: Oct 26, 2003, 5:53 PM

Feb 11, 2005, 4:42 AM

kamalrajm

Novice (23 posts)

Feb 11, 2005, 4:42 AM

Post #20 of 24

Shortcut

Re: [sangiro] Generate Form Fields from a different LinksSQL Instance In reply to

Hi there,
I need directions for creating multiple instances of LinksSQL so that at one moment 2 users can connect and create there own html pages.
Can they do it using the same database which i have created right now or it would be a separate database and separate set of templates for them or probably a separate server all together ?

Kindly suggest.

regards
Kamal

Feb 17, 2005, 11:47 PM

kamalrajm

Novice (23 posts)

Feb 17, 2005, 11:47 PM

Post #21 of 24

Shortcut

Re: [kamalrajm] Instances of LinksSQL for 2 users In reply to

Hi Guys,
Am still waiting for a reply to my previous post.

Just need to know the ideal way to get 2 users working simultaneously on a particular Links SQL instance.
Can this be done or Links SQL is only suited for single users ?
I believe we will face some problems during build all but can it be avoided ?

regards
Kamal

Feb 18, 2005, 12:23 AM

Veteran (2312 posts)

Feb 18, 2005, 12:23 AM

Post #22 of 24

Shortcut

Re: [kamalrajm] Instances of LinksSQL for 2 users In reply to

Quote:

get 2 users working simultaneously on a particular Links SQL instance

What do you mean by this?

Links SQL is a multi-user software.

If you mean, that how many users can access the pages of Links SQL, then it can serve even hundreds of users at the same time (with correct setup and hardware).

If you mean, that how many Editors can access Links SQL editor interface, then it can serve many of users at the same time

If you mean, that how many Admins can access Links SQL admin interface, then you should not give admin access to more users (except if you trust them 200%). But more users could use the admin interface at the same time.
Earlier there were some bugs on the admin interface, where file locking was not used, but I hope these were fixed in the meantime.

This thread was about the solution how to connect multiple Links SQL databases, but this is advanced development solution. It is not for beginners.
99,5% of LSQL users will never need a solution like the one, discussed originally in this thread.

Best regards,
Webmaster33

Feb 18, 2005, 3:01 AM

kamalrajm

Novice (23 posts)

Feb 18, 2005, 3:01 AM

Post #23 of 24

Shortcut

Re: [webmaster33] Instances of LinksSQL for 2 users In reply to

Thanks for replying,
i was talking about admins and the fact that can they do development together without actually hitting each other's templates during build all process.
Then probably later on get in sync with each other with their respective template changes and database changes...
and yes i do trust the other person 200% :)

regards
Kamal

Feb 18, 2005, 3:20 AM