Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Experience with additional tables and timpstamp ?

Quote Reply
Experience with additional tables and timpstamp ?
Hi all,
i have added some further tables to LSQL for additional infos, pics and some other stuff.
While adding them to db_nav.html ist very easy to mange them, in most of my tables i use the field LinkID to hold the ID of the row in table Links.
Evrything is fine so far, but if i change only a row in one of this tables, i have no change for the timestamp for that row in the Links table.
So i have to build with build all. Cause i build a lot of additional static detailed pages (for every link till 6) there is a lot of to do for the machine.
One way is to change a dummy field in the links table very time i change a field for that ID in another table, but a better way should be to change the timestamp in the links table automatically.

Has someone done this before?

At the moment i try to find the right places where LSQL does change that thing, as i remember to 1.x we had an extra table to hold changed IDs,
now the timestamp does this.

Quote Reply
Re: [Robert] Experience with additional tables and timpstamp ? In reply to
Maybe some more info:

For every link it should be possible to save a various number of pics,
so i add the table pics with

ID (int)
LinkID (int to hold the ID of the corresponding Link)
Thumbnail (File)
Picture (File)

With a global i could choose all pics for a special Link with help of pics.LinkID
and pass them to the detailed page. (Its quite some more difficult cause you see only the thumbs in rows at the detailed and click to another page showing every single large picture, but it show what i do.)

One the one side itīs perfect to use the stuff LSQL gives to handle a various number of tables (you only need to add it with phpMyAdmin, add an empty .def, add one row to db_nav.html and resync) on the other hand its quite different to find the right place to change the timestamp, isnīt so?

Maybe i should add the files with a normal php and change the timestamp for that link directly via mysql, but i will wait some hours, maybe someone had the same idea before and realize it.

BTW: I had suggest this two years ago to improve LSQL, but there is still now WIZARD to add new tables. Yes, this would be a magic thing and much more work to program, but as i say till three years, LSQL is much, much more than only a linkengine.
If you want i send you some URLs to show what is possible ;-)

Quote Reply
Re: [Robert] Experience with additional tables and timpstamp ? In reply to
I'm not sure what you are asking, and I've read it 3 times.

If you need to update a field in another table, why not just add that line to whatever code you are using to update the first table? It would 2-3 lines of code at most.

PUGDOGïŋ― Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Experience with additional tables and timpstamp ? In reply to
Hey Robert, itīs still you ;-)

Ok, here we go. Imagine a normal Links with all the tables,
then add

Tables with 1:n
Table MoreInfo
-> ID
A lot of fields

Table Pics
-> ID
Some more fields

Every Link could have n times
MoreInfo or Pics. (1 Link, n * further Info)

Tables with n:1
Table Maps
-> ID
A lot of fields

For the Link table i add another field for MAP
so know which ID of a map in MAP is owned by a link.
(I have a lot of Links using that one Map)
n Links, 1 further Info

Clear till now, isnīt it?

To add, mod, del data from this additional tables
i use the admin from LSQL. Alex has programmed it to handle
every table you want. Never tried it ?

Do it. Itīs fantastic!

Add a table test.
Add an empty .def
Edit bd_nav.html in Admin Templates
and enter another row for the select tag like
the other ones, but with "test" instead "Links" or "Category".
Choose that table and resync !!!

Now you have the right .def for it.

If you want add, mod, del data for "test" you can do it, the same as in
Links, Category, Users and so on.

Now imagine some globals that takes data from this table test
and pass it to detailed page.


The problem now is:
If you change data from test, you dont change the Link that ownes
this or these rows in test.

If you build changed you dont update anything cause you didnīt change the Link.

So i need a function inside the admin scripts that knows:
Attention! This is a table that have an LinkID, so please update the timestamp for
that Link with that LinkID as ID ?

More complicated is the 1:n.
The admin script must know:
Attention! This is a table with an 1:n, so find all Links (the IDs)
with the right field holding the ID of the 1:n table and update them with the timestamp.

Thatīs all.

But it seems to me, that this is a lot of work and if i change the admin scripts,
i change the way of work with every table!

Thatīs what i ment with:
Positive: It could handle every table vs Negative: I couldnīt handle something special for a special table.

Instead of this, i would write a small php that does the following:

1. Change some 1:n tables?
Enter LinkID, change timestamp of that Link.
Pass all the direct Links for the admin to add, edit, del for 1:n table 1, 1:n table 2
and so on.
2. Change some n:1 tables?
Pass all the tables, choose one and look for all Links with that ID in a special field,
pass to edit, del this n:1 table.

2b. When adding a new row her, make it possible to enter some IDs for a Link to add this new ID after adding in the right field of this Links.

Hope my english is enough to understand the things.

The best would be, if i could add some more URLs with a function directly next to the list of fields in admin.

Say i do: Edit Link with ID 212, then i got the page with all the fields and the inputs.
Next to the field Map, there should be a link (URL) to enter the function: edit Map with ID that is inside the field Map in Links.

Or with another 1:n table, there should be a link to that Link with LinkID of the 1:n table.

Think i have to write a small admin for my own in php, that does that stuff directly to the mysql without touching the admin scripts of Links as i do it for some other things, too.

BTW: While i have implemented that imagemagick stuff for adding pics to my Links without touching Links, i still have a problem to fetch the location when a file is saved with HASH.

If you watch the file_table for Links, you see that you could choose almost every information you need.
ID, Name of the JPG and so on.

But cause the file is hashed you dont know this part of the URL.

... /images/*/ID_PicName.jpg
while * = 0 to 9.

Do you have a clue how to fetch this with php or without that

my $fh = $links_db->file_info( 'Bild', $id );
my $location = $fh->File_RelativePath;