Yes. Views can handle update, delete and inserts directly against them – they pass the change statement to the underlying table.
There are rules to using these (https://dev.mysql.com/doc/refman/5.7/en/view-updatability.html) but I think it’ll work for your situation. Views are always “current”. They are not caches, but rather statement combiners: If you have table.column And you make a view of SELECT column FROM table WHERE column > 100 And then you select against that view, like this: SELECT column FROM view WHERE column = 210 What the server will execute is: SELECT column FROM table WHERE column > 100 AND column = 210 It combines the effects of both statements, queries the live table and returns the results. Views get complicated though. You can chain and nest them. You can force them to maintain realized copies of data. And the list goes on. Robert From: Fogelson, Steve [mailto:foge...@askics.net] Sent: Wednesday, March 15, 2017 8:27 AM To: TeraScript-Talk@terascript.com Subject: RE: TeraScript-Talk: Off Topic - MySQL Hi Robert, Thanks for the response. The additional site hasn’t been created yet and will not start with inventory, so I should be able to just create the “common” database with the one table. Not sure exactly what you mean fully 2-way? I haven’t used views a lot. With the views, would I be able to WRITE, INSERT and UPDATE to the common database? Would the views always be current data after begin modified with WRITE, INSERT and UPDATEs? Thanks Steve From: Robert Shubert [mailto:rshub...@tronics.com] Sent: Wednesday, March 15, 2017 10:15 AM To: TeraScript-Talk@terascript.com <mailto:TeraScript-Talk@terascript.com> Subject: RE: TeraScript-Talk: Off Topic - MySQL Schema name (your db_name) support in TS is a little sketchy. We’re trying to improve it, but it’s something that needed to be done correctly upfront and wasn’t. I might suggest: Starting with db1.inventory and db1.inventory create database “common” and move all inventory into a common table: Insert into common.inventory select * db1.inventory union all select * db2.inventory Then drop db1./db2.inventory tables and add views of the same name that reference the common.inventory table. Assuming that your views are fully 2-way you shouldn’t have to change any code. Robert From: Fogelson, Steve [mailto:foge...@askics.net] Sent: Tuesday, March 14, 2017 2:52 PM To: TeraScript-Talk@terascript.com <mailto:TeraScript-Talk@terascript.com> Subject: RE: TeraScript-Talk: Off Topic - MySQL Hi Ian, Well that’s a DUH moment for me. I forgot you can specify db_name.view_name. I assume you can specify db_name.table_name as well for READ, WRITE, INSERT, SELECT AND UPDATE assuming you have given Assigned Privileges for that USER to access that database. Also assuming that in TeraScript, I would specify the alternate database can under the Name in the Deployment Data Source within Data Source Selection for the Data Source action. I am also assuming that this is the “Default Database” and that if I was going to perform a JOIN that I would need to use a “Custom SQL Query” action in order to be able to designate the alternate db_name.table_name within the SQL of the custom query. Thanks for leading me in the right direction and commenting on this additional info. Steve -----Original Message----- From: Ian Evans [mailto:ia...@ubookstore.com] Sent: Tuesday, March 14, 2017 1:50 PM To: TeraScript-Talk@terascript.com <mailto:TeraScript-Talk@terascript.com> Subject: Re: TeraScript-Talk: Off Topic - MySQL Steve, Perhaps create a view? For example: CREATE VIEW database1.somename AS SELECT * FROM database2.sometable; Then you can get that info using: SELECT * FROM database1.somename; -- Ian Evans Web Developer - eCommerce Team University Book Store, Inc. 4326 University Way NE Seattle, Washington 98105 206.634.3400 ext.621 <mailto:ia...@ubookstore.com> ia...@ubookstore.com On 3/14/2017 11:17 AM, Fogelson, Steve wrote: > Over the years, I have written a pretty extensive shopping cart/back > end processing application using TeraScript. The application handles > multiple web sites with a separate MySQL database for each web site. > All databases are running under one MySQL service with the One File > per Table option enabled. This option makes InnoDB to store each > created table into its own .ibd file. > > > > Each web site/database has an Inventory table. I would like to share > the Inventory table from one site with another site. I use many JOINs > and LEFT OUTER JOINs when reading the inventory table. > > > > I could look through all my tafs and tcfs for all INSERTS, UPDATES and > DELETES, add code to update each other’s table and keep the separate > Inventory tables identical, but I am looking for a better solution. > > > > I could pull the Inventory table out of the database and make the > table it’s own database that both sites access, but I am not sure if > JOINs and LEFT OUTER JOINs would work. > > > > Is there a way in MySQL to have one database look to another database > for a table instead of it’s own? > > > > Or any other strategies you might recommend? > > > > Thanks in advance > > > > Steve Fogelson > > Internet Commerce Solutions > > > > > > > > > ---------------------------------------------------------------------- > -- To unsubscribe from this list, please send an email to > <mailto:lists...@terascript.com> lists...@terascript.com < > <mailto:lists...@terascript.com> mailto:lists...@terascript.com> with > "unsubscribe terascript-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to <mailto:lists...@terascript.com> lists...@terascript.com with "unsubscribe terascript-talk" in the body. _____ To unsubscribe from this list, please send an email to lists...@terascript.com <mailto:lists...@terascript.com> with "unsubscribe terascript-talk" in the body. _____ To unsubscribe from this list, please send an email to lists...@terascript.com <mailto:lists...@terascript.com> with "unsubscribe terascript-talk" in the body. _____ To unsubscribe from this list, please send an email to lists...@terascript.com <mailto:lists...@terascript.com> with "unsubscribe terascript-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to lists...@terascript.com with "unsubscribe terascript-talk" in the body.
