Making Vanilla "Pluggable"

leason

First of all, let me say thanks for such a great forum system. I am a long time web developer in systems such as PostNuke and other open source CMS's and I am constantly frustrated by the forums available such as phpBB. Their security holes, shoddy and difficult to use template systems, and inflexibility is out dated and it is time for a change. I have seen comments on other threads regarding the next version of Vanilla and how it will be easier to "bridge" it to other systems such as Joomla or other CMS's. I am curious as to how that is going to be implemented and I have a suggestion if it is not too late as to how it could be done. One system I have worked with and been very happy with is Chat Blazer. It is a commercial chat system that comes with a java based socket server and a java client. What I like about it is that the configuration of the system consists of several XML files. One of those is a database schema definition. Basically, there is a very easy to understand XML file that maps internal column and table names to a physical database name. All queries are run through an engine that parses the database XML file and translates the internal name to the actual database name. What that allows me to do is plug Chat Blazer into our application and point it at our existing users table. I looked through the code and the SqlBuilder class to see how easy it would be to do this same type of thing but it does not look like the current system could be easily adapted like this. If you would like an excerpt of the database configuration XML I would be happy to get that for you to use as an example/explanation of how it works. Great job on the forum so far, keep up the good work. regards, Lee

Minisweeper

Unless this system is truly incredible, i cannot see mark changing his methods for the next rev (which is expected too shortly for a change like this to occur - then again on his last forum he pretty much rewrote the entire thing in a night when he had a good idea) but it could be interesting for the future. In an attempt to clarify (from what little knowledge i have gained about it on here), in the next version the bridging will become easier because the user control will become an extension - i.e. you could choose to keep the existing (or presumably similar) vanilla user authentication/management methods, or you could create an extension which used the auth/man system from a different piece of software. For average joe this still wont be a walk in the park but i suspect for people with a reasonable grounding in php (as required to make worthwhile extensions anyway) it will be considerably easier than trying to hack at the existing system and make it do something it hasnt been designed to. There are people on here (mark particularly) who could probably give you a better explanation but i hope i've atleast cleared things up slightly for you. I'm sure people would be interested to see this xml format (i'm quite curious about it as a method of migration - but thats a different matter) if you can get one easily. If you have any more detailed queries about the future methods then just ask away.. (or wait and see!)

Mark

That's a pretty cool system. I wouldn't want to do it for vanilla because the parsing of the xml on each query would slow things down a great deal - but I'm going to have to think about it for a bit and see if I can come up with something similar. I see the "external site integration" issue as having two problems: 1. User Authentication 2. User Integration 1. User Authentication: In the new system user authentication (signing in and out, session handling, etc) is all handled through a single class that can be overridden by extension authors. You have a simple interface through which you can access a database and authenticate a user in any method you wish. You can create php sessions, pass cookies to the user's browser, etc. In working with a client on a vanilla integration, I came up with this method, and we were able to integrate logins very easily. They ended up not even changing their login screen. The only thing they had to do was add a VanillaUserID column to *their* user table that pointed their user at a vanilla user. Then when they sign in their user they added that VanillaUserID to their php session. When that user then looks at a vanilla page, the custom authenticator just grabs the VanillaUserID out of the session and passes it back to Vanilla - tada, vanilla knows who you are. 2. User Integration: This is definitely the tougher issue. I've considered various methods, but the best I could come up with that would keep things speedy was to change the other system so that when a new user is created in the external system, a new row is added to the vanilla user table (custom coded in the external system). The external system also needs to keep track of username and email address changes - and update the vanilla user table at the same time that it updates it's own. It is a clunky method. The real issue is that the vanilla user table has some custom fields that are required in order for vanilla to work. So, even if we had an xml file that pointed to a different applications user table, that other user table would still need to have those custom vanilla fields in order for stuff to work properly. You couldn't just have it point to any old user table. There would always need to be some customization done on the other application's end.

leason

"the best I could come up with that would keep things speedy was to change the other system so that when a new user is created in the external system, a new row is added to the vanilla user table" Don't feel bad about that implementation -- It is exactly what we had to do in order to integrate phpbb into another mssql based webapp. We used a couple triggers to accomplish it, and it is very difficult to debug or update. Anyways, here is an excerpt from the XML file I was referring to. I guess parsing this would be faster/easier in PHP5, but you are right that there would be a slowdown. I wish there was a way to serialize the data and cache it server side for faster access like this Java based server can. Then you would have to force a reload if you changed something, but it would be faster. Unfortunately I don't think we can do that with PHP. Here is a table-map node which shows how you can define the tables and link it to an internal label ("mapped-name") <table-map name="user" mapped-name="cb_users"> <create type="mysql">CREATE TABLE ${user} (uid INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, passwd VARCHAR(32), avatar VARCHAR(64), permission VARCHAR(8) DEFAULT '0000', contact VARCHAR(8) DEFAULT '11', real_name VARCHAR(64), dob VARCHAR(16), country VARCHAR(64), email VARCHAR(64), gender CHAR(1) DEFAULT '0', age INT DEFAULT -1, my_comment VARCHAR(255))</create> <create type="sqlserver">CREATE TABLE ${user} (uid INT IDENTITY PRIMARY KEY, name VARCHAR(64) NOT NULL, passwd VARCHAR(32), avatar VARCHAR(64), permission VARCHAR(8) DEFAULT '0000', contact VARCHAR(8) DEFAULT '11', real_name VARCHAR(64), dob VARCHAR(16), country VARCHAR(64), email VARCHAR(64), gender CHAR(1) DEFAULT '0', age INT DEFAULT -1, my_comment VARCHAR(255))</create> <create type="hsql">CREATE CACHED TABLE ${user} (uid INT IDENTITY PRIMARY KEY, name VARCHAR(64) NOT NULL, passwd VARCHAR(32), avatar VARCHAR(64), permission VARCHAR(8) DEFAULT '0000', contact VARCHAR(8) DEFAULT '11', real_name VARCHAR(64), dob VARCHAR(16), country VARCHAR(64), email VARCHAR(64), gender CHAR(1) DEFAULT '0', age INT DEFAULT -1, my_comment VARCHAR(255))</create> <create type="access">CREATE TABLE ${user} (uid COUNTER PRIMARY KEY, name VARCHAR(64) NOT NULL, passwd VARCHAR(32), avatar VARCHAR(64), permission VARCHAR(8), contact VARCHAR(8), real_name VARCHAR(64), dob VARCHAR(16), country VARCHAR(64), email VARCHAR(64), gender CHAR(1), age INTEGER, my_comment VARCHAR(255))</create> <create type="oracle">CREATE TABLE ${user} (uid INTEGER PRIMARY KEY, name VARCHAR(64), passwd VARCHAR(32), avatar VARCHAR(64), permission VARCHAR(8) DEFAULT '0000', contact VARCHAR(8) DEFAULT '11', real_name VARCHAR(64), dob VARCHAR(16), country VARCHAR(64), email VARCHAR(64), gender CHAR(1) DEFAULT 0, age INTEGER DEFAULT -1, my_comment VARCHAR(255)) CREATE SEQUENCE seq_${user} START WITH 1 INCREMENT BY 1 NOMAXVALUE CREATE TRIGGER trg_${user} BEFORE INSERT ON ${user} FOR EACH ROW BEGIN SELECT seq_${user}.nextval INTO :new.id FROM DUAL; END;</create> <!-- required columns --> <column name="id" mapped-name="uid" /> <column name="name" mapped-name="name" /> <column name="passwd" mapped-name="passwd" /> <column name="avatar" mapped-name="avatar" /> <column name="permission" mapped-name="permission" /> <column name="contact" mapped-name="contact" /> <!-- customizable columns --> <column name="real" mapped-name="real_name" datatype="s" ctype="user" /> <column name="dob" mapped-name="dob" datatype="s" ctype="user" /> <column name="age" mapped-name="age" datatype="i" ctype="user" default="-1" /> <column name="email" mapped-name="email" datatype="s" ctype="user" /> <column name="gender" mapped-name="gender" datatype="s" ctype="user" /> <column name="country" mapped-name="country" datatype="s" ctype="user" /> <column name="comment" mapped-name="my_comment" datatype="s" ctype="user" /> </table-map>

leason

(had to break this post up into two parts...) The file also contains mappings for the actual SQL statements. Here is a good example of how you can use a configuration file like this to do all kinds of neat things. What you see here is the original statement commented out and replaced by ours. We created a view in the database that gets the data out like Chat Blazer wanted it. <statement sid="s14"> <dep-table name="user" default="1" /> <dep-table name="site_user" /> <!-- <sql type="query">SELECT * FROM ${user} t, ${site_user} u WHERE t.${user.name} = '?{name}' AND u.${site_user.userid} = t.${user.id} AND u.${site_user.siteid} = '?{siteid}'</sql> --> <sql type="query">SELECT * FROM cb_users_view_?{siteid} u WHERE u.${user.name} = '?{name}'</sql> </statement> The config file does everything from map table and column names to defining common selects, inserts, updates, and deletes. You have a good point about the custom fields, but there is a pretty simple way to get around that. Just move extra user data to its own table and join to it when you get that information out. It's not that much slower to do it that way than having it all in one table with lots of columns. You'd have a _users_ table and a _user_information_ table. The _users_ table only needs UserID, and maybe username. It's a bit of extra overhead for standard users, but the flexibility it affords would be worth it I would think. Thanks to both of you for the information too. If I can help with any database stuff please let me know. regards, Lee

Jazzman

"the best I could come up with that would keep things speedy was to change the other system so that when a new user is created in the external system, a new row is added to the vanilla user table"

Also been done by Menalto's Gallery 2... which is also a nice app

leason

I had a couple of other thoughts regarding the XML database configuration idea.... First of all, you probably noticed in the example I gave that there is a variable system whereby you can use dynamically assigned values in the SQL itself. That is not hard to do (substr_replace) and affords easier maintenence. Secondly, regarding caching the XML server side.... I had a thought about that. I wonder how hard it would be to actually parse the XML once and create a native PHP object from it. Once you do that one time you could serialize the object and cache it in a server side file. Your DB class would just include the server side file. In the event the file does not exist a method is called that does the XML reading/parsing. To force a reload, you just delete the cached file and the script will recreate it on the next DB call. I don't know how great an idea that is, just seems like a way to have an easy to edit database configuration but not lose too much speed. No matter how you implement it, any time you take straight code and make it configurable you have to sacrifice a certain amount of speed in the execution. But if you do it right then the sacrifice is worth the payoff. I know that if Vanilla were easy to plug into a CMS then it would open up an incredibly huge world of users out there - most of which are very intelligent and will contribute in many different ways. I am going through a similar transition now with my project, http://www.pnflashgames.com. I'm trying to unmarry it from a particular CMS and make it easier to port and plug into anything. It's not easy, that is for sure. -Lee

Mark

Believe me, I've been racking my brain all month trying to come up with a fast solution to this problem. I've considered the "lazy loading" method you talk about where it builds a class and writes the file if it doesn't exist. Any way you look at it, this is a hell of a lot of work. The SqlBuilder class (which I've wanted to remove for a while) is used in almost every single file in Vanilla. Removing it would take a lot of time. So, the 0.9.3 release will not include this functionality. I just don't have enough time to make my end-of-month deadline since I still have a lot of other work to do. After I get 0.9.3 out and it is being tested and debugged by you guys, I will try to think of a way to do something like this.

leason

I can definitely appreciate you wanting to stay on track. If I can help as a sounding board for implementations on this, please let me know. I think the lazy loader option would work out best. The tricky part will be figuring out a good native PHP structure in which to keep the mapping data. I have not looked at the SQLBuilder in great detail yet, but I will try to do that soon. I thought that the Builder would actually be able to pass SQL through the mapper before execution. Thus, Vanilla's deep integration and reliance on the Builder would not be affected. The downside is adding additional complexity to the Builder. Again though, I don't know enough about the Builder to know if that is even possible. Thanks for being open and talking to me about this. I think you have a great system here, I've not seen anything like it out there. -Lee

Mark

Thanks for the kudos and thanks for the advice/input as well. Vanilla has always been a work in progress, so I'm always open to new ideas. The SqlBuilder is one of those "good in theory" ideas that doesn't really hold it's weight in practice. The idea is that you use this object to build all kinds of SQL. Then when you want to port to a different database, you only need to write a new version of the SqlBuilder that formats the statements in that db's native sql syntax. The reality is that databases are far more complex than they used to be, and there will be times when things like sprocs, triggers, etc are required to speed things up. Simply changing the syntax of a select statement doesn't always cut the mustard. So, I really want to pull the whole thing out of the system. I'd prefer to have a central repository of sql statements that can be completely replaced. So you can throw a sproc in instead of a sql - or whatever may be required. I just haven't thought of all of the different things that might be required yet. I'm going to have to build a test application and play with it once I get Vanilla 0.9.3 finished.