LfDBParse

出自金門農工維基館II
跳至導覽 跳至搜尋

湖濱散記II,2021升學專區,IT,UbuntuLinuxPos,山行,wiki語法練習區,文書技藝競賽,金門農工首頁,學生學習歷程系統, 維護記錄,lfDBParse,gtk9h.c 100px csv

lfDBParse

DB-STRU-1

(1)show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kmvs_wiki          |
| kmvswiki2DB        |
| mysql              |
| performance_schema |
| utf8_lf_db         |
+--------------------+

(2)use utf8_lf_db;
(3)show tables;
+-------------------------------+
| Tables_in_utf8_lf_db          |
+-------------------------------+
| lt_article_categories_link    |
| lt_articles                   |
| lt_articles_categories        |
| lt_articles_comments          |
| lt_articles_notifications     |
| lt_articles_text              |
| lt_bad_behavior               |
| lt_bayesian_filter_info       |
| lt_bayesian_tokens            |
| lt_blog_categories            |
| lt_blogs                      |
| lt_config                     |
| lt_custom_fields_definition   |
| lt_custom_fields_values       |
| lt_filtered_content           |
| lt_gallery_albums             |
| lt_gallery_resources          |
| lt_global_articles_categories |
| lt_host_blocking_rules        |
| lt_mylinks                    |
| lt_mylinks_categories         |
| lt_permissions                |
| lt_phpbb2_users               |
| lt_referers                   |
| lt_tmp_users_permissions      |
| lt_users                      |
| lt_users_permissions          |
+-------------------------------+
27 rows in set (0.000 sec)

DB-STRU-2


(4) desc lt_blogs;
+------------------+------------------+------+-----+---------------------+-------------------------------+
| Field            | Type             | Null | Key | Default             | Extra                         |
+------------------+------------------+------+-----+---------------------+-------------------------------+
| id               | int(10) unsigned | NO   | PRI | NULL                | auto_increment                |
| blog             | varchar(50)      | NO   |     | NULL                |                               |
| owner_id         | int(10) unsigned | NO   | MUL | 0                   |                               |
| blog_category_id | int(10) unsigned | NO   | MUL | 0                   |                               |
| about            | text             | YES  |     | NULL                |                               |
| settings         | text             | NO   |     | NULL                |                               |
| mangled_blog     | varchar(50)      | NO   | MUL | NULL                |                               |
| status           | int(4)           | NO   |     | 1                   |                               |
| show_in_summary  | int(4)           | NO   |     | 1                   |                               |
| create_date      | timestamp        | NO   | MUL | current_timestamp() | on update current_timestamp() |
| last_update_date | timestamp        | NO   |     | 0000-00-00 00:00:00 |                               |
| num_posts        | int(10)          | NO   |     | 0                   |                               |
| num_comments     | int(10)          | NO   |     | 0                   |                               |
| num_trackbacks   | int(10)          | NO   |     | 0                   |                               |
| custom_domain    | varchar(50)      | YES  | MUL | NULL                |                               |
+------------------+------------------+------+-----+---------------------+-------------------------------+
15 rows in set (0.001 sec)

(5) select id,blog,owner_id,num_posts from lt_blogs where id>=70;
+----+---------------------+----------+-----------+
| id | blog                | owner_id | num_posts |
+----+---------------------+----------+-----------+
| 70 | 樹心幽徑            |        1 |       259 |
| 71 | 2019商經一而上      |       72 |        29 |
+----+---------------------+----------+-----------+
2 rows in set (0.001 sec)

(6)
select id,blog,owner_id,num_posts,last_update_date from lt_blogs order by last_update_date desc;
+----+-----------------------------------------------------------------------------------------------+----------+-----------+---------------------+
| id | blog                                                                                          | owner_id | num_posts | last_update_date    |
+----+-----------------------------------------------------------------------------------------------+----------+-----------+---------------------+
| 70 | 樹心幽徑                                                                                      |        1 |       259 | 2020-10-06 04:07:32 |
| 71 | 2019商經一而上                                                                                |       72 |        29 | 2020-06-24 22:05:39 |
| 52 | 教務處kmvsc                                                                                   |       54 |         2 | 2018-12-04 01:07:23 |
| 12 | yiyung                                                                                        |       11 |         4 | 2018-01-10 21:46:51 |
| 28 | 金門農工人事室網誌                                                                            |       29 |         5 | 2017-11-22 03:53:05 |
| 30 | KMVS Health                                                                                   |       31 |        22 | 2016-12-12 04:13:30 |
|  2 | 金門農工設備組BLOG                                                                            |        1 |        74 | 2016-10-11 01:31:53 |
| 69 | 金門農工總務處BLOG                                                                            |       70 |         5 | 2015-11-04 03:51:36 |
| 35 | 科學應用軟體                                                                                  |       36 |         9 | 2015-08-30 00:02:34 |
| 31 | 金門農工進修學校                                                                              |       32 |         4 | 2014-05-22 06:49:33 |
| 29 | 金門農工研習心得分享                                                                          |       30 |         4 | 2013-12-02 20:22:28 |
| 38 | 金門農工實習處                                                                                |       39 |         2 | 2013-09-17 03:25:13 |
| 15 | 金門農工註冊組網誌                                                                            |       15 |        10 | 2013-09-12 04:54:52 |
| 39 | 金門農工機械科                                                                                |       40 |         2 | 2013-09-12 01:23:44 |
| 34 | 金門農工餐飲管理科                                                                            |       35 |         2 | 2013-09-11 05:09:50 |
|  3 | 金門農工汽車科                                                                                |        2 |         4 | 2013-09-11 05:05:19 |
| 33 | 金門農工養殖科                                                                                |       34 |         3 | 2013-09-11 05:02:21 |
| 25 | 金門農工教學組網誌                                                                            |       26 |         3 | 2013-09-11 02:01:00 |
| 42 | 金門農工電子科                                                                                |       43 |         4 | 2013-09-11 00:06:35 |
|  1 | 金門農工部落格                                                                                |        1 |        12 | 2013-09-06 07:04:42 |
| 40 | 金門農工園藝科                                                                                |       41 |         3 | 2013-08-31 00:31:35 |
| 41 | 金門農工資訊科                                                                                |       42 |         2 | 2013-08-23 03:26:03 |
|  9 | 金門農工商業經營科                                                                            |        8 |         3 | 2013-08-23 03:18:21 |
| 16 | kmvsLIB                                                                                       |       16 |         2 | 2013-05-20 22:45:52 |
| 68 | 金門農工綜職科網誌                                                                            |       69 |         1 | 2013-05-14 23:44:59 |
| 64 | tai                                                                                           |       65 |         2 | 2013-05-02 03:33:48 |
| 59 | k7475                                                                                         |       61 |         0 | 2013-04-30 05:15:52 |
| 45 | mei-haematococcus                                                                             |       46 |         1 | 2013-04-30 05:14:09 |
| 17 | 許績炎電機教室                                                                                |       17 |         2 | 2013-04-30 05:06:53 |
| 66 | teacher                                                                                       |       67 |        11 | 2013-04-30 05:04:58 |
| 48 | kmvsca                                                                                        |       49 |         1 | 2013-04-30 05:03:17 |
| 61 | chijung                                                                                       |       63 |         1 | 2013-04-30 05:02:51 |
| 60 | shiowcheau                                                                                    |       62 |         2 | 2013-04-30 05:02:48 |
| 58 | apple                                                                                         |       60 |         2 | 2013-04-30 05:00:39 |
| 51 | urea5231                                                                                      |       53 |         2 | 2013-04-30 04:54:46 |
| 65 | treehrt                                                                                       |       66 |         2 | 2013-04-30 03:34:54 |
| 67 | chching                                                                                       |       68 |         1 | 2013-04-30 03:20:14 |
| 63 | clean                                                                                         |       28 |         1 | 2013-04-30 00:10:15 |
| 62 | yang4684                                                                                      |       64 |         1 | 2013-04-30 00:08:25 |
| 57 | s7522076                                                                                      |       59 |         1 | 2013-04-29 23:46:55 |
| 56 | pupples0718                                                                                   |       58 |         1 | 2013-04-29 23:45:32 |
| 55 | yiyue12                                                                                       |       57 |         1 | 2013-04-29 23:44:11 |
| 54 | chunghsuan                                                                                    |       56 |         1 | 2013-04-29 23:42:53 |
| 53 | ching518                                                                                      |       55 |         1 | 2013-04-29 23:41:52 |
| 49 | kmvscb                                                                                        |       50 |         1 | 2013-04-29 22:09:30 |
| 47 | peter0846                                                                                     |       48 |         1 | 2013-04-29 22:06:27 |
| 46 | babarahorng                                                                                   |       47 |         1 | 2013-04-29 22:04:17 |
| 44 | 金門農工家政科                                                                                |       45 |         1 | 2013-04-29 19:49:52 |
| 43 | 金門農工電機科                                                                                |       44 |         1 | 2013-04-29 19:42:06 |
|  5 | 金門農工優質化BLOG子計畫2:實力精進發展計畫                                                    |        4 |        17 | 2013-03-27 04:07:24 |
| 37 | 「唐詩翻英」擂台                                                                              |       38 |         2 | 2013-03-12 03:20:58 |
| 36 | 金門農工漁業科                                                                                |       37 |         1 | 2013-03-12 02:45:24 |
| 32 | 金門農工教官室                                                                                |       33 |         2 | 2013-03-07 01:52:31 |
| 27 | 金門農工衛保組網誌                                                                            |       28 |         3 | 2012-12-13 10:49:47 |
| 26 | 金門農工實研組網誌                                                                            |       27 |         1 | 2012-11-20 01:34:16 |
| 10 | 金門農工體運組BLOG                                                                            |        9 |         2 | 2012-11-01 23:37:54 |
| 21 | 金門野鳥                                                                                      |       24 |         0 | 2012-10-05 00:46:28 |
| 24 | AutoCAD2010系列基本操作教程                                                                   |       25 |         2 | 2012-10-04 05:15:26 |
| 23 | 金門農工課外活動及競賽公告                                                                    |       25 |         1 | 2012-10-04 05:13:28 |
| 22 | 金門農工獎學金辦法公告區                                                                      |       25 |         1 | 2012-10-04 05:12:11 |
| 11 | LuyfBLOG                                                                                      |       10 |         2 | 2012-10-03 20:06:14 |
| 18 | 謝銳電機教室                                                                                  |       18 |         0 | 2012-10-03 10:58:33 |
| 19 | 文佑汽車教室                                                                                  |       19 |         3 | 2012-10-03 01:05:03 |
| 13 | 盧老師的物理教室                                                                              |       12 |         2 | 2012-10-03 01:03:00 |
| 20 | 交椅哥哥blog                                                                                  |       22 |         1 | 2012-10-02 21:49:00 |
| 14 | 金門農工特教組BLOG                                                                            |       13 |         3 | 2012-10-02 00:16:55 |
|  8 | 金門農工優質化BLOG子計畫5:友善環境快樂學習                                                    |        7 |         2 | 2012-09-24 00:54:24 |
|  7 | 金門農工優質化BLOG子計畫4:魅力教師溫馨校園                                                    |        6 |         1 | 2012-09-23 22:00:09 |
|  6 | 金門農工優質化BLOG子計畫3:親近海洋深耕家鄉                                                    |        5 |         1 | 2012-09-23 21:58:34 |
|  4 | 金門農工優質化BLOG子計畫1:職涯試探適性學習-推動學生就近入學計畫                              |        3 |         2 | 2012-09-23 20:15:35 |
+----+-----------------------------------------------------------------------------------------------+----------+-----------+---------------------+
70 rows in set (0.001 sec)

DB-STRU-3

(7)  desc lt_users;
+---------------------+------------------+------+-----+---------------------+-------------------------------+
| Field               | Type             | Null | Key | Default             | Extra                         |
+---------------------+------------------+------+-----+---------------------+-------------------------------+
| id                  | int(10) unsigned | NO   | PRI | NULL                | auto_increment                |
| user                | varchar(15)      | NO   | UNI | NULL                |                               |
| password            | varchar(32)      | NO   |     | NULL                |                               |
| email               | varchar(255)     | NO   |     | NULL                |                               |
| full_name           | varchar(255)     | NO   |     | NULL                |                               |
| about               | text             | YES  |     | NULL                |                               |
| properties          | text             | NO   |     | NULL                |                               |
| status              | int(4)           | NO   |     | 1                   |                               |
| resource_picture_id | int(10)          | NO   |     | 0                   |                               |
| site_admin          | int(10)          | NO   |     | 0                   |                               |
| last_login          | timestamp        | NO   |     | current_timestamp() | on update current_timestamp() |
+---------------------+------------------+------+-----+---------------------+-------------------------------+
11 rows in set (0.001 sec)


(8) select id,user from lt_users;
+----+-------------+
| id | user        |
+----+-------------+
|  1 | tree        |
|  2 | autocar     |
|  3 | u1          |
|  4 | u2          |
|  5 | u3          |
|  6 | u4          |
|  7 | u5          |
|  8 | bman        |
|  9 | sport       |
| 10 | luyf47      |
| 11 | yiyung      |
| 12 | luyp        |
| 13 | sp1         |
| 14 | sp2         |
| 15 | reg         |
| 16 | lib         |
| 17 | chiyen      |
| 18 | jui         |
| 19 | samio       |
| 20 | sp3         |
| 21 | sp4         |
| 22 | hard        |
| 23 | water       |
| 24 | rich        |
| 25 | jacky918115 |
| 26 | teach       |
| 27 | research    |
| 28 | clean       |
| 29 | person      |
| 30 | tlearn      |
| 31 | health      |
| 32 | night       |
| 33 | sj08        |
| 34 | th10        |
| 35 | th15        |
| 36 | ht          |
| 37 | th09        |
| 38 | english     |
| 39 | practice    |
| 40 | th05        |
| 41 | th02        |
| 42 | th16        |
| 43 | th03        |
| 44 | th04        |
| 45 | th08        |
| 46 | mei         |
| 47 | babarahorng |
| 48 | peter0846   |
| 49 | kmvsca      |
| 50 | kmvscb      |
| 51 | th06        |
| 52 | th07        |
| 53 | urea5231    |
| 54 | kmvsc       |
| 55 | ching518    |
| 56 | chunghsuan  |
| 57 | yiyue12     |
| 58 | pupples0718 |
| 59 | s7522076    |
| 60 | apple       |
| 61 | k7475       |
| 62 | shiowcheau  |
| 63 | chijung     |
| 64 | yang4684    |
| 65 | tai         |
| 66 | treehrt     |
| 67 | teacher     |
| 68 | chching     |
| 69 | th11b       |
| 70 | general     |
| 71 | s2          |
| 72 | 2019bm123   |
+----+-------------+
72 rows in set (0.002 sec)

(8-2)  select id, user ,email ,last_login, password  from lt_users order by last_login desc;
+----+-------------+-----------------------------+---------------------+----------------------------------+
| id | user        | email                       | last_login          | password                         |
+----+-------------+-----------------------------+---------------------+----------------------------------+
| 72 | 2019bm123   | 2019bm123@gmail.com         | 2019-10-04 20:40:52 | 80cf7ef41???????????????????74fd |
|  1 | tree        | treehrt@gmail.com           | 2018-06-04 23:09:05 | 89362466???????????????????233cc |

DB-STRU-4

(9)desc lt_articles;
+------------------------+------------------+------+-----+---------------------+-------------------------------+
| Field                  | Type             | Null | Key | Default             | Extra                         |
+------------------------+------------------+------+-----+---------------------+-------------------------------+
| id                     | int(10) unsigned | NO   | PRI | NULL                | auto_increment                |
| date                   | timestamp        | NO   | MUL | current_timestamp() | on update current_timestamp() |
| modification_date      | timestamp        | NO   |     | 0000-00-00 00:00:00 |                               |
| user_id                | int(10) unsigned | NO   | MUL | 0                   |                               |
| blog_id                | int(10) unsigned | NO   | MUL | 0                   |                               |
| status                 | int(5)           | NO   |     | 1                   |                               |
| num_reads              | int(10)          | YES  | MUL | 0                   |                               |
| properties             | text             | NO   |     | NULL                |                               |
| slug                   | varchar(255)     | NO   | MUL | NULL                |                               |
| num_comments           | int(10)          | NO   |     | 0                   |                               |
| num_nonspam_comments   | int(10)          | NO   |     | 0                   |                               |
| num_trackbacks         | int(10)          | NO   |     | 0                   |                               |
| num_nonspam_trackbacks | int(10)          | NO   |     | 0                   |                               |
| global_category_id     | int(10)          | NO   | MUL | 0                   |                               |
| in_summary_page        | tinyint(1)       | NO   |     | 1                   |                               |
+------------------------+------------------+------+-----+---------------------+-------------------------------+
15 rows in set (0.001 sec)

(10)select id,user_id,blog_id, slug from lt_articles where id >=600;
+-----+---------+---------+----------------------------------+
| id  | user_id | blog_id | slug                             |
+-----+---------+---------+----------------------------------+
| 600 |       1 |      71 | 20200615-mysql-selldb-new_id_reg |
| 601 |       1 |      70 | 20200616-python                  |
| 602 |       1 |      70 | 20200619-dev-c                   |
| 603 |       1 |      71 | 20200706-ii                      |
| 604 |       1 |      71 | 20200625-python                  |
| 605 |       1 |      70 | 20200701-javascript              |
| 606 |       1 |      70 | 20200827-android                 |
| 607 |       1 |      70 | 20200831-libreoffice-writer      |
| 608 |       1 |      70 | 20200917-libreoffice-calc-pdf    |
| 609 |       1 |      35 | 20200919-ubutu-1604-matalab      |
| 610 |       1 |      70 | 20201006-libreoffice-scalc       |
+-----+---------+---------+----------------------------------+
11 rows in set (0.003 sec)

select a.id,user_id,blog_id,b.article_id,b.topic,b.text from lt_articles as a,lt_articles_text as b where a.id=b.id order by a.id desc;

DB-STRU-5:lt_articles_text

(11)desc lt_articles_text;
+------------------+---------+------+-----+---------+----------------+
| Field            | Type    | Null | Key | Default | Extra          |
+------------------+---------+------+-----+---------+----------------+
| id               | int(10) | NO   | PRI | NULL    | auto_increment |
| article_id       | int(10) | NO   | MUL | 0       |                |
| text             | text    | NO   |     | NULL    |                |
| topic            | text    | NO   |     | NULL    |                |
| normalized_text  | text    | NO   | MUL | NULL    |                |
| normalized_topic | text    | NO   | MUL | NULL    |                |
| mangled_topic    | text    | NO   |     | NULL    |                |
+------------------+---------+------+-----+---------+----------------+
7 rows in set (0.001 sec)

(12a) select id,article_id,topic from lt_articles_text where id >600;
+-----+------------+-----------------------------------------------------------------------------------------------------------------------------------+
| id  | article_id | topic                                                                                                                             |
+-----+------------+-----------------------------------------------------------------------------------------------------------------------------------+
| 601 |        601 | 20200616用python來可輸入產生費氏數列的程式                                                                                        |
| 602 |        602 | 20200619用JavaScript來編寫氣泡排序法及選擇排序法程式                                                                              |
| 603 |        603 | 20200706數位科技概論II期末上機實作:建立一個收銀機系統資料庫並用SQL指令記錄3筆交易到資料庫中                                       |
| 604 |        604 | 20200625用python設計讀檔程式來算帳                                                                                                |
| 605 |        605 | 20200701用JavaScript設計二分搜尋法                                                                                                |
| 606 |        606 | 20200827將android手機中的照片傳到Ubuntu 電腦                                                                                      |
| 607 |        607 | 20200831用LibreOffice的writer製作組裝一台中階個人電腦的零件選購清單                                                               |
| 608 |        608 | 20200917使用LibreOffice scalc並將指定列印範圍的內容列印成pdf檔                                                                    |
| 609 |        609 | 20200919在UBUTU 1604安裝MatLab                                                                                                    |
| 610 |        610 | 20201006用LibreOffice的scalc電子試算表製作新冠肺炎統計圖表                                                                        |
+-----+------------+-----------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.000 sec)

(12b)select a.id,user_id,blog_id,b.topic from lt_articles as a,lt_articles_text as b where a.id>600 and a.id=b.id;
+-----+---------+---------+-----------------------------------------------------------------------------------------------------------------------------------+
| id  | user_id | blog_id | topic                                                                                                                             |
+-----+---------+---------+-----------------------------------------------------------------------------------------------------------------------------------+
| 601 |       1 |      70 | 20200616用python來可輸入產生費氏數列的程式                                                                                        |
| 602 |       1 |      70 | 20200619用JavaScript來編寫氣泡排序法及選擇排序法程式                                                                              |
| 603 |       1 |      71 | 20200706數位科技概論II期末上機實作:建立一個收銀機系統資料庫並用SQL指令記錄3筆交易到資料庫中                                       |
| 604 |       1 |      71 | 20200625用python設計讀檔程式來算帳                                                                                                |
| 605 |       1 |      70 | 20200701用JavaScript設計二分搜尋法                                                                                                |
| 606 |       1 |      70 | 20200827將android手機中的照片傳到Ubuntu 電腦                                                                                      |
| 607 |       1 |      70 | 20200831用LibreOffice的writer製作組裝一台中階個人電腦的零件選購清單                                                               |
| 608 |       1 |      70 | 20200917使用LibreOffice scalc並將指定列印範圍的內容列印成pdf檔                                                                    |
| 609 |       1 |      35 | 20200919在UBUTU 1604安裝MatLab                                                                                                    |
| 610 |       1 |      70 | 20201006用LibreOffice的scalc電子試算表製作新冠肺炎統計圖表                                                                        |
+-----+---------+---------+-----------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.001 sec)

12c select text from  lt_articles_text WHERE  article_id =610;

12d UPDATE lt_articles_text SET  text = REPLACE(text,"http://oldwww.","http://lfwiki.") WHERE  article_id =610;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1  Changed: 1  Warnings: 0

12e UPDATE lt_articles_text SET  text = REPLACE(text,"http://oldwww.","http://lfwiki.") WHERE  article_id =608;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1  Changed: 1  Warnings: 0

UPDATE lt_articles_text SET  text = REPLACE(text,"http://oldwww.","http://lfwiki.") WHERE  article_id >=471 and article_id <=480 ;

(12f) select a.id,user_id,blog_id,b.article_id,b.topic from lt_articles as a,lt_articles_text as b where a.id=b.article_id order by a.id desc;

(12g)  UPDATE lt_articles_text SET  text = REPLACE(text,"http://web.","http://lfwiki.") WHERE  article_id =535;   
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(12h) UPDATE lt_articles_text SET  text = REPLACE(text,"http://lfwiki.kmvs.km.edu.tw/lf/index.php?op=ViewArticle&articleId=","http://lfwiki.kmvs.km.edu.tw/view_article.php?article_id=") WHERE  article_id =535;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1  Changed: 0  Warnings: 0

(12i)
MariaDB [utf8_lf_db]> select max(id) from lt_articles;
+---------+
| max(id) |
+---------+
|     610 |
+---------+
1 row in set (0.000 sec)
insert into lt_articles () 

insert into lt_articles_text (article_id ,topic,text,normalized_text,normalized_topic,mangled_topic) values (611,"20201210Test","20201210Test","20201210","20201210","20201210");

Query OK, 1 row affected (0.004 sec)
 insert into lt_articles (user_id,blog_id,status,properties,slug) values (1,70,0,"20201210","20201210");
Query OK, 1 row affected (0.000 sec)

MariaDB [utf8_lf_db]> select max(id) from lt_articles;
+---------+
| max(id) |
+---------+
|     611 |
+---------+
1 row in set (0.000 sec)

select * from lt_articles where id=611;
+-----+---------------------+---------------------+---------+---------+--------+-----------+------------+----------+--------------+----------------------+----------------+------------------------+--------------------+-----------------+
| id  | date                | modification_date   | user_id | blog_id | status | num_reads | properties | slug     | num_comments | num_nonspam_comments | num_trackbacks | num_nonspam_trackbacks | global_category_id | in_summary_page |
+-----+---------------------+---------------------+---------+---------+--------+-----------+------------+----------+--------------+----------------------+----------------+------------------------+--------------------+-----------------+
| 611 | 2020-12-10 00:56:16 | 0000-00-00 00:00:00 |       1 |      70 |      0 |         0 | 20201210   | 20201210 |            0 |                    0 |              0 |                      0 |                  0 |               1 |
+-----+---------------------+---------------------+---------+---------+--------+-----------+------------+----------+--------------+----------------------+----------------+------------------------+--------------------+-----------------+
1 row in set (0.000 sec)



DB-STRU-6

(11)desc lt_gallery_albums;
+------------------------+--------------+------+-----+---------------------+-------------------------------+
| Field                  | Type         | Null | Key | Default             | Extra                         |
+------------------------+--------------+------+-----+---------------------+-------------------------------+
| id                     | int(10)      | NO   | PRI | NULL                | auto_increment                |
| owner_id               | int(10)      | NO   | MUL | 0                   |                               |
| description            | text         | NO   |     | NULL                |                               |
| name                   | varchar(255) | NO   |     | NULL                |                               |
| flags                  | int(10)      | NO   |     | 0                   |                               |
| parent_id              | int(10)      | NO   | MUL | 0                   |                               |
| date                   | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
| properties             | text         | NO   |     | NULL                |                               |
| show_album             | tinyint(1)   | YES  |     | 1                   |                               |
| normalized_description | text         | NO   | MUL | NULL                |                               |
| normalized_name        | varchar(255) | NO   | MUL | NULL                |                               |
| mangled_name           | varchar(255) | NO   | MUL | NULL                |                               |
| num_resources          | int(10)      | NO   |     | 0                   |                               |
| num_children           | int(10)      | NO   |     | 0                   |                               |
+------------------------+--------------+------+-----+---------------------+-------------------------------+
14 rows in set (0.001 sec)

(12) 
select id,owner_id ,name,flags,parent_id,num_resources, mangled_name,normalized_name,num_children,date from lt_gallery_albums where owner_id=70 order by date desc;
+-----+----------+-----------------+-------+-----------+---------------+--------------+-----------------+--------------+---------------------+
| id  | owner_id | name            | flags | parent_id | num_resources | mangled_name | normalized_name | num_children | date                |
+-----+----------+-----------------+-------+-----------+---------------+--------------+-----------------+--------------+---------------------+
| 271 |       70 | 20191130重修    |     1 |         0 |            10 | 20191130ea   | 20191130重修    |            0 | 2019-11-30 02:42:17 |
| 270 |       70 | IT              |     1 |         0 |            68 | it           | IT              |            0 | 2019-10-13 21:49:53 |
| 267 |       70 | twosm           |     1 |         0 |            16 | twosm        | twosm           |            0 | 2019-04-06 16:28:34 |
| 266 |       70 | qemu            |     1 |         0 |             1 | qemu         | qemu            |            0 | 2019-03-14 21:30:11 |
| 265 |       70 | pos             |     1 |         0 |             0 | pos          | pos             |            0 | 2019-03-14 03:57:37 |
| 264 |       70 | vb2019          |     1 |         0 |            15 | vb2019       | vb2019          |            0 | 2019-02-12 20:57:43 |
| 263 |       70 | netBoot         |     1 |         0 |             9 | netboot      | netBoot         |            0 | 2019-01-25 10:49:34 |
| 262 |       70 | xml             |     1 |         0 |            25 | xml          | xml             |            0 | 2018-09-12 02:27:51 |
| 261 |       70 | 107餐一         |     1 |         0 |            14 | 107ea        | 107餐一         |            0 | 2018-09-04 22:00:41 |
| 260 |       70 | 107商二         |     1 |         0 |            41 | 107a         | 107商二         |            0 | 2018-09-02 23:55:56 |
| 259 |       70 | mysqlc          |     1 |         0 |            75 | mysqlc       | mysqlc          |            0 | 2018-05-19 10:07:14 |
| 258 |       70 | 2020-2018重修   |     1 |         0 |            45 | 2018ea       | 2020-2018重修   |            0 | 2018-05-14 05:01:16 |
| 257 |       70 | 106-107商一     |     1 |         0 |             9 | 106a         | 106-107商一     |            0 | 2018-04-20 02:04:38 |
| 255 |       70 | 2017重修        |     1 |         0 |             6 | 2017ea       | 2017重修        |            0 | 2017-12-18 04:39:39 |
| 252 |       70 | 106農資         |     1 |         0 |            12 | 106ee        | 106農資         |            0 | 2017-09-17 22:09:33 |
| 251 |       70 | 106餐一         |     1 |         0 |            16 | 106ea        | 106餐一         |            0 | 2017-09-04 23:56:07 |
| 250 |       70 | 106商二         |     1 |         0 |            30 | 106a         | 106商二         |            0 | 2017-09-01 03:12:37 |
| 249 |       70 | 電腦繪圖        |     1 |         0 |           105 | eec          | 電腦繪圖        |            0 | 2017-06-24 19:13:42 |
| 247 |       70 | readgpx         |     1 |         0 |            91 | readgpx      | readgpx         |            0 | 2017-04-15 21:47:02 |
| 246 |       70 | POS-SQL         |     1 |         0 |            16 | pos-sql      | POS-SQL         |            0 | 2016-11-20 22:51:29 |
| 245 |       70 | 105農資         |     1 |         0 |            11 | 105ee        | 105農資         |            0 | 2016-10-31 23:55:32 |
| 244 |       70 | 105商二         |     1 |         0 |            18 | 105a         | 105商二         |            0 | 2016-09-20 00:04:20 |
| 243 |       70 | 105餐一         |     1 |         0 |            17 | 105ea        | 105餐一         |            0 | 2016-09-06 22:10:19 |
| 242 |       70 | wav             |     1 |         0 |             6 | wav          | wav             |            0 | 2016-06-19 21:40:33 |
| 241 |       70 | mp3             |     1 |         0 |            13 | mp3          | mp3             |            0 | 2016-06-19 21:07:53 |
| 237 |       70 | fruita          |     1 |         0 |             5 | fruita       | fruita          |            0 | 2016-02-29 20:49:07 |
| 235 |       70 | 104商二         |     1 |         0 |            50 | 104a         | 104商二         |            0 | 2015-09-06 22:21:07 |
| 234 |       70 | 104餐一文書     |     1 |         0 |            17 | 104ea        | 104餐一文書     |            0 | 2015-09-03 01:18:23 |
| 233 |       70 | 104計用         |     1 |         0 |             2 | 104ec        | 104計用         |            0 | 2015-09-01 01:30:54 |
| 232 |       70 | 104農資         |     1 |         0 |            20 | 104ee        | 104農資         |            0 | 2015-08-30 23:52:03 |
| 230 |       70 | 農資            |     1 |         0 |            27 | ee           | 農資            |            0 | 2015-03-24 00:17:26 |
| 228 |       70 | gdb             |     1 |         0 |           181 | gdb          | gdb             |            0 | 2014-11-14 21:54:01 |
| 226 |       70 | Excel教學       |     1 |         0 |             3 | excel        | Excel教學       |            0 | 2014-07-02 21:26:13 |
| 225 |       70 | 一般            |     1 |         0 |           169 | ae           | 一般            |            0 | 2014-07-02 21:25:32 |
+-----+----------+-----------------+-------+-----------+---------------+--------------+-----------------+--------------+---------------------+
34 rows in set (0.001 sec)



DB-STRU-7

(13) desc lt_gallery_resources;
+------------------------+--------------+------+-----+---------------------+-------------------------------+
| Field                  | Type         | Null | Key | Default             | Extra                         |
+------------------------+--------------+------+-----+---------------------+-------------------------------+
| id                     | int(10)      | NO   | PRI | NULL                | auto_increment                |
| owner_id               | int(10)      | NO   | MUL | 0                   |                               |
| album_id               | int(10)      | NO   | MUL | 0                   |                               |
| description            | text         | YES  |     | NULL                |                               |
| date                   | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
| flags                  | int(10)      | YES  |     | 0                   |                               |
| resource_type          | int(3)       | YES  | MUL | NULL                |                               |
| file_path              | varchar(255) | YES  |     | NULL                |                               |
| file_name              | varchar(255) | YES  | MUL | NULL                |                               |
| file_size              | int(20)      | NO   |     | 0                   |                               |
| metadata               | text         | YES  |     | NULL                |                               |
| thumbnail_format       | varchar(4)   | NO   |     | same                |                               |
| normalized_description | text         | NO   | MUL | NULL                |                               |
| properties             | text         | NO   |     | NULL                |                               |
+------------------------+--------------+------+-----+---------------------+-------------------------------+
14 rows in set (0.001 sec)

(14)
select id,date,file_name,properties  from lt_gallery_resources where id>2500;
+------+---------------------+------------------------------------------------------------------------------------------------------------------+------------+
| id   | date                | file_name                                                                                                        | properties |
+------+---------------------+------------------------------------------------------------------------------------------------------------------+------------+
| 2501 | 2020-08-31 20:55:54 | 20200901鍵盤指法與中文輸入法.pdf                                                                                 | a:0:{}     |
| 2502 | 2020-08-31 21:00:14 | 20200901鍵盤指法與中文輸入法.doc                                                                                 | a:0:{}     |
| 2503 | 2020-08-31 21:00:48 | 2503-20200901鍵盤指法與中文輸入法.pdf                                                                            | a:0:{}     |
| 2505 | 2020-09-07 21:21:05 | 20200908段落分欄圖案頁頁首尾頁面框線截圖.doc                                                                     | a:0:{}     |
| 2506 | 2020-09-07 21:21:06 | 20200908段落分欄圖案頁頁首尾頁面框線截圖.pdf                                                                     | a:0:{}     |
| 2507 | 2020-09-14 23:50:00 | pcdiy空表.odt                                                                                                    | a:0:{}     |
| 2508 | 2020-09-15 02:01:37 | 20200915定位點分欄及表格文字方塊、文字藝術師、圖表、繪圖及浮水印、自動編號.docx                                  | a:0:{}     |
| 2509 | 2020-09-21 21:51:07 | 20200922標籤製作及統計圖表製作.pdf                                                                               | a:0:{}     |
| 2510 | 2020-09-23 04:18:08 | 20200923B1.png                                                                                                   | a:0:{}     |
| 2512 | 2020-09-23 04:18:09 | 20200923B3.png                                                                                                   | a:0:{}     |
| 2513 | 2020-09-23 04:18:10 | 20200923B4.png                                                                                                   | a:0:{}     |
| 2514 | 2020-09-23 04:53:34 | 20200923B2.png                                                                                                   | a:0:{}     |
| 2515 | 2020-09-28 21:18:15 | 20200929合併列.pdf                                                                                               | a:0:{}     |
| 2517 | 2020-10-03 05:20:32 | 20201002-03嘉興橋登十字路山神代山下茂安橋.gpx                                                                    | a:0:{}     |
| 2518 | 2020-10-06 01:28:18 | 20201006WORD合併列印.png                                                                                         | a:0:{}     |
+------+---------------------+------------------------------------------------------------------------------------------------------------------+------------+
15 rows in set (0.001 sec)


(14b) select id,date,file_name from lt_gallery_resources where owner_id=71 order by date desc;
:
| 2111 | 2019-10-14 03:08:41 | 25商一.mp3                                                                                                       |
| 2110 | 2019-10-14 03:06:42 | 1571036047584.mp3                                                                                                |
| 2109 | 2019-10-14 03:05:41 | 28商一.mp3                                                                                                       |
| 2108 | 2019-10-14 03:04:06 | 商一22.mp3                                                                                                       |
| 2107 | 2019-10-14 03:00:15 | 商一29.mp3                                                                                                       |
| 2106 | 2019-10-14 02:57:59 | 商一11.mp3                                                                                                       |
| 2105 | 2019-10-14 02:47:13 | 商一23.mp3                                                                                                       |
| 2097 | 2019-10-08 20:59:39 | 20191014商經一數位科技概論聲音數位化上機實作.doc                                                                 |
| 2096 | 2019-10-08 20:31:04 | 20191009數位科技概論上機作業一.odt                                                                               |
| 2085 | 2019-10-04 20:37:54 | 20160608waterRoad.JPG                                                                                            |
+------+---------------------+------------------------------------------------------------------------------------------------------------------+
104 rows in set (0.001 sec)


(14c)  select id,date,file_name from lt_gallery_resources where owner_id=70 and album_id=228  order by date desc;
:
|  917 | 2014-11-20 19:44:44 | 20070902崙埤池之旅.gdb                                                                                                       |
|  916 | 2014-11-20 19:42:52 | 20070125_27南山村則前橋夫步爾溪登雪山山脈北稜探查past.gdb                                                                    |
|  915 | 2014-11-20 19:42:52 | 20070125_27南山村則前橋夫步爾溪登雪山山脈北稜探查oldwu.gdb                                                                   |
|  914 | 2014-11-20 19:42:52 | 20060430_大礁溪登中阿玉O型回原地.gdb                                                                                         |
|  913 | 2014-11-20 19:39:55 | 20131011新寮山b.gdb                                                                                                          |
|  912 | 2014-11-20 19:38:18 | 20131207-08霞喀羅古道西段b.gdb                                                                                               |
|  909 | 2014-11-20 19:34:22 | 20140223坪林鄉大林村倒吊蓮倒吊嶺倒吊子山.gdb                                                                                 |
|  910 | 2014-11-20 19:34:22 | 20140216坪林鄉大林村倒吊蓮倒吊嶺山O型.gdb                                                                                    |
|  911 | 2014-11-20 19:34:22 | 20130706_08喀拉業北稜.gdb                                                                                                    |
|  908 | 2014-11-20 19:31:27 | 20140301平堵蘇厝登磨婆寮山倒吊子山.gdb                                                                                       |
|  907 | 2014-11-20 19:29:49 | 20090325_29南湖大山馬比杉山.gdb                                                                                              |
|  906 | 2014-11-20 19:23:20 | 20141109_1116四堵山二回.gdb                                                                                                  |
|  905 | 2014-11-15 04:51:09 | 20141109四堵山O型.gdb                                                                                                        |
|  904 | 2014-11-14 21:57:00 | 20140820-25舞樂妹池.gdb                                                                                                      |
|  903 | 2014-11-14 21:56:03 | 20141026紅柴山東北稜.gdb                                                                                                     |
|  902 | 2014-11-14 21:54:30 | 20140913蘇澳七星嶺.gdb                                                                                                       |
+------+---------------------+------------------------------------------------------------------------------------------------------------------------------+
181 rows in set (0.001 sec)

lf file

(14d) select id,owner_id,album_id,date,file_name from lt_gallery_resources where owner_id=70 and album_id=228  order by date desc;

(14e) cat lf/class/file/file.class.php
]# ls lf/class/file -l
total 28
-rwxr-xr-x. 1 admin admin 15098 Sep 29  2007 file.class.php
-rwxr-xr-x. 1 admin admin  3215 Oct  4  2006 fileproperties.class.php
-rwxr-xr-x. 1 admin admin  2407 Dec 17  2006 fileupload.class.php
-rwxr-xr-x. 1 admin admin  4011 Apr  8  2007 fileuploads.class.php
drwxr-xr-x. 2 admin admin    34 Oct 26 04:57 finder
drwxr-xr-x. 2 admin admin   205 Jan 13  2011 unpacker

(14f)# cat lf/class/file/fileupload.class.php

(14g)# ls lf/class/file/finder/ -l
total 4
-rwxr-xr-x. 1 admin admin 3159 Oct  4  2006 filefinder.class.php

(14h)
]# cat lf/class/file/finder/filefinder.class.php
<?php

        /**
         * \ingroup File
         *
         * given an array with strings and a folder on disk, this class will scan the folder and compare
         * them with the ones in the array and return which ones from disk do NOT exist in the given array.
         * In order to get the array with the elements that are new according to our first array, please
         * use FileFinder::getNew()
         * @see getNew
         */
        class FileFinder
        {
                var $_folder;
                var $_removed;
                var $_new;
                var $_recursive;

                function FileFinder( $folder = '.' )
                {
                        $this->_folder = $folder;
                        $this->_removed = Array();
                        $this->_new = Array();
                        $this->_recursive = true;
                }

                /**
                 * given a file, generates a key for it so that it can be checked whether
                 * it really belongs to the $currentList array or not. This is useful in case of when we're
                 * for example looking for new template files and we'd like to know whether locale_en_UK.php
                 * is alreasdy in the list or not... This class can be extended to reimplement getKeyForFile
                 * so that $this->getKeyForFile( "locale_en_UK.php" ) returns "en_UK" and so forth.
                 * By default is returns the same name but if null is returned for a particular file, this
                 * file will be ignored.
                 *
                 * @param fileName tha name of the file
                 * @return the key for the given file. If null is returned, this file will be ignored.
                 */
                function getKeyForFile( $fileName )
                {
                        return( $fileName );
                }

                /**
                 * uses Glob::glob() to find files that match the given format
                 * and compares the file names against the ones already available
                 *
                 * @param currentList
                 * @param fileName
                 */
                function find( $currentList, $fileName = '*' )
                {
                        lt_include( PLOG_CLASS_PATH.'class/misc/glob.class.php' );
                        $files = Glob::myGlob( $this->_folder, $fileName );

                        // create an empty array if we got something else other than an array so that
                        // we can avoid some ugly error messages!
                        if( !is_array($currentList))
                                $currentList = Array();

                        // loop through the files...
                        foreach( $files as $file ) {
                                // get the key for the given file
                                $key = $this->getKeyForFile( $file );
                                if( $key != null && !in_array( $key, $currentList )) {
                                        // the file is new!
                                        $this->_new[] = $key;
                                }
                        }

                        // and now see which files are new and which ones have been removed, by comparing
                        // both arrays...

                        return true;
                }

        function findBinary( $binary, $searchFolders ) {
            if( $searchFolders == null )
                $searchFolders = Array( $this->_folder );

                        $found = false;
                        $i = 0;
                        while( !$found && $i < count($searchFolders)) {
                                // get the current folder
                                $currentFolder = $searchFolders[$i];
                                // see if the file's there
                                $fullPath = $currentFolder.$binary;
                                if( File::isReadable( $fullPath ))
                                        $found = true;
                                else
                                        $i++;
                        }

                        if( $found )
                                return $fullPath;
                        else
                                return "";
        }

                /**
                 * returns all the new items that are not in the list that was passed as a parameter to the
                 * FileFinder::find() method
                 *
                 * @return An array with the new items
                 */
                function getNew()
                {
                        return( $this->_new );
                }
        }
?>


CODE db.php

 cat db.php
:
<?php
include_once( "db.inc" );
$conn = mysqli_connect( $DBHOST, $DBUSER, $DBPASSWD, $DBNAME);
if (empty($conn)){
  print mysqli_error($conn);
  die ("無法連結資料庫");
  exit;
}
echo "<table border=1><tr bgcolor=yellow><td>id <td> user_id <td> blog_id <td>topic \n";
mysqli_query( $conn, "SET NAMES 'utf8'");
$sql="select a.id,user_id,blog_id,b.article_id,b.topic from lt_articles as a,lt_articles_text as b where a.id=b.article_id order by a.id desc;";
$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
        echo "<tr><td bgcolor=yellow>" . $row['article_id'] . "<td>" . $row['user_id'] . "<td>" .
             $row['blog_id'] . "<td>" . "<a href='view_article.php?article_id=" . $row['article_id']  ."'>"
        . $row['topic'] . "</a>\n";
}
echo "</table>\n";
mysqli_free_result ( $result );
echo "主機資訊: " . mysqli_get_host_info($conn) . "<hr>\n";
mysqli_close($conn);
?>
:

code:folder.php

<?php
include_once( "db.inc" );
$conn = mysqli_connect( $DBHOST, $DBUSER, $DBPASSWD, $DBNAME);
if (empty($conn)){
  print mysqli_error($conn);
  die ("無法連結資料庫");
  exit;
}
echo "<table border=1><tr bgcolor=yellow><td>id  <td>name <td>mangled_name <td>normalized_name <td>num_resources <td>num_children <td> date <td>owner_id\n";
mysqli_query( $conn, "SET NAMES 'utf8'");
$sql="select id,owner_id ,name,flags,parent_id,num_resources, mangled_name,normalized_name,num_children,date from lt_gallery_albums where owner_id=70 order by date desc;";

$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
        echo "<tr><td bgcolor=lightgreen>" . $row['id']
                . "<td> <a href=cd.php?album_id=" . $row['id'] . ">" . $row['name'] . "</a>"
                . "<td>" . $row['mangled_name']
                . "<td>" . $row['normalized_name']
                . "<td>" . $row['num_resources']
                . "<td>" . $row['num_children']
                . "<td>" . $row['date']
                . "<td>" . $row['owner_id'] ;

}
echo "</table>\n";
$rowcount=mysqli_num_rows($result);
printf("計 %d 筆資料!\n",$rowcount);

mysqli_free_result ( $result );
echo "主機資訊: " . mysqli_get_host_info($conn) . "<hr> sql= $sql <hr>";
mysqli_close($conn);
?>

uploadgpx.htm


<!DOCTYPE html>
<html>
<body>

<form action="uploadgpx.php" method="post" enctype="multipart/form-data">
  Select gpx file to upload:
  <input type="file" name="fileToUpload" id="fileToUpload">
  <input type="submit" value="Upload GPX" name="submit">
</form>

</body>
</html>

uploadgpx.php

<?php
$target_dir = "gpx/";
$target_file = $target_dir . basename($_FILES["fileToUpload"]["name"]);
$uploadOk = 1;
$FileType = strtolower(pathinfo($target_file,PATHINFO_EXTENSION));
if(isset($_POST["submit"])) {
  echo "target = $target_file  FileType = $FileType<hr>"  ;
  $uploadOk = 1;
}

if (file_exists($target_file)) {
  echo "Sorry, $target_file already exists.";
  $uploadOk = 0;
} 

if($FileType != "gpx" ) {
  echo "Sorry, only gpx files are allowed.";
  $uploadOk = 0;
}


if ($uploadOk == 0) {
  echo "Sorry, your file was not uploaded.";
} else {
  if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file)) {
    echo "The file ". htmlspecialchars( basename( $_FILES["fileToUpload"]["name"])). " has been uploaded.";
  } else {
    echo "Sorry, there was an error uploading your file.";
  }
}

?>