icons – How should the filter feature manifest itself in google material design tables

This icon is from the category of symbols used in / for buttons. It seems intuitive to me that if I click on the “filter list” icon in your example, the filters are displayed, because, without filters, the action can’t be completed.

How the filters will look, depends on the number of options, page UI and I think Material Design offer some guidance, but it is not so strict that we need to have clear specs for every icon.

In this case, “Material Design Data Table”, the filter is a search field. I didn’t expect this filter, but I can’t say this is not a good UX.

enter image description here

enter image description here

mysql – SQL: How to get all tables’ columns & keys + the primary key?

I’m trying to get all tables in a database, with their column definitions and keys.

Given the following tables

create database test;
use test;

CREATE TABLE authors (
  id int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL UNIQUE,
  PRIMARY KEY(id)
);
CREATE TABLE books (
  id int NOT NULL AUTO_INCREMENT,
  author_id int NOT NULL,
  title varchar(255) NOT NULL UNIQUE,
  summary varchar(255),
  FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE,
  PRIMARY KEY(id)
);
CREATE TABLE authors_books (
  author_id int NOT NULL,
  book_id int NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE,
  FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
  PRIMARY KEY(author_id, book_id)
);

The end result should look something like:

authors
id,int,no,auto_increment
name,varchar(255),no,UNIQUE
PK(id)

books
id,int
author_id,int,FK->authors.id
title,varchar(255),UNIQUE
summary,varchar(255),NULL
PK(id)

authors_books
author_id,int,FK->authors.id
book_id,int,FK->books.id
PK(author_id,book_id)

Now, the following query gets me everything except the keys. It’s a mess in mysql output, but running it with the command helps it look digestible.

mysql -u root -p -NBre "SELECT CONCAT_WS('n', table_name, GROUP_CONCAT(
    CONCAT_WS(',', column_name, column_type)
    ORDER BY ordinal_position
    SEPARATOR 'n'
  ),
  'n'
)
FROM information_schema.columns
WHERE table_schema = 'test'
GROUP BY table_name"

I’ve tried many combinations to get the keys, but could only get as close as the following.

SELECT a.table_name, a.column_name, GROUP_CONCAT(CONCAT_WS(',', a.column_type, b.constraint_name, b.ordinal_position ) SEPARATOR '|')
FROM columns a
LEFT JOIN key_column_usage b
  ON (a.table_name = b.table_name AND a.column_name = b.column_name)
WHERE b.table_schema = 'test'
GROUP BY a.table_name, a.column_name
ORDER BY a.table_name;

Which gives:

| TABLE_NAME    | COLUMN_NAME | GROUP_CONCAT(CONCAT_WS(',', a.column_type, b.constraint_name, b.ordinal_position) SEPARATOR '|') |
|---------------|-------------|------------------------------------------|
| authors       | id          | int,PRIMARY,1                            |
| authors       | name        | varchar(255),name,1                      |
| authors_books | author_id   | int,PRIMARY,1|int,authors_books_ibfk_1,1 |
| authors_books | book_id     | int,PRIMARY,2|int,authors_books_ibfk_2,1 |
| books         | author_id   | int,books_ibfk_1,1                       |
| books         | id          | int,PRIMARY,1                            |
| books         | title       | varchar(255),title,1                     |

Notice, books.summary is missing, so I’m thinking all fields without a constraint will be missing from the results.

Another thing is when a primary key is a composite key, such as in the bridge table authors_books, they should not be part of the GROUP_CONCAT in the 3rd column but instead be its own record at the end of the table_name group.

I might be on the wrong track but.. How can I also get the columns that don’t have key constraints, plus the table’s primary key as its own row?

database design – How to model local exceptions to distributed tables

I am looking for possible ways to model a distributed table(s) and provide local exceptions to that table. Consider a situation where head office publishes a list of products/prices that is distributed to branches and they load this price list into their local database. Now sometimes these branches want to change the head office price to a local price. Clearly we can simply update the price in the local database, but then it gets lost when the next head office update is applied. We also only want to block that single column(s), not changes to other fields in the row.

EG. Head office send
| Name | Price |
|——|——-|
| ABC | $5 |
| DEF | $8 |

In one database (out of say 100) we want ABC to have a price of $3. And this $3 should continue to apply even as future updates from head office are received. Head office are not aware of this local exeception.

In the past we’ve done this in two ways. The first was to maintain a second LocalProducts table with the changes to be applied – then when the head office version was applied, any local changes would then be read from LocalProducts and replace the distributed value.
This works OK, but suffers as the table sizes go up.

The second method we tried was to duplicate the column and use coalesce
eg rather than select price where name='abc' we would use select coalesce(localprice,price) where name='abc' This works ok, but had the overhead that we had to change every SQL and remember to do it on future development too.

Are there any other better methods? I am looking for a database agnostic solution, the target databases are ODBC (90% sql/server), SQL-Lite or MS-Access. Access can be ignored if it helps, it is being actively phased out. Engine specific solutions are ok, we can detect the engine and use a different technique, but would prefer not to have a different technique for each engine.

Other notes

  • The data is distributed by the application, not built in replication. This means we can have code/logic during the loading phase
  • Multiple tables are require this feature, not just one as the example above
  • Some of the tables have a reasonable number of columns that could be involved, but typically not.
  • Most of the tables aren’t large, perhaps 50M rows in the biggest, and only 7 rows in the smallest case.

(please feel free to retag this question – not exactly sure which tags are relevant. And yes I have spent many hours over the years investigating this)

I will do excel spreadsheet formula, vba, macros, chart and pivot tables for your assist for $10

I will do excel spreadsheet formula, vba, macros, chart and pivot tables for your assist

Hello Everyone,

We are specialize in Excel solutions. We have 5+ Year’s experience. Our services are fast as we typically deliver in 48 hours (even for 3-day packages).

We also take pride in providing after sales assistance. 100% Satisfaction Guaranteed and We will always be there for your queries.

We are available almost all hours of the day and would be happy to assist you with any Excel issue(s) you may have.
Services Offered:

  • Customized Automated excel spreadsheet with Formulas, Tables or Graphs.
  • Customized Automated Google Sheets with Formulas, Tables or Graphs.
  • Basic updates to existing worksheets (fixing formulas, re-organizing data, etc.)
  • Amortization and Other Calculators
  • Sales, Expenses and other Trackers
  • Excel VBA
  • Microsoft Excel macro
  • Payroll Software
  • Invoices/Pay Slips
  • Buttons, Drop-down lists, menu, forms.
  • Creating User Forms
  • Charts
  • Vlookup / Hlookup
  • Countif / Countifs
  • Sumif / Sumifs
  • Removing Duplicates
  • PDF to Ms-Excel,
  • Excel to PDF
  • Data Validation
  • Data Analysis
  • Pivot Tables
  • Private Consultations
  • 100% Satisfaction assurance
  • 100% Quality assurance

Thank you for reading, We are looking forward to working with you!

Note: Kindly contact us before placing an order.

Thanks a lot!!!

.(tagsToTranslate)microsoft(t)excel(t)vba(t)formulas(t)data(t)entry

plotting – How can I make 3 separate tables of parameters instead of only one table like this?

In the code below, how can I make 3 separate parameter tables for tup1, tup2, tup3 and plot each function fi of corresponding tupi?
Like:

  • f1 with tup1
  • f2 with tup2
  • f3 with tup3

Currently there is one one tup table.

f1(a_, b_, c_) := (a x^2 + b x + c)/3;
f2(a_, b_, c_) := (a x^2 + b x + c)^2;
f3(a_, b_, c_) := 3 (a x^2 + b x + c);
tup1 := RandomInteger(5, {6, 3});
tup2 := RandomInteger(5, {8, 3});
tup3 := RandomInteger(5, {10, 3});
tup = Join(tup1, tup2, tup3);
functions1 = f1 @@@ tup1;
functions2 = f2 @@@ tup2;
functions3 = f3 @@@ tup3;
functions = Join(functions1, functions2, functions3);
colors = ColorData(97) /@ Range(Length@functions);
functionswithtooltips = MapThread(Tooltip, {functions, tup});
plot1 = Plot(Evaluate(functionswithtooltips), {x, 0, 5}, 
   GridLines -> Automatic, ImageSize -> Large, 
   PlotStyle -> Thread(Directive(colors, Opacity(.3), Thin)));
ttlist = Cases(plot1, _Tooltip, All);
t = Deploy@
  DynamicModule({n = {1}, hidden = ConstantArray(1, Length@ttlist)}, 
   hidden((n)) = 2;
   Dynamic(
    Legended(
     Graphics(
      Table(With({i = i, ind = hidden((i))}, 
        Tooltip({ttlist((i, 1, 1)), 
          
          DynamicModule({boxes = {ttlist((i, 1, 2)), 
              Style(ttlist((i, 1, 2)), Opacity(1), Thickness(Large))},
             index = ind, length = 2}, 
           EventHandler(
            Dynamic(
             boxes((index))), {"MouseClicked" :> (index = 
                index /. {1 -> 2, 2 -> 1};
               hidden((i)) = index; 
               n = Flatten(Position(hidden, 2));), 
             Method -> "Preemptive", PassEventsDown -> True, 
             PassEventsUp -> True}), DynamicModuleValues :> {})}, 
         ttlist((i, 2)))), {i, 1, Length@ttlist}), ImageSize -> 700, 
      AspectRatio -> 1/GoldenRatio, PlotRange -> {{0, 5}, {0, 50}}, 
      Frame -> True, GridLines -> Automatic), 
     TogglerBar(
      Dynamic(n, (n = #; hidden((All)) = 1; hidden((n)) = 2) &), 
      MapIndexed(#2((1)) -> 
         Style(#, 16, Bold, Opacity(1), colors((#2((1))))) &, tup), 
      Appearance -> "Vertical" -> {Automatic, 4}))))

Restricting input from two other tables

I’m trying to create the table CanTeach but I am getting an error ERROR 1005 (HY000): Can't create table 'mydb'.'CanTeach' (errno: 150 "Foreign key constraint is incorrectly formed"). Ultimately, I’m trying to create a table of "classes that teacher’s can teach" verifying they work for the department that offers the class.

CREATE TABLE Department (
    Name varchar(255) NOT NULL PRIMARY KEY
);
CREATE TABLE Person (
    SSN CHAR(9) NOT NULL PRIMARY KEY,
    LastName varchar(255),
    FirstName varchar(255)
);
CREATE TABLE Professor (
    SSN CHAR(9),
    PRIMARY KEY(SSN),
    FOREIGN KEY (SSN) REFERENCES Person(SSN)
);
CREATE TABLE WorksFOR (
    SSN CHAR(9),
    Salary float NOT NULL,
    DepName varchar(255),
    CONSTRAINT WorksFor_Salary CHECK (Salary>=0),
    CONSTRAINT WorksFor_pk PRIMARY KEY(SSN,DepName),
    CONSTRAINT WorksFor_ssn_fk FOREIGN KEY(SSN) REFERENCES Professor(SSN),
    CONSTRAINT WorksFor_dep_fk FOREIGN KEY(DepName) REFERENCES Department(Name)
);
CREATE TABLE Course (
    CourseDepartment varchar(255) NOT NULL,
    CourseNumber varchar(8) NOT NULL,
    CONSTRAINT Course_pk PRIMARY KEY(CourseDepartment,CourseNumber),
    FOREIGN KEY(CourseDepartment) REFERENCES Department(Name)
);
CREATE TABLE CanTeach (
    SSN CHAR(9) NOT NULL,
    Department varchar(255) NOT NULL,
    CourseNum varchar(8) NOT NULL,
    CONSTRAINT CanTeach_pk PRIMARY KEY(SSN,Department,CourseNum),
    FOREIGN KEY(SSN,Department) REFERENCES WorksFor(SSN,DepName),
    FOREIGN KEY(Department,CourseNum) REFERENCES Course(CourseDepartment,CourseNumber)
);

linux – WordPress query on MyISAM tables in MariaDB on ZFS takes too long to execute

I use a pretty common setup, some wordpress sites running on Debian 10 (Buster) with mariadb 10.3.27 on ZFS, on a decent server with SSDs and lots of RAM, pretty smooth operation with low CPU utilization. There is this query wordpress makes,

SELECT DISTINCT meta_key FROM wp_postmeta INNER JOIN  wp_posts ON post_id = ID WHERE post_type <> 'product' AND post_type <> 'product_variation';

For some reason I cannot possibly imagine, on database A (InnoDB tables) it runs in about 3 seconds, and on database B (MyISAM tables) it takes over 2 minutes. wp_postmeta has 198938 records and wp_posts 25189 in database B, where on database A they both have many more records. I’ve tried dumping, deleting the database and recreating it to no avail. If I move the database to another similar server with EXT4, the query on MyISAM tables finishes in about 4 seconds. If I alter the tables to InnoDB, query execution finishes in about 4 seconds. Also, when running the query on MyISAM tables on ZFS I noticed the disk reads spike to about 50MB/s and up to 80MB/s while the query is running. 2 minutes of reads at 50MB/s is 6GB of data, in a database with a total size less than 500MB.

This difference in execution time between MyISAM and InnoDB was noticed on Debian systems with ZFS 0.6 and 0.7. The exact same databases on a FreeBSD/ZFS server display no such difference, with queries executed within a a few seconds for both MyISAM and InnoDB tables.

Does anybody know what could make such a huge difference between MyISAM and InnoDB on ZFS on Linux, and why ZFS on FreeBSD does not show this behavior?

The tables as shown by describe are:

MariaDB (B)> describe wp_posts;
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field                 | Type                | Null | Key | Default             | Extra          |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID                    | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| post_author           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| post_date             | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_date_gmt         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content          | longtext            | NO   |     | NULL                |                |
| post_title            | text                | NO   |     | NULL                |                |
| post_excerpt          | text                | NO   |     | NULL                |                |
| post_status           | varchar(20)         | NO   |     | publish             |                |
| comment_status        | varchar(20)         | NO   |     | open                |                |
| ping_status           | varchar(20)         | NO   |     | open                |                |
| post_password         | varchar(255)        | NO   |     |                     |                |
| post_name             | varchar(200)        | NO   | MUL |                     |                |
| to_ping               | text                | NO   |     | NULL                |                |
| pinged                | text                | NO   |     | NULL                |                |
| post_modified         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_modified_gmt     | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content_filtered | longtext            | NO   |     | NULL                |                |
| post_parent           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| guid                  | varchar(255)        | NO   |     |                     |                |
| menu_order            | int(11)             | NO   |     | 0                   |                |
| post_type             | varchar(20)         | NO   | MUL | post                |                |
| post_mime_type        | varchar(100)        | NO   |     |                     |                |
| comment_count         | bigint(20)          | NO   |     | 0                   |                |
+-----------------------+---------------------+------+-----+---------------------+----------------+
23 rows in set (0.001 sec)

MariaDB (B)> describe wp_postmeta;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| meta_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

MariaDB (B)> show indexes from wp_posts;
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_posts |          0 | PRIMARY          |            1 | ID          | A         |       25819 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_name        |            1 | post_name   | A         |       25819 |      191 | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date |            1 | post_type   | A         |          28 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date |            2 | post_status | A         |          43 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date |            3 | post_date   | A         |       12909 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date |            4 | ID          | A         |       25819 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_parent      |            1 | post_parent | A         |         213 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_author      |            1 | post_author | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.001 sec)

MariaDB (B)> show indexes from wp_postmeta;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_postmeta |          0 | PRIMARY  |            1 | meta_id     | A         |      198938 |     NULL | NULL   |      | BTREE      |         |               |
| wp_postmeta |          1 | post_id  |            1 | post_id     | A         |       24867 |     NULL | NULL   |      | BTREE      |         |               |
| wp_postmeta |          1 | meta_key |            1 | meta_key    | A         |         393 |      191 | NULL   | YES  | BTREE      |         |               |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.000 sec)

mariadb – Cannot remove #sql-ib type tables

Having some space issues with my server and noticed that there are two massive #sql-ibxxx files with no .frm equivalents.

what is the best way to remove these?

My databases are setup so that we have

Database A
Database B – the two files are located here and no other tables exist, its essentially an “empty” database
Database C

I have tried to remove the files with drop table issuing the command DROP TABLE #mysql50##sql-ib53761;

but i get Unknown table ‘#mysql50##sql-ib53761’

I have read online that i need to drop the database and bring it back up.
Does this mean i need to backup database A,B,C or can i just drop B and leave A and C unchanged?

When i did try to drop this database in the past, it would crash the server

Any help would be amazing!
I am using 10.3.18-MariaDB and have innodb-file-per-table =1

Thanks

sql server – Best way to copy tables without a primary key or unique constraint?

sql server – Best way to copy tables without a primary key or unique constraint? – Database Administrators Stack Exchange

DreamProxies - Cheapest USA Elite Private Proxies 100 Private Proxies 200 Private Proxies 400 Private Proxies 1000 Private Proxies 2000 Private Proxies ExtraProxies.com - Buy Cheap Private Proxies Buy 50 Private Proxies Buy 100 Private Proxies Buy 200 Private Proxies Buy 500 Private Proxies Buy 1000 Private Proxies Buy 2000 Private Proxies ProxiesLive Proxies-free.com New Proxy Lists Every Day Proxies123