Critical EB Requirement: MS-Access Expert Needed!

The EB team is seeking an MS-Access Database Expert. To give you some idea of what the job entails - and it's importance - I've attached the Database Requirements document. Please read through it and make sure you have some understanding of what the job entails. Once a volunteer has been selected, we can discuss this in far greater detail.

************* Section I - Purpose ***********************

The purpose of the EB Recruitment Database is to take information from a variety of different tables and assemble it as either a sequence of discrete fields (subject to concatenation after export to Excel) or a string of concatenated text. I cannot overemphasize the CRITICAL importance of this database. The task is almost impossible to perform manually, since we need to code the recruitment of 500 units in 199 provinces by 20 factions using 20 building complexes (each with up to 5 levels).

In Rome Total War - and by extension, Europa Barbarorum - unit recruitment is based on one or more buildings present in a city, and the file which links units to buildings is called export_describe_buildings.txt (EDB). Here is a short example of recruitment text from the EDB file, followed by a section which explains what it means (and more to the point - what has to be extracted from the database):

************* Section II - Raw EDB Code ***********************

PHP Code:

---

building barracks
{
    levels muster_field militia_barracks city_barracks army_barracks royal_barracks
    {
        muster_field requires factions { germans, britons, gauls, dacia, spain, romans_brutii, egypt, romans_scipii, carthage, parthia, numidia, thrace, greek_cities, macedon, romans_julii, seleucid, scythia, }
        {
            capability
            {
                recruit "eastern skirmisher cavalry ayrudzi"  0  requires factions { romans_scipii, } and hidden_resource alpha and not hidden_resource beta and not hidden_resource gamma and not hidden_resource delta and not hidden_resource epsilon and not hidden_resource zeta and not hidden_resource eta and not hidden_resource theta 


---

************* Section III - EDB Code Explained ***********************

1) "building barracks"
- "building" is code which tells the game, "what follows is the name of a building complex"
- "barracks" is the system name of the complex of buildings (i.e. all levels) in which recruitment will occur.
Database Key Point: Building Complex is the primary grouping of unit recruitment.

2) "levels muster_field militia_barracks city_barracks army_barracks royal_barracks"
- "levels" is code which tells the game, "what follows are the names of all buildings contained in the previously named complex"
- "muster_field" is the system name of the Level 1 building.
- "militia_barracks" is the system name of the Level 2 building.
- "city_barracks" is the system name of the Level 3 building.
- "army_barracks" is the system name of the Level 4 building.
- "royal_barracks" is the system name of the Level 5 building.
Database Key Point: Not required - yet.

3) "muster_field requires factions { germans, britons, gauls, dacia, spain, romans_brutii, egypt, romans_scipii, carthage, parthia, numidia, thrace, greek_cities, macedon, romans_julii, seleucid, scythia, }"
- "muster_field" signifies that all the information which follows will apply ONLY to this building complex level.
- "requires factions" is code which tells the game, "what follows are one or more system names of the factions that are allowed to construct this building level"
- "{ germans, britons, gauls, dacia, spain, romans_brutii, egypt, romans_scipii, carthage, parthia, numidia, thrace, greek_cities, macedon, romans_julii, seleucid, scythia, }" are the system names of the factions that are allowed to construct this building level.
Database Key Point: Building Level is the second grouping of unit recruitment. We can ignore the faction information here as it refers to construction capability, and we are only concerned with recruitment.

4) "capability" is code which tells the game, "what follows are one or more capabilities". (As a general note, buildings can do other things besides facilitate recruitment, such as improve happiness, reduce squalor, etc.)
Database Key Point: Not required.

5) "recruit "eastern skirmisher cavalry ayrudzi" 0 requires factions { romans_scipii, } and hidden_resource alpha and not hidden_resource beta and not hidden_resource gamma and not hidden_resource delta and not hidden_resource epsilon and not hidden_resource zeta and not hidden_resource eta and not hidden_resource theta"
- "recruit" is code which tells the game, "what follows is information directly related to the recruitment capability"
- "eastern skirmisher cavalry ayrudzi" is the system name of a single unit.
- "0" unknown (will add to this later). For now, just be aware that this value is always zero except in the case of "roman infantry imperial vigiles", when it is "1"
- "requires" is code which tells the game, "what follows are one or more conditions which must be "true" for this unit to be recruited."
- "factions" is code which tells the game, "what follows are one or more system names of factions which can recruit this unit"
- "{ romans_scipii, }" is the system name of this faction. Potentially there could be more than one. In this case romans_scipii refers to the in-game Hayasdan faction (Armenia).

(Before we dig into the next set of code, first some background. The bizarre looking string of text is actually the special code used by EB to individually mark every province on the map. Unfortunately, the EDB file does not allow us to enter province or city names, so we had to use the "hidden resources" feature. These can be placed in any province on the map, in any combination, and we set up the combinations so they would be unique to every province. Although complicated, this ability is extremely important or else it would be impossible to restrict unit recruitment only to certain provinces. Otherwise you'd be able to recruit Spartan Elites in Bactria and Horse Archer units in the dense forests of Germany (among MANY other historical impossibilities). An example in vanilla RTW is the restriction of elephant unit recruitment to provinces containing the elephant resource.)

- "and hidden_resource alpha and not hidden_resource beta and not hidden_resource gamma and not hidden_resource delta and not hidden_resource epsilon and not hidden_resource zeta and not hidden_resource eta and not hidden_resource theta" is code which tells the game, "what follows are the presence (or absence) of hidden resources which define the province in which this unit can be recruited". Specifically: alpha, (beta), (gamma), (delta), (epsilon), (zeta), (eta), (theta), means that alpha must be present in the province, and seven others must be absent. On the map, this sequence of HR's refers to the province of Adiabene.
Database Key Point: This is where the real work comes in. There is no more grouping, just creating individual rows of recruitment text. First we capture the system name, Second the faction system name, and Third the presence or absence of a string of individual hidden resources which define the province.


************* Section IV - EB Recruitment Database Description (Current State) ***********************

First, here's the good news. The MS Access Recruitment Database has been created, but the limits of my expertise have been reached. I'm going to need help from somebody who can link up all the tables in such a way as to generate the recruitment lines for EDB (as described above). Here are the key tables, with a brief description of each:

1) UnitNames: 8 fields, with the important ones being:
- EDU (a unique number assigned to every unit in the file called export_describe_units.txt, aka "EDU") This is the "Key Field" and will link this table to every other table containing units.
- newType (the unit's "type name", also from the EDU file) The unique unit name identifier in EDB (part of the generated text).

2) Region Codes: 10 fields, with the important ones being:
- Region (The province name from the EB Map) This is the "Key Field" and will link this table to those containing units.
- Div, Sect, 1, 2, 4, 8 (Six Hidden Resource fields that define the province location) These replace the old "eta, beta, theta" HRs used in the v1.2 EB recruitment system. All of these must be generated as text, as they define the provinces where individual unit recruitment can occur.

3) MIC Complex: 6 fields, with the important ones being:
- MIC_C (A unique number assigned to each of the 20 new MIC Complex Buildings) This is the field which links this table to those containing units. (The actual "key field" was generated by Access, but can't be used for linking)
- MIC_N (The "system name" of each MIC Complex) This is the name used in EDB, and must be part of the generated text.
NOTE: This table is incomplete, as it will need to be expanded to include the names and numbers of each MIC level. However, the numbers are captured in other tables (see below), so we can convert them to the text names later.
- Faction (the vanilla faction name for each EB faction) This is the faction name used in EDB, and must be part of the generated text.

4) Faction Tables: There is one table for every faction (except two, which will be added later) and 208 fields in each table. The important ones are:
- EDU (unique identifier for the units recruitable by each faction) This is the field which links these tables to the Unit Names table.
- Faction (the vanilla faction name for each EB faction) This is the field which links these tables to the MIC Complex table.
- MIC_C (the MIC Complex number) Determines which complex will recruit each unit.
- MIC (the MIC Complex level number) Determines the minimum level of the complex which can recruit each unit. As noted above, this will also be used to generate the Building Names for the indicated MIC Level.
- Other (for Building Conditionals) Not 100% worked out, but indicates which units have recruitment affected by another building such as blacksmith or tavern. There are very few of these, fortunately. The Building Conditional must apply to the MIC Level building (for that faction) in order to avoid the CTD problem.
- Province Names (199 fields) These are the fields which link these tables to the Region Codes table. Each field is populated with either "Yes" or "No" to indicate whether individual units can be recruited here.


************* Section V - EB Recruitment Database Output (Required) ***********************

This has been discussed in various places above, but here's roughly what's required:

1) Individual "Recruitment Reports" for every Building Level within every Building Complex. With 20 complexes and 5 levels, that means up to 100 different groupings. Since the data will probably have to be exported, an actual MS-Access report is probably not what we want, but rather something more like a query.

2) Within each grouping, the recruitment text must follow the sequence of unit system name, faction system name, and hidden resource string.

3) Unless the database output can be crafted in such a manner that it spits out perfectly aligned EDB text (probably too much to hope for), we will need to export the individual "reports" into Excel for concatenation and addition of spaces and code elements. That will then have to be assembled for further export into a text delimited file for inclusion directly into EDB. The amount and extent of this activity is wholly dependent upon the nature of the Database output, so we'll have to nail that down first.

************* Section VI - Summary ***********************

In summary, this task is of critical importance to the new Europa Barbarorum Recruitment System. And due to the sheer complexity of the task, the support of an MS-Access Expert is absolutely imperative. And when I say "Expert", that's what is needed. This is NOT a "learn-as-you-go" project for people with limited Access skills, as I can attest from sad experience. One final thought. Given the universal fact that all RTW recruitment passes through EDB and that it's always a laborious, error prone chore, this database is potentially useable by ANY RTW mod. So the development of this tool has application beyond just EB.

Volunteers should post in this thread and/or send me a PM. It would be helpful if you could provide a brief description of the experience that qualifies you as an "Access Expert".

LordBula is using C++ to produce EDB-ready text based on the current Access tables. This is huge, and provides an unlooked for solution to this vexing problem. His intial test has generated a full set of recruitment text for the Romani, so this method looks VERY promising. Generating that text by hand (the old method) would have taken a week of solid labor. Needless to say, this is excellent news! :2thumbsup:

All hail teh Bula!

Quote:

---

Originally Posted by orwell

All hail teh Bula!

---

What he said. :yes:

Yay! :balloon2: :balloon2: :balloon2: :balloon2: :balloon2:

Yes, LordBula is making exceptional work.