SQLite 学习流水账笔记

 

 

1、SQLite随机取n行数据,可加自己的条件

SELECT * FROM TableName WHERE key ? ORDER BY RANDOM() LIMIT 0,Num;

 

2、sql语句中查询带通配符的数据，如下划线 星号等

http://www.cnblogs.com/macroxu-1982/archive/2012/10/27/2742127.html

SELECT * FROM LanguageMoudle_Word WHERE Word LiKE 'ab%'

 

3、拷贝 streamasset 目录下的资源 like db 到不同平台的持久路径下

           #region
            if ( GUI.Button(new Rect (170,10,150,70), "Run SQLite Test \nfor streamingAssets db\nwhich moved to \npersistentDataPath") ) 
            {
                
                db = new SQLiteDB();
                
                log = "";
                
                string dbfilename = "test.db";
    
                byte[] bytes = null;                
                
                
#if UNITY_EDITOR || UNITY_STANDALONE_WIN || UNITY_STANDALONE_OSX
                string dbpath = "file://" + Application.streamingAssetsPath + "/" + dbfilename; log += "asset path is: " + dbpath;
                WWW www = new WWW(dbpath);
                Download(www);
                bytes = www.bytes;
#elif UNITY_WEBPLAYER
                string dbpath = "StreamingAssets/" + dbfilename;                                log += "asset path is: " + dbpath;
                WWW www = new WWW(dbpath);
                Download(www);
                bytes = www.bytes;
#elif UNITY_IPHONE
                string dbpath = Application.dataPath + "/Raw/" + dbfilename;                    log += "asset path is: " + dbpath;                    
                try{    
                    using ( FileStream fs = new FileStream(dbpath, FileMode.Open, FileAccess.Read, FileShare.Read) ){
                        bytes = new byte[fs.Length];
                        fs.Read(bytes,0,(int)fs.Length);
                    }            
                } catch (Exception e){
                    log +=     "\nTest Fail with Exception " + e.ToString();
                    log +=     "\n";
                }
#elif UNITY_ANDROID
                string dbpath = Application.streamingAssetsPath + "/" + dbfilename;                log += "asset path is: " + dbpath;
                WWW www = new WWW(dbpath);
                Download(www);
                bytes = www.bytes;
#endif
                if ( bytes != null )
                {
                    try{    
                        
                        string filename = Application.persistentDataPath + "/demo_from_streamingAssets.db";

                        //
                        //
                        // copy database to real file into cache folder
                        using( FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write) )
                        {
                            fs.Write(bytes,0,bytes.Length);             log += "\nCopy database from streaminAssets to persistentDataPath: " + filename;
                        }
                        
                        //
                        // initialize database
                        //
                        db.Open(filename);                               log += "\nDatabase created! filename: " + filename;
                        
                        Test2(db, ref log);
                        
                    } catch (Exception e){
                        log +=     "\nTest Fail with Exception " + e.ToString();
                        log +=     "\n\n Did you copy test.db into StreamingAssets ?\n";
                    }
                }
            }
            #endregion