查看“Sqlite”的源代码

SQLite3 安装及开发

=== Install ===
 wget <nowiki>https://sqlite.org/2018/sqlite-autoconf-3220000.tar.gz</nowiki>
 tar xzvf sqlite-autoconf-3220000.tar.gz 

 ./configure --prefix=/usr/sqlite3
 make
 make install

 cd /usr/sqlite3/bin/
 sqlite3

=== DBA ===
 # 收缩文件
 # 在建表语句中，PRAGMA auto_vacuum = 1
 vacuum
 
 # 表结构
 select * from sqlite_master where type="table" and name="test";
 
 # 转储 SQL 脚本，重新导入
 sqlite3 database.db .dump > dump.sql
 sqlite3 new_database.db < dump.sql

=== 导入数据 ===
 .separator "," 
 .import tw1_bht.txt tw1_bht 

=== 分页 ===
Sqlite 通过关键字 limit 限制
 select * from tw1_bht limit 10;           --显示10行
 select * from tw1_bht limit 1 offset 2;   --过滤2行，显示1行
 select * from tw1_bht limit 1, 2;         --过滤1行，显示2行
 select * from tw1_bht limit 1000000, 10;
 
 select count(*) from tw1_bht;
 select company_id, count(*) from tw1_bht group by company_id;

=== C++编译 ===
 g++ t1.cpp -o t1 -lsqlite3 -L/usr/sqlite3/lib

==== ERROR ====
Error: database disk image is malformed

SQLite 数据库文件已损坏。这意味着文件的结构不再符合 SQLite 的预期，导致文件无法读取或不可靠。

 sqlite3 database.db "PRAGMA integrity_check;"
其它
 # 主要检查无序记录和丢失的页面
 qlite3 your_database.db "PRAGMA quick_check;"
 
 # 尝试从损坏的数据库中提取尽可能多的数据
 sqlite3 database.db ".recover" > recovered.sql
 sqlite3 new_database.db < recovered.sql

=== Sample ===
 <small><nowiki>select   id             ||'|'||
         company_id.    ||'|'||
         group_id       ||'|'||
         shop_id        ||'|'||
         staff_team     ||'|'||
         house_id       ||'|'||
         trace_type     ||'|'||
         translate(trace_memo, '|'||chr(10)||chr(13), '/ ')||'|'||
         to_char(ct, 'yyyymmddhh24miss') ||'|'||
         staff_id       ||'|'||
         is_to_a        ||'|'||
         (house_id - trunc(house_id/100)*100)
from     dw.tw1_buy_house_trace
where    1=1
and      house_id - trunc(house_id/100)*100 = 0

create table tw1_bht (
  id number(22),
  company_id number(22),
  group_id number(22),
  shop_id number(22),
  staff_team number(22),
  house_id number(22),
  trace_type number(22),
  trace_memo varchar2(4000),
  ct number(14),
  staff_id number(22),
  is_to_a number(22),
  part_id number(2)
);</nowiki></small>

[[分类:Develop]]
[[分类:DB]]
[[分类:OtherDB]]