THE WIDER PICTURE
|
|
The idea beyond and behind the idea
|
This is a SQL (not necessarily mySQL) class in PHP that helps in churning out SQL commands or queries in those cases when the amount of queries you have to produce is consistently high and/or repetitive and yet it would need some sort of adjustment per query.
As it is now, and though it will most likely be updated with new features over time, it can indeed save a lot of typing, and it has been conceived as a first step in the long thought and long belated development of a very complex PHP application that has haunted me as an idea for one year at least by now: developing a database driven application, suitable to be used either for personal use or for public availability (geared to specific subscribed members who will have access to their own SQL tables, or to an unspecific generic public which will access one very same set of tables), meant to learn new human languages.
Well to tell the truth, this is not actually the very first step: when I developed the PRINT IN A HTML FORM INCOMING SQL QUERY FIELDS and the MYSQL DATABASE MANAGEMENT CLASS, I already had this project in mind and I developed those classes having this project secretly as my eventual remote goal.
A Joan Miro painting
|
The name of the project is LEADER, which stands for:
Language E-learning Auto Drilling Expanding Routines.
The acronym is not a futile witticism.
The idea is based upon the difficulties I myself, as an italian native speaker, met when learning english: and I do find that the only right way to program and script is when you yourself have faced and endured the difficulties and the errors you are going to cope with.
My experience proves that nothing can substitute on the field experience when dealing with a language (english, french, italian, chinese, whatever...), and that if you are more inclined towards having a teacher rather than towards being self taught (as I mostly am: when I started dealing with javascript 8 years ago not even one book existed), there is no substitute for a teacher.
Yet what LEADER can do in my conception is not of overtaking a teacher or the on the field experience, but on the contrary to relieve teachers from the less agreeable and creative part of their job, and provide the pupil with a powerful tool that will conversely turn into an agreeable and creative experience the less enticing aspect of every new language: that is, memorizing indeed the new vocabulary.
Whether I will be able to deliver the whole project is not within the scope of my foresight to predict, and no one steals the fire with impunity.
Yet I will try, and anyway I am starting here to state the idea.
What is that I most missed when I realized I could not learn all the new words that I found while reading an english magazine, and what is that I most regret when I find on a book a lot of english terms that I suddenly remember that I have always been knowing and yet I met them so rarely that they never made their way within the range of my everyday colloquial arsenal of terms immediately and spontaneously available to the fore of my mind when engaged in speaking or writing in english?
What I regret is this: that the reason I know them and yet I don't remember them unless I see them written down again is this, that I had no chance to exercise myself with them.
Therefore here comes the idea of LEADER.
I need to be able to store in a database all the new terms I find.
I need to populate each new term with a recordset of data, like its translation (or multiple interconnected translations) and the database must be auto adaptive, that is: if a term leads to a set of translations and a new term leads to one of those translations, the two original terms must be automatically arranged in the database as potential reciprocal synonyms too. For this we will also take avail of PHP implementations of javascript permutations and combinatorial scripts [ more javascript permutations, and, though not directly related, the javascript all anagrams script and the javascript DNA ICHING script] I developed long ago.
There must be phraseology and when I add or update a term, the date must be stored.
Once this is made, and the interface is ready, the great thing which justifies the Auto Drilling part of the acronym LEADER is this: that I must be able to select another interface where I can set a drill, sort of exercises performed without supervision just relying on the correctness of the dictionary entries, that I can perform without the presence of any teacher.
This drill means querying the LEADER database extracting the terms either by a date range or by a term typology, or by the most frequent latest errors (maybe also within a span of time), performing upon demand all the type of data mining the database allows for, and presenting the end user (me, I guess) with an interface where for the extracted terms I am prompted to provide, by typing it in a text field (or eventually also as by picking from a set of predigested choices - thence I account for the expanding part of the acronym LEADER: functionalities can be added and varied at all times, by all developers), the right answer (or hopefully so).
For instance, given the translation(s) of a set of terms into my own native language, provide the answers in english, or viceversa.
This is creative, because the pupil can custom the drill; this is fun; this is something a pupil might even take the initiative to do; this would make me (or any pupil) remember over time also the most exotic terms.
Isn't this interesting and rational?
Now, for all the scripts you find on this website (till now october 2004 above 400 scripts most of the times of remarkable a complexity) I do not charge you one penny. Others would have started asking for your money after two days, or would have inaugurated a "members" only section...
This is all open source stuff. All I am asking you is: if you're to follow me or develop LEADER with your own components, do not forget who has been trudging here for free, and many times even being insulted in the most offensive ways by other fellow programmers who just didn't like my coding habits or who evidently believe they are above errors: so don't forget to pay an acknowledgement and a link, for that's all the wage I have while I live on canned food.
YOUR CODES
|
|
The class code and how to initialize it.
|
Here is the code for the sqlHelpDesk class, for such is its name.
I will document its methods at the next section.
You will probably notice that the examples I provide further on witness that the intention is to build deeply interconnected SQL tables, ideally with a heavy usage of many-to-many tables, and for which the creation of the tables may require repetitive code.
The shape of the database, in the default values the arguments of the methods have, is that of building up tables that can be fully indexed after each field: it may be space consuming for a SQL application, but in the traditional trade off between space and time, it is all a blessing as far as search time saving is concerned; and needless to add that when we're dealing with a potential dictionary, which is at least what I had in mind when this class was crafted (though the class can of course accommodate whatever other type of queries or intentions), search time is of a bigger concern than space, and thus indexes are mission critical.
The initialization of an instance for your class is absolutely trivial, assuming as a variable name placeholder my usual $foo:
$foo = new sqlHelpDesk();
Here is your class code:
METHODS OVERVIEW
|
|
The methods documentation
|
This method is designed to solve the following problem.
Let's imagine you have a function that accepts as an argument a list of strings, or an array of strings. When these strings are passed as arguments, the method can populate them with variable values in place of variable names present in the string(s), example:
function foo($arg1, $arg2){
return "hallo $arg1 I am $arg2";
}
If you pass to the function as $arg1 the value 'World' and as $arg2 the value 'Alberto', the function returns, obviously, the string:
"hallo World I am Alberto"
namely the string with the variable names swapped with the variable values.
Let's imagine that you have a great amount of strings, or of array entries, all likewise "hallo $arg1 I am $arg2" was, and all meant to undergo swappings with some passed arguments: since these string are, in our hypothesis, many and you use them for many applications, you may want to store them somewhere else, say in an external file, so that you can call these strings in only as components when required and you don't bhave to type them all again each time they are needed.
The problem with this is that you can store them nearly nowhere because if they are stored externally to the function meant to perform the designed swappings
variable Name versus variable Value
namely out of the function scope where $arg1 and $arg2 exist, Php would generate an error rightly complaining that the requested interposed variables are not defined.
With this method you have a way to work around this case: saving the strings in whatever external scope (or file) so to call them in as modules, and at the same time being sure that when the called in module is then passed to a function designed to perform the intended swappings like the aforementioned ones, the swappings can occur within the function while all the same causing no errors when the same strings resided without the method.
The method takes in these arguments:
- $string: defaults to 'SELECT * FROM []' for illustration purposes.
The trick is all in this, instead of putting in your string a variable name that cannot be reached any longer for the string is designed to reside in a scope where the variable lifespan doesn't exits, you put in your string a conventional set of chars, in our case two square brackets with no space in between them: []
- $replacements: defaults to array('tableName').
This must be an array of all the strings, or variable names, that must be swapped recursively with each occurrence of [] within $string.
The possible [] that would outlast the amount of available entries in this $replacements array, would be left untouched, that is they would remain in the original shape [].
- $searched: defaults to '[]'.
This is the type of element you want to replace that must be searched for in the string.
Examples:
$foo=new sqlHelpDesk();
print $foo->inString(
'hallo [] I am []',
array('World', 'Alberto'),
'[]'
);
Or, more interestingly:
$foo=new sqlHelpDesk();
function foofoo($input, $arg1, $arg2){
global $foo;
$argsToArray=func_get_args();
return $foo->inString(
$input,
array_slice($argsToArray,1) ,
'[]'
);
}
print foofoo('hallo [] I am []', 'World', 'Alberto');
As you see, you have thus been able to store externally a string meant to include interposed variable names that, if stored outside the scope of the function using such variables, would have triggered errors.
Tech note: the method resorts, in its internal code, to using substr_replace, which can be set to replace not all the instances found of the element meant to be replaced but only a few of them, and does not use str_replace, that would replace all the instances, because not all instance must be swapped with the given replacements: being the latter an array, the method presumes that each newly found instance must be swapped with a consecutive entry of the provided replacements.
This method (note the ending X) behaves almost as the previously documented inString, and is sort of a "savvy" implementation of the PHP built in strtr function.
Please note that the Php method str_replace should not be preferred to strtr when at doing this type of swappings.
The difference between this method and the previous one inString is that this method can recursively replace items on:
- More input strings, in case the first argument is an array of strings.
- More different items, identified by different identifiers, to be searched for being replaced in each string.
The arguments are as follows:
- $string: can be an input array of strings or a mere string.
If it is an array, the method will scan all the entries and will replace them all accordingly to the next two arguments, then returning the input array with its entries all modified.
It defaults, for illustration purposes, to:
'SELECT * FROM [1] WHERE [2]=[1]'
- $replacements: an array.
The difference is that now the entries in $string will not be replaced for as many times as many entries within $replacements are, but would be replaced using str_replace, thus affecting the whole of the string per each entry present in $replacements.
Defaults to:
array('swapped1', 'swapped2')
- $searched: an array (and not a string as with isString).
These can be different types of identifier: it defaults to:
array('[1]', '[2]')
Therefore what is important with this method, is that the order you mean the entries passed in $searched get swapped after, is the same order you arranged the $replacements array entries after; that is: if the, say, second entry in $searched is say ' X', and you want it replaced with ' Y', then Y must be the second entry in $replacements too and not the second only in one of the two passed arrays $replacements and $searched.
If by chance the $searched array entries are more than the $replacements array entries, the exceeding $searched entries would not be replaced in the outcome string(s).
With the following example youn can grasp immediately how the method performs:
$foo=new sqlHelpDesk();
$foo->inStringX(
array(
'SELECT * FROM [1], WHERE [2] = [1] AND [3] = [1]',
'example 2: [1] [2] [3] [1]'
),
array('swapped1', 'swapped2', 'swapped3'),
array('[1]', '[2]', '[3]')
);
/*
SELECT * FROM swapped1, WHERE swapped2 = swapped1 AND swapped3 = swapped1
example 2: swapped1 swapped2 swapped3 swapped1
*/
The purpose of the method is to arrange and return in an array a set of SQL statements out of a reduced set of inputs. Obviously, it is implied in this that the SQL statements include many repetitive parts, and yhet these repetitive portions are not so regular to allow for a banal loop that vbuilds them all out of one single set of inputs.
I need now your cooperation, I need a bit of your attention.
You can understand this with the description of the arguments:
- $tables: must be an associative array where each key field is a string (the name of a SQL table), and the value is a number of your choice, no matter which as long as you remember what number you have passed: say:
array('name1'=>3, 'name2'=>0 /*etc...*/)
Such number will subsequently be used as an identifier to flag other arguments and the target each of them is meant to be dispatched onto. Read on.
- $fields: must be a standard array whose each entry is the name of an involved field.
The method's implied assumption is therefore that for each table mentioned in the first argument, its fields involved in the building up SQL query have all the same names mentioned here: basically, these tables are strongly characterized by their given table name, but not at all by their field names which are all equal per each table.
If you don't find this criteria fitting your needs, then this is not your script. But you should understand that the field names of a table are, in my conception, less revealing than the name of the table. You may agree or disagree, but I can assure you this spells some flexibility.
- $sqlHeaderCommand: defaults to: 'CREATE TABLE IF NOT EXISTS'
- From now on the next arguments are undefined - that is, though the method has a few defaults, the basic underlying idea is that you must now pass more argumens though they could not appear openly in the signature of the method itself.
What data type these additional arguments must be?
Such further "undefined" arguments must be arrays
These arrays must be in the shape:
X
number => String
whereas the number can be whatever number as long as it exists among the numbers passed as values to the $tables argument.
As such, these additional arguments can be either a standard array, for standard arrays are keyed by numbers by default, or in case the numbers that appear in your $tables argument are not sequential from zero onward (which I would not recommend for it would make things more complex), then they should be stated openly via the associative array => notation though technically these arguments would need not to be associative arrays.
How many entry these additional arrays must have?
As many as the different distinct numbers assigned as values in $tables are.
How many these additional arguments must be?
Now that we know what these additional arguments are (arrays) and how these arguments must be shaped (standard arrays or associative ones if the indexes are not consecutive numbers from zero onward, and we know how many entries each of these additional argument arrays must have), how many should these arguments be? Is there a constraint? Yes, there is.
These arguments must be as many are the amount of fields in the $fields argument are (say 3 fields? Then 3 additional arrays as arguments, ok?).
The method will then do the following:
- Iterate through the passed table names in the array $tables
- For each table name, it will iterate through all the passed field names in the $fields array.
- It will create for each table a query, which will be inserted in the returned output (this latter being an array) as one of its entries; the generic shape of each of these queries at this stage of the iteration looks like:
$sqlHeaderCommand + $tables[x] + $fields[y]
- For each field it will append to it a set of statements of your conception.
In the default values of the method arguments, these are SQL field data types and/or SQL field constraints (which as you perhaps know must go after a field name has been declared, upon creation of SQL tables) assuming the method is employed, in its default, which is to create SQL tables and define the data types of their fields.
These appended elements that get tagged to the fields, will be grabbed exactly from these additional argument arrays just discussed.
How?
- The way these appended commands that get appended after each field name are determined, is as follows: for each passed table name, the method will check what number that table name holds for this table name entry (the $tables argument must be an associative array whose each key is a table name string and whose each value is a number, remember?):
array('name1'=>3, 'name2'=>0 /*etc...*/)
This number is the critical element of the whole trick: it is by it that you map these latest "undefined" arguments and you tell them how to append data after each field name.
- Such number will be used to grab by the key the corresponding entry in these latest arguments.
A pseudo overview might be:
$sqlHeaderCommand + $tables[item] +
$fields[num1] + nextArg[num1][item] +
$fields[num2] + nextArg[num2][item] +
$fields[numX] + nextArg[numX][item]
You will understand better by seeing an example.
See this example: we have a set of SQL table names that we want to create (default behaviour).
When arranging these SQL table names in the associative array the method wants as its first argument, we assign to each of these table names, as said, a number; say:
$tables=array(
'types'=>0,
'term'=>0,
'type'=>2,
'created'=>1,
'updated'=>1,
'definition'=>0,
'translation'=>0,
'translate'=>2,
'synonym'=>2,
'example'=>3,
'error'=>2,
'error_date'=>1
);
As you see, the assigned numbers span from zero to three included, therefore we have at least 4 possible data that must be appended to the fields. Let's assume, to simplify, that the fields are only two per table:
$fields=array('id', 'item');
We now do the following:
$foo->queryFactory(
$tables,
$fields,
'CREATE TABLE IF NOT EXISTS',
array(
0=>'INT(5) PRIMARY KEY AUTO_INCREMENT DEFAULT 1',
1=>'INT(5)',
2=>'INT(5)',
3=>'INT(5)'
),
array(
0=>'VARCHAR(60), INDEX(item)',
1=>'DATE, INDEX(id, item)',
2=>'INT(5), INDEX(id, item)',
3=>'VARCHAR(255), INDEX(id)'
)
);
This will produce the following output:
You may note that the data types appended to each field name in each output query are derived per each field by its corresponding passed additional argument (namely the arguments passed after the string 'CREATE TABLE IF NOT EXISTS'), and within each of these latter the grabbed entry is determined by the red number matching the number that that table name held as value when passed to the method.
This is definitely one of those rare and yet nefarious circumstances, that I am sure your yourself have met at times in your life, where something is infinitely more complicated to be explained and to be described, than to be used or perused to grab its logics.
If you test the outputs before delivering a script that uses this method, you can devise nice scripts yourself that, taking avail of this method, can avoid you typing 50 times in the same page the same code to make SQL queries: query code that is repetetive yes, and yet maybe not so completely repetitive to be fit to be accommodated within a mere loop.
This method may help you in those circumstances and therefore be worth 10 minutes of your attention: for as I said many times, the only thing I ask from you is a bit of your attention and nothing of your money. For things can not be 100% for free and also 100% easy, in this best of the possible worlds of ours: can they?
|