I’m taking my first foray in to DB creation/use with a tool/pipeline bit I’m working on.
I’m setting up my DB via phpMyAdmin (got XAMPP because it’s what I use to run a local test of my website). Because I’d like for these skills to eventually transfer to something I can use at a company, how do you set up your users? for developing the tool for me I can just set up a user as myself and use that hard coded in, but I want to be learning how to do this right
I know this is probably DB admin 101 sort of information, but I basically have my DB rock and my SQLAlchemy rock, and i’m banging them together and saying ‘now what?’
also, thank you for the talk during GDC, Adam, since you’ll probably end up reading this eventually. Super inspiring.
okay yes…probably a little vague. Most statements below are made based on assumptions that may be wrong.
Basically I’m making a tool/imaginary pipeline for portfolio usage. I have an exporter from photoshop that I want to have writing information to a database (file location, map use, meta data, etc. etc.). In a company, I would have more than one person using this exporter, and, thus, I assume I’d want each person to have their own access to the DB, meaning their own username/pass.
Is setting up that access something that should/would normally fall under the purview of a tool like this? or is it safe to say “each user magically has a username/pass to the DB. They put their settings in this config file like so, and then it works for them”
I would write a small login tool for use inside photoshop. (javascript that writes to the config) I like the idea of a config file, but I wouldn’t want them editing the file manually. Also just letting you, I don’t use DB’s too much. May want someone smarter to answer…
usually there’s no reason for everyone having an individual user name unless you feel paranoid. Without a special setup this will not give you extra security. Every user with update/delete rights to a table can modify other user’s records. You could just as well use a single user account for everyone, except the admins of course.
However if you feel particularly paranoid and anticipate that people will just log to the DB with a 3rd party tool (i.e. not your script but for example mysql workbench) and start running amok and modify whatever they can, then you could keep individual accounts and try setting up a trigger that gets triggered on updates and/or deletes. That trigger could then, depending on the user name, prevent certain modifications, or keep a backup of the changed data, etc.
[QUOTE=UncCheezy;11743]plus you could log who’s doing what…[/QUOTE]
yes. although just logging who does what doesn’t prevent people from opening your script and using the credentials to log in with a 3rd party tool to modify the db.
mySQL keeps something called a “General Query” log though, where all queries are logged. Although this is not really useful in bringing back your data once it got tampered with. It only helps you to track down the culprit.
If you’re really really paranoid about this then you have to consider accessing your database through other means that offer more protection from user’s tampering attempts - e.g. a compiled access library or some gateway server that sits inbetween the client and the real db.
I’d suggest not getting overly concerned with permissions, and logging who does what. It can really complicate otherwise simple things. Maintaining separate DB logins for everyone would also be a large headache.
I always use one “tool/user” account per database, and have a given tool use that no matter what user is driving. Then have an admin account for yourself or whoever needs more control over the DB table structure, etc.
Are you having troubles getting SQLAlchemy to connect to your DB? Or are you set there and just wondering what to do next?
If connection is the issue, the typical SQLAlchemy connection boilerplate goes like this:
import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.ext.declarative
# Get our declarative base class object
Declarative_Base = sqlalchemy.ext.declarative.declarative_base( )
# URL controls choosing dialect (using MS SQL as example) and all login creds
url = 'mssql://login:password@servername/databasename'
engine = sqlalchemy.create_engine( url )
# Create the session, bind it to engine
Session_Class = sqlalchemy.orm.sessionmaker( )
session = Session_Class( )
session.bind = engine
If you’re wondering what to do with it, that’s hard to say. Do you know what data you’d like to store in the DB? If so, then the next step is designing your DB schema (structure of tables and columns), and how those map to your ORM classes in the code.
If you post what data you hope to store/retrieve I can probably provide more specific suggestions there.
Okay, this makes things much more simple, which is good. I just didn’t want to over-simplify, and, thus, rob myself of a chance to learn something of day-to-day practicality.
I already have been poking around with connecting (I went through the SQLAlchemy tutorial, and then did it again connected to my DB on my machine (instead of just running it in memory). There will be a few sets of data that I assume will probably be different tables on the DB.
I have to run to work, but I’ll post up my specs for all of the information I want stored when I get home (3PM Eastern). If anyone feels like looking at my code, it’s on my github here (it’s still unfinished, and is currently relying on XML files, which is why I have the XMLSidecar class and the XMLSettings class, and why they’re so heavily refrenced). The relevant classes are all in the xml_sidecar module, although I’d probably only directly store instances of the Map class (Channel/ChannelSet are there, but they’re used as properties of a Map.
Anyway, must run, thanks for all the input, learning a lot!