Database Changes in 2.1

[butchs]

I like to see "binary" added to the following resource list:
int
string
text
array_int
array_string
date
float
identifier
raw

This way I can store my IPv6 as a numeric value in VARBINARY instead of a string like as follows:

Code Select Expand

$smcFunc['db_create_table']($db_prefix.'IP_storage',
array(
array(
'name' => 'ip',
'type' => 'varbinary',
'size' => 39,

Code Select Expand

$smcFunc['db_insert']('insert',
'{db_prefix}IP_storage',
array(
'ip' => 'string',

[Oldiesmann]

What advantage would this give us over the existing IP storage methods in SMF (note that 2.1 does indeed support IPv6), and how would we validate/sanitize it? (See smf_db_replacement__callback in any of the Subs-Db files if you're not sure what I mean by that).

[butchs]

I like to see binary added.  I am not looking to change how SMF stores IP addresses and do not want to step on toes.  At the time I started I was told SMF was not IPv6 compatible so I did some research and worked on my own code.  I spent some time working out bugs.  I have not seen the existing for comparison (I can only guess), can you point me to the 2.1 subs file where the IP is stored?

I see the future as large numbers stored in VARBINARY(16).  MySql (INET6_ATON) offers routines to convert the address to a numeric value of the address in network byte order (big endian).  The functionality of INET6_ATON et al should be similar to ip2long/ long2ip was for ipv4 addresses but without the sign issue.

Here are some sanitation possibilities: 

• I have seen people use "mysql_real_escape_string".
• With 2.1, if possible, my preference would be using or combining prepared statements via PHP's PDO database abstraction layer with your existing secure DB functions.  PDOStatement::bindParam looks like it may be a fit.
• Convert it to hex.

Oh, and I found a link.

[butchs]

Though this is auxiliary to my request I poked around 2.1 on Github.

Maybe I am missing something but all I saw was CHAR(16) for ip addresses, looks like IPv4 to me.  When working on IPv6, I tried CHAR, then VARCHAR with collation to UTF-8bin but discovered that some characters will not show up from my unpacked binary.  Those same IPv6 addresses worked when I changed to VARBINARY.

I did notice compatibility in banning in 2.1.  There I saw sixteen SMALLINT(255) fields.  With VARBINARY you can eliminate 14 fields.  The ban name did not appear to be capable of holding an IPv6 address...

[Arantor]

-sigh- No you can't eliminate that.

Considering how the ban query is done, you cannot eliminate it like that at all, seeing how you can do wildcards or partial ranges on each octet of the IP address (you have 8 pairs of smallints, since the largest ban you can do is a /64). If you don't understand how something is used, best not to suggest changing it.

CHAR(16) is wrong, SMF should be using VARCHAR(255) but that's not an ideal solution at all.

You don't put compressed strings or raw binary strings in a text field.

[butchs]

Its good to see you back.  I missed your banter....  Me thoughts you were mad at me for being grumpy.  Hey, you have to admit I warned you about my impending extreme grumpiness!

Nevertheless, with respect to the ban query why not save it as something like a big endian number then bring it back to IPv6 and explode the colons ":".  Wham bam you got all eight. 

All the other IP fluff and IP puff, is another matter...  Possibly for another thread?  Based on recent PM observations, it should be debated elsewhere as it appears to have the likelihood of a long drawn out debate...

The whole point of this request (thread), as distorted as it became, was to simply get the binary resource added to $smcFunc .  One has to admit binary is useful.

[Kindred]

So, drop a pull request with your requested changes...   

[Arantor]

Its good to see you back.  I missed your banter....  Me thoughts you were mad at me for being grumpy.  Hey, you have to admit I warned you about my impending extreme grumpiness!

No, I'm just pissed off with everyone at present. Fed up with having to repeat myself over and over and over to people who claim to want to listen but don't.

Nevertheless, with respect to the ban query why not save it as something like a big endian number then bring it back to IPv6 and explode the colons ":".  Wham bam you got all eight. 

Have you ever IP-banned anyone before in SMF?

The formats allowed:
1.2.3.4
1.2.3.*
1.2.3.4-5

That means an implicit range per octet. When IPv6 support was implemented, it was implemented as a mod in a fashion that was deliberately designed not to have to rewrite the guts of SMF. But you'll notice this is how the ban tables are set up - to list the lower and higher bounds of IP addresses.

Like so many things, staunchly sticking to backwards compatibility is fine if you're looking at a temporary workaround for an intermediate release, whilst preparing for a clean slate later. Which, 2-3 years ago, is where 2.1 was. Since 2.1 is now going to be the long term rel-- <censored, too much doom>

All the other IP fluff and IP puff, is another matter...  Possibly for another thread?  Based on recent PM observations, it should be debated elsewhere as it appears to have the likelihood of a long drawn out debate...

Not really a lot of point. Nothing's going to happen to change this in 2.1 even though it really needs it and trying to ban IPv6 at the SMF level is going to be painful since you can't ban individual IPv6 addresses, only /64 blocks but it's not entirely clear that's what will happen.

The whole point of this request (thread), as distorted as it became, was to simply get the binary resource added to $smcFunc .  One has to admit binary is useful.

I have found it useful on precisely one occasion, when I had to store images directly in a database, which is a whole host of WTFery that should never be repeated if at all possible.

As for adding to SMF... aside from the fact SMF has absolutely no need whatsoever at present for a binary type, good luck supporting PostgreSQL or SQLite. It's a mess in both.

The safest way is to either base64 or hex encode it and store it as a string, cutting out the binary aspect anyway. You know, like I pointed out to you repeatedly in PM.

[butchs]

Yesum.  Some times we ses the same things but with different words.

Yet, I see no argument against using prepared statements via PHP's PDO database abstraction layer.  PDO ships with PHP 5.1!  It allows the use of different database servers using the same code base.

[Arantor]

Except that PDO still isn't enabled by default on a lot of hosts even if it is theoretically supported.

Heck, json_encode which isn't even a direct extension was still disabled on a lot of hosts until quite recently, causing at least one emergency patch from WordPress.

The thing about different database servers is that it still doesn't solve half the problems. Yes, it abstracts away the connection stuff and the functions. It doesn't magically rewrite queries for you, so all the problems the current code base has in that regards would all still be there, where MySQL-specific queries have to be rewritten for other systems.

[butchs]

As far as I can tell SMF 2.1 will not be ready for several months (wishful thinking) so why not standardize on the future?  Isn't that why it requires a minimum php 5.3.8?  JSON, PDO and IPv6 integers appear to be the current internet trends of the future.  This stuff is too much for the old transistor bucket, I'm gonna need a new computer...   Dreaming of a supermicro board...

Since JSON is becoming common I assume XML is on it's way out for SMF 2.1?

It is not like SMF 2.1 will be the first to use PDO.  Both CodeIgniter and Dupral have PDO abstraction layers.

[Oldiesmann]

1. If you want proper IPv6 support, feel free to submit a PR for it. Rewriting half of SMF to further future-proof it at this point is very unrealistic, at least if we want to see 2.1 done within the next year (and I'm seriously hoping we can get a beta out by the end of next month at the latest).

2. XML is not on its way out. What does JSON have to do with that?

3. What advantage does PDO have over our current system exactly? I have yet to see any proof that it would be useful. Either way, SMF 2.1 will most certainly NOT be using PDO.

[Arantor]

Not to mention how much *fun* json_encode is in PHP. Like if, say, you're half of Europe.

[butchs]

You my friend need to upgrade to the latest php.  :0  I mentioned JSON only because JSON claims to be the Fat-Free Alternative to XML?

I did not ask to get SMF converted for IPv6 or for the other things that you have no interest in adding.  As someone told me "that is a core thing and someone besides you should do it".  That is fine for a winter warrior like myself.

Alas I did ask for BINARY support. 

[Arantor]

Except the changes in more recent PHP versions don't entirely mitigate what I was referring to.

Yes, you asked for binary support. Something SMF doesn't need. And the only thing you were arguing for its use for was for IPv6 support in your methodology which as pointed out was flawed in a variety of ways, thus there is no real need for BINARY support in the first place.

[butchs]

We agree to disagree.

Needless to say, it appears that you only want things done your way with little or no compromise.  This prevailing wind, intended or not, could scare off other potential programmers who actually may want to support SMF...

[Arantor]

Congratulations for missing the point of everything I've been saying.

I am not opposed to change.
I am opposed to misinformed change.
I am opposed to change for little benefit given the already enormous technical debt presently in SMF and resistant to change that will increase that technical debt.

You want binary. I get that.

But you are approaching it from the perspective of for your use. Except SMF has other priorities - it has to support this in PostgreSQL and SQLite with feature parity. This may not be possible.

Additionally, there is no *use* for it in core SMF. Adding a feature specifically for a mod's benefit is low benefit, low priority. If something in core SMF could use it I might be persuaded to agree with you but the only case presented thus far is IPv6 address support and as I have advised you repeatedly, this is totally unsuitable in the core to use binary support due to the matching requirements that are required (for the trace-IP functionality, which MUST NOT be done on a binary string for performance)

If my attitude puts off this sort of change, frankly that's a good thing. There's already so much code in SMF that no-one understands why it's there or what it does. There's already so much code that is brittle and that changing it presents a practical and maintenance risk, and to increase that for no perceived benefit is a bad idea.

I hate smacking down reality on enthusiasm but frankly that's the role of being competent and being in some kind of curation role for a platform.

[butchs]

WOW!  What an well written rant...  You are improving...  Though I do not agree I do not like to argue over something that will potentially generate work for me...  You on the other hand, do not have that hindrance.

For someone who complains so much and talks about quitting you sure seem to be committed.  Maybe we need a preacher?  When are you going to look into that mirror and admit to yourself that you care and hunker down to do what you love to do?

[Arantor]

No, I need committing, because this ****** isn't healthy. It's like trying to give up cigarettes. You tell yourself you should. You might even try it for a while, but then something happens and you just have one and the cycle repeats again.

http://what.thedailywtf.com/t/i-am-trwtf/1020/14?u=arantor is relevant in this case, too.

[butchs]

Bla!  A man does what He wants when He wants.  If he truly wants to quit, a real man quits!


I used to smoke.   I loved those Winston Lights!   They were wrapped in foil to keep them fresh.  Ooooh so good!  I refused to quit even though the price went through the roof.  When restaurants stopped allowing people to smoke, I boycotted them and went elsewhere.  Some even went to nearby States just to eat and smoke at the same time.  I refused to stop smoking no matter what Government pressure was applied.  Yet, I decided to quit one day.

The freshness pack (foil) did not seem to work very well.  Sometimes the cigarettes will taste great and other times they were terrible.  The quality control was just too much for me to bear one day when I took an almost full cigarette pack and threw it away.  Never to smoke again!


I have been smoke free for 15 years!  So do not tell me you have an addiction.  In my world there is no such thing.  Emotions are black and white.  There is no other eason but you do what you do because that is what you want to do it.  Only when you truly want to quit something:  You quit.  So stop pretending.

[Arantor]

What more do you want me to say? I'm not a real man, never have been. I'm also not like you in so many ways.

[butchs]

Once again, we agree to disagree.   

[Arantor]

Trust me on this. I'm not a lot like you. Do you often act in a self-deprecating way, telling people that you're not nearly as clever as everyone thinks you are (and also genuinely believe that to be the case)?

[mashby]

I have a feeling this topic is going in a weird way.

Facts are black and white. Emotions are rainbows.

[butchs]

Really weird, just wait until you see our Halloween consumes.  We confuse each other all the time and rarely agree.

[Steve]

Facts are black and white. Emotions are rainbows.

Hear! Hear!

[Arantor]

Unless that rainbow has a low HSV value component in which case it's merely shades of black like my soul.

[Steve]

Sometimes you make me roll my eyes ...

[Arantor]

Then it's a small victory, isn't it? Every little victory helps. Great big ones like HMS Victory help more.

[butchs]

When I was young I liked ships like the HMS Victory but now I am more of a USS Constitution type.  Let them English shots bounce off my hull!  I'll do what I want when I want.

[Steve]

My Naval retirement flag was flown aboard the USS Constitution and I was lucky enough to be aboard her during one of her turn-around cruises.

[live627]

Facts are black and white. Emotions are rainbows.

qft