User Manual
Contents
1. 50 133 Database WO Nano etate cU pae Ca a a a PESCA TANG Dn ecd dem ud 50 192 Upst de Pah cosas ta RS HR RECTOR LES OPE SEATS ee ee RE CPP I dS 51 13 3 Redundancy ceres uncias i IE Geena median KOPA PAG pn ARR MU de EAR qp E S meer REA 52 14 Options and Optimization 0 055c 20x5c5c 20ccq 53 14 1 Optimize Platform wv dodo ek ba ei OR Pac dne Var ehe rri codeword eed Pea Rae 53 14 2 Optimize Data AA Gea cedi e 55 15 FAQ and Troubleshooting 0000 57 15 1 Questions EHORS ee eee OE PA QR Pa adn SR OE P AO A pU A RODA A Cl team 57 150 Answers Resolutions rue CE pete e LU ptam exa Ud opas 57 Q Why are the changes I ve made to the database in my project not showing up when I run my application 2 2 22 ee ee hh ht 58 Q Why can t I drag my database to the manager se eee ee eee eee 59 Q Error Failed to open database at the working path O 012222222222222 eee eee eee 60 Q Error If you want to change the database s working name then you will need to supply a new working name in the SimpleSQLManager 120252202222220 ana una ena nades ena eines 60 Q Error SQLiteException no such table 112222222222222 III 61 16 Revisions serrr 99 9 9 e yer ii ves seca e woe ge E quee wo E e E se P a ee OOS 62 1 Overview SimpleSQL is a plugin for Unity3D that allows you to easily query or modify data2. your sql statement To insert you call the Execute function in your manager string sql INSERT INTO PlayerStats PlayerName OS QUARS Points T 4 VALUES M MED dbManager Execute sql New Player 3 50000 Note that we had three parameters and passed three values in our execute command The number of parameters must match the number of values Insert Record Examples 37 Q You do not need to use parameters at all if you prefer You could just as easily built up a SQL string using concatenation Insert with Class Definition You can also quickly insert into a database using the class definition of your table We first create an instance of our class definition fill it in with data and pass the instance to our manager using Insert ty layerStats playerStats new PlayerStats PlayerName New Player Totalkilis 3 Points 50000 dbManager Insert playerStats For this method to work your class definition must match the table definition with no extra fields For example the Weapon class could not be used like this because it has a definition of a field populated by a join with another table WeaponTypeDescription You may need to create separate classes for your table definitions and your query results 9 Updating Records To change existing data in your database you use update commands 9 1 Update Record Examples Update with SQL Statement strin
3. 1 Create through the unity menu 14 Creating a Database Manager 2 Create through the Hierarchy menu 3 Attach to an existing GameObject Create Through the Unity Menu To create a new SimpleSQL Manager through the Unity menu click on the SimpleSQL gt Create SimpleSQL Database Manager Figure 5 1 Create SimpleSQLManager Through Unity Menu This creates a new DB Manager object in the scene with the SimpleSQL Manager script attached Figure 5 2 New Database Manager 5 74 T Ma anar Tag Untagged Layer Creating a SimpleSQLManager 15 Create Through the Hierarchy Menu To create a DB Manager through the Hierarchy menu Click the Hierarchy window s Create button and select SimpleSQL Database Manager Figure 5 3 Create SimpleSQLManager Through Hierarchy Menu li Create gt SimpleSQL Attach to an Existing GameObject You can attach the SimpleSQLManager script to an existing GameObject by l Dragging the script to the GameObject 2 Attaching the script through the Unity menu 16 Creating a Database Manager Dragging the Script to the GameObject Select the GameObject you want the script to be attached to 2 Expandthe SimpleSQL Runtime dll in your project window You should see the SimpleSQL Manager script located there 3 Drag this script onto the currently selected GameObject Figure 5 4 Select GameObject Create a SimpleSQLManager with System Data 17 Attaching the Script Thr
4. 3 Setting Up Your Project To use SimpleSQL in your project first create a new project in Unity It s best to create a new project for any version changes so you can have a history of the plugin in case something goes wrong and you want to revert to an earlier version Figure 3 1 New Projec New Scene Ctrl N Open Scene Ctrl O Save Scene Ctrl 5 Save Scene as Ctrl Shift 5 Open Project Save Project Build Settings Ctrl4 Shift B Build amp Run Ctrl B Exit You can then import the SimpleSQL plugin from the asset store Go to Window gt Asset Store Figure 3 2 Asset Store ipleSQL Window Help Next Window Ctrl Tab Previous Window Ctrl Shift Tab Layouts b Scene Ctrl 1 D Game Ctrl 2 Le Inspector Ctrl 3 M Hierarchy Ctrl 4 M Project Ctrl 5 Animation Ctrl 6 Particle Effect Ctrl 8 Profiler Gtr14 7 Gtri 0 Asset Server Lightmapping Occlusion Culling Navigation Console Ctrl Shift C Search for SimpleSQL in the Asset Store Figure 3 3 Asset Store Search Categories ft Home 3D Models gt Textures amp Materials gt Andin If you have not purchased SimpleSQL you can click on the Buy link 6 Setting Up Your Project Figure3 4 Buy SimpleSQL If you have already purchased SimpleSQL you will either be shown a download or import link depending on if you have the latest version This will download the entire Sim
5. database by right clicking in your project window and going to Create gt SimpleSQL Empty Database This allows you to specify the exact sub folder of the database without having to move it there Create a Database with a Third Party Tool Figure 4 5 Create Database From Asset Menu XY Ub_CreateAndvrop able Folder Fa Sc iA in Explorer Y lag Scrip he v E301 Delete Import New Asset v bs Import Package aa Export Package Find References In Scene v E304 Select Dependencies Refresh Ctrl R v Reimport Cs C306 Reimport All v Fa Sync MonoDevelop Project Location A Weanan 4 2 Create a Database with a Third Party Tool It is beyond the scope of this document on how you can create a database using a third party tool such as Javascript C Script Boo Script Shader Prefab Material Animation Cubemap Lens Flare Custom Font Physic Material GUI Skin SimpleSQL 11 SQLite Manager for Firefox The key thing to keep in mind here is make sure your database has the extension of bytes Figure 4 6 Bytes Files Mame Fantasy bytes SciFi bytes Size Tvpe 192 KB BYTES File 96 KB BYTES File 12 Creating a Database Q You can change the extension of the database with the SimpleSQL Manager discussed later by changing the database s working name The bytes extension is necessary for your project 4 3 Making Changes to Your Database You can make changes to your d
6. hn 36 Insert with Class Definition PGKA CO Reb Ct Datei NG aate ch rig A nt oda 37 9 Updating Records 2509s e Ru ies es EET ER E UU AREA SA IRURE SERIES S NE DERE 38 9 1 Update Record Examples roca cimera RE Mp ED VEN ET PEC KAN 38 Update with SQL Statement e Inn 38 Update with Class Definition nnn 39 10 Deleting Records 0 4 eere ehh hh hh hh nnn n ng 40 10 1 Delete Record Examples tats imo du od deus ate atado an bor idi oa gabs 40 Delete With SQL Statement 177725255222222 hn 40 Delete With Class Definition ee ee ee ee ee eee eee hh 40 11 Transactions coses ia La sea ee Se a here asain ca taza tat Ds a AA BG ne ER e e cose 42 iv Table of Contents 11 1 Transaction Examples 42439 o ptor ei PAAANO E E eap tuia dd 42 Transactions with SQL Statements e III IM 42 Transactions With Class Definitions ee 43 12 Creating Altering and Dropping Tables 45 12 1 Ca Table Examples shiv sitom a tereni a EE EPI NATI PAA AINA 45 Create Table With SQL Statements 1 eee thit IIIA I IIIA 45 Create Table With lass Definition outetean ae deed b eiu KN Dusche tr aut as 46 22 Alter Table Ep dua PP PAPA du STS ES Sois Bea dta V Gud 46 Alter Table With SQL Statements III II 46 Alter Table With SQL Statements FEDE EDEN IIIA 47 108 Drop Table aot tzesqetai uc Re ENTE Xa A swath KEN DADI I RIN TA GA Rok a KANAN Owes 48 13 Upgrading Databases
7. not change at runtime there is no harm overwriting the working database Dynamic databases should NOT have the Overwrite If Exists property on since this will wipe out any changes made during runtime with the database from the project Manager Settings 23 See Workflow for an explanation of how SimpleSQL uses databases If you need to make changes to a database s structure in the working directory but you don t want to overwrite the database and wipe out any changes in data then you should follow an upgrade path explained in Upgrading Databases Debug Trace This setting is only used in the editor and has no effect on your runtime code within your target device Setting this value will allow you to see the SQL statements that pass through the manager This can help you debug your SQL by showing the statements in the debug console Figure 5 14 Debug Trace 6 Data Structure SimpleSQL allows you to optionally choose to use NET data structures such as DataTable DataRow and DataView or use a lighter weight class based ORM Object Relational Mapping Some advantages of using the ORM method e Much smaller memory footprint The System Data dll that is required for the standard NET data structures takes up a full megabyte of storage In addition to this you also have to use the full NET Unity library for this to work properly further bloating your application e Strongly typed field casting that is set up onc
8. primary key set Then call Delete with the object casting to the appropriate class structure Your class must have a primary key attribute for this to work 11 Transactions A transcation is a collection of database modification commands that will be run all at once Transactions vastly improve performance of Insert Update and Delete functions on a database If you are calling many commands all in a row you will see an improvement in performance by using a transaction To start a transaction simply call before your statements you want to batch dbManager BeginTransaction When you are done with your statements you can then commit the transaction to the database making it run all the commands dbManager Commit 11 1 Transaction Examples Transactions with SQL Statements string sql INSERT Points VALUES dbManager dbManager dbManager dbManager dbManager Qe IA NTO PlayerStats PAWA BeginTransaction Execute sql PI Execute sql PI Execute sql wie Commit ayer 1 ayer 2 ayer 3 2 11 0 Transaction Examples 43 PlayerName TotalKills 100 2 LIGA 2 0 You can see in this example why setting up your SQL statement with parameter bindings using the can come in handy You only have to specify the SQL statement once and then bind it multiple times a started a Transaction Note that your com
9. use the NET System Data library which includes data structures such as DataTable DataRow and DataView then you ll need to select Use System Data If you do not need these structures and prefer to use the ORM classes then you ll want to select No System Data If you switch from using System Data to not using it then you will be warned that you should remove all references to System Data and SimpleSQLManager_WithSystemData from your scripts and gameobjects Failure to do so may cause errors or Unity to crash Figure 14 6 Confirm System Data Removal Confirm System Data Removal Be sure you remove all references to System Data and SimpleSQLManager_WithSystemData From your scripts and gameobjects before continuing If you do not Unity will likely crash or give errors when the libraries are removed Are you sure you want to continue Yes No If you switch from not using System Data to using it you will be notified that you ll need to set the full 56 Options and Optimization NET API in the player settings for the library to work properly at runtime Figure 14 7 Use Full NET Notice Please be sure to set your player setting s API Compatibility Level to NET 2 0 not subset For the System Data library to work properly at runtime Figure 14 8 Full NET API Optimization Api Compatibility Level Sh NET2 O0 iple platforms NET 2 0 Subset E For more information about using System D
10. will be running your application only on Mac OS or iOS then the first platform option is the best solution It strips out the sqlite3 library which already exists on these platforms making your final package much smaller If you will be running your application on Windows 32 bit Mac OS or iOS then the Universal option is the best solution It will include the sqlite3 libraries required by Windows but will also make your final package a little larger Q Note that you can also use the Universal package if you are running on Windows 64 and you select the VVindovvs option in the target platform in the Build Settings Figure 14 3 Windows 32 Be PC and Mac Standalone Target Platform Windows Development Build Autoconnect Profiler Windows 64 bit Script Debugging Mac OS X If you will be running your application on Windows 64 bit then you ll want to choose the last option It has a sglite3 library specially tailored to the 64 bit platform Note that this is only relevant if you choose the Windows 64 bit build option in the Build Settings Figure 14 4 Windows 64 Be PC and Mac Standalone Target Platform Windows 64 bit Development Build Windows Autoconnect Profiler Script Debugging Mac O5 X Optimize Data Library 55 14 2 Optimize Data Library Figure 14 5 Optimize Data Library Optimize Data Libraries X No System Data Use System Data Smaller Pacakge Larger Package If you want to
11. 0 BR NS NU Homer lanse we varchar 100 M DN Wineiace FLOAT U g Armor FLOAT AURET EPON TN FOR Up dbManager Execute sql 46 Creating Altering and Dropping Tables sql CREATE INDEX StarShip_StarShipName on X Sese Simo Nw NU Siemens We dbManager Execute sql Create Table With Class Definition This example shows how powerful SimpleSQL is when creating a table from a predefined class definition See the Data Structure Chapter to see the StarShip class definition dbManager CreateTable lt StarShip gt 12 2 Alter Table Examples Alter Table With SQL Statements This example shows how to add a column to a table gerine SaL sql ALTER TABLE LocationMapping ADD COLUMN NewField INTEGER dbManager Execute sql Alter Table Examples 47 Alter Table With SQL Statements 2 Though not technically an alter statement this example shows how to drop a column from a table This example removes a column by first renaming a table to a temporary location then creating a new table with the original name then copying the data from the temp table to the new table and finally removing the temp table All this is necessary because you cannot simply drop a column This method can also be used to change column names ordering or types as well Note that we use a transaction here to group all the commands into a single call for efficiency and perfor
12. TABLE StarShip dbManager Execute sql 13 Upgrading Databases 13 4 Database Workflow SimpleSQL uses the database located in your application s working directory at runtime This directory is not the same as your project directory where you set up the link to the database This allows SimpleSQL to make modifications to the database It also allows you to create multiple working databases from a single project database Using an Object Oriented Programming analogy you can think of the project database as the template or class and the working databases as the object or instantiated class If your database s data will change during runtime then your database is said to have dynamic data If you are only using the database for settings and values that will not change during runtime then your database is said to have static data You may also have some data that is dynamic and some that is static If you are using only static data then you can safely check the Overwrite if Exists property on the SimpleSQLManager This will completely wipe out the database that exists in your device s working directory and replace it with the database in your project Since nothing changes in your working directory wiping it out will have no consequence If you are using dynamic only data or a mixture of static and dynamic data then you DO NOT want to check the Overwrite if Exists property on the SimpleSQL Manager Overwriting the data would w
13. ata see Using NET s System Data and System Data Query Examples Q You can still use the ORM classes alongside the System Data structures 15 FAQ and Troubleshooting 15 1 Questions Errors e Q Why are the changes I ve made to the database in my project not showing up when I run my application e Q Why can t I drag my database to the manager e Q Error Failed to open database at the working path e Q Error If you want to change the database s working name then you will need to supply a new working name in the SimpleSQLManager e Q Error SQLiteException no such table 15 2 Answers Resolutions 58 FAQ and Troubleshooting Q Why are the changes l ve made to the database in my project not showing up when I run my application A SimpleSQL uses the database in your application s working directory when running the application not the database in your project SimpleSQL copies the database from your project to your application s working directory if one does not already exist If a working database already exists then SimpleSQL will only overwrite the working database if you tell it to in the SimpleSQLManager object in your scene Figure 15 1 Overwrite If Exists c See Overwrite if Exists for more information Use extreme caution when overwriting a database since this will wipe out any changes made while your application was running E If you need to make changes to a working database but don t want to ov
14. atabase s working name then you will need to supply a new working name in the SimpleSQLManager A This error is caused by checking the option to change the working database name but not providing a new name Be sure you either uncheck the name change or provide a new name for the database in the SimpleSQL Manager Es See Change Working Name for more information Answers Resolutions 61 Q Error SQLiteException no such table A Be sure the working database has a table with the one you are trying to access Also be sure you are referencing a valid database file 16 Revisions 2012 07 16 1 Added System Data information to e Create a SimpleSQLManager with System Data e Using NET s System Data e System Data Query Examples e Optimize Data Library Added Options and Optimization
15. atabase through third party tools or through code programmatically at runtime If you are setting up a database structure or data that will persist it is probably better to set up your changes in a third party tool If your database structure will change dynamically then you may want to make your changes in code pa If your database s data will be updated then you will need to make any structure changes in code through an upgrade path Please see Upgrading Databases for more information 5 Creating a Database Manager Once you have your database files ready to go you are now ready to use the data in a scene Fach database that you will reference in a scene will need a SimpleSQLManager script to interact with it You can attach this script to any object but it is usually cleaner to have this on its own GameObject Q You can have multiple managers in a single scene each accessing a database You may want to divide out your static data into a separate database from your dynamic data for instance This way you can overwrite your static database with any new settings or changes without worrying about overwriting dynamic data 5 1 Creating a SimpleSQLManager E This will show you how to set up a SimpleSQL Manager that will use only the ORM data class structure If you are interested in including System Data structures see Create a SimpleSQLManager with System Data There are a few ways to create a SimpleSQL Manager in a scene
16. base using SimpleSQL 1 Create from the unity menu 2 Create from the project menu Create an Empty Database using SimpleSQL in Unity 9 3 Create from the asset menu Create from the Unity Menu To create a database from the Unity menu select SimpleSQL Create Empty Database Figure 4 1 Create Database From Unity Menu errain SimplesQL Window Help Local Create SimpleSQL Database Manager This will add a new empty database file to the currently selected folder or the folder of the currently selected object SimpleSQL automatically assigns the bytes extension to your new file which is not visible from within Unity but can be seen in your OS s folder browser Figure 4 2 New Empty Database 4 06 CreateAndDropTable v CI DataBases Fantasy E SciFi w EA Cerinte Figure 4 3 Bytes Files Mame Size Type Fantasy bytes 192KB BYTES File SciFi bytes 96 KB BYTES File New Database bytes OKB BYTES File 10 Creating a Database Create From the Project Menu You can also create a new database from the Project window menu by clicking on the Create dropdown and going to SimpleSQL Empty Database Figure 4 4 Create Database From Project Menu 3 Project Folder Javascript C Script Boo Script Shader Prefab Material Animation Cubemap Lens Flare Custom Font Physic Material GUI Skin SimpleSQL ce DeleteCommand Create From the Asset Menu You can also create a new
17. cess the table by iterating over the rows filter using a DataView or anything else the System Data library allows public SimpleSQLManager WithSystem public GUIText text VORES ERE f int row 0 ESE EE Ws WeaponTypeDescription Data manager W W W W wi W Wop FROM neos V H DataTable dt manager Query SELECT WeaponID WeaponName Weight WeaponTypel Weapon W KION Domage r Y a Dy Description AS W IN WeaponType T ON W WeaponTypeID System Data Query Examples 35 T WeaponTypeID FORDER DY y W WeaponID foreach DataRow dr in dt Rows text text Row rowslostrung ms LOr int e 02 e lt cdt Columis Counte HE text text dt Columns c ColumnName cee toStrimg 4 W Wp CORE CORE t UNUS O Walay D One advantage to querying your data using this method is that you do not need to know the fields or their types beforehand The DataTable stores each cell generically as an object type which will need to be cast when using the data later 8 Inserting Records To get data into your database you use insert commands 8 1 Insert Record Examples Insert with SQL Statement You can insert into a database using SQL statements You can also bind parameters using the value in
18. e You don t need to constantly cast the data you retrieve from your database to the proper types cutting down on the risk of typos and bugs e Simple calls to update and modify your database with little need for SQL syntax Queries and complex modifications can still be called with SQL statements if you prefer Q SimpleSQL makes use of the Generic library to be able to store your arrays without having to cast them each time they are referenced Be sure your scripts have a reference to the System Collections Generic library Using NET s System Data 25 6 1 Using NET s System Data If you decide you want to use NET s System Data library then you can set this in the Options by going to the Unity menu SimpleSQL Options Figure 6 1 Options ols Simple5QL Window Help i Create SimpleSQL Database Manager Create Empty Database From there you can choose the Data Library Optimization setting If you wish to use System Data you ll need to select the appropriate option Figure 6 2 Use System Data Optimize Data Libraries Ho System Data Use System Data Smaller Pacakge Larger Package When you choose to use the System Data library you will be alerted to the fact that you will need to set your project s player setting s API Compatibility Level to the full NET library not just the subset 26 Data Structure Figure 6 3 Use Full NET Notice Please be sure to set your player setting s API Compatibil
19. erwrite any changes see the upgrade path in Upgrading Databases Answers Resolutions 59 Q Why can t drag my database to the manager Figure 15 2 Bad Extension El Fantasy bytes 192 KB BYTES File SciFi bytes 96KB BYTES File 192 KB SQLITE File Figure 15 3 Can t Drag Non Bytes Files A Be sure your project database has the extension bytes Unity requires this extension when looking for text assets that it can stream to a working directory Figure 15 4 Bytes Files s Fantasy bytes 192KB BYTESFile SciFi bytes 96KB BYTESFile New Database bytes OKB BYTES File 60 FAQ and Troubleshooting Es You can change the name of your database in the working directory in the SimpleSQL Manager in your scene See Change Working Name for more details Q Error Failed to open database at the working path A This error is caused if the database is being used by another program Be sure you do not have a third party application using the working database not the project database while running your project It can also be caused by a crash in Unity which would leave the database in an unclosed state Restarting Unity should resolve the issue This error can also be caused if there is no database set in the SimpleSQL Manager Be sure you set a valid database before running your application pa See Database File for more information on how to assign a database to your manager Q Error If you want to change the d
20. eui Gd doge dai 19 Database Filer cia Ree ea KATE ANA a wt teen un ar Rec e ore OR re a tes ga TA 19 Drag Database Asset to the Inspector eee In 20 Select the Database from the Object Browser s nn 20 Change Working Name eed exem eu eod as ae KPA ANC teh am og dc dated d a a ed hte d or iode 21 Overwte dt ExiMs Nama he cel nee are Ne bU P I sera Pe DE LEN ete eed 22 Debus Tracer stone ide HANGA dap eda a tod andi rade OS ANG NER al 23 6 Data Structure 556 850 coets en CER II KO PLAN UE ERE Se a DE UR RIVA GR EE aa a 24 6 1 Using NET s System Data en 25 6 27 Usins the ORM equi teta t nec ve p exams o duce Tt RARO RUP Vote e Yao e Bai oat i dali 26 Sample Classes sehe hh hehehe hh eh rh rhe e ee 27 AA Rr A HE 28 7 Queries 31 TA Query Examples tt ar a ee AS Pa A nn Sema AO AA 31 Retrieve All Data from a Table waaahh veo P At ERC Vlad 31 etc the Res lts LS AK EE XE PA he 32 Retrieve Data from a Table Join scene de e ve uae vr xac Exo Va st Od e ed ul RR RR VR 32 Using Ling to Retrieve a Table s Data 2 eee eee 33 7 2 System Data Query Examples a7 nei dust kka AY KAPA Destaca TANANG Bd rea V frd 34 Retrieve data from a table and store in a DataTable e ee eee eee 34 8 Inserting Records 0 cere ehh hh hh hh hh hn n n ng 36 8 1 Insert Record Examples anuna Kab pA ERI decla dbi M ote ut Peral 36 Insert with SQL Statement 1775225225222
21. g sql UPDATE PlayerStata V s SET PlayerName 7 T WTOLALRKLLLS Zy W a VIGE W sg WHERE Playvyeri D 2 dbManager Execute sql Updated Player Name 55 120321 2 Q Note that you can use parameter binding with the in your SQL statement to simplify and reuse your query Update Record Examples 39 Update with Class Definition PlayerStats playerStats new PlayerStats PlayerID 2 PlayerName Updated Player Name TotalKills 55 Points 120321 dbManager UpdateTable playerStats To call the UpdateTable function in your manager you set all the values of your class instance including the key field The UpdateTable function will use the key field to look up the record For this to Work you must specify the primary key in your class definition 10 Deleting Records To remove records from your database you will call delete commands 10 1 Delete Record Examples Delete With SQL Statement string sql DELETE FROM PlayerStats WHERE PlayerID dbManager Execute sql 2 Q Note that you can use parameter binding with the to simplify and reuse your SQL statements Delete With Class Definition Delete Record Examples 41 PlayerStats playerStats new PlayerStats PlayerID 2 dbManager Delete PlayerStats playerStats To delete with the class definition you set up your instantiated class object with the
22. h to have a different name for your working database other than the project database s name This can be useful if you are using the same project database template to create multiple working directories or if you just prefer your database to have a different extension perhaps You can change the working database s name by checking the Change Working Name toggle and filling in the name field Figure 5 12 Change Extension Simple SQLManager Script qm Database File lOverwrite If Exists Q If you don t toggle the Change Working Name property on then your database will be copied over with the same name as the project database with the bytes extension 22 Creating a Database Manager If you do toggle the Change Working Name property on then you must supply a name or you will get an error Overwrite if Exists SimpleSQL will first copy your database from the project to your application s working directory if it does not already exist All interaction with the database is done from the working directory The project s database will remain untouched If you want to copy the database as it is in your project to your application s working directory then you will want to toggle this on Figure 5 13 Overwrite If Exists You will be warned that checking this property on will overwrite the database in your working directory Q Static database are good for having the Overwrite If Exists property on Since they do
23. in a database Its primary advantage over other plugins is that you don t have to concern yourself with the specialized dll s necessary for connecting to a database Also it simplifies publishing to mobile devices by doing all the work of moving data to your application s working directory for you Some examples of how you can use a database in a game or project include but are not limited to e Keep track of player stats e Store inventory information such as items weight cost damage armor etc e Keep track of progress and visited areas in a game e Store maps of your world with interlinking connections e Store dialog for an RPG with quick lookups based on person and place And many more SimpleSQL uses SQLite as the database format for its data storage You can easily create and modify databases programmatically or with third party tools A few examples of free tools that you can use include e SQLite Manager addon for Firefox requires Firefox browser also free https addons mozilla org en US firefox addon sqlite manager e SQLite Administrator http sqliteadmin orbmu2k de e SQLiteman http sqliteman com page 4 html These tools are not supported by echo17 so you should check with the above providers if you have questions regarding their products Training on these products is outside of the scope of echo17 and this 2 Overview documentation 2 Workflow SimpleSQL only works with databases in yo
24. ipe out any changes the user makes at runtime and would not be desireable Upgrade Path 51 So how do you update a dynamic database if you can t overwrite it 13 2 Upgrade Path The most common method for keeping a working database in sync with your project database is to keep track of your database version not the same as your project version You can then upgrade your tables and table structures based on a set of steps between the working database s current version and the project database s version This is known as the upgrade path Typically you will store the database version inside each of your databases so that you know what upgrade path to take for each For example Let s say your project database is at version 3 0 You may have three working databases in your application s working directory that are at versions 1 0 2 0 and 3 0 all created from this project database In order to get all your databases in sync with your project database you will need to create an upgrade path for each version of your database At the beginning of your application s life cycle you would call the upgrade path on each of your databases Something like this psuedocode if dbVersion 1 0 add tables change table structure or modify data to get to version 2 0 dbversiion 2 07 update dbVersion in database to 2 0 52 Upgrading Databases if dbVersion 2 0 add tables change table structure or modif
25. ity Level to NET 2 0 not subset For the System Data library to work properly at runtime Using System Data requires the full NET library in your player settings This will increase your final package size Q You can still use the ORM classes alongside the System Data structures 6 2 Using the ORM If you decide to use the ORM then you can turn off the System Data library to streamline your application Note that you can leave the System Data library in your application without harm it will just make the final package larger Figure 6 4 No System Data Optimize Data Libraries Aa PL d e No System Data Use System Data Smaller Pacakge Larger Package Using the ORM 27 You will be asked to confirm removing the System Data library and warned that doing so may cause errors if you have scripts or gameobjects in your scene referencing this library Be sure you remove all these references before turning off System Data to avoid errors Figure 6 5 Confirm System Data Removal Confirm System Data Removal Be sure you remove all references to System Data and SimpleSQLManager_WithSystemData from your scripts and gameobjects before continuing IF you do not Unity will likely crash or give errors when the libraries are removed Are you sure you want to continue Yes No Sample Classes Below is an example of a simple class used to store weapon information in your game This structure is designed a
26. lets you change the file s working name from the project s name to whatever you wish e Overwrite if Exists This will overwrite the database stored in your application s working directory with the database in your project Use this with extreme caution as it can wipe out any changes made by your application e Debug Trace This will output the SQL statements used by the manager to the debug console window for better debugging in the editor Database File To assign a database file to the manager you can do one of the following 1 Drag the database asset to the the database file field of the inspector 2 Click on the circle to the right of the database file field of the inspector and browse for your database C You can store your databases in whatever folder structure that you choose as long as the file has the extension bytes 20 Creating a Database Manager Drag Database Asset to the Inspector Figure 5 9 Database File m 3 1 CAN LAN LAY AN AN LA i9 KA Ki KA ke l Select the Database from the Object Browser Figure 5 Select Database File Browser Manager Settings 21 Figure 5 11 Select Database from Browser Select TextAsset x amp Assets ComplexQuery CreateAndDropTable DeleteCommand Fantasy InsertCommand c Location PlayerStats impleQuery SimpleSQLManager SimpleSQLManagerInspector El StarShip Change Working Name You may wis
27. mance serine SaL start a transaction to speed up processing dbManager BeginTransaction rename our table to a backup name sql ALTER TABLE StarShip RENAME TO Temp_StarShip dbManager Execute sql create a new table with our desired structure leaving out the dropped column s SEE EREA IEA BERAN US EAr SMPN W sx ANS Cang SIA DA T inceger PRIMARY KES NOT NULL 2 US x INIS RS ENE ME NUR Ca CA CASC OD INO EN W 4 UN Visions lanse EVE varenr 100 DEFAULT Eart 2 T U WReince Y cloar NOT NULG p W s Nuno cloar DEFAULT 120 p V 4 48 Creating Altering and Dropping Tables U Wins wepowerr Float Vr dbManager Execute sql copy the data from the backup table to our new table eg VTNSERTI INIO W9SwessSbipW Ww sx SAC A wee 4 O WSEEESI OO IID W sg US tora SEO NE MERS Nena plang ima WU F UR gen ue W ES N WAT MOTAN mm IU REEIZOI QUE VW WD ROM NTremo Suecucsimajo V WE dbManager Execute sql JU drop the backup table sql DROP TABLE Temp_StarShip dbManager Execute sql commit the transaction and run all the commands dbManager Commit 12 3 Drop Table To drop a table and or index programmatically you call the SQL statement like this Drop Table 49 string Souls sql DROP INDEX StarShip_StarShipName dbManager Execute sql sql DROP
28. mands will not process until you call the Commit function if you have Transactions With Class Definitions In this example we first set up a list of PlayerStats We will pass this list to the manager using the InsertAll function which starts and commits a transaction for us We populate the list with instantiated objects of our PlayerStats class PlayerStats playerStats List lt PlayerStats gt playerStatsCollection new List lt PlayerStats gt 44 Transactions playerStats new PlayerStats PlayerName 2 Points 100 playerStatsCollection Add playerStats playerStats new PlayerStats PlayerName ME SPON ar playerStatsCollection Add playerStats playerStats new PlayerStats PlayerName 0 Points U playerStatsCollection Add playerStats dbManager InsertAll playerStatsCollection player 1 lat DN ola Su JE c d a dod s Moncada db db oneal do d 12 Creating Altering and Dropping Tables You can create and drop tables programmatically at runtime 12 1 Create Table Examples Create Table With SQL Statements This example shows how to create a table and an index for the table You can set the attributes of each field such as primary key not null etc using the SQL statement Se SES CIA sgl VCEREATE TABLE LES te IS S ao ds N StarShipIDN INTEGER PRIMARY KEY NOT NULL WN Site ara SEN ME NERE bU 51
29. ough the Unity Menu You can attach the script through the Unity menu by 1 Select the GameObject you want the script to be attached to 2 Inthe Unity menu go to Component Scripts Simple SQLManager Figure 5 6 Select GameObject Hierarchy Create GameObject Main Camera Figure 5 7 Attach SimpleSQLManager Script Through Menu ct Component Terrain SimpleSQL Window Help Mesh gt Effects gt le Physics gt May Mavigation gt Audio gt gt gt r Rendering Miscellaneous Scripts Complex Query Create And Drop Table Delete Command Insert Command Simple Query Update Command 5 2 Create a SimpleSQLManager with System Data 18 Creating a Database Manager E If you want to use System Data with your SimpleSQL Manager then you ll need to set up your project to accommodate See Optimize Data Library on how to set your project to use System Data Q You can still use the ORM classes alongside the System Data structures Once you have your project set to use the System Data library you can drag the SimpleSQLManager_WithSystemData script from the Plugins folder to your gameobject Figure 5 8 SimpleSQLManager WithSystemData Manager Settings 19 5 3 Manager Settings Once you have your manager set up in a GameObject you can specify its settings e Database File This is the SQLite file that will be used by the manager e Change Working Name Using this value
30. pleSQL package demo and all to your new project Once you have the files you can copy the SimpleSQL Editor dll file to your Editor folder in your game project and the SimpleSQL Runtime dll to your Plugins folder in your game project Figure 3 5 Editor Plugin Folders Name Size Type O Asset5toreTools File Folder Demos File Folder Documentation File Folder File Folder File Folder readme txt 1KB Text Document Your final game project layout should have the two dll s in their respective directories SimpleSQL Editor dll in the Editor directory and SimpleSQL Runtime dll in the Plugins directory Figure 3 6 Dlls 7 4 Creating a Database Before you can use SimpleSQL in Unity you will first need to create a database There are a couple of ways to do this 1 Create an empty database in Unity using SimpleSQL then add tables and data programmatically at runtime or modify the database with a third party tool 2 Create a database in a third party tool such as the SQLite manager plugin for Firefox Databases for SimpleSQL have to have the extension bytes You can tell SimpleSQL to change the file name after it has copied the file to your application s working directory but Unity requires all non standard assets such as a SQLite database that will be streamed to have this extension within your project 4 1 Create an Empty Database using SimpleSQL in Unity There are a few ways to create an empty data
31. round the results of a query so it actually has more fields than the Weapon table in the database You can find this class along with the corresponding database in the demo project included with SimpleSQL using SimpleSQL public class Weapon The WeaponID field is set as the primary key in the SQLite database so we reflect that here with the PrimaryKey attribute PrimaryKey 28 Data Structure joule public pub kre publtre publ publ LE ic pub Fre int WeaponID 4 get eet string WeaponName gert Serp I float Damage get set milo Cesc Ger Ses float weight get set int WeaponTypeID gery Sert J string WeaponTypel Description get set The actual Weapon table in the database uses all these fields except for the WeaponTypeDescription field which is pulled in by using a JOIN in a query Attributes You can specify attributes for your fields that will allow you to communicate traits to your database In the above example the WeaponID field has a PrimaryKey attribute added that signifies that this field is a unique key field in the data table Other attributes that you can add to your fields include e PrimaryKey Table s unique key field e Indexed Table is indexed based on this field e NotNull This field cannot be null e Default value If no value is provided when inserting then the default value se
32. s 33 W WeaponName T Description AS WeaponTypeDescription FROM Weapon W JOIN WeaponType T ON W WeaponTypeID T WeaponTypeID List lt Weapon gt weapons dbManager Query lt Weapon gt sql If you look at the Weapon class where we are storing the results of the query you ll notice that it has a field for the WeaponTypeDescription even though the Weapon table does not carry this field We put this field in the class so that we can store the joined value You do not need to fill every field of a class In the above example only three of the Weapon class fields are being populated with data Using Linq to Retrieve a Table s Data You can quickly retrieve all the data in a table using Ling without writing any SQL syntax Be sure you have a reference to the System Ling library in your scripts if you choose to use this method List lt Weapon gt weapons new List lt Weapon gt from w dbManager Table lt Weapon gt select w You can also easily filter down the results by using a where clause with Linq List lt Weapon gt weapons new List lt Weapon gt from w in Table lt Weapon gt where w WeaponName Sword Select w PilesecOrDetaulc P 34 Queries 7 2 System Data Query Examples Retrieve data from a table and store in a DataTable This example shows how you can retrieve data from a SQLite table and store it in a DataTable You can then pro
33. st of these samples can be found in the demo that comes with the SimpleSQL plugin so only the relevant information will be highlighted Please refer to these demos for complete implementations Retrieve All Data from a Table This sample shows how easy it is to retrieve data from the Weapon table of your database and store the results in a generic list of your Weapon class public SimpleSQL SimpleSQLManager dbManager YO Site cate f 32 Queries string sql SELECT FROM Weapon List lt Weapon gt weapons dbManager Query Weapon sal Note that somewhere in your script you will need to set a public reference to the SimpleSQL Manager object in your scene that controls the database you are wishing to query From now on we will leave out the lines that show the reference to the manager object since it is implied that one is needed Iterating the Results Once you have your data stored you can access it by looping through the list of results foreach Weapon weapon in weapons Debug Log weapon WeaponName weapon Damage Notice the simplicity here as compared to accessing the data in a NET DataTable where you would need to cast each field to its appropriate type before using Predefining a class once with types simplifies the amount of work required for every task afterward Retrieve Data from a Table Join gerine sgl US EINES ES W WeaponIiD Query Example
34. t here will be used e MaxLength value The maximum length of a string field Here is an example data structure class that uses multiple attributes on several fields Using the ORM 29 using SimpleSQL pulalie Class Sus eas DE Eq is the prineary kay waleh autonacically gerts Tha NotNull attribute PrimaryKey public int Starship ID 4 cece secs The starship name will have an index created in the database It s max length is set to 60 characters Venc mame cannot loe mum Ca Indexed MaxLength 60 NotNull public string StarShipName get set The home planet name s maximum length is set to 100 characters The default value is set to Earth MaxLength 100 Default Earth public string HomePlanet get set The range cannot be null NotNull public float Range get set Was armors Jarle value is seu to 120 Deck 120 01 public float Armor get set Firepower has no restrictions public float Firepower get set 30 Data Structure In the next chapter you will see how these classes can be used in quick and simple retrieval of data from your database 7 Queries The most common task of a database is to quickly retrieve data SimpleSQL passes a SQL statement to the database and returns a list of data stored in the format of your custom classes that you set up 7 1 Query Examples Mo
35. ur application s working directory so the databases in your project will never be updated at runtime You can use this workflow to your advantage by making your project database a template that can create one or many working databases If you do not have a working database in existence then SimpleSQL will copy the project database to your working directory If you do have a working database in existence then SimpleSQL will only copy the project database over to the working directory if you tell it to If you are using data statically not making changes at runtime then you can overwrite your working database s without any consequences This keeps your working databases in sync with what you have set up in your project c If you are using data dynamically making changes at runtime then you DO NOT want to overwrite your working database since this would wipe out any changes since the last time the application was run If you need to make changes to your working database s structure or static data then you will need to follow an upgrade path as outlined in Upgrading Databases O You may want to divide your data into multiple databases For instance you could keep all your static data such as room layout maps unit properties etc in one database and all your dynamic data such as player stats and achievements in another database This allows you to overwrite the static database without worrying about wiping out dynamic data
36. v1 1 0 SimpleSQL SQLite manager for Unity3D echo17 com Table of Contents Table of Contents 946996 a GPS Y exe e x E Rx OR E REG EE ES Se suu ii 1 OvVervleW y e Rr hme Each rh m Rocca RU TAG R9 er ra 71974 9 979 78 8 9 8 era alto ada 1 2 Workflow e RG TERENA TEA E EVE E S E V S S E e n Vie 3 3 Setting Up Your Project 0 4 4 Creating a Database 8 4 1 Create an Empty Database using SimpleSQL in Unity 27222222222 I 8 Create from the Unity Menu 1122222 I nnn 9 Create From the Project Menu ea rer i La o Ka 10 Create From the Asset Menu RII e 10 4 2 Create a Database with a Third Party Tool reitera aba ni e ei eee rnnene 11 4 3 Making Changes to Your Database cro pike ER EDGE EC Ee ed 12 5 Creating a Database Manager 13 5 1 Creating a SimpleSQLManager sento aaa one pa tse desde dieat en 13 Create Through the Unity Menu 23g oed d pet tope bud Kanan ebat doe ed AN Uu Ud 14 Create Through the Hierarchy Menu eee PR TANAN NA CR MO p eine neues 15 Attach to an Existing GameObject dota vestes aa NANA KANAN DEL De s ata a dA ds 15 Dragging the Script to the GameObject s ee eee ee eee 16 Attaching the Script Through the Unity Menu see ee nnn 17 5 2 Create a SimpleSQLManager with System Data e 17 SA MAISSRSSIIDES maa isset od E NATA Eau a pectet A E
37. y data to get to version 3 0 dbVersion 3 0 update dbVersion in database to 3 0 Your database at version 1 0 will enter the first logic block upgrading to 2 0 It will then enter the second logic block since it is now at 2 0 and upgrade to 3 0 Your database at version 2 0 will skip the first logic block and enter the second upgrading to 3 0 Your database at version 3 0 won t enter any upgrade logic blocks since it is already up to date 13 3 Redundancy You may note that using an upgrade path can introduce redundancy For example let s say in the logic blocks above for upgrading from 1 0 to 2 0 you add a table called TableA Then in the logic block for upgrading 2 0 to 3 0 you delete the table TableA If your database is at version 1 0 it will add then immediately delete TableA which on the surface seems pointless but gives you complete control over a database s changes from any starting version to the final version 14 Options and Optimization To allow more flexibility and optimization SimpleSQL has the ability to use different DLLs for different platforms and data structure libraries To access the options window go to the Unity menu SimpleSQL Options Figure 14 1 Options ols SimpleSQL Window Help Create SimpleSQL Database Manager Create Empty Database 14 1 Optimize Platform Figure 14 2 Optimize Platform Optimize Platform niversa 54 Options and Optimization If you
Download Pdf Manuals
Related Search