Jump to content


  • Content Count

  • Joined

  • Last visited

  • Days Won


Everything posted by gizmola

  1. There are any number of things it could be. Your post is the equivalent of going on a Car mechanic's site, and making a post that says: "There's a weird sound coming from the right front corner of my 79 Subaru and the car won't stop properly now. Oh yeah I drove by the DMV and they said that only people who exclusively work on 79 Subaru's could fix it, assuming it could be fixed. Tell me how to fix this, and btw I'm not in any way mechanical!" What do you actually expect us to do here, other than make the educated guesses that have been made? We have some standards and practices here, and pretty much anywhere else where people help others with code problems or questions, and that is that we need to see some relevant code.
  2. Relational databases were designed for normalization and joining of tables. Don't make the mistake that far too many software developers have of jumping to optimization conclusions that lead them down a path prior to doing any actual tuning or profiling. You don't know what your bottlenecks will be until you have a working application. Don't assume that joining tables together will perform poorly.
  3. I would certainly have the likes/dislikes in a table as Barand suggested, along with an FK to the user who made the like/dislike rating. This allows you to do a number of important things, like for example, preventing a person from liking or disliking the same person repeatedly. There is a cost to summarizing data like that repeatedly in a sub-query so frequently people will implement a de-normalized summary, or better yet, put the summary in a caching system like Redis. The summarized(denormalized) totals could be in the person table if you really wanted them to be, but you should still have the detail person_rating table to store the individual like/dislike rows.
  4. Agree with chhorn: put in the person table, unless you want the historic record of Avatars. Hopefully you are using the InnoDB engine for all your MySQL tables. If not, you can run ALTER TABLE on them to change the engine from the default MyISAM. With InnoDB, when you SELECT a row from an InnoDB table by the primary key, the read activity includes all the data anyways, since InnoDB has clustered indexes. This type of micro-optimization isn't really that useful to consider when thinking about DB design, but you might as well sleep well at night knowing that there's no significant performance cost to putting it into the person table.
  5. There are many different ways to handle this. Personally I would probably use Redis hashes for this, or a mongoDb collection, but if you don't have those options, then one of the simplest and cleanest options is to use SQLLite3. There is full support for Sqllite via a node library, and it has essentially no footprint other than the underlying c libraries. Tables can be persisted to the filesystem or used in memory. I see no reason not to persist them to a file. You would need a simple table keyed by robot name with the following fields: robot (text) user (text) from (integer) to (integer) The from and to columns would be timestamp values. Personally, it would be nicer to use websockets rather than polling, but either way, the client code will query the db for a particular robot where the current timestamp > from and < to. If any rows come back then, you check if the user is the current user. If not, then display to the user that "user x currently has this robot". The entire table could also be queried to show which robots are currently assigned to which users. If a user wants to assign themselves an available robot, then then simply insert a new row with the current time as from, and some reasonable future time that syncs with your client "checkin" ajax code. 5 or 10 minutes might be a good starting point. The "checkin" code would query current user's robot row, and update the from, extending it by 5 or 10 minutes at each checkin. The advantage of this is that any allocations will expire if the user doesn't release the robot properly or their page dies. Sqllite will handle concurrency and locking for you via transaction support (you wrap your select and insert/update activity in a transaction). To release an allocation, you'll just need a routine that deletes the row for that robot. You might also want another table for the robots themselves, and even use a standard relational 1-M model with an id for key, but that is up to you. Somehow the code needs to know the fulll list of available robots, and you might as well use sqllite for both problems. Just to keep the database pruned and small, I'd suggest writing a small program that runs from cron, and cleans up any expired robot allocation rows ( current timestamp > to). You could run this in whatever iteration you would like. There might be some value in having a historic record of robot allocations, so cleaning out old rows removes that, whenever you actually run the pruning.
  6. What's missing from your list of tables is the table that describes the relationship between a member/user and the plan version. Barand is a SQL expert bar none. Do what he said with the only caveat being, that the table relating a member to a plan version would be required to determine if a user should be able to access an article. That 100% should be done with a single query that involves JOINS. As we discussed previously, with an adequate InnoDB buffer pool, repeated queries will come from the buffer pool cache, so there would be essentially no reading of data by the MySQL server.
  7. All the DB's I listed use the same basic Indexing technology. Indexing = query performance. In general, no traditional business would do what you plan to do, even if that is very common for small businesses running internet sites. Monolithic servers just aren't done for any of the commercial DB's. The RDBMS world assumes that the RDBMS will run on a dedicated server, with attached storage or perhaps a NAS or SAN. The majority of available server RAM is allocated to the DB, and nothing else runs on it. You do not want to experience a scenario where the database engine itself might be swapped to disk so that some other set of programs can run. In your monolithic (one server LAMP?) setup, you will not be insulated whatsoever from that happening without careful tuning and configuration. Most probably it will happen at some point. The other rule of thumb for system tuning is that initially, your scalability issues will bottleneck at application server RAM. With PHP that is either just Apache/Mod_php, or php-fpm. In other words, the client connections/php threads will bottleneck before the DB queries will. This is typically why you want a cluster of application/web servers and a dedicated DB server. If you find you need to add application server#2 does your application even work? How will load be balanced? This effects a number of application level configuration and code decisions you might make. For example, to start with, how will you scale sessions? It is much easier to have a basic architecture that has a degree of scalability to begin with, than it is to try and figure out how to fix things when your business no longer works, or is inaccessible due to load that's crashing it, and your only option is to try and move things to a larger server with more resources. Often business system design has historically depended on moving some portion of business rules to the database as trigger/sproc code. Oracle and Sybase/MS Sql Server (Sql Server is a licensed fork of Sybase) built there reputations on the capability and performance of sprocs & triggers. Since you haven't mentioned those this seems like a non-factor. One of the other things important to you, since your codebase is in php, is client library support in PHP. MySQL connections are very lightweight and performant when compared to something like Oracle. This works very well with typical PHP models where connection pools are not important. The Oracle connection process is not lightweight. Once an Oracle connection is made, typically one or more sessions are used, so it's a very different paradigm that doesn't match PHP very well. This is why you usually see Oracle paired with enterprise java server technology where a pool of db connections will be maintained in the java application server(s). I don't think your assessment of PostgreSQL is correct. It is certainly very active and growing. ISP's prefer MySQL because of the simplicity of installation and small footprint. This is the same reason it was used by Facebook and Wikipedia initially. With that said, I'm by no means suggesting you should switch to PostgreSQL without a strong driving reason to do so. I would stay with MySQL, in whatever flavor you would like. The 3 that people are using (Oracle MySQL, MariaDB or Percona) will all work for you. If it's the latest/greatest of each there isn't a huge advantage right now for one over the other, from a performance or technology standpoint. The other things that often concern businesses are backup/recovery and fault tolerance. Another important reason to use InnoDB involves recovery from crashes. What happens when MySQL with MyISAM crashes? How do you get things back up and running if it does? I recommend scanning the answers to this dba.stackexchange question. The key fundamental to understand is the transaction log: something that Oracle, SqlServer, PostgreSQL and InnoDB share. How much data loss can your business tolerate? Hosting companies often provide you limited fault tolerance. I have had for example, an entire AWS EBS volume die with no recovery possible. Over the holiday weekend, a Linode VPS had it's storage die and the entire server had to be migrated. If this was important business data, there could very well have been data loss. From what you've alluded to, you have a SAAS business of some sort, with access to features via paid memberships. I personally would not advocate launching this on a monolithic server. Instead, I would build it on a small cluster. You could start with the the smallest 2 machine cluster available. Rather than running on one "large" server with 8gb you could instead start with 1 medium server allocated to mysql and 1 medium application server. You can start with DNS round robin for load balancing to additional application servers as needed and migrate to load balancing in the future. Monolithic servers are ok for hobby or marketing sites, blogs and other non-transactional sites that are 99% read only. If you have transactional systems with a mix of read/write transactions you are starting out with essentially a non-scalable architecture. The only way to know for sure how much capacity you have or how your system will break is to load test, but rarely is this task performed.
  8. MySQL/InnoDB is highly performant which is one of the reasons (along with simplicity) it continues to be used. All RDBMS's depend on indexing, and the fundamental index type they all provide and implement is the Btree index. FWIW, I have extensive experience with Sybase/Sql Server, Oracle and MySQL. MySQL is unique in that it has alternative engines, as well as forks (MariaDB, Percona). PostgreSQL is often preferred by people coming from Oracle, as it is very similar down to database objects, datatypes and the stored procedure/trigger language (PL/SQL) that are foundations of Oracle use through the years. Sybase/SQL Server were also popularized through use of the stored proc/trigger language T-SQL. Comparatively, MySQL didn't even have Stored Procedures/Triggers until version 5.x beta'd in 2005. The bigger question in the open source world is not MySQL vs the commercial DB's but rather MySQL vs PostgreSQL. Even more recently many RDBMS alternatives have come into the mainstream under the NoSQL or Document DB monikers, most notably MongoDB and CouchDB. This has to do with concerns about scalability, which you don't have reasonable expections will be an issue for you that can't be solved with a bigger server.
  9. With Unit testing, mocks are really the foundation of unit testing with the ultimate goal of improving code coverage. The individual tests themselves have intrinsic input/output, or in some cases a mock is loaded with essential input. I guess the bigger question you want to ask yourself is what is being mocked and why?
  10. In phpMyAdmin, when you look at the list of tables for a database the "type" column shows you the engine. If you did not explicitly set the engine to use the InnoDB engine you will likely have all tables using the default MyISAM engine. You'll need to alter that for each table using a SQL statement: ALTER TABLE TableName ENGINE = InnoDB; MyISAM is not ACID compliant. It also will have thrown away any foreign key constraints, so if you had or wanted those you'd need to recreate them individually after you altered the tables to use InnoDB. Correct. For InnoDB tables. What that means from a performance standpoint, is that if there's a query that uses the primary key index, the entire data for the row gets read in the same operation that searched the index. I only bring this up since you asked about performance, and is one of the many reasons that MySQL DBA's are fairly unanimous in favoring the use of InnoDB.
  11. If your structure is normalized correctly you should be fine. Make sure that your tables are using the InnoDB engine. Any queries that can be satisfied by using the primary key index return all data in the record by reading the index itself. This is because InnoDB has "clustered indexes" which is a fancy term meaning that the data is actually stored in the primary key index. All primary and foreign keys are inherently indexed. So the main concern in regards to other required indexes would be queries that have where clauses that aren't keys in the table definition. This is the single most important detail. Your queries need to use a relevant index. An obvious example would be username & password columns in a user table. Essentially you just need to focus on the WHERE clauses of your queries and make sure that you have an index to handle the query. For example, if you always query for user by username & password, have an index on username, password, and not individual indexes on username and password. If you have a monolithic server (webserver, php, mysql running on one box) then you have to plan for contention. MySQL needs some tweaking to provide a sufficient InnoDB buffer pool allocation. If it's a small DB as you describe your entire dataset can be in the buffer pool cache the majority of the time.
  12. Probably one of the most common misunderstanding for people starting out with the concept of "seo friendly" url's. You are definately not alone, but kuddos for figuring it out on your own.
  13. Phi11w provided an excellent and thorough analysis. In particular consider this data for $admin: <?php $admin = ['fullname' => 'Sally Smith', 'gender' => 'F']; function getAdminName() { global $admin; return ( $admin['gender'] ? 'Mr' : 'Mrs') . ' ' . $admin['fullname'] ; } echo getAdminName(); Do you get what you would expect for a gender 'F' person named 'Sally Smith'? While all of Phi11w's points are valid, I think this is the main thrust of the issue. Even without knowing the scheme to be used, it is unlikely that gender would be a boolean value. Another issue being glossed over, is that 'Mrs' has the connotation of marriage, as well as Barand's point about 'Dr' and other sultations, but that makes things more complicated, and I don't think that was factored into the question, because the existing code has an obvious data typing issue. So an improved function (assuming php7): <?php $admin1 = ['fullname' => 'Sally Smith', 'gender' => 'F']; $admin2 = ['fullname' => 'Randy Jones', 'gender' => 'm']; $admin3 = ['fullname' => 'Pat Samuels']; function getAdminName($admin) { $admin['fullname'] = trim($admin['fullname']) ?? 'unknown'; $admin['gender'] = $admin['gender'] ?? ''; $pre = ''; switch (strtolower($admin['gender'])) { case 'f': $pre = 'Mrs '; break; case 'm': $pre = 'Mr '; break; default: $pre = ''; } return $pre . $admin['fullname']; } echo getAdminName($admin1) . PHP_EOL; echo getAdminName($admin2) . PHP_EOL; echo getAdminName($admin3) . PHP_EOL;
  14. gizmola

    PHP update

    First query, as ginerjm pointed out: SET status = ?, comment = ?, department = ? WHERE id = ? Four '?' , but your bind string is 'ssi'
  15. Another valuable free tool that can be used to test your serverside api calls is Postman. Here's an introduction video I scanned that can teach you the basics of using it effectively in a short amount of time. There are complete courses available on Youtube that cover all aspects of using it, but for your purposes, an investment of 30 minutes or less should have you up and running and testing your login.php script independently from the Unity client code.
  16. Absolutely. The primary options for getting up to date versions of php and the various extensions are: Extra Packages for Enterprise Linux (EPEL) Remi's RPM Repo I have used one or both at various times over the years. One other Repo that has had a focus on PHP is https://ius.io/. The other option is to run your setup in containers using Docker/Kubernetes/rkt etc.
  17. I'm fine with disabling replies. It fits our intentions perfectly. Only thing it doesn't allow is the poster to reply to the original post stating the opportunity is filled, but people rarely if ever have done that anyways. The constant flow of dummies who don't read the instructions and reply anyways is annoying.
  18. Wordpress is going to run under/through the webserver. There are many ways to configure this, whether it be apache/mod_php, or nginx/php-fpm, or apache/php-fpm etc. We would need more information on your webserver and it's configuration. Did you try to make a simple phpinfo() file in the root directory of your wordpress install? Are there any other php apps setup to run on this server? Step 1: make a test file: // test.php <?php phpinfo(); Navigate to this and report back the results. Step2: Provide details on the web server and php configuration. Double check the php specific parts of this configuration Step3: Provde this information from your shell: sudo rpm -qa | grep php
  19. Along the lines of what requinix stated, here are some functions you'll need to calculate where to place your text: imagefontwidth imagefontheight imagesx imagesy To get the length of the string, you need to find imagefontwidth($font) * strlen($yourtext). Hopefully it's fairly obvious that your x,y coordinate for writing the string is going to be: y = imagesy() - (imagefontheight + padding) x = imagesx() - (imagefontwidth($font) * strlen($yourtext) + padding)
  20. I agree with requinix. Newbies really love the facades, but then when they start writing unit tests they are quickly sorry they did. In comparing the 2 frameworks: Laravel is opinionated and comes with things like a user system. Symfony isn't, but does have bundles from groups like "Friends of Symfony" aka FOS, who have a symbiotic relationship with the main Symfony project. There's a large number of FOS bundles you can drop into your Symfony project like the FOS User Bundle and Rest Bundle which might be of specific value to your project. Laravel has an Active Record ORM for database models called Eloquent, that includes a query builder and is solid. Symfony does not provide an ORM, but again has a long relationship both with the Propel and Doctrine projects. Doctrine provides a Data Mapper pattern implementation, which I greatly prefer to Active Record, and it also comes with "Repositories" and a connection manager that handles persistence and transactions in a superior way. The sophistication of the behavior available in Doctrine is far more advanced than what Eloquent offers. Routing and Controllers are comparable in each. Laravel's template language Blade is ok. Symfony's Twig is more advanced in its abilities to compose templates from partials and inheritance. Laravel comes with pretty much everything you need out of the box to develop most types of projects, and has a fair amount of "magic" that handles making everything work together. It's an excellent project with a large community and documentation galore. It's also very popular, and probably has more adoption than Symfony does, particularly in many parts of the US. Symfony also has a large community, and out of that community not only did Laravel emerge, but also Composer, which was an essential dependency management tool that in my opinion saved PHP from obscurity as a web development platform. Having done large projects with both frameworks, I would still choose Symfony (with Doctrine and Twig), but really either one will work for you. They are both dependency injection frameworks, and have a dependency injection container and ways to configure services based on class libraries, whether those be ones you get from FOS or other sources, or that you have built yourself.
  21. Did you try an explain on the underlying query? It might be good to see if an additional index or 2 might help with performance.
  22. Hey Saranac, You seem to be conflating a "transaction" which is a database specific term, with a single page application (SPA) or combined form. What you do serverside with that form is up to you. Ease of use, credit card/payment processing and the dangers and cost of fraud are a complicated witches brew of risk/reward calculations. First of all, I don't know what you know or don't know about payment processing, but just because you got a cc authorization doesn't mean you will ever get paid. Worse yet, you could get a chargeback, and if you have enough chargebacks, you might end up having your merchant account closed. Again, maybe you know this or you don't. I don't know what your content is, or whether or not there will be a high amount of fraudulent activity, but every online business has some. So, as to transactions, and to "best practices" for selling digital goods or memberships, these topics are unrelated. At its simplest, a Member in your system, as you stated yourself, is not equivalent to a membership package. Membership packages also have one or more related "entitlements". So no doubt you have a package described in the database somewhere, and when someone successfully subscribes to a package for a year, one would expect a database structure like this: package ---< member_package >-- member where member_package is a table that stores "member_id", "package_id", "from_date", "to_date". What are strategies for dealing with the system. Well one, would be to have the culmination of a successful payment process be the setting of the "from_date" and "to_date". Another strategy might be to also utilize a "status" column with mutually exclusive states like: pending payment active suspended for fraud expired In that case you create the row, and can then use "active" status to drive access (along with from/to). Regardless of the form you use, there is no benefit to wrapping the creation of a member row with the transaction processing. If a member fills out all their details and has payment processing issues, it behooves you to create the membership row and put them into "pending payment" state. This way you can see how many people had issues with payment processing at any particular period of time. That way you can reach out to people who for whatever reason drop out of the payment process. You have contact info to market to them, or entice them with discounts. And there is always the situation where your payment processor was rejecting everything and these are customers you have 100% lost forever without some record that they tried to subscribe. DB transactions are important from a mechanical standpoint and I highly recommend using them, but you can have multiple transactions in your processing ie (membership account + address) THEN (payment processing & activation) and these can be handled separately in your code. Once you have some identity information, it's valuable to continue to carry this along in your session rather than stubbornly demand that the behavior of your system should conform to your ideal "happy path" scenario where a user puts in all their identity information for their membership perfectly, and at the same time gets the payment information entered perfectly and receives authorization. A "member" row, should be decoupled from membership (which you already stated you understand and agree with) which should be decoupled from subscription(s) which should be decoupled from payment processing details.
  23. Hey Paul-D, Quick clarification: Solaris is not Linux. It's the long standing Sun Unix designed to run on Sun Sparc hardware. Sun was bought by Oracle which is why you can get it from Oracle now. I would not recommend going down that road. Linux has many flavors, but it sound like what you want is a distribution that aims to provide a Desktop environment. Another thing you need to understand about a Linux distro is how committed they are to Free Open Source Software (FOSS). Some distros (for philosophical reasons) will not include or package any non FOSS software, which sometimes means that you have to do a lot of work yourself to get certain drivers or software that you might want or need in your Desktop environment. Linux OS's are in a constant state of flux and new distro's appear regularly. I'll boil down my highly opinionated list for you, with the assumption that you are new to linux, and want a Desktop Linux that looks modern, has package management tools, and is focused on ease of installation, use and configuration. Elementary - Focused on users switching from Mac or PC, this distro has gained a lot of momentum in a short amount of time as an opinionated simple to install OS Zorin OS - Like Elementary a recent entry in the Desktop Windows/Mac replacement distros, with a particular focus on being friendly to former Windows users Pop!_OS - Another of the newer Desktop focused distro's. Comes from System76, which is a Unix hardware vendor that designs and sells everything from laptops and desktops to rackmountable servers. Ubuntu - The longstanding "beginner focused" Debian variant has done a lot to further the goals of Desktop Linux for the masses. I ran it myself for a few years at work. Fedora - This is Redhat's project that has long provided a Desktop OS. Opensuse - Like Fedora, OpenSuse is another of the longstanding linux Desktop focused distros. It has YaST and Zypper for GUI configuration. I have not tried it myself, but many linux Sysadmins have sworn by it for years. I don't know if you listen to Podcasts, but Choose Linux covered many of these OS's to some degree in the first 11 episodes of the podcast. The episodes are fairly short, so you might want to listen to a few of them if a particular distro on this list appeals to you.
  24. Symfony or Laravel, with a strong endorsement of the latest Symfony (v5).
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.