Jump to content

Northwind


Recommended Posts

I think this kind of "schema-full" approach designed for TDataset/TField is useful for some kind of applications in SMS. It is simple, transparent and automatic but for simple applications. I believe we are introducting too much complexity and could expose the database structure, with TTable/TField/TQuery issue might be giving too much details to the project where only the necessary functionality should be added. I would prefer a "schema-less" approach for the TDataset.

This is a typical master-detail pattern. Let's consider we are storing Order and OrderDetails. Using the TDataset schema-less, only the necessary functionality is added.

AAEAAQAAAAAAAAhFAAAAJDQzMzM5ZjNlLTMxZWUt

 

orders.json

There's an interesting project JSON2DelphiRecord on git, and JSON2DelphiClass (a pascal unit is generated based on the JSON object ). I think would be possible to generate schema-less TDataset on the fly.

... just to reference, some time ago MarkusJa - I think he has abandoned smart cause lack of support from the official team, anyway he has shared me the SMS Data Provider prototype.

Link to post
Share on other sites
  • Moderators

Northwind links :

Demo : http://www.lynkit.com.au/northwind/     (customer-order-orderline, last level not quite finished)

Source : http://www.lynkit.com.au/northwind/index.sproj

MySQL DDL : https://github.com/dalers/mywind

Snippet


procedure TForm1.InitializeObject;
begin
  inherited;
  {$I 'Form1:impl'}

//...

  GetCustomers('select * from customers');

end;

procedure TForm1.GetCustomers(FSelect: string);
var
  sql_statement : String;
  encodedstr1 : String;
  var smscursor : Variant;
  var DBRows : Integer;
begin
//Request customers from db
  var FHttp := JXMLHttpRequest.Create;
  FHttp.open("POST",'http://www.lynkit.com.au/northwindxxx.php');
  FHttp.setRequestHeader("Content-type","application/x-www-form-urlencoded");
  encodedstr1 := window.encodeURIComponent(FSelect);
  sql_statement := 'sql_statement=' + encodedstr1;
  FHttp.send(sql_statement);

//fire up spinner
  var Spinner1 := JW3Spinner.Create(self);
  Spinner1.SetBounds(150, 0, 40, 40);

//set up customer panel with data grid
  var CustomerPanel := JW3Panel.Create(self);
  CustomerPanel.SetBounds(20, 95, 600, 220);
  CustomerPanel.SetProperty('border','1px solid silver');

    var CustomerGrid := JW3Grid.Create(CustomerPanel);
    CustomerGrid.SetBounds(5, 10, 594, 200);

    CustomerGrid.AddColumn('id',40);
    CustomerGrid.AddColumn('first name',100);
    CustomerGrid.AddColumn('last name',100);
    CustomerGrid.AddColumn('company',100);
    CustomerGrid.AddColumn('city',100);
    CustomerGrid.AddColumn('phone',100);

//set up search
  var InputSearch := JW3Input.Create(self);
  InputSearch.SetBounds(20, 330, 120, 40);
  InputSearch.SetAttribute('type','search');
  InputSearch.SetAttribute('placeholder','search...');
  InputSearch.handle.onchange := procedure
  begin
    CustomerPanel.Clear;
    GetCustomers('select * from customers where ' +
        'first_name like "%' + InputSearch.handle.value + '%" or ' +
         'last_name like "%' + InputSearch.handle.value + '%" or ' +
           'company like "%' + InputSearch.handle.value + '%" or ' +
              'city like "%' + InputSearch.handle.value + '%";');
  end;

//when data becomes available
  JGlobalEventHandlers(FHttp).onLoad := lambda(e:JEvent)
    Spinner1.SetProperty('display','none');   //kill spinner
    smscursor := JSON.parse(FHttp.ResponseText);
    DBRows := smscursor.smsrows.length;

      //inline format cell function
      function createCell(content: string) : JW3Panel;
      begin
        var Cell := JW3Panel.Create(CustomerGrid);
        Cell.SetinnerHTML(content);
        Cell.Height := 16;
        Cell.SetProperty('font-size', '0.85em');

        Cell.OnClick := procedure(sender: TObject)
        begin
          GetOrders('select orders.*, customers.company from orders,customers ' +
              'where orders.customer_id = customers.id and ' +
              'orders.customer_id = ' + smscursor.smsrows[row].id + ';');
        end;

        Result := Cell;
      end;

    //populate grid (row, column, cell)
    for var i := 0 to DBRows -1 do begin
      CustomerGrid.AddCell(i+1, 1, createCell(smscursor.smsrows[i].id));
      CustomerGrid.AddCell(i+1, 2, createCell(smscursor.smsrows[i].first_name));
      CustomerGrid.AddCell(i+1, 3, createCell(smscursor.smsrows[i].last_name));
      CustomerGrid.AddCell(i+1, 4, createCell(smscursor.smsrows[i].company));
      CustomerGrid.AddCell(i+1, 5, createCell(smscursor.smsrows[i].city));
      CustomerGrid.AddCell(i+1, 6, createCell(smscursor.smsrows[i].business_phone));
    end;
  end;

end;

procedure TForm1.GetOrders(FSelect: string);
begin
  //...
end;

 

Database access to the remote MySQL database here is through XMLHTTPRequests.

While this is simple to use, fast and reliable, I don't think it is robust enough to scale to heavy traffic, so in that case a better option is to use node in the background. (See also :) :

 

Adding to Laksekjønn comments : I find his and Markus_ja's thoughts interesting. Would be good to have a database connectivity component set added to the RTL. I'm sure this is on the todo-list of the development team, definitely is on my wish-list. 

By the way, kudos for everyone on the team, loving the updates and the upswing it creates.

On another note : the snippet code above is based on the native/shoestring framework that I'm exploring lately. However, this code is almost equal to normal, just with a different underlying set of components and can be easily converted to current alpha if so desired.

For interested users :

Kitchensink :  http://www.lynkfs.com/Experiments/shoestring/www/

Sourcecode : http://www.lynkfs.com/Experiments/shoestring/index.sproj

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...