tablemanager description a mess in content types admin / storing tablemanager data/ currency field sorting

Nah I'm afraid it's a necessary evil so to speak! I borrowed *cough*stole* the coding idea from the ecommerce module at the time I wrote the original module and tbh, I've not looked at it again with regards to changing it... Maybe I should, that was quite a few years ago and maybe things are different now... And I have always thought it looks rubbish myself...

The reason for it is because neither tables nor the table entries are 'nodes' and to allow them to be 'enter-able' from the create content screen you need to out-fox Drupal a little bit... I've had issues in the Drupal core queue for years now which go completely unanswered as no-one but me considers that the requirement to add anything to the create content screen which ISN'T a node is important. Which is very annoying... Especially as they've essentially 'closed-shop' in Drupal 6.x hence why the permissions are currently buggered... That'll probably be an issue until someone more knowledgeable helps me out, I doubt I'll be able to fix it myself - it's an impossible situation. If I solve it, it'll only be by luck... And then like with the delete columns problem with Drupal 4.7.x and 5.x, they can easily just change core and break functionality again. That's the problem with trying to out-fox Drupal... Some perhaps could view it as a security flaw and shut it down? For me though, I *require* it... I remember that even the Gmap module used to allow you to have a 'create macro' entry in the create content page, which well... Is where it should go surely? Well, it's certainly not there any more - in fact it's not been there for ages! It gets tiring trying to get Drupal to do things it doesn't want you to do... Drupal is supposed to be flexible, but on occasion it can be your nemesis, making you run around in pointless circles to achieve the 'effect' of functionality which isn't strictly allowed.

...Still on the plus side, only users who have complete administrative control over content types will see the messiness! And on the plus side x 2; Tablemanager v2 creates tables as nodes (which you can still use as filters too) so it's only the entries which are 'bogus'. I have toyed around with making the entries as nodes as well - all this is partly why I've never released any code. I like toying with changing it completely while it's bare-bones, that way I don't have to keep providing upgrade paths in .install files as regardless of its dev/ alpha/ usability stage - someone will use it and complain if something breaks...

Wow! That's a whole lot of guff... Sorry about that!

Pobster

Ah see now I meant the /node/add (access through 'create content') screen. Which is where the content-types all show up... I've hit a crossroads here... I could take the simple route and just create all table entries as nodes... It's not so much a problem, it just might create enormous node tables for people with enormous tables... I kind of consider that they're easier to keep track of when they're all nicely tucked into their own tablemanager_data db table? That'd then cause issues with say... node_clone, all node_access modules, ...forward module perhaps? The real problem here is that there are just so many different ways I could store the table data, that I *still* haven't really decided what is best? I've stuck with the serialized arrays simply because that was the first way I programmed it (which I borrowed *cough*stole* from ... the menu module I think! Was nearly three years ago now!) But I know (now anyway) that this is probably the worst possible way to store it... Reason being is that you can't do any sql sorting/ hiding/ magic as the rows are serialized and look like gobbledegook to mysql. So... Every time a table is viewed (even for a new page of the same table) it's built from the ground up, in FULL and then whatever is needed is taken from it. Awful... And slow...

Here's what I've been playing with;

A table cache.

Nice idea, all it does is cache an entire table in a single db entry. This means the module doesn't have to build the table from the ground up to act on it, it can just whip the table out 'ready-formed' and then act on it then cutting out the whole building process. Cons... Obviously unless you cache all sortable views, only the default page view is cached. Therefore its pointless... It's only getting around a problem which really should be tackled in another way in the first place... Maybe a cache would still be a good idea for the future, but in this context - nope... It's like sticking a band-aid over a zit when you really should just squeeze the zit and put cream on it. Yes I know that's disgusting, but I couldn't think of a better analogy...

Getting shot of serialized arrays pt.1

The serialized arrays definitely have to go... I've already written a nice engine to convert them into other things, but finding the best way to store them is complicated due to their '3D' nature - eg. If you create a table to contain them with a set amount of 'columns' for every table, what happens when you eventually get a long table which has more columns than you can store? I mean obviously this is also a bad way to store them, I'm just using it as an example to describe the issue. Sensibly, what you'd do is create the table like this;

• tid
• uid (? perhaps a separate table for this? Else you're repeating yourself - there's only one 'data' cell stored here)
• column id
• row id
• data
• input format (again, perhaps a separate table to avoid repetition)

But in doing all this I'd be moving away from Tablemanager v1.5 and into the realms of Tablemanager v2... Obviously that's what I want for the future, but it's not necessarily something which would benefit everyone right now. Mainly because, I haven't settled on this problem or a couple of others either...

Getting shot of serialized arrays pt.2

A similar approach which is what Tablemanager v2 currently does is to create a separate db table for each table you create (the same way cck works with new content types). This is a great way to do it, but again it has its disadvantages... That said, they're far outweighed with advantages hence this is currently my method of choice...

db table = tablemanager_table_test

• rid (row id)
• uid
• data
• data
• data
• data
• input format

So no need for tid as the db table is relevant to the tid, the data is stored in the appropriate 'column'/ db table of which a separate table contains the 'actual' column heading whereas this table uses a 'machine-readable' name (same as cck allows). It's far more obvious for users to create their own queries to fetch data from their tables as well as they don't have to mess with complex serialized arrays or grouping tids, rows and columns - the table rows are nicely together already in db rows, even phpmyadmin will display the information correctly. It's all round nice... But... And there's always a "but" sql sorting is shit. Yep, shit... Sure you can do fancy things with it, and ignore case easily enough but one of the things that I require is the ability to sort by price/ number and this is where mysql falls flat on its face. It treats everything as a string and sorts like this;

• 0
• 1
• 10
• 11
• 2
• 22
• 3

Which is alphabetical... You can convert the string into numeric by adding "+ 0" to the sort query, but when the data contains a currency symbol it messes things up (as they're not numbers)... I can't seem to find a happy medium/ complete way to achieve everything I require? Nothing is perfect?!!!

Anyways... The point of all that (also guff) was that it currently really is a necessary evil ;o) The other ways create more problems than they solve, even though that last way is exactly how Tablemanager v2 gains so much more functionality than the current version... And at the same time, is exactly why it's not released yet.

Pobster

IMO: For the simple stuff * I * want the module tablemanager use there is no need of making each row entry a drupal node.

For the version which creates a database table for each tablemanager table:
Why not choose an appropriate field data type for each column when creating or modiying a tablemanager table?

For multi currency field maybe choose decimal plus an extra column for holding the currency (dollar,euro,..) as enum or string.

This would be much cleaner than holding numeric data in text/varchar fields and databases/SQL like that much more for sorting/indexing etc.

Further I tested (only mysql) sorting a mixed data field (an decimal amount with a currency sign saved in a varchar(100) - don't do that in your app!) and following SQL is working too:
SELECT field1 FROM `test_tablemanager1` ORDER BY CAST( field1 AS DECIMAL ) ASC;

output:

+--------+
| field1 |
+--------+
| 12$    |
| 13€    |
| 23$    |
| 32.33€ |
| 44€    |
| 65.3$  |
| 66.40$ |
| 87.2$  |
| 123€   |
| 123.4$ |
+--------+

Even on such mixed data fields (just avoid if possible) SQL can help you out of the mess.

The output you've given as an example is a little 'unnatural' given that you wouldn't normally put a dollar sign at the end of a price?

What I'm toying with is complete control over fieldtypes... When you create a table, it's now created as a node and as such means I can use tabs on the node using Drupals api (as recommended) in the same way you get 'edit' tabs, etc. I'm thinking I can use these for settings per fieldtype? I just don't know how messy it'll get... At the moment it's only a thought in my head... I'm thinking for example, you'd create a currency fieldtype for column 1 - set the currency in the same way ecommerce does it (I've used this code before so I'm familiar with it), then the module generates a new db table containing this new column which will be int(4) or whatever (you'd set the max length in the settings). The table wouldn't contain the currency symbol, that'd be stripped - it'd only contain the number and so can be sorted easily enough, the currency information would be stored in either a settings table or maybe create some specific Drupal variables to contain it? Dunno, it's all a lot of thinking about stuff at the moment. We'll see...

Pobster

The output you've given as an example is a little 'unnatural' given that you wouldn't normally put a dollar sign at the end of a price?

That depends on Language.
In Germany the currency sign is normally written past the amount. In your country it seems to be written before. Another reason to not mix currency sign and amount in one database field.

but one of the things that I require is the ability to sort by price/ number and this is where mysql falls flat on its face.

I wrote my comment #4 as anwser to this in comment #3.

You're not considering the restrictiveness of it? I can't exactly write a caveat saying "only use this if you're German"!!! I think there must be a better way, I've just yet to find it.

Pobster

Closing as D6.x is now unsupported.