Announcement

Collapse
No announcement yet.

Custom SQL to create events

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Custom SQL to create events

    Generally, I have a lot of calendar entries of events and their location. Location is actually a set of GPS coordinates and all events are shown on a Google map.
    While, from year to year, the event dates change, the actual title of each event changes not too much.
    For instance:
    Year 2020: 18th annual event
    Year 2021: 19th annual event
    To maintail all events and their GPS coordinates, I have written a custom php page that shows all events in a table view with an active map on top. With this page, changing event data is fast and easy and much less effort than doing this via vBulletin calendar (I would have to click on each event, etc.)
    CRUD operations in vb4 were very simple. Now, with vb5 everything became more complex.

    Long story short:
    I know you do not support 3rd party plugins but how are events stored?
    Right now, I enter data into tables node, text, event, and closure.
    The respective events show up but have 1 minor flaw:
    On the frontend of my forum in column "Statistics", each newly created event shows: -1 responses
    Creating a reply to this event represents the post correctly and increases the responses counter by 1 to: 0 responses

    I have spent hours on this already but I cannot figue out what's wrong.
    Comparing the data of events created by vBulletin with events created by my scripts does NOT show any difference at all.

    Can you give me a hint please?

  • #2
    I got it! FINALLY!
    It was a timezone issue: The timestamps I create were 3600 seconds (1 hour) ahead of the actual time. Obviously, the time at which events are created matters for counting responses.
    Last edited by enduroforum; Tue 5 Jan '21, 8:35am.

    Comment


    • #3
      You cannot properly create nodes with a single SQL query. You should use the API to insert nodes.

      An new event would have a record in the -
      • node table: Generic node data such as title, description, parentid, topic status.
      • event table: Event specific data such as start and end time and location.
      • text table: Any actual content in the post not specific to the node.

      It would have multiple records in the -
      • closure table: Determines how to find the event including all of its parents (i.e. home -> forum -> forum channel -> sub-forum). There would be a record for each of these along with the depth below the Home node.
      • word table: Each word would have be identified for search.
      • searchtowordsX tables: Each word would be indexed for search.

      Using the API, specifically, the Content Event API would handle all of these for you.
      Translations provided by Google.

      Wayne Luke
      The Rabid Badger - a vBulletin Cloud demonstration site.
      vBulletin 5 API

      Comment


      • #4
        Well, I have to admit: lack of search support is a good argument to use the API.
        I had a look but if one has never worked with the API, it's hard to understand how to apply it.
        Is there a beginner's tutorial somewhere?

        Is it only method add() that would do the job?
        Last edited by enduroforum; Tue 5 Jan '21, 10:28am.

        Comment


        • #5
          The lead developer provided me with a basic script that can add Events via the API provided you have access to the server's command line. It was pulled from the Unit Tests the developer's used and changed to use the CLI Interface. It hasn't been tested but should provide an idea of what is needed. May even work properly without modifications.

          PHP Code:
          <?php

          //this will work in the vB top directory or change this to point to core.
          $root __DIR__ '/core';

          function 
          initCliSession($root$user=0)
          {
              require_once(
          $root '/vb/vb.php');
              
          vB::init();
              
          $request = new vB_Request_Cli();
              
          vB::setRequest($request);
              
          $request->createSessionForUser($user);
          }

          //this needs to be set to the values desired, currently it's garbage.
          $data = array(
              
          'parentid' => $forumChannelId,
              
          'title' => $title,
              
          'rawtext' => $text,
              
          'location' => $location,
              
          'eventstartdate' => $eventStartDate,
              
          'eventenddate' => $eventEndDate,
              
          'allday' => false
          );
          $options = array();
          $result vB_Api::instanceInternal('event')->add($data$options);
          To use this as written put it in your vbulletin root directory (with index.php), add your values to the $data array, then run from the command line with a command like php event.php
          Translations provided by Google.

          Wayne Luke
          The Rabid Badger - a vBulletin Cloud demonstration site.
          vBulletin 5 API

          Comment


          • #6
            I appreciate your help, Wayne; I really do!
            Unfortunately, the script does no work and nothing is being returned (no error message, error code, ...).

            I found another snipped that did not work either but at least I was able to validate that the code in fact getting results from my database by echoing parts of the $auth array:
            Code:
            <?php
            
            // Connection
            $admin = 'username';
            
            $api = init_api ($admin);
            
            // New User Registration
            $username = 'new_user';
            $password = 'pw';
            $email = '[email protected]';
            
            add_user ($username, $password, $email, $api);
            
            function init_api ($admin) {
            
            define("CSRF_PROTECTION", false);
            require_once(__DIR__ . '/includes/vb5/autoloader.php');
            
            \vB5_Autoloader::register(__DIR__);
            \vB5_Frontend_Application::init('config.php');
            \vB::getDbAssertor()->delete("session", array("sessionhash" => vB::getCurrentSession()->get("dbsessionhash")));
            $username = vB_String::htmlSpecialCharsUni($admin);
            $userinfo = vB::getDbAssertor()->getRow("user", array("username" => $username));
            $auth = array_intersect_key($userinfo, array_flip(["userid","secret","lastvisit", "lastactivity"]));
            [B]echo "userid: " . $auth['userid'];[/B]
            $loginInfo = \vB_User:processNewLogin($auth);
            \vB5_Auth::setLoginCookies($loginInfo,"",false);
            $api = Api_InterfaceAbstract::instance();
            
            return $api;
            
            }
            
            function add_user ($username, $password, $email, $api) {
            
            $data = array(
            'userid' => 0,
            'password' => $password,
            'user' => array('username' => $username, 'email' => $email),
            array(),
            array(),
            'userfield' => false,
            array(),
            '',
            array('registration' => true)
            );
            
            $response = $api->callApi('user', 'save', $data, false, true);
            
            }
            
            ?>
            vb has an API that -- I guess -- is meant to be used by customers. Why is this API so complex and why is it not documented?
            I don't understand how customers are supposed to develop on vb without proper documentation. This is so frustrating for customers and must be frustrating for you too as all these questions must be pretty annoying.
            Even if the above code worked, are customers supposed to reverse engineer (or to guess) how updates and deletes work for different content types?
            Do my complains make sense at all?
            Last edited by enduroforum; Wed 6 Jan '21, 4:48am.

            Comment


            • #7
              The first script is an example. I would expect it to be fleshed out by your developer to fill the array and output the return value. You're right in that currently it has no output.

              Add 'print_r($return);' as the last line in the first script... what is the output then?


              The second script is trying to add a new user. it is designed to be run in the vbulletin root directory. $auth only creates a user session. Do you have a user in your database named new_user? Though it was written before password security was added to vBulletin. It would need an 8 digit password.
              Translations provided by Google.

              Wayne Luke
              The Rabid Badger - a vBulletin Cloud demonstration site.
              vBulletin 5 API

              Comment


              • #8
                The script does not reach that line. Everything after the last line is not executed.

                Actually, I do not want to call a CL script. What I need is code that I can put into my php page that executes create, read, update, and delete operations for calendar events.
                That must be basic stuff!
                Is there really no API documentation out there that can help?

                Comment


                • #9
                  I guess for a vb developer, this is a 5min task to help me here:
                  - How do I initialize the API? (I think the above code is either good as it is or at least very close by)
                  - How do I call the respective methods for creating, reading, updating, and deleting events?

                  Comment


                  • #10
                    Is nobody from the VB team willing to help?
                    This is not about modifications, this is simply about using your API...

                    Comment


                    • #11
                      I gave the link to the API documentation already. Even gave an example of using the API from the command line. I have more code on my personal Github. Primarily this though - https://github.com/wayneluke/vbFaker. It is a couple years old and I haven't tested it on a current iteration of vBulletin or using PHP 7.4. it is also very messy code.

                      Using the CLI is going to be the easiest. If you have basic PHP knowledge, you could easily read from a file and create one or more events using it.

                      This would fall under custom code as well. Right now, I don't have a server environment on my new workstation. I've been setting it up when I have time over the last few weeks. Hopefully, I'll have it online today but I can't promise it. Can't really deal with custom code without an environment.

                      From the web, you would need to use the Mobile API. This requires initialization from your code, and then you need to include a signature with every call.
                      Translations provided by Google.

                      Wayne Luke
                      The Rabid Badger - a vBulletin Cloud demonstration site.
                      vBulletin 5 API

                      Comment


                      • #12
                        Though looking at the code from our developer above, there is an issue. it seems the session is never actually initiated. Try this:

                        PHP Code:
                        <?php

                        //this will work in the vB top directory or change this to point to core.
                        $root __DIR__ '/core';

                        // User to use... Change if your Super Admin is not 1.
                        $user=1;

                        require_once(
                        $root '/vb/vb.php');
                        vB::init();
                        $request = new vB_Request_Cli();
                        vB::setRequest($request);
                        $request->createSessionForUser($user);

                        //this needs to be set to the values desired, currently it's garbage.
                        $data = array(
                            
                        'parentid' => $forumChannelId,
                            
                        'title' => $title,
                            
                        'rawtext' => $text,
                            
                        'location' => $location,
                            
                        'eventstartdate' => $eventStartDate,
                            
                        'eventenddate' => $eventEndDate,
                            
                        'allday' => false
                        );
                        $options = array();
                        $result vB_Api::instanceInternal('event')->add($data$options);
                        Translations provided by Google.

                        Wayne Luke
                        The Rabid Badger - a vBulletin Cloud demonstration site.
                        vBulletin 5 API

                        Comment


                        • #13
                          Yes, I looked at the API documentation but frankly speaking, this does not help if one does not already know how to operate the API.
                          Your code above results in the following error:
                          Code:
                          Can't find class vB_Api_Event
                          In general, even if it worked, I am still looking for code that can run from inside a php page.
                          To make my ambitions a bit clearer, here is what my event maintenance page looked like in vb4:
                          Click image for larger version

Name:	unbenanntiqjrijojzr.png
Views:	47
Size:	566.7 KB
ID:	4452357
                          Again, all I am looking for is the view lines of code to be called in order to create, read, update and delete an event.
                          I don't understand why this is so complicated and requires weeks of posting on the support forum.
                          Since you offer an API you should also support it.
                          Attached Files

                          Comment


                          • #14
                            You will have to build the page around the API. No one on this forum is going to create this for you unless you're willing to pay money to have it done.

                            There are already methods to add and delete nodes from the system.
                            Translations provided by Google.

                            Wayne Luke
                            The Rabid Badger - a vBulletin Cloud demonstration site.
                            vBulletin 5 API

                            Comment


                            • #15
                              I am not asking anybody to build it for me. I am asking for help on getting started.
                              I suppose you know exactly what I meand and what I need but for some reason you don't want to provide that information.
                              I am very open to receive help to help myself but with code samples you provide not working, what am I supposed to do??
                              As I said, I am getting the following error:
                              Code:
                              Can't find class vB_Api_Event
                              You are mentioning the API documentation:
                              I may be blind but where would I find the information on what parameters instanceInternal() is accepting, which methods next to add() are available, which parameters each method is expecting, and what the data array needs to contain?

                              Comment

                              Related Topics

                              Collapse

                              • In Omnibus
                                Recurring Calendar Events
                                by In Omnibus
                                I've had a request for recurring calendar events in the tracker for quite some time. Oddly, it has only one vote. I can't fathom I am the only person who uses recurring calendar events for things that...
                                Mon 21 Sep '20, 5:47am
                              • NIKU-DR
                                calendar
                                by NIKU-DR
                                How my users can have right to add they event to calendar ?
                                • Allow users to create new events in configurable 'calendars'. Custom permissions allow you greater control over who can see a event.
                                  • Calendars
                                ...
                                Wed 8 Jul '20, 11:52pm
                              • spiderweb
                                calendar
                                by spiderweb
                                What purpose does the calendar provide besides birthdays? I can't find options, events or anything to actually use it. What am I missing?
                                Sun 7 Jun '20, 9:53am
                              Working...
                              X